mysql 问题

我们在平时工作中，面试中都会用到mysql,那么随之而来的就是许多问题，例事务，索引等，下面说的就是这些问题

MySql的存储引擎有哪些他们的区别

存储引擎查看

MySQL给开发者提供了查询存储引擎的功能，使用 SHOW ENGINES 可以查看。

如果要想查看数据库默认使用哪个引擎，可以通过使用命令：

SHOW VARIABLES LIKE ‘storage_engine’;

Support列的值表示某种引擎是否能使用：YES表示可以使用、NO表示不能使用、DEFAULT表示该引擎为当前默认的存储引擎。

下面来看一下其中几种常用的引擎。

InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有：

InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合
InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
InnoDB被用在众多需要高性能的大型数据库站点上

InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物

MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问

Mysql分表之后想让一个id多个表是自增的

雪花算法 Snowflake
第三个库，设计一张表，每个表插入前的主键由此表生成
使用redis生成主键

事物的四个特性和隔离级别

四个特性（ACID）

Atomic（原子性）：事务中包含的操作被看做一个逻辑单元，这个逻辑单元中的操作要么全部成功，要么全部失败
Consistency（一致性）：事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏，事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
Isolation（隔离性）：事务允许多个用户对同一个数据进行并发访问，而不破坏数据的正确性和完整性。同时，并行事务的修改必须与其他并行事务的修改相互独立
Durability（持久性）：事务结束后，事务处理的结果必须能够得到固化

隔离级别

Read uncommitted(读未提交)

如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据，该隔离级别可以通过“排他写锁”，但是不排斥读线程实现。这样就避免了更新丢失，却可能出现脏读，也就是说事务B读取到了事务A未提交的数据

Read committed(读提交)

如果是一个读事务(线程)，则允许其他事务读写，如果是写事务将会禁止其他事务访问该行数据，该隔离级别避免了脏读，但是可能出现不可重复读。事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变

Repeatable read(可重复读取)

可重复读取是指在一个事务内，多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据(包括了读写)，这样就可以在同一个事务内两次读到的数据是一样的，因此称为是可重复读隔离级别，读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务(包括了读写)，这样避免了不可重复读和脏读，但是有时可能会出现幻读。(读取数据的事务)可以通过“共享读镜”和“排他写锁”实现。

Serializable(可序化)

提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行，如果仅仅通过“行级锁”是无法实现序列化的，必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也是最高的，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读

隔离级别查看和设置

查看：SELECT @@tx_isolation; 设置：set transaction isolation level repeatable reda; 或者 set tx_isolation=’read-uncommitted’

记住：设置数据库的隔离级别一定要是在开启事务之前

mysql 锁表解决办法

锁表查询代码 select * from v$locked_object;
查看那个表被锁 select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
查看是哪个session引起的 select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
查看是哪个sql引起的 select b.username,b.sid,b.serial#,c.* from v$locked_object a,v$session b,v$sql c where a.session_id = b.sidand b.SQL_ID = c.sql_id order by b.logon_time;
杀掉对应进程执行命令：alter system kill session ‘1025,41’; 其中1025为sid,41为serial#.

索引失效场景

Least Frequently Used，最不经常使用。在一段时间内，数据被使用次数最少的，优先被淘汰。

高并发下如何做到安全的修改同一行数据

乐观锁

在表中新增一列该列不参与业务逻辑，仅记录数据版本

A 线程准备往小明的账户上加100， 1, 读取到小明有 1000 元， 1000 + 100 事务未提交，读取到的版本号（oversion）为0;
B线程准备往小明的账户上加100， 1, 读取到小明有 1000 元， 1000 + 100 事务未提交，读取到的版本号（oversion）为0;
线程提交事务，对比版本号，如果数据库该条数据的版本号和线程所持有该条数据的版本号一致，说明数据没有修改过。更新余额以及版本号小明账户余额变成1100 版本号（version）变成+ 1
B 线程提交事务对比版本号，发现说持有的数据和数据中的版本不一致。本次事务回滚

悲观锁

读取的时候就对该数据加锁（排他锁），比如对id 为 1 的数据加了锁，其余的线程加锁读取该数据时将被阻塞

    START TRANSACTION;
    SELECT * FROM USER WHERE id = 1 FOR UPDATE ;

每次读取时总认为可能数据会被修改，每次查询都加锁。被锁住的数据同时只能有一个线程读取，因此称为悲观锁

乐观锁和悲观锁各自适合的场景

乐观锁适合查多改少，经常被并发修改的数据可能老是出现版本不一致导致有的线程操作常常失败。
悲观锁适合短事务（长事务导致其它事务一直被阻塞，影响系统性能），查少改多

INNODB的行级锁有哪2种

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁

karl