（索引 + SQL 优化）：《MySQL 底层探秘②：B + 树索引与 Explain 实战，根治线上慢 SQL》

MySQL深度解析第二篇索引原理、SQL优化、执行计划与慢查询标签MySQL索引B树explainSQL优化慢查询前言第一篇搞定了InnoDB底层、事务、MVCC。开发工作中最常打交道就是索引大部分线上性能问题根源都是索引设计不合理、写出低效SQL。本篇重点讲解InnoDB聚簇索引原理、索引分类、索引失效场景、Explain执行计划慢查询排查以及SQL优化实战技巧。一、InnoDB聚簇索引主键索引InnoDB使用聚簇索引表数据本身按照主键有序存放在B树叶子节点索引和数据在一起。叶子节点完整的一行数据并且按主键排序非叶子节点只存放主键值页指针用于索引检索对比MyISAM非聚簇索引和数据文件分开索引叶子节点存储数据行地址。聚簇索引 vs 二级索引索引类型叶子节点存储内容查询流程特点聚簇索引主键索引完整行数据主键查询直接拿到行数据主键必须有序主键不宜过大二级索引会变大二级索引普通索引主键值查到主键再回表去主键索引读取完整数据回表联合索引最左前缀原则减少回表可以使用覆盖索引覆盖索引二级索引包含查询所需要全部字段不需要回表性能极高。二、B树索引特点InnoDB索引底层是B树不是二叉树、B树。所有数据都在叶子节点非叶子只存索引键树矮IO次数少叶子节点之间用双向链表串联范围查询 betweenorder by非常高效节点大小等于InnoDB页默认16KB一页可以存大量索引条目树高度一般2~4层三、索引分类主键索引PRIMARY聚簇索引唯一非空一张表只能一个唯一索引UNIQUE索引列不能重复可以为NULL普通索引INDEX普通二级索引允许重复和NULL联合索引复合索引多个字段组合索引遵循最左前缀原则全文索引FULLTEXT长文本分词检索不适合模糊like ‘%xxx%’索引设计黄金原则联合索引把区分度高、查询频率高的字段放前面尽量使用覆盖索引避免回表索引字段尽量小不要在大字符串、大文本建索引不要在低区分度字段性别、状态单独建索引索引不是越多越好索引会降低写入性能增删改需要维护B树四、索引失效常见场景索引列做函数运算、隐式类型转换like 以%开头like %abc无法走B树索引or两边字段有一个没有索引违反最左前缀原则跳过前面索引列MySQL优化器判断全表扫描比索引更快主动放弃索引五、Explain执行计划详解使用explain select xxx;查看SQL执行计划判断是否命中索引。字段含义关注点id查询序号子查询、关联查询标识id越大优先级越高select_type查询类型SIMPLE简单查询、SUBQUERY子查询、DERIVED派生表尽量避免DEPENDENT SUBQUERY相关子查询table当前访问哪张表type访问类型性能从优到劣systemconsteq_refrefrangeindexALL目标至少range/refALL代表全表扫描需要优化possible_keys理论可用索引仅参考不一定实际使用key实际选用索引NULL代表没用到索引key_len索引使用字节长度判断联合索引使用多少列ref索引匹配条件哪些列/常量和索引对比rows预估扫描行数越小越好评估扫描代价Extra额外信息核心重点Using filesort 文件排序Using temporary临时表Using index覆盖索引Using where重点警告Using filesort文件排序、Using temporary临时表属于高危性能信号代表MySQL无法利用索引排序需要额外内存/磁盘排序大数据集性能很差。六、慢查询日志慢查询日志用来捕获执行超过阈值的SQL是线上排查性能问题的利器。核心参数slow_query_log开启/关闭慢日志long_query_time慢查询阈值单位秒默认10slog_queries_not_using_indexes记录没有使用索引的SQL生产建议long_query_time 设置为1s开启记录未走索引SQL定期分析慢日志。七、SQL优化通用思路先用explain查看执行计划定位是否全表扫描、文件排序、临时表优先建立合适索引利用覆盖索引减少回表避免select *只查询业务需要字段减少数据传输更容易触发覆盖索引减少子查询优先使用JOINJOIN字段保证类型一致建立索引分页优化大offset分页不要limit 100000,10推荐主键id过滤where idxxx limit 10减少大事务大事务持有锁时间长容易锁等待第二篇小结本篇掌握B树索引、聚簇索引、联合索引最左前缀、索引失效场景看懂explain执行计划慢查询定位方法和SQL优化方案。索引优化是开发高频工作但索引只能解决查询性能。第三篇我们学习主从复制、MGR、高可用架构锁详解运维参数调优线上故障排查和最佳实践。