MySQL数据库优化

1 MySQL 数据库设计原则

1.1 数据库命名规范

  1. 采用 26 个英文字母(区分大小写)和 0-9 的自然数(经常不需要)加上下划线’_'组成。
  2. 命名简洁明确(长度不能超过 30 个字符)。
  3. 除非是备份数据库可以加 0-9 的自然数：user_db_20151210。

1.2 数据库表名命名规范

  1. 采用 26 个英文字母(区分大小写)和 0-9 的自然数(经常不需要)加上下划线’‘组成。
  2. 命名简洁明确,多个单词用下划线’'分隔。
  3. 表前缀可以有效的把相同关系的表显示在一起。

1.3 数据库表字段名命名规范

  1. 采用 26 个英文字母(区分大小写)和 0-9 的自然数(经常不需要)加上下划线’‘组成。
  2. 命名简洁明确,多个单词用下划线’'分隔。
  3. 每个表中必须有自增主键，表与表之间的相关联字段名称要求尽可能的相同。

1.4 数据库表字段类型使用原则

  1. 用尽量少的存储空间来存数一个字段的数据，例如：能使用 INT 就不要使用 VARCHAR、CHAR ,能用 VARCHAR(16) 就不要使用 VARCHAR(256)。
  2. 固定长度的类型最好使用 CHAR。
  3. 能使用 TINYINT 就不要使用 SMALLINT,INT。如果非负则加上 UNSIGNED。
  4. 最好给每个字段一个默认值。
  5. 时间类型数据可以使用 VARCHAR 类型，可以使用 INT 类型，也可以使用时间戳类型。最好使用 INT 类型。
  6. 用好数值类型，用合适的字段类型节约空间。字符能转化为数字的最好转化,同样节约空间、提高查询性能。
  7. 避免使用 NULL 字段，NULL 字段很难查询优化、NULL 字段的索引需要额外空间、NULL 字段的复合索引无效。
  8. 少用 TEXT 类型，尽量使用 VARCHAR 代替 TEXT 字段。非用不可时最好考虑分表。
  9. 单表不要有太多字段，建议在20以内。

1.4.1 INT 类型存储时间

CREATE TABLE test(
id int AUTO_INCREMENT NOT NULL,
timestr int ,
PRIMARY KEY(id)
);
INSERT INTO test (timestr) VALUES (unix_timestamp(CURRENT_TIMESTAMP));
SELECT * FROM test;
+----+------------+
| id | timestr    |
+----+------------+
|  1 | 1689920493 |
+----+------------+
SELECT FROM_UNIXTIME(timestr) FROM test;
+------------------------+
| FROM_UNIXTIME(timestr) |
+------------------------+
| 2023-07-21 14:21:33    |
+------------------------+
-- unix_timestamp() 函数是将日期格式的数据转换为 int 类型。
-- FROM_UNIXTIME(timestr) 函数是将 int 类型转换为时间格式。

1.4.2 BIGINT 类型存储 IP 地址

  IP 地址如果要使用 VARCHAR存的话，需要15个字节，而用 BIGINT 存储的话，只需要8个字节，下面演示使用 BIGINT 存储 IP 地址。

CREATE TABLE sessions(
id int AUTO_INCREMENT NOT NULL,
ipaddress int,
PRIMARY KEY (id)
);
INSERT INTO sessions (ipaddress)VALUES (inet_aton('192.168.0.1'));
SELECT * FROM sessions;
+----+------------+
| id | ipaddress  |
+----+------------+
|  1 | 3232235521 |
+----+------------+
SELECT inet_ntoa(ipaddress) FROM sessions;
+----------------------+
| inet_ntoa(ipaddress) |
+----------------------+
| 192.168.0.1          |
+----------------------+
-- 利用 INET_ATON()，INET_NTOA() 两个函数来进行转换。

