一、数据库基础

1.1 存储过程

存储过程就像是编程语言中的函数一样，封装了我们的代码（PLSQL，T-SQL）

-------------创建名为GetUserAccount的存储过程----------------
create Procedure GetUserAccount
as
select * from UserAccount
go

-------------执行上面的存储过程----------------
exec GetUserAccount

存储过程的优点：

• 能够将代码封装起来
• 保存在数据库之中
• 让编程语言进行调用
• 存储过程是一个预编译的代码块，执行效率比较高
• 一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率

存储过程的缺点：

• 每个数据库的存储过程语法几乎都不一样，十分难以维护（不通用）
• 业务逻辑放在数据库上，难以迭代

1.2 三大范式

1）第一范式： 列不可分

1NF（第一范式）是对属性具有原子性的要求，不可再分

2）第二范式： 消除非主属性对码的部分函数依赖

2NF（第二范式）是对记录有唯一性的要求，即实体的唯一性，不存在部分依赖，每一列与主键都相关

3）第三范式：消除非主属性对码的传递函数依赖

3NF（第三范式）对字段有冗余性的要求，任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在依赖传递

1.3 数据库索引

数据库索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。

数据库索引的底层数据结构是B+树。在数据结构中，我们最为常见的搜索结构就是二叉搜索树和AVL树(高度平衡的二叉搜索树，为了提高二叉搜索树的效率，减少树的平均搜索长度)了。然而，无论二叉搜索树还是AVL树，当数据量比较大时，都会由于树的深度过大而造成I/O读写过于频繁，进而导致查询效率低下，因此对于索引而言，多叉树结构成为不二选择。特别地，B-Tree的各种操作能使B树保持较低的高度，从而保证高效的查找效率。

使用B+树的原因：查找速度快、效率高，在查找的过程中，每次都能抛弃掉一部分节点，减少遍历个数。（此时，你应该在白纸上画出什么是B+树）

1）索引分类

• 唯一索引：唯一索引不允许两行具有相同的索引值
• 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
• 聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个
• 非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

2）索引的优缺点

优点

• 大大加快数据的检索速度，这也是创建索引的最主要的原因；

• 加速表和表之间的连接；

• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度；

• 空间方面：索引需要占物理空间。

3）创建索引的场景和注意事项

唯一、不为空、经常被查询的字段 的字段适合建索引

适合创建索引：

• 经常作查询选择的字段

• 经常作表连接的字段

• 经常出现在order by, group by, distinct 后面的字段

创建索引需要注意：

• 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
• 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多，字段的离散程度高；
• 索引字段越小越好：数据库的数据存储以页为单位。一页存储的数据越多，一次IO操作获取的数据越大，效率越高。

1.4 事务

事务简单来说：一个 Session 中所进行所有的操作，要么同时成功，要么同时失败；作为单个逻辑工作单元执行的一系列操作，满足四大特性（ACID）：

1. 原子性（Atomicity）：事务作为一个整体被执行 ，要么全部执行，要么全部不执行
2. 一致性（Consistency）：保证数据库状态从一个一致状态转变为另一个一致状态
3. 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行
4. 持久性（Durability）：一个事务一旦提交，对数据库的修改应该永久保存

1）事务并发问题

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

2. 脏读（Dirty read）：一个事务读取了另一个事务未提交的数据（当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。）

3. 不可重复读（Unrepeatableread）：不可重复读的重点是修改，同样条件下两次读取结果不同，也就是说，被读取的数据可以被其它事务修改（指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。）

4. 幻读（Phantom read）：幻读的重点在于新增或者删除，同样条件下两次读出来的记录数不一样（幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。）

2）事务的隔离级别

隔离级别决定了一个session中的事务可能对另一个session中的事务的影响。ANSI标准定义了4个隔离级别，MySQL的InnoDB都支持，分别是：

