如何写出高性能SQL语句

锁

共享锁(Share Lock)

共享锁又称读锁，简称S锁；当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。

共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复读的问题。

排他锁（eXclusive Lock）

排他锁又称写锁，简称X锁；当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。

排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读取。避免了出现脏数据和脏读的问题。

慢 SQL 语句的几种常见诱因

1. 无索引、索引失效导致慢查询
2. 锁等待
3. 不恰当的 SQL 语句

使用不恰当的 SQL 语句也是慢 SQL 最常见的诱因之一。例如，习惯使用
SQL 语句，在大数据表中使用 分页查询，以及对非索引字段进行排序等等。

优化 SQL 语句的步骤

1. 通过 EXPLAIN 分析 SQL 执行计划

• select_type：表示 SELECT 查询类型，常见的有 SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。
• type：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。
• possible_keys：可能使用到的索引。
• key：实际使用到的索引。
• key_len：当前使用的索引的长度。ref：关联 id 等信息。
• rows：查找到记录所扫描的行数。
• filtered：查找到所需记录占总扫描记录数的比例。
• Extra：额外的信息。

2. 通过 Show Profile 分析 SQL 执行性能

值得注意的是，MySQL 是在 5.0.37 版本之后才支持 Show Profile 功能的，如果你不太确定的话，可以通过 select @@have_profiling 查询是否支持该功能

Show Profiles 只显示最近发给服务器的 SQL 语句，默认情况下是记录最近已执行的 15 条记录，我们可以重新设置 profiling_history_size 增大该存储记录，最大值为 100。

获取到 Query_ID 之后，我们再通过 Show Profile for Query ID 语句，就能够查看到对应 Query_ID 的 SQL 语句在执行过程中线程的每个状态所消耗的时间了

常用的 SQL 优化

分页优化

limit是mysql的语法

select * from table limit [m],n;

其中，m—— [m]为可选，如果填写表示skip步长，即跳过m条。

　　n——显示条数。指从第m+1条记录开始，取n条记录。

如：

select * from stu limit 2,4;

即：取stu表中第3至第6条，共4条记录。

select * from stu limit 5;

即：取stu表中前5条，共5条记录。

利用子查询优化分页查询

取第10000条后面的一条数据id：

select id from table order by id limit 10000,1

从第10001条数据开始，数10条记录返回

select * from a where id > (select id from table order by id limit 10000,1) limit 20

刚说到为保证主键id连续，要设定记录只能做逻辑删除，但是，假如说如果真的要物理删除，那解决方法的话就只能先取出前offset条数据的id，再去做偏移取数据。

通过 EXPLAIN 分析可知：子查询遍历索引的范围跟上一个查询差不多，而主查询扫描了更多的行数，但执行时间却减少了，只有 0.004s。这就是因为返回行数只有 20 行了，执行效率得到了明显的提升。

优化 SELECT COUNT(*)

近似值、增加汇总统计

优化 SELECT *

MySQL 常用的存储引擎有 MyISAM 和 InnoDB，其中 InnoDB 在默认创建主键时会创建主键索引，而主键索引属于聚簇索引，即在存储数据时，索引是基于 B + 树构成的，具体的行数据则存储在叶子节点。

而 MyISAM 默认创建的主键索引、二级索引以及 InnoDB 的二级索引都属于非聚簇索引，即在存储数据时，索引是基于 B + 树构成的，而叶子节点存储的是主键值。

假设我们的订单表是基于 InnoDB 存储引擎创建的，且存在 order_no、status 两列组成的组合索引。此时，我们需要根据订单号查询一张订单表的 status，如果我们使用 select * from order where order_no=’xxx’来查询，则先会查询组合索引，通过组合索引获取到主键 ID，再通过主键 ID 去主键索引中获取对应行所有列的值。

如果我们使用 select order_no, status from order where order_no=’xxx’来查询，则只会查询组合索引，通过组合索引获取到对应的 order_no 和 status 的值。如果你对这些索引还不够熟悉，请重点关注之后的第 34 讲，那一讲会详述数据库索引的相关内容。

总结

在开发中，我们要尽量写出高性能的 SQL 语句，但也无法避免一些慢 SQL 语句的出现，或因为疏漏，或因为实际生产环境与开发环境有所区别，这些都是诱因。面对这种情况，我们可以打开慢 SQL 配置项，记录下都有哪些 SQL 超过了预期的最大执行时间。首先，我们可以通过以下命令行查询是否开启了记录慢 SQL 的功能，以及最大的执行时间是多少：

Show variables like 'slow_query%';
Show variables like 'long_query_time';

如果没有开启，我们可以通过以下设置来开启：

set global slow_query_log='ON'; //开启慢SQL日志
set global slow_query_log_file='/var/lib/mysql/test-slow.log';//记录日志地址
set global long_query_time=1;//最大执行时间

除此之外，很多数据库连接池中间件也有分析慢 SQL 的功能。总之，我们要在编程中避免低性能的 SQL 操作出现，除了要具备一些常用的 SQL 优化技巧之外，还要充分利用一些 SQL 工具，实现 SQL 性能分析与监控。

思考题

假设有一张订单表 order，主要包含了主键订单编码 order_no、订单状态 status、提交时间 create_time 等列，并且创建了 status 列索引和 create_time 列索引。此时通过创建时间降序获取状态为 1 的订单编码，以下是具体实现代码：

select order_no from order where status =1 order by create_time desc

status和create_time单独建索引，在查询时只会遍历status索引对数据进行过滤，不会用到create_time列索引，将符合条件的数据返回到server层，在server对数据通过快排算法进行排序，Extra列会出现file sort；应该利用索引的有序性，在status和create_time列建立联合索引，这样根据status过滤后的数据就是按照create_time排好序的，避免在server层排序。

打赏