MySQL面试题_where id>n limit n-CSDN博客

本文链接：https://blog.csdn.net/m0_45388156/article/details/113064881

MySQL

MyISAM支持表级锁，不支持事务和外键，并发效率较低，读取数据快，更新数据慢
InnoDB支持行级锁、事务、外键，并发效率好，适用于增删操作(有自适应哈希)
Memory，所有的数据都保存在内存中，访问速度快，一旦服务关闭数据将丢失。适合更新不太频繁的数据量小的表用来快速得到访问结果。

memory用的hash索引放在内存(只存key索引不放值)

磁盘速度慢，内存速度快，磁盘可以通过内存加快读取速度，所以拿key去磁盘找value也快，但一旦断电内存里的数据就没了

MyISAM索引与InnoDB索引的区别？(什么是聚簇索引？何时使用聚簇索引与非聚簇索引？)辅助索引一定查询两次吗？

MyISAM索引是非聚簇索引，叶子节点存储的是行数据地址，通过地址找到对应数据。
InnoDB索引是聚簇索引，主键索引的叶子节点存储着行数据，因此主键索引非常高效，辅助索引需要两次查询，要拿辅助索引关键字索引到主键，查询时做到覆盖索引会非常高效。

不一定的，通过覆盖索引也可以只查询一次。

聚簇索引和非聚簇索引，在查询数据的时候有区别吗？为什么？

聚簇索引查询会更加快些。因为主键索引树的叶子节点存储的是整行数据。也就是我们需要得到的数据。而非主键索引的叶子节点是主键的值，查询的主键之后，我们还需要通过主键的值再次进行查询数据。（这个过程被称之为回表）。
在这里插入图片描述

InnoDB是通过B+树结构对主键创建索引，然后叶子节点中存储记录，如果没有主键那么会选择唯一键，如果没有唯一键那么会生成一个6字节的row_id作为主键

推荐使用主键自增，分布式下则不行

不是自增的话，插入一个中间值将会发生多次页分裂，将消耗许多时间

页分裂：数据插入时尽量减少页分裂

与io相关的两个底层原理

局部性原理：时间局部性和空间局部性，数据和程序都有聚集成群的倾向，之前被读取过的数据有可能很快被下一次读取

磁盘预读：即使每次读一个很小的数，系统也会分页读取(一般4k或8k)

所以尽量减少io次数

为什么采用B+树？不用hash索引？不用二叉树、AVL树、红黑树？

B+树是一种多路平衡查询树，他的节点是天然有序的，所以对于范围查询的时候不需要做全表扫描。

对于哈希索引来说，底层的数据结构就是哈希表，只适用于等值查询的场景，hash索引没办法利用索引完成排序，会有哈希冲突，要占用大量的空间

分支都只有两个，树的层数变高、io次数变得高，进行范围查找时要回旋，速度会变慢

B+树是什么和B树有什么区别？查找的时间复杂度

B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。进行范围查找也要回旋

由于B+树的内部节点只存放键，不存放值(每个块里存放的索引量更大)，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间

每个索引列占用的内存越小磁盘块里面存的索引就越多(字节数小于4用vchar，否则用int)

MySQL的索引对B+树做了哪些优化？

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高范围查询的性能。

----------------------------------

主键、外键作用

主键保证的是数据的唯一性，整个表按主键的顺序存储

外键保证的是数据的完整性，表与表之间的一个连接点

索引有哪些分类？为什么不是越多越好？

普通索引: 基本的索引类型，允许在定义索引的列中插入重复值和空值

唯一索引: 索引列中的值必须是唯一的，允许为NULL值，一个表允许多个列创建唯一索引

主键索引: 索引列中的值必须是唯一的，不允许为NULL，一个表只能有一个主键

组合索引：多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

全文索引：是目前搜索引擎使用的一种关键技术。

索引越多更新速度越慢，占用的物理空间更多

数据库的索引创建有哪些设计原则？

①对查询频次较高，数据量较大的表创建索引。

②限制索引数量：对于增删改操作较多的表，如果索引过多将需要很高的维护代价，降低操作效率，增加操作耗时。

③利用最左前缀：如果索引字段值过长，会降低索引的执行效率。

④删除不常用索引。

⑤使用唯一索引，区分度越高，效率越高。

⑥使用短索引，如果索引值很长则占用磁盘变大，会影响效。

⑦为常作为查询条件、经常需要排序、分组和联合操作的字段建立索引。

⑧尽量扩展现有索引，联合索引的效率高于多个独立索引

为什么产生最左匹配原则，底层是什么引起的

假设联合索引有两个值a、b：总的来说a是有序的，b是无序的

a相等时才有b相等

在a相等的基础上通过二分查找再找b

有哪些MySQL索引相关的优化

