MO_or关于SQL优化的感悟

服务器

浏览数:29

2020-7-5

AD:资源代下载服务

一、引言

本文是对SQL优化的复习总结,主要记录如何使用索引优化SQL,数据库为MySQL。主要从三个部分依次进行探讨。
第一部分:理解MySQL索引底层数据结构。
第二部分:SQL分析工具Explain详解。
第三部分:MySQL的索引最佳实践。

强烈建议:由于本文篇幅较长,内容较多。推荐读者每次仅阅读一部分,请勿一次性读完(并不利于消化吸收,大佬除外)。

二、MySQL索引

2.1 索引的简单介绍

索引就是一种帮助MySQL高效获取数据的排好序的数据结构。

2.2 索引的数据结构

这里先说结论,MySQL索引的数据结构是B+TREE。

我们再依次从二叉树到B+TREE,逐步的理解MySQL索引为什么使用B+TREE。

在开始讨论具体的数据结构之前,我们应该先初步的了解什么是数据结构,数据结构的作用是什么?
如果大家了解设计模式,或者看过我的《MO_or的单例模式复习总结》就能知道。
设计模式是提供了针对不同类型的问题的优质解决方案。
相对应的,数据结构其实就是提供了针对不同数据的优质存储方案,
当然这些方案同设计模式一样,也是通过前辈们无数次的实践试错改良所得出的。
那么下面就正式进入几种数据结构的讲解。

2.2.1 二叉树

在了解二叉树之前,我们需要先明白索引为什么要用数据结构?
结合下图,假如我们在不使用数据结构(图片左侧)情况下,需要读取Col2=89,那么磁盘就需要进行6次I/O。
如果使用二叉树来存储数据(图片右侧),那磁盘仅需进行2此I/O,这就显著的减少了I/O次数,其效率也就相应得到了提升。
由此我们就能明白为什么索引需要使用数据结构。那索引为什么不使用二叉树,而要使用B+TREE呢?

上图的右侧便是一个常见的二叉树模型(模型演示的网址在五、参考)。
二叉树的规则为下一节点左边的元素小于上一节点元素(22<34),
下一节点右边的元素大于等于上一节点元素(89>=34)。
结合下图,我们就能明白为什么索引不使用二叉树。

从上图中便能看出,当元素都为依次递增的情况下,二叉树的元素节点则变成了按单列的方式排布。
这时我们若想取元素6时,那么也只能让磁盘进行6次I/O。
于是为了解决这个问题,我们就可以使用红黑树(平衡二叉树)。

2.2.2 红黑树(平衡二叉树)

直接上图,同样是元素依次递增的情况下。

可以看出红黑树在基于二叉树的基础上,进行了平衡,不再是以单列的方式进行排布。
但索引为什么依然没有使用红黑树?因为目前数据量较小,层级不高,但数据库中通常会出现几十万、几百万乃至上千万的数据。
我们可以估算下,若表中存储的数据有100万条,既2^n=100万,n=log(2)(100万),n≈20。
这意味着若我们需要取得数在最深的节点上,那么就需要读写20次及以上的I/O。
由此我们可以看出,红黑树在遇到大数据量时,性能依旧较差。并不符合索引可以高效获取数据的这一特点。

2.2.3 B-TREE(多路搜索树)

为了解决红黑树在存储大量数据的情况下,层级依旧很深的问题。于是就有了更好方案,B-TREE。那么我们先看下B-TREE的模型吧。

从上图可以直观的看出,在红黑树的基础上。B-TREE的叶节点(15、56、77所类似的行),从原来只能存储一个元素,变为了可以存储多个元素。
这样就大大增加了每个叶节点的利用空间,减少了层数。但我们也看到每个数字节点下方还有个data。
那么新的问题便产生了,这个data即为所存储的数据,那么当一行数据过大时,每个叶节点所能容纳的元素就相应减少了。
我们可以通过以下SQL,来查询叶节点的大小,通常为16kb,
SHOW GLOBAL STATUS LIKE 'INNODB_page_size';
若假设一个data为1kb,那意味着每个叶节点最多能容纳16个元素。那么当数据量过多时上千万,依然存在红黑树一样的问题。

2.2.4 B+TREE

