date
Sep 14, 2021
password
icon
status
Published
category
技术分享
tags
MySql
note
summary
待整理学习笔记
URL
slug
服务器和 mysql 交互连接池
服务器和 mysql 进行交互是使用连接池。
常用的有阿里的 Druid，HikariCP 等连接池。
连接池的作用通过连接池建立连接，可以鉴权。
建立连接和销毁比较消耗性能，连接池可以用线程池维护和 mysql 的链接，可以多个线程同时访问 mysql，加强链接效率，减少销毁的次数。
SQL 执行流程
mysql 会有线程不停监听连接池，如果有连接就调用 sql 接口去执行 SQL，mysql 通过操作SQL控制存储查询，权限，提供一个 SQL 接口来接受所有操作。
SQL 接口会调用解析器解析 SQL，判断 SQL 是查询还是更新或者删除，查询哪些表等等~
接着会调用查询优化器判断怎么执行 SQL 效率最高，会走哪个索引去查询，针对条件作出 SQL 优化，比如让不符合联合索引条件的查询语句调整为可以使用联合索引~
接着调用执行器来执行 SQL，执行器来调用存储引擎去执行 sql，可能是一次，也可能是多次，由优化器决定是否查询多次，或者通过第一次查询得到结果在进行第二次查询，最终的查询数据~
mysql 是个数据库，会把数据存储起来，可能是内存里或者是磁盘中，存储引擎的作用就是从存储里读取或写入数据~
缓冲池，redolog
用户想更改一条数据发送一条请求会首先进入 Tomcat 里，Tomcat 会分配一个线程去执行这次请求，然后从连接池里获取一个数据库连接，这个链接接着会进入数据库连接池，调用 SQL 接口、解析器、查询优化器、执行器、存储引擎，最终的到想要的结果~
MySQL默认的存储引擎默认是 innodb，支持事务、行锁~
innodb 为了查询效率，设置了一个缓冲池 （buffer pool），每次查询先从bufferpool 读取，没读到在从磁盘读取，读取到后会加载到 bufferpool 里，方便后续操作这条数据~
为了数据不被并发修改，所以修改语句会对修改的行添加行锁，避免并发修改~
在修改数据的时候，会先把数据从磁盘加载到缓冲池里，比如一条ID 为 1 的数据原值为 张三，执行如下 SQL
update table set name = 'a' where id = 1; ，MySQL 是支持事务的，为了支持回滚，所以 MySQL 建立了一个日志文件 undolog 撤销日志，在修改前会先把原数据存储起来，如果出了问题需要回滚就可以从 unodlog 里读取原值在给修改回去~MySQL 会先把磁盘加载缓冲池里，修改缓冲池数据，因为修改内存的速度要比修改磁盘快很多，提高了并发性能，这时会引发一个新问题，执行完 SQL 只修改了缓冲池中的数据，磁盘中的数据还是没有修改的，所以现在磁盘中是脏数据，如果这时磁盘发生了宕机，就会导致这次修改的数据丢失~
为了避免这种情况发生，需要把数据从内存写入到磁盘里，如果直接写入，效率会特别低，所以MySQL 还加入了一个日志叫做
redolog 重做日志 ，作用就是修改数据后会把修改的 SQL 记录下来，后续刷入磁盘中，并且如果数据库中间崩溃了，还可以从 redolog 里读取数据重新加载到系统里~那把 redolog 刷入磁盘的时机就需要考虑了，MySQL 提供了三种情况刷入磁盘，
innodb_flush_log_at_trx_commit：分别为 0、1、2。默认为 1 ~
select @@innodb_flush_log_at_trx_commit;在提交事务的时候，
innodb_flush_log_at_trx_commit为 0 时MySQL 每秒刷新一次 redolog buffer 到磁盘，在提交后系统宕机会导致数据丢失~ 缓存在MySQL内存中
为 1 时，会把 redolog buffer 的值刷入到磁盘里，即使系统宕机也可以从 redolog 中恢复数据~
为 2 是，会把 redolog buffer 中的值刷入 redolog file 对应的 OS cache 里，1 秒后再把系统缓存里数据写入 redolog file 里，如果系统宕机，就会丢失事务数据~ 缓存在系统层面
丢失事务数据表现为，系统提交成功了，但系统发生宕机后数据会丢失~
如果不在乎事务数据丢失，可以设置为 0 或 2，可以获得更高的性能
binlog
写完 redolog 后，可以认为这条数据已经处理完了，可以提交事务了，这是 MySQL server 会把这条数据改动写入 binlog 里，binlog 是归档日志，是 MySQL server 的日志，记录的是类似偏逻辑的数据，对哪一页的哪一行数据做了修改，redolog 是 innodb 引擎特有的日志~
binlog 也有两种策略来写入磁盘，有一个参数
sync_binlog ，默认为 0 ，可设置范围是 0 → n为 0 代表写 binlog 时不会直接写入文件里，而是写入文件对应的 OS cache 里，系统会自己判断什么时候再刷入文件里，这是如果系统宕机，那么 binlog 数据是会丢失的~
为 1 代表在提交事务时会直接把 binlog 直接写入文件里，这样即使系统宕机了，数据也不会丢失~
大于 1 时代表事务提交 n 次会把 cache 里的数据写入文件里~
在写入 binlog 文件时，会吧 binlog 文件位置和commit 标记写入 redolog，代表此次事务完成，保证 redolog 和 binlog 之间的同步，只有 commit 标记写入 redolog 里，才代表此次事务成功记录下来，不会因为宕机丢失数据，如果binlog 写入磁盘后宕机，还未把 commit 标记写入 redolog 里，这条事务是不生效的，当 MySQL 服务重启后，会读取 redolog ，发现这个事务没有 commit 标记，从 undolog 读取旧值回滚这次事务~
这个时候修改的数据还在缓冲池里，MySQL 会有一个线程随机刷盘，把缓冲池里的数据写入到磁盘里~
如果缓冲池中的数据还没有同步到磁盘，MySQL 宕机了，也没有关系，因为 redolog 和 binlog 是有这个事务完整的数据的，会把这次数据再次加载缓冲池里，后续再有线程把缓冲池的数据写入到磁盘~
更新语句执行流程
总结下更新语句的流程
- 执行器调用 innodb 引擎
- 先去缓冲池里查询，如果不在缓冲池里，从磁盘加载到缓冲池里
- 将旧数据写入 undolog 里
- 修改内存里的数据
- 将操作写入 redolog 里
- 将修改的 SQL 记入 binlog 里
- 提交事务后将 binlog 的位置和 commit 标记写入redolog 里
- MySQL 线程会随机将缓冲池里的数据写入到磁盘更新脏数据（刷脏页）
性能
大部分 Java 系统来说，服务器基本使用的是 2核4g 或 4 核 8g 的，数据库一般是 16 核 32g 或 8 核 16g。4核 8g 的服务器基本来说能抗每秒几百的并发量，从每秒一两百到七八百这样，主要看业务逻辑复杂性，每个请求处理需要多长时间。
基本生产数据库都是 8 核 16g 起步，基本能支持一到两千并发。
如果是 16 核 32g 吗，基本可以支持两千到四千并发。
如果超过数据库服务器的极限，系统压力极大，io，磁盘，网络都会飙到很高，如果处理不过来，数据库就会宕机。
数据库最好是SSD 固态硬盘，因为数据库的会有大量随机的磁盘 IO，如果使用 SSD ，性能会好很多~
性能指标
在申请数据库之前，架构师或者开发应该都对申请多大的数据库心里有数，8 核 16g 的基本支持 1 千到 2 两千的并发，16核 32g 的支持两千到四千的并发~
在申请完数据库之后，稍微有点规模的公司都会有专业的 DBA 去安装数据库，DBA 会安装指定版本的数据库，根据以往的经验和服务器配置去进行调优和基本配置，甚至包括服务器参数。最后 DBA 会把数据库相关信息发给你，可以进行连接使用~
在获取到数据库后，应该对数据库进行压测，实际看数据库到底能支持多大并发~
重点有两个指标，QPS 和 TPS~
QPS ： queries per second，每秒而已处理的请求数量
TPS ： transactions per second， 每秒能处理的事务数量，一次事务可能有一个或多个 SQL，对这次事务提交或进行回滚算完成一次事务，称为一次 TPS
还有其他几个指标也要关注一下
IOPS ： 每秒支持的 IO 随机读写数量，这个指标也很重要，因为 MySQL 后台会有个线程随机把缓冲池里的数据刷入磁盘里，是随机读写，如果这个指标太低会影响刷入磁盘的效率
吞吐量： MySQL 在执行是会有很多 log 文件需要写，一台机器每秒可以写入多少字节的数据量，就能决定它每秒能写多少条 log ，log文件一般都是顺序读写，一般磁盘的读写量在 200M/s ，所以通常来说，磁盘吞吐量足够承受高并发请求的~
latency： 往磁盘写入数据延迟的时间，比如说写一条日志到 redolog，是延迟 10ms 还是 100us，这个数值越小，那数据库性能越高，每个事务处理的速度就越快~
还有几个负载，CPU 负载 ，网络负载，内存负载
CPU 负载：CPU 每秒处理的请求是有限制的，如果 CPU 负载过高，也是不能继续压测了，否则 CPU 是吃不消的
网络负载：在机器的带宽下，压测时，网卡每秒会输入多少 MB 的数据，输出多少 MB 的数据,因为假如带宽是 100MB 的情况下，带宽满了，每秒请求已经到了 100MB，那么即使其他指标都还正常，性能也不会继续提高了，因为带宽限制了每秒最大数据传输量~
内存负载：在压测时，内存占用过高，也会限制整体性能
为什么要在拿到数据库后去做压测，而不是把 Java 服务部上去后直接对 Java 服务进行压测，因为可能数据库的 QPS 是 2000，但是 Java 服务的 QPS 只有 500，那么性能瓶颈就是在服务上，要提前压测，做到心里有数，才能在出现问题时，快速定位问题~
QPS： 每秒的请求数量，每次请求到响应称为一次 QPS。
TPS：每秒处理的事务数量，就针对单接口来说，TPS 等同于 QPS。
针对一个页面响应来说，调取了一个数据接口和一个 js 还两个 css 样式，那么 qps 是 3，tps 是 1 。
RT: respones-time，一次请求的响应时间
QPS = 每秒并发数/RT，一般C 端项目响应式间都要求在 200 ms 以内，加入每秒并发数是 1000，那么 QPS = 1000/200 = 50。
并发数=QPS* RT
性能压测
测试 mysql 性能的时候还要关注服务器的网络，磁盘 IO ，CPU，内存压力等，如果在某方面有瓶颈，也会导致 MySQL 的整体性能不佳，要在服务器能撑住的前提，测试出 MySQL 的最大的 QPS，做到心里有数。
#创建用户 CREATE USER 'test_user'@'%' IDENTIFIED BY 'test_user'; #授权 GRANT ALL ON test_db.* TO 'test_user'@'%'; ############################### 8.0 后可选，简单密码才需要 ALTER USER 'test_user'@'%' IDENTIFIED BY 'test_user' PASSWORD EXPIRE NEVER; ALTER USER 'test_user'@'%' IDENTIFIED WITH mysql_native_password BY 'test_user';
# 安装 curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash sudo yum -y install sysbench sysbench --version # 数据准备 sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare # 测试数据库的综合读写TPS sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable run # 测试数据库的只读性能 sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_only --db-ps-mode=disable run # 测试数据库的删除性能 sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_delete --db-ps-mode=disable run # 测试数据库的更新索引字段性能 sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_index --db-ps-mode=disable run # 测试数据库的更新非索引字段性能 sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_non_index --db-ps-mode=disable run # 测试数据库的插入性能 sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_insert --db-ps-mode=disable run # 测试数据库的写入性能 sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_write_only --db-ps-mode=disable run # 清理测试数据 sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup
[ 22s ] thds: 10 tps: 380.99 qps: 7312.66 (r/w/o: 5132.99/1155.86/1321.35) lat (ms, 95%): 21.33 err/s: 0.00 reconn/s: 0.00
我来给大家解释一下这是什么意思，首先他说的这是第22s输出的一段压测统计报告，然后是其他的一些统计字段：
thds: 10，这个意思就是有10个线程在压测 tps: 380.99，这个意思就是每秒执行了380.99个事务
qps: 7610.20，这个意思就是每秒可以执行7610.20个请求 (r/w/o: 5132.99/1155.86/1321.35)，这个意思就是说，在每秒7610.20个请求中，有5132.99个请求是读请求，1155.86个请 求是写请求，1321.35个请求是其他的请求，就是对QPS进行了拆解
lat (ms, 95%): 21.33，这个意思就是说，95%的请求的延迟都在21.33毫秒以下
err/s: 0.00 reconn/s: 0.00，这两个的意思就是说，每秒有0个请求是失败的，发生了0次网络重连
这个压测结果会根据每个人的机器的性能不同有很大的差距，你要是机器性能特别高，那你可以开很多的并发线程去压测，比 如100个线程，此时可能会发现数据库每秒的TPS有上千个，如果你的机器性能很低，可能压测出来你的TPS才二三十个，QPS 才几百个，这都是有可能的。
另外在完成压测之后，最后会显示一个总的压测报告，我把解释写在下面了：
SQL statistics:
queries performed:
read: 1480084 // 这就是说在300s的压测期间执行了148万多次的读请求 write: 298457 // 这是说在压测期间执行了29万多次的写请求 other: 325436 // 这是说在压测期间执行了30万多次的其他请求 total: 2103977 // 这是说一共执行了210万多次的请求 // 这是说一共执行了10万多个事务，每秒执行350多个事务
transactions: 105180( 350.6 per sec. ) // 这是说一共执行了210万多次的请求，每秒执行7000+请求
queries: 2103977 ( 7013.26 per sec. )
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
// 下面就是说，一共执行了300s的压测，执行了10万+的事务
General staticstics:
total time: 300.0052s
total number of events: 105180
Latency (ms):
min: 4.32 // 请求中延迟最小的是4.32ms
avg: 13.42 // 所有请求平均延迟是13.42ms
max: 45.56 // 延迟最大的请求是45.56ms 95th
percentile: 21.33 // 95%的请求延迟都在21.33ms以内
# 10个线程 SQL statistics: queries performed: read: 621880 write: 177680 other: 88840 total: 888400 transactions: 44420 (148.03 per sec.) queries: 888400 (2960.54 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 300.0789s total number of events: 44420 Latency (ms): min: 8.26 avg: 67.55 max: 354.93 95th percentile: 125.52 sum: 3000383.83 Threads fairness: events (avg/stddev): 4442.0000/24.27 execution time (avg/stddev): 300.0384/0.02 -------------------------------------------------------------------------- # 20个线程 SQL statistics: queries performed: read: 1072386 write: 306396 other: 153198 total: 1531980 transactions: 76599 (255.24 per sec.) queries: 1531980 (5104.72 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 300.1091s total number of events: 76599 Latency (ms): min: 8.99 avg: 78.34 max: 389.88 95th percentile: 150.29 sum: 6000917.74 Threads fairness: events (avg/stddev): 3829.9500/21.63 execution time (avg/stddev): 300.0459/0.03 -------------------------------------------------------------------------- # 30个线程 SQL statistics: queries performed: read: 1630104 write: 465744 other: 232872 total: 2328720 transactions: 116436 (388.04 per sec.) queries: 2328720 (7760.82 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 300.0598s total number of events: 116436 Latency (ms): min: 8.82 avg: 77.30 max: 386.92 95th percentile: 147.61 sum: 9000154.42 Threads fairness: events (avg/stddev): 3881.2000/25.64 execution time (avg/stddev): 300.0051/0.02 ----------------------------------------------------------------------- # 40个线程 SQL statistics: queries performed: read: 1718164 write: 490904 other: 245452 total: 2454520 transactions: 122726 (408.94 per sec.) queries: 2454520 (8178.74 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 300.1083s total number of events: 122726 Latency (ms): min: 8.63 avg: 97.79 max: 429.18 95th percentile: 183.21 sum: 12001629.35 Threads fairness: events (avg/stddev): 3068.1500/18.82 execution time (avg/stddev): 300.0407/0.03 -------------------------------------------------------------------------------- # 50 thread SQL statistics: queries performed: read: 1951544 write: 557584 other: 278792 total: 2787920 transactions: 139396 (464.42 per sec.) queries: 2787920 (9288.44 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 300.1480s total number of events: 139396 Latency (ms): min: 8.69 avg: 107.63 max: 489.41 95th percentile: 196.89 sum: 15003493.32 Threads fairness: events (avg/stddev): 2787.9200/21.59 execution time (avg/stddev): 300.0699/0.04
# dstat命令是一个用来替换vmstat、iostat、netstat、nfsstat和ifstat这些命令的工具， # 是一个全能系统信息统计工具 # 直接使用dstat，默认使用的是-cdngy参数，分别显示cpu、disk、net、page、system信息 yum install -y dstat
[root@VM-8-17-centos ~]# dstat You did not select any stats, using -cdngy by default. ----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system-- usr sys idl wai hiq siq| read writ| recv send| in out | int csw 0 0 99 0 0 0|7895B 49k| 0 0 | 0 0 | 409 3064 1 1 99 0 0 0| 0 0 | 804B 2086B| 0 0 |1705 3287 0 0 100 0 0 0| 0 0 | 660B 2979B| 0 0 |1701 3276 1 1 98 0 0 0| 0 84k| 726B 1372B| 0 0 |1804 3391 0 0 100 0 0 0| 0 0 | 162B 1026B| 0 0 |1691 3287 0 1 99 0 0 0| 0 0 | 198B 466B| 0 0 |1615 3147 1 0 99 0 0 0| 0 56k| 66B 370B| 0 0 |1621 3167 0 1 99 0 0 0| 0 0 | 660B 1171B| 0 0 |1664 3226
buffer pool
正常有一个数据库，都会进行各种增删改查业务，对于增删改来说，是需要一直对磁盘进行读写，磁盘的随机读写性能是极慢的，随便一个大文件的读写要好几百毫秒，如果直接操作磁盘，那么 MySQL 的性能会极差，可能只能支撑几百的并发了~
所以 MySQL 引入了 buffer pool，其实是一个内存组件，将对磁盘的所有操作放在内存里处理，极大的提升了性能~
但是只在内存里操作，就会引起内存和磁盘的数据不一致的情况，所有 MySQL 提供的 redolog 将所有增删改的日志记入了 redolog 里，防止 MySQL 宕机，或者服务器宕机导致数据丢失~
buffer pool 其实就是一块内存用来加载磁盘里的数据，默认是 128M，是有点小的，针对生产的服务器最少也是 8 核 16g 乃至 16 核 32g ，可以调整为 1g 或者 2g，数据在磁盘里存储是按页存储的，每页大小默认是 16k，buffer pool 加载磁盘数据也是按照页去加载的，内存里是一页一页的数据，每个缓存页会有一个关联的描述信息，也可以叫做元信息，比如数据页归属的表空间，数据页的编号，在buffer pool 里的位置等等，描述数据大概会占缓存页 5% 的大小左右，所以实际 buffer pool 所占的大小是大于 128M 的，大概会有 130M 多点~
free 链表
各种增删改操作都会需要操作缓存页，所以必须知道哪个缓存页是空的，默认值时缓存页和磁盘页大小是一致的都是 16K，MySQL 启动的时候会读取配置文件加载获取 buffer pool 大小，然后申请一块内存空间供 buffer pool 使用，buffer pool 空间和缓存页大小都是已知的, 可以计算出一共有几个缓存页，默认值可以有 8 个缓存页 128/16=8，MySQL 会加载一个双向链表，会把每个空的缓存页都加在到 free 链表里，每个链表里面的数据其实就是缓存页描述数据，这个链表还有个特殊的节点分别指向头节点和尾节点，这个节点大小是 40K，里面还存储了有多少个空闲的缓存页，也就是链表的长度，每当有数据从磁盘加载到缓存页时，会从链表获取一个缓存页的位置，把数据加载到对应的缓存页上，然后把相关的描述信息写到该链表节点里，然后把这个节点从 free 链表里去除~
MySQL 内部还维护了一个 hash 表，每当有数据页被加载到 buffer pool 里时，都会以表空间和数据页号为 key，对应缓存页的地址做 value存入 hash 表，每次使用数据也得时候，先从 hash 表里获取缓存页地址就可以了，如果没有再去读磁盘页加载到缓存页~
flush、lru 链表
free 链表存储了空间的缓存页，如果一直不停的操作，free 链表总会满的，就是所有缓存页都加载了磁盘的数据，这时候如果新来一个SQL 需要执行，就需要把原来 flsuh 链表里的数据写入磁盘里，释放这个缓存页，在把新的数据页加载到缓存页里，这时候引发新问题就是把哪个已有的缓存页刷入到磁盘里呢，引出一个新概念，缓存命中率，比如一个磁盘页，加载到缓存页后，假如说后续 100 次查询里有 30 多次都用到了这个缓存页，说明这个缓存页利用率比较高，因为很多操作直接在内存里直接完成了，还有一个磁盘页被加载后，后续100 次查询只用到了这个缓存页 1 次，那说明这个命中率是比较低的，优先淘汰这种数据，长期不用的数据加载到内存里很浪费的，为了这种问题，MySQL 引入了一个新的 链表，lru 链表，每个加载到缓存页的数据的描述数据都会被引用到 lru 链表里，每当有 lru 链表里的数据页被查询，会把对应链表移到头部，这样链表尾部的都是比较少被使用的缓存页，当需要淘汰缓存页时，直接从链表尾部把数据写入磁盘，释放这个缓存页空间。
预读
MySQL 有个机制叫做预读机制，就是加载一个磁盘页到缓存页时，会把相邻的磁盘页也加载进内存里，因为MySQL可能认为你访问了这个区里的多个页面，可能也要读取其他页面，就提前加载相邻的页面进来，提高以后的访问速度~
MySQL 有两个参数控制预读
1.
innodb_read_ahead_threshold 默认值是56，就是如果顺序的读取了一个区的多个磁盘页，达到了阈值，就会触发预读机制，把相邻区的所有磁盘页都加载到缓存页里
2.innodb_random_read_ahead 这个参数是如果 buffer pool 里缓存了一个区里的 13 个连续的数据页，并且是被频繁访问的，就会把这个区里的其他数据也都加载到缓存里，这个参数默认是关闭的所以默认情况下主要是第一个条件会触发预读，这是可能会有点问题，比如一次请求触发了第一个条件，将其他区的数据页加载了，但是这些数据其实是没人用的，但是这些数据页是被加载到 lru 链表的头部的，原来多次被访问的数据就挪到了链表尾部，如果这是要淘汰链表，就会把多次访问的数据页给淘汰了，这样其实不太合理，应该淘汰的是被预读的那些页面，还有一种情况是全表扫描， 比如 select * from table，会把全表数据全部加载到缓存页里，然后需要淘汰一些缓存页时，就会把本来访问很多次的数据页淘汰，也不太好~
lru 链表冷热分离，淘汰
MySQL 肯定不会犯这么低级的错误，让加载到 buffer pool 里的数据都没用，为了解决这个问题，lru 链表逻辑上其实分成了两部分，一部分是热数据，一部分是冷数据，由这个参数控制，默认是 37, 意思是冷数据的比列占 37%，然后所有的数据页第一次加载都是进入冷数据区头部，在一秒后如果这个数据页再次被访问了，就会把这个数据页从冷数据区挪到热数据区头部，这样上面的问题就都解决了，因为冷热分离后，预读进来的数据页都会加载到冷数据区，不影响那些经常被访问的数据页，需要淘汰缓存页时，也只需要冷数据区取链表后部的数据页进行淘汰即可~
为了进一步优化 lru 链表的效率，在热数据区，如果时前 1/4 的数据被访问，就不需要移动到头部了，因为本身已经很靠前了，如果老是移动链表节点也是要花费时间的，如果时访问后四分之三的数据页才会把数据页移动到热数据区头部~
MySQL 在运行的时候，大量的增删改查，就是操作 buffer pool读取数据，从三个链表来回进行操作。
MySQL 后台会有个线程定时的去从 lru 链表冷数据区尾部获取缓存页将数据写入磁盘并清空缓存页，然后把这个缓存页加载到 free 链表里，在MySQL 空闲的时候也会把 flush 链表里的尾部的数据写入磁盘，清空缓存页，加入到free链表里。
buffer pool数据结构
上面几段已经记录了 buffer pool 的结构了，MySQL 为了更高的效率，将增删改查都放在了 buffer pool 里了，将数据从缓冲池里加载到磁盘的操作都交给后台线程去处理，更加放大了性能，理想情况下，每次增删改查都只需要一次或 0 次 IO 读写，但如果 MySQL 并发特别高时，free 链表没有空闲的，就只能每次操作都要淘汰 lru 链表尾部的数据写入磁盘，在进行操作，就会拖慢 MySQL 的性能。如何能优化 MySQL 的性能呢，增删改查的速度越快，free 链表的消耗就会变快，这和系统的并发有关，并发越高，消耗越快，不能限制这个，将 后台 IO 释放缓存页的时间调短也是一个思路，但是频繁 IO 也会拖慢 MySQL 的性能，所以换个思路，将缓冲池大小调大不就可以了，空间更大，可加载的缓存页就更多，留给 IO 线程去写磁盘的时间也就越充裕，然后去设置对应的缓冲池大小，IO 释放缓存页的时间，进行调优。
在高并发时，每个请求都要等上一个请求完成后才能执行，这中间还涉及到并发修改，加锁，释放缓存页，重新加载磁盘页等操作，还是会降低并发，所以可以设置多个 buffer pool 处理请求，加快响应速度。
[server] # 缓冲池大小 innodb_buffer_pool_size = 8589934592 # 缓冲池个数 innodb_buffer_pool_instances = 4
那么可以动态的调整缓冲池大小吗，向上面说的这种思路是没法动态扩容的，应为调整内存需要申请空间，在设计到数据拷贝，假设 8 核 16g 的机器，缓冲池设置为 2g，想调整为 4g，就需要服务器申请连续的 4g 内存，再将原来的数据拷贝过来，这个代价是及其庞大的，所以 mysql 为了优化这种情况，在缓冲池内部设计了
chunk 这种数据结构，也就是每个缓冲池由多个 chunk 组成，innodb_buffer_pool_chunk_size 控制 chunk 的大小，默认是 128M。
将一个 buffer pool 拆成多个 chunk，但是公用一套 free、flush、lru 链表，基于这种结构，就可以动态的调整缓冲池大小了，这是申请内存只需要申请 128M 的连续内存也就是一个 chunk 就可以，然后把 chunk 分配给 buffer pool 就可以了。
数据库状态
可以通过如下语句查看数据库状态，底下是缓冲池和内存相关内容
SHOW ENGINE INNODB STATUS;
BUFFER POOL AND MEMORY ---------------------- # 缓冲池最终大小 Total large memory allocated 136970240 # 字典表大小 Dictionary memory allocated 2006930 # 最大缓存页数量 Buffer pool size 8192 # 空闲缓存页数量 Free buffers 1024 # lru 链表缓存页数量 Database pages 6990 # 冷数据区缓存页数量 Old database pages 2560 # flush 链表缓存页数量 Modified db pages 0 # 等待加载的缓存页数量 Pending reads 0 # 从 lru、flush 链表即将刷入磁盘的数量 Pending writes: LRU 0, flush list 0, single page 0 # made young：lru 链表从冷数据访问后加载到热数据的数量 # not young： 在冷数据区 1s 内被访问了没进入热数据的数量 Pages made young 383532, not young 15777212 # 每秒从冷数据进入热数据区的数量，每秒在冷数据区 1s 内被访问了没进入热数据的数量 0.00 youngs/s, 0.00 non-youngs/s # 分别代表读取、创建、写入的缓存页数量 Pages read 7160471, created 407662, written 3495282 # 分别代每秒表读取、创建、写入的缓存页数量 0.00 reads/s, 0.00 creates/s, 0.00 writes/s # Buffer pool hit rate：每 1000 次访问，有多少次是命中缓冲池缓存的 # young-making rate： # 每 1000 次访问，有多少次访问将数据从冷数据区挪到了热数据去，以及没移动的数量 Buffer pool hit rate 985 / 1000, young-making rate 210 / 1000 not 44 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s # LRU len:lru 链表缓存页的数量 LRU len: 6990, unzip_LRU len: 0 # I/O sum：最近 50s 读取磁盘页的数量 # I/O cur：现在正在读取磁盘页的数量 I/O sum[44]:cur[0], unzip sum[0]:cur[0] --------------
物理存储格式
MySQL 一行数据是怎样存在磁盘上的
CREATE TABLE table_name (columns) ROW_FORMAT=COMPACT; ALTER TABLE table_name ROW_FORMAT=COMPACT;
可以在建表的时候，指定一个行存储的格式，也可以后续修改行存储的格式。这里指定了一个
COMPACT行存储格式，在这种格式下，每一行数据他实际存储的时候，大概格式类似下面这样：变长字段的长度列表，null值列表，数据头，column01的值，column02的值，column0n的值......
对于每一行数据，他其实存储的时候都会有一些头字段对这行数据进行一定的描述，然后再放上他这一行数据每一列 的具体的值，这就是所谓的行格式。除了COMPACT以外，还有其他几种行存储格式，基本都大同小异。
- Author:木木
- URL:https://www.mumudm.yop/article/d1180d81-b6df-42b5-b49e-5a5171ec9769
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!
Relate Posts
