关系数据库之mysql（一）引言与性能优化思路剖析
前瞻
既然准备写的是mysql性能优化，那基础知识就不多做介绍，但为了保证知识体系的完整性，也为了我更好解释一些优化思路与方向，稍微写一点架构方面的东西：
- Mysql体系架构
- InnoDB体系架构
虽然网上有海量的体系架构介绍，但自己还是再唠叨一遍，权当加深印象。如果是涉及到原理或者相关知识等，随写随用，就不专门写了。主要是知识点太多，另一个是文章之间的耦合度高，看起来比较乱。
Mysql体系架构
Connection pool：连接池组件，建立并缓存连接，减少建立连接，以提升服务器性能。
Enterprise Management Service & Utilities：管理服务和工具组件，功能如备份恢复、mysql复制等。
SQL Interface：SQL接口组件，接受客户端SQL命令并返回结果。
Parser：查询分析器组件，SQL传递到这里会被验证和解析。
Optimizer：优化器组件，SQL语句在查询前会用查询优化器对查询进行优化。
Cache & Buffer：缓存组件，如果查询缓存有命中，查询语句就可以直接去查询缓存中取数据。
Pluggable Storage Engines：存储引擎，主要使用的是InnoDb存储引擎。
File System and File & Logs：文件存储系统。
**参数优化从max_connections开始 **
max_connections
mysql的最大连接数，以保证服务器不会因为应用程序的激增而被限制。但如果程序有问题，创建过多连接或者连接不释放会导致数据库服务异常，这种程序一般会在代码扫描的时候被扫描出来，还到不了性能测试的阶段就会被解决掉。
max_connections 默认值为100，我们公司的模板设置了1000，一般的业务系统和办公系统足够用了，目前核心系统还在用oracle，没有切换mysql，这块如果后面有实践再补充上来。
当然，连接数设置的大小也和服务器配置有关系。如果不知道连接数应该设置多少，可以先设置1000，使用一段时间之后通过max_used_connections查询，它会记录使用连接数的最高水位，然后再设置一个合理的值。
[root@localhost][(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1000 |
+-----------------+-------+
1 row in set (0.00 sec)
[root@localhost][(none)]> show status like 'max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 291 |
+----------------------+-------+
1 row in set (0.00 sec)
query_cache_type
sql查询缓存开关
MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存，会立刻返回结果，跳过了解析，优化和执行阶段。看起来这个功能对性能提升非常大，但是
- 读查询开始之前必须检查是否命中缓存。
- 如果读查询可以缓存，那么执行完之后会写入缓存。
- 当向某个表写入数据的时候，必须将这个表所有的缓存设置为失效，如果缓存空间很大，则消耗也会很大，可能使系统僵死一段时间。
所以生产环境不建议开启查询缓存。
thread_cache_size
线程缓存大小
当客户端断开之后，服务器处理此应用的线程将会缓存起来以响应给下一个应用请求。
[root@localhost][(none)]> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 18 |
+-------------------+-------+
1 row in set (0.00 sec)
查看线程情况
[root@localhost][(none)]> show status like 'Threads%';
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| Threads_cached | 15 |
| Threads_connected | 53 |
| Threads_created | 4323526 |
| Threads_running | 3 |
+-------------------+---------+
4 rows in set (0.00 sec)
Threads_connected：打开的连接数。
Threads_created：表示创建过的线程数。
Threads_running：这个数值指的是激活的连接数，这个数值一般远低于connected数值。 Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，可以适当增加配置文件中thread_cache_size值。
mysql_slow_log
系统到了压测调优阶段之后，在sql优化这一层面，首先考虑到的就是通过mysql的慢日志机制就行排查（后续文章详细介绍使用，包括mysqldumpslow）。
slow_query_log 如果为off，改为on，即为开启慢日志查询
[root@localhost][(none)]> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.01 sec)
log_queries_not_using_indexes 改为on ，查看没有索引的SQL
[root@localhost][(none)]> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.01 sec)
long_query_time 大于多少s的sql可记录到慢查询日志中（set global long_query_time =0.2; 修改之后立即查看还是原来的值，并不是没有修改成功，修改全局变量仅对新会话生效而对当前会话不起作用，新建一个会话再次查看即可）
[root@localhost][(none)]> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
InnoDB引擎体系结构
innodb从最影响性能的一些参数说起：
innodb_buffer_pool_size
InnoDB引擎最重要的一个参数！！！
InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式来管理。缓冲池简单讲就是一块内存区域，通过内存来弥补磁盘IO对数据库的性能影响。
为什么说innodb_buffer_pool_size非常重要？
数据库读取页的操作：
1、将从磁盘读到的页放入缓冲池的数据也中
2、再次读相同的页会判断缓冲池中是否存在
3、若命中，直接读取；若没有再去读磁盘
数据库修改页操作：
1、首先修改缓冲池中的页
2、通过checkpoint机制刷新到磁盘（非实时刷新以提高数据库整体性能）
所以说innodb_buffer_pool_size的大小直接影响了数据库的性能。
设置多少合适？
分配的内存区域占用整个内存的70%~80%，官方推荐的值，也是我们公司请的mysql厂商大佬给我们培训时推荐的值。太大的话压力较大时可能会触发swap。
如何查？
[root@localhost][(none)]> show variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+
1 row in set (0.00 sec)
如何改？
[root@localhost][(none)]> set global innodb_buffer_pool_size = 4292967296;
Query OK, 0 rows affected, 1 warning (0.00 sec)
** mysql5.7版本之后支持动态修改，但是修改时所有的sql是夯住的，所以需要在业务低峰期修改。**
查看修改状态：
[root@localhost][(none)]> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 201029 16:06:08\. |
+----------------------------------+----------------------------------------------------+
1 row in set (0.00 sec)
补充1：
如果mysql要重启或者服务器要重启，InnoDB_buffer_pool的数据会丢失，需要很长时间来预热。
解决：将buffer pool的内容拷⻉到磁盘中，启动时将磁盘中的内容拷⻉回buffer pool
操作：
将buffer pool的状态在关闭数据库时保存：innodb_buffer_pool_dump_at_shutdown
将buffer pool的状态在开启数据库时载⼊：innodb_buffer_pool_load_at_startup
将buffer pool的状态在数据库运⾏时保存：
SET GLOBAL innodb_buffer_pool_dump_now=ON;
将buffer pool的状态在数据库运⾏时载⼊：innodb_buffer_pool_load_now=ON；
停⽌buffer pool的load： SET GLOBAL innodb_buffer_pool_load_abort=ON;
查看buffer pool的dump和load情况： mysql> show global status like 'innodb_%status';
补充二：
与innodb_buffer_pool关系较大的还有一个参数：
innodb_buffer_pool_instances 缓冲池实例数
好处：减小数据库内部的资源竞争，增加数据库的并发处理能力的吞吐量。
条件：
InnoDB的规定，Buffer Pool 的内存小于1G的时候，InnoDB会默认把innodb_buffer_pool_instances 的值修改为1且设置其他值无效。
在MySQL 5.7 中，当innodb buffer size大于1G的时候，就会默认会分成8个instances，如果小于1G，就只有1个 instance。如果服务器是32C或者更高，可以将此值设置为16。
innodb_log_file_size
最影响重做时性能的参数，重做日志文件大小
当实例不可用时，例如主机断电等情况，innodb会使用重做日志恢复到断电前的时刻来保证数据的完整性。默认会写两个文件，一个写满时切换另一个，另一个满了再切回来。
设置过大：恢复时间会很长
设置过小：可能导致一个事务的日志多次切换重做日志。也有可能发生async checkpoint，造成性能抖动。
设置多少合适？
一般设置为64~512MB，我们公司的模板设置为128M，这个值暂时不允许修改，已经满足大部分系统的使用需求，也可以通过命令：
** show engine innodb status\G；**
在业务繁忙期查出来的Log sequence number计算出一个合理的值（间隔为60s）。
Log sequence number（第二个值）- Log sequence number（第一个值）601024*1024得到InnoDB每小时写入的数据量，即为设置innodb_log_file_size的值。
**如何查？ **
[root@localhost][(none)]> show variables like 'innodb_log_file_size';
+----------------------+-----------+
| Variable_name | Value |
+----------------------+-----------+
| innodb_log_file_size | 134217728 |
+----------------------+-----------+
1 row in set (0.00 sec)
**如何改？ **
1.在my.cnf更改innodb_log_file_size
2.停止mysql服务器
3.删除旧的日志,通过执行命令rm -f /var/lib/mysql/ib_logfile*
4.启动mysql服务器
注意：InnoDB1.2.x（mysql5.6.2）版本之前只能设置<=4GB。之后最大可以是512G。
影响IO性能的参数
**max_binlog_size sync_binlog innodb_flush_log_at_trx_commit **
binlog介绍，一个小伙伴写的文章，请看深入了解binlog
max_binlog_size记录binlog日志文件的最大值，默认1G。如果binlog size 设置小了，多余的会记录至磁盘的一个临时文件，然后commit之后会将临时文件中的sql放置binlog，IO阻塞或者并发上不去。
sync_binlog value = 0 时,binlog每秒写一次盘；value = n 时,n个commit写一次盘。一般情况下都设置为1。
innodb_flush_log_at_trx_commit是将事务日志从innodb log buffer写入到redo log中，sync_binlog是将二进制日志文件刷新到磁盘上。
**innodb_flush_log_at_trx_commit + sync_binlog 设置双一模式，不会丢数据，但是性能较低，需根据实际业务场景设置。 **
一点建议
从以上两个层面先简单整理下一些重要的参数，后面想到其他的再补充上去。
分享一个踩过的坑，之前有一个项目，mysql服务器配置是8C16G，压测了好几天，sql该优化的也优化了，结果还是不达标，已经准备申请做服务器扩容再压测了，最后一遍检查时发现innodb_buffer_pool默认设置了1G，顿时心中一群羊驼奔腾而过（据说是同步了生产库，鬼知道这个参数为啥没有同步过来），调整成12G之后QPS立马就上去了。所以在性能测试开展之前完全可以先检查一遍作为预检查项，判断参数设置是是否合理，以免做无用功。
