一、介绍 | 流程引擎原理与实践

# 一、介绍

数据库的优化可以从以下四个方面做，其效果跟投入成相反关系，即：

效果：

SQL及索引 > 数据库表结构 > 系统配置 > 硬件

成本：

硬件 > 系统配置 > 数据库表结构 > SQL及索引

硬件不用说，当然是配置越高越好，最好是专门用于数据库的服务器，并采用ssd的，不过这个成本是最高的，我们主要讲解剩下3种的优化思路。当然MySQL的优化工作是持续进行的，并不是一次性完成。

我们可以在以下阶段持续进行优化，分别是

上线前产品的数据库设计（系统配置、数据库表结构、SQL及索引设计）
上线后的持续监控优化（慢查询、mysql指标监控）。

当然，对于业务、产品的优化，并不单单只是针对MySQL进行优化，还可以通过其他方面来进行，例如缓存的应用。

整体的优化思路可以按照如下的原则逐步进行：

优先考虑通过缓存降低对数据库的读操作（如：redis）
再考虑读写分离，减轻数据库压力
首先考虑按照业务垂直拆分
再考虑水平拆分：先分库（设置数据路由规则，把数据分配到不同的库中）
再考虑分表，单表拆分到数据1000万以内
最后可以考虑分区，例如以用户名做hash分区

# 二、系统配置优化

以下针对的InnoDB引擎的配置文件my.cnf：

[mysqld]
# GENERAL
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid_file = /var/lib/mysql/mysql.pid
user = mysql
port = 3306

# INNODB
innodb_buffer_pool_size = <value>
innodb_log_file_size = <value>
innodb_file_per_table = 1

# LOGGING
slow_query_log = ON
slow_query_log = /var/lib/mysql/mysql-slow.log
log_error = /var/lib/mysql/mysql-error.log

# OTHER
tmp_table_size = 32M
max_heap_table_size = 32M
# 禁用缓存
query_cache_type = 0
query_cache_size = 0
max_connections = <value>
thread_cache_size = <value>
open_files_limit = 65535

[client]
socket = /var/lib/mysql/mysql.sock
port = 3306

# 1、innodb_buffer_pool_size

介绍

InnoDB使用一个缓冲池来保存索引和原始数据，如下图所示：

优缺点

缓冲池的作用可以减少磁盘访问，我们知道内存读写速度比磁盘的读写速度快很多，所以这个参数对mysql性能有很大提升。当然，这里不是越大越好，也要考虑实际的服务器情况。总之，InnoDB严重依赖缓冲池，我们必须为它分配了足够的内存。

更大的缓冲池会使得mysql服务在重启和关闭的时候花费很长时间。

如何配置

如果在一个独立使用的mysql服务器上，这个变量按照流行的经验法则，可以把缓冲池大小设置为服务器内存的约75%~80%。

但是，如果服务器上除了跑mysql服务，还有其他服务也在运行，那么在分配缓冲池空间时，需要减去这部分程序占用的内存、mysql自身需要的内存以及减去足够让操作系统缓存InnoDB日志文件的内存，至少是足够缓存最近经常访问的部分。

# 2、innodb_log_file_size和innodb_log_files_in_group

介绍

InnoDB使用日志来减少提交事务时的开销。

InnoDB用日志把随机I/O变成顺序I/O。

innodb_log_files_in_group 参数控制日志文件数，一般默认为2。mysql事务日志文件是循环覆写的，如下图：

优缺点

当一个日志文件写满后，innodb会自动切换到另一个日志文件，而且会触发数据库的checkpoint，这会导致innodb缓存脏页的小批量刷新，会明显降低innodb的性能。如果innodb_log_file_size设置太小，就会导致innodb频繁地checkpoint，导致性能降低。而如果设置太大，由于事务日志是顺序I/O，大大提高了I/O性能，但是在崩溃恢复InnoDB时，会导致恢复时间变长。

如果InnoDB数据表有频繁的写操作，那么选择合适的innodb_log_file_size值对提升MySQL性能很重要。

如何配置

作为一个经验法则，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。方法如下：

首先，在业务高峰期，计算出1分钟写入事务日志（redo log）的量，然后评估出一个小时的redo log量：

# 使用pager之后，执行命令只显示Log开头的
mysql> pager grep Log
PAGER set to 'grep Log'
mysql> show engine innodb status\G select sleep(60); show engine innodb status\G;
Log sequence number 3257464291
Log flushed up to   3257464278
1 row in set (0.00 sec)

1 row in set (1 min 0.00 sec)

Log sequence number 3257550399
Log flushed up to   3257550399
1 row in set (0.00 sec)

Log sequence number是写入事务日志的总字节数，通过1分钟内两个值的差值，我们可以看到每分钟有多少KB日志写入到MySQL中（备注：该返回值是我环境下的，具体数值请参考自己环境的返回值）

mysql> nopager
PAGER set to stdout
mysql> select (3257550399-3257464291)/1024 as KB;
+---------+
| KB      |
+---------+
| 84.0898 |
+---------+
1 row in set (0.00 sec)

那么，1小时的事务日志写入量为：84KB * 60 = 5040KB，约为5MB。

由于默认有两个日志文件，在日志组中，两个日志文件的大小是一致的。所以我们可以大约设置innodb_log_file_size=3M。

# 3、innodb_log_buffer_size

innodb_log_buffer_size可以控制日志缓冲区的大小。

通常不需要把日志缓冲区设置得非常大。推荐的范围是1MB~8MB，一般来说是足够了，MySQL默认是8MB。

# 4、innodb_flush_log_at_trx_commit

介绍

MySQL支持用户自定义在commit时如何将log buffer中的日志刷到log file中。这种控制通过变量：innodb_flush_log_at_trx_commit 来决定，该变量有：0、1、2三种值，默认为1。注意，这个变量只是控制commit动作是否刷新log buffer到磁盘中。

设置为0。把日志缓冲写到日志文件中，并且每秒钟刷新一次，但是事务提交时不做任何事，该设置是3者中性能最好的。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
设置为1。将日志缓冲写入到日志文件，并且每次事务提交都刷新到持久化存储。这是默认的设置（并且是最安全的），该设置能保证不会丢失任何已经提交的事务。
设置为2。每次提交时把日志缓冲写到日志文件，但并不刷新。InnoDB每秒钟做一次刷新。

如何配置

日志缓冲必须被刷新到持久化存储（磁盘），以确保提交的事务完全被持久化了。如果和持久化相比更在乎性能，则可以修改该参数来控制日志缓冲刷新的频繁程度。

# 5、thread_cache_size

介绍

