【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树,它做了一下优化
    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引擎运行于内存中。

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

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. 数据更新完成

更多技术干货,欢迎关注我!

热门相关:一念路向北(刘恺威主演电视剧原著)   我有一张沾沾卡   圣人门徒   诛天至极   美食供应商