5.mysql篇

旧城等待， 2023-07-03 10:49 4阅读 0赞

我上课学过一遍mysql，然后自己看视频学了一遍“mysql进阶”

然后自己瞎研究了一段时间mysql，也写了一个专栏[https://blog.csdn.net/huaixiaohai\_1/category\_9294028.html][https_blog.csdn.net_huaixiaohai_1_category_9294028.html]

但是发现自己只是停用在使用mysql的层面上，知识能使用各种sql语句，有的地方可能明白原理，但是知识很模糊的那种，只要往深一问的话，就一脸懵逼。

在准备这部分时候，我一个贼拉好的学长，他把他的掘金号给我了，说里边买了一本关于mysql的小册，并且再三叮嘱我好好看看。感觉真的很不多，由于版权关系，我只给大家推荐一下把。![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YWl4aWFvaGFpXzE_size_16_color_FFFFFF_t_70][]

就是这个小册，我就不写关于这个的文章了。

毕竟人家码字不容易。好了广告时间结束，开始正文，总结了一些面试问题：

觉得mysql中重点可能会问索引部分，所以从索引开始吧。

**1.最左匹配原则是什么？**

是指在联合索引中，先根据建立索引语句中的最左一列进行索引建立。比如通过name，和age建立索引，那么先根据name建了一个索引结构，然后如果name相同的时候，再根据排序建立。

**2.说一下innodb中的索引？索引是怎么组织起来的？**

innodb索引是b+树索引。还有hash索引，是在b+树之上又建立的一个索引结构，当索引值使用频繁时会建立。

建立方式：每插入一条记录，会按照找主键大小维护一个单链表，然后为了查询更快，在一个页上将单链表分成好多快（slot）最多8条记录,将块中最大的元素记录在页目录里边，然后用双向链表的形式连接起来，然后为这些块建立了一个目录结构，因为如果数据多的话会有很多页，那么为了快速定位到这些页，他又开辟了一个页专门为页建立了一个目录，同级的页之间也是用双向链表的形式组织起来，组织的时候也是根据主键大小排序建立的，就这样一层一层的建立目录，直到一个块能装下下一层的目录结构。这样就形成了一个b+树。

**3.那如果建立了索引一定会用到吗？在什么时候索引会失效？**

1>.查询条件中有or语句，并且or两边一个并没有索引。

2>.查询条件中有not exist 或者not null

3>.使用模糊查询并且查询条件以%开头

4>.查询字符串但是没有用‘’引起来

5>.查询表中所有数据

6>.在索引列条件左边做运算

**4.什么叫聚集索引？什么 是非聚集索引？**

以主键建立的索引是聚集索引，节点存储的是列的所有信息。

以非主键建立的索引是非聚集索引，节点存储的是 列+页号+主键

**5.索引的优缺点是什么？**

优点查找速度快，缺点是如果是要占用比较多的空间。

而且再插入和删除的时候维护索引也需要消耗时间。

**6.那为什么要分表？建立索引不就可以吗？**

建立索引可以提交速率，但是数据库文件是存在磁盘中的，我们使用的时候需要将数据读到内存中，如果数据量特别大的话，内存大小有限，容易造成溢出。

**7.那如果分表的话，要根据什么来进行分表？**

根据业务需求，可以根据时间，地区等。

**8.分表的方式有几种，举个列子？**

横向分表：按行分表，比如可以通过hash的方式把数据分到几个不同但是

纵向分表：按列分表，比如一个基本信息表，一个详情表。

**9.那事务是什么？他有什么特性？**

事务是数据库执行的最小单位，一个事务中有一系列对数据库的操作，他们要不就提交，要不就都不提交，提交失败就会回滚。

事务有：原子性，一致性，隔离性，持续性。

**10.那回滚是怎么回事？**

数据库回滚主要使inndb引擎，主要通过redo来实现，当数据库更新以后会将更新结果存在内存中，并在redolog插入一条日志记录“在哪个数据页做了哪些修改“，然后设置标识为prepare，等到事务commit以后，将这次事务插入的记录所有都变成commit状态。

**11.如果有多个事务，会发生什么问题？**

1>.脏读，事务A读到事务B未提交的数据，之后事务B回滚了，事务A提交了，发生错误。

2>.不可重复读，设置事务A只能读取事务B已经提交的事务，但是在一次事务中2查询发现结果居然不一样，因为期间事务B提交了事务。

3>.幻读，事务A查询了一个范围，但是事务B插入了一个符合范围的对象。在进行插入的时候主键就会出现冲突等问题

**12，怎么解决这些问题？**

设置数据库隔离级别，共有4个隔离界别，

1.未提交读，指可以读取未提交的数据。

通过对写操作加共享锁，然后事务提交以后解锁。

2.已提交读，指可以读取已提交的数据。

通过对读操作加共享锁，然后及时释放，写操作加写排他锁，事务提交以后释放。

3.可重复读，一次事务中2次查询结果一致

通过对写操作加共享锁，写操作加排他锁，事务提交以后释放（行级锁）。

4.序列化，最高的隔离级别，可以解决上述的所有问题，他强制使所有操作都是串行处理，但是导致性能直线下降。

通过对读操作加读锁，写操作加排他锁，事务提交以后释放（表锁）

**13.了解mysql中的锁机制吗？都有什么锁？**

当数据库有并发事务的时候，可能发生数据不一致的情况。

按照读写来分，有排他锁和共享锁。

排他锁：也叫写锁，当要对数据库发生写操作的时候，会锁定改行数据，别的事务不能读不能写。

共享锁：也叫读锁，当要对数据库发生读操作的时候，会锁定该行数据，别的事务可以读不可以写。

按照锁的粒度来说：

可以分为表锁（Mysaim），行锁（innodb），页锁。

**14.数据库中的悲观锁和乐观锁？**

悲观锁和乐观锁知识一种思想，并不是真实存在的一种。

悲观锁觉得有较大机率别的线程会来抢夺.

悲观锁的实现:

修改数据之前，先对数据加排他锁，如果加锁失败那么说明有其他事务在使用该数据，那么就会等待或者直接抛出错误。

如果加锁成功，那么进行修改，修改成功之后，解锁。在修改期间如果有其他事务也想要修改，那么他就要等待或者抛出错误。

悲观锁缺点，在写多读少的情况下，容易造成cpu的暴增。

乐观锁觉得别的线程来抢夺的机率非常小。

乐观锁实现：乐观锁只会在提交的时候对数据进行检测冲突，采用cas来实现乐观锁，先更新以前，先读取一份备份到本地，然后等待提交的时候，比较本地和数据库数据是否一致，如果一致那么进行修改，如果不一致根据用户指示做。

乐观锁的ABA问题：指原来是A,事务1对A进行修改，备份一个A,然后来一个事务对A进行修改成了B,然后又来了一个线程对B进行修改，修改成了A,这时候事务1去修改的时候，比对发现没问题。这就是ABA问题。

**15.如何解决ABA问题呢？**

在执行数据库修改的时候加上一个序列号，每次修改以后序列号+1

**16.mysql的优化手段？**  
 第一个想起来的肯定就是对经常作为查询的条件的字段建立索引。除此以后还可以对数据进行分区处理：

分区的四种方式：

1>.range分区

通过查询范围来进行分区，每个分区都有自己独立的数据，索引和目录结构。

PARTITION child VALUES LESS THAN (12) ENGINE = InnoDB, 
    PARTITION young VALUES LESS THAN (18) ENGINE = InnoDB,
    PARTITION adult VALUES LESS THAN (40) ENGINE = InnoDB,
    PARTITION middleage VALUES LESS THAN (60) ENGINE = InnoDB, 
    PARTITION `old` VALUES LESS THAN MAXVALUE ENGINE = InnoDB

2>.List分区

相当于一个集合，判断字段是否在集合之中。

PARTITION BY LIST (‘分区字段’) (       
    PARTITION p0 VALUES IN (0,4,8,12) ,     
    PARTITION p1 VALUES IN (1,5,9,13) ,    
    PARTITION p2 VALUES IN (2,6,10,14),     
    PARTITION p3 VALUES IN (3,7,11,15)  
     );

3.hash分区

通过分区的数量来进行hash分区，只支持数字类型

PARTITION BY KEY (id) PARTITIONS 4 (  
         PARTITION p0,  
         PARTITION p1,  
         PARTITION p2,  
         PARTITION p3
    
    );

4.key分区

对hash分区的一种延申，除了数字类型以外还支持text和blob之外的所有类型，如果没有指定字段，那么默认以主键分区，如果没有指定主键，那么默认以非空字段分区。

PARTITION BY KEY (id) PARTITIONS 4 (  
         PARTITION p0,  
         PARTITION p1,  
         PARTITION p2,  
         PARTITION p3
    );

还有就是如果在查询中尽量避免索引失效，不要使用select \*这种语句，还有不要查询不需要用到的字段。

**17.mysql的执行流程？**

1.首先会先查一下缓存，看看这个sql有没有执行过。

2.如果没有缓存，那么就进入分析器：进行词法分析（提取select等关键子）和语法分析（判断你的sql是否正确，是否符合sql语法）。

3.优化器：去对要执行的sql进行优化。比如多个索引选择索引，多表查询的时候如何选择关联顺序。

4.执行器：会对权限进行校验。

5.通过引擎去进行数据查找。

6.返回结果给客户端。

**18.什么是MVCC,原理是什么，在哪个隔离级别有效？**

MVCC:对版本并发控制（其实是乐观锁的一种一种实现形式）。

它主要是通过某个时间的快照来来实现的，事务无论运行多长时间都能看到一致性的试图。

它在已提交读和可重复读有效。

**19.mysql中innodb和mysaim引擎的区别及B+树的区别？**

innodb支持事务，支持回滚，是行级锁。b+树叶子节点存储的是一行的数据。适用于写多的情况。

mysaim不支持事务，是表级锁。b+树索引叶子节点存储的是数据的地址。适用于读多的情况。

**20.mysql中主从同步原理？**

mysql中主要采用增量同步的形式来实现的。主服务器将将数据变动记录到二进制文件中（binlog），从服务器通过读取和执行数据库的日志文件来保存一致性。

主服务器会 将所有的信息记录下来，然后从服务器收到一个副本，从服务器可以指定该数据库的哪一类事件（比如只插入或只更新），默认会执行所有的语句。

**21.select(),poll(),epoll()区别？**

select()：他只能知道有io事件发生了，但是不知道式哪个发生的，所以他这时候就需要去遍历。本质上是通过检查fd数组上的状态位来检查的。

poll（）：这个与select()类似，他将用户传入的数组拷贝到内核态，然后查询每个fd对应的设备状态，他没有 最大连接限制，因为它采用的是链表存储。

epoll():主要基于事件驱动，他会把哪个哪个流发生了怎样的io事件告诉我们。

[https://www.cnblogs.com/aspirant/p/9166944.html][https_www.cnblogs.com_aspirant_p_9166944.html]

**22.mysql中的io是什么样的？为什么使用这种io模型？**

mysql中把数据组织成树结构，在内存和磁盘中进行转化，进行大量的查找和排序，这些查找和排序耗费的时间比较长，主要时间是花在cpu和内存上，而不是网络等待上，非阻塞io没有优势，所以mysql采用的是阻塞式io。

redis中采用的非阻塞式io，因为redis中经常把数据存到内存中，而不是没有进行大规模的排序这种运算，所以redis采用非阻塞io。

**23.Mysql中的间隙锁？**

mysql如果我们进行范围搜索的时候，比如select \* from user where id >1 and id<10.

这时候我们中没有id=5的数据，那么id等于5的这条不存在的数据也会被锁住，即无法插入删除。

[https_blog.csdn.net_huaixiaohai_1_category_9294028.html]: https://blog.csdn.net/huaixiaohai_1/category_9294028.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YWl4aWFvaGFpXzE_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200205210231995.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YWl4aWFvaGFpXzE=,size_16,color_FFFFFF,t_70
[https_www.cnblogs.com_aspirant_p_9166944.html]: https://www.cnblogs.com/aspirant/p/9166944.html