【转载】SQL优化的N种技巧

SQL优化，算是数据库优化的一个子集。

因此，吹大牛的候选人简历上，会赫然写着”擅长MySQL数据库优化“，而吹小牛的候选人简历上，往往会写”擅长SQL优化“。

但结局是殊途同归的，就是当问他们用什么方式做的优化，他们都会说上三个字：”加索引“。

当然，好一点儿的会说可以加联合索引，它有最左前缀匹配原则（8.0以后的版本就不完全对了）之类的，还能说说覆盖索引。

那么，我的这篇文章，就好好聊聊这个面试话题。

先教大家一个小窍门，最好大家在回答面试官这个问题的时候，最好可以做到跟自己简历中项目的进行真实场景带入，这样会给面试官一种可以理论结合实践的感觉，是个大大的加分项。

举个例子，话术为：

“当时我负责电商平台的商品中心优化，我发现在展示商品列表的时候，一旦深分页就会出现加载缓慢的问题，然后我就看了一下对应的SQL语句，是这样写的：

select id, name, status, detail from product limit 10, 30;

那么一旦在深分页的话，SQL语句就会变成这样：

select id, name, status, detail from product limit 100000, 30;

那么MySQL的执行方式为：一共需要查100030条数据，然后丢弃前面的100000条，只返回后面的30条数据，这样做是非常浪费资源的。

于是我把SQL改为：

select id, name, status, detail from product where id > 100000 limit 30;

100000为上次分页中最大的商品ID，先找到它，然后再根据主键ID扫描后续30条数据。

这样做性能很高，把SQL语句从原先的耗时4300ms，降低到了18ms。”

好了，下面我正式给大家列举一下，SQL优化的N种技巧，select * 这种的就不写了哈。

1、巧用limit

上文已经讲解了，仔细看下即可。

2、like百分号向右

反例：

select * from employee where address like '%通州区%';
select * from employee where address like '%通州区';

正解：

select * from employee where address like '北京市通州区%';

原因：

（1）全模糊查询，或者左边出现%的模糊查询，会导致索引实效，应该尽量从查询方式或表结构设计上避免。

（2）若无法避免，且数据量庞大的情况下，一定要使用ElasticSearch进行替代。

3、union 加个 all

反例：

select product_id from orders where id = 100
union
select product_id from orders where id = 200;

正解：

select product_id from orders where id = 100
union all
select product_id from orders where id = 200;

原因：

union：对两个结果集进行并集操作，不包括重复行，相当于distinct，同时进行默认规则的排序；

union all：对两个结果集进行并集操作，包括重复行，不进行排序；

union因为要进行重复值扫描，所以在结果集庞大的情况下，效率极低，因此建议使用union all。

若结果集去重是强需求，则在应用程序代码上进行去重，因为数据库资源要比应用服务器资源更加珍贵。

4、善用 staright_join

straight_join功能同inner join类似，但能让左边的表来驱动右边的表，通过改变优化器对于联表查询的执行顺序的方式，获取更好的性能。

btw：若驱动表（左边）的数据量小于（被驱动表），它的执行性能要高于，驱动表（左边）的数据量大于（被驱动表）。

举个例子：

select * from t2 straight_join t1 on t2.a = t1.a;

比如上面这个，如果我们事先知道t2表的数据量一定小于t1表的话，就可以使用上面的方式指定t2表为驱动表。

需要注意的点：

（1）straight_join只适用于inner join，并不适用于left join，right join。

（2）大部分情况下，MySQL优化器是可以做出正解的。因此，使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

5、exists 和 in 的取舍

select * from student where school_id in (select id from school);
select * from police p where exists (select 1 from user u where u.id = p.id);

如果子查询得出的结果集数据较少，主查询中的表较大且又有索引时，应该用in；反之，如果外层的主查询数据较少，子查询中的表大，又有索引时使用exists。

• 如果是exists，那么以外层表为驱动表，先被访问。
• 如果是in，那么先执行子查询。

in 是把外表和内表作 hash 连接，而 exists 是对外表作 loop 循环，每次 loop 循环再对内表进行查询。所以，我们会以驱动表的快速返回为目标，目标是以小表驱动大表，这是性能优化的本质。

