深入浅出Mysql(四)-Mysql索引优化实战-下篇

60 阅读5分钟

前言

跟着本文你将学到:

  • 分页查询优化详解
  • 表JOIN关联原理详解及优化
  • 表COUNT查询优化
  • 阿里巴巴MySQL规范
  • MySQL数据类型选择分析

一、准备工作

创建示例表

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('curtis',i),i,'dev');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();

二、分页场景的优化技巧

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

优化前:select * from employees limit 90000,5;

优化后:select * from employees where id > 90000 limit 5;

上面两条sql语句,在连续且自增主键的情况下,查询到的结果一样,但是第一条语句是把90000条数据都取了出来,然后筛出5条数据;而第二条语句是根据大于90000的id筛出5条(只取5条数据),所以第二条语句远远比第一条要快得多。

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

优化前:select * from employees ORDER BY name limit 90000,5;

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

优化后的sql是使用的覆盖索引,在辅助索引树里面先查找了需要的id,再去主树里面查找。

需要的结果与原 SQL一致,执行时间减少了一半以上。

原 SQL 使用的是 filesort 排序,而优化后的 SQL 使用的是索引排序。

三、MySQL的表关联常见的算法

1、准备工作

1 ‐‐ 示例表:
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 ;26 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();

2、嵌套循环连接 Nested-Loop Join(NLJ) 算法(使用索引)

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

image.png 解析:

  1. 驱动表是 t2,被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql);优化器一般会优先选择小表做驱动表。所以使用 inner join 时,排在前面的表并不一定就是驱动表。

  2. 当使用left join时,左表是驱动表,右表是被驱动表,当使用right join时,右表时驱动表,左表是被驱动表,当使用join时,mysql会选择数据量比较小的表作为驱动表,大表作为被驱动表。

  3. 使用了 NLJ算法。一般 join 语句中,如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

执行流程:

  1. 从表 t2 中读取一行数据(如果t2表有查询过滤条件的,会从过滤结果里取出一行数据);

  2. 从第 1 步的数据中,取出关联字段 a,到表 t1 中查找;

  3. 取出表 t1 中满足条件的行,跟 t2 中获取到的结果合并,作为结果返回给客户端;

  4. 重复上面 3 步。

总结:

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

如果被驱动表的关联字段没索引,使用NLJ算法性能会比较低(下面有详细解释),mysql会选择Block Nested-Loop Join算法。

3、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法(不使用索引)

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

image.png 上面sql的大致流程如下:

  1. 把 t2 的所有数据放入到 join_buffer 中

  2. 把表 t1 中每一行取出来,跟 join_buffer 中的数据做对比

  3. 返回满足 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 的所有数据的话,策略很简单,就是分段放

3、总结

  • 关联的那张大表尽量要走索引。
  • 使用小表作为驱动表,小表驱动大表
  • 使用straight_join,straight_join功能同join类似,但能让左边的表来驱动右边的表,能改表优化器对于联表查询的执行顺序。

四、count( )解析

‐‐ 临时关闭mysql查询缓存,为了查看sql多次执行的真实时间

set global query_cache_size=0;
set global query_cache_type=0;

EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;

1、字段有索引的情况

count(*)≈count(1)>count(字段)>count(主键 id)

字段有索引,count(字段)统计走二级索引,二级索引存储数据比主键索引少,所以count(字段)>count(主键 id)。

2、字段无索引的情况

count(*)≈count(1)>count(主键 id)>count(字段)

字段没有索引count(字段)统计走不了索引,count(主键 id)还可以走主键索引,所以count(主键 id)>count(字段)。

3、总结

count(1)跟count(字段)执行过程类似,不过count(1)不需要取出字段统计,就用常量1做统计,count(字段)还需要取出字段,所以理论上count(1)比count(字段)会快一点。

count(*) 是例外,mysql并不会把全部字段取出来,而是专门做了优化,不取值,按行累加,效率很高,所以不需要用count(列名)或count(常量)来替count(*)。

为什么对于count(id),mysql最终选择辅助索引而不是主键聚集索引? 因为二级索引相对主键索引存储数据更少,检索性能应该更高,mysql内部做了点优化(应该是在5.7版本才优化)。

五、阿里巴巴MySQL手册规则

这里就不放链接了,直接百度就可~

下面挑几条重要的记录下

1、【推荐】单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表。

说明:如果预计三年后的数据量根本达不到这个级别,请不要在创建表时就分库分表。

2、【强制】业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引。

说明:不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的;另外,

即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。

3、【推荐】利用覆盖索引来进行查询操作,避免回表。

说明:如果一本书需要知道第 11 章是什么标题,会翻开第 11 章对应的那一页吗?目录浏览一下就好,这个目录就是起到覆盖索引的作用。

正例:能够建立索引的种类分为主键索引、唯一索引、普通索引三种,而覆盖索引只是一种查询的一种效果,用 explain 的结果,extra 列会出现:using index。

4、【强制】超过三个表禁止 join。

需要 join 的字段,数据类型保持绝对一致。

多表关联查询时,保证被关联的字段需要有索引。

说明:即使双表 join 也要注意表索引、SQL 性能。

5、【强制】在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度。

说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90%

以上,可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

6、【推荐】SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts最好。

说明:

1)consts 单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。

2)ref 指的是使用普通的索引(normal index)。

3)range 对索引进行范围检索。

反例:explain 表的结果,type=index,索引物理文件全扫描,速度非常慢,这个 index 级别比较 range还低,与全表扫描是小巫见大巫。

7、【强制】不要使用 count(列名)或 count(常量)来替代 count(),count()是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。

说明:count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行。

8、【推荐】in 操作能避免则避免。

若实在避免不了,需要仔细评估 in 后边的集合元素数量,控制在 1000 个之内。

六、MySQL数据类型选择分析

待更新....