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

原理

执行一条语句的整体流程

宏观上

1. 通过连接器和 MySQL 建立连接
2. 通过分析器、优化器、执行器
3. 返回执行结果

功能

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

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

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

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

微观上

1. 客户端通过连接器权限认证后
2. 查询缓存，命中则直接返回
3. 通过分析器进行词法和语法的分析
4. 优化器会根据一些成本的计算，去决定具体走哪个索引，或者连表的顺序，最终生成执行计划
5. 执行器通过执行计划去调用存储引擎层的 API 接口
6. 存储引擎层，它是一个可插拔的设计，不同的存储引擎会去实现一套统一的 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 语句

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

1. Read uncommitted（读未提交）： 在一个事务中读取到另一个事务还没有提交的数据
2. Read committed（读已提交）: 已经解决了脏读问题，在一个事务中只会读取另一个事务已提交的数据，这种情况会出现不可重复读的问题。就是：在事务中重复读数据，数据的内容是不一样的
3. 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