京东二面：日常工作中，你是如何优化SQL的？-51CTO.COM

大家好，我是田螺。

我们去面试的时候，经常被问到，日常工作中，是如何优化SQL的。今天跟大家再聊聊哈。

这里应该如何去回答呢？可以从各种不同维度的，今天我再换个角度。

加索引
避免常见的索引不生效场景
避免返回不必要的数据
减少不必要的逻辑
分批量进行思想
读写分离
优化sql结构
分库分表
性能优化分析神器—explain
慢SQL排查思路

1. 加索引

很多时候，我们的慢查询，都是因为历史原因没有加索引，或者忘记加索引导致的。如果没有加索引的话，会导致全表扫描的。因此，应考虑在where的条件列，建立索引，尽量避免全表扫描。

反例：

select * from user_info where name ='捡田螺的小男孩公众号' ;

正例：

//添加索引
alter table user_info add index idx_name (name);

2. 避免常见的索引不生效场景

我之前整理了常见的十种索引不生效的场景，大家可以看看：

隐式的类型转换，索引失效
查询条件包含or，可能导致索引失效
like通配符可能导致索引失效
查询条件不满足联合索引的最左匹配原则
在索引列上使用mysql的内置函数
对索引进行列运算(如，+、-、*、/)
索引字段上使用(!=或者<>)，索引可能失效
索引字段上使用is null，is not null，索引可能失效
左右连接，关联的字段编码格式不一样
优化器选错了索引

3. 避免返回不必要的数据

这个点，我在昨天的文章，其实就提到一个点，包括尽量使用limit，避免不必要的返回。

其实这不仅仅是一个点，而是一种思想，就是要什么查什么，而不是返回一些不必要的数据。还有：查询SQL尽量不要使用select *，而是select具体字段。也是这种思想。

反例子：

select * from employee;

正例子：

select id，name, age from employee;

select具体字段，节省资源、减少网络开销。
select * 进行查询时，很可能就不会使用到覆盖索引了，就会造成回表查询。

4. 减少不必要的逻辑

其实，尽量用 union all 替换 union，就是这种思想。

如果我们明知道，检索结果中不会有重复的记录，推荐union all 替换 union。

因为：

如果使用union，不管检索结果有没有重复，都会尝试进行合并，然后在输出最终结果前进行排序。如果已知检索结果没有重复记录，使用union all 代替union，这样会提高效率。

5. 分批量进行思想

我们更推荐批量查询、插入、删除。

反例：

for(User u :list){
 INSERT into user(name,age) values(#name#,#age#)   
}

正例：

//一次500批量插入，分批进行
insert into user(name,age) values
<foreach collectinotallow="list" item="item" index="index" separator=",">
    (#{item.name},#{item.age})
</foreach>

理由：

批量插入性能好，更加省时间
打个比喻: 假如你需要搬一万块砖到楼顶,你有一个电梯,电梯一次可以放适量的砖（最多放500）,你可以选择一次运送一块砖,也可以一次运送500,你觉得哪个时间消耗大?

6. 读写分离

一般情况我们的数据库架构，都要做主从的，然后进行读写分离。主库主要负责写，和一些实时性比较高的读。而从库就负责读实时性要求不高的请求。

图片

这样的话，我们不用所有请求都到主库，大大降低了主库的压力。你试想一下，如果所有读请求都到主库，查询压力肯定很大，处理也会相对慢一点。

7. 优化sql结构、逻辑

有些时候，优化SQL结构，都能有一些预想不到的优化效果。

假设我们有个客户表和一个订单表。其中订单表有10万记录，客户表只有1000行记录。

现在要查询下单过的客户信息，可以这样写：

SELECT * FROM customers
WHERE id IN (
    SELECT customer_id FROM orders
);

in 查询会先执行内部查询部分 SELECT customer_id FROM orders，获得 orders 表（大表）中的所有 customer_id，然后在 customers 表（小表）中查找匹配的 id。

也可以这样实现：

SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

EXISTS 会逐行扫描 customers 表（即小表），对每一行 c.id，在 orders 表（大表）中检查是否有 customer_id = c.id 的记录。

因为orders表的数据量比较大，因此这里用exists效果会相对更好一点。其实这就是小表驱动大表的思想。我们也只是调整SQL结构，用exists去替换in，优化效果也是比较明显的。

8. 分库分表

如果单表的数据量很大，达到百万甚至千万级别，我们这种时候，就是加了索引，可能效果也不是很明显。这时候我们可以考虑分库分表啦~~

分库分表一般都是依赖客户号、用户Id、或者时间来拆分。但是需要注意一下，分库分表存在的一些一些问题：

事务问题
跨库关联JOIN
排序问题
分页问题
分布式ID选择

9. 性能优化分析神器—explain

之前我写SQL习惯的时候，有提到explain，就是每次写完查询SQL，都用explain看一下它的执行计划。

有些面试官会单独问这个，我们可以走面试官的路，让面试官无路可走。在回答SQL优化的时候，就把这个回答了。

一般在使用explain的时候，我们要关注:type、rows、filtered、extra、key。

9.1 type

type表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system：这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。
const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快。
eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询
ref : 常用于非主键和唯一索引扫描。
ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行
index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
unique_subquery：类似于eq_ref，条件用了in子查询
index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
range：常用于范围查询，比如：between ... and 或 In 等操作
index：全索引扫描
ALL：全表扫描

9.2 rows

该列表示MySQL估算要找到我们所需的记录，需要读取的行数。对于InnoDB表，此数字是估计值，并非一定是个准确值。

9.3 filtered

该列是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

9.4 extra

该字段包含有关MySQL如何解析查询的其他信息，它一般会出现这几个值：

Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句
Using index ：表示是否用了覆盖索引。
Using temporary: 表示是否使用了临时表,性能特别差，需要重点优化。一般多见于group by语句，或者union语句。
Using where : 表示使用了where条件过滤.
Using index condition：MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。

9.5 key

该列表示实际用到的索引。一般配合possible_keys列一起看。

10. 慢SQL排查思路

如果大家平时有优化过生产的慢SQL，有自己的一套排查那一套最好哈。如果没有的话，可以在自己搞个深分页，或者因为数据量、或者因为没加索引等原因，导致的慢SQL，然后按照这个思路去排查一遍。

查看慢查询日志记录，分析慢SQL
explain分析SQL的执行计划
profile 分析执行耗时
Optimizer Trace分析详情
确定问题并采用相应的措施

如果不熟悉的话，可以多操作几遍，尽量熟悉操作流程，在面试的时候，讲一下这个主要流程。