MySQL高性能索引

目录

索引是什么

MySQL中的索引

B+树索引

磁盘和B+树

InnoDB中的索引

索引在查询中使用

索引的代价

索引创建策略

---

索引是什么

1. 是帮助MySQL高效获取数据的数据结构
2. 本质：数据结构
3. 作用：高效获取数据

MySQL中的索引

1. InnoDB支持以下几种常见的索引：B+树索引、全文索引、哈希索引(内部)，其中比较关键的是B+树索引
2. 哈希索引：不适合做索引，如果找数据，需要把所有哈希全部遍历才能找到，哈希无法排序，哈希是个压缩算法，会将多个值放到少量的哈希桶中，必然会有一个桶有多个值，产生哈希冲突

B+树索引

1. B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最常用和最为有效的索引
2. 根据数组的特性我们知道，有最优的查找效率和空间需求；能够进行有序性相关操作，缺点也很明显，插入和删除操作很慢，所以为了结合链表插入的灵活性和有序数组查找的高效性，引入了二叉查找树
3. 二叉查找树：将数组中间元素作为根节点，然后分别在左右子树在循环找中间节点为根节点
4. 平衡二叉树：两个子树高度差不超过1
5. B+树对比B树
   1. B+树中间节点不放数据，只存放关键字和下层节点引用
   2. B+树相邻叶子结点会有指针连成链表
   3. 所以B树中间节点要存放数据，节点存放下个节点指针要少，同样数据规模比B+树高，增加磁盘IO
   4. 插入操作
      1. 索引结点和根节点都不满，正常查询索引节点，插入叶子结点相应位置
      2. 如果索引结点不满，当插入叶子结点是发现满了，那么会把叶子结点中间值拆分到索引节点正确位置，同时产生新叶子位置，将值插入新叶子位置
      3. 如果索引结点需要插入值，那么索引结点也会分裂，进行如上类似操作，如下图
      4. 
   5. 删除操作：填充因子为50%，要求叶子结点有值个数不少于50%
   6. 

磁盘和B+树

1. 柱面+盘片+扇区定位一个最小数据存储单元，一般是512B~4Kb
2. 预读：当数据被用到时，会读取其附近扇区内容，磁盘读取到内存按照页或整数倍读取，一页一般是4Kb
3. 局部性原理：程序运行期间其附近的数据也通常会马上被使用，而一个数据可能会在短时间内再次被用到
4. 顺序读VS随机读40~400倍，顺序写VS随机写10~100倍
5. B+树存放
   1. InnoDB中一个B+树节点16Kb，一页放一个节点，需要4页，一个key8B，那么可以有1000左右个索引
   2. MySQL优化方向就是数据顺序读写，少随机读写；主键无序，B+树叶子结点要有序排列，无序很大可能主键在不同页，磁盘上随机分布导致IO效率低

InnoDB中的索引

1. 聚集索引：将表的主键构造B+树，表的行完整数据存在该B+树叶子节点中，没有主键MySQL生成默认主键
2. 二级索引：自定义的索引，也是B+树，叶子结点存放索引列和主键列，索引列有序
3. 回表：查询条件恰好是辅助索引，通过索引找到对应主键然后通过主键回表查询数据，需要回表查询数据越多数据库越倾向全表扫描
4. 联合索引：将表上的多个列组合起来进行索引，但是也会只有一个B+树
5. 索引覆盖：从辅助索引中就可以得到查询的记录，而不需要查询联合索引中的记录
6. 哈希索引：InnoDB存储引擎内部监控索引热数据，然后内部创建一个hash索引，基本无法人工干预
7. 全文检索问题：唐诗根据文章关键字查询会很难很慢，于是可以倒排索引，把文档中关键字全部提取和文档对应关系保存，关键字排序，查找时先对关键字检索，然后根据关系找到文档

索引在查询中使用

