sql查询百万数据优化
- 为何要优化自己的sql?
- 我是怎么做到优化的?
- sql语句优化
- 数据库优化
- 总结
为何要优化自己的sql?
今天点击分页的时候,点击到86363页,出现了系统在转圈圈赚了快一分钟,寻思着以为电脑坏了,仔细一看,竟是代码的问题,于是乎请教导师之后。emmm。看来是我见识短浅了。为了提高系统的响应能力,不得不进行优化sql处理应对万级数据了。
我是怎么做到优化的?
sql语句优化
其实都知道,mysql支持索引机制,在查询条件的时候,尽量查询索引。但我之前都没有走,以为查出来就是王道。
- 分页
假如直接查询
select * from table limit 1000000,1000;
时间长达一分钟以上.
优化
select id from table limit 1000000,1000;
select * from table where id in (刚才查出来的id列);
这时你会发现飞一样的感觉,原因是查出来的id值列是走索引,再利用索引覆盖的机制,进行对id列进行取值,时间自然减少了。值得注意的是,在两个查询的id的时候,两者对id排序是有不同的,所以要对id的排序做规定,才能保证两者相同
- in 与 between
select * from table where id in (id列);
假如id为有顺序的,避免in的使用不走索引。建议采用
select * from table where id between 初始值 and 结尾值;
两者左右都是包括在内的。
- or 与 union
亲测有效
select * from table where id =10000 or id = 20000;
优化
- select * from table where id = 10000;
- union
- select * from table where id = 20000;
时间也降低了不少,建议在数量丰富的数据库里查询。这样效果会明显。
- 模糊查询
select * from table where name like "%chen%";
假如使用前缀索引,在开头避免添加%
select * from table where name like "chen%";
- 索引最前匹配
假如有索引 index_for_student(name,ago)
以下是不走索引,原因是复合索引会走最前匹配,故单单ago是不支持索引的。
select * from student where ago = 16;
走索引情况
select * from student where name = '陈';
select * from student where name = '陈' and ago = 16
- 尽量不要select * ,需要查什么字段就什么字段
- 在反复出现where 和 order by 下的查询语句要添加索引
- 不要将表达式至于左边
select * from student where 15*9 = name;
- order by的字段最好与where 后面字段一致
select * from table where ago =1 order by ago;
数据库优化
当然,在平常sql语句优化,在建立数据库时也要进行优化。
- 文本索引可以使用fulltext 全文索引来,但数据量大的建议走es,秒级查询
- 在将来可能出现大量查询的字段添加索引,或者复合索引
- 选择合适的字段类型,比如varchar 替代char。字段设计也要考虑用数字替代一些文字,例如性别可以用 0 或1 表示
- 选择合适的引擎
- 用‘’或者0替代 null字段,查询速度会上升许多
select * from table where name = null;
select * from table where name = '';
尝试一下是否有作用,自己尝试之后两者区别巨大。
- 索引不是越多越好,也会存在相应的内存损耗,导致查询效果慢,所以要根据需求,在达到最优的情况下(没有最优,只有相对),设计符合系统需求的设计。
总结
索引的好处可以明显看出来,但是像维生素一样,涉入过多,自己身体也会发生不良反应,所以具体需求具体设计。
在进行对数据库查询时,要根据自身作为用户去体会,而非自己能完成工作就可以,要往最优、最快、以及最实践的方法去修改自己的代码。
本文会持续更新(只是整理与笔记,具体还是自己尝试是否符合自己,可以用explain 解释自己的sql语句走了索引没)。