数据库性能调优-07|数据库参数设置优化，失之毫厘差之千里

七、数据库参数设置优化，失之毫厘差之千里

一.MySQL 体系结构

我们一般可以将 MySQL 的结构分为四层，最上层为客户端连接器，主要包括了数据库连接、授权认证、安全管理等，该层引用了线程池，为接入的连接请求提高线程处理效率。
第二层是 Server 层，主要实现 SQL 的一些基础功能，包括 SQL 解析、优化、执行以及缓存等，其中与我们这一讲主要相关的就是缓存。
第三层包括了各种存储引擎，主要负责数据的存取，这一层涉及到的 Buffer 缓存，也和这一讲密切相关。
最下面一层是数据存储层，主要负责将数据存储在文件系统中，并完成与存储引擎的交互。

1、查询语句执行流程

一个应用服务需要通过第一层的连接和授权认证，再将 SQL 请求发送至 SQL 接口。SQL 接口接收到请求之后，会先检查查询 SQL 是否命中 Cache 缓存中的数据，如果命中，则直接返回缓存中的结果；否则，需要进入解析器。
解析器主要对 SQL 进行语法以及词法分析，之后，便会进入到优化器中，优化器会生成多种执行计划方案，并选择最优方案执行。
确定了最优执行计划方案之后，执行器会检查连接用户是否有该表的执行权限，有则查看 Buffer 中是否存在该缓存，存在则获取锁，查询表数据；否则重新打开表文件，通过接口调用相应的存储引擎处理，这时存储引擎就会进入到存储文件系统中获取相应的数据，并返回结果集。

2、更新语句执行流程

数据库更新 SQL 的执行流程其实跟查询 SQL 差不多，只不过执行更新操作的时候多了记录日志的步骤。在执行更新操作时 MySQL 会将操作的日志记录到 binlog（归档日志）中，这个步骤所有的存储引擎都有。而 InnoDB 除了要记录 binlog 之外，还需要多记录一个 redo log（重做日志）。

3、redo log（重做日志，存储引擎层日志）

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”，redo log 是循环写的，空间固定会用完；

4、binlog（归档日志，Server层日志）

binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”，binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

二.内存调优（查多，存和改少再使用）

Query Cache 虽然可以优化查询操作，但也仅限于不常修改的数据，如果一张表数据经常进行新增、更新和删除操作，则会造成 Query Cache 的失效率非常高，从而导致频繁地清除 Cache 中的数据，给系统增加额外的性能开销。

1、查看是否开启数据库缓存

SELECT @@query_cache_type;

2、查看缓存数据

show status like 'Qcache%'

3、

二.InnoDB 存储引擎参数设置调优

1、innodb_buffer_pool_size（InnoDB缓冲池大小）

InnoDB Buffer Pool（简称 IBP）是 InnoDB 存储引擎的一个缓冲池，与 MyISAM 存储引擎使用 key buffer 缓存不同，它不仅存储了表索引块，还存储了表数据。查询数据时，IBP 允许快速返回频繁访问的数据，而无需访问磁盘文件。
IBP 默认的内存大小是 128M，我们可以通过参数 innodb_buffer_pool_size 来设置 IBP 的大小，IBP 设置得越大，InnoDB 表性能就越好。但是，将 IBP 大小设置得过大也不好，可能会导致系统发生 SWAP 页交换。所以我们需要在 IBP 大小和其它系统服务所需内存大小之间取得平衡。MySQL 推荐配置 IBP 的大小为服务器物理内存的 80%。

2、innodb_buffer_pool_instances（缓冲池实例个数）

InnoDB 中的 IBP 缓冲池被划分为了多个实例，对于具有数千兆字节的缓冲池的系统来说，将缓冲池划分为单独的实例可以减少不同线程读取和写入缓存页面时的争用，从而提高系统的并发性。该参数项仅在将 innodb_buffer_pool_size 设置为 1GB 或更大时才会生效。

3、innodb_read_io_threads / innodb_write_io_threads（idb读写线程）

1.查询读写比

读取数量

SHOW GLOBAL STATUS LIKE 'Com_select';//读取数量

查询写入比

SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_insert', 'Com_update', 'Com_replace', 'Com_delete');//写入数量

2.读写线程（根据服务器核数增大读写线程数，根据读写比例分配读写线程数）

在默认情况下，MySQL 后台线程包括了主线程、IO 线程、锁线程以及监控线程等，其中读写线程属于 IO 线程，主要负责数据库的读取和写入操作，这些线程分别读取和写入 innodb_buffer_pool_instances 创建的各个内存页面。MySQL 支持配置多个读写线程，即通过 innodb_read_io_threads 和 innodb_write_io_threads 设置读写线程数量。

SELECT @@innodb_read_io_threads ;#查询读线程数量
SELECT @@innodb_write_io_threads ;# 查询写入线程数量

4、innodb_log_file_size/innodb_log_files_in_group（redolog分区空间大小/分区个数）

redolog是一个环形的数据结构，它的大小是固定的，总共可以写4GB，Redo log的空间通过innodb_log_file_size innodb_log_files_in_group（默认为2）参数来调节。将这俩参数相乘即可得到总的可用Redo log 空间。尽管技术上并不关心你是通过innodb_log_file_size还是innodb_log_files_in_group来调整Redo log空间，不过多数情况下还是通过innodb_log_file_size 来调节。

5、innodb_log_buffer_size（redolog缓冲的大小）

这个参数决定了 InnoDB 重做日志缓冲池的大小，默认值为 8MB。如果高并发中存在大量的事务，该值设置得太小，就会增加写入磁盘的 I/O 操作。我们可以通过增大该参数来减少写入磁盘操作，从而提高并发时的事务性能。

6、innodb_flush_log_at_trx_commit（redolog写入策略）

我觉得这个没必要，因为
这个参数可以控制重做日志从缓存写入文件刷新到磁盘中的策略，默认值为 1。
当设置该参数为 0 时，InnoDB 每秒种就会触发一次缓存日志写入到文件中并刷新到磁盘的操作，这有可能在数据库崩溃后，丢失 1s 的数据。
当设置该参数为 1 时，则表示每次事务的 redo log 都会直接持久化到磁盘中，这样可以保证 MySQL 异常重启之后数据不会丢失。
当设置该参数为 2 时，每次事务的 redo log 都会直接写入到文件中，再将文件刷新到磁盘。
在一些对数据安全性要求比较高的场景中，显然该值需要设置为 1；而在一些可以容忍数据库崩溃时丢失 1s 数据的场景中，我们可以将该值设置为 0 或 2，这样可以明显地减少日志同步到磁盘的 I/O 操作。