MySQL 基础

• DML 操作完全遵循 ACID 模型，支持事务，支持崩溃恢复，能够极大地保护用户的数据安全；
• 支持多版本并发控制，它会保存数据的旧版本信息，从而可以支持并发和事务的回滚；
• 支持行级锁，支持类似 Oracle 的一致性读的特性，从而可以承受高并发地访问；
• InnoDB 组织数据时默认按照主键进行聚簇，从而可以提高主键查找的效率。对于频繁访问的数据，InnoDB 还会为其建立哈希索引，从而提高等值查询的效率，这也称为自适应哈希索引；
• InnoDB 基于磁盘进行存储，所有存储记录按 页 的方式进行管理。为弥补 CPU 速度与磁盘速度之间的鸿沟，InnoDB 引用缓存池 (Buffer Pool) 来提高数据的整体性能。查询时，会将目标页读入缓存中；修改时，会先修改缓冲池中的页，然后再遵循 CheckPoint 机制将页刷回磁盘。所有缓存页通过最近最少使用原则 ( LRU ) 来进行定期清理。
• InnoDB 支持两次写 (DoubleWrite) ，从而可以保证数据的安全，提高系统的可靠性。

• 扩展名为 .MYD（MYData）：用于存储表数据；
• 扩展名为 .MYI （MYIndex）： 用于存储表的索引信息。

• MEMORY 表的表定义信息存储在 MySQL 数据字典中，而实际的数据则存储在内存空间中，并以块为单位进行划分；因此当服务器重启后，表本身并不会被删除，只是表中的所有数据都会丢失。
• MEMORY 存储引擎支持 HASH 索引和 BTREE 索引，默认采用 HASH 索引。
• MEMORY 表使用固定长度的行存储格式，即便是 VARCHAR 类型也会使用固定长度进行存储。
• MEMORY 支持 AUTO_INCREMENT 列，但不支持 BLOB 列或 TEXT 列。
• MEMORY 表和 MySQL 内部临时表的区别在于：两者默认都采用内存进行存储，但 MEMORY 表不受存储转换的影响，而内部临时表则会在达到阈值时自动转换为磁盘存储。

• 一个扩展名为 csv ，负责存储表的数据，其文件格式为纯文本，可以通过电子表格应用程序 (如 Microsoft Excel ) 进行修改，对应的修改操作也会直接反应在数据库表中。
• 另一个扩展名为 CSM，负责存储表的状态和表中存在的行数。

• ARCHIVE 引擎支持 INSERT，REPLACE 和 SELECT，但不支持 DELETE 或 UPDATE。
• ARCHIVE 引擎支持 AUTO_INCREMENT 属性，并支持在其对应的列上建立索引，如果尝试在不具有 AUTO_INCREMENT 属性的列上建立索引，则会抛出异常。
• ARCHIVE 引擎不支持分区操作。

mysql> CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT,message CHAR(20), INDEX(a))
       ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

mysql>  SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+

以上图片均通过数据结构可视化网站 Data Structure Visualizations 自动生成，感兴趣的小伙伴也可自行尝试。

• B+ Tree 树的所有非叶子节点 (如 003，007)，都会在叶子节点冗余一份，所有叶子节点按照链表的方式进行组织，这样带来的好处是在范围查询中，只需要通过遍历叶子节点就可以获取到所有的索引信息。
• B+ Tree 的所有非叶子节点都可以存储多个数据值，存储量取决于节点的大小，在 MySQL 中每个节点的大小为 16K ，因此其具备更大的出度，即在相同的数据量下，其树的高度更低。
• 所有非叶子节点都只存储索引值，不存储实际的数据，只有叶子节点才会存储指针信息或数据信息。按照每个节点为 16K 的大小计算，对于千万级别的数据，其树的高度通常都在 3~6 左右 (取决于索引值的字节数)，因此其查询性能非常优异。
• 叶子节点存储的数据取决于不同数据库的实现，对于 MySQL 来说，取决于使用的存储引擎和是否是主键索引。

• 前缀匹配 ：以联合索引的前缀为查找条件。如 emp_no 和 dept_no 为联合索引，查找条件为 emp_no = 10008。
• 列前缀匹配 ：匹配索引列的值的开头部分。如 dept_no 为索引，查询条件为 dept_no like "d1%"。前缀匹配和列前缀匹配都是索引前缀性的体现，在某些时候也称为前缀索引。
• 匹配范围值 ：按照索引列匹配一定范围内的值。如 emp_no 字段为索引，查询条件为 emp_no > 10008。
• 只访问索引的查询 ：如 emp_no 字段为索引，查询语句为 select emp_no from employees，此时 emp_no 索引被称为本次查询的覆盖索引，即只需要从索引上就可以获取全部的查询信息，而不必访问实际表中的数据。
• 精确匹配某一列并范围匹配某一列 ：如 emp_no 和 dept_no 为联合索引，查找条件为 dept_no = "d004" and emp_no < 10020，这种情况下索引顺序可以是（emp_no, dept_no），也可以是（dept_no, emp_no），使用 EXPLAIN 来分析的话，其 TYPE 类型都是 range（使用索引进行范围扫描），但（dept_no, emp_no）性能更好。