1.5 数据库表索引使用原则

  1. 命名简洁明确,例如：user_login 表 user_name 字段的索引应为 user_name_index 唯一索引。
  2. 为每个表创建一个主键索引。
  3. 为每个表创建合理的索引，建立复合索引请慎重。合理使用索引，改善查询，减慢更新，索引一定不是越多越好。
  4. 字符字段如果建索引的话必须建前缀索引。字符字段最好不要做主键。
  5. 不在索引做列运算。
  6. InnoDB 主键推荐使用自增列，主键建立聚簇索引,主键不应该被修改,字符串不应该做主键。
  7. 不用外键，由程序保证约束。
  8. 值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段。

1.6 数据库范式

  1. 第一范式(1NF)：字段值具有原子性,不能再分。例如：姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段。
  2. 第二范式(2NF)：一个表必须有主键,即每行数据都能被唯一的区分。并且必须满足第一范式。
  3. 第三范式(3NF)：一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有沉余字段。并且必须先满足第二范式。
  4. 注意：往往我们在设计表中不能遵守第三范式,因为合理的沉余字段将会给我们减少 Join 的查询。

1.7 核心原则

  1. 不在数据库做运算，计算务必移至业务层。
  2. 控制列数量(字段少而精,字段数建议在 20 以内)。
  3. 平衡范式与冗余(效率优先；往往牺牲范式)。
  4. 拒绝大 SQL 语句、拒绝大事务提交、拒绝大批量执行。

1.8 SQL 类原则

  1. SQL 语句尽可能简单，一条 SQL 只能在一个 CPU 运算,大语句拆小语句,减少锁时间,一条大 SQL 可以堵死整个库。
  2. 简单的事务。
  3. 不用select *，消耗 cpu,io,内存,带宽,这种程序不具有扩展性。
  4. OR 改写为 IN 或者改为 UNION。
  5. LIMIT 高效分页( LIMIT 越大，效率越低)。
  6. 使用 UNION ALL 替代 UNION，UNION 有去重开销。
  7. 少用连接 JOIN。
  8. 使用GROUP BY 代替 DISTINCT 。
  9. 请使用同类型比较。
  10. 不用函数和触发器，在应用程序实现。
  11. 对于连续数值，使用 BETWEEN不用 IN。例如 SELECT id FROM t WHERE num BETWEEN 1 AND 5。
  12. 尽量避免在 WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
  13. 避免 ‘%xxx’ 式查询。

2 数据库结构优化

2.1 数据库表的范式化优化

2.1.1 表范式化

  如上图的表结构，存在依赖关系：商品名称->分类->分类描述，也就是说这张表不符合第三范式。不符合第三范式的表存在冗余数据。想把不符合第三范式的表转换为符合第三范式的表，只能进行拆表。如下图所示。

2.1.2 反范式化

  反范式化是指为了查询效率的考虑把原本符合第三范式的表“适当”的增加冗余，以达到优化查询效率的目的，反范式化是一种以空间来换取时间的操作。

select b.用户名，b.电话，b.地址，a.订单ID，sum（c.商品价格*c.商品数量）as 订单价格
from 订单表 as a
join 用户表 as b on a.用户ID=b.订单ID
join 订单商品表 as c on c.订单ID=b.订单ID
group by b.用户名，b.电话，b.地址，a.订单ID
-- 对于这样的SQL，对于sum（），group by会产生临时表，增加IO量。我们怎么优化都效率不高，那我们怎么样才能让它效率高了，就需要一些字段进行冗余。

select a.用户名，a.电话，a.地址，a.订单ID,a.订单价格 
from 订单表 as a

2.2 表分区

  MySQL 在 5.1 版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码。
  对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引。

2.2.1 分区的优点

  1. 可以让单表存储更多的数据。
  2. 分区表的数据更容易维护，可以通过清除整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作。
  3. 部分查询能够从查询条件确定只落在少数分区上，速度会很快。
  4. 分区表的数据还可以分布在不同的物理设备上，从而利用多个硬件设备。
  5. 可以使用分区表避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争。
  6. 可以备份和恢复单个分区。

2.2.2 分区的限制和缺点

  1. 一个表最多只能有1024个分区。
  2. 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
  3. 分区表无法使用外键约束。
  4. NULL值会使分区过滤无效。
  5. 所有分区必须使用相同的存储引擎。

