面试专题之——Mysql

Mysql执行SQL的流程是怎么样的？

1、客户端提交SQL

2、查询缓存，命中则直接返回（key为这条SQL语句），未命中则进行查表操作

3、SQL解析器生成一个解析树

4、预处理器处理解析树，并重新生成一个解析树，并改写SQL

5、查询优化器根据解析树生成SQL执行计划

6、执行引擎根据执行计划调用存储引擎的API接口进行查询数据，存储引擎常见的有InnoDB、MyISAM等，这是一个可插拔的设计，可以自定义存储引擎

7、最后返回给客户端并进行缓存

索引

索引的实质是什么？

索引的实质就是一种数据结构，这个数据结构是树形的

索引的优势和劣势有哪些？

优势：

​ 1、提高查询效率，降低数据库的IO成本

​ 2、因为索引本质是树形结构，所以可以降低数据排序成本，降低CPU消耗

劣势：

​ 1、创建的索引实质上也是一张表，也会占用空间

​ 2、因为增删改时需要维护索引，所以进行这些操作时也会影响效率，所以增删改操作多的表不建议建立索引

常见的索引类型有哪些？

BTREE：最常见的索引类型

HASH：只有Memory引擎支持，使用场景简单

R-tree：空间索引，它是MyISAM引擎的一个特殊索引类型，使用少

Full-text：全文索引，他也是MyISAM引起的一个特殊索引类型，不过全文索引一般使用ElasticSearch

在Mysql索引中用什么数据结构建立索引？为什么？

使用优化后的B+TREE（B+树），而不是经典的B+树

原因：

b-Tree的定义（其中M自定义，例如3就是三阶树）：
1、根节点至少有两个孩子
2、树每个节点最多有M个孩子（M>=2）
3、除了根节点和叶子节点，其他节点至少有M / 2个孩子，有小数取天花板，例如3 / 2 = 1.5 那么取2
4、所有叶子节点都在同一层，也就是所有末尾节点的辈分是一样的
5、同节点中必须是升序排列，左边的比右边的小
6、任何节点的关键字都比它指向的子节点少一个，比如M是3，那么一个节点的关键字必须是2个
7、任何非叶子节点的第一个关键字比它的第一个子节点内关键字都大，最后一个关键字比最后一个子节点内关键字都小，其余子节点的关键字都在它们相邻父节点之间

b+-Tree数据结构的定义：它是b-Tree的变异，在b-Tree的基础上它有如下特征，它可以比b-Tree更矮
1、非叶子节点中，关键字和子节点数量是一样的
2、非叶子节点中，所有关键字所对应的子节点中的所有关键字，都比它大（就是子节点所有关键字都比它大），但又都比它后面那个关键字小
3、非叶子节点只用来做索引，数据都保存在叶子节点中
4、所有叶子节点都有一个指针指向下一个叶子节点，这样能做横向的数据查询，而不用再一次从根节点出发了

b+树相对其他数据结构更加适合做索引，原因如下：
1、B+树因为所有数据都放在叶子节点，非叶子节点只存放索引指针，这样一次读取的数据更多，对磁盘读写较少
2、查询效率更加稳定，因为非叶子节点并不指向文件内容，而是叶子节指向文件内容，所以所有查询都要查到叶子节点
3、更有利于对数据库的扫描、范围查询、排序，因为它的所有叶子节点都存放了下一个叶子节点的指针

索引的分类有哪些？

单列索引：只包含单个列的索引，例如单个name字段建立索引，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但是可以为空。主键索引就是特殊的唯一索引，在建表的时候Mysql就会自动创建

复合索引：一个索引可以包含多个列

说说密集索引和稀疏索引

密集索引：叶子节点保存了索引值，还保存了该条记录的所有信息，也就是整条记录保存
稀疏索引：叶子节点只保存了某列的索引

说说聚簇和非聚簇

说到聚簇和非聚簇就涉及到密集索引和稀疏索引

聚簇：在innoDB引擎中，主键为密集索引，没有主键就用第一个非空列，再没有就自己创建。在我们查询一个记录时，会先从稀疏索引中查询出它的主键值，然后根据主键值查询密集索引，查出整条记录，这就是聚簇

非聚簇：在MyISAM引擎中，索引和表数据是分开存储的，它只有稀疏索引，叶子节点都是一个地址指向真正的表数据，所以它不需要像聚簇一样查询两遍

建立索引有什么原则？

1、查询频次高，且数据量大

2、where子句中使用较多的字段建立索引，并且建立的顺序很重要，因为有一个最左匹配原则

3、尽量使用唯一索引，因为这样区分度较高，查询效率也更高

4、在查询多增删改少的表建立索引，因为增删改操作需要维护索引，所以会影响效率

InnoDB和MyISAM的区别

事务：InnoDB支持，MyISAM不支持

锁机制：InnoDB行级锁、表级锁，MyISAM表级锁

