MySQL索引优化

本篇字数: 1.7k字   |   阅读时长: 6分

2024-3-29

一、联合索引第一个字段使用范围查找不会使用索引

1
2
3
4
5
6
# name > <user_name>为范围查找
EXPLAIN SELECT * FROM <table_name> WHERE name > <user_name> AND age = <user_age> AND position = <user_position>;
# 强制使用索引
EXPLAIN SELECT * FROM <table_name> force index(<user_name>,<user_age>,<user_position>) WHERE name > <user_name> AND age = <user_age> AND position = <user_position>;
# 总结：强制使用索引让联合索引的范围查找使用索引，rows减少，但查找效率未必比全表扫描高，因为回表效率不高

二、in和or在表数据量比较大时使用索引，否则使用全表扫描

1
2
3
EXPLAIN SELECT * FROM <table_name> WHERE name in (<user_name1>,<user_name2>,<user_name3>) AND age = <user_age> AND position=<user_position>;

EXPLAIN SELECT * FROM <table_name> WHERE (name=<user_name1> or name=<user_name2>) AND age = <user_age> AND position=<user_position>;

三、like example% 一般情况使用索引

1
2
3
4
EXPLAIN SELECT * FROM <table_name> WHERE name like <user_name%> AND age = <user_age> AND position =<user_position>;
# 索引下推（Index Condition Pushdown，ICP），辅助的联合索引(name,age,position)在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。
# 遍历时同时判断字段age和position是否包含<user_name%>，判断完在回表。
# 索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

四、MySQL索引优化

使用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高，可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就能拿到所有结果。

1
2
3
EXPLAIN select * from <user_table> where name > <user_name> ;
# 覆盖索引
EXPLAIN select name,age,position from <user_table> where name > <user_name> ;

五、Order By与Group By

1.对于排序来说，多个相等条件也是范围查询，使用Extra 为Using filesort。

1
EXPLAN select * from <user_table> where name in(<user_name1>,<user_name2>) order by age,position;

2.可以使用覆盖索引优化

七、优化总结

MySQL优化小结：

  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限定了。

[!CAUTION]

Using filesort文件排序原理：

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

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

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

[!NOTE]

1
select * from <user_table> where name = <user_name> order by position;

单路排序流程：

  1. 从索引name找到第一个满足 name = 条件的主键 id
  2. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
  3. 从索引name找到下一个满足 name = 条件的主键 id
  4. 重复步骤 2、3 直到不满足 name =
  5. 对 sort_buffer 中的数据按照字段 position 进行排序

双路排序流程：

  1. 从索引 name 找到第一个满足 name = 的主键id
  2. 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
  3. 从索引 name 取下一个满足 name = 记录的主键 id
  4. 重复 2、3 直到不满足 name =
  5. 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
  6. 遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回给客户端

如果 MySQL 排序内存 sort_buffer 配置的比较小并且没有条件继续增加了，可以适当把max_length_for_sort_data 配置小点，让优化器选择使用双路排序算法，可以在sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据。
如果 MySQL 排序内存有条件可以配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器
优先选择全字段排序(单路排序)，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了。
所以，MySQL通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，
从而提升排序效率。

如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增
大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不可轻易调整。

[!TIP]

索引设计原则

1、代码先行，索引后上

2、联合索引尽量覆盖条件

4、长字符串我们可以采用前缀索引

5、where与order by冲突时优先where

6、基于慢SQL查询做优化（慢SQL查询：把耗时较长的SQL语句写入文件中，方便优化，但是消耗性能）

数据

Column Type Describe
id int（11） NOT NULL AUTO_INCREMENT
name varchar(24) NOT NULL DEFAULT ‘ ’
age int（11） NOT NULL DEFAULT ‘0’
position varchar(20) NOT NULL DEFAULT ‘ ’
hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
键 值
PRIMARY KEY ID
KEY idx_name_age_position (name,age,position`) USING BTREE
AUTO_INCREMENT 1
ENGINE InnoDB
CHARSET utf8
本文作者： Seyou
发布时间： 2024-03-29
最后更新： 2024-03-29
本文标题： MySQL索引优化
本文链接： https://seyou.lol/posts/2024/03/b542e386.html
版权声明： 本作品采用 CC BY-NC-ND 4.0 许可协议进行许可。转载请注明出处！