1. 读未提交（READ UNCOMMITTED）：最低级别的隔离，通常又称为dirty read，它允许一个事务读取另一个事务还没 commit 的数据，这样可能会提高性能，但是会导致脏读问题；
2. 读已提交（READ COMMITTED）：在一个事务中只允许对其它事务已经 commit 的记录可见，该隔离级别不能避免不可重复读问题；
3. 可重复读（REPEATABLE READ）：在一个事务开始后，其他事务对数据库的修改在本事务中不可见，直到本事务 commit 或 rollback。但是，其他事务的 insert/delete 操作对该事务是可见的，也就是说，该隔离级别并不能避免幻读问题。在一个事务中重复 select 的结果一样，除非本事务中 update 数据库。
4. 序列化（SERIALIZABLE）：最高级别的隔离，只允许事务串行执行。

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	√	√	√
不可重复读（read-committed）	×	√	√
可重复读（repeatable-read）	×	×	√
串行化（serializable）	×	×	×

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

MySql 的事务支持

MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关：

• MyISAM：不支持事务，用于只读程序提高性能；
• InnoDB：支持ACID事务、行级锁、并发；
• Berkeley DB：支持事务。

1.5 视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。相比于多表查询，它使得我们获取数据更容易。

如下两种场景一般会使用到视图：

1. 不希望访问者获取整个表的信息，只暴露部分字段给访问者，所以就建一个虚表，就是视图。
2. 查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异。

注意：这个视图是在数据库中创建的 而不是用代码创建的。

1.6 触发器

触发器是与表相关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据库的完整性。

1.7 SQL 约束

• NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
• UNIQUE: 控制字段内容不能重复，一个表允许有多个 Unique 约束。
• PRIMARY KEY: 也是用于控制字段内容不能重复，但它在一个表只允许出现一个。
• FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
• CHECK: 用于控制字段的值范围。

1.8 SQL解析顺序

1.9 其他概念

1）超键、候选键、主键、外键分别是什么？

• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
• 候选键（候选码）：是最小超键，即没有冗余元素的超键。
• 主键（主码）：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
• 外键：在一个表中存在的另一个表的主键称此表的外键。

2）主键、自增主键、主键索引与唯一索引概念区别

1. 主键：指字段 唯一、不为空值 的列；
2. 主键索引：指的就是主键，主键是索引的一种，是唯一索引的特殊类型。创建主键的时候，数据库默认会为主键创建一个唯一索引；
3. 自增主键：字段类型为数字、自增、并且是主键；
4. 唯一索引：索引列的值必须唯一，但允许有空值。主键是唯一索引，这样说没错；但反过来说，唯一索引也是主键就错误了，因为唯一索引允许空值，主键不允许有空值，所以不能说唯一索引也是主键。

3）主键就是聚集索引吗？主键和索引有什么区别？

**主键是一种特殊的唯一性索引，其可以是聚集索引，也可以是非聚集索引。**在SQLServer中，主键的创建必须依赖于索引，默认创建的是聚集索引，但也可以显式指定为非聚集索引。InnoDB作为MySQL存储引擎时，默认按照主键进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。所以，对于聚集索引来说，你创建主键的时候，自动就创建了主键的聚集索引。

4）drop、delete与truncate的区别？

drop 直接删除表；truncate 删除表中数据，再插入时自增长id又从1开始 ；delete 删除表中数据，可以加where字句。

drop table 属于DDL（Data Definition Language，数据库定义语言）、不可回滚、不可带 where、表内容和结构删除、删除速度快

truncate table 属于DDL（Data Definition Language，数据库定义语言）、不可回滚、不可带 where、表内容删除、删除速度快

delete from 属于DML（Data Manipulation Language，数据操纵语言）、可回滚、可带where、表结构在，表内容要看where执行的情况、删除速度慢,需要逐行删除

简单来说

• 不再需要一张表的时候，用drop
• 想删除部分数据行时候，用delete，并且带上where子句
• 保留表而删除所有数据的时候用truncate

二、MYSQL

2.1 MYSQL 逻辑架构

Mysql逻辑架构图主要分三层：

（1）第一层负责连接处理，授权认证，安全等等

（2）第二层负责编译并优化SQL

