开发技术分享

large picture of the cover

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

【MySQL】一文看懂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再次查询会发现存在修改未生效的数据，如同幻觉
幻读
不可重复读：多次读取同一数据得到不同结果
不可重复读

区别：

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

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），它支持三种策略

  1. 设置为0的时候，表示每次事务提交时不进行刷盘操作，只是保留在 redo log buffer中，mysql 崩溃会丢失1s的数据；
  2. 设置为1的时候，表示每次事务提交时都将进行刷盘操作（默认值），持久化到磁盘；
  3. 设置为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参数指定。

  1. statement ：记录的内容是SQL语句原文，存在数据一致性问题；
  2. row：记录包含操作的具体数据，能保证同步数据的一致性；
  3. 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树，它做了一下优化
    1. B+树把所有数据都存在了叶子结点，非叶子节点不存数据。减少了每次判断节点时的拿取节点的效率
    2. B+树的叶子节点之间增加了双向指针。提高了范围查找的效率

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

B树与B+树的区别

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

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

2. Hash索引

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

Hash索引

? 优点：等值比较效率高
? 缺点：不支持范围查询，也不支持排序，因为索引列无序

3. 聚集索引和非聚集索引

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

-rw-r----- 1 mysql mysql  114688 Oct 31 11:30 test_innodb.ibd     
-rw-r----- 1 mysql mysql       0 Oct 31 11:31 test_myisam.MYD     
-rw-r----- 1 mysql mysql    1024 Oct 31 11:31 test_myisam.MYI   
-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主键去查找数据（回表），这也是非聚集索引

SELECT * FROM `user` WHERE age = 17;

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

5. 覆盖索引

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

SELECT id FROM `user` WHERE age = 17;

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

6. 索引下推

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

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

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

7. 单列索引和联合索引

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

联合索引

最左前缀原则

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

SELECT * FROM `user` WHERE age=15;

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

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

联合索引优势

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

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

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

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

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

9. 索引优化

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

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

  • 索引列不要函数计算
    如果在索引字段上进行了函数计算，则索引失效，进行全表扫描，比如以下场景
SELECT * FROM `user` WHERE left(name,2) = '张三';
  • 范围后面的索引全失效
    在联合索引的情况下，范围查询会使后面字段无序，造成部分索引失效，比如以下场景
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 的区别

区别 MyISAM InnoDB
最小锁粒度 表级锁 行级锁
外键 不支持 支持
索引 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），它支持三种策略

  1. 设置为0的时候，表示每次事务提交时不进行刷盘操作，只是保留在 redo log buffer中，mysql 崩溃会丢失1s的数据；
  2. 设置为1的时候，表示每次事务提交时都将进行刷盘操作（默认值），持久化到磁盘；
  3. 设置为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参数指定。

  1. statement ：记录的内容是SQL语句原文，存在数据一致性问题；
  2. row：记录包含操作的具体数据，能保证同步数据的一致性；
  3. 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+树的区别

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

12. 什么是最左前缀原则

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

13. 什么是聚簇索引和非聚簇索引

聚簇索引即B+树种存储索引和数据
非聚簇索引在B+树种存储索引，而完整数据存在其他地方
MyISAM使用非聚簇索引，InnoDB的主键索引使用聚簇索引，非主键索引使用非聚簇索引

14. 索引失效有哪些情况

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

15. 什么是索引下推

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

#ON表示已开启
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. 事务的隔离级别

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

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

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

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

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

若采用隔离级别是可重复读，如不开启事务，多次sql的查询的时间维度不同，如开启事务则能保证多条sql的结果集处于同一个时间维度。开启不开启具体看业务场景。

21. MySQL中什么情况适合建立索引，什么情况不适合建立索引

  1. 频繁查询的字段适合建立索引
  2. 更新频繁的字段不适合建立索引
  3. 不进行查询的字段不适合索引
  4. 字段唯一性太差不适合索引

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

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

23. 说说MySQL InnoDB事务的原理

InnoDB中事务靠ACID特性来保证

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

并通过四种读未提交、读已提交、可重复读和串行化四种事务隔离级别，来分别解决脏读、幻读、不可重复读问题。四种隔离级别从前往后安全级别越来越高，性能越来越低。
一般情况下，隔离级别采用读已提交（Oracle默认）或可重复读（MySQL默认），具体要根据业务使用场景判断。

关于隔离级别详细信息参考上面原文

24. 一条更新SQL的执行过程

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

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

qrcode

site logo

Stop The World

© 2024 开发技术分享