【MySQL】一文看懂MySQL所有常见问题

正义夫

MySQL作为一款开源关系型数据库，如今绝对是占据关系型数据库的主导地位，不仅是面试中的常客，也是日常工作中最主要接触的数据库。因此，无论是背面试八股，还是工作使用，都是一定要深度掌握的一个知识点。今天就用一篇文章讲清楚MySQL的所有问题

着急的小伙伴可直接跳到最后MySQL常见面试题总结

一、事务

定义：一组操作要么全部成功，要么全部失败，目的是为了保证数据最终的一致性
在MySQL中，提供了一系列事务相关的命令：

• start transaction | begin | begin work：开启一个事务
  
• commit：提交一个事务
  
• rollback：回滚一个事务
  

事务的ACID

• 原子性（Atomicity）：当前事务操作要么同时成功，要么同时失败。原子性由undo log日志来保证
  
• 一致性（Consistency）：使用事务的最终目的，由其他三个特性保证
  
• 隔离性（Isolation）：事务并发执行时，内部操作互不干扰。InnoDB中隔离性由各种锁和MVCC保证
  
• 持久性（Durability）：一旦提交事务，它对数据的改变是永久性的。持久性由redo log日志来保证
  

事务的隔离级别

• read uncommit（读未提交）：处于该隔离级别的数据库，脏读、不可重复读、幻读问题都有可能发生
  
• read commit（读已提交）：处于该隔离级别的数据库，解决了脏读问题，不可重复读、幻读问题依旧存在
  
• repeatable read（可重复读）：处于该隔离级别的数据库，解决了脏读、不可重复读问题，幻读问题依旧存在
  
• serializable（串行）：处于该隔离级别的数据库，以上问题全部解决
  

脏读、幻读、不可重复读问题

脏读：读取到其他事务未提交的数据，由于数据还没提交，因此可能产生回滚

幻读：主要针对插入删除操作来说，比如事务A对全部数据的某一字段做了修改并提交，若事务A提交前，事务B插入了一条数据，事务A再次查询会发现存在修改未生效的数据，如同幻觉

不可重复读：多次读取同一数据得到不同结果

区别：

• 脏读重在指一个事务读到了其他事务未提交的数据。
  
• 不可重复读主要在于一个事务中多次读到同一条数据，但前后读到的结果不一样，这是因为其他事务对数据进行修改并提交导致。
  
• 幻读则是因为被其他事务插入或者删除的数据影响，一个事务内同样条件的数据记录变多或者变少了。
  

MVCC

MVCC机制的全称为Multi-Version Concurrency Control，即多版本并发控制技术，主要是为了提升数据库并发性能而设计的，其中采用更好的方式处理了读-写并发冲突，做到即使有读写冲突时，也可以不加锁解决，从而确保了任何时刻的读操作都是非阻塞的。
对于使用 InnoDB 存储引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列：

• trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
  
• roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。
  

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。
MCC的多版本主要依赖Undo-log日志来实现，而并发控制则通过表的隐藏字段+ReadView快照来实现，通过Undo-log日志、隐藏字段、ReadView快照，就实现了MVCC机制
MySQL中的日志

undo日志

undo log 有两个作用：提供回滚和多个行版本控制(MVCC)
undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

• 执行 rollback 时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
  
• 在应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。
  
• undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment
  
• undo log也会产生redo log，因为undo log也要实现持久性保护。
  
• undo 日志一般会在事务提交时被删除，但是如果 undo 日志为 MVCC 服务 则暂时保留
  
• 一个事务会产生多个 undo 日志，mysql有专门的 undo 页 保存 undo 日志。innodb 会为每一个事务单独分配 undo 页链表（最多分配 4 个链表）
  

redo日志

InnoDB独有，用于MySQL崩溃后重启时的数据恢复
更新表数据的时候，如果发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。
刷盘时机

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数（默认1），它支持三种策略

• 设置为0的时候，表示每次事务提交时不进行刷盘操作，只是保留在 redo log buffer中，mysql 崩溃会丢失1s的数据；
  
