Mysql必备知识点

1. 数据库的三大范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

• 第一范式：确保每列保持原子性，即数据库表的每一列（字段）都是不可分割的原子数据项。不可分割也就是不能再拆分为更小的单位。

• 第二范式：确保非主属性完全依赖于主属性。所谓完全依赖是指不能存在仅依赖某一部分主属性的属性。

  比如一个表包含学号、姓名、课程和分数这几个属性，主属性为学号、课程。但是姓名属性并不完全依赖于主属性，而只依赖于学号属性，因此就不满足第二范式。如果想要其满足第二范式，则可以将此表拆分为两个表，一个表中包含学号、姓名，另一个表包含学号、课程、分数。

• 第三范式：确保表中不存在传递依赖。即非主属性只能依赖于主属性，而不能依赖于其他非主属性。

  比如一个表包含学号、系名、系主任这几个属性，主属性为学号。但是系主任这个属性不仅依赖于学号，也可以依赖于系名，此时就不满足第三范式。如果想要其满足第三范式，则可以将此表拆分为两个表，一个表中包含学号、系名，另一个表包含系名、系主任。

2. MySQL 存储引擎

MySQL 支持多种存储引擎,比如 InnoDB，MyISAM，Memory，Archive 等。InnoDB 也是 MySQL 的默认存储引擎（MySQL 5.5 及以后）。

MyISAM 和 InnoDB 的区别：

	InnoDB	MyISAM
是否支持事务	是	否
是否支持外键	是	否
支持的锁种类	表级锁和行级锁（默认行级锁）	表级锁
是否支持崩溃完全恢复	是（使用 redo log）	否（容易发生损坏）

两种引擎该如何选择？

• 是否需要事务？有，InnoDB
• 是否存在并发修改？有，InnoDB
• 是否追求快速查询，且数据修改少？是，MyISAM
• 在绝大多数情况下，推荐使用 InnoDB

MyISAM 比 InnoDB 查询快的原因：

• InnoDB 要加载数据页到缓存内，MyISAM 只缓存索引块，MyISAM 较少的磁盘 IO
• InnoDB 映射到数据页、到行，MyISAM 记录文件的 offset，定位比 InnoDB 快
• InnoDB 需要维护版本并发控制
• 没有 where 的 count(*) 使用 MyISAM 要比 InnoDB 快得多。因为 MyISAM 内置了一个计数器，count(*) 时它直接从计数器中读，而 InnoDB 必须扫描全表。

3. varchar 和 char 区别

• varchar 用于存储可变长字符串，它仅使用必要的空间。需要额外适用 1~2 个字节记录字符串的长度，如果列的最大长度小于或等于 255，用 1 个字节记录长度，否则用 2 个字节记录长度。
• char 用于存储定长字符串，适合存储很短的字符串或所有值都接近同一个长度。比如适合存储密码的 MD5 值，对于经常变更的数据，char 比 varchar 更好，因为定长的 char 不容易产生碎片，且在存储空间上更有效率（因为 varchar 要额外记录唱长度）。

4. in 和 exists 的区别

in 和 exists 一般用于子查询。

• 使用 exists 时会先进行外表查询，将查询到的每行数据带入到内表查询中看是否满足条件；使用 in 一般会先进行内表查询获取结果集，然后对外表查询匹配结果集，返回数据。
• in 在内表查询或者外表查询过程中都会用到索引。
• exists 仅在内表查询时会用到索引
• 一般来说，当子查询的结果集比较大，外表较小使用 exist 效率更高；当子查询寻得结果集较小，外表较大时，使用in效率更高。
• 对于 not in 和 not exists，not exists 效率比 not in 的效率高，与子查询的结果集无关，因为 not in 对于内外表都进行了全表扫描，没有使用到索引。not exists 的子查询中可以用到表上的索引

5. drop、delete 和 truncate 的区别

	drop	delete	truncate
速度	快	逐行删除，慢	较快
类型	DDL	DML	DDL
回滚	不可回滚	可回滚	不可回滚
删除内容	删除整个表，数据行、索引都会被删除	表结构还在，删除表的一部分或全部数据	表结构还在，删除表的全部数据

6. MySQL常见约束

• NOT NULL：非空，表示该字段的值必填。通常情况下，最好指定列为 NOT NULL，除非真的需要存储 NULL 值，如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间。
• UNIQUE：唯一，表示该字段的值不可重复
• DEFAULT：默认，表示该字段的值如果不插入会有默认值
• CHECK：检查，mysql 8.0.16 版本后才支持
• PRIMARY KEY：主键，表示该字段的值不可重复并且非空 unique+not null
• FOREIGN KEY：外键，表示该字段的值引用了另外的表的字段（弊大于利，有利于数据一致性，但会导致更新一张表还得去查其外键表，慢，而且复杂不利于开发）

7. 什么是存储过程？

存储过程是一组经过预先编译的sql语句的集合，存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

好处：

• 存储过程在创建的时候直接编译，而sql语句每次使用都要编译，提高执行效率
• 提高了sql语句的重用性
• 减少网络之间的数据传输，节省开销

坏处：

• 存储过程难以调试和扩展，更没有移植性

  《阿里巴巴 Java 开发手册》中指明禁止使用存储过程。存储过程的管理不方便，同时，复用性也没有写在服务层好。

8. MySQL 执行查询的过程

