sql优化分析

查询优化器
explain
开启慢日志
开启全局查询日志
mysqldumpshow
- 查mysqldumpslow的帮助信息
- 工作常用参考
show Profile
sql优化分析总结

查询优化器

查询优化器步骤分为：

条件优化
计算全表扫描成本
找出所有能用到的索引
针对每个索引计算不同的访问方式的成本
选出成本最小的索引以及访问方式

开始查询优化器日志
```
-- 开启
set optimizer_trace="enabled=on";
-- 执行sql
-- 查看日志信息
select * from information_schema.OPTIMIZER_TRACE;
-- 关闭
set optimizer_trace="enabled=off";
```
成本

I/O成本
InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。(页面花费的成本默认是1.0)

CPU成本
读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。(每一条记录的成本0.2)

NULL成本分三种
nulls_equal：认为所有NULL值都是相等的。这个值也是innodb_stats_method的默认值。如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。
nulls_unequal：认为所有NULL值都是不相等的。如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别少，所以倾向于使用索引进行访问。
nulls_ignored：直接把NULL值忽略掉。

explain

使用explain关键字可以模拟优化器执行sql查询语句,从而知道mysql是如何处理你的sql语句.分析你的查询语句是否是表结构的性能瓶颈

id

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

id相同	执行顺序由上至下
id不同	如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同,同时存在	先读顺序由上至下,然后看第二个参数

select_type

查询的类型: 主要用来区分普通查询、联合查询、子查询等复杂查询。

SIMPLE	简单的select查询,查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DERIVED	在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询, 把结果放在临时表里。
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为: DERIVED
UNION RESULT	从UNION表获取结果的SELECT

table

显示这一行数据是关于哪张表的

type

依次顺序: system > const > eq_ref > ref > range > index > all

system	表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计。
const	表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
eq_ref	唯一性索引扫描，对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行. 本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
range	只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、 >、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
index	Full Index Scan, index 与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。( 也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的)
all	FullTableScan,将遍历全表以找到匹配的行
	备注:一般来说，得保证查询至少达到range级别，最好能达到ref。

possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key

实际使用的索引。如果为NULL,则没有使用索引
查询中若使用了覆盖索引，则该索引和查询的select字段重叠(查看Extra的 USING index)

key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key len显示的值为索引字段的最大可能长度，并非实际使用长度，即key len是根据表定义计算而得，不是通过表内检索出的。

所有的索引字段，如果没有设置not null，则需要加一个字节。
可变长字段（varchar），两个字节存储实际的长度。
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

Extra

包含不适合在其他列中显示，但十分重要的额外信息(前3个，比较重要)

Using filesort	说明mysq\|会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”
Using temporary	使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
USING index	1. 表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错! 1. 如果同时出现using where,表明索引被用来执行索引键值的查找; 1. 如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。 1. 覆盖索引(Covering Index)
Using where	表明使用了where过滤
using join buffer	使用了连接缓存
impossible where	where子句的值总是false,不能用来获取任何元组
select tables optimized away	在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct	优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

覆盖索引(Covering Index) , 也说为索引覆盖。
就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
注意:
如果要使用覆盖索引，一定要注意select列表中只取出需要的咧，不可select *,
因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

查询优化建议

SQL语句中IN包含的值不应过多，不能超过200个，200个以内查询优化器计算成本时比较精准，超过200个是估算的成本，另外建议能用between就不要用in，这样就可以使用range索引了。
SELECT语句务必指明字段名称：SELECT * 增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。
当只需要一条数据的时候，使用limit 1
排序时注意是否能用到索引
使用or时如果没有用到索引，可以改为union all 或者union
如果in不能用到索引，可以改成exists看是否能用到索引
使用合理的分页方式以提高分页的效率
不建议使用%前缀模糊查询
避免在where子句中对字段进行表达式操作
避免隐式类型转换
对于联合索引来说，要遵守最左前缀法则
必要时可以使用force index来强制查询走某个索引
对于联合索引来说，如果存在范围查询，比如between,>,<等条件时，会造成后面的索引字段失效。
尽量使用inner join，避免left join，让查询优化器来自动选择小表作为驱动表
必要时刻可以使用straight_join来指定驱动表，前提条件是本身是inner join
优化口诀
全值匹配我最爱，最左前缀要遵守;
带头大哥不能死，中间兄弟不能断;
索引列上少计算，范围之后全失效;
LIKE百分写最右，覆盖索引不写星;
不等空值还有or，索引失效要少用;
VAR引号不可丢，SQL高级也不难!

