mysql高级

1. sql_mode

控制group by a 的分组, 同级a列有多少行, 然后只展示该行数, 不展示多余行数, 如果查询的其他字段在分组的任意一行有两个数据就会报错

可以设置SQL_mode的开关, 关闭则不会报错

2. mysql的逻辑架构

①客户端通过连接层和mysql进行连接

②在服务层会先检查SQL的语法, 然后解析SQL, 然后SQL优化, 最后查询缓存(如果是DQL)

③在存储引擎中会执行SQL, 来修改存储区数据(落盘)或者获取存储区数据(DQL)

④SQL执行, 存储区数据更新或者被获取

2.1. 连接层

负责外部客户端和mysql进行连接

适配各种语言

java中和连接层连接的就是jdbc(加载驱动, 获取连接, 获取SQL执行对象)

2.1.1 connection_pool(连接池)

存放连接: 连接是珍惜资源, 不应该随意创建销毁

复用连接: 快速连接客户端

控制资源: 连接数量固定, 控制连接量, 保障数据库服务器的不被冲垮

2.2. 服务层

2.2.1. SQL interface

SQL规范检查, SQL语法检查, 和客户端交互, 接收SQL命令并返回结果

2.2.2. Parser(解析器)

语法解析, 通过对SQL的关键字进行解析, 生成解析树

select * from table_name
left join xxx on 
where xxx group by xxx having xxx order by xxx limit xx
机读顺序:
from > on(连表条件) > where > group by > having > select(distinct去重) > order by > limit
FOJWGHSDOL
#  佛叫我干活，速度上线(OL)

2.2.3.Optimizer(优化器)

SQL优化 索引优化等

2.2.4. 缓存(Buffer and Cache)

存放之前SQL查询出的数据, 发生DML则清空缓存,

查询缓存有读写锁

缓存除了查询还能临时存放存储引擎查询的临时数据, 然后server层对数据进行处理, 比如非聚簇索引查询的主键集

2.3. 引擎层

存储引擎负责操作数据(执行SQL)

2.4. 存储层

在服务器的磁盘空间中存储数据库数据, 具有持久性

2.5. Management Service (mysql管理工具)

mysql的备份, 复制, 集群等功能

3. mysql数据结构

时间复杂度:

线性存储: 在一段连续的空间中存储

非线性存储: 在一段不连续的空间中存储

数据结构:

数组: 存储在连续的空间, 对于有序数组, 查询时间复杂度为O(log2N), 无序数组为O(N)

链表: 链表的每个节点中存放着当前节点的数据和下一个节点的地址, 插入和删除容易(链表删除只能删除首尾)

hash: 以键值对存储, 对键进行hash计算, 可以根据hash值直接找到键值对, 时间复杂度为O(1), 但是数据量大了存在hash碰撞

​ 通过键查找hash的时候会直接对该键求hash, 然后通过hash锁定键值对, 如果有hash碰撞则会通过键的真实 值继续和hash一样的进行比较, 所以hash不太使用超大数据量的存储, 会产生大量的hash碰撞, 进而提高查询 时间复杂度.

根节点:没有父节点的节点, 叶子节点:没有子节点的节点, 树的子节点和父节点是以链表的方式相连

树: 树的左边子节点比父节点大, 右边子节点比父节点小(有序)

平衡树: 可通过旋转来平衡, 最终使得所有叶子节点到根节点的层级相差不超过1

平衡树查询会查父节点然后查子节点

**B树(多路平衡树)😗*三路平衡树如图

n叉树每个子节点可以存放(n-1)个数据, 每个节点最大可以有n个子节点, 当一个节点的存放数据为n时, 会提起最中间的数据作为父节点

**B+树:**三叉B+树

基于B树(多路平衡树), 每次提起一个阶段作为父节点时, 会复制该节点为父节点, 最后导致结果:(和B树的不同)

所有节点都在叶子节点存储 , 优点(范围查询快)

4. 存储和索引

**mysql存储以页空间为单位, 每一页存储16384字节(16KB), 前面页数放索引, 可以根据索引找到数据页中的数据(回表) **

数据页之间是以双向链表形式连接(数据页存放相邻数据页的地址)

回表: 根据非聚簇索引索引找到叶子节点的主键值, 根据主键值(聚簇索引)查询真实数据

**覆盖索引: 索引查询叶子节点中会包含索引字段值和主键值, 若查询结果在这个范围内则不需要回表 **

索引以B+树的数据结构存储在索引页中

InnoDB会以指定主键为主键, 若未定义主键则选取一个unique键为主键, 若也无, 则默认添加row_id列为主键

数据存储过程:

数据存储会存储到数据页中, 每条数据存储会建立目录页,目录页主键(聚簇索引)会遵循B+树规则, 然后根据叶子节点的顺序将数据存入到数据页(叶子节点不在索引页, 所以聚簇索引叶子节点存放真实数据), 每一行数据会有一个recode_type的属性, 记录和下一条数据的偏移量, 所以数据页中的数据以链表连接, 根据索引查询时, 会先查询索引树, 聚簇索引叶子节点的父节点会找到叶子节点的真实地址值, 非聚簇索引会找到叶子节点获取索引值和主键值. 根据主键值走聚簇索引查询路线获取索引的记录(回表)

4.1. 索引

4.1.1 聚簇索引和非聚簇索引

聚簇索引: 主键索引, 叶子节点存放真实数据

非聚簇索引: 非主键索引, 叶子节点存放当前索引值和主键

聚簇索引查询: B+树的非叶子节点存放在索引页中, 查询时, 会查找找到叶子节点的父节点, 父节点提供叶子节点的真实地址值, 然后通过真实地址值找到在数据页中的叶子节点(真实数据)

4.1.2. 单列索引

4.1.3. 复合索引

先比较一级索引, 一级索引一样比较二级索引,以此类推, 叶子节点存放复合索引的所有索引和主键

在一级索引下是有序的, 一级一样在二级索引下有序, 类推

排序: 根据复合索引层级排序可以命中, 排序第一字段必须是第一索引, 类推

分组: 分组是先排好序然后再进行分组的, 对于索引和排序类似

4.1.4 索引空洞

索引空洞:删除数据后, 数据页的真实数据会删除, 但是B+树中的对应节点不会删除, 导致索引会比真实数据多, 索引未匹配上真实数据

结果: 索引空洞会导致空洞的索引值未命中

解决: 表用久了重新构建索引

4.2. 索引优化

数据过多的话(百亿数据)采用分库分表

使用频繁的查询条件建立索引

子查询会多次查询SQL语句, 会有多次io(一次查询会有一次io, 而连表查询只会有一次io)

尽量使用索引, 优先使用主键索引,唯一键修饰的索引 (非聚簇索引会进行一次回表操作,慢于聚簇索引, 唯一键会降低回表次数)

使用索引字段进行范围查询时, 优先使用>,<,(&lt,gt), 少使用or , in , 原因:范围查询只比较一次, 过多次数比较会导致优化器放弃使用索引

定期重新导入索引, 防止索引空洞

总结: 尽量使用索引, 减少索引在条件中比较的次数, 减小索引搜索范围

修饰的索引 (非聚簇索引会进行一次回表操作,慢于聚簇索引, 唯一键会降低回表次数)

使用索引字段进行范围查询时, 优先使用>,<,(&lt,gt), 少使用or , in , 原因:范围查询只比较一次, 过多次数比较会导致优化器放弃使用索引

定期重新导入索引, 防止索引空洞

总结: 尽量使用索引, 减少索引在条件中比较的次数, 减小索引搜索范围