MySQL面试题库

MySQL的执行流程

1. 语法解析和预处理

   对语句基于 SQL 语法进行词法和语法分析和语义的解析。

• 词法解析：词法分析就是把一个完整的 SQL 语句打碎成一个个的单词。
• 语法解析：语法分析会对 SQL 做一些语法检查，比如单引号有没有闭合， 然后根据 MySQL 定义的语法规则，根据 SQL 语句生成一个数据结构。这个数据结构我 们把它叫做解析树
• 预处理器：解析器可以分析语法，但是它怎么知道数据库里面有什么表，表里面有什么字段呢? 实际上还是在解析的时候报错，解析 SQL 的环节里面有个预处理器。 它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。 预处理之后得到一个新的解析树。

2. 查询优化(Query Optimizer)与查询执行计划：

   一条 SQL 语句是可以有很多种执行方式的，最终返回相同的结 果，他们是等价的。查询优化器的目的就是根据解析树生成不同的执行计划(Execution Plan)，然后选择一种最优的执行计划，MySQL 里面使用的是基于开销(cost)的优化器，那种执行计 划开销最小。查看查询的开销：

show status like 'Last_query_cost';

3. 优化器得到的结果

   优化器最终会把解析树变成一个查询执行计划，查询执行计划是一个数据结构。

4. 存储引擎
5. 执行引擎，返回结果。

通信协议

MySQL 是支持多种通信协议的，可以使用同步/异步的方式，支持长连接/短连接。
同步通信的特点:

1. 同步通信依赖于被调用方，受限于被调用方的性能。也就是说，应用操作数据库， 线程会阻塞，等待数据库的返回。
2. 一般只能做到一对一，很难做到一对多的通信。

异步跟同步相反:

1. 异步可以避免应用阻塞等待，但是不能节省 SQL 执行的时间。
2. 如果异步存在并发，每一个 SQL 的执行都要单独建立一个连接，避免数据混乱。 但是这样会给服务端带来巨大的压力(一个连接就会创建一个线程，线程间切换会占用 大量 CPU 资源)。另外异步通信还带来了编码的复杂度，所以一般不建议使用。如果要 异步，必须使用连接池，排队从连接池获取连接而不是创建新连接。

一般来说我们连接数据库都是同步连接。

连接方式

MySQL 既支持短连接，也支持长连接。短连接就是操作完毕以后，马上 close 掉。 长连接可以保持打开，减少服务端创建和释放连接的消耗，后面的程序访问的时候还可 以使用这个连接。一般我们会在连接池中使用长连接。
保持长连接会消耗内存。长时间不活动的连接，MySQL 服务器会断开。

show global status like 'Thread%';
Threads_cached:缓存中的线程连接数。
Threads_connected:当前打开的连接数。
Threads_created:为处理连接创建的线程数。 
Threads_running:非睡眠状态的连接数，通常指并发连接数。

每产生一个连接或者一个会话，在服务端就会创建一个线程来处理。反过来，如果要杀死会话，就是 Kill 线程。

MySQL 支持哪些通信协议呢?

