前言

本文主要给大家分享了关于sql语句优化的一般步骤，分享出来供大家参考学习，下面话不多说了，来一起看看详细的介绍吧。

一、通过 show status 命令了解各种 sql 的执行频率

mysql 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息，也可以在操作系统上使用 mysqladmin extend-status 命令获取这些消息。

show status 命令中间可以加入选项 session（默认） 或 global：

• session （当前连接）
• global （自数据上次启动至今）

# Com_xxx 表示每个 xxx 语句执行的次数。
mysql> show status like '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. 慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 mysql 在进行的线程，包括线程的状态、是否锁表等，可以实时的查看 sql 的执行情况，同时对一些锁表操作进行优化。

三、通过 explain 分析低效 sql 的执行计划

测试数据库地址：https://downloads.mysql.com/docs/sakila-db.zip（本地下载）

统计某个 email 为租赁电影拷贝所支付的总金额，需要关联客户表 customer 和 付款表 payment ， 并且对付款金额 amount 字段做求和（sum） 操作，相应的执行计划如下：

mysql> explain select sum(amount) from customer a , payment b where a.customer_id= b.customer_id and a.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: Using where
*************************** 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
2 rows in set, 1 warning (0.00 sec)

• 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> explain select * from film where rating > 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: Using where
1 row in set, 1 warning (0.01 sec)

2.type=index, 索引全扫描，mysql 遍历整个索引来查询匹配的行

mysql> explain select title form film\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: Using index
1 row in set, 1 warning (0.00 sec)

3.type=range,索引范围扫描，常见于<、<=、>、>=、between等操作：

mysql> explain select * from payment where customer_id >= 300 and customer_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: Using index condition
1 row in set, 1 warning (0.07 sec)

4.type=ref, 使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行，例如：

mysql> explain select * from payment where customer_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
1 row in set, 1 warning (0.01 sec)

索引 idx_fk_customer_id 是非唯一索引，查询条件为等值查询条件 customer_id = 350, 所以扫描索引的类型为 ref。ref 还经常出现在 join 操作中：

mysql> explain select b.*, a.* from payment a,customer b where a.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
2 rows in set, 1 warning (0.00 sec)

5.type=eq_ref,类似 ref，区别就在使用的索引时唯一索引，对于每个索引的键值，表中只要一条记录匹配；简单的说，就是多表连接中使用 primary key 或者 unique index 作为关联条件。

mysql> explain select * from film a , film_text b where a.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: Using where
2 rows in set, 1 warning (0.03 sec)

6.type=const/system,单表中最多有一个匹配行，查起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键 primary key 或者唯一索引 unique index 进行查询。

mysql> create table test_const (
 ->  test_id int,
 ->  test_context varchar(10),
 ->  primary key (`test_id`),
 -> );
 
insert into test_const values(1,'hello');

