Mysql常见面试题

1.MySQL是如何执行一条SQL的？

客户端先与mysql服务器建立连接，有个连接池，客户端发送sql给服务器的sql接口，然后回对sql语句进行解析，变成一个合法的语法树，然后会对sql语句进行一个优化，选择更优的执行方式，然后可以选择开启缓存（默认是关闭的），等下次查询可以直接从缓存查找，最后去存储引擎执行，这是一个可插拔的存储引擎，可以增加，也可以去掉一些没用的，执行最优的sql，然后去磁盘文件系统加载数据，然后返回客户端。

2.MySQL数据库InnoDB存储引擎是如何工作的？ 

 比如一条更新sql语句，由执行器去执行，发送到存储引擎，步骤如下：

1.将需要更新的记录从磁盘文件加载到缓冲池

2.缓冲池会写入旧值到undo log日志文件磁盘上，便于回滚

3.然后执行器去更新缓冲池里的数据

4.执行器写redo日志到存储引擎（redo日志存入新值，可以在mysql宕机时候恢复数据）

5.然后准备提交事务，将redo日志写入磁盘

6.执行器准备提交事务，binlog日志写入磁盘（binlog日志，在主从复制的时候复制数据）

7.在向磁盘的redo日志写入binlog文件与位置写入commit标记，事务提交完成

8.最后通过IO线程更新磁盘数据

3.如果要对数据库进行优化，该怎么优化？ 

1、服务器硬件：cpu、内存、磁盘io、网卡流量；

2、服务器的操作系统：Linux的配置参数不同性能不同；

3、数据库存储引擎的选择：根据需要选择不同的存储引擎，MyISAM 不支持事务，表级锁；InnoDB 事务级存储引擎，完美支持行级锁和事务ACID特性；

4、数据库自身配置参数：MySQL有上百项的数据库配置参数；（my.cnf）

5、数据库表结构的设计和SQL语句的执行效率：慢查询是性能问题的罪魁祸首，不合理的数据库表结构设计和不合理的索引是影响数据库查询性能的重要因素；

6、数据库架构：高并发下读写分离、分库分表、多级缓存、搜索引擎； 

4.MySQL如何定位慢查询？ 

定位慢查询主要有如下几种方式：

1、业务驱动，主要是业务及运营人员或者用户反馈给我们的，他们在使用系统的过程中发现某些功能很慢，这种方式一般是项目上线后出现的，开发人员会比较被动，建议不要采用这种方式；

2、测试驱动，系统上线前通过测试人员的反馈，了解到哪些功能比较慢；

3、系统跟踪监控，比如Prometheus、SkyWalking；

4、慢查询日志，通过开启MySQL慢查询日志监控慢查询sql并及时进行优化；

show variables like '%query%';

slow_query_log = ON   （开启慢查询日志）

long_query_time = 2       （查询时间超过2s计入慢查询日志）

log_queries_not_using_indexes = ON     （查询没有使用索引计入慢查询日志）

5.对MySQL索引了解吗？

索引是什么？

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的一种数据结构，而且是排好序的数据结构，使用索引可快速访问数据库表中的特定信息，索引存储在磁盘文件里； 索引就像一本书的目录，根据目录可以快速定位要找的内容所在页码； 索引能极大的减少存储引擎需要扫描的数据量，从而提高数据的检索速度； 

索引底层数据结构？

MySQL索引主要有两种结构：B+Tree索引和Hash索引； 如果没有特别说明，我们平常所说的索引一般都是指B+Tree结构的索引；

索引的类型？

1、Normal 普通索引 表示普通索引，大多数情况下都可以使用；

2、Unique 唯一索引 表示唯一不允许重复的索引，例如注册手机号用作索引时，可设置为unique唯一索引； Primary Key是自动拥有Unique约束的唯一索引；

3、Full Text 全文索引 在检索长文本的时候效果较好，比如搜索一篇文章；

4、SPATIAL空间索引 空间索引是对地理空间位置数据类型的字段建立的索引；

6.MySQL索引底层是什么数据结构？为什么选择这种结构？ 

