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)
尽量使用索引, 优先使用主键索引,唯一键修饰的索引 (非聚簇索引会进行一次回表操作,慢于聚簇索引, 唯一键会降低回表次数)
使用索引字段进行范围查询时, 优先使用>,<,(<,gt), 少使用or , in , 原因:范围查询只比较一次, 过多次数比较会导致优化器放弃使用索引
定期重新导入索引, 防止索引空洞
总结: 尽量使用索引, 减少索引在条件中比较的次数, 减小索引搜索范围
修饰的索引 (非聚簇索引会进行一次回表操作,慢于聚簇索引, 唯一键会降低回表次数)
使用索引字段进行范围查询时, 优先使用>,<,(<,gt), 少使用or , in , 原因:范围查询只比较一次, 过多次数比较会导致优化器放弃使用索引
定期重新导入索引, 防止索引空洞
总结: 尽量使用索引, 减少索引在条件中比较的次数, 减小索引搜索范围