• 设置为1的时候，表示每次事务提交时都将进行刷盘操作（默认值），持久化到磁盘；
  
• 设置为2的时候，表示每次事务提交时都只把redo log buffer内容写入page cache（OS Buffer），OS宕机会丢失1s的数据，因为未进行持久化；
  

InnoDB 存储引擎有一个后台线程，每隔1秒，就会把 redo log buffer 中的内容写到文件系统缓存（OS Buffer），然后调用 fsync 刷盘。
redo log buffer占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。
binlog日志

binlog 是归档日志，属于 Server 层的日志，是一个二进制格式的文件，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”。
不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。它的主要作用就是数据备份、主从复制。
binlog会记录所有涉及更新数据的逻辑操作，属于逻辑日志，并且是顺序写。
binglog格式

binlog 日志有三种格式，可以通过binlog_format参数指定。

• statement ：记录的内容是SQL语句原文，存在数据一致性问题；
  
• row：记录包含操作的具体数据，能保证同步数据的一致性；
  
• mixed：记录的内容是前两者的混合，MySQL会判断这条SQL语句是否可能引起数据不一致：如果是，就用row格式，否则就用statement格式。
  

写入机制

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。
因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。
binlog 也提供了 sync_binlog 参数来控制写入 page cache 和磁盘的时机：

• 0：每次提交事务都只写入到文件系统的 page cache，由系统自行判断什么时候执行fsync，机器宕机，page cache里面的 binlog 会丢失。
  
• 1：每次提交事务都会执行fsync，就如同 redo log 日志刷盘流程 一样。
  
• N(N>1)：每次提交事务都写入到文件系统的 page cache，但累积N个事务后才fsync。如果机器宕机，会丢失最近N个事务的binlog日志。
  

relay日志

主从同步使用的中继日志，用于存储从master节点同步过来的binlog日志内容
二、索引

索引能快速定位数据的一种数据结构，其设计思想是以空间换时间

1. B+树索引

衍变过程：

• 二叉树：每个节点包含两个叶子节点，小的在左，大的在右。
  
  
  
  • 缺点：如果数据有序插入，树的高度会增高，降低查询效率，最坏情况是二叉树变成一个链表
    
  
  

• 红黑树：平衡二叉树，在二叉树的基础上，通过自旋进行平衡，从而减少树的高度。
  
  
  
  • 优点：数据有序插入时性能最高
    
  • 缺点：随数据量增加，树的高度依然会增加
    
  
  

• B树：平衡多路查找树，解决二叉树的高度问题，不再限制节点只能为两个；节点中的数据索引从左往右递增排列
  
  
  
  • 优点：降低树的高度，搜索深度降低
    
  • 缺点：作为存储索引，范围查找的搜索难度大
    
  
  

如下图是一个三阶B树，每个节点存两个数据，并存三个寻址地址指向下一个节点

• B+树：B树的优化，使其更适合存储索引结构
  相对于B树，它做了一下优化
  
  
  • B+树把所有数据都存在了叶子结点，非叶子节点不存数据。减少了每次判断节点时的拿取节点的效率
    
  • B+树的叶子节点之间增加了双向指针。提高了范围查找的效率
    
  
  

mysql默认文件页大小是16kb，一个寻址数据大概6b，若存储的是bigint，即8b，那么一块文件页能存储 16kb / (8b + 6b) = 1170个索引；那么一棵三阶B+树，能存储 16x1170x1170≈2000万数据

B树与B+树的区别

• B树的所有节点都会存储数据，B+树仅把数据存在叶子节点，而非叶子节点可以存储更多的阶数指针，并且节点不存数据，磁盘IO也会更快
  
• B+树的范围查找，排序查找，分组查找以及去重查找更简单
  
• 数据记录之间通过链表连接，可以很方便的在数据查询后进行升序或降序操作
  