（3）第三层是存储引擎。

QA: 一条SQL查询语句在MySQL中如何执行的？

1. 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限会先查询缓存(MySQL8.0 版本以前)。
2. 如果没有缓存，分析器进行词法分析，提取 sql 语句中 select 等关键元素，然后判断 sql 语句是否有语法错误，比如关键词是否正确等等。
3. 最后优化器确定执行方案进行权限校验，如果没有权限就直接返回错误信息，如果有权限就会调用数据库引擎接口，返回执行结果。

2.2 存储引擎

通过命令查看下MYSQL支持的存储引擎

mysql> show engines;

Engine	Support	Comment	Transactions	XA	Savepoints
FEDERATED	NO	Federated MySQL storage engine	NULL	NULL	NULL
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO

这么多引擎中，经常遇到的也就是MyISAM和InnoDB这两个。

1）MyISAM

*MyISAM*是MySQL官方提供的存储引擎，其特点是支持全文索引，查询效率比较高，缺点是不支持事务、使用表级锁。

MyISAM 物理文件结构为：

• .frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
• .MYD (MYData) 文件：MyISAM 存储引擎专用，用于存储MyISAM 表的数据
• .MYI (MYIndex)文件：MyISAM 存储引擎专用，用于存储MyISAM 表的索引相关信息

2）InnoDB

InnoDB在5.5版本后成为了Mysql的默认存储引擎，特点是支持ACID事务、支持外键、支持行级锁提高了并发效率。

InnoDB 物理文件结构为：

• .frm 文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等

• .ibd 文件或 .ibdata 文件：这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。

独享表空间存储方式使用.ibd文件，并且每个表一个.ibd文件 共享表空间存储方式使用.ibdata文件，所有表共同使用一个.ibdata文件（或多个，可自己配置）

3）MyISAM和InnoDB的对比

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	是	是

1. InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
3. InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. InnoDB 不保存表的具体行数，执行select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

QA: 一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

• 如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；

• 如果表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。

QA: 哪个存储引擎执行 select count(*) 更快，为什么?

MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

• 在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。
• 在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。

InnoDB 中 count(*) 语句是在执行的时候，全表扫描统计总数量，所以当数据越来越大时，语句就越来越耗时了，为什么 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上？这跟 InnoDB 的事务特性有关，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

2.3 MYSQL的基本存储结构

MySQL的基本存储结构是页 (记录都存在页里边)

这里的各个数据页可以组成一个双向链表， 而每个数据页中的记录又可以组成一个单向链表。

每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。而以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写select * from user where col_name = 'xxx'这样没有进行任何优化的sql语句，默认会这样做：

1. 定位到记录所在的页：需要遍历双向链表，找到所在的页
2. 从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了

很明显，在数据量很大的情况下这样查找会很慢！这样的时间复杂度为O（n）。

我们分析下，要找到id为8的记录简要步骤：

record_type 属性，它的各个取值代表的意思如下：

• 0 ：普通的用户记录
• 1 ：目录项记录
• 2 ：最小记录
• 3 ：最大记录

有上图的“页33”看出，“页30”号存储的是id从1~320的数据，“页32”存储的是id大于320的数据，于是我们进入了“页30”， 从“页30”看出我们要找的8，在“页28”下（存储了id从5~12的数据）。然后我们到了“页28”找到了id为8的用户数据。

很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过 “目录” 就可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为O(logn)）

其实底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。

2.4 MYSQL的索引

**索引可以大幅增加数据库的查询的性能，**在实际业务场景中，或多或少都会使用到。

但是索引是有如下2个代价的:

• 需要额外的磁盘空间来保存索引
• 对于插入、更新、删除等操作由于更新索引会增加额外的开销

因此索引比较适合用在读多写少的场景。

1）索引分类

数据结构实现角度

