Mysql 优化 调优

此篇作为自己调优和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子句的顺序。

1. id相同，执行顺序从上至下
2. id不同，执行顺序由大到小

select_type：反映的是（子）查询类型

1. simple：简单的select查询，查询中不包含子查询或union。
2. primary： 复杂查询最外层
3. subquery: 除了from之外的子查询
4. derived： from中包含的子查询，也叫做派生表，会将查询结果放入临时表
5. union： union连接的两个select查询 这是标记第二个select ，如果union在from里，第一个union会被标记为derived，最外层为primary
6. union result：不需要参与查询 union结果集

table：反映这一行数据是关于哪张表的

type：连接类型
查询效率： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >all

1. system: 表中只有一行数据或者是空表,只能用于myisam和memory表。 如果是Innodb引擎表,type列在这个情况通常都是all或者index，这是const类型的特例，一般不会出现
2. const：查询条件用到了常量，通过索引一次就找到，常在使用primary key或unique索引中出现。
3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。
4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行
5. range: 这个连接类型使用索引返回一个范围中的行，比如使用>,<,between,in查找东西时发生的情况。
6. index: index类型只遍历索引树,通常比all快
7. all: 全表扫描

possible_keys：可能用到的索引

key： 实际用到的索引

key_len: 索引长度

ref：反映哪些列或常量被用于查找索引列上的值

rows: MySQL所认为的它在找到正确的结果之前必须扫描的记录数

filtered:这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例

Extra: 连接查询的一些信息:

1. using filesort：mysql中无法利用索引完成排序，这时会mysql需要进行额外的步骤来发现如何对返回的行排序,创建索引时就会对数据先进行排序，出现using filesort一般是因为order by后的条件导致索引失效，最好进行优化,order by的排序最好和所建索引的顺序和个数一致
2. using temporary: mysql在对查询结果排序时使用临时表,这通常发生在对不同的列集进行ORDER BY上
3. using index: 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率好
4. using where: 使用了where
5. Not exists: 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

三、避免索引失效
列举了一些索引失效和避免索引失效的注意点：

1. 查询从索引的最左前列开始并且不跳过索引中的列，中间跳过的值，后面的索引会失效

2. 索引列上做了计算、函数，会导致索引失效而转向全表扫描

3. 索引会对范围生效，但是范围条件右边会失效
   
   
   

4. 减少select *，尽量使用覆盖索引

5. mysql在使用不等于（!= 或 <>）时无法使用索引，is null，is not null也无法使用索引

6. like避免使用%%查询，全模糊。不会走索引。而半模糊 “123%”后面带%会走索引，通过覆盖索引可以解决like '%字符串%'索引失效的问题

7. 字符串不加单引号索引失效

8. 排序必须根据索引顺序

9. 对于常量类型，查询优化器会自动调优SQL，顺序不影响

10. 左连接把索引建在从表的关联字段比较好，主表一定会有，从表才是检索的关键，join语句中被驱动表上join条件字段加索引可以提高效率

11. count(*) 加上条件会变慢。