MySQL【高级】学习笔记

1.MySQL逻辑架构

1.1基本架构

典型C/S架构

• 连接层
• 服务层

SQL接口/解析器/查询优化器/查询缓存（8.0删除）

• 引擎层

1.2 SQL执行流程

• 查询缓存阶段

Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没
有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃 了这个功能。

• 解析器处理

在解析器中对 SQL 语句进行语法分析、语义分析。

词法分析：你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。
语法分析：根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。
如果SQL语句正确，则会生成一个这样的语法树：

• 优化器处理

在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引检索等。

• 执行器处理

在执行之前需要判断该用户是否具备权限 。如果没有，就会返回权限错误。如果具备权限，就执行 SQL 查询并返回结果。在 MySQL8.0以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

1.3 SQL语法顺序

2. 数据缓冲池

为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请 占用内存来作为 数据缓冲池 ，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才可以访 问。可以让磁盘活动最小化，从而 减少与磁盘直接进行 I/O 的时间。

2.1 缓存的原则：

“ 位置 * 频次 ”这个原则，可以帮我们对 I/O 访问效率进行优化。 首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。
其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有 200G，但是内存只有 16G，缓冲 池大小只有
1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会 优先对使用频次高 的热数据进行加载 。

2.2缓冲池的预读特性

预读是Innodb 通过在缓冲池中提前读取多个数据页来优化 I/O
的一种方式。因为磁盘读写的时候，是按照页的方式来读取的（你可以理解为固定大小的数据，例如一页数据为
16K），每次至少读入一页的数据，如果下次读取的数据就在页中，就不用再去磁盘上读取了，从而减少了磁盘 I/O。

	缓冲池和查询缓存是一个东西吗？不是。
	缓冲池存放的是各种经常被使用的数据，而查询缓存的是查询结果

3. 存储引擎

存储隐形就是指表的类型，曾经叫表处理器，功能为接受上层传下来的指令，然后对表中的数据进行提取或写入操作。

3.1 InnoDB：具备外键支持功能的事务存储引擎

• 默认的事务型引擎，被设计用来处理大量的短期事务，可以确保事务的完整commit和rollback
• 除了增加和查询外还需要更新和删除，推荐InnoDB
• 为处理巨大数据量而设计
• 支持行锁，适合并发场景下的操作
• 处理效率差，数据量小不需要事务时最好用MyISAM
• 内存要求高，索引即数据

3.2 MyISAM：主要的非事务处理存储引擎

• 不支持、行级锁、外键，崩溃后无法安全恢复
• 5.5之前的默认引擎
• 访问速度快，对事务完整性没要求或以插入查询为主时用MyISAM
• 针对数据统计有额外常数存储，count效率高
• 数据索引分开存储

3.3 Archive引擎：用于数据归档

• 仅支持插入和查询
• 5.5后支持索引功能
• 拥有很好的压缩机，使用zlib压缩裤
• 采用行锁
• 适合存储大量的独立的作为历史记录的数据，拥有很高的插入速度

3.4 Blackhole引擎：丢弃写操作，读操作会返回空内容

• 丢弃所有插入的数据
• 会记录日志，可用于复制数据到备库或简单的记录日志

3.5 CSV引擎：存储数据时，以逗号分隔各个数据项

• 可将CSV文件作为MySQL表处理
• 可作为数据交换的机制
• 不支持索引

3.6 Memory：置于内存的表

• 逻辑介质为内存，响应快，但当mysqld守护进程崩溃时数据会丢失
• 要求存储的数据长度不变—>Blob和Text不可用
• 同时支持Hash和B+树索引
• 适合目标数据小、临时数据、可以丢失的数据等场景

3.7 Federated引擎：访问远程表

3.8 Merge引擎：管理多个MyISAM表的集合

3.9 NDB引擎：MySQL集群专用存储引擎

4. 索引的数据结构

4.1 为什么使用索引

用于快速找到数据记录的一种数据结构，目的减少磁盘I/O次数

4.2 索引及其优缺点

• 索引是在存储引擎中实现的帮助MySQL高校获取数据的数据结构，简单理解为排好序的快速查找数据结构
• 优点：
  - 降低I/O次数
  - 通过唯一索引实现数据唯一性
  - 加速表间连接
  - 减少分组和排序的时间
