MO_or关于SQL优化的感悟

哈喽！今天心血来潮给大家带来了《MO_or关于SQL优化的感悟》，想必大家应该对数据库都不陌生吧，那么阅读本文就都不会很困难，以下内容主要涉及到MySQL、索引、优化、数据结构和算法，若是你正在学习数据库，千万别错过这篇文章~希望能帮助到你！

一、引言

本文是对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)。
结合下图，我们就能明白为什么索引不使用二叉树。

从上图中便能看出，当元素都为依次递增的情况下，二叉树的元素节点则变成了按单列的方式排布。
这时我们若想取元素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中有子查询时，该列展示为，N表示id编号，意味着先执行id=N的查询。
当有union时，UNION RESULT的table列的值为 ，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 

没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。
比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引。
优化方法：可以讲大的范围拆分成多个小范围。

EXPLAIN SELECT * FROM employees WHERE age >= 1 AND age 

EXPLAIN SELECT * FROM employees WHERE age >= 51 AND age 

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

五、参考

数据结构动态演示模型

MO_or的单例模式复习总结

书写高质量SQL的30条建议

六、最后

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

以上就是《MO_or关于SQL优化的感悟》的详细内容，更多关于mysql的资料请关注golang学习网公众号！