# MySQL相关面试题 **Repository Path**: elephant111/MySQL-related-interview-questions ## Basic Information - **Project Name**: MySQL相关面试题 - **Description**: MySQL相关面试题 - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 1 - **Created**: 2025-09-01 - **Last Updated**: 2025-09-01 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README ### [PDF离线版](https://pan.quark.cn/s/26520d74d643) # MySQL相关面试题 ## 1 SQL规范 ### 1.1 Mysql查询语句的执行顺序？ >难易程度：☆☆ > >出现频率：☆☆☆ sql的编写顺序 ```sql select 字段列表 from 表名列表 join on 表关联 where 条件列表 group by 分组字段列表 having 分组后的条件列表 order by 排序字段列表 limit 分页参数 ``` sql的执行顺序 ```sql from 表名列表 join on 表关联 where 条件列表 group by 分组字段列表 having 分组后的条件列表 select 字段列表 order by 排序字段列表 limit 分页参数 ``` >举例: > >sql1：select name ,age from tb_user where name = '张三' order by age limit 10; > >给表起别名 > >sql2：select **u.name** ,**u.age** from tb_user **u** where name = '张三' order by **u.age** limit 10; > >可以正常执行，如果from后不先执行，则select和order by无法引用 > > > >给sql的字段起别名 > >sql3：select u.name **uname** ,u.age **uage** from tb_user u where **uname**= '张三' order by u.age limit 10; > >在where之后使用别名，执行报错，因为where在select 之前执行 > > > >sql4：select u.name **uname** ,u.age **uage** from tb_user u where u.name= '张三' order by **uage** limit 10; > >在order by后面使用别名，可以正常执行，因为select执行完成后才会执行order by ### 1.2 Mysql 如何实现多表查询？ >难易程度：☆☆ > >出现频率：☆☆☆ 多表关系 - 一对多：在多的一方设置外键，关联一的一方的主键 - 一对一：用于表结构拆分，在其中任何一方设置外键（给唯一约束UNIQUE）,关联另一方的主键 - 对多对：需要建立中间表，中间表包含两个外键，关联两张表的主键 多表查询 - 内连接 - 隐式：`select ...from 表A,表B where 条件...` - 显式：`select ... from 表A inner join 表B on 条件...` - 外连接 - 左外连接：`select ...from 表A left join 表B on 条件...` - 右外连接：`select ...from 表A right join 表B on 条件...` - 自连接：`select ...from 表A 别名1,表A 别名2 where 条件...` - 子查询：列子查询、行子查询、表子查询 ### 1.3 MYSQL内连接和外连接的区别 ? >难易程度：☆☆ > >出现频率：☆☆☆ - 内连接会取出连接表中匹配到的数据，匹配不到的不保留； - 外连接会取出连接表中匹配到的数据，匹配不到的也会保留，其值为NULL。 - 左外连接，以左边的表为主表 - 右外连接，以右边的表为主表 以某一个表为主表后，进行关联查询，不管能不能关联的上，主表的数据都会保留，关联不上的以NULL显示 ### 1.4 CHAR和VARCHAR的区别？ >难易程度：☆☆ > >出现频率：☆☆☆☆ 区别主要有以下几个方面 1、最大长度：char最大长度是255字符，varchar最大长度是65535个字节。 2、定长：char是定长的，不足的部分用隐藏空格填充，varchar是不定长的。 3、空间使用：char会浪费空间，varchar会更加节省空间。 4、查找效率：char查找效率会很高，varchar查找效率会更低。 ​ varchar需要计算内容占用的长度，而char不会，所以char的效率稍高一些 在项目中的使用，这两种方式都会用到，比如像一些枚举值可以选择使用char，像一些描述信息或名字类可以选择使用varchar ## 2 事务 ### 2.1 什么是事务？ >难易程度：☆☆ > >出现频率：☆☆☆ 概述：由多个操作组成的一个逻辑单元，组成这个逻辑单元的多个操作要么都成功，要么都失败。 > 举例：转账 > > A向B转账500，转账成功，A扣除500元，B增加500元 > > A向B转账500，转账未成功，A已经扣除的500元要恢复，B不变 ### 2.2 ACID是什么？可以详细说一下吗？ >难易程度：☆☆☆ > >出现频率：☆☆☆☆☆ A=Atomicity原子性：就是上面说的,要么全部成功,要么全部失败，不可能只执行一部分操作。 C=Consistency一致性：系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态。 I=Isolation隔离性: **通常**来说:一个事务在完全提交之前,对其他事务是不可见的.注意前面的通常来说加了红色,意味着有例外情况。 D=Durability持久性：一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果。 >举例：转账 > >A向B转账500，转账成功，A扣除500元，B增加500元，原子操作体现在要么都成功，要么都失败 > >在转账的过程中，数据要一致，A扣除了500，B必须增加500 > >在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰 > >在转账的过程中，持久性体现在事务提交后，要把数据持久化（可以说是落盘操作） ### 2.3 并发事务带来哪些问题？ >难易程度：☆☆☆☆ > >出现频率：☆☆☆☆ 在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。 **脏读（Dirty read）**: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。 **丢失修改（Lost to modify）**：指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。 **不可重复读（Unrepeatableread）**：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。 **幻读（Phantom read）**：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。 ### 2.4 怎么解决这些问题呢？MySQL的默认隔离级别是？ >难易程度：☆☆☆☆ > >出现频率：☆☆☆☆ 解决方案：对事务进行隔离 MySQL的四种隔离级别如下: **未提交读(READ UNCOMMITTED)**：这个隔离级别下,其他事务可以看到本事务没有提交的部分修改。因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚)。这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用. sql演示： ```sql # 插入数据 insert into goods_innodb(name) values('华为'); insert into goods_innodb(name) values('小米'); # 会话一 set session transaction isolation level read uncommitted ; # 设置事务的隔离级别为read uncommitted start transaction ; # 开启事务 select * from goods_innodb ; # 查询数据 # 会话二 set session transaction isolation level read uncommitted ; # 设置事务的隔离级别为read uncommitted start transaction ; # 开启事务 update goods_innodb set name = '中兴' where id = 10 ; # 修改数据 # 会话一 select * from goods_innodb ; # 查询数据 ``` **已提交读(READ COMMITTED)**：其他事务只能读取到本事务已经提交的部分。这个隔离级别有不可重复读的问题，在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改。 sql演示： ```sql # 会话一 set session transaction isolation level read committed ; # 设置事务的隔离级别为read committed start transaction ; # 开启事务 select * from goods_innodb ; # 查询数据 # 会话二 set session transaction isolation level read committed ; # 设置事务的隔离级别为read committed start transaction ; # 开启事务 update goods_innodb set name = '中兴' where id = 1 ; # 修改数据 # 会话一 select * from goods_innodb ; # 查询数据 # 会话二 commit; # 提交事务 # 会话一 select * from goods_innodb ; # 查询数据 ``` **REPEATABLE READ(可重复读)**：可重复读隔离级别解决了上面不可重复读的问题(看名字也知道)，但是不能完全解决幻读。MySql默认的事务隔离级别就是：**REPEATABLE READ** ```sql select @@tx_isolation; ``` ![image-20220208091344590](MySQL相关面试题.assets/image-20220208091344590.png) sql演示(解决不可重复读)： ```sql # 会话一 start transaction ; # 开启事务 select * from goods_innodb ; # 查询数据 # 会话二 start transaction ; # 开启事务 update goods_innodb set name = '荣耀' where id = 1 ; # 修改数据 # 会话一 select * from goods_innodb ; # 查询数据 # 会话二 commit; # 提交事务 # 会话一 select * from goods_innodb ; # 查询数据 ``` sql演示(测试不会出现幻读的情况)： ```sql # 会话一 start transaction ; # 开启事务 select * from goods_innodb ; # 查询数据 # 会话二 start transaction ; # 开启事务 insert into goods_innodb(name) values('小米'); # 插入数据 commit; # 提交事务 # 会话一 select * from goods_innodb ; # 查询数据 ``` sql演示(测试出现幻读的情况)： ```sql # 表结构进行修改 ALTER TABLE goods_innodb ADD version int(10) NULL ; # 会话一 start transaction ; # 开启事务 select * from goods_innodb where version = 1; # 查询一条不满足条件的数据 # 会话二 start transaction ; # 开启事务 insert into goods_innodb(name, version) values('vivo', 1); # 插入一条满足条件的数据 commit; # 提交事务 # 会话一 update goods_innodb set name = '金立' where version = 1; # 将version为1的数据更改为'金立' select * from goods_innodb where version = 1; # 查询一条不满足条件的数据 ``` **SERIALIZABLE(可串行化)**：这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用。 ## 3 索引 ### 3.1 MYSQL支持的存储引擎有哪些, 有什么区别 ? >难易程度：☆☆☆ > >出现频率：☆☆☆☆ - 常用的存储引擎 - MyISAM存储引擎 - 访问快,不支持事务和外键。表结构保存在.frm文件中，表数据保存在.MYD文件中，索引保存在.MYI文件中。 - InnoDB存储引擎(MySQL5.5版本后默认的存储引擎) - 支持事务 ,占用磁盘空间大 ,支持并发控制。表结构保存在.frm文件中，如果是共享表空间，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。如果是多表空间存储，每个表的数据和索引单独保存在 .ibd 中。 - MEMORY存储引擎 - 内存存储 , 速度快 ,不安全 ,适合小量快速访问的数据。表结构保存在.frm中。 - 特性对比 | 特性 | MyISAM | InnoDB | MEMORY | | ------------ | ---------- | ------------- | ---------- | | **事务安全** | **不支持** | **支持** | **不支持** | | **锁机制** | **表锁** | **表锁/行锁** | **表锁** | | **外键** | **不支持** | **支持** | **不支持** | ### 3.2 了解过Mysql的索引嘛 ? >难易程度：☆☆ > >出现频率：☆☆☆ 索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。 ### 3.3 索引的底层数据结构了解过嘛 ? >难易程度：☆☆☆☆ > >出现频率：☆☆☆☆☆ MySQL默认使用的索引底层数据结构是B+树。再聊B+树之前，我们先聊聊二叉树和B树 #### 2.3.1 二叉树 假如说MySQL的索引结构采用二叉树的数据结构，比较理想的结构如下： ![image-20220911183152014](MySQL相关面试题.assets/image-20220911183152014.png) 如果主键是顺序插入的，则会形成一个单向链表，结构如下： ![image-20220911183211048](MySQL相关面试题.assets/image-20220911183211048.png) 所以，如果选择二叉树作为索引结构，会存在以下缺点： - 顺序插入时，会形成一个链表，查询性能大大降低。 - 大数据量情况下，层级较深，检索速度慢。 此时大家可能会想到，我们可以选择红黑树，红黑树是一颗自平衡二叉树，那这样即使是顺序插入数据，最终形成的数据结构也是一颗平衡的二叉树,结构如下: ![image-20220911183128328](MySQL相关面试题.assets/image-20220911183128328.png) 但是，即使如此，由于红黑树也是一颗二叉树，所以也会存在一个缺点： - 大数据量情况下，层级较深，检索速度慢。 所以，在MySQL的索引结构中，并没有选择二叉树或者红黑树，而选择的是B+Tree，那么什么是B+Tree呢？在详解B+Tree之前，先来介绍一个B-Tree。 #### 2.3.2 B-Tree B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。 以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5个指针： ![image-20220120230814729](MySQL相关面试题.assets/image-20220120230814729.png) > 知识小贴士: 树的度数指的是一个节点的子节点个数。 我们可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~galles/visualization/BTree.html ![image-20220120231104019](MySQL相关面试题.assets/image-20220120231104019.png) 插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 。然后观察一些数据插入过程中，节点的变化情况。 ![image-20220120231135819](MySQL相关面试题.assets/image-20220120231135819.png) 特点： - 5阶的B树，每一个节点最多存储4个key，对应5个指针。 - 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。 - 在B树中，非叶子节点和叶子节点都会存放数据。 #### 2.2.3 B+Tree B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一下其结构示意图： ![image-20220120233158924](MySQL相关面试题.assets/image-20220120233158924-1662891127168.png) 我们可以看到，两部分： - 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。 - 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。 我们可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html ![image-20220120233447695](MySQL相关面试题.assets/image-20220120233447695-1662891127168.png) 插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 。然后观察一些数据插入过程中，节点的变化情况。 ![image-20220911183055238](MySQL相关面试题.assets/image-20220911183055238.png) 最终我们看到，B+Tree 与 B-Tree相比，主要有以下三点区别： - 所有的数据都会出现在叶子节点。 - 叶子节点形成一个单向链表。 - 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。 上述我们所看到的结构是标准的B+Tree的数据结构，接下来，我们再来看看MySQL中优化之后的B+Tree。 MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。 ![image-20220120235829054](MySQL相关面试题.assets/image-20220120235829054-1662891127168.png) ### 3.4 什么是聚簇索引什么是非聚簇索引 ? >难易程度：☆☆☆ > >出现频率：☆☆☆ 而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种： | 分类 | 含义 | 特点 | | -------------------------- | ---------------------------------------------------------- | ------------------- | | 聚集索引(Clustered Index) | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 | | 二级索引(Secondary Index) | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 | 聚集索引选取规则: - 如果存在主键，主键索引就是聚集索引。 - 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。 - 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。 聚集索引和二级索引的具体结构如下： ![image-20220911183033682](MySQL相关面试题.assets/image-20220911183033682.png) - 聚集索引的叶子节点下挂的是这一行的数据 。 - 二级索引的叶子节点下挂的是该字段值对应的主键值。 ### 3.5 知道什么是回表查询嘛 ? >难易程度：☆☆☆ > >出现频率：☆☆☆ 介绍回表之前，我们先看一个例子 比如执行了一条sql语句`select * from user where name = 'Arm'`，其中name字段已经创建了索引 ![image-20220911183010564](MySQL相关面试题.assets/image-20220911183010564.png) 具体过程如下: ①. 由于是根据name字段进行查询，所以先根据name='Arm'到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。 ②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。 ③. 最终拿到这一行的数据，直接返回即可。 > 回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。 ### 3.6 索引创建原则有哪些？ >难易程度：☆☆☆ > >出现频率：☆☆☆☆ 1). 针对于数据量较大，且查询比较频繁的表建立索引。 2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。 3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。 4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。 5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。 6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。 7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。 ### 3.7 知道什么是左前缀原则嘛 ? >难易程度：☆☆ > >出现频率：☆☆☆☆ 如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。 以 tb_user 表为例，我们先来查看一下之前 tb_user 表所创建的索引。 ![image-20220121230021077](MySQL相关面试题.assets/image-20220121230021077.png) 在 tb_user 表中，有一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession，age，status。 对于最左前缀法则指的是，查询时，最左变的列，也就是profession必须存在，否则索引全部失效。 而且中间不能跳过某一列，否则该列后面的字段索引将失效 ### 3.8 知道什么叫覆盖索引嘛 ? >难易程度：☆☆ > >出现频率：☆☆☆ 覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。 例： 执行SQL : `select * from tb_user where id = 2`; ![image-20220911182940268](MySQL相关面试题.assets/image-20220911182940268.png) 根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。 ### 3.9 索引是越多越好嘛? 什么样的字段需要建索引, 什么样的字段不需要 ? >难易程度：☆☆☆ > >出现频率：☆☆☆☆ 索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。 - 针对于数据量较大，且查询比较频繁的表建立索引。 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。 - 不适合常见索引的字段 - 更新频繁字段不适合创建索引 - 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低) - 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。 - 比如省会，城市、月份 - 对于定义为text、image和bit的数据类型的列不要建立索引。 ## 4 调优 ### 4.1 如何定位慢查询 ? >难易程度：☆☆☆ > >出现频率：☆☆☆☆☆ 可以开启mysql的慢查询日志 慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。 如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息： ```properties # 开启MySQL慢日志查询开关 slow_query_log=1 # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志 long_query_time=2 ``` 配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。 如果这个时候有一条sql执行的时间超过2秒，则会记录到慢日志文件中 ### 4.2 一个SQL语句执行很慢, 如何分析 ? >难易程度：☆☆☆☆ > >出现频率：☆☆☆☆☆ 可以采用EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。 语法: ```SQL -- 直接在select语句之前加上关键字 explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ; ``` ![image-20220121151322661](MySQL相关面试题.assets/image-20220121151322661.png) Explain 执行计划中各个字段的含义: | 字段 | 含义 | | ------------ | ------------------------------------------------------------ | | id | select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。 | | select_type | 表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、
UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等 | | type | 表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。 | | possible_key | 显示可能应用在这张表上的索引，一个或多个。 | | key | 实际使用的索引，如果为NULL，则没有使用索引。 | | key_len | 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。 | | rows | MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。 | | filtered | 表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。 | | Extra | 额外的建议 | 主要可以根据以下字段，判断sql是否需要优化，特别是是否能命中索引或命中索引的情况 - type 通过sql的连接的类型进行优化 - possible_key 通过它查看是否可能会命中索引 - key 当前sql实际命中的索引 - key_len 索引占用的大小 - Extra 额外的优化建议 ### 4.3 什么情况下索引会失效 ? >难易程度：☆☆☆ > >出现频率：☆☆☆☆☆ 1). 最左前缀法则 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。 匹配最左前缀法则，走索引： ![image-20220402015342903](MySQL相关面试题.assets/image-20220402015342903.png) 违法最左前缀法则 ， 索引失效： ![image-20220402015405424](MySQL相关面试题.assets/image-20220402015405424.png) 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效： ![image-20220402015425744](MySQL相关面试题.assets/image-20220402015425744.png) 2). 范围查询右边的列，不能使用索引 。 ![image-20220402015442589](MySQL相关面试题.assets/image-20220402015442589.png) 根据前面的两个字段 name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。 3). 不要在索引列上进行运算操作， 索引将失效。 ![image-20220402015507834](MySQL相关面试题.assets/image-20220402015507834.png) 4). 字符串不加单引号，造成索引失效。 ![image-20220402015524822](MySQL相关面试题.assets/image-20220402015524822.png) 由于，在查询是，没有对字符串加单引号， MySQL的查询优化器，会自动的进行类型转换，造成索引失效。 5). 尽量使用覆盖索引，避免select * 尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。 ![image-20220402015553678](MySQL相关面试题.assets/image-20220402015553678.png) 如果查询列，超出索引列，也会降低性能。 ![image-20220402015610221](MySQL相关面试题.assets/image-20220402015610221.png) ```sql TIP :  using index ：使用覆盖索引的时候就会出现  using where：在查找使用索引的情况下，需要回表去查询所需的数据  using index condition：查找使用了索引，但是需要回表查询数据  using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表 查询数据 ``` 6). 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。 示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ： ```sql explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'; ``` ![image-20220402015711415](MySQL相关面试题.assets/image-20220402015711415.png) 7). 以%开头的Like模糊查询，索引失效。 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。 ![image-20220402015729911](MySQL相关面试题.assets/image-20220402015729911.png) 解决方案 ： 通过覆盖索引来解决 ![image-20220402015748294](MySQL相关面试题.assets/image-20220402015748294.png) 8). 如果MySQL评估使用索引比全表更慢，则不使用索引。 ![image-20220402015838310](MySQL相关面试题.assets/image-20220402015838310.png) 9). is NULL ， is NOT NULL 有时索引失效。 ![image-20220402015859587](MySQL相关面试题.assets/image-20220402015859587.png) 10). in 走索引， not in 索引失效。 ![image-20220402015915816](MySQL相关面试题.assets/image-20220402015915816.png) ### 4.4 MYSQL超大分页怎么处理 ? >难易程度：☆☆☆☆ > >出现频率：☆☆☆ 在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。 我们一起来看看执行limit分页查询耗时对比： ![image-20220123132838745](MySQL相关面试题.assets/image-20220123132838745.png) 通过测试我们会看到，越往后，分页查询效率越低，这就是分页查询的问题所在。 因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。 优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。 ```sql explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id; ``` ### 4.5 谈谈你对sql的优化的经验 >难易程度：☆☆☆☆ > >出现频率：☆☆☆☆☆ #### 4.5.1 表的设计优化 ①选择表合适存储引擎： - myisam: 应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的。 - Innodb： 事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。尽量 设计 所有字段都得有默认值,尽量避免null。 ②选择合适的数据类型 数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型，一般来说，数据库中的表越小，在它上面执行的查询也就会越快。 比如设置合适的数值（tinyint int bigint），要根据实际情况选择 比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低 #### 4.5.2 索引优化 - 表的主键、外键必须有索引； - 数据量大的表应该有索引； - 经常与其他表进行连接的表，在连接字段上应该建立索引； - 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； - 索引应该建在选择性高的字段上； （sex 性别这种就不适合） - 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； - 频繁进行数据操作的表，不要建立太多的索引； - 删除无用的索引，避免对执行计划造成负面影响； #### 4.5.3 sql语句优化 - SELECT语句务必指明字段名称（避免直接使用select * ） - SQL语句要避免造成索引失效的写法 - SQL语句中IN包含的值不应过多 - 当只需要一条数据的时候，使用limit 1 - 如果排序字段没有用到索引，就尽量少排序 - 如果限制条件中其他字段没有索引，尽量少用or - 尽量用union all代替union - 避免在where子句中对字段进行null值判断 - 不建议使用%前缀模糊查询 - 避免在where子句中对字段进行表达式操作 - Join优化 能用innerjoin 就不用left join right join，如必须使用 一定要已小表为驱动 #### 4.5.4 主从复制、读写分离 如果数据库的使用场景读的操作比较的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构，读写分离，解决的是，数据库的写入，影响了查询的效率。读写分离的基本原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。 数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。 #### 4.5.5 mysql的分库分表 见下文 ## 5 分库分表 ### 5.1主从同步原理 >难易程度：☆☆☆ > >出现频率：☆☆☆ MySQL主从复制的核心就是二进制日志， > 二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。 具体的主从同步过程如下： ![image-20220911213148696](MySQL相关面试题.assets/image-20220911213148696.png) 从上图来看，复制分成三步： 1. Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。 2. 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。 3. slave重做中继日志中的事件，将改变反映它自己的数据。 ### 5.2 读写分离的时候主从同步延时怎么解决？ >难易程度：☆☆☆☆ > >出现频率：☆☆☆ #### 5.2.1 为什么会延时 **随机重放** MySQL的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高。Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随机的，不是顺序的，成本高很多。所以SQL Thread线程的速度赶不上主库写binlog的速度，就会产生主从延迟 **锁等待** 另一方面，由于SQL Thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL Thread所能处理的速度，或者当slave中有大型query语句产生了锁等待那么延时就产生了。 #### 5.2.2 解决方案 **强制读主库** 如果你做的是类似支付这种对实时性要求非常高的业务，那么最直接的方法就是直接读主库，当然这种方法相当于从库做一个备份的功能了。 **延迟读** 就是在写入之后，等一段时间再读，Eg：写入后同步的时间是0.5s，读取的时候可以设置1s后再读，但是这个方案主要存在的问题就是，不知道主从同步完成所需要的时间。 **降低并发** 如果你理解了随机重放这个导致主从延迟的原因，那么就比较好理解了，控制主库写入的速度，主从延迟发生的概率自然就小了。{原因：因为主库中sql可能并发执行，可以控制并发速度}。 **并行复制(推荐)** MySQL 5.6 版本后，提供了一种并行复制的方式，通过将 SQL 线程转换为多个 work 线程来进行重放，这样就解决了主从延迟的问题。 ### 5.3 Mysql为什么要分库分表？分库分表的策略有哪些？ >难易程度：☆☆☆☆ > >出现频率：☆☆☆ #### 5.3.1 为什么要分库分表 ![image-20220911220816527](MySQL相关面试题.assets/image-20220911220816527.png) 随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈： 1. IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。 请求数据太多，带宽不够，网络IO瓶颈。 2. CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。 为了解决上述问题，我们需要对数据库进行分库分表处理。 ![image-20220911220759014](MySQL相关面试题.assets/image-20220911220759014.png) 分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。 #### 5.3.2 拆分策略 分库分表的形式，主要是两种：垂直拆分和水平拆分。而拆分的粒度，一般又分为分库和分表，所以组成的拆分策略最终如下： ![image-20220911220948216](MySQL相关面试题.assets/image-20220911220948216.png) #### 5.3.3 垂直拆分 1. 垂直分库 ![image-20220911221003953](MySQL相关面试题.assets/image-20220911221003953.png) 垂直分库：以表为依据，根据业务将不同表拆分到不同库中。 特点： - 每个库的表结构都不一样。 - 每个库的数据也不一样。 - 所有库的并集是全量数据。 2. 垂直分表 ![image-20220911221024489](MySQL相关面试题.assets/image-20220911221024489.png) 垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。 特点： - 每个表的结构都不一样。 - 每个表的数据也不一样，一般通过一列（主键/外键）关联。 - 所有表的并集是全量数据。 #### 5.3.4 水平拆分 1. 水平分库 ![image-20220911221042512](MySQL相关面试题.assets/image-20220911221042512.png) 水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。 特点： - 每个库的表结构都一样。 - 每个库的数据都不一样。 - 所有库的并集是全量数据。 2. 水平分表 ![image-20220911221100744](MySQL相关面试题.assets/image-20220911221100744.png) 水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。 特点： - 每个表的表结构都一样。 - 每个表的数据都不一样。 - 所有表的并集是全量数据。 > 在业务系统中，为了缓解磁盘IO及CPU的性能瓶颈，到底是垂直拆分，还是水平拆分；具体是分库，还是分表，都需要根据具体的业务需求具体分析。 #### 5.3.5 实现技术 - shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言，性能较高。 - MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。 ![image-20220911221413349](MySQL相关面试题.assets/image-20220911221413349.png) ### 5.4 分库分表后id主键如何处理？ >难易程度：☆☆☆ > >出现频率：☆☆ ![image-20220911221100744](MySQL相关面试题.assets/image-20220911221100744.png) - **主键自增**：数据库中间件可以设置主键的自增 ![image-20220911221829775](MySQL相关面试题.assets/image-20220911221829775.png) - **UUID** 好处就是本地生成，不要基于数据库来了；不好之处就是，UUID 太长了、占用空间大，**作为主键性能太差** 了；更重要的是，UUID 不具有有序性，会导致 B+ 树索引在写的时候有过多的随机写操作，不能产生有顺序的 append 操作，而需要进行 insert操作，将会读取整个 B+ 树节点到内存，在插入这条记录后会将整个节点写回磁盘，这种操作在记录占用空间比较 大的情况下，性能下降明显 - **snowflake** **算法** ## 6.MySQL锁相关 ### 6.1 Mysql中有哪几种锁？ MySQL中的锁，按照锁的粒度分，分为以下三类： - 全局锁：锁定数据库中的所有表。 - 表级锁：每次操作锁住整张表。 - 行级锁：每次操作锁住对应的行数据。 ### 6.3 表级锁和行级锁了解吗？有什么区别？ 表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。 行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。 ### 6.4 意向锁有什么作用？ 1). 介绍 为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。 假如没有意向锁，客户端一对表加了行锁后，客户端二如何给表加表锁呢，来通过示意图简单分析一下： 首先客户端一，开启一个事务，然后执行DML操作，在执行DML语句时，会对涉及到的行加行锁。 ![image-20220918204320511](MySQL相关面试题.assets/image-20220918204320511.png) 当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就会从第一行数据，检查到最后一行数据，效率较低。 ![image-20220918204337685](MySQL相关面试题.assets/image-20220918204337685.png) 有了意向锁之后 : 客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。 ![image-20220918204355492](MySQL相关面试题.assets/image-20220918204355492.png) 而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。 ![image-20220918204413960](MySQL相关面试题.assets/image-20220918204413960.png) ### 6.5 共享锁和排他锁呢？ InnoDB实现了以下两种类型的行锁： - 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。 - 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。 常见的SQL语句，在执行时，所加的行锁如下： | SQL | 行锁类型 | 说明 | | -------------------------------- | ---------- | ------------------------------------------ | | INSERT ... | 排他锁 | 自动加锁 | | UPDATE ... | 排他锁 | 自动加锁 | | DELETE ... | 排他锁 | 自动加锁 | | SELECT（正常） | 不加任何锁 | | | SELECT ... LOCK IN SHARE MODE | 共享锁 | 需要手动在SELECT之后加LOCK IN SHARE MODE | | SELECT ... FOR UPDATE | 排他锁 | 需要手动在SELECT之后加FOR UPDATE | ### 6.6 解释一下MVCC 全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。 #### 6.6.1 隐藏字段 ![image-20220125155559099](MySQL相关面试题.assets/image-20220125155559099.png) 当我们创建了上面的这张表，我们在查看表结构的时候，就可以显式的看到这三个字段。 实际上除了这三个字段以外，InnoDB还会自动的给我们添加三个隐藏字段及其含义分别是： | 隐藏字段 | 含义 | | ----------- | ------------------------------------------------------------ | | DB_TRX_ID | 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。 | | DB_ROLL_PTR | 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。 | | DB_ROW_ID | 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。 | 而上述的前两个字段是肯定会添加的， 是否添加最后一个字段DB_ROW_ID，得看当前表有没有主键，如果有主键，则不会添加该隐藏字段。 #### 6.6.2 undolog 回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。 当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。 而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。 #### 6.6.3 版本链 有一张表原始数据为： ![image-20220125162711914](MySQL相关面试题.assets/image-20220125162711914.png) > DB_TRX_ID : 代表最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID，是自增的。 > > DB_ROLL_PTR ： 由于这条数据是才插入的，没有被更新过，所以该字段值为null。 然后，有四个并发事务同时在访问这张表。 A. 第一步 ![image-20220125162202010](MySQL相关面试题.assets/image-20220125162202010.png) 当事务2执行第一条修改语句时，会记录undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本。 ![image-20220918214543062](MySQL相关面试题.assets/image-20220918214543062.png) B.第二步 ![image-20220918214529046](MySQL相关面试题.assets/image-20220918214529046.png) 当事务3执行第一条修改语句时，也会记录undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本。 ![image-20220918214513175](MySQL相关面试题.assets/image-20220918214513175.png) C. 第三步 ![image-20220125163929573](MySQL相关面试题.assets/image-20220125163929573.png) 当事务4执行第一条修改语句时，也会记录undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本。 ![image-20220918214457963](MySQL相关面试题.assets/image-20220918214457963.png) > 最终我们发现，不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。 #### 6.6.4 readview ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。 ReadView中包含了四个核心字段： | 字段 | 含义 | | -------------- | ---------------------------------------------------- | | m_ids | 当前活跃的事务ID集合 | | min_trx_id | 最小活跃事务ID | | max_trx_id | 预分配事务ID，当前最大事务ID+1（因为事务ID是自增的） | | creator_trx_id | ReadView创建者的事务ID | 而在readview中就规定了版本链数据的访问规则： trx_id 代表当前undolog版本链对应事务ID。 | 条件 | 是否可以访问 | 说明 | | ---------------------------------- | ----------------------------------------- | ------------------------------------------ | | trx_id == creator_trx_id | 可以访问该版本 | 成立，说明数据是当前这个事务更改的。 | | trx_id < min_trx_id | 可以访问该版本 | 成立，说明数据已经提交了。 | | trx_id > max_trx_id | 不可以访问该版本 | 成立，说明该事务是在ReadView生成后才开启。 | | min_trx_id <= trx_id <= max_trx_id | 如果trx_id不在m_ids中，是可以访问该版本的 | 成立，说明数据已经提交。 | 不同的隔离级别，生成ReadView的时机不同： - READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。 - REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。 #### 6.6.5 原理分析 ##### 6.6.5.1 RC隔离级别 RC隔离级别下，在事务中每一次执行快照读时生成ReadView。 我们就来分析事务5中，两次快照读读取数据，是如何获取数据的? 在事务5中，查询了两次id为30的记录，由于隔离级别为Read Committed，所以每一次进行快照读都会生成一个ReadView，那么两次生成的ReadView如下。 ![image-20220125225124616](MySQL相关面试题.assets/image-20220125225124616.png) 那么这两次快照读在获取数据时，就需要根据所生成的ReadView以及ReadView的版本链访问规则，到undolog版本链中匹配数据，最终决定此次快照读返回的数据。 A. 先来看第一次快照读具体的读取过程： ![image-20220125225104133](MySQL相关面试题.assets/image-20220125225104133.png) ![image-20220125221055898](MySQL相关面试题.assets/image-20220125221055898.png) 在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配： - 先匹配![image-20220125221318271](MySQL相关面试题.assets/image-20220125221318271.png) 这条记录，这条记录对应的trx_id为4，也就是将4带入右侧的匹配规则中。 ①不满足 ②不满足 ③不满足 ④也不满足 ，都不满足，则继续匹配undo log版本链的下一条。 - 再匹配第二条 ![image-20220125221613700](MySQL相关面试题.assets/image-20220125221613700.png) ，这条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也不满足 ，都不满足，则继续匹配undo log版本链的下一条。 - 再匹配第三条 ![image-20220125222501959](MySQL相关面试题.assets/image-20220125222501959.png) ，这条记录对应的trx_id为2，也就是将2带入右侧的匹配规则中。①不满足 ②满足 终止匹配，此次快照读，返回的数据就是版本链中记录的这条数据。 B. 再来看第二次快照读具体的读取过程: ![image-20220125225338333](MySQL相关面试题.assets/image-20220125225338333.png) ![image-20220125225403552](MySQL相关面试题.assets/image-20220125225403552.png) 在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配： - 先匹配![image-20220125221318271](MySQL相关面试题.assets/image-20220125221318271.png) 这条记录，这条记录对应的trx_id为4，也就是将4带入右侧的匹配规则中。 ①不满足 ②不满足 ③不满足 ④也不满足 ，都不满足，则继续匹配undo log版本链的下一条。 - 再匹配第二条 ![image-20220125221613700](MySQL相关面试题.assets/image-20220125221613700.png) ，这条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中。①不满足 ②满足 。终止匹配，此次快照读，返回的数据就是版本链中记录的这条数据。 ##### 6.6.5.2 RR隔离级别 RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。 而RR 是可重复读，在一个事务中，执行两次相同的select语句，查询到的结果是一样的。 那MySQL是如何做到可重复读的呢? 我们简单分析一下就知道了 ![image-20220125225724902](MySQL相关面试题.assets/image-20220125225724902.png) 我们看到，在RR隔离级别下，只是在事务中第一次快照读时生成ReadView，后续都是复用该ReadView，那么既然ReadView都一样， ReadView的版本链匹配规则也一样， 那么最终快照读返回的结果也是一样的。 所以呢，MVCC的实现原理就是通过 InnoDB表的隐藏字段、UndoLog 版本链、ReadView来实现的。 而MVCC + 锁，则实现了事务的隔离性。 而一致性则是由redolog 与 undolog保证。 ![image-20220125230214424](MySQL相关面试题.assets/image-20220125230214424.png) ## 7 面试现场 ### 7.1 SQL规范 >**面试官**：Mysql查询语句的执行顺序？ > >**候选人**： > >嗯~~ > >我们一般编写sql的顺序是 > >```sql >select 字段列表 >from 表名列表 >join on 表关联 >where 条件列表 >group by 分组字段列表 >having 分组后的条件列表 >order by 排序字段列表 >limit 分页参数 >``` > >其实sql的执行顺序是： > >```sql >from 表名列表 >join on 表关联 >where 条件列表 >group by 分组字段列表 >having 分组后的条件列表 >select 字段列表 >order by 排序字段列表 >limit 分页参数 >``` > >**面试官**：Mysql 如何实现多表查询？ > >**候选人**： > >嗯，这个也分了好多种，分为内连接和外连接，还能自连接和子查询，这些都是多表查询 > >- 内连接又有两种 > - 隐式：`select ...from 表A,表B where 条件...` > - 显式：`select ... from 表A inner join 表B on 条件...` > >- 外连接也是两种 > - 左外连接：`select ...from 表A left join 表B on 条件...` > - 右外连接：`select ...from 表A right join 表B on 条件...` >- 自连接，比如`select ...from 表A 别名1,表A 别名2 where 条件...` >- 子查询又包含了列子查询、行子查询、表子查询 > >**面试官**：MYSQL内连接和外连接的区别 ? > >**候选人**： > >嗯~~是这样 > >内连接会取出连接表中匹配到的数据，匹配不到的不保留； > >而外连接会取出连接表中匹配到的数据，匹配不到的也会保留，其值为NULL。 > >- 左外连接，以左边的表为主表 >- 右外连接，以右边的表为主表 > >以某一个表为主表后，进行关联查询，不管能不能关联的上，主表的数据都会保留，关联不上的以NULL显示 > >**面试官**：CHAR和VARCHAR的区别？ > >**候选人**： > >嗯~~是这样的 > >区别主要有几个 > >第一，char最大长度是255字符，varchar最大长度是65535个字节。 > >第二，char是定长的，不足的部分用隐藏空格填充，varchar是不定长的。 > >第三，char会浪费空间，varchar会更加节省空间。 > >第四，查找效率：char查找效率会很高，varchar查找效率会更低。 > >​ varchar需要计算内容占用的长度，而char不会，所以char的效率稍高一些 > >在项目中的使用，这两种方式都会用到，比如像一些枚举值可以选择使用char，像一些描述信息或名字类可以选择使用varchar ### 7.2 事务 >**面试官**：什么是事务？ > >**候选人**： > >简单说就是由多个操作组成的一个逻辑单元，组成这个逻辑单元的多个操作要么都成功，要么都失败。 > >**面试官**：ACID是什么？可以详细说一下吗？ > >**候选人**： > >嗯~好的 > >A的意思是Atomicity原子性：就是上面说的,要么全部成功,要么全部失败，不可能只执行一部分操作。 > >C的意思是Consistency一致性：系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态。 > >I的意思是Isolation隔离性: **通常**来说:一个事务在完全提交之前,对其他事务是不可见的.注意前面的通常来说加了红色,意味着有例外情况。 > >D的意思是Durability持久性：一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果。 > >举例：转账 > >A向B转账500，转账成功，A扣除500元，B增加500元，原子操作体现在要么都成功，要么都失败 > >在转账的过程中，数据要一致，A扣除了500，B必须增加500 > >在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰 > >在转账的过程中，持久性体现在事务提交后，要把数据持久化（可以说是落盘操作） > >**面试官**：并发事务带来哪些问题？ > >**候选人**： > >我们在项目开发中，多个事务并发进行是经常发生的，并发也是必然的，有可能导致一些问题 > >第一是脏读（Dirty read）， 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。 > >第二是丢失修改（Lost to modify）：指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。 > >第三是不可重复读（Unrepeatableread）：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。 > >第四是幻读（Phantom read）：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。 > >**面试官**：怎么解决这些问题呢？MySQL的默认隔离级别是？ > >**候选人**： > >解决方案是对事务进行隔离 > >MySQL支持四种隔离级别: > >**未提交读(READ UNCOMMITTED)**：这个隔离级别下,其他事务可以看到本事务没有提交的部分修改。因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚)。这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用. > >**已提交读(READ COMMITTED)**：其他事务只能读取到本事务已经提交的部分。这个隔离级别有不可重复读的问题，在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改。 > >**REPEATABLE READ(可重复读)**：可重复读隔离级别解决了上面不可重复读的问题(看名字也知道)，但是不能完全解决幻读。MySql默认的事务隔离级别就是：**REPEATABLE READ** > >**SERIALIZABLE(可串行化)**：这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用。 ### 7.3 索引 >**面试官**：MYSQL支持的存储引擎有哪些, 有什么区别 ? > >**候选人**： > >常见的存储有两个，InnoDB和MyISAM，当然mysql还支持其他的存储引擎，只是用的不多。 > >InnoDB和MyISAM主要的区别是： > >- InnoDB支持事务，而MyISAM不支持事务 > >- InnoDB支持表锁和行锁，MyISAM只支持表锁 > >- InnoDB支持外键，MyISAM不支持外键 > >我们通常在创建表的时候都是使用的InnoDB作为存储引擎，而mysql5.5以后默认的引擎也是InnoDB > >**面试官**：了解过Mysql的索引嘛 ? > >**候选人**： > >嗯，清楚的，这个是比较常用的 > >索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。 > >**面试官**：索引的底层数据结构了解过嘛 ? > >**候选人**： > >嗯，了解过~~~ > >MySQL默认使用的索引底层数据结构是B+树 > >B+树不同于二叉树，一种多叉路衡查找树，每个节点可以有多个分支 > >比如一棵5阶的B树，每一个节点最多存储4个key，对应5个指针。一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。所有的数据都会出现在叶子节点，叶子节点形成一个双向链表，利于范围查询。非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。 > > > >**面试官**：什么是聚簇索引什么是非聚簇索引 ? > >**候选人**： > >嗯~~ > >在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种： > >聚集索引(Clustered Index)，将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，必须有,而且只有一个，通常情况下一个表的主键就是聚集索引，如果表不存在则会使用唯一索引作为聚集索引，如果也没有唯一索引，InnoDB会自动生成一个rowid作为隐藏的聚集索引 > >二级索引(Secondary Index)，也可以成为非聚簇索引，将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个。一般我们自己创建的复合索引这些都是二级索引 > >**面试官**：知道什么是回表查询嘛 ? > >**候选人**： > >嗯，知道的~ > >回表查询是使用二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式 > >举个例子： > >有一个user表，给user表中的name创建了索引，那这个name的索引其实就是二级索引 > >当我们select * from user where name = xx 的时候，条件name是可以走索引，但是要查询表中所有的字段，这个时候name的这个索引是没有的，但是能查询出来name这一行的id值，然后这个id值其实就是我们刚才说的聚集索引，然后根据这个id再去到索引去查一次，就相当于回表查询了 > > > >**面试官**：索引创建原则有哪些？ > >**候选人**： > >嗯，这个限制很多，我想一下~~~~ > >有这么几种情况吧 > >1). 针对于数据量较大，且查询比较频繁的表建立索引。 > >2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。 > >3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。 > >4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。 > >5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。 > >6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。 > >7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。 > > > >**面试官**：知道什么是左前缀原则嘛 ? > >**候选人**： > >嗯！！，这个是比较常见的 > >就是说如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。 > >比如有一个user表，给里面的字段创建了一个复合索引，顺序是name,age,email > >当查询索引不包含name的时候会失效的，当然查询name和age则不会失效，查询name和email跳过了age，则只有name会命中索引 > >**面试官**：知道什么叫覆盖索引嘛 ? > >**候选人**： > >嗯~~ > >覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。 > >比如有一个user表，给里面的字段创建了一个复合索引，顺序是name,age,email > >当查询索引只select后面只包含name,age,email的时候就算是覆盖索引了 > >**面试官**：索引是越多越好嘛? 什么样的字段需要建索引, 什么样的字段不需要 ? > >**候选人**： > >索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。 > >有些字段是不适合创建索引的，比如 > >- 更新频繁字段不适合创建索引 > >- 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低) >- 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。 > - 比如省会，城市、月份 >- 对于定义为text、image和bit的数据类型的列不要建立索引。 > >我们通常创建索引都是选择，比如 > >- 数据量较大，且查询比较频繁的表建立索引。 > > 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。 ### 7.4 调优 >**面试官**：如何定位慢查询 ? > >**候选人**： > >可以开启mysql的慢查询日志 > >慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。 > >如果要开启慢查询日志，需要在MySQL的配置文件中开启就行了，配置完毕之后，一旦产生了慢查询可以查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log，然后在进行分析就行了 > >我们一般设置的一条sql执行的时间超过2秒，就会记录到慢日志文件中 > >**面试官**：一个SQL语句执行很慢, 如何分析 ? > >**候选人**： > >可以采用EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。 > >主要可以根据几个字段，判断sql是否需要优化，特别是是否能命中索引或命中索引的情况 > >- type 通过sql的连接的类型进行优化 >- possible_key 通过它查看是否可能会命中索引 >- key 当前sql实际命中的索引 >- key_len 索引占用的大小 >- Extra 额外的优化建议 > >**面试官**：什么情况下索引会失效 ? > >**候选人**： > >嗯，这个情况有很多，比如： > >1). 违反了最左前缀法则 > >2). 范围查询右边的列，不能使用索引 。 > >3). 在索引列上进行运算操作， 索引将失效。 > >4). 字符串不加单引号，造成索引失效。 > >5). 尽量使用覆盖索引，避免select * >尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。 > >如果查询列，超出索引列，也会降低性能。 > >6). 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。 > >7). 以%开头的Like模糊查询，索引失效。通过覆盖索引来解决 > >8). 如果MySQL评估使用索引比全表更慢，则不使用索引。 > >9). is NULL ， is NOT NULL 有时索引失效。 > >10). in 走索引， not in 索引失效。 > >**面试官**：MYSQL超大分页怎么处理 ? > >**候选人**： > >在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。 > >一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，然后通过覆盖索引加子查询形式进行优化。 > >**面试官**：谈谈你对sql的优化的经验 > >**候选人**： > >嗯，这个是方面的，我谈下我们的做法： > >①选择表合适存储引擎： > >- myisam: 应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的。 >- Innodb： 事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。尽量 设计 所有字段都得有默认值,尽量避免null。 > >②选择合适的数据类型 > >数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型，一般来说，数据库中的表越小，在它上面执行的查询也就会越快。 > >比如设置合适的数值（tinyint int bigint），要根据实际情况选择 > >比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低 > >③索引优化 > >- 表的主键、外键必须有索引； >- 数据量大的表应该有索引； >- 经常与其他表进行连接的表，在连接字段上应该建立索引； >- 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； >- 索引应该建在选择性高的字段上； （sex 性别这种就不适合） >- 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； >- 频繁进行数据操作的表，不要建立太多的索引； >- 删除无用的索引，避免对执行计划造成负面影响； > >④sql语句优化 > >- SELECT语句务必指明字段名称（避免直接使用select * ） >- SQL语句要避免造成索引失效的写法 >- SQL语句中IN包含的值不应过多 >- 当只需要一条数据的时候，使用limit 1 >- 如果排序字段没有用到索引，就尽量少排序 >- 如果限制条件中其他字段没有索引，尽量少用or >- 尽量用union all代替union >- 避免在where子句中对字段进行null值判断 >- 不建议使用%前缀模糊查询 >- 避免在where子句中对字段进行表达式操作 >- Join优化 能用innerjoin 就不用left join right join，如必须使用 一定要已小表为驱动 > >⑤主从复制、读写分离 > >如果数据库的使用场景读的操作比较的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构，读写分离，解决的是，数据库的写入，影响了查询的效率。读写分离的基本原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。 数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。 > >⑥mysql的分库分表 > >当单表的数据量太大或单库的数据量太大的话，性能会降的比较厉害，这个时候就要考虑选择合适的拆分策略，比如垂直分库和水平分库 ### 7.5 分库分表 >**面试官**：说一下主从同步的原理？ > >**候选人**： > >嗯，好的。 > >MySQL主从复制的核心就是二进制日志，二进制日志记录了所有的 DDL语句和 DML语句 > >具体的主从同步过程大概的流程是这样的： > >1. Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。 >2. 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。 >3. slave重做中继日志中的事件，将改变反映它自己的数据。 > >**面试官**：好的，如果读写分离的时候主从同步延时怎么解决？ > >**候选人**： > >嗯，我想一下~~~ > >其实在实际应用中如果数据量超大的话，可能会出现延迟，MySQL的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高。Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随机的，不是顺序的，成本高很多。所以SQL Thread线程的速度赶不上主库写binlog的速度，就会产生主从延迟 > >第二个出现延迟的情况是由于SQL Thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL Thread所能处理的速度，或者当slave中有大型query语句产生了锁等待那么延时就产生了。 > >那解决方案的可以设置并行复制 >MySQL 5.6 版本后，提供了一种并行复制的方式，通过将 SQL 线程转换为多个 work 线程来进行重放，这样就解决了主从延迟的问题。 > >**面试官**：Mysql为什么要分库分表？分库分表的策略有哪些？ > >**候选人**： > >我们当时项目业务量其实一直都在增长，有的单表就接近了2千万的数据，如果不分库分表的话，性能会直线下降。 > >那关于数据库拆分的策略有很多，比如：垂直分库，垂直分表、水平分库，水平分表。 > >因为我们现在微服务项目的开发，每个微服务都是管理的自己的业务库，在一定意义上其实已经进行了垂直分库，不过如果单库的数据量太大的话，也是装不下，所以，我们后来又进行了水平分库 > >水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。当然为了让我们的微服务刚方便的去调用多个库中的数据，我们当时采用的mycat作为我们的分库分表的中间件来使用的。 > >**面试官**：分库分表后id主键如何处理？ > >**候选人**： > >嗯，这个也有很多种方案 > >第一如果选择的主键还是自增的话，可以使用数据库中间件去解决，比如mycat，就能在配置中设置一个表是自增，由mycat帮助我们去设置自增 > >第二，如果不是自增的，可以选择UUID或雪花算法来生成id，尽可能不要使用UUID，因为UUID 太长了、占用空间大，作为主键的性能不高，也不能排序 > > ### 7.6 MySQL锁相关 >**面试官**：Mysql中有哪几种锁？ > >**候选人**： > >嗯~~~ > >MySQL中按照锁的粒度分有三类，分别是 > >- 全局锁：锁定数据库中的所有表。 > >- 表级锁：每次操作锁住整张表。 > >- 行级锁：每次操作锁住对应的行数据。 > >**面试官**：表级锁和行级锁了解吗？有什么区别？ > >**候选人**： > >嗯~是这样的 > >表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB等存储引擎中。 > >行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。 > >**面试官**：意向锁有什么作用？ > >**候选人**： > >嗯，是这样的 > >我们在DML操作的时候有可能会产生行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。 > > > >**面试官**：共享锁和排他锁呢？ > >**候选人**： > >InnoDB实现了两种类型的行锁，第一个就是共享锁，第二个是拍他锁 > >- 共享锁是指允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。 >- 排他锁是指允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。 > >在我们平时的sql语句中，insert、update、delete都会自动添加排他锁，而select不会加锁 > >当然，如果select想要加锁也是可以的，如果是共享锁的话则可以在select之后加LOCK IN SHARE MODE ，如果是排他锁需要手动在SELECT之后加FOR UPDATE > >**面试官**：解释一下MVCC > >**候选人**： > >嗯~~ > >MVCC多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。 > >- 三个隐藏字段分别是隐藏主键，事务ID，回滚指针。 > >- undo log是各个事务修改同一条记录的时候生成的历史记录，方便回滚，同时会生成一条版本链。 > >- ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。 > > - ReadView中包含了四个核心字段： > > - m_ids：当前活跃的事务ID集合 > - min_trx_id：最小活跃事务ID > - max_trx_id：预分配事务ID，当前最大事务ID+1（因为事务ID是自增的） > - creator_trx_id：ReadView创建者的事务ID > > - 而在readview中就规定了版本链数据的访问规则： > > trx_id 代表当前undolog版本链对应事务ID。 > > - trx_id == creator_trx_id 可以访问该版本，成立，说明数据是当前这个事务更改的。 > - trx_id < min_trx_id 可以访问该版本，成立，说明数据已经提交了。 > - trx_id > max_trx_id 不可以访问该版本，成立，说明该事务是在ReadView生成后才开启。 > - min_trx_id <= trx_id <= max_trx_id，如果trx_id不在m_ids中，是可以访问该版本的，成立，说明数据已经提交。 > > 不同的隔离级别，生成ReadView的时机不同： > > - READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。 > > - REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。