基础

什么是事务？MySQL 事务四大特性 事务的隔离级别

事务是一组操作，组成这组操作的各个单元，要么全都成功要么全都失败，这个特性就是事务。
在 MySQL 中，事务是在引擎层实现的，只有使用 innodb 引擎的数据库或表才支持事务。

• 原子性（Atomicity）：原子性指的就是 MySQL 中的包含事务的操作要么全部成功、要么全部失败回滚，因此事务的操作如果成功就必须要全部应用到数据库，如果操作失败则不能对数据库有任何影响。

• 一致性（Consistency）：一致性指的是一个事务在执行前后其状态一致。比如 A 和 B 加起来的钱一共是 1000 元，那么不管 A 和 B 之间如何转账，转多少次，事务结束后两个用户的钱加起来还得是 1000，这就是事务的一致性。

• 隔离性（Isolation）：隔离性需要重点说一下，当多个事务同时进行时，就有可能出现脏读(dirty read)、不可重复读(non-repeatable read)、幻读(phantom read) 的情况，为了解决这些并发问题，提出了隔离性的概念。

• 持久性（Durability）：持久性指的是一旦事务提交，那么发生的改变就是永久性的，即使数据库遇到特殊情况比如故障的时候也不会产生干扰。

脏读：事务 A 读取了事务 B 更新后的数据，但是事务 B 没有提交，然后事务 B 执行回滚操作，那么事务 A 读到的数据就是脏数据 简而言之：事务A读取到了事务B 未提交的内容
不可重复读：事务 A 进行多次读取操作，事务 B 在事务 A 多次读取的过程中执行更新操作并提交，提交后事务 A 读到的数据不一致。
幻读：事务 A 将数据库中所有学生的成绩由 A -> B，此时事务 B 手动插入了一条成绩为 A 的记录，在事务 A 更改完毕后，发现还有一条记录没有修改，那么这种情况就叫做出现了幻读。

SQL的隔离级别：

• 读未提交（Read uncommitted）：读未提交指的是一个事务在提交之前，它所做的修改就能够被其他事务所看到。
• 读已提交（Read committed）：读已提交指的是一个事务在提交之后，它所做的变更才能够让其他事务看到。
• 可重复读（Repeatable read）：可重复读指的是一个事务在执行的过程中，看到的数据是和启动时看到的数据是一致的。未提交的变更对其他事务不可见。
• 串行化（Serializable）：顾名思义是对于同一行记录，写会加写锁，读会加读锁。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

这四个隔离级别可以解决 脏读、不可重复读、幻读 这三类问题。总结如下：

其中隔离级别由低到高是：读未提交 < 读已提交 < 可重复读 < 串行化
【注】隔离级别越高，越能够保证数据的完整性和一致性，但是对并发的性能影响越大。大多数数据库的默认级别是读已提交(Read committed)，比如 Sql Server、Oracle ，但是 MySQL的默认隔离级别是 可重复读(repeatable-read)。

能说下myisam 和 innodb的区别吗？

在 MySQL 中，事务是在引擎层实现的，只有使用 innodb 引擎的数据库或表才支持事务。
InnoDB 是 MySQL 默认支持的存储引擎，支持事务、行级锁定和外键。

MyISAM 存储引擎的特点

在 5.1 版本之前，MyISAM 是 MySQL 的默认存储引擎，MyISAM 并发性比较差，使用的场景比较少，主要特点是

• 不支持事务操作，ACID 的特性也就不存在了，这一设计是为了性能和效率考虑的。
• 不支持外键操作，如果强行增加外键，MySQL 不会报错，只不过外键不起作用。
• 锁粒度为表锁，MyISAM 默认的锁粒度是表级锁，所以并发性能比较差，加锁比较快，锁冲突比较少，不太容易发生死锁的情况。
• SELECT 性能较高增删改查性能方面：SELECT 性能较高，适用于查询较多的情况
• MyISAM 会在磁盘上存储三个文件，文件名和表名相同，扩展名分别是 .frm(存储表定义)、.MYD(MYData,存储数据)、MYI(MyIndex,存储索引)。这里需要特别注意的是 MyISAM 只缓存索引文件，并不缓存数据文件。
• MyISAM 支持的索引类型有 全局索引(Full-Text)、B-Tree 索引、R-Tree 索引