1. 大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

   Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

   而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5 版本开始成为了默认存储引擎。

   下面是 MySQL 执行一条查询语句的具体流程：

   1. 建立连接。客户端通过 TCP 连接发送连接请求到 MySQL 连接器，连接器会对该请求进行权限验证及连接资源分配

   2. 查询缓存。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中，key 是查询的语句，value 是查询的结果。

      • 如果查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。
      • 如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

      注意：

      • 当判断缓存是否命中时，MySQL 不会进行解析查询语句，而是直接使用 SQL 语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。
      • 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。因此大多数情况下建议不要使用查询缓存。
      • 可以将 MySQL 参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定。
      • MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。。

   3. 语法分析（SQL 语法是否写错了）。 如何把语句给到预处理器，检查数据表和数据列是否存在，解析别名看是否存在歧义。

   4. 优化器。优化器是在表里面有多个索引的时候，决定是否使用索引，使用哪个索引；

   5. 交给执行器，将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，最终将结果集返回给客户端。

9. MySQL 执行更新的过程

首先通过连接器进行数据库的连接；

接着更新语句就会把表上所有缓存结果都清空（在一个表上有更新的时候，跟这个表有关的查询缓存会失效，这也就是我们一般不建议使用查询缓存的原因）；

接下来，分析器会通过词法和语法解析知道这是一条更新语句。

优化器决定是否使用索引，使用哪个索引。

执行器负责具体执行，找到更新行，然后更新。

执行器和 InnoDB 引擎在执行 update 语句时的内部流程。以 update T set c=c+1 where ID=2; 为例：

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是”两阶段提交”。为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。

• binlog 存在于Mysql Server 层中，主要用于数据恢复；当数据被误删时，可以通过上一次的全量备份数据加上某段时间的 binlog 将数据恢复到指定的某个时间点的数据。
• redo log 存在于 InnoDB 引擎中，InnoDB 引擎是以插件形式引入 Mysql 的，redo log 的引入主要是为了实现 Mysql 的 crash-safe 能力。

假设 redo log 和 binlog 分别提交，可能会造成用日志恢复出来的数据和原来数据不一致的情况。

• 假设先写 redo log 再写 binlog，即 redo log 没有 prepare 阶段，写完直接置为 commit 状态，然后再写 binlog。那么如果写完 redo log 后 Mysql 宕机了，binlog 没有写成功，重启后系统自动用 redo log 恢复出来的数据就会比 binlog 记录的数据多出一些数据，这就会造成磁盘上数据库数据页和 binlog 的不一致，下次需要用到 binlog 恢复误删的数据时，就会发现恢复后的数据和原来的数据不一致。
• 假设先写 binlog 再写 redo log。如果写完 binlog 后 Mysql 宕机了，redo log没有写成功，那么 binlog 上的记录就会比磁盘上数据页的记录多出一些数据出来，下次用 binlog 恢复数据，就会发现恢复后的数据和原来的数据不一致。

10. MySQL 索引

索引（index）是存储引擎用于高效快速获取数据的一种数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录，如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

索引优化是对查询性能优化最有效的手段。

（1）索引的优缺点

优点

• 索引大大减少了服务器需要扫描的数据量，提高数据检索的效率，降低数据库的IO成本。
• 索引可以帮助服务器避免排序和临时表（索引本身基于B+Tree，已经排好序了），降低排序成本。

缺点

• 时间方面：索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行 INSERT、UPDATE、DELETE时，效率降低。使用索引也不一定能提高查询性能，大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。
• 空间方面：索引需要占物理空间。

（2）索引的类型

从存储结构上来划分：

索引结构	InnoDB是否支持	描述
B+Tree索引	支持	最常见的索引类型，大部分引擎都支持 B+ 树索引
Hash索引	不支持	底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
R-tree(空间索引）	不支持	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)	5.6版本后支持	是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

从应用层次来分：

索引结构	描述
主键索引	数据列不允许重复，不能为NULL，一个表只能有一个主键索引
普通索引	即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引	索引列的值必须唯一，但允许有空值
复合索引	多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
聚集索引	不是一种单独索引类型，而是一种数据存储方式。InnoDB 的聚簇索引是在同一结构中保存了索引和数据行。
非聚集索引	不是聚簇索引，就是非聚簇索引

（3）如何创建索引

创建索引有三种方式。

1、 在执行 CREATE TABLE 时创建索引

CREATE TABLE user_index2 (
    id INT auto_increment PRIMARY KEY,
    first_name VARCHAR (16),
    last_name VARCHAR (16),
    id_card VARCHAR (18),
    information text,
    KEY name (first_name, last_name),
    FULLTEXT KEY (information),
    UNIQUE KEY (id_card)
);

2、 使用 ALTER TABLE 命令去增加索引。

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE 用来创建普通索引、UNIQUE 索引或 PRIMARY KEY 索引。

其中 table_name 是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名 index_name 可自己命名，缺省时，MySQL 将根据第一个索引列赋一个名称。另外，ALTER TABLE 允许在单个语句中更改多个表，因此可以在同时创建多个索引。

3、 使用 CREATE INDEX 命令创建。

CREATE INDEX index_name ON table_name (column_list);

（4）索引底层实现

① B-Tree 索引

B-Tree （B 是 Balanced 的意思）能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中，目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

② B+Tree 索引（MySQL 默认）

B+Tree 是 B-Tree 的改进版本，同时也是 Mysql 索引所采用的存储结构。其数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。

【重要】B+Tree 索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索，通过不断比较，根据指针向下层寻找，最终走到叶子节点找到/没找到指定的记录。

进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。

进行插入删除操作时，由于插入删除操作会破坏平衡树的平衡性，因此在进行插入删除操作之后，需要对树进行分裂、合并、旋转等操作来维护平衡性。

B+Tree 和 B-Tree 的异同：

• B 树的所有节点既存放键（key） 也存放数据（data），而 B+ 树只有叶子节点存放 key 和 data（所有的叶子结点中包含了全部关键字的信息），其他内节点只存放 key。
• B 树的叶子节点都是独立的；B+ 树 的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，
• B+ 树的磁盘读写代价更低：B+ 树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO 读写次数就降低了。
• 由于 B+ 树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是 B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以 B+ 树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

