目录

• MySQL 之SQL优化详解（三）

MySQL 之SQL优化详解（三）

1. 索引优化

一旦建立索引，select 查询语句的where条件要尽量符合最佳左前缀的原则，如若能做到全值匹配最好。

索引优化的第一个前提就是建好索引，第二个就是避免索引失效

 

索引失效的场景

1. 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
2. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
3. 存储引擎不能使用索引中范围条件右边的列

1. mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
2. is null ,is not null 也无法使用索引
3. like以通配符开头('%abc...') mysql索引失效会变成全表扫描的操作
4. 少用or,用它来连接时会索引失效

 

小总结：

假设index(a,b,c)

 

2. 剖析报告:Show Profile

是什么：是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
官网介绍：show profile

默认情况下，参数处于关闭状态，开启后默认保存最近15次的运行结果

 

1.是否支持，看看当前的mysql版本是否支持

Show  variables like 'profiling';

 

2.开启功能，默认是关闭，使用前需要开启

set profiling=on;

 
3.运行SQL

select * from emp group by id%10 limit 150000;
select * from emp group by id%20  order by 5

 

4.查看结果 show profile;

 

5.诊断SQL，show profile cpu,block io for query 上一步前面的问题SQL数字号码;

 
6. 日常开发需要注意的结论

• Creating tmp table 创建临时表
• Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！
• locked 被锁住

 

示例：剖析 select * from emp group by id%20 order by 5

由剖析报告看出，其中 Copying to tmp table 步骤花费了大量的时间，所以这条SQL应该优化了。
 
注：该文的SQL只是为了便利的梳理知识点使用，不需要关心这条SQL为什么这样写，了解以上知识的使用方法就可以啦