1. 一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录
2. 一个select语句在执行过程中一般最多能使用一个二级索引，即使在where条件中用多个二级索引
3. 扫描区间
   1. SELECT * FROM order_exp WHERE id in(3,9) OR (id>=23 AND id<= 99);有三个扫描区间3 9 [23,99]
   2. SELECT * FROM order_exp WHERE order_no <'DD00_10S' AND expire_time> '2021-03-22 18:28:28' AND order_note > '7排';order_no或者expire_time使用一个索引，order_note非扫描区间
   3. 范围区间扫描：对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个区间
   4. LIKE操作符，只有在匹配完整的字符串或者字符串前缀时才产生合适的扫描区间
   5. 有的无法使用索引情况
   6. SELECT * FROM order_exp WHERE expire_time> '2021-03-22 18:35:09'  OR order_note = 'abc';因为在通过expire_time回表后，查找到记录无法判断是否满足OR order_note，所以需要全表扫描
   7. 复杂搜索条件找出匹配范围区间：假设使用一个索引，用不到索引的条件用true来表示，再进一步筛选把能得到结果的条件用true或false表示，一系列简化后计算成本
   8. 联合索引查找匹配范围：需要有第一个联合索引才能查找适用范围，如果没有第一个是无法找到对应范围，需要使用连续两个联合索引之一才有范围

索引的代价

1. 空间代价：每个索引都需要一个B+树，每个节点都是一个数据页，都要占用空间
2. 时间代价：增删改查也需要同时修改索引B+树

索引创建策略

1. 索引类型尽量小：数据类型越小越好，查询时比较快，索引占用空间少，一个数据页放下更多记录，减少IO
2. 索引离散性高：离散性高说明重复值少，做索引就更合适，扫描范围越小
3. 前缀索引：有时需要索引很长的字符，通常可以索引开始的部分字符，这样可以大大节约索引空间，但这样也会降低索引的选择性
   1. 计算截取前缀去重数量在总字符串数的重复性
4. 后缀索引：业务在存储时翻转存储，再建立前缀索引
5. 只为用于搜索、排序或分组的列创建索引：只为出现在WHERE子句中的列、连接子句中的连接列创建索引，而出现在查询列表中的列一般就没必要建立索引，除非是需要使用覆盖索引；又或者为出现在ORDER BY或GROUP BY子句中的列创建索引
6. 多列索引如何选择顺序
   1. 经验法则，将选择性最高的列放到索引最前列，MySQL搜索时候范围最小
   2. 需要根据那些运行频率最高的查询来调整索引列的顺序，优先于经验法则
   3. 可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求，新建一个不同顺序索引
7. 设计三星索引
   1. 索引将相关的记录放到一起则获得一星，索引扫描范围很小或者连续，不一定要全部索引都查询
   2. 如果索引中的数据顺序和查找中的排列顺序一致则获得二星(排序星)
   3. 如果索引中的列包含了查询中需要的全部列则获得三星(索引覆盖)，重要性最高，因为如果回表会发生随机读，性能下降
8. 主键尽量选择很少改变的列，主键频繁改变，物理顺序会改变，性能会急剧降低
9. 冗余和重复索引：主键ID，列A、B
   1. 索引(A、B)和索引(A)，那么索引(A)属于重复没必要，因为都是以A优先排序
   2. 索引(A)和索引(A、ID)，也属于重复没必要，因为索引(A)算是二级索引，默认会追加一个ID索引

高性能索引

1. 不在索引列上做任何操作，可能索引失效，比如运算操作然后等值匹配、大小写转换等操作
2. 尽量全值匹配
3. 最佳左前缀，多个索引最好按照索引顺序匹配，不要跳过前面的索引直接匹配后面索引
4. 范围条件放最后，联合索引中，保证索引连续顺序情况下，范围条件放到最后
5. 索引覆盖尽量用，同样空间情况下，存储的索引肯定更多
6. 不等于要慎用，几乎全表扫描
7. Null/Not Null有影响，not null容易导致索引失效，is null如果允许那么ref扫描，如果不允许null会全表
8. Like查询要当心，尽量%不要在开头，会全表扫描，非要用尽量和要查询列一起联合索引
9. 字符类型加引号，不然无法索引生效
10. 使用OR关键字要注意，那么可以换为union来执行，效率更高
11. 使用索引来做分组和order by排序，group by自带order by，如果不想排序可以order by null
12. 尽可能按主键顺序插入行
13. 优化count查询，不需要那么精确使用count(某列)估算值计算
14. 优化limit分页，查询10000条以后数据，select * from order where id > 10000 order by id limit 10，但这个id需要前端配合返回