那么终于轮到B+TREE上场了,我们通过下图一起看看B+TREE是如何巧妙地解决B-TREE所面临的问题的吧。

可以看出,B+TREE非叶子节点(叶子节点为最下面的一行)是没有存储data的,而是存储索引(冗余)。
只有叶子节点才存储data,并且包含了所有的索引。那么这样做的意义是什么呢?
上面说了叶节点的大小通常为16KB。若索引(冗余)为bigint,再加上空白(连接箭头的起始位置其实是指针),即8b+6b=14b(估算)。
那么每个叶节点所能容纳的元素个数:16kb=16*1024b,n=16*1024/14≈1170。那就表示叶子节点大约可以存储1170个元素。
再假设data为1kb,同B-TREE,那就是可以容纳16个元素,那么非叶子节点总共就有:1170*1170*16≈2200万个元素。
通常来说B+TREE的层次就是2~4层,上千万的数据量也仅需2~4次I/O就能准确定位。
在大数据量的情况下依旧能高效的获取数据,这便是索引的底层数据结构为B+TREE的原因。

2.3 最左前缀原理

这里仅简单概括其原理,具体如何在SQL中体现的,将结合三、Explain的部分进行解读。
当使用联合索引(由多个列组成的索引)时,查询需遵从从左到右的顺序,且不能跳过中间的列。否则会导致索引失效。

三、Explain

3.1 Explain详解

在上一部分中,我们对索引有了较为深入的理解了,但并不要着急,这一部分暂时还不会详细的探讨如何利用索引优化SQL。
在此之前,我们还需要了解分析SQL性能的一个工具,即Explain。
使用Explain关键字,可以模拟优化器执行SQL语句,分析查询语句或结构的性能瓶颈。我们可以根据分析结果,进行对应的优化。

那么现在结合SQL我们来看看Explain吧。
-- 演员表
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor`  (
  `id` int(11) NOT NULL,
  `name` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `update_time` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `actor` VALUES 
(1, 'a', '2018-10-23 16:04:40'),
(2, 'b', '2018-10-23 16:04:40'),
(3, 'c', '2018-10-23 16:04:40');

-- 电影表
DROP TABLE IF EXISTS `film`;
CREATE TABLE `film`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `film` VALUES 
(1, 'film1'),
(2, 'film2'),
(3, 'film0');

-- 电影演员关系表
DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor`  (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_film_actor_id`(`film_id`, `actor_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `film_actor` VALUES 
(1, 1, 1, NULL),
(2, 1, 2, NULL),
(3, 2, 1, NULL);
mysql> EXPLAIN SELECT * FROM actor;

上图为执行EXPLAIN展示的结果,若有join连接多个表时,则每join一个表多输出一行。
其中type列是需要较长时间理解的列,当然随着使用次数增多自然而然也会熟能生巧,所以并不需要死记硬背。

3.1.1 id列

1)id编号就是select的序列号,有几个select就有几个id,并且id的顺序是按select出现顺序而增长的。
2)id越大执行优先级越高,id相同则从上至下执行,id为null则最后执行。

3.1.2 select-type列

select-type列表示简单还是复杂查询,共有以下类型:
1)simple:简单查询,不包含子查询subquery、derived和union。
2)primary:复杂查询最外层的select。
3)subquery:select后的子查询(不包含from后)
4)derived:from后的子查询,MySQL会将查询结果存入临时表,也称派生表。我们通过SQL来看一下:
EXPLAIN SELECT
    ( SELECT 1 FROM actor WHERE id = 1 ) 
FROM
    ( SELECT * FROM film WHERE id = 1 ) der;

5)union:在union中的第二个和随后的select。

3.1.3 table列

该列表示explain的一行正在访问哪个表。
当from中有子查询时,该列展示为<derivedN>,N表示id编号,意味着先执行id=N的查询。
当有union时,UNION RESULT的table列的值为 <union1,2>,1和2表示参与 union 的select 行id。

3.1.4 type列

这一列表示关联类型或访问类型,即MySQL决定如何查找表中的行,查找数据行记录的大概范围。
依次从最优到最差分别为:system > const > eq_ref > ref > range > index > ALL。
一般来说,得保证查询达到range级别,最好达到ref。
NULL:mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。
例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表 

