数据库优化

## 1 优化方法

### 1.1 SQL 语句的优化

> 优化 SQL 语句时可以按照以下的步骤来进行：
> 
> 1. 首先**分析慢查询日志**，这里面**记录了响应时间超过阈值 `long_query_time` 的 SQL 语句**，通过日志**找出 IO 大的 SQL 以及发现未命中索引的 SQL**。
> 2. 然后**使用 `EXPLAIN` 对慢查询 SQL 进行分析**，通过 `EXPLAIN` 命令可以得到**表的读取顺序**、**数据读取操作的操作类型**、**哪些索引可以使用**、**哪些索引被实际使用**、**表之间的引用**以及**被扫描的行数**等问题。

1. 应尽量**避免在 `WHERE` 子句中使用 `!=`**、`<`、`>`**操作符或对字段进行 `null` 值判断**，**否则数据库引擎将放弃使用索引而进行全表扫描**。
2. **只返回必要的列**，**最好不要用 `SELECT *` 语句**。
3. **只返回必要的行**，**使用 `LIMIT` 语句来限制返回的数据**。
4. 高并发高性能的应用中**尽量对关联查询分解为单表查询**，**然后将结果在应用程序中进行关联**，例如下面这个查询：
   
   ```sql
   select * from tag
   join tag_post on tag_post.tag_id=tag.id
   join post on tag_post.post_id=post.id
   where tag.tag='mysql';
   ```
   
   可以分解成下面这些查询来代替：
   
   ```sql
   select * from tag where tag='mysql';
   select * from tag_post where tag_id=1234;
   select * from post where id in(123,456,567,9989,8909);
   ```
   
   这种**用分解关联查询的方式重构查询**具有如下优势：
   
   1. **让缓存的效率更高：**
      1. **许多应用程序可以方便地缓存单表查询对应的结果对象**。
      2. 对于 MySQL 的**查询缓存**来说，**如果关联中的某个表发生了变化**，**那么就无法使用查询缓存了**，而**拆分后**，**如果某个表很少改变**，那么**基于该表的查询就可以重复利用查询缓存结果了**。
   2. 将**查询分解后**，**执行单个查询可以减少锁的竞争**。
   3. 在**应用层做关联**，可以**更容易对数据库进行拆分**，**更容易做到高性能和可扩展**。
   4. 分解成多个单表查询，这些**单表查询的缓存结果更可能被其他查询使用到**，从而**减少冗余的查询**。

### 1.2 索引的优化

