【好看的备注前缀】掌门MySQL数据库协议登陆和实战优化-拍拖百科

前言

MySQL数据库托管负责大部分核心业务的数据存储库，因此MySQL数据库的稳定运行至关重要。DBA团队致力于确保数据库环境的顺利运行，编写MySQL数据库规范文档，并为在线操作和维护平台提供强大的数据库查询。

规范很全面，但如果不理解数据库原理，不知道规范效率的提高，开发者就不一定严格遵守，规范也会失去意义。

这篇文章从数据库原理三个角度(表、索引、SQL语句)进行深入分析，了解数据库基本原则，了解数据库规范和数据库调整方法。

第一，做手表

如果把MySQL数据库实例看作图书馆，数据表可以看作一本书。题目应该像表明，概述用途，易于理解，有意义。如果书内容太多，可以选择发行分册，数据表可以选择分册或分册。

开发人员需要根据实际业务场景、数据量设计各种表以满足业务需求。关系数据库中的数据字段在以后的更改成本很高，因此在前期设计阶段必须考虑用途，设计合理的表结构。优秀的表结构设计是数据库优化中非常重要的部分。数据表设计应符合以下规范要求：

1.字符集

规格：

数据库表字符集均匀设置为utf8mb4，排序规则为utf8mb4_general_ci。DBA根据字符集utf8mb4根据排序规则utf8mb4_general创建数据库，因此表和字段的字符集可以保持与数据库配置相同的默认值，而无需进一步设置。

解决：

1.如果字符集或排序规则不匹配，则无法将查询与表相关联

2.文字转换会使索引无效，还会进一步消耗数据库性能

3.通过统一字符集和排序规则设置，可以减少不必要的字符集问题

1 charset=ut F8 mb4 collate=ut F8 mb4 _ general _ ci

2.主键ID

规格：

必须创建“无符号”(unsigned)自增量(auto_increment)成型(int或bigint)关键字ID字段

解决：

1.自增密钥非常适合MySQL数据库群集索引数据结构

2.Int类型长度为4字节，bigint为8字节，数据长度较小。字符类型越小，密钥长度越短(索引章节中详细说明)

3.无符号数据可以比基本有符号数据大一倍，无符号int类型可以存储42亿行数据，对于一般应用程序来说足够了。

1id int unsigned not null auto _ increment comment ' id主键'

2primary key(id)、

3.必需列

规格：

必须包含Deleted、create_time和update_time数据列

解决：

1.掌门数据系统禁止物理删除，必须使用逻辑删除方法，必须在代码级别保留标记deleted字段

2.create_time获取记录创建时间，update_time获取记录更新时间，由数据库自动维护

3.创建要查询的create_time、update_time列索引

4.根据此规范设计的表大大方便了BI部门的增量数据同步，减少了同步操作中的数据量

1 deleted tinyint not null default 0 comment '是否删除0不删除1删除默认值为0 '

2 create _ time timestamp not null default current _ timestamp comment '记录创建时间，默认当前时间'

3 update _ time timestamp not null default current _ timestamp on update current _ timestamp comment '记录更新时间，默认当前时间'

4 index idx _ create _ time(create _ time)、

5 index idx _ update _ time(update _ time)、

4.大球场

规格：

尽可能不要使用blog和text等大字段

解决：

1.保存时：

实际数据存储在外部存储器上，数据列仅存储指向外部存储器的大字段指针。大字段需要额外的IO来存储实际数据。

2.查询时：

如果列包含blog和text列，则在查询时，请尽可能不要查询列，也不要使用select *查询数据。

要查询大型字段，必须通过指针查找外部存储库，然后读取字段内容。需要额外的IO来读取实际数据。

读取的大字段数据只能存储在磁盘上以供后续操作使用，效率低下。

5.线条长度，页面长度

规格：

MySQL定义的行长度不能超过64KB(blog、text类型除外)

页面长度的缺省值为16KB，由innodb_page_size参数定义

解决：

1.所有列

长度总定义不能超过 64KB ，超过后将无法添加新列

2. 当行长度增加，会导致单个页存放的数据行数减少，检索数据需要消耗更多磁盘 IO

备注：基于以上行长度和页长度的定义，也许有小伙伴会有疑问，如果表真的存放了 64KB 数据，那岂不是一行数据会占用四个数据页，一个数据页中只能存放一行数据（或一行数据的一部分）。那么此时 MySQL 的 B+ 树的数据存储结构就退变为线性了，这是 MySQL 设计中绝对不允许的！！！
按照 MySQL 的 B+ 树设计，一个数据页中至少要存放两行数据，否则 B+ 树会从树状结构退化为线性结构。
因此当行存储的实际数据过大，在页中存放不下时，MySQL 在存储这行记录的时候，会将较大的数据列的数据存放在外部存储页中，数据页只保存指向外部存储页的指针。此时如果存储读取这个大的数据列，会额外消耗更多的 IO

6.字段类型

规范：

根据字段存储内容定义适当的字段类型

解析：

1. 时间类型使用 date 、datetime 或者 timestamp ，不要使用 varchar 或者 int

2. ID 类型尽量使用 tinyint、smallint、int、bigint，不要使用 varchar

3. 定长字符串尽量使用 char，不要使用 varchar（注意 char 最大定义为 255 ）

4. 字段尽量设置为非空（ not null ），并设置 default 属性

5. varchar 类型按需定义长度，不要定义过长。

5.1. 字段过长会导致索引长度过长，超过一定长度只能创建前缀索引，而前缀索引不能走到索引覆盖

