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