• **B+树：**b+树比较适合用作'>'或'<'这样的范围查询，是MySQL中最常使用的一种索引实现。
• **R-tree：**是一种用于处理多维数据的数据结构，可以对地理数据进行空间索引。不过实际业务场景中使用的比较少。
• **Hash：**是使用散列表来对数据进行索引，Hash方式不像Btree那样需要多次查询才能定位到记录，因此Hash索引的效率高于B-tree，但是不支持范围查找和排序等功能.实际使用的也比较少。
• **FullText：**就是我们前面提到的全文索引，是一种记录关键字与对应文档关系的倒排索引。

物理存储角度

• 聚集索引（clustered index）： 数据直接存储在叶子节点上。

• 非聚集索引（non-clustered index），也叫辅助索引（secondary index）： 叶子节点上存储的是指向数据的地址，而不是数据。

  聚集索引和非聚集索引都是B+树结构

逻辑角度

• 唯一索引：就是索引列中的值必须是唯一的，但是允许出现空值。这种索引一般用来保证数据的唯一性，比如保存账户信息的表，每个账户的id必须保证唯一，如果重复插入相同的账户id时会MySQL返回异常。
• 主键索引：是一种特殊的唯一索引，但是它不允许出现空值。
• 普通索引：与唯一索引不同，它允许索引列中存在相同的值。例如学生的成绩表，各个学科的分数是允许重复的，就可以使用普通索引。
• **联合索引：**就是由多个列共同组成的索引。一个表中含有多个单列的索引并不是联合索引，联合索引是对多个列字段按顺序共同组成一个索引。**应用联合索引时需要注意最左原则，就是Where查询条件中的字段必须与索引字段从左到右进行匹配。**比如，一个用户信息表，用姓名和年龄组成了联合索引，如果查询条件是姓名等于张三，那么满足最左原则；如果查询条件是年龄大于20，由于索引中最左的字段是姓名不是年龄，所以不能使用这个索引。
• **全文索引：**前面提到了，MyISAM引擎中实现了这个索引，在5.6版本后InnoDB引擎也支持了全文索引，并且在5.7.6版本后支持了中文索引。全文索引只能在CHAR,VARCHAR,TEXT类型字段上使用，底层使用倒排索引实现。要注意对于大数据量的表，生成全文索引会非常消耗时间也非常消耗磁盘空间。

2）B Tree和B+Tree

B Tree

• 度(Degree)-节点的数据存储个数
• 叶节点具有相同的深度
• 叶节点的指针为空
• 节点中的数据key从左到右递增排列

B+ Tree

• 非叶子节点不存储data，只存储key，可以增大度
• 叶子节点不存储指针
• 顺序访问指针，提高区间访问的性能

3）聚簇索引和非聚簇索引

InnoDB使用的是聚簇索引。InnoDB引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录（对于主索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行），或者说，InnoDB的数据文件本身就是主键索引文件，这样的索引被称为“聚簇索引”，一个表只能有一个聚簇索引。

MyISAM使用的是非聚簇索引。MyISAM引擎的索引文件和数据文件是分离的。MyISAM引擎索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址。索引文件与数据文件分离，这样的索引称为"非聚簇索引"

4）主索引（主键索引）和辅助索引（普通索引）

主索引是指主键索引，键值不可能重复；辅助索引则是普通索引，键值可能重复。

我们以示例学生表中的name列建立辅助索引，它的索引结构跟主键索引的结构有很大差别，在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照ASCII码进行排序，第二行的整数是主键的值。

这就意味着，对name列进行条件搜索，需要两个步骤：

① 在辅助索引上检索name，到达其叶子节点获取对应的主键；

② 使用主键在主索引上再进行对应的检索操作

这也就是所谓的“回表查询”

具体见上方“3）聚簇索引和非聚簇索引”中InnoDB的聚簇索引和辅助索引结构示意图

5）覆盖索引（Covering Index）

覆盖索引（Covering Index）,或者叫索引覆盖， 也就是平时所说的不需要回表操作。

• 就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
• 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据，当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含（覆盖）满足查询结果的数据就叫做覆盖索引。

# 例如，学生表中的name和birthday是联合索引，通过name索引条件查询 name和birthday，此处不需要“回表”获取整行数据
mysql> select name, birthday from stu where name = 'Jim'