B+Tree 与红黑树的比较

• B+ 树有更低的树高。

  平衡树的树高 O(h)=O(logdN)，其中 d 为每个节点的出度。红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多。

• 磁盘访问原理。

  操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。

  如果数据不在同一个磁盘块上，那么通常需要移动制动手臂进行寻道，而制动手臂因为其物理结构导致了移动效率低下，从而增加磁盘数据读取时间。B+ 树相对于红黑树有更低的树高，进行寻道的次数与树高成正比，在同一个磁盘块上进行访问只需要很短的磁盘旋转时间，所以 B+ 树更适合磁盘数据的读取。

• 磁盘预读特性。

  为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻的节点也能够被预先载入。

③ Hash 索引

基于哈希表实现，只有精确匹配索引所有列的查询（无法用于部分查找和范围查找）才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且 Hash 索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。Hash 索引无法用于排序与分组；

（5）主键索引和二级索引

主键索引：

数据表的主键列使用的就是主键索引。当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6 Byte 的自增主键。

二级索引（辅助索引）：

二级索引又称为辅助索引，其叶子节点存储的数据是主键的值。因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主键索引中进行查找。

1. 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
3. 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

（6）聚簇索引与非聚簇索引

• ==聚集索引==：聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。定位到节点时也就找到了数据。

  优点：

  • 查询速度非常快，定位到索引的节点，就定位到了数据。

  缺点：

  • 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
  • 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

• ==非聚集索引==：非聚集索引即索引结构和数据分开存放的索引。二级索引属于非聚集索引。

  优点：

  • 更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

  缺点：

  • 跟聚集索引一样，非聚集索引也依赖于有序的数据
  • 可能会二次查询(回表) ：这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

  非聚簇索引一定会回表查询吗？

  不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。一个索引包含（覆盖）所有需要查询字段的值，被称之为”覆盖索引”。

  举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select score from student where score > 90的查询时，在索引的叶子节点上，已经包含了 score 信息，不会再次进行回表查询。

（7）覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢，覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

• 如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。
• 再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

（8）前缀索引

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

使用场景：前缀的区分度比较高的情况下。对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大 （MySQL也不允许索引过大），查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

流程是：

• 先计算完整列的选择性 :select count(distinct col_1)/count(1) from table_1
• 再计算不同前缀长度的选择性 :select count(distinct left(col_1,4))/count(1) from table_1
• 找到最优长度之后，创建前缀索引 : create index idx_front on table_1 (col_1(4))

注意，前缀索引可以使得索引更快、更小，但 MySQL 无法使用前缀索引做 order by 和 group by，也无法使用前缀索引做覆盖查询。

（9）联合索引

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

在一个多列 B+Tree 索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，以此类推。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的 order by 、group by 和 distinct 等子句的查询需求，因而多列索引的列顺序至关重要！

举例：

MySQL 使用索引时需要索引有序，假设现在建立了”name，age，school”的联合索引，那么索引的排序为：先按照name 排序，如果 name 相同，则按照 age 排序，如果 age 的值也相等，则按照 school 进行排序。

当进行查询时，此时索引仅仅按照 name 严格有序，因此必须首先使用 name 字段进行等值查询，之后对于匹配到的列而言，其按照 age 字段严格有序，此时可以使用 age 字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

（10）MySQL最左前缀原则

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边。 mysql 会一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配

比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立 (a,b,d,c) 的索引则都可以用到，a,b,d 的顺序可以任意调整。

= 和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式。

（11）explain

explain 用来分析 select 查询语句，查看执行计划。

比较重要的字段有：

• select_type : 查询类型，有普通查询、联合查询、子查询等
• type：判断查询是否高效，是全表扫描还是索引扫描等
• key : 使用的索引
• rows : 扫描的行数

通过 explain，如以下例子：

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

字段	含义	示例
id	SELECT 查询的序列号，是一组数字，表示的是查询中操作表的顺序（多表查询）。	1
select_type	查询类型，常见的取值有 SIMPLE（简单查询，即单表查询，没有表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等	SIMPLE
table	每个查询对应的表名	titles
type	表示表的连接类型，type 字段提供了判断查询是否高效的重要依据依据。通过 type 字段，我们判断此次查询是全表扫描还是索引扫描等。性能由好到差的连接类型为 (system —> const –> eq_ref —> ref —> ref_or_null—> index_merge —> index_subquery –> range –> index —> all )。比如 const 是主键索引或者唯一索引扫描，range 是范围查询，ref 是普通索引扫描，index 是覆盖索引扫描， all 类型是全表扫描……	const
possible_keys	查询中可能用到的索引*(可以把用不到的删掉，降低优化器的优化时间)* 。	PRIMARY
key	MySQL 在当前查询时所真正使用到的索引。	PRIMARY
key_len	索引字段的长度。	59
rows	扫描行的数量。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好。	1
extra	表示额外信息，如 Using where，Using filesort（文件排序，效率低），Using temporary 等。	NULL

（12）为什么建议使用自增长主键作为索引？

结合 B+Tree 的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。

页分裂是指在插入一个数据时，如果数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。而自增主键的插入数据模式，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

自增长主键长度小，普通索引的叶子节点就小，普通索引占用的空间也就小。而如果使用业务上的主键，比如使用身份证号，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

但是自增长键也存在一些问题：

• 规律性太明显，可能会暴露一些信息。（比如用户第一次注册的 id 为 10，下一天它又注册了一次，id 为 100，那么就知道在前一天一共有 90 人左右进行了注册 ）
• 受单表数据量的限制，当数据量大时，可能需要分库分表，自增长键就会存在一定的限制，可能多个表有相同的 id。