①二叉查找树（Binary Search Tree）

特点：

1、每个节点最多只能有两个子节点；

2、左子树的键值小于根的键值（hash值），右子树的键值大于等于根的键值；

3、对二叉查找树的节点进行查找时，深度为1的节点查找次数为1，深度为2节点查找次数为2，深度为3的节点查找次数为3，深度为n的节点查找次数为n，因此查找时间复杂度依赖于节点深度，如果节点很深，则查找效率降低；

4、极端情况下，如果键值是顺序增大的，则二叉查找树“退化”为像链表的结构；

MySQL索引结构为什么不使用二叉查找树？

1、二叉查找树是一种高度不平衡的树形数据结构，它的查找、插入和删除效率与树的高度密切相关，当数据量较大时，二叉查找树的高度会增加，导致查找效率降低，因为查找数据时 ，每个节点都需要进行一次IO读取，那么高度越高，IO次数越多，效率越低；

2、二叉查找树每个节点只存储一个数据，存储的数据项较少，会导致磁盘块的利用率低，导致性能下降，即不能发挥磁盘预读功能；

②平衡二叉查找树（AVL Tree (Balanced binary search tree)）

        为了提高二叉查找树的的查找效率，又提出一种新的数据结构：平衡二叉查找树（也叫AVL树：1962 年G. M. Adelson-Velsky 和 E. M. Landis两个人提出来的）； 平衡二叉查找树（AVL树）在满足二叉查找树的条件下，还需满足任何节点的两个子树的高度最大差为1，所以它呈现出是一种左右平衡的状态；（height <=1） 强平衡， 自平衡

上图中左边的是AVL树，它的任何节点的两个子树的高度差<=1； 右边的不是AVL树，其根节点的左子树高度为3，而右子树高度为1，两个子树的高度差为2； 当我们向平衡二叉树（AVL Tree）插入新的节点（或者删除新的节点），有可能打破它原有的平衡，那么它会通过旋转使其恢复平衡；

MySQL索引结构为什么不选择平衡二叉查找树（AVL Tree）？

1、维护平衡过程的成本代价很高，因为每次删除或者增加一个节点，需要一次或者多次的左旋或右旋去维护“平衡”状态；

2、如果节点很多的话，那么这个AVL树的高度依然还是会很高的，那么查询效率还是会很低；

3、每个节点只存储一个数据，节点存储内容太少，没有很好地利用磁盘IO的预读能力，幸幸苦苦做了一次IO操作，却只加载了一个关键字数据，另外树的高度很高，恰好又查询位于叶子节点的数据，那么要做多次IO；

③红黑树（Red-Black Tree）

红黑树的特点：（约定）

（1）每个节点要么是红的要么是黑的；

（2）根节点和叶节点（叶节点即指树尾端NIL节点）都是黑的；

（3）如果一个结点是红的，那么它的两个子节点都是黑的（不可能连续两个红色节点，但是可以连续两个黑色节点）；

（4）任意节点到叶节点的链路中都包含相同数目的黑节点；

MySQL索引结构为什么不使用红黑树？

        红黑树依然是二叉树，虽然旋转次数比AVL树要少，但如果节点很多的话，那么这个红黑树的高度依然还是会很高的，那么查询效率还是会很低，所以还是没有有效的减少查找过程中磁盘I/O的存取次数； 每个节点只存储一个数据，不能填满一个页上的所有内容，没有很好地利用磁盘IO的预读能力，每做一次IO操作，却只加载了一个关键字数据，另外树的高度很高，恰好又查询位于叶子节点的数据，那么要做多次IO；

④多路平衡查找树（Balance Tree  /  B Tree）

        全称Balance-tree(多路平衡查找树)，多路意思是相对于二叉查找树而言，二叉查找树查找时只有两条路，而B-tree有多条路，即B Tree的父节点有多个子节点，而平衡的意思是左边和右边分布均匀； 每个节点最大可以存放（路数-1）个关键字信息； 每个节点最大可以有（路数）个分支；

⑤改进版多路平衡查找树（Balance+ Tree  /  B+ Tree） 

