Mysql 常见面试题
Mysql 常见面试题
1.索引
1.什么是索引？
索引是对数据库中一个或者多个值进行排序的数据结构，以便实现快速查询
2.Mysql索引是如何实现的？
mysql索引使用的是B+树，主要是查询效率高，时间复杂度是O(log N),可以充分利用磁盘预读的特性，树可以有多个孩子节点，并且树深度很小，叶子节点有序且存储数据
3.为什么选择B+树作为索引结构？
- hash索引：hash索引的底层是哈希表，因为hash表是存储key和value键值对的，所以区间查询无法通过hash索引实现，需要做全表扫描，所以hash索引只适用于等值查询的场景，而B+树是一种多路平衡查找树，节点是天然有序的(左孩子小于父节点，右孩子大于父节点)，对于范围查询的时候不需要做全表扫描
- 二叉查找树：解决了排序的基本问题，但是无法保证平衡，可能会退化为链表
- 平衡二叉树：通过旋转操作解决了平衡问题，但是旋转操作效率太低
- 红黑树：通过舍弃严格的平衡和引入红黑节点，解决了平衡二叉树旋转效率太低的问题，但在磁盘的场景下，树的高度还是太高了，IO次数太多
- B树：非叶子节点会存储数据，内存占用较高
- B+树：在B树的基础上，将非叶节点改造为不存储数据的纯索引节点，进一步降低了树的高度，此外，将叶节点使用指针连接成链表，范围查询更加高效
4.mysql为什么加了索引可以加快查询？
因为使用索引后可以不用扫描整个表来定位某行数据，可以先通过索引表找到改行对应的物理地址然后访问相应数据
5.索引的优点和缺点
优点：
- 可以加快检索数据的速度，减少IO次数
- 可以加快数据的分组与排序操作
- 可以加速表和表之间的连接，因为扫描整个表的索引消耗的时间远远比扫描整个表的时间要少
缺点：
- 空间方面：索引本身也是表，会占用存储空间，索引占用的空间是数据表的1.5倍
- 时间方面：创建和维护索引都需要时间，数据量越多，消耗的时间也越多，对数据进行增删改查都需要动态维护索引
6.mysq的索引主要有哪些？
- 唯一索引：不允许出现相同的值，可以是NULL
- 普通索引：允许出现相同值
- 主键索引：不允许出现相同值，不可以是NULL
- 全文索引：针对值中的某个单词查询，解决海量数据模糊查询的问题
- 组合索引：实际上是将多个字段建到一个索引里，列值的组合必须唯一
7.使用组合索引有什么好处？
- 空间开销更小：建立一个N字段的组合索引，功能相当于建立了N个索引，但开销会小很多
- 覆盖索引：这样有些查询仅通过索引就能获得结果，不必再从表中获取，称之为实现了索引覆盖，是提升性能的优化手段之一
- 效率更高：索引列越多，通过索引筛选出数据更快
8.mysql组合索引最左匹配原则
最左匹配原则：即最左优先，在检索数据时，从组合索引的最左边开始匹配，然后一直向右匹配，直到遇到范围查询(即<,>,between,like)就停止匹配
比如a=3 and b=4 and c>5 and d=6，abcd建立组合索引，d就用不到索引了，因为c>5是范围查询，根据最左匹配原则，在c时就停止匹配所以d就用不到索引了
9.什么叫做索引失效？什么情况下会失效？如何判断是否失效？
索引失效：检索时因为某些特别操作导致用不上索引，称之为索引失效
什么情况下索引会失效？
-
对索引字段做任何计算，函数操作
-
SELECT * FROM user WHERE age = age+1
-
-
对索引列做范围性操作，比如>,<,!=
-
使用like通配符，并且%在数据左侧时,注意,%在数据右侧时索引并不会失效
-
SELECT * FROM user WHERE name like "%小"
-
-
查询中存在类型转换时
-
违法最左前缀法则时
-
where对null判断
-
or操作至少有一个字段没有索引
判断索引是否失效：explain命令执行sql语句，type为all代表失效
-
失效：
-
有效：
7.聚集索引和非聚集索引的区别？
-
聚集索引：决定表中数据的物理顺序，逻辑顺序和物理顺序是一样的，一张表只能有一个，聚集索引对范围查找特别有效，因为其数据是物理相邻的，可以加快范围查找的速度
-
非聚集索引：决定表中数据的逻辑顺序，但和其记录的物理顺序不一定一致
聚集索引的叶子节点是数据节点，非聚集索引的叶子节点仍是索引节点，通过一个指针再指向数据块
8.什么字段适合创建索引？
- 经常做查询的字段
- 经常做表连接的字段
- 经常需要排序，分组或过滤的字段，即出现在order by，group by，discinct
2.事务
1.什么叫事务？
一系列操作的集合，要么都执行，要么都不执行
2.事务有什么特性？
ACID特性
- 原子性：事务是一个原子的操作单元，其对数据的修改，要么全都执行，要么全都不执行
- 一致性：事物把系统从一个一致状态转变为另外一个一致状态，即事物完成后，所有数据必须符合业务规范
- 隔离性：多个事物并发执行时，一个事物的执行不应该影响其他事物的执行，即事物以相互隔离的方式执行，事物以外的实体无法知道事物过程的中间状态，防止数据损坏
- 持久性：事物提交以后，事物必须以一种持久性方式存储起来，一旦事物完成，无论发生什么系统错误，它的结果都不应该受到影响，这样就能从任何系统崩溃中恢复过来，通常情况下，事物结果被写到持久化存储器中
3.事务隔离的级别有哪几种？
隔离级别：一个session中的事务可能对另一个session中的事务的影响
总共有四个隔离级别
- 读未提交：一个事务可以读取其他事务未提交的数据，最低级别，存在脏读问题
- 读已提交：一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生，但不能避免不可重复读问题
- 可重复读：在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读问题，但不能避免幻读问题
- 串行：事务串行执行，可避免脏读，不可重复读，幻读，但效率比较低
mysql的默认隔离级别是可重复读，隔离级别越高，并发越低
4.事务的并发可能会出现什么问题？
- 丢失更新：A事务的更新覆盖了B事务的更新
- 脏读：A事务读取了B事务写但未提交的数据
- 不可重复读：同样条件下两次读取结果不同，也就是两次读取期间，数据被其他事务篡改了
- 幻读：同样条件下读取出的记录数量不一样，新增或减少了一些记录
5.如何解决幻读问题？
- 方法1：将事务的隔离级别设置成串化，但效率太低
- 方法2：通过next-key lock
- 即在当前读事务开启时，给涉及到的行加写锁，防止写操作，给涉及到的行两端加Gap lock间隙锁防止新增的行写入
- 但是next-key lock在并发情况下经常造成死锁问题，死锁的检测和处理也会花费时间，一定程度上影响并发量
实际上很多项目实际上不会用到上面两种方法，串化性能差，有的时候其实幻读问题是可以接受的