（13） 创建索引的原则

1、最左前缀匹配原则，非常重要的原则，mysql 会一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a,b,c,d) 顺序的索引，d 是用不到索引的，如果建立 (a,b,d,c)的索引则都可以用到，a,b,d 的顺序可以任意调整。

2、= 和 in 可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式。

3、尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0。，一般需要 join 的字段我们都要求是 0. 1 以上，即平均 1 条扫描 10 条记录。

4、索引列不能参与计算，比如 from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+ 树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 create_time = unix_timestamp(’2014-05-29’)。

5、尽可能的考虑建立复合索引而不是单列索引。复合索引相当于创建了多个索引，并且更容易形成覆盖索引。当查询多列时，如果没有建立复合索引，则即使建立了多个单列索引，最多也只能走一个索引（MySQL 评估哪个更好就使用哪个）。

6、被频繁更新的字段应该慎重建立索引。虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

（14）索引失效

• 违反最左前缀原则导致索引失效（① 没有从最左边开始，则索引失效 ② 遇到范围查询，后面均失效）

• 使用 != 或 <> 或 NOT IN 或 NOT EXISTS 导致索引失效。运算符导致的索引失效。

  SELECT * FROM `user` WHERE age - 1 = 20;

• 如果对列进行了计算或一些函数, 那么都将不会走索引。

• OR 引起的索引失效（如果前后都有索引，则不会失效）。

  SELECT * FROM `user` WHERE `name` = '张三' OR height = '175';

  可以使用 union 联合查询代替 or 进行查询。

• 模糊搜索导致的索引失效。

  SELECT * FROM `user` WHERE `name` LIKE '%冰';

  当 % 放在匹配字段前是不走索引的，放在后面才会走索引。如果业务中必须要在前面加入%，那么可以采用覆盖索引（即建一个索引包含要查询的所有字段）的方法避免回表。

• 字符串没加单引号引起索引失效。（没加单引号，Mysql 也可以将数字类型转换为字符串类型，但是底层会进行了运算，导致索引失效）

• MySQL 评估后认为走全表扫描比索引更快，则索引失效。

  -- 假设有一个字段是 status，而其中有9个记录为1，1个记录为2
  select * from user where status = 1  -- 会走全表扫描，而不是索引（因为全表扫描更快，索引扫描量相当于全表扫描量，而且还需要回表）。
  select * from user where status = 2  -- 会索引（因为索引更快）。

  注意： is null 和 is not Null 走不走索引也取决于 MySQL 评估走全表扫描好还是走索引好，如果 非 null 值特别多，则 is null 走索引，is not null 不走索引。如果 null 值特别多，则 is null 走索引，is not null 不走索引。

11. MySQL 事务

一条或多条 sql 语句组成一个执行单位，这一组sql语句要么都执行，要么都不执行；

（1）ACID

• 原子性（Atomicity）：一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个 sql 语句执行失败，那么已执行的语句也会回滚，数据库退回到事务前的状态，就像这个事务从来没有执行过一样，也就是说，对于一个事务而言，不可能只执行其中的一部分操作。
• 一致性（Consistency）：事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。数据库总是从一个一致性的状态转换到另外一个一致性的状态。
• 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改，一个事务的执行不受另外一个事务的干扰，通常来说，一个事务所做的修改在最终提交之前，对其他事务时不可见的（但是具体也要看隔离级别的设置状态）。
• 持久性（Durability ）：事务一旦提交后，对数据的修改就是永久的，即便系统故障也不会丢失。

（2）四种隔离级别

事务的并发问题：

• 脏读：事务A访问数据时，读取到了事务B修改了但尚未提交的数据。（事务B没有提交，可能提交成功，但也有可能回滚）
• 不可重复读：事务A在访问数据时，由于事务B对数据进行了修改，使得事务A多次读取到的数据不一致。（违反了事务的一致性）
• 幻读：事务A读取某个范围内的数据时，事务B再该范围内进行了插入或删除，导致事务A再次读取该范围的记录时，会产生幻行。
• 区别：不可重复读侧重于修改，幻读侧重于新增或删除（多了或少量行），脏读是一个事务回滚影响另外一个事务。

如何避免事务的并发问题？（设置隔离级别）

隔离级别	脏读	不可重复读	幻读
read uncommitted（读未提交）	可能发生	可能发生	可能发生
read committed（读已提交）	不可能发生	可能发生	可能发生
repeatable read（可重复读，默认）	不可能发生	不可能发生	可能发生
serializable（串行化）	不可能发生	不可能发生	不可能发生

注意：事务隔离级别越高，性能越差。MySQL 默认采用的 REPEATABLE_READ隔离级别， Oracle 默认采用的 READ_COMMITTED隔离级别

设置隔离级别：

set session|global  transaction isolation level 隔离级别名;

查看隔离级别：

select @@tx_isolation; # mysql默认的隔离级别是repeatable read（可重复读）

（3）事务的实现原理

• MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。

  每提交一个事务必须先将该事务的所有日志写入到重做日志文件进行持久化，数据库就可以通过重做日志来保证事务的原子性和持久性。每当有修改事务时，还会产生 undo log，如果需要回滚，则根据 undo log 的反向语句进行逻辑操作，比如 insert 一条记录就 delete 一条记录。undo log 主要实现数据库的一致性。

• MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是 REPEATABLE-READ ）。

• 保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

（4）MySQL 事务日志

MySQL 日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的是二进制日志和事务日志。

事务日志包括 redo log（重做日志） 和 undo log（回滚日志）。

