sql语句优化的一般步骤详解
前言
本文主要给大家分享了关于sql语句优化的一般步骤，分享出来供大家参考学习，下面话不多说了，来一起看看详细的介绍吧。
一、通过showstatus命令了解各种sql的执行频率
mysql客户端连接成功后，通过show[session|global]status命令可以提供服务器状态信息，也可以在操作系统上使用mysqladminextend-status命令获取这些消息。
showstatus命令中间可以加入选项session（默认）或global：
- session（当前连接）
- global（自数据上次启动至今）
#Com_xxx表示每个xxx语句执行的次数。 mysql>showstatuslike'Com_%';
我们通常比较关心的是以下几个统计参数：
- Com_select:执行select操作的次数，一次查询只累加1。
- Com_insert:执行insert操作的次数，对于批量插入的insert操作，只累加一次。
- Com_update:执行update操作的次数。
- Com_delete:执行delete操作的次数。
上面这些参数对于所有存储引擎的表操作都会进行累计。下面这几个参数只是针对innodb的，累加的算法也略有不同：
- Innodb_rows_read:select查询返回的行数。
- Innodb_rows_inserted:执行insert操作插入的行数。
- Innodb_rows_updated:执行update操作更新的行数。
- Innodb_rows_deleted:执行delete操作删除的行数。
通过以上几个参数，可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的sql大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。
对于事务型的应用，通过Com_commit和Com_rollback可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。
此外，以下几个参数便于用户了解数据库的基本情况：
- Connections：试图连接mysql服务器的次数。
- Uptime：服务器工作时间。
- Slow_queries:慢查询次数。
二、定义执行效率较低的sql语句
1.通过慢查询日志定位那些执行效率较低的sql语句，用--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的sql语句的日志文件。
2.慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候慢查询日志并不能定位问题，可以使用showprocesslist命令查看当前mysql在进行的线程，包括线程的状态、是否锁表等，可以实时的查看sql的执行情况，同时对一些锁表操作进行优化。
三、通过explain分析低效sql的执行计划
测试数据库地址：https://downloads.mysql.com/docs/sakila-db.zip（本地下载）
统计某个email为租赁电影拷贝所支付的总金额，需要关联客户表customer和付款表payment，并且对付款金额amount字段做求和（sum）操作，相应的执行计划如下：
mysql>explainselectsum(amount)fromcustomera,paymentbwherea.customer_id=b.customer_idanda.email='JANE.BENNETT@sakilacustomer.org'\G ***************************1.row*************************** id:1 select_type:SIMPLE table:a partitions:NULL type:ALL possible_keys:PRIMARY key:NULL key_len:NULL ref:NULL rows:599 filtered:10.00 Extra:Usingwhere ***************************2.row*************************** id:1 select_type:SIMPLE table:b partitions:NULL type:ref possible_keys:idx_fk_customer_id key:idx_fk_customer_id key_len:2 ref:sakila.a.customer_id rows:26 filtered:100.00 Extra:NULL 2rowsinset,1warning(0.00sec)
- select_type:表示select类型，常见的取值有：
simple：简单表，及不使用表连接或者子查询
primary：主查询，即外层的查询
union：union中的第二个或后面的查询语句
subquery：子查询中的第一个select - table：输出结果集的表
- type：表示mysql在表中找到所需行的方式，或者叫访问类型，常见类型性能由差到最好依次是：all、index、range、ref、eq_ref、const，system、null：
1.type=ALL，全表扫描，mysql遍历全表来找到匹配的行：
mysql>explainselect*fromfilmwhererating>9\G ***************************1.row*************************** id:1 select_type:SIMPLE table:film partitions:NULL type:ALL possible_keys:NULL key:NULL key_len:NULL ref:NULL rows:1000 filtered:33.33 Extra:Usingwhere 1rowinset,1warning(0.01sec)
2.type=index,索引全扫描，mysql遍历整个索引来查询匹配的行
mysql>explainselecttitleformfilm\G ***************************1.row*************************** id:1 select_type:SIMPLE table:film partitions:NULL type:index possible_keys:NULL key:idx_title key_len:767 ref:NULL rows:1000 filtered:100.00 Extra:Usingindex 1rowinset,1warning(0.00sec)
3.type=range,索引范围扫描，常见于<、<=、>、>=、between等操作：
mysql>explainselect*frompaymentwherecustomer_id>=300andcustomer_id<=350\G ***************************1.row*************************** id:1 select_type:SIMPLE table:payment partitions:NULL type:range possible_keys:idx_fk_customer_id key:idx_fk_customer_id key_len:2 ref:NULL rows:1350 filtered:100.00 Extra:Usingindexcondition 1rowinset,1warning(0.07sec)
4.type=ref,使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行，例如：
mysql>explainselect*frompaymentwherecustomer_id=350\G ***************************1.row*************************** id:1 select_type:SIMPLE table:payment partitions:NULL type:ref possible_keys:idx_fk_customer_id key:idx_fk_customer_id key_len:2 ref:const rows:23 filtered:100.00 Extra:NULL 1rowinset,1warning(0.01sec)
索引idx_fk_customer_id是非唯一索引，查询条件为等值查询条件customer_id=350,所以扫描索引的类型为ref。ref还经常出现在join操作中：
mysql>explainselectb.*,a.*frompaymenta,customerbwherea.customer_id=b.customer_id\G ***************************1.row*************************** id:1 select_type:SIMPLE table:b partitions:NULL type:ALL possible_keys:PRIMARY key:NULL key_len:NULL ref:NULL rows:599 filtered:100.00 Extra:NULL ***************************2.row*************************** id:1 select_type:SIMPLE table:a partitions:NULL type:ref possible_keys:idx_fk_customer_id key:idx_fk_customer_id key_len:2 ref:sakila.b.customer_id rows:26 filtered:100.00 Extra:NULL 2rowsinset,1warning(0.00sec)
5.type=eq_ref,类似ref，区别就在使用的索引时唯一索引，对于每个索引的键值，表中只要一条记录匹配；简单的说，就是多表连接中使用primarykey或者uniqueindex作为关联条件。
mysql>explainselect*fromfilma,film_textbwherea.film_id=b.film_id\G ***************************1.row*************************** id:1 select_type:SIMPLE table:b partitions:NULL type:ALL possible_keys:PRIMARY key:NULL key_len:NULL ref:NULL rows:1000 filtered:100.00 Extra:NULL ***************************2.row*************************** id:1 select_type:SIMPLE table:a partitions:NULL type:eq_ref possible_keys:PRIMARY key:PRIMARY key_len:2 ref:sakila.b.film_id rows:1 filtered:100.00 Extra:Usingwhere 2rowsinset,1warning(0.03sec)
6.type=const/system,单表中最多有一个匹配行，查起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键primarykey或者唯一索引uniqueindex进行查询。
mysql>createtabletest_const( ->test_idint, ->test_contextvarchar(10), ->primarykey(`test_id`), ->); insertintotest_constvalues(1,'hello'); explainselect*from(select*fromtest_constwheretest_id=1)a\G ***************************1.row*************************** id:1 select_type:SIMPLE table:test_const partitions:NULL type:const possible_keys:PRIMARY key:PRIMARY key_len:4 ref:const rows:1 filtered:100.00 Extra:NULL 1rowinset,1warning(0.00sec)
7.type=null,mysql不用访问表或者索引，直接就能够得到结果：
mysql>explainselect1fromdualwhere1\G ***************************1.row*************************** id:1 select_type:SIMPLE table:NULL partitions:NULL type:NULL possible_keys:NULL key:NULL key_len:NULL ref:NULL rows:NULL filtered:NULL Extra:Notablesused 1rowinset,1warning(0.00sec)
类型type还有其他值，如ref_or_null(与ref类似，区别在于条件中包含对null的查询)、index_merge(索引合并优化)、unique_subquery(in的后面是一个查询主键字段的子查询)、index_subquery(与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询)等。
- possible_keys:表示查询时可能使用的索引。
- key：表示实际使用索引
- key-len:使用到索引字段的长度。
- rows：扫描行的数量
- extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。
showwarnings命令
执行explain后再执行showwarnings，可以看到sql真正被执行之前优化器做了哪些sql改写：
MySQL[sakila]>explainselectsum(amount)fromcustomera,paymentbwhere1=1anda.customer_id=b.customer_idandemail='JANE.BENNETT@sakilacustomer.org'\G ***************************1.row*************************** id:1 select_type:SIMPLE table:a partitions:NULL type:ALL possible_keys:PRIMARY key:NULL key_len:NULL ref:NULL rows:599 filtered:10.00 Extra:Usingwhere ***************************2.row*************************** id:1 select_type:SIMPLE table:b partitions:NULL type:ref possible_keys:idx_fk_customer_id key:idx_fk_customer_id key_len:2 ref:sakila.a.customer_id rows:26 filtered:100.00 Extra:NULL 2rowsinset,1warning(0.00sec) MySQL[sakila]>showwarnings; +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Level|Code|Message| +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Note|1003|/*select#1*/selectsum(`sakila`.`b`.`amount`)AS`sum(amount)`from`sakila`.`customer``a`join`sakila`.`payment``b`where((`sakila`.`b`.`customer_id`=`sakila`.`a`.`customer_id`)and(`sakila`.`a`.`email`='JANE.BENNETT@sakilacustomer.org'))| +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1rowinset(0.00sec)
从warning的message字段中能够看到优化器自动去除了1=1恒成立的条件，也就是说优化器在改写sql时会自动去掉恒成立的条件。
explain命令也有对分区的支持.
MySQL[sakila]>CREATETABLE`customer_part`( ->`customer_id`smallint(5)unsignedNOTNULLAUTO_INCREMENT, ->`store_id`tinyint(3)unsignedNOTNULL, ->`first_name`varchar(45)NOTNULL, ->`last_name`varchar(45)NOTNULL, ->`email`varchar(50)DEFAULTNULL, ->`address_id`smallint(5)unsignedNOTNULL, ->`active`tinyint(1)NOTNULLDEFAULT'1', ->`create_date`datetimeNOTNULL, ->`last_update`timestampNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP, ->PRIMARYKEY(`customer_id`) -> ->)partitionbyhash(customer_id)partitions8; QueryOK,0rowsaffected(0.06sec) MySQL[sakila]>insertintocustomer_partselect*fromcustomer; QueryOK,599rowsaffected(0.06sec) Records:599Duplicates:0Warnings:0 MySQL[sakila]>explainselect*fromcustomer_partwherecustomer_id=130\G ***************************1.row*************************** id:1 select_type:SIMPLE table:customer_part partitions:p2 type:const possible_keys:PRIMARY key:PRIMARY key_len:2 ref:const rows:1 filtered:100.00 Extra:NULL 1rowinset,1warnings(0.00sec)
可以看到sql访问的分区是p2。
四、通过performance_schema分析sql性能
旧版本的mysql可以使用profiles分析sql性能，我用的是5.7.18的版本，已经不允许使用profiles了，推荐用
performance_schema分析sql。
五、通过trace分析优化器如何选择执行计划。
mysql5.6提供了对sql的跟踪trace，可以进一步了解为什么优化器选择A执行计划而不是B执行计划，帮助我们更好的理解优化器的行为。
使用方式：首先打开trace，设置格式为json，设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。
MySQL[sakila]>setoptimizer_trace="enabled=on",end_markers_in_json=on; QueryOK,0rowsaffected(0.00sec) MySQL[sakila]>setoptimizer_trace_max_mem_size=1000000; QueryOK,0rowsaffected(0.00sec)
接下来执行想做trace的sql语句，例如像了解租赁表rental中库存编号inventory_id为4466的电影拷贝在出租日期rental_date为2005-05-254:00:00~5:00:00之间出租的记录：
mysql>selectrental_idfromrentalwhere1=1andrental_date>='2005-05-2504:00:00'andrental_date<='2005-05-2505:00:00'andinventory_id=4466;
+-----------+
|rental_id|
+-----------+
|39|
+-----------+
1rowinset(0.06sec)
MySQL[sakila]>select*frominformation_schema.optimizer_trace\G
***************************1.row***************************
QUERY:select*frominfomation_schema.optimizer_trace
TRACE:{
"steps":[
]/*steps*/
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE:0
INSUFFICIENT_PRIVILEGES:0
1rowinset(0.00sec)
六、确定问题并采取相应的优化措施
经过以上步骤，基本就可以确认问题出现的原因。此时可以根据情况采取相应的措施，进行优化以提高执行的效率。
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。