MYSQL优化技巧：5 大步骤 + 10 个案例
-
ALL 全表扫描 -
index 索引全扫描 -
range 索引范围扫描，常用语<,<=,>=,between,in等操作 -
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中 -
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询 -
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询 -
null MySQL不访问任何表或索引，直接返回结果
idx1(a, b, c),
idx2(a, c)，SQL为
select * from t where a = 1 and b in (1, 2) order by c;如果走idx1，那么是type为range，如果走idx2，那么type是ref；当需要扫描的行数，使用idx2大约是idx1的5倍以上时，会用idx1，否则会用idx2
-
Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。 -
Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化 -
Using index：表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据。 -
Using index condition：MySQL5.6之后新增的ICP，using index condtion就是使用了ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。
SHOW PROFILES ;
SHOW PROFILE FOR QUERY #{id};
set optimizer_trace= "enabled=on";
set optimizer_trace_max_mem_size= 1000000;
select * from information_schema.optimizer_trace;
-
优化索引 -
优化SQL语句：修改SQL、IN 查询分段、时间查询分段、基于上一次数据过滤 -
改用其他实现方式：ES、数仓等 -
数据碎片处理
基于微服务的思想，构建在 B2C 电商场景下的项目实战。核心技术栈，是 Spring Boot + Dubbo 。未来，会重构成 Spring Cloud Alibaba 。 项目地址：https://github.com/YunaiV/onemall
KEY `idx_shopid_orderno` (`shop_id`,`order_no`)
select * from _t where orderno= ''
shop_id,
order_no)调换前后顺序。
KEY `idx_mobile` (`mobile`)
select * from _user where mobile= 12345678901
KEY `idx_a_b_c` (`a`, `b`, `c`)
select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;
SELECT
t1.*
FROM
_t t1,
( SELECT id FROM _t WHERE a = 1 AND b = 2 ORDER BY c DESC LIMIT 10000, 10 ) t2
WHERE
t1.id = t2.id;
KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)
SELECT
*
FROM
_order
WHERE
shop_id = 1
AND order_status IN ( 1, 2, 3 )
ORDER BY
created_at DESC
LIMIT 10
n*m的方式去搜索，类似union，但是效率比union高。
代价 = 元组数 * IO平均值），是通过将in包含的数值，一条条去查询获取元组数的，因此这个计算过程会比较的慢，所以MySQL设置了个临界值(
eq_range_index_dive_limit)，5.6之后超过这个临界值后该列的cost就不参与计算了。
order_status,
created_at)互换前后顺序，并且调整SQL为延迟关联。
KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)
SELECT
*
FROM
_order
WHERE
shop_id = 1
AND created_at > '2021-01-01 00:00:00'
AND order_status = 10
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
select * from _order where order_status = 1
select sum(amt) from _t where a = 1 and b in ( 1, 2, 3) and c > '2020-01-01';
select * from _t where a = 1 and b in ( 1, 2, 3) and c > '2020-01-01' limit 10;
select * from _t where a= 1 order by b desc, c asc
个人博客网站：https://programmerblog.xyz