undo log 指事务开始之前，将所需操作的数据备份到一个地方。undo log 是逻辑日志，记录的是与当前 SQL 逻辑相反的语句，比如 insert 对应 delete，delete 对应 insert，update 对应一个与之相反的 update（将修改前的行放回去）。

redo log 指在事务中操作中，将最新的数据备份到一个地方。redo log是物理日志，记录内容为该数据页更新状态内容（即每个页的更改的物理情况），属于 InnoDB 存储引擎。

① redo log

redo log（重做日志）是 InnoDB 存储引擎独有的，它让 MySQL 拥有了崩溃恢复能力。比如 MySQL 实例挂了或宕机了，重启时，InnoDB 存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。redo log 记录的是事务提交时数据页的物理修改。

redo log 不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入 redo 中。redo log 是为了实现事务的持久性而出现的产物。（当由于发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复）

redo log包括两部分：一个是内存中的日志缓冲(redo log buffer)，另一个是磁盘上的日志文件(redo log file)。mysql 每执行一条 DML/DDL 语句，先将记录存放入日志缓存中，在按一定频率将其刷新到 redo log 里面（一般情况下每一秒中会将重做日志缓存刷新到日志文件）。后续 InnoDB 引擎会在适当的时候再一次性将多个操作记录更新到磁盘里面。这种先写日志，再写磁盘的技术就是 MySQL 里经常说到的WAL(Write-Ahead Logging) 技术。（如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本（随机 IO）、查找成本都很高，数据库性能将会变得非常差 。WSA 解决了此问题）

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么此日志最多可以记录 4GB 的操作，从头开始写，写到末尾就又回到开头循环写。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是日志上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示日志满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。即数据库发生宕机时，数据库不需要重做所有的日志，因为 Checkpoint 之前的页都已经刷新回磁盘。故数据库只需对 CheckPoint 后的重做日志进行恢复，这样也大大缩短了恢复的时间。

checkpoint 所做的事即将缓冲池中的脏页（即在缓冲池中，但还没有刷回到磁盘的页）刷回到磁盘，发生时机有（不一定准确）：

• 数据库关闭时将所有的脏页刷新回磁盘；
• Master Thread 以每秒或每十秒的速度从缓冲池的脏页页表中刷新一定比例的页回磁盘；
• 当脏页的数量太多，导致 InnoDB 存储引擎强制进行 CheckPoint，保证缓冲池中有足够可用的页。

② undo log

想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用回滚日志中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

（5）MySQL 二进制日志 binlog

binlog 是逻辑日志，记录内容是语句的原始逻辑，即更新过程。属于MySQL Server 层。不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。

【重要】MySQL数据库的数据备份、主从复制都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

1. 主从复制：在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重写binlog从而达到主从数据一致。
2. 数据恢复：通过使用mysqlbinlog工具来恢复数据。

MySQL的 binlog 是记录所有数据库表结构变更（例如 CREATE、ALTER TABLE）以及表数据修改（INSERT、UPDATE、DELETE）的二进制日志。binlog 不会记录 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看 MySQL 执行过的所有语句。

MySQL binlog 以事件形式记录，还包含语句所执行的消耗的时间，MySQL 的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

binlog 有三种格式，各有优缺点：

• statement： 基于 SQL 语句的模式，某些语句和函数如 UUID, LOAD DATA INFILE 等在复制过程可能导致数据不一致甚至出错。
• row： 基于行的模式，记录的是行的变化，很安全。但是 binlog 会比其他两种模式大很多，在一些大表中清除大量数据时在 binlog 中会生成很多条语句，可能导致从库延迟变大。
• mixed： 混合模式，根据语句来选用是 statement 还是 row 模式。

	redo log	binlog
实现方式	InnoDB 引擎特有的	MySQL 的 Server 层实现的，所有引擎都可以使用
内容形式	物理日志，记录的是对于每个页的修改	逻辑日志，记录的是这个语句的原始逻辑
空间占用	循环写入，空间固定会用完	追加写入。写到一定大小后会切换到下一个，而不会覆盖以前的日志
写入时机	在事务运行中不断地被写入	只在事务提交完成后进行一次写入
适用场景	适用于崩溃恢复(crash-safe)	适用于主从复制和数据恢复

为什么 binlog 没有 crash-safe 功能？

redo log 和 binlog 有一个很大的区别就是，一个是循环写，一个是追加写。也就是说 redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存的是全量日志。当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innodb 判断哪些数据已经刷盘，哪些数据还没有。但 redo log 不一样，只要刷入磁盘的数据，都会从 redo log 中抹掉，数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了。这就是为什么 redo log 具有 crash-safe 的能力，而 binlog 不具备。

（6）MVCC

对于 一致性非锁定读（Consistent Nonlocking Reads） open in new window的实现，通常做法是加一个版本号或者时间戳字段，在更新数据的同时版本号 + 1 或者更新时间戳。查询时，将当前可见的版本号与对应记录的版本号进行比对，如果记录的版本小于可见版本，则表示该记录可见

在 InnoDB 存储引擎中，多版本控制 (multi versioning)open in new window 就是对非锁定读的实现。如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据，对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)

在 Repeatable Read 和 Read Committed 两个隔离级别下，如果是执行普通的 select 语句（不包括 select ... lock in share mode ,select ... for update）则会使用 一致性非锁定读（MVCC）。并且在 Repeatable Read 下 MVCC 实现了可重复读和防止部分幻读

MVCC， 即多版本并发控制。MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

MVCC 的实现依赖于：隐藏字段、Undo log、Read View。

对于 InnoDB ，在建表时包含 3 个隐藏的列：

