您现在的位置是:首页 >其他 >5. Mysql索引优化实战二网站首页其他

5. Mysql索引优化实战二

俺就是菜得很 2024-06-14 17:20:24
简介5. Mysql索引优化实战二


本文是按照自己的理解进行笔记总结,如有不正确的地方,还望大佬多多指点纠正,勿喷。

本节课内容:

1、分页查询优化详解

2、表JOIN关联原理详解及优化

3、表COUNT查询优化

4、阿里巴巴MYSQL规范解读

5、MYSQL数据类型选择分析

1. 分页查询优化

示例表:

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='员工记录表';

很多时候我们业务系统实现分页功能可能会用如下sql实现

select * from employees limit 10000,10;

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

所以我们在查一些数据进行翻页的时候前面几页可能就很快翻到了,越往后面越翻不动就是这个原因。

============================================================================================

============================================================================================

常见的分页场景优化技巧

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

首先来看一个根据自增且连续主键排序的分页查询的例子:

select * from employees limit 90000,5;

在这里插入图片描述

该 SQL 表示查询从第 90001开始的五行数据,没添加单独 order by,表示通过主键排序。我们再看表 employees ,因为主键是自增并且连续的,所以可以改写成按照主键去查询从第 90001开始的五行数据,(按照下面的这种方式进行优化是有严格的条件的:主键是自增的且是连续的)如下:

select * from employees where id > 90000 limit 5;

在这里插入图片描述
查询的结果是一致的。我们再对比一下执行计划:

EXPLAIN select * from employees limit 90000,5;

在这里插入图片描述

EXPLAIN select * from employees where id > 90000 limit 5;

在这里插入图片描述
显然改写后的 SQL 走了索引,而且扫描的行数大大减少,执行效率更高。

但是,这条改写的SQL 在很多场景并不实用,因为表中可能某些记录被删后,主键空缺,导致结果不一致,如下图试验所示(先删除一条前面的记录,然后再测试原 SQL 和优化后的 SQL):

EXPLAIN select * from employees limit 90000,5;

在这里插入图片描述

EXPLAIN select * from employees where id > 90000 limit 5;

在这里插入图片描述
所以虽然下面这条语句改写的效率高了,但是有前提条件,这个条件要求太苛刻了,一般很少有这种情况的。

两条 SQL 的结果并不一样,因此,如果主键不连续,不能使用上面描述的优化方法。

另外如果原 SQL 是 order by 非主键的字段,按照上面说的方法改写会导致两条 SQL 的结果不一致。所以这种改写得满足以下两个条件:

  • 主键自增且连续
  • 结果是按照主键排序的

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

再看一个根据非主键字段排序的分页查询,SQL 如下:

select * from employees ORDER BY name limit 90000,5;

在这里插入图片描述

这个是有前提条件的,前提条件是name是其中一个联合索引。

KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE,

没有走任何索引

EXPLAIN select * from employees ORDER BY name limit 90000,5;

在这里插入图片描述

发现并没有使用 name 字段的索引(key 字段对应的值为 null),具体原因上个博客写过:扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高,所以优化器放弃使用索引。因为还要扫描辅助索引树还要回表扫描聚簇索引树,最后还没有扫描全表快

知道不走索引的原因,那么怎么优化呢?

其实关键是让排序时返回的字段尽可能少,所以可以让排序和分页操作先查出主键,然后根据主键查到对应的记录,SQL 改写如下:

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

在这里插入图片描述
可以看出查询的结果是一样的。

需要的结果与原 SQL 一致,执行时间减少了一半以上,我们再对比优化前后sql的执行计划:

优化之前的sql

没有走任何索引且有文件排序

EXPLAIN select * from employees ORDER BY name limit 90000,5;

在这里插入图片描述

优化之后的sql

EXPLAIN select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

在这里插入图片描述

优化之后的sql没有文件排序了,2走了覆盖索引且走的索引排序。1表走的是主键关联。且只有五条记录。即使1走的是all,但是他是一个临时表,且这个临时表就5条数据,每条数据都只有id.

2. 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();
风语者!平时喜欢研究各种技术,目前在从事后端开发工作,热爱生活、热爱工作。