Innodb

原文：https://nullwy.me/2023/07/innodb-locking/如果感觉我的文章对你有用，请随便赞叹目前支流数据库事务的并发管制实现，如 MySQL InnoDB、PostgreSQL、Oracle，都应用两阶段封闭 2PL 与 MVCC 技术，但具体实现细节上存在差别。InnoDB 是在以封闭技术为主体的状况下，用 MVCC 技术辅助实现读-写、写-读操作的并发。PostgreSQL 的并发控制技术是以 MVCC 技术为主，封闭技术为辅。本文次要关注 InnoDB 事务的并发管制实现。 背景常识并发管制，是数据库系统的 ACID 个性中的隔离性（Isolation)）的保障。所谓隔离性，就是事务的执行不应受到其余并发执行事务的烦扰，事务的执行看上去应与其余事务是隔离的。被隔离的执行，等价于事务的某种串行执行，或者说，它等价于一个没有并发的执行。保障串行性可能只容许极小的并发度，采纳较弱隔离性，能带来更高的并发度，是并发事务的正确性和性能之间的斗争。 晚期各大数据库厂商实现并发管制时多采纳基于封闭的并发控制技术，所以在基于封闭的技术背景下，才在 ANSI SQL-92 规范中提出了四种隔离级别：未提交读（Read Uncommitted）、己提交读（Read Committed）、可反复读（Repeatable Read）、可串行化（Serializable）（附注：为了书写简便本文将各个隔离级别顺次缩写为 RU、RC、RR、SER）。ANSI SQL-92 规范的四种隔离级别，是依据三种读异常现象（phenomena）定义的，隔离级别和异常现象的关系如下： 隔离级别P1 脏读P2 不可反复读P4 幻读Read Uncommitted可能可能可能Read Committed防止可能可能Repeatable Read防止防止可能Serializable防止防止防止ANSI SQL-92 规范文档对三种读异常现象的定义原文如下 [ref]： The isolation level specifies the kind of phenomena that can occur during the execution of concurrent SQL-transactions. The following phenomena are possible:1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed.2) P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.3) P3 ("Phantom"): SQL-transaction T1 reads the set of rows N that satisfy some <search condition>. SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the <search condition> used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows.除了脏读、不可反复读和幻读这 3 种读数据异样外，还有写数据异样，即脏写和失落更新。各个异样的含意如下： ...

1 InnoDB存储引擎InnoDB存储引擎最早由Innobase Oy公司开发（属第三方存储引擎）。从MySQL 5.5版本开始作为表的默认存储引擎。该存储引擎是第一个残缺反对ACID事务的MySQL存储引擎，特点是行锁设计、反对MVCC、反对外键、提供一致性非锁定读，非常适合OLTP场景的利用应用。目前也是利用最宽泛的存储引擎。 InnoDB存储引擎架构蕴含内存构造和磁盘构造两大部分，总体架构图如下： 8.0版本： []() 5.5版本： []() 2 InnoDB 存储构造2.1 磁盘构造2.1.1 表空间 TablespacesInnoDB存储引擎的逻辑存储构造是将所有的数据都被逻辑地放在了一个空间中，这个空间中的文件就是理论存在的物理文件（.ibd文件），即表空间。默认状况下，一个数据库表占用一个表空间，表空间能够看做是InnoDB存储引擎逻辑构造的最高层，所以的数据都寄存在表空间中，例如：表对应的数据、索引、insert buffer bitmap undo信息、insert buffer 索引页、double write buffer files 等都是放在共享表空间中的。 表空间分为零碎表空间(ibdata1文件)(共享表空间)、长期表空间、惯例表空间、Undo表空间和file-per-table表空间(独立表空间)。零碎表空间又包含双写缓冲区(Doublewrite buffer)、Change Buffer等 1.零碎表空间 System Tablespace 零碎表空间能够对应文件系统上一个或多个理论的文件，默认状况下， InnoDB会在数据目录下创立一个名为.ibdata1，大小为 12M的文件，这个文件就是对应的零碎表空间在文件系统上的示意。这个文件是能够自扩大的，当不够用的时候它会本人减少文件大小。须要留神的一点是，在一个MySQL服务器中，零碎表空间只有一份。从MySQL5.5.7到MySQL5.6.6之间的各个版本中，咱们表中的数据都会被默认存储到这个零碎表空间。 show variables like '%innodb_data_file_path%'[]() 2.独立表空间 在MySQL5.6.6以及之后的版本中， InnoDB并不会默认的把各个表的数据存储到零碎表空间中，而是为每一个表建设一个独立表空间，也就是说咱们创立了多少个表，就有多少个独立表空间。应用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创立一个示意该独立表空间的文件，文件名和表名雷同，只不过增加了一个.ibd的扩展名而已。 show variables like '%innodb_file_per_table%'[]() 独立表空间只是存放数据、索引和插入缓冲Bitmap页，其余类的数据如回滚（undo）信息、插入缓冲索引页、零碎事务信息、二次写缓冲等还是寄存在原来的零碎表空间。 3.其余类型的表空间 随着MySQL的倒退，除了上述两种表空间之外，当初还新提出了一些不同类型的表空间，比方通用表空间 (general tablespace)、undo表空间(undo tablespace)、长期表空间(temporary tablespace)等 4.表空间结构 表空间又由段(segment)、区( extent)、页(page)组成，页是InnoDB磁盘治理的最小单位。在咱们执行sql时，不论是查问还是批改，mysql 总会把数据从磁盘读取内内存中，而且在读取数据时，不会独自加在一条数据，而是间接加载数据所在的数据页到内存中。表空间实质上就是一个寄存各种页的页面池。 []() 「页」是InnoDB治理存储空间的根本单位，也是内存和磁盘交互的根本单位。也就是说，哪怕你须要1字节的数据，InnoDB也会读取整个页的数据，InnoDB有很多类型的页，它们的用途也各不相同。比方：有寄存undo日志的页、有寄存INODE信息的页、有寄存Change Buffer信息的页、寄存用户记录数据的页（索引页）等等。 InnoDB默认的页大小是16KB，在初始化表空间之前能够在配置文件中进行配置，一旦数据库初始化实现就不可再变更了。 SHOW VARIABLES LIKE 'innodb_page_size'[]() 2.1.2 重写日志 redo log文件redo log记录数据库的变更，数据库解体后，会从redo log获取事务信息，进行零碎复原。redo log在磁盘上体现为ib_logfile0和ib_logfile1两个文件。MySQL会在事务的提交前将redo日志刷新回磁盘。 ...

作者：刘开洋 爱可生交付服务团队北京 DBA，对数据库及周边技术有浓重的学习趣味，喜爱看书，谋求技术。 本文起源：原创投稿 *爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。 问题共事在客户那里解决问题时发现一个报错，出于趣味就进行了钻研： Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions,and never in the same statement as updates to transactional tables. 报错的意思是指业务端下发的事务违反了 GTID 的一致性。 起因是，非事务表和事务表在一条事务里进行了更新，MySQL 是不容许咱们这么做的，但业务非要胳膊掰大腿，怎么办？ 官网阐明： Only statements that can be logged using GTID safe statements can be logged when enforce_gtid_consistency is set to ON,so the operations listed here cannot be used with this option: ...

问题：MySQL 官网文档说 innodb_log_buffer_size 是 redo log 的写缓存，设置大一点能缩小写操作。 咱们用试验来学习一下这个参数的作用。 试验：宽油起个数据库： 先将 innodb_log_buffer_size 设置为最小值。 接下来，咱们先建一个有数据的表，建表的办法参考试验 11： 反复执行最初一句 insert，直到表中有 65536 行数据： 接下来咱们复制一张表，因为咱们要做两次试验： 接下来咱们开启 innodb metrics 和 performance_schema： 执行一次 insert，计算一下 LSN 的差值，也就是这个事务产生了多少 redo log： 能够看到这个事务大略产生了 3M 左右的 redo log。 再看一下这个过程产生了多少 IO： 能够看到产生了 7 次 IO，6 次 write 和 1 次 sync，每次 write 大略须要 0.8ms。 当初咱们变更一下 my.cnf，将 innodb_log_buffer_size 变更为默认值 16777216，重启数据库。此处省略步骤, 只看后果： 因为重启了一次数据库，记得再开启一次 innodb metrics 和 performance_schema： ...

作者：郭斌斌爱可生 DBA 团队成员，负责我的项目日常问题解决及公司平台问题排查。 本文起源：原创投稿 *爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。 对于主从复制，目前用的比拟广泛的是 RBR（Row-base replication） 这种形式，有这么一个问题大家能够一起思考一下。 问题：RBR 主从复制，主库表 A 有两个字段 (c1 int,c2 int)，从库表 A 有一个字段 （c1 int），主库对表 A 的 c2 字段执行插入或者更新操作，主从复制是否会受影响？ 论断：论断是复制不受影响。 验证（版本 MySQL 5.7.25）：主库 t1 表构造和数据 从库 t1 表构造和数据 从库复制状态： 主库执行 insert 操作： 从库查看复制状态和同步的后果： 主库执行 update 操作 从库查看复制状态，并查看 t1 数据 起因：对于主从复制，主从同步的表构造其实是能够不统一的，主库表的列数能够比从库表的多，也能够比从库表的少，然而这种场景对于复制无影响也是有条件的。 条件：1、雷同列的定义程序必须完全一致。 2、雷同列的定义肯定在在差别列之前。 3、差别的列必须要有默认值。 参考链接：https://dev.mysql.com/doc/ref...

