高性能MySQL的实现策略_mysql实现

1.基础架构

1.1.网络连接层

位于最上层的客户端服务，包括连接处理、身份验证等功能，支持多种服务端语言，通过 API 接口与 MySQL 建立连接。

---

1.2.数据库服务层

MySQL 的核心功能都在这一层，包括查询解析、分析、优化，以及所有的内置函数(例如:日期、数学函数)，所有跨存储引擎的功能也都在这一层实现：存储过程、触发器、视图等。

1.2.1.连接管理:

每个客户端连接都会在服务器进程中拥有一个线程，该连接的查询只会在这个单独的线程中执行，该进程驻留在内核或者 CPU 上。服务器维护了一个缓冲区，作为连接池，存储已就绪的线程。

1.2.2.优化与执行:

MySQL 解析查询以创建解析树，然后对其进行各种优化，包括重写查询，决定表的读取顺序，以及选择合适的索引等。

---

1.3.存储引擎层

存储引擎负责 MySQL 中数据的存储和提取，服务器通过存储引擎 API 进行通信。这些 API 屏蔽了不同存储引擎之间的差异，使得它们对上面的查询层基本上是透明的。最常用的存储引擎是 InnoDB。

---

1.4.系统文件层

1.4.1.binlog

• binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用，用于归档；
• binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”；binlog 是可以追加写入的，不会覆盖之前的日志；

1.4…2.redo log

• redo log 是 InnoDB 引擎实现的，用于 Write-Ahead Logging(提升效率)、crash-safe(故障恢复)；
• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；redo log 是循环写的，空间固定会用完；

1.4…3.undo log

• undo log 提供了回滚和多版本控制(MVCC)功能；
• unlo log 用于存放数据被修改之前的值；

---

2.Schema 设计

2.1.数据类型选择

• 更小的通常更好：尽可能使用能够正确存储和表示数据的最小数据类型。更小的数据类型通常更快，因为它们占用的磁盘、内存和 CPU 缓存的空间更少，并且处理时需要的 CPU 周期也更少；
• 简单为好：简单数据类型的操作通常需要更少的 CPU 周期。例如，整型数据比字符型数据的比较操作代价更低（因为字符集和排序规则使得字符型数据的比较更加复杂）；应该将日期和时间存储为 MySQL 的内置类型而非字符串类型；
• 尽量避免存储 NULL：通常情况下最好指定列为 NOT NULL，除非明确需要存储 NULL 值。因为可为 NULL 的列，对 MySQL 来说更难优化，在索引、索引统计和值比较时都更复杂；在存储时需要特殊处理，会占用更多的存储空间；（在调优时通常没有必要将可为 NULL 的列改成 NOT NULL，带来的性能提升比较小）

---

2.2.整数类型

整数的类型有：TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT，分别使用 8、16、24、32、和 64 位存储空间，可以存储的值的范围从 -2 的(n-1)次方到 2 的(n-1)次方-1，其中 n 是存储空间的位数。

整数类型有可选的 UNSIGNED 属性，表示不允许负值，大致上可以使正整数的上限提高一倍。

MySQL 可以为整数类型指定宽度，只是规定了 MySQL 的客户端显示字符的个数，不会限制值的合法范围。

---

2.3.实数类型

实数是带有小数部分的数字。也可以使用 DECIMAL 存储比 BIGINT 还大的整数，MySQL 既支持精确类型，也支持不精确类型。

FLOAT 和 DOUBLE 类型支持使用标准的浮点运算进行近似计算。FLOAT 列使用 4 字节的存储空间，DOUBLE 占用 8 字节。

DECIMAL 需要额外的存储空间和计算成本，尽量在对小数需要进行精确计算时进行使用，例如：存储财务数据。在一些大容量的场景，可以考虑使用 BIGINT 代替 DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可，可以避免浮点存储计算不精确和 DECIMAL 精确计算代价高的问题。

---

2.4.字符串类型

VARCHAR：

• 用于存储可变长度的字符串，比固定长度的类型更节省空间，因为它仅使用必要的空间；
• 但是由于行是可变长度的，在更新时可能会增长，将导致额外的工作；
• VARCHAR 需要额外使用 1 或者 2 字节记录字符串的长度；
• 适用场景：字符串的最大长度远大于平均长度；列的更新很少；

CHAR：

