MySQL

索引

什么数据结构

B+树

---

MySQL的索引失效有哪些场景？

1. 不符合最左匹配原则
2. 在索引上使用计算、函数、类型的转换
3. 使用了不等于
4. 使用了 is null 或 is not null
5. 使用了 like
6. 字符串不加单引号

InnoDB引擎为什么使用B+树？

（1）B+树空间利用率更高，可减少I/O次数

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。而因为B+树的内部节点只是作为索引使用，而不像B-树那样每个节点都需要存储硬盘指针。

也就是说：B+树中每个非叶节点没有指向某个关键字具体信息的指针，所以每一个节点可以存放更多的关键字数量，即一次性读入内存所需要查找的关键字也就越多，减少了I/O操作。
e.g.假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内 部结点需要2个盘快。而B+ 树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B 树就比B+ 树多一次盘块查找时间(在磁盘中是盘片旋转的时间)。

（2）增删文件（节点）时，效率更高

因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率，基于范围查询更好。

（3）B+树的查询效率更加稳定

因为B+树的每次查询过程中，都需要遍历从根节点到叶子节点的某条路径。所有关键字的查询路径长度相同，导致每一次查询的效率相当。

B-树的每个节点都有data域

---

为什么不适用 B- 树

B-树的每个节点都有data域，增加了 I/O 操作，而B+数除了叶子节点有数据其他没有

---

Innodb 和 myisam 分别适用于哪些场景

innodb 是一种事务性的存储引擎，一般的业务其实都是对可靠性有要求的，所以基本上都是用 innodb

而 myisam 是一种 olap 存储引擎，适用于读多写少的场景，比如像年度总结这种，我们只用来读取的数据，
我们就可以适用 myisam 作为存储引擎

为什么 myisam 在只读的情况下比 innodb 快

存储结构有区别，myisam 是非聚簇索引，它的索引存储的都是我们具体的记录行的地址，
而 innodb 是聚簇索引，它可能会出现回表查询，而且每次查询都需要去维护一个 mvcc 版本的情况，因此相对
而言会比较慢

有一亿条数据，走主键索引， mysql 底层 IO 次数

传统来看，如果是一个二叉树存了1亿条数据，是需要花费以2为底100000000的对数，向上取也就是27，也就是说如果以二叉树存储的话，获取一个节点最小可以1一次到，最多要27次，考虑到IO操作是非常消耗性能的，所以MySQL使用了B+树

一般来说，IO扫描一次磁盘块的大小是 4KB，而非叶子节点通常占用 16Byte，所以一个磁盘块可以存256个叶子节点
也就是说，不会像二叉树一样只能从左右两个节点进行判断，而是会通过 256 个节点判断，并且B+树的数据是存在叶子节点上的，所以速度非常稳定，所以花费的时间是 256 为底 1000000000 的对数，向上取也就是 4

原理

执行一条语句的整体流程

宏观上

一、通过连接器和 MySQL 建立连接

二、通过分析器、优化器、执行器

三、返回执行结果

功能

连接器：管理连接，权限认证

分析器：词法分析，语法分析

优化器：执行计划生成，索引选择

执行器：操作引擎，返回结果

微观上

一、客户端通过连接器权限认证后

二、查询缓存，命中则直接返回

三、通过分析器进行词法和语法的分析

四、优化器会根据一些成本的计算，去决定具体走哪个索引，或者连表的顺序，最终生成执行计划

五、执行器通过执行计划去调用存储引擎层的 API 接口

六、存储引擎层，它是一个可插拔的设计，不同的存储引擎会去实现一套统一的 API 接口，
因此可以自由的去更换我们的存储引擎，上层是无感知的

redolog、undolog、binlog

redoLog：属于物理日志，事务中修改的任何数据，将最新的数据备份存储的位置，被称为重做日志

undoLog：数据库事务开始之前，会将要修改的记录存放到undo日志里，当事务回滚时或数据库崩溃时，可以利用undo日志撤销未提交事务对数据库产生的影响

binLog：属于innodb，binlog是属于mysql server自带功能，帮助实现数据恢复达到主从数据一致性

调优

MySQL的调优看过吗？有调优经验吗？

确保命中索引，在复合索引的条件下符合最左匹配原则
分页优化：先通过覆盖索引查找，再使用join连接查询

select * from A where id in (select id from B) 
# 相当于：	A > B
for (select id from B) { //B的数据量少，所以循环次数少。
   select * from A where A.id = B.id
}

select * from A where exists (select 1 from B where B.id = A.id)  true / false
# 等价于 B > A
for (select * from A) {
   select * from B where B.id = A.id
}

---

如何发现慢SQL

万能的 explain 语句

---

解决脏数据、可重复读、幻读的方式

Read uncommitted（读未提交）： 在一个事务中读取到另一个事务还没有提交的数据

Read committed（读已提交）: 已经解决了脏读问题，在一个事务中只会读取另一个事务已提交的数据，这种情况会出现不可重复读的问题。就是：在事务中重复读数据，数据的内容是不一样的

Repeatable read（可重复读）：在一个事务中每次读取的数据都是一致的，不会出现脏读和不可重复读的问题。会出现虚读（幻读）的问题

怎么避免回表查询

覆盖索引，也就是 select 的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

锁

MySQL中都有哪些锁？

• 行锁
• 表锁

---

语法

基本语句，如何增加列？

ALTERTABLE table_name ADD column_name datatype

查询语句的执行顺序

1、FROM table1 left join table2 on 将table1和table2中的数据产生笛卡尔积，生成Temp1

2、JOIN table2 所以先是确定表，再确定关联条件

3、ON table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp2

4、WHERE 对中间表Temp2产生的结果进行过滤 产生中间表Temp3

5、GROUP BY 对中间表Temp3进行分组，产生中间表Temp4

6、HAVING 对分组后的记录进行聚合 产生中间表Temp5

7、SELECT 对中间表Temp5进行列筛选，产生中间表 Temp6

8、DISTINCT 对中间表 Temp6进行去重，产生中间表 Temp7

9、ORDER BY 对Temp7中的数据进行排序，产生中间表Temp8

10、LIMIT 对中间表Temp8进行分页，产生中间表Temp9