• 缺点：
  - 创建和维护索引耗费时间
  - 索引占磁盘空间
  - 降低更新表的速度-

4.3 常见索引概念

• 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在了叶子节点），所谓的索引即数据，数据即索引。
优点：1.数据访问更快2.对于主键的排序查找和范围查找速度快3.节省io
缺点：1.插入速度依赖于插入顺序2.更新主键代价很高3.二级索引访问需要两次索引查找

• 二级索引

按照其他字段查找时，需要用到二级索引，根据其他字段建立若干B+树，在查找时先根据对应B+树查找到对应的主键值，再进行回表操作。.

• 联合索引

基于多个列建立索引，本质上也是二级索引

5. InnoDB数据存储结构

5.1 数据库的存储结构：页

• 磁盘与内存交互基本单位：页，默认大小为16kb
• 页的上层结构：区、段、表空间

5.2 页的内部结构

• 文件头和文件尾

文件通用部分。文件头描述了页的通用信息，如页号、页类型、上下页、校验和（类似页的hash值）、日志序列位置；文件尾用来校验页的完整性，包含校验和（类似页的hash值）、日志序列位置。

• 记录部分

最大最小记录；用户记录；空闲空间

• 页目录和页头

将所有记录分成几个组，页目录用来存储魅族最后一条记录的地址偏移量，用来做二分查找；页面头部储存各种状态信息

5.3 InnoDB行格式

• compact

记录的真实数据+记录的额外信息（变长字段长度列表/NULL值列表/记录头信息）

• dynamic（默认）与compressed

行溢出：一个页最多16kb但varchar可以达到65533个字节，这种情况下compact和redundan中只存储一部分数据，剩余部分分页存储然后真实数据出用20个字节记录这些页的地址；而compressed和dynamic中采用完全溢出，只记录地址值

• redundan

记录的真实数据+记录的额外信息（字段长度偏移列表/记录头信息）

6. 索引的创建与设计原则

6.1 索引创建

• 创建表时，隐式创建（主键、唯一、外键）
• 创建普通索引

index idx_name(col_name)

• 查看索引

show index from table_name

• 性能分析工具

explain+sql语句

• 创建唯一索引

unique index

• 建表后

CREATE INDEX idx_name ON table_name(cik_name)

6.2 删除索引

ALTER TABLE table_name DROP INDEX idx_name
或者
DROP INDEX idx_name on table_name 

6.3 适合添加索引的情况

• 有唯一性的字段
• 经常用where查询的字段
• 经常group by 和 order by 的字段
• update、delete的where中出现的字段
• distinct字段
• 多表join连接操作时，创建索引注意事项

1.表数量不要超过三张
2.对where条件创建索引
3.对连接的字段创建索引，且类型必须一致
-使用列的类型小的字段

• 使用字符串前缀创建索引
• 区分度高（散列性高）的列适合创建索引
• 使用最频繁的列放在联合索引左侧
• 在多个字段都要创建索引时，优先使用联合索引

6.4 不适合索引的情况

• where中用不到的字段
• 数据量小的表
• 有大量重复数据的列上
• 经常更新的表不要创建过多索引
• 不建议用无序的值做索引
• 不定义冗余的索引

7. 索引优化与查询优化

sql查询优化大方向上分为物理查询优化和逻辑查询优化
1.物理查询优化----索引、表连接方式
2.逻辑查询优化----sql等价变换

7.1 索引失效的情况

• 全值匹配
• 最佳左前缀
• 主键插入顺序
• 计算、函数、类型转换导致索引失效
• 范围条件右侧的列索引失效
• 不等于索引失效
• is null可以使用索引；is not null不可以使用索引
• like通配符以%开头索引失效
• OR前后存在非索引的列，索引失效
• 字符集不统一时，转换使索引失效

7.2 连接查询优化

• 作为内连接来说，优化器可以决定谁作为驱动表
• 作为内连接来说，如果只有一个字段有索引，那么优化器将其作为被驱动表
• 作为内连接来说，如果两个字段都有索引（或都没有索引），那么优化器将数据量少的作为驱动表

JOIN的底层原理
1.简单嵌套循环连接
2.索引嵌套循环连接
3.快嵌套循环连接
4.哈希

