针对近期项目中遇到的大数据查询进行总结。
这篇文章花费了我大量的时间,通过网上查找资料和自己的总结归纳,如有不当,请大家指正:
1.在大数据查询中要避免like模糊查询,在进行模糊查询时会进行多次全表遍历,影响查询效率,速度。一定要使用模糊查询,请使用前置模糊查询,即百分号后置
2.避免or条件查询,如非空一般我们会用到(<>'' or <> null),可以使用is not null
3.避免使用降序
4.避免在where子句中使用 !=和 <>操作符,会导致引擎放弃使用索引,进行全表遍历
5.在进行关联查询时,索引应该建立在关联条件上(on)
6.去重使用group by
7.字段属性设置为not null(当查询字段为null时会导致索引失效)
8.避免使用SQL内置函数
9.合理的创建索引(索引是使用空间换时间优化方式,当创建过多的索引时,在新增数据,修改数据时(同时修改了索引列),索引也会同步更新,影响效率)
10.查询优化
-- 使用count(*)的方式 统计行数,不需要对表进行遍历
EXPLAIN SELECT SQL_NO_CACHE count(*) FROM `log`
EXPLAIN SELECT SQL_NO_CACHE count(1) FROM `log`
-- 当字段不存在null
EXPLAIN SELECT SQL_NO_CACHE count(id) FROM `log`
-- 查询一个字段如果字段为null 进行全表遍历
EXPLAIN SELECT SQL_NO_CACHE count(param) FROM `log`
-- ====================================================================
-- 查询列中有不存在索引栏位 全表遍历
EXPLAIN SELECT SQL_NO_CACHE id,group FROM `log` LIMIT 0,10
-- 查询字段中,ttime为索引字段 直接通过索引可以拿到所有列值
EXPLAIN SELECT SQL_NO_CACHE id,ttime FROM `log` LIMIT 0,10
-- 查询多个字段为普通索引 查询类型为all 全表遍历(可尝试组合索引)
EXPLAIN SELECT SQL_NO_CACHE id,ttime,code FROM `log` LIMIT 500,10
-- 当数据表超过10w使用此方式,效率更高,当数据量小时,则直接查询
EXPLAIN SELECT SQL_NO_CACHE
a.id,a.ttime,a.code
FROM
`log` a
INNER JOIN
(SELECT id FROM `log` LIMIT 500,10 ) b
ON
a.id = b.id
如果存在有误的地方,欢迎大家指正。