2.2.3 分区的类型

  1. RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
  2. LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
  3. HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
  4. KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

2.2.4 分区适合的场景

  最适合的场景数据的时间序列性比较强，则可以按时间来分区，如下所示：

CREATE TABLE members
(firstname VARCHAR(25) NOT NULL,
 lastname VARCHAR(25) NOT NULL,
 username VARCHAR(16) NOT NULL,
 email VARCHAR(35),
 joined DATE NOT NULL
)
 PARTITION BY RANGE(YEAR(joined)) (PARTITION p0 VALUES LESS THAN (1960),
                                   PARTITION p1 VALUES LESS THAN (1970),
                                   PARTITION p2 VALUES LESS THAN (1980),
                                   PARTITION p3 VALUES LESS THAN (1990),
                                   PARTITION p4 VALUES LESS THAN MAXVALUE);

  查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据能很容的批量删除。
  如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将热点数据单独放在一个分区，让这个分区的数据能够有机会都缓存在内存中，查询时只访问一个很小的分区表，能够有效使用索引和缓存。

2.3 数据库表的垂直拆分

  垂直分库是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联

2.3.1 垂直拆分原则

  1. 把不常用的字段表单独存放到一个表中。
  2. 把大字段独立存放到一个表中。
  3. 把经常一起使用的字段放到一起。

2.3.2 垂直拆分的优点

  1. 可以使得行数据变小，一个数据块(Block)就能存放更多的数据，在查询时就会减少I/O次数(每次查询时读取的Block 就少)。
  2. 可以达到最大化利用Cache的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起。
  3. 数据维护简单。

2.3.3 垂直拆分的缺点

  1. 主键出现冗余，需要管理冗余列。
  2. 会引起表连接JOIN操作（增加CPU开销）可以通过在业务服务器上进行join来减少数据库压力。
  3. 依然存在单表数据量过大的问题（需要水平拆分）。
  4. 事务处理复杂。

2.4 数据库表的水平拆分

  水平拆分是通过某种策略将数据分片来存储，分库内分表和分库两部分，每片数据会分散到不同的 MySQL 表或库，达到分布式的效果，能够支持非常大的数据量。前面的表分区本质上也是一种特殊的库内分表。
  库内分表，仅仅是单纯的解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻 MySQ L服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的 IO、CPU、网络，这个就要通过分库来解决。

2.4.1 水平拆分的优点

  1. 不存在单库大数据和高并发的性能瓶颈。
  2. 应用端改造较少。
  3. 提高了系统的稳定性和负载能力。

2.4.2 水平拆分的缺点

  1. 分片事务一致性难以解决。
  2. 跨节点Join性能差，逻辑复杂。
  3. 数据多次扩展难度跟维护量极大。

2.4.3 分片原则

  1. 分片数量尽量少，分片尽量均匀分布在多个数据结点上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量。
  2. 分片规则需要慎重选择做好提前规划，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性 Hash 分片，这几种分片都有利于扩容。
  3. 尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直是个不好处理的问题。
  4. 查询条件尽量优化，尽量避免 Select * 的方式，大量数据结果集下，会消耗大量带宽和 CPU 资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。
  5. 通过数据冗余和表分区降低跨库 Join 的可能。
  这里特别强调一下分片规则的选择问题，如果某个表的数据有明显的时间特征，比如订单、交易记录等，则他们通常比较合适用时间范围分片，因为具有时效性的数据，我们往往关注其近期的数据，查询条件中往往带有时间字段进行过滤，比较好的方案是，当前活跃的数据，采用跨度比较短的时间段进行分片，而历史性的数据，则采用比较长的跨度存储。
  总体上来说，分片的选择是取决于最频繁的查询 SQL 的条件，因为不带任何 WHERE 语句的查询 SQL，会遍历所有的分片，性能相对最差，因此这种 SQL 越多，对系统的影响越大，所以我们要尽量避免这种 SQL 的产生。

