索引的优化

1.基本知识

1. 索引的优点：
   • 大大减少服务器需要扫描的数据量
   • 帮助服务器避免排序和临时表
   • 将随机IO变成顺序IO
2. 索引分类：
   • 主键索引
   • 唯一索引
   • 普通索引
   • 全文索引
   • 组合索引（多列值组成一个索引，专门用于组合搜索，其效率大于索引合并）
3. 索引匹配方式
   • 全职匹配（和索引中的所有列进行匹配）
   • 匹配最左前缀（只匹配前面几列）
   • 匹配列前缀（匹配某一列值的开头部分）
   • 可以查找某一范围的数据（explain select * from staffs where name > 'Mary';）
   • 精确匹配某一列并范围匹配另外一列（explain select * from staffs where name = ‘July’ and age > 25;）
   • 查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引

索引的口诀如下：全值匹配我最爱,最左前缀要遵守，带头大哥不能死,中间兄弟不能断，索引列上少计算,范围之后全失效(跟创建索引的顺序有关系)，like百分写最右,
覆盖索引不写星，不等非空还有or,索引失效要少用，varchar引号不可丢(不加也可以查询出来，sql底层会做一个隐式转，但是索引就会失效)
4. 聚簇索引和非聚簇索引

 1. 聚簇索引（一种存储方式，指的是数据航跟相邻的键值紧凑的存储再一起） 。*
 	* 当直接采用主键进行检索时，可通过主键索引直接获得数据；而当采用非主键进行检索时，先需要通过辅助索引来获得主键，然后再通过这个主键在主键索引中找到对应的数据行    
 2. 非聚簇索引（数据文件跟索引文件分开存放）
    *  无论是主键索引还是辅助索引的检索过程，都只需要通过相应的 B+Tree 进行搜索即可获得数据对应的物理地址，然后经过依次磁盘 I/O 就可访问数据
1
2
3
4

2.优化小细节

1. 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层

-- 执行计划是const
select actor_id from actor where actor_id=4;
-- 执行计划是index
select actor_id from actor where actor_id+1=5;
1
2
3
4

2. 尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询
   https://www.cnblogs.com/taojietaoge/p/16167188.html
3. 使用前缀索引。有时候索引是很长的字符串，这会让索引变得大且慢，通常情况下可以使用某列开始的部分字符串，这样大大节约索引空间，提高效率，可能会降低些索引的
   选择性。索引的选择性越高则查询的效率越高。一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能。
4. 使用索引扫描来排序
   MySQL的排序操作；按照索引顺序进行扫描 后者由于只需要按照索引进行顺序扫描，速度会比前者快很多。
   但是如果索引列无法满足查询结果的列，那么每次扫描一条索引记录就需要回表查询一次对应的行，由于是随机IO，因此按照“索引顺序回表查询”比“顺序全表扫描”要慢，因此尽可能在设计索引时既满足排序又可以用于查找行
   （排序要查询出所有的数据行，所以全盘扫描比每条数据都要回表快，是要加载全量的数据）
5. union all ,in,or都能够使用索引，但是推荐使用in。
   查看执行计划：

-- 2步
explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
-- 耗时最短
explain select * from actor where actor_id in (1,2);
-- 耗时币in 长
explain select * from actor where actor_id = 1 or actor_id =2;
1
2
3
4
5
6

6. 范围也可以用索引
   范围的条件：>,>=,<,<=,between；范围条件后的列无法再使用索引
7. 强制类型转换回全表扫描。
8. 更新频繁，数据区分度不高的数据不宜建索引。
   • 索引列更新会变更B+树的，频繁更新的会大大降低数据库性能。
   • 类似于性别这类(只有男女，或者未知)，不能有效过滤数据。
   • 一般区分度在80%以上就可以建立索引，区分度可以使用count(distinct(列名))/count(*)
9. 创建索引的列，不允许为null，可能会得到不符合预期的结果
10. 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致
    表连接其实就是多张表循环嵌套匹配，是比较影响性能的
11. 能使用limit的时候尽量使用limit
    limit的作用不仅仅是用于分页，本质作用是控制输出。limit其实是挨个遍历查询数据，
    如果只需要一条数据添加 ​​limit 1​​的限制，那么索引指针找到符合条件的数据之后就停止了，不会继续向下判断了，直接返回。如果没有limit，就会继续判断。
    但是如果分页取1万条后的5条​​limit就需要慎重了，他会遍历1万条之后取出5条，效率很低的。如果id是有序的，可以通过id做分页

问题：
select * from product limit 866613, 20 37.44秒
优化：
对limit分页问题的性能优化方法 利用表的覆盖索引来加速分页查询
这次我们之间查询最后一页的数据（利用覆盖索引，只包含id列），如下：
select id from product limit 866613, 20 0.2秒
相对于查询了所有列的37.44秒，提升了大概100多倍的速度
那么如果我们也要查询所有列，有两种方法，一种是id>=的形式(相当于查出来第866613行的id，再通过索引查20条)
SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20
查询时间为0.2秒，
另一种就是利用join
另一种写法SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id查询时间也很短，赞！
参考：
https://juejin.cn/post/6844903621390254093
1
2
3
4
5
6
7
8
9
10
11
12
13
14

12. 单表索引尽量控制在5个内
    建立/维护索引也是需要代价的，也需要占用空间的。索引并不是越多越好，要合理使用索引。
13. 单个组合索引的字段个数不宜超过5个
    字段越多，索引就会越大，占用的存储空间就越多。
    参考：
    https://blog.51cto.com/ajisun/5222625