面试官:如何进行SQL优化?

138 阅读9分钟

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等技术方案了。

结语

整体就这么多,后续如果有新的,我再补充。