集群索引：InnoDB支持，MyISAM不支持

外键：InnoDB支持，MyISAM不支持

MyISAM会自动加共享锁（读锁）和排他锁（写锁）

InnoDB会自动加排他锁，但是Select不会加锁，可以lock in share mode加共享锁。在使用到索引的时候用的是行级锁，在不用到索引的时候用的是表级锁

什么是视图？

简单来说就是一个虚拟的表，在数据库中并不存在，而是通过Select语句查询后返回的结构集合构建出来的一个虚拟表

什么是存储过程&存储函数？

简单来说就是在数据库中预先编译好的一段SQL语句集合，它可以简化开发人员的很多工作。相当于把业务逻辑封装到Mysql中，而不是在Service层去写业务。

什么是触发器？

就是在增删改操作时，再它们之前或者之后做的事，有点类似与AOP的思想。一般可以用来做数据的完整性效验、日志记录等

如何定位且优化慢查询？

如何定位慢查询SQL？

1、查看慢查询日志

show variables like ‘%quer%’
show status like ‘%%slow_queries’

2、使用show processlist命令查询实时SQL执行情况

如何分析慢SQL？

使用explain 后接SQL语句分析慢SQL，或者show profile命令，这里主要用explain

查询后的结果列如下：

• id：执行的顺序，值越大越先执行，相同则顺序执行

• select_type：当前SQL语句的查询类型

• table：查询SQL语句时用到了哪些表

• type：访问类型，可以用于区分当前SQL语句的性能等级

  性能排序：system > const > eq_ref > ref > range > index > all

  一般情况下至少要达到range级别，最好达到ref

  system：整个表只有一行记录，一般不会出现

  const：通过索引一次就找到了，一般出现在主键或唯一索引中的扫描

  eq_ref：使用唯一索引进行关联查询，并且结果只有一条，一般出现在主键或唯一索引扫描

  ref：使用非唯一索引进行关联查询，结果可以有多条

  range：一般出现在范围查询

  index：遍历了索引树

  all：遍历了整个表possible_keys：可能用到的索引

• key：实际使用的索引

• key_len：索引字段最大的可能长度，长度越短越好（是索引的长度，而不是字段的长度）

• rows：扫描行的数量，越小越好

• extra：其他执行信息，using filesort文件排序效率低，uning temporary使用了临时表导致效率低 通常使用了order by 或 group by，using index使用了索引，效率高，尽量要优化成这样

如何解决慢SQL以及进行SQL优化？

避免索引失效

1、直接使用全值匹配：在条件查询中，对索引中的所有列都指定具体的值，这样索引生效，并且效率最高

2、必须符合最左匹配原则：我们在条件查询时的条件顺序必须与组合索引中字段的顺序保持一致，并且不能中断，而且从左到右匹配到多少个字段就使用多少个字段做索引

例如：组合索引有三个字段，依次为A, B, C，条件查询字段顺序如下：

B, C：无法使用索引

A, C：只能匹配A字段的索引

A,B：能匹配到A, B两个字段的索引

联合索引最左匹配原则的成因：
因为联合索引创建时使用最左边那个字段生成B+树，叶子节点储存了联合索引这几个字段的所有值， 只有根据最左边这个值才能找到相应的叶子节点

3、索引列字段必须放在范围查询左边：范围查询右边的字段不能使用索引

4、不要在索引列上进行运算操作

5、字符串必须加单引号

6、查询的字段尽量覆盖索引字段，减少使用select *：这样可以尽量减少回表查询，也就是聚簇索引中二次查找

7、在sql语句中用union替代or：因为or会导致索引失效

7、%开头的like模糊查询会导致索引失效

如何解决？

答：查询的字段去覆盖索引字段，如果索引了的字段不满足要求，那么可以只查询出id，然后嵌套进另一个select语句中使用in拼接，当然用其他方式把它当作一个临时表也行

例如：

select name, phone from user where id in (

​ select id from user where name like ‘%张%’

)

虽然是走了两次索引，但总比全表扫描块

8、不要写is null与is not null，因为可能不会命中索引

9、不要写not in

10、尽量使用组合索引而不使用单值索引，尽量也不要建立太多索引，因为索引会影响增删改的效率

如何进行SQL优化

1、进行大量插入操作时，暂时把主键取消，插入完再建立主键，因为如果有主键约束则会在插入每条数据时进行检查唯一性

2、进行大量insert into操作时，不要一次插入一个数据，而是直接批量插入，values后接多个（）即可，用逗号分开

3、order by也要准寻最左匹配原则，并且尽量都是升序或降序

4、在使用group by分组时，因为它是先排序后分组的，在不需要排序的时候使用order by null禁止排序可以提升性能

5、使用limit进行海量数据分页查询时可以按照如下方式优化：

先覆盖索引分页查询id，然后通过关联查询或join查询所有需要的字段也就是回表查询，类似于上面解决模糊查询的方式