线程缓存保存哪些当前没有与连接关联但是准备为后面新的连接服务的线程。当一个新的链接创建时，如果缓存中有线程存在，MySQL从缓存中删除一个线程，并且把它分配给这个新的连接。当连接关闭时，如果线程缓存还有空间的话，MySQL又会把线程放回缓存。如果没有空间的话，MySQL就会销毁这个线程。

只要MySQL在缓存中还有空闲的线程，它就可以迅速地响应连接请求，因为这样就不用为每个连接创建新的线程。

如何配置

thread_cache_size指定了MySQL可以保存在缓存中的线程数。一般不需要配置这个值，除非服务器会有很多连接请求。

一般，可以根据机器的内存进行设置：

# 1G  —> 8  
# 2G  —> 16  
# 3G  —> 32  
# 大于3G  —> 64或更大

# 6、tmp_table_size和max_heap_table_size

介绍

tmp_table_size：临时表的内存缓存大小，临时表是指sql执行时生成的临时数据表。在优化sql时，应该尽量避免临时表。 max_heap_table_size：该参数也会影响到临时表的内存缓存大小。在增加tmp_table_size的同时，也需要增加max_heap_table_size的大小。

如何配置

可以通过Created_tmp_disk_tables和Created_tmp_tables状态来分析是否需要增加tmp_table_size和max_heap_table_size。

#Created_tmp_disk_tables : 磁盘临时表的数量
#Created_tmp_tables      : 内存临时表的数量

mysql> show global status like 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 15668 |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Created_tmp_tables';
+--------------------+--------+
| Variable_name      | Value  |
+--------------------+--------+
| Created_tmp_tables | 737670 |
+--------------------+--------+
1 row in set (0.00 sec)

# 7、max_connections

介绍

MySQL的max_connections参数用来设置最大连接数。如果该参数设置太小，会导致出现“Too many connections”的错误。

如果服务器的并发连接请求量比较大，建议提高此值，以增加并行连接数量。但是这个是要建立在机器的性能能支撑的情况下，因为MySQL会为每一个连接提供连接缓冲区，如果并发连接数量太高，会导致消耗内存过多。

如何配置

如何判断max_connections设置的是否合理？

首先，查看最大连接上限：

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+

然后，可以查看服务器响应的最大连接数：

mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 3     |
+----------------------+-------+

可见，服务器历史最大连接数远远低于mysql服务器允许的最大连接上限。

对于mysql服务器最大连接上限的设置范围，最理想的是：服务器响应的最大连接数值占服务器上限连接数值的比例值在10%以上，如果在10%以下，说明mysql服务器最大连接上限值设置过高。

# 三、数据库表结构设计优化

# 1、InnoDB逻辑存储结构

InnoDB所有数据都存放在一个叫表空间（tablespace）的地方（ibdata1）。表空间由段（segment）、区（extent）、页（page）组成。InnoDB逻辑存储存储结构如下图：

注意，如果用户启用了参数innodb_file_per_table，则每张表内的数据（包括数据、索引和插入缓冲Bitmap页）可以单独放到一个表空间内，但是其他数据，如回滚信息、插入缓冲索引页、系统事务信息等还是存放在原来的共享表空间内。

# （1）段（segment）

常见的段有数据段、索引段、回滚段等

# （2）区（extent）

区是由连续的页组成的空间，每个区大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘中申请4~5个区。在默认情况下，InnoDB存储引擎页大小为16KB，即一个区中一共有64个连续的页。

# （3）页（page）

页是InnoDB磁盘管理的最小单位，默认每个页大小为16KB。常见的页类型有：

数据页(B-tree Node)
undo页(undo Log Page)
系统页(System Page)
事务数据页(Transaction system Page)
插入缓冲位图页(Insert Buffer Bitmap)
插入缓冲空闲列表页(Insert Buffer Free List)
未压缩的二进制大对象页(Uncompressed BLOB Page)
压缩的二进制大对象页(compressed BLOB Page)

这里重点将数据也的存储结构，该类型存放的是表中行的实际数据。

InnoDB数据也由以下7个部分组成，如下：

File Header用来记录页的一些头信息
Page Header用来记录数据页的状态信息
每个页中都有两个虚拟行记录，用来限定记录的边界：Infimum和Supremum Record，如下：

User Record是实际存储行记录的内容。
Page Directory存放了记录的相对位置，这些记录指针也称为Slots（槽）。在Slots中记录按照索引键值顺序存放，这样可以利用二叉查找迅速找到记录的指针。
File Trailer用于检测页是否完整地写入磁盘

# （4）行

InnoDB存储引擎是按行进行存放的。一个页中存放的行数据越多，其性能越高，这也是为什么创建字段的时候应该按照最小可用原则。 InnoDB存储引擎提供了Compact和Redundant两种格式来存放行记录数据，默认设置为Compact行格式。可以通过如下命令查看：

mysql> show table status like 't_ci_sessions'\G;
*************************** 1. row ***************************
           Name: t_ci_sessions
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 9
 Avg_row_length: 1820
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-06-10 13:04:27
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

Compact行记录格式：

Compact记录头信息的格式如下：

数据库表结构设计原则如下：

尽量避免过度设计，例如设计极其复杂查询的schema设计，或者有很多列的表设计
使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL值
尽量使用相同的数据类型存储相似的值，尤其是要在关联条件中使用的列
注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存
尽量使用整形定义标识列
小心使用ENUM和SET，最好避免使用BIT
范式是好的，但是反范式有时也是必须的，并且能带来好处。预先计算、缓存或生成汇总表也可能获得大的好处
ALTER TABLE是让人痛苦的操作，因为大部分情况下，它都会锁表并且重建整张表。解决方法有两种：一种是在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；另一种方法是“影子拷贝”，即用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表
在查找表时采用整数逐渐而避免采用基于字符串的值进行关联

# 四、索引设计优化

关键原则：通过分层缩小数据范围，提高数据区分度，扫描的记录数就越少。例如主键ID做索引，只有唯一一个，数据区分度最高。如果用例如性别做索引，则数据范围还是很大，数据区分度不高，索引效果就很差。

InnoDB存储引擎支持以下几种常见索引：

B+树索引
全文索引
哈希索引

这里我们重点介绍B+树索引，也是使用最频繁的。

B+树索引，可以分为聚集索引（clustered index）和辅助索引（secondary index）。两者区别是，叶子节点存放的是否一整行的信息。

# 1、数据结构和算法

B+树索引是目前关系型数据库系统中查找最为常用和最为有效的索引。B+树索引类似于二叉树，根据键值快速找到数据。

注意，B+树本身不一定找到具体的一条记录，能找到的只是该记录所在的页。数据库把页加载到内存，然后通过Page Directory再进行二分查找。

# （1）二分查找法

