MySQL索引（六）索引优化补充，分页查询、多表查询、统计查询

MySQL
18
0
0
2024-10-31
MySQL事务（一）MySQL事务隔离级别、锁机制
上一篇
MySQL索引（五）索引优化分析工具
下一篇
标签   MySQL优化 MySQL索引 MySQL语句

前言

本文若未特意说明使用的数据表，均为 MySQL索引（四）常见的索引优化手段 中的示例表。

分页业务的索引优化

在业务场景中，经常会使用到分页处理，那么sql 实现语句可能如下：

SELECT * FROM employees limit 10000,10;

从"employees"表中选取10条数据，跳过前10000条数据，查询结果将返回"employees"表中第10001到第10010条记录。

实际上MySQL 会先读取完 10010 条数据，再过滤掉前 10000 条数据，这样的执行效率是非常低的。

优化手段

若是根据id 主键分页，同时主键自增且连续。对于 SELECT * FROM employees limit 10000,10; 我们可以改成：

SELECT * FROM employees id > 10000 limit 10;

从这两个语句的执行计划中我们可以得知，修改后的sql 语句使用了主键索引，并且减少了一半的扫描行数，执行的效率更高。

回到小鱼刚刚提到的条件：根据 id 主键分页，同时主键自增且连续。对于其他条件还适用吗？

  • 若主键不连续，得到的分页结果可能就不正确。
  • 若sql 语句采用了 ORDER BY 排序非主键字段，上诉优化方案也是不能使用的。

那么根据非主键进行排序的分页查询有办法进行优化吗？

小鱼来带给位同学看一个SQL 查询示例：

SELECT * FROM employees ORDER BY name limit 10000,10;

根据 MySQL索引（四）常见的索引优化手段 分析，可以知道该 sql 语句没有使用索引name 字段的原因：扫描整个索引的成本要比扫描全表的成本更高，mysql 优先选择成本低的方案。

优化方案：排序时返回的字段尽可能少，即在排序子查询时得到的结果集字段少，如只有id，再根据id 去查找其他字段。

SELECT * FROM employees e1 INNER JOIN (SELECT id FROM employees ORDER BY name limit 10000,10) e2 on e1.id = e2.id;

如此得到的结果也是与之前一致的，同时也使用了索引。

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表插入1万行记录
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();

创建两个表 t1 和 t2，并向这两个表插入相应的示例数据。

首先，创建了一个名为 t1 的表。该表包含了三个示例字段：id、a 和 b。Id 字段是自增的整数类型，不允许为空；a 和 b 字段都是整数类型，并设置了默认值为 NULL。Id 字段被指定为主键，a 字段上创建了一个名为 idx_a 的索引。表的存储引擎设置为 InnoDB，字符集设置为 utf 8。

接下来，使用"create table t 2 like t 1;"语句创建了一个名为 t2 的表。它使用了 t1 表的结构作为模板。

然后，定义了一个名为 insert_t1 的存储过程，该存储过程用于向 t 1 表中插入 1 万行记录。使用一个循环，从 1 到 10000，逐行插入数据，并将该数据的值作为 a 和 b 字段的值。最后，调用 insert_t1 存储过程，执行插入操作。

同样，定义了一个名为 insert_t2 的存储过程，用于向 t2 表中插入 100 行记录。使用一个循环，从 1 到 100，逐行插入数据，并将该数据的值作为 a 和 b 字段的值。最后，调用 insert_t2 存储过程，执行插入操作。

多表查询的两种算法

MySQL 的多表查询会用到两种方案：嵌套循环连接（Nested-Loop Join） 算法和基于块的嵌套循环连接 (Block Nested-Loop Join) 算法。

嵌套循环连接（Nested-Loop Join） 算法

NLJ 算法就是一次一行循环地从连接的第一张表（驱动表）中读取数据行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的数据行，再从中取出结果合集。

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

BNL 算法先把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

接下来，我们通过示例来进行说明。

EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a;

先执行的数据表就是驱动表，所以驱动表为 t2，被驱动表为 t1。

那这是怎么区分的呢？看过前序文章的同学应该知道，执行计划中序号越小越先执行，相同的序号则按顺序执行。

并且MySQL 会优先选择小表作为驱动表，先用where 条件过滤驱动表，再根据被驱动表做关联查询。所以在使用 inner join 关联查询时，排在前面的表不一定时驱动表。

而 left join 和 right join 则会指定驱动表，left join 以左表为驱动表；right join 以右表为驱动表。

如果MySQL 多表查找中使用了NLJ 算法，则在执行计划中extra 字段不会显示 Using join buffer。

对于 SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a; MySQL 大致的执行流程如下：

  1. 从驱动表 t2 中读取一行数据，若 t2 表存在查询条件，则先执行条件过滤，再从过滤条件中取一行数据。
  2. 从第一步中取出关联字段 a，到被驱动表 t1 中查找。
  3. 从第二部中取出满足条件的数据行，与 t2 表中获取的结果合并，作为结果返回。
  4. 重复上述三步骤。 此过程会扫描驱动表 t2 的所有数据行（100 行），再去遍历每行数据的a 字段，根据驱动表 t2 的a 值索引扫描被驱动表 t1 中对应的数据行，即会扫描 100 次 t1 表的索引，在示例表中最终也只扫描到 t1 表中一行数据。所以整个过程总共扫描到 200 行数据。