5.2. 数据读取到内存中是按照定义长度存放，过长的定义会占用更多内存空间

7.字段长度

规范：

不同字段类型占用的数据存储空间不同，应尽量控制字段在满足业务场景可用的前提下，长度越短越好

解析：

1.如果字段可以为空，则需要 1 个字节存放字段是否为空标识

2.如果字段为 varchar 变长类型，当定义小于 255 字节时，需要 1 字节存放字段长度；当定义超过 255 字节时，需要 2 字节存放字段长度

3.常见字段类型的长度（以下字段长度均为不为空时的字段长度）

3.1 tinyint 1 字节；smallint 2 字节；int 4 字节；bigint 8 字节；

3.2 date 3 字节；datetime 8 字节；timestamp 4 字节；

3.3 字符类型括号中定义的数字为字符数，即 varchar(32) 可以存放 32 个数字或者汉字

3.4 字符类型长度跟字符集有关（其中 utf8 占用 3 字节，utf8mb4 占用 4 字节）

varchar(10) 类型（utf8mb4）字节数： 10*4B+1B=41B

varchar(100) 类型（utf8mb4）字节数：100*4B+2B=402B

char(10) 类型（utf8mb4）字节数：10*4B=40B

二、索引

上文中把数据表比作是一本本的书，那么索引就是好比书本的目录。如果想要快速从书本中提取某项内容，那么查找目录必定是最快捷的。

MySQL 的索引是一把双刃剑，一方面能大幅提高查询速度，但同时索引也需要额外的存储，并且索引的维护是有代价的，每一行数据的增删改都需要维护索引信息，因此索引并不是多多益善，而应该按需创建合理的索引。

1.索引目的

建立索引的目的是为了减少扫描范围，提高查询速度。通过索引快速锁定数据范围，返回需要结果。一般情况下，如果单次扫描行数超过数据表总行数的一定比例（预估 10% 左右，官方并没有提供一个确定的数值），查询可能会放弃索引走全表扫描。因此必须要控制查询的数据范围，这也是我一直强调的，如果不能限制数据查询范围，那么所有的优化都是徒劳的。

由于数据增删改都会额外维护索引信息，索引过多会降低数据表的 DML 速度，因此只在经常查询并且选择性高的列上创建索引。

2.索引原理

通过索引(目录)方式，快速提取(查找)需要的记录。

MySQL 数据存放在磁盘上，索引结构为 B+ 树，索引即通过 B+ 树实现快速的从磁盘中读取所需要的数据（后面章节会详细介绍 B+ 树结构）

生活中随处可见索引的例子，如火车站的车次表、图书的目录、字典的查找等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，来提高检索效率。数据库索引也是类似的原理

3.索引长度

索引的长度决定不仅决定了索引占用的数据空间大小，也会影响查找数据的 IO 次数。

在同等数据量下，索引长度过长会导致单个数据页存放的索引条目数减少，索引高度增加，磁盘 IO 增加，并且索引占用空间增大。所以应该在满足要求的前提下，尽量减少索引长度。

索引的长度限制及计算方式如下：
1.索引最大长度为 767 字节，若索引长度超过 767 字节将无法创建（可考虑创建前缀索引）
2.索引长度与字段定义长度基本相同，前缀索引长度与定义的前缀长度有关
3.变长类型如 varchar，额外需要 2 个字节存放索引长度
4.如果字段可以为空，额外需要 1 个字节存放为空标识
5.索引长度 = 字段长度 + 是否为空(+1) + 是否变长(+2)

可通过执行 explain 查看执行计划，key 字段记录查询走哪个索引，key_len 记录所走索引的长度信息

如上表中，name 字段索引 uni_name 的 key_len 长度为 43 ，具体长度是按照以下方式计算规则：

索引长度 = 字段长度 + 是否为空(+1) + 是否变长(+2)

如 name 字段 varchar(10) 可以为空，字符集为 utf8mb4 ，则索引长度为： 10*4B+1B+2B=43B

而 a2 字段 int 可以为空，索引长度为：4B+1B=5B

4.聚集索引和非聚集索引

聚集索引

一个表只能有一个聚集索引，如果有主键列，则主键列为聚集索引；如果没有主键，有非空唯一列，则以第一个非空唯一列为聚集索引；否则数据库选择内部隐藏 6 字节的 ROWID 作为聚集索引。数据表的数据按照主键的顺序存放，因此对于 MySQL 顺序写入场景下，创建无意义的自增 ID 是最合适的。

聚簇索引：物理存储按照聚集索引列排序，聚集索引叶子节点 data 中存放完整行数据。

非聚集索引

一个表可以有多个非聚集索引，创建聚集索引只为提高查询效率。通过非聚集索引查找的是聚集索引指针信息，还需要通过回表方式查找具体的数据。所谓回表，是指通过普通索引获取到的聚集索引指针信息，再在聚集索引中执行一次 B+ 树查找，获取到最终的行数据。

非聚簇索引：非聚集索引列是逻辑排序，与实际数据的物理存储顺序不同。非聚集索引叶子节点 data 中存放聚集索引信息。

5.磁盘 IO 和预读

MySQL 的数据都是存储在磁盘上的，磁盘的查找方式是怎样的？查询速度怎样呢？如何才能快速的从磁盘中拿到所需要的数据。在介绍 B+ 树之前，我们先了解一下磁盘 IO 和预读机制。

磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分：

