此篇作为自己调优和sql性能优化做的一些基础方案和笔记,借鉴于多篇文章和自己实用总结;
一、慢查询日志
慢查询日志是在整个库上做一定的慢执行sql抓取
(1)设置开启:SET GLOBAL slow_query_log = 1; # 默认未开启,开启会影响性能,mysql重启会失效
(2)查看日志路径和是否开启:SHOW VARIABLES LIKE '%slow_query_log%';
(3)设置阈值:SET GLOBAL long_query_time=3; # 执行3秒上的加入慢查询日志
(4)查看阈值:SHOW VARIABLES LIKE 'long_query_time%'; # 需要重连或新开一个会话
查看慢查询日志记录数:SHOW GLOBAL STATUS LIKE '%Slow_queries%';
进入路径查看日志
二、执行计划分析
有了查询慢的sql后就需要进行执行计划的分析
Explain可以模拟优化器执行SQL查询语句,语法:Explain + SQL
id:反映的是表的读取的顺序,或查询中执行select子句的顺序。
- id相同,执行顺序从上至下
- id不同,执行顺序由大到小
select_type:反映的是(子)查询类型
- simple:简单的select查询,查询中不包含子查询或union。
- primary: 复杂查询最外层
- subquery: 除了from之外的子查询
- derived: from中包含的子查询,也叫做派生表,会将查询结果放入临时表
- union: union连接的两个select查询 这是标记第二个select ,如果union在from里,第一个union会被标记为derived,最外层为primary
- union result:不需要参与查询 union结果集
table:反映这一行数据是关于哪张表的
type:连接类型
查询效率: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >all
- system: 表中只有一行数据或者是空表,只能用于myisam和memory表。 如果是Innodb引擎表,type列在这个情况通常都是all或者index,这是const类型的特例,一般不会出现
- const:查询条件用到了常量,通过索引一次就找到,常在使用primary key或unique索引中出现。
- eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描。
- ref:非唯一性索引扫描,返回匹配某个单独值的所有行
- range: 这个连接类型使用索引返回一个范围中的行,比如使用>,<,between,in查找东西时发生的情况。
- index: index类型只遍历索引树,通常比all快
- all: 全表扫描
possible_keys:可能用到的索引
key: 实际用到的索引
key_len: 索引长度
ref:反映哪些列或常量被用于查找索引列上的值
rows: MySQL所认为的它在找到正确的结果之前必须扫描的记录数
filtered:这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例
Extra: 连接查询的一些信息:
- using filesort:mysql中无法利用索引完成排序,这时会mysql需要进行额外的步骤来发现如何对返回的行排序,创建索引时就会对数据先进行排序,出现using filesort一般是因为order by后的条件导致索引失效,最好进行优化,order by的排序最好和所建索引的顺序和个数一致
- using temporary: mysql在对查询结果排序时使用临时表,这通常发生在对不同的列集进行ORDER BY上
- using index: 表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率好
- using where: 使用了where
- Not exists: 优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了
三、避免索引失效
列举了一些索引失效和避免索引失效的注意点:
-
查询从索引的最左前列开始并且不跳过索引中的列,中间跳过的值,后面的索引会失效
-
索引列上做了计算、函数,会导致索引失效而转向全表扫描
-
索引会对范围生效,但是范围条件右边会失效
-
减少select *,尽量使用覆盖索引
-
mysql在使用不等于(!= 或 <>)时无法使用索引,is null,is not null也无法使用索引
-
like避免使用%%查询,全模糊。不会走索引。而半模糊 “123%”后面带%会走索引,通过覆盖索引可以解决like '%字符串%'索引失效的问题
-
字符串不加单引号索引失效
-
排序必须根据索引顺序
-
对于常量类型,查询优化器会自动调优SQL,顺序不影响
-
左连接把索引建在从表的关联字段比较好,主表一定会有,从表才是检索的关键,join语句中被驱动表上join条件字段加索引可以提高效率
-
count(*) 加上条件会变慢。