• ROW ID：隐藏的自增 ID，如果表没有主键，InnoDB 会自动按 ROW ID 产生一个聚集索引树（如果有主键，则不会生成 ROW ID）。
• 事务 ID：记录最后一次修改该记录的事务 ID（每开启一个事务，都会从数据库中获得一个事务 ID，这个事务 ID 是自增长的，通过 ID 大小，就可以判断事务的时间顺序）。
• 回滚指针：指向这条记录的上一个版本。对记录每次更新后，都会将旧值放到一条 undo 日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被回滚指针属性连接成一个链表，把这个链表称为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务 id，这个信息很重要，在根据 ReadView 判断版本可见性的时候会用到。

**Read View（读视图） **：

ReadView 是事务在使用 MVCC 机制进行快照读操作时产生的读视图。当事务启动时，会生成数据库系统当前的一个快照，InnoDB 为每个事务构造了一个数组，用来记录并维护系统当前活跃事务的 ID（“活跃”指的就是，启动了但还没提交)。

• 使用 READ UNCOMMITTED 隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本行。
• 使用 SERIALIZABLE 隔离级别的事务，InnoDB 规定使用加锁的方式来访问记录。
• 使用 READ COMMNITTED 和 REPEATABLE READ 隔离级别的事务，都必须保证读到已经提交了的事务修改过的记录。假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是需要判断一下版本链中的哪个版本是当前事务可见的（既可能是当前最新的数据，也有可能是该行记录的 undo log 里面的某个版本的数据。），这就是 ReadView 要解决的主要问题（即判断版本的可见性）。

ReadView 中包含四个重要内容：

1. creator_trx_id，创建这个 Read View 的事务 ID。注意：只有在对表中的记录做改动时才会为事务分配事务 ID，否则在一个只读事务中的事务 ID 值都默认为 0。
2. trx_ids，表示在生成 ReadView 时当前系统中活跃的读写事务的事务 ID 列表。
3. up_limit_id，活跃的事务中最小的事务 ID。
4. low_limit_id，表示生成 ReadView 时系统中应该分配给下一个事务的 ID 值。low_limit_id 是系统最大的事务 ID 值，这里要注意是系统中的事务 ID，需要区别于正在活跃的事务 ID。
5. low_limit_id 并不是 trx_ids 中的最大值，事务 ID 是递增分配的。比如，现在有 ID 为 1，2，3 这三个事务，之后 ID 为 3 的事务提交了。那么一个新的读事务在生成 ReadView 时， trx_ids 就包括 1 和 2，up_limit_id 的值是 1，low_limit_id 的值是 4。

ReadView 的规则：

• 如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
• 如果被访问版本的 trx_id 属性值小于 ReadView 中的 up_limit_id 值，表明生成该版本的事务在当前事务生成ReadView 前已经提交，所以该版本可以被当前事务访问。
• 如果被访问版本的 trx_id 属性值大于或等于 ReadView 中的 low_limit_id 值，表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
• 如果被访问版本的 trx_id 属性值在 ReadView 的 up_limit_id 和 low_limit_id 之间，那就需要判断一下 trx_id 属性值是不是在 trx_ids 列表中。
  • 如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问。
  • 如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问

MCCC 流程：

1. 首先获取事务自己的版本号，也就是事务 ID；
2. 获取 ReadView；
3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；
4. 如果不符合 ReadView 规则（即当前版本不可以被访问），就需要从 Undo Log 中获取历史快照（有一个版本链）；
5. 最后返回符合规则的数据。
6. 注意：
   • 在可重复读隔离级别，当前事务中，每次执行查询 sql 时都只会使用第一次生成的一致性视图 Read-View，即该 Read-view 视图在事务结束前都不会变化。（即对于快照数据，总是读取事务开始时的行数据版本）
   • 在读已提交隔离级别，当前事务中，每次执行查询 sql 时都会生成并获取最新一致性视图 Read-View，即该 Read-view 视图在每次查询时都会重新生成。（即对于快照数据，总是读取被锁定行的最新一份快照数据）

解决幻读问题：

• 快照读：通过 MVCC 来进行控制的，不用加锁。按照 MVCC 中规定的“语法”进行增删改查等操作，以避免幻读。MVCC 仅仅解决来于 select 的幻读。
• 当前读：通过 next-key 锁（行锁 + gap 锁）来解决问题的。在 update/delete/insert 命令中的 where 条件查询，并没有用到 MVCC，而是通过锁来解决部分情况的幻读。

12. MySQL 锁?

当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。可以通过锁机制保证数据访问顺序。

（1）MySQL 锁分类

① 行级锁 VS 表级锁 VS 页级锁

MySQL锁类别	资源开销	加锁速度	是否会出现死锁	锁的粒度	并发度
表级锁	小	快	不会	大	低
行级锁	大	慢	会	小	高
页面锁	一般	一般	不会	一般	一般

行级锁（InnoDB 默认）

• 行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
• 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁（MyISAM 默认）

• 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
• 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁

• 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁
• 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

② 共享锁 VS 排他锁

共享锁：又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。一个事务对一个数据对象加了读锁，可以对这个数据对象进行读取操作，但不能进行更新操作。并且在加锁期间其他事务只能对这个数据对象加读锁，不能加写锁。

排他锁：又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

③ 乐观锁 VS 悲观锁

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过 version 的方式来进行锁定。数据库只在更新操作提交的时候对数据检测冲突，如果存在冲突，则数据更新失败。实现方式：一般通过版本号和CAS算法实现。

两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行 retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

（2）隔离级别与锁的关系

隔离级别	实现方式
未提交读	总是读取最新的数据，无需加锁
提交读	读取数据时加共享锁，读取数据后释放共享锁
可重复读	读取数据时加共享锁，事务结束后释放共享锁
串行化	锁定整个范围的键，一直持有锁直到事务结束