6、在特定情况下使用force index强制使用索引

7、使用join代替子查询

8、使用union联合查询替代临时表

锁相关

Mysql数据库InnoDB与MyISAM锁方面的区别：
InnoDB支持行级锁、表级锁
MyISAM只支持表级锁

共享锁（读锁）：其他事务可以读，但不能写。
排他锁（写锁） ：其他事务不能读取，也不能写。

乐观锁与悲观锁：
乐观锁：乐观的认为在拿数据的时候别人不会修改，在应用上做并发控制，乐观锁实现的常见方式：加version版本号
例如：事务1拿到版本号为0，同时事务2也拿到了为0，进行修改操作时版本号加1，同时where条件判断版本号是否为0，为0代表没人操作，修改成功，不为0代表有人操作了，修改失败。因为select默认不加锁，所以可以同时获取相同的值，因为修改操作有排他锁，所以不同事务中只会运行一个再运行下一个

悲观锁：认为别人一定会改，在数据库层面做并发控制，实现方式：用for update给select加上排他锁

在更新操作不多的时候用乐观锁，在更新很多的时候用悲观锁

什么是MVCC？

MVCC就是多版本并发控制，它做到了在即使有读写冲突时，也能做到不加锁进行非阻塞的并发读

在RR级别下是如何基于MVCC解决不可重复读问题的？

当前读：即加了共享锁与排他锁的sql语句，使用当前读因为需要加锁，这样会导致性能下降

快照读（非阻塞读）：
在可重复读事务隔离级别下，我们知道它解决了在一个事务连续读取过程中，另一个事务进行update操作，第一个事务两次读取的数据不一样的问题，解决问题的方法可以是锁行，就是给第一个事务select语句加上lock in share mode加上共享锁，让查询的时候不让update操作，但是性能会有所下降。Mysql这里就加上了一个快照读的机制，select默认不加共享锁，当第一次查询时生成一个快照，后面再次读取都是使用快照，这样另一个事务进行update操作也不会影响第一个事务的再次读取，解决了读两次数据不一致的问题，使用快照与使用共享锁相比，性能上提高了很多

Mysql就是利用快照读来解决不可重复读问题的，而快照读就是基于MVCC理念形成的

快照读在RC与RR级别下的区别有哪些？

RR级别：在一个事务中，第一次快照读会创建一个Read View，后续所有的快照读都使用这个Read View

RC级别：在一个事务中，每次快照读都会创建一个Read View，后续所有的快照读都使用这个Read View

MVCC的实现原理

它主要依赖了：三个隐式字段、undo log、Read View

三个隐式字段为：

• DB_ROW_ID：隐藏主键，如果该表没有主键，InnoDB则会自动生成一个聚簇索引
• DB_TRX_ID：最近修改的事务ID，每次修改都会记录当前事务ID
• DB_ROLL_PTR：回滚指针，记录上一个版本

在进行数据修改时，InnoDB会把旧的数据存放进undo log中，并且隐式字段中最近修改的事务ID也存放旧的值，在进行快照读时，会创建一个Read View，这个Read View遵循一个可见性算法，它的作用是取出最新记录中的当前事务ID，然后进行可见性对比，最后找出undo log中特定事务ID的版本

InnoDB事务提交的流程

例如：有数据A，它的值为1，要修改为2

1、事务开始

2、记录A=1到undo log中

3、修改A=2

4、记录A=2到redo log中

5、将undo log写入磁盘（为了减少IO，实际上它先写入到redo log中，然后一起写入磁盘）

6、将redo log写入磁盘

7、提交事务

如何避免脏数据？

答：数据提交后会按照特定的频率将内存中的数据刷入磁盘

为什么不直接写入磁盘而是写入redo log中？

答：因为redo log是在磁盘中开辟连续的空间，是顺序写入，速度快，而直接写入磁盘是随机写入，速度慢

redo log中有未提交的事务如何完成数据恢复？

答：重做之前所有事务，然后通过undo log回滚那些没有提交的事务

InnoDB在可重复读隔离级别下如何解决幻读的？

InnoDB引入了gap锁（间隙锁）与next-key lock机制去解决幻读的问题

gap锁：

用于在可重复读隔离级别下解决幻读问题，实质上是对索引前后的间隙上锁

例如执行：select * from test where n = 105 for update；
会对 n 在 (102,105), (105, 107)的数据上锁

next-key lock机制：

它是一种机制，他会锁定多个索引区间，也就是锁定多个gap锁

例如执行：select * from test where n > 105 for update；
会对 n 在(105, 107], (107, 109], (109, +♾️]的数据上锁

数据：

+—-+——+

| id | n |

+—-+——+

| 1 | 1 |

| 3 | 102 |

| 5 | 105 |

| 7 | 107 |

| 9 | 109 |

+—-+——+