如何定位不合理的SQL引用在应用开发过程中，由于初期数据量较小，开发人员在编写SQL语句时更注重功能的实现，但是当应用系统正式上线后，随着生产数据量的快速增长，很多SQL语句逐渐出现性能问题，对生产的影响也越来越大。这时候，这些有问题的SQL语句就成为了整个系统性能的瓶颈。因此，我们必须对它们进行优化，本章将详细介绍如何优化MySQL中的SQL语句。当面对一个存在SQL性能问题的数据库时，我们应该从哪里入手进行系统的分析，这样才能尽快定位到问题SQL，解决问题。4.1如何查看SQL执行频率MySQL客户端连接成功后，可以通过--serverstatusinformationshow[session|global]status提供服务器状态信息；命令。show[session|global]status您可以根据需要添加参数“session”或“global”来显示会话级别（当前连接）和全局级别（自上次启动数据库以来）的统计结果。如果不写，默认参数是“session”。以下命令显示当前会话中所有统计参数的值：showstatuslike'Com______';显示像'Innodb_rows_%'这样的状态；com_xxx表示每条xxx语句执行的次数。我们通常关心以下统计参数。参数含义Com_select执行select操作的次数，一次查询加起来才1。Com_insert执行INSERT操作的次数对于批量插入INSERT操作只累加一次。Com_update执行更新操作的次数。Com_delete执行的DELETE操作数。Innodb_rows_readselect查询返回的行数。Innodb_rows_insertedINSERT操作插入的行数。Innodb_rows_updatedUPDATE操作更新的行数。Innodb_rows_deletedDELETE操作删除的行数。Connections尝试连接到MySQL服务器的次数。Uptime服务器工作时间。Slow_queries慢查询的数量。Com_*：这些参数是为所有存储引擎表操作累积的。Innodb_*：这些参数只针对InnoDB存储引擎，累加算法略有不同。4.2如何定位低效SQL有以下两种方法：慢查询日志（重要）：通过慢查询日志定位那些执行效率低的SQL语句，启动时加上--log-slow-queries[=file_name]选项,mysqld写入一个日志文件，其中包含所有执行时间超过long_query_time秒的SQL语句。Tips：如何获取慢查询SQL，参考上一章showprocesslist（重要）：慢查询日志是在查询完成后记录的，所以在应用反映有时查询慢查询日志无法定位问题是执行效率的问题。可以使用showprocesslist命令查看当前MySQL线程，包括线程状态，是否锁表等，可以实时查看SQL的执行情况，优化一些锁表操作。属性字段解释1）id列，用户登录mysql时系统分配的“connection_id”，可以使用函数connection_id()查看2）user列，显示当前用户。如果你不是root，这个命令只会显示用户权限范围内的sql语句。3）host栏，显示语句是从哪个IP端口发出的，可以用来跟踪出问题语句的用户。4）db列，显示这个进程当前连接的是哪个数据库？5）Command栏，显示当前连接执行的命令，一般取值为sleep（睡眠），query（查询），connection（连接）等。6）Time栏，显示该状态持续时间，单位是秒7）状态栏，显示使用当前连接的SQL语句的状态，是一个很重要的栏目。state描述语句执行中的某种状态。一条sql语句，以查询为例，可能需要经过复制到tmp表、排序结果、发送数据等状态才能完成8)info栏显示这条sql语句，是查询的重要依据判断问题陈述4.3使用explain分析执行计划--explain分析执行计划explainSELECT*FROMproduct_listWHEREstore_name='联想北大兴科店';字段含义idselectquerysequencenumber，是一组数字，表示select子句或操作按照表的查询顺序执行。select_type表示SELECT的类型。常用值包括SIMPLE（简单表，即不使用表连接或子查询）、PRIMARY（主查询，即外查询）、UNION（UNION语句中的第二个或后续查询）、SUBQUERY（首先在子查询中SELECT）等表输出结果集。表输出结果集的表分区匹配的分区类型表示表的连接类型，连接类型性能从好到差为(system--->const----->eq_ref------>ref------->ref_or_null---->index_merge--->index_subquery----->range----->index------>all)possible_keys表示查询时可能用到的索引键表示实际索引key_len索引字段rows的长度过滤的扫描行数表条件过滤的行的百分比额外解释和执行说明4.3.1环境PrepareCREATETABLE`t_role`(`id`varchar(32)NOTNULL,`role_name`varchar(255)DEFAULTNULL,`role_code`varchar(255)DEFAULTNULL,`description`varchar(255)DEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`unique_role_name`(`role_name`))ENGINE=InnoDBDEFAULTCHARSET=utf8;CREATETABLE`t_user`(`id`varchar(32)NOTNULL,`username`varchar(45)NOTNULL,`password`varchar(96)NOTNULL,`name`varchar(45)NOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;CREATETABLE`user_role`(`id`int(11)NOTNULLauto_increment,`user_id`varchar(32)DEFAULTNULL,`role_id`varchar(32)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_role_user`(`role_id`,`user_id`),)ENGINE=InnoDBDEFAULTCHARSET=utf8;insertinto`t_user`(`id`,`username`,`password`,`name`)values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');insertinto`t_user`(`id`,`username`,`password`,`name`)values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');insertinto`t_user`(`id`,`username`,`password`,`name`)values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');插入`t_user`(`id`,`username`,`password`,`name`)values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');插入`t_user`(`id`,`username`,`password`,`name`)values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','Student2');insertinto`t_user`(`id`,`username`,`password`,`name`)values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师1');插入`t_role`(`id`,`role_name`,`role_code`,`description`)VALUES('5','student','student','student');INSERTINTO`t_role`(`id`,`role_name`,`role_code`,`description`)VALUES('7','teacher','teacher','teacher');INSERTINTO`t_role`(`id`,`role_name`,`role_code`,`description`)VALUES('8','教学经理','teachmanager','教学经理');INSERTINTO`t_role`(`id`,`role_name`,`role_code`,`description`)VALUES('9','admin','admin','admin');INSERTINTO`t_role`(`id`,`role_name`,`role_code`,`description`)VALUES('10','superadministrator','super','superadministrator');INSERTINTOuser_role(id,user_id,role_id)VALUES(NULL,'1','5'),(NULL,'1','7'),(NULL,'2','8'),(NULL,'3','9'),(NULL,'4','8'),(NULL,'5','10');4.3.2explain中的idid字段为select查询的序号，是一组数字，表示select子句所在的序列id被执行或在查询中执行操作表。分三种情况类型：1）id相同表示加载表的顺序是从上到下。说明select*fromt_roler,t_useru,user_roleurwherer.id=ur.role_idandu.id=ur.user_id;2）id值越大，优先级越高，越早执行。EXPLAINSELECT*FROMt_roleWHEREid=(SELECTrole_idFROMuser_roleWHEREuser_id=(SELECTidFROMt_userWHEREusername='stu1'))3)id可以相同也可以不同，同时存在。id相同的可以看作一个组，从上到下依次执行；在所有组中，id的值越大，优先级越高，越早执行。EXPLAINSELECT*FROMt_roler,(SELECT*FROMuser_roleurWHEREur.`user_id`='2')aWHEREr.id=(selectrole.idfromt_user,user_rolerolewhererole.id=10);4.3.3explain的select_type表示SELECT的类型，常用值见下表：EXPLAINSELECT*FROMt_roleWHEREid=(SELECTrole_idFROMuser_roleWHEREuser_id=(SELECTidFROMt_userWHEREusername='stu1'));select_type含义SIMPLE是一个简单的选择查询，如果查询不包含子查询或者如果任何复杂的子查询包含在UNIONPRIMARY查询中，最外层的查询被标记有这个标志SUBQUERY包含在SELECT或WHERE列表DERIVEDFROM列表中包含被标记为DERIVED（派生）的子查询，MYSQL会递归执行这些子查询，并将结果放入临时表UNION中，如果第二个SELECT出现在UNION之后，则被标记为UNION；如果UNION包含在FROM子句中，外层SELECT会被标记为：DERIVEDUNIONRESULTSELECT4.3.4explain从UNION表中获取结果EXPLAINSELECT*FROMt_roleWHEREid=(SELECTrole_idFROMuser_roleWHEREuser_id=(SELECTidFROMt_userWHEREusername='stu1'));4.3.5解释类型EXPLAINSELECT*FROMt_roleWHEREid=(SELECTrole_idFROMuser_roleWHEREuser_id=(SELECTidFROMt_userWHEREusername='stu1'));type表示访问类型，这是一个比较重要的指标，可能的取值为：type表示NULLMySQL不访问任何表、索引，直接返回结果。系统表只有一行记录（等于系统表），是const类型的特例。一般不会有const表示索引一次就可以找到。const用于比较主键或唯一索引，因为它只匹配一行数据，所以速度非常快。如果将主键放在where列表中，MySQL可以将查询转换为常量。const会将“主键”或“唯一”索引的所有部分与常量值进行比较。eq_ref与ref类似，不同的是它使用了唯一索引，使用主键的关联查询只返回一条记录。常见于主键或唯一索引扫描ref非唯一索引扫描，返回与单个值匹配的所有行。它本质上也是一个索引访问，返回与单个值匹配的所有行。range只检索给定返回的行，使用索引来选择行。where之后出现between,<,>,in等操作。index和ALL的区别在于索引类型只是遍历索引树，通常比ALL快，ALL遍历数据文件。all会遍历整个表寻找匹配的行，结果值从最好到最差依次为：NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALLsystem>const>eq_ref>ref>range>index>ALL一般来说，我们需要保证查询至少达到范围级别，最好是ref。4.3.6keypossible_keysofexplain：显示可能应用于该表的一个或多个索引。key:实际使用的索引，如果为NULL，则不使用该索引。key_len：指示索引中使用的字节数。len=3*n+2(n为索引字段的长度)EXPLAINselect*fromt_rolewhererole_name='superadministrator';选择255*3+2；--role_nameVARCHAR(255)4.3.7解释行扫描的行数。4.3.8extraofexplain该栏显示其他额外的执行计划信息。EXPLAINselectu.usernamefromt_useruorderbyu.usernamedesc;extrameaningusingfilesort表示mysql会使用外部索引对数据进行排序，而不是按照表中的索引顺序读取，这叫做“文件排序”，效率低。Usingtemporary使用临时表保存中间结果，MySQL在对查询结果进行排序时使用临时表。常见于orderby和groupby；lowefficiencyusingindex是指对应的select操作使用覆盖索引，避免访问表的数据行，效率好。本文由传智教育博学谷-狂野建筑师教研团队发布，转载请注明出处！如果本文对您有帮助，请关注并点赞；有什么建议也可以留言或私信。您的支持是我坚持创作的动力