也叫折半查找法。详细算法原理不展开讲，网上有很多，可以查看：二分查找法

这里为什么要介绍二分查找法，是因为：

我们知道每个数据页中包含Page Directory，Page Directory中的槽是按照主键的顺序存放的，对于某一条具体记录的查询是通过对Page Directory进行二分查找法得到的。也就是说，通过B+树索引定位到具体页以后，就需要引用二分查找法定位具体的键值。

# （2）二叉查找树

二叉查找树的原理，请自行查阅。

# （3）平衡二叉树

平衡二叉树的原理，请自行查阅。

# （4）B+树

如下是一颗B+索引树，如果主键是按照顺序递增地，则新插入的数据只需追加到末尾或生成新的叶子节点即可，不会对前面的节点造成修改。而假如主键是随机并非递增的，则新插入的主键有可能需要插入到之前的叶子节点中，这就可能导致叶子节点的分裂以及B+树的重新平衡，这造成的代价是比较大的。这也是为什么主键索引建议顺序递增，而不建议采用类似md5作为主键或索引。

同样的，删除或者修改操作都会导致B+树进行重新平衡，也需要付出一定的代价。所以，使用索引，对于查询是非常友好的，可以极大提高速度，但是对于增、删、改操作者成本变高。

# 2、聚集索引

InnoDB的聚集索引实际上在同一个结构中保存了B-Tree索引和数据行，一个表只能有一个聚集索引。

如下图，聚集索引，叶子页包含了行的全部数据，但节点页只包含索引列。

# 3、辅助索引

辅助索引也称为非聚集索引，叶子节点并不包含行记录的全部数据。每个叶子节点除了索引列还包含主键列，通过这个主键列就可以找到一个完整的行记录。也就是说，通过辅助索引，除了首先要在辅助索引树中查找到主键，还需要在聚集索引树中通过该主键找到对应的完整行数据所在的页。这也是为什么辅助索引需要进行两次索引查找的原因。

如下图所示：

# 4、例子说明

如下表，我们有一个主键col1以及三个索引。假设主键取值为1~10000，按随机顺序插入并使用OPTIMIZE TABLE命令做优化。col2和col3随机赋值，数据如下表所示。

CREATE TABLE layout_test (
    id int NOT NULL,
    col2 varchar(16) NOT NULL DEFAULT '',
    col3 varchar(16) NOT NULL DEFAULT '',
    PRIMARY KEY(id),
    KEY(col2),
    KEY(col3)
);

行号	id	col2	col3
0	99	Tom	apple
1	12	Mary	banana
2	3000	John	milk
...	...	...	...
9997	18	Judi	tea
9998	4700	Tom	apple
9999	3	Peter	coffee

聚集索引叶子节点分布如下图所示（按id排序）：

以col2为索引列的辅助索引叶子节点分布图（按col2排序）：

以col3为索引列的辅助索引叶子节点分布图（按col3排序）：

覆盖索引

严格地说，覆盖索引只是辅助索引的一个衍生定义，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，所以它要比聚集索引小很多，因此一页包含的辅助索引数量更多，从而可以减少大量的IO操作。

# 5、总结

设计和使用索引时，可以遵循以下原则：

选择数据类型遵循小而简单的原则，这样做的好处是可以节省索引空间，对于较短的键值，索引页中能容纳更多的键值，这样查找速度也会提升。下表是mysql各个字段类型的大小和使用范围：

字段	存储大小(单位：字节)	最小值	最大值
TINYINT	1	-128	127
SMALLINT	2	-32768	32767
MEDIUMINT	3	-8,388,608	8,388,607 (838萬)
INT	4	-2,147,483,648	2,147,483,647 (21億)
BIGINT	8	-9,223,372,036,854,775,808	9,223,372,036,854,775,807 (922京)
FLOAT	4	-3.402823466E+38	3.402823466E+38
DOUBLE	8	-1.7976931348623157E+308	1.7976931348623157E+308
DECIMAL*	每9个数字4个字节	~ -1E+66	~ 1E+66
TIMESTAMP	4	1970（unix时间戳）	2038（unix时间戳）
DATETIME	8	1001年	9999年

所以，如果存储IP地址，使用UNSIGNED INT存储，刚好够用，比使用字符串占用更少空间，搜索更快。

同样的。而时间使用DATETIME存储，比使用字符串（19字节）足足少了11字节。

整形数据比起字符，处理开销更小，在MySQL中，建议使用内置的日期和时间数据类型，而不是用字符串来存储时间。
利用覆盖索引进行查询，避免回表。Explain返回的Using index就代表从索引中查询。这也是为什么要避免使用SELECT *的原因之一。
尽量指定列为NOT NULL，NULL会使索引、索引统计和值更加复杂，并且需要额外的存储空间。这个可以查看这篇文章《一千个不用NULL的理由》 (opens new window)。
建议在选择性高的列上建立索引，最好是唯一索引，区分度越大，则我们扫描的记录数越少，例如性别区分度不大，就不适合做索引。
更新非常频繁的数据不适合建索引。频繁更新会导致变更B+索引树，重建索引，这个过程很消耗数据库性能。
利用最左前缀原则，比如建立一个联合索引(a,b,c)，我们可以利用的索引就有(a),(a,b),(a,b,c)
如果确定有多少条数据，使用limit限制一下，MySQL在查找到对应条数的数据的时候，会停止继续查找
删除不再使用的索引
join语法，尽量将小的表放在前面，在需要on的字段上，数据类型保持一致，并设置素银，否则MySQL无法使用索引来Join查询
like "xxx%"可以用到索引，like"%xxx%"则不行
在设计开发阶段，数据库字段的定义要避免出现由数据类型定义不当造成的隐式转换

# 五、SQL查询优化

# 1、分解关联查询

很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行一次单表查询，然后将结果都再应用程序中进行管理。如下这个查询:

mysql> 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;

可以分解成下面这些查询来代替：

mysql> SELECT * FROM tag WHERE tag='mysql';
mysql> SELECT * FROM tag_post WHERE tag_id=1234;
mysql> SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

用分解关联查询的方式重构查询有如下的优势：

让缓存的效率更高。
将查询分解后，执行单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
查询效率也有可能会有所提升。
可以减少冗余记录的查询。
更进一步，这样做相当于在应用层中实现了哈希关联，而不是是使用MySQL的嵌套循环关联。

# 2、优化COUNT()查询

count()可以统计某个列值的数量，也可以统计行数。

一个常见的错误是，在括号内指定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数，最好使用COUNT(*)，这样写意义清晰，性能也会很好。

