mysql常见问题

哈希(hash)比树(tree)更快，索引结构为什么要设计成树型？

• 如果只是单行查询，哈希索引确实比较快
• 对于排序查询的SQL需求，哈希型的索引，时间复杂度会退化为O(n)，而树型的“有序”特性，依然能够保持O(log(n)) 的高效率。

数据库索引为什么使用B+树？

• 很适合磁盘存储，能够充分利用局部性原理，磁盘预读；
• 很低的树高度，能够存储大量数据；
• 索引本身占用的内存很小；
• 数据只存储在同一层的叶子节点上，叶子之间，增加了链表；能够很好的支持单点查询，范围查询，有序性查询；

MyISAM与InnoDB的索引差异

• MyISAM的索引与行记录是分开存储的，叫做非聚集索引；MyISAM的表可以没有主键，主键索引与普通索引无太大区别。
• InnoDB的主键索引与行记录是存储在一起的，故叫做聚集索引；因此，InnoDB的PK查询是非常快的。InnoDB的聚集索引存储数据行本身，普通索引存储主键，InnoDB一定有且只有一个聚集索引。

InnoDB的哈希索引

InnoDB用户无法手动创建哈希索引。InnoDB会自己判断是否加速查询而创建自适应哈希索引。符合的业务场景如下：

• 很多单行记录查询（例如passport，用户中心等业务）
• 索引范围查询（此时AHI可以快速定位首行记录）
• 所有记录内存能放得下

如何避免回表查询？什么是索引覆盖？

• 回表查询：先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。
• 索引覆盖：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
• 要使用索引覆盖，要将被查询的字段，建立到联合索引里去。
• 哪些场景可以利用索引覆盖来优化SQL？例如全表count查询优化、列查询回表优化、分页查询

explain结果中的type字段

• system：系统表，速度最快，不进行磁盘IO
• const：常量连接；PK或者unique上的等值查询
• eq_ref：主键索引PK或者unique上的join查询，等值匹配，对于前表的每一行(row)，后表只有一行命中
• ref：非主键非唯一索引等值扫描；
• range：范围扫描；例如：between/in/>
• index：索引上的全集扫描；例如：InnoDB的count
• ALL：全表扫描

建立正确的索引(index)，使用explain了解并优化执行计划。

explain结果中的Extra字段

• Using where：SQL使用了where条件过滤数据
• Using index：SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。
• Using index condition：确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。
• Using filesort：得到所需结果集，需要对所有记录进行文件排序。
• Using temporary：需要建立临时表(temporary table)来暂存中间结果。
• Using join buffer (Block Nested Loop)：需要进行嵌套循环计算。

死锁分析

• 通过多终端模拟并发事务，复现死锁；
• 通过show engine innodb status; 可以查看事务与锁的信息；
• 通过explain可以查看执行计划；

主键与唯一索引约束

• 执行insert和update时，会触发约束检查
• InnoDB违反约束时，会回滚对应SQL
• MyISAM违反约束时，会中断对应的SQL，可能造成不符合预期的结果集
• 可以使用 insert … on duplicate key 来指定触发约束时的动作
• 通常使用 show warnings;来查看与调试违反约束的ERROR

两类隐蔽的不能利用索引的case

• “列类型”与“where值类型”不符，不能命中索引，会导致全表扫描；
• 相join的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循环计算；

负向查询，IN，OR，UNION哪些会对索引命中有影响？

• 负向查询肯定不可以命中索引
• IN查询是可以命中索引的
• OR查询，在新版MySQL，可以优化为IN查询，故也是可以命中索引的
• UNION是可以命中索引的

用表锁比行锁性能更高的场景

• 大部分SQL都是读请求。
• SQL是读写混合，写请求是单行的delete或update
• SQL是读写混合，写请求是高并发的insert，很少有delete或update。
• SQL会扫描大量行记录，且有很多group by。

文章中的知识内容大部分来自架构师之路，相关链接：https://mp.weixin.qq.com/s/syli7vs7Jw_VOTl5B2YUqg