3 数据库系统配置优化

  数据库是基于操作系统的，目前大多数 MySQL 都是安装在 Linux 系统之上，所以对于操作系统的一些参数配置也会影响到 MySQL 的性能，下面就列出一些常用的系统配置。

3.1 操作系统的优化

3.1.1 网络连接配置

# TCP 是有连接状态，通过 netstat 查看连接状态，经常会看到 timeout 状态或者 timewait 状态连接，为了加快 timewait 状态的连接回收，就需要调整下面的四个参数，保持TCP连接数在一个适当的状态。
vim /etc/sysctl.conf
# 在 TCP 连接关闭后的一段时间内，会进入 TIME_WAIT 状态。在此状态下，系统会维护一段时间（通常为几分钟）以保证连接的可靠关闭。这个时间段称为 2MSL（Maximum Segment Lifetime）。在 2MSL 时间内，TCP 连接的双方可以确认该连接是否已经正确关闭，如果有任何数据包传输错误或重传，会被丢弃。
      # net.ipv4.tcp_max_tw_buckets参数指定了系统可同时保持在TIME_WAIT状态的最大连接数量。当系统中的TIME_WAIT连接达到这个数目时，新的连接可能会被拒绝或延迟处理。
      net.ipv4.tcp_max_tw_buckets = 8000
      # net.ipv4.tcp_tw_reuse参数有两个可能的取值：0：表示禁用地址和端口的重用。在TIME_WAIT状态下，如果有新的连接使用相同的本地地址和端口，将被拒绝；1：表示启用地址和端口的重用。在TIME_WAIT状态下，如果有新的连接使用相同的本地地址和端口，将会被接受。
      net.ipv4.tcp_tw_reuse = 1
      # net.ipv4.tcp_tw_recycle参数有两个可能的取值：0：表示禁用TCP连接TIME_WAIT状态的快速回收机制；1：表示启用TCP连接TIME_WAIT状态的快速回收机制。启用net.ipv4.tcp_tw_recycle参数可以让系统更快地回收TIME_WAIT状态的连接，以适应高并发连接的情况。该机制会使用IP地址进行时间戳计算，以尽快地释放TIME_WAIT连接。然而，启用该机制可能会导致某些情况下的数据冲突或连接问题。慎重使用。
      net.ipv4.tcp_tw_recycle = 1
      # net.ipv4.tcp_fin_timeout是一个Linux内核参数，用于控制TCP连接中的FIN-WAIT-2状态的超时时间。在TCP连接关闭过程中，当一方发送FIN包表示连接关闭后，对方进入CLOSE-WAIT状态并发送ACK包作为确认，然后进入FIN-WAIT-1状态。在FIN-WAIT-1状态下，如果对方也发送了FIN包，表示连接关闭，此时进入FIN-WAIT-2状态。在FIN-WAIT-2状态下，等待对方发送ACK包作为确认，然后关闭连接。net.ipv4.tcp_fin_timeout参数表示进入FIN-WAIT-2状态后，等待对方发送ACK包的超时时间，默认值为60秒。如果在超时时间内没有收到对方的ACK包，连接会自动关闭。这个超时时间可以根据系统的需求进行配置。
      net.ipv4.tcp_fin_timeout = 10
# net.ipv4.tcp_max_syn_backlog是一个Linux内核参数，用于控制TCP协议中的SYN连接backlog队列的最大长度。在TCP三次握手过程中，当服务器收到客户端发送的SYN请求时，会在backlog队列中暂存这些请求，然后逐个进行处理。当一个SYN请求到达服务器时，如果backlog队列未满，服务器会将这个SYN请求加入到队列中，然后向客户端发送一个SYN-ACK响应。如果backlog队列已满，服务器会直接丢弃这个SYN请求，不发送任何响应。客户端在收到服务器的SYN-ACK响应后，会发送ACK确认，完成三次握手。通过调整net.ipv4.tcp_max_syn_backlog参数，可以控制服务器backlog队列的大小，进而影响服务器的连接处理能力。较大的backlog队列可以暂存更多的连接请求，但也需要消耗更多的系统资源。而较小的backlog队列则可能导致连接请求被直接丢弃，从而影响服务器的可连接性。
net.ipv4.tcp_max_syn_backlog = 65535

