高性能MySQL的实现策略

最新推荐文章于 2024-05-05 16:55:02 发布

阿昕在听歌

最新推荐文章于 2024-05-05 16:55:02 发布

阅读量142

点赞数

分类专栏：其他文章标签： mysql 服务器数据库

本文链接：https://blog.csdn.net/ahmuAMu/article/details/129677126

版权

其他专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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”字样，说明使用了临时表、文件进行排序。