InnoDB存储引擎

最新推荐文章于 2024-05-27 17:55:17 发布

jiangfeng999

最新推荐文章于 2024-05-27 17:55:17 发布

阅读量1.3k

点赞数

分类专栏：数据库/NoSQL # MySQL

本文链接：https://blog.csdn.net/MSDA/article/details/103287461

版权

数据库/NoSQL 同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

MySQL

2 篇文章 0 订阅

订阅专栏

InnoDB存储引擎

1. 连接mysql
2. 数据库与引擎的关系
3. 版本介绍
4. 体系架构
5. 文件
6. 表
7. 索引
8. 锁
9. 事务

innodb采用聚集的方式存储数据，其数据结构是一颗B+树，其所有数据都以主键的顺序排在B+树的叶子节点上。同级叶子节点都有指向旁边叶子节点的指针，方便顺序读取。

如果在定义表时没有显式的生成主键，那么InnoDB会为每一行自动生成一个6字节的ROWID，作为主键。

从mysql5.5.8开始，innoDB作为mysql 的默认存储引擎。

mysql支持全文索引，MyISAM、InnoDB、Sphinx存储引擎都支持全文索引
不同的操作应该使用不同的存储引擎才能发挥最大效率。对于ETL（抽取转换加载）这种操作MyISAM有优势，而对于OLTP（联机事务处理）的操作，InnoDB的效率更好。

1. 连接mysql

tcp 远程连接 mysql -h [host] -u [username] -p [password]
linux支持socket连接

查询socket文件地址
show variables like 'socket'
执行
mysql -u[root] -S [/var/lib/mysql/mysql.sock]

配置文件中datadir，指定数据库所在的路径。
可通过show variables like 'datadir'

2. 数据库与引擎的关系

数据库：物理操作系统文件与其他文件类型的集合，通常以ibd、frm、MYD、MYI（MYSAM引擎）文件结尾的文件。有种特殊的引擎，NDB引擎是内存型数据库，文件存储于内存中，但时定义不变。
实例：Mysql数据库由后台线程和一个共享区域组成。数据库实例才是用来操作数据库文件的。
数据库实例在系统上表现就是一个进程。一个数据库可能对应多个实例，如分布式一个数据库被多个实例使用。

Mysql是啥，mysql又是数据库，又是数据库实例。数据是存储在存储器上的，我们想要对数据进行DML之类的操作，那么需要通过mysql程序去执行，由程序去管理数据库文件。

mysql组成

连接池组件
管理服务和工具组件
SQL接口组件
查询分析器组件
优化器组件
缓冲组件
插件式存储引擎
物理文件

3. 版本介绍

InnoDB个版本功能对比

版本	功能
老版本InnoDB	支持ACID、行锁设计、MVCC
InnoDB 1.0.x(Mysql 5.1)	继承之前，增加compress和dynamic页支持
InnoDB1.1.x(Mysql 5.5)	继承之前，增加Linux AIO、多回滚段
InnoDB1.2.x（Mysql5.6+）	继承之前，增加全文索引、在线索引

show engines;

4. 体系架构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c4K7Ba7w-1574904515580)(./1478846975826.png)]

4.1. 后台线程

InnoDB存储引擎是多线程模型，后台有多个线程负责处理不同的任务。

4.1.1. Master Thread

主要负责将缓冲池的数据异步刷新到磁盘上,保证数据一致性.

4.1.2. IO Thread

InnoDB使用大量异步IO来处理写IO请求,IO Thread主要负责这些IO请求的回调处理.

可通过show ENGINE INNODB STATUS 来观察

截取部分字段
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
256 OS file reads, 55 OS file writes, 9 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------

可看到有十个IO Thread,一个插入缓冲，一个log，四个读，四个写

4.1.3. Purge Thread

事务提交后,使用undolog不再需要,需要PurgeThread来回收使用并分配的undo页，1.1版本只支持1个线程。1.2开始支持1个以上的线程了。

[mysqld]
innodb_purge_threads=1

可查询
show variables like 'innodb_purge_threads';

4.1.4. Page Cleaner Thread

1.2版本才引入的，将之前版本中脏页的刷新操作都放在单独的线程中执行，减少Master Thread的工作以及对用户查询线程的阻塞。

4.2. 内存

内存由以下三个区域组成

4.2.1. 缓冲池

InnoDB是基于磁盘存储的，其数据结构B+树的一个叶子节点的大小通常是一个操作系统的页的大小。

将磁盘数据读到内存中，用于加快数据库的读取速度，而专门划分出来的这块内存区域就叫做缓冲池。

数据库优先从缓冲池中读取数据，如果命中则返回，如没命中就读取磁盘。对于数据库中的修改操作，也是先修改缓冲池中的页，然后以一定频率（不是在每次更新时马上）同步到磁盘上。

缓冲池中的页主要是

数据页
索引页
undo页
插入缓冲
自适应哈希索引
锁信息
数据字典

其中数据页和索引页占了很大一部分。

下图是innodb内存图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E4m3D65J-1574904515582)(./1478850222440.png)]

可自行配置

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=96M

从解释上看,保存了索引行数据,设置大小为80%的物理内存即可.不要设置太大,可能导致和操作系统的分页进行竞争.32位系统内存也就2-3.5G,不要设置太高
查询show variables like 'innodb_buffer_pool_size'

从InnoDB 1.0.x开始就支持多个缓冲池实例。

每个页根据hash值分配到不同的缓冲池中，减少数据库内部资源竞争，提高并发量。
可自行配置

# The number of regions that the InnoDB buffer pool is divided into.
# For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,
# by reducing contention as different threads read and write to cached pages.
innodb_buffer_pool_instances=8

show engine innodb status

---BUFFER POOL 0
Buffer pool size   16384
Free buffers       16346
Database pages     38
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 38, created 0, written 2
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 38, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   16384
Free buffers       16378
Database pages     6
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
...

可查到八个缓冲池实例的运行情况。

在mysql5.6后，information_schema下的INNODB_BUFFER_POOL_STATUS记录了每个缓冲池的运行情况

在工作机16G内存的机器上，每个缓冲池200M，8个缓冲池实例是ok的。在我的个人8G笔记本上，这样配置只有缓冲池大小生效，缓冲池实例数死活不生效。后面网上查询必须得缓冲池大小大于1G，多个缓存池实例才生效

4.2.1.1. 缓冲池管理算法 -LRU算法

缓冲池是一个很大的区域，用于存放各种类型的页，对于页的资源管理一般做法是通过LRU最近最少使用的算法来进行管理的。即最少使用的页放在列表尾，最新使用的放在列表头，当有新页加载进内存时，移除列尾的页。

在InnoDB引擎中，一页的大小默认为16KB，INnoDB中对传统的LRU算法做了优化，加了一个midpoint位置。
可通过show variables like 'innodb_old_blocks_pct'查询

mysql> show variables like 'innodb_old_blocks_pct';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+
1 row in set

这个值是百分制,表示新读取的页放在LRU列表的尾端37%的位置.

在innodbDB中,把midpoint之后的的列表成为old列表,之前的列表成为new列表.new列表中的页都是活跃数据.

为什么要划分LRU区域？
对于常见的索引和数据库的扫描操作都会读取全部的页，如果每读一页都放到LRU列表的队首，那么这个算法的意义就没了。这些被查询的页通常只在这次查询中被用到，并不是活跃的，会导致真正活跃的页被移除。

对于如何判断活跃页，有个参数innodb_old_blocks_time,表示页读取到mid位置后需要等待多久才会被加到LRU列表的热端.

整个运行流程

数据启动时，LRU列表是空的，此时页都放在Free页中。即内存大小约等于Free列表+LRU列表。当需要从缓冲池中分页时，首先从Free列表中查找是否有可用空闲页，若有从Free页中删除，移到LRU列表中。否则，淘汰掉LRU列表中末尾的页，将内存分配给新的页。
当页从LRU的old部分加入到new部分时,叫做page made young。因为Innodb_old_blocks_time的设置导致页没有从old部分移动到new部分的操作成为page not made young

此时再看缓冲池运行状态

----------------------
---BUFFER POOL 0
Buffer pool size   16384    缓冲池大小/页.  16384*16k = 256MB
Free buffers       16346    Free列表中页的数量
Database pages     38       LRU中的页的数量
Old database pages 0      
Modified db pages  0  flush列表
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0         由old移到new部分和 因为时长限制而没移到new部分
0.00 youngs/s, 0.00 non-youngs/s
Pages read 38, created 0, written 2
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 38, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

InnoDB从1.0.X支持压缩页的功能,将原本16KB的压缩为1kb,2kb,4kb,8kb.对于非16kb的页，由unzip_LRU列表进行管理,应当注意LRU中的页包括了unzip_LRU的页.

unzip_LRU列表分配页的过程

检查4KB的unzip_LRU列表
若有，则直接使用。
若没有，检查8KB的unzip_LRU列表
若能得到空闲页，将页分成两个4KB页，存放到4KB的unzip_LRU列表中.

在LRU列表中的页被修改后,该页称为脏页(缓冲池上的页和磁盘上的页数据不一致).数据库会通过checkpoint机制将脏页刷新回磁盘.
Flush列表中的页就是脏页列表,Flush列表和LRU列表中都有脏页.LRU列表用来管理缓冲池中页的可用性,Flush列表用来将脏页刷新回磁盘…

innodb引擎状态中 Modified db pages就是脏页数量

4.2.2. 重做日志缓存

InnoDB首先讲重做日志信息放到这个缓冲区，按一定频率将其刷新到重做日志文件，可由innodb_log_buffer_size配置大小，默认8MB。
以下三种情况会将重做日志缓存中内容刷新到重做日志文件中