一般，COUNT都需扫描大量的行才能获得精确的结果，因此很难优化。在MySQL层面还能做的就只有索引覆盖扫描（不回表查询）。如果这样还不能满足，就需要考虑修改应用架构或者增加缓存（如redis）统计。

# 3、关联查询优化

确保ON或者USING子句的列上有索引。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引
确保任何GROUP BY和ORDER BY中的表达式值涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。

# 4、优化子查询

关于子查询优化，建议就是尽可能使用关联查询代替。

# 5、优化GROUP BY和DISTINCT

如果需要对关联查询做分组（GROUP BY)，并且是按照查找表中的某个列进行分组，那么通常采用查找表的主键分组的效率比其他列更高。
如果没有通过ORDER BY子句显示地指定排序列，当查询使用GROUP BY子句的时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的顺寻，而这种默认又导致了需要文件排序，则可以使用ORDER BY NULL，让MySQL不再进行文件排序。

# 6、优化LIMIT分页

一个非常常见的问题是，在偏移量很大的时候，例如：limit 10000,20这样的查询，MySQL需要查询10020条记录后只返回最后20条，前面10000条记录都将被抛弃，这样代价非常高。针对这个问题，有以下几个方法：

最简单的办法就是尽可能地使用覆盖扫描，而不是查询所有的列。

例如下面的查询：
```
mysql> SELECT file_id,description FROM sakila.film ORDER BY title LIMIT 50,5
```
如果表数据非常大，可以改成如下:
```
mysql> SELECT file_id,description FROM sakila.film
    -> INNER JOIN(
    ->    SELECT file_id FROM sakila.film ORDER BY title LIMIT 50,5    
    ) AS lim USING(film_id);
```
这里“延迟关联”大大提高了查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后在根据关联列回原表查询需要的所有列
可以记录上次取数据的主键ID位置，那么下次就可以直接从该记录的位置开始扫描，这样就可以避免OFFSET导致MySQL扫描大量不需要的行然后抛弃掉。

例如：
```
mysql> SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20;
```
假如上面的查询返回的是主键为16049到16030的记录，那么下一页查询就可以从16030这个点开始：
```
mysql> SELECT * FROM sakila.rental WHERE rental_id < 16030 ORDER BY rental_id DSEC LIMIT 20;
```
该技术的好处是，无论翻页到多么后面，性能都会很好。

# 7、优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询。在UNION查询优化中，经常需要手工地将WHERE,LIMIT,ORDER BY等子句下推到UNION的各个子查询中，一边优化器可以充分利用这些条件进行优化。
除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这回导致对整个临时表的数据做唯一性检查，代价很高。

# 六、读写分离

读写分离是数据库架构中的一种常见模式，主要是为了解决数据库的性能瓶颈。读写分离的基本原理是将读操作和写操作分开，分别发送到不同的服务器上执行，从而提高数据库的处理能力和性能。

在MySQL中，实现读写分离架构通常使用主从复制技术。主从复制技术是指设置一个主数据库（Master）和一个或多个从数据库（Slave）。主数据库处理所有的写操作（INSERT、UPDATE、DELETE），从数据库处理所有的读操作（SELECT）。主数据库在处理完写操作后，会把数据的变更记录到二进制日志（Binary Log）中，然后从数据库通过复制线程将这些变更同步到自己的数据库中。

# 物理层设计

下面是实现MySQL读写分离架构的步骤：

配置主数据库

在主数据库的配置文件（my.cnf或my.ini）中，需要开启二进制日志，并设置一个唯一的服务器ID。例如：

[mysqld]
log-bin=mysql-bin
server-id=1

然后重启MySQL服务使配置生效。

配置从数据库

在从数据库的配置文件中，也需要设置一个唯一的服务器ID（不能和主数据库的ID相同）。例如：

[mysqld]
server-id=2

然后重启MySQL服务使配置生效。

在主数据库中创建一个用于复制的用户，并授权：

GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

在从数据库中配置主数据库的信息：

CHANGE MASTER TO
  MASTER_HOST='master_host',
  MASTER_USER='replica',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=0;

然后启动从数据库的复制线程：

START SLAVE;

以上就是实现MySQL读写分离架构的基本步骤。在应用程序中，需要根据操作类型（读或写）将数据库请求发送到相应的数据库服务器（主或从）。这通常可以通过数据库中间件或连接池来实现。

需要注意的是，读写分离架构虽然可以提高数据库性能，但也会带来数据一致性的问题。因为主从数据库的数据同步是有延迟的，如果一个写操作后立即进行读操作，可能会读到旧的数据。因此，对数据一致性要求很高的系统需要谨慎使用读写分离架构。

当然，如果使用云计算产品，例如腾讯云、阿里云等云上的CDB产品，他们本身就自带支持设置读写分离以及异地的架构。

# 应用层设计

要通过数据库中间件实现读写分离，首先需要创建一个中间件，该中间件可以拦截数据库请求，并根据请求类型（读或写）将请求路由到不同的数据库服务器（主或从）。在Golang中，可以使用database/sql包来实现这个中间件。

以下是使用Golang实现一个简单的读写分离中间件的示例：

安装Golang的MySQL驱动：

go get -u github.com/go-sql-driver/mysql

创建一个名为dbmiddleware.go的文件，实现读写分离中间件：

package main

import (
    "database/sql"
    "fmt"
    "strings"

    _ "github.com/go-sql-driver/mysql"
)

type DBMiddleware struct {
    master *sql.DB
    slave  *sql.DB
}

func NewDBMiddleware(masterDSN, slaveDSN string) (*DBMiddleware, error) {
    master, err := sql.Open("mysql", masterDSN)
    if err != nil {
        return nil, fmt.Errorf("failed to connect to master: %w", err)
    }

    slave, err := sql.Open("mysql", slaveDSN)
    if err != nil {
        return nil, fmt.Errorf("failed to connect to slave: %w", err)
    }

    return &DBMiddleware{
        master: master,
        slave:  slave,
    }, nil
}

func (dbm *DBMiddleware) Exec(query string, args ...interface{}) (sql.Result, error) {
    return dbm.master.Exec(query, args...)
}

func (dbm *DBMiddleware) Query(query string, args ...interface{}) (*sql.Rows, error) {
    if isWriteOperation(query) {
        return dbm.master.Query(query, args...)
    }
    return dbm.slave.Query(query, args...)
}

func isWriteOperation(query string) bool {
    // 简单判断是否为写操作，实际项目中应该更严格地检查SQL语句
    return strings.HasPrefix(strings.ToUpper(strings.TrimSpace(query)), "INSERT") ||
        strings.HasPrefix(strings.ToUpper(strings.TrimSpace(query)), "UPDATE") ||
        strings.HasPrefix(strings.ToUpper(strings.TrimSpace(query)), "DELETE")
}

