Xing's Techlog

MySQL知识点总结

Posted on Edited on In

MySQL数据库知识点总结。

查考：高性能MySQL（第三版）。

MySQL架构与历史

MySQL逻辑架构

MySQL的逻辑架构图如下所示：

mysql逻辑架构图

每个客户端连接都会在服务器中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。

QPS (Queries Per Second) : 单个进程美妙请求服务器的成功次数。

TPS (Transaction Per Second) ： 美妙处理的事务数。

PV (Page View) : 页面被浏览的次数。

并发控制

读写锁

在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题，这两种类型的锁通常被称为共享锁（shared lock）和排他锁（exclusive lock）也叫读锁（read lock）和写锁（write lock）。

读锁是共享的，或者说是相互不 阻塞的。多个客户在同一时刻可以同时读取同一个资源，而互不干扰。写锁则是排他的， 也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑，只有这样，才 能确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写人的同 一资掘。

锁粒度

锁策略：就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响 到性能。大多数商业数据库系统没有提供更多的选择，一般都是在表上施加行级锁( row­ level lock)，并以各种复杂的方式来实现，以便在锁比较多的情况下尽可能地提供更好 的性能。

两种最重要的锁策略：

  1. 表锁（table lock）：表锁是 MySQL 中最基本的锁策略，井且是开销最小的策略。表锁会锁定整张表。一个用户在对表进行写操作(插入、删除、更新等) 前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不相互阻塞的。
  2. 行级锁（row lock）：行级锁可以最大程度地支持井发处理(同时也带来了最大的锁开销)。在 InnoDB 和 XtraDB，以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实 现，而 MySQL 服务器层没有实现。服务器层 完全不了解存储引擎中的锁实现。

事务

事务就是一组原子性的 SQL 查询，或者说一个独立的工作单元。事务内的语句，要么全部执行成功，要么全部执行失败。事务是由下层的存储引擎实现的。

事务的ACID特性

