SQL优化分析

标签：分析 column ref 查询索引 key SQL table 优化

一、慢查询日志与分析

什么是慢查询日志

1 MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。
2 具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。long_query_time 的默认值为 10，意思是运行 10秒以上的语句。
3 由它来查看哪些 SQL 超出了我们的最大忍耐时间值，比如一条 sql 执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的 sql，结合 explain 进行全面分析。

开启慢查询日志

修改配置文件my.cnf，在[mysqld]段落中加入如上参数即可，例如：

[mysqld]
# ...
slow_query_log=1 
slow_query_log_file=/var/lib/mysql/cakin-slow.log 
long_query_time=3 
log_output=FILE

然后重启MySQL。

分析慢查询日志

分析慢查询日志表

当log_output = TABLE时，可直接用如下语句分析:

select * from `mysql`.slow_log

然后按照条件做各种查询、统计、分析。

分析慢查询日志文件
使用show variables like 'slow_query_log_file';可以查询到日志的名称

日志分析工具 mysqldumpslow

1、查看mysqldumpslow的帮助信息

[root@iZ251fpy8x9Z sfeuser01]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are
  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries # 即为返回前面多少条的数据
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string # 后边搭配一个正则匹配模式，大小写不敏感的
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

2、参考

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

二、SQL分析

explain 的使用

explain可用来分析SQL的执行计划，如：

explain select * from salaries where from_date = '1996-12-02';

输出结果：

表头说明：

字段	format=json时的名称	含义
id	select_id	该语句的唯一标识
select_type	无	查询类型
table	table_name	表名
partitions	partitions	匹配的分区
type	access_type	联接类型
possible_keys	possible_keys	可能的索引选择
key	key	实际选择的索引
key_len	key_length	索引的长度
ref	ref	索引的哪一列被引用了
rows	rows	估计要扫描的行
filtered	filtered	表示符合查询条件的数据百分比
Extra	没有	附加信息

EXPLAIN 结果解读

id
该语句的唯一标识。如果explain的结果包括多个id值，则数字越大越先执行；而对于相同id的行，则表示从上往下依次执行。

select_type
查询类型，有如下几种取值：

查询类型	作用
SIMPLE	简单查询（未使用UNION或子查询）
PRIMARY	最外层的查询
UNION	在UNION中的第二个和随后的SELECT被标记为UNION。如果UNION被FROM子句中的子查询包含，那么它的第一个SELECT会被标记为DERIVED。
DEPENDENT UNION	UNION中的第二个或后面的查询，依赖了外面的查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个 SELECT
DEPENDENT SUBQUERY	子查询中的第一个 SELECT，依赖了外面的查询
DERIVED	用来表示包含在FROM子句的子查询中的SELECT，MySQL会递归执行并将结果放到一个临时表中。MySQL内部将其称为是Derived table（派生表），因为该临时表是从子查询派生出来的
DEPENDENT DERIVED	派生表，依赖了其他的表
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	子查询，结果无法缓存，必须针对外部查询的每一行重新评估
UNCACHEABLE UNION	UNION属于UNCACHEABLE SUBQUERY的第二个或后面的查询

table
表示当前这一行正在访问哪张表，如果SQL定义了别名，则展示表的别名。

partitions
当前查询匹配记录的分区。对于未分区的表，返回null。

type
连接类型，有如下几种取值，性能从好到坏排序 如下：

system：该表只有一行（相当于系统表），system是const类型的特例;
const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可;

eq_ref：当使用了索引的全部组成部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型，性能仅次于system及const。

-- 多表关联查询，单行匹配
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

-- 多表关联查询，联合索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

ref：当满足索引的最左前缀原则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。

-- 根据索引（非主键，非唯一索引），匹配到多行
SELECT * FROM ref_table WHERE key_column=expr;

-- 多表关联查询，单个索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

-- 多表关联查询，联合索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

fulltext：全文索引。
ref_or_null：该类型类似于ref，但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询
```
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
```
index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引。
unique_subquery：该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引。例如：
```
value IN (SELECT primary_key FROM single_table WHERE some_expr)
```
index_subquery：和unique_subquery类似，只是子查询使用的是非唯一索引
```
value IN (SELECT key_column FROM single_table WHERE some_expr)
```
range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。
index：全索引扫描，和ALL类似，只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：
- 如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain的Extra 列的结果是Using index。index通常比ALL快，因为索引的大小通常小于表数据。
- 按索引的顺序来查找数据行，执行了全表扫描。此时，explain的Extra列的结果不会出现Uses index。
ALL：全表扫描，性能最差。

possible_keys
展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。

key
表示MySQL实际选择的索引。

key_len
索引使用的字节数。由于存储格式，当字段允许为NULL时，key_len比不允许为空时大1字节。
key_len计算公式： https://www.cnblogs.com/gomysql/p/4004244.html

ref
表示将哪个字段或常量和key列所使用的字段进行比较。

如果ref是一个函数，则使用的值是函数的结果。要想查看是哪个函数，可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。

rows
MySQL估算会扫描的行数，数值越小越好。

filtered
表示符合查询条件的数据百分比，最大100。用rows × filtered可获得和下一张表连接的行数。例如rows = 1000，filtered = 50%，则和下一张表连接的行数是500。

索引优化的方法

（1）选择合适的索引列，选择在where，group by，order by，on从句中出现的列作为索引项，对于离散度不大的列没有必要创建索引。

（2）索引字段越小越好(因为数据库的存储单位是页，一页中能存下的数据越多越好 )

（3）离散度大得列放在联合索引前面

标签：分析,column,ref,查询,索引,key,SQL,table,优化
From： https://www.cnblogs.com/d111991/p/16916072.html