MySQL常问的基础理论知识

1、事务

四个特性ACID

1、原子性

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。

2、一致性

执行事务后，数据库从一个正确的状态变化到另一个正确的状态；比如两个人转账，两个人总的钱500，不管两个人怎么转账，最后数据库两个人总的钱还是500.

3、隔离性

发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

4、持久性

一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事务隔离级别

以上介绍完事务的四大特性(简称ACID)，现在重点来说明下事务的隔离性，当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性。

不考虑事务的隔离性，会发生的几种问题：

脏读

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。

不可重复读

对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据**更新（UPDATE）**操作。

幻读

幻读是针对数据**插入（INSERT）**操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

第一类丢失：事务B在事务A后提交，事务A的数据就没了

第二类丢失：事务B在事务A提交前修改，事务A提交后但事务B后面又回滚到事务A提交前的状态了，事务A的数据就没了

注意：不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。解决幻读一般需要对整个表加锁，而解决不可重复读只需对行加锁。

事务隔离分为了四个级别

1. 读未提交（READ UNCOMMITTED）
2. 读提交 （READ COMMITTED）
3. 可重复读 （REPEATABLE READ）
4. 串行化 （SERIALIZABLE）

1、读未提交

读未提交，其实就是可以读到其他事务未提交的数据，但没有办法保证你读到的数据最终一定是提交后的数据，如果中间发生回滚，那就会出现脏数据问题，读未提交没办法解决脏数据问题。更别提可重复读和幻读了，想都不要想。

2、读提交

只能读取已经提交了的数据，解决了脏读的问题，但两次读取数据：第一次是别人修改后提交前的数据，第二次是别人已经修改了的数据，这就导致两次读取的数据不一致，产生了不可重复读问题！

3、可重复读

事务不会读到其他事务对已有数据的修改，及时其他事务已提交，也就是说，事务开始时读到的已有数据是什么，在事务提交前的任意时刻，这些数据的值都是一样的。但是，对于其他事务新插入的数据是可以读到的，这也就引发了幻读问题。

MySQL 的可重复读隔离级别其实解决部分幻读问题

4、串行化

串行化是4种事务隔离级别中隔离效果最好的，解决了脏读、可重复读、幻读的问题，但是效果最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束。

2、MyISAM和InnoDB区别

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃恢复问题的话）。

两者的对比：

1. 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
2. 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
3. 是否支持外键： MyISAM不支持，而InnoDB支持。
4. 是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。推荐阅读：MySQL-InnoDB-MVCC多版本并发控制

3、锁机制

Mysql为了解决并发、数据安全的问题，使用了锁机制。MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

按照锁的粒度分类

表级锁

Mysql中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单 ，资源消耗也比较少，加锁快，不会出现死锁 。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

行级锁

Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 InnoDB支持的行级锁，包括如下几种。

• Record Lock: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
• Gap Lock: 间隙锁，对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
• Next-key Lock： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。
  虽然使用行级索具有粒度小、并发度高等特点，但是表级锁有时候也是非常必要的：

InnoDB的行级锁是基于索引实现的，如果查询语句为命中任何索引，那么InnoDB会使用表级锁. 此外，InnoDB的行级锁是针对索引加的锁，不针对数据记录，因此即使访问不同行的记录，如果使用了相同的索引键仍然会出现锁冲突。使用锁的时候，如果表没有定义任何索引，那么InnoDB会创建一个隐藏的聚簇索引并使用这个索引来加记录锁。

使用

事务更新大表中的大部分数据直接使用表级锁效率更高；
事务比较复杂，使用行级索很可能引起死锁导致回滚。

按照是否可写分类

表级锁和行级锁可以进一步划分为共享锁（s）和排他锁（X）。

共享锁（s）

共享锁（Share Locks，简记为S）又被称为读锁，可以多个用户同时加共享锁并发读取数据，但任何事务都不能获取数据上的排他锁，直到所有用户已释放所有共享锁。

排他锁（X）：

排它锁（(Exclusive lock,简记为X锁)）又称为写锁，一个事务加上了排他锁，其它任何事务都不能再对A加任何类型的锁，直到之前事务释放排他锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁。

两者之间的区别：

共享锁（S锁）：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不 能加排他锁。获取共享锁的事务只能读数据，不能修改数据。

排他锁（X锁）：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获取排他锁的事务既能读数据，又能修改数据。

另外两个表级锁：IS和IX

意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

• 意向共享锁（IS）： 表示事务准备给数据行记入共享锁，事务在一个数据行加共享锁前必须先取得该表的IS锁。

• 意向排他锁（IX）： 表示事务准备给数据行加入排他锁，事务在一个数据行加排他锁前必须先取得该表的IX锁。