func main() {
    // 示例代码，使用DBMiddleware进行读写操作
    dbm, err := NewDBMiddleware("user:password@tcp(master_host:3306)/dbname", "user:password@tcp(slave_host:3306)/dbname")
    if err != nil {
        fmt.Println("Error:", err)
        return
    }

    // 写操作
    result, err := dbm.Exec("INSERT INTO users (name, age) VALUES (?, ?)", "John", 30)
    if err != nil {
        fmt.Println("Error:", err)
        return
    }
    fmt.Println("Insert result:", result)

    // 读操作
    rows, err := dbm.Query("SELECT * FROM users")
    if err != nil {
        fmt.Println("Error:", err)
        return
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var name string
        var age int
        err = rows.Scan(&id, &name, &age)
        if err != nil {
            fmt.Println("Error:", err)
            return
        }
        fmt.Printf("User: id=%d, name=%s, age=%d\n", id, name, age)
    }
}

在这个示例中，我们创建了一个名为DBMiddleware的结构体，它包含两个*sql.DB对象，分别表示主数据库和从数据库。然后，我们实现了Exec和Query方法，分别用于执行写操作和读操作。在Query方法中，我们根据SQL语句是否为写操作，将请求路由到主数据库或从数据库。

需要注意的是，这个示例中的isWriteOperation函数只是一个简单的判断，实际项目中应该更严格地检查SQL语句。此外，这个示例没有考虑连接池、错误处理和事务等高级功能。在实际项目中，应该根据需求进行相应的扩展和优化。

# 七、分库、分表、分区

为了适应大数据量（亿级别）的业务场景，需要对架构进行分层设计，这里采用类似地图的分层理念：国家->省份->城市->镇->乡。

分层越多，具体到最底层的级别，数据量的级别就下降越多，查询速度就越快。

我们这里就采用三层结构

# 第一层：分库（按业务分db）

这里可以根据实际的业务模块进行数据库的划分，可以避免一个业务的数据量过大导致影响到其他业务的数据库查询。

具体到实际的操作划分，有几种方案可以参考：

一种是根据业务模块将数据分布到不同的数据库中

例如，您可以将用户信息、订单信息、商品信息等分别存储在不同的数据库中。这样可以降低单个数据库的压力，提高系统的可扩展性。

示例：
- 用户库：存储用户相关信息，如用户基本信息、用户登录记录等；
- 订单库：存储订单相关信息，如订单详情、支付记录等；
- 商品库：存储商品相关信息，如商品基本信息、库存信息等。
根据URL的访问路径进行路由来划分db，例如：

http://xxxx.com/v1/api/workspace_id/execution/xxx

可以对/v1/api/workspace_id进行路由区分到不同db

如下，我们把数据库中数据量最大的appinst表和execution表分离出来，剩下的其他表由于数据量比较少就放在同一个数据库里。

而对于不同的业务，可以使用不同的数据库来存储appinst表和execution表。

当用户访问workflow数据时，就去总表查询，如果想要访问具体的execution和appinst执行数据，则要根据前面的访问路径进行路由到某个数据库上。

# 第二层：分表（按周分表）

对于访问量较大的数据表，可以按照时间维度进行分表，例如按周分表操作。这样可以减少单表的数据量，提高查询效率。

我们用上一节snowflake雪花算法生成的uid作为execution_xx和appinst_xx表的主键。

同时，表明的命名格式如下：

execution_年周，例如今天是2023年9月，则是第39周，那么execution表名为：execuiton_202339 appinst_年周，例如今天是2023年9月，则是第39周，那么appinst表名为：appinst_202339

根据雪花算法的生成原理，我们知道雪花算法的第二部分是毫秒时间戳。通过这个时间戳我们可以知道对应数据存储到哪一张表里。

这种根据每周来对表明进行划分，需要对下一周的表提前创建。

例如下面的分表方式：

mysql> show tables;
+-------------+
| Tables_in_test|
+-----------+
| tb_202013 |
| tb_202014 |
| tb_202015 |
| tb_202016 |
| tb_202017 |
| tb_202018 |
| tb_202019 |
| tb_202020 |
| tb_202021 |
| tb_202022 |
| tb_202023 |
| tb_202024 |
| tb_202025 |
| tb_202026 |
| tb_202027 |
| tb_202028 |
| tb_202029 |
| tb_202030 |
+-----------+

然后再按照表中某个字段，如手机号、用户id去hash分区：

CREATE TABLE `tb_202013` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `telephone` int(10) NOT NULL DEFAULT '0'
  //...
) ENGINE=InnoDB AUTO_INCREMENT=202013278841900 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (telephone)
PARTITIONS 4 */ |

# 第三层：分区（按用户名做hash分区）

MySQL的分区是一种将数据分散到多个独立的物理分区的数据库设计方案。分区表现得就像一个普通的MySQL表，但实际上，它是由多个子表（分区）组成的，每个分区包含表的一部分数据。分区可以基于多种方式，比如按照范围、列表、哈希或键进行。

分区能提高查询和写入效率的原因主要有以下几点：

分区剪枝（Partition Pruning）：当执行查询时，MySQL能够确定只从一个或少数几个分区中检索数据，而不是全表扫描。这大大减少了查询所需检查的数据量，从而提高查询速度。
并行执行：在某些情况下，如果查询涉及多个分区，MySQL可以并行地从每个分区检索数据，从而提高查询速度。
数据管理：通过分区，可以更容易地管理数据。例如，可以通过删除一个分区来快速地删除大量数据，这比删除表中的行要快得多。
I/O吞吐量：如果分区位于不同的物理设备上，可以利用更多的硬件并行性来提高查询速度，因为I/O操作可以在多个设备上并行进行。
写入效率：对于插入和更新操作，如果它们能够定位到特定的分区，那么这些操作只会锁定该分区，而不会影响到其他分区，从而提高并发性能。

所以，分区可以提高大型表的查询性能，简化大量数据的管理，并可能提高多用户并发性能。但是，分区并不总是适用，对于小型表或不符合分区策略的查询，分区可能不会带来性能提升，甚至可能导致性能下降。因此，是否使用分区需要根据具体情况来决定。

例如，可以将用户表按照用户ID的hash值进行分区，将数据分布到不同的表空间中。

示例，用户表：将用户表按照用户ID的hash值分为多个分区，如 user_partition_1, user_partition_2, user_partition_3...，每个分区存储一部分用户数据。

MySQL支持以下几种分区方式：

# RANGE分区

基于给定列的连续范围值进行分区。例如，按日期范围分区。

创建RANGE分区的示例：