• CHAR 是固定长度的，非常适合存储较短的字符串，或者所有值的长度都几乎相同的情况；
• 对于经常修改的数据，CHAR 比 VARCHAR 更好，因为固定长度的行不容易出现碎片；

BLOB 和 TEXT 类型：

• BLOB 和 TEXT 类型是为了存储很大的数据而设计的字符串数据类型，分别采用二级制和字符串方式存储；
• MySQL 将 BOLO 和 TEXT 值当作具有自己标识的对象来处理，内容太多时会使用外部存储区域；

---

2.5.日期和时间类型

DATETIME:

• 可以保存从 1000 年到 9999 年范围内的数值，精度为 1 微秒；
• 以 YYYYMMDDHHMMSS 格式存储压缩成整数的日期和时间，与时区无关，需要 8 字节的存储空间；

TIMESTAMP：

• 与 UNIX 时间戳相同，存储自 1970 年 1 月 1 日以来经过的秒数，使用 4 字节存储空间，只能表示 1970 年 2038 年范围内的数据；
• 时间戳显示的值依赖于时区，MySQL 服务器、操作系统和客户端连接都有时区设置；

---

2.6.JSON 数据类型

• 相比较于 SQL 列，JSON 列需要占用过多的存储空间，有额外的字符需要存储；
• 查询速度上，SQL 列也更占优势；是否使用 JSON，取决于在数据库中使用 JSON 的便捷性是否大于性能；

---

2.7.其他数据类型

BIT：

• 可以使用 BIT 列存储一个或者多个 true/false 值；在存储时不会节省任何存储空间；
• MySQL 在处理时将 BIT 视为字符串类型，在数字上下文中进行数据比较时会转换为数字，得到非预期结果，使用时需要谨慎；
• 建议使用 TINYINT 进行代替；

IP：

• IP 地址实际上是无符号整数，用小数点分隔只是为了方便阅读，因此应该将 IP 地址存储为无符合整数；
• MySQL 提供了 INET_ATON()和 INET_NTOA()函数在这两种表达形式之间进行转换；

---

2.8.schema 管理

如何在不影响数据库或者依赖它们的服务正常运行的情况下，进行 schema 的变更？

原生 DDL 语句：

MySQL 在 5.6 版本中引入了非阻塞的 schema 更改，对原生 DDL 的支持有限，在需要更改的表非常大时，可能会遇到回滚的情况。

外部的工具：

主要选择有 Percona 的 pt-online-schema-change 和 Github 的 gh-ost。实现原理是对正在更改的表进行完整的复制，执行完表结构变更，再把源表的全量数据和增量数据同步过去，最后进行表替换。

---

3.索引

索引是存储引擎用于快速找到记录的一种数据结构。

3.1.索引的类型

B-tree 索引：

• 是指使用 B-tree 数据结构来存储数据，在实际实现中，很多存储引擎使用的是 B+ Tree 索引；
• B-tree 索引能够加快数据访问的速度，因为有了索引，在查询某些条件的数据时，不需要全表扫描；从索引的根节点根据子节点的指针，依次向下层开始查找；
• B-tree 是按照索引列中的数据大小顺序存储的，非常适合按照范围进行查询；

自适应哈希索引：

InnoDB 存储引擎在发现某些索引值访问非常频繁时，会在原有 B-tree 索引之上，在内存中在构建一个哈希索引；

全文索引：

FULLTEXT 是一种特殊类型的索引，查找的是文本中的关键字，类似于搜索引擎；

---

3.2.B-tree 索引的使用

适用的查询类型：

• 全值匹配：和索引中的所有列匹配；
• 匹配最左前缀：只使用第一列，或者前几列；
• 匹配列前缀：只匹配某一列的值的开头部分；
• 匹配范围值；
• 精准匹配某一列而范围匹配另外一列；
• 只访问索引的查询：查询只需要访问索引而无须访问数据行，也称之为覆盖索引；

索引排序：

• 因为索引树中的节点是有序的，除了按值查找，索引还可以用于查询中的 ORDER BY 操作；
• 如果 ORDER BY 子句满足索引查询类型，则索引也可以用于这类的排序场景；

索引的限制：

• 不是按照索引的最左列开始查找，则无法使用索引；
• 不能跳过索引中的列；
• 如果查询中有某列的范围查询，则其右边所有列都无法使用索引优化查询；

---

3.3.索引的优点