Full-Text 索引：它的出现是为了解决针对文本的模糊查询效率较低的问题。
B-Tree 索引：所有的索引节点都按照平衡树的数据结构来存储，所有的索引数据节点都在叶节点
R-Tree索引：它的存储方式和 B-Tree 索引有一些区别，主要设计用于存储空间和多维数据的字段做索引,目前的 MySQL 版本仅支持 geometry 类型的字段作索引，相对于 BTREE，RTREE 的优势在于范围查找。

数据库所在主机如果宕机，MyISAM 的数据文件容易损坏，而且难以恢复。

InnoDB 存储引擎的特点

自从 MySQL 5.1 之后，默认的存储引擎变成了 InnoDB 存储引擎，相对于 MyISAM，InnoDB 存储引擎有了较大的改变，它的主要特点是

• 支持事务操作，具有事务 ACID 隔离特性，默认的隔离级别是可重复读(repetable-read)、通过MVCC（并发版本控制）来实现的。能够解决脏读和不可重复读的问题。
• 支持外键，InnoDB 支持外键操作。
• 锁粒度为行锁，InnoDB 默认的锁粒度行级锁，并发性能比较好，会发生死锁的情况。
• 和 MyISAM 一样的是，InnoDB 存储引擎也有 .frm文件存储表结构 定义，但是不同的是，InnoDB 的表数据与索引数据是存储在一起的，都位于 B+ 数的叶子节点上，而 MyISAM 的表数据和索引数据是分开的。
  InnoDB 有安全的日志文件，这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题，保证数据的一致性。
  InnoDB 和 MyISAM 支持的索引类型相同，但具体实现因为文件结构的不同有很大差异。
  增删改查性能方面，如果执行大量的增删改操作，推荐使用 InnoDB 存储引擎，它在删除操作时是对行删除，不会重建表。

MyISAM 和 InnoDB 存储引擎的对比

• 锁粒度方面：由于锁粒度不同，InnoDB 比 MyISAM 支持更高的并发；InnoDB 的锁粒度为行锁、MyISAM 的锁粒度为表锁、行锁需要对每一行进行加锁，所以锁的开销更大，但是能解决脏读和不可重复读的问题，相对来说也更容易发生死锁
• 可恢复性上：由于 InnoDB 是有事务日志的，所以在产生由于数据库崩溃等条件后，可以根据日志文件进行恢复。而 MyISAM 则没有事务日志。
• 查询性能上：MyISAM 要优于 InnoDB，因为 InnoDB 在查询过程中，是需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行；而 MyISAM 可以直接定位到数据所在的内存地址，可以直接找到数据。
• 表结构文件上：MyISAM 的表结构文件包括：.frm(表结构定义),.MYI(索引),.MYD(数据)；而 InnoDB 的表数据文件为:.ibd和.frm(表结构定义)；

image

SQL 的执行顺序

#-----正常的SQL顺序
SELECT DISTINCT <select_list>
FROM 
  <left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>


#-----MySQL执行的顺序
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT <select_list>
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

• 1、FROM 连接
  对FROM子句中的左表<left_table>和右表<right_table>执行连接，会形成笛卡尔积，这时候会产生一个虚表VT1(virtual table)
• 2、ON 筛选
  对虚拟表 VT1 进行ON筛选，只有那些符合<join_condition>的行才被插入虚拟表VT2;
• 3、JOIN 连接
  如果是 OUTER JOIN(left join、right join) ，那么这一步就将添加外部行，如果是 left join 就把 ON 过滤条件的左表添加进来，如果是 right join ，就把右表添加进来，从而生成新的虚拟表 VT3。如果FROM子句包含两个以上的表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1~步骤3，直到处理完所有的表;
• 4、WHERE 过滤
  对虚拟表VT3应用WHERE过滤条件，只有符合<where_condition>的记录才会被插入虚拟表VT4。
