《数据库系统概念》、《高性能 MySQL》、《深入理解 MySQL》、《MySQL 技术内幕: InnoDB 存储引擎》等.

事务（transaction）

ACID

  • 原子性（atomicity）:
    事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
    恢复系统 保证
  • 一致性（consistency）:
    执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
    程序员 保证
  • 隔离性（isolation）:
    并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
    并发控制系统 保证
  • 持久性（durability）:
    一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
    恢复系统 保证

状态变化

并发问题及相应隔离级别

  • 丢失修改（Lost to modify）:
    指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
  • 脏读（Dirty read）:
    当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
  • 不可重复读（Unrepeatable read）:
    指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
  • 幻读（Phantom read）:
    幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
    不可重复度和幻读区别：不可重复读的重点是修改，幻读的重点在于新增或者删除。
  • 未提交读（read uncommitted）:
    允许读取未提交数据.
  • 已提交读（read committed）:
    只允许读取已提交数据.
  • 可重复读（repeatable read）:
    只允许读取已提交数据, 而且在一个事务两次读取一个数据项期间, 其他事务不得更新该数据.
  • 可串行化（serializable）:
    通常保证可串行化调度.
隔离级别 未提交读 已提交读 可重复读 可串行化
并发问题 丢失修改? 脏读 不可重复读 幻读

以上所有隔离级别都不允许脏写（dirty write）, 即如果一个数据项已经被另外一个尚未提交或中止的事务写入, 则不允许对该数据执行写操作.

“串行化”为最高隔离级别, 完全服从 ACID;

在 SQL 中, 除了接受系统的默认设置, 还可以显示地设置隔离性级别. 例如, “set transaction isolation serializable”.

这里需要注意的是：与 SQL 标准不同的地方在于InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以说 InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL 标准的 SERIALIZABLE (可串行化)隔离级别。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE (可串行化)隔离级别。

并发控制

机制: 锁、时间戳、多版本和快照隔离.

  • 两段锁: 第一个阶段只获得锁不释放锁, 第二个阶段只释放锁不获得锁.

  • 共享锁: 用于事务读的数据项

  • 排他锁: 用于数据写的数据项

这两种锁模式以及两阶段封锁协议在保证可串行化的前提下允许数据的并发读.

Next-Key Lock

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

Nexy-Key Lock 降级为 Record Lock 仅在查询的列是唯一索引的情况下, 若是辅助索引则情况不同, 此时对聚集索引加的是 Record Lock, 对辅助索引加的是 Next-Key Lock. 需要特别注意的是, InnoDB 引擎还会对辅助索引下一个键值加上 Gap Lock.

Gap Lock 的作用是为了阻止多个事务将记录插入到同一范围内, 这会导致幻影读的问题产生.
Phantom Problem 是指在同一事务下, 连续执行两次同样的 SQL 语句可能导致不同的结果, 第二次的 SQL 语句可能会返回之前不存在的行.

InnoDB 引擎在默认可重复读隔离级别下采用 Next-Key Lock加锁, 而在已提交读级别下仅采用 Record Lock.

用户可通过 InnoDB 的 Next-Key Lock 机制在应用层面实现唯一性的检查.

索引（index）

索引是存储引擎用于快速找到记录的一种数据结构. 由存储引擎实现索引.

索引优点

1、索引大大减少了服务器需要扫描的数据量
2、索引可以帮助服务器避免排序和临时表
3、索引可以将随机 IO 变为顺序 IO