Master Thread 每秒将重做日志缓存刷到日志文件中
每个事务提交时会将触发刷新
当重做日志缓存剩下空间小于一半时，触发刷新。

4.2.3. 额外内存池

内存管理通过内存堆的方式进行。
在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。

4.3. Checkpoint技术

由于脏页的原因，数据刷新方式是一个问题。
若每发生一次脏页就同步一次磁盘，那么如果热点数据集中在某几个页中，那么数据库性能会很差。
如果在刷新回磁盘过程中发生宕机，那么内存中的数据会丢失。为了避免这种情况，当事务提交时，先写重做日志，再修改页，当宕机导致数据挂掉时，还有重做日志文件记录来恢复数据
使用checkpoint技术来进行同步刷新解决点

缩短数据库的恢复时间

如果重做日志太大，恢复起来代价很大

缓冲池不够用时，将脏页刷新回磁盘
重做日志不够用时，刷新脏页

可以理解为checkpoin就是对重做日志执行点的一个标记，checkpoint之前的重做日志都刷新回磁盘上了，checkpoint之后的重做日志记录都在内存中。
当缓存池不够用时，LRU淘汰最长不被使用的页，如果此页是脏页，那么就得强制执行checkpoint。

innoDB中，有两种checkpoint

Sharp Checkpoint
发生在数据库关闭时将所有脏页刷新回磁盘，默认选择.
Fuzzy Checkpoint
只刷新一部分脏页。

对于FuzzyCheckpoint有几种触发情况

Master Thread Checkpoint
每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘，这个过程是异步的
FULSH_LRU_LIST Checkpoint
LRU列表要保证有100个左右空闲页可使用。在InnoDB1.1.x之前，需要检查LRU列表是否有足够的空闲页供用户查询使用，这会阻塞用户的查询操作。若没有则会移除LRU末端的页，如果页中有脏页，那么需要进行Checkpoint。innodb 1.2.x开始，这个检查放在了Page Cleaner线程中，可通过innodb_lru_scan_depth控制可用页数量,默认1024.
Async/Sync Flush Checkpoint 指重做日志不可用的情况,将一些页强制刷新回磁盘,此时脏页是从脏页列表中选取的.LSN表示标记版本.还未同步的重做日志的大小占整个重做日志大小的75%以下是不会刷新任何脏页的.当大小在75到90之间,触发Async Flush,从Flush列表中刷新足够的脏页回磁盘,保证小于75%.当大于90%时,触发Sync Flush,刷新足够多脏页回磁盘,保证占比到75%以下.在1.2x后的innodb中,都单独的page cleaner thread中,不会阻塞用户查询线程.
Diry page too mush, 当脏页数量太多,强制执行Checkpoint,默认占比缓冲池75%
mysql> show variables like 'innodb_max_dirty_pages_pct';

±---------------------------±----------+
| Variable_name | Value |
±---------------------------±----------+
| innodb_max_dirty_pages_pct | 75.000000 |
±---------------------------±----------+

4.4. Master Thread

Master Thread具有最高的线程优先级别，内部由多个循环组成：主循环、后台循环、刷新循环、暂停循环。

主循环主要分两块，每秒的操作和每十秒的操作。当然这时间并不能保持很精确，在负载很大的情况下可能会有延迟，但是保持这个频率。
每秒一次的操作包括

日志缓冲刷新回磁盘，即使事务没提交（总是）

保证再大的事务也能很快的提交

合并插入缓冲（可能）

InnoDB判断前一秒的IO次数是否小于五次,如果小于五次,认为IO压力小,可以执行合并插入缓冲的操作

最多刷新100个innodb的缓冲池中的脏页到磁盘（可能）

通过判断当前缓冲池脏页比例是否超过配置的'innodb_max_dirty_pages_pct';默认75%,超过阈值就强制执行磁盘同步,将1000个脏页写回到磁盘中.

如果当前用户没有活动，切换到后台循环（可能）

每十秒的操作

刷新100个脏页到磁盘(可能)
合并至多5个插入缓冲(总是)
将日志缓冲到磁盘(总是)
删除无用的undo页(总是)
刷新100个或10个脏页到磁盘(总是)

后台循环

删除无用的Undo页(总是)
合并20个插入缓冲(总是)
跳回到主循环(总是)
不断刷新100个页直到符合条件

1.2.x之后的刷新脏页的操作都由单独线程Page cleaner Thread来完成,提高系统并发性

4.5. InnoDB关键特性

特性包括

插入缓冲
两次写
自适应哈希索引
异步io
刷新邻接页

先了解聚集索引和非聚集索引

4.5.1. 插入缓冲 insert buffer

对于一个只维护主键索引的表的插入是非常快的，只需往B+树后面的叶子节点插入即可，但是对于非聚集索引的叶子节点插入就不是顺序的了，此时需要离散的访问非聚集索引页.

有了insert buffer，对于非聚集索引的插入和更新操作不是每一次直接插入到索引页中，先判断插入的非聚集索引是否在缓冲池中，若在，则将记录直接插入缓冲池中，由缓冲池的机制同步回磁盘。若不在，则插入到insert buffer中，以一定频率和情况进行insert buffer和辅助索引的子节点的合并操作。注意，insert buffer也是一颗B+树

使用插入缓冲要满足以下条件

索引是辅助索引
索引不是唯一的，在插入时，不会查找索引来判断唯一性，如果要去查找就会出现离散读取，那样就和插入缓冲的初衷相悖了

当大量数据在插入缓冲中，一旦宕机恢复起来就慢咯。

在show engine innodb status可查

-------------------------------------
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

修改IBUF_POOL_SIZE_PER_MAX_SIZE来对缓冲池大小限制,3就是值插入缓冲最大占缓冲池1/3内存

4.5.1.1. Change Buffer

InnoDB对于DML操作(INSERT,DELETE,UPDATE)都进行冲.和Insert buffer一样，适用对象依然是非唯一的辅助索引。
对于一条update操作可分为两个过程

标记为已删除，由Delete buffer管理
真正的删除，由purge buffer管理

知道有货这货就行

4.5.2. 两次写

Inser buffer给InnoDB带来的是性能上的提升，那么两次写就是给innoDB带来数据页可靠性。

如果InnoDB引擎在进行写操作时，一个16KB的页只写了4KB就发生宕机了，这种情况叫部分写失效。

对于部分写，首先想到的是重做日志恢复。重做日志是对页的物理操作，如果这个页出现了损坏，重做是没有意义的，这个页不能用了。在进行重做时，需要这个页的副本来还原该页，再进行重做，这就是两次写，。

两次写由两部分组成

内存中的doublewrite buffer，大小为2MB
物理磁盘上共享表空间中连续的128个页，两个区，大小也是2MB。
对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页复制到内存中的doublewrite buffer中，然后doublewrite buffer再分两次，每次1MB得顺序的写入到共享表空间的物理磁盘上，然后调用fsync函数，同步磁盘，防止缓冲写带来的问题。
对于需要提供数据高可靠性的主服务器，任何时候都要开启doublewrite功能。

4.5.3. 自适应哈希索引

从hashmap的数据结构中可知道hash查找的复杂度是O(1)，B+树往往要3-4次。
InnoDB会监控对表上各索引页的查询，如果观察到建立哈希索引可以带来速度提升，就建立hash索引，这称为自适应hash索引（AHI）

AHI是通过缓冲池的B+树页构造而来，建立速度很快，并且只针对热点页，因此不会对全表建立自适应索引

当对某页连续访问多次，并且模式是一样的。如某个查询条件是一样的，那么就会建立AHI。
还有以下要求

以该模式访问100次
页通过该模式被访问N次，其中N= 页中记录/16

启用AHI，读取和写入速度提高2倍，辅助索引的连接操作性能提高5倍。由于它是自动生成，无需人为干预。

还是查看引擎状态
-------------------------------------
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
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s  这个就是了

注意 AHI只能适用等值查询，范围查询不适用，这货默认开启。

4.5.4. 异步IO(aio)

所谓异步IO，即每进行一次IO操作，用户不必等待IO操作返回，可以立即发送下一个IO操作，当全部IO操作发送完毕，等待所有IO操作的返回。

优势

快，不用傻傻等待每一个IO
可以进行IO合并，当有多个连续页的IO请求，会合并为一个IO，读取页的大小变了而已。

show variables like "innodb_user_native_aio"
默认情况开启,相比关闭,恢复速度提高75%.
在innodb中,read ahead 和脏页刷新都是异步io

4.5.5. 刷新邻接页

当刷新某个脏页时,会检查这个脏页所在区的所有页,如果是脏页,那么就一块刷新.可以将多个IO操作合并为一个io操作.

对于传统机械硬盘建议启用,对于固态硬盘有超高的IOPS(?)建议关闭.

4.6. 启动/关闭/恢复

Mysql实例启动时会对InnoDB处理,通过innodb_fast_shutdown配置

0 ：表示关闭mysql时，innoDB要完成所有的full purge和merge insert buffer，且将所有的脏页刷新回磁盘。这个会需要一些时间，有时会需要1个多小时。
1：默认值，只需要将脏页刷新回磁盘即可。
2：以上的都不做，将日志写入日志文件，不会有任何事物丢失，下次启动会进行恢复操作。

这个深究就是dba的事啦

5. 文件

5.1. 参数文件

这货就是最初的配置文件啦，文章开头有些默认路径。当我们把默认路径的文件删掉，mysql在启动时找不到参数文件就会启动失败，可在错误日志中查看信息。

如何查看？

找到文件路径，打开查询
运行时也能查询，infomation_schema-GLOBAL_VARIABLES,不过只支持条件查询，不支持全表查询。

推荐使用最常用的show variables "值"来查询

select * from GLOBAL_VARIABLES where variable_name like '值'
或者 
show variables like "值"