• 5、GROUP BY
  根据 group by 字句中的列，会对 VT4 中的记录进行分组操作，产生虚拟机表 VT5。
• 6、HAVING
  紧跟着 GROUP BY 字句后面的是 HAVING，使用 HAVING 过滤，会把符合条件的放在 VT6
• 7、SELECT
  执行 SELECT 语句，将 VT6 中的结果按照 SELECT 进行筛选，生成 VT7
• 8、DISTINCT
  在第八步中，会对 TV7 生成的记录进行去重操作，生成 VT8。事实上如果应用了 group by 子句那么 distinct 是多余的。
• 9、ORDER BY
  将上一个虚拟表按照 <order_by_condition> 排序，此时返回的一个游标，而不是虚拟表。SQL是基于集合的，集合不会预先对行进行排序，它只是成员的逻辑集合，成员的顺序是无关紧要的。对表进行排序的查询可以返回一个对象，这个对象包含特定的物理顺序的逻辑组织。这个对象就叫游标。正因为返回值是游标，那么使用ORDER BY子句查询不能应用于表达式。
• 10、LIMIT
  取出指定街行的记录，产生虚拟表VT11，并返回给查询用户

【注】每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者（客户端应用程序或者外部查询）不可用。只是最后一步生成的表才会返回 给调用者。

Mysql的索引有哪些

从数据结构角度

1、B+树索引(O(log(n)))：关于B+树索引，可以参考 MySQL索引背后的数据结构及算法原理
2、hash索引：
a 仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询
b 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引
c 只有Memory存储引擎显示支持hash索引
3、FULLTEXT索引（现在MyISAM和InnoDB引擎都支持了）
4、R-Tree索引（用于对GIS数据类型创建SPATIAL索引）

从物理存储角度

1、聚簇索引（聚集索引）（clustered index）

聚簇索引 就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。
　　Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

2、非聚集索引（non-clustered index）

在聚簇索引之上创建的索引称之为辅助索引，如果建立的索引不能满足要查询的字段，辅助索引访问数据总是需要 二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。
　　Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

聚集索引和非聚集索引的区别如下：
　　1) 聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致，聚集索引表记录的排列顺序与索引的排列顺序一致，优点是查询速度快，因为一旦
具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后。
　　2) 聚集索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索
引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，
降低了执行速度。非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的

从逻辑角度

1、主键索引：主键索引是一种特殊的唯一索引，不允许有空值（一般情况下是聚簇索引）
2、普通索引或者单列索引
3、复合索引（多列索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
4、唯一索引或者非唯一索引

那你知道什么是覆盖索引和回表吗？

首先回表，上面有隐示的提到过：如果建立的索引不能满足要查询的字段，辅助索引访问数据总是需要 二次查找。

由于普通索引无法直接定位行记录，通常情况下，需要扫描两遍索引树。先通过普通索引定位到主键值，再通过聚集索引定位到行记录。这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树效率更低。

什么是覆盖索引(Covering index)？

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

• 理解方式一：select的数据列只用从索引中就能够获取到，不必读取没有必要多余的数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列 要被 所建索引 覆盖。

• 理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个数据行，毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果 的数据 就叫做覆盖索引。

索引的优缺点

索引是一种快速查找的数据结构

优点

• 创建唯一性索引，保证数据库表中每一行数据的唯一性
• 大大加快数据的检索速度，这也是创建索引的最主要的原因
• .加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

缺点

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

锁的类型有哪些呢

按照锁操作类型

• 读锁（共享锁）
  针对同一份数据，多个读操作可以同时进行而不会相互影响，在读锁释放前，会阻塞对同一表的写操作。
• 写锁（排它锁）
  写锁是排他的，当前写操作没有释放前，写锁会阻塞其他的写和读操作。

按照锁粒度

• 行锁
• 表锁
• 页锁

谈谈 SQL 优化的经验（常问）