1. **注意会引起索引失效的情况**，以及**在适合的地方建立索引**，具体可参考[2.1 索引](https://notebook.ricear.com/project-37/doc-720)。

### 1.3 数据库表结构的优化

1. **设计表时遵循[三范式](https://notebook.ricear.com/project-37/doc-748)**。
2. **选择合适的数据类型**：
   1. **简单的原则**：
      1. 通常**最小的是最好的**：
         1. 因为这样可以**用更少的磁盘**、**内容**、**CPU 缓存**、**大大减少 IO 开销**。
      2. **简单就好**：
         1. **简单的数据类型操作**通常**需要更少的 CPU 周期**，例如：
            1. **整型比字符操作代价更小**，因为**字符集和校对规则**（排序规则）**使他比整型更复杂**。
            2. 应该**使用 MySQL 内置的类型而不是使用字符型来存储日期和时间**。
      3. 尽量**避免使用 NULL**：
         1. **NULL 是列默认的属性**，**通常我们要指定为 NOT NULL**。
         2. 有 NULL 的列值会**使得索引**、**索引统计和值比较更加复杂**。
         3. 可为 NULL 的列会**使用更多的存储空间**，在 MySQL 中也**需要对他特殊处理**，当**可为 NULL 的列做索引**时，**每个索引需要一个额外的字节**，**在 MyISAM 更有可能导致固定大小的索引变成可变大小的索引**，**在 InnoDB 中使用单独的位**（`bit`）**存储 NULL 值**。
   2. 相关数据类型的使用方法如下：
      1. **整数类型**：
         
         1. 几种整数类型：TINYINT（1 字节）、SMALLINT（2 字节）、MEDIUMINT（3 字节）、INT（4 字节）、BIGINT（8 字节），他们的**范围是-2 的 $(n-1)$ 次方到 2 的 $(n-1)$ 次方-1**，如果选择了 UNSIGNED，表示**非负**，他可以**使整数最大值提高一倍**，**有符号和无符号使用相同的存储空间**，具有**相同的性能**。
            
            > 注：上面的 $n$ 指的是整数类型的位数，其中 1 字节等于 8 位。
         2. 为**整型指定宽度**，如 INT（11），对于**存储**来说，**INT（11）和 INT（20）是相同的**，他**不会限制值的合法范围**，**只是规定了 MySQL 与客户端的交互应该显示多少位**，而且**这个值和 `ZEROFILL` 结合着用才有效**，当**该字段开启了 `ZEROFILL` 时**，如果**存储的位数小于定义的位数**，则会**在前面补 0**，如果**大于定义的位数**，则**直接显示**，例如：
            
            1. 定义一张表 `test_data_type`，包含两个字段，分别为 `a int(4)`、`b int(4) unsigned zerofill`：
               
               ```sql
               DROP TABLE IF EXISTS `test_data_type`;
               CREATE TABLE `test_data_type` (
                 `a` int(4) NOT NULL,
                 `b` int(4) unsigned zerofill DEFAULT NULL
               );
               ```
            2. 向这张表中插入数据：
               
               ```shell
               mysql> insert into test_data_type (a,b) values (12,12);
               mysql> select * from test_data_type;
               +----+------+
               | a  | b    |
               +----+------+
               | 12 | 0012 |
               +----+------+
               
               mysql> insert into test_data_type (a,b) values (123456,123456);
               mysql> select * from test_data_type;
               +--------+--------+
               | a      | b      |
               +--------+--------+
               |     12 |   0012 |
               | 123456 | 123456 |
               +--------+--------+
               ```
            3. 从上面的结果中可以看出：
               
               1. `a` 字段没有开启 `zerofill`，因此即使存储的数据位数小于该字段定义时的位数，也不会补 0，而是直接显示相应数据。
               2. `b` 字段开启了 `zerofill`，因此当存储的数据位数小于该字段定义时的位数时，会自动在前面补 0。
               3. 存储的数据位数大于该字段定义时的位数时，两个字段都是直接显示相应数据。
      2. **实数类型**：
         
         1. MySQL 中有 3 种类型可以表示实数，分别是 `float`、`double`、`decimal`，这三种类型的详细信息如下：
            1. `float`：**4 字节**，**单精度**，**浮点数值**。
            2. `double`，**8 字节**，**双精度**，**浮点数值**。
            3. `decimal`：对于 `decimal(m,d)`，如果**m>d**，则为**m+2**，否则为**d+2**，**小数值**。
         2. 选择方式：
            1. 如果我们要表示的**浮点型数据转成二进制之后能被 32 位 `float` 存储**，或者**可以容忍截断**，则**使用 `float`**，这个范围大概为要**精确保存 6 位数字**左右。
            2. 如果我们要表示的**浮点型数据转成二进制之后能被 64 位 `double` 存储**，**或者可以容忍截断**，则**使用 `double`**，这个范围大概为要**精确保存 13 为数字**左右。
            3. 相比 `double`，**已经能满足我们大部分浮点型数据存储精度要求**，如果还要精益求精，可以**使用 `decimal` 存储一些科学数据**或者**精度要求很高的金钱**。
            4. 因为 `decimal`**需要额外的空间和计算开销**，应**尽量在只对小数进行精确计算的时候才使用 `decimal`**，**在有金额交易的过程中**，**更倾向于使用 `bigint` 代替 `decimal`**，**将金额单位扩大相应倍数**，如**金额单位为元**，**可以在存储的时候扩大 100 倍**，**使用分为单位**，**这样可以避免使用浮点计算不精确的问题和使用 `decimal` 计算代价高的问题**。
      3. **字符串类型**：
         
         1. **`varchar` 和 `char`**：
            1. **字符串类型的表示**：
               1. `char` 和 `varchar` 表示字符串的格式为 `char(N)` 和 `varchar(N)`。
               2. **其中 $N$ 在 MySQL 5.0 之前表示最大存储的字节数**，**在 MySQL 5.0 之后表示最大存储的字符数**，**字符数超过 $N$ 会被截断**，**超过 $N$ 的部分将会被丢弃**。
            2. **存储上的不同**：
               1. 对于 `char` 来说，**最多存放 255 个字符**，**和编码无关**。
               2. 对于 `varchar` 来说，**可以表示 65535 个字节**，但是**最多存放 65532 个字节**，因为**需要 2 个字节来存放字符串的长度**，以及**结尾还要用 1 个字节表示结束**，所以**有效长度就是 65535-1-2 =65532**。
            3. **定长和变长**：
               1. `char(N)`：`char` 是**定长**的，**插入数据不足规定长度 $N$**，**右边使用空格补全**，**字符数超过 $N$ 会被截断**，**超过 $N$ 的部分将会被丢弃**。
               2. `varchar(N)`：`varchar` 是**不定长**的，**当 $N \le 255$ 时**，**需要用 1 个字节来存放字符串的长度**，**当 $N \gt 255$ 时**，**需要用 2 个字节来存放字符串的长度**，另外外加**1 个代表结束的字节**，**其他的就是所需表示字符**。
            4. **查找效率**：
               1. `char`**大于**`varchar`。
               2. 因为 `char` 在**存放数据的时候中间没有间隔**，**数据长度是固定的**，而且**数据段之间没有间隔**，因此在 MySQL**查询的时候只需要按部就班寻找就行了**，**不需要在中途计算这个数据段的长度**。
               3. **`varchar` 类型的存储方式就不同了**，**在每个数据段开头**，**都要有一段空间**（1~2 个字节）**存放数据段的长度**，**在数据段的结尾还有一段空间**（1 个字节）**标记此字段的字节数**，因此 MySQL 在**读取一个数据段的时候**，**首先要读开头**，比如读到了 3，说明数据段的长度是 3，之后就不多不少，只读 3 个字节，又因为**数据段被隔开了**，所以 MySQL**在遍历 `varchar` 类型数据的时候**，**磁针要比 `char` 类型的列多读很多次磁盘来获取字段的真实长度**，**这也就是为什么 `varchar` 比 `char` 查询效率低的原因了**。
            5. **末尾空格处理**：
               1. **`char(N)` 会去掉结尾的空格**，`varchar(N)`**不会去掉结尾的空格**。
               2. 例如：
                  1. 创建如下所示的表 `test_data_type`：
                     
                     ```shell
                     DROP TABLE IF EXISTS `test_data_type`;
                     CREATE TABLE `test_data_type` (
                       `e` varchar(10) COLLATE utf8_bin DEFAULT NULL,
                       `f` char(10) COLLATE utf8_bin DEFAULT NULL
                     );
                     ```
                  2. 向 `test_data_type` 中插入数据：
                     
                     ```shell
                     mysql> insert into test_data_type (e, f) values ("abcde ", "abcde ");
                     mysql> select e, char_length(e) as e_length, f, char_length(f) as f_length from test_data_type;
                     +--------+----------+-------+----------+
                     | e      | e_length | f     | f_length |
                     +--------+----------+-------+----------+
                     | abcde  |        6 | abcde |        5 |
                     +--------+----------+-------+----------+
                     ```
                  3. 从上面的结果中可以看出 `e` 插入 `abcde ` 后占用 6 个字符，说明没有去除结尾的空格，而 `f` 插入 `abcde ` 后占用 5 个字符，说明去除了结尾的空格。
            6. **使用建议**：
               1. 在设置 MySQL 字符串字段属性的时候要**结合业务场景进行选择**，**不要脱离实际业务盲目选择**。
               2. **存储定长字符串**，**尽量用 `char`**，**索引极快**（例如手机号、身份证号）。
               3. **长度 255 以上字符串**，**只能用 `varchar` 和 `text`**，**能用 `varchar` 尽量不用 `text`**（这点会在下面进行说明）。
         2. **BLOB 和 TEXT**：
            1. **主要差别**：
               1. TEXT 和 BLOB 的主要差别就是**BLOB 保存二进制数据**，**TEXT 保存字符数据**。
            2. **类型区别**：
               1. BLOB 有四种类型：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB，他们只是**可容纳值的最大长度不同**。
               2. TEXT 也有四种类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT，这些类型**同 BLOB 类型一样**，**有相同的最大长度和存储需求**。
            3. **字符集**：
               1. **BLOB 列没有字符集**，并且**排序和比较基于列值字节的数据**。
               2. **TEXT 列有一个字符集**，并且**根据字符集的校对规则对值进行排序和比较**。
            4. **大小写**：
               1. 在 TEXT 或 BLOB 列的存储或检索过程中，**不存在大小写转换**，都一样。
            5. **严格模式**：
               1. 运行在**非严格模式**下，如果我们为 BLOB 或 TEXT 列**分配一个超过该列类型的最大长度的值**，那么**超过长度的部分将会被截断**，如果**被截掉的字符不是空格**，那么将**会产生一条警告**。
               2. 如果使用**严格模式**，那么将会**直接报错**。
            6. **其他**：
               1. 当保存或检索 BLOB 和 TEXT 列的值时**不删除尾部空格**。
               2. **对于 BLOB 和 TEXT 列的索引**，**必须指定索引前缀的长度**。
               3. BLOB 和 TEXT 列**不能有默认值**。
               4. 当**排序时只能使用该列的前 `max_sort_length` 个字节**，其**默认值是 1024**。
               5. **BLOB 和 TEXT 对象的最大大小由其类型决定**，但在**客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓冲区大小决定**，我们**可以通过更改 `max_allowed_packet` 变量的值更改消息缓冲区的大小**，但**必须同时修改服务器和客户端程序**。
      4. **时间和日期类型**：
         
         1. **DATETIME**：
            1. 这个类型**能保存 1001 到 9999 年**，**精度为秒**，**与时区无关**。
            2. **使用 8 个字节存储**，**存储格式封装为 `YYYYMMDDHHMMSS` 的整数**，因此**是一种可排序的类型**。
            3. 显示时**以 ANSI 标准定义的日期和时间表示方法显示**。
         2. **TIMESTAMP**：
            1. 他**保存了从 1970 年 1 月 1 日午夜以来的秒数**，也就是常说的**时间戳**。
            2. **使用 4 个字节存储**，**依赖于时区**。
            3. 除特殊情况外，通常我们应该**尽量使用 TIMESTAMP 存储时间**，因为**他比 DATETIME 更省空间**。
   3. 进行[**分库分表**](https://notebook.ricear.com/project-37/doc-761)。

### 1.4 系统配置的优化

1. **操作系统**：
   1. **增加 TCP 支持的队列数**（网络方面的配置，要修改 `/etc/sysctl.conf`）：
      
      ```xml
      #增加 tcp 支持的队列数
      net.ipv4.tcp_max_syn_backlog = 65535
      #减少断开连接时，资源回收
      net.ipv4.tcp_max_tw_buckets = 8000
      net.ipv4.tcp_tw_reuse = 1
      net.ipv4.tcp_tw_recycle = 1
      net.ipv4.tcp_fin_timeout = 10
      ```
2. **MySQL 配置文件优化**：
   1. `innodb_buffer_pool_size`：配置**缓冲池大小**，如果数据库中**只有 `innodb` 表**，则**推荐配置量为总内存的 75%**。
   2. `innodb_buffer_pool_instances`：配置**缓冲池的个数**，**默认只有一个缓冲池**。
   3. `innodb_log_buffer_size`：配置**日志缓冲大小**，**由于日志最长每秒钟就会刷新一次**，**所以一般不用太大**。
   4. `innodb_flush_log_at_trx_commit`：配置**数据多久将变更刷新到磁盘**，具体可参考[2.2 含义](https://notebook.ricear.com/project-37/doc-740/#2-2-%E5%90%AB%E4%B9%89)。
   5. `innodb_read_io_threads`：配置**读的 IO 进程数**，**默认为 4**。
   6. `innodb_write_io_threads`：配置**写的 IO 进程数**，**默认为 4**。
   7. `innodb_file_per_table`：配置**每一个表是否使用独立的表空间**，具体可参考[2.5 Log Group 和 Redo Log File](https://notebook.ricear.com/project-37/doc-740/#2-5-Log-Group-%E5%92%8C-Redo-Log-File)。

### 1.5 硬件的优化

1. **使用固态硬盘**。
2. **使用多核且高频的 CPU**。
3. **增大内存**。

## 2 场景示例

### 2.1 MySQL 处理千万级数据分页查询的优化方案

1. 使用分页查询时我们**一般使用 `limit` 关键词**，对于**小的偏移量**，**直接使用 `limit` 来查询没有什么问题**，但**随着数据量的增大**，**越往后分页**，`limit`**语句的偏移量就会越大**，**速度也会明显变慢**，这是因为**每次都要取出大量的数据**，然后**把大部分的数据抛弃**，**只留下后面一下部分的数据**，**性能较差**。
2. 假如有一张表，表的相关信息如下：
   
   1. **表名**：`order`，订单表。
   2. **字段情况**：该表一共 37 个字段，不包含 `text` 等大型数据，最大为 `varchar(500)`，`id` 字段为索引，且为递增。
   3. **数据量**：5709294。
3. 假设现在有如下查询：
   
   ```sql
   select * from order where user_id = 3 order by id limit 100000, 100;
   ```
   
   该查询大约耗时 14 秒多，耗时较长。
4. 可以按照如下方式进行优化：
   
   1. **使用[覆盖索引](#6-4-1-覆盖索引)**：
      1. 假设我们**只需要查询 `order_type` 和 `order_amt` 两个字段**，**可以建立联合索引**（`order_type`、`order_amt`），这样**可以让查询走[联合索引](#6-2-2-联合索引)**，**加快性能**：
         
         ```sql
         select order_type, order_amt from order limit 100000, 100;
         ```
      2. 但是我们这个表有 37 个字段，这么优化明显**不合适**，而且这种**加快性能的效果并不明显**。
   2. **将**`offset`**计算出来后将主键索引作为**`where`**条件**：
      1. 假如**数据表的 `id` 是连续递增的**，可以**直接将对应的 `offset` 计算出来**，**作为 `where` 条件**，这样**可以利用主键索引**，**性能提升非常明显**：
         
         ```sql
         -- 方式一
         select order_type, order_amt from order where id >= 100000 limit 100;
         -- 方式二
         select order_type, order_amt from order where id between 100000 and 100100 limit 100;
         ```
      2. 但是这种方案存在严重问题，**可能存在部分数据被删过**，**导致 `id` 不连续**，所以上面**查出来的数据并不是我们想要的**。
   3. **每次分页查询记录上一次分页最后一条 `id`**：
      1. 我们可以在**每次分页查询的时候记录上一次分页最后一条 `id`**，然后在**查询的时候直接根据上一次分页最后一条 `id` 来进行查询**即可，此时就算 `id` 不连续也没问题，但是必须自增，由于数据库主键我们一般设置成连续自增，所以这种方式**可以大幅度提升性能**：
         
         ```sql
         select * order where id >= 上一页最大 id limit 100;
         ```
   4. **使用子查询**：
      1. 上面的一种方法**每次都需要记录上一次分页的最大 `id`**，**比较麻烦**，我们可以**使用子查询代替**：
         
         ```sql
         -- 方式一
         select * from order where id in (select id from order limit 100000, 100);
         -- 方式二
         select * from order where id >= (select id from order limit 100000, 1) limit 100;
         -- 方式三
         select * from order t1 join (select id from order limit 100000, 1) t2 on t1.id >= t2.id limit 100;
         ```
      2. 这种方式之所以能够大幅度优化性能，主要在于：
         
         1. **直接分页**，**不会走索引**，**全表扫描**，其实是**遍历主键索引树**，但是**每次都需要把对应行的数据取出来**，**要取 100100 条数据**，**然后丢弃前 100000 条**，**太耗性能**，可以通过 `explain` 执行计划查看对应索引情况。
         2. **使用子查询**，**会走主键索引**，**虽然也是遍历主键索引树**，**但是只取 `id`**，**不需要取整行数据**，**最后外层查询拿到对应的 100 条 `id`**，**查询对应数据即可**，**通过数据索引字段定位后**，**大大减少了查询的数据量**，**效率自然大大提升**。
         3. **上述使用 `in`**、`where`**条件和 `join`**，**差别不大**。
   5. **业务优化**：
      1. **一般来说翻页不会超过 20 页**，**可以通过限制翻页的数量来解决这个问题**，像百度分页最多只展示 76 页，还有一种方式，就是**使用滚动**，和微博一样，没有翻页，只能不断下拉，就是**使用之前记录上一页最大 `offset` 那个方法就可以做到**。

## 参考文献

1. [如何优化数据库？](https://github.com/wolverinn/Waking-Up/blob/master/Database.md#%E5%A6%82%E4%BD%95%E4%BC%98%E5%8C%96%E6%95%B0%E6%8D%AE%E5%BA%93)
2. [MySQL 数据库优化，看这篇就够了](https://mp.weixin.qq.com/s?src=11×tamp=1625475928&ver=3172&signature=jstFHGmbf8R1nBABholOYppBhHrN8VbvOt0Dt3PJAX7*6KfKWPxsWAFokV5YOA6wtvAT7E2VZoukDbyeQ-3uuME6iJQSJjvGNJjCS71O*S1D6gikve8NmyOUbXVTkUa7&new=1)。
3. [数据库优化和查询优化方案](https://juejin.cn/post/6844904038459244552)。
4. [MySQL 数据库优化的八种方式(经典必看)](https://www.jianshu.com/p/dac715a88b44)。
5. [单表查询和多表连接查询哪个效率更快](https://www.cnblogs.com/fxl-njfu/p/11759973.html)。
6. [MySQL 之选择字段数据类型](https://www.cnblogs.com/457248499-qq-com/p/7392369.html)。
7. [What does “size” in int(size) of MySQL mean?](https://alexander.kirk.at/2007/08/24/what-does-size-in-intsize-of-mysql-mean)
8. [MySQL 如何选择 float, double, decimal](http://blog.leanote.com/post/weibo-007/mysql_float_double_decimal)。
9. [MySQL 实数类型使用注意事项](https://www.jianshu.com/p/674dd6ee9150)。
10. [【Mysql】：搞清楚字符串类型 char、varchar、text](https://joyohub.com/2020/07/04/mysql/mysql-string)。
11. [MySQL 中 TEXT 与 BLOB 字段类型的区别](https://www.cnblogs.com/printN/p/7463737.html)。
12. [MySql 系统配置优化](https://www.jianshu.com/p/586860446c6e)。
13. [MySQL 大数据量分页 limit 优化_LJJZJ 的博客-程序员宅基地](https://cxyzjd.com/article/LJJZJ/103074816)。