常用索引类别

  • 顺序索引:

  • 哈希索引:
    基于哈希表实现, 只有精确匹配索引所有列的查询才有效, 即不支持部分索引列匹配查找, 也不支持任何范围查询. 只支持等值比较查询, 包括 =、IN()、<=>.
    哈希索引数据并不是按照索引值顺序存储的, 所以也就无法排序.
    在 MySQL 中只有 Memory 引擎显示支持哈希索引, 并且其支持非唯一哈希索引.
    访问哈希索引的速度非常快, 除非有很多哈希冲突.
    在数据仓库应用中有一种经典的星型 schema, 需要关联很多查找表, 哈希索引就非常适合查找表的需求.
    InnoDB 有一个特殊功能”自适应哈希索引”, 即针对某些使用频繁的索引值, 在内存中基于 B-Tree 索引之上再创建一个哈希索引, 使得 B-Tree 索引也具有哈希索引的一些优点.
    也有创建自定义哈希索引的方法, 就是新增一个用于索引的哈希值列.

  • B-Tree 索引:
    B-Tree 索引适用于全键值、键值范围或键前缀查找, 其中键前缀查找只适用于根据最左前缀的查找（所以在优化性能的时候可能需要使用相同的列但顺序不同的索引来满足不同类型的查询请求）.
    支持以下查询: 全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另一列、只访问索引的查询.

  • R-Tree 索引（空间数据索引）:
    MyISAM 表支持空间索引, 可以用作地理数据存储.

  • full-text 索引（全文索引）:
    全文索引是一种特殊类型的索引, 它查找的是文本中的关键词, 而不是直接比较索引中的值. 与其他几类索引的匹配方式完全不同.
    仅可用于MyISAM表，针对较大的数据，生成全文索引非常的消耗时间和空间（在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引）。

聚集索引（主索引）
非聚集索引（辅助索引）

稠密索引
稀疏索引

索引评价

  • 第一颗星:
    索引将相关的记录放在一起

  • 第二颗星:
    索引中的数据顺序和查找中的排列顺序一致

  • 第三颗星:
    索引中的列包含了查询中需要的全部列

高性能的索引策略

正确地创建和使用索引是实现高性能查询的基础.

  • 独立的列:
    即索引列不能是表达式的一部分, 也不能是函数的参数.

  • 前缀索引:
    索引很长的字符列, 会让索引变得大且慢.
    前缀索引是一种能使索引更小更快的有效办法.
    MySQL无法使用前缀索引做 ORDER BY 和 GROUP BY 以及做覆盖扫描.
    对于 BLOB、TEXT 或者很长的 VARCHAR 类型的列, 必须使用前缀索引, 因为 MySQL 不允许索引这些列的完整长度.
    后缀索引也有用途, 例如查找某个域名的所有电子邮件. MySQL 原生并不支持反向索引, 但是可以把字符串反转后存储, 并基于此建立前缀索引, 通过触发器维护这种索引.

  • 多列索引

又叫组合索引、联合索引。用多个列组合构建的索引，这多个列中的值不允许有空值。可以在创建表的时候指定，也可以修改表结构。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。示例的组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。

在多个列上建立单独的单列索引大部分情况下并不能提高 MySQL 的查询性能.
MySQL 5.0 之后引入了”索引合并”的策略, 一定程度上可以使用表上的多个单列索引来定位指定的行. 索引合并策略有时候是一种优化的结果, 但实际上更多时候说明表上的索引建得很糟糕.
当出现服务器对多个索引作相交操作时（通常有多个 AND 条件）, 通常意味着需要一个包含所有相关列的多列索引, 而不是多个独立的单列索引.

  • 选择合适的索引列顺序
    当不需要考虑分组和排序的时候, 将选择性最高的列放在最前面通常是很好的.

索引的选择性是指, 不重复的索引值（也称为基数）和数据表的记录总数（#T）的比值, 范围在 1/#T 到 1 之间.

  • 聚簇索引
    聚簇索引并不是一种单独的索引类型, 而是一种数据存储方式. 具体的细节依赖于其实现方式, 但 InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行.
    因为无法把数据行同时存放在两个不同的地方, 所以一个表只能有一个聚簇索引（不过覆盖索引可以模拟聚簇索引的情况）.
    InnoDB 通过主键聚集数据, 即索引列为主键列. 如果没有定义主键, InnoDB 则会选择一个唯一的非空索引代替. 若没有这样的索引则隐式定义一个主键来作为聚簇索引.
    一些数据库服务器允许选择哪个索引作为聚簇索引, 但目前没有一个 MySQL 内建存储引擎支持这一点.
    优点：
    1、可以把相关数据保存在一起
    2、数据访问更快
    3、使用覆盖索引扫描的查询可以直接使用叶节点中的键值
    缺点：
    1、聚簇索引最大限度地提高了 I/O 密集型应用的性能, 但如果数据全部放在内存中, 则访问顺序就没那么重要了, 聚簇索引也就没什么优势了
    2、插入速度严重依赖于插入顺序
    3、更新聚簇索引列的代价很高
    4、基于聚簇索引的表在插入新行或者主键被更新导致需要移动行的时候, 可能面临”页分裂”的问题
    5、聚簇索引可能导致全表扫描变慢, 尤其是行比较稀疏, 或者由于页分裂导致数据存储不连续的时候
    6、二级索引（非聚簇索引）可能比想象的要更大, 因为在二级索引的叶子节点包含了引用行的主键列
    7、二级索引访问需要两次索引查找, 而不是一次
    InnoDB 的二级索引与聚簇索引很不相同. InnoDB 二级索引的叶子节点中存储的不是”行指针”而是主键值, 这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作.

  • 覆盖索引
    指从辅助索引中就能获取到需要的记录，而不需要查找主键索引中的记录。使用覆盖索引的一个好处是因为辅助索引不包括一条记录的整行信息，所以数据量较聚集索引要少，可以减少大量io操作。

