mysql高性能3

mysql高性能3

QA

事务执行过程中系统宕机的处理方案

事务执行ACID保证的是事务本身的完整(例如子事务,子update),系统宕机是来自事务外部机器的问题，需要保证的事务宕机重启后能继续执行事务，一般通用解决方案是先写日志.例如mysql的redolog,业务场景的pre-record.

遇到的问题：单sql超时问题

事务级别和对应的并发问题

read uncommit:(dirty read,脏读，读到的是错误数据) read commit:(unrepeatable read,不可重复读,同一事务中前后读到的数据值不一样)，解决了脏读 => mvcc repeatable read:(幻读，同一事务中,前后读到的数据行数不一样);解决了重复读问题 => next-key-lock serializable: 不存在并发的问题

mvcc + record-lock => 解决不可重复读的问题 mvcc + next-key-locking => 解决幻读问题

通用配置RR + MVCC 不存在并发问题; MVCC：处理的事务版本问题； next-key-lock解决的锁的范围问题

死锁发生和解决

死锁检测机制，死锁超时机制

事务日志

write ahead logging: 先写缓存，在追加持久化日志(顺序IO:redolog)，最后持久化修改(随机IO:binlog), 总计写2次磁盘.

binlog: raw + statement
redolog: redolod -> binlog
undolog: 记录mvcc版本

MVCC

DB_TRX_ID:当前已经提交的事务ID(事务ID是递增的)
DB_ROLL_PTR:回滚指针(undo-log指针),头部插入

通过trx_id + undolog保存数据快照实现,TX_ID系统版本号=事务ID

sql优化

mysql函数没有使用查询缓存

SELECT username FROM user WHERE signup_date >= CURDATE(); CURDATE() 替换为常量可以使用查询缓存

使用limit 1 查询单行记录

SELECT 1 FROM user WHERE country = 'China' LIMIT 1 即使只有一条数据也不会去fetch cursor

使用 ENUM 而不是 VARCHAR
从 PROCEDURE ANALYSE() 取得建议
尽可能的使用 NOT NULL
拆分大的 DELETE 或 INSERT 语句

mvcc-case

初始数据selet * from t;

没有画出rowid,rowid对多版本并发控制没用,主要用来建聚簇索引,trx_id=80当前已经提交的事务id(insert操作),roll_pointer是undo-log日志指针(insert,update.delete都会生成对应的undo-log);

执行2个事务:id=100和id=200

操作执行顺序:3-4-5-6;

执行的结果

回滚指针:头插法,trx_id当前最新的事务id=200,undolog日志的格式...(id,data,trx_id,roll_ptr)

tx-case

事务隔离与mvcc的关系;mvcc实现了第2，3级(read-commit,repeat-read)的读控制.

事务分析insert into T(c) values(1);

read-uncommit

读数据与事务的commit无关,有啥读啥

v1=2,v2=3,v3=2; 不受事务的提交影响

read-commit

读数据只能读到commit的数据;每次读都构建一次readView(每一次select都构建新的view)

v1=1(未提交读不到),v2=2,v3=2

repeat-read

同一事务内可重复读(前提是同一事务内的2次select),只能读到提交的数据(毕竟比等级2高),事务执行期间,只有第一次读时构建一次readview(同一select-sql,只在第一次查询时构建readview，在同一事务中)

同一事务中,事务视图只会构建一次.

事务A-uncommit中: v1=1(B未提交,读不到),v2=1(B提交,但是都在同一事务内,使用1的结果);

事务A-commit后:v3=2最新的B提交数据;

原则:事务执行期间,前后select看到的数据是一样的。

serializable

事务完全串行执行:先执行A,在执行B;

v1=1,v2=1(B未执行) v3=2(B已经commit)

页分裂-页合并

性能:增加页空间的利用率,避免频繁的出现页分裂和页合并;
空间: bigint=>8byte 利用率高; [要使用自增主键PK]

tx-error

read-uncommited,读到未提交的数据,即脏读,数据没有提交就是脏数据
read-commited: 不会出现脏数据,但是不可重复度,前后2次读到的数据值不一样,被别的事务update了
repeated-read: 解决了不可重复度,但是出现了幻读,前后2次读到的数据量不一致
serializable: 串行没有问题

死锁的检查/排除

检查 : 检查排他锁是否存在循环竞争关系排除 : 超时回滚,执行超时把行少的数据回滚

autocommit

--mysql默认自提交;每个语句都是按事务执行
show variables like 'autocommit'

加锁

-- 显示加锁; mysql默认是隐示自动加锁,insert,update,delete
-- select 手动加锁
select .. lock in share mode;  -- S锁
select .. for update;          -- X锁

锁的协议-2阶段锁; precommit预提交,写redolog,commit提交写binlog; (pre_commit -> redolog -> commit -> binlog)

analyze-table

-- 索引优化,1. 重新计算索引区分度
analyze table;

mysql-note-3