注意：

1. 这里的意向锁是表级锁，表示的是一种意向，仅仅表示事务正在读或写某一行记录，在真正加行锁时才会判断是否冲突。意向锁是InnoDB自动加的，不需要用户干预。
2. IX，IS是表级锁，不会和行级的X，S锁发生冲突，只会和表级的X，S发生冲突。

死锁与避免

死锁

此外，不同于MyISAM总是一次性获得所需的全部锁，InnoDB的锁是逐步获得的，当两个事务都需要获得对方持有的锁，导致双方都在等待，这就产生了死锁。 发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个则可以获取锁完成事务

避免死锁：

通过表级锁来减少死锁产生的概率；
多个程序尽量约定以相同的顺序访问表（这也是解决并发理论中哲学家就餐问题的一种思路）；

4、查询执行过程

MySQL基本架构

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
• 查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
• 分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行。
• 执行器: 执行语句，然后从存储引擎返回数据。
• 存储引擎：负责数据的存储和读取

总结：

单来说 MySQL 主要分为 Server 层和存储引擎层：

1. Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
2. 存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

语句执行过程

SELECT XXX FROM XXX WHERE XXX GROUP BY XXX HAVING XXX ORDER BY XXX LIMIT XXX;

查询语句

连接器检查是否有权限==》查询缓存（MySQL 8.0前）》分析器进行词法分析》优化器确定最后执行方案==》执行器再次权限校验（比如对表是否有权限），调用存储引擎接口返回结果

更新语句

主要涉及到日志处理

MySQL 自带的日志模块式 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），我们就以 InnoDB 模式下来探讨这个语句的执行流程。

连接器==》分析器==》权限校验----》执行器—》引擎—redo log prepare—》binlog—》redo log commit

5、查询优化

1、WHERE子句中的连接顺序

数据库采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。

2、SELECT子句中避免使用*号

我们当时学习的时候，“*”号是可以获取表中全部的字段数据的。

• 但是它要通过查询数据字典完成的，这意味着将耗费更多的时间
• 使用*号写出来的SQL语句也不够直观。

用TRUNCATE替代DELETE

DELETE是一条一条记录的删除，而Truncate是将整个表删除，保留表结构，这样比DELETE快

使用表或列的别名

如果表或列的名称太长了，使用一些简短的别名也能稍微提高一些SQL的性能。毕竟要扫描的字符长度就变少了。。。

善用索引

1、避免在索引列上使用计算

2、用IN替代OR

使用到IN查询时，都是按照如下方式处理的:

• 对IN列表中的数值进行排序。

• 对于查询的匹配，每次使用二分查找去匹配IN列表的数值。

  所以对于第[2]步，每次比较的算法复杂度大概为O(log n)。相反，对于同样逻辑的OR列表，每次都要遍历，所以OR相应的算法复杂度为O(n)(因此对于遍历非常大的OR列表，会很缓慢！)。

3、总是使用索引的第一个列

如果索引是建立在多个列上，只有在它的第一个列被WHERE子句引用时，优化器才会选择使用该索引。 当只引用索引的第二个列时，不引用索引的第一个列时，优化器使用了全表扫描而忽略了索引

6、索引

索引是什么？

• （1）是一种快速查询表中内容的机制，类似于新华字典的目录
• （2）运用在表中某个些字段上，但存储时，独立于表之外

索引优缺点

索引加快数据库的检索速度

索引降低了插入、删除、修改等维护任务的速度(虽然索引可以提高查询速度，但是它们也会导致数据库系统更新数据的性能下降，因为大部分数据更新需要同时更新索引)

唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能

索引需要占物理和数据空间

实现索引的方式

Hash表实现的索引

实现原理

通过哈希算法计算索引列的对应哈希值，然后在哈希表中对应哈希值的位置存储的就是该列的数据

优缺点

优点：

1. 查询速度效率高，比较适合小规模多查询的数据，树来寻找目标行的时间复杂度是O(log(n))，哈希映射时间复杂度O(1)

缺点：所有缺点基本都是因为哈希索引是通过唯一的哈希值查找这个特性而产生的

1. 只能满足 =、！=、IN查询，范围查询无法用到该索引
2. 无法利用索引数据快速排序
3. 无法做到最左前缀匹配
4. 键值重复太多会产生太多哈希冲突，查询效率降低许多，甚至有可能不如B+树实现的索引

B+树实现的索引

B+树特性

• 关键字的信息都存放在叶子节点中，所有的非叶子节点可以看作是索引部分，节点中仅含子树的最大值（最小值）
• 叶子节点本身依据关键字的大小自小而大顺序链接
• 一般有两个头指针，一个指向根节点，一个指向关键字最小的叶子节点

