定位并优化慢sql

一、根据慢日志定位慢查询sql

分析慢日志，慢日志是用来记录我们执行的比较慢的一些sql。

1.查出相关变量

show variables like ‘%quer%’
需要关注三个变量：
slow_query_log 慢日志开关
slow_query_log_file 全地址，这个文件会记录慢日志，慢sql会被写到这里
long_query_time 表示每次执行sql花费时间超过这个值，就被被记录到慢日志中。
设置：把slow_query_log打开 set global slow_query_log = on;
long_query_time设置为1s set global long_query_time = 1; 设置后需要重新连接客户端
可以在配置文件中改动，是永久保存的。在窗口中设置，重启数据库就会恢复默认值。

2.查出相关系统状态

show status like ‘%slow_queries%’;
slow_queries 慢查询的数量。这个是本次会话的慢sql条数，一旦关闭客户端，再重启，慢sql的条数会清零。

二、使用explain等工具分析sql

1.explain工具

在分析查询性能的时候，explain非常管用，这个关键字一般放在select查询语句前面。用于描述mysql如何执行查询操作，以及mysql成功返回结果集需要执行的行数。explain可以帮我们分析select语句，让我们知道查询效率低下的原因，从而改进我们的查询，让查询优化器能够更好的工作。

2.explain应用

直接在sql语句前面加上explain
explain select name from person_info_large order by name desc; 执行非常快。
该sql并没有被运行，只是对它进行了分析。explain的结果由多列组成：

其中有两列对于调优sql非常重要：type、extra
id表明了sql的执行顺序，id越大的越先执行。

3.type类型

type表示mysql找到需要数据行的方式。性能从最优到最差按照如下顺序排序：
system>const>eq_ref>ref>fulltext>ref_ornull>index merge>unique_subquery>index_subquery>range>index>all
其中index和all表明本次查询是全表扫描，因此当我们看到语句是慢查询语句，并且type是两个值中的一个，说明语句是需要优化的。index与all区别为index类型只遍历索引树。

4.extra类型

extra虽然没有type这么直观，但可以从这里获取到更为详细的信息，辅助我们了解语句的执行方式。它的类型也非常多。这里只需要关注两种极有可能需要优化的方式。
exra 中出现以下 2 项意味着 MYSQL 根本不能使用引,效率会受到重大影响。应尽可能对此进行优化.

extra项	说明
Using filesort	表示 MySQL 会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索完成的排序操作称为“文件排序”
Using temporary	表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 groupby。

Using filesort 用不到表里的任何索引，以mysql外部的排序方式做排序，这样会远慢于索引的排序方式。
Using temporary 将排序的结果存到临时表，方便后面做select等去使用。

5.例子

在查询表person_info_large时，发现name没有索引但account有，可以用account替代name进行查找，可以满足业务上的需求。
有时候业务要求，改sql无法满足。我们就需要加索引。

三、增加索引

alter table person_info_large add index idx_name(name);给name加索引，属于DDL，用来操作表结构。用来操作数据的DML语言才有可能进入慢查询语句中。
加了索引之后，type变成了index，extra变成了Using index。

四、索引分析例子

1.sql语句

explain select count(id) from person_info_large;
统计person_info_large有多少条数据，会走哪个索引？
B+树的叶子节点是有链表的，所以统计的时候就走链表。
会走密集索引还是稀疏索引？
运行后，type是index, key是account，用的是唯一键，并不是主键。extra是Using index用了索引。

2.为什么用account而不是主键id?

是查询优化器做决定的，mysql的查询优化器最重要的目标是尽可能的使用索引，并且使用最严格的索引来消除尽可能多的数据行。最终目标是比较select语句查找数据行，而不是排除数据行，优化器试图排除数据行的原因是它排除数据行的速度越快找到与条件匹配的数据行条件也就越快。因此查询优化器会根据它的分析和判断的标准决定走哪一个索引。
这里没有选择主键索引大致原因是由于密集索引的叶子节点包含其他列的全部数据，这里变成了它的缺点，这样查询的效率比稀疏索引低，因为稀疏索引只存储了关键字和主键的值，这样在内存中就能加载更多的关键字和主键值，能提高性能。

3.强制语句走主键索引

explain select count(id) from person_info_large force index(primary);

比较执行时间，主键索引更慢。