CREATE TABLE orders (
  order_id INT,
  user_id INT,
  order_date DATE
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p0 VALUES LESS THAN (2000),
  PARTITION p1 VALUES LESS THAN (2010),
  PARTITION p2 VALUES LESS THAN (2020),
  PARTITION p3 VALUES LESS THAN MAXVALUE
);

这个示例中，orders表按照order_date的年份进行RANGE分区，分为4个分区。分区p0包含2000年以前的数据，分区p1包含2000-2009年的数据，分区p2包含2010-2019年的数据，分区p3包含2020年及以后的数据。

使用EXPLAIN分析查询：

mysql> EXPLAIN SELECT * FROM orders WHERE order_date >= '2010-01-01' AND order_date <= '2019-12-31';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | orders | p2         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

在查询结果中，可以看到MySQL只会扫描分区p2，因为它知道这个时间范围的数据只存在于这个分区中。这就是分区剪枝，它可以提高查询性能。

# LIST分区

基于给定列的离散值列表进行分区。例如，按国家/地区代码分区。

创建LIST分区的示例：

CREATE TABLE users (
  user_id INT,
  user_name VARCHAR(50),
  country_code CHAR(10),
  country_id INT
)
PARTITION BY RANGE (country_id) (
  PARTITION p_us VALUES LESS THAN (2),
  PARTITION p_uk VALUES LESS THAN (3),
  PARTITION p_other VALUES LESS THAN MAXVALUE
);

在这个示例中，我们为每个国家/地区分配了一个整数ID（例如，'US'为1，'UK'为2，'OTHER'为3）。然后我们根据country_id进行RANGE分区。

使用EXPLAIN分析查询：

mysql> EXPLAIN SELECT * FROM users WHERE country_id = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | p_us       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

在查询结果中，可以看到MySQL只会扫描分区p_us，因为它知道美国用户的数据只存在于这个分区中。

# HASH分区

基于给定列的哈希值进行分区。这种分区方式可以实现均匀的数据分布。

创建HASH分区的示例：

CREATE TABLE products (
  product_id INT,
  product_name VARCHAR(50),
  price DECIMAL(10, 2)
)
PARTITION BY HASH (product_id)
PARTITIONS 4;

这个示例中，products表按照product_id的哈希值进行分区，分为4个分区。数据将根据product_id的哈希值均匀分布在这4个分区中。

使用EXPLAIN分析查询：

mysql> EXPLAIN SELECT * FROM products WHERE product_id = 100;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | products | p0         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

在查询结果中，可以看到MySQL会根据product_id的哈希值定位到一个特定的分区p0，然后只扫描这个分区。

# KEY分区

类似于HASH分区，但是使用MySQL的内置哈希函数进行分区。当分区键是字符串时，这种分区方式比HASH分区更高效。

创建KEY分区的示例：

CREATE TABLE sessions (
  session_id CHAR(32),
  user_id INT,
  login_time DATETIME
)
PARTITION BY KEY (session_id)
PARTITIONS 4;

这个示例中，sessions表按照session_id的哈希值进行分区，分为4个分区。数据将根据session_id的哈希值均匀分布在这4个分区中。

使用EXPLAIN分析查询：

mysql> EXPLAIN SELECT * FROM sessions WHERE session_id = 'abcdef1234567890abcdef1234567890';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sessions | p0         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

在查询结果中，可以看到MySQL会根据session_id的哈希值定位到分区p0，然后只扫描这个分区。

综上，可以根据实际业务需求和数据量进行分库、分表、分区的设计。同时，还需要注意以下几点：

在进行数据库架构优化时，需要考虑数据迁移和同步的问题，确保数据的一致性和完整性。
在应用层，需要实现相应的路由策略，将请求分发到正确的数据库、表和分区中。
需要定期对数据库进行监控和维护，以确保系统的稳定性和性能。

# 八、上线后监控优化

MySQL数据库上线后，可以等其稳定运行一段时间后再根据服务器的status装填进行适当优化，可以用如下命令列出MySQL服务器运行的各种状态值。

# 查询所有状态值
show global status;
# 查询具体某个状态值
show status like '%xx%';

从我实际的线上系统运维经验来看，CDB上线后的监控最最重要的还是监控慢查询日志，及时发现慢查询SQL并优化修复，可以解决很多问题。大部分时候，我们碰到的CDB高负载，很多是因为慢查询语句堆积导致的。

如果使用的是云厂商的CDB产品，可以直接使用他们的慢查询日志查询功能：

现在很多云厂商的CDB在这方面的监控已经做得非常好了，下面是某个云厂商CDB的监控，但是如果自建CDB的话就需要自己去关注各种指标。

# 1、慢查询

为了定位系统中效率比较低下的查询语句，需要通过慢查询日志来定位：

mysql> show variables like '%slow%';
+---------------------------+---------------------------------------------+
| Variable_name             | Value                                       |
+---------------------------+---------------------------------------------+
| log_slow_admin_statements | OFF                                         |
| log_slow_slave_statements | OFF                                         |
| slow_launch_time          | 2                                           |
| slow_query_log            | ON                                          |
| slow_query_log_file       | /data1/mysql_root/data/20144/slow_query.log |
+---------------------------+---------------------------------------------+

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 18014 |
+---------------------+-------+

一个好的性能分析工具，能很好地提高数据库性能的管理效率，而pt-query-diget就是专门针对MySQL数据库慢查询的一个分析工具，相比于官方的mysqldumpslow，这个工具分析结果更加具体完善。

# （1）pt-query-digest工具安装

# 安装最新的 percona release package
yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# 安装 percona toolkit
yum -y install percona-toolkit

# 验证 pt-queyr-digest 是否可以使用
pt-query-digest --help

# （2）慢查询日志分析

首先获取MySQL服务器上慢查询日志的路径：

mysql> show variables like '%slow_query_log%';
+---------------------+----------------------+
| Variable_name       | Value                |
+---------------------+----------------------+
| slow_query_log      | ON                   |
| slow_query_log_file | /data/mysql/slow.log |
+---------------------+----------------------+

然後通過pt-query-digest工具分析下，看看是否有什么问题：

 pt-query-digest --report /data/mysql/slow.log  /data/mysql/report.log

分析报告分为三部分：

# 第一部分：总体统计结果

# 700ms user time, 20ms system time, 27.61M rss, 214.82M vsz
# Current date: Mon Jun 10 09:13:13 2019
# Hostname: VM_16_17_centos
# Files: /data/mysql/slow.log
# Overall: 3.24k total, 12 unique, 0.01 QPS, 0.01x concurrency ___________
# Time range: 2019-06-03 13:18:15 to 2019-06-08 17:25:52
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          4296s      1s    194s      1s      2s      5s      1s
# Lock time          244ms       0     3ms    75us   103us    72us    66us
# Rows sent          3.13k       0      10    0.99    0.99    0.22    0.99
# Rows examine       4.14G       0   1.38M   1.31M   1.32M 228.71k   1.32M
# Query size       781.01k      15     425  247.14  246.02   27.68  246.02