const, system:
mysql能对查询的某部分进行优化并将其转化成一个常量(可以看showwarnings 的结果)。
用于 primary key 或 unique key 的所有列与常数比较时,所以表最多有一个匹配行,读取1次,速度比较快。
system是const的特例,表里只有一条元组匹配时为system。

eq_ref:
primary key 或 unique key 索引的所有部分被连接使用 ,最多只会返回一条符合条件的记录。
这可能是在 const 之外最好的联接类型了,简单的 select 查询不会出现这种type。

ref:相比 eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,
索引要和某个值相比较,可能会找到多个符合条件的行。

range:范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

index:扫描全表索引,这通常比ALL快一些。

ALL:即全表扫描,意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了。

3.1.5 possible_keys列

该列表示可能使用到的索引列。
explain时可能出现possible-keys列有值,key列为null。可能时因为数据量较少,mysql认为全表扫描效率更高。
若该列为null,则没有相关索引。此时可考虑增加适当索引来提高查询效率。

3.1.6 key列

该列表示mysql实际使用的索引。
若没有使用索引,则该列为null。
如果想强制mysql使用或忽视possible_keys列中的索引,在查询中使用 force index、ignore index。

3.1.7 key_len列

该列表示mysql使用索引的字节数,在使用联合索引时通过key_len就能知道具体使用了那些列。
比如下面的SQL,key_len=4,就可以推断出仅用了联合索引中的id列,因为int占4个字节。
mysql> EXPLAIN SELECT * FROM film_actor WHERE film_id = 2;

key_len计算规则如下:
1)字符串
    char(n):n字节长度
    varchar(n):2字节存储字符串长度,如果是utf-8,则长度3n+2
2)数值类型
    tinyint:1字节
    smallint:2字节
    int:4字节
    bigint:8字节
3)时间类型
    date:3字节
    timestamp:4字节
    datetime:8字节
如果字段允许为 NULL,需要1字节记录是否为 NULL

3.1.8 ref列

这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名(例:film.id)。

3.1.9 rows列

这一列是mysql估计要读取并检测的行数,注意这个不是结果集里的行数。

3.1.10 Extra列

这一列展示的是额外信息。常见的重要值如下:
1)Using index:使用覆盖索引。
2)Using where:使用 where 语句来处理结果,查询的列未被索引覆盖。
3)Using index condition:查询的列不完全被索引覆盖,where条件中是一个前导列的范围。
4)Using temporary:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的,首先是想到用索引来优化。
5)Using filesort:将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
6)Select tables optimized away:使用某些聚合函数(比如 max、min)来访问存在索引的某个字段是。

四、索引最佳实践

-- 员工表
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 = 4 DEFAULT CHARSET = utf8 COMMENT = '员工记录表';

INSERT INTO employees ( NAME, age, position, hire_time )
VALUES
    ( 'LiLei', 22, 'manager', NOW( ) ),
    ( 'HanMeimei', 23, 'dev', NOW( ) ),
    ( 'Lucy', 23, 'dev', NOW( ) );

4.1全值匹配

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

4.2.最左前缀法则

如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

4.3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描

4.4.存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT \* FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

4.5.尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少select *语句

4.6.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

4.7.is null,is not null 也无法使用索引

4.8.like以通配符开头(’$abc…’)mysql索引失效会变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

4.9.字符串不加单引号索引失效

4.10.少用or或in,用它查询时,mysql不一定使用索引,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引,详见范围查询优化

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

4.11.范围查询优化

给年龄添加单值索引。  
ALTER TABLE `employees` ADD INDEX `idx_age` ( `age` ) USING BTREE;
EXPLAIN SELECT * FROM employees WHERE age >= 1 AND age <= 100;

没走索引原因:mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。
比如这个例子,可能是由于单次数据量查询过大导致优化器最终选择不走索引。
优化方法:可以讲大的范围拆分成多个小范围。
EXPLAIN SELECT * FROM employees WHERE age >= 1 AND age <= 50;
EXPLAIN SELECT * FROM employees WHERE age >= 51 AND age <= 100;

以上全部代码均已在本机执行且无误。

五、参考

数据结构动态演示模型

MO_or的单例模式复习总结

书写高质量SQL的30条建议

六、最后

若有不足,敬请指正。
求知若渴,虚心若愚。

作者:MO_or