Linux下读取配置文件顺序

/etc/my.cnf 推荐
/etc/mysql/my.cnf
/usr/local/mysql/etc/my.cnf
~/.my.cnf
越往后优先级越高

windows下可查看mysql --help

Default options are read from the following files in the given order:
C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf D:\Program Files\MySQL\my.ini D:\Program Files\MySQL\my.cnf
按这个顺序来读，越往后优先级越高

5.1.1. 参数类型

可分为两类

动态参数:意味着在mysql运行中进行更改
静态参数：在整个实例生命周期中都能更改，想改得重启。

可通过set来设置动态参数
set @@global.read_buffer_size = 1021312;

select @@global.read_buffer_size

5.2. 日志文件

日志文件可分为好几种

错误日志
二进制日志
慢查询日志
查询日志
这可是我们观察数据库运行情况的重要来源

5.2.1. 错误日志 error.log

这货记录mysql启动、运行、关闭过程

show variables like 'log_error';
+---------------+-------------------------------------------------+
| Variable_name | Value                                           |
+---------------+-------------------------------------------------+
| log_error     | D:\Program Files\MySQL\data\DESKTOP-G73GBHQ.err |
+---------------+-------------------------------------------------+

5.2.2. 慢查询日志

这个需要手动设置阈值，超过(注意:等于不算)这个阈值的sql记录都会保存进日志。

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set
单位秒

mysql> show variables like 'long_query_queries';

默认10秒,一般生产环境超过1s都是很慢了.

另外一个是没走索引的查询

show variables like 'long_queries_not_using_indexes';

注意：mysql5.6.5新增参数log_throttle_queries_not_using_indexes,用来限制每分钟允许记录到show log并且没使用索引的语句的次数，默认为0，表示无限制。如果不做限制，每次不走索引的语句都会记录到show log中，导致文件大小不断增加，建议限制。

Mysql5.1开始可以将慢查询放入一张表中mysql.slow_log

mysql> show create table mysql.slow_log;
+----------+-----------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+----------+-----------------------------------------------------------+
| slow_log | CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' |
+----------+--------------------------------------+
1 row in set

参数log_output指定慢查询输出格式，默认为File，可以设置为TABLE，这样我们可以直接在mysql中查询
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set
mysql> set global log_output='TABLE';
Query OK, 0 rows affected

mysql> select * from mysql.slow_log;
Empty set

5.2.3. 查询日志

查询日志记录了所有对Mysql数据库请求的信息。默认文件名：主机名.log
mysql 5.1开始也能将查询日志放入general_log日志中，使用方法和之前慢查询日志一样。

5.2.4. 二进制日志 binlog 重要

二进制日志记录了对mysql的所有更改操作，因此不包括select和show这种，如更新操作影响行为0，那么也会记录下来。

主要作用

恢复：某些数据的恢复需要二进制日志
复制：通过复制来进行主从同步
审计：通过二进制日志来进行审计，判断是否对数据库进行注入的攻击

位置可通过show variables like 'datadir';查看,即位于初始配置的datadir的文件夹内.
可通过配置参数log-bin [=name]启动二进制日志。如果不指定name，默认文件名是主机名，后缀名是二进制日志的序列号。

5.3. 套接字文件

在UNIX系统下可通过UNIX域套接字方式连接本地Mysql，这种方式就需要套接字文件啦。

5.4. Pid文件

用于记录进程id

6. 表

6.1. 索引组织表

InnoDB中，表都是按主键顺序存放的，如果建表时没有指定主键，那么InnoDB会按一定方式创建主键

判断表中有无非空唯一索引，如果有，则该列（第一个唯一索引列）作为主键。
如果不满足第一条，InnoDB会自动创建一个6字节大小的指针。

这里创建一个简单表做测试

