1.大量数据的update和delete操作分批执行
原始SQL语句:
update status=0 FROM `coupon` WHERE expire_date <= #{currentDate} and status=1;
如果大量数据需要更新状态,执行这条SQL可能会堵死其他SQL,分批处理伪代码如下:
int pageNo = 1;
int PAGE_SIZE = 100;
while(true) {
List<Integer> batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}');
if (CollectionUtils.isEmpty(batchIdList)) {
return;
}
update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}')
pageNo ++;
}
2.负向条件查询
负向条件查询通常不能使用索引,比如<>,!=等操作符。
举例:查询金额不为100元的订单:
select id from orders where amount != 100;
如果金额为100的订单极少,这种数据分布严重不均的情况下,有可能使用索引。鉴于这种不确定性,可以采用union聚合搜索来替代负向条件查询,改写方法如下:
(select id from orders where amount > 100)
union all
(select id from orders where amount < 100 and amount > 0);
3.前导模糊查询
前导模糊查询不能使用索引,举个例子(field已建立索引):
SELECT column FROM table WHERE field like '%keyword%';
这个查询未命中索引,换成下面的写法:
SELECT column FROM table WHERE field like 'keyword%';
去除了前面的%查询将会命中索引,但是一定要前后模糊匹配呢?全文索引fulltext可以尝试一下,但Elasticsearch才是终极武器。
4.数据区分度不大的字段不宜使用索引
select * from user where sex = 1;
原因:性别一般只有男和女,每次过滤掉的数据很少,不宜使用索引。
经验上,能过滤80%数据时就可以使用索引。对于订单状态,如果状态值很少,不宜使用索引,如果状态值很多,能够过滤大量数据,则应该建立索引。
5.OR优化
在Innodb引擎下or无法使用组合索引,比如:
select id,product_name from orders where mobile_no = '13421800407' or user_id = 100;
OR无法命中mobile_no + user_id的组合索引,可采用union,如下所示:
(select id,product_name from orders where mobile_no = '13421800407')
union
(select id,product_name from orders where user_id = 100);
此时id和product_name字段都有索引,查询才最高效。
6.IN优化和JOIN优化
IN适合主表大子表小,EXIST适合主表小子表大。由于查询优化器的不断升级,很多场景这两者性能差不多一样了。
尝试改为JOIN查询,举例如下:
select id from orders where user_id in (select id from user where level = 'VIP');
采用JOIN如下所示:
select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';
JOIN的实现是采用Nested Loop Join算法,就是通过驱动表的结果集作为基础数据,通过该结果数据作为过滤条件到下一个表中循环查询数据,最后合并结果。
如果有多个JOIN,则将前面的结果集作为循环数据,再次到后一个表中查询数据。
驱动表和被驱动表尽可能增加查询条件,满足ON的条件而少用Where,用小结果集驱动大结果集。
被驱动表的JOIN字段上加上索引,无法建立索引的时候,设置足够的Join Buffer Size。
禁止JOIN连接三个以上的表,尝试增加冗余字段。
7.避免在属性上进行计算
通常在查询条件列运算会导致索引失效,如下所示:
查询当日订单
select id from order where date_format(create_time,'%Y-%m-%d') = '2019-07-01';
date_format函数会导致这个查询无法使用索引,改写后:
select id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';
此外,把计算放到业务层而不是数据库层,除了节省数据的CPU,还有意想不到的查询缓存优化效果:
select id from order where date < = CURDATE();
应该优化为:
$curDate = date('Y-m-d');
$res = mysql_query( 'select id from order where date < = $curDate');
原因:
释放了数据库的CPU,多次调用,传入的SQL相同,才可以利用查询缓存。
8.单条查询使用Hash索引性能更好
比如用户相关查询:
select * from user where uid=?
select * from user where login_name=?
原因:
B-Tree索引的时间复杂度是O(log(n));
Hash索引的时间复杂度是O(1)。
9.Limit优化
Limit用于分页查询时越往后翻性能越差,如下所示:
select * from orders order by id desc limit 100000,10
耗时0.4秒
select * from orders order by id desc limit 1000000,10
耗时5.2秒
解决原则:缩小扫描范围,先筛选出ID,写法如下:
select * from orders where id > (select id from orders order by id desc limit 1000000, 1) order by id desc limit 0,10
耗时0.5秒
如果查询条件仅有主键ID,写法如下:
select id from orders where id between 1000000 and 1000010 order by id desc
耗时0.3秒
10.允许为null的列,查询有潜在大坑
单列索引不存null值,复合索引不存全为null的值,如果列允许为null,可能会得到不符合预期的结果集:
select * from user where name != 'shenjian';
如果name允许为null,由于索引不存储null值,结果集中不会包含这些记录。
11.复合索引最左前缀,不是指SQL语句的where顺序要和复合索引一致
建立(login_name, passwd)的复合索引:
select * from user where login_name = ? and passwd = ?;
select * from user where passwd = ? and login_name = ?;
结果是都能够命中索引;
select * from user where login_name = ?;
也能命中索引,满足复合索引最左前缀。
select * from user where passwd = ?;
不能命中索引,不满足复合索引最左前缀。
12.使用ENUM而不是字符串
ENUM保存的是TINYINT,别在枚举中搞一些“已处置”,“未处置”这样的字符串,字符串空间又大,效率又低。
13.如果明确知道只有一条结果返回,limit 1能够提高效率
select id from user where login_name = ?;
可以优化为:
select id from user where login_name = ? limit 1;
原因:
数据库并不知道只有一条结果,明确告诉它,让它主动停止游标移动。
14.强制类型转换会全表扫描
select * from user where phone = 13800001234;
应该改为:
select * from user where phone = ‘13800001234’;
参考文章: