sql优化篇（一）：sql性能分析查找

1、通过show status 命令来显示各种sql的执行效率

命令： show session 或者 global status （session表示当前的连接中的统计、global表示子数据库上次启动至今）

例如：

Com_xxx表示的是每个xxx语句执行的次数

• Com_select:表示的是执行select语句的次数
• …
• Innodb_rows_read：select查询返回的行数
• …
• Show_queries：慢查询的次数

2、定位执行效率偏低的语句

全局查询日志

SET GLOBAL general_log=1;#0为关闭 1为开启
SET GLOBAL log_output='Table';

执行：SELECT * FROM mysql.general_log; 将看到执行过的sql信息。

慢查询开启及分析

慢查询日志在查询结束后才记录，可以通过命令：

show processlist

来查看当前的MySQL在执行的线程。

3、通过explain分析低效的sql的执行计划

执行一个sql（sakila数据库）：

• select_type： 表示select的类型；SIMPLE 代表简单表，不用表连接或子查询，PRIMRY 主查询（外层查询），UNION UNION中的第二个或者后面的查询语句，SUBQUERY 子查询中的第一个SELECT。
• table：输出结果集的表。
• possible_keys：查询中可能用到的索引。
• key：查询中实际用到的索引。
• key_len 索引的长度
• rows 扫描的行数
• type：访问类型 下面几种：

从左到右，性能由最差到最好

ALL index range ref eq_ref const,system NULL

ALL：全表扫描

index:索引全扫描

range: 索引的范围扫描 用于<,<=,>,>=,between等操作

ref:使用非唯一索引扫描或者唯一索引的前缀扫描

eq_ref:使用唯一索引扫描，多表链接中使用primary key或者unique key作为关联条件

const/system:单表中只有一个匹配行，查询速度快，根据主键或者唯一索引进行的查询

NULL:mysql不用访问表或者索引，直接能够得到结果

有时候通过explain分析执行计划不能很快的定位到sql的问题，这个时候需要选择profile联合分析。

4、通过show profile分析sql

默认profile是关闭的，可以通过set语句在session级别开启profile

select @@profiling;

set profiling =1 ;

通过show profile可以看到每个语句的id，在查看这个sql的执行过程中各个状态消耗的时间。

此外可以通过

SHOW PROFILE cpu,block io FOR QUERY Query_ID;或将 cpu,block io 改为 all

来查看各个查询的cpu 以及 io 的状态消耗时间。

若：Status中出现以下4个中一个必须优化

总结

1. 慢查询开启、查看status
2. explain+慢sql分析
3. show profile查询sql执行细节和生命周期
4. 后续调优