索引下推
概念： 什么是索引下推 索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。
作用：
- 减少回表查询的次数
- 减少存储引擎和MySQL Server层的数据传输量
架构： MySQL大概的架构：
原理：
Mysql索引合并
数据结构
select count(*)计数很慢
结论：count(*) ≈ count(常量) > count(id) > count(字段)
count(*) 、 count(常量) 是直接统计表中的总行数，效率较高。
而 count(id) 还需要把数据返回给MySQL Server端进行累加计数。
最后 count(字段)需要筛选不为null字段，效率最差。
联合索引
MySQL联合索引遵循最左前缀匹配原则，在少数情况下也会不遵循
统计每个字段的区分度：
select
count(distinct a)/count(*),
count(distinct b)/count(*),
count(distinct c)/count(*)
from test;
复制代码
值越大，区分度越高，优先放在第一列。
（a,b）字段上面创建联合索引，存储结构类似下面这样：
叶子节点存储全部数据，用双链表指针相连，数据都是先按a字段排序，a字段的值相等时再按b字段排序。
a字段的值是全局有序的，分别有1,1,1,2,2,2。
b字段的值是全局无序的，分别有1,3,5,1,3,5，只有在a字段的值相等时才呈现出局部有序。
第一题：
下面这条SQL，该怎么创建联合索引？
SELECT * FROM test WHERE a = 1 and b = 1 and c = 1;
复制代码
你以为的答案是（a,b,c），其实答案是6个，abc三个的排列组合，（a,b,c）、（a,c,b）、（b,a,c）、（b,c,a）、（c,a,b）、（c,b,a）。
MySQL优化器为了适应索引，会调整条件的顺序。
再给面试官补充一句，区分度高的字段放在最前面，大大加分。
第二题：
下面这条SQL，该怎么创建联合索引？
SELECT * FROM test WHERE a = 1 and b > 1 and c = 1;
复制代码
考察的知识点是： 联合索引遇到范围匹配会停止，不会再匹配后面的索引字段。
所以答案应该是：（a,c,b）和 （c,a,b）。
当创建（a,c,b）和 （c,a,b）索引的时候，查询会用到3个字段的索引，效率更高。
怎么判断是用到了3个字段的索引，而不是只用到前两个字段的索引呢？
有个非常简单的方法，看执行计划的索引长度。
由于int类型的字段占4个字节，3个字段长度刚好是12个字节。
第三题：
下面这条SQL，该怎么创建联合索引？
SELECT * FROM test WHERE a in (1,2,3) and b > 1;
复制代码
答案是（a,b）。in条件查询会被转换成等值查询，可以验证一下：
可以看到用到了两个字段的索引。
所以我们在平时做开发，尽量想办法把范围查询转换成in条件查询，效率更高。
链路追踪
在MySQL提供了一个好用的分析工具 — optimizer trace（优化器追踪）, 不用担心找不到MySQL用错索引的原因
1. 查看optimizer trace配置
show variables like '%optimizer_trace%';
复制代码
输出参数详解：
optimizer_trace 主配置，enabled的on表示开启，off表示关闭，one_line表示是否展示成一行
optimizer_trace_features 表示优化器的可选特性，包括贪心搜索、范围优化等
optimizer_trace_limit 表示优化器追踪最大显示数目，默认是1条
optimizer_trace_max_mem_size 表示优化器追踪占用的最大容量
optimizer_trace_offset 表示显示的第一个优化器追踪的偏移量
2. 开启optimizer trace
optimizer trace默认是关闭，我们可以使用命令手动开启：
SET optimizer_trace="enabled=on";
复制代码
3. 线上问题复现
先造点数据备用，创建一张用户表：
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(100) NOT NULL COMMENT '姓名',
`gender` tinyint NOT NULL COMMENT '性别',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_gender_name` (`gender`,`name`)
) ENGINE=InnoDB COMMENT='用户表';
复制代码
创建了两个索引，分别是(name)和(gender,name)。
执行一条SQL，看到底用到了哪个索引：
select * from user where gender=0 and name='一灯';
复制代码
跟期望的一致，优先使用了(gender,name)的联合索引，因为where条件中刚好有gender和name两个字段。
我们把这条SQL传参换一下试试：
select * from user where gender=0 and name='张三';
复制代码
这次竟然用了(name)上面的索引，同一条SQL因为传参不同，而使用了不同的索引。
到这里，使用现有工具，我们已经无法排查分析，MySQL优化器为什么使用了(name)上的索引，而没有使用(gender,name)上的联合索引。
只能请今天的主角 —optimizer trace（优化器追踪）出场了。
3. 使用optimizer trace
使用optimizer trace查看优化器的选择过程：
SELECT * FROM information_schema.OPTIMIZER_TRACE;
复制代码
输出结果共有4列：
QUERY 表示我们执行的查询语句
TRACE 优化器生成执行计划的过程（重点关注）
MISSING_BYTES_BEYOND_MAX_MEM_SIZE 优化过程其余的信息会被显示在这一列
INSUFFICIENT_PRIVILEGES 表示是否有权限查看优化过程，0是，1否
接下来我们看一下TRACE列的内容，里面的数据很多，我们重点分析一下range_scan_alternatives结果列，这个结果列展示了索引选择的过程。
输出结果字段含义：
index 索引名称
ranges 查询范围
index_dives_for_eq_ranges 是否用到索引潜水的优化逻辑
rowid_ordered 是否按主键排序
using_mrr 是否使用mrr
index_only 是否使用了覆盖索引
in_memory 使用内存大小
rows 预估扫描行数
cost 预估成本大小，值越小越好
chosen 是否被选择
cause 没有被选择的原因，cost表示成本过高
从输出结果中，可以看到优化器最终选择了使用(name)索引，而(gender,name)索引因为成本过高没有被使用。
索引潜水（Index dive）
mysql 排序语句
排序序号，从开始1 顺序递增
SELECT
resultNo
FROM
(
SELECT
p.billno,
concat(
NO,
lpad(@rank := @rank + 1, 5, '0')
) AS resultNo
FROM
(
SELECT
concat('NO.', '20', LEFT(BillNo, 6)) AS NO,
billno
FROM
`BusinessBill`
WHERE
`IsDelete` = 0
AND `BillPeriod` > '2023-01-01'
ORDER BY
`CorpNo` ASC,
`BillNo` ASC
) p,
(SELECT @rank := 0) r
) x
WHERE
x.billno = '2302040303170713451'
排序序号，并列排名 （从1开始 递增，重复则并列第二，有第三名）
排序序号，并列排名 （从1开始 重复则并列第二， 无第三名）
@prevRank = grade_subject,
@prevRank,
case when (@prevRank = grade_subject) and (@prevScore = score) then @curRank:= @curRank-1 else @nextRank := 1 end,
@curRank := IF(@prevRank = grade_subject, @curRank + 1, @incRank) AS rank,
@incRank := 0,
@incRank := @incRank + 1,
@prevRank := grade_subject,
@prevScore := score
FROM (
(SELECT * from table ) p,
(
SELECT @curRank :=0, @prevRank := NULL, @incRank := 1 , @prevScore:=null
) r
ORDER BY grade_subject
排序序号，并列排名
查询--点餐次数最多的人的排名
select count(1) from (
SELECT create_user as create_user1,count(1) num
FROM
tb_stem m
GROUP BY m.create_user
order by num desc
) xx
where num >=(SELECT count(1) num
FROM
tb_stem m where create_user='28'
GROUP BY m.create_user
)
获取 某个账单的序号 例如：NO.2023010900006
SELECT resultNo FROM (
select
p.billno,
concat(NO, lpad( sum(num), 5, '0')) as resultNo
FROM
(
SELECT
concat('NO.', '20', LEFT(BillNo, 6)) as NO,
billno,count(DISTINCT billno) as num -- count(1)
FROM
`BusinessBill`
WHERE
`IsDelete` = 0
and `BillPeriod` > '2023-01-01'
GROUP BY billno
ORDER BY `BillNo` ASC
) p where p.billno<='2301092305172396226'
) x
OR 语句 优化掉，通过 if() 和 union 结合业务组合改造
优化前：
SELECT COUNT(*) FROM PersonConsumptionDetailTab WHERE IsDelete=0 AND ClearingStateName != '' AND CorpNo='20210226150255633171' AND ( (ProductType =5 and ((TMCInsuranceFactoryID = 1 AND ProductSubTypeName IN ('出票','改签')) OR (TMCInsuranceFactoryID = 6 AND ProductSubTypeName IN ('退票')))) OR (ProductType IN (1,2) and RealOrderId != '' and DeptProcessStatus = 0 and TMCInsuranceFactoryID = 1 and CustomerProcessStatus = 0) ) AND ProductBillSureDate>='2023-03-06 00:00:00' AND ProductBillSureDate <= '2023-03-06 23:59:59';
优化后：
SELECT count(*) FROM PersonConsumptionDetailTab WHERE IsDelete = 0 AND CorpNo = '20210226150255633171' and ProductBillSureDate >= '2023-01-06 00:00:00' AND ProductBillSureDate <= '2023-03-06 23:59:59' AND ClearingStateName > '' and ProductSubTypeName IN ('出票', '改签') AND TMCInsuranceFactoryID IN (1, 6) and DeptProcessStatus = 0 and CustomerProcessStatus = 0 AND ProductType IN (1, 2, 5) and if(ProductType=5 ,'x',RealOrderId > '') >'' UNION SELECT COUNT(*) FROM PersonConsumptionDetailTab WHERE IsDelete = 0 AND ClearingStateName != '' AND CorpNo = '20210226150255633171' AND ProductType = 5 and TMCInsuranceFactoryID = 6 AND ProductSubTypeName IN ('退票') AND ProductBillSureDate >= '2023-03-06 00:00:00' AND ProductBillSureDate <= '2023-03-06 23:59:59'
mysql优化，导致查询不走索引
MySQL数据库order by 主键(索引) 查询慢解决方案
order by主键，这个可能是我们最常用到的一个不符合预期的情境
order by 和 limit 结合使用，如果where 字段，order by字段都是索引，那么有limit索引会使用order by字段所在的索引，没有limit会使用where 条件的索引
答案一： 尽可能将所有的查询字段包括在索引中，同时使用索引来消除排序； 答案二：force index
