mysql面试总结

最新推荐文章于 2023-03-10 15:10:09 发布

weixin_42990057

最新推荐文章于 2023-03-10 15:10:09 发布

阅读量150

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_42990057/article/details/112150121

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL之存储引擎

MySQL存储引擎有哪些

sql语句查看MySQL所有存储引擎

show engines;

MySQL存储引擎主要有InnoDB, MyISAM, MEMORY等，每种引擎都有各自不同的特性
MySQL的默认存储引擎为InnoDB

InnoDB存储引擎

InnoDB是MySQL的默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它的存储引擎。
它实现了四个标准的隔离离别，默认级别是可重复读（REPEATABLE-READ），在可重复读的隔离级别下，通过多版本并发控制（MVCC）来防止幻影读。
主索引是聚集索引，在索引中保存了数据，从而避免了直接读取磁盘，因此对查询性能有很大提升。
支持真正的在线热备份，其它引擎不支持。

MyISAM存储引擎

设计简单，数据以紧密格式存储
提供了大量的特性，包括压缩表，空间索引等。
不支持事务。
不支持行级锁，只能对整张表加锁。
可以手动或自动检查和修复操作，但是可能会导致丢失一些数据。

InnoDB和MyISAM的区别

是否支持行级锁：MyISAM只支持表级锁，而InnoDB支持行级锁和表级锁，默认为行级锁，适合高并发操作。
是否支持事务及崩溃后的恢复：MyISAM不支持事务，因此没有恢复策略，而InnoDB支持事务、外键等高级功能，具有事务回滚和崩溃修复的能力。
是否支持外键：MyISAM不支持外键，InnoDB支持外键。
缓存区别：MyISAM 只缓存索引，而InnoDB缓存索引和真实数据，所以对内存要求高。
是否支持MVCC：仅InnoDB支持MVCC，应对高并发事务，MVCC比单独的加锁更高效，MVCC只工作在读取已提交和不可重复读两个隔离级别下，MVCC可以使用乐观锁和悲观锁来实现。

字符集及校对规则

字符集指的是从一种二进制编码到某类字符符号的映射。校对规则则是指某种字符集下的排序规则。MySQL中每一种字符集都会对应一系列的校对规则。
MySQL采用的是类似继承的方式指定字符集的默认值，每个数据库以及每张数据表都有自己的默认值，他们逐层继承。比如：某个库中所有表的默认字符集将是该数据库所指定的字符集（这些表在没有指定字符集的情况下，才会采用默认字符集）

MySQL之索引

索引是什么

索引是一种用于快速查询和检索数据的数据结构，常见的索引有hash索引和B+树索引。

为什么索引能提高效率

数据索引的存储时有序的。
在有序的情况下，通过索引查询一个数据时无需遍历索引记录的。
极端情况下，数据索引的查询效率为二分法查询效率，趋近于 $log_2(N)$ 。
避免了全表扫描。

什么情况下应不建或少建索引

非常小的表。
非常大的表，建议使用分区技术。
经常插入、删除、修改的表。
数据重复且分布平均的表。

索引的优点和缺点

优点：可以加快数据的检索速度；减少了服务器需要扫面的数据行数；创建唯一索引可以保证数据库中的每一行数据的唯一性；将随机I/O变为顺序I/O。
缺点：创建索引和维护索引需要耗费太多的时间；回降低SQL的执行效率；占用物理存储空间。

索引中B树与B+树的区别

B树所有节点都存放键（key）和值（value）,而B+树只有叶子节点存放键和值，其它节点都只存放键，而不存放值。
B树的叶子节点都是独立的；B+树的叶子节点有一条引用链，指向与它相邻的叶子节点。
B树的检索过程相当于对范围内的每个节点的关键字做二分查找，可能还没到达叶子节点就结束了；而B+树的检索效率就很稳定，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

索引中hash索引与B+树索引的比较

hash索引是哈希链表来实现的，最大的优点就是能够在很短的时间内，根据hash函数来定位到数据所在的位置，这是B+树所不能比的。
hash索引会出现hash冲突。
hash索引不支持范围和顺序查询。

索引类型分类

主键索引：一个表只能有一个主键，并且主键不能为null,也不能重复。
二级索引（辅助索引）：二级索引的叶子节点存放的数据是主键，也就是说，通过二级索引，可以定位到主键的位置。二级索引包括：普通索引、唯一索引、前缀索引、全文索引等都属于二级索引。

普通索引：唯一作用就是加快数据查询速度，可以重复和为空。
唯一索引：唯一索引的数据列不能出现重复数据，唯一索引大多用来保证数据的唯一i选哪个而不是加快数据的查询速度。
前缀索引：适合字符串类型的数据，对文本的前几个字符创建索引。
全文索引：主要是为了检索大文本数据中的关键字的信息。

聚集索引和非聚集索引

聚集索引：索引的叶子节点就是数据节点，聚集索引一个表只能有一个，主键索引属于聚集索引。
（1）优点：查询速度非常快，叶子节点本身就是有序的，定位到索引的节点，就相当于定位到了数据。
（2）缺点：更新代价特别大。
非聚集索引：索引的结构和数据是分开存放的，二级索引都数据非聚集索引，所以一个表可以有多个非聚集索引。
（1）优点：更新代价比聚集索引要小，因为叶子节点不存放数据，不需要维护数据。
（2）缺点：需要依赖有序的数据；可能会二次查询（回表查询）。

