SQL性能优化技巧

最新推荐文章于 2024-08-13 10:55:29 发布

区块链专家

最新推荐文章于 2024-08-13 10:55:29 发布

阅读量355

点赞数

分类专栏：开发文章标签：学习开发语言 sql

原文链接：http://baijiantest.co/

版权

开发专栏收录该内容

28 篇文章 1 订阅

订阅专栏

这里就给小伙伴们带来工作中常用的一些 SQL 性能优化技巧总结，包括常见优化十经验、order by 与 group by 优化、分页查询优化、join 关联查询优化、in 和 exsits 优化、count(*)查询优化。

一、常见优化十经验

其实这个十经验不一定准确， MySQL 执行查询语句时会进行成本分析，数据量和实际数据值会影响到 MySQL 的实际查询过程，所以要是小伙伴们根据这常见十经验写了后发现和小二文章中的不一样也不要奇怪。

所以...小二纠结了挺久要不要写这 SQL 优化常见优化十经验，毕竟这些很多博客已经提到过了，想了想，还是写出来吧。

-- 示例表
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('itwxe',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('weiwei', 23,'test',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('leilei',23,'dev',NOW());

-- 插入10w条测试数据
drop procedure if exists insert_employees; 
delimiter $$
create procedure insert_employees()        
begin
  declare i int;                    
  set i = 1;                          
  while(i <= 100000)do                 
    insert into employees(name, age, position) values(CONCAT('itwxe', i), rand() * 42 + 18, 'dev');  
    set i = i + 1;                       
  end while;
end$$
delimiter ;
call insert_employees();

1. 尽量全值匹配

explain select * from employees where name = 'itwxe';
explain select * from employees where name = 'itwxe' and age = 22;
explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';

记住这三个 key_len 的值，idx_name_age_position (name,age,position)由这三个字段组成，74代表使用了 name 列；78代表使用了 name,age 列；140代表使用了 name,age,position 列。

2. 最左前缀原则

在使用联合索引的时候要特别注意最左前缀原则，即查询从联合索引的最左前列开始并且不跳过索引中的列。

explain select * from employees where name = 'itwxe' and age = '18';
explain select * from employees where name = 'itwxe' and position = 'manager';
explain select * from employees where position = 'manager';

应该也是比较好理解的，不过需要注意的是和查询 SQL 书写的顺序无关，最左指的是联合索引创建时列的顺序。例如 where 中颠倒顺序还是会使用 idx_name_age_position (name,age,position) 中的三个列索引查询。

explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';
explain select * from employees where age = 22 and position = 'manager' and name = 'itwxe';
explain select * from employees where position = 'manager' and name = 'itwxe' and age = 22;

可以看到即使颠倒了顺序，三个的执行计划也是一毛一样的。

3. 不在索引列上做任何操作(计算、函数、(自动/手动)类型转换)，会导致索引失效而转向全表扫描

性能检测需要注意的是这里说的索引列任何操作(计算、函数、(自动/手动)类型转换)不做操作指的是 where 条件之后的，而不是查询结果字段里面的。

例如对 name 列进行 left 函数操作。

explain select * from employees where name = 'weiwei';
explain select * from employees where left(name,6) = 'weiwei';

4. 存储引擎不能使用索引中范围条件右边的列

explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';
explain select * from employees where name = 'itwxe' and age > 22 and position = 'manager';

可以看到第二条 SQL 使用了 name,age 列作为索引来查询，position 并没有使用。

5. 尽量使用覆盖索引，减少 `select *` 语句

覆盖索引前面的文章提到过，不赘述了。

6. MySQL 在使用不等于( != 或者 <> )，not in，not exists 的时候无法使用索引会导致全表扫描

<、>、<=、>= 这些，MySQL 内部优化器会根据检索比例、表大小等多个因素计算查询成本是否使用索引。

explain select * from employees where name != 'itwxe';

7. is null 和 is not null 一般情况下也无法使用索引

explain select * from employees where name is null;
explain select * from employees where name is not null;

8. like 以通配符开头('%itwxe...') MySQL 索引失效会变成全表扫描操作

explain select * from employees where name like 'wei%';
explain select * from employees where name like '%wei';

相信理解 B+tree 底层数据结构的小伙伴都很容易就知道为啥了，解决办法：

根据业务建立联合索引，使用覆盖索引查询。
不能使用覆盖索引的则借助 ES 等搜索引擎。

查询中可以简单的把 like KK% 理解为 = 常量，%KK和%KK% 理解为 范围查询。

这里引入一个索引下推的概念：

explain select * from employees where name like 'weiwei%' and age = 22 and position = 'manager';
explain select * from employees where name like 'itwxe%' and age = 22 and position = 'manager';

可以看到第一条SQL中 name = 'weiwei%' ，根据 B+tree 的结构可以知道其后的 age,position 列是无序的，应该无法使用 age,position 列过滤数据才对，但是最后的 key_len 确是140，也就意味着 MySQL 利用到了 name,age,postion 三个列来查询，这是因为 MySQL 在5.6版本做的优化，引入了索引下推。

索引下推可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。

即当 name = 'weiwei%' 过滤时，使用了索引下推优化，过滤 name 时同时还会在索引里过滤 age,position 两个列的条件，拿着过滤完剩下的索引对应的主键 id 再回表查整行数据。

那么很简单，第二条 SQL 没有使用索引的原因是因为 MySQL 计算使用索引下推过滤出数据后，查询二级索引+回表的查询成本大于全表扫描，所以 MySQL 选择全表扫描。

9. 类型不匹配 MySQL 自动转型导致索引失效

字符串不加单引号索引失效，亦或是数值类型加单引号索引失效，也就是第三点中提到的自动类型转换(也叫隐式转换)导致索引失效。

10. 少用 or 或 in ，用它查询时，MySQL 不一定使用索引

MySQL 内部优化器会根据检索比例、表大小等多个因素计算查询成本是否使用索引。

二、order by 和 group by 优化

1. order by 优化

在前面的文章中小二给大家介绍了索引的底层数据结构，知道了索引本身就是一种排好序的数据结构，所以排序优化最好的办法就是落实到索引上，这样查询出来的数据就已经排好序了，这种排序在 MySQL 中被称之为 Using Index，即覆盖索引。那么如果查询出来后的数据本身没有按所需字段排序，那么就会出现 Using filesort，即文件排序。

所以，我们要优化 order by，那么主要就是消灭低效的 Using filesort，建立合适的联合索引使用覆盖索引来排序。

Using filesort文件排序原理详解

Using filesort 分为单路排序和双路排序(又叫回表排序模式)。

单路排序：一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序；用 trace 工具可以看到 sort_mode 信息里显示 < sort_key, additional_fields > 或者 < sort_key, packed_additional_fields >
双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用 trace 工具可以看到sort_mode 信息里显示 < sort_key, rowid >

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

如果字段的总长度小于 max_length_for_sort_data ，那么使用单路排序模式
如果字段的总长度大于 max_length_for_sort_data ，那么使用双路排序模式

接下来使用 trace 工具来瞅瞅单路排序和双路排序：

-- 单路排序
mysql> set session optimizer_trace="enabled=on", end_markers_in_json=on;

mysql> select * from employees where name = 'itwxe' order by position;

mysql> select * from information_schema.OPTIMIZER_TRACE;

"join_execution": {  -- sql执行阶段
  "select#": 1,
  "steps": [
    {
      "filesort_information": [
        {
          "direction": "asc",
          "table": "`employees`",
          "field": "position"
        }
      ] /* filesort_information */,
      "filesort_priority_queue_optimization": {
        "usable": false,
        "cause": "not applicable (no LIMIT)"
      } /* filesort_priority_queue_optimization */,
      "filesort_execution": [
      ] /* filesort_execution */,
      "filesort_summary": {  -- 文件排序信息
        "rows": 1,  -- 预计扫描行数
        "examined_rows": 1,  -- 参与排序的行数
        "number_of_tmp_files": 0,  -- 使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用的 磁盘文件排序
        "sort_buffer_size": 262056,  -- 排序缓存的大小，单位Byte
        "sort_mode": "<sort_key, packed_additional_fields>"  -- 排序方式，这里用的单路排序
      } /* filesort_summary */
    }
  ] /* steps */
} /* join_execution */

-- 双路排序
mysql> set max_length_for_sort_data = 10;  -- employees表所有字段长度总和哪怕一条记录也肯定大于10字节

mysql> select * from employees where name = 'itwxe' order by position;

mysql> select * from information_schema.OPTIMIZER_TRACE;

"join_execution": {
  "select#": 1,
  "steps": [
    {
      "filesort_information": [
        {
          "direction": "asc",
          "table": "`employees`",
          "field": "position"
        }
      ] /* filesort_information */,
      "filesort_priority_queue_optimization": {
        "usable": false,
        "cause": "not applicable (no LIMIT)"
      } /* filesort_priority_queue_optimization */,
      "filesort_execution": [
      ] /* filesort_execution */,
      "filesort_summary": {
        "rows": 1,
        "examined_rows": 1,
        "number_of_tmp_files": 0,
        "sort_buffer_size": 262136,
        "sort_mode": "<sort_key, rowid>"  -- 排序方式，这里用的双路排序
      } /* filesort_summary */
    }
  ] /* steps */
} /* join_execution */

单路排序的详细过程：

从索引 name 找到第一个满足 name = 'itwxe' 条件的主键 id
根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
从索引 name 找到下一个满足 name = 'itwxe' 条件的主键 id。
重复步骤 2、3 直到不满足 name = 'itwxe'
对 sort_buffer 中的数据按照字段 position 进行排序
返回结果给客户端

双路排序的详细过程：

从索引 name 找到第一个满足 name = 'itwxe' 的主键id
根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
从索引 name 取下一个满足 name = 'itwxe' 记录的主键 id
重复 3、4 直到不满足 name = 'itwxe'
对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出所有字段的值返回给客户端

总结：单路排序会将整行所有数据缓存到 sort buffer 中，双路排序只将主键id和排序字段放入到 sort buffer 中排序，在根据排序好的数据，从原来表中根据id查询数据返回给客户端。

如何选项单路排序还是多路排序？

MySQL 优化器使用双路排序还是单路排序是有自己的算法判断的，如果查询的列字段大于 max_length_for_sort_data 变量，则会使用双路排序，反之则会使用单路排序，单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加 max_length_for_sort_data 变量的大小。

不过需要注意，如果全部使用 sort_buffer 内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大 sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

2. group by 优化

group by 与 order by 很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于 group by 的优化如果不需要排序的可以加上 order by null 禁止排序。

三、分页查询优化

分页查询优化示例表仍为 employees。

select * from employees limit 90000,10;

很多时候我们业务系统实现分页功能可能会用如下 SQL 实现，看似 MySQL 是取90001行开始的10条记录，但是实际上 MySQL 在处理这个分页的时候是先读取前 90010 条记录，然后把前90000条记录舍弃，取出90001-90010的数据返回给客户端。因此如果要查询一张大表比较靠后的数据，执行效率是很低的。

1. 根据自增且连续的主键排序的分页查询

select * from employees where id > 90000 limit 10;

原理：根据主键索引 id 排除 <90000 的数据，取后10条数据避免全表扫描。

缺点：如果主键 id 不连续，或者中间有删除数据，则无法实现效果，所以通常使用下面的第二种方式。

2. 根据非主键字段排序的分页查询

select * from employees order by name limit 90000,10;
select * from employees ed_all inner join (select id from employees order by name limit 90000,10) ed_id on ed_all.id = ed_id.id;

可以看到查询相同的结果，但是非常大的查询速度差距，这还只是10w+的测试数据，加入是100w呢，差距得多大嘞！

阿里Java开发手册中也对这种情况进行了说明，非常推荐各位小伙伴们看下阿里Java开发手册，其中专门有一个章节对 MySQL 开发规范进行了说明。

四、join关联查询优化

-- 示例表
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table t2 like t1;

-- 往t1表插入1w行测试数据
drop procedure if exists insert_t1;
delimiter $$
create procedure insert_t1()
begin
  declare i int;
  set i = 1;
  while(i <= 10000)do
    insert into t1(a,b) values(i,i);
    set i = i + 1;                  
  end while;
end$$
delimiter ;
call insert_t1();

-- 往t2表插入100行测试数据
drop procedure if exists insert_t2;
delimiter $$
create procedure insert_t2()
begin
  declare i int;
  set i = 1;
  while(i <= 100)do
    insert into t2(a,b) values(i,i);
    set i = i + 1;                  
  end while;
end$$
delimiter ;
call insert_t2();

1. 表关联常见的两种算法

嵌套循环连接Nested-Loop Join(NLJ) 算法

NLJ 算法一次一行循环地从第一张表(驱动表)中读取行，在这行数据中取到关联字段，根据关联字段在另一张表(被驱动表)里取出满足条件的行，然后取出两张表的结果合集。

explain select * from t1 inner join t2 on t1.a = t2.a;

从执行计划可以看出，t2 作为驱动表，t1 作为被驱动表。先执行的就是驱动表(执行计划结果的 id如果一样则按从上到下顺序执行 SQL)，优化器通常会优先选择小表做驱动表，用 where 条件过滤完驱动表，然后再跟被驱动表做关联查询。所以使用 inner join 时，排在前面的表并不一定就是驱动表。

当使用 left join 时，左表是驱动表，右表是被驱动表。
当使用 right join 时，右表时驱动表，左表是被驱动表。
当使用 inner join 时，MySQL 通常会选择数据量比较小表作为驱动表，大表作为被驱动表。

注意：在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。而不是简单的比较两个表的总数据量。

上面 SQL 的大致流程如下：

从表 t2 中读取一行数据(如果t2表有查询过滤条件的，用先用条件过滤完，再从过滤结果里取出一行数据)。
从第 1 步的数据中，取出关联字段 a，到表 t1 中查找。
取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端。
重复上面 3 步。

整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行)。因此整个过程扫描了200 行。

如果连接查询的列上没有索引，NLJ 算法性能会比较低，那么 MySQL 则会选择 BNL 算法。

基于块的嵌套循环连接Block Nested-Loop Join(BNL)算法

BNL 算法把驱动表的数据读入到 join_buffer(连接查询缓存) 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

explain select * from t1 inner join t2 on t1.b = t2.b;

Extra 中的 Using join buffer (Block Nested Loop) 说明该关联查询使用的是 BNL 算法。同时可以看到 t2 仍然作为驱动表，t1 作为被驱动表。

上面 SQL 的大致流程如下：

把 t2 的所有数据放入到 join_buffer 中。
把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比。
返回满足 join 条件的数据。

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为 10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100万次。

这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。流程如下：

取驱动表的一部分数据放入 join_buffer，直至 join_buffer 放不了。(比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录)
扫描被驱动表的每一行数据，跟 join_buffer 中的数据做对比，满足 join 条件的数据作为结果集的一部分返回。(从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果)
清空 join_buffer。(清空 join_buffer)
继续读取驱动表剩下的数据，重复前三个步骤，一直驱动表的数据被扫描完。(再将 t2 表剩余200行记录记录放到 join_buffer 中，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以多扫了一次 t1 表和多扫描分段次数的 t2 表)

那么以这个分段放的例子来说，若驱动表的行数是 N，需要分 K 段才能扫描完，被驱动表的行数是 M，则扫描的行数是 N + K * M，即 1000 + 2 * 10000 = 21000 行；总的内存判断次数为 (800 + 200) * 10000 = 100万次。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 NLJ 算法呢？

假设没有索引的情况下选择 NLJ 算法，那么需要扫描的行数为 100 * 10000 = 100w万次，但是这个确是磁盘扫描。

很显然，用 BNL 算法磁盘扫描次数少很多，并且相比磁盘扫描，BNL 的内存计算会快得多。

因此 MySQL 对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法；如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高。

2. 对于关联SQL的优化

看完了 NLJ 算法和 BLJ 算法，想必关联 SQL 的优化也有一些思路了。

超过三个表禁止 join，多表关联查询时，保证被关联的字段需要有索引，尽量选择 NLJ 算法，同时需要 join 的字段，数据类型必须绝对一致。
小表驱动大表，写多表连接 SQL 时如果明确知道哪张表是小表可以用 straight_join 写法固定连接驱动方式，省去mysql优化器自己判断的时间。

straight_join：straight_join 功能同 inner join 类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定 MySQL 选择 t2 表作为驱动表。

straight_join 只适用于 inner join，并不适用于 left join、right join。(因为 left join、right join 已经代表指定了表的执行顺序)
尽可能让优化器去判断，使用 straight_join 一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

五、in和exsits优化

原则：小表驱动大表。

in优化：当B表的数据集小于A表的数据集时，in 优于 exists。

select * from A where id in (select id from B);
# 等价于：
for(select id from B) {
    select * from A where A.id = B.id;
}

exsits优化：当A表的数据集小于B表的数据集时，exists 优于 in。

select * from A where exists (select 1 from B where B.id = A.id);
# 等价于：
for(select * from A) {
    select 1 from B where B.id = A.id;
}

exists (subquery) 只返回 true 或 false，因此子查询中的 SELECT * 也可以用 SELECT 1 替换，官方说法是实际执行时会忽略 select 清单，因此没有区别。
exists 子查询往往也可以用 join 来代替，怎么最优查询需要具体问题具体分析。

六、count(*)查询优化

1. count比较

-- 临时关闭MySQL查询缓存
set global query_cache_size=0;
set global query_cache_type=0;

-- count
explain select count(*) from employees;
explain select count(1) from employees;
explain select count(id) from employees;
explain select count(name) from employees;

小伙伴们可能都听过 DBA 或者某些博客建议不要使用 count(*) 来统计数据行数，但是实际上并不是这样的，可以发现上面四条 SQL 的执行计划是一毛一样的。

那么既然执行计划是一样的，那么说明这4条语句的执行效率差不多其实，甚至5.7版本后 count(*) 效率还更高。不过需要注意的是 count(name) 不会统计 name 为 null 的数据行。

字段有索引：count(*) ≈ count(1) > count(字段) > count(主键id) // 字段有索引，count(字段) 统计走二级索引，二级索引存储数据比主键索引少，所以 count(字段) > count(主键id)。
字段无索引：count(*) ≈ count(1)> count(主键id) > count(字段) // 字段没有索引，count(字段) 统计走不了索引，count(主键id) 还可以走主键索引，所以 count(主键id) > count(字段)
count(1) 跟 count(字段) 执行过程类似，不过 count(1) 不需要取出字段统计，就用常量1做统计，count(字段) 还需要取出字段，所以理论上 count(1) 比 count(字段) 会快一点。
count(*) 是例外，MySQL 并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用 count(列名)或 count(常量) 来替代 count(*)。

为什么对于 count(id)，MySQL 最终选择辅助索引而不是主键聚集索引？

因为二级索引相对主键索引存储数据更少，检索性能应该更高，MySQL5.7 版本内部做了点优化。

2. 常见优化方法

a. 查询MySQL自己维护的总行数

对于 MyISAM 存储引擎的表做不带 where 条件的 count 查询性能是很高的，因为 MyISAM 存储引擎的表的总行数会被 MySQL 存储在磁盘上，查询不需要计算。

select count(*) from test_myisam;
explain select count(*) from test_myisam;

可以看到执行计划表都没有查询。

b. show table status

如果只需要知道表总行数的估计值可以用 show table status like 'employyees'; 查询，查询结果是个估计值。

c. 将总数维护到Redis里

插入或删除表数据行的时候同时维护 Redis 里的表总行数 key 的计数值(用 incr 或 decr 命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性。

d. 增加数据库计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作。

常见的 SQL 性能优化技巧就写到这里啦，写到此处已是深夜~~~

当然啦，SQL 优化技巧当然离不开怎么创建一个合适的索引，这篇文章的篇幅估计很长了...所以...下篇文章小二会通过一个有趣的小案例说说索引设计原则和技巧，小伙伴们下篇见