sql优化十条规则

目录

1. 使用索引注意事项

2. order by 优化

3. 单路、双路排序：

4. group by 优化

5. 延迟关联

6. 关联查询

7. in和exist优化

8. count(*)查询优化

9. 聚族索引和辅助索引

10. 分表分库

---

1. 使用索引注意事项

1. 全值匹配"*"慎用；

2. 遵循最佳左前缀法则，比如在建立联合索引时；

3. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描;

4. 联合索引中使用范围条件（范围之后全失效，不包括本身）;

5. 尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句;

6. mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫;

7. is null,is not null 也无法使用索引;

8. like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作;

9. 字符串不加单引号索引失效；

10. 少用or,用它连接时很多情况下索引会失效；

11. 数据库字符集不一致也会导致索引失效；

12. 数据库查询字段类型和建立索引类型不同会导致索引失效；

13. 在建立联合索引时，尽量保证索引的唯一性，这样数据的离散性更好，定位数据更快；

通过Explain和使用optimizer_trace 分析sql的索引效率，后面会有文章单独分析；

2. order by 优化

Mysql排序支持两种filesort和index

1. 这种方式在使用explain分析时显示为using index，不需要额外的排序， 是指mysql扫描索引本身完成排序，操作效率较高；
2. 通过对返回数据进行排序，即filesort，所有不通过索引直接返回排序结果的排序都是filesort排序。

3. 单路、双路排序：

单路排序会将整行所有数据缓存到sort buffer中；

双路排序只将主键id和排序字段放入到sort buffer中排序，在根据排序好的数据，从原来表中根据id查询数据返回给客户端；

4. group by 优化

1. group by 没有排序要求，可以在最后加上 order by null 避免不必要的排序;
2. group by的字段尽量覆盖索引，避免文件排序带来的性能损；
3. 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
4. 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

5. 延迟关联

先查询索引列id

6. 关联查询

在使用Join 进行关联查询的时候最多关联3张表，并且在使用Left join/ right join注意驱动表顺序，小表驱动大表；

在关联查询会使用到下面关联查询算法：

Nested-Loop Join 嵌套循环连接 算法（主键关联查询）

Block Nested-Loop Join    基于块的嵌套循环连接 算法（非索引关联查询）

有索引的情况下 NLJ 算法比 BNL算法性能更高

7. in和exist优化

原则：小表驱动大表，即小的数据集驱动大的数据集

in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。

exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。

总结一下：
in 适用于左边大表，右边小表。
exists 适用于左边小表，右边大表。

8. count(*)查询优化

1.count(field)不包含字段值为NULL的记录。

2.count(*)包含NULL记录。

3.select count(*)与select count(1) 在InnoDB中性能没有任何区别，处理方式相同。

常见优化方案：

1. 对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算
2. show table status
3. 使用Redis set key记录表总数

9. 聚族索引和辅助索引

主键索引就是聚族索引，普通索引就是辅助索引；

只查询主键时，直接走主键索引获取数据；查询其他字段需要先走辅助索引，然后获取到聚族索引地址，然后在根据主键地址获取到其他字段值。

所以查询字段尽量全覆盖到索引避免回表查询；

10. 分表分库

在数据量大于2000W或者存储数据大于2G时通过shading jdbc组件进行分表、分库；

对于大数据量还可以采用冷热数据分离、newSql、搜索引擎等解决方案。