CREATE TABLE `t_index` (
  `c` varchar(255) DEFAULT NULL,
  `b` varchar(255) NOT NULL,
  `a` varchar(255) NOT NULL,
  UNIQUE KEY `index_b` (`b`),
  UNIQUE KEY `index_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

虽然不能直接查找哪个是主键，但是可通过sql分析
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sktjOvQm-1574904515583)(./1481200735168.png)]
可见 b的条件查询是符合索引查询的。

6.2. innoDB逻辑存储结构

所有数据都被逻辑的存在表空间中，表空间由段组成，段又由区组成，区又由页组成。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IpRYTtG8-1574904515584)(./1481200923341.png)]

6.2.1. 表空间

默认情况下InnoDB存储引擎有个共享表空间ibdata1在定义的datadir目录下.如果启用参数innodb_file_per_table,每张表数据可单独放在一个表空间内.

启用拆分表空间参数,每张表的表空间中放的只是数据、索引和插入缓冲bitmap页，其他信息如回滚信息，插入缓冲索引页，系统事务信息等还在共享表空间中。

6.2.2. 段

常见段有数据段、索引段、回滚段。
注意数据即索引，索引即数据，那么数据段就是B+树的叶子节点，索引段就是B+树的非叶子节点
回滚段比较特殊啦

6.2.3. 区

区是由连续页组成的空间，区的固定大小为1MB。
默认情况下，页大小为16KB，那么一个区就由64个页组成，如果引入的压缩页，即一页大小可为8KB或4KB，一个区还是1MB，只是页数多了而已。

对于刚开始创建的表，一个区刚开是是由32个碎片页组成，当这些页使用完了，才开始申请64个连续页，这样可以节省磁盘容量的开销。

6.2.4. 页

页是InnoDB用来管理磁盘的最小单位，默认16KB，可通过innodb_page_size来设置页大小.
最初数据是按照插入的先后顺序排列的，但是随着新数据的插入和旧数据的删除，数据物理顺序会变得混乱，但他们依然保持着逻辑上的先后顺序。

6.2.5. 行

数据是按行存放的,每页存放行数也是也有限制的,最多存放16KB/2-200行记录，即最多7992行记录。

6.3. InnoDB行记录格式

InnoDB提供Compact和Redundant两种格式来存放记录数据,默认是Compact,Redundant是用来兼容之前的版本的。

可使用show table status like'table_name'来查看当前表的行格式

6.3.1. Compact模式

涉及目的就是高效的存储数据,一页中存放的数据越多,性能越高.

6.3.2. 行溢出数据

mysqlvarchar字段最长是65535,但是真正去创建65535长度是不行的,会有一些其他的占用空间.
并且65535是所有列长度的总和.

一页=16kb=16384字节,65535是大于一页的,因此会发生行溢出,数据是存在放特殊的页中 ,Uncompress BLOB

一页至少要放两行数据,因此数据会有个阈值用于判断是在数据页还是特殊页中,这个值大概是8098.

对应Blob和text类型的数据,也不一定放在特殊页中,也要看大小的,保证一页能放两条数据就能进数据页中.

6.3.3. Compressed和Dunamic行记录格式

InnoDB1.0.X引入新的文件格式,新的格式支持两种新的行记录格式Compressed和Dunamic行记录格式.

两种记录对于BLOB的数据采用完全溢出的行存储模式.
Compress对于行记录数据进行zlib算法压缩,性能更优.

6.4. InnoDB数据页结构

一个数据页就是一个堆，该页中记录按堆顺序，查找记录就是二分查找
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WuwX9uQO-1574904515585)(./1497329958294.png)]

只寻到索引页时是无法知道链接的数据页是有多少数据的，必须load到内存才知道有多少数据。

innoDB数据页由以下几段组成

6.4.1. file header

主要有 记录表空间中页的偏移值，前页后页的指针，页类型等

6.4.1.1. Page header

页目录中的slot数，堆中第一个记录的指针,堆中记录数,

6.4.2. 最小虚记录和最大虚记录

英文名就是Infinum和Supremum Record.
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XOAzn4uM-1574904515586)(./1497405986549.png)]

6.4.3. 数据行记录和空闲空间

实际存储的行记录。
空闲空间就是一个链表，指向被删除的空间

6.4.4. 页目录

存放记录对于页的相对位置，这些记录指针记为slot（槽），一个槽可能有多个记录，槽中记录按索引键值顺序存放。查找数据还是得去数据行记录中查找，这边只是一个位置联系点
查找记录是通过二叉查找。

不管怎样，B+数索引页并不能找到具体的一条记录，必须先找到相关数据页，然后把数据页load内存中，再通过页目录进行二叉查找

6.4.5. 文件结尾信息

为了检测页的完整性设置的一个标志位，比较小。

6.5. 约束

6.5.1. 数据完整性

关系型数据库一个特点就是完整性。

在一般的文件系统中,需要程序端来进行控制,关系型数据库都提供了约束机制来保证完整性.

在InnoDB中,可通过以下几种途径来保证完整性

合适的数据类型来保证一个数值满足条件
外键约束
编写触发器
使用默认值来约束完整性
非空

创建约束可以在表创建时创建或者alter table来添加

约束: 逻辑上的概念,用于保证数据完整性
索引: 有逻辑上的概念,更多的是代表物理存储方式

对于非严格模式下的数据库我们插入违反约束的数据不会报错，会出现警告，可通过设置sql_mode使用严格模式，默认值为STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,严格模式

select @@sql_mode

6.5.2. ENUM和set约束

可通过枚举值来限制数据值，

ALTER TABLE `b`
ADD COLUMN `sex`  enum('女','男') NULL AFTER `IdB`;

[SQL]INSERT into b VALUES(1,"男as");
[Err] 1265 - Data truncated for column 'sex' at row 1
当输入枚举类型外的值就报错

枚举类型能够解决大多部分的约束，对于连续范围的约束或更复杂的约束还是无能为力的。

6.5.3. 触发器与约束

对于一些字段的更新、插入、删除可通过编写触发器来自动执行一段SQL命令或者存储过程来保证数据完整性。

一张表最多定义6个触发器，分表为插入/更新/删除的before/after ,before和after表示触发器发生的时间,作用在数据行变化之前或之后

目前mysql只支持for each row触发,即按每行触发

6.5.4. 外键约束

MySAM引擎并不支持外键,对于外键的定义只是起到一个注释作用.

InnodB支持完整的外键约束.

被关联的表称为父表,声明外键的表为字表,定义时的on delete和on update表示在对父表进行操作时,字表也可以自动操作,可定义的操作有

cascade 级联,即父表删除,子表也删除,父表更新，字表也更新
set null：父表更新或删除，子表置空
not action/RESTRICT:在mysql这俩相同，抛出错误，不允许操作，默认RESTRICT

在其他数据库中，有外键约束叫延时检查，sql执行完再进行检查，mysql都是执行时检查。

在其他数据库中建立外键还得给这列建立索引，mysql在建立外键时会自动对该列添加索引，可以避免无索引导致的死锁问题。

由于外键会在导入时及时检查数据完整性，因此会耗费大量时间

6.6. 视图

在mysql中，视图是虚表，并无实际的物理存储

6.6.1. 视图的作用

无需关心基表的结构，只需按视图的定义来取数据或更新数据。

oracle支持物化视图，物化视图可以用于预先计算并保存多表的连接或聚集等耗时的sql操作，可避免这些耗时操作，快速得到结果。

create view v_num AS
select * from test_number where id<20;

虽然在navicat界面上看不见视图，但是我们可以通过show tables来搜索到视图

insert into v_num VALUES(100,100);
直接插入到test_number 中，但是视图不显示

注意，当我们向视图中插入值时，是可以的，会影响到基表，但是视图依然只显示符合规范的值（插入100，超过20所以不显示）

上面是单表的视图，进行插入会影响到基表，如果我们用多表查询进行插入或删除呢？

create view v_name as select a.`name`,b.sex from a join b on a.idA=b.IdB;

[SQL]insert into v_name VALUES("拖欠",'男');
[Err] 1394 - Can not insert into join view 'work.v_name' without fields list

[SQL]delete from v_name  where name='啊';
[Err] 1395 - Can not delete from join view 'work.v_name'

结果报错，
so，视图在进行新增时，涉及多表视图时，是无法插入的，但是不妨碍删除，相当于把条件为视图那些值的记录删掉。对于单表视图新增时，不存在视图中的字段设为null，如果那个字段不允许为null，那么就不让添加，如果删除，和多表一样相当于条件删除

6.6.2. 物化视图

oracle支持物化视图。即将视图数据存储在非易失的设备上。而不是一张虚表

oracle支持的物化视图方式包括两种

build immediate，默认，创建时就生成数据
build deferred，创建时不生成数据，根据需要生成数据

物化视图刷新是指基本发生DML操作后，物化视图采用何种方式进行同步
，刷新有两种，

ON DEMAND用户需要时进行刷新
ON COMMIT 基表的DML进行提交时刷新

刷新也分几种，增量刷新，整个完全刷新，自动策略（自动选择前两种）和不刷新

Mysql不支持物化视图，总是虚拟的

6.7. 分区表

常见引擎MySAM，NDB和InnoDB都支持分区功能。也有些引擎不支持

分区的过程是将一个表或索引分解为多个更小,更可管理的部分.在物理层面上一个表或索引可能由数十个物理分区组成,每个分区都是独立对象,可以独自处理,也可以作为一个更大对象的一部分进行处理.

mysql支持的分区类型为水平分区,并不支持垂直分区.

mysql不支持全局分区,只支持局部分区,:一个分区中既存放了数据又存放了索引. 全局分区是指数据存放在各个分区中,但是所有数据的索引都在一个对象中.

查看分区命令：

show VARIABLES like '%partition%'

show plugins

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dVS3itDU-1574904515587)(./1482843898874.png)]

Mysql支持以下几种类型的分区

RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区。mysql5.5开始支持
LIST分区：同range分区规则，只是面向离散的值，mysql5.5支持
HASH分区：根据用户定义的表达式的返回值进行分区，返回值不能为负值。
KEY分区：根据mysql提供的hash函数来进行分区。

对于任意类型分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分，如ab列组成唯一索引，那么a列或b列都可以作为分区列

如果表中无主键和唯一索引，那么可以指定任意一列为分区列

6.7.1. RANGE分区

我们知道，对于一张表，其物理数据在date文件夹中的表现形式是一个ibd文件和一个frm文件
我们来创建一个分区表

create table t(
    id int 
) ENGINE=InnoDB
PARTITION by range(id)(
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p2 VALUES LESS THAN (30)
);

然后再看文件存储方式
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-COjdCAL7-1574904515588)(./1482845414012.png)]
如图一个ibd变成了三个ibd文件
对于id范围不同的数据，存储在不同的ibd文件中。

也可以添加分区

alter table t 
add PARTITION (
PARTITION p3 VALUES LESS THAN MAXVALUE
);

通常分区字段是按日期，数据可按日期分区，不同的年段存储方式不同。
如果我们需要删除某一段时间的数据，不需要delete from t where date>='2016-1-1' and date<='2017-1-1'.
只需要删除指定分区即可

alter talbe t drop partition p2017即可。

创建分区的另一个好处是加快某些查询操作，对于同一条查询计划
select * from t where date>='2016-1-1' and date<='2017-1-1'
不建分区最优的办法就是查询索引，如果建立分区，直接去对应的分区中查询，当然最好不要写出跨很多分区的sql。

注意对时间建立分区需要用函数TO_DAYS('2016-6-6'),这样优化器才能工作。

6.7.2. List分区

分区值是离散的

create table t_list(
    id int 
)ENGINE=INNODB
PARTITION by list(id)(
    PARTITION p0 values in (1,3,5,7,9),
    PARTITION p1 values in (2,4,6,8,10)
);

同理物理存储也是分为俩。因为是离散的，只能定义值
插入1-7的值,可以查看不同分区的列数

mysql> select table_name,partition_name,table_rows from information_schema.`PARTITIONS`
where TABLE_NAME='t_list' and TABLE_SCHEMA=DATABASE();
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| t_list     | p0             |          4 |
| t_list     | p1             |          3 |
+------------+----------------+------------+
2 rows in set

如果在插入遇到不在分区范围内的数据值时，插不进去的

[SQL]insert into t_list VALUES(122);
[Err] 1526 - Table has no partition for value 122

如果批量插入，innodb和mysam处理方式不同。

mysam引擎会将之前的数据都插入，不在范围的那条以及之后的不再插入
innoDB会视为一个事物，都不插入。

[SQL]insert into t_list VALUES(7),(9),(122);
[Err] 1526 - Table has no partition for value 122

6.7.3. hash分区

hash目的就是均匀嘛，将数据均匀的分布到各个分区中，保证每个分区中数据大致一样。
使用hash分区时要指定一个返回整数的表达式。后面添加一个partition num，num是一个非负整数,表示要被分割分区的数量,如果没设就默认为1.

create table t_hash(
    a int ,
    b datetime
)engine = innodb
partition by hash(year(b))
partitions 4;

这里我们用b字段的年份做hash 分区
测试插入


mysql> select mod(year('2017-1-1'),4);

select table_name,partition_name,table_rows from information_schema.`PARTITIONS`
where TABLE_NAME='t_hash' and TABLE_SCHEMA=DATABASE();
+-------------------------+
| mod(year('2017-1-1'),4) |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set

+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| t_hash     | p0             |          0 |
| t_hash     | p1             |          1 |
| t_hash     | p2             |          0 |
| t_hash     | p3             |          0 |
+------------+----------------+------------+

看来学的散列算法还是没错的!

mysql支持一种更发杂的算法来确定插入分区表中的位置,称为linear hash,创建的语法和hash一样,把hash改成linear hash即可.

linear hash分区优点:
增删合并拆分分区将变得更加快捷,对于大量数据的表十分方便
缺点:与hash相比各区间分布不太均衡

6.7.4. KEY分区

key分区和hash分区相似,key分区使用数据库提供的函数进行分区,

create table t_hash(
    a int ,
    b datetime
)engine = innodb
partition by key(b)
partitions 4;

6.7.5. COLUMNS分区

之前四种对于分区列数据必须是整形，如果不是整形也要通过函数计算出整形。
mysql5.5支持columns分区，它是range和list分区的进化，可使用非整形数据进行分区。
它支持以下数据类型

所有整形，float和decimal不支持
日期类型如date和datetime，其他不支持
字符串，blob和text不支持

之前我们有个用year来计算分区hash值，那个表更适合用columns分区，直接支持日期。

6.7.6. 子分区

子分区是在分区的基础上再进行分区，有时也称复合分区。mysql支持在range和list上进行hash或key子分区。

create table t_s(
    id int ,
b    date
)ENGINE=INNODB
PARTITION by range(year(b) )
    subpartition by hash( TO_DAYS(b))
    subpartitions 2 (
    PARTITION p0 VALUES less than (1990),
    PARTITION p1 VALUES less than (2000),
    PARTITION p2 VALUES less than MAXVALUE
);

查文件,变成了6个ibd文件
分析:先根据b字段年份做range分区,然后进行hash分区.

6.7.7. 分区中的null值

mysql将null值视小于任何一个非null值,这也是为什么可以用>’'来寻找非null值
不同分区做法按不同

range：null放入最左边分区
list：必须显示指定哪个分区包含null值，否则不让插入
hash和key：任何函数的表达式都会将null值计算返回0

6.7.8. 分区的性能

数据库的应用分为两类

OLTP（在线事务处理）：如博客、电子商务、网游
OLAP（在线分析处理）：数据仓库和数据集市。

实际环境中，一个项目可能都拥有两种类型。

对于OLTP，分区能很好提高查询性能，因为应用中数据需要频繁扫描一张很大的表，如获取这一年的数据，按时间戳分区能优化搜索对应的分区。

对于OLAP应用，取数据很可能不会取大于表10%的数据，那么更需要索引的精准查询，如果加分区又变成范围查询了，效率往往会下降。

举个例子，一个1000W数据的表，1000W数据的B+树高假设3，主键搜索那么要对磁盘io三次才找到数据，如果分区，拆成10个分区，主键查询需要遍历10个分区，每个分区100W数据，树高为2，根据主键查找到对应的分区只需要2次io，看似快了。
如果搜索非分区字段，按索引走，每个分区2次，10个分区，20次io，完蛋

因此使用分区要十分小心啊！！

6.7.9. 在表和分区间交换数据

mysql5.6支持alter table ... exchange partition语法，该语句允许分区或子分区中的数据与另一个非分区表中的数据进行交换。
如果非分区表中数据为空，那么相当于将分区中数据移动到非分区表中。
如果分区表为空，相当于将外部表中数据移动到分区表中。

使用条件

要交换的表和分区表必须表结构相同，其中一个表不能分区
非分区表中的数据必须在交换的分区定义内
被交换的表不能有外键或者其他表对该表的外键引用
用户得拥有drop等全部权限
注意：
不会触发两张表上的触发器
自增长列会重置

7. 索引

InnoDB支持常见索引

B+树
全文索引
哈希索引

InnoDB会根据表的使用情况自动为表生成hash索引，不能人为的干预是否在一张表中生成hash索引

B+树找到数据所在的页，然后把页读入内存，在内存中进行查找。

7.1. 聚集索引

聚集索引和非聚集索引区别不多说，我另外一篇总结有写，这里说一些细节

聚集索引的存储不是物理上连续的，而是逻辑上连续的，页通过双向链表连接，页按主键顺序排序

7.1.1. 辅助索引即非聚集索引

这货就是存储指向主键索引的主键，然后通过主键索引来找到记录。

例：一颗B+树高度为3，通过辅助索引找到目标的主键，这要花3次IO，然后通过主键索引又是3次，最终花6次IO找到目标页。

7.1.2. B+索引的分裂

了解过B+树的分裂,咱们知道是从页中点数据进行左右分裂的,但是这在索引中有问题的

由于主键是按顺序插入的，左边分裂后有空位，但是顺序后一位是在右边页了，如果插入新数据就不会插入到左边页空位了。空间浪费，多几条又这样分裂循环，完蛋

B+索引的分裂点是按情况决定的，innodDB在page Header中存放一些信息用于决定向左分裂还是向右分裂。

若插入是随机的（即主键uuid），则按B+树分裂算法执行即可。
若往一页中插入五条连续记录，并且定位到的记录之后还有三条在同一页中（所谓定位记录就是要插入的记录插入到定位记录之后），那么分裂点就是定位记录的后第三条，感觉这就是向后分裂。
若插入点之后没有记录，加上新记录这一页超载了，那么新纪录就是下一页的第一条记录

7.2. B+树索引管理的那些事

7.2.1. 索引管理

查看索引

show index from table

有几个返回值说明：
seq_in_index:索引列的位置
Collaton:列以说明方式存储在索引中。A或NUll，A表示排序的，如果是Heap引擎使用hash索引就显示null
cardinality:索引值的多样性预估值

7.2.2. 快速创建索引

mysql5.5前创建索引mysql的操作原理

创建一张临时表，使用alter table来创建表结构
原表数据导入到临时表
删除原表
临时表改为原表名

重点这会非常慢啊！！如果线上数据，那么会导致长时间数据库不可用，临时表的位置是由tmpdir决定，如果路径空间不够大，索引创建失败

innoDB在1.0.x版本开始支持快速索引创建

对于辅助索引的创建，InnoDB存储引擎会在表上加一个S锁，这样不用重建表，快很多

删除辅助索引：更新内部视图，将辅助索引空间标记可用，同时删除mysql数据库内部视图上对该表的索引定义即可

S锁的用处：在创建期间只能读，如果要写，那么写服务不可用

对于主键的创建和删除同样性需要重建表

7.2.3. 在线架构改变（online schema change）

这是FaceBook实现的一种在线执行DDL的方式，广泛应用于FaceBook的Mysql数据库。

在线指事物的创建过程中，可以有读写事物对表进行操作，提高了mysql在DDL操作时的并发性。

这货是PHP脚本来实现的，并不是通过修改InnoDB存储引擎源码的方式，有一定局限性，并且会导致主从不一致的情况

7.2.4. Online DDL（数据少时推荐）

快速创建索引可以避免innodb创建临时表提高效率。但是会阻塞DMl操作啊。
5.6开始支持在线数据定义操作，在创建辅助索引的同事，还允许其他诸如Insert，update，delete等操作，大大提高了MySql在生产环境中的可用性

online DDl支持下面几类操作

辅助索引的创建和删除
改变自增长值
添加或删除外键约束
列的重命名

mysql5.6后支持新的alter table语法，用户可以选择创建索引的方式

alter table table_name  
add index index_name [index_type可选] (index_col_name,..) ..
,ALGORITHM [=] {DEFAULT|INPLACE|COPY}
,LOCK [=] {default|NONE|SHARED|EXCLUSIVE}

mysql> alter table t_index 
add index index_b(b),
ALGORITHM=INPLACE,
lock=none;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

ALGORITHM 指定创建或删除索引的方法

COPY：老版本方式，即创建临时表
INPLACE：表示创建删除索引不需要创建临时表
DEFAULT：表示根据参数old_alter_table来判断是通过INPLACE还是copy算法,默认OFF,表示采用INPLACE方式.

查看old_alter_table方法
mysql> show variables like '%old_alter_table%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| old_alter_table | OFF   |
+-----------------+-------+
1 row in set

LOCK表示索引创建、删除时对表加锁的情况

NONE：不加锁，事务自由读写，不会阻塞，并发量最大
SHARE：加S锁，只能读，如果引擎不支持SHARE就会返回错误信息
EXCLUSIVE：加一个X锁，读写都不能进行，会阻塞所有线程
DEFAULT：自动，判断顺序：none-share-exclusive

OnlineDDl 实现原理：

在创建或删除操作的同时，将INSERT、update、delete这类DML操作写入到一个缓存中，等待索引创建完毕就将重做应用到表上。
注意了：这个缓存大小由innodb_online_alter_log_max_size控制，默认大小128M，如果超过大小，就会报错了
还有一点，由于在创建索引过程中，SQL优化器不会选择正在创建的索引

7.3. Cardinality

Cardinality表示索引中不重复记录数量的预估值。通常我们对于一个列使用索引得保证这个索引值的多样性要在整体数据的30%以下，大于30%就没意义建索引了。
在生产环境中，索引的更新是非常频繁的，因此数据库对cardinality的统计都是通过采样的方法来完成的.

一般情况下,只有插入和更新时才会更新Cardinality的统计.具体更新策略为

表中1/16的数据已发生变化：这个就是整体范围性变化超过十六分之一
stat_modified_counter>20亿：如果对某一行数据频繁更新，实际上数据库的数据并没有增加，就用这个计数器来表示发生变化的次数

Cardinality这个值的预估值原理

取得B+树叶子节点数量 total
随机抽取八个叶子节点，统计每个叶子页的不同记录的个数p1…p8
Cardinality = (p1+p2+…+p8)*total/8

由于是随机获取的，对于一张大表来说，这个货可能每次查询的值不一样show index from tableName

可通过配置参数innodb_stats_sample_pages来设置随机抽取页的数量。
mysql的索引是如何判断null值的呢？

innodb_stats_method用来判断如何对待索引中null值记录。默认nulls_equasl，表示null值都是相等的记录。例：一张表记录null,null,1,2,2,3,3 计算出来的Cardinality就是4
可配置值有:

nulls_unequlas：那么该页的样本值为5
nulls_ignored: 样本值为3

当我们执行analyze table、show table status、show index 以及访问information_schema下的tables和statistics会导致innodb去重新计算Cardinality的值,因此执行这些操作会慢

计算是否适合建索引?
DISTINCT 字段/count DISTINCT 值越小即Cardinality越小，不适合建索引

7.4. B+树的索引使用

老规矩,按具体情况使用,如果对于大量数据中检索某一条记录,索引很好使

7.4.1. 联合索引

这货的结构和辅助索引没区别,就是多个列的关键字组合而成的一个索引key.

注意：使用联合索引时如（a,b）如何使用后面的那个值如b来进行查找是用不上索引的

7.4.2. 覆盖索引

InnoDB支持覆盖索引,从辅助索引中就可以直接得到查询记录,而不需要查询聚集索引中的记录.可大大减少IO操作
注意，如果查询了不在索引范围内的字段，就无法使用覆盖索引
使用覆盖索引的好处就是辅助索引不包含整行记录的信息,所以大小远远小于聚集索引

CREATE TABLE `buy_log` (
  `userId` int(11) unsigned NOT NULL,
  `buy_date` date DEFAULT NULL,
  KEY `index_userId` (`userId`,`buy_date`),
  KEY `index_u` (`userId`)
) ENGINE=InnoDB

如果我们对buy_date进行范围查询,由于联合主键使用的是最左匹配,按道理来说是不能用联合主键的

mysql> desc SELECT count(*) FROM `buy_log` where buy_date>'2011-01-01' and buy_date<'2016-01-01';
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | buy_log | NULL       | index | NULL          | index_userId | 8       | NULL |    6 |    16.67 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set

可看见在分析中 Extra中为 using index,表示使用了覆盖索引,优化器自动选择利用了覆盖索引

7.4.3. 优化器不使用索引的情况

当从查询计划中进行分析时,发现并没有选择索引去查找数据,而是通过扫描聚集索引即主键来得到数据,通常发生在范围查找和JOIN连接等情况下

CREATE TABLE `t_orderdetail` (
  `orderId` int(11) NOT NULL,
  `productId` int(11) DEFAULT NULL,
  PRIMARY KEY (`orderId`),
  KEY `index_all` (`orderId`,`productId`),
  KEY `index_order` (`orderId`)
) ENGINE=InnoDB

当这种表数据很少时，做范围查询是这样的

mysql> desc SELECT * FROM `t_orderdetail` where orderId<100000 and orderId>50000;
+----+-------------+---------------+------------+-------+-------------------------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table         | partitions | type  | possible_keys                 | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------------+------------+-------+-------------------------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_orderdetail | NULL       | index | PRIMARY,index_all,index_order | index_all | 9       | NULL |    1 |      100 | Using where; Using index |
+----+-------------+---------------+------------+-------+-------------------------------+-----------+---------+------+------+----------+--------------------------+
1 row in set

可见，他使用了辅助索引。
当表数据很大时，如范围查找数据大于整表20%就得使用聚集索引来查找数据了，因为使用辅助索引还得从聚集索引中读值。当相当于磁盘随机读取。

当然如果十分有自信，用ssd硬盘可使用关键字force index强制使用某个索引

7.4.4. 索引提示 index hint

当可使用index非常多时，优化器选择执行计划时间的开销就会大于SQL本身。
使用 index Hint来强制使优化器不进行各个执行路径的成本分析,直接使用指定索引来完成查询.
这货也只是给优化器推荐索引,实际上优化器还会根据自己的判断进行选择,如果想优化器完全听命与你,就用上面的force index吧

7.4.5. Multi-range Read优化

mysql5.6开始支持Multi Range read优化，目的是减少磁盘随机访问，将随机访问转化为较为顺序的数据访问。适用于range，ref，eq-ref类型的查询
MRR好处

使数据访问变得较为顺序。在查询辅助索引时，首先根据查询结果按照主键排序，按主键排序的结果进行书签查找
减少缓冲池中页被替换的次数
批量处理对键值的查询操作

对于InnoDB和Mysam引擎的范围查询和join查询操作，mrr工作方式如下

将查询得到的辅助索引键值存放于一个缓存中，此时缓存中的数据是根据辅助索引键值排序的。
将缓存中的键值根据rowId排序
根据rowId的排序顺序来访问实际数据文件

为什么要加入缓存？
如果缓存不够大或者不用缓存，频繁的离散读取会使缓存中的页被替换出缓冲池，又然后不断读入缓冲池。如果按主键排序，可将重复值降到最低

如果使用了mrr，那么在分析sql时会看见using mrr选项。
简单来说mysql支持范围查询时自动优化辅助索引加快查询速度

可通过optimizer_switch中标记控制是否使用mrr。
可通过如下语句配置mrr

set @@optimizer_switch='mrr=on,mrr_cost_based=off'

可通过配置全局参数read_rnd_buffer_size来控制缓冲区大小,当大于该值时,则执行器对缓存数据根据rowId进行排序,通过rowId来取数据,默认256K.

7.4.6. index condition pushdown优化

这个也是5.6新增的优化方式.
当进行索引查询时,首先根据索引来查找数据,然后再根据where条件来过滤记录
支持index condition pushdown 后,mysql在取出索引的同时判断可以进行where条件过滤,在某些条件下,大大减少对index的索取,从而提高数据库性能

当数据库使用了icp,可以在执行计划的extra上看到using index conditon提示.
当前支持mysam和innodb引擎.

一个例子
code,lastname,address三键索引
一个查询条件
where code=1
and lastname like %sad%
and address like'%a%'

知道索引的最左匹配原则话，按道理只能支持code的索引，后面的两个是用不到的。
若支持index condition pushdown优化，则会对索引进行where条件过滤，再去获取记录。

如何知道使用了ICP呢，看执行计划Extra有Using Index Condition就表示有优化。

可以在使用icp的同时也使用mrr，效率将大大提高，可达400%

7.5. 哈希算法

先看之前说的

哈希算法原理就是散列表，不记得的去看hashmap源码。

他也会出现hash碰撞，同一个桶中也是一个链表。定为也是取模运算。

7.5.1. InnoDB中的hash算法

对于缓冲池的页的hash表来说，缓冲池的page页都有一个chain指针，指向相同hash函数的页。一般一个桶的大小定为近似缓冲池中页的最大数量的两倍的质数。
页是如何变为hash值？
innodb表空间都有一个space_id,用户查找的就是表空间的某一段16KB的页.就是通过偏移量和space_id来计算hash值.

自适应hash索引就是通过等值查询来进行优化的,由于是innoDB自主控制的,只能看看了,默认开启的,通过innodb_adaptice_hash_index来开关此特性,.

7.6. 全文检索

由于索引的特性,只支持最左匹配的索引,但是这常常不符合我们的需求.[##自适应哈希索引]
工作中常常用的是%key%来进行查找,因此看起来是无法使用索引的.

从innoDB1.2.x(mysql5.6)版开始,innoDB支持全文索引.

看到一个非常关键的词,不支持中文,于是我就不看这货了.

了解下原理吧
全文检索使用倒排索引.
先拆词(中文拆不了 GG)
将每个词和在文中的位置定义为一对键值对,放入一个辅助表中.模糊查询通过这个辅助表来查询.一张表也只能定义一个全文检索.

8. 锁

锁机制用于管理对共享资源的并发访问。InnoDB是行级锁，不仅仅是对表加锁，也会对数据库其他地方(缓冲池LRU列表)使用锁，从而保证对多种不同资源提供并发访问。

MySAM是表级锁，不支持事务的。

8.1. lock和latch

latch 为闩锁，锁的对象是线程。分为互斥锁mutex和读写锁rwlock。用来保证并发线程的操作临界资源的正确性，并且无死锁检测机制。
查看方法

 show engine innodb mutex;
 +--------+-----------------------------+---------+
| Type   | Name                        | Status  |
+--------+-----------------------------+---------+
| InnoDB | rwlock: log0log.cc:844      | waits=2 |
| InnoDB | sum rwlock: buf0buf.cc:1457 | waits=6 |
+--------+-----------------------------+---------+
type总是innodb，name是latch的信息和源码的位置。status复杂。

重点学习的是lock。

Lock锁的对象是事务，用来锁定数据库中的对象。lock在事务commit或rollback后释放

8.2. InnoDB中的锁

有两种行级锁

S Lock 共享锁（读锁），允许事务读一行的数据
X Lock 排他锁（写锁），允许事务删除或更新一行数据。

即S锁能兼容S锁，但X锁不兼容任何锁。有了一个X锁，再来一个写事务也得等待锁释放。

InnoDB支持多粒度的锁定，允许事务在行级上和表级上都有锁，InnoDB有一个叫意向锁：将锁定的对象分层，意向锁表示希望事务在更细粒度上加锁。
分为两种意向锁：

IS Lock意向共享锁：事务想要获得一张表中某几行的数据
IX Lock意向排他锁：事务想要获得一张表中某几行的排他锁

兼容情况	IS	IX
IS	兼容	兼容
IX	兼容	兼容
S	兼容	不兼容
X	不兼容	不兼容

概括性的说：意向锁之间都互相兼容，IS锁只兼容S锁，IX锁对于S和X都不兼容。

可通过show engine innodb status来查看锁的情况
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v6vZcGxk-1574904515589)(./1498398007497.png)]

这个锁说明 SQL在等待2秒了。后面表示锁住的资源，locks rec but not gap代表锁住的是一个索引，不是一个范围。

8.2.1. 查看锁情况

innodb 的information_schema中有三张表可以看见锁的相关情况。
INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS

INNODB_TRX表示存储当前事务情况，并不能查看锁的一些情况。

字段	说明
trx_id	事务id
trx_state	当前事务状态，如果被锁等待就是`LOCK WAIT`
trx_started	事务开始时间
trx_requested_lock_id	当前等待事务的锁id。如果这个事务被阻塞，那么就会显示那个锁住资源事务的id
trx_wait_started	事务等待时间
trx_weight	事务权重，反映一个事务修改和锁住的行数。在 innoDB中，发生死锁时，InnoDB会选择该值最小的进行回滚
trx_mysql_thread_id	mysql 线程id
trx_query	事务SQL语句

如果要看锁的情况，需访问INNODB_LOCKS

字段	说明
lock_id	锁id
lock_trx_id	锁对应的事务id
lock_mode	锁的模式，S锁还是X锁
lock_type	锁的类型，表锁还是行锁
lock_table	锁住的表
lock_index	锁住索引
lock_space	锁对象的space id
lock_page	锁住的页数量。若表锁，该值为NULL
lock_rec	锁住的行数量。若表锁，该值为NULL
lock_data	事务锁定记录的主键值。若表锁，该值为NULL

INNODB_LOCK_WAITS结构

字段	说明
requesting_trx_id	申请锁资源的事务id
requested_lock_id	申请的锁id
blocking_trx_id	阻塞的事务id
blocking_lock_id	阻塞的锁id

例子

事务A：
mysql> begin;
Query OK, 0 rows affected

mysql> update t_three_index set age=999 where name<4;
Query OK, 3 rows affected

事务B：
mysql> begin ;
Query OK, 0 rows affected

mysql> select * from t_three_index where name=1;
+------+--------+------+---------+------+
| name | adress | role | sale    | age  |
+------+--------+------+---------+------+
|    1 | 湖南   | 销售 | 售前 啊 | NULL |
+------+--------+------+---------+------+
1 row in set

mysql> update t_three_index set sale='hgh' where name=2;
-- 锁住

为什么X锁没有阻塞S锁？这里是一致性非锁定读，读快照数据。
trx表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kLS4ibzb-1574904515590)(./1498401477479.png)]
LOCKs表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QyOSooUy-1574904515591)(./1498401488560.png)]
LOCK WAIT表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DJbZs9NV-1574904515591)(./1498401504063.png)]

注意：这里设置的事务隔离级别是REPEATABLE READ，默认的

因为如果事务一直不提交，那么意味着事务会一直等待，因此innodb设置了超时时间，等待事务会出现提示而取消等待。

注意：INNODB_LOCKS.lock_data不一定是个准确值，如果是范围，这个现实第一条记录。

8.3. 一致性非锁定读

INNODB通过多版本控制来读取当前执行时间数据库中行的数据。
如果读取的行正在进行update或DELETE操作，这时读取不会等待锁的释放。直接去读取快照数据。 即不需要等待X锁的释放。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-d7KmpbxC-1574904515593)(./1498401955299.png)]

实现是通过undo段来完成，undo段是用来在事务中回滚数据。因此没有额外开销。读取快照不需要上锁，没有事务会对历史的数据进行操作。

一致性非锁定读大大提高了数据库的并发性。在innoDB默认的设置下，这是默认方式。不同的事务级别，不一定启用一致性非锁定读。

在REPEATABLE READ重复读，READ COMMITTED提交读,使用一致性非锁定读。但也有不同。

REPEATABLE READ：非一致性读总是读取事务开始时的行数据版本
READ COMMITTED：读取锁定行的最新的undo段数据

8.4. 一致性锁定读

某些情况下，用户需要显示的对数据库进行读取操作加锁以保证数据逻辑的一致性。此时必须得对读加锁。
innodb支持两种一致性的锁定读操作
- select .. for UPDATE：对读取行记录加一个X锁。
- select ... LOCK IN SHARE MODE：对行记录加一个S锁，其他读事务不会阻塞，但是如果是X锁。则会阻塞。
这两个只在事务中应用。

8.5. 自增长与锁

在innodb引擎的内存结构中，每个自增长的表都有一个自增长计数器

select MAX(anto_inc_col) from t for update

为了提高插入性能，锁不是在一个事务完成后才释放，而是完成对自增长值插入的SQL语句后立即释放。
多个事务之间的插入会阻塞，还是有性能问题。
从innodb5.1.22后，提供了一种轻量级的互斥量的自增长实现机制，大大提高了插入性能。
innodb提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，该参数默认值为1.
自增长插入还分类的。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZKTCkRBL-1574904515593)(./1498404900823.png)]

不同值对自增长锁影响
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UaUR6JJp-1574904515594)(./1498404941317.png)]

8.6. 外键和锁

对于一个外键列，innodb会自动建立索引，像oracle数据库就不会了。这样会避免表锁。
对于外键值的插入和更新，需要先查询父表中的数据。对于附表查询使用的是一致性锁定读。如果非锁定读会造成数据不一致问题。即使用的是select… lock in share mode

8.7. 锁的算法

8.7.1. 行锁的三种算法

Record Lock：单个行记录上的锁
Gap Lock：间隙锁,锁定一个范围，但不含记录本身
Next-Key Lock：单行锁+间隙锁。锁定一个范围，并且锁定记录本身。

Record Lock会去锁定索引记录，如果建表时没有建立索引，那么就会用隐式的主键进行锁定。

InnoDB的默认事务级别是REPEATABLE READ,对于行查询都是使用Next key Lock算法,保证不会出现幻象读（同一事务两次读结果不一样）。

如果一个索引有3,5,9三个值。可能被next key lock分成四个区级 (-∞，1],(1,5],(5,8],(8,+∞)，要是中间插入新纪录，则按新纪录继续分裂。

当查询的索引有唯一属性时，即主键索引和唯一索引，那么 Next-Key Lock会降级成Record Lock。仅锁定记录本身而不是范围。

CREATE TABLE `s` (
  `a` int(11) NOT NULL,
  `b` varchar(255) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`a`),  KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `s` (`a`, `b`, `c`) VALUES ('1', '1', '1');
INSERT INTO `s` (`a`, `b`, `c`) VALUES ('5', '5', '5');
INSERT INTO `s` (`a`, `b`, `c`) VALUES ('8', '8', '8');
INSERT INTO `s` (`a`, `b`, `c`) VALUES ('13', '13', '13');

demo1:

session1: select * from s where a=5 for update

session2:
  inser into s values(3,3,3)  成功
   update s set b=32 where a=5 阻塞

可见,针对主键的话只有同个主键才会竞争.但是当对不存在的主键加锁时就变成间隙锁了.

session1: 
  select * from s where a=3 for update
  此时锁住的范围是1-5
session2 :
   insert into s values(6,4,4); 成功
   insert into s values(4,4,4); 阻塞

对b列辅助索引操作:

session1 : 
    select * from s where b=5 for update; 
    此时对于B列索引使用next key lock算法。锁住1-5,5-8的范围，也锁住5.  对于主键就是锁住5
session2:
    select * from s where a =5 lock in share mode; S锁阻塞
    insert into s values(4,4,4); b列在1-5范围，阻塞
    insert into s values(6,6,4); b列在5-8范围，阻塞

可见，间隙锁的作用是防止事务将记录插到同一个范围内，从而导致幻象读的产生。

可以手动关闭间隙锁

事务隔离级别设置为：Read committed
参数innodb_unsafe_for_binlog设置为1
这个配置会破坏事务隔离性，会导致主从数据不一致，性能上提交读也没有可重复读高。

对于insert操作，会检查插入记录的下一条记录是否被锁定，即插入的记录在间隙锁的范围内，那么就会被阻塞。
注意：next key lock降级为 record lock 只存在于查询所有的唯一索引列。如果唯一索引是复合索引，那么实际上是范围查询，会使用next key lock算法。

8.7.2. 如何解决幻象读

幻象读：指同一事务下，连续执行两次同样的sql查询会获得不同的结果。

默认的事务隔离机制可重复读 REPEATABLE READ下，使用next key Lock来避免幻象读。即事务都是使用一致性非锁定读，读取的数据都是事务开始时的undo端数据。

select @@tx_isolation; 查看事务隔离级别

如何重现幻象读？
首先读肯定不能被阻塞，使用一致性非锁定读，那么还记得提交读吗，他读取的是最新的undo段数据。

set session tx_isolation='READ-COMMITTED'; //设置session级别事务隔离
seesion A:
     select * from s where a<5； 只有1

session B；
    insert 444
seesion A:
     select * from s where a<5； 1和4

为什么使用next key lock能解决幻象读？
当我们针对某一行记录查询时，加锁一般是一行，这种情况单行锁互斥其他锁保证事务不干扰。
如果查询某一个范围，那么会对该范围内的最大两条相邻记录之间的范围加锁，保证其他事务加x锁无法加进来新纪录。自然就解决了幻象读了。

8.8. 锁问题

8.8.1. 脏读-`READ-UNCOMMITTED`

指的是不同事务下，当前事务读取到其他事务未提交的事务。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-a4Qjgo6N-1574904515594)(./1498489500553.png)]
未提交读READ-UNCOMMITTED会出现这货，事务能读取到其他事务未提交的数据。会导致同一事务两次读不一致的结果。

InnoDB默认级别是repeatable read隔离，不会出现。像SQL server和oracle都是提交读，也不会出现这种问题。

好处
这货并不是一无是处，对于slave节点可以设置提交读，从节点查询不需要特别精确的数字。

8.8.2. 不可重复读-`READ COMMITTED`

不可重复读指同一事务两次读取结果不一致，和脏读不一样的是：脏读读取到的是其他事务未提交的事务，不可重复读读取的是其他事务已提交的数据。
是READ COMMITTED出现的情况. 反正这货的一致性锁定读能马上读到其他事务对相关主键进行的修改.
前提主事务不结束,区别就是,

脏读:B事务也没结束,主事务的查询发生在B事务修改语句的前后.
不可重复读:主事务先查一次,然后B事务修改提交.主事务再查.

一般来说,不可重复读是可以接受的,读到的是提交的数据.很多数据库默认都支持不可重复读.
InnoDB中，Mysql官方文档把幻象读定义成不可重复读问题。InnoDB的repeatable read不会出现这个问题。

repeatable read使用next key lock可有效防止不可重复读的情况

8.8.3. 丢失更新

出现情况

A事务将列a更新为 1,未提交
B事务将列a更新为2,未提交
A事务提交
B事务提交

即使在最低级别read committed上也不会出现这个问题,因为两个X锁会互斥,B事务会等待A事务完成.

8.9. 阻塞

innoDB中,innodb_lock_wait_timeout可以用来控制阻塞时间,默认50秒.可及时调整。

set @@innodb_lock_wait_timeout=60

innodb_rollback_on_timeout用来设置等待超时时对事务进行回滚操作。默认OFF不会滚.参数是静态的，只能在数据库启动前调整。

假设事务a插入1成功，插入2值超时，那么超时后再去查询，会发现1值是在的，2存在。

8.10. 死锁

死锁就是两个事务互相等待对方持有的锁而造成的一种互相等待的现象。

解决死锁最简单的方法就是超时：当等待时间超过阈值，一个事务回滚，另外一个事务获得锁就可以正常执行。
但如果先回滚的事务占用的undo段较大，就有点不合适了。

数据库普遍采用等待图的方式来进行死锁检测。InnoDB也采用这种方式.

8.10.1. 死锁概率

总结下就是

事务数量越多，发生死锁概率越大
每个事务操作的数量越大，发生死锁概率越大
操作数据的集合越小，发生死锁概率越大。

--- session1
mysql> begin;
Query OK, 0 rows affected

mysql> select * from s where a=1 for update;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 1 | 1 |
+---+---+---+
1 row in set

mysql> select * from s where a=7 for update;
1213 - Deadlock found when trying to get lock; try restarting transaction

---- session2
mysql> select * from s where a=7 for update;
+---+---+---+
| a | b | c |
+---+---+---+
| 7 | 7 | 7 |
+---+---+---+
1 row in set

mysql> select * from s where a=1 for update;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 1 | 1 |
+---+---+---+
1 row in set

可见一个事务成功，一个事务回滚。

8.11. 锁升级

锁升级指的是将当前锁的粒度降低。比如把1000个行锁升级为页锁。将页锁升级为表锁。

innoDB不存在锁升级的情况，因为其不是根据每个记录来产生行锁的，其根据每个事务访问的每个页来进行锁管理。

9. 事务

事务四大特性

原子性：要么做要么都不做。指的是整个事务是一个不可分割的单位。
隔离性：事务提交前，其他事务都不可见。
一致性：事务开始前和事务完成后，数据库的完整性约束没有被破坏。
持久性：事务一旦提交，结果就是永久性的。

事务分类，可分为五种

扁平事务：
带有保存点的扁平事务
链事务
嵌套事务
分布式事务

扁平事务是最常见的事务。由begin开始，commit/rollback结束。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LBUjQ4TP-1574904515595)(./1498706928536.png)]
限制是不能提交或回滚事务的某一部分，或分几个步骤提交。

带有保存点的扁平事务：支持扁平事务的操作外，允许事务执行过程中回滚到同一事务中较早的一个状态。保存点用来记录事务某一个状态。注意：当系统崩溃时，所有保存点都会丢失，意味着恢复时，事务要从头开始重新执行

begin;
insert into s value(6,6);
savepoint t1;
delete s where a=6;
rollback to savepoint t1;

-- 最终结果等同
insert into s value(6,6);

**链事务：提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式的传给下一个要开始的事务。注意：提交事务操作和开始下一个事务操作将合并为一个原子操作。意味着下一个事务将看到上一个事务的结果。**链式事务只能回滚当前事务。

嵌套事务：即事务中又有子事务，而且子事务可能还是嵌套事务或者扁平事务。
子事务可以提交也可以回滚，但是他的提交操作不能马上生效，除非顶层事务提交后才真正提交。

分布式事务：就是在分布式环境下运行的扁平事务，数据在不同节点中而已

9.1. 事务的实现

主要同过redo log重做日志undo log来实现。
redo log用来保证事务的原子性和持久性。
undo log用来保证事务的一致性。

9.1.1. redo log

由两部分组成：

内存中的重做日志缓冲：易失
重做日志文件，持久的

当事务提交后，必须先将事务的所有日志写到redo log中进行持久化，事务的commit才算完成。
undo log用来帮助事务回滚和多版本并发控制的功能。
redo log都是顺序写的。数据库运行时是不需要对redo log进行读取操作的。undo log是需要进行随机读写的

innodb_flush_log_at_trx_commit是用来控制重做日志刷新到磁盘的策略。默认为1，即每次commit都写入到重做日志文件中（此时还是缓存中），然后调用fsync操作，使日志写回到磁盘上。fsync操作效率取决于磁盘性能。还可设置0和2,0表示提交时不进行写入重做日志操作，只在master thread中完成。2表示提交时将日志写入重做日志文件，但只写入文件系统缓存中，不做fsync操作。因此，一旦操作系统宕机就会丢失数据。

如果想要大批量导入数据，innodb_flush_log_at_trx_commit默认1是很慢的。可以临时配置为0或2,0最快。

前面说过binlog是用来主从复制的文件，和redo log区别：

redo log是innodbDB引擎层面的日志。bin log是数据库层面的日志，任何引擎对于数据库的更改都会产生二进制日志。
bin log是逻辑日志，记录是sql语句。 redo log是物理格式日志，记录的是对页的修改。
binlog只在事务提交后一次写入。redo log是在事务进行时不断写入，并不是随日志提交顺序进行写入的，写入是并发的。

innodb引擎中，redo log都是以512字节进行存储的，以块的方式进行保存，称为重做日志块。

9.1.1.1. 恢复

innodb存储引擎在启动时不管上次数据库是否正常关闭，都会尝试进行恢复操作。使用的就是redolog，因为重做日志记录是物理记录，记录的是页的操作，因此恢复速度会比bin log快很多。
在show engine innodb status可看见这一段

LOG
---
Log sequence number 149853316  当前日志序列号
Log flushed up to   149853316  刷新到重做日志文件的序列号
Pages flushed up to 149853316 
Last checkpoint at  149853307  刷新到磁盘的日志序列号
0 pending log flushes, 0 pending chkp writes
26 log i/o's done, 0.00 log i/o's/second

通常生产条件下三个值是不同的。
因此恢复只会恢复checkpoint开始的日志部分。

9.1.2. undo log

重做日志记录事务的行为，能够通过其对页进行重做操作。但是事务需要回滚操作，就需要undo了。
当对数据库进行修改时，innodb不仅会产生redo，也会产生undo，当用户执行事务失败或者执行rollback进行回滚，那就得利用undo 将数据回滚到修改之前的样子。
undo存储在特殊的段中，成为undo 段。undo段位于共享表空间中。

undo log也是逻辑日志，将数据库逻辑的恢复到原来的样子，但是页和数据结构回滚后不太相同，因此页可能被多个线程同时访问。
undo log实质就是：事务insert 一条数据，undo log就是一条delete记录。update就是一个相反的update。
通过undo来进行多版本控制：当用户读取一条记录，该记录被其他事务占用，当前事务可以通过undo读取之前的版本信息，从而实现非锁定读。

undo log也会产生redo log，因为undo log也需要持久性的保护。

9.1.2.1. undo存储管理

innodb有回滚段，每个回滚段记录了1024个undo log段。
事务提交后不能马上删除undo log和undo log所在的页，有可能其他事务在通过undo log来进行非锁定读。是否删除由purge线程判断。

9.1.3. purge

之前说过 delete 和update不会直接删除数据，其他事务可能正在引用这行，在内存中标记记录被删除，由Purge线程来判断，当这条记录没有被其他事务引用时，就可以进行真正的delete操作。

9.2. 事务控制语句

默认情况下，事务都是自动提交的。
显示开启事务语句有begin,start trasaction或者set autocommit=0禁用当前自动提交.

begin,start trasaction:显式开启一个事务
commit:提交事务,等同commit work
rollback:回滚事务,等同rollback work
savepoint name:在事务中创建一个保存点,一个事务可以有多个保存点.
release savepoint name:删除一个保存点,当没有保存点会抛异常.
rollback to [savepoint] name:事务回滚到某个保存点
set transaction：设置事务隔离级别。innodb有四种，read uncommitted，read committed，repeatable read，serializable。

注意： rollback to [savepoint] name，虽然是回滚到某个保存点，但事务依然没有结束，需要执行commit或rollback才结束。

在存储过程中,只能用start trasaction来开启一个事务

9.3. 隐式提交的SQL语句

DDL语句、修改Mysql架构操作，analyze table，truncate等操作会有隐式的commit操作。

9.4. 事务操作统计

tps（每秒事务处理能力）计算 = $com\_commit+com\_rollback)/time$

show global status like `com_commit`;

9.5. 事务隔离级别

read uncommitted:事务能读到其他事务未提交的结果，产生脏读。
read committed:事务能读到其他事务提交的结果。产生幻象读，不会使用间隙锁算法。
repeatable read:innodb默认算法，使用next lock key，防止幻象读。这个级别已经能完全保证事务的隔离性要求，媲美其他数据库的serializable级别。
serializable:innodb默认对每个select 加上 lock in share mode,不再支持一致性非锁定读。主要用于分布式事务

注意，不加lock in share mode，使用的是一致性非锁定读，加锁读就很影响并发性，无法使用多版本控制mvcc特性。

设置事务级别

set [GOBAL|SESSION] tx_isolation = 级别

如果设置静态变量，在mysqld中配置

[mysqld]
transaction-isolation= 级别

查询事务级别

select @@tx_isolation 
select @@global.tx_isolation

9.6. 分布式事务

Innodb提供XA事务支持,使XA事务来支持分布式实现.分布式事务指的是允许多个独立的事务资源参与到一个全局的事务中。全局事务要求其中所有的事务要么都提交，要么都回滚。InnoDB存储引擎的事务隔离级别必须设置为Serializable

XA事务允许不同数据库之间的分布式事务.
一般是通过编程语言来控制数据的XA事务.

9.7. 不好的事务习惯

9.7.1. 在循环中提交

innoDB引擎默认是commit的,不需要手动去提交.

9.7.2. 使用自动提交

自动提交不是一个好习惯.程序中使用自动提交应该仔细考虑是否适用.

9.7.3. 存储过程中使用自动回滚

存储过程中使用自动回滚会导致错误不可见.

9.8. 长事务

施行时间较长的事务.
建议将长事务转换成小批量的事务来处理,一但事务发生错误,回滚又要很久又重复.部分提交接着上次已完成的事务来继续进行很不错.

jiangfeng999

关注

0
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
InnoDB存储引擎

InnoDB存储引擎1. 连接mysql2. 数据库与引擎的关系3. 版本介绍4. 体系架构4.1. 后台线程4.1.1. **Master Thread**4.1.2. **IO Thread**4.1.3. **Purge Thread**4.1.4. **Page Cleaner Thread**4.2. 内存4.2.1. 缓冲池4.2.1.1. 缓冲池管理算法 -LRU算法4.2.2. 重...
复制链接

扫一扫