大表和大事务的常用处理方案

　　影响mysql的性能因素：

超高的QPS和 TPS

　　QPS（Queries Per Second 每秒处理的查询量）：假设处理一个SQL 需要 10ms , 1s 最多也就处理100个，那么QPS < = 100 ,如果 100ms处理一个呢？那 QPS <=10 ,可以推断出SQL的执行效率队QPS的影响很重要。一般来说，80%的数据库问题都可以通过SQL优化来解决。

TPS(Transactions Per Second,事务数/秒，这个完整的事务包括用户请求服务器，服务器内部处理，服务器返回信息给用户三个过程)

　　MySQL数据库中的QPS和TPS的计算方法

Questions = SHOW GLOBAL STATUS LIKE 'Questions';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
QPS=Questions/Uptime

-----------------------

Com_commit = SHOW GLOBAL STATUS LIKE 'Com_commit';
Com_rollback = SHOW GLOBAL STATUS LIKE 'Com_rollback';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
TPS=(Com_commit + Com_rollback)/Uptime

　大表定义：1、记录超过1千万 2、表数据文件巨大，超过10G

　风险：1、对查询的影响，从超巨数据中，查找区分度不高的数据，将导致大量的磁盘I/O,有可能导致数据库hang死，从而产生大量的慢查询，需要特别关注解决。

　　　　 2、对DDL的影响：建立索引耗时特别长，风险：会引起主从延迟修改表结构的话，需要长时间锁表

　如何应对大表

1. 分库分表（分表主键如何选择，分表后跨分区的查询和统计如何解决）慎重！！！
2. 对历史数据进行归档（归档时间点的选择、如何高效的归档）

　大事务：运行时间比较长，操作数据比较多的事务

　风险：1、锁定太多的数据，容易造成阻塞和超时

　　　　事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态

2、回滚时间耗时较长，回滚过程中也容易阻塞

　　　　实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

3、容易造成主从延迟：因为主库上必须等事务执行完成才会写入binlog，再传给备库。所以，如果一个主库上的语句执行10分钟，那这个事务很可能就会导致从库延迟10分钟

　 措施：

　　基于两阶段锁协议

　　两阶段锁协议是什么？在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放，基于两阶段锁协议可以做这样的优化：如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

从顾客A账户余额中扣除电影票价;
给影院B的账户余额增加这张电影票价; 3. 记录一条交易日志。也就是说，要完成这个交易，我们需要update两条记录，并insert一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么，你会怎样安排这三个语句在事务中的顺序呢? 试想如果同时有另外一个顾客C要在影院B买票，那么这两个事务冲突的部分就是语句2了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句2安排在最后，比如按照3、1、2这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

避免一次处理太多数据

移除事务中不必要的 select操作

通过SETMAX_EXECUTION_TIME命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间

监控 information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill

posted on 2020-11-29 18:53 溪水静幽阅读(444) 评论(0) 收藏举报