掌门 MySQL 数据库规约落地及优化实战

前言

MySQL 数据库承载了掌门绝大部分核心业务的数据存储，因此 MySQL 数据库的稳定运行至关重要。DBA 团队一直致力于保障数据库环境的平稳运行，编写有掌门 MySQL 数据库规范文档，也提供有掌门数据库查询上线运维平台。

规范虽然很全面，但是如果不了解数据库原理，不知道规范带来的效率提升，开发人员并不一定会严格遵守，规范也就失去了意义。

本篇文章旨在从数据库原理出发，从三个角度（建表、索引、SQL 语句）进行深入分析，在了解数据库底层原理的基础上，理解数据库规范、以及数据库优化方法。

一、建表

如果把 MySQL 数据库实例看作是一个图书馆，数据表就可以看作一本本的书。书名如同表名，概述用途，需要取的易懂且有意义；书本内容过多会选择分册发行，数据表也可以选择分表或者分区。

开发人员需要根据实际的业务场景、数据量大小设计不同的表，来满足业务需求。关系型数据库的数据字段后续更改代价较高，因此需要在前期设计阶段就需要考虑用途并设计合理的表结构。优秀的表结构设计，是数据库优化中非常重要的一环。数据表设计需要遵循以下规范要求：

1.字符集

规范：

数据库表字符集统一设置为 utf8mb4 ，排序规则为 utf8mb4_general_ci 。由于 DBA 已统一按照字符集 utf8mb4 ，排序规则 utf8mb4_general 创建数据库，因此表和字段的字符集可以不再额外设置，保持默认与数据库配置相同即可。

解析：

1.当字符集或者排序规则不一致时，会导致表无法关联查询

2. 如果进行字符转换，会导致索引失效，而且也会额外消耗数据库性能

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

1CHARSET=utf8mb4 COLLATE=utf8mb4_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部门进行增量数据同步，减少同步任务的数据量

1deleted tinyint not null DEFAULT 0 COMMENT '是否删除 0 未删除 1 删除 默认是0’,
2create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间，默认当前时间’,
3update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间，默认当前时间’,
4index idx_create_time(create_time),
5index 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 记录所走索引的长度信息

 1mysql> explain select * from t1 where name like '张三%';
 2+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
 3| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
 4+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
 5|  1 | SIMPLE      | t1    | range | uni_name      | uni_name | 43      | NULL |    1 | Using index condition |
 6+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
 71 row in set (0.05 sec)
 8mysql> show create table t1;
 9| Table | Create Table  
10| t1    | CREATE TABLE `t1` (
11  `id` int(11) DEFAULT NULL,
12  `name` varchar(10) DEFAULT NULL,
13  `age` int(11) DEFAULT NULL,
14  `cnts` int(11) NOT NULL DEFAULT '1',
15  `a1` varchar(10) DEFAULT NULL,
16  `a2` int(11) DEFAULT NULL,
17  `a3` varchar(32) DEFAULT NULL,
18  `a4` varchar(32) NOT NULL DEFAULT '',
19  `a5` int(11) DEFAULT '1',
20  UNIQUE KEY `uni_name` (`name`),
21  KEY `idx_a2` (`a2`)
22) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

如上表中，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