mysql
2021-03-01 07:59:38 0 举报AI智能生成
mysql
作者其他创作
大纲/内容
执行过程
FROM <left_table>
对 from 子句的右表执行笛卡尔积， 产生虚拟表 VT1
ON <join_condition>
对虚拟表VT1 进行on 筛选， 符合 <join_condition> 的行插入到虚拟表 VT2
<join_type> JOIN <right_table>
保留未匹配的行， 作为外部行 插入到虚拟表 VT2， 产生虚拟表VT3
如果from 包含多个表，则对上一个连接产生的结果表VT3和下一个表， 重复执行 1,2,3 步骤
WHERE <where_condition>
对虚拟表VT3 应用where 过滤条件， 符合条件的数据 记录到虚拟表 VT4
GROUP BY <group_by_list>
根据group by 子句中的列，对VT4 中的记录进行分组操作， 产生VT5
WITH {CUBE|ROLLUP}
对VT5进行 curbe或ROLLUP 操作， 产生VT6
HAVING <having_condition>
SELECT <select_list>
再次进行select 操作， 获取指定列
DISTINCT
去除重复数据
ORDER BY <order_by_list>
LIMIT <limit_number>
日志
错误日志 error log
记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息
查询日志(general log)
记录建立的客户端连接和执行的语句。
二进制日志(bin log)
记录所有更改数据的语句，可用于数据复制。
慢查询日志(slow log)
记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。
中继日志(relay log)
主从复制时使用的日志
分库分表
拆分标准
单表数据量大于2000w
拆分方式
水平拆分
数据拆分
垂直拆分
业务拆分
中间件
proxy
mycat
client
sharding-jdbc
迁移
停机迁移
老库数据写入中间件他， 同步到新库
双写迁移
同时写入新库和旧库
开启任务
将旧数据迁移到新库
一轮结束后，比较数据，最终达到数据一致性
动态扩容缩容
第一次迁移设计好库和表
4台机器 * 4个 库 * 4张表
最多支持迁移到64台机器
每台机器支持2000并发
全局id生成
全局唯一数据库自增
uuid
数据库序号自增
当前时间撮 + 业务id + 一些规则
snowFlake
64位二级制
前缀0
时间撮 41位
机房id 5位
最多32
超过报异常
机器id 5位
最多32
超过报异常
当前毫秒内 序号
最多4096位
超过进行等待下一毫秒
最终18位long类型
读写分离
并行复制
同一台从库服务器，可以开启多个sql线程，可以保证多个库同时进行
注解切换
定义方法调用
直接切换数据库
原理
主库
写入数据库， 同时写入binlog
IO线程读取binlog同步到从库
多线程
从库
IO线程写入到relay日志
多线程
sql线程读取relay日志写入到数据库
单线程
主从延迟
高并发下，导致从库延迟
1000/s, 延迟几毫秒
解决方案
分库
手动选择主库
不建议， 丧失了读写分离的意义
重写代码
避免插入后，立即查询数据
主库挂掉
数据同步失败
semi-sync
主库写入后，会同时同步从库
从库写入本地relay日志，并返回ack
最少一台从库返回ack，才算写入成功
数据结构
B+tree
叶子节点挂载数据+索引+指针
形成有序链表
方便按区间查询
非叶子节点挂载索引列数据
数据存储量
最小存储引擎为页
默认每页16K
非叶子节点存储
索引字段
bigInt
8个字节
varchar（M）
L+1， L <= M
0<=M<=65535
指针
6个字节
计算数据量
单条数据行假设为：1k
计算公式
指针数 * 对应节点存储数据
两层树
主键：bigInt
16 * 1024 / (8+6) = 1170
范围： -2^63到2^63-1
主键：varchar(32)
子主题
子主题
子主题
子主题
Btree
每个节点都会挂载数据
引擎
ISAM
优点
执行速度快，不占用内存和存储资源
缺点
不支持事务，不能够容错
MYISAM
优点
增加索引、字段管理
表格锁定、多优化多个并发的读写操作
mysql默认
缺点
表格损坏后，无法恢复数据
非聚集索引
InnoDB
优点
提交、回滚、崩溃恢复等事务
缺点
聚集索引
查看引擎
show engines
alter table cloud_user engine = InnoDB
选择
MYISAM
count计算
查询频繁
没有事务
InnoDB
可靠性高、要求事务
表更新和查询比较频繁
设计原则
避免使用空值
不利于前台程序编写
索引失效
字段空间占用
主键选择
采用自增主键
叶子节点存储的是有序链表，可以直接在当前索引节点的后续位置追加
如果非自增主键，每次主键都近似于随机值， 每次插入数据，会要移动数据
三大范式
三大范式
第一范式
列的原子性
第二范式
每列都和主键有关系
第三范式
所有列都和主键有直接关系，而非间接关系
反范式
冗余数据，来提高减少关联
事务及锁
事务
原子性（Atomicity）
事务要么一起成功，要么一起失败
隔离性（Isolation）
事务之间不会互相干扰
一致性（Consistency）
事务进行的前后要保持一致
持久性（Durability）
事务一旦提交，改变是永久性的
常见问题
脏读
事务B在修改数据，但是没有提及事务；
事务A读取了数据，并使用事务B事务进行业务处理
不可重复读
事务A读取某条数据进行业务操作后，
事务B改变该条数据，发现数据不匹配
幻读
事务A根据条件查询一些数据，之后事务B又插入了一些符合A条件的数据；
事务A再次查询就会把事务B后来插入的记录读出来
隔离级别
读未提交（read_uncommitted）
简介
一个事务可以读到其他事务没有提交的数据
问题
脏读、不可重复读、幻读
读已提交（reds_committed）
简介
一个事务等另一个事务提交后才可以读取的到
其他事务对该数据进行一次修改并提及后，该事务都能查得到值；
问题
不可重复读、幻读
oracle默认级别
可重复读（repeatable_read）
简介
每次查询的数据是一样的
出现问题
幻读
mysql默认级别
MVCC
Multi-version concurrency controller
读操作
快照读
不加锁
当前读
加锁
串行化（Serializable）
不会出现问题
性能不高
锁
读写锁
S锁（读锁）
一个事务加了S锁，其他事务只能加S锁，不能加X锁
X锁（写锁）
一个事务加了X锁，其他事务任何锁都不能加
行锁和表锁
行锁
单个记录上的锁
表锁
为一个表加上S锁或者X锁
IS锁和IX锁
IS锁
意向共享锁，当事务准备在某条记录上加S锁时，需要在表上加上一个IS锁
IX锁
意向排他锁，当事务准备在某条记录上加X锁时，需要在表上加一个IX锁
悲观锁
数据库的行锁，认为数据库会发生冲突，直接商量把数据锁住，其他事务不能修改，直接提交了当前事务
乐观锁
不锁定的情况下去更新数据，发现不对劲，才不更新或者回滚，需要在数据库添加version字段来实现
死锁
简介
事务A更新字段a
事务B更新字段b，再更新字段a
事务B阻塞，等待事务A释放锁
事务A也阻塞，等待B释放锁
解决
以固定顺序访问表和行
把大事务拆小
在一个事务里，尽可能做到一次锁定所有需要的资源
降级隔离级别
为表添加合理的索引
索引
创建原则
区分度比较大
查询条件
频繁更新不适合做索引
where 和 order by 涉及的列简历索引
聚簇索引
主键索引
挂载的是数据
非聚簇索引
非主键索引
挂载的是主键
回表到主键索引
联合索引
最左原则
覆盖索引
索引上已经存在我们需要查询的数据
索引失效
索引使用了函数
索引列参与计算
前缀模糊
字符串类型和数字直接比较
子主题
or操作
NULL 查询
调优
优化方式
合理的表设计，适量的冗余
添加索引
SQL语句优化
使用连接来代替子查询，使用join，mysql不需要在内存中创建临时表
使用group by分组查询，默认分组后，还会排序，降低速度，增加 order by null 防止排序
避免索引失效
分析技巧
定位慢查询
EXPLAIN
id
每个执行计划有一个ID， 联合查询有多个ID
select_type
SIMPLE
普通查询，没有联合查询，子查询
PRIMARY
主查询
UNION
UNION中后面的查询
SUBQUERY
子查询
table
执行计划查询的表
type
system/const
只有一行匹配数据， 根据索引查询一次就能找到
eq_ref
使用唯一索引扫描， 多表连接中使用主键和唯一索引作为关联条件
ref
非唯一索引扫描，唯一索引最左匹配原则扫描
range
索引范围扫描
index
索引全表扫描，遍历整个索引树，没有where过滤
All
全表扫描， 遍历整个索引树
possible_keys
可能使用到的索引
key
实际使用到的索引
key_len
当前使用到的索引的长度
ref
关联id等信息
rows
查找到记录所扫描的行数
filtered
查找到所需记录占总扫描数的比例
extra
额外的信息、是否使用索引、排序等
SHOW PROFILE
分析SQL执行性能
0 条评论
下一页