参数说明：

Overall: 总共有多少条查询，上例为总共3.24k个查询。
Time range: 查询执行的时间范围。
unique: 唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询，该例为12。
total: 总计 min:最小 max: 最大 avg:平均
95%: 把所有值从小到大排列，位置位于95%的那个数，这个数一般最具有参考价值。
median: 中位数，把所有值从小到大排列，位置位于中间那个数。

# 第二部分: 查询分组统计结果:

# Profile
# Rank Query ID                     Response time   Calls R/Call   V/M   I
# ==== ============================ =============== ===== ======== ===== =
#    1 0x523892349F733C254576AB7... 3731.1533 86.8%  3140   1.1883  0.08 SELECT t_xxx_result_? t_xxx_plugin_?
#    2 0xA72A99A2AD696A84F9CCD57...  360.7725  8.4%     2 180.3862  2.08 DELETE t_xxx_result_?
#    4 0x40485EE0C3F3C643A5035DC...   62.4520  1.5%    34   1.8368  0.56 SELECT
#    5 0x266AA5D17D7ACC44DEC38DA...   53.5156  1.2%    40   1.3379  0.02 REPLACE t_xxx_result_?
# MISC 0xMISC                         88.5263  2.1%    20   4.4263   0.0 <8 ITEMS>

这部分对查询进行参数化并分组，然后对各类查询的执行情况进行分析，结果按总执行时长，从大到小排序。

参数说明：

Response: 总的响应时间。
time: 该查询在本次分析中总的时间占比。
calls: 执行次数，即本次分析总共有多少条这种类型的查询语句。
R/Call: 平均每次执行的响应时间。
Item : 查询对象

# 第三部分：每一种查询的详细统计结果:

这里我们以第一个查询为例:

# Query 1: 0.06 QPS, 0.07x concurrency, ID 0x523892349F733C254576AB7240430D5A at byte 204852
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.08
# Time range: 2019-06-04 19:44:09 to 2019-06-05 10:20:42
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         97    3140
# Exec time     86   3731s      1s      3s      1s      2s   314ms      1s
# Lock time     97   237ms    48us     2ms    75us   103us    53us    66us
# Rows sent     98   3.07k       1       1       1       1       0       1
# Rows examine  99   4.13G 677.82k   1.38M   1.35M   1.32M  72.24k   1.32M
# Query size    97 760.47k     248     248     248     248       0     248
# String:
# Databases    vul_xxx
# Hosts        localhost
# Users        scan
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `vul_xxx` LIKE 't_xxx_result_0'\G
#    SHOW CREATE TABLE `vul_xxx`.`t_xxx_result_0`\G
#    SHOW TABLE STATUS FROM `vul_xxx` LIKE 't_xxx_plugin_xxx'\G
#    SHOW CREATE TABLE `vul_xxx`.`t_xxx_plugin_xxx`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT count(distinct resultId) as cnt, p.level
FROM `t_xxx_result_0` as `result`
JOIN `t_xxx_plugin_xxx` as `p` ON (`result`.`pId`=p.pId)
WHERE `tId` = '100011'
GROUP BY `level`
ORDER BY `level` DESC\G

我们通过explain来分析该sql语句：

mysql> explain SELECT count(distinct resultId) as cnt, plugin.level FROM `t_xxx_result` as `result` JOIN `t_plugin` as `plugin` ON (`result`.`pId`=plugin.pId) WHERE `tId` = '1001' GROUP BY `level` ORDER BY `level` DESC\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: result
         type: ref
possible_keys: tId,pId
          key: tId
      key_len: 4
          ref: const
         rows: 1
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: plugin
         type: eq_ref
possible_keys: PRIMARY,level
          key: PRIMARY
      key_len: 4
          ref: vul_xxx.result.pId
         rows: 1
        Extra: NULL
2 rows in set (0.00 sec)

可见，这里使用了文件排序，导致了查询速度变慢。

通过pt-query-digest定位到具体的问题sql语句，然后通过explain具体分析sql语句并优化。

# 2、连接数

如果经常遇到MySQL：ERROR 1040：Too many connections的情况，一般有两种可能：

访问量确实很高，MySQL服务器扛不住，这个时候需要考虑增加从服务器分散读压力
MySQL配置文件中max_connections的值过小，达到了max_connections限制。此时，它就会开始拒绝新连接，同时Connection_errors_max_connections指标会开始增加，追踪所有失败连接尝试的Aborted_connects指标也会开始增加。

（1）查询MySQL服务器的最大连接数

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+

mysql> show global status like '%errors_max_connections%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Connection_errors_max_connections | 0     |
+-----------------------------------+-------+

mysql> show global status like '%Aborted_connects%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_connects | 216   |
+------------------+-------+

（2）查询MySQL服务器历史的最大连接数

mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 3     |
+----------------------+-------+

由于MySQL历史最大的连接数只有3，没有达到MySQL服务器最大连接数上限1000，所以不会出现1040错误。

# 3、缓存使用情况

key_buffer_size是设置MyISAM表索引缓存空间的大小，此参数对MyISAM表性能影响最大。下面是一台MyISAM为主要存储引擎服务器的配置：

mysql> show variables like 'key_buffer_size';
+-----------------+-----------+
| Variable_name   | Value   |
+-----------------+-----------+
| key_buffer_size | 536870912 |
+-----------------+-----------+

从上面知道，分配了512MB内存给key_buffer_size。再来看看key_buffer_size的使用情况：

mysql> show global status like 'key_read%';
+-------------------+--------------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 27813678766 |
| Key_reads          |  6798830      |
+-------------------+--------------+

一共有27813678766个索引读取请求，有6798830个请求在内存中没有找到，直接从硬盘读取索引。

key_cache_miss_rate = key_reads /key_read_requests * 100%

比如上面的数据，key_cache_miss_rate为0.0244%，效果非常好，key_cache_miss_rate在0.1%以下都很好，如果key_cache_miss_rate在0.01%以下的话，说明key_cache_miss_rate分配得过多，可以适当减少。

# 4、临时表

每次创建临时表，Created_tmp_tables都会增加，如果在磁盘上创建临时表，Created_tmp_disk_tables也会增加，Created_tmp_files表示MySQL服务创建临时文件数。

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 502   |
+-------------------------+-------+

理想的配置如下：

Created_tmp_disk_tables/ Created_tmp_files * 100% <= 25%