MySQL中的InnoDB引擎会为每个表创建主键索引，如果表没有明确的主键索引，InnoDB会使用自动生成的隐藏的主键（RowId）来创建索引
2. Hash索引

Hash索引即哈希结构索引，数据结构和Java的HashMap差不多，是数组加链表的形式
InnoDB不支持显示创建Hash索引，只支持自适应Hash索引。仅memory引擎支持hash索引，memory引擎运行于内存中。

• 优点：等值比较效率高
• 缺点：不支持范围查询，也不支持排序，因为索引列无序
3. 聚集索引和非聚集索引

按物理存储方式分类：InnoDB使用聚集索引，MyISAM使用非聚集索引
创建两个测试数据表test_innodb（使用InnoDB引擎）和test_myisam（使用MyISAM引擎），最终在mysql的服务器目录下看到的文件如下：

1. -rw-r----- 1 mysql mysql  114688 Oct 31 11:30 test_innodb.ibd     
2. -rw-r----- 1 mysql mysql       0 Oct 31 11:31 test_myisam.MYD     
3. -rw-r----- 1 mysql mysql    1024 Oct 31 11:31 test_myisam.MYI   
4. -rw-r----- 1 mysql mysql    1653 Oct 31 11:31 test_myisam_491.sdi  

复制代码

InnoDB仅用一个文件ibd存储了索引和数据
MyISAM分为了三个文件，MYD存储表数据，MYI存储表索引，sdi存储表元数据
MyISAM的叶子节点存储的不是数据，而是数据对应的磁盘指针
InnoDB的主键索引的叶子节点存储的表的所有列，非主键索引的叶子结点存储的表的主键
因此InnoDB是聚集索引，MyISAM是非聚集索引
• 聚集索引优点：因为所有数据都存在B+树的叶子节点，因此可以减少一个数据的查询，提高了查询效率
• 聚集索引缺点：对数据进行修改删除操作时需要更新索引树，增加系统开销，非聚集索引可以避免这个缺陷
4. 二级索引

所有非主键索引均为二级索引，InnoDB中二级索引采用非聚集索引

如上所示，我们对一个age字段进行二级索引，最终得到数据时ID主键，我们需要再根据ID主键去查找数据（回表），这也是非聚集索引

1. SELECT * FROM `user` WHERE age = 17;

复制代码

比如上面sql，首先会通过age=17这个条件在age的二级索引中找到id7，然后再用id7去ID索引去查找对应的数据
5. 覆盖索引

还是比如上面的sql，若使用select *，那么在通过二级索引找到id后，还需要到id索引找到数据列，假如对上述sql优化如下

1. SELECT id FROM `user` WHERE age = 17;

复制代码

那么在age的二级索引的数据中，就已经有了id列，不再需要回表查询这个ID对应的所有数据，这种情况叫覆盖索引，即索引结果包含了所有所需的数据。
覆盖索引也是sql优化中的一个手段
6. 索引下推

是MySQL5.6针对扫描二级索引的优化，用于减少范围查询时的回表次数。

1. SELECT * FROM `user` WHERE age > 10 AND age < 20;

复制代码

比如在这次查询中，我们查到5个人年龄在10到20之间，如果没有索引下推，即会产生5次回表查询来获取所有数据。索引下推即让5次回表合并为一次回表查询。
7. 单列索引和联合索引

• 单列索引：只有一个字段的索引
• 联合索引：有多个字段的索引
在联合索引时，多个字段会存在同一个索引页（B+树节点）中，并且会以优先级按第一个字段，第二个字段，第N字段组合排序

最左前缀原则

在联合索引时，SQL查询需要遵循最左前缀原则，假如建立name和age字段的联合索引。如果我们查询条件只查第二个索引字段age，而不查第一个索引字段name，那么在扫描时，是无序的，只能做全盘扫描，比如以下例子：
以下SQL会执行全盘扫描

1. SELECT * FROM `user` WHERE age=15;

复制代码

而以下SQL则会快速定位索引

1. SELECT * FROM `user` WHERE name='张三' AND age=15;