• Unix Socket：比如我们在 Linux 服务器上，如果没有指定-h 参数，它就用 socket 方式登录(省略了-S /var/lib/mysql/mysql.sock，它不用通过网络协议，也可以连接到 MySQL 的服务器，它需要用到服务器上的一个 物理文件(/var/lib/mysql/mysql.sock)
• TCP/IP 协议：如果指定-h 参数，就会用第二种方式，TCP/IP 协议。我们的编程语言的连接模块都是用 TCP 协议连接到 MySQL 服务器的，比如 mysql-connector-java-x.x.xx.jar。
• 另外还有命名管道(Named Pipes)和内存共享(Share Memory)的方式，这两种 通信方式只能在 Windows 上面使用，一般用得比较少。

MySQL的通信方式。

MySQL 使用了半双工的通信方式。
要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，这两个动作不能 同时发生。所以客户端发送 SQL 语句给服务端的时候，(在一次连接里面)数据是不能 分成小块发送的，不管你的 SQL 语句有多大，都是一次性发送。
比如我们用 MyBatis 动态 SQL 生成了一个批量插入的语句，插入 10 万条数据，values 后面跟了一长串的内容，或者 where 条件 in 里面的值太多，会出现问题。
这个时候我们必须要调整 MySQL 服务器配置 max_allowed_packet 参数的值(默认 是 4M)，把它调大，否则就会报错。

存储引擎

• MyISAM(3 个文件)

  应用范围比较小。表级锁定限制了读/写的性能，因此在 Web 和数据仓库配置中， 它通常用于只读或以读为主的工作。

  特点:

  • 支持表级别的锁(插入和更新会锁表)。不支持事务。
  • 拥有较高的插入(insert)和查询(select)速度。存储了表的行数(count 速度更快)。(怎么快速向数据库插入 100 万条数据?我们有一种先用 MyISAM 插入数据，然后修改存储引擎为 InnoDB 的操作。)

  适合:

  • 只读之类的数据分析的项目。

• InnoDB(2 个文件)

  mysql 5.7 中的默认存储引擎。InnoDB 是一个事务安全(与 ACID 兼容)的 MySQL 存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB 行级锁(不升级 为更粗粒度的锁)和 Oracle 风格的一致非锁读提高了多用户并发性和性能。InnoDB 将 用户数据存储在聚集索引中，以减少基于主键的常见查询的 I/O。为了保持数据完整性，InnoDB 还支持外键引用完整性约束。

  特点:

  • 支持事务，支持外键，因此数据的完整性、一致性更高。
  • 支持行级别的锁和表级别的锁。 支持读写并发，写不阻塞读(MVCC)。
  • 特殊的索引存放方式，可以减少 IO，提升查询效率。

  适合:

  • 经常更新的表，存在并发读写或者有事务处理的业务系统。

• Memory(1 个文件)

  将所有数据存储在 RAM 中，以便在需要快速查找非关键数据的环境中快速访问。这 个引擎以前被称为堆引擎。其使用案例正在减少。

  特点:

  • 把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消 失。只适合做临时表。
  • 将表中的数据存储到内存中。

• CSV(3 个文件)：

  它的表实际上是带有逗号分隔值的文本文件。csv 表允许以 csv 格式导入或转储数据， 以便与读写相同格式的脚本和应用程序交换数据。因为 csv 表没有索引，所以通常在正 常操作期间将数据保存在 innodb 表中，并且只在导入或导出阶段使用 csv 表。

  特点:

  • 不允许空行，不支持索引。格式通用，可以直接编辑，适合在不同数据库之 间导入导出。

• Archive(2 个文件)

  这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息。

  特点:

  • 不支持索引，不支持 update delete。

如何选择存储引擎?

• 如果对数据一致性要求比较高，需要事务支持，可以选择 InnoDB。
• 如果数据查询多更新少，对查询性能要求比较高，可以选择 MyISAM。
• 如果需要一个用于查询的临时表，可以选择 Memory。
• 如果所有的存储引擎都不能满足你的需求，并且技术能力足够，可以根据官网内部 手册用 C 语言开发一个存储引擎: https://dev.mysql.com/doc/internals/en/custom-engine.html

Binlog

binlog 以事件的形式记录了所有的 DDL 和 DML 语句(因为它记录的是操作而不是 数据值，属于逻辑日志)，可以用来做主从复制和数据恢复。 跟 redo log 不一样，它的文件内容是可以追加的，没有固定大小限制。 在开启了 binlog 功能的情况下，我们可以把 binlog 导出成 SQL 语句，把所有的操 作重放一遍，来实现数据的恢复。 binlog 的另一个功能就是用来实现主从复制，它的原理就是从服务器读取主服务器 的 binlog，然后执行一遍。

事务的四大特性ACID

• 原子性：要么都是成功，要么都是失败
• 一致性：数据库的完整性约束没有被破坏，事务执行的 前后都是合法的数据状态
• 隔离性：对表或者行的并发操作，应该是透 明的，互相不干扰的。
• 持久性：我们对数据库的任意 的操作，增删改，只要事务提交成功，那么结果就是永久性的。

开启事务

手动开启事务也有几种方式，

• 一种是用 begin;
• 一种是用 start transaction。

结束事务：

• 第一种就是提交一个事务， commit;
• 还有一种就是 rollback，回滚的时候，事务也会结束。

事务隔离级别	脏读	不可和重复读	幻读
未提交读	可能	可能	可能
已提交读	不可能	可能	可能
可重复读	不可能	不可能	对InnoDB不可能
串行化	不可能	不可能	不可能

• 脏读即为事务1第二次读取时，读到了事务2未提交的数据。
• 不可重复读：与脏读逻辑类似。主要在于事务2在事务1第二次读取时，提交了数据。导致事务1前后两次读取的数据不一致。
• 幻读：事务1第二次查询时，读到了事务2提交的数据。

MVCC

MVCC 的核心思想是: 我可以查到在我这个事务开始之前已经存在的数据，即使它 在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。

查找规则:只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事务 ID 的行(或未删除)。

MySQL优化思路

• 连接——配置优化：

  1. 我们可以增加服务端的可用连接数。修改配置参数增加可用连接数，修改 max_connections 的大小。

     show variables like 'max_connections'; -- 修改最大连接数，当有多个应用连接的时候

  2. 及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是 28800 秒，8 小时，我们可以把这个值调小。

     show global variables like 'wait_timeout'; --及时释放不活动的连接，注意不要释放连接池还在使用的连接

• 从客户端来说：
  这个时候我们可以引入连接池，实现连接的重用。

  Druid 的默认最大连接池大小是 8。Hikari 的默认最大连接池大小是 10。 为什么默认值都是这么小呢?
  在 Hikari 的 github 文档中，给出了一个 PostgreSQL 数据库建议的设置连接池大小的公式: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing

  它的建议是机器核数乘以 2 加 1。也就是说，4 核的机器，连接池维护 9 个连接就 够了。这个公式从一定程度上来说对其他数据库也是适用的。这里面还有一个减少连接 池大小实现提升并发度和吞吐量的案例。

Explain

• ID：如果 ID 有相同也有不同，就是 ID 不同的先大后小，ID 相同的从上往下。
• select_type：SIMPLE，PRIMARY，SUBQUERY，DERIVED，UNION，UNION_RESULT
• type 连接类型：一般来说，需要保证查询至少达到 range 级别，最好能达到 ref。

当我们的 SQL 语句比较复杂，有多个关联和子查询的时候，就要分析 SQL 语句有没 有改写的方法。

-- 大偏移量的 limit
select * from user_innodb limit 900000,10; 
-- 改成先过滤 ID，再 limit
SELECT * FROM user_innodb WHERE id >= 900000 LIMIT 10;

https://dev.mysql.com/doc/refman/5.7/en/optimization.html

MySQL规范

https://www.cnblogs.com/huchong/p/10219318.html

MySQL执行很慢原因

1. 大多数情况是正常的，只是偶尔会出现很慢的情况。

• redolog写满了：redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。
• 内存不够用了：如果一次查询较多的数据，恰好碰到所查数据页不在内存中时，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页。
• MySQL 认为系统“空闲”的时候：这时系统没什么压力。
• MySQL 正常关闭的时候：这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

2. 一直很慢：

• 索引是否正确创建。
• sql是否正常使用到了索引。

索引类型

• 普通索引：也叫非唯一索引，是最普通的索引，没有任何的限制
• 唯一索引：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一 种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用 primay key 创建。
• 全文索引：针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数 据的这种情况，如果要解决 like 查询效率低的问题，可以创建全文索引。只有文本类型 的字段才可以创建全文索引，比如 char、varchar、text。

索引原理

通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

• MyISAM：
  一个是.MYD 文件，D 代表 Data，是 MyISAM 的数据文件，存放数据记录
  一个是.MYI 文件，I 代表 Index，是 MyISAM 的索引文件，存放索引
  MyISAM 的 B+Tree 里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI 中找到键值后，会到数据文件.MYD 中获取相应的数据记录。

• InnoDB：
  在 InnoDB 里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文 件是同一个文件，都在.ibd 文件里面。
  就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。(比如字典的目录 是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引)。
  在 InnoDB 里面，它组织数据的方式叫做叫做(聚集)索引组织表(clustered index organize table)，所以主键索引是聚集索引，非主键都是非聚集索引。

  

  

  

为什么用索引？

• 索引能极大的减少存储引擎需要扫描的数据量
• 索引可以把随机IO变成顺序IO
• 索引可以帮助我们在进行分组、排序等操作时，避免使 用临时表

为什么用B+ tree？

• B+树是B-树的变种(PLUS版)多路绝对平衡查找树，他拥有B-树的优势B+树扫库、表能力更强
• B+树的磁盘读写能力更强。
  内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
• B+树的排序能力更强
• B+树的查询效率更加稳定(仁者见仁、智者见智)。
  由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

聚簇索引与非聚簇索引

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
• 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id = 14”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

若对Name列进行条件搜索，则需要两个步骤

• 第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。
• 第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引)

