MySQL总结

一、事务四大特性(ACID)

原子性(Atomicity)

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性(Consistency)

如果事务执行之前数据库是一个完整性的状态，那么事务结束后，无论事务是否执行成功，数据库仍然是一个完整性状态(数据库的完整性状态:当一个数据库中的所有的数据都符合数据库中所定义的所有的约束，此时可以称数据库是一个完整性状态)

隔离性(Isolation)

事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离

持久性(durability)

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

扩展

二、数据库隔离级别

Read Uncommitted

读取未提交内容, 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）

Read Committed

读取提交内容, 这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。 这种隔离级别可能会导致所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果

Repeatable Read

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）

Serializable

序列化, 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争

脏读 不可重复读 幻读
Read Uncommitted ✔️ ✔️ ✔️
Read Committed ✖️ ✔️ ✔️
Repeatable Read ✖️ ✖️ ✔️
Serializable ✖️ ✖️ ✖️

脏读

某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的

幻读

在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几行(Row)数据，而另一个事务却在此时插入了新的几行数据，先前的事务在接下来的查询中，就会发现有几行数据是它先前所没有的, InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题

三、 Mysql 的锁机制

参考链接: MySQL锁总结
联想记忆: Java中的Happens Before语义保证(volatile关键字)

共享锁与排他锁

  • 共享锁(读锁): 其他事务可以读，但不能写
  • 排他锁(写锁): 其他事务不能读取，也不能写

锁的粒度

  • 表级锁

    1. 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
    2. 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用
    3. 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。

  • 行级锁

    1. 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
    2. 最大程度的支持并发，同时也带来了最大的锁开销。
    3. 行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

    默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成

各种引擎锁

  • MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）
  • BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁
  • InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁 
    1
    在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。

MyISAM 表锁

MyISAM表级锁模式

  • 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

  • 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作

    MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止

    默认情况下，写锁比读锁具有更高的优先级,当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求

    这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞

MyISAM加表锁方法

在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因

MyISAM存储引擎支持并发插入，以减少给定表的读和写操作之间的争用：

如果MyISAM表在数据文件中间没有空闲块，则行始终插入数据文件的末尾。 在这种情况下，你可以自由混合并发使用MyISAM表的INSERT和SELECT语句而不需要加锁——你可以在其他线程进行读操作的时候，同时将行插入到MyISAM表中。 文件中间的空闲块可能是从表格中间删除或更新的行而产生的。 如果文件中间有空闲快，则并发插入会被禁用，但是当所有空闲块都填充有新数据时，它又会自动重新启用。 要控制此行为，可以使用MySQL的concurrent_insert系统变量：

  • 当concurrent_insert设置为0时，不允许并发插入。
  • 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个线程读表的同时，另一个线程从表尾插入记录。这也是MySQL的默认设置。
  • 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录

查询表级锁争用情况

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁的争夺，如果 Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况：

1
2
3
4
5
6
7
mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+

InnoDB行级锁和表级锁

InnoDB锁模式

InnoDB 实现了以下两种类型的行锁：

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
  • 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

  • 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁
  • 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁

InnoDB加锁方法

  • 意向锁是 InnoDB 自动加的， 不需用户干预。
  • 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)；
  • 对于普通 SELECT 语句，InnoDB 不会加任何锁；
  • 事务可以通过以下语句显式给记录集加共享锁或排他锁： 
    1
    2
    (1) 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
    (2) 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。
隐式锁定

InnoDB在事务执行过程中，使用两阶段锁协议(联想记忆到 2pc 协议)：

  • 随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；
  • 锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放
显式锁定
1
2
select ... lock in share mode //共享锁
select ... for update //排他锁

  • select for update

    1. 在执行这个 select 查询语句的时候，会将对应的索引访问条目进行上排他锁（X 锁），也就是说这个语句对应的锁就相当于update带来的效果
    2. select *** for update 的使用场景：为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句
    3. 其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

  • select lock in share mode

    1. in share mode 子句的作用就是将查找到的数据加上一个 share 锁，这个就是表示其他的事务只能对这些数据进行简单的select 操作，并不能够进行 DML 操作
    2. 为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁
    3. 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁
    • 两者区别：for update 是排他锁（X 锁），一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 ；lock in share mode 是共享锁，多个事务可以同时的对相同数据执行