索引包含所有需要查询的字段的值, 而无需回表查询.
覆盖索引必须要存储索引列的值, 而哈希索引、空间索引和全文索引都不存储索引列的值, 所以 MySQL 只能使用 B-Tree 索引做覆盖索引.
当发起一个被索引覆盖的查询（也叫做索引覆盖查询）时, 在 EXPLAIN 时 Extra 列的值为 “Using index”.

  • 使用索引扫描来做排序
    EXPLAIN 的 type 列表示这个查询访问数据的方式, 或者说是 MySQL 查找行的方式.
    当 MySQL 使用了索引扫描来做排序时, 在 EXPLAIN 时 type 列的值为 “index”.
    只有当索引的列顺序和 ORDER BY 子句的顺序完全一致, 并且所有列的排序方向都一致时, MySQL 才能够使用索引来对结果做排序…

  • 压缩（前缀压缩）索引
    MyISAM 使用前缀压缩来减少索引的大小, 可能只需要原来的十分之一大小的存储空间, 从而让更多的索引可以放入内存中, 代价是某些操作更慢, 因为每个值得压缩前缀都依赖前面的值, 所以查找时无法使用二分查找而只能从头扫描.
    对于 I/O 密集型应用, 对某些查询带来的好处会比成本多得多;
    而对于 CPU 密集型应用, 因为扫描需要随机查找, 压缩索引使得 MyISAM 在索引查找上要慢上好几倍, 倒序扫描就更慢了.

  • 冗余和重复索引
    重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引. 应该避免这样创建重复索引.
    冗余索引则不同, 比如 索引（A）是索引（A, B）的冗余索引, 因为后者是前者的前缀索引.
    （A, B）<- （A） 冗余
    （A, B）<- （B, A）或（B） 非冗余
    （A） <- （A, 主键） 冗余 因为对 InnoDB 来说主键列已经包含在二级索引中了, 所以也是冗余的.
    其他不同类型的索引也不会是 B-Tree 索引的冗余索引, 无论覆盖的索引列是什么.
    表中的索引越多, 数据插入速度就会越慢, .

  • 未使用的索引
    利用工具删除

  • 索引和锁
    即是使用了索引, InnoDB 也可能锁住一些不需要的数据.
    InnoDB 在二级索引上使用共享（读）锁, 但访问主键索引需要排他（写）锁, 这消除了使用覆盖索引的可能性.

查询性能优化

使用 EXPLAIN 进行分析

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。
比较重要的字段有：

  • select_type : 查询类型，有简单查询、联合查询、子查询等
  • key : 使用的索引
  • rows : 扫描的行数

优化数据访问

1、减少请求的数据量

  • 只返回必要的列：最好不要使用 SELECT * 语句
  • 只返回必要的行：使用 LIMIT 语句来限制返回的数据
  • 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的

2、减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询。

重构查询方式

  1. 切分大查询
    一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询

  2. 分解大连接查询
    将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

  • 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用
  • 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询
  • 减少锁竞争
  • 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩
  • 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效

存储引擎（MyISAM 与 InnoDB）

对于索引,
前者使用前缀压缩技术使得索引更小;
后者按照原数据格式进行存储.
前者通过数据的物理位置引用被索引的行;
后者根据主键引用被索引的行.