什么是索引索引的定义是帮助存储引擎快速获取数据的一种数据结构形象地说索引就是数据的目录。索引的分类可以从四个角度来分类索引分类角度索引类型数据结构BTree索引、Hash索引、Full-text索引物理存储聚簇索引主键索引、二级索引辅助索引字段特性主键索引、唯一索引、普通索引、前缀索引字段个数单列索引、联合索引按数据结构分类InnoDB存储引擎在不同场景下选择不同的列作为索引有主键使用主键作为聚簇索引的索引键无主键选择第一个不包含NULL值的唯一列都没有自动生成隐式自增id列BTree索引查询过程示例查询过程自顶向下逐层查找经历3个节点即3次I/O操作。二级索引与回表二级索引的叶子节点存放的是主键值而非实际数据使用二级索引查询需要先查二级索引获得主键值再查主键索引获取完整数据这个过程叫回表如果查询的数据能在二级索引中直接获取则称为覆盖索引无需回表为什么MySQL InnoDB选择BTree对比项BTree优势vs B树非叶子节点不存数据单节点数据量更小I/O次数更少叶子节点双向链表适合范围查询vs 二叉树高度维持在3~4层千万级数据只需3~4次I/Ovs HashHash不适合范围查询BTree适用范围更广按物理存储分类类型叶子节点存储内容聚簇索引主键索引实际数据二级索引辅助索引主键值按字段特性分类1. 主键索引2. 唯一索引3. 普通索引4. 前缀索引对字符类型字段的前几个字符建立索引按字段个数分类联合索引联合索引的BTree先按第一个字段排序在第一个字段相同的情况下再按第二个字段排序以此类推。最左匹配原则使用联合索引时需按照最左优先的方式进行索引匹配否则索引会失效。联合索引范围查询的特殊情况查询条件索引使用情况a 1 and b 2只有a用到索引b用不到会停止匹配a 1 and b 2a和b都用到了索引不会停止匹配a BETWEEN 2 AND 8 AND b 2a和b都用到了索引name like j% and age 22name和age都用到了索引索引下推MySQL 5.6在没有索引下推时二级索引查询需要先回表再判断条件。使用索引下推后可以在联合索引遍历过程中先对索引中包含的字段做判断直接过滤不满足条件的记录减少回表次数。执行计划中出现Using index condition即表示使用了索引下推。索引区分度区分度 字段不同值的个数 ÷ 表的总行数建立联合索引时应把区分度大的字段排在前面。联合索引用于排序什么时候需要/不需要创建索引适用索引的场景字段有唯一性限制如商品编码经常用于WHERE查询条件的字段经常用于GROUP BY和ORDER BY的字段不需要创建索引的场景WHERE、GROUP BY、ORDER BY里用不到的字段字段中存在大量重复数据如性别字段表数据太少经常更新的字段优化索引的方法1. 前缀索引优化使用字符串字段的前几个字符建立索引可减小索引字段大小提高查询速度。但无法用于ORDER BY和覆盖索引。2. 覆盖索引优化索引的叶子节点上包含查询所需的所有字段可避免回表操作减少I/O。3. 主键索引最好是自增的自增主键的好处每次插入新数据是追加操作不需要移动已有数据避免页分裂提高插入效率减少内存碎片提高查询效率4. 索引最好设置为NOT NULLNULL会使优化器在选择索引时更加复杂NULL会占用物理空间需要NULL值列表5. 防止索引失效常见索引失效的情况使用左模糊或左右模糊匹配like %xx或like %xx%对索引列做了计算、函数、类型转换操作违反联合索引的最左匹配原则OR条件中只有部分列是索引列执行计划关键指标字段说明possible_keys可能用到的索引key实际使用的索引NULL表示未使用索引key_len索引长度rows扫描的数据行数type数据扫描类型性能从低到高All → index → range → ref → eq_ref → constExtra字段重要指标Using filesort需要文件排序效率低Using temporary使用临时表效率低Using index使用了覆盖索引效率高总结问题答案什么是索引帮助存储引擎快速获取数据的数据结构常用索引类型BTree、Hash、Full-text聚簇、二级主键、唯一、普通、前缀单列、联合为什么用BTree查询效率高I/O次数少适合范围查询什么是回表使用二级索引查询后再查主键索引获取完整数据什么是覆盖索引查询所需数据都能在索引中直接获取无需回表最左匹配原则联合索引按最左优先方式匹配遇到、会停止匹配、、BETWEEN、like前缀不会停止主键为什么推荐自增避免页分裂提高插入效率和空间利用率索引失效常见情况like左模糊、索引列计算/函数/类型转换、违反最左匹配、OR条件不全为索引列
