PHP面试宝典之Mysql数据库高级篇（一）

聚簇索引和非聚簇索引的区别？

聚簇索引：是索引结构和数据一起存放的索引，一个表只能有一个，存储记录是物理上的连续，在innodb表中如果有主键，那聚簇索引就等于主键索引

非聚簇索引：是索引结构和数据分开存放的索引，一个表可以存在多个，存储记录是逻辑上的连续，非聚簇索引就是普通索引

每张表都会有一个主键索引和随便几个普通索引，每个索引都会构建属于自己的索引树

索引树

主键索引树、二级索引树

主键索引树：叶子节点保存的是行数据

二级索引树：叶子节点保存的是索引值和主键ID

回表：就是通过二级索引，找到主键ID，再通过主键ID查找到行数据

索引结构？

二叉树，红黑树，哈希表，b+树

B+树和哈希的区别？

B+树：范围查询性能更优；
哈希：全值匹配效率更高如：= 和in等

为什么b+树是范围查询更优？

因为b+树的叶子节点，使用了指针顺序（链表）从小到大连接，树叶节点两两相连，增加区间访问性，只需要遍历叶子节点就可以实现整棵树的遍历，而b树的叶子节点是独立的，无法查找区间

mysql为什么使用b+树？不使用b树？

1：b树只适合随机检索；b+树同时支持随机检索和顺序检索

2：b+树空间利用率更高（b+树，索引节点不存数据，只存索引，可以存更多，索引树就变的更矮，减少了i/o次数）

3：b+树查询效率更稳定

4：b+树范围查询性能更优

5：b+树增删索引节点时，效率更高

慢sql该如何处理？

explain关键字

table：操作的表

type：访问类型，查询级别，最好ref，至少要range，最后是index，all

possible_keys：可能应用的索引

key：使用的索引

rows：查询行数

MYSQL分库分表？

垂直分表和水平分表

垂直分表：按字段、功能、重要程度拆分

水平分表：按数据逻辑拆分，如ID范围、ID奇偶数；
例：创建总表：保留ID、子表名、子表ID范围；创建子表，存储数据；查询时：先查总表，得到子表名，再去子表查询

为什么要分库分表？

分库：单库的存储能力（磁盘容量）是固定的，单库连接数是有限的；

分表：单条sql执行过长，单表行数过多；（单表超过500万行、容量超过2G，如果预计三年后达不到这个级别，创建表时就不需要分库分表）

mysql都有哪些日志类型？

redo log重做日志，保证持久性

undo log回滚日志，保证原子性

bin log保证一致性

MySQL隔离级别？

序列化、可重复读、提交读、未提交读

序列化：最高隔离级别，按先后顺序执行事务

可重复读：mysql默认隔离级别；在当前事务提交前，对同一字段的多次读取，结果都一致，除非数据是被自己修改（可能导致：幻读，事务中插入报错）

提交读：其他数据库默认级别；允许读取已经提交的数据（可能导致：不可重复读、幻读）

未提交读：可以读取到，其他尚未提交的数据变更（可能导致：脏读、不可重复读、幻读）；

脏读：正在执行的事务，读取到其他事务，未提交的数据

不可重复读：正在执行的事务，读取到其他事务，已提交的数据，导致前后不一

幻读：正在执行的事务，多次读取数据不变，但插入时，因其他事务的插入，造成插入失败

如何解决幻读问题？

1：在可重复读隔离级别下：
1）：快照读，通过mvcc避免幻读
2）：当前读，通过next-key（间隙锁+行级锁）避免幻读
2：使用序列化的隔离级别

什么是mvcc机制?

多版本 并发控制；是为了读取数据时，不加锁，提高读取效率和并发性；
解决的是：读写时的线程安全问题，线程不去争抢读写锁；

什么是当前读，快照读？

快照读：普通select，在读写时不加锁，但可能会读到历史数据，基于mvcc

当前读：悲观锁实现，读取时对数据加锁，读到的是最新数据；

提交读、可重复读这两种隔离机制，使用的都是快照读，基于mvcc实现

mvcc是如何实现提交读、可重复读的隔离级别的？

提交读：rc；在每次读取时，都会获得最新的 readview（事务修改前，事务提交后）

可重复读：rr；在事务执行之前读取一个 readview，之后每次读取的都是同一个，所以每次结果都一样

什么是readview？