1.寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在 5ms 以下；
2.旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘 7200 转，表示每分钟能转 7200 次，也就是说 1 秒钟能转 120 次，旋转延迟就是 1/120/2 = 4.17ms；
3.传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。

那么访问一次磁盘的时间，即一次磁盘 IO 的时间约等于 5+4.17 ≈ 9ms 左右，听起来还挺不错的，但要知道一台 500 -MIPS 的机器每秒可以执行 5 亿条指令，因为指令依靠的是电的性质，换句话说执行一次 IO 的时间可以执行 40 万条指令，数据库动辄十万百万乃至千万级数据，每次 9 毫秒的时间，显然是个灾难。

考虑到磁盘 IO 是非常高昂的操作，计算机操作系统做了一些优化，当一次 IO 时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内。局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次 IO 读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为 4k 或 8k ，也就是我们读取一页内的数据时候，实际上才发生了一次 IO，这个理论对于索引的数据结构设计非常有帮助。

6.B+树

了解完操作系统的磁盘 IO 和预读机制，我们知道磁盘 IO 是十分耗时的操作，并且一次 IO 拿到的并不是单条数据，而是预读到的操作系统 page 大小的数据（ 4k或者8k ）。

而 MySQL 数据库的 page 大小默认为 16k ，即一次 IO 预读 16k 的数据到数据库的 buffer pool 中。然后在内存中对数据进行过滤，内存中的数据过滤非常快，与磁盘 IO 时间相比时间可忽略不计。

那么好了，如何降低磁盘 IO 是数据库设计优化的重点。而 B+ 树就是为了降低磁盘 IO ，提高数据库查询效率而生。

备注：索引树的高度跟磁盘 IO 次数呈正相关，可简单理解为索引树高度即是磁盘 IO 次数。

谈到 B+ 树这个概念前，我们可以稍微了解一下二叉树、平衡二叉树、红黑树，B 树，这些树都有不同的数据结构，应用于不同的数据场景。MySQL 数据库选择 B+ 树作为数据存储结构，那么相比其他树，B+ 树有何优势呢？

备注：B+ 树是在 B 树上优化衍生而来，本章节我们主要谈谈 B+ 树与 B 树的区别及优势
1.B 树的非叶子节点会存放数据，导致一个页存放的索引数较少，索引树较高
B+ 树的非叶子节点不会存放数据，只存放键值，一个页可以存放更多索引，索引树较矮
2.B 树的查找可能会在非叶子节点命中，查找不稳定
B+ 树的查找必须到叶子节点才会命中，查找十分稳定
3.B 树的范围遍历效率非常低
B+ 树的叶子节点中存放有双向指针构成一种链表结构，范围查询效率非常高效。而数据库的范围查询十分常见

因此 B+ 树更加适合作为 MySQL 数据库的数据存储结构，下图是一个 B+ 树的结构图

备注：在 MySQL 的 B+ 树结构中，聚簇索引和非聚簇索引存储稍有差异
聚簇索引的 data 部分，存储的是具体的行数据
非聚簇索引的 data 部分，存储的是主键 ID 信息

7.索引高度

既然磁盘 IO 是最影响性能的操作，那么优化的目的就是为了减少磁盘 IO 。而索引高度与磁盘 IO 是息息相关的，按照 MySQL 的索引设计，索引的高度就决定了磁盘 IO 的次数。那关于索引的高度，我们需要了解以下其计算规则

8.索引存储条目数

很多参考文档都会告诉你，索引的高度一般都在 3-4 层，但到底是 3 层还是 4 层呢？3 层索引和 4 层索引又能存放多少索引量，很多文档又会含糊描述的不清楚。此处我们根据实际例子，来看以下具体的索引能存储多少索引量

假设：表平均行长度为 300B，主键索引列 ID 为 int 类型
普通索引列 name 为 varchar(20) 类型非空 (utf8mb4)
普通索引列 info 为 varchar(150) 类型可以为空 (utf8mb4)
分别了解主键索引和普通索引在不同类型下的最大能存储条目数
主键索引的叶子节点中存放具体行数据；普通索引的叶子节点中存放主键信息
备注：索引存储中除索引信息外，还会用 4B 存储页号，6B 存储其他数据

主键索引 ID（int），索引长度 4B

1每个非叶子节点可存放 key 个数为 M1=16KB/(4B+4B+6B)≈1170
2每个叶子节点可存放 key 个数为 M2=16KB/(300B+4B+6B) ≈52
3则 3 层索引最终能存放最大条目数为：L=1170*1170*52 ≈7118W
4 4 层索引最终能存放最大条目数为：L=1170*1170*1170*52 ≈832亿

普通索引 name（varchar(20)），非空，索引长度 20*4B+2B

1每个非叶子节点可存放 key 个数为 M1=16KB/(20*4B+2B+4B+6B)≈178
2每个叶子节点可存放 key 个数为 M2=16KB/(4B+20*4B+2B+4B+6B) ≈170
3则 3 层索引最终能存放最大条目数为：L= 178*178*170 ≈538W
4 4 层索引最终能存放最大条目数为：L= 178*178*178*170 ≈9.58亿

普通索引 info（varchar(150)），可为空，索引长度 150*4B+2B+1B

1每个非叶子节点可存放 key 个数为 M1=16KB/(150*4B+2B+1B+4B+6B)≈26
2每个叶子节点可存放 key 个数为 M2=16KB/(150*4B+2B+1B+4B+6B+4B) ≈26
3则 3 层索引最终能存放最大条目数为：L= 26*26*26 ≈17576
4 4 层索引最终能存放最大条目数为：L= 26*26*26*26 ≈456976
5 5 层索引最终能存放最大条目数为：L= 26*26*26*26*26 ≈1188W