实现原理

将索引列的值当作key，B+树叶子节点存放 key 和 该列其它数据，其他内节点只存放 key。

优缺点

优点：

1. 任何查找都是从根节点到叶子节点的过程，查询效率稳定，时间复杂度为O(log(n))
2. 由于索引的所有信息在叶子节点中，且叶子节点自小而大连接着，所以遍历所有信息、范围查找非常方便快速，而我们数据库查询中范围查找是经常用到的。

为什么用B+树？普通平衡二叉树、B树为什么不行？

1、先说为啥普通的平衡二叉树不行：

由于普通平衡二叉树只有两个子树，每次判断下一次进入哪一个子树都需要和知道当前节点的值并与目标值比较，如果待查找数据在外存，我们每次都需要从外存中取得当前节点的值以作比较，这样频繁的内外存交换很费时，显然不行，所有它只适合查找存储在计算机内存中较小的文件，称为“内查找法”。

B树与B+树都可以看作多叉平衡二叉树，每次可以从外存中取多个数据到内存与目标值进行比较然后选择进入哪一条子树，这样就减少了内外存交换，这就比较适合外查找，而数据库存的索引数据肯定不是存在内存的而是在外存，所以不用普通平衡二叉树。

2、为什么不用B树？

B树每一个节点都存放关键字和对应数据，比关键字小的存左子树，大的存右子树。普通单行查询两者其实没多大区别，但是如果涉及到排序和范围查询普通B树的结构就不太好处理了。正因为B+树有前面说的优点2，所以选用了B+树。

主键索引与二级索引

主键索引

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 mysql 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以知道主键值。

唯一索引，普通索引，前缀索引等索引属于二级索引。

聚集、非聚集索引

聚集

聚集索引即索引结构和数据一起存放的索引，聚集索引的查询速度非常的快，定位到索引的节点，就相当于定位到了数据。

缺点：

更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是很少修改的。

非聚集索引

非聚集索引即索引结构和数据分开存放的索引。就是关键字对应的数据需要再进行一层某种转换才能得到数据，比如下面两种情况都是进行了一层转换才得到的数据：

1. MYISAM 引擎的表的.MYI 文件包含了表的索引， 该表的索引(B+树)的每个非叶子节点存储索引， 叶子节点存储索引和索引对应数据的指针，指向.MYD 文件的数据。索引文件与数据文件分开的，得到指针后还需要转换得到数据文件的数据
2. 二级索引的叶子节点就存放的是主键，根据主键再回表（根据主键索引查询）查询得到对应数据。

缺点：

1. 虽然非聚集索引的叶子节点是不存放数据的，更新代价小了，但可能会二次查询(回表)** :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

非聚集索引优化：覆盖索引

前面说了需要根据主键回表查询，试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

 SELECT name FROM table WHERE name='guang19';Copy to clipboardErrorCopied

那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

即使是 MYISAM 也是这样，虽然 MYISAM 的主键索引确实需要回表（根据指针）， 因为它的主键索引的叶子节点存放的是指针。但是如果 SQL 查的就是主键呢?

SELECT id FROM table WHERE id=1;Copy to clipboardErrorCopied

主键索引本身的 key 就是主键，查到返回就行了。这种情况就称之为覆盖索引了。

总结：

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

MyISAM和InnoDB实现BTree索引方式的区别

MyISAM

B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。是“非聚簇索引”。

InnoDB

其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。 PS：整理自《Java工程师修炼之道》

最左前缀匹配

单列索引：单列索引即由一列属性组成的索引。

联合索引(多列索引）：联合索引即由多列属性组成索引。

假设创建的联合索引由三个字段组成:

ALTER TABLE table ADD INDEX index_name (num,name,age)Copy to clipboardErrorCopied

那么当查询的条件有为:num / (num AND name) / (num AND name AND age)时，索引都会生效。但是以下情况不会生效：name / age / name AND age / num AND age，这就叫最左前缀匹配。

所以得出一个结论：在创建联合索引时，尽量把查询最频繁的那个字段作为最左(第一个)字段。查询的时候也尽量以这个字段为第一条件。

创建索引的字段如何选择

合适的字段

1.不为 NULL 的字段

索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。

2.被频繁查询的字段

3.被作为条件查询的字段

被作为 WHERE 条件查询的字段，应该被考虑建立索引。

4.被经常频繁用于连接的字段

经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

不合适的

1、频繁更新的

2、尽可能的考虑建立联合索引而不是单列索引

4.注意避免冗余索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

创建索引的语句

1.添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) Copy to clipboardErrorCopied

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) Copy to clipboardErrorCopied

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )Copy to clipboardErrorCopied

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) Copy to clipboardErrorCopied

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )