MySQL语法（事务/锁/MVCC）
2021-02-06 15:47:04 9 举报AI智能生成
MySQL数据库
作者其他创作
大纲/内容
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中，再取其他字段，最终的到目标数据
单路排序：Mysql4.1之后使用单路排序，从磁盘读取查询需要的所有列，按照order by列在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：系统启动到现在总共等待的次数
概念
多版本并发控制，增加了隐藏字段
行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
双主相互复制，分别执行操作命令如右所示：
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(切入点)，用来配置从机
从机
Slave_IO_Running：Yes
Slave_SQL_Running：Yes
如之前配置过主从，需重置
mysql> ：stop slave;
停止从服务器的复制功能
mysql> ：reset master;
重新配置主从
0 条评论
回复 删除
下一页
