2022-03-20 22:45 上海大学大数据开发工程师

关注

【面试-八股文】mysql 万字总结，助你吊打面试官

大家好，我是温大大

前段时间大家在面试过程中，经常被问到数据库相关的问题。

像：sql怎么优化，解释下数据库常见锁的，having 和 where区别等等。

所以温大大爆肝1天2夜。

肝了「万字」从数据库基础知识、到数据索引、索、事务以及面试高频面试题。

包你从sql入门到入土，其他面试汇总：

Linux 万字总结

网络 100道高频面试题

欢迎加入温大大面试群，找到温大大，让我帮你规划下学习线路 & 职业规划线路，帮你升职加薪。

建议可以先收藏，然后遇到有不会了查看目录，直接跳到该目录进行查阅。

基础

0.0 数据准备
0.1 关联 inner/left/right/full join
0.2 数据库的三大范式

索引

1.1 什么是索引
1.2 索引的优缺点？
1.3 索引的作用？
1.4 索引的数据结构
1.5 索引的分类
1.6 索引的设计原则
1.7 索引的失效原则
1.8 哪些场景能建立索引
1.9 哪些场景不能建立索引
1.10 什么是最左匹配原则？
1.11 什么是聚集索引？
1.12 什么是覆盖索引？
1.13 什么是前缀索引？
1.14 什么是分库分表？
1.15 什么是分区表？

2.1 共享锁和排他锁是什么
2.2 乐观锁和悲观锁是什么

事务

3.1 事务四大特性
3.2 事务隔离级别有哪些

关键词

4.1 having 和 where区别？
4.2 exist 和 in的区别？
4.3 truncate、delete与drop区别？
4.4 bin log/redo log/undo log 有什么区别？
4.5 int(10)和char(10)的区别？
4.6 preparedStatement和statement的区别？
4.7 union 和union all的区别？
4.8 数据库查询语言 DQL/DML/DCL 区别？

MySQL 底层原理

5.1 查询执行流程
5.2 更新执行过程
5.3 MySQL架构

引擎

6.1 MyISAM
6.2 InnoDB
6.3 MEMORY
6.4 MERGE
6.5 Archive
6.6 引擎选择

0 关联

0.0 数据准备

表创建