复制代码

联合索引优势

• 减少开销
  假如有三个字段abc需要建立索引，分别建立三个索引比建立一个索引的开销更大，因为它会创建3个B+树，并且每次插入删除操作都要操作3个B+树。而建立一个联合索引相当于建立了(a)(a,b)(a,b,c)三个索引。对于大数据量的表来说，使用联合索引会大大减少开销。
  
• 覆盖索引
  对于联合索引(a,b,c)，如有sql
  

1. SELECT a,b,c FROM table WHERE a='xxx' AND b='XX';

复制代码

那么MySQL可以通过索引直接得到数据而省略回表操作，提高IO性能

• 效率高
  索引列多，通过联合索引筛选出的数据越少
  

8. 索引优缺点及适合场景

• 索引优点：
  
  
  • 提高检索效率
    
  • 降低排序成本，索引对应字段可以自动排序，默认升序ASC
    
  
  
• 索引缺点：
  
  
  • 创建索引和维护索引增加成本，并且这种成本随数据量的增加而增加
    
  • 索引占用物理空间，数据量越大空间越大
    
  • 降低表的增删改效率，每次更新操作都会维护索引表
    
  
  
• 适合场景：
  
  
  • 频繁作为查询条件的字段应该创建索引
    
  
  
• 不适合场景：
  
  
  • 字段唯一性太差不适合索引
    
  • 更新频繁字段不适合索引
    
  • 不在where语句中字段（不会作为查询条件）不适合索引
    
  
  

9. 索引优化

• 最左前缀原则
  
  
  
  • 联合索引必须包含第一个索引字段，才能让索引生效
    
  • 查询条件不能跳过联合索引的中间的字段，必须依次加入查询条件
    
  
  

可归纳为：保证查询条件的有序性才能让索引生效

• 索引列不要函数计算
  如果在索引字段上进行了函数计算，则索引失效，进行全表扫描，比如以下场景
  

1. SELECT * FROM `user` WHERE left(name,2) = '张三';

复制代码

• 范围后面的索引全失效
  在联合索引的情况下，范围查询会使后面字段无序，造成部分索引失效，比如以下场景
  

1. SELECT * FROM `user` WHERE name = '张三' AND age > 18 AND position = 0;

复制代码

• 覆盖索引不写星
  SELECT的字段尽量使用联合索引中的字段
  
• 不等空值和or会让索引失效
  MySQL中使用不等于(!=或者)，not in，not exist的时候无法使用索引导致全表扫描，而，=，MySQL内部优化器会根据检索比例，表大小等因素决定是否使用索引
  当SQL的EXPLAIN出现possible keys时，则代表MySQL可能使用索引，可通过FORCE INDEX(idx)来强制使用索引
  
• LIKE百分号写最右
  等同于最左前缀原则，即字符串也是按照从左往右进行查询，若第一个字母就是百分号，则索引直接失效
  
• VAR引号不能丢
  若对varchar类型的字段进行索引查找时，不写引号，那么代表需要mysql进行一次类型转换，则相当于进行了函数计算，进而让索引失效
  
• 范围查询优化
  

三、SQL优化

1. 避免SELECT星号

• 增加查询分析器解析成本
  
• 增加字段容易与resultMap配置不一致
  
• 无用字段增加网络消耗，尤其text字段
  
• 应用层增加无用字段的反序列化解析
  
• SELECT * 查询不走覆盖索引，造成大量回表查询
  

2. 小表驱动大表

小表驱动大表指用数据量小、索引完备的表，使用其索引和条件对大表进行数据筛选，从减少数据计算量，提高查询效率。
3. 连接查询代替子查询

• 子查询需要执行两次数据库查询，一次外部查询，一次嵌套子查询。因此使用连接查询可以减少数据库查询次数，提高查询效率。
  
• 连接查询可以更好利用索引，提高查询性能。子查询通常会使用临时表或内存表，而连接查询可以直接用表上的索引。
  
