知识整理（三）：Mysql 数据库知识

一、Mysql数据库知识

1. MYISAM和InnoDB的区别？

   答：MYISAM不支持事务和行级锁，而且其最大的缺陷在于数据库崩溃后无法安全恢复。

2. 事务的四大特性

   答：ACID

   ① 原子性：事务是最小执行单元，要么全部成功，要么全部失败。
   ② 一致性：事务改变的数据，前后要一致。
   ③ 隔离性：并发操作时，当一个事务在执行时，外界无法操作它。
   ④ 持久性：一旦一个事务执行成功，它对数据库的操作是永久性的，即使数据库发生崩溃，数据也不会改变。

3. 并发事务会带来哪些问题？

   答：
   ① 脏读：当事务A在对某条数据做修改时，还没有保存到数据库中，恰好此时事务B读取了这条数据，这就是脏数据，以脏数据为标准做的操作可能并不正确。
   ② 丢失修改：当事务A对数据A(值为20)进行A=A-1操作，还没存入数据库时，事务B也对数据A(值为20)进行A=A-1操作，然后事务A和事务B相继保存到数据库，最终数据A的值为19，事务A的操作丢失了。
   ③ 不可重复读：当事务A在对数据B(值为100)进行每次递减1的操作时，事务B开始读取数据B的值，结果发现每次读到的值并不一样，这就是不可重复读。
    ④ 幻读：当事务A在新增数据时，事务B在查询，结果发现每次查询的数据都会多出来几条，以为自己出现了幻觉，这就是幻读。

   注：不可重复读和幻读很相似，但是区别在于：不可重复读重点在于关注单条数据的变化，而幻读的重点在于关注突然新增或减少了几条数据。

4. 事务隔离级别有哪些？

   答：
   ① 读 - 未提交：最低的隔离级别，可以读取未提交的数据，可能会引起脏读、不可重复读和幻读。
   ② 读 - 已提交：可以读取已经提交的数据，可能会引起不可重复读和幻读。
   ③ 可重复读：在一个事务内，多次读取同一个字段，其值始终一样，除非该事务自己修改了字段的值，可能会引起幻读。
   ④ 串行化：最高的隔离级别，完全遵循ACID原则，可以防止脏读、不可重复读和幻读。

   注：隔离级别越低，请求的锁就越少，性能也就越快。

5. MySQL的默认隔离级别是?

   答：

   通过命令：

  SELECT @@tx_isolation;

   可知，是可重复读。

   注1：与SQL标准不同的是，InnoDB虽然是可重复读的隔离级别，但是其采用了Next-key Lock算法，可以避免幻读的出现，即达到了SQL的串行化隔离级别。

   注2：InnoDB存储引擎在分布式事务中一般会采用串行化的隔离级别。

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

   答：MYISAM默认使用表级锁。InnoDB默认使用行级锁，也支持表级锁。

7. 大表怎么优化？

   答：
   ① 限定数据范围：比如我们查订单表的时候，限制必须传入查询条件，如只能查询近一个月的订单。
   ② 读写分离：经典的方案，主库负责写，从库负责读。
   ③ 垂直分区：比如，用户表可分为用户信息表和用户登录表。
   ④ 水平分区：利用Mycat等一些中间件做集群。

8. Mysql的基本架构

   答：包含Server层和存储引擎。

   Sever层包括：

• 连接器 ：验证用户身份，鉴权等
• 查询缓存：执行查询语句时，先查缓存。但不太实用，Mysql 8.0后已去除。
• 分析器：分析SQL语句
• 优化器：选择最优的执行SQL语句的方式
• 执行器：调用存储引擎接口执行SQL
• binlog 日志模块

   存储引擎：负责数据存储和读取。采用可替换的插件式架构，支持MYISAM、InnoDB、Memory等多种存储引擎，InnoDB自带一个redolog日志模块。

9. 一条SQL的执行过程

   答：分两种情况：查询的SQL和增、改、删的SQL。

• 查询的SQL：权限校验 → 分析器 → 优化器 → 权限校验 → 执行器 → 存储引擎
• 增、改、删的SQL：分析器 → 权限校验 → 执行器 → 存储引擎 → redolog prepare → binlog → redolog commit

10. 索引的优点那么多，为什么不对每一个字段都创建索引呢？

   答：
   ① 当对表中的索引字段进行修改时，索引也要动态的维护。
   ② 索引会占用物理空间。
   ③ 创建索引和维护索引需要时间，这个时间会随着数据量的增加而增加。

11. 使用索引的注意事项

   答：
   ① 索引加在经常作为搜索内容的字段上，可以加快搜索速度。
   ② 索引加在WHERE字句的搜索条件字段上，可以加快条件的判断速度。
   ③ 索引加在经常需要排序的字段上，可以加快排序速度，因为索引自带排序。
   ④ 索引加在经常需要连接其他表的外键字段上，可以加快连接速度。
   ⑤ 删除长期未使用的索引。空置的索引也会造成性能的损耗，具体可以查询sys 库的 chema_unused_indexes 视图来查询哪些索引从未被使用
   ⑥ 在使用limit offset时添加索引，可以提升速度。

12. Mysql索引主要使用的两种数据结构

   答：哈希索引和BTree索引。

   哈希索引主要用于查询单条记录，性能最佳，其余场景建议选用BTree索引。

   根据存储引擎的不同，MYISAM使用的是BTree索引中的非聚簇索引，其特点是索引和数据分离。

   而InnoDB使用的是聚簇索引(分为主索引和辅助索引),其索引和数据本就是一体。使用主索引查询时，可以直接根据key获得数据。根据辅助索引查询时，需要先取出主键的值，再走一遍主索引，称为“回表”。

13. 什么是覆盖索引？

   答：索引的字段和要查询的字段完全一致，就是覆盖索引，因为不用回表，所以它的速度很快。如下：

  SELECT username, age FROM t_user WHERE username = 'wxj' 
  AND age = 26 

   username和age字段都是索引字段，而且查询的内容也是它俩，所以这个是覆盖索引。

14. Mysql的基本储存结构

   答：

• 各个数据页会组成一个双向链表
• 每个数据页中的记录又会组成一个单向链表

15. 使用索引为什么会加快速度？

   答：
   当执行一条没有加过索引的查询SQL时，

  SELECT * FROM t_user WHERE username = 'wxj'

   具体过程如下：
   ① 遍历双链表，找到该条记录所在的数据页。
   ② 遍历单链表，找到此数据页中的该条记录。

   使用索引后，我们不需要遍历双链表，只用通过索引这个“目录”，就能快速地定位到该数据页。

   索引就是将无序变得有序了。

16. 索引的最左前缀原则

   答：Mysql中的索引可以引用多个，如index_name(name,city)，这就是一个联合索引。

   最左前缀原则是指，在使用联合索引作为条件查询时，只有查询条件精确匹配到左边的一列或多列时，联合索引才生效。

   举例如下：

  这是一个联合索引：index_name(username, age)

  // 可以命中索引
  SELECT * FROM t_user WHERE username = 'wxj' AND age = 26

  // 可以命中索引
  SELECT * FROM t_user WHERE username = 'wxj'

  // 可以命中索引
  SELECT * FROM t_user WHERE age = 26 AND username = 'wxj'
  
  // 无法命中索引
  SELECT * FROM t_user WHERE age = 26