目录
MySQL 之SQL优化详解（三）
1. 索引优化
一旦建立索引，select 查询语句的where条件要尽量符合最佳左前缀的原则，如若能做到全值匹配最好。
索引优化的第一个前提就是建好索引，第二个就是避免索引失效
索引失效的场景
- 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
- 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
- is null ,is not null 也无法使用索引
- like以通配符开头('%abc...') mysql索引失效会变成全表扫描的操作
- 少用or,用它来连接时会索引失效
小总结：
假设index(a,b,c)
| Where语句 | 索引是否被使用 |
|---|---|
| where a = 3 | Y,使用到a |
| where a = 3 and b = 5 | Y,使用到a，b |
| where a = 3 and b = 5 and c = 4 | Y,使用到a,b,c |
| where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 | N |
| where a = 3 and c = 5 | 使用到a， 但是c不可以，b中间断了 |
| where a = 3 and b > 4 and c = 5 | 使用到a和b， c不能用在范围之后，b断了 |
| where a = 3 and b like 'kk%' and c = 4 | Y,使用到a,b,c |
| where a = 3 and b like '%kk' and c = 4 | Y,只用到a |
| where a = 3 and b like '%kk%' and c = 4 | Y,只用到a |
| where a = 3 and b like 'k%kk%' and c = 4 | Y,使用到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 54.查看结果 show profile;
5.诊断SQL，show profile cpu,block io for query 上一步前面的问题SQL数字号码;
| Status | 建议 |
|---|---|
| System lock | 确认是由于哪个锁引起的，通常是因为MySQL或InnoDB内核级的锁引起的建议：如果耗时较大再关注即可，一般情况下都还好 |
| Sending data | 从server端发送数据到客户端，也有可能是接收存储引擎层返回的数据，再发送给客户端，数据量很大时尤其经常能看见备注：Sending Data不是网络发送，是从硬盘读取，发送到网络是Writing to net建议：通过索引或加上LIMIT，减少需要扫描并且发送给客户端的数据量 |
| Sorting result | 正在对结果进行排序，类似Creating sort index，不过是正常表，而不是在内存表中进行排序建议：创建适当的索引 |
| Table lock | 表级锁，没什么好说的，要么是因为MyISAM引擎表级锁，要么是其他情况显式锁表 |
| create sort index | 当前的SELECT中需要用到临时表在进行ORDER BY排序建议：创建适当的索引 |
| checking query cache for querychecking privileges on cachedsending cached result to clienstoring result in query cache | 和query cache相关的状态，已经多次强烈建议关闭 |
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为什么这样写，了解以上知识的使用方法就可以啦