• 子查询通常需要扫描整个表，而连接查询可以利用索引加速查询效率
  

4. 提升group by效率

创建group by的字段的索引
5. 批量插入

有多条插入语句时，可通过SQL进行批量插入，减少数据库IO次数
INSERT INTO user(id,name,age) VALUES(1,'张三',18),(1,'李四',20),(1,'王五',25),(1,'赵六',30);
6. 使用limit

• 提高查询效率：一次查询数据量过多，会占用大量系统资源
  
• 避免过度提取数据：对应用层来说，过多的数据量也可能造成应用层的内存和CPU的异常
  
• 优化分页查询：查询需要的数据行，减少资源浪费
  
• 简化查询结果
  
• limit后不宜用过大的数字
  
• 比如limit 10000,,10MySQL使用limit查询的逻辑会先从第一条遍历到第10000条，然后再往后偏移10条
  

7. union all代替union

• union all：获取所有数据不去重，包含重复数据
  
• union：获取所有数据且去重，不包含重复数据
  
• 
  

如果没有去重需求，直接优化为union，如果有去重需求，尽量再查询条件去重
8. 尽量少join关联表

• 查询效率下降：多表join对比时间变长
  
• 系统负载增加：join操作需要大量计算，导致系统负载增加
  
• 维护难度加大
  

四、MySQl常见面试题

1. 数据库三大范式

• 第一范式(1NF)：属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)
  
• 第二范式(2NF)：满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)
  
• 第三范式(3NF)：满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）
  

2. DML 语句和 DDL 语句区别

• DML 是数据库操作语言（Data Manipulation Language）的缩写，是指对数据库中表记录的操作，主要包括表记录的插入、更新、删除和查询。
  
• DDL （Data Definition Language）是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。
  

3. drop、delete、truncate 区别

• 用法区别：
  
  
  
  • drop(丢弃数据): drop table 表名 ，直接将表结构都删除掉，在删除表的时候使用。
    
  • truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。
    
  • delete（删除数据） : delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和truncate table 表名作用类似。
    
  
  
• 数据库语言区别：truncate和drop是DDL语言，delete是DML语言
  
• 执行速度区别：
  
  
  
  • delete命令执行的时候会产生数据库的binlog日志，而日志记录是需要消耗时间的，但是也有个好处方便数据回滚恢复。
    
  • truncate命令执行的时候不会产生数据库日志，因此比delete要快。除此之外，还会把表的自增值重置和索引恢复到初始大小等。
    
  • drop命令会把表占用的空间全部释放掉。
    
  
  

4. MyISAM 和 InnoDB 的区别

区别MyISAMInnoDB最小锁粒度表级锁行级锁外键不支持支持索引B+树非聚集索引B+树聚集索引主键可以没有若没有主键会自动生成隐藏主键Row_id事务不支持支持异常崩溃恢复不支持可根据redo日志在重启后恢复5. 什么是redo日志

InnoDB独有，用于MySQL崩溃后重启时的数据恢复
更新表数据的时候，如果发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。
刷盘时机

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数（默认1），它支持三种策略

• 设置为0的时候，表示每次事务提交时不进行刷盘操作，只是保留在 redo log buffer中，mysql 崩溃会丢失1s的数据；
  
• 设置为1的时候，表示每次事务提交时都将进行刷盘操作（默认值），持久化到磁盘；
  
• 设置为2的时候，表示每次事务提交时都只把redo log buffer内容写入page cache（OS Buffer），OS宕机会丢失1s的数据，因为未进行持久化；
  InnoDB 存储引擎有一个后台线程，每隔1秒，就会把 redo log buffer 中的内容写到文件系统缓存（OS Buffer），然后调用 fsync 刷盘。
  redo log buffer占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。
  

6. 什么是binlog日志

binlog 是归档日志，属于 Server 层的日志，是一个二进制格式的文件，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”。
不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。它的主要作用就是数据备份、主从复制。
binlog会记录所有涉及更新数据的逻辑操作，属于逻辑日志，并且是顺序写。
binglog格式