查看MySQL服务器对临时表的配置：

mysql> 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 |
+---------------------+----------+

# 5、线程使用情况

如果在MySQL服务器的配置中设置了thread_cache_size，当客户端断开时，服务器处理此客户请求的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达到上线）。即可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能。查看命令如下：

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 512   |
+-------------------+-------+

查看当前MySQL服务器的线程使用情况：

mysql> show global status like 'thread%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Threadpool_idle_threads | 0     |
| Threadpool_threads      | 0     |
| Threads_cached          | 381   |
| Threads_connected       | 5     |
| Threads_created         | 386   |
| Threads_running         | 2     |
+-------------------------+-------+

mysql> show global status like 'Connection_errors_internal';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Connection_errors_internal | 0     |
+----------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Aborted_connects';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| Aborted_connects | 1044566 |
+------------------+---------+
1 row in set (0.00 sec)

mysql> show global status like 'Connection_error_max_connections';
Empty set (0.00 sec)

说明：

Threads_cached：代表当前此时此刻线程缓存中有多少空闲线程
Threads_connected：代表当前已建立连接的线程数量，每个连接对应一个线程。当所有可用连接都被占用时，如果一个客户端试图连接至MySQL，后者会返回"Too many connections"的错误，同时将Connection_errors_max_connections的值增加
Threads_created：代表从最近一次服务启动，已创建线程的数量
Threads_running：当前运行的连接
Connection_errors_internal：由于服务器内部本身导致的错误
Aborted_connects：尝试与服务器建立连接但是失败的次数

# 6、缓冲池利用情况

InnoDB在内存中使用一片区域作为缓冲区，用来缓存数据表、索引等数据（如上表），缓冲区太小，会导致数据库性能下降，致使磁盘IO增加。

我们知道，内存的读取速度比磁盘读取速度要快很多，当Innodb_buffer_pool_reads的值开始增加，就意味着数据库性能有可能出现问题。

mysql> show global status like 'Innodb_buffer_pool_read%';
+---------------------------------------+---------------+
| Variable_name                         | Value         |
+---------------------------------------+---------------+
| Innodb_buffer_pool_read_ahead_rnd     | 0             |
| Innodb_buffer_pool_read_ahead         | 60272         |
| Innodb_buffer_pool_read_ahead_evicted | 0             |
| Innodb_buffer_pool_read_requests      | 3959122635741 |
| Innodb_buffer_pool_reads              | 30673         |
+---------------------------------------+---------------+

说明：

Innodb_buffer_pool_read_requests：总共从缓冲池中缓存的页面中读取出的页数
Innodb_buffer_pool_reads：从磁盘上一页一页的读取的页数，从缓冲池中读取页面，若缓冲池里面没，就会从磁盘读取

缓冲池利用率是在考虑扩大缓冲池之前应该检查的重要指标，可以通过如下方式计算得到：

(Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) / 
 Innodb_buffer_pool_pages_total

另一个重要的指标，缓冲池的命中率计算方法：

(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) /
Innodb_buffer_pool_read_requests * 100%

mysql> show global status like '%buffer_pool_pages%';
+----------------------------------+-------------+
| Variable_name                    | Value       |
+----------------------------------+-------------+
| Innodb_buffer_pool_pages_data    | 852799      |
| Innodb_buffer_pool_pages_dirty   | 31          |
| Innodb_buffer_pool_pages_flushed | 30796616008 |
| Innodb_buffer_pool_pages_free    | 9544        |
| Innodb_buffer_pool_pages_misc    | 52537       |
| Innodb_buffer_pool_pages_total   | 914880      |
+----------------------------------+-------------+

缓冲池转化为字节大小的计算公式：

Innodb_buffer_pool_pages_total * innodb_page_size
# 上面的配置对应的缓冲池大小为：
# 16KB * 914880 / 1024 / 1024 = 14.295GB

innodb_page_size页面大小是可调整的，默认是16384字节，即16KB，可以通过如下命令查看：

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

也可以通过innodb_buffer_pool_size直接获取缓冲池大小：

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-------------+
| Variable_name           | Value       |
+-------------------------+-------------+
| innodb_buffer_pool_size | 14989393920 |
+-------------------------+-------------+

# 7、磁盘排序

监控磁盘是否出现磁盘排序命令：

mysql> show global status like 'sort_merge_passes';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Sort_merge_passes | 2169684 |
+-------------------+---------+
1 row in set (0.00 sec)

sort_merge_passes：必须要做归并排序的次数

# 8、查看网络传输量

mysql> show global status like 'bytes_received';
+----------------+---------------+
| Variable_name  | Value         |
+----------------+---------------+
| Bytes_received | 2604213103777 |
+----------------+---------------+
1 row in set (0.00 sec)

mysql> show global status like 'bytes_sent';
+---------------+---------------+
| Variable_name | Value         |
+---------------+---------------+
| Bytes_sent    | 7074371933580 |
+---------------+---------------+
1 row in set (0.00 sec)

# 9、吞吐量

MySQL中有各种不同的统计指标，其监控指标多以Com_xxx的方式命名，比较常用的有QPS和TPS。

MySQL与QPS相关的三个监控项，分别为Queries、Questions、Com_select，一般我们采用Com_select作为QPS的指标。同样，对于TPS，采用Com_insert + Com_update + Com_delete三个统计项之和作为指标。

mysql> show global status like 'Com_select';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| Com_select    | 6552507251 |
+---------------+------------+
1 row in set (0.00 sec)

mysql> show global status like 'Com_insert';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| Com_insert    | 1940243238 |
+---------------+------------+
1 row in set (0.00 sec)

mysql> show global status like 'Com_update';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| Com_update    | 1493408493 |
+---------------+------------+
1 row in set (0.00 sec)

mysql> show global status like 'Com_delete';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_delete    | 1966462 |
+---------------+---------+
1 row in set (0.00 sec)

# 10、查看请求来源

有时候数据库并发量过大，想要定位是那个ip频繁请求，可以使用如下命令：

mysql> SELECT substring_index(host, ':',1) AS host_name,state,count(*) FROM information_schema.processlist GROUP BY state,host_name;
+---------------+------------------------------------------------------------------+----------+
| host_name     | state                                                            | count(*) |
+---------------+------------------------------------------------------------------+----------+
|               |                                                                  |        2 |
| 10.5.xx.xx |                                                                  |        3 |
| 10.5.9.x    | executing                                                        |        1 |
|               | Master has sent all binlog to slave; waiting for binlog to be up |        1 |
| 10.5.xx.xx | update                                                           |       62 |
+---------------+------------------------------------------------------------------+----------+

← 11.2 分布式系统基础 11.4 全局唯一ID生成器 →