目录
MySQL 之 SQL 优化详解(三)
1. 索引优化
2. 剖析报告: Show Profile
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 5
4. 查看结果 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 为什么这样写, 了解以上知识的使用方法就可以啦
来源: https://www.cnblogs.com/dwlovelife/p/11110561.html