• 判断标准

  使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index，MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

2.5 MYSQL的事务

1）MVCC 多版本并发控制

MySQL的大多数事务型存储引擎实现都不是简单的行级锁。基于提升并发性考虑，一般都同时实现了多版本并发控制（MVCC），包括Oracle、PostgreSQL。只是实现机制各不相同。

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

MVCC 的实现是通过保存数据在某个时间点的快照来实现的。也就是说不管需要执行多长时间，每个事物看到的数据都是一致的。

典型的MVCC实现方式，分为乐观（optimistic）并发控制和悲观（pressimistic）并发控制。下边通过 InnoDB的简化版行为来说明 MVCC 是如何工作的。

InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现。这两个列，一个保存了行的创建时间，一个保存行的过期时间（删除时间）。当然存储的并不是真实的时间，而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

REPEATABLE READ（可重读）隔离级别下MVCC如何工作：

• SELECT

  InnoDB会根据以下两个条件检查每行记录

• • InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在开始事务之前已经存在要么是事务自身插入或者修改过的
  • 行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除

• 只有符合上述两个条件的才会被查询出来

• INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号

• DELETE：InnoDB为删除的每一行保存当前系统版本号作为行删除标识

• UPDATE：InnoDB为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识

保存这两个额外系统版本号，使大多数操作都不用加锁。使数据操作简单，性能很好，并且也能保证只会读取到符合要求的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。

MVCC 只在 COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作。

2）事务日志

InnoDB 使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无须在每个事务提交时把缓冲池的脏块刷新(flush)到磁盘中。

事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机 IO。

InnoDB 假设使用常规磁盘，随机 IO 比顺序 IO 昂贵得多，因为一个 IO 请求需要时间把磁头移到正确的位置，然后等待磁盘上读出需要的部分，再转到开始位置。

InnoDB 用日志把随机 IO 变成顺序 IO。一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB 可以重放日志并且恢复已经提交的事务。

InnoDB 使用一个后台线程智能地刷新这些变更到数据文件。这个线程可以批量组合写入，使得数据写入更顺序，以提高效率。

事务日志可以帮助提高事务效率：

• 使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。
• 事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序 I/O，而不像随机 I/O 需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。
• 事务日志持久以后，内存中被修改的数据在后台可以慢慢刷回到磁盘。
• 如果数据的修改已经记录到事务日志并持久化，但数据本身没有写回到磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这一部分修改的数据。

目前来说，大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。

3）事务的实现

事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。MySQL 中支持事务的存储引擎有 InnoDB 和 NDB。

事务的实现就是如何实现 ACID 特性。

事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现 。

事务日志包括：重做日志 redo 和回滚日志 undo

• redo log（重做日志） 实现持久化和原子性

  在innoDB的存储引擎中，事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是DBA们口中常说的“日志先行”(Write-Ahead Logging)。当事务提交之后，在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。

  在系统启动的时候，就已经为redo log分配了一块连续的存储空间，以顺序追加的方式记录Redo Log，通过顺序IO来改善性能。所有的事务共享redo log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起。

• undo log（回滚日志） 实现一致性

  undo log 主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。

  Undo记录的是已部分完成并且写入硬盘未完成的事务，默认情况下回滚日志是记录下表空间中的（共享表空间或者独享表空间）

二种日志均可以视为一种恢复操作，redo_log 是恢复提交事务修改的页操作，而 undo_log 是回滚行记录到特定版本。二者记录的内容也不同，redo_log 是物理日志，记录页的物理修改操作，而 undo_log 是逻辑日志，根据每行记录进行记录。

QA: 你知道 MySQL 有多少种日志吗？

• 错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
• 查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
• 慢查询日志：设置一个阈值，将运行时间超过该值的所有 SQL 语句都记录到慢查询的日志文件中。
• 二进制日志：记录对数据库执行更改的所有操作。
• 中继日志：中继日志也是二进制日志，用来给 slave 库恢复
• 事务日志：重做日志 redo 和回滚日志 undo