聚簇索引的优势

• 由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问,不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据，获得数据更快。
• 辅助索引使用主键作为”指针”而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个”指针”。也就是说行的位置（实现中通过16K的Page来定位）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。
• 聚簇索引适合用在排序的场合，非聚簇索引不适合
• 取出一定范围数据的时候，使用用聚簇索引
• 二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据
• 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。

数据库建立索引为什么会加快查询速度

数据库在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。

添加索引的话，首先去索引列表中查询，而我们的索引列表是B类树的数据结构，查询的时间复杂度为O(log2N)，定位到特定值得行就会非常快，所以其查询速度就会非常快。

MySQL中的锁

加锁机制:

1. 乐观锁：先修改，保存时判断是够被更新过，应用级别
2. 悲观锁：先获取锁，再操作修改，数据库级别

锁粒度：

• 表级锁：开销小，加锁快，粒度大，锁冲突概率大，并发度低，适用于读多写少的情况。
• 页级锁：BDB存储引擎
• 行级锁：Innodb存储引擎，默认选项

兼容性：

• S锁，也叫做读锁、共享锁，对应于我们常用的 select * from users where id =1 lock in share mode
• X锁，也叫做写锁、排它锁、独占锁、互斥锁，对应对于select * from users where id =1 for update

	X	S
