MySQL语法（事务/锁/MVCC）
2021-02-06 15:47:04 9 举报AI智能生成
MySQL数据库
作者其他创作
大纲/内容
搜索引擎
InnoDB
聚集索引、支持事务、支持外键、支持行锁、并发好
应用场景：应用于需要事务和高可用数据更新操作频繁的场景
MyISAM
非聚集索引、仅支持表锁、速度快
应用场景：应用于需要事务和高可用数据更新操作频繁的场景
索引
优缺点
优点
提高数据的检索效率，减少IO次数
加快分组和排序，降低CPU消耗
缺点
索引实际上也是一张表，会占用空间
需要额外的成本来维护索引
类型
B+Tree
B+树有单向指针，mysql优化为双向（利于范围查找）
数据全在叶子节点，非叶子节点只存键值
一个节点上可存多个元素
Hash
等值查询，速度快
不支持范围查找和排序
常见问题
为什么用B+树而不用B树？
IO次数更少
查询性能稳定
便于范围查询
哪些情况不需要创建索引？
表数据太少
经常增删的列
有大量重复值的列
数据库优化
创建时优化
设计要合理，比如使用最合适的数据类型和长度保存数据
枚举类或者boolean类型使用tinyint
订单号使用bigint
金额使用decimal，不能用double
日期类型使用datetime不建议使用时间戳
尽量不用text或blob
合理的创建索引
查询时优化
避免索引失效，否则导致全表扫描
全值匹配、最佳左前缀法则
范围查询以后的索引字段会失效
尽量使用覆盖索引，减少select *的使用
mysql在使用不等于(!=或<>时索引失效)
is null，is not null 也无法使用索引
like不要以通配符开头('%abc...')
少用 or，字符串记得加单引号
不在索引列上做任何操作(计算、函数、类型转换)
explain：部分重要参数
type：访问类型，保证查询至少达到range级别，最好能达到 ref
possible_keys：可能用到的索引
key：实际用到的索引
key_len：索引中使用的字节数，越短越好
ref：显示索引的哪一列被使用了，最好是个常数
rows：找到所需记录需要读取的行数，越少越好
Extra
Using filesort：使用了文件排序，不好
Using temporary：使用了临时表，非常耗性能
Using index：使用了【覆盖索引】，效果不错
order by
尽可能在索引列上完成排序操作，根据最佳左前缀法则，否则会产生filesort
双路排序：Mysql4.1之前使用双路排序，扫描两次磁盘，先从磁盘
取出排序字段存在buffer中，再取其他字段，最终的到目标数据
取出排序字段存在buffer中，再取其他字段，最终的到目标数据
单路排序：Mysql4.1之后使用单路排序，从磁盘读取查询需要的所有列，按
照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出
照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出
二者比较：单排效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序
IO，但是它会使用更多的空间，有可能超出sort_buffer的容量导致多次IO，得不偿失
IO，但是它会使用更多的空间，有可能超出sort_buffer的容量导致多次IO，得不偿失
优化策略
order by 时不要使用 select *，容易把sort_buffer占满
增大 sort_buffer_size 参数的设置
增大 max_length_for_sort_data 参数的设置
group by
group by 的优化和order by大体相同
group by 实质是先排序后进行分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大 max_length_for_sort_data 参数的设置+增大 sort_ buffer_ size 参数的设置
where 高于 having，能写在 where 限定的条件就不要写 having 了
分库分表
在架构设计时做分库，根据不通的业务模块进行划分，业务之间不会影响性能
单表超过500万时读写性能明显下降，此时需要考虑分表
主键ID不是使用的自增ID，而是使用单独数据表分批生成
路由策略
根据主键ID对机器数量取模
采用分组+切片来解决高并发数据量过大的问题
事务
ACID
原子性 Atomicity
一致性 Consistency
隔离性 Isolation
持久性 Durability
隔离级别
读未提交：会出现脏读
读已提交：会出现不可重复读
可重复读(默认)：会出现幻读
串行化：使用表锁，降低并发性能
锁
抽象锁
乐观锁
引入版本号或时间戳
实现层面：业务代码
适用并发量小的场景
悲观锁
实现
共享锁（读锁）
排他锁（写锁）
行锁、表锁、读写锁
实现层面：数据库本身
适用并发量大的场景
粒度锁
表锁
开销小，加锁快；不会出现死锁
锁定粒度大，发生锁冲突的概率最高，并发度最低
默认搜索引擎：MyISAM
页锁
开销、加锁时间、锁粒度都界于表锁和行锁之间；会出现死锁；并发度一般
行锁
开销大，加锁慢；会出现死锁
锁定粒度最小，发生锁冲突的概率最低，并发度也最高
默认搜索引擎：InnoDB
间隙锁（防止发生幻读的关键）
当我们使用范围条件检索数据并请求共享或排他锁时，InnoDB会给条件范围内但不存
在的记录加间隙锁，如果用相等条件请求一个不存在的记录并加锁时也会触发间隙锁
在的记录加间隙锁，如果用相等条件请求一个不存在的记录并加锁时也会触发间隙锁
上锁与排查
表锁
上锁
隐式（默认）
select（读锁）
insert、update、delete（写锁）
显式上锁（手动）
lock table table_name read;
lock table table_name write;
解锁（手动）
unlock table table_name;（单表）
unlock tables;（所有表）
排查
show open tables;（查看）
show status like 'table%';（分析）
table_locks_waited：因表级锁争用而等待的次数
table_locks_immediate：产生表级锁定的次数
行锁
上锁
隐式（默认）
select（快照读，不加锁）
MVCC
insert、update、delete（当前读，加写锁）
显式上锁
select * from table_name lock in share mode;
select * from table_name for update;（写锁）
解锁
提交事务（commit）
回滚事务（rollback）
kill 阻塞进程
排查
show status like 'innodb_row_lock%';
innodb_row_lock_current_waits：当前等待锁的数量
innodb_row_lock_time：锁定总时长
innodb_row_lock_time_avg：平均等待时长
innodb_row_lock_time_max：最长一次等待时间
innodb_row_lock_waits：系统启动到现在总共等待的次数
MVCC
概念
多版本并发控制，增加了隐藏字段
行ID、事务ID、回滚指针、删除标识
有一个read view的概念，对比最早事务ID和最迟事务ID确定本次事务的可见范围
应用
读已提交：事务中每条select语句都会创建一个新的read view，可以读取到其他事务提交的内容
可重复读：read view是在执行事务中第一条select语句时创建read view，后续操作都是复用这个read view
行锁实现算法
Record Lock
记录锁：锁定一个行记录
Gap Lock
间隙锁：锁定一个区间，不包括记录本身
危害：间隙锁的区间锁定可能锁定某些不存在的键
值，造成锁定期间，无法插入区间内的任何数据
值，造成锁定期间，无法插入区间内的任何数据
Next-key Lock（默认）
Record Lock+Gap Lock：锁定记录+区间
死锁
概念
指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象
产生条件
互斥
请求与保持
不可剥夺
循环等待
解除死锁
查看：show engine innodb status \G;
查看是否锁表，查看进程，杀死进程
查看当前锁定和等待的事务，杀死进程
如何避免
加锁顺序一致，尽可能一次锁定所需数据行
保持简短的事务，单次操作数量不宜过多
使用较低的隔离级别
合理使用索引，减少不必要的索引
主从复制
修改配置
一主一丛
主机：vim/etc/my.cnf
如有必要可关闭防火墙
如有必要可关闭防火墙
主服务器唯一ID：server-id=1（双主切忌冲突）
启用二进制日志：log-bin=mysql-bin
设置不要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
设置需要复制的数据库：binlog-do-db=testdb（自定义）
设置binlog格式：binlog_format=STATEMENT
从机：vim/etc/my.cnf
从服务器唯一ID：server-id=2
启用中继日志：relay-log=mysql-relay
双主双从
两台主机需额外配置
（一主一从请忽略）
（一主一从请忽略）
在作为从库时，有写入操作也要更新二进制日志：log-slave-updates
设置自增长字段每次递增的步长：auto-increment-increment=2
设置自增长字段的起始值：auto-increment-offset=1 和 2
双主相互复制，分别执行
操作命令如右所示：
操作命令如右所示：
CHANGE MASTER TO MASTER_HOST='另一台主机地址',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='MYSQL-BIN.000001',
MASTER_LOG_POS=154;
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='MYSQL-BIN.000001',
MASTER_LOG_POS=154;
start slave;
show slave status\G;
重启服务使配置生效：systemctl restart mysqld
设置权限
主机
mysql -uroot -proot
mysql> ：GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
mysql> ：show master status;
查看File(日志名)和Position(切入点)，用来配置从机
从机
CHANGE MASTER TO MASTER_HOST='10.211.55.6',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='MYSQL-BIN.000001',
MASTER_LOG_POS=154;
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='MYSQL-BIN.000001',
MASTER_LOG_POS=154;
start slave;
show slave status\G;
Slave_IO_Running：Yes
Slave_SQL_Running：Yes
如之前配置过主从，需重置
mysql> ：stop slave;
停止从服务器的复制功能
mysql> ：reset master;
重新配置主从
0 条评论
下一页
