性能调优|explain、show index、show status、show variables经典知识整理

Explain

       explain做为sql查询调优的必备技巧，其查询出来的计划内容需要完全理解，才能更好的调优

示例：EXPLAIN SELECT 字段1,字段2，… FROM TABLENAME;

• id ：SELECT的查询序列号，数值越大，越先执行，一般子查询数值较大，数值相同，为同一执行组，从上往下执行,若为null（union查询），最后执行。
• select_type ：表每个select子句的查询类型
  1> SIMPLE(简单SELECT，不使用UNION或子查询等)
  2> PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
  3>UNION(UNION中的第二个或后面的SELECT语句)
  4> DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
  5>UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
  6>SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
  7> DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
  8> DERIVED(派生表的SELECT, FROM子句的子查询)
  9> UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
• table ：表名
• partitions ：匹配的分区
• type ：(以下排序，性能由好到差)

1）NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。一般在对索引列使用聚合函数上出现.
示例：explain select count(id) from dc_recordseller_saletran

2）const(system) ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或唯一索引），优化器优化时，可以转换为常量查询，而system则是const中的特例，只有少量的行，不需要进行磁盘IO；一般在where条件后紧跟主键、唯一索引检索会出现const。
示例：explain SELECT * FROM dc_recordseller_saletran WHERE id=“380”;

3）eq_ref:在连接(左连接、右连接，全连接，内连接等)中，查询使用了索引为主键或唯一键，连接表中只有一条记录匹配。
示例：EXPLAIN SELECT t.,r. FROM dc_recordseller_saletran t LEFT JOIN dc_recordseller r ON t.rsid= r.id

4）ref：与eq_ref不同的地方，在于连接表中有多条记录匹配，eq即为euqal单词缩写，这个连接类型只有在查询使用了不是唯一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。
示例：explain SELECT t.* FROM dc_recordseller_saletran t where card_number=“1”

5）range：只检索，索引范围内检索的值，范围查询
示例：explain SELECT t.* FROM dc_recordseller_saletran t where card_number in (“test”,“16ECX200000170”)

6）index：只遍历索引的值，一般索引值都小于全表

7）ALL：性能最差，全表扫描

• possible_keys ：列出可能被优化器使用的索引名称，可能有多个
• key ：实际使用的索引名称，可能有多个
• key_len ：实际使用的索引长度，字节单位，显示的值为索引字段的最大可能长度，并非实际使用长度
• ref ：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名，函数等
• rows ：显示被估算出来的扫描行数
• filtered ：返回结果的行占需要读到的行(rows列的值)的百分比,但总是显示100%，越大越好，此值无参考意义
• Extra ： 解析查询的额外信息，常见出现一些优化器执行过程中所做的操作提示，例如：Backward index scan、Using index、Using temporary、Using filesort、No tables used

Show index

示例：show index from tableName;

• Table：表名。
• Non_unique：是否唯一索引，0表否，即非唯一索引，1表是唯一索引。
• Key_name：索引名称，名称重复是联合索引。
• Seq_in_index ：在索引中的列序列号，联合索引（col1,col2）,col1列号为1
• Column_name： 索引的列名。
• Collation：列以什么方式存储在索引中，’A’（升序）或NULL（无分类）。默认的类型是utf8_general_ci。
• Cardinality：基数，表示索引中唯一值的数目的估计值。值越大越适合索引。
• Sub_part： 前置索引，列只有部分被编入索引，则为被编入索引的字符的数目，对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引。
• Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案。
• Null：如果列含有NULL，则含有YES。
• Index_type：索引类型，Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。
• Index_comment：注释。

Show status

       一般我们都会用show status 来查询服务器状态，一般我们用"handler_read"来查看索引的使用情况。查询索引使用率（使用前可以先清除状态，flush status）

示例：show status like “handler_read%”;

• Handler_read_first：此选项表明SQL是在做一个全索引扫描，注意是全部.
• Handler_read_key：使用索引key的读取次数
• Handler_read_next：此选项表明在进行索引扫描时，按照索引从数据文件里顺序取数据的次数。
• Handler_read_prev：此选项表明在进行索引扫描时，按照索引逆序从数据文件里取数据的次数，一般就是ORDER BY … DESC
• Handler_read_rnd：就是查询直接操作了数据文件，很多时候表现为没有使用索引或者文件排序。（d表disk，表磁盘）
• Handler_read_rnd_next：此选项表明在进行数据文件扫描时，从数据文件里取数据的次数。

结论：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高，说明查询低效。

Show variables

       我们常常需要查看服务的某些参数，包括慢日志，隔离级别，优化器参数等，都需要使用show variables

        慢日志

示例：show variables like “%long%”; #查询列入慢sql的时间
set long_query_time=10s #默认10s

show variables like “%slow%”;

       slow_query_log是否开启慢日志；slow_query_log_file慢日志所在路径

        隔离级别

示例：show VARIABLES like “%isolation%”

参考

• https://blog.csdn.net/youanyyou/article/details/109664306