• 索引大大减少了服务器需要扫描的数据量；
• 索引可以帮助服务器避免排序和临时表；
• 索引可以将随机 I/O 变成顺序 I/O；

---

3.4.高性能的索引策略

前缀索引：

• 为了提升索引性能，节省索引空间，可以只对字段的前一部分字符进行索引；
• 针对 BLOB、TEXT 或者很长的 VARCHAR 类型的列，只支持使用前缀索引。

索引的选择性：

是指不重复的值(也称之为基数)和数据表的总记录数(T)的比值，范围是 1/T 到 1 之间；索引的选择性越高则查询效率越高。

如何确定前缀索引的长度？

前缀索引的长度，既要足够长来保证较高的选择性，又不能太长(节省空间)；

可以通过计算不同长度前缀和完整列，与总行数的比率，使用较接近的长度前缀；

多列索引：

索引的常见误区是为每列创建独立的索引，或者按照错误的顺序创建多列索引。

如何选择合适的索引列顺序：

根据经验法则：将选择性最高的列放到索引的最前列，在大部分场景下是有效的。

但是索引性能不仅仅依赖于选择性，和查询条件的具体值，以及值的分布都有关，此时可以根据运行频率最高的查询来适当调整索引列的顺序。

聚簇索引：

聚簇索引是一种数据存储方式，InnoDB 的聚簇索引是在同一个结构中保存了 B-tree 索引和数据行。InnoDB 根据主键聚簇数据，如果没有定义主键，会选择唯一的非空索引代替，次之会隐式定义一个主键。

按主键顺序插入行：

如果 InnoDB 表中没有数据需要聚集，可以使用一个代理键来作为主键（例如 AUTO_INCREMENT 自增列），以保证数据行是按顺序写入的，来提升性能。

随机的聚簇索引，例如 UUID 会使得数据没有任何聚集特性，插入也变得完全随机，应该尽量避免。

覆盖索引：

如果一个索引包含所有需要查询的字段的值，称之为覆盖索引。不需要回表查询，效率更高。

使用索引来排序：

只有当使用的顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向(顺序或者倒序)都一样时，MySQL 才能使用索引对结果进行排序。

在 EXPLAIN 的输出结果中，type 列的值为”index”，说明 MySQL 使用了索引扫描来进行排序。

---

4.查询性能优化

查询的生命周期，大致上可以分为：客户端连接到服务端，服务端进行语法解析，生成执行计划，执行，并给客户端返回结果。

其中“执行”阶段是整个生命周期中最重要的阶段，包括大量为了检索数据对存储引擎的调用，以及调用后的数据处理(排序、分组等)。

---

4.1.优化数据访问

查询的数据是否过多：

• 查询了不需要的记录；
• 多表连接时返回全部列；
• 总数取出全部列；
• 重复查询相同的数据；

MySQL 是否在扫描额外的记录：

• 扫描的行数和返回行数之间的比率；
• 扫描的行数和访问类型：全表扫描、索引扫描、范围扫描、唯一索引查询，常数引用等；

---

4.2.重构查询的方式

• 将复杂查询重构为多个简单查询；
• 切分查询，每次只返回部分查询结果；
• 分解连接查询；

---

4.3.查询状态

• Sleep：线程正在等待客户端发送新的请求；
• Query：线程正在执行查询或者正在将结果发送给客户端；
• Locked：在 MySQL 服务器层，线程正在等待表锁；（存储引擎级别实现的锁，例如 InnoDB 的行锁，不会体现在线程状态中）
• Analying and statistics：线程正在检查存储引擎的统计信息，并优化查询；
• Copying to tmp table：线程正在执行查询，将结果复制到临时表中；
• Sorting result：线程正在对结果集进行排序；

---

4.4.排序优化

在不能使用索引生成排序结果的时候，MySQL 需要自己继续排序，数据量小则在内存中进行，数据量大则需要使用磁盘，在 MySQL 中统称文件排序。

排序的具体实现：

如果需要排序的数据量小于“排序缓存区”，MySQL 使用内存进行快速排序操作，如果内存不够排序，那么 MySQL 会先将数据分块，对每个独立的块使用快速排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并，最后返回排序结果。

在 MySQL 的 EXPLAIN 结果的 Extra 字段可以看到“Using temporary; Using filesort”字样，说明使用了临时表、文件进行排序。