4）MySQL 对分布式事务的支持

分布式事务的实现方式有很多，既可以采用 InnoDB 提供的原生的事务支持，也可以采用消息队列来实现分布式事务的最终一致性。这里我们主要聊一下 InnoDB 对分布式事务的支持。

MySQL 从 5.0.3 InnoDB 存储引擎开始支持XA协议的分布式事务。一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚。

在MySQL中，使用分布式事务涉及一个或多个资源管理器和一个事务管理器。

如图，MySQL 的分布式事务模型。模型中分三块：应用程序（AP）、资源管理器（RM）、事务管理器（TM）:

• 应用程序：定义了事务的边界，指定需要做哪些事务；
• 资源管理器：提供了访问事务的方法，通常一个数据库就是一个资源管理器；
• 事务管理器：协调参与了全局事务中的各个事务。

分布式事务采用两段式提交（two-phase commit）的方式：

• 第一阶段所有的事务节点开始准备，告诉事务管理器ready。
• 第二阶段事务管理器告诉每个节点是commit还是rollback。如果有一个节点失败，就需要全局的节点全部rollback，以此保障事务的原子性。

2.6 MYSQL的锁

1）MYSQL锁的分类

从对数据操作的类型分类：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响
• 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分类：

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）；
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）；
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

	行锁	表锁	页锁
MyISAM		√
BDB		√	√
InnoDB	√	√
Memory		√

2）MyISAM的锁

MyISAM 的表锁有两种模式：

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

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

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

3）InnoDB的锁

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

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

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

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

索引失效会导致行锁变表锁。比如 vchar 查询不写单引号的情况。

4）加锁机制

乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式

悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

5）锁模式(InnoDB有三种行锁的算法)

1. 记录锁(Record Locks)：单个行记录上的锁。对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；

2. 间隙锁（Gap Locks）：当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做“间隙”。InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。

   使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。

   GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

3. 临键锁(Next-key Locks)：临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。(临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。)

   Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

   对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

2.7 MYSQL 分区、分表、分库

1）MYSQL分区

分区类型及操作

• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。mysql将会根据指定的拆分策略，,把数据放在不同的表文件上。相当于在文件上,被拆成了小块.但是,对外给客户的感觉还是一张表，透明的。

  按照 range 来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，比如交易表啊，销售表啊等，可以根据年月来存放数据。可能会产生热点问题，大量的流量都打在最新的数据上了。

  range 来分，好处在于说，扩容的时候很简单。

• LIST分区：类似于按RANGE分区，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。

• HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

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

• KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

看上去分区表很帅气，为什么大部分互联网还是更多的选择自己分库分表来水平扩展咧？

• 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
• 一旦数据并发量上来，如果在分区表实施关联，就是一个灾难
• 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控

随着业务的发展，业务越来越复杂，应用的模块越来越多，总的数据量很大，高并发读写操作均超过单个数据库服务器的处理能力怎么办？

这个时候就出现了数据分片，数据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中。数据分片的有效手段就是对关系型数据库进行分库和分表。

区别于分区的是，分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是mysql内部实现。分库分表和分区并不冲突，可以结合使用。

2）MYSQL分表

分表有两种分割方式，一种垂直拆分，另一种水平拆分。

• 垂直拆分

  垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表。然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。

• 水平拆分(数据分片)

  单表的容量不超过500W，否则建议水平拆分。是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。

  水平分割的几种方法：

• • 使用MD5哈希，做法是对UID进行md5加密，然后取前几位（我们这里取前两位），然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。
  • 还可根据时间放入不同的表，比如：article_201601，article_201602。
  • 按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。
  • 根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二 个表user_0001中，随用户增加，直接添加用户表就行了。

3）MYSQL分库

为什么要分库?

数据库集群环境后都是多台 slave，基本满足了读取操作; 但是写入或者说大数据、频繁的写入操作对master性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题，所以就会考虑分库。

一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上， 通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。

优点：

