Skip to main content

MySQL

黑马MySQL总结​

1.为什么InnoDB引擎用B+树作为这种索引结构？​

（1）可以讲一下从二叉树到红黑树，到多路平衡搜索树，在到这个B树和B+树的过程。

（2）具体讲一下，为什么不用B树，而用B+树。答案就是B+树只在叶子节点存数据，其他节点不用存数据，这样可以存的节点的数量会更多，树会更低矮，因此效率更高。第二个就是B+树，有一个双向链表，做范围查询方便很多。

（3）讲一下为什么不用哈希索引，同样虽然非常快，但是不支持范围查询，以及排序。

2.InnoDb主键索引的B+tree有多高呢？​

这个有多高其实和你表的结构和主键的类型等有关系，但是大概率在3层以内。因为我去了解过这个估算的数量，通常来说，这个两层的B+树就能够存1.8w条数据，这个3层就可以存这个上千万的数据量。我可以去说一下这个估算的过程。

首先我们假设的前提的，数据库表中，每一行的数据，占据大小为1kb，然后主键采用的是BigInt的类型，也就是8个字节，我们可以去列出来一个方程式，就是，n * 8 + (n+1) * 6 = 16 * 1024 ,可以推算出这个一个节点的可以最多存这个1170个指针。这个公式我可以去具体解释一下，就是，B+树中的每一个树的节点，都存在一个 页/块中，而这个页的大小是固定的16kb，并且每一个非叶子节点，都存的这个主键和指针没，没有数据，并且这个指针的数量永远比这个主键的数量多1 ，所以，这个方程式是这么来的。那既然知道了这个非叶子节点最大的指针数量的话，我们还需要知道这个叶子节点，每一个节点他又能去存储多少的数据，同样也就是16 / 1 = 16，所以假如只有两层，那么这个b+树，就是第一层为非叶子节点，第二层是这个叶子节点，相当于就是 1170 * 16 ，如果是三层，就是第一二层是非叶子节点，第三层是叶子节点，也就是1170 * 1170 * 16 ，这个结果基本上是千万的量级。所以我的结论是基本上3层以内，再多的还可能就要去考虑分库分表了。

优化​

1.如何定位慢查询？​

有两种方法，第一种方法可以利用一些工具，比如普罗米修斯或者skywalking这些工具，这些工具可以监测所有接口的耗时，并且可以查询每一个接口中耗时的构成，其中数据库相关的耗时又是多少。

第二种方法就是剋一利用MySQL的慢查询日志，这个我们需要提前手动开启这个日志的记录开关，并且设置慢查询的阈值，一般是两秒。这样也可以在日志中查看的慢查询的相关SQL语句。

2.SQL语句执行很慢，如何分析？​

第一点，我们可以去使用explain或者desc这两个关键词加载一条SQL命令之前去分析这条命，explain之后会返回很多信息，其中有possible keys和keys这两个字段，我们可以去看一下我们的查询语句有没有走索引，设置了索引那索引又有没有失效。第二点，我们可以去查询SQL语句的Type字段，通过Type字段看一下SQL的性能好坏，里面最好的应该是NULL，代表没用到表，其次是只用到了系统表格，其次是使用到了主键索引，其次使用到了唯一索引，再其次使用到了索引，再其次是用到了范围查询，再其次是用到了索引范围扫描，再其次最后是用到了全标扫描，后两种应该去避免。第三点，我们还可以去看一下这个SQL语句有没有用到回表查询，如果用到了我们还可以想一下是不是可以进一步去优化索引，或者说，返回字段进行一定的修改。

3.了解过索引吗？​

索引本质上是一个数据结构，这个数据结构类似于字典的目录，可以帮助你更快速地检索到数据，并且索引是排序好的一个数据。

4.索引的底层数据结构了解过吗，B树和B+树的区别是什么？​