InnoDB 行锁实现方式

  • InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
  • 不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
  • 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，
  • 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）

MySQL的锁算法

  • Record Lock：单个行记录上的锁。
  • Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
  • Next-Key Lock：Record + Gap，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题
  • 别废话，各种SQL到底加了什么锁？

四、MySQL的MVCC机制

MVCC机制是什么？

其实就是在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号，而每一个事务在启动的时候，都有一个唯一的递增的版本号。 在InnoDB中，给每行增加两个隐藏字段来实现MVCC，两个列都用来存储事务的版本号，每开启一个新事务，事务的版本号就会递增

一致性非锁定读

consistent read （一致性读），InnoDB用多版本来提供查询数据库在某个时间点的快照。如果隔离级别是REPEATABLE READ，那么在同一个事务中的所有一致性读都读的是事务中第一个这样的读读到的快照； 如果是READ COMMITTED，那么一个事务中的每一个一致性读都会读到它自己刷新的快照版本。Consistent read（一致性读）是READ COMMITTED和REPEATABLE READ隔离级别下普通SELECT语句默认的模式。 一致性读不会给它所访问的表加任何形式的锁，因此其它事务可以同时并发的修改它们。

解决不可重复读

当一个 MVCC 数据库需要更一个一条数据记录的时候，它不会直接用新数据覆盖旧数据，而是将旧数据标记为过时（obsolete）并在别处增加新版本的数据。这样就会有存储多个版本的数据，但是只有一个是最新的。这种方式允许读者读取在他读之前已经存在的数据，即使这些在读的过程中半路被别人修改、删除了，也对先前正在读的用户没有影响。保证了在同一个事务中多次读取相同的数据返回的结果是一样的，解决了不可重复读的问题。

缺点在于：
这种多版本的方式避免了填充删除操作在内存和磁盘存储结构造成的空洞的开销，但是需要系统周期性整理（sweep through）以真实删除老的、过时的数据。

总结就是: MVCC是同一份数据临时保留多版本的一种方式，进而实现并发控制

参考链接

  • 通过etcd学习MVCC机制: ectd

五、MySQL的存储引擎

InnoDB

简介

  1. 支持ACID的事务，支持事务的四种隔离级别;
  2. 支持行级锁及外键约束：因此可以支持写并发;
  3. 不存储总行数;
  4. 一个InnoDB引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空间，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制;
  5. 主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引;
  6. 最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整;
  7. 适用OLTP(联机事务处理)，实时性要求高

主要特性

插入缓存（insert buffer）、两次写(double write)、自适应哈希(Adaptive Hash index)、异步IO(Async IO)、刷新邻接页(Flush Neighbor Page)

参考

MyISAM

  1. 不支持事务，但是每次查询都是原子的；
  2. 支持表级锁，即每次操作是对整个表加锁；
  3. 存储表的总行数；
  4. 一个MyISAM表有三个文件：索引文件、表结构文件、数据文件；
  5. 采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。
  6. 适用OLAP(联机分析处理),实时性要求不高但一般数据量大

MEMORY

ARCHIVE

参考链接

六、MySQL的索引

主要有有B+索引和hash索引, 区别：

  1. 如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
  2. 如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
  3. 同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
  4. 哈希索引也不支持多列联合索引的最左匹配规则；
  5. B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题

B+索引数据结构，和B树的区别

  1. B树：有序数组+平衡多叉树
    它的特点是：
    (1)不再是二叉搜索，而是m叉搜索；
    (2)叶子节点，非叶子节点，都存储数据；
    (3)中序遍历，可以获得所有节点；

  2. B+树：有序数组链表+平衡多叉树, 在B树的基础上，做了一些改进
    (1)非叶子节点不再存储数据，数据只存储在同一层的叶子节点上
    (2)叶子之间，增加了链表，获取所有节点，不再需要中序遍历

  3. B+树改进后更优的特性
    (1)范围查找，定位min与max之后，中间叶子节点，就是结果集，不用中序回溯
    (2)叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储；
    (3)非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引