总结：

可以发现当索引长度增加时，会导致每个 page 页存放的索引数减少，索引高度增加。

特别是 char/varchar 类型，索引长度会因为 utf8mb4 字符集原因导致索引长度急剧增长，因此需要严格控制字段和索引长度。

9.索引优化

针对索引的优化，主要在于选择合适的列创建最优的索引，删除无效或者重复索引：

1.列的选择性越高，越适合创建索引。列的选择性计算 count(distinct coumn_name)/count(0)

2.选择性低的列上不要添加索引，如 bu，type，status，state，deleted 等列，索引列可能会导致查询效率下降

3.通过 Show index from table_name，查看表上的索引信息，cardinality 越大选择性越高

4.前缀索引。如果索引列太长，可考虑创建前缀索引 index idx_name(column_name(pre_len)) 达到 pre_len 的条数，如果达到总条数的 80% 即可

备注：前缀索引无法走索引覆盖

1如 lessons 表的 les_uid 字段，定义为
2`les_uid` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
3但是其实该字段存放的时 uuid 信息，长度为固定的 32 位，该字段如需创建索引，则可考虑前缀索引，指定前缀长度为 32
4index idx_les_uid(les_uid(32)),

5.组合索引。多列查询条件一起出现，可创建组合索引。并将经常查询列放前面，选择性高的放前面

如 index(A,B,C) 相当于建了 index(A,B,C)，index(A,B)，index(A) 三个索引

备注：组合索引的列数不宜过多，一般 2-3 列即可。列过多很难用到后面的列，且会增加索引长度。

1如经常查询需要查询学生在某一个时间范围的上课信息，可创建基于 stu_id 和 les_start_time 的组合索引
2index idx_stu_id_start_time(stu_id,les_start_time)

6.索引覆盖。当需要查询的列都在索引中，通过扫描索引即可获取所有信息。不需要回表操作，效率较高

7.索引合并。在 MySQL 5.0 之前，一个表最多只能使用一个索引，从MySQL 5.1 开始引入索引合并（index_merge）技术优化，对同一个表可以使用多个索引分别进行条件扫描，然后将各自的结果进行合并（intersect/union）。

备注：如果单列索引创建的不合理，比如在 bu、state、status 等列上创建单列索引，当 index_merge 使用到这些列做索引扫描合并，那么查询效率会非常低，需要关注！！！产生这种问题的主要原因是在选择性低的列上创建了索引，选择性低的列不适合创建索引，可能会降低查询效率！！！

1index_merge之using union:
2mysql> explain
3select count(0)
4from lessons
5where stu_id=116401
6or sel_id=1113;
7+----+-------------+---------+-------------+----------------------------------------------------------------------------------------+-----------------------------------+---------+------+------+-------------------------------------------------------------+
8| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
9+----+-------------+---------+-------------+----------------------------------------------------------------------------------------+-----------------------------------+---------+------+------+-------------------------------------------------------------+
10| 1 | SIMPLE | lessons | index_merge | lessons_stu_id,idx_stuid_lessub,IDX_LES_TYPE_STU_ID,stu_id_pay_type,idx_lessons_sel_id | lessons_stu_id,idx_lessons_sel_id | 4,5 | NULL | 1467 | Using union(lessons_stu_id,idx_lessons_sel_id); Using where |
11+----+-------------+---------+-------------+----------------------------------------------------------------------------------------+-----------------------------------+---------+------+------+-------------------------------------------------------------+
121 row in set sec)
13查询使用 or 连接多个条件，查询分别使用 stu_id 和 sel_id 列索引进行扫描，并将扫描结果进行取并集(union)
14
15index_merge之using intersect:
16mysql> explain
17select count(0)
18from lessons
19where stu_id=116401
20and sel_id=1113;
21+----+-------------+---------+-------------+----------------------------------------------------------------------------------------+-----------------------------------+---------+------+------+------------------------------------------------------------------------------+
22| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
23+----+-------------+---------+-------------+----------------------------------------------------------------------------------------+-----------------------------------+---------+------+------+------------------------------------------------------------------------------+
24| 1 | SIMPLE | lessons | index_merge | lessons_stu_id,idx_stuid_lessub,IDX_LES_TYPE_STU_ID,stu_id_pay_type,idx_lessons_sel_id | lessons_stu_id,idx_lessons_sel_id | 4,5 | NULL | 1 | Using intersect(lessons_stu_id,idx_lessons_sel_id); Using where; Using index |
25+----+-------------+---------+-------------+----------------------------------------------------------------------------------------+-----------------------------------+---------+------+------+------------------------------------------------------------------------------+
261 row in set sec)
27查询使用 and 连接多个条件，查询分别使用 stu_id 和 sel_id 列索引进行扫描，并将扫描结果进行取交集(intersect)

8.重复索引。重复的索引会需要更多的存储更见和维护代价，可考虑删除重复索引。

如索引 index(A) 和 index(A,B) 是重复的，重复的索引需要更多的存储空间和维护代价，可考虑删除 index(A)。

三、SQL语句

前面提到的建表和索引都是数据库优化中的基础，是非常关键的优化点，只有把基础打好，才能在此基础上做更深层次的优化。而 SQL 优化则是上层建筑，是查询的最终表现，通过 SQL 语句的执行计划、扫描行数、查询时间也能直观的反映出查询语句是否优秀。

