SQL语句百万数据量优化方案

Sql查询速度慢的原因：
1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)
2、I/O吞吐量小，形成了瓶颈效应。
3、没有创建计算列导致查询不优化。
4、内存不足
5、网络速度慢
6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）
7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)
8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。
9、返回了不必要的行和列
10、查询语句不好，没有优化

 

先进行查询优化（索引/缓存等）再考虑分库分表，添加ES的支持，利用文档存储，采用分布式数据库（例如：TiDB等）,具体的问题还是需要更具具体的业务场景进行定位。

 

 

一、理解SQL执行顺序

在sql中，第一个被执行的是from语句，每一个步骤都会产生一个虚拟表，该表供下一个步骤查询时调用。

比如语句：

select top 10 col1,col2,max(col3) from user where id>1 group by col1,col2 having count(col1)>1 order by col2

标准sql执行顺序是：

1：from 组装来自不同表的数据，如 from user或者，from user as u join goodsOrder as r on u.id=r.userId

2：where 过滤符合查询条件的数据，如：id>1000

3：group by：将查询数据进行分组

4：使用sum等聚合函数

5：使用having进行筛选分组

6：执行select语种

7：执行排序语句

 

如：

select count(gid),gname from shopping_goods 

where gcid=1 

group by gname having count(gid)>1 

order by count(gid) desc

1：首页查询 shopping_goods 表，得到表中的数据

2：执行 where，过滤出 gcid=1 的商品

3：对 gname 进行分组 group by

4：使用聚合函数count()，计算出商品类型为1，不同商品名称的数量

5：使用 having，过滤出类型为1，商品统计数量大于1的商品

6：执行 select 语句

7：执行 order by，按照商品数量降序排列

 

二、百万数据量优化

这里只介绍查询和修改的方法，如果是系统优化，需要从表结构、索引、表分区等方面处理。

1：合理使用索引，在一个大数据量的表中，并不是索引越多越好，索引越多，写操作越慢，建议在以下字段上创建索引。

• 在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引；
• 在频繁进行排序或分组（group by/order by操作）的列上建立索引；
• 在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如雇员表的“性别”列上只有“男”与“女”两个不同值，因此就不必要建立索引，如果建立索引不但不会提高查询效率，反而会严重降低更新速度。
• 如果待排序的列有多个，可以在这些列上建立复合索引（compound index）
• 使用系统工具。在可疑的索引上进行检查。在一个数据库服务器上，索引可能失效或者因为频繁操作而使得读取效率降低，如果一个使用索引的查询不明不白的慢下来，可以试着使用系统工具检查索引的完整性，必要时进行修复。另外，当数据库表更新大量数据后，删除并重建索引可以提高查询速度。

2：尽量少用或不用MySQL自带的函数

• 如需计算两个日期之前的差值，或者得到日期中的整数部分，建议查询完毕后用java程序来计算，不要什么都让数据库来做.

• substring(name,1,3)
  = ’abc’,建议修改为 name like 'abc%'

   

3：尽量不要在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，强烈建议 where 涉及的列，不要留空，创建表时赋予初始值

【错误】

select id from table where name is not null

【正确】

create table table(name varchar(20) default '')

 

4：应尽量避免在 where 子句中使用 != 或者 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

【错误】

select id from table where id <> 100

 

5：应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，建议使用 union all 来替代 or

select id from table where num=1 or Name = 'zhangsan'

建议修改为

select id from table where num=1

union all

select id from table where name = 'zhangsan'

 6：建议用 exists 来替代 in ,能用 between 就不用 in 。如： age in (20,21,22) 建议修改为 age between 20 and 11

select id from t where role in (select rid from role where rName
= '经理')

建议修改为

select id from t as a where exists (select rid from
role as b where a.role = b.rid
and rName = '经理')

 

 7：like的用法

除了 title like '重庆%'，其它使用方法

(不能前置百分号)(如：title like '%王%' title like '%天')也将导致全表扫描

 

8：where 中尽量不出现表达式计算

如： 

select id from t where num/2 = 100

应该改为：

select id from t where num = 100*2

 

9：update语句，如果只改1~2个字段，不要 update 全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。强烈建议修改时使用动态sql语句，类似hibernate的dynamic-update = true，不过hibernate需要将修改对象通过id查询出来，才会动态修改，如果是普通sql，直接组装就可以。

 

10：对于多张大数据量（这里几百条就算大了）的表join，要先分页再join，否则逻辑度会很高，性能很差

 

11：尽量使用数字型字段，若只含有数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型只需要比较一次就够了。

 

12：不建议使用 select * from t，用具体的字段列替代 " * " ，不要返回用不到的任何字段

 

13：尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过一万行，那么就应该考虑改写

 

14：在所有存储过程和触发器的开始处设置 set nocount on，在结束处设置 set nocount off。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

 

15：尽量避免大事务操作，提高系统并发能力。并且不要事务嵌套。不要在事务中去调用其它系统的接口，不要在事务中耗时操作，不然死锁并伴你左右。

 

16：尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。(笔者曾经处理过，从3000万手机号码库中，模糊查询出上万个手机号码，这种需求是客户硬性要求，就要通过executorservice了，不要直接写sql查)

 

17：如果数据库是mysql，一定要利用数据库引擎，不同业务要使用不同的数据库引擎。比如常用的innodb和myisam,innodb支持事务，支持外键，锁是表级锁，缺点是查询速度慢，Myisam 的执行速度更快，性能更好，但不支持外键，不支持事务，锁是行锁级。比如日志表，数据量大，强烈建议使用myisam引擎.

 

18：在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。

 

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

转载于:https://www.cnblogs.com/Roni-i/p/10747925.html