若在被驱动表关联字段没有索引，使用NLJ 算法性能会比较低，这个时候MySQL 就可能会选择BNL 算法。

EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.b = t2.b;

从查询计划中我们得知，b 字段没有索引，MySQL 选择BNL 算法来执行多表查询，extra 字段中显示 Using join buffer。

对于 SELECT * FROM t1 INNER JOIN t2 ON t1.b = t2.b; MySQL 大致的执行流程如下：

  1. 取出驱动表 t2 所有数据到join_buffer 中。
  2. 再把被驱动表 t1 中每一行数据取出来，跟join_buffer 中数据进行对比。
  3. 返回满足条件的数据结果集。 整个过程中会对 t2 和 t1 表做一次全表扫描，扫描的行数为 10100，同时由于join_buffer 中数据是无序的，对比时还有作 100 次判断，内存判断次数为 100 万。

若是驱动表数据较大，join_buffer 空间是有限的，这时MySQL 会分段操作。

join_buffer 是由 join_buffer_size 参数设定，默认值为 256K。

试想，如果采用的是 NLJ 算法会怎么样？

在内存执行 100 万次判断和在磁盘中执行 100 万次判断哪个快一些？答案显而易见：内存操作会快很多。

当然如果关联字段有索引，是有序的，一般会选择 NLJ 算法。

多表查询优化

  1. 对关联字段设计索引：对于索引字段，MySQL 一般会选择NLJ 算法，
  2. 使用小表驱动大表：在设计时如果明确哪个关联表是小表，可以使用 straight_join，会节省MySQL 优化器判断大小表时间。
straight_join ：straight_join 与 join 类似，但会股东驱动表，让左表来驱动右表，即能改表优化器对于联表查询的执行顺序。但对于 left join 和 right join 是不适用的，这两已经指定过驱动表。 使用 straight_join 需要谨慎，MySQL 优化器会比人为指定驱动表要靠谱。 关于小表定义：并不是表的数据量大小，而是表根据条件过滤后，参与join 关联的字段数据量，数据量小的才是小表。

in、exists 优化

in、exsits 的优化原则就是小表驱动大表。

假设有A、B 两张表，当B 表数据集小于A 表数据集时，如下的sql 语句中 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
};

当A 表的数据小于B 表数据时，在如下的sql 语句中 exists 要好于 in。即将主查询A 的数据放入到子查询B 中作条件验证，再根据验证条件（只有true 和false）决定主查询的数据是否保留。

SELECT * FROM A WHERE EXISTS(SELECT 1 FROM B WHERE B.id = A.id);

--- 相当于
FOR(SELECT * FROM A) {
	SELECT * FROM B WHERE B.id = A.id
};
  1. EXISTS (subquery) 子查询返回的结果只有 TRUE 或 FALSE，所以子查询中的 SELECT * 也可以用 SELECT 1 替换。官方文档中提到实际执行过程中会忽略 SELECT，两种方式没有区别。
  2. EXISTS 子查询实际执行过程由MySQL 进行了优化，并不是通常理解上的逐条对比。
  3. EXISTS 子查询通常可以用 JOIN 实现，不过最优方案需要根据具体问题去具体分析。

COUNT(*) 查询优化

sql 查询数据表的总量有四种方式，如下：

SELECT COUNT(1) FROM employees;
SELECT COUNT(id) FROM employees;
SELECT COUNT(name) FROM employees; --- 不会统计name字段为null的情况
SELECT COUNT(*) FROM employees;

从执行计划中看四条语句都会使用索引，小鱼来分析下这四种情况。

  • 若字段存在索引：count(*) ≈ count(1) > count(字段) > count(主键)由于二级索引存储的数据相较于主键索引较少，所以 count(字段) > count(主键)
  • 若字段无索引：count(*) ≈ count(1) > count(主键) > count(字段)由于字段没有索引，主键索引要比全表扫描快，所以 count(主键) > count(字段)。
  • count(*) ≈ count(1) ：count(1) 用常量 1 计算，count(*) 由MySQL 特意优化，不会取值，而是按行计算。

为什么 count(id) 没有使用主键索引？

答案是二级索引相对于主键索引存储的数据较少，检索的效率更高。

优化

  • 若使用myisam 存储引擎，每个表会维护一个总行数，查询总行数是不需要进行计算的。
  • 若只需要估算总行数，可以使用 SHOW TABLE STATUS LIKE 'employees'

  • 使用缓存维护总行数，再更新数据行时将数据表名作为key，总行数作为value 更新至redis，这种方式需要考虑数据的一致性。
  • 增加数据库统计表，在更新数据行的事务中，增加维护统计表操作。注意需要在一个是事务中实现。
MySQL事务（一）MySQL事务隔离级别、锁机制
上一篇
MySQL索引（五）索引优化分析工具
下一篇