SQL 语句的不同写法，对于底层数据的检索方式有着非常大的差异，也会影响查询方式和最终的数据结果。SQL 优化是在不改变结果的前提下，优化语句，降低扫描数据范围，达到缩短查询时间和扫描行数的目的。如果查询逻辑不合理，无法通过改写 SQL 达到优化的目的，那么则需要考虑调整查询逻辑。

1.执行计划

SQL 语句的执行顺序真实的记录了解释器会如何一步步的执行语句，了解执行计划对于我们分析 SQL 语句，优化语句有非常大的作用

我们可以通过 explain + SQL 可以查看语句的执行计划。但是执行计划包含的内容太多，如果详细描述，又能整一篇文章了。因此本文只对我们比较关心的地方做重点叙述，简洁而又核心~

Id：id 列数字越大越先执行，数字一样则从上而下执行
Type：依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL，除了 all 之外，其他的 type 都可以使用到索引，如果 type 为 all 就需要关注并优化 SQL 了。
Key：查询真正使用到的索引，select_type 为 index_merge 时，这里可能出现两个以上的索引，其他的 select_type 这里只会出现一个
Key_len：处理查询的索引长度，前文中已经介绍过 key_len 的计算规则。ICP 特性使用的索引不会计入
Rows：这里是执行计划中估算的扫描行数，不是精确值
Extra：如果你想要优化你的查询，那就要注意 extra 辅助信息中的 using filesort 和 using temporary ，这两项非常消耗性能，需要注意。

2.表关联

MySQL 数据表关联采用 Nested-Loop 方式，又名嵌套循环，是 MySQL 中最重要的表关联方式。在 Mysql 8.0 之前，也是仅有的一种表关联方式。想要优化查询语句，对于表关联方式必须要十分清楚。MySQL 在 Nested-Loop Join 的基础上，优化出 Block Nested-Loop Join 关联，两者比较类似。

2.1.Nested-Loop Join

一个简单的嵌套循环联接 (NLJ) 算法一次从循环中的第一个表读取一行，将每一行传递到一个嵌套循环，该循环处理联接中的下一个表。只要还有需要连接的表，这个过程就会重复多次。由于 NLJ 算法每次从外层循环传递一行数据到内存循环，因此循环会重复很多次。

一个简单的 BLJ 算法执行过程如下：

1for each row in t1 matching range {
2 for each row in t2 matching reference key {
3 for each row in t3 {
4 if row satisfies join conditions, send to client
5 }
6 }
7}

2.2.Block Nested-Loop Join

块嵌套循环 (BNL) 连接算法使用缓冲在外部循环中读取的行，以减少必须读取内部循环中的表的次数，（其中 join_buffer_size 参数决定每次读取并放入缓存中的数据量）。例如，如果将 10 行读入一个缓冲区并将该缓冲区传递给下一个内部循环，则可以将内部循环中读取的每一行与缓冲区中的所有 10 行进行比较。这可以将必须读取内部表的次数减少一个数量级。

一个 BNL 算法执行过程如下：

1for each row in t1 matching range {
2 for each row in t2 matching reference key {
3 store used columns from t1, t2 in join buffer
4 if buffer is full {
5 for each row in t3 {
6 for each t1, t2 combination in join buffer {
7 if row satisfies join conditions, send to client
8 }
9 }
10 empty join buffer
11 }
12 }
13 }
14if buffer is not empty {
15 for each row in t3 {
16 for each t1, t2 combination in join buffer {
17 if row satisfies join conditions, send to client
18 }
19 }
20}

2.3.总结

不管是 NLJ 或者是优化后的 BNL 算法，都是从驱动表中筛选数据，与被驱动表进行匹配，有以下特性：

MySQL 优化器会自动选择小表作为驱动表，以减少循环的次数。但并不会只以表大小作为唯一选择依据
最好不要干涉数据库的驱动表选择，让 MySQL 优化器自动选择最合适的表作为驱动表
查询会首先通过查询条件，过滤驱动表上的数据记录，筛选满足要求的结果放入缓存中。驱动表上需尽量通过索引扫描降低数据量。
被驱动表的关联字段上，需要有索引，否则每一次关联，被驱动表都要全表扫描，效率非常低。
被驱动表的字段类型、字符集、排序方式需和驱动表保持一致，否则无法直接关联
使用外关联时，只有基准表才能被选择为驱动表
如 A LEFT JOIN B ON A.KEY=B.KEY ,则只有 A 表才能被选择为驱动表，B 表不能作为驱动表

3.SQL 优化

SQL 优化的方式方法很多，此处罗列一些比较重要，容易出现问题的优化点：

尽量避免全表扫描，应考虑在 where 和 order by 涉及的列上建立索引
被驱动表的关联字段需要创建索引，否则被驱动表会走全表扫描
索引遵循最左前缀匹配原则，like 写法只能将 % 放在右边，如 name like ‘掌门学员%’；若 % 放在左边会导致索引失效
应尽量避免在 where 子句中使用 != ， <> ，not in 操作符，会导致索引失效
应尽量避免在 where 子句中使用 or 来连接条件，可尝试拆分为 union/union all
可考虑使用 join/left join 关联查询，替代子查询、in/not in 的写法，尽量不要用子查询
如果 in 的内容是连续的，可使用 between…and 或者 >….< 替代，改走范围扫描
不要在查询字段上使用函数或者表达式，会导致索引失效，可在参数字段上做函数或表达式运算
查询时需根据字段定义类型进行传参，若参数类型与字段定义类型不一致，会导致索引失效
不要使用 select * 写法，只查询需要的列
不要在数据库中使用变量 + 分组排序方式构造排序字段，MySQL 需要基于全量数据做排序分组，效率很低
通过 limit 方式分页会导致后续分页越来越慢，可取前一次分页的最大 ID 作为下一页参数输入，进行分页
不要通过 order by rand() 方式取随机数，效率极低。如果需要取随机数，可以先用随机数方法取得一个整数，然后根据 id>= 该整数即可。
禁止不必要的排序，排序操作极耗资源，不要轻易分组排序
不要在程序中使用 using index 强制索引写法