drop table if exists test_a;
CREATE TABLE `test_a` (
 `id` varchar(10) NOT NULL,
 `username` varchar(10) NOT NULL,
 `password` varchar(10) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

drop table if exists test_a_description;
CREATE TABLE `test_a_description` (
 `id` varchar(10) NOT NULL,
 `age` varchar(10) ,
 `address` varchar(50) ,
 `parent_id` varchar(10) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据创建

insert into test_a values('1','小明','11');
insert into test_a values('2','宁宁','22');
insert into test_a values('3','敏敏','33');
insert into test_a values('6','生生','66');

insert into test_a_description values('1','10','aaa','1');
insert into test_a_description values('2','20','bbb','2');
insert into test_a_description values('3','30','ccc','3');
insert into test_a_description values('4','40','ddd','4');

0.1 关联

内连接（inner join）

典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。
select * from 表A inner join 表B on 判断条件;

外连接

左外连接（left join）以左表为主表（查询全部），右表为辅表（没有的显示null）
SQL：select * from 表A left join 表B on 判断条件;

右外连接（right join）
以右表为主表（查询全部），左表为辅表（没有的显示null）
SQL：select * from 表A right join 表B on 判断条件;

全连接（full join）

两个表的所有数据都展示出来
SQL：select * from 表A full join 表B on 判断条件;

联合(union / union all）

union 操作符合并的结果集，不会允许重复值，如果允许有重复值的话，使用UNION ALL.
SQL：

select * from A   
union
select * from B

0.2 数据库的三大范式

第1范式

确保数据库表字段的原子性。
比如字段 userInfo: 广东省 10086' ，依照第一范式必须拆分成 userInfo: 广东省 userTel:10086两个字段。

第2范式

首先要满足第一范式，另外包含两部分内容，一是表必须有一个主键；二是非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。
举个例子。假定选课关系表为student_course(student_no, student_name, age, course_name, grade, credit)，主键为(student_no, course_name)。其中学分完全依赖于课程名称，姓名年龄完全依赖学号，不符合第二范式，会导致数据冗余（学生选n门课，姓名年龄有n条记录）、插入异常（插入一门新课，因为没有学号，无法保存新课记录）等问题。
可以拆分成三个表：学生：student(stuent_no, student_name, 年龄)；课程：course(course_name, credit)；选课关系：student_course_relation(student_no, course_name, grade)。

第3范式

首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
假定学生关系表为Student(student_no, student_name, age, academy_id, academy_telephone)，主键为"学号"，其中学院id依赖于学号，而学院地点和学院电话依赖于学院id，存在传递依赖，不符合第三范式。
可以把学生关系表分为如下两个表：学生：(student_no, student_name, age, academy_id)；学院：(academy_id, academy_telephone)。

2NF和3NF的区别

2NF依据是非主键列是否完全依赖于主键，还是依赖于主键的一部分。
3NF依据是非主键列是直接依赖于主键，还是直接依赖于非主键。

1 索引

1.1 什么是索引

索引是存储引擎用于提高数据库表的访问速度的一种「数据结构」。

1.2 索引的优缺点

优点：

加快数据查找的速度
为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度
加快表与表之间的连接

缺点：

建立索引需要占用物理空间
会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

1.3 索引的作用？

数据是存储在磁盘上的，查询数据时。
如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。
有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。

1.4 索引的数据结构

索引的数据结构主要有「B+树」和「哈希表」
InnoDB引擎的索引类型有「B+树索引」和「哈希索引」
默认的索引类型为「B+树索引」

B+树索引

B+ 树是基于「B 树」和「叶子节点」顺序访问指针进行实现，它具有B树的平衡性，并且通过「顺序访问指针」来提高区间查询的性能。
在 B+ 树中，节点中的 key 从「左到右递」增排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。
进行查找操作时，首先在根节点进行「二分查找」，找到key所在的指针，然后「递归」地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的数据项。
MySQL 数据库使用最多的索引类型是「BTREE索引」，底层基于「B+树｜数据结构来实现。

哈希索引

哈希索引是基于「哈希表」实现的
对于每一行数据，存储引擎会对索引列进行哈希计算得到「哈希码」
并且哈希算法要尽量保证不同的列值计算出的「哈希码值」是不同的，将哈希码的值作为哈希表的key值
将指向数据行的「指针」作为哈希表的value值。这样查找一个数据的时间复杂度就是「O(1)」，一般多用于精确查找。

Hash索引和B+树索引的区别？

哈希索引「不支持排序」，因为哈希表是无序的。
哈希索引「不支持范围查找」。
哈希索引「不支持模糊查询」及「多列索引的最左前缀匹配」。
因为哈希表中会存在哈希冲突，所以哈希索引的「性能是不稳定的」，而B+树索引的性能是「相对稳定的」，每次查询都是从根节点到叶子节点。

为什么B+树比B树更适合实现数据库索引？

由于B+树的数据都存储在「叶子结点」中，叶子结点均为索引，方便扫库，只需要扫一遍叶子结点即可
但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次「中序遍历」按序来扫
所以B+树更加适合在「区间查询」的情况，而在数据库中基于范围的查询是「非常频繁」的，所以通常B+树用于数据库索引。
B+树的节点只存储「索引key」值，具体信息的地址存在于「叶子节点」的地址中。
这就使以页为单位的索引中可以存放更多的节点，减少更多的「I/O支出」。
B+树的查询「效率更加稳定」，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

1.5 索引的分类

1、主键索引：名为primary的唯一非空索引，不允许有空值。
2、唯一索引：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：唯一约束的列可以为null且可以存在多个null值。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。
3、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。
4、全文索引：只有在MyISAM引擎上才能使用，只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。

1.6 索引的设计原则

设计原则

尽量使用「短索引」，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。
索引「不是越多越好」，每个索引都需要额外的物理空间，维护也需要花费时间。
利用「最左前缀原则」。

1.7 索引的失效原则

导致索引失效的情况

对于组合索引，不是使用组合索引最左边的字段，则不会使用索引
以%开头的like查询如%abc，无法使用索引；非%开头的like查询如abc%，相当于范围查询，会使用索引
查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效，例：where col=a
判断索引列是否不等于某个值时，例：where col!=123
对索引列进行运算，查询条件使用or连接，也会导致索引失效，例：where col_a=123 or col_b=456

1.8 哪些场景能建立索引

经常用于查询的字段
经常用于连接的字段建立索引，可以加快连接的速度
经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

1.9 哪些场景不能建立索引

where条件中用不到的字段不适合建立索引
表记录较少
需要经常增删改
参与列计算的列不适合建索引
区分度不高的字段不适合建立索引，如性别等

1.10 什么是最左匹配原则？

最左匹配原则

如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。
当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引。
对(a,b,c)建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。
对(a,b,c,d)建立索引，查询条件为a = 1 and b = 2 and c > 3 and d = 4，那么a、b和c三个字段能用到索引，而d无法使用索引。因为遇到了范围查询。

1.11 什么是聚集索引？

InnoDB使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。
聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。
对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为6个字节，它的值会随着数据的插入自增。

1.12 什么是覆盖索引？

select的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。
不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以MySQL使用b+树索引做覆盖索引。
对于使用了覆盖索引的查询，在查询前面使用explain，输出的extra列会显示为using index。
比如user_like 用户点赞表，组合索引为(user_id, blog_id)，user_id和blog_id都不为null。

explain select blog_id from user_like where user_id = 13;

explain结果的Extra列为Using index，查询的列被索引覆盖，并且where筛选条件符合最左前缀原则，通过索引查找就能直接找到符合条件的数据，不需要回表查询数据。

explain select user_id from user_like where blog_id = 1;

explain结果的Extra列为Using where; Using index，查询的列被索引覆盖，where筛选条件不符合最左前缀原则，无法通过索引查找找到符合条件的数据，但可以通过索引扫描找到符合条件的数据，也不需要回表查询数据。

1.13 什么是前缀索引？

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。
前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。
创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。
建立前缀索引的方式：

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

1.14 什么是分库分表？

原因：索引不能提升性能时，引入分库分表

当单表的数据量达到1000W或100G以后，优化索引、添加从库等可能对数据库性能提升效果不明显，此时就要考虑对其进行切分了。切分的目的就在于减少数据库的负担，缩短查询的时间。
数据切分可以分为两种方式：垂直划分和水平划分。

垂直划分

垂直划分数据库是根据业务进行划分，例如购物场景，可以将库中涉及商品、订单、用户的表分别划分出成一个库，通过降低单库的大小来提高性能。同样的，分表的情况就是将一个大表根据业务功能拆分成一个个子表，例如商品基本信息和商品描述，商品基本信息一般会展示在商品列表，商品描述在商品详情页，可以将商品基本信息和商品描述拆分成两张表。

优点：行记录变小，数据页可以存放更多记录，在查询时减少I/O次数。
缺点：主键出现冗余，需要管理冗余列；会引起表连接JOIN操作，可以通过在业务服务器上进行join来减少数据库压力；依然存在单表数据量过大的问题。

水平划分

水平划分是根据一定规则，例如时间或id序列值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致，但是数据得以拆分，从而提升性能。

优点：单库（表）的数据量得以减少，提高性能；切分出的表结构相同，程序改动较少。
缺点：分片事务一致性难以解决，跨节点join性能差，逻辑复杂数据分片在扩容时需要迁移

1.15 什么是分区表？

分区表是一个独立的逻辑表，但是底层由多个物理子表组成。
当查询条件的数据分布在某一个分区的时候，查询引擎只会去某一个分区查询，而不是遍历整个表。在管理层面，如果需要删除某一个分区的数据，只需要删除对应的分区即可。

分区表类型

按照范围分区。

CREATE TABLE test_range_partition(
       id INT auto_increment,
       createdate DATETIME,
       primary key (id,createdate)
   ) 
   PARTITION BY RANGE (TO_DAYS(createdate) ) (
      PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
      PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
      PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
      PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
      PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
      PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
      PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
      PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
      PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
      PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
      PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
      PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
   );

list分区

create table test_list_partiotion
   (
       id int auto_increment,
       data_type tinyint,
       primary key(id,data_type)
   )partition by list(data_type)
   (
       partition p0 values in (0,1,2,3,4,5,6),
       partition p1 values in (7,8,9,10,11,12),
       partition p2 values in (13,14,15,16,17)
   );

hash分区

create table test_hash_partiotion
   (
       id int auto_increment,
       create_date datetime,
       primary key(id,create_date)
   )partition by hash(year(create_date)) partitions 10;

分区的问题

打开和锁住所有底层表的成本可能很高。

例子：
当查询访问分区表时，MySQL 需要打开并锁住所有的底层表，
这个操作在分区过滤之前发生，所以无法通过分区过滤来降低此开销，
会影响到查询速度。可以通过批量操作来降低此类开销，比如批量插入、
LOAD DATA INFILE和一次删除多行数据

维护分区的成本可能很高。

例子：
重组分区，会先创建一个临时分区，
然后将数据复制到其中，最后再删除原分区。

所有分区必须使用相同的存储引擎。

2 锁

2.1 共享锁和排他锁是什么

共享锁

例子：我们进入洗手间只是想洗手的话，我们一般不会锁门。而其他人也可以进来洗手、化妆等。但是，其他人是不可以进来上厕所的。这就是共享锁，也叫读锁。
就是只读不写。

用法

select * from table where id<6 lock in share mode;--共享锁
select ... lock in share mode;
当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁

例子：如果我们进入洗手间是为了上厕所，那么任何人不能再进来做任何事。这就是排他锁，也叫写锁。

用法

select * from table where id<6 for update;--排他锁
select ... for update;
在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁

加锁原则

拿MySql的InnoDB引擎来说，对于insert、update、delete等操作。会自动给涉及的数据加排他锁；
对于一般的select语句，InnoDB不会加任何锁，事务可以通过以下语句给显示加共享锁或排他锁。
共享锁：SELECT ... LOCK IN SHARE MODE;
排他锁：SELECT ... FOR UPDATE;

事务

3.1 事务四大特性

事务特性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

原子性：指事务包含的所有操作要么全部成功，要么全部失败回滚。
一致性：指一个事务执行之前和执行之后都必须处于一致性状态。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。
隔离性：跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。
持久性：指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。

3.2 事务隔离级别有哪些

问题

脏读：是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
幻读：是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行，就像产生幻觉一样，这就是发生了幻读。
不可重复读：是指在对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，另一个事务修改了数据并提交了。

区别

不可重复读和脏读的区别是：脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。
不可重复读和幻读都是读取了另一条已经提交的事务，不同的是不可重复读的重点是修改，幻读的重点在于新增或者删除。

事务隔离就是为了解决上面的问题

Serializable (串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。
Repeatable read (可重复读)：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读的问题。
Read committed (读已提交)：一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。
Read uncommitted (读未提交)：所有事务都可以看到其他未提交事务的执行结果。

4 关键词

4.1 having 和 where区别？

二者作用的对象不同，where子句作用于「表和视图」，having作用于「组」。
where在数据「分组前」进行过滤，having在数据「分组后」进行过滤。

4.2 exist 和 in的区别？

exists用于对外表记录做筛选。exists会遍历外表，将外查询表的每一行，代入内查询进行判断。当exists里的条件语句能够返回记录行时，条件就为真，返回外表当前记录。反之如果exists里的条件语句不能返回记录行，条件为假，则外表当前记录被丢弃。

select a.* from A awhere exists(select 1 from B b where a.id=b.id)

in是先把后边的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，代入外查询去查找。

select * from Awhere id in(select id from B)

子查询的表比较大的时候，使用exists可以有效减少总的循环次数来提升速度；
当外查询的表比较大的时候，使用in可以有效减少对外查询表循环遍历来提升速度。

4.3 truncate、delete与drop区别？

相同

truncate和不带where子句的delete、以及drop都会删除表内的数据。
drop、truncate都是DDL语句（数据定义语言），执行后会自动提交。

不同

truncate 和 delete 只删除数据不删除表的结构；
drop 语句将删除表的结构被依赖的约束、触发器、索引；
一般来说，执行速度: drop > truncate > delete。

4.4 bin log/redo log/undo log 有什么区别？

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

bin log（二进制日志）
redo log（重做日志）
undo log（回滚日志）

bin log

bin log是MySQL数据库级别的文件，记录对MySQL数据库执行修改的所有操作，不会记录select和show语句，主要用于恢复数据库和同步数据库。

redo log

redo log是innodb引擎级别，用来记录innodb存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据恢复。当数据库发生故障，innoDB存储引擎会使用redo log恢复到发生故障前的时刻，以此来保证数据的完整性。将参数innodb_flush_log_at_tx_commit设置为1，那么在执行commit时会将redo log同步写到磁盘。

undo log

除了记录redo log外，当进行数据修改时还会记录undo log，undo log用于数据的撤回操作，它保留了记录修改前的内容。通过undo log可以实现事务回滚，并且可以根据undo log回溯到某个特定的版本的数据，实现MVCC

4.5 int(10)和char(10)的区别？

int(10) 表示「显示」数据的长度，
char(10)表示「存储」数据的长度。

4.6 preparedStatement和statement的区别？

任何时候使用preparedStatement而不是statement
PreparedStatement预编译，防止SQL注入
PreparedStatement多次使用可提高效率

4.7 union 和union all的区别？

union会对结果集进行处理排除掉相同的结果
union all 不会对结果集进行处理，不会处理掉相同的结果

4.8 数据库查询语言 DQL/DML/DCL 区别？

DQL（Data Query Language）数据查询语言DQL由SELECT子句，FROM子句，WHERE子句组成
DML（Data Manipulation Language）数据操纵语言DML包含INSERT，UPDATE，DELETE
DDL（Data Definition Language）数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：CREATE TABLE/VIEW/INDEX/SYN/CLUSTER DDL操作是隐性提交的！不能rollback
DCL（Data Control Language）数据控制语言（DCL）是用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、DENY、REVOKE等语句，在默认状态下，只有sysadmin、dbcreator、db_owner或db_securityadmin等角色的成员才有权利执行数据控制语言。

5 mysql执行原理

5.1 查询执行流程

查询语句的执行流程如下：权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。

举个例子，查询语句如下：

select * from user where id > 1 and name = '温大大';

首先检查权限，没有权限则返回错误；
MySQL8.0以前会查询缓存，缓存命中则直接返回，没有则执行下一步；
词法分析和语法分析。提取表名、查询条件，检查语法是否有错误；
两种执行方案，先查 id > 1 还是 name = '大彬'，优化器根据自己的优化算法选择执行效率最好的方案；
校验权限，有权限就调用数据库引擎接口，返回引擎的执行结果。

5.2 更新执行流程

更新语句执行流程如下：分析器、权限校验、执行器、引擎、redo log（prepare状态）、binlog、redo log（commit状态）

举个例子，更新语句如下：

update user set name = '温大大' where id = 1;

先查询到 id 为1的记录，有缓存会使用缓存。
拿到查询结果，将 name 更新为大彬，然后调用引擎接口，写入更新数据，innodb 引擎将数据保存在内存中，同时记录redo log，此时redo log进入 prepare状态。
执行器收到通知后记录binlog，然后调用引擎接口，提交redo log为commit状态。
更新完成。

问：为什么记录完redo log，不直接提交，而是先进入prepare状态？
答：假设先写redo log直接提交，然后写binlog，写完redo log后，机器挂了，binlog日志没有被写入，那么机器重启后，这台机器会通过redo log恢复数据，但是这个时候binlog并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

5.3 MySQL架构

MySQL主要分为

Server 层
存储引擎层：

Server 层：

主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

存储引擎：

主要负责数据的存储和读取。server 层通过api与存储引擎进行通信。 Server 层基本组件
连接器：当客户端连接 MySQL 时，server层会对其进行身份认证和权限校验。
查询缓存: 执行查询语句的时候，会先查询缓存，先校验这个 sql 是否执行过，如果有缓存这个 sql，就会直接返回给客户端，如果没有命中，就会执行后续的操作。
分析器: 没有命中缓存的话，SQL 语句就会经过分析器，主要分为两步，词法分析和语法分析，先看 SQL 语句要做什么，再检查 SQL 语句语法是否正确。
优化器：优化器对查询进行优化，包括重写查询、决定表的读写顺序以及选择合适的索引等，生成执行计划。
执行器：首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会根据执行计划去调用引擎的接口，返回结果。

6 引擎

6.1 MyISAM

简介

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。
MyISAM拥有较高的插入、查询速度，但不支持事务。
MyISAM表格可以被压缩，而且它们支持全文搜索。不支持事务，而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。在进行updata时进行表锁，并发量相对较小。如果执行大量的SELECT，MyISAM是更好的选择。
MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大。
MyISAM缓存在内存的是索引，不是数据。而InnoDB缓存在内存的是数据，相对来说，服务器内存越大，InnoDB发挥的优势越大。

特性

大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持
当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成
每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上
BLOB和TEXT列可以被索引
NULL被允许在索引的列中，这个值占每个键的0~1个字节
所有数字键值以高字节优先被存储以允许一个更高的索引压缩
每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
可以把数据文件和索引文件放在不同目录
每个字符列可以有不同的字符集
有VARCHAR的表可以固定或动态记录长度
VARCHAR和CHAR列可以多达64KB
使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）

6.2 InnoDB

简介

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。
InnoDB 采用MVCC（多版本并发控制）来支持高并发，并实现了四个标准的隔离级别。其默认级别是REPEATABLE READ（可重复读），并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁是的 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
InnoDB 表是基于聚簇索引建立的。InnoDB 的索引结构和 MySQL 的其他存储引擎有很大不同，聚簇索引对主键查询有很高的性能。不过它的二级索引（secondary index，非主键索引）中必须包含主键列，所以如果主键很大的话，其他的所有索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小。
InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

特性

InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合
InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
InnoDB被用在众多需要高性能的大型数据库站点上

6.3 MEMORY

简介

使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。获得速度的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。MEMORY主要特性有：

特性

MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度
MEMORY存储引擎执行HASH和BTREE缩影
可以在一个MEMORY表中有非唯一键值
MEMORY表使用一个固定的记录长度格式
MEMORY不支持BLOB或TEXT列
MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）
MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享
当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）

6.4 MERGE

简介

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。
说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。
主要应用于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。

特性

MERGE数据表可以用来创建一个尺寸超过各个MyISAM数据表所允许的最大长度逻辑单元
你看一把经过压缩的数据表包括到MERGE数据表里。比如说，在某一年结束之后，你应该不会再往相应的日志文件里添加记录，所以你可以用myisampack工具压缩它以节省空间，而MERGE数据表仍可以像往常那样工作
MERGE数据表也支持DELETE 和UPDATE操作。INSERT操作比较麻烦，因为MySQL需要知道应该把新数据行插入到哪一个成员表里去。在MERGE数据表的定义里可以包括一个INSERT_METHOD选项，这个选项的可取值是NO、FIRST、LAST，他们的含义依次是INSERT操作是被禁止的、新数据行将被插入到现在UNION选项里列出的第一个数据表或最后一个数据表。

6.5 Archive

简介

Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

6.6 引擎选择

不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：

InnoDB：如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择。
MyISAM：如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率。并且，如果你的应用程序对查询性能要求较高，就要使用MYISAM了。MYISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MYISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。
Archive：如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive。
MERGE：对日志的一些综合操作，通常使用的是MERGE存储引擎。
Memory：目标数据较小，而且被非常频繁地访问。1）在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。2）如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。3）存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。4）如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果。

后续：最近3-4月份面试的人挺多的，如果你也想抓住这次涨薪的机会，关注我，加我好友拉你进面试群，一起讨论面试干货 / 套路，大家一起升职加薪