MySQL学习笔记

数据库软件架构

1.分层架构 (mvc model view controller 分层)
2.事件驱动架构（event==>event channel => processor）
3.管道-过滤架构 （source => filter => filter => sink ）
4.微核架构（plugin1、plugin2、CoreSystem、plugin3、plugin4）

存储引擎的作用就是将执行器的物理执行计划转化为对系统文件的读写

MySQL软件架构

客户端（JDBC，ODBC，.NET, CLI） =>
连接器=>（缓存/分析器）=>优化器 =>执行器 => （InnoDB，MyISAM，Archive）=>
文件系统（Linux，Windows，Mac）

大型软件往往不是单一的架构设计，而是多种混合设计，像MySQL软件架构可以从中看到分层、管道、微核架构等设计。真正研究MysQL的人往往认为，

连接器=>缓存
连接器=>分析器=>优化器=>执行器这些才是MySQL的server部分，而InnoDB、MyISAM、Archive只是它的一部分插件而已

客户端与MySQL的链接方式

TCP/IP链接、（命名管道、共享内存、unix域套接字）不常用

MySQL TCP报文格式
三次握手建立连接，通过TCP报文格式来传输数据，WireShark抓包工具可查看Navicat与MySQL服务之间的TCP通讯

一个SQL语句如何执行？

1. 连接器进行链接
2. 查询缓存：之前的查询语句会以KV的形式缓存在内存中，K sql语句 V 查出的结果
        不推荐使用，数据表修改后，会删除所有的相关缓存
3. 分析器：分析关键字（SELECT，DELETE，JOIN）分析句法 判断SQL语句是否符合语法
4. 优化器：决定SQL语句怎么做，决定如何使用索引
5. 执行器：校验用户对目标数据库有无权限，调用存储引擎，执行SQL，没有索引的情况下会循环查询所有的行
6. 存储引擎：将执行器的指令落实到数据文件上，不同的存储引擎的原理和执行方法有很大的不同

MySQL8.0 之后停用了缓存功能
一个SQL语句的执行涉及到了MySQL的所有模块，按照分析->优化->执行->落盘步骤执行。

四种常见的MySQL存储引擎

InnoDB
MySQL5.5之后的默认存储引擎
支持事务、外键
支持崩溃修复能力和并发控制

MyISAM
MySQL5.5之前的默认存储引擎
插入数据快（使用B+树的结构，不支持事务）
空间利用率高
Memory
数据都在内存中，速度快（适合建立临时表）
安全性差
Archive
数据压缩，空间利用率高
插入速度快
不支持索引，查询性能差
总结：InnoDB适合各种互联网业务，查询性能要求高可以选择MyISAM，日志信息归档考虑Archive，临时表考虑Memory

MySQL是索引组织语言

主键：字段
主索引：主键排序后组成的索引
InnoDB存储引擎中，每张表都有一个主键
如果没有指定主键，第一个定义的非空唯一索引即为主键
如果没有唯一索引，InnoDB会自动创建一个6字节的指针，作为主键

B树
线性结构+数的结合
通过多数据节点大大降低树的高度
不需要旋转就可以保证数的平衡

B+数
由B数发展而来
所有的数据都在叶子节点
所有的数据形成了一个线性表

InnoDB的逻辑存储结构：

表空间，段，区，页，行（InnoDB的页就是一个B+树的结点，整个区就是一个完整的B+树）

InnoDB行记录的结构：

变长字段：

长度不固定的数据类型：
VARCHAR, VARBINARY, BLOB, TEXT
占用768Byte的不变长类型： CHAR
变长编码下的CHAR也可能是变长字段：utf8编码 一个字符占用的空间字节数是不一样的
（utf8是变长编码）

行溢出数据：

1. InnoDB每个数据页的容量有限，数据字段的存储也是有限的
2. 字段过大时，会采用行溢出机制
3. 溢出机制会把超长的字段存储到一个单独开辟的数据页当中

行记录格式

InnoDB的两个时代的行记录格式：

Redundant / Compact （Antelope文件格式）
Dynamic / Compressed （Barracuda文件格式）

5.0 之前默认的行记录格式 Redundant

字段偏移列表	Header	RowID	TxID	Roll Pointer	Col1	Col2	Col3	...	Coln

字段偏移列表：记录每个字段的相对位置，按照列的顺序逆序放置
Header：列数量，字段偏移表的单位，下一行记录的指针等信息
RowID：没有主键时，使用RowID作为隐藏主键
TxID：事务ID
Roll Pointer：回滚指针
Col1...Coln：数据（未溢出），溢出的数据：存放前768Byte字节数据和BLOB页指针

字段偏移列表即存放了定长字段的位置，又存放了不定长字段的位置，

5.7之后的默认行记录格式 Dynamic

变长字段长度表	NULL标志位	Header	RowID	TxID	Roll Pointer	Col1	Col2	Col3	...	Coln

Col1...Coln: 数据长度大于40Byte（溢出） 存放BLOB页指针

总结：

两个时代，四种类型
核心需求是节约行记录的空间，增加每页的数据行数，提高查询效率

索引有哪些左侧用法？

