摘要:
致索引失效而转向全表扫描存储引擎不能使用索引中范围条件右边的列mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描isnull,isnotnull也无法使用索引like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作少用or,用它来连接时会索引失效 小总结:假设index(a,b,c)Where语句索引是否被使用wherea=3
目录
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
MySQL 之SQL优化详解(三)
1. 索引优化
一旦建立索引,select 查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
索引优化的第一个前提就是建好索引,第二个就是避免索引失效
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
议关闭 6.日常开发需要注意的结论Creatingtmptable创建临时表Copyingtotmptableondisk把内存中临时表复制到磁盘,危险!!!locked被锁住 示例:剖析select
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
索引失效的场景
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
is null ,is not null 也无法使用索引
like以通配符开头("%abc...") mysql索引失效会变成全表扫描的操作
少用or,用它来连接时会索引失效
议关闭 6.日常开发需要注意的结论Creatingtmptable创建临时表Copyingtotmptableondisk把内存中临时表复制到磁盘,危险!!!locked被锁住 示例:剖析select
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
小总结:
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
假设index(a,b,c)
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
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
议关闭 6.日常开发需要注意的结论Creatingtmptable创建临时表Copyingtotmptableondisk把内存中临时表复制到磁盘,危险!!!locked被锁住 示例:剖析select
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
2. 剖析报告:Show Profile
是什么:是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
官网介绍:show profile
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
默认情况下,参数处于关闭状态,开启后默认保存最近15次的运行结果
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
议关闭 6.日常开发需要注意的结论Creatingtmptable创建临时表Copyingtotmptableondisk把内存中临时表复制到磁盘,危险!!!locked被锁住 示例:剖析select
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
1.是否支持,看看当前的mysql版本是否支持
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
Show variables like "profiling";
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
议关闭 6.日常开发需要注意的结论Creatingtmptable创建临时表Copyingtotmptableondisk把内存中临时表复制到磁盘,危险!!!locked被锁住 示例:剖析select
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
2.开启功能,默认是关闭,使用前需要开启
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
set profiling=on;
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
3.运行SQL
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5
议关闭 6.日常开发需要注意的结论Creatingtmptable创建临时表Copyingtotmptableondisk把内存中临时表复制到磁盘,危险!!!locked被锁住 示例:剖析select
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
4.查看结果 show profile;
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
议关闭 6.日常开发需要注意的结论Creatingtmptable创建临时表Copyingtotmptableondisk把内存中临时表复制到磁盘,危险!!!locked被锁住 示例:剖析select
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
5.诊断SQL,show profile cpu,block io for query 上一步前面的问题SQL数字号码;
=5Y,使用到a,bwherea=3andb=5andc=4Y,使用到a,b,cwhereb=3或者whereb=3andc=4或者wherec=4Nwherea=3andc=5使用到a,但是c不可以
议关闭 6.日常开发需要注意的结论Creatingtmptable创建临时表Copyingtotmptableondisk把内存中临时表复制到磁盘,危险!!!locked被锁住 示例:剖析select
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. 日常开发需要注意的结论
议关闭 6.日常开发需要注意的结论Creatingtmptable创建临时表Copyingtotmptableondisk把内存中临时表复制到磁盘,危险!!!locked被锁住 示例:剖析select
Creating tmp table 创建临时表
Copying to tmp table on disk 把内存中临时表复制到磁盘,危险!!!
locked 被锁住
议关闭 6.日常开发需要注意的结论Creatingtmptable创建临时表Copyingtotmptableondisk把内存中临时表复制到磁盘,危险!!!locked被锁住 示例:剖析select
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
QL之SQL优化详解(三)1.索引优化一旦建立索引,select查询语句的where条件要尽量符合最佳左前缀的原则,如若能做到全值匹配最好。索引优化的第一个前提就是建好索引,第二个就是避免索引失效 索
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表createsortindex当前的SELECT中需要用到临时表在进行ORDERBY排序建议:创建适当的索引checkingqueryca
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
erby5 4.查看结果showprofile; 5.诊断SQL,showprofilecpu,blockioforquery上一步前面的问题SQL数字号码;Status建议Systemlock确认是
示例:剖析 select * from emp group by id%20 order by 5
议关闭 6.日常开发需要注意的结论Creatingtmptable创建临时表Copyingtotmptableondisk把内存中临时表复制到磁盘,危险!!!locked被锁住 示例:剖析select
由剖析报告看出,其中 Copying to tmp table 步骤花费了大量的时间,所以这条SQL应该优化了。
注:该文的SQL只是为了便利的梳理知识点使用,不需要关心这条SQL为什么这样写,了解以上知识的使用方法就可以啦
的列mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描isnull,isnotnull也无法使用索引like以通配符开头("%abc...")mysql索引失效会变成全