MySql学习（五）-MySql慢查询开启和性能优化分析

本文详细介绍了如何开启MySQL的慢查询日志，并通过分析慢查询的关键字段，如Select_type、table、type等，来理解查询性能。同时，文章提出了优化慢查询的注意事项，包括合理创建索引、避免过多索引、利用最左前缀原则等策略，旨在提高数据库性能。

MySql慢查询开启和性能优化分析

一、Mysql开启慢查询日志

Mysql中慢查询日志默认是关闭的，如果需要打开慢SQL日志，需要修改my.ini的配置文件。我们日常在工作中需要关注这些慢查询指标，我们一般设置慢SQL的标准为300ms。

1、修改my.ini配置文件

slow_query_log_file=path/filename。如果不设置，默认

long_query_time=0.3 单位是秒，0.3是指300ms。如果不认识，默认是10秒。

2、通过mysql命令设置

set global slow_query_log='ON';

set global slow_query_log_file='/usr/local/mysql/data/slow.log';

set global long_query_time=0.3;

二、分析mySql的慢查询

分析MySql的慢查询，使用Explain或者DESC命令，两者是一模一样的，以下几个字段的分析。

1、Select_type：表示Select的语句类型，有以下属性

1）SIMPLE：表示简单查询，其中不包含简单查询和子查询，

分析语句：explain select * from pay_account where account_no='1'

2）PRIMARY 表示主查询，或者最外层的查询语句

3）DERIVED 导出表的SELECT（FROM子句的子查询）

分析语句：explain select * from (select * from pay_account where account_no='1') b;

4）UNION 表示连接查询的第二个或者后面的语句，在SELECT之后使用了UNION。

5） UNION RESULT 连接查询的结果

分析语句：explain select * from pay_account where account_no='1' union select * from pay_account where account_no='2'

6）DEPENDENT UNION 连接查询中的第2个或者后面的语句，取决于外面的查询

7）DEPENDENT SUBQUERY 子查询的第一个Select，取决于外部的查询

分析语句：explain select * from pay_account where account_no in (select account_no from pay_account union select account_no from pay_account where account_no like '%jin%')

8）SUBQUERY 子查询中的第一个SELECT

分析语句：explain select * from pay_account where id =(select id from pay_account where account_no = "1")

2、table ：顾名思义，是用到的表名OR 临时创建的表名

3、type 表示表的链接类型，下面从最佳类型到最差类型给出各种连接类型

const：数据表中最多匹配一行，最常用的就是主键和唯一索引
eq_ref：联合查询时，使用主键或者唯一索引列
ref：用于带普通索引的列的 = >= <=的查询
ref_or_null：专门搜索包含NULL值的索引
index_merge：表示使用了索引合并优化方法，常用于（And、OR ）等多条件都命中索引的组合查询，key列包含了使用索引的清单，key_len包含了使用的索引的最长的关键元素.。较复杂，详细了解请参考：MySQL 优化之 index merge(索引合并)
unique_subquery：语句：value in ( select primary_key or uniq_key from xxx where some_expr)。子查询的索引查找函数，适用于查询出来的是主键OR唯一索引
index_subquery：同unique，子查询的是普通的索引。
range：范围类查询 > < between and in 查询等。
index：该类型与ALL相同，但是只查找索引树，比ALL更快。语句 select index_key from table where ...
All：最差的方式，需要扫描全表。造成慢查询的基本就是这个了

4、possible_keys:表示能使用哪个索引在表中找到该行。

5、Key:表示查询使用使用到的索引，如果没有选择索引，则该列的值是NULL，如果需要强制使用possible_keys中的索引，可以使用FORCE index、USR INDEX 或者 IGNORE INDEX。

6、key_len:表示MySql选择的索引字段按字节计算的长度，key_len可以用于协助查询组合索引中用了几个字段

7、ref:显示索引的哪一列被使用了，如果可能的话，是一个常数

8、rows:表示需要扫描的行数

9、Extra:表示查询时处理的详情信息

extra列返回的描述的意义

Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）

system 表只有一行：system表。这是const连接类型的特殊情况

const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待

eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）

ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免