联合索引（带头大哥不能死，中间兄弟不能丢）
前缀索引
1. 字符串太长可以使用前缀索引，节约磁盘空间
2. 如果字符串前缀包含大量相同字符，区分力度小可以使用两种方法：
  1. 倒叙存储：存储时把字符串倒着存进去，索引会按照后缀进行排序，取出的时候在倒序取出
  2. 新建Hash字段：字符串大量包含相同串，索引查询效率不高，可以根据字符串的hash值进行索引排序，查询的时候根据hash值查询
  alter table user add index email_idx ( email (6) ) 表示根据user表的email字段的前六位作为索引
LIKE 左模糊可以有效用到索引，%关键字%会使索引失效

MySQL如何约束数据？

Primary Key 主键， Unique Key 唯一
Foreign Key 外键（不常用）
Default， NOT NULL
1. Not Null 受到严格模式影响，innodb_strict_mode 默认是开启状态
2. Default Null字段加索引会影响到索引的性能，因为null不能作为索引的建
触发器：插入，修改数据使用触发器校验数据，干扰业务，基本不使用

MySQL视图

创建视图尽量使用MERGE算法，并且避免使用无法使用MERGE的SQL，例如聚集函数 DISTINCT， GROUP BY HAVING， UNION，子查询

MySQL where查询慢怎么办？

减少回表操作，提高查询效率（尽量不使用select *）多余的字段需要回表，再次进行查询效率很低。
通过优化sql或者优化联合索引，来使用覆盖索引（Using index）

有合适的索引不走怎么办？

强制走索引： force index
优化索引：analyze table 重新统计索引信息，会重新计算索引的基数
根据基数大小可以判断索引性能的好坏

MySQL基数：随机几个页取出平均值 × 页数 = 基数

count()查询慢，怎么办？

count(非索引字段)：无法使用索引覆盖，最慢
count(索引字段)：可以使用覆盖索引，但需要对数据判空
count(1)：不需要取数据，但需要判断 1是否为null （MySQL没有做优化）
count(*)：专门优化，直接返回行记录个数，也就是索引树中的数据个数，理论最快。

order by 查询慢怎么办？

索引覆盖是最高效的处理排序方式

order by执行原理：

根据where条件查询，将结果集放入sort_buffer中
对结果集按照order by字段进行排序
回表生成完整结果（若需要）

中间表结果集：

中间表比较小的时候，直接放入到内存中
中间表大于sort_buffer_size时，放入到硬盘中
sort_buffer_size增大：内存占用增加，查询时间减少
sort_buffer_size减少：内存占用减少，查询时间增大

回表生成结果集：

当行小于max_length_for_sort_data时，生成全字段中间表
大于阈值时，生成排序字段+主键中间表，需要回表
调大阈值不一定改善查询效果，因为结果集太大排序效率就会降低

MySQL的rand()函数

# rand()函数随机选取表中的一条数据
select * from film order by rand() limit 1

执行原理：

创建一个临时表，字段为rand, film表所有字段
从表中取出一行，调用rand()函数，将结果和函数插入到临时表中
把临时表的rand字段+行位置（主键），放入sort_buffer
对sort_buffer进行排序，将第一个行位置（主键）取出，查询临时表

分析：

效率很低出现了两次中间结果（临时表，sort_buffer），且都是全长度
多次调用rand()函数，开销较大
只需要一个结果但是对真个表进行了排序

解决方案：

修改SQL解决（临时解决方案）：
1. 取出表中的主键的最大值和最小值
2. 从最大值和最小值中用rand()取一个中间值
3. 查询这个中间值
修改业务解决：
1. 查询表数据的TOTAL
2. 在TOTAL范围内，随机取出一个值R
3. 执行SQL：select * from film limit R, 1

带头大哥死了怎么办？

索引下推

select * from film where store_id in (1,2) and film_id = 3;

MySQL5.6之前，首先使用索引查询store_id in (1,2) 然后回表查询film_id = 3
MySQL5.6之后，使用索引查询store_id in (1,2) 然后直接使用索引过滤film_id = 3 (Using index condition) 大大减少回表次数

松散索引扫描

select * from film where film_id = 3;

MySQL8.0新特性，打破最左匹配原则，使用索引字段store_id 逐个查询film_id是否等于3 （Using index for skip scan）

MySQL索引失效的情况

select * from film where film + 1 = 100;

对索引字段做函数操作，优化器会放弃索引

字符串与数字进行比较

如果出现数字与字符串进行比较， MySQL会优先将字符串转为数字在比较

#若f1是varchar类型
select * from t1 where f1 = 6;
#相当于
select * from t1 where CAST(f1 AS signed int) = 6;

隐式字符编码转换

MySQL中，utf8与utf8mb4进行比较，会把utf8转换为utf8mb4;

#若两个编码不同的sql进行子查询, 会将字段进行函数操作转码
select t2.* from t1, t2 where t1.f1 = CONVERT(t2.f1 Using utf8mb4) and t1.f2 = 6;
#解决方法：将查询条件转为索引字段的编码
select t2.* from t1, t2 where CONVERT(t1.f1 Using utf8) = t2.f1 and t1.f2 = 6;

分页查询慢，怎么办？

使用索引，尽量用到索引覆盖，减少回表操作
先使用索引覆盖查询Id，最后使用ID进行回表

什么是覆盖索引？

指从查询，到执行，到返回，均使用同一条索引，在同一条B+树上操作，不需要回主B+树，取消回表操作，提升查询效率

posted @ 2022-08-20 21:16 mmpyeah 阅读(86) 评论(0) 收藏举报

刷新页面返回顶部

mmpyeah