我叫它 可读视图；当我们读取数据时，会有多个版本的数据，我们需要哪个版本并不知道，这时，readview就会根据条件，返回适合我们读取的版本数据；

什么是间隙锁？

使用范围查询，并请求共享锁或排它锁时，innodb会给符合条件的已有数据加锁，对于键值在条件范围内，但又不存在的记录，叫做间隙；例：ID：1～5，其中2、3、4被删除了不存在，但如果查询范围小于5时，被删除的数据，所在的记录就是间隙锁

事务的四大特性ACID?

原子性、一致性、隔离性、持续性

原子性：要么都做，要么都不做（undolog实现）

一致性：要么都成功，要么都失败（undolog、redolog、隔离性共同实现）

隔离性：并发情况下，事务数据不冲突，互不干涉（当前读、加锁，或mvcc快照读实现）

持续性：事务一旦提交，对数据库的改变是永久性的（redolog实现）

锁是为了解决什么问题？

锁是为了解决事务的隔离性问题，为了使事务之间互不影响，每个事务操作时都会对数据加锁，防止其他事务同时操作数据

锁是基于什么实现的？

锁是基于索引实现的，如果sql命中了锁，那锁住的就是索引所在节点（行级锁），如果没有命中索引，锁住的就是整个索引树（表级锁）

乐观锁、悲观锁？

乐观锁：认为操作无害，不会导致数据冲突；可以通过代码逻辑实现
实现：新增版本号字段，更新语句时：where子句中，当前版本号做为条件，更新字段中，当前版本号加一

悲观锁：认为操作有害，会导致数据冲突，所以每次操作都要先获取锁再进行操作
实现：共享锁、排它锁

共享锁：允许多事务同时查询当前数据
排它锁：只能由一个事务操作当前数据

注：详情可参考悲观锁、乐观锁

什么是死锁？

所谓死锁，是两个或两个以上进程，在执行过程中，由于竞争资源或彼此通信，造成的一种阻塞现象，如果没有外力作用，它们将无法继续执行，这就是死锁

出现死锁如何解决？

超时机制、死锁检测

超时机制：设置事务超时时间

死锁检测：等待图，判断事务是否存在回路，若存在则回滚最小事务

死锁场景及处理方案？

1：逻辑冲突，两个事务同时操作关联的a、b两表，但修改顺序交叉

2：并发修改同一记录（悲观锁、乐观锁）

3：索引使用不当，导致全表扫描

4：事务运行时间过长，操作范围过大

5：网络问题导致死锁

解决死锁：
1：使用乐观锁

2：事务中关联表的修改，前后顺序保持一致

3：减少事务的运行时间和修改范围

事务的底层是如何实现的？

通过dbms数据库管理系统实现的；
1：日志系统：是基础；记录所有数据库操作的详细信息，包括事务开始和结束的时间、操作类型、数据变化等，系统崩溃也可以使用日志恢复

2：读写锁定机制：避免数据冲突，通过读锁多事务共享，通过写锁，由一个事务独占

3：事务管理器：是核心；负责管理事务的生命周期，包括开始、提交、回滚等操作。当事务开始时，分配一个唯一标识，用于在日志系统中标记，当事务提交或回滚时，事务管理器将相关信息记录到日志中，并释放所有相关资源，包括读写锁，数据库连接等等；

4：内存缓存：为了提高数据库性能，使用内存缓存频繁访问的数据和索引，在事务中，缓存的更新会被缓存到内存中，直到事务提交时，才会写到磁盘中的数据库文件

5：数据库引擎：核心组件之一，负责管理数据的存储和检索。数据库引擎需要支持，原子性、一致性、隔离性和持久性，确保事务的所有操作要么全部成功，要么全部回滚到原始状态

事distinct 和 group by的区别

1：distinct 是为了字段去重，在select语句后，针对的是主句中所有字段
2：group by是对结果集进行分组，跟在where条件后，针对的是一个或对个列
3：更推荐group by，原因：语义更清晰，可以对数据进行更复杂的处理，更灵活
4：在语义相同，有索引的情况下：group by和distinct都能使用索引，效率相同。
5：在语义相同，无索引的情况下：distinct效率高于group by。原因是distinct 和 group by都会进行分组操作，但group by可能会进行排序，触发filesort，导致sql执行效率低下。（还有种说法加不加索引group by都比distinct更快，未印证）