mysql分页,排序索引优化

2022-08-04 11:36:39

建测试表

-- 给name age position加联合索引
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('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

-- 插入一些示例数据
DROP PROCEDURE IF EXISTS insert_emp;
delimiter $$
CREATE PROCEDURE insert_emp ()
BEGIN
    DECLARE i INT;
    SET i =1;
    WHILE i < 100000 DO
        INSERT INTO employees(name,age,position)VALUES(CONCAT('xiaohong',i),i,'dev');
        SET i=i+1;
    END WHILE;
END$$
CALL insert_emp();
-- 示例表:
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$$
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$$
CALL insert_t2();

分页查询优化

select * from employees limit 10000,10;

表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录,实际这条 SQL 是先读取 10010 条记录,然后抛弃前 10000 条记录,然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据,执行效率是非常低的。

-- 执行时间0.116
select * from employees ORDER BY name limit 90000,5;
EXPLAIN select * from employees ORDER BY name limit 90000,5;

在这里插入图片描述
在这里插入图片描述
不走索引文件排序,因为虽然用的是name字段,这里没有用索引,和mysql的引擎优化有关,它可能觉得扫描整个索引没有全表扫描来的快,所有这里没有用索引

强制走索引

-- 执行时间0.090(会比不使用强制索引来的快一些)
select * from employees force index(idx_name_age_position) ORDER BY name limit 90000,5;
EXPLAIN select * from employees force index(idx_name_age_position) ORDER BY name limit 90000,5;

在这里插入图片描述

优化

-- 执行时间0.040快了一半
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
EXPLAIN select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

通过使用id或者联合索引的name先用索引树查出结果,在通过inninner join内连接的方式去比对全表,找到指定的数据即可
这样就可以不用考虑会先查出90000然后舍弃90000取后5行了
在这里插入图片描述
id越大的先执行可以看到,先通过索引树查出5行的结果集,然后id相同从上往下执行
拿结果集去查询全表,拿到全表的结果之后,在通过主键索引去做inninner join连接

Join关联查询优化

mysql的表关联常见有两种算法

驱动表与被驱动表

连表的时候,有分为驱动表与被驱动表
当使用left join时,左表是驱动表右表是被驱动表(已左表为主,左表是驱动表)
当使用right join时,右表时驱动表左表是被驱动表(已右表为主,右表是驱动表)
当使用join时,mysql会选择数据量比较小的表作为驱动表大表作为被驱动表
所以使用inner join 时,排在前面的表并不一定就是驱动表,mysql优化器会自己根据where条件过滤后的结果集确定哪个为驱动表,哪个为被驱动表(原则就是小表作为驱动表,大表被驱动表)

Nested-Loop Join 算法

嵌套循环连接 Nested-Loop Join(NLJ) 算法
一次一行循环地从第一张表(称为驱动表)中读取行,在这行数据中取到关联字段,根据关联字段在另一张表(被驱动表的索引树中)里取出满足条件的行,然后取出两张表的结果合集。
在磁盘中运算

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

在这里插入图片描述
使用了NLJ算法。一般 join 语句中,如果执行计划Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。
上面sql的大致流程如下:
1.从表 t2(驱动表的100行) 中读取一行数据(如果t2表有查询过滤条件的,用先用条件过滤完,再从过滤结果里取出一行数据);
2.从取出的数据中,取出关联字段 a,因为t1表有创建了a索引,所有到表 t1索引树中查找对应的主键索引
3.取出表 t1 中满足条件的行,跟 t2 中获取到的结果合并;
4.重复上面 3 步。
整个过程会读取 t2 表的所有数据(扫描100行),然后扫描t1的索引树,通常情况下扫描一行索引树足够了,所有总的下来
t1扫描了100次,t2扫描索引树也是100次,合起来200次
如果被驱动表的关联字段没索引,使用NLJ算法性能会比较低(下面有详细解释),mysql会选择Block Nested-Loop Join算法(内存)。

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

把驱动表(t2)的数据读入到 join_buffer(内存) 中,然后扫描被驱动表t1,把被驱动表每一行取出来跟 join_buffer 中的数据做对比(因为没有建立索引)

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

在这里插入图片描述
Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。
上面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 的所有数据话,策略很简单,就是分段放。
比如 t2 表有1000行记录, join_buffer 一次只能放800行数据,那么执行过程就是先往 join_buffer 里放800行记录,然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果,然后清空 join_buffer ,再放入 t2 表剩余200行记录,再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。

问题

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢?
因为BNL是基于内存去做排序的,内存的处理速度远远快于NLJ的硬盘处理速度
被驱动表的关联字段有索引为什么要选择使用 Nested-Loop Join算法而不使用 BNL 呢?
因为索引是存在硬盘中,而不是存在内存中,在者,内存中的数据是无序的,计算成本会大大提升,不值得

结论

如果有索引一般选择 NLJ 算法,有索引的情况下 NLJ 算法比 BNL算法性能更高

对于关联sql的优化

关联字段加索引,让mysql做join操作时尽量选择NLJ算法,驱动表因为需要全部查询出来,所以过滤的条件也尽量要走索引,避免全表扫描,总之,能走索引的过滤条件尽量都走索引
小表驱动大表,写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式,省去mysql优化器自己判断的时间
straight_join解释:straight_join功能同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已经代表指定了表的执行顺序)
尽可能让优化器去判断,因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重,因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

对于小表定义的明确
在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与 join 的各个字段的总数据量==,数据量小的那个表,就是“小表”,应该作为驱动表==。

count(*)查询优化

show table status

如果只需要知道表总行数的估计值可以用如下sql查询,性能很高

show table status like 'employees'

在这里插入图片描述

将总数维护到Redis里

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

增加数据库计数表

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

  • 作者:wan_Szw
  • 原文链接:https://blog.csdn.net/qq_41116023/article/details/110134985
    更新时间:2022-08-04 11:36:39