基础

作为SQL Boy，没有人不会有基本部分，对吧？面试也不怎么问，基础掌握好的小伙伴可以跳过这部分。当然，现场可能会写一些。SQL语句，SQ句子可以通过牛客，LeetCode、LintCode练习等网站。

1. 内连接、外连接、交叉连接、笛卡尔积是什么？

• 内连接（inner join）：在两张表中获得满足连接匹配关系记录的记录。

• 外连接（outer join）：不仅在两张表中获得符合连接匹配关系的记录，而且在一张表（或两张表）中获得不符合匹配关系的记录。

• 交叉连接（cross join）：显示两张表的所有记录都是一一对应的，没有匹配的筛选关系。它是笛卡尔积SQL如果A表有m行，B表有n行，那么a和B结果是交叉连接m*n行。

• 笛卡尔积:是数学中的一个概念，比如集合A={a,b}，集合B={1，2，3}A??B={,,,,,,}。

2. 那MySQL 内连接、左连接么区别？

MySQL连接主要分为内连接和外连接，外连接常用于左连接和右连接。

• inner join 内部连接，在连接查询两个表时，只保留两个表中完全匹配的结果集

• left join 连接查询两个表时，即使右表没有匹配记录，也会返回左表的所有行。

• right join 连接查询两个表时，即使左表没有匹配记录，也会返回右表的所有行。

3.数据库的三大范式？

• 第一范式：数据表中的每列（每个字段）不能再次拆分。例如，用户表、用户地址也可分为国家、省、市，符合第一范式。

• 第二个范式：在第一个范式的基础上，非主键列完全依赖于主键，而不是主键的一部分。例如，如果商品信息（商品价格、商品类型）存储在订单表中，则需要使用商品ID和订单ID只有作为联合主键，才能满足第二范式。

• 第三范式：在满足第二范式的基础上，表中的非主键仅依赖于主键，而不依赖于其他非主键。例如，用户信息（姓名址）不能存储在订单表中。

这三个范式的功能是控制数据库的冗余，节省空间。事实上，一般互联网公司的设计都是反范式的。通过冗余数据，避免跨表和库，利用空间改变时间，提高性能。

4.varchar与char的区别？

char：

• char表示定长字符串，长度是固定的；

• 如果插入数据的长度小于char用空格填充固定长度；

• 由于长度固定，存取速度比varchar快很多，甚至可以快50%，但由于长度固定，会占用多余的空间，这是空间换时间的做法；

• 对于char最多可以存储的字符数为255，与编码无关

varchar：

• varchar表示可变长字符串，长度可变；

• 根据插入的数据存储多长；

• varchar存取取char相反，它存取缓慢，因为长度不固定，但正因为如此，不占用多余的空间，是时间换空间的做法；

• 对于varchar最多可以存储的字符数量为65532

日常设计可用于长度相对固定的字符串char，长度不确定的，使用varchar更合适。

5.blob和text有什么区别？

• blob用于存储二进制数据，text用于存储大字符串。

• blob没有字符集，text有一个字符集，根据字符集的校对规则对值进行排序和比较

6.DATETIME和TIMESTAMP的异同？

相同点：

1. 两种数据类型存储时间的表现格式相同。YYYY-MM-DD HH:MM:SS

2. 包括两种数据类型「日期」和「时间」部分。

3. 两种数据类型都可以存储微秒小数秒(秒后6位小数秒)

区别：

1. 日期范围：DATETIME 的日期范围是1000-01-01 00:00:00.000000到9999-12-31 23:59:59.999999；TIMESTAMP 时间范围是1970-01-01 00:00:01.000000UTC到 ``2038-01-09 03:14:07.999999UTC

2. 存储空间：DATETIME 存储空间为 8 字节；TIMESTAMP 存储空间为 4 字节

3. 时区相关：DATETIME 存储时间与时区无关；TIMESTAMP 存储时间与时区有关，显示值也取决于时区

4. 默认值：DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认值是当前时间(CURRENT_TIMESTAMP)

7.MySQL中 in 和 exists 的区别？

MySQL中的in语句是将外表和内表作为语句hash 连接，而exists句子是外表作loop循环，每次loop循环查询内表。我们可能认为exists比in语句的效率要高，这种说法其实是不准确的，要区分情景：

1. 如果查询的两个表大小相等，则使用in和exists差别不大。

2. 如果两个表中的一个更小，另一个是大表，则用于查询大表exists，小用子查询表in。