之前有一种比较扯淡的说法，“exists 比 in 效率高”，大家试想一下，如何一个事物在任何场景下，都优于另外一个事物，那另外一个事物就没有存在的必要性了。

6、清表要用truncate

反例：

delete from user;

正解：

truncate user;

原因：

（1）truncate是直接把表删除，然后再重建表结构，性能很高，但删除操作记录不记入日志，不能回滚。

delete语句执行删除的过程是每次从表中删除一行，性能较低，但该行的删除操作会作为事务记录在日志中保存，以便进行进行回滚操作。

（2）truncate后，表和索引所占用的空间会恢复到初始大小，而delete只是将被删除的记录标记为已删除，不会立即减少表或索引所占用的空间。

7、尽量批量操作

反例：

insert into student(name, sex, age) values('Tom', 1, 20);
insert into student(name, sex, age) values('Tony', 1, 18);

正解：

insert into student(name, sex, age) values('Tom', 1, 20), ('Tony', 1, 18);

原因：

SQL批量操作，即一次数据库操作中插入多个数据行，相比于单条插入，可减少大量的IO交互和SQL解析开销，从而提高了插入效率。

8、过滤优先于一切

反例

select city, avg（area） from country  group by city having city ='beijing' or city = 'shanghai';

正解：

select city, avg（area） from country  where city ='beijing' or city = 'shanghai'  group by city;

原因：

记住，无论是分组还是排序，或者多表join，如果可以的话，第一件事就是把用不到的记录先过滤掉。

9、函数在等号右侧

反例：

select * from article where left(title, 4) = '环球资讯';

正解：

select * from article where title = left('环球资讯', 4);

原因：

如果在索引列上使用函数，会导致索引实效。

10、数据类型最小可用

一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常会使SQL执行更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。

但是，要确保没有低估需要存储的值的范围，因为在表schema中修改数据类型是一件非常耗时和痛苦的操作（特指表数据量很大的场景）。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。

举个例子：如果确定只需要存0—200，tinyint unsigned类型是最适合的。

11、char 和 varchar的抉择

char：定长，存取效率高，一般用于固定长度的表单提交数据存储，例如：身份证号，手机号，电话，密码等，长度不够的时候，会采取右补空格的方式。

varchar：不定长，更节省空间，需要用一个或者两个字节来存储数据的长度。具体规则是：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。

varchar由于行是变长的，在UPDATE时可能使行变得比原来更长，会导致分裂页和产生碎片。

12、varchar长度最小可用

有人认为，既然varchar是变长的，那我就尽量给它设置得大一些，以备不时之需，反正没有坏处。

其实，varchar(5) 和 varchar(200)是不一样的！

我们看下《高性能MySQL》一书中的原话：

因此，当你把varchar的长度调整为最小可用，是可以帮助你优化SQL排序性能的。

13、适当的索引策略

• 频繁作为查询条件的字段应该创建索引，频繁更新的字段不适合创建索引；
• 多表关联查询中的关联字段，查询中统计或者分组字段，查询中排序字段，应该创建索引；
• 尽量使用数据量少或区分度高的字段创建索引；
• 多条件组合查询优先创建组合索引，熟悉组合索引的最左前缀原则，不要创建冗余索引；
• 禁止使用全文索引，可以用前缀索引进行替代；
• 善于利用覆盖索引来优化查询；
• delete和update语句的where条件必须由索引，否则会导致锁表；

适当的索引策略，经过业务取舍后，可以使SQL执行得更快。

14、force index强制化

MySQL查询优化器在执行SQL语句时，会选择它认为最合适的索引，但有时却并不准确，不是实际上最快的索引，此时可以用force index人为指定索引。

force index 跟着表名后面，用于强制使用指定的索引名(key)。

如下列所示：

select * from msg force index(idx_dest_src) where dest = '18736809673' and src in ('15144804019', '18674654894');

15、多表关联控制量

据说，阿里巴巴开发者手册规定，join表的数量不应该超过3个，这个我还真没看到。

我个人觉得，多表关联需要控制量，但没必要完全一杆子拍死。

如果某个系统中，有很多多表关联的大SQL，那确实意味着表结构设计有问题，或者需要引入ES等技术方案了。