分享好友 站长动态首页 网站导航

阿里一面：SQL 优化有哪些技巧？

2022-05-11 16:00 · 头闻号数据库

今天，Tom哥就带大家复习一个高频面试考点，SQL 优化有哪些技巧？

当然这个还是非常有实用价值的，工作中你也一定用的上。如果应用得当，升职加薪，指日可待

1、创建索引

一定要记得创建索引，创建索引，创建索引

重要的事说三遍！

执行没有索引的 SQL 语句，肯定要走全表扫描，慢是肯定的。

这种查询毫无疑问是一个慢 SQL 查询。

那么问题来了，是不是要收集所有的 where 查询条件，然后针对所有的组合都创建索引呢？

答案肯定是否定的。

MySQL 为了提升数据查询速率，采用 B+ 树结构，通过 空间换时间 设计思想。另外每次对表数据做更新操作时，都要调整对应的 索引树 ，执行效率肯定会受影响。

本着 二八原则 ，互联网请求 读多写少 的特点，我们一定要找到一个平衡点。

阿里巴巴的开发者手册建议， 单表索引数量控制在5个以内，组合索引字段数不允许超过5个

其他建议：

2、避免索引失效

不要以为有了索引，就万事大吉。

殊不知， 索引失效 也是慢查询的主要原因之一。

常见的索引失效的场景有哪些？

3、锁粒度

MySQL 的存储引擎分为两大类：MyISAM  和 InnoDB 。

MyISAM 支持表锁；InnoDB 支持行锁和表锁

更新操作时，为了保证表数据的准确性，通常会加锁，为了提高系统的高并发能力，我们通常建议采用 行锁 ，减少 锁冲突 、 锁等待 的时间。所以，存储引擎通常会选择 InnoDB

行锁可能会升级为表锁，有哪些场景呢？

注意：行锁将锁的粒度缩小了，进而提高了系统的并发能力。但是也有个弊端，可能会产生死锁，需要特别关注。

4、分页查询优化

如果要开发一个列表展示页面并支持翻页时，我们通常会这样写 SQL

select * from 表  limit #{start}, #{pageSize};

随着翻页的深度加大， start 值越来越大，比如：limit 10000 ，10

看似只返回了 10 条数据，但数据库引擎需要查询 10010 条记录，然后将前面的 10000 条丢弃，最终只返回最后的 10 条记录，性能可想而知

针对这个问题，我们通常有另一种解决方案：

先定位到上一次分页的最大 id，然后对 id 做条件索引查询。由于数据库的索引采用 B+ 树结构，这样可以一步到位

select * from 表 where id > #{id}  limit  #{pageSize};

任何事情，有利就有弊

这种翻页方式只支持 上一页 、 下一页 ，不支持跨越式直梯翻页

上图是淘宝的商品搜索列表，为了用户体验更好，采用的也是 直梯式 翻页。

为了避免翻页过深，影响性能，产品交互上做了一些取舍，对总页数做了限制，最多支持 100 页。

方案二：采用子查询

select * from 表 where id > ( select id from 表 order by id limit 10000 1) limit 20;

将原来的单 SQL 查询拆成两步：

阿里的规约手册也有对应描述：

5、避免 select  *

反面案例：

select  * from 表 where buyer_id = #{buyer_id}

我们知道，MySQL 创建表后，具体的行数据存储在主键索引（属于聚簇索引）的叶子节点。

二级索引属于非聚簇索引，其叶子节点存储的是 主键值

select * 的查询过程：

所以，我们需要罗列清楚必须的查询字段，且字段尽量在 覆盖索引 中，从而减少 回表 操作。

6、EXPLAIN 分析 SQL 执行计划

授人以鱼不如授人以渔

除了知晓常见的不规范 SQL 写法，在开发过程中，避免踩坑

我们还应知道，出现了慢 SQL 该如何排查、优化

实验安排起来

创建表

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `income` bigint(20) NOT NULL COMMENT '收入',
  `expend` bigint(20) NOT NULL COMMENT '支出',
  PRIMARY KEY (`id`),
  KEY `idx_income` (`income`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';

CREATE TABLE `biz_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `user_id` bigint(20) NOT NULL COMMENT '用户id',
  `money` bigint(20) NOT NULL COMMENT '金额',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='订单表';

插入记录：

insert into user values(10,100,100);
insert into user values(20,200,200);
insert into user values(30,300,300);
insert into user values(40,400,400);

insert into biz_order values(1,10,30);
insert into biz_order values(2,10,40);
insert into biz_order values(3,10,50);
insert into biz_order values(4,20,10);

比如下面的语句，我们看是否使用了索引，可以通过 explain 分析相应的执行计划

explain select * from user where  id<20;

接下来，我们来逐一来说明每个字段的含义

system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据
eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件
ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描
range：索引范围扫描，比如，<，>，between 等操作
index：索引全表扫描，此时遍历整个索引树
ALL：表示全表扫描，需要遍历全表来找到对应的行
explain select * from user u , biz_order b where u.id=b.user_id and u.id<20;

7、Show Profile 分析 SQL 执行性能

Show Profile 与 EXPLAIN 的区别是，前者主要是在外围分析；后者则是深入到 MySQL 内核，从执行线程的状态和时间来分析。

MySQL 是在 5.0.37 版本之后才支持 Show Profile ， select @@have_profiling 返回 YES 表示功能已开启。

mysql> show profiles;
Empty set, 1 warning (0.00 sec)

显示为空，说明profiles功能是关闭的。

通过下面命令开启

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

获取 Query_ID 之后，通过 show profile for query ID ，查看 SQL 语句在执行过程中线程的每个状态所消耗的时间

​ ​

免责声明：本平台仅供信息发布交流之途，请谨慎判断信息真伪。如遇虚假诈骗信息，请立即举报

举报
反对 0
打赏 0
更多相关文章

评论

0

收藏

点赞