MySQL杂记



最近在复习 MySQL，感觉知识点很零散，顺便巩固总结下相关的知识。

1.事务四个特性：

面试常客，再基础不过

• A 原子性（Atomicity）
• C 一致性（Consistency）
• I 隔离性（Isolation）
• D 持久性（Durability）

  2.并发事务带来的问题（要理解每个概念）

1. 脏读
2. 丢失修改
3. 不可重复读
4. 幻读

3.事务隔离级别有哪些

1. READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
2. READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
3. REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。（MySQL默认的事务隔离级别）
4. SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

   3.MyISAM和InnoDB区别

   MyISAM是MySQL的默认数据库引擎（5.5版之前）。但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃回复问题的话）。

二者区别：

1. MyISAM 不支持外键，而 InnoDB 支持
2. MyISAM 是非事务安全型的，而 InnoDB 是事务安全型的。
3. MyISAM 锁的粒度是表级，而 InnoDB 支持行级锁定。
4. MyISAM 支持全文类型索引，而 InnoDB 不支持全文索引。
5. MyISAM 相对简单，所以在效率上要优于 InnoDB，小型应用可以考虑使用 MyISAM。
6. MyISAM 表是保存成文件的形式，在跨平台的数据转移中使用 MyISAM 存储会省去不少的麻烦。
7. InnoDB 表比 MyISAM 表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。

锁的区别:

• InnoDB(默认) ：事务优先 （适合高并发操作；行锁）
• MyISAM ：性能优先 （表锁）

应用场景:

• MyISAM 管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的 SELECT 查询，那么 MyISAM 是更好的选择。
• InnoDB 用于事务处理应用程序，具有众多特性，包括 ACID 事务支持。如果应用中需要执行大量的 INSERT 或 UPDATE 操作，则应该使用 InnoDB，这样可以提高多用户并发操作的性能。

4.B树和B+树有什么区别？为什么索引不用B树？

B-树

是一种平衡的多路查找(又称排序)树，就是B树，在文件系统中有所应用。主要用作文件的索引。其中的B就表示平衡(Balance)

• B-树 ：

  1. 定义任意非叶子结点最多只有M个儿子；且M>2；

2. 根结点的儿子数为[2, M]；

3. 除根结点以外的非叶子结点的儿子数为[M/2, M]；

   4. 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）

4. 非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；

5. 所有叶子结点位于同一层；
   （图片来自网上，侵删）
   
   B-树的搜索：从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；其搜索的特点：

• 关键字集合分布在整颗树中；

• 任何一个关键字出现且只出现在一个结点中；

• 搜索有可能在非叶子结点结束；

• 其搜索性能等价于在关键字全集内做一次二分查找；

• 由于限制了除根结点以外的非叶子结点，至少含有M/2个儿子，确保了结点的至少利用率，其最低搜索性能为lgN；

• B-树的性能总是等价于二分查找（与M值无关），没有B树平衡的问题；

B+树

性质：B+树是B-树的变体，也是一种多路搜索树：

1. 其定义基本与B-树同，有以下不同点：

2. 非叶子结点的子树指针与关键字个数相同；

3. 非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；

4. 为所有叶子结点增加一个链指针；

5. 所有关键字都在叶子结点出现；

B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在

非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；
（图片来自网上，侵删）

B+的特性：

1. 所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；

   2. 不可能在非叶子结点命中；

2. 非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；

   4. 更适合文件索引系统；

5.数据库的索引有什么作用？

优点：

1. 提高查询效率（降低IO使用率）
2. 降低CPU使用率 （…order by age desc，因为 B树索引 本身就是一个 好排序的结构，因此在排序时 可以直接使用）

缺点：

• 索引本身很大， 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的；
• 索引在以下情况不适用： a. 少量数据，b.频繁更新的字段，c.很少使用的字段
• 索引会降低增删改的效率；MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息。

6.乐观锁和悲观锁区别：

• 乐观锁：乐观锁的特点先进行业务操作，不到万不得已不去拿锁。即“乐观”的认为拿锁多半是会成功的，因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。乐观锁是否在事务中其实是无所谓的。

• 悲观锁：悲观锁的特点是先获取锁，再进行业务操作，即“悲观”的认为获取锁是非常有可能失败的，因此要先确保获取锁成功再进行业务操作。通常来讲在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁。当数据库执行select for update时会获取被select中的数据行的行锁，因此其他并发执行的select for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

区别：
乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。
乐观锁还适用于一些比较特殊的场景，例如在业务操作过程中无法和数据库保持连接等悲观锁无法适用的地方。

7.一条sql语句在mysql中如何执行的

• MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。
• 引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
• 查询语句的执行流程如下：权限校验（如果命中缓存）—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎
• 更新语句执行流程如下：分析器—-》权限校验—-》执行器—》引擎—redo log(prepare 状态—》binlog—》redo log(commit状态)
  参考一条sql语句在mysql中如何执行的