代码工艺：SQL 优化的细节

1. 巧用 limit

当出现深分页的时候，例如：

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条数据。

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 的取舍

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

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

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

6. 清表要用 truncate

（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. 过滤优先于一切

记住，无论是分组还是排序，或者多表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》书中提到 MySQL 通常会分配固定大小的空间，在磁盘利用内部临时表进行排序时的性能会非常的糟糕，因此最佳的策略是分配真正需要的大小空间。因此，当你把varchar的长度调整为最小可用，是可以帮助你优化SQL排序性能的。

13. 适当的索引策略

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

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

14. force index 强制化

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

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

15. 多表关联控制量

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