A_108_MySql
2021-04-09 10:49:26 0 举报AI智能生成
全面、高效的知识图谱：A_108_MySql!! 全面又深度的提升认知，达到实际应用的目的! 建议先纵观全局，掌握好大方向。 再根据自己的需要，针对性的学习某一个点，最后做到逐步由点及面。
作者其他创作
大纲/内容
优化案例
获取大数据表的总行数
存储过程
优点
减少数据库连接次数
无需重新编译，而SQL需要经过解析编译再执行
提高代码安全，减少SQL注入
缺点
移植性较差
开发调试复杂
SQL本身是一种结构化查询语言，但不是面向对象的的，本质上还是
过程化的语言，面对复杂的业务逻辑，过程化的处理会很吃力
高可用
主从同步
主从不一致如何处理（存在时延）
1. 若业务可接受则可以忽略
2. 强制读主，高可用主库，
用缓存提高性能
3. 在缓存中记录哪些记录发生过
写请求，来路由读主还是读从
读写分离
主从同步延时
1. 分库，将一个主库分为多个主库，每个主库的写并发
压力减少了几倍，此时主从延时可以忽略不计
2. 打开MySQL支持的并行复制，多个库并行复制。
若某个库的写并发很高，单库达到2k/s，则并行复制还是无意义
3. 重写代码，插入数据后立马查询可能查不到
监控
主从延迟监控
精确做法，判断主从二进制文件的偏移量
简单做法，通过查看从节点中的Seconds_Behind_Master查看
主从数据一致性检测
TPS/QPS
Innodb线程堵塞
事务
原子性（A）
一致性（C）
隔离性（I）
持久性（D）
隔离级别
读未提交（Read UnCommit）
读已提交（Read Commited）
可重复读（Repetable Read）
串行化（Serialazable）
存储引擎
InnoDB
并发控制
锁
共享锁
排他锁
数据多版本
redo日志
1. 事务提交的数据先写到redo日志里（此时变成了顺序写），
再定期将数据刷到磁盘上，即随机写变为顺序写
2. 若数据库崩溃，还没来得及刷盘的数据，在数据库重启后，
会重做redo日志里的内容，以保证已提交事务对数据产生的影响都刷到磁盘上。
undo日志
当事务回滚时，或者数据库奔溃时，可使用undo日志来恢复数据
对insert操作，undo日志记录新数据的PK(ROW_ID)，回滚时直接删除
对于delete/update操作，undo日志记录旧数据row，回滚时直接恢复；
回滚端 rollback segment
行数据的三个内部属性
DB_TRX_ID，6字节，记录每一行最近一次修改它的事务ID
DB_ROLL_PTR，7字节，记录指向回滚段undo日志的指针
DB_ROW_ID，6字节，单调递增的行ID
InnoDB为何能够做到这么高的并发
锁类型
记录锁 Record Locks
间隙锁 Gap Locks
插入意向锁
临键锁 Next-Key Locks
自增锁 Auto-inc Locks
共享/排它锁(Shared and Exclusive Locks)
意向锁(Intention Locks)
分类
意向排它锁(intention exclusive lock, IX)
意向共享锁(intention shared lock, IS)
意向锁协议(intention locking protocol)
事务要获得某些行的S锁，必须先获得表的IS锁
事务要获得某些行的X锁，必须先获得表的IX锁
插入意向锁
MyISAM
不支持外键
七种连接
索引
定义
优势
劣势
分类
单值索引
唯一索引
复合索引
覆盖索引
索引结构
B树索引
Hash索引
全文索引
R-Tree 索引
数据结构
B树
叶子节点，非叶子节点，都存储数据
中序遍历，可以获得所有节点
B+树
非叶子节点不再存储数据，数据只存储在同一层的叶子节点上
叶子之间，增加了链表，获取所有节点，不再需要中序遍历
性能分析
mysql 查询优化器
常见瓶颈
CPU
IO
explain
能做什么
表的读取顺序
数据读取操作的操作类型
可能用到哪些索引
实际用到哪些索引
表之间的引用
每张表有多少行被优化器查询
列名解析
id
id相同
id 不同
ID相同又不同
select_type
Simple
Primary
SubQuery
Derived
Union
Union result
table
type
system
const
equ_ref
ref
range
index
all
possible_keys
key
key_len
ref
rows
Extra
Using filesort
Using temporary
Using index
using where
using join buffer
impossible where
select table optimized away
distinct
哪些情况需要创建索引
主键自动创建唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系创建索引
频繁更新的字段不合适建索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或分组的字段
查询截取分析
查询优化
永远是小表驱动大表
order by
若排序的字段不在索引列上，
则mysql会启用两种filesort算法
双路排序
单路排序
优化策略
增大sort_buffer_size参数
增大 max_length_for_sort_data 参数
group by
慢查询日志
开启 set global slow_query_log=1;
阀值 long_query_time
日志分析工具 mysqldumpslow
相关参数
s 按照何种方式排序
c 访问次数
l 锁定时间
r 返回记录
t 查询时间
al 平均锁定时间
ar 平均返回记录数
at 平均查询时间
g 后面搭配一个正则表达式
show profile
分析步骤
1. 查看数据库是否支持
2. 运行 sql
3. show profiles; 查看执行的语句
4. 诊断 sql , show profile cpu , block io for query N
需要注意的结论
converting heap to myisam
表示查询结果太大，内存不够用改用磁盘
creating tmp table
copying to tmp table on disk
把内存中的临时表复制到磁盘
locked
批量插入数据脚本
建表
dept表
emp表
订单表
大数据插入注意事项
创建函数
随机字符串函数
随机部门编号
随机状态数字
随机指定位数数字字符串
随机用户ID
创建存储过程
部门表dept
员工表emp
订单表
执行存储过程
0 条评论
下一页