MySQL的索引主要使用的是B+树，为什么选择B+树，其实我们还需要了解一些其他的数据结构进行对比，比如二叉查找树、红黑树、B树，其中第一个是一个二叉树，分支只有两个，数据量大的话树会非常高，查询效率会下降，并且如果插入的顺序不同，有可能出现退化成链表的情况，红黑树尽可能保持了树的平衡，但是本质还是二分支，效率还是低。其中B树和B+树很像，首先他们呢都是多分枝树，因此树可以保持比二叉树低，查找效率有保障，其次B树会把数据存在树的每一个节点之中，查询的过程中查到了就直接返回,B+树则把所有的数据都存在了叶子节点上，中间的节点都只存储了指针，指向下一个节点的位置， 必须去查找到叶子节点，因此B+树的查找就会更稳定，第三点，B+树的所有的叶子节点都由一个双向链表所连接，并且排好序了,所以再进行范围查询的时候会比B树方便很多

5.什么是聚簇索引和非聚簇索引？​

聚簇索引又叫一级索引，非聚簇索引又叫二级索引，前者的意思是B+树中，叶子节点会保存完整的一个一行数据，而后者的叶子节点只会保存相对应的主键id。聚簇索引有且只能有一个，一般来说就是主键索引，如果没有主键索引就是唯一索引，如果这两个都没有，那就是MySQL会默认给表的隐藏字段rowid设置为一个聚餐索引。

6.什么是回表查询？​

回表查询总结来说，先通过聚簇索引查询到一个值，再根据这个值通过非聚簇索引去查询到数据。举个例子，有一个学生信息表，有两个索引，分别是主键索引，和年龄索引，再根据姓名去查询的时候，会先走这个姓名的非聚簇索引查询到这个学生的id，再通过这个id去主键索引查询这个学生的所有信息。这个过程就是所谓的回表查询

7.什么是覆盖索引？​

覆盖索引指的是需要查询的字段，存储在非聚簇索引中，通过一次索引扫描可以直接返回结果，不需要再进行相应的回表查询

8.MySQL超大分页怎么处理？​

//TODO

9.索引创建的原则有哪些？​

首先如果一个表格的数据量特别大，查询起来性能就可能不高，可以考虑创建一个索引，一般这个数量额阈值是10w，再者我在在平常根据 where进行条件差查询的时候，对于某些频繁被作为查询条件的字段的可以创建索引，并且为了达到联合索引避免回表查询，可以考虑创建联合索引。再者我们需要选择一下区分度比较大的字段来进行创建索引，例如根据地点北京市大概率就不是一个好的选择，因为北京市的人太多了。另外对于某些特别长的字段值，我们也可以考虑使用前缀索引来提升效率。最后索引的整体数量我们也需要进行控制，因为索引也是需要一定的维护成本的，所有数据修改之后都要去同步修改索引的数据

10.什么情况下索引会失效？​

索引失效的情况有很多种。

第一种就是违反了联合索引的最左匹配原则，，会导致部分索引的失效

第二种就是进行模糊查询的时候，如果在查询值的后面用了%也会导致索引失效

第三种就是如果联合查询中中间使用了范围查询，那么右边的索引值也会失效

第四种就是，如果进行字符串的匹配的时候忘记加上引号也会进行失效

第五种就是，在索引上进行运算操作，索引也会失效

11.谈一谈你对SQL优化的经验​

第一点，就是数据库的表格本身可以优化，比如tinyint和int、char和varchar等等

第二点，就是索引相关的优化，考虑去创建索引，避免一些索引的失效

第三点，就是sql语句的优化，比如查询的时候尽量避免去写select *

第四点，mysql从架构上也可以优化，比如采用集群进行读写分类来提供sql的执行效率，或者是进行分库分表，分散数量过大的数据

事务​

1.事务是什么，他的特性是什么？​

