MySQL性能调优(7)复习补充

1
2
3
4
5
6

SHOW STATUS LIKE '%innodb_buffer_pool%';
官网链接
Buffer Pool 默认大小是 128M（134217728 字节），可以调整。
这个可以作为优化，根据业务把缓冲池调大有利于更多的索引热点数据查询
https://dev.MySQL.com/doc/refman/5.7/en/server-status-variables.html
内存的缓冲池写满了InnoDB 用 LRU算法来管理缓冲池（链表实现，不是传统的 LRU，分成了 young 和 old），经过淘汰的数据就是热点数据。这个策略跟redis一样。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

同样是写磁盘，为什么不直接写到 db file 里面去？为什么先写日志再写磁盘？
我们先来了解一下随机 I/O 和顺序 I/O 的概念。
磁盘的最小组成单元是扇区，通常是 512 个字节。
操作系统和内存打交道，最小的单位是页 Page。
操作系统和磁盘打交道，读写磁盘，最小的单位是块 Block。
如果我们所需要的数据是随机分散在不同页的不同扇区中，那么找到相应的数据需要等到磁臂旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块数据，一次进行此过程直到找完所有数据，这个就是随机 IO，读取数据速度较慢。
假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序 IO。
刷盘是随机 I/O，而记录日志是顺序 I/O，顺序 I/O 效率更高。因此先把修改写入日志，可以延迟刷盘时机，进而提升系统吞吐。
当然 redo log 也不是每一次都直接写入磁盘，在 Buffer Pool 里面有一块内存区域（Log Buffer）专门用来保存即将要写入日志文件的数据，默认 16M，它一样可以节省磁盘 IO。
需要注意：redo log 的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自 buffer pool。redo log写入磁盘，不是写入数据文件。那么，Log Buffer 什么时候写入 log file？
在我们写入数据到磁盘的时候，操作系统本身是有缓存的。flush 就是把操作系统缓冲区写入到磁盘
log buffer 写入磁盘的时机，由一个参数控制，默认是 1。
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
0（延迟写）
log buffer 将每秒一次地写入 log file 中，并且 log file 的 flush 操作同时进行。
该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。
1（默认，实时写，实时刷）
每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，并且刷到磁盘
中去。
2（实时写，延迟刷）
每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file。但是 flush 操
作并不会同时进行。该模式下，MySQL 会每秒执行一次 flush 操作

1

跟redo log一样，double write 由两部分组成，一部分是内存的 double write，一个部分是磁盘上的 double write。因为 double write 是顺序写入的，不会带来很大的开销

1
2
3
4
5

例如一条语句：update teacher set name='盆鱼宴' where id=1;
1、先查询到这条数据，如果有缓存，也会用到缓存。
2、把 name 改成盆鱼宴，然后调用引擎的 API 接口，写入这一行数据到内存，同时记录 redo log。这时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，可以随时提交。
3、执行器收到通知后记录 binlog，然后调用存储引擎接口，设置 redo log为 commit状态。
4、更新完成。

1
2
3
4
5

1. 创建新页
2. 判断当前页（页#10）可以从哪里进行分裂（记录行层面）
3. 移动记录行
4. 重新定义页之间的关系
页分裂会发生在插入或更新，并且造成页的错位（dislocation，落入不同的区）论id设计的重要性

1
2
3
4
5

Using filresort代表：不能使用索引来排序，用到了额外的排序。
例如：查询用到了a字段上的idx_a，但是后面有order by b。
一些优化方式：
1.建立联合索引idx(a,b)
2.不使用MySQL的排序，改成在代码中排序

1
2
3
4

设置页的大小与磁盘的预读取特性有关系。局部性原理认为：当一个数据被用到时，其附近的数据也通常会马上被使用。所以顺序读取附近的数据，可以提升I/O效率。每次都至少读取一页
默认的16KB大小，在涉及表扫描和批量更新的业务场景中更实用，效率已经很高了
官网解释
https://dev.MySQL.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_size

1
2
3

如果我们定义了主键(PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引。 
如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引、
如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐 藏的聚集索引，它会随着行记录的写入而主键递增。

1
2
3

例如索引是index(a,b,c），select 索引里面的列
(包括select a，select b，select c，select a,b select a,c select b,c，select a,b,c)，
都不需要到主键索引的叶子节点获取完整数据，此时不需要回表。

1
2

举个例子，order_info 和 order_detail 有逻辑的主外键关系。
在操作order_detail的时候，不希望order_info被其他的事务修改，可以用共享锁，阻塞其他的事务修改

1
2

快照读（普通的select）用MVCC保证读一致性。
加锁的读和增删改，用lock保证读一致性

1
2
3
4

原子性（Atomicity，或称不可分割性） 通过undo log 实现
一致性（Consistency）通过AID特性与应用保证一致性
隔离性（Isolation）MVCC
持久性（Durability）通过redo log实现

1
2
3

我们不妨想一下，为啥 Nginx 内部仅仅使用了 4 个线程，其性能就大大超越了 100 个进程的 Apache HTTPD 呢？
在一核 CPU 的机器上，顺序执行A和B永远比通过时间分片切换“同时”执行A和B要快，其中原因，学过操作系统这门课程的童鞋应该很清楚。一旦线程的数量超过了 CPU 核心的数量，再增加线程数系统就只会更慢，而不是更快，因为这里涉及到上下文切换耗费的额外的性能。
在加上现在的磁盘又基本是SSD，无需寻址和没有旋回耗时的确意味着更少的阻塞所以更少的线程（更接近于CPU核心数）会发挥出更高的性能，只有当阻塞密集时，更多的线程数才能发挥出更好的性能。

1
2
3
4

MyISAM之所以可以把表中的总行数记录下来供COUNT(*)查询使用，那是因为MyISAM数据库是表级锁，不会有并发的数据库行数修改，所以查询得到的行数是准确的。
但是，对于InnoDB来说，就不能做这种缓存操作了，因为InnoDB支持事务，其中大部分操作都是行级锁，所以可能表的行数可能会被并发修改，那么缓存记录下来的总行数就不准确了。
我们知道，InnoDB中索引分为聚簇索引（主键索引）和非聚簇索引（非主键索引），聚簇索引的叶子节点中保存的是整行记录，而非聚簇索引的叶子节点中保存的是该行记录的主键的值。
所以，相比之下，非聚簇索引要比聚簇索引小很多，所以MySQL会优先选择最小的非聚簇索引来扫表。所以，当我们建表的时候，除了主键索引以外，创建一个非主键索引还是有必要的。

1
2
3
4
5
6

假设我们的树深度为2 我们id用bigint数据类型占8字节 我们一条数据1k大小
指针在InnoDB中占6字节 一个页为16k=16384字节 
我们一页就可以存放16384/14=1170个指向记录 注意！这是根节点
每个指向记录下面存(16k/1k)*1170=18720记录 
如果深度为3那么就是1170*1170*16=21902400条记录
所以说我们2000万的数据如果通过id搜索也就3次io。