3.1.2 打开文件数限制

vim /etc/security/limits.conf
# 打开文件数
* soft nofile 65535
* hard nofile 65535
# 可最大创建的进程或线路的数量
* soft nproc 131072
* hard nproc 131072
# 修改之后重新登录可以通过ulimit -a查看是否生效。

3.2 MySQL 的配置文件参数优化

-- 服务器状态变量，运行服务器的统计和状态指标
SHOW STATUS;
-- 服务器系统变量，实际上使用的变量的值
SHOW VARIABLES;

3.2.1 max_connections

-- 最大连接数
show variables like 'max_connections';
+-----------------+-------+ 
| Variable_name   | Value | 
+-----------------+-------+ 
| max_connections |  500  | 
+-----------------+-------+
-- 响应的连接数
show status like 'max_used_connections';
+--------------------+-------+ 
| Variable_name      | Value | 
+--------------------+-------+ 
| Max_used_connections | 478 | 
+--------------------+-------+

  如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，这建立在机器能支撑的情况下，MySQL会为每个连接提供连接缓冲区，如果连接数越多，内存开销也会越大，所以要适当调整该值，不能盲目提高设值。数值过小会经常出现ERROR 1040: Too many connections错误。
  max_connections的理想值 = max_used_connections / max_connections * 100% ≈ 85%。如果 max_used_connections 和 max_connections 相同 那么就是 max_connections 设置过低或者超过服务器负载上限了，低于10%则设置过大。

3.2.2 back_log

  MySQL 能暂存的连接数量。当 MySQL 线程在一个很短时间内得到非常多的连接请求，这就起作用。如果 MySQL 的连接数据达到 max_connections 时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即 back_log，如果等待连接的数量超过 back_log，将不被授予连接资源。
  back_log 值指出在 MySQL 暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。

show full processlist;
-- 发现大量如下的待连接进程时，就要加大back_log的值了。
-- 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL

  在 MySQL5.7.43 版本中 back_log 是一个只读的参数，如果要修改的话，需要在my.cf配置文件中进行修改。

3.2.3 slow_query_log

  慢查询日志记录执行时间超过指定阈值的查询语句，可以帮助识别和优化慢查询，提高数据库的性能。

show variables like '%slow%';
+---------------------------+-----------------------------------+
| Variable_name             | Value                             |
+---------------------------+-----------------------------------+
| slow_launch_time          | 2                                 |
| slow_query_log            | ON                                |
| slow_query_log_file       | /var/lib/mysql/localhost-slow.log |
+---------------------------+-----------------------------------+
-- slow_query_log 参数表示是否开启慢查询日志功能。
-- slow_launch_time 参数表示慢查询的时间阈值设置，默认为2秒，如果一条 SQL 执行的时间大于2秒会记录到慢查询日志中。
-- slow_query_log_file 参数表示慢查询日志的绝对路径。
-- log_queries_not_using_indexes 参数用于记录执行的查询语句中未使用索引的情况，会记录到错误日志中。这可能会导致日志文件增长较快。因此，在生产环境中，建议仅在需要进行性能优化分析时启用该参数，并在使用完毕后将其禁用。
-- 开启慢查询日志
SET GLOBAL slow_query_log = 1;
-- 设置慢查询的阈值为5秒
SET GLOBAL slow_launch_time = 5;
-- 开启该参数
SET GLOBAL log_queries_not_using_indexes = 1;
-- 可以查看开启慢查询之后有多少慢查询语句被监控到。
show global status like 'Slow_queries';

3.2.4 key_buffer_size

  key_buffer_size 只对 MyISAM 表起作用。key_buffer_size 是对 MyISAM 表性能影响最大的一个参数。即使你不使用 MyISAM 表，但是内部的临时磁盘表是 MyISAM 表，也要使用该值。