3. not in 和not exists：如果查询语句使用了not in，然后内外表面全表面扫描，没有索引；但是not extsts表上的索引仍然可以用于子查询。所以不管表大，用not exists都比not in要快。

8.MySQL哪种类型的字段更适合记录货币？

数据库中的货币MySQL常用Decimal和Numric这两种类型表示MySQL实现被用来保存与货币相关的数据。

例如salary DECIMAL(9,2)，9(precision)存储值的总小数位数为2(scale)代表将用于存储小数点后的位数。salary列中值的范围为-9999999.99到9999999.99。

DECIMAL和NUMERIC存储值作为字符串，而不是作为二进制浮点，以保存这些值的小数精度。

不使用的原因float或者double原因:因为float和double存储在二进制中，因此存在一定的误差。

9.MySQL怎么存储emoji???

MySQL可直接用字符串存储emoji。

但需要注意的是，utf8 编码不好，MySQL中的utf8是阉割版的 utf最多只用8，它 3 字节存储字符，所以不能存储表情。我该怎么办？

需要使用utf8mb4编码。

altertableblogsmodifycontenttextCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_cinotnull; 

10.drop、delete与truncate的区别？

三者都表示删除，但三者有一些区别:

	delete	truncate	drop
类型	属于DML	属于DDL	属于DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表的结构仍然存在，删除表的全部或部分据行	表结构还在，删除表中的所有数据	从数据库中删除表，所有数据行，索引和权限也会被删除
删除速度	删除速度慢，需要逐行删除	删除速度快	删除速度最快

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

11.UNION与UNION ALL的区别？

• 如果使用UNION ALL，不会合并重复的记录行

• 效率 UNION 高于 UNION ALL

12.count(1)、count(*) 与 count(列名) 的区别？

执行效果：

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

• count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行速度：

• 列名为主键，count(列名)会比count(1)快

• 列名不为主键，count(1)会比count(列名)快

• 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）

• 如果有主键，则 select count（主键）的执行效率是最优的

• 如果表只有一个字段，则 select count（*）最优。

13.一条SQL查询语句的执行顺序？

1. FROM：对FROM子句中的左表和右表执行笛卡儿积（Cartesianproduct），产生虚拟表VT1

2. ON：对虚拟表VT1应用ON筛选，只有那些符合的行才被插入虚拟表VT2中

3. JOIN：如果指定了OUTER JOIN（如LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表VT2中，产生虚拟表VT3。如果FROM子句包含两个以上表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1）～步骤3），直到处理完所有的表为止

4. WHERE：对虚拟表VT3应用WHERE过滤条件，只有符合的记录才被插入虚拟表VT4中

5. GROUP BY：根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5

6. CUBE|ROLLUP：对表VT5进行CUBE或ROLLUP操作，产生表VT6

7. HAVING：对虚拟表VT6应用HAVING过滤器，只有符合的记录才被插入虚拟表VT7中。

8. SELECT：第二次执行SELECT操作，选择指定的列，插入到虚拟表VT8中

9. DISTINCT：去除重复数据，产生虚拟表VT9

10. ORDER BY：将虚拟表VT9中的记录按照进行排序操作，产生虚拟表VT10。11）

11. LIMIT：取出指定行的记录，产生虚拟表VT11，并返回给查询用户

数据库架构

14.说说 MySQL 的基础架构?

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

• 客户端：最上层的服务并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。

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

• 存储引擎层：第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。Server层通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。

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

• 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限会先查询缓存 (MySQL8.0 版本以前)。

• 如果没有缓存，分析器进行语法分析，提取 sql 语句中 select 等关键元素，然后判断 sql 语句是否有语法错误，比如关键词是否正确等等。

• 语法解析之后，MySQL的服务器会对查询的语句进行优化，确定执行的方案。

• 完成查询优化后，按照生成的执行计划调用数据库引擎接口，返回执行结果。

存储引擎

16.MySQL有哪些常见存储引擎？

主要存储引擎以及功能如下：

功能	MylSAM	MEMORY	InnoDB
存储限制	256TB	RAM	64TB
支持事务	No	No	Yes
支持全文索引	Yes	No	Yes
支持树索引	Yes	Yes	Yes
支持哈希索引	No	Yes	Yes
支持数据缓存	No	N/A	Yes
支持外键	No	No	Yes

MySQL5.5之前，默认存储引擎是MylSAM，5.5之后变成了InnoDB。

InnoDB支持的哈希索引是自适应的，InnoDB会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。

MySQL 5.6开始InnoDB支持全文索引。

17.那存储引擎应该怎么选择？

