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