（3）行锁是怎么实现的

InnoDB 是基于索引来完成行锁。InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么 InnoDB 将完成表锁。如果索引失效，那么行锁也会升级为表锁！

（4）死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的预防死锁的方法：

1. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；如果业务处理不好可以用分布式事务锁或者使用乐观锁。

常见的解决死锁的方法：

1. 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innbdb_lock_wait_timeout 来设置（默认值为 50s）。但这种方式不太好，如果超时时间设置太大，则等待时间太长；如果超时时间设置太小，可能会出现误伤（比如不是死锁，只是简单的锁等待而已）。
2. 另一种策略是，发起死锁检测（在每个事务请求锁并发生等待时，通过等待图的方式判断图中是否有回路，如果有，则发生了死锁），发现死锁后，主动回滚死锁链条中的某一个事务（将持有最少行级排他锁的事务进行回滚)，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。但是检测死锁也是需要时间成本的。

（5）加锁的建议

• 使用较低的隔离级别
• 设计索引，尽量使用索引去访问数据，加锁更加精确，从而减少锁冲突
• 选择合理的事务大小，给记录显示加锁时，最好一次性请求足够级别的锁。列如，修改数据的话，最好申请排他锁，而不是先申请共享锁，修改时在申请排他锁，这样会导致死锁
• 不同的程序访问一组表的时候，应尽量约定一个相同的顺序访问各表，对于一个表而言，尽可能的固定顺序的获取表中的行。这样大大的减少死锁的机会。
• 尽量使用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
• 不要申请超过实际需要的锁级别
• 数据查询的时候不是必要，不要使用加锁。MySQL 的 MVCC 可以实现事务中的查询不用加锁，优化事务性能：MVCC 只在committed read（读提交）和 repeatable read （可重复读）两种隔离级别
• 对于特定的事务，可以使用表锁来提高处理速度活着减少死锁的可能。

13. MySQL 运维

1. MySQL 主从复制

主从复制是将一个数据库服务器中的数据复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。

主从复制是异步进行的，从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库/某个数据库/某个数据库上的某个表。

优点：

• 提高了数据库的性能。在主服务器上执行写入和更新，在从服务器上向外提供读功能（读写分离），可以动态地调整从服务器的数量，从而调整整个数据库的性能。
• 从服务器相当于进行了数据备份，提高了数据安全。
• 提升了系统的可用性。主库出现故障或宕机后，可以快速切换到从库提供服务。

复制流程：

1. master 在每个事务更新数据时，将该操作记录串行地写入到 binlog 文件中。
2. salve 开启一 个 I/O Thread，该线程在 master 打开一个普通连接，主要工作是 binlog dump process。如果读取的进度已经跟上了 master，就进入睡眠状态并等待 master 产生新的事件。I/O 线程最终的目的是将这些事件写入到中继日志中（Relay log）。I/O 线程将来自 master 的 binlog 存储到中继日志中，中继日志充当缓冲，这样 master 不必等待 slave 执行完成就可以发送下一个 binlog。
3. slave 的 SQL Thread 会读取中继日志，并顺序执行该日志中的 SQL 事件，从而与主数据库中的数据保持一致。

复制过程如下：

MySQL 主从同步解决延时问题：

MySQL 有两个同步机制，一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决主从同步延时问题。

• 半同步复制，也叫 semi-sync 复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。
• 并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

2. 分库分表

为什么要分库分表：当数据库出现瓶颈时，比如出现以下情况，就需要考虑进行分库分表了。

• 大量请求阻塞：在高并发场景下，大量请求都需要操作数据库，导致连接数不够了，请求处于阻塞状态。
• SQL 操作变慢：如果数据库中存在一张上亿数据量的表，一条 SQL 没有命中索引会全表扫描，这个查询耗时会非常久。
• 存储出现问题：业务量剧增，单库数据量越来越大，给存储造成巨大压力。

分表：单表数据量太大时，会严重影响 sql 执行的性能。一般单表到达几百万的时候，性能就会相对差一些了，这时就得分表了。分表就是把一个表的数据放到多个表中，然后查询的时候只查一个表。比如按照项目 id 来分表：将固定数量的项目数据放在一个表中，这样就可以控制每个表的数据量在可控的范围内。

分库：根据经验来讲，一个库最多支持到并发 2000 时就需要扩容了，而且一个健康的单库并发值最好保持在 1000左右。那么可以将一个库的数据拆分到多个库中，访问的时候就访问一个库好了。

优势：

	分库分表前	分库分表后
并发支撑情况	MySQL 单机部署，扛不住高并发	MySQL 从单机到多机，能承受的并发增加了多倍
SQL 执行性能	单表数据量太大，SQL 越跑越慢	单表数据量减少，SQL 执行效率明显提升
磁盘使用情况	MySQL 单机磁盘容量几乎撑满	拆分为多个库，数据库服务器磁盘使用率大大降低

3. 垂直/水平拆分

水平拆分：

• 水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。
  • 每个库的表结构都一样。
  • 每个库的数据都不一样。
  • 所有库的并集是全量数据。
• 水平分表：
  • 每个表的表结构都一样。
  • 每个表的数据都不一样。
  • 所有表的并集是全量数据。

垂直拆分：

• 垂直分库：以表为依据，根据业务将不同表拆分到不同库中。
  • 每个库的表结构都不一样。
  • 每个库的数据也不一样。
  • 所有库的并集是全量数据。
• 垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。
  • 每个表的结构都不一样。
  • 每个表的数据也不一样，一般通过一列（主键/外键）关联。
  • 所有表的并集是全量数据。

两种分库分表的方式：

