开发必知必会Mysql
2020-04-01 10:16:38 4 举报AI智能生成
开发必知必会Mysql
作者其他创作
大纲/内容
事务与锁
事务定义
数据库最小的工作单元
可能包含了一个或者一组DML语句
事务特性：ACID
原子性：Atomicity
一致性：Consistent
数据库自身完整性约束
用户自定义完整性约束
隔离性：Isolation
持久性：Durable
事务开启
支持事务的存储引擎：InnoDB、NDB
事务开启方式
手动开启
自动开启
事务并发问题
脏读
不可重复读
幻读
事务隔离级别
读未提交：RU
读已提交：RC
可重复读：RR
串行化
事务隔离级别解决方案
LBCC
MVCC
InnoDB MVCC实现方式
InnoDB隔离级别实现
RU：不加锁
RC：快照+记录所
RR：快照+临键锁
Serializable：共享锁
锁
锁基本模式
表锁
意向共享锁
意向排他锁
行锁
共享锁
排他锁
锁算法
记录锁
间隙锁
左开右开的区间
阻塞插入操作
临键锁
左开右闭的区间
InnoDB中默认行锁算法
锁的问题
死锁
产生条件
死锁的解决
查看锁信息
分析事物与锁情况
kill掉指定事务
死锁的避免
性能优化
优化层次
硬件与操作系统
服务器硬件优化
Centos系统的内核参数、磁盘调度策略等优化
连接
增加服务端可用连接数
减少服务端占用连接数
使用连接池
配置优化
数据库配置
操作系统配置
架构优化
缓存
主从复制
异步复制
半同步复制
全同步复制
多库并行复制
GTID复制
读写分离
分库分表
垂直分库
水平分库分表
高可用
HAProxy+keepalived
NDB
Galera Cluster
MMM-->MHA
MGR
存储引擎选择
MyISAM：查询插入操作多的业务表
Memeory：临时数据
InnoDB：常规的更新频繁并发大的业务
表及字段
为字段选择合适的数据类型
非空字段尽量定义为NOT NULL
不要用外键、触发器、视图
不要存储大文件
表拆分
SQL与索引优化
慢日志
打开慢日志
mysqldumpslow
show profile
show processlist
explain
查询序列编号：id
查询类型：select type
连接类型：type
可能用到的索引：possible_key
实际用到的索引：key
索引长度：key_len
扫描行数：row
filtered
ref
额外信息：Extra
优化方案
优化思路
优化工具
常用操作
秀儿
show variables like
wait_timeout
interactive_timeout
max_connections
max_allowed_packet
datadir
innodb_change_buffer_max_size
slow_query%
show status like
Thread%
Threads_cached
Threads_connected
Threads_created
Threads_running
Last_query_cost
show processlist
常见连接状态
基础架构
server层
连接器
管理连接、权限验证
通信类型：同步/异步
连接方式：长连接/短连接
通信协议：Unix Socket、TCP/IP
通信方式：单工/半双工/全双工
查询缓存
默认关闭，不建议试用；Mysql8.0已移除
解析器
1、词法解析、语法解析
2、语义解析(预处理)
优化器
生成执行计划
基于规则的优化
基于开销（cost）的优化
辅助工具
查看执行计划：explain
开启优化器跟踪
查看执行计划：explain
执行器
调用存储引擎的API进行操作，返回结果
binlog
存储引擎
MyISAM
InnoDB
Memory
csv(导入导出)、Archive(归档)、Blackhole(黑洞)等
自定义存储引擎
执行流程
一条查询语句的执行
一条更新语句的执行
0、和查询基本流程一致，区别在拿到数据之后的修改操作
1、从内存或磁盘取到数据
2、执行器修改数据值
3、记录undo log
4、记录redo log
5、记录binlog
6、写入Buffer Pool数据，然后等待刷盘
InnoDB
In-Memory Structures
Buffer Pool：缓冲池
缓存数据页和索引页
LRU算法 (链表 young/old)
Change Buffer：写缓冲
Adaptive Hash Index
Log Buffer
On-Desk Structures
系统表空间
数据字典
Doublewrite Buffer
Change Buffer
Undo Logs
独占表空间
通用表空间
临时表空间
Redo Log
undo Log tablespace
索引
索引本质
数据库管理系统中的一个有序的数据结构，以协助快速查询、更新数据库表中的数据
索引类型
主键索引
在InnoDB中 主键索引 存储索引和数据
辅助索引
在InnoDB中，辅助索引 存储索引和主键值
唯一索引、普通索引、全文索引、前缀索引
索引数据结构
数据结构对比
有序数组
链表
二叉查找树
AVL树(平衡二叉树)
B树(多路平衡查找树)
B+树
千万级数据查找较少IO次数
优点
B树解决的问题，B+树都可以解决
扫库、扫表能力更强
磁盘读写能力更强
排序能力更强
效率更加稳定
Hash
优势：时间复杂度 O(1)，适合在内存中使用，不适合磁盘数据结构
缺点
不能排序
需要计算hash，只能用等值查询
Hash碰撞
索引在存储引擎落地
InnoDB-聚集(聚簇)索引
优点
数据访问更快
对于主键的排序查找和范围查找非常快
缺点
插入速度严重依赖插入顺序，可能页分裂
更新主键的代价很高
辅助索引查找需要两次索引查找(回表)
MyISAM-非聚簇索引
索引使用原则
离散度高的字段适合创建索引
联合索引最左匹配
覆盖索引
索引下推(ICP)
减少io次数，提高查询语句性能
MRR
减少io次数，减少了随机IO
BAK
减少了随机IO
长度较长的字段，使用前缀索引
索引的创建和使用
创建
索引创建了但用不到的情况
log buffer-->磁盘上的redo log
一条更新语句的完整执行流程
0 条评论
下一页