4.SQL 优化示例

枯燥的知识点，哪有举个例子来的更清晰易懂，本章节我们用具体的例子讲述 SQL 优化的方法跟技巧。

4.1.使用 union/union all 替换 or 写法

MySQL 的 or 写法，会导致查询索引失效，而更换为 union/union all 写法，虽然代码长度会增加，但是查询效率会有很大的提升

优化：

使用 union/union all 写法代替 or。鉴于 or 的关联记录可能存在重复，使用 union 写法，在外层对结果进行 count

4.2.使用 join 关联替换子查询、in、exists 写法

子查询的写法虽然看起来直观清晰，但是子查询是一个独立的查询，不能参与到驱动表的数据过滤，而且子查询的结果数据会被放到临时表中存放，然后与驱动表进行关联，效率非常低。

在 Mysql 的写法中，非常不建议子查询写法，而应该尽量用 join 方式替换子查询写法。

优化：

不使用子查询，直接使用 join 进行多表关联，并将查询条件写入到 where 中。

4.3.使用 join/left join 替代 in/exists、not in/not exists 写法

join 写法相当于做等值匹配，可以直接替代大部分场景的 exits 和 in 的写法

left join/right join 外关联的写法会以驱动表为母表，被驱动表只包含匹配的数据，配合在 where 条件中添加条件筛选，可用来替代 not exist 和 not in 写法

优化：

直接用 join/left join 的写法替代，效率将会有大幅提升

1mysql> explain
2select u.id
3from users u
4left join users_account_number a on u.id=a.user_id
5where u.updated_at > '2021-05-20 12:00:00'
6and a.user_id is null;
7+----+-------------+-------+--------+----------------+----------------+---------+--------------+--------+--------------------------------------+
8| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
9+----+-------------+-------+--------+----------------+----------------+---------+--------------+--------+--------------------------------------+
10| 1 | SIMPLE | u | range | idx_uptime | idx_uptime | 6 | NULL | 105958 | Using where; Using index |
11| 1 | SIMPLE | a | eq_ref | user_id_unique | user_id_unique | 4 | zm-u | 1 | Using where; Not exists; Using index |
12+----+-------------+-------+--------+----------------+----------------+---------+--------------+--------+--------------------------------------+
132 rows in set sec)
14说明：查询 select_type 都变为 simple ，并且查询先基于 updated_at 做索引过滤，然后与 users_account_number 进行匹配，效率非常高

4.4.根据字段类型合理传参

在 MySQL 中，如果参数类型与字段定义类型不一致，会导致查询无法走到索引，这点需要关注。

优化：

根据字段类型进行合理的传参，如果 mobile 为 varchar 类型，则在参数上添加引号''标识为字符类型

4.5.不要在查询字段上使用函数或表达式

如果在查询字段上使用函数或者表达式，MySQL 会首先对查询字段做函数运算，如果原本是准备基于该字段做索引匹配，函数运算会导致索引失效

优化：

不要在查询字段上使用函数，如果需要使用函数，那么函数可以用在参数列上

4.6.防止分页过大导致查询越来越慢，可使用id优化

在程序开发中，经常会对大量的返回结果进行分页返回展示，但是当分的页数过大，后续的分页查询会越来越慢，例如 limit 100 和limit 1000000,100 的查询效率差别很大。

优化：

MySQL 更适合于精确查询，如果满足条件的结果很多，是不合理的，应该通过范围限制使得满足条件的结果足够小，最终结果应该控制在 1000 条、100 条甚至 10 条以内。

但是如果一定要对大量结果数据分页，那么可以考虑根据 ID 进行适当的范围限制

4.7. where + order by + limit 查询陷阱

在程序设计中，经常需要用到 where+order by+limit 写法获取满足条件的、排序后的 N 条数据结果。这种写法本身并没有问题，但是在实际使用中，这条语句却常常引起严重的性能问题，需要我们重点关注。

敲黑板咯，拿出小本本~

当查询语句中包含 where+order by+limit 时

由于 MySQL 优化器会认为排序 order by 是非常耗时的操作，如果能在一开始就将结果做排序返回，那是最好的。而且 limit 100 会让优化器认为这 100 条记录是很容易满足的，此时优化器会走如下执行计划：

1.由于字段本身就有索引（已排序），按照 updated_at 字段每次取 100 条结果，然后走 where 匹配，将满足的结果返回

2.重复以上操作，直到有 100 条结果满足要求，循环结束

3.如果查询按照排序的数据经过 where 条件过滤后能快速满足 100 条结果输出，那么查询或许会非常快

4.但如果一致没有取到满足 where 条件的 100 条结果，会一直循环操作直至遍历 students 整个表！那这个代价是非常恐怖的

优化：

可以有很多办法避免以上执行计划与预想不一致的情况，本文列举两种办法：

方法1.