本篇次要介绍 MySQL 的函数索引（也叫表达式索引）。 通常来讲，索引都是基于字段自身或者字段前缀（第 20 篇），而函数索引是基于字段自身加上函数、操作符、表达式等计算而来。如果将表达式或者操作符也看做函数的话，简略来说，这样的索引就能够统称函数索引。 MySQL 的函数索引外部是基于虚构列（generated columns）实现，不同于间接定义虚构列，函数索引主动创立的虚构列自身实时计算结果，并不存储数据，只把函数索引自身存在磁盘上。 MySQL 8.0.13 之前不反对函数索引，所以老版本包含当初支流的 MySQL 5.7 也不反对函数索引，须要手工模仿创立或者改 SQL。 本章基于以下几点来讲函数索引： 1.函数索引的应用场景函数索引最最经典的应用场景莫过于就是对日期的解决，特地是表中只定义了一个字段，前期对这个字段的查问都是基于局部后果。比方 “2100-02-02 08:09:09.123972” 蕴含了日期 “2100-02-02”，工夫 “08:09:09”，小数位工夫 “123972”，有可能会对这个值拆解后局部查问。 举个简略例子，表 t1 有两个字段，一个主键，另外一个工夫字段，总记录数不到 40W。 <localhost|mysql>show create table t1\G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `log_time` datetime(6) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_log_time` (`log_time`)) ENGINE=InnoDB AUTO_INCREMENT=524268 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)<localhost|mysql>select count(*) from t1;+----------+| count(*) |+----------+| 393216 |+----------+1 row in set (0.07 sec)执行上面这条 SQL 1，把日期独自拿进去，执行了 0.09 秒。 ...

innodb存储引擎应用B+树实现索引的B+树不同于B树的规定：1、数据或者数据的指针只寄存在叶子节点，非叶子节点只存储关键字2、叶子节点中关键字由小到大排列，左叶子开端数据会保留右叶子开始数据的指针；所以，B+树的长处：1、因为非叶子节点只存储关键字，能够寄存的关键字大大增加，因而树的层级更少2、查问任何一个数据都须要从叶子节点获取数据的地址，因而每次数据的查问次数完全一致，因而查问的速度更稳固3、叶子节点人造就是有序链表4、全表遍历时，只须要遍历叶子节点链表即可，不须要像B树一样须要遍历每一层，性能更好当然B树也有B+树所不具备的有点：如果要查问的某条数据离树的根节点更近，这时查问速度比拟B+树更快

这里次要介绍 MySQL 的前缀索引。从名字上来看，前缀索引就是指索引的前缀，当然这个索引的存储构造不能是 HASH，HASH 不反对前缀索引。 先看上面这两行示例数据： 你是中国人吗？是的是的是的是的是的是的是的是的是的是的确定是中国人？是的是的是的是的是的是的是的是的是的是的这两行数据有一个独特的特点就是后面几个字符不同，前面的所有字符内容都一样。那面对这样的数据，咱们该如何建设索引呢？ 大抵有以下 3 种办法： 拿整个串的数据来做索引这种办法来的最简略直观，然而会造成索引空间极大的节约。反复值太多，进而索引中无用数据太多，无论写入或者读取都产生极大资源耗费。 将字符拆开，将一部分做索引把数据后面几个字符和残余的局部字符分拆为两个字段 r1_prefix，r1_other，针对字段 r1_prefix 建设索引。如果排除掉表构造更改这块影响，那这种办法无疑是最好的。 把后面 6 个字符截取进去的子串做一个索引是否不拆分字段，又能防止太多反复值的冗余？咱们明天讨论一下前缀索引。 前缀索引前缀索引就是基于原始索引字段，截取后面指定的字符个数或者字节数来做的索引。 MySQL 基本上大部分存储引擎都反对前缀索引，目前只有字符类型或者二进制类型的字段能够建设前缀索引。比方：CHAR/VARCHAR、TEXT/BLOB、BINARY/VARBINARY。 字符类型基于前缀字符长度，f1(10) 指的前 10 个字符；二进制类型基于字节大小，f1(10) 指的是前 10 个字节长度；TEXT/BLOB 类型只反对前缀索引，不反对整个字段建索引。举个简略例子，表 t1 有两个字段，针对字段 r1 有两个索引，一个是基于字段 r1 的一般二级索引，另外一个是基于字段r1的前缀索引。 10px;">`<localhost|mysql>show create table t1\G*************************** 1\. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `r1` varchar(300) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_r1` (`r1`), KEY `idx_r1_p` (`r1`(6))) ENGINE=InnoDB AUTO_INCREMENT=32755 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)<localhost|mysql>select count(*) from t1;+----------+| count(*) |+----------+| 24576 |+----------+1 row in set (0.04 sec)上面别离是表 t1 只有 idx_r1 和 idx_r1_p 的表空间文件大小，很显著，前缀索引十分有劣势。 ...

这里讲述 MySQL 哈希索引的实现形式以及应用场景。哈希表在 MySQL 里有如下利用： 各种存储引擎的哈希索引存储。MySQL 的 Memory，NDB 都有哈希索引的具体实现；MySQL 外部生成的哈希表。比方：数据在 innodb buffer pool 里的疾速查找；子查问的物化主动加哈希索引；JOIN KEY 无 INDEX 下的 HASH JOIN 等。一、哈希数据分布哈希索引显式利用次要存在于内存表，也就是 Memory 引擎，或者是 MySQL 8.0 的 Temptable 引擎。本篇的内容上都是基于内存表，MySQL 内存表的大小由参数 max_heap_table_size 来管制，其中蕴含了表数据，索引数据等。 举个例子，表 t1 有六行记录，主键哈希索引。 # MySQL 内存表主键默认哈希索引mysql> create table t1(id int , name varchar(64), gender char(1), status char(2),primary key (id)) engine memory;Query OK, 0 rows affected (0.02 sec)mysql> insert into t1 values(101,'张三','男','未婚');Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values(102,'小明','男','已婚');Query OK, 1 row affected (0.01 sec)mysql> insert into t1 values(103,'李四','男','未婚');Query OK, 1 row affected (0.01 sec)mysql> insert into t1 values(104,'李庆','女','已婚');Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values(105,'杨阳','女','未婚');Query OK, 1 row affected (0.01 sec)mysql> insert into t1 values(106,'余欢水','男','二婚');Query OK, 1 row affected (0.01 sec)我简略画了张主键哈希索引的分布图，见图 1： ...

MySQL 的默认索引构造是 B+ 树，也能够指定索引构造为 HASH 或者 R 树等其余构造来适应不同的检索需要。这里咱们来介绍 MySQL 哈希索引。 MySQL 哈希索引又基于哈希表（散列表）来实现，所以理解什么是哈希表对 MySQL 哈希索引的了解至关重要。接下来，咱们来一步一部介绍哈希表。 1. 数组数组是最罕用的数据结构，是一种线性表的顺序存储形式，由下标（也叫索引）和对应的值形成。数组在各个开发语言以及数据库中都有相似的构造，相似下图1： 图 1 展现了一个一维整数数组，数组的长度为 10，下标从 0-9， 每个下标对应不同的值。每种编程语言基本上都有数组，大部分数据库也提供了数组或者是相似数组的构造，MySQL 也有数组，以下为 MySQL 的一维数组： mysql> select @a as "array",json_length(@a) as "array_size";+-------------------------------------------+------------+| array | array_size |+-------------------------------------------+------------+| [10, 20, 30, 40, 50, 60, 70, 80, 90, 100] | 10 |+-------------------------------------------+------------+1 row in set (0.00 sec)数组元素也能够是数组，这样的示意称为多维数组，如图 2，一个二维字符串数组： 以下为 MySQL 里的多维数组： mysql> select json_pretty(@a)\G*************************** 1. row ***************************json_pretty(@a): [ [ "mysql", "db2" ], [ "oracle", "mongodb", "sql server", "redis" ], [ "memcached", "dble", "postgresql", "mariadb" ]]1 row in set (0.01 sec)数组优缺点如下，长处： ...

从数据结构角度1：B+树索引（o(logn)2：hash索引 (key=>value)3：全文索引4：R-tree索引物理角度1：汇集索引2：飞汇集索引逻辑角度1：主键索引2：一般索引3：惟一索引为什么会抉择B+树数据结构来存储数据在B+树中，非叶子节点上是不存数据的，仅存储键值。因为在数据中的页大小是固定的，Innodb中页的默认大小是16kb，如果不存储数据，那么节点就能够存储更多的键值，相应的树的节数更大，对于同样的数据量来说，须要的树高就会变低。如此一来查问数据的时候进行磁盘的IO次数就会缩小，晋升查问效率。因为B+树的节数等于键值数量，假如B+树的一个结点能够存储1000个键值，那么3层的B+树能够存储100010001000=10亿个数据。并且个别节点是常驻内存的，所以查找10亿个数据，只有两次IOB+树的叶子节点中的索引数据是依照程序排序的，并且叶子节点间是通过双向链表进行链接 主键索引和一般索引的差异主键索引 主键索引的叶子节点存的整行数据，主键索引也称为聚簇索引非主键索引 非主键索引的叶子节点内容存在的主键的值，非主键索引也成为二级索引或是一般索引基于主键索引和一般索引的查问有什么区别？主键索引，索引查问进去间接就是查问的值非主键索引，先搜寻查到主键索引，而后主键索引在搜寻一次。这个过程成为回表

1：InnoDb的四个个性：一致性、原子性、持久性、隔离性2：事物的隔离级别：可反复读、读已提交、读未提交、串行化3：查看以后的事物隔离级别 show varibles like "transaction-isolation'4：幻读：幻读是一个事物在事物开始中，两次select查问的后果不始终， 这产生的幻读。幻读存在的隔离级别有 R-U R-C R-R 。5：幻读的解决办法：在RR的隔离级别下，咱们对记录手动加上X锁的办法打消幻读（在Innodb的行锁锁定的是这个索引，估在记录实体存在与否没有关系，存在就在X锁，不存在就加 next-key lock/间隙锁），其余事物则无奈插入此索引记录，防止幻读）5：共享锁（shared）：又称为S锁，读锁。一个事物对数据对象O记上S锁，能够对O进行读取操作，然而不能进行更新操作。加锁期间其余事物能对o加s锁，然而能加X锁6：排他锁（Exclusive）：又称为X锁，写锁。一个事物对数据0加上X锁，就能够对O进行读取和更新。加上期间其余事物不能对0加任何锁。 参考文档：https://juejin.cn/post/684490...https://segmentfault.com/a/11...

上一章讲了数据库基本上都用 B+ 树来存储索引的起因：适宜磁盘存储，可能充分利用多叉均衡树的个性，磁盘预读，并且很好的反对等值，范畴，程序扫描等。这篇次要介绍 MySQL 两种罕用引擎，MyISAM 和 InnoDB 的索引组织形式，理解这些存储形式，对数据库优化很有帮忙。 MySQL 的索引依照存储形式分为两类：汇集索引：也称 Clustered Index。是指关系表记录的物理程序与索引的逻辑程序雷同。因为一张表只能依照一种物理程序寄存，一张表最多也只能存在一个汇集索引。与非汇集索引相比，汇集索引有着更快的检索速度。 MySQL 里只有 INNODB 表反对汇集索引，INNODB 表数据自身就是汇集索引，也就是常说 IOT，索引组织表。非叶子节点依照主键程序寄存，叶子节点寄存主键以及对应的行记录。所以对 INNODB 表进行全表程序扫描会十分快。 非汇集索引：也叫 Secondary Index。指的是非叶子节点依照索引的键值程序寄存，叶子节点寄存索引键值以及对应的主键键值。MySQL 里除了 INNODB 表主键外，其余的都是二级索引。MYISAM，memory 等引擎的表索引都是非汇集索引。简略点说，就是索引与行数据离开存储。一张表能够有多个二级索引。  MYISAM 表：MYISAM 表是典型的数据与索引拆散存储，主键和二级索引没有本质区别。比方在 MYISAM 表里主键、惟一索引是一样的，没有本质区别。 假如表 t1 为 MYISAM 引擎，列为 ID，姓名，性别，年龄，手机号码。其中 ID 为主键，年龄为二级索引。记录如下： 那对应的两个 B+ 树索引如下图所示， 主键字段索引树： 上图是一个 3 阶的 B+ 树，非叶子节点依照主键的值排序存储，叶子节点同样依照主键的值排序存储，并且蕴含指向磁盘上的物理数据行指针。  年龄字段索引树： 上图年龄字段索引树同样是一个 3 阶的 B+ 树，非叶子节点依照年龄字段的值顺序存储，叶子节点保留年龄字段的值以及指向磁盘上的物理数据行指针。 从下面两张图能够看出，MYISAM 表的索引存储形式最大的毛病没有依照物理数据行顺序存储，这样无论对主键的检索还是对二级索引的检索都须要进行二次排序。  举个简略例子演示下， 以下 SQL 1 默认没有排序，乱序输入；须要依照 ID 程序输入，就得用 SQL 2，显式加 ORDER BY 。 ...

ref重做日志double write

作者：宗杨爱可生产品交付团队成员，次要负责公司运维平台和数据库运维故障诊断。青睐数据库、容器等技术，喜好历史、追剧。 本文起源：原创投稿*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。一、事件背景咱们的单干客户，驻场人员报告说一个 RDS 实例呈现磁盘有余的告警，须要排查。 告警信息： 告警内容：数据库 data 磁盘有余，磁盘占用 80% 以上数据库 binlog 磁盘有余，磁盘占用 80% 以上 二、排查过程登陆告警的服务器，查看磁盘空间，并寻找大容量文件后，发现端口号为 4675 的实例长期表空间 ibtmp1 的大小有 955G，导致磁盘被应用了 86%； 猜想和库里执行长 SQL 有关系，产生了很多长期数据，并写入到长期表空间。      看到有这样一条 SQL，持续剖析它的执行打算； 很显著看到图中标记的这一点为应用了长期计算，阐明长期表空间的快速增长和它有关系。这条 SQL 进行了三表关联，每个表都有几十万行数据，三表关联并没有在 where 条件中设置关联字段，造成了笛卡尔积，所以会产生大量长期数据；而且都是全表扫描，加载的长期数据过多;还波及到排序产生了长期数据；这几方面导致 ibtmp1 空间疾速爆满。 三、解决办法和项目组沟通后，杀掉这个会话解决问题； 然而这个 SQL 停下来了，长期表空间中的长期数据没有开释； 最初通过重启 mysql 数据库，开释了长期表空间中的长期数据，这个只能通过重启开释。 四、剖析原理通过查看官网文档，官网是这么解释的： 翻译： 依据官网文档的解释，在失常敞开或初始化停止时，将删除长期表空间，并在每次启动服务器时从新创立。重启可能开释空间的起因在于失常敞开数据库，长期表空间就被删除了，重新启动后从新创立，也就是重启引发了长期表空间的重建，从新初始化，所以，重建后的大小为 12M。 从谬误日志里能够验证下面的观点： 五、官网对于 ibtmp1 大小的阐明 六、如何防止1. 对长期表空间的大小进行限度，容许主动增长，但最大容量有下限,本例中因为 innodb_temp_data_file_path 设置的主动增长，但未设下限，所以导致 ibtmp1 有 955G。正确办法配置参数 innodb_temp_data_file_path：[mysqld]innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M参考官网文档： ...

MySQL InnoDB 表数据页或者二级索引页（简称数据页或者索引页）的合并与决裂对 InnoDB 表整体性能影响很大；数据页的这类操作越多，对 InnoDB 表数据写入的影响越大。MySQL 提供了一个数据页合并临界值（MERGE_THRESHOLD），在某些场景下，能够人为染指，缩小数据页的合并与决裂。在 InnoDB 表里，每个数据页默认16K 大小，默认 MERGE_THRESHOLD 值为 50，取值范畴从 1 到 50，默认值即是最大值。也就是当页面记录数占比小于 50% 时，MySQL 会把这页和相邻的页面进行合并，保证数据页的紧凑，防止太多节约。 触发临界值场景场景一：页 A 里原本数据占用 100%，有一部分记录被删掉后，数据占用小于 50%，刚好触发了临界值。 场景二：页 B 里寄存的记录被更新为更短的模式，比方记录值由 rpad（'我爱你们所有人' , 10000, '增加冗余字符'）变为 '我只爱你' ，这时候记录对数据页占用也小于 50%，刚好触发了临界值。 简述数据页的合并页 A 在删除一些记录后，此页里残余记录对页 A 的占用小于 MERGE_THRESHOLD 设定的值，此时刚好页 A 相邻的一个页 C，数据占用也不到 50%，这时候 MySQL 会把页 C 的记录并入页 A，之后页 C 的空间就被开释，不蕴含任何数据，页 C 就可用于当前新记录的写入，防止空间的节约。 简述数据页的决裂页 D 和页 E，两个页面记录占用都在 49%。那么页合并后，页 D 记录占用 98%，只剩下 2%。页 F 和页 H，两个页面记录占用也都是 49%，那么合并后，页 F 记录占用 98%，也只剩下 2%。此时有新的插入申请过去，这条记录的主键刚好在页 D 和页 F 之间，可是页 D 和页 F 都只剩下 2% 的空间，不够插入这条记录。那怎么办？此时只能拆分页 D。建设一个新的页 I，完了把页 D 原来的记录和新插入的记录做一个排序，再依照新的程序把页 D 填满，剩下的数据放到页 I。所以页决裂会波及到老页数据的迁徙到新建页的建设，如果页的决裂频繁，那开销很大。 下来看看 MERGE_THRESHOLD 的理论用法与监测。留神！MERGE_THRESHOLD 不能小写，必须大写！小写就会被 MySQL 当作简略的正文。 ...

作者：岳明强爱可生北京分公司 DBA 团队成员，负责数据库治理平台的运维和 MySQL 问题解决。善于对 MySQL 的故障定位。本文起源：原创投稿*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。问题有的时候 SQL 语句被锁住了，可是通过 show processlist 找不到加锁的的 SQL 语句，这个时候应该怎么排查呢 前提performance_schema = on;试验1、建一个表，插入三条数据 mysql> use test1;Database changedmysql> create table action1(id int);Query OK, 0 rows affected (0.11 sec) mysql> insert into action1 values(1),(2),(3);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from action1;+------+| id |+------+| 1 || 2 || 3 |+------+3 rows in set (0.00 sec)2、开启一个事务，删除掉一行记录，但不提交 mysql> begin;Query OK, 0 rows affected (0.00 sec) mysql> delete from action1 where id = 3;Query OK, 1 row affected (0.00 sec)3、另开启一个事务，更新这条语句，会被锁住 ...

本文目录： 一、表空间加密概述 应⽤场景加密插件加密限制注意事项二、加密表空间 安装加密插件配置表空间加密查看表空间被加密的表三、更新 master encryption key四、导⼊导出 案例五、备份恢复 innobackupex六、参考文档 表空间加密概述从 5.7.11 开始，InnoDB 支持对独立表空间进行静态数据加密。该加密是在引擎内部数据页级别的加密手段，在数据页写入文件系统时加密，从文件读到内存中时解密，目前广泛使用的是 YaSSL/OpenSSL 提供的 AES 加密算法，加密前后数据页大小不变，因此也称为透明加密。 它使用两层加密密钥架构，包括 master encryption key 和 tablespace key： master encryption key 用于加密解密 tablespace key。当对一个表空间加密时，一个 tablespace key 会被加密并存储在该表表空间的头部tablespace key 用于加密表空间文件。当访问加密表空间时， InnoDB 会先用 master encryption key 解密存储在表空间中的加密 tablespace key，得到明文的 tablespace key 后，再用 tablespace key 解密数据tablespace key 解密后的明文信息基本不变（除非进行 alter table test_1 encrytion=NO, alter table test_1 encrytion=YES）。而 master key 可以随时改变（比如使用 ALTER INSTANCE ROTATE INNODB MASTER KEY;），这个称为 master key rotation。因为 tablespace key 的明文不会变，所以更新 master encryption key 之后只需要把 tablespace key 重新加密写入第一个页中即可。应用场景未配置表空间加密时，当发生类似拖库操作时，数据极可能会泄漏。配置表空间加密时，如果没有加密时使用的 keyring（该文件由 keyring_file_data 参数设定），是读取不到加密表空间数据的。所以当发生类似拖库操作时，没有相关的 keyring 文件时，数据基本不会泄漏的。这就要求存储的 keyring 一定要严加保管，可以采取以下措施来保存 keyring： ...

最近更新为spring boot 2.1.7后，遇到了一系列小的问题。本文阐述下spring boot对mysql引擎的支持。 解决方法spring: jpa: properties: hibernate: dialect: org.hibernate.dialect.MySQL55Dialect问题描述当我们配置spring.jap.hibernate.ddl-auto: create或是update等属性后，hibernate为我们自己动生成了数据表。但系统启动时在控制台中有报错，报错内容指明hibernate在给字段添加外键时产生了错误。经排查，错误产生的原因在于hibernate为我们自己动生成的表的引擎为MyISAM，而MyISAM并不支持外键。其实我们想要的引擎是Innodb。 尝试解决由于spring 1.x版本中，是不需要配置此项的，所以我首先来到了spring boot官方参考文档： https://docs.spring.io/spring-boot/docs/2.x.x.RELEASE/reference/html/common-application-properties.html具体使用时，请将2.x.x换成自己使用的版本，比如2.1.7。很遗憾，自2.0.0开始至2.1.8结束，我们以关键字dialect查询，并没有找到关于dialect的选项。而且我们查看jpa的配置项，也没有找到相关的记录： spring.data.jpa.repositories.bootstrap-mode=default # Bootstrap mode for JPA repositories.spring.data.jpa.repositories.enabled=true # Whether to enable JPA repositories.spring.jpa.database= # Target database to operate on, auto-detected by default. Can be alternatively set using the "databasePlatform" property.spring.jpa.database-platform= # Name of the target database to operate on, auto-detected by default. Can be alternatively set using the "Database" enum.spring.jpa.generate-ddl=false # Whether to initialize the schema on startup.spring.jpa.hibernate.ddl-auto= # DDL mode. This is actually a shortcut for the "hibernate.hbm2ddl.auto" property. Defaults to "create-drop" when using an embedded database and no schema manager was detected. Otherwise, defaults to "none".spring.jpa.hibernate.naming.implicit-strategy= # Fully qualified name of the implicit naming strategy.spring.jpa.hibernate.naming.physical-strategy= # Fully qualified name of the physical naming strategy.spring.jpa.hibernate.use-new-id-generator-mappings= # Whether to use Hibernate's newer IdentifierGenerator for AUTO, TABLE and SEQUENCE.spring.jpa.mapping-resources= # Mapping resources (equivalent to "mapping-file" entries in persistence.xml).spring.jpa.open-in-view=true # Register OpenEntityManagerInViewInterceptor. Binds a JPA EntityManager to the thread for the entire processing of the request.spring.jpa.properties.*= # Additional native properties to set on the JPA provider.spring.jpa.show-sql=false # Whether to enable logging of SQL statements.除此以外，spring.DATASOURCE中的配置项目也未找到相关的配置信息。莫非，官方将其取消了？ ...

MySQL 你好，死锁 前言在日常的生活中，相信大家曾或多或少有这么一种体验："每到下班高峰期的时候，原本宽坦的交通干道，一时间变得水泄不通，司机和乘客都烦躁不安，喇叭声响成一片，当车卡在十字路口中间，会很尴尬的发现，此时无论想走哪都…..."。对于这样的体验，大家都是十分的害怕接触和体验，交通部门也无时无刻为解决交通拥堵问题而努力。 其实上面生活案例中拥堵就类似于——高并发场景； 而所有方向的车堵在十字路口中间就类似于——数据库死锁场景。 本章主要围绕InnoDB存储引擎死锁相关的一些概念、产生死锁的原因、死锁场景以及死锁的处理策略。 相关概念为了更好的认识死锁，我们先来了解MySQL中与死锁相关的一些基本概念。 并发控制并发控制（Concurrency control）指的是当多个用户同时更新运行时，用于保护数据库完整性的各种技术。 读写锁为了保证数据库的并发控制，因此MySQL设置了两种锁： 共享锁（Shared Lock）：也叫读锁（Read Lock），允许多个连接可以同一时刻并发的读取同一资源，互不干扰排他锁（Exclusive Lock）：也叫写锁（Write Lock），会阻塞其他写锁或者读书的请求，保证同一时刻只有一个连接可以操作数据，包括读锁策略所谓锁策略就是在锁的开销和数据的安全性之间寻求平衡，这种平衡会影响到性能。目前InnoDB存储引擎有以下两种锁策略： Table Lock（表锁）策略：最基本的锁策略，开销最小，加锁快，不会出现死锁，但发生锁冲突概率高，粒度大，并发低Row Lock（行锁）策略：粒度最小，发生锁冲突态度低，并发也高，但是开销大，加锁慢，会出现死锁事务所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。一个事务是需要通过严格ACID测试的： 原子性(ATOMICITY)：一个事务的整个操作，要么全部提交成功，要么全部失败回滚，不能执行其中的某一部分一致性(CONSISTENCY)：数据库总是从一个一致性的状态转换到另外一个一致性的状态隔离性(ISOLATION)：一个事物所作的修改在提交前，其他事务是看不到的持久性(DURABILITY)：一旦事务提交，则其所做的修改就会永久保存到数据库中隔离级别SQL标准制定了四种隔离级别，规定事务的修改对其它事务是否可见 READ UNCOMMITED（未提交读）：未提交也可见，又称脏读READ COMMITED （提交读）：只有提交才可见，大多数DBMS默认隔离级别都是这个，MySQL不是，也称不可重复读REPEATABLE READ （可重复读），多次重复读取结果一致，MySQL默认这个级别，解决脏读问题，但存在幻读问题（某个事务读取记录时，另一事务插入了新纪录，原事务再读取记录时产生幻行）。SERIALIZABLE （可串行化），最高隔离级别，强制事务串行执行，避免了前面说的幻读问题，并发性能差隔离级别脏读可能性不可重复读可能性幻读可能性加锁读READ UNCOMMITEDYesYesYesNoREAD COMMITEDNoYesYesNoREPEATABLE READNoNoYesNoSERIALIZABLENoNoNoYes死锁的定义死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源（我等待你的资源，你却等待我的资源，我们都相互等待，谁也不释放自己占有的资源），从而导致恶性循环的现象： 当多个事务试图以不同顺序锁定资源时，就可能会产生死锁多个事务，同时锁定同一个资源时，也会产生死锁死锁的危害死等和死锁可不是一回事，如果你遇到了死等，大可放心，肯定不是死锁；如果发生了死锁，也大可放心，绝对不会死等。 这是因为MySQL内部有一套死锁检测机制，一旦发生死锁会立即回滚一个事务，让另一个事务执行下去。并且这个死锁回滚的的错误消息也会发送给客户端。即使正常的业务中，死锁也时不时会发生，所以遇到死锁不要害怕，因为这也是对数据安全的一种保护，但是若死锁太频繁，那可能会带来许多的问题： 使进程得不到正确的结果：处于死锁状态的进程得不到所需的资源，不能向前推进，故得不到结果使资源的利用率降低：处于死锁状态的进程不释放已占有的资源，以至于这些资源不能被其他进程利用，故系统资源利用率降低导致产生新的死锁：其它进程因请求不到死锁进程已占用的资源而无法向前推进，所以也会发生死锁死锁产生的原因死锁有四个必要的条件： 互斥排他：一个资源每次只能被一个进程使用保持着排他资源又提出新资源请求：一个进程因请求资源而阻塞时，对已获得的资源保持不放不可剥夺：资源不能被抢占，即资源只能在进程完成任务后自动释放环路：有一组等待进程｛P0、P1、P2｝，P0等待的资源被P1所占有，P1等待的资源被P2所占有，而P2等待的又被P0所占有，形成了一个等待循环死锁的发生场景以下的所有场景是基于 InnoDB存储引擎并且隔离级别为REPEATABLE-READ（可重复读） 查询当前的隔离级别： select @@global.tx_isolation,@@tx_isolation;+-----------------------+-----------------+| @@global.tx_isolation | @@tx_isolation |+-----------------------+-----------------+| REPEATABLE-READ | REPEATABLE-READ |+-----------------------+-----------------+修改隔离级别： set global transaction isolation level read committed; ## 全局的set session transaction isolation level read committed; ## 当前会话(session)创建数据表 CREATE TABLE `deadlock` ( `id` int(11) NOT NULL, `stu_num` int(11) DEFAULT NULL, `score` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_uniq_stu_num` (`stu_num`), KEY `idx_score` (`score`)) ENGINE=InnoDB;insert into deadlock(id, stu_num, score) values (1, 11, 111);insert into deadlock(id, stu_num, score) values (2, 22, 222);insert into deadlock(id, stu_num, score) values (3, 33, 333);id主键索引 ...

