MySQL索引(六)索引优化补充,分页查询、多表查询、统计查询

MySQL
34
0
0
2024-10-31

前言

本文若未特意说明使用的数据表,均为 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();

创建两个表 t1t2,并向这两个表插入相应的示例数据。

首先,创建了一个名为 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 joinright 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. 返回满足条件的数据结果集。 整个过程中会对 t2t1 表做一次全表扫描,扫描的行数为 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_joinstraight_join 与 join 类似,但会股东驱动表,让左表来驱动右表,即能改表优化器对于联表查询的执行顺序。但对于 left joinright join 是不适用的,这两已经指定过驱动表。 使用 straight_join 需要谨慎,MySQL 优化器会比人为指定驱动表要靠谱。 关于小表定义:并不是表的数据量大小,而是表根据条件过滤后,参与join 关联的字段数据量,数据量小的才是小表。

inexists 优化

inexsits 的优化原则就是小表驱动大表。

假设有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,这种方式需要考虑数据的一致性。
  • 增加数据库统计表,在更新数据行的事务中,增加维护统计表操作。注意需要在一个是事务中实现。