事务是一组操作的集合，是一个不可分割的工作单位，要么就全部成功，要么就全部失败。事务有四大特性，也被称为ACID，其中A是原子性，代表操作集合的不可分割，C是一致性意思是事务完成后所有数据的改变都要保持一致，I是隔离性，这个也是最终要追求的目标，代表该事务的操作不受其他事务的影响，D是持久性，代表事务结合术后的操作，数据不会被还原，数据永久落库。可以举一个例子，A给B转账1000块钱，可以简单分为两部，A的钱少1000，B的钱多1000两个操作，A原子性指的是，这两个操作必须全部执行成功或者全部执行失败，C一致性代表，事务结束之后，数据也就是钱的总额是不会变化的 I隔离性指的是转账的过程中不会受其他的事务影响，D持久性指的是转账操作结束之后，A的钱永久就会少1000，B的钱永久会多1000，不会变回一开始转账之前的状态

2.并发事务会带来哪些问题？如何解决这些问题？MySQL的默认隔离级别是？​

MySQL并发事务主要会带来三个高频的问题，分别是脏读、不可重复读和幻读，第一个脏读指的是一个事务读取了另一个另一个事务中的数据，但是另一个事务还没来得及对数据进行修改，相当于读到了修改之前的脏数据。第二个不可重复度，是指在同一个事务中多次读取一个数据，但是另一个事务在本事务多次读取数据之间修改了数据，造成了本事务多次读取的数据不一致，这也被称为不可重复读。另一个就是幻读，这个和不可重复读有一点类似，但是区别就在于幻读是读取一个范围的额数据，但是如果有其他事务对该范围的数据进行了修改或者插入的操作，读出来的数据可能不一致，比如一开始读取数据的数量是10，其他事务又插入了一个数据，数量又变成了11。

这些问题的解决主要是通过事务的隔离机制。事务的隔离机制从弱到强分为两四个等级，最弱的等级是读未提交，该隔离机制等级最弱，三个问题一个都没有解决，另一个是读已提交，该隔离机制强度大了一点，解决了脏读的问题，第三个等级是可重复度，这个是第二强的等级，也是MySQL默认的等级，最严格的等级就是串行化，解决了所有的问题，不过该等级会造成性能比较大的一个下降

3.redo log日志和undo log日志的区别​

redo log记录了物理数据，分为两部分，一部分叫redo log缓冲，另一部分叫做日志文件，前者存在内存中，后者存在计算机的磁盘中，在执行insert或者update的一些语句的时候，会采用一个WAL技术，也就是先写日志在传输数据，现在bufferpool的缓冲区记录redo log数据，在传输到磁盘的日志文件里，紧接着传输数据，如果成功了，其实之前的这个redo log日志没啥用了，会定期删除，但是一旦失败，会从这个日志文件中读取然后去恢复数据。因此总得来说redo log保证的是数据的持久性。

undo log记录了逻辑日志，比如说执行力一条insert操作，就会增加相应的一个del逆操作，一旦事务失败了，就可以通过这个undo log进行操作的回滚，因此这个undo log保证了事务的原子性和一致性。

4.解释一下mvcc？​

MVCC是多版本并发控制器，和锁共同实现了事务的隔离 复用第一次生成的ReadView视图。

其他​

1.MySQL主从同步的原理​

MySQL主从同步会用到binlog日志，这个是一个二进制文件，其记录了SQL的DDL和DML语句，主要是依赖于这个binlog进行同步，主要分为三个步骤吧

第一个步骤：主库在提交事务的时候会去将自己的操作记录到binlog日志中

第二个步骤：从库把主库的binlog日志复制到自己的中继日志relaylog中

第三个步骤：从库根据中继日志执行复制过来的命令，进行数据同步

2.说一说分库分表？​

分库分表一般是真对数据量较大的情况使用的，标准大概是单表1000w或者是20G数据，一般分为四种策略。

第一种策略就是水平分库，可以把一个数据库中的表的内容拆分到三个数据库的表中去存，这三个库的表共同记录了一个完整的数据

第二种策略就是水平分表，这种一般是解决单表的压力和性能问题的，可以分到同一个数据库中，也可以分到不同的数据库里面

第三种策略就是垂直分库，这种一般就是根据业务去数据库中的表，把具有相关性的几张表放到一个数据库里面，这个也符合当下微服务的思想

第四种策略就是垂直分表，这种一般就是跟觉业务和数据的冷热和大小关系把一张表格拆分成两张甚至多张表格来进行存储，来提升效率