03 MYSQL索引实践(sql优化，索引设计原则)

假装自己会头腔共鸣

已于 2024-07-23 14:07:27 修改

阅读量419

点赞数 4

分类专栏： MYSQL 文章标签： mysql 数据库

于 2024-07-16 20:48:27 首次发布

本文链接：https://blog.csdn.net/lizhihappy01/article/details/140475929

版权

MYSQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

索引实践(索引下推)

1、联合索引第一个字段用范围不会走索引

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

这里不走索引的原因：如果按照索引去找，就算找到了LiLei后面的这些数据，因为要求age=22 和position=‘manager’ 这俩并没有排序，所以还要全表扫描，所以无论如何都要全表扫描。

2.强制走索引

EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';

结论：虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高

3.覆盖索引优化

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

4.in和or在表数据量较大的情况下会走索引，在表记录不多的情况下会选择全表扫描

5.like KK% 一般情况都会走索引

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

以上两个sql，表达的意思大体相同，但是一个是范围查找，一个是like通配符查找，注意like查找要当作常量查找。

为什么：范围查找没走索引，但是like查询走了索引。

这里给大家补充一个概念，索引下推（Index Condition Pushdown，ICP）, like KK%其实就是用到了索引下推优化

什么是索引下推了？

对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager' 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。

在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 'LiLei' 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

为什么范围查找Mysql没有用索引下推优化？

估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

Order by与Group by优化

case1

分析：利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort

case2

分析：从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了age，出现了Using filesort。

case3

分析：查找只用到索引name，age和position用于排序，无Using filesort。

case4

分析：和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。

case6

分析：虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

case7

分析：对于排序来说，多个相等条件也是范围查询

case8

分析：为什么没有走索引，因为数量太少了，使用覆盖索引优化

优化总结：

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

2、order by满足两种情况会使用Using index。

1) order by语句使用索引最左前列。

2) 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

Using filesort文件排序原理详解

filesort文件排序方式

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

如果字段的总长度小于max_length_for_sort_data ，那么使用单路排序模式；
如果字段的总长度大于max_length_for_sort_data ，那么使用双路排序模式。

分页查询优化

在真实场景当中，经常会遇到数据分页越往后越慢的情况，具体的sql是什么样的呢？

select * from employees limit 10000 10

表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

优化方式1

场景：如果表当中存在连续而且自增的主键，就可以使用下面的sql进行优化

select * from emp where id > 10000 limit 10

这表示翻到第1000页，我取10条，进行展示。

但是这种优化方式存在一个弊端，如果我第9999条记录被删除了，那么它就不是连续自增得了，我看第999页会展示，9991~10001的数据，因为这一共就10条，翻到第1000页就展示10001~10010重要的是10001重复展示了，所以再id不连续的情况下，这种方式不可取。

优化方式2

分页的时候，一定是按照某一个字段进行排序的，如果不按照主键进行排序，那么就是按照name进行排序，比如说

select * from emp limit 10000 10 order by name

这个时候一样会很慢，因为还是查询前面的10000条然后在查10条记录，但是如果我们给name字段建立上索引

这里因为数据太多了，mysql认为不走索引可能还快一些，因为走索引还要回表，所以没走索引，但是这样既没走索引，又使用了文件排序

如果我们对sql进行这样的优化

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

第一个执行的sql语句使用的是索引，因为它查询出来的字段只有id，那么就使用索引文件就可以查询出来了，甚至不需要回表，这时我们再用查询出来的id去原表做一个关联，就可以

查询出来数据了，甚至它的执行计划当中的type提升到了 eg_ref.

Join关联查询优化

t1有10000条记录 t2有100条记录

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table t2 like t1;

嵌套循环连接 Nested-Loop Join(NLJ) 算法

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

这个根据执行计划得到，先查询的表为t2表，t2表为小表，只是使用where条件，将t2表的内容查询出来，然后去t1表当中进行关联，t1表又存在索引，还是主键索引，所以t2表的执行计划的type字段达到了ref，这个就是一个小表关联大表的例子

其实再查询当中，我们尽量用小表关联大表，大表小表是由where条件决定的，但如果表数量差不多，或者没有写查询条件，那么mysql会自动判定到底谁是大表，谁是小表。

上面sql的大致流程如下：

从表 t2 中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据）；
从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
重复上面 3 步。

整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行)。因此整个过程扫描了 200 行。

如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低(下面有详细解释)，mysql会选择Block Nested-Loop Join算法。

基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

>EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

上面sql的大致流程如下：

把 t2 的所有数据放入到 join_buffer 中
把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次。

这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？·

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。

比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。

对于关联sql的优化

关联字段加索引，让mysql做join操作时尽量选择NLJ算法
小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

straight_join解释：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。

比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。

straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）
尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。