覆盖索引

如果一个索引包含所有需要查询的字段的值，我们就称为“覆盖索引”，在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值，最终还是要“回表”，也就是通过主键再查找一次，这样就会比较慢，覆盖索引就是把要查询出的列和主键是对应的，不需要做回表操作。

MySQL之事务

什么是事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

事物的四大特性

原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么不完全不起作用。
一致性：执行事务后，数据库从一个正确的状态变化到另一个正确的状态。
隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。
持久性：一个事务被提交后，它对数据库中的数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务带来的问题

脏读：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。
丢失修改：指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么第一个事务中修改了这个数据后，第二个事务也修改了这个数据，这样第一个事务修改的结果被丢失，称为丢失修改。例如A=20，事务1对A做A=A-1操作，事务2对A也进行A=A-1操作，但最终A=19，事务1对A的修改被丢失。
不可重复读：指在一个事务内，多次读取同一个数据，这个事务还没有结束，另一个事务也访问该数据并进行了修改，那么在第一个事务的两次读取之间，由于第二个事务的修改导致第一个事务两次读取结果可能不一样，这种情况称为不可重复读。
幻读：幻读与不可重复读类似，它发生在一个事务读取了几行数据后，接着另一个事务进行了插入操作，导致第一个事务读取到一些原本不存在的记录，就像幻觉一样，因此称为幻读。

事务的隔离级别

READ-UNCOMMITTED（读取未提交）：最低的隔离级别，允许读取未提交的数据，可能会导致脏读、不可重复读、幻读。
READ-COMMITTED（读取已提交）：允许事务读取已提交的事务，能阻止脏读，可能会导致不可重复读、幻读。
REPEATABLE-READ（可重复读）：对同一字段的多次读取结果是一样的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但会出现幻读。
SERIALIZABLE（序列化）：最高的隔离级别，完全服从ACID的隔离级别，所有事物依次逐个执行，事物之间不可能会发生干扰，能阻止脏读、不可重复读、幻读。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

InnoDB引擎事务隔离级别

InnoDB引擎的默认隔离级别是可重复读，但是它在该隔离级别下使用的是Next-Key-Lock算法，因此可以避免幻读的产生，这与其它数据库是不同的。
MySQL可重复读的隔离级别已经达到了序列化的隔离级别，隔离级别越低，事务所需要加的锁越少，并发性能更好，所以，使用可重复读隔离级别并不会有什么性能损失。
InnoDB存储引擎一般在分布式情况下会用到序列化隔离级别。

MySQL锁算法

MyISAM和InnoDB存储引擎使用的锁：

MyISAM采用表级锁(table-level locking)。
InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

表级锁和行级锁对比

表级锁： MySQL中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
行级锁： MySQL中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB存储引擎的锁算法

Record lock：单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 锁定一个范围，包含记录本身

其它相关知识

InnoDB对于行的查询使用next-key lock。
Next-locking keying为了解决Phantom Problem幻读问题。
当查询的索引含有唯一属性时，将next-key lock降级为record key。
Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生。
有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1。

MySQL优化

MySQL大表优化

当MySQL单表记录数据过大时，数据库的CRUD性能就会下降，一些常见的措施有：

限定数据的范围：务必禁止不带任何限制条件的查询语句。
读写分离：经典的数据库拆分方案，主库负责写，从库负责读。
垂直分区：数据表列的拆分，把一表多列拆分为多表，主键列不拆分，每个表都有。
优点：可以使数据列变小，在查询的时候，可以减少读取的Block次数，减少I/O次数，简化表的结构，易于维护。
缺点：主键会出现冗余，会出现冗余列，并会引起Join操作。此外，垂直分区会让事务变得更加复杂。
水平分区：保持数据库表的结构不变，通过某种策略存储数据分片（即行的拆分），这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑起非常大的数据量。
注意：单一数据表分表解决数据量大的问题，对于提升MySQL并发能力没有什么意义，所以水平拆分最好是分库。

分库分表之后id主键如何处理：生成全局id有以下几种方式

UUID：不适合做主键，因为太长了，并且无序，可读性不好，查询效率低，只适合用来作为唯一标识。
数据库自增id：两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的id有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
利用redis生成id：性能好，不依赖于数据库。但是引入新的组件，系统变得更复杂了，可用性降低，编码复杂，增加了系统成本。
美团的Leaf分布式ID生成系统：分布式ID生成器，需要依赖关系数据库，Zookeeper等中间件。

MySQL查询优化

单表查询优化

最佳左前缀法则。
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描,在where后使用计算、函数、(自动or手动)类型转换都会使索引失效。
存储引擎不能使用索引中范围条件右边的列，即在建立索引时，范围查询要放到最后。
MySQL在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描。
is not null 也无法使用索引,但是is null是可以使用索引的。
like以通配符开头(’%abc…’)MySQL索引失效会变成全表扫描的操作。
字符串不加单引号索引失效，JavaBean类型和MySQL字段的字符类型要一致，不然所以会失效。