• 减少增量数据写入时的锁对查询的影响
• 由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短

但是它无法解决单表数据量太大的问题

分库分表后的难题

分布式事务的问题，数据的完整性和一致性问题。

4）主从复制

复制的基本原理

• slave 会从 master 读取 binlog 来进行数据同步

三个步骤

1. master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
2. salve 将 master 的 binary log events 拷贝到它的中继日志（relay log）;
3. slave 重做中继日志中的事件，将改变应用到自己的数据库中。MySQL 复制是异步且是串行化的。

复制的基本原则

• 每个 slave只有一个 master
• 每个 salve只能有一个唯一的服务器 ID
• 每个master可以有多个salve

复制的最大问题

• 延时

2.8 MYSQL调优原则

一般MySQL调优有以下4个维度：

• 针对数据库设计、表结构设计以及索引设置维度进行的优化。
• 对业务中使用的SQL语句进行优化，例如调整Where查询条件。
• 对mysql服务的配置进行优化，例如对链接数的管理，对索引缓存、查询缓存、排序缓存等各种缓存大小进行优化。
• 对硬件设备和操作系统设置进行优化，例如调整操作系统参数、禁用Swap、增加内存、升级固态硬盘等等。

这里主要关注前两个维度

1）表结构和索引的优化

**第1个原则：**要在设计表结构时，考虑数据库的水平与垂直扩展能力，提前规划好未来1年的数据量、读写量的增长，规划好分库分表方案。比如设计用户信息表，预计1年后用户数据10亿条，写QPS约5000，读QPS30000，可以设计按UID纬度进行散列，分为4个库每个库32张表，单表数据量控制在KW级别。

**第2个原则：**要为字段选择合适的数据类型，在保留扩展能力的前提下，优先选用较小的数据结构。例如保存年龄的字段，要使用TINYINT而不要使用INT。

**第3个原则：**可以将字段多的表分解成多个表，必要时增加中间表进行关联。假如一张表有4、50个字段显然不是一个好的设计。

**第4个原则：**是设计关系数据库时需要满足第三范式，但为了满足第三范式，我们可能会拆分出多张表。而在进行查询时需要对多张表进行关联查询，有时为了提高查询效率，会降低范式的要求，在表中保存一定的冗余信息，也叫做反范式。但要注意反范式一定要适度。

**第5个原则：**要擅用索引，比如为经常作为查询条件的字段创建索引、创建联合索引时要根据最左原则考虑索引的复用能力，不要重复创建索引；要为保证数据不能重复的字段创建唯一索引等等。不过要注意索引对插入、更新等写操作是有代价的，不要滥用索引。比如像性别这样唯一很差的字段就不适合建立索引。

**第6个原则：**列字段尽量设置为Not Null，MySQL难以对使用Null的列进行查询优化，允许Null会使索引、索引统计和值更加复杂。允许Null值的列需要更多的存储空间，还需要MySQL内部进行特殊处理。

2）SQL语句进行优化的原则

**第1个原则：**要找的最需要优化的SQL语句。要么是使用最频繁的语句，要么是优化后提高最明显的语句，可以通过查询MySQL的慢查询日志来发现需要进行优化的SQL语句。

**第2个原则：**要学会利用MySQL提供的分析工具。例如使用Explain来分析语句的执行计划，看看是否使用了索引，使用了哪个索引，扫描了多少记录，是否使用文件排序等等。或者利用Profile命令来分析某个语句执行过程中各个分步的耗时。

**第3个原则：**要注意使用查询语句是要避免使用Select *，而是应该指定具体需要获取的字段。原因一是可以避免查询出不需要使用的字段，二是可以避免查询列字段的元信息。

**第4个原则：**是尽量使用Prepared Statements，一个是性能更好，另一个是可以防止SQL注入。

**第5个原则：**是尽量使用索引扫描来进行排序，也就是尽量在有索引的字段上进行排序操作。

2.9 MYSQL性能分析

1）MySQL Query Optimizer