大致上可以这么选择：

• 大多数情况下，使用默认的InnoDB就够了。如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 就是比较靠前的选择了。

• 如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。

• 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。

使用哪一种引擎可以根据需要灵活选择，因为存储引擎是基于表的，所以一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。

18.InnoDB和MylSAM主要有什么区别？

PS:MySQL8.0都开始慢慢流行了，如果不是面试，MylSAM其实可以不用怎么了解。

1.  存储结构：每个MyISAM在磁盘上存储成三个文件；InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

2. 事务支持：MyISAM不提供事务支持；InnoDB提供事务支持事务，具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全特性。

3  最小锁粒度：MyISAM只支持表级锁，更新时会锁住整张表，导致其它查询和更新都会被阻塞InnoDB支持行级锁。

4. 索引类型：MyISAM的索引为聚簇索引，数据结构是B树；InnoDB的索引是非聚簇索引，数据结构是B+树。

5.  主键必需：MyISAM允许没有任何索引和主键的表存在；InnoDB如果没有设定主键或者非空唯一索引，**就会自动生成一个6字节的主键(用户不可见)**，数据是主索引的一部分，附加索引保存的是主索引的值。

6. 表的具体行数：MyISAM保存了表的总行数，如果select count(*) from table;会直接取出出该值; InnoDB没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表;但是在加了wehre条件后，MyISAM和InnoDB处理的方式都一样。

7.  外键支持：MyISAM不支持外键；InnoDB支持外键。

日志

19.MySQL日志文件有哪些？分别介绍下作用？

MySQL日志文件有很多，包括 ：

• 错误日志（error log）：错误日志文件对MySQL的启动、运行、关闭过程进行了记录，能帮助定位MySQL问题。

• 慢查询日志（slow query log）：慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。

• 一般查询日志（general log）：一般查询日志记录了所有对MySQL数据库请求的信息，无论请求是否正确执行。

• 二进制日志（bin log）：关于二进制日志，它记录了数据库所有执行的DDL和DML语句（除了数据查询语句select、show等），以事件形式记录并保存在二进制文件中。

还有两个InnoDB存储引擎特有的日志文件：

• 重做日志（redo log）：重做日志至关重要，因为它们记录了对于InnoDB存储引擎的事务日志。

• 回滚日志（undo log）：回滚日志同样也是InnoDB引擎提供的日志，顾名思义，回滚日志的作用就是对数据进行回滚。当事务对数据库进行修改，InnoDB引擎不仅会记录redo log，还会生成对应的undo log日志；如果事务执行失败或调用了rollback，导致事务需要回滚，就可以利用undo log中的信息将数据回滚到修改之前的样子。

20.binlog和redo log有什么区别？

• bin log会记录所有与数据库有关的日志记录，包括InnoDB、MyISAM等存储引擎的日志，而redo log只记InnoDB存储引擎的日志。

• 记录的内容不同，bin log记录的是关于一个事务的具体操作内容，即该日志是逻辑日志。而redo log记录的是关于每个页（Page）的更改的物理情况。

• 写入的时间不同，bin log仅在事务提交前进行提交，也就是只写磁盘一次。而在事务进行的过程中，却不断有redo ertry被写入redo log中。

• 写入的方式也不相同，redo log是循环写入和擦除，bin log是追加写入，不会覆盖已经写的文件。

21.一条更新语句怎么执行的了解吗？

更新语句的执行是Server层和引擎层配合完成，数据除了要写入表中，还要记录相应的日志。

1. 执行器先找引擎获取ID=2这一行。ID是主键，存储引擎检索数据，找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2. 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。

4. 执行器生成这个操作的binlog，并把binlog写入磁盘。

5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

从上图可以看出，MySQL在执行更新语句的时候，在服务层进行语句的解析和执行，在引擎层进行数据的提取和存储；同时在服务层对binlog进行写入，在InnoDB内进行redo log的写入。

不仅如此，在对redo log写入时有两个阶段的提交，一是binlog写入之前prepare状态的写入，二是binlog写入之后commit状态的写入。

22.那为什么要两阶段提交呢？

为什么要两阶段提交呢？直接提交不行吗？

我们可以假设不采用两阶段提交的方式，而是采用“单阶段”进行提交，即要么先写入redo log，后写入binlog；要么先写入binlog，后写入redo  log。这两种方式的提交都会导致原先数据库的状态和被恢复后的数据库的状态不一致。

先写入redo log，后写入binlog：

