sql优化的技巧

1 避免使用 select *  

很多时候，写 sql 语句时，为了方便，喜欢直接使用select *，一次性查出表中所有列的数据。

在实际业务场景中，可能我们真正需要使用的只有其中一两列。查了很多列，但是不用，浪费了数据库资源，比如：内存或者 cpu。

此外，多查出来的数据，通过网络 IO 传输的过程中，也会增加数据传输的时间。

还有一个最重要的问题是：select *不会走覆盖索引，会出现大量的回表操作，而从导致查询 sql 的性能很低。

优化：sql 语句查询时，只查需要用到的列，多余的列根本无需查出来

2 用 union all 代替 union

 sql 语句使用union关键字后，可以获取排重后的数据，而如果使用union all关键字，可以获取所有数据，包含重复的数据

排重的过程需要遍历、排序和比较，它更耗时，更消耗 cpu 资源

优化 ：如果能用 union all 的时候，尽量不用 union

3 小表驱动大表

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

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

优化：in 适用于左边大表，右边小表， exists 适用于左边小表，右边大表

4 批量操作

如果进行循环逐条插入数据会进行多次数据库请求消耗性能

优化：提供一个批量插入数据的方法，这样只需要远程请求一次数据库，sql 性能会得到提升，数据量越多，提升越大

但需要注意的是，不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在 500 以内。如果数据多于 500，则分多批次处理。

5 多用 limit

在删除或者修改数据时，为了防止误操作，导致删除或修改了不相干的数据，也可以在 sql 语句最后加上 limit

6 in 中值太多

in中的值太多时，可以用limit进行限制 然后 分批去查询

7 增量查询

增量查询的方式，能够提升单次查询的效率

8 高效的分页

一般数据时，我们用limit进行分页限制，当数据量很大时，我们可以找到最后一次查询的id 然后 进行limit限制，或者使用between进行分页不过要求id是连续的

9 用连接查询代替子查询

mysql 中如果需要从两张以上的表中查询出数据的话，一般有两种实现方式：子查询 和 连接查询。

子查询语句可以通过in关键字实现，一个查询语句的条件落在另一个 select 语句的查询结果中。程序先运行嵌套在最内层的语句，再运行外层的语句。

子查询语句的优点是简单，结构化，如果涉及的表数量不多的话。

但缺点是 mysql 执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗，这时可以改成连接查询

10  join 的表不宜过多

join 表的数量不应该超过3个

如果 join 太多，mysql 在选择索引的时候会非常复杂，很容易选错索引。

并且如果没有命中，nested loop join 就是分别从两个表读一行数据进行两两对比，复杂度是 n^2。

所以我们应该尽量控制 join 表的数量

11 join 时要注意

在涉及到多张表联合查询的时候，一般会使用join关键字

left join：求两个表的交集外加左表剩下的数据

inner join：求两个表交集的数据

如果两张表使用 inner join 关联，mysql 会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。

如果两张表使用 left join 关联，mysql 会默认用 left join 关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题。

要特别注意的是在用 left join 关联查询时，左边要用小表，右边可以用大表。如果能用 inner join 的地方，尽量少用 left join。

12 控制索引的数量

索引能够显著地提升查询 sql 的性能，但索引数量并非越多越好。

因为表中新增数据时，需要同时为它创建索引，而索引是需要额外的存储空间的，而且还会有一定的性能消耗

单表的索引数量应该尽量控制在5个以内，并且单个索引中的字段数不超过5个

mysql 使用 B+ 树的结构来保存索引，在 insert、update 和 delete 操作时，需要更新 B+ 树索引。如果索引过多，会消耗很多额外的性能

高并发系统如何优化索引数量: 能够建联合索引，就别建单个索引，可以删除无用的单个索引,将部分查询功能迁移到其他类型的数据库中，比如：Elastic Seach、HBase 等，在业务表中只需要建几个关键索引即可

13 选择合理的字段类型

char表示固定字符串类型，该类型的字段存储空间是固定的，会浪费存储空间。

varchar表示变长字符串类型，该类型的字段存储空间会根据实际数据的长度调整，不会浪费存储空间。

我们在选择字段类型时，应该遵循这样的原则：

1. 能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。

2. 尽可能使用小的类型，比如：用 bit 存布尔值，用 tinyint 存枚举值等。

3. 长度固定的字符串字段，用 char 类型。

4. 长度可变的字符串字段，用 varchar 类型。

5. 金额字段用 decimal，避免精度丢失问题。

14 提升 group by 的效率

group by关键字，它主要的功能是去重和分组 通常它会跟having一起配合使用，表示分组后再根据一定的条件过滤数据。

15 索引优化

索引优化的第一步是：检查 sql 语句有没有走索引,可以使用explain命令，查看 mysql 的执行计划。

可以通过 type,key,key_len来判断索引的使用情况

 

索引失效的原因

1不满足最左前缀原则

2范围索引列没有放最后

3使用了select * 

4索引列上有计算

5索引列上使用了函数

6字符类型没加引号

7用is_null和is not null 没注意字段是否允许为空

8like查询左右有 %

9使用or关键字