声明：本博客所有文章均为博主原创，请勿非法复制。请勿盗取原创图片。
1.内存管理
- 1.需要经常找free数据块； free list（free链）
- 2.需要经常找冷的数据块； lru list（最近最少使用链）
- 3.就需要系统将这些logfile对应的脏块，写入到磁盘上。flush list（按照脏的早晚链起来的链）
默认2个logfile，每个20M。logfile小了，容易hang住。
一个数据库最基本的调整
- 增加组数；
- 增加logfile大小；
- 增加写线程的数量。
需要3种链（chain）
一种链一种作用。
一个个体可以在多个链上。
（比如同是一个班的学生，1.按高矮链起来 2.按男女链起来…）
对于链来说，我们需要进行遍历。
为什么要引入free,lru,flush list三种链
内存管理：
读线程将数据页从磁盘中调入内存之中时，会首先寻找free数据块，如果有free数据块，就将新来的数据放入free数据块中；如果没有free，就会寻找冷数据块，放入冷数据块中。而存储中的logfile文件也存在着轮转覆盖，假如有两个logfile文件，第一个写满后会写第二个，第二个写满后会重新覆盖第一个，这时候如果第一个logfile中对应的脏块还没有写完，就会先将脏块写入到磁盘之中再覆盖，首先要将较早脏的数据块写入到磁盘之中，这时候就会有问题，系统怎么知道哪个是free数据块，那些是冷数据块，那些是较早脏的快。这时候就引入了free list，lru list，flush list。
mysql中用链的形式把数据块穿起来。
- freelist就能够free块穿起来，当寻找free块时，就按照某种顺序来将新来的数据写入到free数据块中。
- lru list就是将冷数据块连接起来，按照某种顺序将新来的数据覆盖上去
- flush list原理相同，通过链把脏数据按照某种顺序（比如最早脏的在最左边）将脏数据连接起来，方便写入。
锁，用来管理对共享文件的并发访问
锁的类型
- latch称为栓锁（轻量级的锁），因为其要求锁的时间非常短。
- 在innodb存储引擎中，又可以分为mutex（互斥量）和rwlock（读写锁）。其目的是用来保证并发线程操作临街资源的正确性，并且通常没有死锁检测的机制。
- 而lock锁的对象是事务，用来锁定数据库的对象，如表、页、行。并且一般lock的对象仅在事务commit或者rollback后释放（不同事务的隔离级别释放的时间可能不一样）。有死锁机制。
链上是有锁的。latch 锁。
例子：
线程1持有latch，对free list正在进行遍历；
线程2也想持有latch，对free list进行遍历；
结果，线程2被阻塞。
线程的三种状态
- gets：获取latch
- misses：获取latch失败了
- sleeps：执行空代码，显得很忙。
都伴随着latch的征用。
latch争用的表现
- 1.latch争用会表现为cpu繁忙；
- 2.latch争用没有排队。
latch（锁）争用的原因
- 1.内存访问频繁；
- 2.list 太长了。
latch争用的过程
首先链上是有锁的，这个锁叫latch，当一个线程持有latch时（也就是对这个链进行操作的时候），其他的线程得在一边看着。比如线程1持有latch，正在对freelist进行遍历，线程2也想进行遍历，是不可能的，所以现在线程2阻塞，只有两种选择，一是退出，二是等待，但如果等待就会占用cpu，如果想占用cpu就要有事可做，否则cpu会把没事做的线程踢出去，所以线程2就会执行某段空代码来让自己忙起来（所以虽然线程2在等待，但cpu还是忙碌的），这时候线程2会有三种情况：gets，misses，sleeps。gets就是随机的去看看freelist是否已经可以遍历，misses就是线程2错过了freelist可遍历时间，被线程3抢走了，只能重新等待，sleeps就是misses的太多了，受不了了，不执行空代码乖乖的退出了。
- latch争用过程中，会表现的cpu繁忙
- latch争用没有排队
- latch争用比较严重时，要调整innodb_buffer_pool中instance的数量，
- instance数量多了，每个instance中数据就少了，链也就短了。
- latch争用繁忙时，内存被频繁的访问，因为链在内存之中。
- 对于访问不是很频繁，同时相对较短的链，用mutex来保护而不用latch，因为mutex更为简单，占用空间小。
latch争用常用监控指标
RW-shared spins 0, rounds 16, OS waits 8
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait:16.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
这几个数据是看latch与mutex的争用成都如果过高
需要优化mysql，降低对内存读的数量（很重要）。
mutex 互斥锁
操作系统的mutex 的特点：
- 小；通过os申请，不占用自己的内存。
mysql>show engine innodb status \G
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2, signal count 2
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 2, rounds 60, OS waits 2
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 30.00 RW-shared, 0.00 RW-excl
--------- oswaits：sleep的时间
- innodb在获得mutex时是两阶段的。如果Mutex被人锁住了，那么它不会像教科书里面的那样，直接就sleep等待被唤醒了。而是先做一个循环，不断去获得mutex锁，称之为spin-wait，然后才sleep。因为sleep等待被唤醒的代价还是比较高的。通过spin-wait，可以明显降低这个开销。
- mutex spin waits 这个代表的是线程获得的锁，但是被别人锁住了，所以它得首先spin-wait；
- rounds：是线程在spin-wait循环检查mutex是否已经释放的探测次数；
- OS waits：是spin-wait完成以后，还是没有获得mutex，不得不sleep的次数。这个主要是评估mutex获取不到的比例。比如：
- 请求mutex不到的情况是 mutex spin waits的数，但是经过spin-wait，实际上只有OS waits的次数，也就是说，中间的差值，就是稍微等一下就能拿到mutex。
mutex：排斥。
spin：旋转。
rounds：旋转一周是1round；
os waits：就是sleep，被踢出去了；
第二行是：以共享的形式访问的时候的统计；
第三行是：以排他的形式访问的时候的统计。
如何降低latch争用
- 对访问不是很频繁，同时相对较短的链，我们使用mutex就可以来保护这些链。
- 所以，对于访问频繁的链，（会有大量的读和写），还是要用latch来进行保护。
- mutex可以理解为排他的latch。
- mutex只能写编号。
解决方案：
- 1.优化SQL，降低对内存读的数量；
- 2.增加instances的数量。
关于LRU链表
Innodb存储引擎对传统的LRU（Least Recently Used 最近最少使用）算法做了优化，LRU列表中加入了midpoint位置。新读取到的页，虽然是最新访问的页，但并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint位置。
这个算法在InnoDB存储引擎下称为 midpoint inserttion strategy。在默认配置下，该位置在LRU列表长度的5/8处。
midpoint位置可由参数 innodb_old_blocks_pct 控制，eg：
mysql>show variables like 'innodb_old_blocks_pct'\G;
可以看到，参数innodb_old_blocks_pct 默认值是37，表示新读取的页插入到LRU列表尾端的37%的位置（差不多3/8的位置）。在innodb存储引擎中，把midpoint之后的列表称为old列表，之前的列表称为new列表。可以简单理解为new列表中的页都是最为活跃的热点数据。
innodb的37%的空间是可以让人来刷的。（意思就是内存的37%拿出来让人刷，就是冷数据区的大小）；
建议innodb_old_blocks_pct 调成20%（调整内存参数）：
（查官方手册，发现是全局动态变量。）
set @@global.innodb_old_blocks_pct=20;（内存的80%给DB，DB的80%给热数据（在一个时间段内被频繁的访问！！））
即，一个64G的物理内存，80%给数据库，数据库的80%给热数据区，即40.96G给了热数据区。
innodb_old_blocks_time的建议
这个参数用来表示 页读取到mid位置后，需要等待多久才会被加入到LRU列表的热端。
使LRU列表中的热点数据不被刷出：
set global innodb_old_blocks_time=1000；放在冷热数据交界处，默认1000ms，过了这1s，还能存活下去，就调到热数据区了。
冷热数据区的监控
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
- youngs/s：坚持到了1s，进入了热数据区；
- non-youngs/s：没有坚持到1s，于是被刷出去了。
non-youngs/s的值过大原因
- 1.可能存在严重的全表扫描（频繁的被刷出来）
- 2.可能是pct设置的过小（冷数据区就很小，来一点数据就刷出去了）
- 3.可能是time设置的过大（没坚持到1万s，被刷出去了）
youngs/s的值过大的原因
正常不可能一直很高，因为热数据区就那么大，不可能一直往里调。
- 1.pct过大（pct过大，冷区大，热区小，time过小，就容易young进去。）
- 2.time过小
log_buffer_pool默认8M够用吗
默认8M。8M足够用。
在通常情况下，8M的重做日志缓冲池足以满足绝大部分的应用，因为重做日志在下列三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中：
master thread 每一秒将重做日志缓冲刷新到重做日志文件；
每个事务提交时会将重做日志缓冲刷新到重做日志文件；
当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件。
PS：
但是，在实际中，一般设置为50M或100M。因为内存足够。
关于log buffer 调整成100M的理论依据和思路解析：
1.我们的内存足够
2.当系统IO忽然阻塞了，忽然读进程大量的上来了，就会忽然产生了大量的redo log，log_buffer就会瞬间满了，从而写进程就被阻塞了，数据库瞬间就被hang住。
所以，为了防止出现这种情况，一般实际就设置为100M了。
InnoDB关键特性
InnoDB存储引擎的关键特性包括：
- 插入缓冲（Insert Buffer）
- 两次写（Double Write）
- 自适应哈希索引（Adaptive Hash Index）
- 异步IO（Async IO）
- 刷新临接页（Flush Neighbor Page）
上述这些特性为InnoDB存储引擎带来更好地性能以及更高的可靠性。
1.插入缓冲 insert buffer
Insert Buffer 可能是InnoDB存储引擎关键特性中最令人激动与兴奋的一个功能。把插入缓冲认为是缓冲池的一个组成部分是完全错误的！InnoDB缓冲池中有insert buffer信息固然不错，但是insert buffer和数据页一样，也是物理页的一个组成部分。
在InnoDB存储引擎中，主键是行唯一的标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引（Primary Key）一般是顺序的，不需要磁盘的随机读取。比如按下列SQL定义表：
create table t (
a int auto_increment,
b varchar(30),
primary key(a)
);mysql里面的表，有个特点：
为什么会有insert buffer：为了解决索引的离散读问题
索引组织表
- 这个表在存储的时候，按照主键排序进行存储。
- 同时在主键上建立一棵树，这样就形成了一个索引组织表。
我们往表中插入数据的时候，一般都是按照主键的顺序，依次插入；
所以，对于表来说，因为插入操作导致的物理读就很低；
但是对于这个表上的索引来说，索引的顺序和表的主键的顺序，有可能完全不一样。 
没有插入缓冲可能会出现的问题：索引的离散读
对于表来说的顺序插入，但是对于索引来说，就可能是离散插入。既然是离散，那么就可能产生过多的物理读。
- 我们在insert的时候，产生了过多的物理读，导致了性能低下。
怎么解决索引的离散读问题
插入缓冲 insert buffer
在内存中建一个插入缓冲，三个要插入的数据对应的数据链在内存中没有，就先在插入缓冲里存着。存的时候，按照地址存，发现有一些会放在同一个数据页里，攒的足够多了，这时，就把该数据页调到内存中（这个动作叫merge）（多次insert才对应一次merge）（减少了物理读），之后就可以删除该数据页了。
用到页的时候，才调到内存里。减少了物理读。
为了解决索引的离散读的问题，我们采取了一个措施：insert buffer：
内存里面单独开辟这样一个区域，用来作为insert buffer区域；
insert buffer具体的作用
4种情况时：
- 1.表上的索引比较多；
- 2.很多索引的顺序和表的顺序（主键的顺序）非常不一致；
- 3.这个表的insert/update/delete很多，特别是insert很多；
- 4.如果因为DML导致的物理读很高，那就是insert buffer过小的问题。
PS：
insert或delete很多的情况下，就会出现索引的离散读的问题，
同时update索引列的时候，也会出现索引离散读的问题。
小结：如何解决索引的离散读问题（因为DML造成的）
答：设置足够高的change buffer（解决insert、delete、update的索引离散读问题）
（PS：一个表上的索引最好不要超过6个！！）
mysql中的实例：
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0①change buffer 占用的内存大小：seg size：2*16K
②2个页，实际用了1个：size：1；
空闲列表的长度：free list len
change buffer过大或者增长迅猛原因
- 1.数据库的insert、update、delete（索引列）频繁，或者出现批量操作
- 2.表的索引过多
- 3.很多索引压根没有被使用
被合并的条件：
- 1.索引数据页里面需要被合并的记录数足够多；
- 2.这个索引页被使用到，例如：这个索引页被读取到，对于这个索引页，我们顺便进行合并。
change buffer 实例
（一次insert就是一次物理读！！！但是只有224万次，所以，因为合并，节省了物理读！！）
（第二行的discarded）
上图解析：
- seg size 显示了当前 insert buffer 的大小为 11336*16KB，大约是177MB；
- free list len 代表了空闲列表的长度；
- size 代表了已经合并记录页的数量。
- 而黑体部分的第2行可能是用户真正关心的，因为它显示了插入性能的提高。
- inserts 代表了插入的记录数；
- merged recs 代表了合并的插入记录数量；
- merges 代表合并的次数，也就是实际读取页的次数。
- merges:merged recs 大约是1:3，代表了插入缓冲将对于非聚集索引页的离散IO逻辑请求大约降低了 2/3。
怎么调整change buffer？
答：通过 innodb_change_buffer_max_size 变量来调整。
（指的是25%，意思是innodb buffer pool大小的25%）
①25%占满了
②效果不明显。
只想对insert进行合并怎么办？调整innodb_change_buffering的all改成insert！
ibdata共享表空间
mysql中：
- 数据库：一个数据库就是一个独立的目录；
- 表（innodb）：一个表对应着两个文件，对于ibd文件来说，里面存放着表数据和这个表对应的索引。
mysql里面有很多数据库，还有很多表。
ibdata共享表空间里有什么
- 1.undo数据放在里面
- 2.insert buffer
- 3.double write
insert buffer带来的问题
- 1.磁盘上的索引在正常工作期间，索引数据和表数据是不一致的。也就是磁盘上的这些索引压根不能使用。
- 2.数据库崩溃以后（insert buffer里面的内容就没了），索引要能够使用，需要下面的一些内容进行组合：
- 1.磁盘上的索引数据+ibdata中的索引数据+内存中的insert buffer对应的redo log；
补充知识之索引
在数据库中，从表中取数据的方式：
- 1.全表扫描（从表的第一行开始，一直找到最后一行）
- 2.通过索引
（优化SQL的时候：走全表扫描如果性能差，就建立索引。）
（一般，取少量数据的时候，适合按索引；取大量数据，适合全表扫描。）
eg：要找：select * from t where name=’james’;
如果走全表扫描的话，相当于对100万行数据全部扫描，对于每一行数据都使用where条件进行过滤。（99.9999%数据被过滤掉）
索引的工作过程
用户抛出某个请求需要用到索引，就将索引从存储中读出来，然后将索引对应的数据页从磁盘中读出来，并将其中的符合需求的数据呈现给用户。
索引工作的场合
对一个100万行表进行
select * from t where name=‘james’;如果做全表扫描的话，相当于100万行数据全部扫描，对于每一行数据都使用where条件进行过滤，大多数数据都被过滤掉，这时候就会显得全表扫描效果很差，就需要通过索引来进sql 。
索引和insert buffer结合
在内存中插入一个数据行内存会读取对应的数据块，并存放在内存里，而这些数据页对应的索引页也将被读取到内存中，但是读取索引页会造成大量物理读（一个数据块可能有很多索引页），所以就将索引页和数据行对应的索引行放入到 insert buffer中（而存放索引行的顺序是按将来索引行所在索引页的地址存）
如何进行数据访问如何建立索引
一般的索引通过树高来进行数据访问，
create insex + 索引的名字 + on +表的名字 + （指定表的列）
如何建立索引
CREATE [UNIQUE] [CLUSER] INDEX <索引名> ON <表名> (<列名>);
UNIQUE：表明此索引的每一个索引值只对应唯一的数据记录
CLUSTER：表明要建立的是聚簇索引（指索引项的顺序与表中记录的物理顺序一致）
例：
CREATE CLUSER INDEX Stuname ON Student(Sname);
#在student表的sname列建立一个聚簇索引，student中记录按照sname值的升序排列.如何确认索引是否生效
explain select * from t2 where name='x';
2.double write（两次写）
介绍double write之前我们有必要了解partial page write 问题 :
InnoDB 的Page Size（页面大小）一般是16KB，其数据校验也是针对这16KB来计算的，将数据写入到磁盘是以Page为单位进行操作的。而计算机硬件和操作系统，在极端情况下（比如断电）往往并不能保证这一操作的原子性，16K的数据，写入4K 时，发生了系统断电/os crash ，只有一部分写是成功的，这种情况下就是 partial（局部） page write 问题。
很多DBA 会想到系统恢复后，MySQL 可以根据redolog 进行恢复，而mysql在恢复的过程中是检查page的checksum，checksum就是page的最后事务号，发生partial page write 问题时，page已经损坏 ，找不到该page中的事务号，就无法恢复。
Double write 是InnoDB在 tablespace上的128个页（2个区）是2MB；
其原理：
为了解决 partial page write 问题 ，当mysql将脏数据flush到data file的时候, 先使用memcopy 将脏数据复制到内存中的double write buffer ，之后通过double write buffer再分2次，每次写入1MB到共享表空间，然后马上调用fsync函数，同步到磁盘上，避免缓冲带来的问题，在这个过程中，doublewrite是顺序写，开销并不大，在完成doublewrite写入后，在将double write buffer写入各表空间文件，这时是离散写入。
如果发生了极端情况（断电），InnoDB再次启动后，发现了一个Page数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了。
内存将原先写入磁盘的脏页先写到内存里的double write中，然后从内存里double write中将脏页写入到ibdata里的double write中和数据文件中。
判断数据库系统繁忙度
mysql> show global status like '%dbl%'; 
（现在的比值是21:1，意思就是每次写，写21个页。）
注意比值：当written/writes比值为128:1时，就是写操作比较繁忙，压力比较大。
小于128时，就是不繁忙。
为什么written/writes比值是128:1就是繁忙的？
关于索引的资源消耗（图解）
主要的资源消耗在：
- 1.索引的树高；
- 2.要访问的索引的数据；
- 3.（主要消耗）从索引往表上跳的时候，而跳的时候，又主要是取决于：表的主键列的有序度和索引列的有序度。
索引的物理布局的三大特点
- 索引小（找起来就快了）
- 索引有序（快速定位终点）
- 索引上面有棵树（能快速定位起点）
3.自适应哈希索引AHI（Adaptive Hash Index）
（可以理解为就是为了处理上面的“通过索引访问表的时候的主要资源消耗问题”）
（其实自适应hash索引解决的是：树高的问题）
自适应哈希索引
哈希（hash）是一种非常快的查找方法，在一般情况下这种查找的时间复杂度为O(1)，即一般仅需要查找一次就能定位数据。而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般为3~4层，所以需要3~4次的查询。
InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。AHI是通过缓冲池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。
自适应hash索引
- 1.解决的问题：频繁访问一个索引，而且一定是使用==号！
select * from t where name=='x';
select * from t where name<='x';就不起作用了假如你频繁的访问一个索引，就要频繁的访问索引对应的树高，假如这个索引是x，这时候针对x会做一个函数运算，得到一个地址，将这个地址存放到一个新建的索引中去。经过时间的推移，xyz。。都会被这样做，这个新建的索引列里的地址就会越来越多，以后再来找x的时候就可以通过函数得到和新建的索引区对应的数据块寻找。（就是不走树高了，直接去找新建的索引，就省去了时间咯）
- 2.自适应：只要打开功能，就自己去适应；
- 3.解决的是访问数的问题。
自适应哈希索引的监控指标
MariaDB [(none)]> show engine innodb status \G
Hash table size 276671, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s- Hash table size：以字节为单位。
- hash searches/s：使用的数。
- non-hash searches/s：没有使用hash的数。
对频繁访问的列的值进行hash运算，得到值，存到哈希索引里，没找到，则non-hash searches/s 加1， 找到了hash searches/s加1。
打开或关闭自适应哈希索引的功能
变量名：innodb_adaptive_hash_index
（自适应哈希索引可以关闭，起的作用并不是很大。）
这些参数都是通过官方文档查到的：
4.异步IO
启用Naive AIO，恢复速度可以提高75%！！
（一定要打开此功能！）
（在linux中，默认是打开的：）
mysql>show variables like '%aio%';
可以通过开启和关闭Native AIO 功能来比较InnoDB性能的提升。官方的测试显示，启用Native AIO，恢复速度可以提高75%。
在InnoDB存储引擎中，read ahead方式的读取都是通过AIO完成，脏页的刷新，即磁盘的写入操作则全部由AIO完成。
如何确认是否打开异步IO
- 操作系统层面默认是开启的；
- Linux默认是开启的；
- ldconfig -p | grep libaio（ldconfig看加载了哪些库）
- ldconfig -p | grep libaio（ldconfig看加载了哪些库）
- 数据库层面默认也是开启的。innodb_use_naive_aio
数据库：innodb_use_naive_aio参数
在数据库系统中，不管是用户线程还是读线程，他们发出请求后是，不会等待请求回应的，直接就去干别的去了，而它们的请求被放到队列里，后台的文件系统会抓紧处理队列里请求，所以用户会觉得读线程处理请求熟读特别快（用户线程抛出的请求，读线程立马抛入到队列里，接着响应接下来的请求）；有可能在队列的一些请求对应的数据页是相同的，这时候就会大量减少磁盘io。
（图解：） 
5.刷新邻接页（Flush Neighbor Page）
参数：innodb_flush_neighbors
此功能建议关闭。（这功能可以打开，影响不大。）
什么是刷新邻接页？
InnoDB存储引擎提供了flush neighbor page（刷新邻接页）的特性。其工作原理为：
当刷新一个脏页时，InnoDB存储引擎会检测到该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新。
这样做的好处是：通过AIO可以将多个IO写入操作合并为一个IO操作，所以该工作机制在传统机械磁盘下有着显著的优势，但是需要考虑到下面两个问题：
- 是不是可能将不怎么脏的页进行了写入，而该页之后又会很快变成脏页？
- 固态硬盘有着较高的IOPS，是否还需要这个特性？
为此，InnoDB存储引擎从1.2.x版本开始提供了参数innodb_flush_neighbors，用来控制是否启用该特性。
对邻接页参数的建议
- 对于传统机械硬盘建议启用该特性；
- 而对于固态硬盘有着超高IOPS性能的磁盘，则建议将该参数设置为0，即关闭此特性。
邻接页的具体功能、弊端、好处
假如将innodb_flush_neighboors功能打开，在写冷脏页的时候如果发现冷脏页周围的页在物理地址上挨着，就会将周围的脏页也顺手写入磁盘，（磁盘不害怕一次写多个而害怕写多次）。坏处，还是增加了物理io（虽然不多）而且如果这个顺便被写入的脏页是个热脏页（总是被修改），那么在内存里一会又被写入了 。