MySQL优化__基础优化注意选项

SQL语句基本优化

 MySQL优化：我们在写SQL的过程中要注意一些点，合理地SQL对于项目的稳定性以及未来的发展性以及可塑性都是至关重要的。该篇是一些最简单的优化点。

1. SQL语句中包含的值不应该过多

 MySQL对于IN做了相应的优化，即将IN中的常量全部都储存在一个数组里面，而且这个数组还是排好序的。但是如果数值太多，产生的性能消耗也是比较大的。

 建议：

1. 对于连续数值，能用 between 就不要用 in 。
2. 如果不方便使用 between 可以考虑使用连接来替换。

例如：
SELECT id FROM iot_product_discount p WHERE p.`id` IN (1, 2, 3, 4);

优化为：
SELECT id FROM iot_product_discount p WHERE p.`id` BETWEEN 1 AND 4;

 
2. select 语句指定查询字段

 SELECT * 的缺点有：

1. 增加了很多不必要的消耗（CPU、IO开销、内存、网络带宽等）。
2. 因为是查询所有的，所以可能会为了一些我们不需要的数据而产生索引失效或覆盖索引的可能性。
3. 当表结构发生变更时，往往可能将一些我们不希望展示的数据发送出去。
4. select * 的操作需要数据库先 Query Table Matedata For Columns，增加了数据库的负担，
   然而并没有提高效率。

 建议：即使是要全部数据，也麻烦将其字段全都标明。除了在写SQL的时候可能会更长一点，并不会带来其他坏处。

 
3.确定只有一条数据的时候，使用 limit 1

 原因：

1. 如果加上 limit 1 将会使 explain 中的 type 列类型到达 const 类型。
2. 第二点是对上一点的原理补充。
   因为加上了 limit 1 后，将会避免全表扫描，找到了相对应结果就不会再继续扫描了。

4.排序字段没有用到索引，就尽量少排序

 原因：

1. 巧妇难煮无米之炊。既然数据库对数据进行了排序，比之不排序，肯定是加大了性能消耗。
2. 如果需要排序，那么排序字段有没有索引对性能消耗差别比较大。有索引的字段，性能开销小。
3. 还有一点，注意SQL的书写，在临时表中进行排序会花费巨大的性能消耗（如果临时表还不小的话）。

 

5.限制条件中其他字段没有索引，尽量少用 or

 原因： OR 两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走 索引的效果。
 建议：可以考虑使用 union all 或是 union的方式来代替 or 会得到更好的效果。
 

6.分清 union all 和 union 的使用

  union all 和union都是将两个结果集合进行合并操作，而它们的区别是union all不会作结果唯一性过滤处理。

 原因： union 操作会在两个结果集合合并后进行排序，那么就不可避免得会增加CPU运算，加大资源消耗和延迟。

 建议：在能够确定两个结果集合没有重复数据的情况下，使用union all 来代替union。

 
7.避免在where子句中对字段进行表达式操作

 原因： 在where子句中对字段进行算术运算将会导致引擎放弃使用索引。

例如：

SELECT id,discount FROM iot_product_discount p WHERE p.`discount` * 2 = 66;

可以优化为：
SELECT id,discount FROM iot_product_discount p WHERE p.`discount` = 66/2;

 
8.联合索引要遵守左前缀法则

 举例来说，如果索引中包含有字段id、name、school，可以直接用id字段，也可以用id、name这样的顺序，但是name、school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段的顺序，把常用的查询字段放在前面。

9.避免在where子句中对字段进行null值判断

 原因： 在where子句中对字段进行 null 判断会使引擎放弃使用索引而进行全表扫描。

10.分段查询

 在一些用户选择页面中，可能有一些用户的选择的时间范围过大，造成查询缓慢的结果。主要的原因就是扫描行数过多。这个时候可以通过程序，进行分段查询，循环遍历，将结果合并处理。

建议：在扫描的行数接近百万的时候，就应该使用程序分段查询。

11. 注意 in 和 exists的区别

  我们先开看着用 IN 和 EXISTS 的SQL区别。

IN：
	SELECT * FROM iot_product_discount p WHERE  p.`flow_product_id` 
		IN(SELECT id FROM iot_flow_product);

EXISTS：
	SELECT * FROM iot_product_discount p WHERE 
		EXISTS(SELECT * FROM iot_flow_product f WHERE f.`id` = p.`flow_product_id`);

 区分 IN 和 EXISTS 两个关键字的原理。
两者的驱动顺序不一致（这是性能变化的关键）。
 如果是 in，那么子查询为驱动表，先执行查询，所以IN适合 外表大而内表小的情况。
 如果是exists，那么以外层为驱动表，先被访问。所以EXISTS适合外表小而内表大的情况。

12.小心 not in 和 not exists

 关于not in 和 not exists， 这边推荐使用not exists，不仅仅是因为效率问题，还有是因为 not in 可能存在逻辑问题。

NOT IN：
	SELECT * FROM iot_product_discount p WHERE  
		p.`flow_product_id` NOT IN(SELECT id FROM iot_flow_product);

优化：
	SELECT * FROM iot_product_discount p LEFT JOIN iot_flow_product f 
		ON p.`flow_product_id` = f.`id` AND f.`id` IS NULL;
该SQL最后取得的范围是下图表A中黄色的部分（不包含绿色的）。