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语句走了索引没）。