-- 在数据库操作中，有时需要创建临时表来处理一些中间结果。默认情况下，MySQL 尝试在内存中创建临时表来提高性能，但是如果内存不足以容纳临时表，MySQL 将不得不将临时表存储在磁盘上进行处理。Created_tmp_disk_tables 变量用于记录在磁盘上创建的临时表的数量。
show global status like 'Created_tmp_disk_tables';

show variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
-- key_buffer_size参数的默认值为 8 M，可以通过下面的命令进行调整。
SET GLOBAL key_buffer_size = 536870912;

show global status like 'key_read%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 108   |
| Key_reads         | 24    |
+-------------------+-------+
-- Key_read_requests 参数表示从缓存中读取索引的请求次数。
-- Key_reads 参数用于记录从磁盘读取索引数据的次数。
-- 一共有108个索引读取请求，有24个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：key_cache_miss_rate ＝Key_reads / Key_read_requests * 100%，设置在1/1000左右较好。可以通过调整缓存的大小进行设置。
show global status like 'key_blocks_u%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_blocks_unused | 6698  |
| Key_blocks_used   | 3     |
+-------------------+-------+
-- Key_blocks_unused 参数表示未使用的缓存块数。较高的 Key_blocks_unused 值通常表示索引缓存中有一定数量的空闲块，这些块未被使用。
-- Key_blocks_used 参数表示当前已在索引缓存中使用的索引块数量。较高的值通常表示索引内存缓存使用较多。
-- 如果所有缓存块都被使用了，需要增加key_buffer_size的参数设置。理想的使用比例应该控制在 Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%。

3.2.5 tmp_table_size & max_heap_table_size

show global status like 'created_tmp%'; 
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 2319   |
| Created_tmp_files       | 98401  |
| Created_tmp_tables      | 643541 |
+-------------------------+--------+
-- Created_tmp_disk_tables参数用于记录在磁盘中创建的临时表数量。
-- Created_tmp_files参数表示 MySQL 服务创建的临时文件文件数。
-- Created_tmp_tables参数表示创建在内存和磁盘的临时表的总数据量。
-- 比较理想的配置是：Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%。比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% = 0.36% 。
show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size'); 
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size      | 16777216 |
+---------------------+----------+
-- max_heap_table_size 在内存中可以创建的临时表的最大内存大小。默认为16M。
-- tmp_table_size 设置临时表的内存大小。

3.2.6 table_open_cache

show global status like 'open%tables%'; 
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Open_tables   | 400    |
| Opened_tables | 454466 |
+---------------+--------+
-- Open_tables参数表示当前打开表的数量。
-- Opened_tables表示自MySQL服务启动以来打开过的表数量。
-- 如果Opened_tables数量过大，说明配置中table_open_cache 值可能太小。
show variables like 'table_open_cache'; 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 400   |
+------------------+-------+
-- table_open_cache 当 Mysql 访问一个表时，如果该表在缓存中已经被打开，则可以直接访问缓存；如果还没有被缓存，但是在 Mysql 表缓冲区中还有空间，那么这个表就被打开并放入表缓冲区；如果表缓存满了，则会按照一定的规则将当前未用的表释放，或者临时扩大表缓存来存放，使用表缓存的好处是可以更快速地访问表中的内容。每个连接进来，都会至少打开一个表缓存。因此， table_cache 的大小应与 Threads_connected 的值有关。例如，对于 200 个并行运行的连接，应该让表的缓存至少有 200 × N ，这里 N 是网站程序一次查询所用到的表的最大值。
-- 理想的值为： 
　　-- Open_tables / table_cache * 100% >= 85%
　　-- Open_tables / table_cache * 100% <= 95%
-- 可以通过如下命令调整table_open_cache
SET GLOBAL table_open_cache = 500;
-- 如果调整之后很快Open_tables数据又增加了，这就必须通过使用适当的索引、优化查询语句和避免全表扫描，可以减少对表的频繁打开。

3.2.7 thread_cache_size