为什么B+树适合作为索引的结构

  1. 不同于二叉搜索树, B树是m分叉的, 树高度能大大降低，所以能够存储大量数据
  2. 很适合磁盘存储，能够充分利用局部性原理，磁盘预读
    (1)内存读写块，磁盘读写慢，而且慢很多；
    (2)磁盘预读：磁盘读写并不是按需读取，而是按页预读，一次会读一页(一页数据是4K)的数据，每次加载更多的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘IO，提高效率；
    (3)局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO
  3. MyISAM和InnoDB都使用了B+树作为索引存储结构，但是叶子上数据的存储方式不同。前者索引文件和数据文件是分离的，索引文件仅保存记录所在页的指针（物理位置）， 而后者直接存储数据，或者存储主键值（存储主键值并检索辅助索引，此时实际上进行了二次查询，增加IO次数

索引分类

  • 普通索引：最基本的索引，没有任何限制。
  • 唯一索引：与“普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。
  • 主键索引：它 是一种特殊的唯一索引，不允许有空值。
  • 全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时耗空间。（MATCH… AGAINST…）
  • 组合索引：为了更多的提高MySQL效率可建立组合索引，遵循“最左前缀”原则。
  • 覆盖索引：包含（覆盖）所有需要查询的字段的值的索引

explain模拟SQL查询计划

explain执行计划包含的信息

id select_type table type possible_keys key key_len ref rows Extra

对应含义详解

  • id: select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

  • select_type: 查询的类型，分为：

    1. SIMPLE：简单的select查询，查询中不包含子查询或者union
    2. PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary
    3. SUBQUERY：在select 或 where列表中包含了子查询
    4. DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在零时表里
    5. UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
    6. UNION RESULT：从union表获取结果的select

  • type: 访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是:
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    一般来说，好的sql查询至少达到range级别，最好能达到ref

    1. system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计
    2. const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const
    3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
    4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
    5. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询
    6. index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）
    7. ALL：Full Table Scan，遍历全表以找到匹配的行

  • possible_keys: 查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

  • key: 实际使用的索引，如果为NULL，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅出现在key列表中

  • key_len: 表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的

  • ref: 显示索引的那一列被使用了，如果可能，是一个常量const

  • rows: 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

  • Extra: 不适合在其他字段中显示，但是十分重要的额外信息

    1. Using filesort:mysql对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”
    2. Using temporary:使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 和 group by
    3. Using index:表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高;如果同时出现Using where，表明索引被用来执行索引键值的查找;如果没同时出现Using where，表明索引用来读取数据而非执行查找动作
    4. Using Where: 使用了where过滤
    5. Using join buffer: 使用了链接缓存
    6. Impossible WHERE: where子句的值总是false，不能用来获取任何元祖
    7. select tables optimized away: 在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化
    8. distinct: 优化distinct操作，在找到第一个匹配的元祖后即停止找同样值得动作

参考链接

聚集索引和非聚集索引区别

聚集（clustered）索引，也叫聚簇索引

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
如果没定义主键，会选择一个唯一的非空索引代替，如果没有这样的索引，则会隐式定义一个主键作为聚簇索引

非聚集（unclustered）索引

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引

参考

七、数据库的主从复制

  • 就算MySQL拆成了多个，也必须分出主和从，所有的写操作都必须要在主MySQL 上完成;
  • 所有的从MySQL的数据都来自于(同步于)主MySQL
  • 在MySQL主从时，如果一个业务(service中的一个方法)中，如果既有R操作，又有W操作，因为W操作一定要在主MySQL上，所以在一个事务中所有的数据来

拓展

MySQL主从延时这么长，要怎么优化

八、范式设计

  • 第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库，是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值；
  • 第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。 即各字段和主键之间不存在部分依赖
  • 第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。即在第二范式的基础上，不存在传递依赖 (不允许有冗余数据)

九、拓展