MySQL优化笔记 其一

MySQL优化笔记

explain参数篇

• type类型性能好坏

ALL, index,  range, ref, eq_ref, const, system（从左到右，性能从差到好）

• Extra 可能的值

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

索引

• 最左匹配原则
  资料网址
• 所谓索引的选择性（Selectivity），是指不重复的行记录数（也叫基数，Cardinality）与表记录数（#T）的比值：
  Index Selectivity = Cardinality / #T
• 两表连接，左连接（left join）在右表加索引，右连接（right join）在左表加索引。因为左连接是以左表为基准查询右表的数据，左表肯定是全查询，右表才会用到索引。右连接同理。
• 三表连接，和上面同理。
• Join 优化。尽可能减少Join语句中的NestedLoop的循环总次数；永远用小结果集驱动大的结果集。（例如：书籍类别库驱动书籍库）。尽量优化NestedLoop的内层循环。保证Join语句中被驱动表上条件字段已经建立索引。无法保证索引的情况下可以增加JoinBuffer的大小。

索引失效情况

这里没有列出全部情况，有需要请自行百度

• 覆盖索引可解决%左前缀模糊查询 在InnoDB引擎下
  前置知识

针对未加辅助索引（比如这里建立name的索引）时，select id from xxxxx where name like ‘%xx%’（以下简称语句，区别只有select后的字段不同，这里id是主键）使用的是全表扫描；添加辅助索引之后使用到的是索引列扫描(type为index) 问题的分析

未加辅助索引时，查询id是直接走主（primary）索引，而主索引叶子节点存储的是行数据，左前缀%模糊查询就需要扫描整个主索引的叶子节点来匹配值，即扫描全表数据，type自然是ALL。
添加辅助索引后，辅助索引的叶子节点存储的是主键的值，后续需要第二次查询主索引才能找到行数据。但是上面的sql语句获取的只是id这一列，在查找辅助索引时就能定位到具体id，左前缀%模糊查询必然是搜索所有叶子节点的，而这里所需要的只是查找所有的辅助索引的叶子节点，Type自然是index。

• 隐式类型转换会使得索引失效，比如name是字符类型，但是在where中这么用 where name=111 这里会发生自动转换。
• 范围条件后索引失效，现有索引idx(a,b,c)，查询条件where a=‘ss’ and b>1 and c=‘zz’

where a=‘ss’ and b>1 and c='zz'  可以用到a和b的索引，type为range

where a=‘ss’ and c>'zz' and b=1  可以用到a，b，c的索引，因为mysql解释器会优化语句，判定顺序以建索引的顺序为准。

• order by 后接的列与where中的条件列共享相同的索引，现有索引idx(a,b,c,d)

select * from `xxx` where a='a' and b='b' and d='d' order by c  该条语句使用到了a,b两个索引列，但是order by走的是索引，只是此时c是用作排序而不是查找。

select * from `xxx` where a='a' and d='d' order by b,c  使用到了a索引列，order by排序使用到了b,c索引列。

select * from `xxx` where a='a' and d='d' order by c,b  与where不同，order by后面的顺序不能随意写。这里order by无法使用到索引。

select * from `xxx` where a='a' and b='b' and d='d' order by c,b  这里因为b已经确定为一个常量，等同于order by c,'b'，实际是c的排序，因此可以使用到索引。

• group by 和order by 同理，group by会有临时表产生。分组之前必排序。

分析步骤

1. 观察，至少跑一天，看看生产的慢SQL情况。
2. 开启慢查询日志，设置阈值，比如超过5秒就是慢SQL，并把它抓取出来。
3. explain+慢SQL分析。
4. show profile
5. 运维经理或者DBA，进行SQL数据库服务器的参数调优。

exists和in

功能类似，在特定情况下性能优于对方

• exists 将主查询的数据放到子查询中做条件验证，根据验证结果来决定主查询的数据结果是否保留

select ... from table where exists (subquery)

• exists子查询中可以是select * 也可以是 select 1或select ‘X’，官方说法是实际执行时会忽略select清单，因此没有区别。
• 小表驱动大表原则。