6.复习-SQL优化

SQL优化

1、SQL语句方面

1、避免使用select *

select 不走覆盖索引，会有大量的回表操作，从而导致查询SQL的性能很低。应该使用具体的字段代替，只返回使用到的字段。

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

2、用union all代替union

使用union关键字后，可以获取排重后的数据。
而如果使用union all关键字，可以获取所有数据，包含重复的数据。
排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源。

3、小表驱动大表

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

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

in 适用于左边大表，右边小表。

exists 适用于左边小表，右边大表。

4、多用limit

尽量避免取出自己不需要的数据，同时还能让误操作的影响降低

5、in中值太多

查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。如果超过一定数量可以通过limit来限制数量

6、增量查询

​ 直接获取所有的数据，然后同步过去，如果数据很多的话，查询性能会非常差。按id和时间升序，每次只同步一批数据，这一批数据只有100条记录。每次同步完成之后，保存这100条数据中最大的id和时间，给同步下一批数据的时候用。

1
select * from user where id > #{lastId} and create_time >= #{lastCreateTime} limit 100;

7、高效的分页

​ 列表页在查询数据的时候，为了避免一次性返回过多的数据影响接口性能，我们一般会对查询接口做分页处理，在数据库中分页一般用的limit关键字

1
select id,name,age from user limit 10,20;

如果表中的数据量较少，用limit关键字做分页，没什么问题，但如果表中数据量很多，用他就会出现性能问题，

1
select id,name,age from user limit 1000000,20;

优化

1
select id,name,age from user where id >1000000 limit 20;

利用id上的索引查询，要求id是连续的，并且是有序的，还可以使用between优化分页

1
select id,name,age from user where id between 1000000 and  1000020;

between要在唯一索引上分页，不然会出现每页大小不一致的问题

8、用连接查询代替子查询

子查询语句的优点是简单，结构化，如果涉及的表数量不多的话。
但缺点是mysql执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗。
这时可以改成连接查询。

1
select * from order where user_id in (select id from user where status=1);

连接查询，性能会更高

1
2
3
select o.* from order o
inner join user u on o.user_id=u.id
where u.status=1;

9、join表数量不应该超过3个。

​ 如果join太多，mysql在选择索引的时候会非常复杂，很容易选错索引。
并且如果没有命中，就是分别从两个表读一行数据进行两两对比，复杂度是 n^2。
所以我们应该尽量控制join表的数量。
​ join时要注意，涉及到多张表联合查询的时候，一般会使用join关键字。而join使用最多的是left join和inner join。
​ left join：求两个表的交集外加左表剩下的数据。
​ inner join：求两个表交集的数据。
​ 使用inner join关联，mysql会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。

10、控制索引的数量

​ 表中新增数据时，需要同时为它创建索引，而索引是需要额外的存储空间的，而且还会有一定的性能消耗。能够建联合索引，就别建单个索引，可以删除无用的单个索引。

​ 索引问题 法则：不要在建立的索引的数据列上进行下列操作:避免对索引字段进行计算操作。避免在索引字段上使用not，<>，!=。避免在索引列上使用IS NULL和IS NOT NULL。避免在索引列上出现数据类型转换。避免在索引字段上使用函数。避免建立索引的列中使用空值。

11、提升group by的效率

​ 使用where条件在分组前，就把多余的数据过滤掉了，这样分组时效率就会更高一些。
其实这是一种思路，不仅限于group by的优化。我们的sql语句在做一些耗时的操作之前，应尽可能缩小数据范围，这样能提升sql整体的性能。

1
select user_id,user_name from order where user_id <=200 group by user_id;

12、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

  • 不要在where条件中使用左右两边都是%的like模糊查询，这样会导致数据库引擎放弃索引进行全表扫描。优化：尽量在字段后面使用模糊查询

  • 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，优化：可以用in代替or。

  • 尽量不要在 where 子句中对字段进行表达式操作，这样也会造成全表扫描。

  • where条件里尽量不要进行null值的判断，null的判断也会造成全表扫描。给字段添加默认值，对默认值进行判断。

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

  • 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。将表达式.函数操作移动到等号右侧。

  • 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

索引优化

很多时候sql语句，走了索引，和没有走索引，执行效率差别很大。所以索引优化被作为sql优化的首选。
索引优化的第一步是：检查sql语句有没有走索引。
那么，如何查看sql走了索引没？
可以使用explain命令，查看mysql的执行计划。

执行计划包含列的含义

​ id (select唯一标识）
​ select_type（select类型）
​ table（表名称）
​ partitions（匹配的分区）
​ type（连接类型）
​ possible_ keys（可能的索引选择）
​ key（实际用到的索引）
​ key_len（实际索引长度)
​ ref（与索引比较的列）
​ filtered（按表条件过滤的行百分比）
​ extra（附加信息）

  • 可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，
  • 第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，
  • 第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

索引失效的常见原因

  1. 不满足最左前缀原则
  2. 范围索引没有放最后
  3. 索引列上有计算
  4. 索引列上使用了函数
  5. 字符类型没加引号
  6. 模糊（like）查询左边有%
  7. 使用了select *

建表

选择合理的字段类型

  • 能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
  • 尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等。
  • 长度固定的字符串字段，用char类型。
  • 长度可变的字符串字段，用varchar类型。
  • 金额字段用decimal，避免精度丢失问题。