show global status like 'Thread%'; 
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 14    |
| Threads_connected | 362   |
| Threads_created   | 376   |
| Threads_running   | 2     |
+-------------------+-------+
-- Threads_created 表示创建过的线程数
-- Threads_connected 的值反映了当前活动的数据库连接数。
-- Threads_running 表示当前正在执行的线程数量。
-- Threads_cached 表示当前缓存的线程数量。它表示MySQL服务器为了提高性能而保留的空闲线程数。
show variables like 'thread_cache_size'; 
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 49    |
+-------------------+-------+
-- thread_cache_size 用于设置线程缓存的大小。线程缓存用于存储空闲线程，以便在新的客户端连接到达时可以重用这些线程，而不是频繁地创建和销毁线程。

3.2.8 sort_buffer_size

show global status like 'sort%'; 
+-------------------+-----------+
| Variable_name     | Value     |
+-------------------+-----------+
| Sort_merge_passes | 52609     |
| Sort_range        | 107137    |
| Sort_rows         | 136898754 |
| Sort_scan         | 1122992   |
+-------------------+-----------+
-- Sort_merge_passes 用于表示在执行排序操作时进行的磁盘排序合并的次数。当MySQL需要对大量数据进行排序时，它可能无法在内存中完成排序操作。相反，它将数据分割成多个小块，每个小块在内存中进行排序，然后将它们合并成一个有序的结果。Sort_merge_passes 表示在进行这些排序合并过程中涉及的磁盘写入和读取操作的次数。正常情况下，Sort_merge_passes 的值可能会随着时间的推移而增加，因为排序操作的数量会随着查询的执行而累积。因此，重点是观察其变化趋势。

-- Sort_range 用于表示在需要进行范围查询（Range Query）时执行的内部排序操作的次数。范围查询是指根据某个范围条件（例如 WHERE 子句中的 BETWEEN、>、< 等）来检索数据的查询。当执行范围查询时，MySQL可能需要对检索到的数据进行排序，以返回满足范围条件的有序结果。Sort_range 的值表示在执行范围查询时进行的内部排序操作的次数。较高的 Sort_range 值可能表明查询中存在较多的范围查询，并且需要频繁进行排序操作，这可能对性能产生影响。通过观察 Sort_range 的值和趋势，可以了解到系统中范围查询的频率以及排序操作的负载情况。如果 Sort_range 值过高，可能需要考虑一些优化措施，例如优化查询语句、使用合适的索引或增加相应的缓存等。
-- Sort_rows 用于表示对数据进行排序时处理的行数。当MySQL需要对数据进行排序时，它可能会将数据分割成多个小块，每个小块在内存中进行排序。Sort_rows 表示在进行这些排序过程中涉及的行数。Sort_rows 的值可以用来评估排序操作的工作量和性能开销。较高的 Sort_rows 值通常表示需要对大量的行数据进行排序，这可能会对系统资源和性能产生影响。
-- Sort_scan 扫描表所完成的排序数。应该尽量小一些，优化时添加索引，使其排序使用有序索引顺序扫描来返回结果数据
show variables like 'Sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
-- sort_buffer_size 参数它定义了排序操作使用的缓冲区大小。当执行排序操作时，MySQL 会为每个线程分配一个缓冲区，用于对排序数据进行存储和处理。该缓冲区的大小由 sort_buffer_size 决定。增加 sort_buffer_size 的值可以提高排序操作的性能，特别是对于大排序操作。较大的缓冲区可以减少磁盘访问次数，从而加快排序速度。但是sort_buffer_size 是每个线程都分配的大小，为排序设置的内存大小应该是sort_buffer_size*Threads_connected，需要谨慎使用。
SET GLOBAL sort_buffer_size = 67108864;  -- 64MB

3.2.9 open_files_limit

show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 1     |
+---------------+-------+
-- Open_files 当前 MySQL 打开的文件数。
show variables like 'open_files_limit'; 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 5000  |
+------------------+-------+
-- open_files_limit用于控制 MySQL 实例能够打开的最大文件数。
-- 理想情况下 Open_files / open_files_limit * 100% <= 75%，open_files_limit是一个只读参数，修改的话，只能修改my.cf配置文件然后重启MySQL服务。

3.2.10 表锁情况

