慢SQL-处理思路

通过explain 分析原因

type至少为range、ref、const，如果为all和index说明执行了全表扫描
extra中如果出现using filesort 和 using temporary 则可能因为排序和或者使用临时表导致SQL性能差，使用using index（覆盖索引） 或者 using index conditon（索引下推）来提高性能。

确定查询执行效率的是 “扫描行数”与“回表次数”。

优化索引

索引失效

最左匹配

索引：

KEY `idx_shopid_orderno` (`shop_id`,`order_no`)

SQL 语句：

select * from _t where orderno=''

查询匹配从左往右匹配，要使用 order_no 走索引，必须查询条件携带 shop_id 或者索引（shop_id，order_no）调换前后顺序。

比如 like '%xxx ’ 左like 不能使用索引

隐私转换

索引：

KEY `idx_mobile` (`mobile`)

SQL 语句：

select * from _user where mobile=12345678901

隐式转换相当于在索引上做运算，会让索引失效。
同时函数操作或者表达式都会让索引失效。

or条件

避免使用or！改成union all 详情查看

in条件

如下SQL所示，即使有business_id 和 sender 的联合索引，也不会使用

避免使用in！ 改成foreach + union， 详情查看

数据量太大

如果要求访问的数据量很小，则优化器会选择索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般大于 20% ），优化器会选择全表扫描。

假设我们有一个订单表 order_detail，其中有主键 id、主订单 order_id、商品 sku 等字段，其中该表有主键索引、主订单 id 索引。现在有一个查询订单详情的 SQL 如下，查询订单号范围在 5000~10000，请问该查询选择的索引是什么？有什么方式可以强制使用我们期望的索引呢？

select * from order_detail where order_id between 5000 and 10000；

小部分区间是会走order_id索引的，但是查询范围继续增大 ，反而不走索引而是全表扫描，小于全表5分之一区间能够走索引，超过5分之一会全表扫描，可以使用force index（索引名）强制使用该索引，这就是有些sql表开始跑的挺快的，后面越来越慢的原因吧。

因为order_id索引不能覆盖要查询的信息，所以在对order_id查询之后还需要一次回表来查找到整行数据，虽然order_id索引是顺序存放的，但是相对于主键id存放的数据顺序是不一致的，所以存在每次回表都是随机获取整行数据，如果在获取大量数据时，通过这种方式获取数据性能肯定是不理想的。所以mysql一般判断在查询超过整个表20%的数据时，就会考虑使用聚族索引来查找数据，这种方式顺序读取数据的可能性要大于使用辅助索引的随机读。在查询少量数据的情况下，使用辅助索引性能更加，而查询大量数据时，就未必了。如果我们发现在查询一定量数据使用辅助索引要比主键索引快，而数据库又没有按照我们期望的去使用辅助索引，则我们可以通过子查询或force index来强制使用辅助索引。

SELECT *
FROM order_detail
WHERE id IN (SELECT id FROM order_detail WHERE order_id BETWEEN 5000 AND 10000);

不等于、不包含

select * from _order where shop_id=1 and order_status not in (1,2)
select * from _order where shop_id=1 and order_status != 1

在索引上，避免使用 NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等。

索引有序性失效

1、order by 前缀索引
给某列定义一个前缀索引，即只保留前几位，那么这个索引并不能用于该列的排序！

2、范围查询导致索引有序性失效
如果联合索引（a, b）,那么where a in (‘1’,‘2’) order by b 则需要排序

详情查看

大分页（大OFFSET）

索引：

KEY `idx_a_b_c` (`a`, `b`, `c`)

SQL 语句：

select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;

解决办法：

1、去掉OFFSET，比如只提供上一页、下一页和最后一页，因为没有用户需要关注一个又大又随机的一页

这样的好处是可以把当前页的最大id传给服务端，例如当前页的最大值是100000，那么下一页的查询SQL可以是：

select * from _t where a = 1 and b = 2 and c > 100000 order by c limit 10;

这样会利用索引直接定位好 c >= 100000 的叶子节点，然后向后查询10行记录，避免了OFFSET

例如：

2、避免大OFFSET，限制查询范围
比如只允许查询最近3天的数据，这样符合条件的页数会比较小。

3、延迟关联，避免大量回表
比如下面SQL通过子查询优化，避免了大量没有意义的回表，因为如果是直接分页查询，索引每匹配一行都会回表查询整行数据，但是这些数据又回被丢弃！

select * from _t as t1, ( select id from _t where a = 1 and b = 2 order by c desc limit 10000, 10 ) as t2 where t1.id = t2.id

阿里巴巴开发规约也有这条建议：

using filesort

假设有一张订单表 order，主要包含了主键订单编码 order_no、订单状态 status、提交时间 create_time 等列，并且创建了 status 列索引和 create_time 列索引。此时通过创建时间降序获取状态为 1 的订单编码，以下是具体实现代码：

select order_no from order where status =1 order by create_time desc

你知道其中的问题所在吗？我们又该如何优化？

status和create_time单独建索引，在查询时只会遍历status索引对数据进行过滤，不会用到create_time列索引，将符合条件的数据返回到server层，在server对数据通过快排算法进行排序，Extra列会出现file sort；应该利用索引的有序性，在status和create_time列建立联合索引，这样根据status过滤后的数据就是按照create_time排好序的，避免在server层排序

索引口诀

全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
Like百分写右边，覆盖索引不写*
不等空值还有or，索引失效要少用