MySQL数据库区别于其他数据库的很重要的一个特点就是其插件式的表存储引擎，其基于表，而不是数据库。由于每个存储引擎都有其特点，因此我们可以针对每一张表来挑选最合适的存储引擎。 作为DBA，我们应该深刻的认识存储引擎。今天介绍两种最常见的存储引擎和它们的区别：InnoDB和MyISAM。 InnoDB存储引擎InnoDB存储引擎支持事务，其设计目标主要就是面向OLTP（On Line Transaction Processing 在线事务处理）的应用。特点为行锁设计、支持外键，并支持非锁定读。从5.5.8版本开始，InnoDB成为了MySQL的默认存储引擎。 InnoDB存储引擎采用聚集索引（clustered）的方式来存储数据，因此每个表都是按照主键的顺序进行存放，如果没有指定主键，InnoDB会为每行自动生成一个6字节的ROWID作为主键。 MyISAM存储引擎MyISAM存储引擎不支持事务、表锁设计，支持全文索引，主要面向OLAP（On Line Analytical Processing 联机分析处理）应用，适用于数据仓库等查询频繁的场景。在5.5.8版本之前，MyISAM是MySQL的默认存储引擎。该引擎代表着对海量数据进行查询和分析的需求。它强调性能，因此在查询的执行速度比InnoDB更快。 MyISAM存储引擎还有一个特点是只缓存索引文件，而不缓存数据文件，这点非常独特。 InnoDB和MyISAM的区别事务为了数据库操作的原子性，我们需要事务。保证一组操作要么都成功，要么都失败，比如转账的功能。我们通常将多条SQL语句放在begin和commit之间，组成一个事务。 InnoDB支持，MyISAM不支持。 主键由于InnoDB的聚集索引，其如果没有指定主键，就会自动生成主键。MyISAM支持没有主键的表存在。 外键为了解决复杂逻辑的依赖，我们需要外键。比如高考成绩的录入，必须归属于某位同学，我们就需要高考成绩数据库里有准考证号的外键。 InnoDB支持，MyISAM不支持。 索引为了优化查询的速度，进行排序和匹配查找，我们需要索引。比如所有人的姓名从a-z首字母进行顺序存储，当我们查找zhangsan或者第44位的时候就可以很快的定位到我们想要的位置进行查找。 InnoDB是聚集索引，数据和主键的聚集索引绑定在一起，通过主键索引效率很高。如果通过其他列的辅助索引来进行查找，需要先查找到聚集索引，再查询到所有数据，需要两次查询。 MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据的指针。 从InnoDB 1.2.x版本，MySQL5.6版本后，两者都支持全文索引。 auto_increment对于自增数的字段，InnoDB要求必须有只有该字段的索引。但MyISAM可以将该字段与其他字段组成联合索引。 表行数很常见的需求是看表中有多少条数据，此时我们需要select count(*) from table_name。 InnoDB不保存表行数，需要进行全表扫描。MyISAM用一个变量保存，直接读取该值，更快。当时当带有where查询的时候，两者一样。 存储数据库的文件都是需要在磁盘中进行存储，当应用需要时再读取到内存中。一般包含数据文件、索引文件。 InnoDB分为： .frm表结构文件.ibdata1共享表空间.ibd表独占空间.redo日志文件MyISAM分为三个文件： .frm存储表定义.MYD存储表数据.MYI存储表索引执行速度如果你的操作是大量的查询操作，如SELECT，使用MyISAM性能会更好。如果大部分是删除和更改的操作，使用InnoDB。 delete调用delete from table时，MyISAM会直接重建表，InnoDB会一行一行的删除，但是可以用truncate table代替。参考: mysql清空表数据的两种方式和区别。 锁MyISAM仅支持表锁，每次操作锁定整张表。InnoDB支持行锁，每次操作锁住最小数量的行数据。 表锁相比于行锁消耗的资源更少，且不会出现死锁，但同时并发性能差。行锁消耗更多的资源，速度较慢，且可能发生死锁，但是因为锁定的粒度小、数据少，并发性能好。如果InnoDB的一条语句无法确定要扫描的范围，也会锁定整张表。 当行锁发生死锁的时候，会计算每个事务影响的行数，然后回滚行数较少的事务。 数据恢复MyISAM崩溃后无法快速的安全恢复。InnoDB有一套完善的恢复机制。 数据缓存MyISAM仅缓存索引数据，通过索引查询数据。InnoDB不仅缓存索引数据，同时缓存数据信息，将数据按页读取到缓存池，按LRU（Latest Rare Use 最近最少使用）算法来进行更新。 如何选择存储引擎创建表的语句都是相同的，只有最后的type来指定存储引擎。 MyISAM大量查询总count查询频繁，插入不频繁没有事务操作InnoDB需要高可用性，或者需要事务表更新频繁参考资料MySQL InnoDB索引原理和算法：https://segmentfault.com/a/11...《MySQL技术内幕 InnoDB存储引擎》 1.3节mysql清空表数据的两种方式和区别：https://segmentfault.com/a/11...Mysql 中 MyISAM 和 InnoDB 的区别有哪些？：https://www.zhihu.com/questio...MySQL存储引擎MyISAM与InnoDB区别总结整理：https://blog.csdn.net/xlgen15...MySQL InnoDB的存储文件：https://blog.csdn.net/chenjia...

