【转】SQL 优化有哪些技巧？

转：
https://mp.weixin.qq.com/s/ChwnzSCwc8HU4QQLz7VNPQ

1、创建索引

执行没有索引的 SQL 语句，肯定要走全表扫描，慢是肯定的。

这种查询毫无疑问是一个慢 SQL 查询。

是不是要收集所有的 where 查询条件，然后针对所有的组合都创建索引呢？

答案肯定是否定的。

MySQL 为了提升数据查询速率，采用 B+ 树结构，通过空间换时间的设计思想。另外每次对表数据做更新操作时，都要调整对应的索引树，执行效率肯定会受影响。

阿里巴巴的开发者手册中，建议单表的索引数量控制在 5 个以内，组合索引字段数不允许超过 5 个。

其他建议：

• 禁止给表中的每一列都建立单独的索引
• 每个 InnoDB 表必须有个主键
• 要注意组合索引的字段顺序
• 优先考虑覆盖索引
• 避免使用外键约束

2、避免索引失效

不要以为有了索引，就万事大吉。

殊不知，索引失效也是慢查询的主要原因之一。

常见的索引失效的场景有哪些？

• 以 % 开头的 LIKE 查询
• 创建了组合索引，但查询条件不满足“最左匹配原则”。如：创建索引 idx_type_status_uid（type, status, uid），但是使用 status 和 uid 作为查询条件
• 查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到
• 在索引列上的操作，函数 upper() 等、or、!= （<>）、not in 等

3、锁粒度

MySQL 的存储引擎分为两大类：MyISAM 和 InnoDB。

MyISAM 支持表锁；InnoDB 支持行锁和表锁。

更新操作时，为了保证表数据的准确性，通常会加锁，为了提高系统的高并发能力，我们通常建议采用行锁，减少锁冲突和锁等待的时间。所以存储引擎通常会选择 InnoDB。

有哪些场景会导致行锁可能会升级为表锁呢？

• 如果一个表批量更新，大量使用行锁，可能会导致其他事务长时间的等待，严重影响事务的执行效率。此时，MySQL 会将行锁升级为表锁
• 行锁是针对索引加的锁，如果条件索引失效，那么行锁也会升级为表锁

注意，行锁将锁的粒度缩小了，进而提高了系统的并发能力。但是可能会产生死锁，需要特别关注。

4、分页查询优化

如果要开发一个列表展示页面并支持翻页，我们通常会这样写 SQL。

select * from 表 limit #{start}, #{pageSize};

随着翻页的深度加大， start 值会越来越大，比如 limit 10000, 10 看似只返回了 10 条数据，但是数据库引擎需要查询 10010 条记录，然后将前面的 10000 条丢弃，最终只返回最后的 10 条记录，性能可想而知。

针对这个问题，我们通常有另一种解决方案：

• 先定位到上一次分页的最大 id，然后对 id 做条件索引查询。由于数据库的索引采用 B+ 树结构，这样可以一步到位。但是这种翻页方式只支持上一页、下一页，不支持跨越式翻页

select * from 表 where id > #{id} limit #{pageSize};

上图是淘宝的商品搜索列表，为了让用户的体验更好，采用的也是直梯式翻页。

为了避免翻页过深，影响性能，产品交互上还做了一些取舍，对总页数做了限制，最多支持 100 页。

• 方案二：采用子查询

select * from 表 where id > (select id from 表 order by id limit 10000 1) limit 20;

将原来的单 SQL 查询拆成两步：

1. 首先，查询出一页数据中的最小 id
2. 然后，通过 B+ 树，精确定位到最小 id 的索引树节点位置，通过偏移量读取后面的 20 条数据

5、避免 select *

反面案例：

select * from 表 where buyer_id = #{buyer_id};

我们知道，MySQL 在创建表后，具体的行数据存储在主键索引（聚簇索引）的叶子节点。

二级索引属于非聚簇索引，其叶子节点存储的是主键值。

select * 的查询过程：

1. 先在 buyer_id 的二级索引 B+ 树上，查出对应的主键 id 列表
2. 然后进行回表操作，在主键索引中查询 id 对应的行数据

所以，我们需要罗列清楚必须的查询字段，且字段尽量在覆盖索引中，从而减少回表操作。

6、EXPLAIN 分析 SQL 执行计划

// 创建表
CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `income` bigint(20) NOT NULL COMMENT '收入',
  `expend` bigint(20) NOT NULL COMMENT '支出',
  PRIMARY KEY (`id`),
  KEY `idx_income` (`income`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';

CREATE TABLE `biz_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `user_id` bigint(20) NOT NULL COMMENT '用户id',
  `money` bigint(20) NOT NULL COMMENT '金额',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='订单表';

// 插入记录
insert into user values(10,100,100);
insert into user values(20,200,200);
insert into user values(30,300,300);
insert into user values(40,400,400);

insert into biz_order values(1,10,30);
insert into biz_order values(2,10,40);
insert into biz_order values(3,10,50);
insert into biz_order values(4,20,10);

比如下面的语句，我们检查是否使用了索引，可以通过 explain 分析相应的执行计划。

explain select * from user where id < 20;

接下来，我们来逐一来说明每个字段的含义。

• id：每一次 select 查询都会生成一个 id，值越大，优先级越高，会被优先执行
• select_type：查询类型，SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等
• table：查询哪张表
• partitions：分区，如果对应的表存在分区表，那么这里就会显示具体的分区信息
• type：执行方式，是 SQL 优化中一个很重要的指标，其结果值从好到差依次是 system > const > eq_ref > ref > range > index > ALL
  • system/const：表中只有一行数据匹配，此时根据索引，查询一次就能找到对应的数据
  • eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件
  • ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描
  • range：索引范围扫描，比如 <、>、between 等操作
  • index：索引全表扫描，此时遍历整个索引树
  • ALL：表示全表扫描，需要遍历全表来找到对应的行
• possible_keys：可能用到的索引
• key：实际用到的索引
• key_len：索引长度
• ref：关联 id 等信息
• rows：查找到记录所扫描的行数，SQL 优化的重要指标，扫描的行数越少，性能越高
• filtered：查找到所需记录占总扫描记录数的比例
• Extra：额外的信息

explain select * from user u, biz_order b where u.id = b.user_id and u.id < 20;

7、Show Profile 分析 SQL 执行性能

Show Profile 与 EXPLAIN 的区别：前者主要是在外围分析；而后者则是深入到 MySQL 内核，从执行线程的状态和时间来分析。

MySQL 在 5.0.37 版本之后才支持 Show Profile，select @@have_profiling 返回 YES 则表示功能已开启。

mysql> show profiles;
Empty set, 1 warning (0.00 sec)

显示为空，说明 profiles 功能是关闭的。

可以通过下面命令开启。

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

获取 Query_ID 之后，通过 show profile for query ID，查看 SQL 语句在执行过程中线程的每个状态所消耗的时间。