[大数据]sql优化

1. 尽量避免全表扫描，考虑在where和order by涉及的列上建立索引。

2. 尽量避免在where子句里对字段进行null值判断，否则会导致引擎放弃索引而进行全表扫描。

3. 尽量避免在where子句中使用!=或<>操作符，否则会导致引擎放弃使用索引而进行全表扫描。

4. 尽量避免在where子句中使用or来连接条件，否则如果or连接条件中一个字段有索引，另一个字段没有索引，会导致引擎放弃使用索引而进行全表扫描。可用联合查询【union all】代替or。

5. 尽量避免在where子句中对字段进行表达式操作或函数操作，这会导致引擎放弃索引而进行全表扫描。

6. 尽量避免更新聚簇【clustered】索引数据列。聚簇索引的顺序就是数据的物理存储顺序，如果该列值被改变会导致整个数据表记录的顺序调整，耗费很大的资源。如果使用聚簇索引，应该选择最合适的索引来作为聚簇索引，一个表最多只能有一个聚簇索引。

7. 尽量避免大事务操作，以提高系统支持高并发的能力。

8. 尽量避免向客户端返回大数据量的数据，可通过限制返回数据的条数【分页】和字段等方式实现。

9. 尽量避免使用DISTINCT关键字去重，可用GROUP BY子句代替，因为GROUP BY可以走索引。

10. 尽量避免隐式数据类型转换。

11. 慎用in和not in，否则会导致全表扫描。对于连续数值，可用between代替in；对于非连续数值，可用exists代替in。

12. 如果是在大段文本中查询字符串，用"%xxx%"和"%xxx"这样的方式进行模糊查询会导致全表扫描，这时应该用全文检索【match(查询字符串) against(查询字符串的编码])】方式代替。

13. 如果在where子句中使用参数，会导致全表扫描，这时可改为在查询语句中强制使用索引。SQL Server里的强制索引方法时with(index(索引名称)),而Mysql里的强制索引方法时force index(索引名称)。

示例代码：

-- select id from article where category=@category;
-- 改为
select id from article with(index(索引名)) where category=@category;

14. 如果作为条件的索引是复合索引，那么必须使用到该索引中的第一个字段作为条件才能保证系统使用该索引，同时应尽可能让字段顺序与索引顺序一致。

15. 如果更新【update】数据时只更新少量字段，那么不要更新多余字段，否则会引起明显的性能损耗，同时产生大量日志。

16. 如果是多张数据表进行关联【join】查询，那么要先对数据表进行分页，然后再关联查询，否则逻辑读写量会很高，导致性能变差。

17. select count(*) from table;这样不带任何条件的统计【count】会引起全表扫描，且没有任何业务意义，一定不要这样写。另外count()会直接忽视所有列的值而直接统计行数，可替代count(*)。

18. 适当使用索引，索引虽然可以提高相应的查询效率，但同时也会降低新增【insert】和更新的效率，因为新增或更新数据时有可能会重建索引。一个建议是一个表的索引数最好不要超过6个。

19. 尽量使用数字类字段类型，因为引擎在处理查询时会逐个比较字符串中每一个字符，而对于数字类字段类型的查询则只需要比较一次。

20. 尽量避免使用select * ，而是应该用具体的字段列表代替*，不要返回用不到的任何字段。

21. 应适当使用临时表，避免频繁创建和删除临时表。例如当需要重复引用大型数据表或者常用数据表中的某个数据集可以使用临时表，但是对于一次性事件建议考虑使用导出表来替代临时表。

22. 创建临时表时，如果一次性新增的数据量很大，那么可以使用select into代替create table，避免产生大量日志。

23. 如果使用了临时表和存储过程，那么在存储过程最后需要将所有临时表显式删除，先使用truncate table，然后使用drop table，这样可以避免系统表的较长时间的锁定。

24. 游标可用于定位到结果集中的某一行，适合于小结果集的逐行处理。但是效率不高，所以在操作大数据量的结果集时，应该尽量避免使用游标。

25. 做Mysql语句优化时，要善用EXPLAIN关键字来查看SQL执行计划。

26. 不使用ORDER BY RAND()。