explain select * from ( select * from test_const where test_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
 1 row in set, 1 warning (0.00 sec)

7.type=null, mysql 不用访问表或者索引，直接就能够得到结果：

mysql> explain select 1 from dual where 1 \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: No tables used
1 row in set, 1 warning (0.00 sec)

　　类型 type 还有其他值，如 ref_or_null (与 ref 类似，区别在于条件中包含对 null 的查询)、index_merge(索引合并优化)、unique_subquery (in 的后面是一个查询主键字段的子查询)、index_subquery(与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询)等。

• possible_keys : 表示查询时可能使用的索引。
• key ：表示实际使用索引
• key-len : 使用到索引字段的长度。
• rows ： 扫描行的数量
• extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

show warnings 命令

执行explain 后再执行 show warnings，可以看到sql 真正被执行之前优化器做了哪些 sql 改写：

MySQL [sakila]> explain select sum(amount) from customer a , payment b where 1=1 and a.customer_id = b.customer_id and 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: Using where
*************************** 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
2 rows in set, 1 warning (0.00 sec)

MySQL [sakila]> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                               |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select sum(`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')) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

从 warning 的 message 字段中能够看到优化器自动去除了 1=1 恒成立的条件，也就是说优化器在改写 sql 时会自动去掉恒成立的条件。

explain 命令也有对分区的支持.

MySQL [sakila]> CREATE TABLE `customer_part` (
 -> `customer_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 -> `store_id` tinyint(3) unsigned NOT NULL,
 -> `first_name` varchar(45) NOT NULL,
 -> `last_name` varchar(45) NOT NULL,
 -> `email` varchar(50) DEFAULT NULL,
 -> `address_id` smallint(5) unsigned NOT NULL,
 -> `active` tinyint(1) NOT NULL DEFAULT '1',
 -> `create_date` datetime NOT NULL,
 -> `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 -> PRIMARY KEY (`customer_id`)
 -> 
 -> ) partition by hash (customer_id) partitions 8;
Query OK, 0 rows affected (0.06 sec)

MySQL [sakila]> insert into customer_part select * from customer;
Query OK, 599 rows affected (0.06 sec)
Records: 599 Duplicates: 0 Warnings: 0

MySQL [sakila]> explain select * from customer_part where customer_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
1 row in set, 1 warnings (0.00 sec)

可以看到 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]> set optimizer_trace="enabled=on",end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)

MySQL [sakila]> set optimizer_trace_max_mem_size=1000000;
Query OK, 0 rows affected (0.00 sec)

接下来执行想做 trace 的 sql 语句，例如像了解租赁表 rental 中库存编号 inventory_id 为 4466 的电影拷贝在出租日期 rental_date 为 2005-05-25 4:00:00 ~ 5:00:00 之间出租的记录：

mysql> select rental_id from rental where 1=1 and rental_date >= '2005-05-25 04:00:00' and rental_date <= '2005-05-25 05:00:00' and inventory_id=4466;
+-----------+
| rental_id |
+-----------+
| 39 |
+-----------+
1 row in set (0.06 sec)

MySQL [sakila]> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
    QUERY: select * from infomation_schema.optimizer_trace
    TRACE: {
 "steps": [
 ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
  INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

六、 确定问题并采取相应的优化措施

经过以上步骤，基本就可以确认问题出现的原因。此时可以根据情况采取相应的措施，进行优化以提高执行的效率。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对的支持。

# sql语句优化方法  # sql语句优化综述  # sql优化  # 面试题  # 浅谈MySQL中优化sql语句查询常用的30种方法  # sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句  # SQL语句优化方法30例(推荐)  # 如何优化SQL语句的心得浅谈  # SQL 语句优化方法30例  # 如何优化SQL语句（全）  # 通过分析SQL语句的执行计划优化SQL  # 常用SQL语句优化技巧总结【经典】  # SQL语句优化提高数据库性能  # SQL语句性能优化（续）  # 几个  # 的是  # 行数  # 遍历  # 可以看到  # 可以使用  # 较低  # 执行情况  # 主键  # 是一个  # 也有  # 就在  # 本地下载  # 第一个  # 最多  # 的说  # 说了  # 出现在  # 不多  # 执行时间 

相关栏目： 【 网站优化151355 】 【 网络推广146373 】 【 网络技术251813 】 【 AI营销90571 】

相关推荐： HTML5打空格有哪些误区_新手常犯的空格使用错误【技巧】  深圳网站制作培训,深圳哪些招聘网站比较好？  Laravel模型关联查询教程_Laravel Eloquent一对多关联写法  Laravel如何为API编写文档_Laravel API文档生成与维护方法  如何快速上传自定义模板至建站之星？  高性能网站服务器部署指南：稳定运行与安全配置优化方案  Android okhttputils现在进度显示实例代码  Laravel如何设置自定义的日志文件名_Laravel根据日期或用户ID生成动态日志【技巧】  zabbix利用python脚本发送报警邮件的方法  微信小程序 HTTPS报错整理常见问题及解决方案  Laravel怎么实现一对多关联查询_Laravel Eloquent模型关系定义与预加载【实战】  文字头像制作网站推荐软件,醒图能自动配文字吗？  Laravel storage目录权限问题_Laravel文件写入权限设置  如何快速生成凡客建站的专业级图册？  如何用AI帮你把自己的生活经历写成一个有趣的故事？  javascript中对象的定义、使用以及对象和原型链操作小结  php增删改查怎么学_零基础入门php数据库操作必知基础【教程】  Javascript中的事件循环是如何工作的_如何利用Javascript事件循环优化异步代码？  javascript日期怎么处理_如何格式化输出  网站制作软件免费下载安装,有哪些免费下载的软件网站？  BootStrap整体框架之基础布局组件  Win11怎么恢复误删照片_Win11数据恢复工具使用【推荐】  Laravel如何实现API速率限制？（Rate Limiting教程）  如何将凡科建站内容保存为本地文件？  Laravel怎么实现模型属性转换Casting_Laravel自动将JSON字段转为数组【技巧】  公司网站制作需要多少钱,找人做公司网站需要多少钱？  怎么制作一个起泡网,水泡粪全漏粪育肥舍冬季氨气超过25ppm，可以有哪些措施降低舍内氨气水平？  Laravel怎么创建自己的包(Package)_Laravel扩展包开发入门到发布  Laravel如何操作JSON类型的数据库字段？（Eloquent示例）  Laravel观察者模式如何使用_Laravel Model Observer配置  如何在建站宝盒中设置产品搜索功能？  如何快速配置高效服务器建站软件？  详解jQuery中的事件  香港服务器选型指南：免备案配置与高效建站方案解析  Win11怎么关闭透明效果_Windows11辅助功能视觉效果设置  Laravel如何清理系统缓存命令_Laravel清除路由配置及视图缓存的方法【总结】  如何快速搭建高效服务器建站系统？  深入理解Android中的xmlns:tools属性  HTML透明颜色代码在Angular里怎么设置_Angular透明颜色使用指南【详解】  Python正则表达式进阶教程_复杂匹配与分组替换解析  如何在七牛云存储上搭建网站并设置自定义域名？  bing浏览器学术搜索入口_bing学术文献检索地址  Python图片处理进阶教程_Pillow滤镜与图像增强  Laravel如何实现多表关联模型定义_Laravel多对多关系及中间表数据存取【方法】  图册素材网站设计制作软件,图册的导出方式有几种？  php在windows下怎么调试_phpwindows环境调试操作说明【操作】  开心动漫网站制作软件下载,十分开心动画为何停播？  javascript中数组（Array)对象和字符串（String)对象的常用方法总结  Laravel如何集成微信支付SDK_Laravel使用yansongda-pay实现扫码支付【实战】  实现点击下箭头变上箭头来回切换的两种方法【推荐】