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