查看mysql版本mysql> select version();+------------+| version() |+------------+| 5.6.32-log |+------------+1 row in set (0.00 sec)或者：mysql> status;--------------mysql Ver 14.14 Distrib 5.6.32, for Win32 (AMD64)Connection id: 9Current database:Current user: root@localhostSSL: Not in useUsing delimiter: ;Server version: 5.6.32-log MySQL Community Server (GPL)Protocol version: 10Connection: localhost via TCP/IPServer characterset: latin1Db characterset: latin1Client characterset: gbkConn. characterset: gbkTCP port: 3307Uptime: 4 hours 7 min 11 secThreads: 4 Questions: 126 Slow queries: 0 Opens: 73 Flush tables: 1 Open tables: 66 Queries per second avg: 0.008查看MySQL存储引擎show enginesEngineSupportCommentTransactionsXASavepointsMyISAMYESMyISAM storage engineNONONOMRG_MYISAMYESCollection of identical MyISAM tablesNONONOCSVYESCSV storage engineNONONOBLACKHOLEYES/dev/null storage engine (anything you write to it disappears)NONONOMEMORYYESHash based, stored in memory, useful for temporary tablesNONONOFEDERATEDNOFederated MySQL storage engine(NULL)(NULL)(NULL)ARCHIVEYESArchive storage engineNONONOInnoDBDEFAULTSupports transactions, row-level locking, and foreign keysYESYESYESPERFORMANCE_SCHEMAYESPerformance SchemaNONONOSupport：DEFAULT表示MYSQL数据库默认的存储引擎，其它存储引擎就是你可以选择的。 如上表，InnoDB是默认的存储引擎 ...

数据库文件基础.frm文件: 只存储表的结构信息，frm文件跟数据库存储引擎无关，这也就是说，它和表的索引、数据都无关。MyISAM引擎.myd文件: 数据文件.myi文件：索引文件Innodb引擎ib_logfile0和ib_logfile1：重做日志文件,两个文件一模一样，之所以存在两个是，为了避免一个文件损坏后，而且MySQL crash之后，innodb无法恢复数据。ibdata1:数据文件根据《MySQL技术内幕》一书的介绍，innodb存在表空间的概念，是以共享表空间的格式，将数据都存入到一个文件中。介绍ibdata1，ibdata1是与存储引擎相关的，只在innodb存储引擎下出现，而且是mysql-server中所有innodb存储引擎的表，不分数据库都存储到ibdata1中，当然这个文件中还会存储其他信息，如索引等。当在MySQL中开启innodb_file_per_table后，那么将会在每个数据库对应的文件夹下，每张表将有存在两个对应文件，一个是.frm，另外一个是.ibd文件。这种情况成为私有表空间。但是这个时候共享表空间ibdata1，仍然是存在的。使用数据库文件恢复数据yum安装的mysql,数据目录是/var/lib/mysql，可以通过/etc/my.cnf查看。 MyISAM引擎表恢复数据直接备份.frm，.myd，.myi文件，然后再复制粘贴到目标数据库文件夹即可。 Innodb引擎表恢复数据如果是系统表空间的情况，一个笨办法就是建立一个新的mysql环境，将旧环境中的ibdata1文件和需要恢复的aa,bb等数据库文件夹复制到新mysql数据目录。然后重启mysqld守护进程就行。 systemctl restart mysqld下面是恢复的rbac数据库user表的数据 如果是私有表空间的话，虽然没实验过，但我猜想，直接复制.frm文件和.ibd文件到目标数据库即可。 参考文章利用数据库文件恢复MySQL数据

我们已经讲过了MySQL InnoDB索引原理和算法，这里来说下如何管理和使用B+树索引以及一些新的特性。 B+ 树索引的管理我们在InnoDB引擎中常用的索引基本都是B+ 树索引。 创建和删除索引它的创建和删除有两种方法： # 方式一：alter table, 此时index和key均可以，如果要求所有值均不重复，加上uniquealter table tbl_name add [unique] index|key index_name (index_col_name,...);alter table tbl_name drop index|key index_name;# 方式二：create/drop index, 此时只能用indexcreate index index_name on tbl_name (index_col_name,...);drop index index_name on tbl_name;修改索引MySQL没有提供修改索引的命令，我们一般先删掉索引，再重建同名索引达到修改的目标。 查看索引我们在查看数据表描述时可以看到都有哪些索引，有三种方法： # 方法一：查看创建表的语句mysql> show create table serviceHost;| Table | Create Table | t | CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(20) DEFAULT NULL, `b` varchar(20) DEFAULT NULL, `c` varchar(20) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, `e` varchar(20) DEFAULT NULL, `f` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `a` (`a`), KEY `idx_b` (`b`), KEY `idex_cde` (`c`,`d`,`e`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |1 row in set (0.00 sec)可以看到该表中有4个索引，主键集合索引(id)，唯一辅助索引(a)，单列辅助索引(b)，组合辅助索引(c,d,e)。 ...

也许你经常用MySQL，也会经常用索引，但是对索引的原理和高级功能却并不知道，我们在这里一起学习下。 InnoDB存储索引在数据库中，如果索引太多，应用程序的性能可能会受到影响；如果索引太少，又会对查询性能产生影响。所以，我们要追求两者的一个平衡点，足够多的索引带来查询性能提高，又不因为索引过多导致修改数据等操作时负载过高。 InnoDB支持3种常见索引： 哈希索引B+ 树索引全文索引我们接下来要详细讲解的就是B+ 树索引和全文索引。 哈希索引InnoDB存储引擎支持的哈希索引是自适应的，会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。这块内容我们先不展开说，后续补充。 B+ 树索引B+ 树索引是目前关系型数据库系统中查找最为常用和有效的索引，其构造类似于二叉树，根据键值对快速找到数据。B+ 树（balance+ tree）由B树（banlance tree 平衡二叉树）和索引顺序访问方法（ISAM: Index Sequence Access Method）演化而来，这几个都是经典的数据结构。而MyISAM引擎最初也是参考ISAM数据结构设计的。 基础数据结构想要了解B+ 树数据结构，我们先了解一些基础的知识。 二分查找法又称为折半查找法，指的是将数据顺序排列，通过每次和中间值比较，跳跃式查找，每次缩减一半的范围，快速找到目标的算法。其算法复杂度为log2(n)，比顺序查找要快上一些。 如图所示，从有序列表中查找48，只需要3步： 详细的算法可以参考二分查找算法。 二叉查找树二叉查找树的定义是在一个二叉树中，左子树的值总是小于根键值，根键值总是小于右子树的值。在我们查找时，每次都从根开始查找，根据比较的结果来判断继续查找左子树还是右子树。其查找的方法非常类似于二分查找法。 平衡二叉树二叉查找树的定义非常宽泛，可以任意构造，但是在极端情况下查询的效率和顺序查找一样，如只有左子树的二叉查找树。 若想构造一个性能最大的二叉查找树，就需要该树是平衡的，即平衡二叉树（由于其发明者为G. M. Adelson-Velsky 和 Evgenii Landis，又被称为AVL树）。其定义为必须满足任何节点的两个子树的高度最大差为1的二叉查找树。平衡二叉树相对结构较优，而最好的性能需要建立一个最优二叉树，但由于维护该树代价高，因此一般平衡二叉树即可。 平衡二叉树查询速度很快，但在树发生变更时，需要通过一次或多次左旋和右旋来达到树新的平衡。这里不发散讲。 B+ 树了解了基础的数据结构后，我们来看下B+ 树的实现，其定义十分复杂，目标是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在该树中，所有的记录都按键值的大小放在同一层的叶子节点上，各叶子节点之间有指针进行连接（非连续存储），形成一个双向链表。索引节点按照平衡树的方式构造，并存在指针指向具体的叶子节点，进行快速查找。 下面的B+ 树为数据较少时，此时高度为2，每页固定存放4条记录，扇出固定为5（图上灰色部分）。叶子节点存放多条数据，是为了降低树的高度，进行快速查找。 当我们插入28、70、95 3条数据后，B+ 树由于数据满了，需要进行页的拆分。此时高度变为3，每页依然是4条记录，双向链表未画出但是依然是存在的，现在可以看出来是一个平衡二叉树的雏形了。 InnoDB的B+ 树索引InnoDB的B+ 树索引的特点是高扇出性，因此一般树的高度为2~4层，这样我们在查找一条记录时只用I/O 2~4次。当前机械硬盘每秒至少100次I/O/s，因此查询时间只需0.02~0.04s。 数据库中的B+ 树索引分为聚集索引（clustered index）和辅助索引（secondary index）。它们的区别是叶子节点存放的是否为一整行的完整数据。 聚集索引聚集索引就是按照每张表的主键（唯一）构造一棵B+ 树，同时叶子节点存放整行的完整数据，因此将叶子节点称为数据页。由于定义了数据的逻辑顺序，聚集索引也能快速的进行范围类型的查询。 聚集索引的叶子节点按照逻辑顺序连续存储，叶子节点内部物理上连续存储，作为最小单元，叶子节点间通过双向指针连接，物理存储上不连续，逻辑存储上连续。 聚集索引能够针对主键进行快速的排序查找和范围查找，由于是双向链表，因此在逆序查找时也非常快。 我们可以通过explain命令来分析MySQL数据库的执行计划： # 查看表的定义，可以看到id为主键，name为普通列mysql> show create table dimensionsConf;| Table | Create Table | dimensionsConf | CREATE TABLE `dimensionsConf` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `remark` varchar(1024) NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `fullindex_remark` (`remark`)) ENGINE=InnoDB AUTO_INCREMENT=178 DEFAULT CHARSET=utf8 |1 row in set (0.00 sec)# 先测试一个非主键的name属性排序并查找，可以看到没有使用到任何索引，且需要filesort（文件排序），这里的rows为输出行数的预估值mysql> explain select * from dimensionsConf order by name limit 10\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: dimensionsConf type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 57 Extra: Using filesort1 row in set (0.00 sec)# 再测试主键id的排序并查找，此时使用主键索引，在执行计划中没有了filesort操作，这就是聚集索引带来的优化mysql> explain select * from dimensionsConf order by id limit 10\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: dimensionsConf type: indexpossible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 10 Extra: NULL1 row in set (0.00 sec)# 再查找根据主键id的范围查找，此时直接根据叶子节点的上层节点就可以快速得到范围，然后读取数据mysql> explain select * from dimensionsConf where id>10 and id<10000\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: dimensionsConf type: rangepossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 56 Extra: Using where1 row in set (0.00 sec)辅助索引辅助索引又称非聚集索引，其叶子节点不包含行记录的全部数据，而是包含一个书签（bookmark），该书签指向对应行数据的聚集索引，告诉InnoDB存储引擎去哪里查找具体的行数据。辅助索引与聚集索引的关系就是结构相似、独立存在，但辅助索引查找非索引数据需要依赖于聚集索引来查找。 ...