order by 的字段调整为与查询条件中的字段一致，但是可能造成结果并非原始期望结果，需要沟通业务部门是否能接受该 SQL 改造。（最优）

方法2.

将内部查询使用括号包裹，强制其作为一个先导执行的子查询，然后对子查询的最终结果集进行排序返回。由于子查询中会存放所有满足条件的结果，并且进行文件排序，如果满足条件的结果非常大，该方法会消耗较多资源效率较低。（亦可）

优化方案1：

优化方案2：

5.掌门慢SQL优化示例

上文中讲到的优化办法和优化示例，各位掌门人如能融会贯通，SQL 水平必定能更上一层楼。掌门生产环境中的 SQL 为满足业务功能，可能会关联较多的数据表，写的十分复杂，但是再复杂的查询，也是一个个的 Nested-Loop 关联。按照上述方法改写也能有优化效果，下面就是对掌门线上慢 SQL 的优化示例：

SQL1：数据仓库中优课 BU 的查询服务

1mysql> EXPLAIN SELECT
2 count( 0 )
3FROM
4 `uke_hours`.uke_retire_record rr
5 JOIN `uke_hours`.uke_apply_refund_info ari ON rr.apply_refund_id = ari.id
6 JOIN `uke`.uke_student stu ON rr.user_id =
7 LEFT JOIN `forge`.students students ON = ari.stu_user_Id
8 JOIN `zm-user`.users users ON rr.user_id = u
9 LEFT JOIN `forge`.sellers se ON = rr.handel_user
10 JOIN (
11 SELECT
12 ard.apply_refund_id
13 FROM
14 `uke_hours`.uke_apply_refund_detail ard,
15 `uke`.uke_class c
16 WHERE
17 ard.prod_id = c.prod_id
18 AND c.is_deleted = 0
19 GROUP BY
20 ard.apply_refund_id
21 ) ard ON rr.apply_refund_id = ard.apply_refund_id
22WHERE
23 rr.is_new_data = 1
24 AND (
25 rr.cc_id IN ( 1071853472 )
26 OR rr.cr_id IN ( 1071853472 )
27 OR rr.team_user_id IN ( 1071853472 )
28 OR rr.apply_user_id IN ( 1071853472 ));
29+----+-------------+------------+--------+-----------------------------------------------------------------------+-----------------------------+---------+---------------------------+--------+---------------------------------+
30| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
31+----+-------------+------------+--------+-----------------------------------------------------------------------+-----------------------------+---------+---------------------------+--------+---------------------------------+
32| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 145787 | NULL |
33| 1 | PRIMARY | rr | ref | idx_apply_refund_id,idx_user_id,idx_apply_user_id,idx_cc_id,idx_cr_id | idx_apply_refund_id | 8 | ard.apply_refund_id | 1 | Using where |
34| 1 | PRIMARY | ari | eq_ref | PRIMARY | PRIMARY | 8 | ard.apply_refund_id | 1 | Using where |
35| 1 | PRIMARY | se | eq_ref | sellers_user_id | sellers_user_id | 4 | uke_ | 1 | Using where; Using index |
36| 1 | PRIMARY | stu | ref | idx_userId_isDeleted_unique | idx_userId_isDeleted_unique | 8 | uke_ | 1 | Using index |
37| 1 | PRIMARY | students | eq_ref | user_id | user_id | 4 | uke_ | 1 | Using where; Using index |
38| 1 | PRIMARY | users | eq_ref | PRIMARY | PRIMARY | 4 | uke_ | 1 | Using where; Using index |
39| 2 | DERIVED | ard | ALL | idx_apply_refund_id,idx_prod_id | NULL | NULL | NULL | 145787 | Using temporary; Using filesort |
40| 2 | DERIVED | c | ref | idx_prod_version | idx_prod_version | 130 | uke_ | 1 | Using where |
41+----+-------------+------------+--------+-----------------------------------------------------------------------+-----------------------------+---------+---------------------------+--------+---------------------------------+
429 rows in set sec),实际执行时间 1.267s
43说明：该查询问题有二。
44其一，查询中用到子查询，id=2 的 derived 子查询走 type=all 得全表扫描，且因为 group by 语句需要走文件排序操作
45其二，查询条件使用到 or 条件，并且由于 or 的列太多,查询无法使用索引.只能基于 id=2 中子查询结果驱动整个查询

优化：

1.想办法用 join 方式将子查询部分改写，不要用子查询。

2.or 条件中不应该传入 cc_id、cr_id、team_user_id、apply_user_id 多种筛选条件，而应该根据实际传参精确匹配列

3.可以将 or 条件改写为 union 写法

