索引的优化

Songbl_

已于 2022-08-02 10:17:24 修改

阅读量637

点赞数

分类专栏： MySQL调优文章标签：数据库 java mysql

于 2022-08-01 14:32:11 首次发布

本文链接：https://blog.csdn.net/sbl19940819/article/details/126100751

版权

MySQL调优专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1.基本知识

索引的优点：
- 大大减少服务器需要扫描的数据量
- 帮助服务器避免排序和临时表
- 将随机IO变成顺序IO
索引分类：
- 主键索引
- 唯一索引
- 普通索引
- 全文索引
- 组合索引（多列值组成一个索引，专门用于组合搜索，其效率大于索引合并）
索引匹配方式
- 全职匹配（和索引中的所有列进行匹配）
- 匹配最左前缀（只匹配前面几列）
- 匹配列前缀（匹配某一列值的开头部分）
- 可以查找某一范围的数据（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 就可访问数据

2.优化小细节

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

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

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

范围也可以用索引
范围的条件：>,>=,<,<=,between；范围条件后的列无法再使用索引
强制类型转换回全表扫描。
更新频繁，数据区分度不高的数据不宜建索引。
- 索引列更新会变更B+树的，频繁更新的会大大降低数据库性能。
- 类似于性别这类(只有男女，或者未知)，不能有效过滤数据。
- 一般区分度在80%以上就可以建立索引，区分度可以使用count(distinct(列名))/count(*)
创建索引的列，不允许为null，可能会得到不符合预期的结果
当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致
表连接其实就是多张表循环嵌套匹配，是比较影响性能的
能使用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