原文链接：MySQL InnoDB 存储引擎探秘在MySQL中InnoDB属于存储引擎层，并以插件的形式集成在数据库中。从MySQL5.5.8开始，InnoDB成为其默认的存储引擎。InnoDB存储引擎支持事务、其设计目标主要是面向OLTP的应用，主要特点有：支持事务、行锁设计支持高并发、外键支持、自动崩溃恢复、聚簇索引的方式组织表结构等。体系架构InnoDB存储引擎是由内存池、后台线程、磁盘存储三大部分组成。线程InnoDB 使用的是多线程模型, 其后台有多个不同的线程负责处理不同的任务Master ThreadMaster Thread是最核心的一个后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。包括脏页刷新、合并插入缓冲、UNDO页的回收等。IO Thread在 InnoDB 存储引擎中大量使用了异步IO(Async IO)来处理写IO请求, IO Thread的工作主要是负责这些 IO 请求的回调。Purge Thread事务提交后，其所使用的undo log可能不再需要，因此需要Purge Thread来回收已经分配并使用的UNDO页。InnoDB支持多个Purge Thread, 这样做可以加快UNDO页的回收，提高CPU的使用率以及提升存储引擎的性能。Page Cleaner ThreadPage Cleaner Thread的作用是取代Master Thread中脏页刷新的操作，其目的是减轻原Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。内存InnoDB 存储引擎内存的结构缓冲池InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。但是由于CPU速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池记录来提高数据库的的整体性能。缓冲池其实就是通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库进行读取操作时，首先将磁盘中的页放入缓冲池中，下次再读取相同页时，首先从缓冲池中获取该页数据，起到高速缓存的作用。数据的修改操作，则首先修改在缓冲池中的页数据，然后使用一种称为Checkpoint的机制刷新到磁盘上。缓冲池的大小直接影响数据库的整体性能，对于InnoDB存储引擎而言,缓冲池配置通过参数 innodb_buffer_pool_size 来设置。使用 SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’ 命令可查看缓冲池配置：mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’ \G*************************** 1. row ***************************Variable_name: innodb_buffer_pool_size Value: 1342177281 row in set (0.01 sec)缓冲池中缓存的数据页类型有: 索引页、undo页、插入缓冲、自适应哈希索引、InnoDB锁信息、数据字典信息等，索引页和数据页占缓冲池很大的一部分。重做日志缓冲缓冲池中的页数据比磁盘要新时，需要将新数据刷新到磁盘中。InnoDB采用Write Ahead Log策略来刷新数据，即当事务提交时，先写入重做日志缓冲，重做日志缓冲会按一定频率刷新到重置日志文件中，然后脏页会根据checkpoint机制刷新到磁盘。重做日志缓冲不需要设置很大，通常情况下8M能满足大部分的应用场景。重做日志支持一下三种情况触发刷新：Master Thread每一秒将重做日志缓冲刷新到重做日志文件每次事务提交时将重做日志缓冲刷新到重做日志文件当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件额外内存池在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆的方式进行的。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。锁InnoDB支持的锁有：共享锁和排它锁意向锁记录锁间隙锁自增锁共享锁和排他锁InnoDB引擎实现了两种标准的行级锁，共享锁(shared (S) locks) 和排他锁 (exclusive (X) locks)。共享锁允许一个占有锁的事务去读取一行数据，排它锁则允许事务对某一行记录进行写操作。如果一个事务持有了一个共享锁，其他事务仍然可以获取这行记录的共享锁，但不能获取到这行记录的排它锁。当一个事务获取到了某一行的排它锁，则其他事务将无法再获取这行记录的共享锁和排它锁。意向锁在InnoDB中，意向锁是一种表级锁，分为共享锁和排他锁：意向共享锁：将要去获取某一行的共享锁意向排它锁：将要去获取某一行的排它锁事务在获取共享/排它锁之前必须先获取意向共享/排它锁，意向锁不会阻塞其他任何对表的操作，他只是告诉其他事务他将要去获取某一行的共享锁或者排他锁。记录锁记录是是作用在索引上的一种锁，他锁住的是某一条记录的索引而非记录本身，如果当前表没有索引那么InnoDB将会为其创建一个隐藏的聚集索引，而Record Locks将会锁住这个隐藏的聚集索引。间隙锁间隙锁和记录锁一样也是作用在索引上，不同的是记录锁只作用于一条索引记录而间隙锁可以锁住一个范围内的索引。间隙锁在InnoDB的唯一作用就是防止其他事务的插入操作，以此防止幻读的发生。自增锁自增锁是一种特殊的表级锁，他只作用在包含自增列的插入操作时。当一个事务正在插入一条数据时，其他的任何事务都必须等待整个事务完成插入操作，在取获取锁来执行插入操作。事务ACID事务是数据库作为OLTP最为重要的特性，说起事务不得不提起ACID四个基本特性：原子性(Atomicity) ：事务最小工作单元，要么全成功，要么全失败一致性(Consistency)： 事务开始和结束后，数据库的完整性不会被破坏隔离性(Isolation) ：不同事务之间互不影响，四种隔离级别为RU（读未提交）、RC（读已提交）、RR（可重复读）、SERIALIZABLE （串行化）持久性(Durability) ：事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失InnoDB的原子性、持久性和一致性主要是通过Redo Log、Undo Log和Force Log at Commit机制机制来完成的。Redo Log用于在崩溃时恢复数据，Undo Log用于对事务的影响进行撤销，也可以用于多版本控制。而Force Log at Commit机制保证事务提交后Redo Log日志都已经持久化。隔离性则是由锁和MVCC来保证的。隔离级别在MySQL中，事务有4种隔离级别，分别是：Read Uncommitted 未提交读Read Committed 已提交读Repeatable Read 可重复读Serializable 可串行化在理解四种隔离级别之前，我们需要先了解另外三个名词：脏读 a事务会读取到b事务还未提交的数据，但是b事务由于某种原因进行回滚操作，这样，a事务读取的数据是不可用的，进而会造成一些异常结果。不可重复读 a事务周期内对某一数据多次查询，同时这些数据在b事务中进行了update或delete操作。那么a事务每次查询出来的结果可能都不一样。幻读 幻读的结果其实和不可重复读是一样的表现，差异就在于不可重复读主要是针对其他事务进行了编辑(update)和删除(delete)操作。而幻读主要是针对插入(insert)操作。也就是在一个事务生命周期内，会查询到另外一个事务新插入的数据。Read uncommitted 未提交读未提交读，这种情况下，一个事务a可以看到另一个事务b未提交的数据，如果此时事务b发生回滚，那么事务a拿到的就是脏数据，这也就是脏读的含义。此隔离级别在MySQL InnoDB一般不推荐使用。Read Committed 已提交读已提交读，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。解决了脏读问题，但是存在幻读现象。Repeatable Read 可重复读可重复读，该级别保证在同一事务中多次读取同样记录的结果是一致的，在InnoDB存储引擎中同时解决了幻读和不可重复读问题。InnoDB引擎通过使用Next-Key Lock解决了幻读的问题。Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。Serializable 可串行化Serializable 是最高的隔离级别，它通过强制事务串行执行，避免了幻读的问题，但是 Serializable 会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题,因此并发度急剧下降，在MySQL InnoDB同样不被建议使用。开启事务BEGIN、BEGIN WORK、START TRANSACTION 执行BEGIN命令不会真正在引擎层开启新事务，仅仅是为当前线程设定标记，表示为显式开启的事务。START TRANSACTION READ ONLY 开启只读事务，当MySQL Server接收到任何数据更改的SQL时，都会直接拒绝修改并返回错误，此错我不会进入引擎层。START TRANSACTION READ WRITE 允许super用户在当前线程只读状态为true的情况下启动读写事务。START TRANSACTION WITH CONSISTENT SNAPSHOT 开启事务会进入引擎层，并开启一个readview。只有在RR隔离级别下，这种操作才有效，否则会报错。Undo log在数据进行修改时会记录相应的undo日志，如果事务失败或者回滚，可以借助记录的undo日志进行回滚。Undo log是逻辑日志，记录更改前的数据镜像。在修改时如果同时需要读取当前数据的时候，它可以根据版本信息分析出该行记录以前版本的数据。另外Undo log也会产生重做日志，因为Undo log也要进行持久化保护。事务提交使用全局事务ID产生器生成事务NO，将当前连接的事务指针(trx_t)添加到全局提交事务链表(trx_serial_list)中标记undo，如果这个事务只使用了一个UndoPage且使用量小于3/4个Page，则把这个Page标记为TRX_UNDO_CACHED，如果不满足且是insert undo则标记为TRX_UNDO_TO_FREE，否则undo为update undo则标记为TRX_UNDO_TO_PURGE。标记为TRX_UNDO_CACHED的undo会被引擎回收。把update undo放入所在undo segment的history list，并递增rseg_history_len(全局)。同时更新Page上的TRX_UNDO_TRX_NO, 如果删除了数据，则重置delete_mark把undate undo从update_undo_list中删除，如果被标记为TRX_UNDO_CACHED，则加入到update_undo_cached队列中mtr_commit(日志undo/redo写入公共缓冲区)，至此，在文件层次事务提交。这个时候即使crash，重启后依然能保证事务是被提交的。接下来要做的是内存数据状态的更新(trx_commit_in_memory)只读事务只需要把readview从全局readview链表中移除，然后重置trx_t结构体里面的信息即可。读写事务首先需要是设置事务状态为TRX_STATE_COMMITTED_IN_MEMORY，释放所有行锁并且将trx_t从rw_trx_list中移除，readview从全局readview链表中移除。如果有insert undo则在这里移除，如果有update undo则唤醒Purge线程进行垃圾清理，最后重置trx_t里的信息，便于下一个事务使用回滚如果是只读事务，则直接返回判断当前是回滚整个事务还是部分事务，如果是部分事务，则记录下需要保留多少个Undo log，多余的全进行回滚从update undo和insert undo中找出最后一条undo，从这条undo开始回滚如果是update undo则将标记为删除的记录清理标记，更新过的数据回滚到最老的版本。如果是insert undo则直接删除聚集索引和二级索引如果所有undo都已经被回滚或者回滚到了指定的undo则停止，把Undo log删除索引InnoDB引擎使用B+树作为索引结构，主键索引的叶子节点data域保存了完整的字段数据，非主键索引的叶子节点保存了指向主键的值数据。上图是 InnoDB 主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录，这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键，如果没有显式指定，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。结尾对于MySQL InnoDB的诸多特性，本文只介绍了很小的一部分，感兴趣的同学可阅读 《MySQL技术内幕：InnoDB存储引擎》了解更多相关知识。原文链接：MySQL InnoDB 存储引擎探秘扫码关注微信公众号: Learn2Code ...