1mysql> EXPLAIN SELECT
2 count( 0 )
3FROM
4 `uke_hours`.uke_retire_record rr
5 JOIN `uke_hours`.uke_apply_refund_info ari ON rr.apply_refund_id = ari.id
6 JOIN `uke`.uke_student stu ON rr.user_id =
7 LEFT JOIN `forge`.students students ON = ari.stu_user_Id
8 JOIN `zm-user`.users users ON rr.user_id = u
9 LEFT JOIN `forge`.sellers se ON = rr.handel_user
10 JOIN `uke_hours`.uke_apply_refund_detail ard on rr.apply_refund_id = ard.apply_refund_id
11 join `uke`.uke_class c on ard.prod_id = c.prod_id
12 AND c.is_deleted = 0
13WHERE
14 rr.is_new_data = 1
15 AND (
16 rr.cc_id IN ( 1071853472 )
17 OR rr.cr_id IN ( 1071853472 ) );
18+----+-------------+----------+-------------+-----------------------------------------------------+-----------------------------+---------+------------------------------+------+-----------------------------------------------+
19| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
20+----+-------------+----------+-------------+-----------------------------------------------------+-----------------------------+---------+------------------------------+------+-----------------------------------------------+
21| 1 | SIMPLE | rr | index_merge | idx_apply_refund_id,idx_user_id,idx_cc_id,idx_cr_id | idx_cc_id,idx_cr_id | 9,9 | NULL | 2 | Using union(idx_cc_id,idx_cr_id); Using where |
22| 1 | SIMPLE | ari | eq_ref | PRIMARY | PRIMARY | 8 | uke_ | 1 | Using where |
23| 1 | SIMPLE | se | eq_ref | sellers_user_id | sellers_user_id | 4 | uke_ | 1 | Using where; Using index |
24| 1 | SIMPLE | ard | ref | idx_apply_refund_id,idx_prod_id | idx_apply_refund_id | 8 | uke_ | 1 | NULL |
25| 1 | SIMPLE | c | ref | idx_prod_version | idx_prod_version | 130 | uke_ | 1 | Using where |
26| 1 | SIMPLE | users | eq_ref | PRIMARY | PRIMARY | 4 | uke_ | 1 | Using where; Using index |
27| 1 | SIMPLE | stu | ref | idx_userId_isDeleted_unique | idx_userId_isDeleted_unique | 8 | uke_ | 1 | Using index |
28| 1 | SIMPLE | students | eq_ref | user_id | user_id | 4 | uke_ | 1 | Using where; Using index |
29+----+-------------+----------+-------------+-----------------------------------------------------+-----------------------------+---------+------------------------------+------+-----------------------------------------------+
308 rows in set sec),实际执行时间 0.033s
31说明：改写后 where 查询中只保留 cc_id 和 cr_id ，由于都是 in 的等值查询，且列上均有索引条件，查询可以使用 index_merge 的优化特性，扫描行数为 2
32
33并且查询将子查询写法替换为 join ，使得原本子查询中的全表扫描，变为普通的表关联，查询效率得到极大提升。优化效率 38 倍

SQL2：market_audit 数据库的查询服务

1mysql> EXPLAIN
2SELECT
3 ,
4 userId,
5 scanCode,
6 ,
7
8FROM
9 t_screenshot ss
10 LEFT JOIN t_screenshot_analyze sa ON =
11WHERE
12 = 0
13 AND IS NULL
14 AND IN ( 3, 5, 4, 11, 12, 1, 2 )
15 AND ((
16 IS NOT NULL
17 AND >= '2021-05-14 11:30:00.145'
18 AND <= '2021-05-21 10:30:00.145' )
19 OR ( IS NULL
20 AND >= '2021-05-14 11:30:00.145'
21 AND <= '2021-05-21 10:30:00.145'
22 ))
23 AND (
24 != 2
25 OR IS NULL);
26+----+-------------+-------+--------+------------------------------------------------------+--------------------+---------+--------------------+---------+------------------------------------+
27| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
28+----+-------------+-------+--------+------------------------------------------------------+--------------------+---------+--------------------+---------+------------------------------------+
29| 1 | SIMPLE | ss | ref | idx_create_time,idx_re_upload_time,idx_re_apply_time | idx_re_upload_time | 6 | const | 1393993 | Using index condition; Using where |
30| 1 | SIMPLE | sa | eq_ref | PRIMARY | PRIMARY | 8 | market_audit. | 1 | Using where |
31+----+-------------+-------+--------+------------------------------------------------------+--------------------+---------+--------------------+---------+------------------------------------+
322 rows in set sec)
33说明：看解释计划似乎查询是走到 type=ref 的非唯一索引扫描，实则查询只能根据 IS NULL 这一个条件做简单的非空过滤
34按照表数据的组成，无异于全表扫描。该查询执行时间 4.87s

优化：

查询中条件基本都无筛选性可言。可以用作筛选条件的就是 re_apply_time 和 upload_time ，但是这个条件在查询中使用 or 连接

需要用 union 代替 or 的写法，使查询能分别走到 re_apply_time 和 upload_time 列的索引。

SQL3: forge 数据库的分页取数

优化：

针对这种循环取数，数据量很大的情况，需要根据 ID>last_max_id 的方式取数

结束语

MySQL 数据库博大精深，只有理解数据库底层原理，才能更好的做好优化工作。因此本文在介绍优化相关的规则和方法时，也会引申介绍相关数据库原理，也是希望帮助大家更好的理解并运用这些优化方法。

MySQL 是一个 OLTP 数据库，基于事务的增删改效率很高，基于索引的单值查询也非常快，MySQL 本身也非常适合这种短而快的数据操作或查询。

但是多表数据关联查询、查询返回大量数据结果，基于大量数据的统计或者排序操作，并不是 MySQL 数据库所擅长的。倘若不能减少数据扫描范围，那任何查询优化都是空谈。如果是这类 OLAP 数据需求，可以考虑一下其他架构设计，不能完全依赖 MySQL 数据库。

---------- END ----------

总结了很多有关于java面试的资料，希望能够帮助正在学习java的小伙伴。由于资料过多不便发表文章，创作不易，望小伙伴们能够给我一些动力继续创建更好的java类学习资料文章，
请多多支持和关注小作，别忘了点赞+评论+转发。右上角私信我回复【03】即可领取免费学习资料谢谢啦！

原文出处：