如何优化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 collection="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分析详情
-
确定问题并采用相应的措施