数据库应用排查优化

最近部门内在做数据方面的优化，这里总结一下。

一般数据库优化可以从以下几个方面入手：

大事务

慢查询

锁表

死锁

锁降级

主从延迟

大事务排查优化

1. 排查带有Trasactional注解的方法调用栈中是否存在rpc、http请求等重度操作，考虑事务拆解，实现最终一致性；
2. 排查Trancation接口调用begin和commit、rollback语句之间的事务操作内容是否存在1中所述情况；
3. 排查1和2中所述事务是否存在跨库、跨表等重度操作；
4. 排查1和2中所述事务中是否存在慢查询、锁表操作、死锁以及可降级锁。

慢查询排查优化

1. 慢查询告警sql语句及查询语句中不带有索引字段；
2. 查询语句中采用联合索引，但没有出现联合索引中左优先的字段；
   index: KEY `idx_ab` (`a`,`b`)     sql: select XXXX where b=1
3. 查询语句中带有内置函数如NOW()、CURDATE()等导致无法开启查询缓存优化；
4. 排查应用需要导致的扫表语句，考虑分页处理；

锁表排查优化

1. 更新、删除语句中条件不带有索引字段导致锁表；
   table：a,b  index(b)          sql: update(delete) XXXX where a=1
2. 更新、删除语句中采用联合索引，但没有出现联合索引中左优先的字段；
   index: KEY `idx_ab` (`a`,`b`)          sql: update(delete) XXXX where b=1
3. 更新、删除避免跨表join

死锁排查优化

1. 并发操作中，加锁顺序不一致
   并发1：lock a,b       并发2：lock b,a
2. 数据表并发更新潜在索引不一致
   table: id, name  pk(id), index(name)  并发1：update table set name='1' where name='2'    并发2：update table set name='1' where id=2;
3. 带有唯一键的表高并发写入，同时有三个及以上的会话写入同一个唯一索引值
   table: id, name unique(name)    并发1：insert into table values(1, "test")  插入后回滚    并发2：insert into table values(1, "test")    并发3：insert into table values(1, "test")
4. 带有索引的表中进行并发事务操作，操作中包含查询加排他锁和插入数据，且两个并发中的索引值在数据库索引同一范围内
   table: a, b  index(b)

    

   	T1(36831)	T2(36832)
   mysql> select * from t; +----+------+ | a | b | +----+------+ | 1 | 2 | | 2 | 3 | | 3 | 4 | | 11 | 22 | +----+------+	begin;	begin;
   	select * from t where b = 6 for update;
   		select * from t where b = 8 for update;
   	insert into t values (4,5);
   		insert into t values (4,5);
   		ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
   	Query OK, 1 row affected (5.45 sec)

锁降级优化

1. 排查select XXX for update, 考虑修改为乐观锁；
   old=select value XXXX        update XXX where index=1 and value=old

主从延迟优化

1. 应用中存在对数据高并发写和读，主从延迟导致查询时从库数据不一致，建议对该类查询改为主库查询；