原子性 (Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。一个运行良好的事务处理系统，必须具备这些标准特征。

  • 原子性 (Atomicity)：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全 部提交成功，要么全部失败回攘，对于一个事务来说，不可能只执行其中的一部分 操作，这就是事务的原子性。
  • 一致性(Consistency)：数据库总是从一个一致性的状态转换到另外一个一致性的状态。
  • 隔离性(Isolation)：一个事务所做的修改在最终提交以前，对其他事务是不可见的。
  • 持久性(Durability)：一且事务提交，则其所做的修改就会永久保存到数据库中。

隔离级别

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别 的隔离通常可以执行更高的井发，系统的开销也更低。

  • READ UNCOMMITTED (未提交读)：在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见 的。事务可以读取未提交的数据，这也被称为脏读( Dirty Read)。这个级别会导致很多问题，从性能上来说， READ UNCOMMITTED 不会比其他的级别好太多，但却缺乏 其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。
  • READ COMMITTED (提交读)：大多数数据库系统的默认隔离级别都是 READ COMMITTED (但 MySQL 不是)。 READ COMMITTED 满足前面提到的隔离性的简单定义 : 一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读( nonrepeatable read)，因为两次执行同样的查询，可能会得到不一样的结果。
  • REPEATABLE READ (可重复读)：可重复读是 MySQL 的默认事务隔离级别。REPEATABLE READ 解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读 (Phantom Read)的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时， 另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行( Phantom Row)。 InnoDB 和 XtraDB 存储引擎通过多版本井发控制( MVCC, Multiversion Concurrency Control)解决了幻读的问题。
  • SERIALIZABLE (可串行化)：SERIALIZABLE 是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题。简单来说， SERIALIZABLE 会在读取的每一行数据上都加锁，所以可能导致 大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有井发的情况下，才考虑采用该级别。

隔离级别

死锁

死锁是指两个或者多个事务在同一资源上相互占用，井请求锁定对方占用的资源，从而导致恶性循环的现象。复杂的系统，比如 InnoDB 存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。InnoDB 目前处理死锁的方站是，将持有最少行级排他锁的事务进行回攘(这是相对比较简单的死锁回滚算法)。

事务日志

事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。

事务日志采用的是追加的方式，因此写日志的操作是磁盘 上一小块区域内的顺序 I/O，而不像随机 I/O 需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志( Write-Ahead Logging)，修改数据需要写两次磁盘。

MySQL中的事务

MySQL 提供了两种事务型的存储引擎: InnoDB 和 NDB Cluster。另外还有一些第三方 存储引擎也支持事务，比较知名的包括 XtraDB 和 PBXT 。

自动提交( AUTOCOMMIT)

MySQL 默认采用自动提交( AUTOCOMMIT)模式。既如果不是显式地开始一 个事务，则每个查询都被当作一个事务执行提交操作。在当前连接中，可以通过设置 AUTOCOMMIT 变量来启用或者禁用自动提交模式：

1
2
mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
mysql> SET AUTOCOMMIT = 1;

1或者 ON表示启用，0或者 OFF 表示禁用。当 AUTOCOMMIT=0时，所有的查询都是在一个 事务中，直到显式地执行 COMMIT提交或者 ROLLBACK 回滚，该事务结束，同时又开始了另一个新事物。修改 AUTOCOMMIT对非事务型的表，比如 MyISAM 或者内存表，不会有任何影响。

MySQL 可以通过执行 SET TRANSACTION ISOLATION LEVEL 命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别:

1
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
在事务中混合使用存储引擎

MySQL 服务器层不管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。

如果在事务中混合使用了事务型和非事务型的表(例如 InnoDB 和 MyISAM 表)，在正常提交的情况下不会有什么问题。但是在事务需要回滚时，非事务型的表上的变更无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确定。所以，为每张表选择合适的存储引擎非常重要。

隐式和显式锁定

InnoDB 采用的是两阶段锁定协议( two-phase locking protocol)。在事务执行过程中，随时都可以执行锁定，锁只有在执行 COMMIT或者 ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定， InnoDB 会根据隔离级别在需要的时候自动加锁。

InnoDB也支持通过特定的语句进行显式锁定 ：

  • SELECT — LOCK IN SHARE MODE
  • SELECT … FOR UPDATE

MySQL 也支持 LOCK TABLES和 UNLOCK TABLES语句，这是在服务器层实现的，和存储引擎无关。LOCK TABLES 和事务之间相互影响的话，情况会变得非常复杂，在某些 MySQL 版本 中甚至会产生无怯预料的结果。因此，除了事务中禁用了 AUTOCOMMIT, 可以使用 LOCK TABLES 之外，其他任何时候都不要显式地执行 LOCK TABLES，不管 使用的是什么存储引擎。

多版本并发控制

MySQL 的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考 虑，它们一般都同时实现了多版本井发控制(MVCC)。

可以认为 MVCC 是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。MVCC 是通过保存数据在某个时间点的快照来实现的。

MySQL 的存储引擎

在文件系统中， MySQL将每个数据库 (也可以称之为schema) 保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。因为 MySQL 使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关。在 Windows 中，大小写是不敏感的，而在类 Unix 中则是敏感的。不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在 MySQL 服务层统一处理的。

lnnoDB 存储引擎

MylSAM 存储引擎

MySQL 内建的其他存储引擎

Archive 引擎
Blackhole 引擎
CSV 引擎
Federated 引擎
Memory 引擎
Merge 引擎
NOB 集群引擎

第三方存储引擎

OLTP 类引擎
面向列的存储引擎
社区存储引擎

选择合适的引擎

转换表的引擎

创建高性能索引

索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。索引是在存储引擎层而不是服务器层实现的。

索引基础

索引的类型

B-Tree 索引

B-Tree（平衡多路查找树）通常意味着所有的值都是按顺序存储的，井且每一个叶子页到根的距离相同。下图是InnoDB索引抽象工作图：

BTree索引

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要 的数据，取而代之的是从索引的根节点(图示并未圃出)开始进行搜索。根节点的槽中 存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和 要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值 的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

叶子节点比较特殊，它们的指针指向的是被索引的数据，而不是其他的节点页。

B-Tree 对索引列是顺序组织存储的，所以很适合查找范围数据。例如，在一个基于文本 域的索引树上，按字母顺序传递连续的值进行查找是非常合适的，所以像“找出所有以 I到 K开头的名字”这样的查找效率会非常高。

下面语句会创建一个表然后创建这个表的B-Tree索引

1
2
3
4
5
6
7
CREATE TABLE People (
    last_name  varchar(50)    not null,
    first_name varchar(50)    not null,
    dob        date           not null,
    gender     enum('m', 'f') not null,
    key(last_name, first_name, dob)
);

B-Tree索引对如下类型的查询有效：

  • 全值匹配
  • 匹配最左前缀
  • 匹配列前缀
  • 匹配范围值
  • 精确匹配某一列并范围匹配另外一列
  • 只访问索引的查询

B-Tree索引的限制：

  • 如果不是按照索引的最左列开始查找，则无法使用索引。也无法查找姓氏以某个字母结尾的人。
  • 不能跳过索引中的列。
  • 如果查询中有某个列的范围查询，则其右边所有列都无怯使用索引优化查找。

索引列的顺序特别重要！，这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码( hash code)，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希 码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在 MySQL 中，只有 Memory 引擎显式支持哈希索引。这也是 Memory 引擎表的默认索引类型， Memory 引擎同时也支持 B-Tree 索引。值得一提的是， Memory 引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同， 索引会以链表的方式存放多个记录指针到同一个哈希条目中。

因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找 的速度非常快。然而，哈希索引也有它的限制：

  • 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避 免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响井不明显。
  • 哈希索引数据井不是按照索引值顺序存储的，所以也就无法用于排序。
  • 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列(A,B)上建立哈希索引，如果查询只有数据列 A, 则无法使用该索引。
  • 哈希索引只支持等值比较查询，包括=、 IN()、<=> (注意。和〈=〉是不同的操作)。 也不支持任何范围查询，例如WHERE price > 100.
  • 访问哈希索引的数据非常快，除非有很多哈希冲突(不同的索引列值却有相同的哈 希值)。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
  • 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到井删除对应行的引用，冲突越多，代价越大。

InnoDB 引擎有一个特殊的功能叫做“自适应哈希索引( adaptive hash index)”。当 InnoDB 注意到某些索引值被使用得非常频繁时，它会在内存中基于 B-Tree 索引之上再 创建一个哈希索引，这样就让 B-Tree 索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部的行为，用户无摇控制或者配置，不过如果有必要， 完全可以关闭该功能。

空间数据索引( R-Tree)
全文索引
其他索引类别

索引的优点

  • 大大加快数据的检索速度，这也是创建索引的最主要的原因；
  • 加速表和表之间的连接；
  • 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；