如何减少开发过程中的bug-数据库篇

1.1慢查询

1.1.1 是否命中索引

提起慢查询，我们马上就会想到加索引。如果一条SQL没加索引，或者没有命中索引的话，就会产生慢查询。

索引哪些情况会失效？

• 查询条件包含or，可能导致索引失效

• 如果字段类型是字符串，where时一定用引号括起来，否则索引失效

• like通配符可能导致索引失效。

• 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

• 在索引列上使用mysql的内置函数，索引失效。

• 对索引列运算（如，+、-、*、/），索引失效。

• 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。

• 索引字段上使用is null， is not null，可能导致索引失效。

• 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

1.1.2 数据量大，考虑分库分表

单表数据量太大，就会影响SQL执行性能。我们知道索引数据结构一般是B+树。因此，数据量大的时候，建议分库分表。分库分表的中间件有mycat、sharding-jdbc。

1.2 死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

暂时无法在飞书文档外展示此内容

MySQL内部有一套死锁检测机制，一旦发生死锁会立即回滚一个事务，让另一个事务执行下去。但死锁有资源的利用率降低、进程得不到正确结果等危害。

1.2.1 9种情况的SQL加锁分析

要避免死锁，需要学会分析：一条SQL的加锁是如何进行的?一条SQL加锁，可以分9种情况进行探讨：

• 组合一：id列是主键，RC隔离级别

• 组合二：id列是二级唯一索引，RC隔离级别

• 组合三：id列是二级非唯一索引，RC隔离级别

• 组合四：id列上没有索引，RC隔离级别

• 组合五：id列是主键，RR隔离级别

• 组合六：id列是二级唯一索引，RR隔离级别

• 组合七：id列是二级非唯一索引，RR隔离级别

• 组合八：id列上没有索引，RR隔离级别

• 组合九：Serializable隔离级别

1.2.2 如何分析解决死锁？

分析解决死锁的步骤如下：

• 模拟死锁场景

• show engine innodb status;查看死锁日志

• 找出死锁SQL

• SQL加锁分析

• 分析死锁日志（持有什么锁，等待什么锁）

• 熟悉锁模式兼容矩阵，InnoDB存储引擎中锁的兼容性矩阵。

1.3 一些SQL的经典注意点

1.3.1 limit大分页问题

limit大分页是一个非常经典的SQL问题，我们一般有这3种对应的解决方案

方案一： 如果id是连续的，可以这样，返回上次查询的最大记录(偏移量)，再往下limit

select id,name from employee where id>1000000 limit 10.

方案二: 在业务允许的情况下限制页数：

建议跟业务讨论，有没有必要查这么深度的分页啦。因为绝大多数用户都不会往后翻太多页。谷歌搜索页也是限制了页数，因此不存在limit大分页问题。

方案三： 利用延迟关联或者子查询优化超多分页场景。（先快速定位需要获取的id段，然后再关联）

SELECT a.* FROM employee a, (select id from employee where 条件 LIMIT 1000000,10 ) b where a.id=b.id

1.3.2 修改、查询数据量多时，考虑分批进行。

我们更新或者查询数据库数据时，尽量避免循环去操作数据库，可以考虑分批进行。比如你要插入10万数据的话，可以一次插入500条。