尽量使用主键查询: 聚簇索引上存储了全部数据, 相比普通索引查询, 减少了回表的消耗.
MySQL5.6之后引入了索引下推优化, 通过适当的使用联合索引, 减少回表判断的消耗.
若频繁查询某一列数据, 可以考虑利用覆盖索引避免回表.
联合索引将高频字段放在最左边.

索引在什么情况下会失效?

①复合索引未使用最左列索引时或跳跃使用时失效，例如以name，age和sex字段建立索引，只使用age和sex或只使用name和sex时索引失效。

②在索引上进行运算或函数操作时索引失效。

③数字字符没有加单引号索引失效，因为MySQL查询优化器会自动进行类型转换。

④在索引字段上使用，NOT、 <>、！= 或LIKE以%开头的查询索引失效，会进行全表扫描。

⑤OR的前后没有同时使用索引时索引失效。

⑥当全表扫描比使用索引速度更快时会使用全表扫描。

----------------------------------

MySQL有哪些锁

按照对数据操作的锁粒度来分：行级锁、表级锁、页级锁、间隙锁
行级锁：mysql中锁定粒度最细的一种锁，开销大，加锁慢，会出现死锁。发生锁冲突的概率最低，并发度也最高。行级锁分为共享锁和排他锁
表级锁：开销小，加锁快，不会出现死锁。发生锁冲突的概率最高，并发度也最低。
页级锁：一次锁定相邻的一组记录，开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
间隙锁：对索引的前后上锁、，不对索引本身
能防止幻读：1、防止间隙内有新数据被插入 2、防止已存在的数据，更新成间隙内的数

innodb的锁包括

在这里插入图片描述

MySQL怎样实现读锁和写锁，写锁的时候可以读么

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

读-读可以共存,读-写不能共存,

乐观锁的业务场景及实现方式

比较适合读取操作比较频繁的场景
版本号机制 or CAS 算法

----------------------------------

简述事务的ACID属性

答：①Atomicity表示原子性，事务中的所有操作都是不可分割的原子单位，要么全部成功，要么全部失败。

②Consistency表示一致性，无论正常执行还是异常退出，事务执行前后数据的完整性必须保持一致，比如转账前后双方的总金额是不变的。

③Isolation表示隔离性，并发操作中不同事务是互相隔离的，之间不会互相影响。

④Durability表示持久性，事务操作完成后数据就会被持久化修改到永久存储中。

MySQL数据库的隔离级别有哪些？分别有什么特点？

①读未提交，一个事务会读取到另一个事务没有提交的数据，存在脏读、不可重复读、幻读的问题。

②读已提交，一个事务可以读取到另一个事务已经提交的数据，解决了幻读的问题，存在不可重复读、幻读的问题。

③可重复读，MySQL默认的隔离级别，在一次事务中读取同一个数据结果是一样的，解决了不可重复读的问题，存在幻读问题。

④串行化，每次读都需要获得表级共享锁，读写互相阻塞，效率低，解决了幻读问题。

mvcc

多版本并发控制，为每次事务生成一个新版本的数据，实现对数据库的并发访问，提高并发的读写性能。
在这里插入图片描述

主要针对，读已提交和可重复读
读已提交：有一个read-view记录当前事务是否可读(已提交的事务才可读)，每次查询都有自己的read-view
可重复读：每次查询都继承上一个read-view
在这里插入图片描述

读取数据库时可能出现哪些问题？

①脏读，一个事务中会读取到另一个事务中还没有提交的数据，如果另一事务最终回滚了数据，那么所读取到的数据就是无效的。

②不可重复读，一个事务中可以读取到另一个事务中已经提交的数据，在同一次事务中对同一数据读取的结果可能不同。

③幻读，一个事务在读取数据时，当另一个事务在表中插入了一些新数据时再次读取表时会多出几行，如同出现了幻觉。

怎么解决幻读？

----------------------------------

数据库表设计流程

建立索引和分表

简述主从复制的基本原理

答：①主从复制是指一台服务器充当主数据库服务器，另外一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器中。对于多级复制，数据库服务器既可充当主机也可充当从机。

②MySQL主从复制的基础是主服务器对数据库修改二进制记录，从服务器通过主服务器的二进制日志自动执行更新。

一千万条数据的表, 如何分页查询

数据量过大的情况下, limit offset分页会由于扫描数据太多而越往后查询越慢. 可以配合当前页最后一条ID进行查询, SELECT * FROM T WHERE id > #{ID} LIMIT #{LIMIT}. 当然, 这种情况下ID必须是有序的, 这也是有序ID的好处之一.

订单表数据量越来越大导致查询缓慢, 如何处理

分库分表. 由于历史订单使用率并不高, 高频的可能只是近期订单, 因此, 将订单表按照时间进行拆分, 根据数据量的大小考虑按月分表或按年分表. 订单ID最好包含时间(如根据雪花算法生成), 此时既能根据订单ID直接获取到订单记录, 也能按照时间进行查询.

MySQL数据库cpu飙升到500%的话他怎么处理？

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。
如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。
一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。
也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。