• 一种是按照 range 来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了。

  range 来分，好处在于说，扩容的时候很简单，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了；缺点，但是大部分的请求，都是访问最新的数据。实际生产用 range，要看场景。

• 或者是按照某个字段 hash 一下均匀分散，这个较为常用。

  hash 分发，好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表

4. 分库分表中间件

• sharding-jdbc

  基于 AOP 原理，在应用程序中对本地执行的 SQL 进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持 Java 语言，性能较高。

• mycat

  基于 Java 语言编写的数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。

  

14. MySQL 性能优化

（1）定位 SQL 语句的性能问题

对于低性能的 SQL 语句的定位，最有效的方法是使用执行计划。 执行计划显示了数据库引擎对于 SQL 语句的执行的详细情况，其中包含了是否使用索引，使用什么索引等相关信息。 MySQL 提供了 explain 命令来查看语句的执行计划。

（2）大表数据查询优化方式

• 通过索引进行优化；
• 使用缓存中间件，memcached, redis；
• 主从复制，读写分离；
• 分库分表

（3）超大分页怎么处理?

采取子查询或者关联查询的方式进行优化。

select * from user where age > 20 limit 1000000,10 ，这条语句需要 load 1000000 数据然后基本上全部丢弃，只取 10 条。

• 可以采用子查询的方式进行修改。

  select * from user where id in (select id from user where age > 20 limit 1000000,10)。

  这样虽然也 load 了一百万的数据,但是由于覆盖索引，要查询的所有字段都在索引中，所以速度会很快。

• 也可以采用关联查询的方式进行修改。

  select a.* from user a, (select id from user where age>20 limit 1000000,10) b where a.id =b.id

  这样虽然也 load 了一百万的数据,但是由于覆盖索引，要查询的所有字段都在索引中，所以速度会很快。

《阿里巴巴Java开发手册》

【推荐】利用延迟关联或者子查询优化超多分页场景。

说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

正例：先快速定位需要获取的 id 段，然后再关联：

SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

（4）慢查询优化

慢查询的优化首先要搞明白慢的原因是什么？ 是查询条件没有命中索引？是 load 了不需要的数据列？还是数据量太大？

• 首先分析语句，看看是否 load 了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

15. mysqldump 指令

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL 语句。

语法︰

mysqldump [options] db_name [tables] > file	-- 导出哪个库的哪些表，如果不加表，表示所有表
mysq1dump [options] --database/-B db1 [db2 db3...]  > file -- 导出哪些库
mysq1dump [options] --all-databases/-A  > file  -- 导出所有库

-- 连接选项（options）
-u    -- 指定用户名
-p    -- 指定密码
-h    -- 指定服务器IP或域名
-P	  -- 指定连接端口

示例：

mysqldump -uroot -p123 project user > user.sql   -- 导出project库的user表,导出文件为user.sql

16. 重启后自增长键的值

• MyISAM 引擎的自增值保存在数据文件中，所以会一直保存着和自增长键的最大值。
• InnoDB 引擎的自增值，
  • 在 MySQL5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id) +步长 作为这个表当前的自增值。
  • 在 MySQL8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

17. 实际生产环境的 B+ 树索引有多少层

一般是 2 ~ 3 层，可以存放约 两千万行 的数据。

页是 InnoDB 磁盘管理的最小单位，在 InnoDB 存储引擎中，默认每个页的大小为 16KB（16*1024 = 16384Byte）。而页里面存放的东西就是一行一行的记录。

假设一行数据的大小是 1k，那么一页就可以存放 16 行这样的数据。众所周知，B+ 树的叶子节点存储真正的记录，而非叶子节点的存在是为了更快速的找到对应记录所在的叶子节点，所以可以简单理解为非叶子节点存放的是键值 + 指针。

假设现有一个 B+ 树只有两层，即一个根节点和若干个叶子节点，如下图：

在上图中，p10 为 B+ 树的根结点，而 p11，p12，p12 为三个叶子结点，每个叶子结点的大小为 16K。

那么对于这棵 B+ 树能够存放多少行数据，其实问的就是这棵 B+ 树的叶子节点中存放的数据量，可以通过下面这个简单的公式来计算：

• 根节点指针数 * 每个叶子节点存放的行记录数

每个叶子节点存放的行记录数就是每页存放的记录数，由于各个数据表中的字段数量都不一样，这里我们就不深究叶子节点的存储结构了，简单按照一行记录的数据大小为 1k 来算的话（实际上现在很多互联网业务数据记录大小通常就是 1K 左右），一页或者说一个叶子节点可以存放 16 行这样的数据。

那么 B+ 数的根节点（非叶子节点）能够存储多少数据呢？

非叶子节点里面存的是主键值 + 指针，我们假设主键的类型是 BigInt，长度为 8 字节，而指针大小在 InnoDB 中设置为 6 字节，这样一共 14 字节。

为了方便行文，这里我们把一个主键值 + 一个指针称为一个单元，这样的话，一页或者说一个非叶子节点能够存放 16384 / 14=1170 个这样的单元。

也就是说一个非叶子节点中能够存放 1170 个指针，即对应 1170 个叶子节点，所以对于这样一棵高度为 2 的 B+ 树，能存放 1170（一个非叶子节点中的指针数） * 16（一个叶子节点中的行数）= 18720 行数据。

OK，分析完高度为 2 的 B+ 树，同样的道理，我们来看高度为 3 的：

根页（p10）可以存放 1170 个指针，然后第二层的每个页（p:11,12,13）也都分别可以存放 1170 个指针。这样一共可以存放 1170 * 1170 个指针，即对应的有 1170 * 1170 个非叶子节点，所以一共可以存放 1170 * 1170 * 16 = 21902400 行记录。 即大约 2000 万条记录。