SQL 优化

负向查询不能使用索引

1	select name from user where id not in (1,3,4);

应该修改为:

1	select name from user where id in (2,5,6);

前导模糊查询不能使用索引

如:

1	select name from user where name like '%zhangsan'

非前导则可以:

1	select name from user where name like 'zhangsan%'

建议可以考虑使用 Lucene 等全文索引工具来代替频繁的模糊查询。

数据区分不明显的不建议创建索引

如 user 表中的性别字段，可以明显区分的才建议创建索引，如身份证等字段。

字段的默认值不要为 null

这样会带来和预期不一致的查询结果。

在字段上进行计算不能命中索引

1	select name from user where FROM_UNIXTIME(create_time) < CURDATE();

应该修改为:

1	select name from user where create_time < FROM_UNIXTIME(CURDATE());

最左前缀问题

如果给 user 表中的 username pwd 字段创建了复合索引那么使用以下SQL 都是可以命中索引:

select username from user where username='zhangsan' and pwd ='axsedf1sd'

select username from user where pwd ='axsedf1sd' and username='zhangsan'

select username from user where username='zhangsan'

但是使用

1	select username from user where pwd ='axsedf1sd'

是不能命中索引的。

如果明确知道只有一条记录返回

1	select name from user where username='zhangsan' limit 1

可以提高效率，可以让数据库停止游标移动。

不要让数据库帮我们做强制类型转换

1	select name from user where telno=18722222222

这样虽然可以查出数据，但是会导致全表扫描。

需要修改为

1	select name from user where telno='18722222222'

如果需要进行 join 的字段两表的字段类型要相同

不然也不会命中索引。

数据库水平垂直拆分

当数据库量非常大的时候，DB 已经成为系统瓶颈时就可以考虑进行水平垂直拆分了。

水平拆分

一般水平拆分是根据表中的某一字段(通常是主键 ID )取模处理，将一张表的数据拆分到多个表中。这样每张表的表结构是相同的但是数据不同。

不但可以通过 ID 取模分表还可以通过时间分表，比如每月生成一张表。
按照范围分表也是可行的:一张表只存储 0~1000W的数据，超过只就进行分表，这样分表的优点是扩展灵活，但是存在热点数据。

按照取模分表拆分之后我们的查询、修改、删除也都是取模。比如新增一条数据的时候往往需要一张临时表来生成 ID,然后根据生成的 ID 取模计算出需要写入的是哪张表(也可以使用分布式 ID 生成器来生成 ID)。

分表之后不能避免的就是查询要比以前复杂，通常不建议 join ，一般的做法是做两次查询。

垂直拆分

当一张表的字段过多时则可以考虑垂直拆分。
通常是将一张表的字段才分为主表以及扩展表，使用频次较高的字段在一张表，其余的在一张表。

这里的多表查询也不建议使用 join ，依然建议使用两次查询。

拆分之后带来的问题

拆分之后由一张表变为了多张表，一个库变为了多个库。最突出的一个问题就是事务如何保证。

两段提交

最终一致性

如果业务对强一致性要求不是那么高那么最终一致性则是一种比较好的方案。

通常的做法就是补偿，比如一个业务是 A 调用 B，两个执行成功才算最终成功，当 A 成功之后，B 执行失败如何来通知 A 呢。

比较常见的做法是失败时 B 通过 MQ 将消息告诉 A，A 再来进行回滚。这种的前提是 A 的回滚操作得是幂等的，不然 B 重复发消息就会出现问题。