show global status like 'table_locks%'; 
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| Table_locks_immediate | 39233577 |
| Table_locks_waited    | 85       |
+-----------------------+----------+
-- Table_locks_immediate表示立即释放表锁数，
-- Table_locks_waited表示需要等待的表锁数
-- 如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。

3.2.11 read_buffer_size

show global status like 'handler_read%'; 
+-----------------------+-------------+
| Variable_name         | Value       |
+-----------------------+-------------+
| Handler_read_first    | 20357140    |
| Handler_read_key      | 2907838809  |
| Handler_read_last     | 1240        |
| Handler_read_next     | 39246746769 |
| Handler_read_prev     | 5382315     |
| Handler_read_rnd      | 123399377   |
| Handler_read_rnd_next | 29805755788 |
+-----------------------+-------------+
show global status like 'com_select'; 
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Com_select    | 57063155 |
+---------------+----------+
-- Handler_read_first 是 MySQL 中的一个状态变量，用于表示执行的查询操作需要从索引中读取第一行的次数。
-- Handler_read_key 是 MySQL 中的一个状态变量，用于表示执行的查询操作通过索引键读取行的次数。
-- Handler_read_last 是 MySQL 中的一个状态变量，用于表示执行的查询操作需要从索引中读取最后一行的次数。
-- Handler_read_next 是 MySQL 中的一个状态变量，用于表示执行查询操作时需要从存储引擎读取下一行的次数。
-- Handler_read_prev 是 MySQL 中的一个状态变量，用于表示执行查询操作时需要从存储引擎读取上一行的次数。
-- Handler_read_rnd 是 MySQL 中的一个状态变量，用于表示执行查询操作时需要随机读取行的次数。这通常发生在没有使用索引或使用非唯一索引进行范围扫描时，MySQL 需要通过随机读取来获取匹配的行。
-- Handler_read_rnd_next 是 MySQL 中的一个状态变量，用于表示执行查询操作时需要进行随机读取下一行的次数。通常发生在没有使用索引或使用非唯一索引进行范围扫描时，MySQL 需要通过随机读取来获取匹配的行，并读取下一行数据。
-- Com_select 是 MySQL 中的一个状态变量，用于表示执行 SELECT 查询的次数。这包括所有的 SELECT 查询，无论是简单的查询还是复杂的查询。
-- 如果表扫描率Handler_read_rnd_next / Com_select 超过4000，说明进行了太多表扫描，很有可能索引没有建好。
SHOW VARIABLES LIKE 'read_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
-- read_buffer_size  是 MySQL 中的一个配置参数，用于设置内部的读缓冲区的大小。读缓冲区是在执行查询操作时用于读取数据块的临时存储区域。当 MySQL 需要从磁盘读取数据时，会将数据读入读缓冲区，然后再从缓冲区提取数据供查询使用。增大读缓冲区可以提高查询性能，尤其是对于大型表或需要进行大量数据读取的查询。可以通过如下命令进行设置。该参数对应的分配内存也是每个连接独享。
set global read_buffer_size = 131072;

3.2.12 sync_binlog

  这个参数用于控制二进制日志(binlog)的持久化方式。
  二进制日志是MySQL中用于记录数据库更新操作的日志，它可以用于复制(replication)、恢复和故障转移等场景。sync_binlog参数定义了将二进制日志写入磁盘时的持久化策略。默认值为1。对数据来说是最安全的一种方式。
  sync_binlog参数有以下可选值：
  1. 0: 表示不将二进制日志文件写入磁盘，而是通过操作系统缓存来写入。这是性能最好的选项，但在发生系统崩溃或机器断电时，可能会丢失一部分事务。
  2. 1: 表示每次提交事务时，都将相应的二进制日志写入磁盘。这是安全性最高的选项，但性能可能会受到一定影响，因为每次提交都需要等待磁盘写入完成。
  3， N (正整数值): 表示每次提交事务时，将二进制日志写入操作系统缓存，并每隔N秒将缓存中的日志写入磁盘。这种方式在安全性和性能之间取得了一定的平衡，可以通过适当设置N来控制持久化的频率。

set global sync_binlog = 1;

3.2.13 InnoDB