1. MySQL 中有专门负责优化 SELECT 语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的 Query 提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是 DBA 认为是最优的，这部分最耗费时间）
2. 当客户端向 MySQL 请求一条 Query，命令解析器模块完成请求分类，区别出是 SELECT 并转发给 MySQL Query Optimizer时，MySQL Query Optimizer 首先会对整条 Query 进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息（如果有），看显示 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。

2）MySQL常见瓶颈

• CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈：top，free，iostat 和 vmstat来查看系统的性能状态

3）性能下降SQL慢 执行时间长 等待时间长 原因分析

• 查询语句写的烂
• 索引失效（单值、复合）
• 关联查询太多join（设计缺陷或不得已的需求）
• 服务器调优及各个参数设置（缓冲、线程数等）

4）MySQL常见性能分析手段

在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

性能瓶颈定位

我们可以通过 show 命令查看 MySQL 状态及变量，找到系统的瓶颈：

mysql> show status ——显示状态信息（扩展show status like ‘XXX’）

mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’）

mysql> show innodb status ——显示InnoDB存储引擎的状态

mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等

shell> mysqladmin variables -u username -p password——显示系统变量

shell> mysqladmin extended-status -u username -p password——显示状态信息

5）Explain（执行计划）

使用 Explain 关键字可以模拟优化器执行SQL查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈，通过explain可以看到：

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

使用方式： explain [SQL语句]

mysql> explain select col1, col2 from t1;

explain各字段解释

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

• • id相同，执行顺序从上往下
  • id全不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  • id部分相同，执行顺序是先按照数字大的先执行，然后数字相同的按照从上往下的顺序执行

• select_type（查询类型，用于区别普通查询、联合查询、子查询等复杂查询）

  • SIMPLE ：简单的select查询，查询中不包含子查询或UNION
  • PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY
  • SUBQUERY：在select或where列表中包含了子查询
  • DERIVED：在from列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里
  • UNION：若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在from子句的子查询中，外层select将被标记为DERIVED
  • UNION RESULT：从UNION表获取结果的select

• table（显示这一行的数据是关于哪张表的）

• type（显示查询使用了那种类型，从最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）

  tip: 一般来说，得保证查询至少达到range级别，最好到达ref

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

• possible_keys（显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用）

• key

• key_len

• ref（显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）

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

• Extra（包含不适合在其他列中显示但十分重要的额外信息）

  • using filesort: 说明mysql会对数据使用一个外部的索引排序，不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中
  • Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
  • using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作
  • using where：使用了where过滤
  • using join buffer：使用了连接缓存
  • impossible where：where子句的值总是false，不能用来获取任何元祖
  • select tables optimized away：在没有group by子句的情况下，基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
  • distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作

explain 示例解析

1. 第一行（执行顺序 4）：id 列为 1，表示是 union 里的第一个 select，select_type 列的 primary 表示该查询为外层查询，table 列被标记为，表示查询结果来自一个衍生表，其中 derived3 中 3 代表该查询衍生自第三个 select 查询，即 id 为 3 的 select。【select d1.name......】
2. 第二行（执行顺序 2）：id 为 3，是整个查询中第三个 select 的一部分。因查询包含在 from 中，所以为 derived。【select id,name from t1 where other_column=''】
3. 第三行（执行顺序 3）：select 列表中的子查询 select_type 为 subquery，为整个查询中的第二个 select。【select id from t3】
4. 第四行（执行顺序 1）：select_type 为 union，说明第四个 select 是 union 里的第二个 select，最先执行【select name,id from t2】
5. 第五行（执行顺序 5）：代表从 union 的临时表中读取行的阶段，table 列的<union1,4>表示用第一个和第四个 select 的结果进行 union 操作。【两个结果 union 操作】

6）慢查询日志

TODO

7）Show Profile 分析查询

TODO

参考文章： MySQL 三万字精华总结 + 面试100 问，吊打面试官绰绰有余 (opens new window)

QA: 百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

1. 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
2. 然后删除其中无用数据（此过程需要不到两分钟）
3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
4. 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。