binlog 日志有三种格式，可以通过binlog_format参数指定。

• statement ：记录的内容是SQL语句原文，存在数据一致性问题；
  
• row：记录包含操作的具体数据，能保证同步数据的一致性；
  
• mixed：记录的内容是前两者的混合，MySQL会判断这条SQL语句是否可能引起数据不一致：如果是，就用row格式，否则就用statement格式。
  

写入机制

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。
因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。
binlog 也提供了 sync_binlog 参数来控制写入 page cache 和磁盘的时机：

• 0：每次提交事务都只写入到文件系统的 page cache，由系统自行判断什么时候执行fsync，机器宕机，page cache里面的 binlog 会丢失。
  
• 1：每次提交事务都会执行fsync，就如同 redo log 日志刷盘流程 一样。
  
• N(N>1)：每次提交事务都写入到文件系统的 page cache，但累积N个事务后才fsync。如果机器宕机，会丢失最近N个事务的binlog日志。
  

7. binlog和redolog区别

• redolog 是 Innodb 独有的日志，而 binlog 是 server 层的，所有的存储引擎都有使用到；
  
• redolog 记录了具体的数值，对某个页做了什么修改，binlog 记录的操作内容；
  
• binlog 大小达到上限或者 flush log 会生成一个新的文件，而 redolog 有固定大小只能循环利用；
  
• binlog 日志没有 crash-safe 的能力，只能用于归档，而 redo log 有 crash-safe 能力；
  
• redo log 在事务执行过程中可以不断写入（刷盘设置为1，后台线程1s执行一次或者 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候），而 binlog 只有在提交事务时才写入文件缓存系统；
  

8. 什么是undo日志

MySQL中所有的事务中进行的修改都记录到undo日志，若需要进行回滚，则通过undo日志进行回滚
9. 什么是relay日志

主从同步使用的中继日志，用于存储从master节点同步过来的binlog日志内容
10. 什么是Hash索引

Hash索引即哈希结构索引，数据结构和Java的HashMap差不多，是数组加链表的形式
InnoDB不支持显示创建Hash索引，只支持自适应Hash索引。仅memory引擎支持hash索引，memory引擎运行于内存中。
• 优点：等值比较效率高
• 缺点：不支持范围查询，也不支持排序，因为索引列无序
11. B树和B+树的区别

• B树的所有节点都会存储数据，B+树仅把数据存在叶子节点，而非叶子节点可以存储更多的阶数指针，并且节点不存数据，磁盘IO也会更快
  
• B+树的范围查找，排序查找，分组查找以及去重查找更简单
  
• 数据记录之间通过链表连接，可以很方便的在数据查询后进行升序或降序操作
  

12. 什么是最左前缀原则

最左前缀原则即在有联合索引的查询中，where条件必须按照联合索引字段的顺序依次查询，才能有效利用索引
13. 什么是聚簇索引和非聚簇索引

聚簇索引即B+树种存储索引和数据
非聚簇索引在B+树种存储索引，而完整数据存在其他地方
MyISAM使用非聚簇索引，InnoDB的主键索引使用聚簇索引，非主键索引使用非聚簇索引
14. 索引失效有哪些情况

• 没有使用索引列作为where的查询条件
  
• 对索引列进行函数操作（字符串或日期操作）
  
• 对索引列进行类型转换
  
• LIKE查询字符串以通配符开头
  
• or条件查询
  
• 查询条件涉及大量数据
  

15. 什么是索引下推

索引条件下推，是防止明明可以在存储引擎层判断，但还回表查询后拿到server层判断，减少回表次数。它是MySQL的内部优化，将判断where条件从server层下推到存储引擎层，也就是说存储引擎层也会判断查询其他条件

1. #ON表示已开启
2. show variables like 'log_bin_trust%';

复制代码

16. mysql索引类型normal，unique，full text的区别是什么？

• normal：表示普通索引
  
• unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique
  
• full text: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
  

17. 事务的特性

