MySQL知识框架
2021-08-11 22:24:00 29 举报AI智能生成
关于MySQL的实际经验总结、高级语法总结
作者其他创作
大纲/内容
读锁会阻塞写，不会阻塞读
写锁把读和写都堵塞
偏向Myisam存储引擎
并发低、开销小
查看加锁的表
分析表锁定
表锁分析
表锁（偏读）
行锁（偏写）
MySQL锁机制
调优工具
网络带宽提升
CPU性能
SSD固态硬盘
服务器硬件优化
例：关闭全局日志（general_log=0）
例：MySQL复制形式（binlog_format=row）
数据库配置优化
联合索引应遵守最左前缀规则
模糊查询%在最右，例：like 'aa%'
如：100页的书有50页目录就很不合适
索引避免过多
索引长度尽量短，节省空间
建立索引的字段不能参与计算，否则失效！
索引优化规则
left join替代join
避免在where子句后使用!=或<>操作符，否则将会全表扫描导致索引失效
避免select *
for update悲观锁谨慎用
使用exists，not exists代替IN、NOT IN
减少对字段进行null判断
索引列避免使用函数和参与计算
查询SQL优化
开启慢查询
其他查询
实时获取存在性能问题的SQL
Using filesort（文件排序）
Using temporary
extra
使用explain关键字段
根据慢日志定位慢查询sql
使用explain等工具分析sql
修改SQL或者尽量让SQL走索引
如何定位并优化慢查询SQL
性能调优
读未提交（READ-UNCOMMITTED(）
读已提交（READ-COMMITTED）
可重复读（REPEATABLE-READ）
可串行化（SERIALIZABLE）
隔离级别
四大特性：ACID
事务t1读取到了另一个事务t2还未提交的数据，从而造成脏读
脏读（Dirty Read）
事务t1多次读同一个数据，由于期间有事务t2对该数据进行修改，导致事务t1内读取的数据不一样
前后多次读取，数据内容不一致
不可重复读（修改）
事务t1读取几行数据，接着另一个事务t2插入了一些数据，事务t1读取到了一些原本不存在的数据记录，导致幻读
幻读（新增/删除）
并发事务
数据库事务
底层为哈希表结构，k，v存储完全无顺序关系
等值查询，可能出现hash碰撞
适合等值查询，无法利用索引进行排序、范围
不支持联合索引的最左匹配原则
hash索引
解决了排序问题，出现线性二叉树问题
无法保证平衡，导致线性链表
二叉查找树 Olog(n)
通过旋转解决了平衡问题，数据量一旦增多，旋转操作效率很低
查找速度是红黑树的高度
平衡二叉树/红黑树（AVL）
平衡的多叉树
比平衡二叉树减少了一次IO操作
B-Tree Olog(n)
非叶子节点只存储键值信息
所有数据信息都在叶子节点
子节点之间会有指针连接！
所有叶子节点之间有一个链指针
B+Tree
索引结构
主键索引(PRIMARY)
聚簇索引
唯一索引（UNIQUE）
普通索引（INDEX）
全文索引（FULLTEXT）
联合索引
非聚簇索引
索引模块
符合最左前缀原则，联合索引要把查询频率最⾼的列放在最左边
查询频率较高的列
索引列不能参与计算，最好简单数字类型
主键自增
使用“覆盖索引”查询，避免回表带来的性能损耗
索引建立原则
联合索引的排序方式是关键，第一个字段会先进行排序，然后对之后的字段排序若不遵循最左法则，则一切都乱序查找
不遵循最佳左前缀法则
索引失效
索引
lock in share mode
其他事务可以继续加共享锁，但是不能继续加排它锁
共享锁/读锁（S）
for update
一旦加写锁，其他事务不能加任何锁
排它锁/写锁（X）
Innodb（行锁）
适合大量的查询场景
MyIsam（表锁）
两个存储引擎的区别
存储引擎
主从复制
数据恢复
使用场景
server层面
逻辑日志，记录对应的是SQL
非循环使用，在写满/或重启后 会生成新的binlog文件
写入磁盘时间点不同，binlog日志是在事务提交完成后写入
特性
0：不强制要求，有系统自动判断何时写入磁盘
1：每次commit时都要将binlog写入磁盘
N：每N事务时才会将binlog写入磁盘
binlog刷盘时机
基于语句的复制（排除查询SQL外都会被记录到binlog中）
概念
无需记录每行变化，减少binlog的日志量
减少IO，提升性能
优点
某种情况下导致数据不一致，如系统函数sysdate（）
缺点
总结
statment
基于行的复制
需要记录每行的修改逻辑，比如给“id=2的这行记录中的b字段+1”
特殊情况下不存在数据不一致情况（如调用了sysdate函数）
会产生大量日志，如alter table时，会产生大量日志
row
是基于statment和row两种模式的混合
一般复制采用statment模式
保存采用binlog
mixed
binlog日志格式
binlog（归档日志）
Innodb存储引擎层面
WAL（write-ahead logging）：先写日志，再写磁盘
crash-safe能力（崩盘恢复），即使数据库发生异常重启，之间的数据也不会丢失
redolog有crash-safe能力
因Innodb引擎是以”页“为单位进行磁盘交互的，一般一个事务只是修改一个数据页或多个数据也中的几个字节的数据，若将完整的数据刷到磁盘的话，非常耗费性能
产生背景
物理日志
循环使用
写入磁盘时间点不同，在事务进行中不断的写入
redolog可作异常宕机、介质故障的数据恢复使用
持久性
redo log buffer（内存中的日志缓冲）
redo log file（磁盘上的日志文件）
包括两部分
先将记录写入redo log buffer
后续某个时间点再一次性将多个操作记录写到redo log file
流程
内存写入磁盘机制
redo log buffer写入redo log file的三种机制
工作原理
redolog（重做日志）
redolog和binlog区别对比
日志模块
实现了RC和RR，也就是读已提交、可重复读
结构
undo log版本链
活跃的事务ID，未提交的事务组
m_ids
最小的事务ID
min_trx_id
当前系统中事务的 id 值最大的那个事务 id 值再+ 1，也就是系统中下一个要生成的事务 id。
max_trx_id
已提交的事务
creator_trx_id
readview
一个select生成一个readview
以select为单位，因而会出现不可重复读问题！
RC（读已提交）
事务级别的，只生成一个事务的readview
以事务为单位，如：三个select语句只会生成一个readview
间隙锁（默认开启的）
当前读
MVCC
快照读
解决了幻读问题
innodb引擎幻读解决
RR（可重复读）
记录锁封住的是索引记录，而不是我们真正的数据记录！
id 列必须为主键列或唯一索引列
查询语句为精确查询，不能为范围或模糊查询
SELECT * FROM table WHERE id = 1 FOR UPDATE;
通过“主键索引”，“唯一索引”对数据进行update操作
UPDATE SET age = 50 WHERE id = 1;
举例
记录锁（Record-Lock）
基于非唯一索引，它锁定的是一段范围的索引记录
它是基于Next-Key Locking算法
1和10两条记录并不会锁住
锁住的是开区间范围内的数据行
SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;
举例
间隙锁（Gap Lock）
可理解为一种特殊的间隙锁，一种特殊算法
临建锁（Next-Key Lock）
InnoDB加锁算法
MySQL知识框架
0 条评论
回复 删除
下一页