相对于B Tree，根节点不存放数据，只有叶节点存放数据，这样根节点可以存放跟多键值，可以更矮更胖，减少IO操作，提升查找效率，并且叶节点之间会形成一个双向链表做连接。

MySQL索引结构为什么使用B+Tree？

1、Ｂ＋树中间非叶子节点没有数据，只有索引，而Ｂ树每个结点中的每个关键字都有数据，这样使得同样大小的磁盘页可以容纳更多节点元素，在相同数据量下（1kw），Ｂ＋树更加“矮胖”，IO操作更少；

2、因为要查询的数据不同，导致查询过程也不同，Ｂ树的查找只需找到匹配元素即可，最好情况下查找到根节点，最坏情况下查找到叶子结点，所以性能很不稳定，而Ｂ＋树每次必须查找到叶子结点，性能稳定；

3、在范围查询方面，B+树的优势更加明显;（B+树叶节点形成双向链表，更适合范围查找）

举例：查找范围 [3-11]，B树和B+树的查询过程：（见下图）

7.MySQL为什么建议使用自增数字作为主键索引？

1.自增主键是连续的，在插入过程中能减少页分裂，当一页写满，就会在当前索引节点页的后续位置开辟一个新的页，能减少数据的移动；

2.非自增主键是不连续的，比如UUID，每次新记录都要被插到现有索引页的中间某个位置，这不得不移动原来索引页的数据，频繁的移动，增加了很多开销；

注意：

1、如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为主键索引；

2、如果没有定义主键，则InnoDB会选择第一个不包含有Null值的唯一索引作为主键索引； 3、如果没有主键索引，也没有上面第2种情况这样的唯一索引，那么InnoDB会选择内置的6字节长的ROWID作为隐含的主键索引，ROWID随着行记录的写入而自动递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的； 

8.请解释一下 主键索引、聚集索引、聚簇索引、辅助索引、二级索引、非聚集索引、非聚簇索引？ 

1、主键索引；

把表的主键作为索引

2、辅助索引（二级索引）；

除了主键或者加了唯一索引的其他字段的索引是辅助索引（二级索引、普通索引）

3、聚集索引（聚簇索引）、非聚集索引（非聚簇索引）； 

聚集索引就是索引与数据存放在一起，非聚集索引就是索引与数据不放在一起，在InnoDB中主键采用的是聚集索引，就是数据与索引放在一起，MyISAM中就是非聚集索引，存的是数据地址，查找的时候需要需要根据地址再去找数据

9.什么是MySQL回表查询？ 

回表是指根据索引查询到的主键值再去访问主键索引，从而获取完整的数据记录。 

10.什么是MySQL覆盖索引？ 

 所查找的字段刚好是索引，SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询主键索引获取数据。

11.什么是MySQL的索引下推？ 

索引下推(Index Condition Pushdown，简称ICP)，是MySQL 5.6 版本新增的特性，它的主要作用是减少回表查询次数，提高查询效率； 

就是先在索引中把符合条件的数据过滤出来尽量避免回表查询，所以下推就是我在索引层面尽量往下推，把能筛选的数据现在索引层面筛选好，然后再进行回表查询，可以有效减少回表次数，提高效率。

举例：

查询姓张的并且年龄等于10，如果只有姓名是索引可能再索引层面过滤出十条姓张的，然后找出id值，最后分别回表查询数据，返回server层再过滤出年龄等于10的，但是如果年龄也是索引，就可以在索引层面直接过滤出姓张的年龄为10的，肯定比十条少，然后再回表查询，有效减少了回表次数

12.MySQL数据库InnoDB和MyISAM存储引擎有什么区别？ 

1、存储方式：InnoDB使用行级锁，MyISAM使用表级锁定来管理数据；

2、事务支持：InnoDB支持事务，MyISAM不支持事务；

3、外键支持：InnoDB支持外键，MyISAM不支持外键；

4、数据一致性：在数据库崩溃或断电的情况下，InnoDB具有很好的数据恢复能力，MyISAM容易发生数据损坏和不一致性；

5、索引方式：InnoDB使用聚集索引，MyISAM非聚集索引；