说明本文绝大部分内容来源《MySQL技术内幕：InnoDB存储引擎》一书，部分图片来源网络。#我是搬运工#InnoDB 体系结构后台线程InnoDB存储引擎是多线程模型，其后台有多个不同的后台线程，负责处理不同的任务。Master ThreadMaster Thread 主要负责将缓存池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERT BUFFER）、UNDO页的回收。IO ThreadIO Thread 主要负责 Async IO 请求的回调处理，包含 write、read、insert buffer 和 log IO thread。Purge ThreadPurge Thread 负责回收已经使用并分配的 undo 页，减轻 Master Thread 的工作。Page Cleaner ThreadPage Cleaner Thread 作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成，减轻 Master Thread 的工作及对于用户查询线程的阻塞。内存缓冲区一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响；读取数据时，首先将从磁盘读到的数据存放在缓冲池中，下一次读取直接从缓冲池中取。更新数据时，先更显缓冲池的数据，然后通过后台线程定期将有过更新的缓冲数据刷新到磁盘。从而减少磁盘IO的读写。LRU List、Free List 和 Flush List数据库缓冲池通过 LRU （Last Recent Used） 算法管理，LRU List 用来管理已经读取的页，数据库启动时，LRU List 为空列表，没有任何的页。此时页都存放在 Free List 中，当需要从缓冲池中分页时，首先从 Free List 中查找是否有可用的空闲页，若有空闲页则将该页从 Free 列表中删除并能够放入到 LRU List 中，否则淘汰 LRU List 中末尾的页。在 LRU List 中的页被修改后，称该页为脏页（dirty page）。脏页存储于 Flush List，表示缓冲池中的页与磁盘页不一致，等待被调度刷新。脏页同时存在于 Flush List 与 LRU List 中。重做日志缓冲 redo buffer cacheInnoDB 将重做日志首先写入 redo buffer cache，之后通过一定频率写入到重做日志（redo logo）中。redo buffer cache 不需要设置太大，重做日志缓冲在一下情况下被刷入到重做日志文件中：(1) Master Thread 每一秒将重做日志缓冲刷到重做日志文件(2) 每个事务提交时会将重做日志缓冲刷新到重做日志文件(3) 当重做日志缓冲池剩余空间小于50%时，重做日志缓冲刷新到重做日志额外的内存池InnoDB 对内存的管理是通过一种称为内存堆的方式进行的，对一些数据结构进行内存分配时，需要从额外的内存池中申请，当该区域不够时，会从缓冲池中进行申请。InnoDB 关键特性插入缓冲（insert buffer）Insert Buffer对于【非聚集索引】的更新或插入操作，不是直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入，否则先放入到一个 Insert Buffer 中。再以一定频率和情况进行 Insert Buffer 和辅助索引页子节点的merge操作，合并插入操作，提高非聚集索引的插入性能。Change BufferInsert Buffer 的升级，InnoDb 1.0.x 版本开始引入，同样适用对象为非唯一的辅助索引。可以对 DML 操作进行缓冲：insert、delete、update。两次写（double write）double write 带给 InnoDB 存储引擎的是数据页的可靠性。当数据库发生宕机时，可能InnoDB存储引擎正在写入某个页到列表中，而这个页只写了一部分，，比如16KB的页，只写了4KB，之后发生宕机，此时次出现【部分写失效】（页断裂）的情况，InnoDB 通过 double write 解决出现这种情况时造成的数据丢失并且无法恢复的问题。double write 工作流程：脏页刷新时，先拷贝至内存的 double write buffer，从缓冲区分两次接入磁盘共享表空间红，顺序写，缓冲区中的脏页数据写入实际的各个表空间，离散写。页断裂数据恢复流程：通过页的 checksum，校验 double write 在磁盘中的数据块，通过 double write 缓冲区数据来修复。自适应哈希索引（Adaptive Hash Index）InnoDB 会监控对表上各索引页的查询。如果观察到建议哈希索引可以带来速度的提升，则建立哈希索引，称之为自适应哈希索引（AHI）。AHI 是通过缓冲池的 B+ 树构造来的，因此建立的速度非常快，而且不需要对整张表构建哈希索引，InnoDB 会根据访问频率和模式来自动创建自适应哈希索引，无需人为设置干预。自适应哈希索引只适用于等值查询，比如 where smsId = ‘XXXXXX’，不支持范围查找。异步 IO（Asynchronous IO）InnoDB 采用异步IO（AIO）的方式来处理磁盘操作，进而提高对磁盘的操作性能。InnoDB 存储引擎中，read ahead 方式的读取是通过 AIO 完成，脏页的刷新，即磁盘的写入操作也是由 AIO 完成。刷新临接页（Flush Neighbor Page）当刷新一个脏页到磁盘时，InnoDB 会检测该页所在区的所有页，如果是脏页，则一起进行刷新。通过 AIO 合并多个 IO 写入，减少磁盘的 IO，但是可能造成将不怎么脏的页的磁盘写入，对于 SSD 磁盘，本身有着较高的 IOPS，则建议关闭该特性，InnoDB 1.2.x 版本提供参数 innodb_flush_neighbors，设置为 0 可关闭该特性。而对于普通磁盘，建议开启。Checkpoint 技术Checkpoint 技术的目的是解决以下问题：缩短数据库的恢复时间缓冲池不够用时，刷新脏页重做日志不可用时，刷新脏页Checkpoint 类型Sharp Checkpoint：发生在数据库关闭时，将所有脏页刷新到磁盘。Fuzzy Chckpoint：数据库运行时使用该方式进行页的刷新，刷新部分脏页进磁盘。InnoDB 中可能发生的 Fuzzy CheckpointMaster Thread CheckpointMaster Thread 以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页到磁盘，异步进行，用户查询线程不会阻塞。FLUSH_LRU_LIST CheckpointInnoDB 存储引擎需保证差不多 100 个空闲页可用，空闲也不足时，InnoDB 会将 LRU 列表尾端的页移除，如果尾端页存在脏页，则需要进行 Checkpoint。Async/Sync Flush Checkpoint重做日志不可用时进行，强制将一些页刷新回磁盘，从脏页列表中选取。根据不同的状态使用不同的刷新方式（同步或异步）。Dirty Page too much Checkpoint脏页数量太多，比如占据缓冲池比例大于 75% 时，强制进行刷新，比例可调。MySQL 文件参数文件告诉 MySQL 实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置。在默认情况下，MySQL 实例会按照一定的顺序在指定的位置进行读取，通过以下命令可以寻找：mysql –help | grep my.cnfMySQL 数据库中的参数分类动态参数：MySQL 运行期间中可以进行实时修改静态参数：MySQL 运行期间不可修改，只读日志文件记录了影响 MySQL 数据库的各种类型活动，常见的日志文件有：错误日志对 MySQL 的启动、运行、关闭过程进行记录，可根据错误日志定位问题。不仅记录错误信息，同时也记录一些告警信息或正确的信息。# 查看日志文件存储路径mysql> show variables like ’log_error’;+—————+——————————–+| Variable_name | Value |+—————+——————————–+| log_error | /data/mysql_data/data/r002.err |+—————+——————————–+慢查询日志帮助查找存在问题的 SQL 语句，记录执行时间超过某个时间长度的 SQL 语句。# 查询记录执行时间长度（秒）mysql> show variables like ’long_query_time’ \g;+—————–+———-+| Variable_name | Value |+—————–+———-+| long_query_time | 1.000000 |+—————–+———-+# 慢查询记录开关mysql> show variables like ’log_slow_queries’ \g;+——————+——-+| Variable_name | Value |+——————+——-+| log_slow_queries | ON |+——————+——-+慢查询日志文件可通过 mysqldumpslow 解析结果并查看。查询日志查询日志记录了所有对 MySQL 数据库请求的信息，无论这些请求是否得到了正确的执行。默认文件名为：主机名.log。二进制日志二进制日志（binary log）记录了对 MySQL 数据库执行更改的所有操作，不包含只读操作。二进制文件主要有以下几种作用：恢复：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复。复制：通过复制和执行二进制日志使一台远程的 MySQL 数据库与另一台 MySQL 数据库进行实时同步。审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。套接字文件在 UNIX 系统下本地连接 MySQL 可以采用 UNIX 域套接字方式。# 查看套接字文件地址mysql> show variables like ‘socket’;+—————+———————–+| Variable_name | Value |+—————+———————–+| socket | /var/mysql/mysql.sock |+—————+———————–+pid 文件在 MySQL 实例启动时，生成的进程ID会被写入到一个文件中，即 pid 文件。# 查看 pid 文件mysql> show variables like ‘pid_file’;+—————+——————————–+| Variable_name | Value |+—————+——————————–+| pid_file | /data/mysql_data/data/r002.pid |+—————+——————————–+表结构定义文件MySQL 数据的存储是根据表进行的，每个表都有与之对应的文件，是以 frm 为后缀名的文件，记录了表的表结构定义。InnoDB 存储引擎文件InnoDB 存储引擎独有的文件，与InnoDB 存储引擎密切相关，包括表空间文件、重做日志文件。表空间文件InnoDB 采用将存储的数据按表空间进行存放的设计。默认配置下有一个初始化大小为 10MB 的 ibdata1 文件，可自动增长。可以通过参数 innodb_data_file_path 对其进行设置。若设置了参数 innodb_file_per_table，则用户可以将每个基于 InnoDB 存储引擎的表产生一个独立表空间。独立表空间命名规则：表名.ibd# 查看是否开启独立表空间存储mysql> show variables like ‘innodb_file_per_table’;+———————–+——-+| Variable_name | Value |+———————–+——-+| innodb_file_per_table | ON |+———————–+——-+需要注意的是，这些单独的表空间文件仅存储该表的数据、索引和插入缓冲 BITMAP 等信息，其余信息还是存放在默认表空间中。下图显示了 InnoDB 存储引擎对于文件的存储方式：重做日志文件默认情况下，InnoDB 存储引擎的数据目录下会有两个名为 ib_logfile0 和 ib_logfile1 的文件，即 InnoDB 存储引擎的重做日志文件（redo log file），记录了对于 InnoDB 存储引擎的事务日志。当实例或介质存储失败时，例如由于主机断电导致实例失败，InnoDB 存储引擎会使用重做日志恢复到断电前的时刻，一次来保证数据的完整性。InnoDB 存储引擎会逐个循环写日志文件，当前写的日志文件被写满后，切到下一个日志文件，当下一个日志文件也被写满后，循环写前一个日志文件。日志文件数量及大小可配置（innodb_log_files_in_group、innodb_log_file_size）。关于重做日志文件的大小设置：(1) 不能设置太大，如果设置得很大，在恢复时可能需要很长的时间(2) 不能设置过小，可能会导致一个事务的日志需要多次切换重做日志文件，也会导致频繁的发生 async checkpoint，导致性能抖动。写入重做日志文件的操作不是直接写，而是先写入一个重做日志缓冲（redo log buffer）中，然后按照一定的顺序写入日志文件：MySQL 二进制文件MySQL 二进制文件记录 MySQL 数据库执行的更新操作。包含二进制日志文件和二进制索引文件。mysql-bin.indexmysql-bin.000001mysql-bin.000002mysql-bin.XXXXXXmysql-bin.000001 即为二进制日志文件，日志文件超过一定大小（根据 max_binlog_size 确定）时生成新的文件，后缀名 +1。binlog 相关的参数有如下：max_binlog_size # 单个 binlog 日志文件的最大值binlog_cache_size # 事务提交时的二进制日志写入的缓冲区大小sync_binlog # 表示每写入多少次缓冲区就同步至磁盘binlog-do-db # 表示需要写入哪些库的日志binlog-ignore-db # 表示忽略写入哪些库的日志bin_log_format # 表示二进制日志的记录格式，包含 STATEMENT、ROW、MIXED二进制日志记录格式STATEMENT：MySQL 5.1 之前的存储格式，5.1 版本以后可选格式，记录日志的逻辑 SQL 语句。ROW：二进制日志不再是简单的 SQL 语句，而是记录表行的更改情况，包含一行数据更改前与更改后列的内容。MIXED：默认采用 STATEMENT 格式进行二进制日志文件的记录，但是在一些情况下回使用 ROW 格式，如以下情况：1）表的存储引擎为 NDB2）使用了 UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT() 等不确定函数3）使用了 INSERT DELAY 语句4）使用了用户定义函数（UDF）5）使用了临时表STATEMENT 与 ROW 模式对比通常情况下 STATEMENT 因为只记录逻辑 SQL 语句，相关 ROW 模式下日志存储大小较小，特别是批量更新情况下，ROW 模式的日志文件远远大于 STATEMENT 模式下的日志文件，在做日志复制时，由于要传输 binlog 文件的内容，STATEMENT 模式的传输要优于 ROW 模式如果更新的 SQL 语句中存在不确定的函数调用等情况，用 STATEMENT 模式记录的 SQL 语句做同步会导致数据不一致，因此使用场景有所局限。二进制文件（binlog）与重做日志文件（redo log）二进制日志记录所有与 MySQL 数据库有关的日志记录，包括 InnoDB、MyISAM以及其他存储引擎的日志。而 InnoDB 存储引擎的重做日志只记录 InnoDB 存储引擎本身的事务日志。记录的内容不同，二进制日志文件记录的是关于一个事务的具体操作内容，即该日志的逻辑日志。而重做日志文件记录的是关于每个页（Page ）的更改的物理情况。写入时间不同，二进制日志文件仅在事务提交后进行写入，即只写磁盘一次，不论事务有多大。而在事务进行的过程中，却不断有重做日志条目被写入到重做日志文件中。MySQL 分区表InnoDB 逻辑存储结构从 InnoDB 存储引擎的逻辑存储结构看，所有数据都被逻辑的存放在表空间（tablespace），表空间又分为段（segment）、区（extend）、页（page）组成。而表的行数据则存储在页中，一个页存储多个行。分区MySQL 数据库在 5.1 版本时添加了对分区的支持。分区功能并不是在存储引擎层完成的，因此不仅 InnoDB 存储引擎支持分区，MyISAM、NDB 等都支持。也并不是所有存储引擎都支持分区，如 CSV、MERGE、FEDORATED 等就不支持。分区的作用对一部分 SQL 语句性能带来明显的提高，但是分区主要用于数据库高可用性的管理。分区类型RANGE 分区行数据基于属于一个给定连续区间的列值被放入分区。mysql> create table test_range (id int) ENGINE = INNODB PARTITION BY RANGE (id)( -> PARTITION P0 VALUES LESS THAN (10), -> PARTITION P1 VALUES LESS THAN (20));Query OK, 0 rows affected (0.03 sec)创建分区后，存储文件的独立表空间将根据分区存储，如下图所示：-rw-rw—- 1 _mysql _mysql 96K Jan 28 17:05 test_range#P#P0.ibd-rw-rw—- 1 _mysql _mysql 96K Jan 28 17:05 test_range#P#P1.ibd-rw-rw—- 1 _mysql _mysql 8.4K Jan 28 17:05 test_range.frm-rw-rw—- 1 _mysql _mysql 28B Jan 28 17:05 test_range.par对表添加数据时，会根据指定的列将数据存储到对应的分区存储文件中，如列对应的值不在对应范围内，将写入失败：mysql> insert into test_range values (30);ERROR 1526 (HY000): Table has no partition for value 30LIST 分区LIST 分区与 RANGE 分区非常相似，只是分区列的值是离散的，而非连续的。如下：mysql> create table test_list (a INT, b INT) ENGINE = INNODB PARTITION BY LIST (b)( -> PARTITION P0 VALUES IN (1, 3 ,5, 7, 9), -> PARTITION P1 VALUES IN (0, 2, 4, 6, 8));Query OK, 0 rows affected (0.03 sec)同样的，添加数据时，对应的列必须在指定的范围内，否则将写入失败：mysql> insert into test_list (a, b) values (1, 11);ERROR 1526 (HY000): Table has no partition for value 11HASH 分区HASH 分区的目的是将数据均匀的分布到预先定义的各个分区中，保证各分区的数据量大致一样。用于需要对将要进行哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量：mysql> create table test_hash (a INT, b DATETIME) ENGINE = INNODB -> PARTITION BY HASH (YEAR(b)) -> PARTITIONS 4;Query OK, 0 rows affected (0.03 sec)KEY 分区KEY 分区与 HASH 分区相似，HASH 分区使用用户定义的函数进行分区，KEY 分区使用 MySQL 数据库提供的函数进行分区。mysql> create table test_key (a INT, b DATETIME) ENGINE = INNODB -> PARTITION BY KEY (b) -> PARTITIONS 4;Query OK, 0 rows affected (0.04 sec)COLUMNS 分区以上四种区分方式均存在一样的分区条件：数据必须是整型（INT）的，如果不是整型，需要将对应的值转化为整型，如 YEAR()，TO_DAYS() 等函数。MySQL 5.5 版本开始支持 COLUMNS 分区，可视为 RANGE 分区和 LIST 分区的一种进化。可以直接使用非整形的数据进行分区，如所有的整型类型 SMALLINT、BIGINT，日期类型 DATE、DATETIME，字符串类型 CHAR、VARCHAR，相应的 FLOAT、DECIMAL、BLOB、TEXT、TIMESTAMP 不予支持。分区和分区性能数据库的应用分为两类：OLTP（在线事务处理）和 OLAP（在线分析处理）。对于 OLAP 的应用，分区的确可以很好地提高查询性能。体现在扫描表时不需要扫描全表，扫描单个分区时效率最高；同时也依赖于应用的处理以及分区方式，如不合理的分区，将带来巨大的性能下降。比如对主键进行 HASH 分区，查询的时候通过非主键字段匹配查询，则同样是全量数据扫描，但是由于分区的数量较多，会大量增加系统的 IO 调用。对于 OLTP 的应用，分区并不会明显的降低 B+ 树索引高度，一般的 B+ 树需要 2~3 次的磁盘 IO，分区并不能明显提升写入速度。但是设计不好的分区会带来严重的性能问题。MySQL 索引InnoDB 存储引擎支持以下几种常见索引：B+ 树索引哈希索引全文索引B+ 树B+ 树的概念在此不做介绍，B+ 树的操作演示地址：https://www.cs.usfca.edu/~gal…B+ 树索引MySQL 并不是通过 B+ 树索引直接找到数据行，而是找到数据行所在的页，将页加载到内存，最后查找到行数据。一个数据页包含多行数据。B+ 树索引包含数据页与索引页。数据页存放完整的行数据，索引页存放键值以及指向数据页的偏移量，而非完整的行记录。B+ 树索引分类聚集索引（Clustered Index）InnoDB 存储引擎是索引组织表，即表中数据按照主键顺序存放，聚集索引就是按照主键构造一颗 B+ 树，同时叶子节点存放表的行记录数据，也将聚集索引的叶子节点称为数据页。简而言之，数据是索引的一部分。MySQL 通过索引构造数据，所以一张数据表中只能有一个聚集索引。辅助索引（Secondary Index）也成为非聚集索引，叶子节点并不包含数据行的全部数据。叶子节点中的索引行中包含一个书签，该书签就是相应行数据的聚集索引键，因此通过非聚集索引查找行数据需要经过两级索引才能查找到具体的数据内容。比如，非主键索引查找行数据，先通过非主键索引查找到主键，再通过主键查找行数据。哈希索引MySQL 中的 HASH 索引为自适应的，无需人工干扰，MySQL 内部会针对查询业务自动创建 HASH 索引，以提高业务的查询效率。HASH 索引仅适用的等值匹配查询，对于范围查找无能为力。全文索引InnoDB 1.2.x 版本开始，InnoDB 存储引擎开始支持全文索引，通过倒排索引来实现。因为业务极少使用 MySQL 的全文索引，通常如果需要做全文搜索，可选择 Elasticsearch。Cardinality 值Cardinality 值对列创建索引的选择性提供了较好的参考，Cardinality 为一个预估值，非准确值，某一个列的 Cardinality 值代表该列在整张表中不重复值的数量。忽略业务因素以及数据类型，表中某个列是否适合创建索引，体现在该列所有的值是否相对分散，重复数据越少，相对来说越适合添加索引。因此，当某个列 Cardinality值/表行数 约接近 1，代表重复数据越少，为该列建索引的选择性便越高。InnoDB 存储引擎对于 Cardinality 的更新是非实时的，并且获取到的值为预估值，通过采样统计来获取该值。通常具体业务只需关心该值是否接近于表的行数，以判断某个列是否适合创建索引。MySQL 锁Innodb 存储引擎锁类型行级锁共享锁（S Lock）：允许事务读一行数据排他锁（X Lock）：允许事务删除或更新一行数据表级锁意向共享锁（IS Lock）：事务想要获得一张表中某几行的共享锁意向排他锁（IS Lock）：事务想要获得一张表中某几行的排他锁InnoDB 存储引擎中锁的兼容性： ISIXSXIS兼容兼容兼容不兼容IX兼容兼容不兼容不兼容S兼容不兼容兼容不兼容X不兼容不兼容不兼容不兼容如下图所示，当 InnoDB 需要做细粒度加锁时，比如对某一行加 X 锁，需要先对该行所在的表、页加 IX 锁。若其中任何一个部分导致等待，那么该操作需要等待粗粒度锁的完成。一致性非锁定读：Consistent Nonlocking ReadInnoDB 存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行 UPDATE 或 DELETE 操作，这时读取操作不会因此等待行上锁的释放。相应的，InnoDB 存储引擎会去读取行的一个快照数据。非锁定读机制极大的提高了数据库的并发性，在 InnoDB 存储引擎的默认设置下，读取不会占用和等待表上的锁。快照数据即当前行记录的历史版本，每行记录可能有多个版本，由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control，MVCC）。一致性锁定读：Consistent Locking Read在某些情况下，用户需要显示的对数据库读取操作进行加锁以保证数据逻辑的一致性。这要求数据库支持加锁语句，即使对于 SELECT 的支付操作。InnoDB 存储引擎对于 SELECT 语句支持两种一致性的锁定读操作:# 对读取的行记录添加一个 X 锁SELECT … FOR UPDATE;# 对读取的行记录添加一个 S 锁SELECT … LOCK IN SHARE MODE;锁的算法1）Record Lock：单个行记录上的锁# 锁定 id = 5 的行记录SELECT … FROM … WHERE id = 5 FOR UPDATE;2）Gap Lock：间隙锁，锁定一个范围，单不包含记录本身# 锁定 id < 5 的所有记录SELECT … FROM … WHERE id < 5 FOR UPDATE;3）Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身# 锁定 id <= 5 的所有记录SELECT … FROM … WHERE id <= 5 FOR UPDATE;幻像问题：Phantom ProblemPhantom Problem 是指同一事务下，连续执行两次同样的 SQL 语句可能导致不同的结果，第二次的 SQL 语句可能会返回之前不存在的行。比如：# 表 t 中存在 id 为 1,2,3,4 四条数据，事务隔离级别为 READ-COMMITTEDBEGIN;SELECT * FROM t WHERE id > 2; # 此时查询结果有id为 3，4 的记录# 此时其他线程增加一条数据 id = 5SELECT * FROM t WHERE id > 2; # 此时查询结果有id为 3，4，5 的记录上述查询结果，在一个事务中出现同一个查询返回不同的结果，违反了事务的隔离性，即当前事务能够看到其他事务的结果。InnoDB 存储引擎默认的事务隔离级别是 REPEATABLE READ，在该事务隔离级别下采用 Next Key Locking 的方式来加锁解决。同时应用也可以通过 InnoDB 存储引擎的 Next Key Locking 机制在应用层面实现唯一性的检查。例如：SELECT * FROM t WHERE col = xxx LOCK IN SHARE MODE;锁问题脏读脏读指一个事务可以读到另一个事务中未提交的修改数据，违反了数据库的隔离性。脏读发生的条件是事务隔离级别是 READ UNCOMMITTED，目前大部分数据库都至少设置成 READ COMMITTED。不可重复读不可重复读指在一个事务内多次读取同一数据集合，出现了不同的数据结果。不可重复读发生在事务隔离级别为 READ COMMITTED，事务 A 读取一个结果集，事务 B 同样读取到该结果集并对其进行修改，提交事务，事务 A 再次读取结果集时，两次结果不一致。一般情况下，不可重复的问题是可接受的，因为读取的是已经提交的数据，本身不会带来很大问题。InnoDB 存储引擎的隔离级别为 READ REPEATABLE 时，采用 Next Key Lock 算法，避免了不可重复读的现象。丢失更新一个事务的更新操作结果被另一个事务的更新操作结果所覆盖，从而导致数据的不一致。数据库层面可以阻止丢失更新问题的发生，但是应用中存在一个逻辑意义的丢失更新问题。例如，多个线程同时读取到某条数据，之后均对数据进行修改再更新库，此时会出现最后一个线程的更新结果覆盖了先执行的更新结果。应用层面可以通过对查询的数据进行加锁，如前文提到的一致性锁定读方式，对需要更新的数据进行加锁，其他线程即会出现阻塞串行等待。死锁死锁是指两个或两个以上的事务在执行过程中，因抢夺锁资源而造成的互相等待的现象。解决死锁的方式：1）超时当一个等待时间超过设置的某一阈值时，对该事务进行回滚，InnoDB 中通过参数 innodb_lock_wait_timeout 设置超时时间。超时处理机制简单，但不判断事务所占权重，比如一个事务更新的行非常多，回滚也需要占用更多的时间，同时与该事务抢占资源的事务可能仅更新少量数据，回滚该事务应当更合理。2） wait-for graph（等待图）死锁检测主动检测死锁，判断事务之间的等待状态是否存在闭环。若检测到存在死锁的情况，InnoDB 存储引擎选择回滚 undo 量最小的事务。锁升级锁升级指将当前锁的粒度降低。比如数据库把一个表的 1000 个行锁升级为一个页锁，或者将页锁升级为表锁。从而避免锁的开销。InnoDB 存储引擎根据页进行加锁，并采用位图方式， 开销由页的量决定，因此 InnoDB 引擎不会产生锁升级的问题。MySQL 事务事务的实现InndoDB 是事务的存储引擎，其通过 Forece Log at Commit 机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的 COMMIT 操作完成才算完成。这里的日志是指重做日志，由两部分组成，即 redo log 和 undo log。事务的 ACID 特性实现：隔离性，通过锁实现；原子性、一致性、持久性，通过数据库的 redo log 和 undo log 实现。redo log 与 undo logredo 和 undo 的作用都可以视为是一种恢复操作，redo 恢复提交事务修改的页操作，而 undo 回滚行记录到某个特定的版本，用来帮助事务回滚及 MVCC 的功能。因此两者记录的内容不同，redo 通常是物理日志，记录的是页的物理修改操作。redo log 基本上都是顺序写的，undo 是逻辑日志，根据每行记录进行记录。undo log 是需要进行随机读写的。redo重做日志用来实现事务的持久性，即事务 ACID 的 D。其由两部分组成：一是内存中的重做日志缓冲（redo log buffer），其是容易丢失的；二是重做日志文件（redo log file），其是持久的。为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB 存储引擎都需要调用一次 fsync 操作。因此磁盘的性能决定了事务提交的性能，也就是数据库的性能。参数 innodb_flush_log_at_trx_commit 用来控制重做日志刷新到磁盘的策略，其参数含义如下：1 -> 表示事务提交时必须调用一次 fsync0 -> 表示事务提交是不进行写入重做日志操作，这个操作尽在 master thread 中完成，而 master thread 中每 1 秒进行一次重做日志文件的 fsync 操作2 -> 表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行 fsync 操作。MySQL 默认参数为 1，保证最高的数据可靠性，为 0 或 2 时可以提供更好的事务性能，但是存在数据库宕机时数据丢失风险。undo重做日志记录了事务的行为，可以很好的通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就需要 undo。在对数据库进行修改时，InnoDB 存储引擎不但会产生 redo，还会产生一定量的 undo，这样如果执行的事务或语句由于某种原因失败了，又或者是用户主动 ROLLBACK 请求回滚，就可以利用 undo 进行数据回滚到修改之前的样子。purgedelete 和 update 操作并不直接删除原有的数据，执行 delete 语句时，受影响的数据被标记为逻辑删除，真正删除这行记录的操作在 purge 操作中完成。purge 用于最终完成 delete 和 update 操作。这样设计是因为 InnoDB 存储引擎支持 MVCC，所以记录不能再事务提交时立即进行处理。而是否可以删除该条记录通过 purge 来判断，若该行记录已不再被任务其他事务引用，那么就可以进行真正的 delete 操作。group commit若事务为非只读事务，则每次事务提交时需要进行一次 fsync 操作，以保证重做日志都已经写入磁盘。为了提高磁盘 fsync 的效率，当前数据库提供了 group commit 的操作，即一次 fsync 可以刷新确保多个事务日志被写入文件。对于 InnoDB 存储引擎来说，事务提交时会进行两个阶段的操作：1）修改内存中事务对应的信息，并且将日志写入重做日志缓冲2）调用 fsync 确保日志都从重做日志缓冲写入磁盘步骤 2）相对步骤 1）是一个较慢的过程，当有事务进行步骤 2）时，其他事务可以进行步骤 1）的操作，正在提交的事务完成提交操作后，再次进行步骤 2）时，可以将多个事务的重做日志通过一次 fsync 刷新到磁盘，这样就大大的减少了磁盘的压力，从而提高了数据库的整体性能。MySQL 备份与恢复备份分类根据不同的类型来划分：Hot Backup（热备）Cold Backup（冷备）Warm Backup（温备）按照备份后文件的内容划分：逻辑备份裸文件备份按照备份数据库的内容划分：完全备份增量备份日志备份冷备对于 InnoDB 存储引擎的冷备，只需要备份 MySQL 数据库的 frm 文件，共享表空间文件，独立表空间文件（*.ibd），重做日志文件。另外建议定期备份 MySQL 数据库的配置文件 my.cnf，有利于恢复的操作。冷备的优点：备份简单，只需要复制相关文件即可备份文件易于在不同操作系统，不同 MySQL 版本上进行恢复恢复相当简单，只需要把文件恢复到指定位置即可回复速度快，不需要执行任何 SQL 语句，不需要重建索引冷备的缺点：InnoDB 存储引擎冷备的文件通常比逻辑文件大很多冷备也不总是可以轻易的跨平台逻辑备份mysqldump用来完成转存数据库的备份及不同数据库之间的移植，如从 MySQL 低版本数据库升级到 MySQL 高版本数据库，又或者从 MySQL 移植到 Oracle，SQL Server 等。mysqldump [arguments] > file_namemysqldump –all-databases > dump.sqlmysqldump –databases db1 db2 db3 > dump.sqlSELECT … INTO OUTFILE逻辑备份方法，更准确的说是导出一张表中的数据。SELECT * INTO OUTFILE ‘/home/yw/a.txt’ FROM test;逻辑备份的恢复SOURCEmysqldump 的恢复操作简单，仅需执行导出的 SQL 语句即可。source /home/yw/dump.sqlLOAD DATA INFILE恢复通过 SELECT INTO OUTFILE 导出的数据LOAD DATA INTO TABLE test IGNORE 1 LINES INFILE ‘/home/yw/a.txt’二进制日志备份与恢复二进制日志非常关键，用户可以通过它完成 point-in-time 的恢复工作，MySQL 的 replication 同样需要二进制日志，在默认情况下并不开启二进制日志，要使用二进制日志必须启用它。InnoDB 存储引擎推荐的二进制日志的服务器配置如下：[mysqld]log-bin = mysql-binsync_binlog = 1innodb_support_xa = 1在备份二进制日志文件前，可以通过 FLUSH LOGS 命令生成一个新的二进制日志文件，然后备份之前的二进制日志。恢复二进制日志也非常简单，通过 mysqlbinlog 即可：mysqlbinlog [options] log_filemysqlbinlog binlog.0000001 | mysql -uroot -p test也可以先将二进制文件导出到一个文件，然后通过 source 进行导入：shell > mysqlbinlog binlog.0000001 > /home/yw/binlog.sql…mysql > source /home/yw/binlog.sql复制的工作原理复制（replication）是 MySQL 数据库提供的一种高可用高性能的解决方案，replication 的工作原理分为以下 3 个步骤：主服务器把数据更改记录到二进制日志（binlog）中从服务器把主服务器的二进制日志复制到自己的中继日志（relay log）中从服务器重做中继日志中的日志，把更改用到自己的数据库上，以达到最终一致性复制的工作原理如下图所示： ...