在写完redo log之后，数据此时具有crash-safe能力，因此系统崩溃，数据会恢复成事务开始之前的状态。但是，若在redo log写完时候，binlog写入之前，系统发生了宕机。此时binlog没有对上面的更新语句进行保存，导致当使用binlog进行数据库的备份或者恢复时，就少了上述的更新语句。从而使得id=2这一行的数据没有被更新。

先写入binlog，后写入redo log：

写完binlog之后，所有的语句都被保存，所以通过binlog复制或恢复出来的数据库中id=2这一行的数据会被更新为a=1。但是如果在redo log写入之前，系统崩溃，那么redo log中记录的这个事务会无效，导致实际数据库中id=2这一行的数据并没有更新。

简单说，redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

23.redo log怎么刷入磁盘的知道吗？

redo log的写入不是直接落到磁盘，而是在内存中设置了一片称之为redo log buffer的连续内存空间，也就是redo 日志缓冲区。

什么时候会刷入磁盘？

在如下的一些情况中，log buffer的数据会刷入磁盘：

• log buffer 空间不足时

log buffer 的大小是有限的，如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。如果当前写入 log buffer 的redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。

• 事务提交时

在事务提交时，为了保证持久性，会把log buffer中的日志全部刷到磁盘。注意，这时候，除了本事务的，可能还会刷入其它事务的日志。

• 后台线程输入

有一个后台线程，大约每秒都会刷新一次log buffer中的redo log到磁盘。

• 正常关闭服务器时

• 触发checkpoint规则

重做日志缓存、重做日志文件都是以块（block）的方式进行保存的，称之为重做日志块（redo log block）,块的大小是固定的512字节。我们的redo log它是固定大小的，可以看作是一个逻辑上的 log group，由一定数量的log block 组成。

它的写入方式是从头到尾开始写，写到末尾又回到开头循环写。

其中有两个标记位置：

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

当write_pos追上checkpoint时，表示redo log日志已经写满。这时候就不能接着往里写数据了，需要执行checkpoint规则腾出可写空间。

所谓的checkpoint规则，就是checkpoint触发后，将buffer中日志页都刷到磁盘。

SQL 优化

24.慢SQL如何定位呢？

慢SQL的监控主要通过两个途径：

• 慢查询日志：开启MySQL的慢查询日志，再通过一些工具比如mysqldumpslow去分析对应的慢查询日志，当然现在一般的云厂商都提供了可视化的平台。

• 服务监控：可以在业务的基建中加入对慢SQL的监控，常见的方案有字节码插桩、连接池扩展、ORM框架过程，对服务运行中的慢SQL进行监控和告警。

25.有哪些方式优化慢SQL？

慢SQL的优化，主要从两个方面考虑，SQL语句本身的优化，以及数据库设计的优化。

避免不必要的列

这个是老生常谈，但还是经常会出的情况，SQL查询的时候，应该只查询需要的列，而不要包含额外的列，像slect * 这种写法应该尽量避免。

分页优化

在数据量比较大，分页比较深的情况下，需要考虑分页的优化。

例如：

select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

优化方案：

