今天分享一下如何快速定位慢查询SQL并优化。1、如何定位和优化慢查询SQL？一般有3个思考方向：根据慢日志定位慢查询SQL。使用explain等工具分析SQL执行计划。修改sql或者尽量让sql带索引。2、慢查询日志如何使用？先给出步骤，后面再说明，一共有3步1.开启慢查询日志首先开启慢查询日志，参数slow_query_log决定是否开启。在MySQL命令行中输入如下命令：setglobalslow_query_log=on;默认环境下，慢查询日志是关闭的，所以这里开启。2.设置慢查询阈值setgloballong_query_time=1;只要你的SQL实际执行时间超过了这个阈值，就会记录在慢查询日志中。阈值默认为10s。对于线上业务，一般建议将long_query_time设置为1s。如果某项业务的MySQL对QPS要求比较高，可以将慢查询设置为0.1s。当发现慢查询时，及时优化或者提醒开发重写。一般建议在测试环境中将long_query_time的阈值设置的比生产环境小。例如生产环境为1s，测试环境建议设置为0.5s。方便及时在测试环境中找到一些高效的SQL。甚至一些重要的业务测试环境也可以将long_query_time设置为0，以便记录所有语句。并注意慢查询日志的输出。上线前功能测试后，分析慢查询日志中每类语句的输出，重点关注Rows_examined（语句执行时从存储引擎读取的行数），提前优化。3.确定慢查询日志的文件名和路径显示全局变量，如'slow_query_log_file'，你会发现慢日志的默认路径是MySQL数据目录。我们可以看一下MySQL的数据目录。显示全局变量，如“datadir”；不用注意为什么不是MySQL8.0，跟版本没有关系。来吧，直接上菜吧，我自己也受不了干巴巴的定义。我们先来看变量，我把需要注意的地方框起来了。用quer查询相关变量：showglobalvariableslike'%quer%';这里设置慢查询阈值为1s：setgloballong_query_time=1;可以看到修改了：但是重启mysql客户端设置和计数慢查询的日志条目数会被清空，即恢复所有配置修改。用命令修改配置后，在命令行netstopmysql关闭MySQL服务，然后用netstartmysql启动MySQL服务，然后执行showglobalvariableslike'%quer%';你会发现配置已经恢复了。只有修改了配置文件才能永久改变，否则重启后数据库会恢复。3.慢查询实例演示，新手可以看懂数据表结构，偷懒没写注释：CREATETABLE`person_info_large`(`id`BIGINTUNSIGNEDNOTNULLAUTO_INCREMENT,`account`VARCHAR(10),`name`VARCHAR(20),`area`VARCHAR(20),`title`VARCHAR(20),`motto`VARCHAR(50),PRIMARYKEY(`id`),UNIQUE(`account`),KEY`index_area_title`(`area`,`title`))ENGINE=INNODBAUTO_INCREMENT=1DEFAULTCHARSET=utf8这里的数据是200W。请注意表结构，只要记住哪些字段有索引，然后围绕这张表进行分析。这个3.36s不是实际执行时间，实际执行时间还得去慢查询日志里看Query_time参数。可以看到Query_time:6.337729s，超过了1s，所以会记录下来。一条select语句查询这么久，实在受不了。图中其他参数解释如下：Time：慢查询发生的时间Query_time：查询时间Lock_time：等待锁表的时间Rows_sent：语句返回的行数Rows_exanined：读取的行数在语句执行期间从存储引擎。以上方法用于查看系统自带的慢查询日志。如果觉得系统自带的慢查询日志不方便查看，可以使用pt-query-digest或mysqldumpslow等工具对慢查询日志进行分析。:正在执行一些慢查询，结果数据库负载过高。由于慢查询还没有执行，所以在慢查询日志中看不到任何语句。这时可以使用showprocesslist命令查看正在执行的慢查询。showprocesslist显示哪些线程正在运行。如果您具有PROCESS权限，则可以查看所有线程。否则，只有当前会话线程可见。4、查询语句慢怎么办？explain带你分析SQL执行计划。根据上一节的表结构可以知道account是一个加了唯一索引的字段。explain分析执行计划。我们需要关注select_type,type,possible_keys,key,Extra这些列，我们一一解释，看select_type列，这里是一个简单的简单查询，其他值列在下面。type列，这里是index，意思是全索引扫描。从上到下的表格代表从最好到最差的SQL查询性能。如果type类型为all，则表示SQL语句需要优化。注意：如果type=NULL，表示MySQL不需要访问表或索引，可以直接获取结果，如explainselectsum(1+2);possible_keys表示可能使用的索引列，key表示实际使用的索引列，以实际使用的索引列为准，是查询优化器优化后选择的，然后我们也可以强制使用自己的索引列根据实际情况查询。Extracolumn，这里是Usingindex一定要注意的，如果Extra中出现Usingfilesort和Usingtemporary，说明MySQL根本不能使用该索引，效率会受到严重影响，所以尽量优化可能的。Usingfilesort的出现表明MySQL使用外部索引对结果进行排序，而不是按照索引的顺序从表中读取相关内容。有了索引，B+树就维护好了。数据已经排序，这意味着根本没有使用索引，但在读取后对数据进行排序，可能在内存或磁盘上。也有人把MySQL中不能使用索引的排序操作称为“文件排序”。Usingtemporary的出现说明MySQL在对查询结果进行排序时使用了一张临时表，常见于orderby和groupbygroup查询。回到上一个话题，我们看到account是一个加了唯一索引的字段。explain分析执行计划后。直接按账号降序查询：查看慢查询日志，发现使用索引后，200万条数据的查询速度快了2s。然后我们分析查询名称的sql执行计划。然后给name字段加索引：加了索引之后，继续看查询name的SQL执行计划：对比之前name没有加索引时的执行计划，会发现加了索引之后，type从ALL全表扫描更改为Into索引索引扫描。orderby不是使用filesort，而是使用index，这里B+树已经对这个非聚集索引的索引字段的值进行了排序，而不是等查询的时候再排序。然后我们继续执行查询语句。这个时候，这个名字已经被索引了。原来在将姓名加入索引之前，按降序查询姓名需要6.337729s。添加索引后，按降序查询名称需要3.479827s。原因是B+树的结果集已经有序了。5、当主键索引、唯一索引、公共索引都存在时，查询优化器如何选择？查询数据条数，这里是count(id)，分析sql执行计划：这里实际使用的索引是account的唯一索引。Analyze：实际使用哪个索引是由查询优化器决定的。B+树的叶子节点是链表结构，遍历链表可以统计其个数。但是这张表有主键索引、唯一索引和普通索引。优化器选择account的唯一索引肯定不会使用主键索引，因为主键索引是聚集索引，每个叶子包含特定的行记录（很多列数据都在里面），非索引的每个叶子-聚簇索引只包含下一个主键索引的指针。显然，叶子节点包含的数据越少越好，查询优化器不会选择主键索引。当然也可以强制使用主键索引，然后分析SQL执行计划。我们看一下优化器默认使用唯一索引的大概执行时间：676ms：强制使用主键索引大概执行时间为779ms：我们可以使用forceindex来强制指定索引，以及然后分析执行计划，看哪个索引更好，因为查询优化器选择索引不一定100%准确。您可以根据实际场景分析来决定是否使用查询优化器选择的索引。
