在一个千万级的数据库查寻中，如何提高查询效率？

目录

一、数据库设计方面

引擎选择

索引选择

表结构设计

二、SQL语句优化

避免索引失效

避免长事务

避免返回大量数据

三、Java方面

---

假设前提是：单库单表千万量级

主要从3方面来考虑吧。

一、数据库设计方面

引擎选择

1、针对不同的场景使用不同的数据库引擎，多读少写用MyISAM，少读多写用InnoDB。

索引选择

      不是约多约好，索引不一定有效。这个原因是索引的数据结构

2、索引数量：建索引要考虑最左匹配原则，一个表的索引数量最好不要超过6个，会降低insert 和update的效率。

3、在哪些字段建立索引呢？

• 主键
• 经常需要搜索的列
• 经常需要根据范围搜索
• 经常需要排序的列
• 经常使用WHERE子句中的列

表结构设计

4、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

 

二、SQL语句优化

避免索引失效

• 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
• 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以改为union all

select id from t where num=10 or num=20 

可以这样查询：

select id from t where num=10 union all select id from t where num=20

• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

• in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用 between 就不要用 in 了
• 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

select id from t where num/2=100 

应改为: 

select id from t where num=100*2

• 很多时候用 exists 代替 in 是一个好的选择

select num from a where num in(select num from b) 

用下面的语句替换： 

select num from a where exists(select 1 from b where num=a.num)

避免长事务

• 尽量避免大事务操作，提高系统并发能力。

避免返回大量数据

• 当只要一行数据时使用LIMIT 1
• 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理

三、Java方面

• 尽可能的少造对象。
• 合理摆正系统设计的位置。大量数据操作，和少量数据操作一定是分开的。大量的数据操作，肯定不是ORM框架搞定的。，
• 使用jDBC链接数据库操作数据
• 控制好内存，让数据流起来，而不是全部读到内存再处理，而是边读取边处理；
• 合理利用内存，有的数据要缓存

 

如果数据量太大的话，可以分库分表、冷热数据分离。