mysql面试总结

主键索引（聚簇索引）

聚集索引：指索引项的排序方式和表中数据记录排序方式一致的索引。聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。

叶子节点包含完整的数据记录

辅助索引（非聚簇索引）

叶子节点只包含主键索引的id

覆盖索引

Extra列
Using index：使用覆盖索引
覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。

例如，通过创建一个联合索引覆盖多个列，可以优化涉及这些列的查询。如果查询的字段都可以从索引树中获取，那么这个查询就利用了覆盖索引。

回表

第一次搜索 B+Tree 拿到主键值后再去搜索主键索引的 B+Tree，这个过程就是所谓的回表。

最左前缀法则

最左前缀法则是数据库索引使用中的一个重要原则，特别是在处理复合索引时。这一原则要求查询从索引的最左列开始，并且不跳过索引的列。如果查询跳跃了索引中的某一列，那么索引部分失效（跳过列之后的索引失效）。此外，如果查询中出现了范围查询（如>、<操作），那么也会导致索引列后的索引失效。另外，中间字段断开也会导致索引失效。

备注：Order by与Group by优化，的时候尽量满足最左前缀法则， 或者覆盖索引。
能用覆盖索引最好使用覆盖索引

索引下推

对于辅助的联合索引(name,age)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like 'aa%'AND age = 11 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age，无法很好的利用索引。

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘aa’ 开头的索引之后，同时还会在索引里过滤age字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。
索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

索引设计原则

1.先设计表，后上索引
2 尽量少建单值索引，用联合索引，尽量满足最左前缀原则。
3.不要在小基数字段上建立索引
4.where与order by冲突时优先where

隔离级别

Mysql默认的事务隔离级别是可重复读

脏读

事务A读取到了事务B已经修改但尚未提交的数据

不可重复读
事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性

幻读
事务A读取到了事务B提交的新增数据，不符合隔离性

可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制，select操作是快照读（历史版本）；insert、update和delete是当前读（当前版本）

快照读
读取的是记录数据的可见版本（有旧的版本）。不加锁,普通的select语句都是快照读,如：
select * from core_user where id > 2

当前读
读取的是记录数据的最新版本，显式加锁的都是当前读
select * from core_user where id > 2 for update; select * from account where id>2 lock in share mode;

MVCC
MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前永远都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成read-view)

Read View
Read View是事务执行SQL语句时，产生的读视图。实际上在innodb中，每个SQL语句执行前都会得到一个Read View。

Read View主要是用来做可见性判断的，即判断当前事务可见哪个版本的数据。

Read view 的几个重要属性

• m_ids:当前系统中那些活跃(未提交)的读写事务ID, 它数据结构为一个List。
• min_limit_id:表示在生成Read View时，当前系统中活跃的读写事务中最小的事务id，即m_ids中的最小值。
• max_limit_id:表示生成Read View时，系统中应该分配给下一个事务的id值。
• creator_trx_id: 创建当前Read View的事务ID

Read view 匹配条件规则如下：

• . 1.如果数据事务ID trx_id < min_limit_id，表明生成该版本的事务在生成Read
  View前，已经提交(因为事务ID是递增的)，所以该版本可以被当前事务访问。
• 2.如果trx_id>= max_limit_id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。
• 3.如果 min_limit_id =<trx_id< max_limit_id，需要分3种情况讨论
  （1）.如果m_ids包含trx_id,则代表Read View生成时刻，这个事务还未提交，但是如果数据的trx_id等于creator_trx_id的话，表明数据是自己生成的，因此是可见的。
  （2）如果m_ids包含trx_id，并且trx_id不等于creator_trx_id，则Read View生成时，事务未提交，并且不是自己生产的，所以当前事务也是看不见的；
  （3）.如果m_ids不包含trx_id，则说明你这个事务在Read View生成之前就已经提交了，修改的结果，当前事务是能看见的。