• 索引极大减少了服务器需要扫描的数据量；
• 索引可以帮助服务器避免排序和临时表；
• 索引可以将随机 IO 转换为顺序 IO。

• 在查询时，应该避免在索引列上使用函数或者表达式。
• 对于多列索引，应该按照使用频率由高到低的顺序建立联合索引。
• 尽量避免创建冗余的索引。如存在索引 (A，B)，接着又创建了索引 A，因为索引 A 是索引 (A，B) 的前缀索引，从而出现冗余。
• 建立索引时，应该考虑查询时候的排序和分组的需求。只有当索引列的顺序和 ORDER BY 字句的顺序完全一致，并且遵循同样的升序或降序规则时候，MySQL 才会使用索引来对结果做排序。

• 共享锁 (S Lock，又称读锁) ：允许加锁事务读取数据；
• 排它锁 (X Lock，又称写锁) ：允许加锁事务删除或者修改数据。

• 意向共享锁 (IS Lock) ：当前表中某行或者某几行数据存在共享锁；
• 意向排它锁 (LX Lock) ：当前表中某行或者某几行数据存在排它锁。

• 在 READ COMMITTED 事务隔离级别下，读取被锁定行的最新一份快照数据；
• 在 REPEATABLE READ 事务隔离级别下，读取事务开始时所处版本的数据。

• SELECT ... FOR SHARE ：在读取行上加 S 锁；
• SELECT ... FOR UPDATE ：在读取行上加 X 锁。

• Record Lock ：行锁，用于锁定单个行记录。示例如下：

  -- 利用行锁可以防止其他事务更新或删除该行
  SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;
  

• Gap Lock ：间隙锁，锁定一个范围，但不包括记录本身，主要用于解决幻读问题，示例如下：

  -- 利用间隙锁可以阻止其他事务将值15插入列 t.c1
  SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
  

• Next-Key Lock ：等价于 行锁+间隙锁，既锁定范围，也锁定记录本身。可以用于解决幻读中的 ”当前读“ 的问题。

• 原子性（Atomicity）：事务是不可分割的最小工作单元，事务的所有操作要么全部提交成功，要么全部失败回滚，不存在部分成功的情况。
• 一致性（Consistency）：数据库在事务执行前后都保持一致性状态，数据库的完整性没有被破坏。
• 隔离性（Isolation）：允许多个并发事务同时对数据进行操作，但一个事务所做的修改在最终提交以前，对其它事务是不可见的。
• 持久性（Durability）：一旦事务提交，则其所做的修改将会永远保存到数据库中。即使宕机等故障，也不会丢失。

• undo log ：存储在 undo 表空间或全局临时表空间的 undo 日志段 (segment) 上，用于记录数据修改前的状态，主要用于帮助事务回滚以及实现 MVCC 功能 (如一致性非锁定读)。
• redo log ：负责记录数据修改后的值，主要用于保证事务的持久化。

• 读未提交 ：在此级别下，一个事务中的修改，即便没有提交，对其他事务也是可见的。
• 读已提交 ：在此级别下，一个事务中的修改只有已经提交的情况下，对其他事务才是可见的。
• 可重复读 ：保证在同一个事务中多次读取同样数据的结果是一样的。
• 串行化 ：所有事务强制串行执行，由于已经不存在并行，所以上述所有并发问题都不会出现。

1. 官方文档：The InnoDB Storage Engine，Optimization and Indexes，InnoDB Locking and Transaction Model
2. 姜承尧 . MySQL技术内幕:InnoDB存储引擎(第2版) . MySQL技术内幕 . 2013-05
3. 施瓦茨 (Baron Schwartz) / 扎伊采夫 (Peter Zaitsev) / 特卡琴科 (Vadim Tkachenko) . 高性能mysql(第3版) . 电子工业出版社 . 2013-05-01
4. InnoDB 数据页解析
5. MySQL索引背后的数据结构及算法原理
6. MYSQL-B+TREE索引原理
7. Innodb中的事务隔离级别和锁的关系