数据库事务隔离事务的介绍事务就是一组原子性的sql查询，或者说是一个独立的工作单元。简而言之，事务内的语句要么全部执行成功，要么全部执行失败。在Mysql中，事务支持是在引擎层实现的，但并不是所有的Mysql引擎都支持事务，比如MyISAM引擎就不支持事务，这也是MyISAM被InnoDB取代的重要原因之一。提到事务，我们肯定会想到ACID：原子性(Atomicity)一致性(Consistency)隔离性(Isolation)持久性(Durability)隔离级别当数据库中有多个事务同时执行时，就可能会出现脏读、不可重复读、幻读等问题，因为就有了事务隔离级别的概念。SQL标准正定义了四种隔离级别：READ UNCOMMITTED (未提交读)事务中的修改，即使还没有提交，对其他事务都是可见的。事务可以读取未提交的数据，也被称为脏读(Dirty Read)。READ COMMITTED(提交读)一个事务提交后，所做的变更才能被其他事务看到。这个级别也叫不可重复读，因为事务中执行2次相同的查询，可能得到的结果是不一样的。REPEATABLE READ(可重复读)一个事务执行的过程中，总是和这个事务在启动时看到的数据是一致的。当然在这个级别下，未提交的数据变更对其他事务也是不可见的。SERIALIZABLE(可串行化)对同一行记录，写和读都会加锁，当出现读写锁冲突时，后访问的事务必须等前一个事务执行完成才能继续执行，就会导致大量的超时和锁争用的问题。在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑为准。在可重复读这个隔离级别下，这个视图是事务开启的时候创建的，整个事务期间都用这个视图。在读提交的隔离级别下，这个视图是在sql语句开始执行的时候创建的。在读未提交的隔离级别下，直接返回记录上的最新值，没有视图概念。在串行化的隔离级别下，直接用加锁的方式避免并行访问。配置的方式是将启动参数transaction-isolation设置成想要的隔离级别。查看当前设置：mysql> show variables like ’transaction_isolation’;+———————–+—————–+| Variable_name | Value |+———————–+—————–+| transaction_isolation | REPEATABLE-READ |+———————–+—————–+1 row in set (0.00 sec)总之，存在即合理，不同的隔离级别适用于不同的场景，具体我们应该根据业务场景来决定。事务隔离的实现在Mysql中，实际上每条记录的更新同时也会记录一条回滚操作，记录上的最新值通过回滚操作，都可以得到前一个状态的值。系统会自动判断，当没有事务再需要回滚日志时，会删除回滚日志。为什么不建议使用长事务：长事务意味着系统里面会存在很老的事务视图，由于这些事务随时可以访问数据库里面的任何数据，所以这个事务提交之前，数据库里可能用到的回滚记录必须保留着，这就会占用大量的存储空间。同时长事务还占用锁资源，也可能拖垮整个库。事务启动的方式显式启动事务语句，begin或者start transaction,提交就是commit,回滚用rollback。set autocommit = 0,这个命令会将线程的自动提交关掉，意味着如果执行一个select 语句，这个事务就启动了，并且不会自动提交，直到你主动执行commit或者rollback，或者断开连接。个人建议还是通过第一种方式显式启动事务，避免长事务的发生。在 set autocommit = 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。查询长事务：下面语句是查询持续时间超过60s的事务mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;Empty set (0.00 sec)总结下来，我们在开发过程中，尽量少用长事务，如果无法避免，保证逻辑日志空间足够大，并且支持动态日志空间增长。监控Innodb_trx表，发现长事务报警。欢迎交流。参考资料《高性能Mysql》极客时间-Mysql实战45讲

