一 概述
1.0 sql调优的过程
SQL调优过程:
- 观察,至少跑1天,看看生产的慢SQL情况。
- 开启慢查询日志,设置阙值,比如超过5秒钟的就是慢SQL,并将它抓取出来。
- explain + 慢SQL分析。
- show profile,查询SQL在Mysql服务器里面的执行细节和生命周期情况。
- 运维经理 or DBA,进行SQL数据库服务器的参数调优。
1.1索引失效以及优化口诀
优化的口诀如下:
全值匹配我最爱, 最佳左前缀法则 ;
带头大哥不能死, 中间兄弟不能断;
索引列上少计算, 范围之后全失效;
LIKE 百分写最右, 覆盖索引不写 *
;
不等空值还有 OR, 索引影响要注意;
VAR 引号不可丢, SQL 优化有诀窍。
在索引列上进行了函数操作,MySQL内部会进行了隐式转换,导致索引失效,从而产生全表扫描。
注意:以下操作都是在这个新建的索引下进行的操作,如下:
alter table staffs add index idx_staffs_nameAagePos(name,age,pos)
1.1.1 全值匹配我最爱
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
结论:全职匹配我最爱指的是,查询的字段按照顺序在索引中都可以匹配到,优化器会在不影响 SQL 执行结果的前提下,给你自动地优化。
1.1.2 最佳左前缀法则,带头大哥不能死, 中间兄弟不能断;
使用复合索引,需要遵循最佳左前缀法则,即如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
创建索引:alter table staffs add index idx_staffs_nameAagePos(name,age,pos)
下图中的第二个sql: 即使跳过了中间的索引,但是其长度没变化,跟第一个sql只使用name的索引的长度一样,那就说明第二个sql值使用了部分索引,只使用了name的索引,后面的age,pos失效。不然的话长度肯定大于74。
1.1.3 索引列上少计算
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。
所以字符串类型的数据,该加单引号的一定要加!
1.1.4 范围之后全失效
复合索引:CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
索引列上不能有范围查询,少用>,<,between…and等结构;范围查询的列忽略,索引失效,后面的索引列也跟着失效,不起作用。
建议:将可能做范围查询的字段的索引顺序放在最后
1.1.5 覆盖索引不写 *
1.1.6 使用不等于(!= 或者<>)的时候
1.1.7 不用 is null 或者is not null
2.如果字段age实则为null 时候, age is null 使用到了索引,age is not null 没有使用到索引