MySQL重难点+常见误区整理
MySQL之索引
- 无论如何，聚簇索引是一定存在的（每张表都一定会创建一棵聚簇索引树）。有以下规则：
（1）如果有主键，Innodb会把主键作为聚簇索引。
（2）如果没有主键，Innodb会选择第一个不包含有 NULL 值的唯一索引作为主键索引。
（3）如果没有主键且没有唯一索引，Innodb会选择内置的rowId（Innodb内部对每一行数据维护了一个递增的rowId）作为聚簇索引。
索引最左匹配原则的理解
如有索引(a,b,c),先有以下几种情况，求用到的索引字段为?
（1）where a = ? and c = ?
只会用到索引(a)，c是不会用到。索引是不允许跳过的。
（2） where b = ？
索引不允许跳过，因此没用上索引。
(3) where b = ? and a = ?
会用上（a,b)索引，因为SQL查询器会自动优化。
（4） where a BETWEEN 90 AND 110 and b = ?
会用上（a,b) 索引。
（5）
//索引（a,b,c) a,b,c都是not null约束，int类型的字段
explain select * from test_table where a < 100 and b = 100;//type:range key_len:4
explain select * from test_table where a <= 100 and b = 100;//type:range key_len:8
explain select * from test_table where a > 100 and b = 100;//type:range key_len:4
explain select * from test_table where a >= 100 and b = 100;//type:range key_len:8
另外：一定要往表中插入足够多的数据，如果数据量少到只有几行，type可能变为index（全索引扫描）。
因为a,b,c都在索引上(索引覆盖),所以type是index(全索引扫描)，如果加多个字段d,d不在索引上,select *,type将会变为all(全表扫描).
-
type小知识: type的类型有：system（表只有一条数据),const(主键或者唯一索引的等值查询),eq_ref(主键或唯一索引的链表（等值）查询)，ref(普通索引的等值查询)， range（有使用到索引的范围查询），index(全索引扫描)，all(全表扫描)。
-
MySQL优化器的其他优化策略使索引失效的情况，比如： 发现通过索引扫描的行记录数超过全表的10%-30%时，优化器会放弃走索引，自动变成全表扫描。
冷知识：MySQL也有自适应Hash索引/全文索引
MySQL大家比较清楚的索引是B+树，但是MySQL的InnoDB其实也支持Hash索引，全文索引。
不过InnoDB没有使用纯粹的Hash索引，还是基于B+树索引，不过倒是有叫做自适应哈希索引的机制，无法人为控制那些字段会添加哈希索引。 自适应哈希索引需要通过命令或者配置开启，然后在等值查询的时候可能会生效。
MySQL MVCC难点
- MVCC有两种机制：快照读与当前读
MVCC的快照读方式：快照读很好理解，通过找到这条事务及其事务之前已经存在的记录才会显示出来。 MVCC之当前读方式：基于记录锁+间隙锁的方式实现。
- 那么什么时候会用快照读，什么时候会有当前读？它们触发的时机是怎么样的？
快照读隔离级别可以是Read Committed和Read Repeatable。
当前读 则要求隔离级别至少达到Read Repeatable。(因为可重复读级别下才有间隙锁！！！）
（1）默认普通读select *都只会用到快照读机制；
（2）如果使用update ... where ...会加上记录锁，
或者是select * where ... for update会加上记录锁或者间隙锁（根据锁的是一行记录或者是一个范围加对应不同的锁），
此时就属于当前读的场景。
- MySQL隔离级别为【可重复读】下，能解决幻读问题吗？
首先，MySQL的MVCC机制都主要针对不可重复读的问题的。
幻读广义有两种情况：（1）第二次查比第一次多出一些数据；（2）第二次查比第一次少一些数据。
使用MVCC机制的当前读或者快照读，都可以很大程度上解决幻读问题。
对于当前读，第一次读的时候就给读的区间间隙加锁，期间即使有其他事务进行插入操作，这个插入操作会被阻塞下去。
对于快照读，第二次读的时候，期间即使有其他事务插入了新的数据，ReadView也能找到回滚链开始的数据。
不过，快照读是有缺陷的。phantom.html#第二个发生幻读现象的场景
MySQL之锁的基本误区
MySQL行锁有：记录锁、间隙锁、临键锁、插入意向锁 MySQL表锁有：表读写锁、元数据锁、自增锁、意向锁
-
行锁：行锁锁的对象基于索引，如果没有走索引，就会升级为表锁。 这是十分危险的操作！ 这是十分危险的操作！ 所以
update ... where ...或者是select * where ... for update的SQL语句一定一定要注意条件有用上索引。 -
行锁之间隙锁：只有在Read Repeatable、Serializable隔离级别才有的。
MySQL之意向锁与插入意向锁
MySQL行锁有：记录锁、间隙锁、临键锁、插入意向锁 MySQL表锁有：表读写锁、元数据锁、自增锁、意向锁
这些锁都比较好理解,难点还是在插入意向锁。其中插入意向锁属于行锁，意向锁属于表锁。
插入意向锁
-
插入意向锁属于行锁，它的作用范围与间隙锁都是作用于一个间隙区间。 不过，插入意向锁和间隙锁之间是互斥的。
-
要清楚插入意向锁与间隙锁的区别与联系，必须知道间隙锁和插入意向锁是如何共同工作的？
如有表test(id,其他字段)，id添加了主键索引，两条数据（50,其他字段值)（100,其他字段值)。 A事务往(50,100)的间隙添加间隙锁后，B事务想要往(50,100)这个间隙添加数据， 此时发现区间已经加上间隙锁了，插入操作阻塞，这时候就会创建一个插入意向锁。
- 现在来详细谈谈插入意向锁
（1）还是上面那个例子，A事务往(50,100)的间隙添加间隙锁后，B事务想要往(50,100)这个间隙添加数据，这时候就会创建一个插入意向锁。
插入意向锁类似Java AQS的等待队列CLH的一个节点,也是等待资源释放后（对比->间隙锁事务提交），用Locksupport机制唤醒当前线程（对比->当前事务的插入操作）
不过区别在于即使没有间隙锁，MySQL进行insert插入操作时，也会往插入区间添加插入意向锁，只不过此时不会被阻塞，插入操作也能迅速执行。
（2）另外，补充一点：插入意向锁之间是不会相互排斥的。
意向锁
-
意向锁与插入意向锁完全不同，不止在于它是表锁，它的作用也完全不一样。
-
要清楚意向锁的作用,必须知道没有意向锁之前是怎么样的？
如果没有意向锁，加表级别的读写锁之前，需要一行行的遍历判断每一行是否有加锁。
有了意向锁之后，就不再需要每一行都判断，只需要看有没有意向锁就可以判断是否有行级别的锁。
- 意向锁是分为两类的，分别是意向共享锁和意向独占锁。
