MySQL之SQL优化详解(三)

1|01. 索引优化

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

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

 

索引失效的场景

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

  1. mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
  2. is null ,is not null 也无法使用索引
  3. like以通配符开头('%abc...') mysql索引失效会变成全表扫描的操作
  4. 少用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|02. 剖析报告: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为什么这样写,了解以上知识的使用方法就可以啦


__EOF__

本文作者丁可乐
本文链接https://www.cnblogs.com/dwlovelife/p/11110561.html
关于博主:评论和私信会在第一时间回复。或者直接私信我。
版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角推荐一下。您的鼓励是博主的最大动力!
posted @   丁可乐  阅读(2522)  评论(0编辑  收藏  举报
编辑推荐:
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· .NET10 - 预览版1新功能体验(一)
点击右上角即可分享
微信分享提示