1. 联合索引和模糊查询的常见问题
1.1 联合索引第一个字段用范围导致不会走索引的情况
联合索引第一个字段就用范围查找不会走索引,mysql内部可能觉得第一个字段就用范围,结果集应该很大,回表效率不高,还不如就全表扫描
注意:employees数据偏少时会走索引(测试employees表一万多条测试数据)
在mysql内部没有走索引的情况下,强制该范围查询走索引的话,效率提升并不大,因为回表效率不高。
优化方案:
覆盖索引优化。尽量让索引覆盖查询的字段
1.2 in和or在表数据量比较大的情况会走索引,在表记录不多的情况下会选择全表扫描
1.3 like KK% 一般情况都会走索引
注意: 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再回表查整行数据。
简单来说,即是索引下推之前是:name字段走索引查询符合的值的索引 —> 用这些索引去聚簇索引去查询相应的值 —> 再从这些值里用后续的age和position这两个字段去过滤符合条件的值
索引下推是:name字段走索引查询符合的值的索引 —> 再在这些索引判断符合age和position这两个字段的值的索引 —> 再把这些符合条件的值的索引去聚簇索引去查询相应的值(回表)
索引下推会减少回表次数,对于innodb引擎的表索引下推只能用于二级索引,innodb的主键索引(聚簇索引)树叶子节点上保存的是全行数据,所以这个时候索引下推并不会起到减少查询全行数据的效果。
为什么范围查找Mysql没有用索引下推优化?
估计应该是Mysql认为范围查找过滤的结果集过大,like KK% 在绝大多数情况来看,过滤后的结果集比较小,所以这里Mysql选择给 like KK% 用了索引下推优化,当然这也不是绝对的,有时like KK% 也不一定就会走索引下推。
2. Mysql的索引分析工具—trace
在判断mysql最终是否选择走索引或者一张表涉及多个索引,mysql最 终如何选择索引,可以用trace工具来分析。
注意:开启trace工具会影响mysql性能,所以只能临时分析sql使用,用完之后立即关闭
# 开启trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
# 示例sql语句
select * from employees where name > 'a' order by position;
# 查看trace分析日志
SELECT * FROM information_schema.OPTIMIZER_TRACE;
trace分析日志:
{
"steps": [
{
"join_preparation": { ‐‐ 第一阶段:SQL准备阶段,格式化sql
"select#": 1,
"steps": [
...
},
{
join_optimization": { ‐‐第二阶段:SQL优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { ‐‐条件处理
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
...
},
{
"table_dependencies": [ ‐‐表依赖详情
{
...
{
"rows_estimation": [ ‐‐预估表的访问成本
{
"table": "`employees`",
"range_analysis": {
"table_scan": { ‐‐全表扫描情况
"rows": 10123, ‐‐扫描行数
"cost": 2054.7 ‐‐查询成本
} /* table_scan */,
"potential_range_indexes": [ ‐‐查询可能使用的索引
{
"index": "PRIMARY", ‐‐主键索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position", ‐‐辅助索引
...
},
"analyzing_range_alternatives": { ‐‐分析各个索引使用成本
...
},
...
"rowid_ordered": false, ‐‐使用该索引获取的记录是否按照主键排序
"using_mrr": false,
"index_only": false, ‐‐是否使用覆盖索
"rows": 5061, ‐‐索引扫描行数
"cost": 6074.2, ‐‐索引使用成本
"chosen": false, ‐‐是否选择该索引
"cause": "cost"
...
"best_access_path": { ‐‐最优访问路径
"considered_access_paths": [ ‐‐最终选择的访问路径
{
"rows_to_scan": 10123,
"access_type": "scan", ‐‐访问类型:为scan,全表扫描
"resulting_rows": 10123,
"cost": 2052.6,
"chosen": true, ‐‐确定选择
"use_tmp_table": true
}
...
"join_execution": { ‐‐第三阶段:SQL执行阶段
...
结论:全表扫描的成本低于索引扫描,所以mysql最终选择全表扫描
3. 常见优化场景
3.1 Order by与Group by优化
联合索引(name,age,position)
①:age索引列用在排序过程中,因为Extra字段里没有using filesort
②:用了position进行排序,跳过了 age,出现了Using filesort。
③:查找只用到索引name,age和position用于排序,无Using filesort
④:查找只用到索引name,出现了Using filesort,因为排序的时候age和position颠倒位置了
⑤:与 ④ 相比,并未出现Using filesort,因为age为常量,在排序中被优化,所以索引未颠倒,不会出现Using filesort
⑥:虽然排序的字段列与索引顺序一样,且order by默认升序,这里position desc变成了降序,导致与索引的排序方式不同,从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式
⑦:对于排序来说,多个相等条件也是范围查询
总结:
-
MySQL支持两种方式的排序filesort和index,Using index是指MySQL扫描索引本身完成排序。index 效率高,filesort效率低
-
order by满足两种情况会使用Using index。
1) order by语句使用索引最左前列
2) 使用where子句与order by子句条件列组合满足索引最左前列 -
尽量在索引列上完成排序,遵循索引建立(索引创建的顺序)时的最左前缀法则
-
如果order by的条件不在索引列上,就会产生Using filesort
-
能用覆盖索引尽量用覆盖索引
-
group by与order by很类似,其实质是先排序后分组,遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意,where高于having,能写在where中 的限定条件就不要去having限定了
Using filesort文件排序原理:
- 单路排序:是一次性取出满足条件行的所有字段,然后在sort buffer(内存开辟一块空间)中进行排序;用trace工具可 以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key
- 双路排序(又叫回表排序模式):是首先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行 ID,然后在 sort buffer (内存开辟一块空间)中进行排序,排序完后需要再次取回其它需要的字段;用trace工具 可以看到sort_mode信息里显示< sort_key
MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式
- 如果字段的总长度小于max_length_for_sort_data ,那么使用单路排序模式
- 如果字段的总长度大于max_length_for_sort_data ,那么使用双路排序模式
例:
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY position;
单路排序的过程:
- 从索引name找到第一个满足 name = ‘LiLei’ 条件的主键 id
- 根据主键 id 取出整行,取出所有字段的值,存入 sort_buffer 中
- 从索引name找到下一个满足 name = ‘LiLei’ 条件的主键 id
- 重复步骤 2、3 直到不满足 name = ‘LiLei’
- 对 sort_buffer 中的数据按照字段 position 进行排序
- 返回结果给客户端
双路排序的过程:
- 从索引name找到第一个满足 name = ‘LiLei’ 条件的主键 id
- 根据主键 id 取出整行,把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
- 从索引name找到下一个满足 name = ‘LiLei’ 条件的主键 id
- 重复步骤 3、4直到不满足 name = ‘LiLei’
- 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
- 遍历排序好的 id 和字段 position,按照 id 的值回到原表中取出 所有字段的值返回给客户端
区别:
单路排序会把所有需要查询的字段都放到 sort buffer 中
双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序,然后再通过主键回到原表查询需要的字段
注意
如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序,但不能因为这个就随便增大sort_buffer(默认1M),mysql很多参数设置都是做过优化的,不要轻易调整。
4. 索引设计原则
4.1 应该等到主体业务功能开发完毕,再实际分析怎么建立索引
4.2 联合索引尽量覆盖条件
尽量让每一个联合索引都尽量去包含sql语句里的 where、order by、group by的字段,还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则
4.3 不要在小基数字段上建立索引
4.4 长字符串可以采用前缀索引
例:
对于varchar(255)的类型大字段可以考虑针对这个字段的前20个字符建立索引,就是说,对这个字段里的每个值的前20个字符放在索引树里,类似于 KEY index(name(20),age,position)
注意:
order by name,那么此时你的name因为在索引树里仅仅包含了前20个字符,所以这个排序是没法用上索引的, group by也是同理
4.5 where与order by冲突时优先where
因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据,然后做排序的成本可能会小很多
4.6 基于慢sql查询做优化
参考https://blog.csdn.net/qq_40884473/article/details/89455740