7.3 子查询优化

尽量使用多表连接不用子查询

7.4 排序优化

1.避免在ORDER BY语句中使用filesort排序
2.若where和order by中相同则使用单列索引，不同则用联合索引
3.无法使用index则对filesort方法进行调优

• 不加limit导致索引失效，二级索引需要回表，开销大于直接查再排序（不需回表时索引可用）
• 排序规则不一致索引失效
• filesort

1.双路排序
先取行指针与排序字段进行排序，再取其他字段
2.单路排序
直接取全部进行排序，空间换时间（order by尽量不要select *，使空间尽可能小）

7.5 GROUP BY优化

与order by相似

7.6 分页查询优化

7.7 覆盖索引

优点：
不需要回表
将随机IO变为顺序IO
缺点：
索引字段的维护

7.8 索引条件下推（ICP）

过滤条件中有索引中的字段，但索引失效，通过ICP在回表之前进行过滤

使用条件：

1. range、ref、eq_ref、ref_or_null
2. 仅用于二级索引
3. 覆盖索引不使用ICP
4. 相关子查询条件不适用ICP

7.9其他

• EXISTS和IN选择标准

选择标准为小表驱动大表

• COUNT（*/1/具体字段）区别

*和1本质没区别, MyISAM复杂度O(1) InnnoDB复杂度O(n)
具体字段尽量使用二级索引，聚簇索引包含信息多，占用空间大，多个二级索引靠key_len选择

• SELECT(*)尽量不要用

1.解析时需要查询数据字典
2.无法使用覆盖索引

• LIMIT 1

针对全表扫描的SQL，确定结果只有一条时可加，唯一索引时不加

• COMMIT

释放资源：

1. 回滚段上恢复数据的信息
2. 被程序获得的锁
3. redo/undo logo buffer中的空间
4. 管理上述的资源花费

7.10淘宝主键设计

自增id的问题
1.可靠性不高，自增id回溯
2.安全性不高，猜测数据库信息，如用户数量
3.性能差，需要在服务端生成
4.交互多，业务需要额外获取刚才插入的自增值
5.局部唯一性

8. 数据库设计规范

8.1范式

范式的优点：有助于消除数据冗余
范式的缺点：可能降低查询效率（可能需要关联多张表、索引失效）

• 第一范式

确保数据表中的每个字段的值必须具有原子性，即不可再拆分

• 第二范式

在第一范式基础上，满足每一条记录都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。确保了每张表表示一个意思。

• 第三范式

在一二基础上，确保每个非主键字段与主键字段直接关联，要求数据表中的所有非主键字段不能依赖于其他非主键字段，必须相互独立

8.2 设计原则

• 数据表个数越少越好，方便理解方便操作
• 字段越少越好，字段越多冗余可能越大
• 联合主键字段个数越少越好，索引空间大
• 使用主键和外键（关系）越多越好，这种关系多证明冗余度低

8.3 数据库调优措施

• 调优目标:

1.占用资源少，吞吐量更大
2.相应速度更快

• 如何定位问题：

1.用户反馈
2.日志分析

• 调优维度和步骤

1.选择合适的DBMS
2.优化表的设计
3.优化逻辑查询（sql重新）
4.优化物理查询（索引）
5.使用缓存
6.库级优化

• 优化数据库结构

1.拆分表：冷热数据（字段）分离，可减少磁盘I/O，更有效的利用缓存
2.增加中间表：针对联合查询
3.增加冗余字段：反范式化
4.优化数据类型：使用存储需要小的
5.优化插入记录的速度
6.使用非空约束，计算时省去判断非空的开销，创建索引时节省存储空间

8.4 大表优化

• 限定查询范围
• 读写分离
• 分库分表

垂直分库：按功能将不同库分在不同主机上
垂直分表：把经常一起使用的列放在一张表里
水平拆分

9.事务

9.1 ACID特性

• 原子性（atomicity）

事务不可分割，要么全部提交要么全部回滚

• 一致性（consistency）

事务执行前后，数据从一个合法状态转换到另一个合法状态

• 隔离性（isolation）

事务的执行不能被其他事务干扰，事物内部的操作以及实用的数据对其他的并发事务是隔离的

• 持久性（durability）

事务一旦提交改变是永久性的