开启慢日志

三个参数

slow_query_log 是否开启慢查询
slow_query_log_file 慢查询的地址
long_query_time 慢查询的时间

开启全局查询日志
三个参数
general_log=1 是否开启
general_log_file=file_path 记录日志文件的路径
log_output=FILE / TABLE 输出格式

select * from mysql.general_log; 查看日志

mysqldumpshow

如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了该日志分析工具。

查mysqldumpslow的帮助信息

s	是表示按照何种方式排序:
c	访问次数
l	锁定时间
r	返回记录
t	查询时间
al	平均锁定时间
ar	平均返回记录数
at	平均查询时间
t	即为返回前面多少条的数据
g	后边搭配“个正则匹配模式，大小写不敏感的

工作常用参考

#得到返回记录集最多的10个SQL
  mysqldumpslow -s r -t 10 Ivar/lib/mysql/atguigu-slow.log
#得到访问次数最多的10个SQL
  mysqldumpslow -s c -t 10 Ivar/lib/mysql/atguigu-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
  mysqldumpslow -s t -t 10 -g "left join" /var/ib/mysql/atguigu-slow.log
#另外建议在使用这些命令时结合|和more使用，否则有可能出现爆屏情况
  mysqldumpslow -S r -t 10 /var/ib/mysql/atguigu-slow.log | more

show Profile

mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量。
命令

show variables like 'profiling';  -- 是否开启profile
set profiling=on;   -- 开启profile
show profiles;  -- 看结果,得到最近的sql

诊断SQL

show profile [参数[,参数…]] for query [上面的Query_ID]
参数说明 | 参数 | 说明 | | —- | —- | | ALL | 显示所有的开销信息 | | BLOCK IO | 显示块IO相关开销 | | CONTEXT SWITCHES | 上下文切换相关开销 | | CPU | 显示CPU相关开销信息 | | IPC | 显示发送和接收相关开销信息 | | MEMORY | 显示内存相关开销信息 | | PAGE FAULTS | 显示页面错误相关开销信息 | | SOURCE | 显示和Source function， Source file， Source_ line相关的开销信息 | | SWAPS | 显示交换次数相关开销的信息 |

mysql> show profile cpu,block io for query 7;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000060 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables       | 0.000026 | 0.000000 |   0.000000 |         NULL |          NULL |
| System lock          | 0.000006 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                 | 0.000013 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing           | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics           | 0.000014 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing            | 0.000005 | 0.000000 |   0.000000 |         NULL |          NULL |
| Creating tmp table   | 0.000288 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing            | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| Copying to tmp table | 0.064244 | 0.062500 |   0.000000 |         NULL |          NULL |
| Sorting result       | 0.000021 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sending data         | 0.000011 | 0.000000 |   0.000000 |         NULL |          NULL |
| end                  | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| removing tmp table   | 0.000006 | 0.000000 |   0.000000 |         NULL |          NULL |
| end                  | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end            | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables       | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items        | 0.000046 | 0.000000 |   0.000000 |         NULL |          NULL |
| logging slow query   | 0.000023 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up          | 0.000001 | 0.000000 |   0.000000 |         NULL |          NULL |
+----------------------+----------+----------+------------+--------------+---------------+

表中遇到Status 需要注意执行事件

converting HEAP to MySIAM 数据过大MyISAM内存装不下，向磁盘上搬运
Creating tmp table 临时表创建
Copying to tmp table on disk 复制临时表到磁盘
locked 锁。阻塞