我个人认为呢其实SQL优化不仅仅是对于SQL的优化，还有对索引的优化。什么是索引的优化呢，比如说我们怎么写SQL能更多的利用上索引，避免全表扫描。我们怎么建立索引能避免回表查询（查一次表和查两次表的速度 必定会有差距）等等。因为对于数据库来说，索引是一个能大大提升查询效率的数据结构（能有效减少磁盘IO），

那么怎么提升SQL的查询速度 我个人认为主要在于平常 怎么写SQL 和 怎么建立索引两个方面来控制（这两者缺一不可）

查询语句无论是使用哪种判断条件 等于、小于、大于， WHERE 左侧的条件查询字段不要使用函数或者表达式
使用 EXPLAIN 命令优化你的 SELECT 查询，对于复杂、效率低的 sql 语句，我们通常是使用 explain sql 来分析这条 sql 语句，这样方便我们分析，进行优化。
当你的 SELECT 查询语句只需要使用一条记录时，要使用 LIMIT 1
不要直接使用 SELECT *，而应该使用具体需要查询的表字段，因为使用 EXPLAIN 进行分析时，SELECT * 使用的是全表扫描，也就是 type = all。
为每一张表设置一个 ID 属性
避免在 WHERE 字句中对字段进行 NULL 判断
避免在 WHERE 中使用 != 或 <> 操作符
使用 BETWEEN AND 替代 IN
为搜索字段创建索引
选择正确的存储引擎，InnoDB 、MyISAM 、MEMORY 等
使用 LIKE %abc% 不会走索引，而使用 LIKE abc% 会走索引
对于枚举类型的字段(即有固定罗列值的字段)，建议使用ENUM而不是VARCHAR，如性别、星期、类型、类别等
拆分大的 DELETE 或 INSERT 语句
选择合适的字段类型，选择标准是 尽可能小、尽可能定长、尽可能使用整数。
字段设计尽可能使用 NOT NULL
进行水平切割或者垂直分割
“
水平分割：通过建立结构相同的几张表分别存储数据
垂直分割：将经常一起使用的字段放在一个单独的表中，分割后的表记录之间是一一对应关系。

高级

explain关键字

mysql> explain select * from employee where id = 1;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.32-log |
+------------+
1 row in set (0.03 sec)

【id】

id是select查询的序列号，是一组数字，表示的是查询中执行select子句或者操作表的执行顺序，有三种情况：id相同、id不同、id相同又不同。

1、id相同的情况下，数据表的执行顺序由上往下依次执行；
2、id不同的情况下，如果是子查询，id的序号递增，id值越大优先级越高，越先被执行；
3、id相同又不同的情况下，id如果相同，可以认为是一组，从上往下执行，在所有组中，id值越大，优先级越高，越先被执行；

select_type

select_type的类型主要有六个：SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT
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（union的结果集）

【type】

type主要有：ALL、INDEX、RANGE、REF、EQ_REF、CONST、SYSTEM、NULL这几种值；
以上值从最好到最差依次是：system > const > eq_ref > ref > range > index > all

• 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：全表扫描

【possible_keys和key】

• possible_keys：显示可能应用到这张表的索引，有一个或者多个；查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际用到。
• key：实际使用的索引，如果该值为NULL，则没有使用索引；如果查询中使用了覆盖索引，则该索引仅出现在key列表中。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不丢失精确性的情况下，长度越短越好；
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref

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

【rows】

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

【Extra】

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

什么原因会导致行锁升级为表锁

为什么索引失效会升级成表锁

个人理解，加索引后，读取数据按索引查找行，行锁；不加索引，会查询整张表，为了避免被其他线程进来，所以锁住整张表

文章参考

• https://www.cnblogs.com/xieshilin/p/12734780.html
• https://blog.csdn.net/u013887008/article/details/93377939
• https://www.cnblogs.com/shoshana-kong/p/10517488.html
• https://www.cnblogs.com/jiawen010/p/11805241.html
• https://www.jianshu.com/p/8991cbca3854
• https://niuzheng.net/archives/1678/
• https://mp.weixin.qq.com/s/oWNrLHwqM-0ObuYbuGj98A
• https://www.bilibili.com/video/BV12b411K7Zu
• https://blog.csdn.net/bin0503/article/details/88607311