转载请备注来源： 《MySQL性能优化系统整理》 | shuwoom.com
在实际开发过程中，深深感到MySQL的性能优化对产品的性能重要性，设计好的表结构、索引以及SQL查询对后台接口性能是有很大的帮助。很多应用，数据量如果不大，很多时候是看不出MySQL设计上放方方面面的问题。当数据量达到千万级别甚至是上亿的数据量时，MySQL性能优化的技术就至关重要了。
有感于MySQL性能优化的重要性，我特别花了很多时间，系统性地梳理了MySQL性能优化的方方面面知识，从配置文件的参数选择、库表结构的设计原则、索引的优化设计、SQL查询的优化，以及上线后的监控指标等系统讲解。
一、介绍
数据库的优化可以从以下四个方面做优化，其效果更投入成相反，即：
效果：
SQL及索引 > 数据库表结构 > 系统配置 > 硬件
成本：
硬件 > 系统配置 > 数据库表结构 > SQL及索引
硬件不用说，当然是配置越高越好，最好是专门用于数据库的服务器，并采用ssd的，不过这个成本是最高的，我们主要讲解剩下3种的优化思路。当然MySQL的优化工作是持续进行的，并不是一次性完成。我们可以在3个阶段持续进行优化，分别是：产品上线前的数据库设计（系统配置、数据库表结构、SQL及索引设计）以及上线后的持续监控优化（慢查询、mysql指标监控）。
当然，对于业务、产品的优化，并不单单只是针对MySQL进行优化，还可以通过其他方面来进行，例如缓存的应用。
整体的优化思路可以按照如下的原则逐步进行：
优先考虑通过缓存降低对数据库的读操作（如：redis）
再考虑读写分离，降低数据库写操作
最后开始数据拆分，切分模式：首先垂直（纵向）拆分、再次水平拆分
首先考虑按照业务垂直拆分
再考虑水平拆分：先分库（设置数据路由规则，把数据分配到不同的库中）
最后再考虑分表，单表拆分到数据1000万以内
二、配置文件优化
以下针对的InnoDB引擎的配置文件my.cnf：
[client]
socket = /var/lib/mysql/mysql.sock
port = 3306
[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
1、innodb_buffer_pool_size
（1）介绍
InnoDB使用一个缓冲池来保存索引和原始数据，如下图所示：
（2）优缺点
缓冲池的作用可以减少磁盘访问，我们知道内存读写速度比磁盘的读写速度快很多，所以这个参数对mysql性能有很大提升。当然，这里不是越大越好，也要考虑实际的服务器情况。总之，InnoDB严重依赖缓冲池，我们必须为它分配了足够的内存。
更大的缓冲池会使得mysql服务在重启和关闭的时候花费很长时间。
（3）如何配置
如果在一个独立使用的mysql服务器上，这个变量按照流行的经验法则，可以把缓冲池大小设置为服务器内存的约75%~80%。
但是，如果服务器上除了跑mysql服务，还有其他服务也在运行，那么在分配缓冲池空间时，需要减去这部分程序占用的内存、mysql自身需要的内存以及减去足够让操作系统缓存InnoDB日志文件的内存，至少是足够缓存最近经常访问的部分。
2、innodb_log_file_size和innodb_log_files_in_group
（1）介绍
InnoDB使用日志来减少提交事务时的开销。
InnoDB用日志把随机I/O变成顺序I/O。
innodb_log_files_in_group 参数控制日志文件数，一般默认为2。mysql事务日志文件是循环覆写的，如下图：
（2）优缺点
当一个日志文件写满后，innodb会自动切换到另一个日志文件，而且会触发数据库的checkpoint，这回导致innodb缓存脏页的小批量刷新，会明显降低innodb的性能。如果innodb_log_file_size设置太小，就会导致innodb频繁地checkpoint，导致性能降低。而如果设置太大，由于事务日志是顺序I/O，大大提高了I/O性能，但是在崩溃恢复InnoDB时，会导致恢复时间变长。
如果InnoDB数据表有频繁的写操作，那么选择合适的innodb_log_file_size值对提升MySQL性能很重要。
（3）如何配置
作为一个经验法则，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。方法如下：
首先，在业务高峰期，计算出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
（1）介绍
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每秒钟做一次刷新。
（2）如何配置
日志缓冲必须被刷新到持久化存储（磁盘），以确保提交的事务完全被持久化了。如果和持久化相比更在乎性能，则可以修改该参数来控制日志缓冲刷新的频繁程度。
5、thread_cache_size
（1）介绍
线程缓存保存哪些当前没有与连接关联但是准备为后面新的连接服务的线程。当一个新的链接创建时，如果缓存中有线程存在，MySQL从缓存中删除一个线程，并且把它分配给这个新的连接。当连接关闭时，如果线程缓存还有空间的话，MySQL又会把线程放回缓存。如果没有空间的话，MySQL就会销毁这个线程。
只要MySQL在缓存中还有空闲的线程，它就可以迅速地响应连接请求，因为这样就不用为每个连接创建新的线程。
（2）如何配置
thread_cache_size指定了MySQL可以保存在缓存中的线程数。一般不需要配置这个值，除非服务器会有很多连接请求。
一般，可以根据机器的内存进行设置：
# 1G —> 8
# 2G —> 16
# 3G —> 32
# 大于3G —> 64或更大
6、tmp_table_size和max_heap_table_size
（1）介绍
tmp_table_size：临时表的内存缓存大小，临时表是指sql执行时生成的临时数据表。在优化sql时，应该尽量避免临时表。
max_heap_table_size：该参数也会影响到临时表的内存缓存大小。在增加tmp_table_size的同时，也需要增加max_heap_table_size的大小。
（2）如何配置
可以通过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
（1）介绍
MySQL的max_connections参数用来设置最大连接数。如果该参数设置太小，会导致出现“Too many connections”的错误。
如果服务器的并发连接请求量比较大，建议提高此值，以增加并行连接数量。但是这个是要建立在机器的性能能支撑的情况下，因为MySQL会为每一个连接提供连接缓冲区，如果并发连接数量太高，会导致消耗内存过多。
（2）如何配置
如何判断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服务器最大连接上限值设置过高。
8、总结
如果使用的是InnoDB，在参数优化的选项里面，最重要的就是如下两个：
innodb_buffer_pool_size
innodb_log_file_size
三、数据库表结构设计优化
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记录头信息的格式如下：
数据库表结构设计原则如下，具体可参考另一篇博客文章：《MYSQL性能优化学习笔记-(1)数据库设计》
尽量避免过度设计，例如设计极其复杂查询的schema设计，或者有很多列的表设计
使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL值
尽量使用相同的数据类型存储相似的值，尤其是要在关联条件中使用的列
注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存
尽量使用整形定义标识列
小心使用ENUM和SET，最好避免使用BIT
范式是好的，但是反范式有时也是必须的，并且能带来好处。预先计算、缓存或生成汇总表也可能获得大的好处
ALTER TABLE是让人痛苦的操作，因为大部分情况下，它都会锁表并且重建整张表。 解决方法有两种：一种是在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；另一种方法是“影子拷贝”，即用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表
在查找表时采用整数逐渐而避免采用基于字符串的值进行关联
四、索引设计优化
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索引和数据行，一个表只能有一个聚集索引。
如下图，聚集索引，叶子页包含了行的全部数据，但节点页只包含索引列。
4、例子说明
如下表，我们有一个主键col1以及三个索引。假设主键取值为1~10000，按随机顺序插入并使用OPTIMIZE TABLE命令做优化。name和age随机赋值，数据如下表所示。
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 |
聚集索引叶子节点分布如下图所示：
以col2为索引列的辅助索引叶子节点分布图：
以col3为索引列的辅助索引叶子节点分布图：
（1）覆盖索引
严格地说，覆盖索引只是辅助索引的一个衍生定义，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，所以它要比聚集索引小很多，因此一页包含的辅助索引数量更多，从而可以减少大量的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的理由》。
建议在选择性高的列上建立索引，最好是唯一索引，区分度越大，则我们扫描的记录数越少，例如性别区分度不大，就不适合做索引。
更新非常频繁的数据不适合建索引。频繁更新会导致变更B+索引树，重建索引，这个过程很消耗数据库性能。
利用最左前缀原则，比如建立一个联合索引(a,b,c)，我们可以利用的索引就有(a),(a,b),(a,b,c)
如果确定有多少条数据，使用limit限制一下，MySQL在查找到对应条数的数据的时候，会停止继续查找
删除不再使用的索引
join语法，尽量将小的表放在前面，在需要on的字段上，数据类型保持一致，并设置素银，否则MySQL无法使用索引来Join查询
like “xxx%”可以用到索引，like”%xxx%”则不行
在设计开发阶段，数据库字段的定义要避免出现由数据类型定义不当造成的隐式转换
详细的原理及其设计可以查看另一篇我的博客文章： 《MySQL性能优化学习笔记-(2)索引优化》
这里推荐一个MySQL大牛的技术教程，作者是阿里的技术专家，目前在腾讯任职数据库的负责人，对MySQL的原理由深入的研究了解。
五、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扫描尽可能少的页面，获取需要访问的记录后在根据关联列回原表查询需要的所有列
可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免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性能优化学习笔记-(3)查询优化》
而对于SQL语句的性能分析可以查看我专门写的Explain的博客文章：《MySQL性能优化学习笔记-(4)性能分析》
六、上线后监控优化
MySQL数据库上线后，可以等其稳定运行一段时间后再根据服务器的status装填进行适当优化，可以用如下命令列出MySQL服务器运行的各种状态值：
# 查询所有状态值
show global status;
# 查询具体某个状态值
show status like '%xx%';
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_vul_scan_result_? t_vul_scan_plugin_mng_?
# 2 0xA72A99A2AD696A84F9CCD57... 360.7725 8.4% 2 180.3862 2.08 DELETE t_vul_scan_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_vul_scan_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_scan
# Hosts localhost
# Users scan
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `vul_scan` LIKE 't_vul_scan_result_0'\G
# SHOW CREATE TABLE `vul_scan`.`t_vul_scan_result_0`\G
# SHOW TABLE STATUS FROM `vul_scan` LIKE 't_vul_scan_plugin_mng_0'\G
# SHOW CREATE TABLE `vul_scan`.`t_vul_scan_plugin_mng_0`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT count(distinct resultId) as cnt, plugin.level
FROM `t_vul_scan_result_0` as `result`
JOIN `t_vul_scan_plugin_mng_0` as `plugin` ON (`result`.`pluginId`=plugin.pluginId)
WHERE `taskInstanceId` = '100032'
GROUP BY `level`
ORDER BY `level` DESC\G
我们通过explain来分析该sql语句：
mysql> explain SELECT count(distinct resultId) as cnt, plugin.level FROM `t_vul_scan_result_0` as `result` JOIN `t_vul_scan_plugin_mng_0` as `plugin` ON (`result`.`pluginId`=plugin.pluginId) WHERE `taskInstanceId` = '100032' GROUP BY `level` ORDER BY `level` DESC\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: result
type: ref
possible_keys: taskInstanceId,pluginId
key: taskInstanceId
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_scan.result.pluginId
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)
七、分库分表
1、垂直拆分
垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表，如下图所示：
通常按照以下原则进行垂直拆分：
把不常用的字段单独放在一张表
把text,blob等大字段拆分出来放在附表中
经常组合查询的列放在一张表中
2、水平拆分
水平拆分是把一张表的数据拆分成多张表来存放，如下图：
有两种水平拆分方式：
方法1：通过取模方式进行表的拆分
例如一张800w的用户表users，为提高效率，将表分成users1,user2,user3,user4。通过ID取模的方法把数据分散到四张表内 Id % 4 + 1 = [1,2,3,4]。查询、更新、删除也是通过取模的方法来查询：
$_GET['id'] = 17,
17%4 + 1 = 2,
$tableName = 'users'.'2'
Select * from users2 where id = 17;
方法2：通过InnoDB存储引擎支持的分区功能进行
该方法，就访问数据库的应用而言，从逻辑上将只有一个表或索引，但是在物理上这个表或索引可能由多个物理分区组成，每个分区都是独立的对象，可以独自处理。
当前MySQL数据库支持以下几种类型的分区：
RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区。
LIST分区：和RANGE分区类似，只是LIST分区面向的是离散的值。
HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。
KEY分区：根据MySQL数据库自定义的哈希函数来进行分区。
这里举HASH分区为例子：
CREATE TABLE t_hash(
a INT,
b DATETIME
)ENGINE=InnoDB
PARTITION BY HASH(Year(b))
PARTITIONS 4;
如果插入一个列b为2010-04-01的记录到t_hash中，那么保存该记录的分区如下：
MOD(YEAR('2010-04-01'),4)
= MOD(2010,4)
= 2
因此记录会放入分区p2中，
mysql> INSERT INTO t_hash(a,b) VALUES(1,'2010-04-01');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT table_name,partition_name,table_rows FROM information_schema.PARTITIONS WHERE table_schema=DATABASE() AND table_name='t_hash';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| t_hash | p0 | 0 |
| t_hash | p1 | 0 |
| t_hash | p2 | 1 |
| t_hash | p3 | 0 |
+------------+----------------+------------+
4 rows in set (0.04 sec)
八、海量架构
1、MySql读写分离
2、MySQL分布式集群
3、NoSQL海量架构方案
如果到了要考虑对MySQL做读写分离或者要搭建分布式集群的地步，是不是要开始考虑采用海量的NoSQL架构方案。要知道读写分离、分布式集群这个方案会导致更复杂的问题，而且还存在主从之间数据同步时延的问题。
而目前的NewSQL数据库方案，是很好的海量架构选择。据我一个在微信工作的同事了解，目前微信是基本上都采用NewSQL的存储数据库，很少用MySQL，而且TiDB是兼容MySQL协议，迁移也很方便，基本上不需要改动代码。
最关键的是NewSQL支持水平弹性扩展、数据强一致性保证、海量数据高并发实时写入与实时查询。
