深入浅出 MySQL：SQL 优化 - 优化 SQL 一般步骤

SQL 优化测试数据：http://downloads.mysql.com/docs/sakila-db.zip
 

一、通过 show status 了解 SQL 的执行频率

show global status like 'com_%';                       -- 数据库上次启动至今的状态信息
show session status like 'com_%';                      -- 当前连接的状态信息（默认）

Com_[select|update|insert|delete] ：执行 select|update|insert|delete 操作的次数 

innodb_rows_[read|inserted|updated|deleted] ：Innodb引擎中 select|update|insert|delete 操作的次数 

Com_[commit|rollback] : 执行事务提交和回滚的次数

Connections : 试图连接 MySQL 次数

Uptime : 服务器工作时间

Slow_queries : 慢查询的次数

通过上述参数，了解当前数据库的应用是以查询还是插入、更新为主。

二、定位执行效率较低的 SQL

-- 启动MySQL时，指定 --log-slow-queries ，将慢日志写入到文件中

-- show processlist; 查看当前线程状态，是否锁表等

三、通过 Explain 分析低效的 SQL

select_type ： 
    SIMPLE : 简单表，不使用表连接或子查询
    PRIMARY : 主查询，外层查询
    UNION : UNION 第二个或后面的查询语句
    SUBQUERY : 子查询的第一个select语句

table : 输出结果集的表

type : 
    ALL : 全表扫描
    index : 索引全扫描，遍历整个索引来查询
    range : 索引范围查询，常见于 < > >= between 等操作
    fulltext : 全文索引检索，全文索引的优先级很高，若全文索引和普通索引同时存在时，优先选择使用全文索引
    ref : 使用非唯一索引扫描 或 唯一索引的前缀扫描
    eq_ref : 使用唯一索引
    const/system : 单表最多一个匹配行进行搜索，如主键或唯一键的搜索
    NULL : 不需访问表或索引，就能得到结果

-- type 从上往下是性能越来越好

-- 更多参数：https://www.cnblogs.com/liujingyuan789/p/6061188.html

possible_keys : 查询时可能使用到的索引

keys : 实际使用到的索引

key_len : 使用到的索引长度

rows : 扫描行的数量

Extra : 执行情况的说明与描述

explain extended xxx;

show warnings\G;                -- 查看实际执行（优化）的SQL语句

explain partitions xxx;                        -- 查看SQL访问的分区，如果表有分区的话

四、通过 show profile 查看 SQL 执行时间

select @@have_profiling;            -- 查看是否支持 profiling

select @@profiling;                 -- 查看 profiling 是否开启

set profiling = 1;                  -- 开启 profiling

select * from xxx ...;                  -- 当执行完sql语句时

show profiles;                          -- 找到sql语句的Query_ID

show profile for query Query_ID;        -- 查看该SQL语句每个状态的耗时

                                        -- sending data 表示开始访问数据行到返回给客户端，需要做大量磁盘读取，一般耗时较长

-- MyISAM 直接缓存了 count(*) 操作，所以统计 count(*) 速度较快

五、通过 optimizer trace 的方式