X	冲突	冲突
S	冲突	兼容

冲突的时候会阻塞当前会话，直到拿到锁或者超时

这里要提到的一点是，S锁 和 X锁是可以是表锁，也可以是行锁

1. 普通select

select  * from user where id =1;

begin;
select  * from user where id =1;
commit:

普通的select 语句是不加锁的。select包裹在事务中，同样也是不加锁的。where后面的条件不管多少，普通的select是不加锁的。

2. 显式加锁

select  * from user where id =1 lock in share mode;
select  * from user where id =1 for update;

显式指出要加什么样的锁。上面一个加的是共享锁，下面的是互斥锁。

这里需要强调的一点，需要明确在事务中是用这些锁，不在事务中是没有意义的。

3. 隐式加锁

update user set address '北京' where id=1;
delete from user where id=1;

update和delete也会对查询出的记录加X锁，隐式加互斥锁。加锁类型和for update 类似

后面只按照显式加锁的select for update 举例子，更新和删除的加锁方式是一样的。

6. insert

   在插入之前，会先在插入记录所在的间隙加上一个插入意向锁。

   insert会对插入成功的行加上排它锁，这个排它锁是个记录锁，而非next-key锁（当然更不是gap锁了），不会阻止其他并发的事务往这条记录之前插入 。

主键索引和唯一索引的区别

1. 主键一定是唯一性索引，唯一性索引并不一定就是主键。

2. 一个表中可以有多个唯一性索引，但只能有一个主键。

3. 主键列不允许空值，而唯一性索引列允许空值。

4. 索引可以提高查询的速度。

   主键和索引都是键，不过主键是逻辑键，索引是物理键，意思就是主键不实际存在，而索引实际存在在数据库中
   主键索引也被称为聚簇索引,叶子节点存放的是整行数据; 而非主键索引被称为二级索引,叶子节点存放的是主键的值.
   如果根据主键查询, 只需要搜索ID这颗B+树
   而如果通过非主键索引查询, 需要先搜索k索引树, 找到对应的主键, 然后再到ID索引树搜索一次, 这个过程叫做回表.
   总结, 非主键索引的查询需要多扫描一颗索引树, 效率相对更低。

InnoDB和MyISAM 区别

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 的重要原因之一；

聚簇索引与非聚簇索引

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
• 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引