事务的特性：
A：原子性（Atomicity），原子性是指事务是一个不可分割的工作单位，事务中的操作，要么都发生，要么都不发生。
C：一致性（Consistency），在一个事务中，事务前后数据的完整性必须保持一致。
I：隔离性（Isolation），存在于多个事务中，事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。
D：持久性（Durability），持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
18. 事务的隔离级别

为了解决以上隔离性引发的并发问题，数据库提供了事务的隔离机制。

• read uncommitted（读未提交）: 一个事务还没提交时，它做的变更就能被别的事务看到，读取尚未提交的数据，哪个问题都不能解决；
  
• read committed（读已提交）：一个事务提交之后，它做的变更才会被其他事务看到，读取已经提交的数据，可以解决脏读（oracle默认的）；
  
• repeatable read（可重复读）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的，可以解决脏读和不可重复读 （mysql默认的）；
  
• serializable（串行化）：顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。可以解决脏读、不可重复读和虚读---相当于锁表。
  虽然 serializable 级别可以解决所有的数据库并发问题，但是它会在读取的每一行数据上都加锁，这就可能导致大量的超时和锁竞争问题，从而导致效率下降。所以我们在实际应用中也很少使用 serializable，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。
  

19. 为什么不要使用长事务

• 并发情况下，数据库连接池容易被撑爆
  
• 容易造成大量的阻塞和锁超时，长事务还占用锁资源，也可能拖垮整个库
  
• 执行时间长，容易造成主从延迟
  
• 回滚所需要的时间比较长，事务越长整个时间段内的事务也就越多
  
• undolog 日志越来越大，长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。
  

20. 查询方法是否需要开启事务

若采用隔离级别是可重复读，如不开启事务，多次sql的查询的时间维度不同，如开启事务则能保证多条sql的结果集处于同一个时间维度。开启不开启具体看业务场景。
21. MySQL中什么情况适合建立索引，什么情况不适合建立索引

• 频繁查询的字段适合建立索引
  
• 更新频繁的字段不适合建立索引
  
• 不进行查询的字段不适合索引
  
• 字段唯一性太差不适合索引
  

22.  为什么不建议使用Select *

• 只查询必要的字段，可以减少字段序列化，减少查询对象大小
  
• 如果查询条件是索引列，且只需要查询索引列，只select需要的字段可以避免索引回表查询
  

23. 说说MySQL InnoDB事务的原理

InnoDB中事务靠ACID特性来保证

• 原子性（Atomicity）：当前事务操作要么同时成功，要么同时失败。原子性由undo log日志来保证
  
• 一致性（Consistency）：使用事务的最终目的，由其他三个特性保证
  
• 隔离性（Isolation）：事务并发执行时，内部操作互不干扰。InnoDB中隔离性由各种锁和MVCC保证
  
• 持久性（Durability）：一旦提交事务，它对数据的改变是永久性的。持久性由redo log日志来保证
  

并通过四种读未提交、读已提交、可重复读和串行化四种事务隔离级别，来分别解决脏读、幻读、不可重复读问题。四种隔离级别从前往后安全级别越来越高，性能越来越低。
一般情况下，隔离级别采用读已提交（Oracle默认）或可重复读（MySQL默认），具体要根据业务使用场景判断。
关于隔离级别详细信息参考上面原文
24. 一条更新SQL的执行过程

• 加载对应id的数据的整页数据到缓存池
  
• 旧值写入undo log，便于回滚
  
• 更新内存数据
  
• 写redo log到redo buffer
  
• redo log顺序写入磁盘，准备提交事务（prepare阶段）
  
• 准备提交事务，binlog写入磁盘
  
• 写入commit标记到redo log文件，提交事务完成；该标记为了保证事务提交后redo log和bin log数据一致
  
• 系统空闲时，随机写入磁盘，以page页为单位写入
  
• 数据更新完成
  

更多技术干货，欢迎关注我！

来源:https://www.cnblogs.com/hjcenry/p/17936889
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！