• 延迟关联

  先通过where条件提取出主键，在将该表与原数据表关联，通过主键id提取数据行，而不是通过原来的二级索引提取数据行

  例如：

  select a.* from table a, 
   (select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b
   where a.id = b.id
  

• 书签方式

  书签方式就是找到limit第一个参数对应的主键值，根据这个主键值再去过滤并limit

  例如：

  select * from table where id >
  (select * from table where type = 2 and level = 9 order by id asc limit 190

索引优化

合理地设计和使用索引，是优化慢SQL的利器。

利用覆盖索引

InnoDB使用非主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引

例如对于如下查询：

select name from test where city='上海'

我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取

alter table test add index idx_city_name (city, name);

低版本避免使用or查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题。

避免使用 != 或者 <> 操作符

SQL中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引

解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描

例如，把column<>’aaa’，改成column>’aaa’ or column<’aaa’，就可以使用索引了

适当使用前缀索引

适当地使用前缀所云，可以降低索引的空间占用，提高索引的查询效率。

比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

alter table test add index index2(email(6));

PS:需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引

避免列上函数运算

要避免在列字段上进行算术运算或其他表达式运算，否则可能会导致存储引擎无法正确使用索引，从而影响了查询的效率

select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;

正确使用联合索引

使用联合索引的时候，注意最左匹配原则。

JOIN优化

优化子查询

尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大

小表驱动大表

关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL内部会遍历驱动表，再去连接被驱动表。

比如left join，左表就是驱动表，A表小于B表，建立连接的次数就少，查询速度就被加快了。

 select name from A left join B ;

适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略

避免使用JOIN关联太多的表

《阿里巴巴Java开发手册》规定不要join超过三张表，第一join太多降低查询的速度，第二join的buffer会占用更多的内存。

如果不可避免要join多张表，可以考虑使用数据异构的方式异构到ES中查询。

排序优化

利用索引扫描做排序

MySQL有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的

但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机IO，通常会比顺序全表扫描还慢

因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行

例如：

--建立索引（date,staff_id,customer_id）
select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序

UNION优化

条件下推

MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引

最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化

此外，除非确实需要服务器去重，一定要使用union all，如果不加all关键字，MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高。

26.怎么看执行计划（explain），如何理解其中各个字段的含义？

explain是sql优化的利器，除了优化慢sql，平时的sql编写，也应该先explain，查看一下执行计划，看看是否还有优化的空间。

直接在 select 语句之前增加explain 关键字，就会返回执行计划的信息。

explain

1. id 列：MySQL会为每个select语句分配一个唯一的id值

2. select_type 列，查询的类型，根据关联、union、子查询等等分类，常见的查询类型有SIMPLE、PRIMARY。

3. table 列：表示 explain 的一行正在访问哪个表。

4. type 列：最重要的列之一。表示关联类型或访问类型，即 MySQL 决定如何查找表中的行。

   性能从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

   • system

     system：当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快

   • const

     const：表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量(const)值。这类扫描效率极高，返回数据量少，速度非常快。

   • eq_ref

     eq_ref：查询时命中主键primary key 或者 unique key索引， type 就是 eq_ref。

   • ref_or_null

     ref_or_null：这种连接类型类似于 ref，区别在于 MySQL会额外搜索包含NULL值的行。

   • index_merge

     index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。

   • unique_subquery

     unique_subquery：替换下面的 IN子查询，子查询返回不重复的集合。

   • index_subquery

     index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。

   • range

     range：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。

   • index

     index：Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。

   • ALL

     就不用多说了，全表扫描。

5. possible_keys 列：显示查询可能使用哪些索引来查找，使用索引优化sql的时候比较重要。

6. key 列：这一列显示 mysql 实际采用哪个索引来优化对该表的访问，判断索引是否失效的时候常用。

7. key_len 列：显示了 MySQL使用

8. ref 列：ref 列展示的就是与索引列作等值匹配的值，常见的有：const（常量），func，NULL，字段名。

9. rows 列：这也是一个重要的字段，MySQL查询优化器根据统计信息，估算SQL要查到结果集需要扫描读取的数据行数，这个值非常直观显示SQL的效率好坏，原则上rows越少越好。

10. Extra 列：显示不适合在其它列的额外信息，虽然叫额外，但是也有一些重要的信息：

• Using index：表示MySQL将使用覆盖索引，以避免回表

• Using where：表示会在存储引擎检索之后再进行过滤

• Using temporary ：表示对查询结果排序时会使用一个临时表。

索引

索引可以说是MySQL面试中的重中之重，一定要彻底拿下。

27.能简单说一下索引的分类吗？

从三个不同维度对索引分类：

例如从基本使用使用的角度来讲：

• 主键索引: InnoDB主键是默认的索引，数据列不允许重复，不允许为NULL，一个表只能有一个主键。

• 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

• 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

• 组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

28.为什么使用索引会加快查询？

传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，MySQL需要将表的数据从头到尾遍历一遍。

在我们添加完索引之后，MySQL一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历，在比较小的索引数据里查找，然后映射到对应的数据，能大幅提升查找的效率。

和我们通过书的目录，去查找对应的内容，一样的道理。

29.创建索引有哪些注意点？

索引虽然是sql性能优化的利器，但是索引的维护也是需要成本的，所以创建索引，也要注意：

1. 索引应该建在查询应用频繁的字段

   在用于 where 判断、 order 排序和 join 的(on)字段上创建索引。

2. 索引的个数应该适量

   索引需要占用空间；更新时候也需要维护。

3. 区分度低的字段，例如性别，不要建索引。

   离散度太低的字段，扫描的行数降低的有限。

4. 频繁更新的值，不要作为主键或者索引

   维护索引文件需要成本；还会导致页分裂，IO次数增多。

5. 组合索引把散列性高(区分度高)的值放在前面

   为了满足最左前缀匹配原则

6. 创建组合索引，而不是修改单列索引。

   组合索引代替多个单列索引（对于单列索引，MySQL基本只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）

7. 过长的字段，使用前缀索引。当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。

8. 不建议用无序的值(例如身份证、UUID )作为索引

   当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化

30.索引哪些情况下会失效呢？

• 查询条件包含or，可能导致索引失效

• 如果字段类型是字符串，where时一定用引号括起来，否则会因为隐式类型转换，索引失效

• like通配符可能导致索引失效。

• 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

• 在索引列上使用mysql的内置函数，索引失效。

• 对索引列运算（如，+、-、*、/），索引失效。

• 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。

• 索引字段上使用is null， is not null，可能导致索引失效。

• 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

• MySQL优化器估计使用全表扫描要比使用索引快,则不使用索引。

31.索引不适合哪些场景呢？

• 数据量比较少的表不适合加索引

• 更新比较频繁的字段也不适合加索引

• 离散低的字段不适合加索引（如性别）

32.索引是不是建的越多越好呢？

当然不是。

• 索引会占据磁盘空间

• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

33.MySQL索引用的什么数据结构了解吗？

MySQL的默认存储引擎是InnoDB，它采用的是B+树结构的索引。

• B+树：只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

在这张图里，有两个重点：

• 最外面的方块，的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（粉色所示）和指针（黄色/灰色所示），如根节点磁盘包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、4、5……、65。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

• 叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表，可以进行范围查询。

34.那一棵B+树能存储多少条数据呢？

假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。非叶子节点(一页)可以存储 16384/14=1170 个这样的 单元(键值+指针)，代表有 1170 个指针。

树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据为 1170*1170*16=21902400。

在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。

所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。

35.为什么要用 B+ 树，而不用普通二叉树？

可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数。

为什么不用普通二叉树？

普通二叉树存在退化的情况，如果它退化成链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

为什么不用平衡二叉树呢？

读取数据的时候，是从磁盘读到内存。如果树这种数据结构作为索引，那每查找一次数据就需要从磁盘中读取一个节点，也就是一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是 B+ 树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快。

36.为什么用 B+ 树而不用 B 树呢？

B+相比较B树，有这些优势：

• 它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。

  B Tree 解决的两大问题：每个节点存储更多关键字；路数更多

• 扫库、扫表能力更强

  如果我们要对表进行全表扫描，只需要遍历叶子节点就可以 了，不需要遍历整棵 B+Tree 拿到所有的数据。

• B+Tree 的磁盘读写能力相对于 B Tree 来说更强，IO次数更少

  根节点和枝节点不保存数据区， 所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多，IO次数更少。

• 排序能力更强

  因为叶子节点上有下一个数据区的指针，数据形成了链表。

• 效率更加稳定

  B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的。

37.Hash 索引和 B+ 树索引区别是什么？

• B+ 树可以进行范围查询，Hash 索引不能。

• B+ 树支持联合索引的最左侧原则，Hash 索引不支持。

• B+ 树支持 order by 排序，Hash 索引不支持。

• Hash 索引在等值查询上比 B+ 树效率更高。

• B+ 树使用 like 进行模糊查询的时候，like 后面（比如 % 开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

38.聚簇索引与非聚簇索引的区别？

首先理解聚簇索引不是一种新的索引，而是而是一种数据存储方式。聚簇表示数据行和相邻的键值紧凑地存储在一起。我们熟悉的两种存储引擎——MyISAM采用的是非聚簇索引，InnoDB采用的是聚簇索引。

可以这么说：

• 索引的数据结构是树，聚簇索引的索引和数据存储在一棵树上，树的叶子节点就是数据，非聚簇索引索引和数据不在一棵树上。

• 一个表中只能拥有一个聚簇索引，而非聚簇索引一个表可以存在多个。

• 聚簇索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

• 聚簇索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

39.回表了解吗？

在InnoDB存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

例如:select * from user where name = ‘张三’;

40.覆盖索引了解吗？

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用辅助索引中就能够取得，不用去查主键索引，这时候使用的索引就叫做覆盖索引，避免了回表。

比如，select name from user where name = ‘张三’;

41.什么是最左前缀原则/最左匹配原则？

注意：最左前缀原则、最左匹配原则、最左前缀匹配原则这三个都是一个概念。

最左匹配原则：在InnoDB的联合索引中，查询的时候只有匹配了前一个/左边的值之后，才能匹配下一个。

根据最左匹配原则，我们创建了一个组合索引，如 (a1,a2,a3)，相当于创建了（a1）、(a1,a2)和 (a1,a2,a3) 三个索引。

为什么不从最左开始查，就无法匹配呢？

比如有一个user表，我们给 name 和 age 建立了一个组合索引。

ALTER TABLE user add INDEX comidx_name_phone (name,age);

组合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的 (name 在左边，age 在右边)。

从这张图可以看出来，name 是有序的，age 是无序的。当 name 相等的时候， age 才是有序的。

这个时候我们使用 where name= ‘张三‘ and age = ‘20 ‘去查询数据的时候， B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name 相同的时候再比较age。但是如果查询条件没有 name，就不知道下一步应该查哪个 节点，因为建立搜索树的时候 name 是第一个比较因子，所以就没用上索引。

42.什么是索引下推优化？

索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询。

• 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL Server，MySQL Server进行过滤条件的判断。

• 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL Server将这一部分判断条件下推给存储引擎，然后由存储引擎通过判断索引是否符合MySQL Server传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

例如一张表，建了一个联合索引（name, age），查询语句：select * from t_user where name like '张%' and age=10;，由于name使用了范围查询，根据最左匹配原则：

不使用ICP，引擎层查找到name like '张%'的数据，再由Server层去过滤age=10这个条件，这样一来，就回表了两次，浪费了联合索引的另外一个字段age。

但是，使用了索引下推优化，把where的条件放到了引擎层执行，直接根据name like '张%' and age=10的条件进行过滤，减少了回表的次数。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

锁

43.MySQL中有哪几种锁，列举一下？

如果按锁粒度划分，有以下3种：

• 表锁：开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低;不会出现死锁。

• 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。

• 页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

如果按照兼容性，有两种，

• 共享锁（S Lock）,也叫读锁（read lock），相互不阻塞。

• 排他锁（X Lock），也叫写锁（write lock），排它锁是阻塞的，在一定时间内，只有一个请求能执行写入，并阻止其它锁读取正在写入的数据。

44.说说InnoDB里的行锁实现?

我们拿这么一个用户表来表示行级锁，其中插入了4行数据，主键值分别是1,6,8,12，现在简化它的聚簇索引结构，只保留数据记录。

InnoDB的行锁的主要实现如下：

• Record Lock 记录锁

记录锁就是直接锁定某行记录。当我们使用唯一性的索引(包括唯一索引和聚簇索引)进行等值查询且精准匹配到一条记录时，此时就会直接将这条记录锁定。例如select * from t where id =6 for update;就会将id=6的记录锁定。

• Gap Lock 间隙锁

间隙锁(Gap Locks) 的间隙指的是两个记录之间逻辑上尚未填入数据的部分,是一个左开右开空间。

间隙锁就是锁定某些间隙区间的。当我们使用用等值查询或者范围查询，并且没有命中任何一个record，此时就会将对应的间隙区间锁定。例如select * from t where id =3 for update;或者select * from t where id > 1 and id < 6 for update;就会将(1,6)区间锁定。

• Next-key Lock 临键锁

临键指的是间隙加上它右边的记录组成的左开右闭区间。比如上述的(1,6]、(6,8]等。

临键锁就是记录锁(Record Locks)和间隙锁(Gap Locks)的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。当我们使用范围查询，并且命中了部分record记录，此时锁住的就是临键区间。注意，临键锁锁住的区间会包含最后一个record的右边的临键区间。例如select * from t where id > 5 and id <= 7 for update;会锁住(4,7]、(7,+∞)。mysql默认行锁类型就是临键锁(Next-Key Locks)。当使用唯一性索引，等值查询匹配到一条记录的时候，临键锁(Next-Key Locks)会退化成记录锁；没有匹配到任何记录的时候，退化成间隙锁。

间隙锁(Gap Locks)和临键锁(Next-Key Locks)都是用来解决幻读问题的，在已提交读（READ COMMITTED）隔离级别下，间隙锁(Gap Locks)和临键锁(Next-Key Locks)都会失效！

上面是行锁的三种实现算法，除此之外，在行上还存在插入意向锁。

• Insert Intention Lock 插入意向锁

一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了意向锁 ，如果有的话，插入操作需要等待，直到拥有 gap锁 的那个事务提交。但是事务在等待的时候也需要在内存中生成一个 锁结构 ，表明有事务想在某个 间隙 中插入新记录，但是现在在等待。这种类型的锁命名为 Insert Intention Locks ，也就是插入意向锁 。

假如我们有个T1事务，给(1,6)区间加上了意向锁，现在有个T2事务，要插入一个数据，id为4，它会获取一个（1,6）区间的插入意向锁，又有有个T3事务，想要插入一个数据，id为3，它也会获取一个（1,6）区间的插入意向锁，但是，这两个插入意向锁锁不会互斥。

45.意向锁是什么知道吗？

意向锁是一个表级锁，不要和插入意向锁搞混。

意向锁的出现是为了支持InnoDB的多粒度锁，它解决的是表锁和行锁共存的问题。

当我们需要给一个表加表锁的时候，我们需要根据去判断表中有没有数据行被锁定，以确定是否能加成功。

假如没有意向锁，那么我们就得遍历表中所有数据行来判断有没有行锁；

有了意向锁这个表级锁之后，则我们直接判断一次就知道表中是否有数据行被锁定了。

有了意向锁之后，要执行的事务A在申请行锁（写锁）之前，数据库会自动先给事务A申请表的意向排他锁。当事务B去申请表的互斥锁时就会失败，因为表上有意向排他锁之后事务B申请表的互斥锁时会被阻塞。

46.MySQL的乐观锁和悲观锁了解吗？

• 悲观锁（Pessimistic Concurrency Control）：

悲观锁认为被它保护的数据是极其不安全的，每时每刻都有可能被改动，一个事务拿到悲观锁后，其他任何事务都不能对该数据进行修改，只能等待锁被释放才可以执行。

数据库中的行锁，表锁，读锁，写锁均为悲观锁。

• 乐观锁（Optimistic Concurrency Control）

乐观锁认为数据的变动不会太频繁。

乐观锁通常是通过在表中增加一个版本(version)或时间戳(timestamp)来实现，其中，版本最为常用。

事务在从数据库中取数据时，会将该数据的版本也取出来(v1)，当事务对数据变动完毕想要将其更新到表中时，会将之前取出的版本v1与数据中最新的版本v2相对比，如果v1=v2，那么说明在数据变动期间，没有其他事务对数据进行修改，此时，就允许事务对表中的数据进行修改，并且修改时version会加1，以此来表明数据已被变动。

如果，v1不等于v2，那么说明数据变动期间，数据被其他事务改动了，此时不允许数据更新到表中，一般的处理办法是通知用户让其重新操作。不同于悲观锁，乐观锁通常是由开发者实现的。

47.MySQL 遇到过死锁问题吗，你是如何解决的？

排查死锁的一般步骤是这样的：

（1）查看死锁日志 show engine innodb status;

（2）找出死锁 sql

（3）分析 sql 加锁情况

（4）模拟死锁案发

（5）分析死锁日志

（6）分析死锁结果

当然，这只是一个简单的流程说明，实际上生产中的死锁千奇百怪，排查和解决起来没那么简单。

事务

48.MySQL 事务的四大特性说一下？

• 原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

• 一致性：指在事务开始之前和事务结束以后，数据不会被破坏，假如 A 账户给 B 账户转 10 块钱，不管成功与否，A 和 B 的总金额是不变的。

• 隔离性：多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效果。简言之，就是事务之间是进水不犯河水的。

• 持久性：表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

49.那ACID靠什么保证的呢？

• 事务的隔离性是通过数据库锁的机制实现的。

• 事务的一致性由undo log来保证：undo log是逻辑日志，记录了事务的insert、update、deltete操作，回滚的时候做相反的delete、update、insert操作来恢复数据。

• 事务的原子性和持久性由redo log来保证：redolog被称作重做日志，是物理日志，事务提交的时候，必须先将事务的所有日志写入redo log持久化，到事务的提交操作才算完成。

50.事务的隔离级别有哪些？MySQL 的默认隔离级别是什么？

• 读未提交（Read Uncommitted）

• 读已提交（Read Committed）

• 可重复读（Repeatable Read）

• 串行化（Serializable）

MySQL默认的事务隔离级别是可重复读 (Repeatable Read)。

51.什么是幻读，脏读，不可重复读呢？

• 事务 A、B 交替执行，事务 A 读取到事务 B 未提交的数据，这就是脏读。

• 在一个事务范围内，两个相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。

• 事务 A 查询一个范围的结果集，另一个并发事务 B 往这个范围中插入 / 删除了数据，并静悄悄地提交，然后事务 A 再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。

不同的隔离级别，在并发事务下可能会发生的问题：

隔离级别	脏读	不可重复读	幻读
Read Uncommited  读取未提交	是	是	是
Read Commited 读取已提交	否	是	否
Repeatable Read 可重复读	否	否	是
Serialzable 可串行化	否	否	否

52.事务的各个隔离级别都是如