事务是数据库最为重要的机制之一，凡是使用过数据库的人，都了解数据库的事务机制，也对ACID四个基本特性如数家珍。但是聊起事务或者ACID的底层实现原理，往往言之不详，不明所以。所以，今天我们就一起来分析和探讨InnoDB的事务机制，希望能建立起对事务底层实现原理的具体了解。 数据库事务具有ACID四大特性。ACID是以下4个词的缩写：原子性(atomicity) ：事务最小工作单元，要么全成功，要么全失败 。一致性(consistency)： 事务开始和结束后，数据库的完整性不会被破坏 。隔离性(isolation) ：不同事务之间互不影响，四种隔离级别为RU（读未提交）、RC（读已提交）、RR（可重复读）、SERIALIZABLE （串行化）。持久性(durability) ：事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失 。 下面，我们就以一个具体实例来介绍数据库事务的原理，并介绍InnoDB是如何实现ACID四大特性的。示例介绍 我们首先来看一下具体的示例。大家可以自己亲自试验一下，这样理解和记忆都会更加深刻。 首先，使用如下的SQL语句创建两张表，分别是goods和trade，代表货物和交易。并向goods表中插入一条记录，id为1的货物数量为10。CREATE TABLE goods (id INT, num INT, PRIMARY KEY(id));CREATE TABLE trade (id INT, goods_id INT, user_id INT, PRIMARY KEY(id));INSERT INTO goods VALUES(1, 10); 然后打开终端，连接数据库，开启会话一，先用BEGIN显示开启一个事务。会话一先将goods表中id为1的货物的数量减一，然后向trade表中添加一笔交易的记录，最后使用COMMIT显示提交事务。 而会话二则先查询goods表中id为1的货物数量，然后向trade表中添加一笔交易记录，接着更新goods表中id为1的货物的数量，最后使用ROLLBACK进行事务的回滚。其中，两个会话中执行的具体语句和先后顺序如下图所示。 这个示例可以体现数据库事务的很多特性，我们一一来介绍。首先会话一的操作2更新了id为1的货物的数量，但是会话二的操作5读出来的数量仍然是10，这体现了事务的隔离性，使用InnoDB的多版本控制机制实现。 会话二的操作7也要更新同种货物的数量，此时因为会话一的操作2已经更新了该货物的数量，InnoDB已经锁住了该记录的行锁，所以操作7会被阻塞，直到会话一COMMIT。但是会话一的操作4和会话二的操作7都是向trade表中插入记录，后者却不会因为前者而阻塞，因为二者插入的不是同一行记录。锁机制是一种常见的并发控制机制，它和多版本控制机制一起实现了InnoDB事务的隔离性，关于InnoDB锁相关的具体内容可以参考InnoDB锁的类型和状态查询和InnoDB行锁算法。 会话一事务最终使用COMMIT提交了事务而会话二事务则使用ROLLBACK回滚了整个事务，这体现了事务的原子性。即事务的一系列操作要么全部执行(COMMIT)，要么就全部不执行(ROLLBACK)，不存在只执行一部分的情况。InnoDB使用事务日志系统来实现事务的原子性。这里有的同学就会问了，如果中途连接断开或者Server Crash会怎么样。能怎么样，直接自动回滚呗。 一旦会话一使用COMMIT操作提交事务成功后，那么数据一定会被写入到数据库中并持久的存储起来，这体现了事务的持久性。InnoDB使用redo log机制来实现事务的持久性。 而事务的一致性比较难以理解，简单的讲在事务开始时，此时数据库有一种状态，这个状态是所有的MySQL对象处于一致的状态，例如数据库完整性约束正确，日志状态一致等。当事务提交后，这时数据库又有了一个新的状态，不同的数据，不同的索引，不同的日志等。但此时，约束，数据，索引，日志等MySQL各种状态还是要保持一致性。 也就是说数据库从一个一致性的状态，变到另一个一致性的状态。事务执行后，并没有破坏数据库的完整性约束。 下面我们就来详细讲解一下上述示例涉及的事务的ACID特性的具体实现原理。总结来说，事务的隔离性由多版本控制机制和锁实现，而原子性、一致性和持久性通过InnoDB的redo log、undo log和Force Log at Commit机制来实现。原子性，持久性和一致性 原子性，持久性和一致性主要是通过redo log、undo log和Force Log at Commit机制机制来完成的。redo log用于在崩溃时恢复数据，undo log用于对事务的影响进行撤销，也可以用于多版本控制。而Force Log at Commit机制保证事务提交后redo log日志都已经持久化。 开启一个事务后，用户可以使用COMMIT来提交，也可以用ROLLBACK来回滚。其中COMMIT或者ROLLBACK执行成功之后，数据一定是会被全部保存或者全部回滚到最初状态的，这也体现了事务的原子性。但是也会有很多的异常情况，比如说事务执行中途连接断开，或者是执行COMMIT或者ROLLBACK时发生错误，Server Crash等，此时数据库会自动进行回滚或者重启之后进行恢复。 我们先来看一下redo log的原理，redo log顾名思义，就是重做日志，每次数据库的SQL操作导致的数据变化它都会记录一下，具体来说，redo log是物理日志，记录的是数据库页的物理修改操作。如果数据发生了丢失，数据库可以根据redo log进行数据恢复。 InnoDB通过Force Log at Commit机制实现事务的持久性，即当事务COMMIT时，必须先将该事务的所有日志都写入到redo log文件进行持久化之后，COMMIT操作才算完成。 当事务的各种SQL操作执行时，即会在缓冲区中修改数据，也会将对应的redo log写入它所属的缓存。当事务执行COMMIT时，与该事务相关的redo log缓冲必须都全部刷新到磁盘中之后COMMIT才算执行成功。 redo log写入磁盘时，必须进行一次操作系统的fsync操作，防止redo log只是写入了操作系统的磁盘缓存中。参数innodb_flush_log_at_trx_commit可以控制redo log日志刷新到磁盘的策略，它的具体作用可以查阅InnoDB的磁盘文件及落盘机制 redo log全部写入磁盘后事务就算COMMIT成功了，但是此时事务修改的数据还在内存的缓冲区中，称其为脏页，这些数据会依据检查点(CheckPoint)机制择时刷新到磁盘中，然后删除相应的redo log，但是如果在这个过程中数据库Crash了，那么数据库重启时，会依据redo log file将那些还在内存中未更新到磁盘上的数据进行恢复。 数据库为了提高性能，数据页在内存修改后并不是每次都会刷到磁盘上。而是引入checkpoint机制，择时将数据页落盘，checkpoint记录之前的数据页保证一定落盘了，这样相关的redo log就没有用了(由于InnoDB redo log file循环使用，这时这部分日志就可以被覆盖)，checkpoint之后的数据页有可能落盘，也有可能没有落盘，所以checkpoint之后的redo log file在崩溃恢复的时候还是需要被使用的。InnoDB会依据脏页的刷新情况，定期推进checkpoint，从而减少数据库崩溃恢复的时间。检查点的信息在第一个日志文件的头部。 数据库崩溃重启后需要从redo log中把未落盘的脏页数据恢复出来，重新写入磁盘，保证用户的数据不丢失。当然，在崩溃恢复中还需要回滚没有提交的事务。由于回滚操作需要undo日志的支持，undo日志的完整性和可靠性需要redo日志来保证，所以崩溃恢复先做redo恢复数据，然后做undo回滚。 在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。 undo log的存储不同于redo log，它存放在数据库内部的一个特殊的段(segment)中，这个段称为回滚段。回滚段位于共享表空间中。undo段中的以undo page为更小的组织单位。undo page和存储数据库数据和索引的页类似。因为redo log是物理日志，记录的是数据库页的物理修改操作。所以undo log的写入也会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。如上图所示，表空间中有回滚段和叶节点段和非叶节点段，而三者都有对应的页结构。 我们再来总结一下数据库事务的整个流程，如下图所示。 事务进行过程中，每次sql语句执行，都会记录undo log和redo log，然后更新数据形成脏页，然后redo log按照时间或者空间等条件进行落盘，undo log和脏页按照checkpoint进行落盘，落盘后相应的redo log就可以删除了。此时，事务还未COMMIT，如果发生崩溃，则首先检查checkpoint记录，使用相应的redo log进行数据和undo log的恢复，然后查看undo log的状态发现事务尚未提交，然后就使用undo log进行事务回滚。事务执行COMMIT操作时，会将本事务相关的所有redo log都进行落盘，只有所有redo log落盘成功，才算COMMIT成功。然后内存中的数据脏页继续按照checkpoint进行落盘。如果此时发生了崩溃，则只使用redo log恢复数据。隔离性 InnoDB事务的隔离性主要通过多版本控制机制和锁机制实现，具体可以参考多版本控制，InnoDB锁的类型和状态查询和InnoDB行锁算法三篇文章。后记 本来想一篇文章将MySQL的事务机制讲明白，写完自己读了一遍，还是发现内容有些晦涩难懂，复杂的知识本来就是很难讲明白的，夫夷以近，则游者众；险以远，则至者少，希望读者以本文作为一篇指引性的文章，自己再去更加深入的地方去探秘。不过，能将复杂知识讲解的通俗简单也是一项很大的本领，文字和讲解能力还是需要提示的。Mysql探索(一):B-Tree索引数据库内部存储结构探索MySQL探秘(二)：SQL语句执行过程详解MySQL探秘(三):InnoDB的内存结构和特性MySQL探秘(四):InnoDB的磁盘文件及落盘机制MySQL探秘(五):InnoDB锁的类型和状态查询MySQL探秘(六):InnoDB一致性非锁定读参考MySQL · 引擎特性 · InnoDB 事务系统MySQL · 引擎特性 · InnoDB 崩溃恢复过程 ...