详解MySQL索引失效、实际使用场景，慢查询优化实战

索引失效情景

平时我们在使用索引时，有时会以为自己的sql语句能命中索引，但索引实际上是失效的，我们可以用explain关键字来判断，以下总结了几种索引失效情景~

1. 对索引使用左模糊匹配 or 左右模糊匹配

有一张表user, 存在联合索引name-age-male, name age male分别为表的字段, 执行如下sql

                    select * from user where name like "%test";

通过explain字段分析可得到：

key为NULL，未命中索引！ 在执行该条sql时，筛选条件中的name会匹配到联合索引中的最左项name, 但是我们查询条件中的name的左边是模糊的，只限制了一定要以test结尾，开头可匹配上任意值。因此，索引树拿到该查询条件，不知道该往“哪边走”。索引的有序性被破坏。索引失效！

将模糊匹配换个边，执行如下sql

                    select * from user where name like "test%";

通过explain字段分析可得到：

key不为NULL，命中索引！ 在执行该条sql时，筛选条件中的name会匹配到联合索引中的最左项name, 与前者不同，name的右边是模糊的，只限制了要以test开头，结尾可匹配上任意值。因此，索引树拿到该查询条件，知道该往“testXXXX的方向走”。没有破坏索引的有序性。索引仍旧生效！

2. 对索引使用函数计算

继续在相同的表user, 相同的联合索引下操作, 执行如下sql

                    select * from user where lengh(name) = 6;

通过explain字段分析可得到：

key为NULL, 索引失效！ 索引的字段经过计算后，索引的有序性无法得到保证；假设存在一函数，y = f(x), 原先的x可能是有序的，但经过f(x)计算后，结果y的数值顺序可能被完全打乱，有序性被破坏。

PS: MySQL在这里偷了个懒, 即使筛选条件是 x+1=2; x+"123"="test123"; 这种条件，能保证计算结果y依旧是有序的函数，也会直接令索引失效

3. 对索引隐式类型进行转换

有一张表user, 存在索引name和age, 分别执行如下sql

                    select * from user where name = 12345;
                    select * from user where name = "12345";

通过explain分别分析两条sql结果：

对比可知，sql(1)的索引失效了，而sql(2)的索引成立！，表中的name字段的类型是varchar，当索引字段为字符串时，输入的参数是整型，会导致索引失效。反之则不会。MySQL在计算的时候，会自动将字符串转换为数字

我们做个证明，通过如下sql

                                select "10" > 9;

如果是数字转字符串，"10" > 9输出的结果为0；因为 字符串"1"大于字符串"0"

如果是字符串转数字，"10" > 9输出的结果是1；因为 10>9是必然现象

结果如下：

因此，原来的sql(2)等价于

                    select * from user where name = "12345"; 
                                    |
                                    |
                                   \|/
          select * from user where cast(name as signed int) = "12345";

本质上还是对索引列做了函数计算, 从而导致破坏了索引的有序性

4. 多表联查引起的隐式类型转换

存在一张表user, 该表的字符编码为uf8mb4, id为主键, name为索引列字段; 同时还存在另外一个表user_test, 该表的字符编码为uf8, id为主键, name为索引列字段

执行如下sql:

       select * from user_test where user_test.name = user.name and user.id = 1;
     select * from user_test where user.name = user_test.name and user_test.id = 1;

通过explain分析两句sql 如下：

explain中，有多条数据时，当id一样时，语句从上到下执行，因此，我们可分析出该条sql的执行顺序。

先是执行了user.id = 1的查询条件，走了user表的主键索引树，找到所有符合user.id = 1的数据
在找到的user表的数据中，取出name字段，准备和user_test表的name字段比较，但这里看，原来user_test表的name字段是有索引的，但索引失效了
表user中的字符编码为utf8mb4, 而user_test表的字符编码为utf8, utf8mb4为utf8的超集，因此，对于user_test的name字段来说，需要隐式的执行convert(name using utf8bm4)，在索引列上计算，会导致索引失效

Tips:（S1和S2是两个集合，S1包含了S2的所有元素，且存在S2没有的元素，则称S1为S2的超集）

对于sql(2)来说，我们也可分析出该条sql的执行顺序

先是执行了user_test.id = 1的查询条件，走了user_test表的主键索引树，找到所有符合user_test.id = 1的数据
在找到的user_test表的数据中，取出name字段，准备和user表的name字段比较。此刻，由于上述所说的编码问题，需要对user_test的name字段做隐式类型转换，但函数是作用在具体数据上，非索引列！ 因此索引未失效。

5. 联合索引不满足最左匹配原则

创建了一个(a, b, c)联合索引，根据最左匹配原则，可以看成建立了a, (a,b), (a,b,c)三个索引，当执行的sql带有以下的筛选条件，均会走索引

                                   Where a=N;
                               Where a=N and b=N;
                           Where a=N and b=N and c=N;

如果是查询条件中缺少了a，则不满足最左匹配原则（联合索引的最左项未被匹配到，后续索引均失效），例如：

                                   Where b=N;
                                   Where c=N;
                               Where b=N and c=N;

当情况是

                               Where a=N and c=N;

只有a和c，跳过了b，由于最左匹配原则，a能被匹配到，而b未出现，导致后续的索引不会继续匹配；总体来说索引仍旧生效

6. 联合索引中出现计算符号

创建了一个联合索引（a, b, c），可以看成建立了a, (a,b), (a,b,c)三个索引，当执行的sql，如果有一索引列出现了 > < != 等计算符号，会直接导致后续列的索引直接失效

表user, 存在联合索引name-age-male, name(varchar(32)), age(int(11)), male(tinyint(1))分别为表的字段，执行如下sql:

          select * from user where name like "张%" and age > 10 and male = 1;

由于age中出现了 > 计算符号，会导致后面的索引（male）失效，通过explain分析

通过explain的分析结果可知，仅走了name-age索引，male索引失效，通过key_len（实际走的索引列的字节长度）可知：(4×32+3) + (4+1) = 136

Tips: utf8mb4编码-> 1个字符占4个字节, varchar(32)占32个字符, 另外需要1个字节保存是否为NULL, 2个字节记录真实长度; 因此name实际会走 4×32+3个字节

int占4个字节，另外需要1个字节保存是否为NULL，因此age占5个字节

7. where语句中出现or

存在一张表user，有字段name和age, name和age都是普通索引列，执行如下sql

              select * from user where name = "test" or age = 123;

通过explain字段分析可知

即使name和age都是普通索引列， 索引也会失效，走全表扫描... （感觉这里更像是Mysql偷了个懒, 可以直接找两颗索引树，取并集）

但是如果一定要走索引，强制走索引，可以将sql修改成如下:

                  select * from user where name = "test" 
                              union
                  select * from user where age = 123;

通过explain字段分析可知

通过explain分析我们可知，该条sql的实际操作为分头执行两条索引sql，再对结果取并集

总结上文我们可知，即使是查询条件的列都有索引，若是Mysql强制走索引，后续还需要合并多个结果集，在数据量大的情况可能复杂度暴涨，操作效率也是极低，因此MySQL会直接放弃索引，全表扫描

8. 特殊案例1——or存在走索引可能性

在某一特别条件下，or条件还是会走索引的，看如下sql对比, id是表的主键索引，type是表的普通索引

  explain select * from inspection_group_plan where id = xxx or type = "AT_ONCE"; 
  explain select id, type from inspection_group_plan where id = xxx or type = "AT_ONCE";

对比可知，仅在主键索引+二级索引的组合下，此时MySQL只要遍历二级索引树，就可以直接得到筛选条件中的id和type值（二级索引树的叶子节点存储的是主键），并且不能存在回表，（不能为select *），避免为了补齐*中的数据，还需要去主键索引树查询。

9. 特殊案例2——左（右）模糊匹配不一定会导致索引失效

存在一张表user，表中存在id, name, age, male字段，建立（name,age,male）联合索引，当执行以下sql时：

                   select * from user where name like "%test%";

结果令人意外，索引居然没有失效，原因是：表user中不存在非索引字段，id为主键索引，另外有（name,age,male）联合索引，MySQL会觉得：我直接去遍历二级索引树（记录的东西更少），并且也可以直接获取我们所需要的数据（*表示了id, name, age, male；刚好联合索引树中都有），不用回表，因此MySQL会直接遍历二级索引树！

Tips: explain中的type为index，代表该sql的执行方式为遍历二级索引树；key_len为138，通过上文的计算我们也可得知走的是name_age_male联合索引；Extra中存在Using index, 表明该sql语句用到了覆盖索引，避免回表

如果修改一下联合索引，改为name-age索引，male为非索引字段，执行同样的sql，结果为：

索引直接失效！

小小总结下：

如果使用联合索引，一定要遵循最左匹配原则（查询从索引的最左列开始写，不要跳过索引列）
联合索引出现范围查询，会导致右侧的索引直接失效，即使有匹配上
尽量不要使用左模糊查询，即使有特例，也容易导致索引失效
从索引的有序性出发，不要做任何会破坏索引有序性的操作。例如：对索引做函数计算，有可能产生的隐式转换（字符串转数字，表的字符集转换）
数据量大时，查询尽可能避免使用到or

索引的实际使用场景

具体会展开说说索引在join，排序，计数发挥的作用

1. 排序

存在一张表datasource, 有一个索引字段device_measurement_name和非索引字段tsdata_id, 执行如下sql

  select * from datasource where device_measurement_name like "33%" order by tsdata_id;

分析结果如下：

通过上述结果，我们可以看出该sql大致的执行顺序：

先命中索引（key不为NULL），将符合筛选条件的主键都取出来
通过回表获取完整的行数据（Extra中不存在using index）
分配到一块内存，将获取到的行数据放在里面通过tsdata_id排序（Extra存在Using filesort）字段

如何进一步分析Using filesort？三步曲如下：

                           /*打开optimizer_trace*/
                    set optimizer_trace = 'enabled=on';
                               /*执行具体sql*/
  select * from datasource where device_measurement_name like "33%" order by tsdata_id;
                             /*输出optimizer_trace*/
                  select * from `information_schema`.`optimizer_trace`;

输出的结果如下：

rows表明符合条件的行数据的数量
number_of_tmp_files表明用来排序的磁盘文件的数量，MySQL先用自己的内存池排序，如果空间不够，才会向磁盘申请空间。 number_of_tmp_files=15 表明使用了15个磁盘文件来排序。
sort_buffer_size为排序缓存池的实际空间
sort_mode为实际放入缓存池排序的数据，本来应是要把整行数据放入内存池排序，但由于数据庞大，MySQL为了节约空间，用行数据的隐藏列rowId替换完整数据，放入内存池排序。算是一个优化。

如果数据天然有序，就完全用不到排序内存池，极大节省了空间和时间。

索引天生就具备有序性， 因此我们给tsdata_id加上索引，再执行相同的sql, 分析结果如下：

PS：Extra中已经没有Using filesort字段，表明该sql已经不再需要额外内存来排序

当然还可以近一步优化，如上图，把select *替换成索引字段，使用覆盖索引避免回表（Extra中出现Using index）

2. Count

count的含义是：统计符合查询条件的记录中，Count中指定的参数不为NULL的个数, 下面分别对比 count(主键)，count(1)，count(普通列)的区别

count(主键)，会走索引树统计，将符合条件的数据取出来，返回给MySQL的Server层，再由Server层判断主键是否为NULL，计算出总值。

分以下两种情况，走的索引树不同

假设一张表中除了主键，没有其他索引，执行的sql如下：

                      select count(*) from audit_log;

分析结果如下：

可以看出，该sql走的是主键索引树，如果再将表中的user_id设置为索引列，再执行同样的sql，分析结果如下：

走的是二级索引树，原因是：二级索引树的叶子节点存储的是对应的主键值，比起主键索引树，占用更小的空间，IO成本更小。遍历二级索引树就能达到count的目的。

count(1),会走索引，将符合条件的数据取出来返回给Server层，不会做任何判断，直接计数+1，（比其他省略了判NULL的步骤）。1直接是数字，必然不会NULL，执行的count(1) sql如下：

和count(主键)一样，走成本最小的二级索引树实现统计效果。

count(*)，和count(1)类似，MySQL专门对count(*)优化，将count(*)约等于count(0)，来做处理。结果如下：

执行sql，通过show waring可看出，MySQL对其做的优化

count(列名)

如果该列存在索引，则会遍历对应的二级索引树拿到符合条件的数据，再返回给Server层，由Server层做判NULL操作，再计数+1，分析结果如下：

如果该列不存在索引，则会直接全表扫描，再将符合的结果返回处理。分析结果如下：

总结下：从效率的角度比较，count(*) = count(1) > count(主键) > count(普通索引列) > count(无索引列)

3. Join

我们写sql的时候，往往会涉及到多表联查，这时候就会用到join，而join的使用更要小心再小心，一不小心就会导致灾难级别的效率问题

假设有一张表t1, 存在索引字段a,非索引字段b, 有100条数据；还有一张表t2, 也存在索引字段a,非索引字段b, 有1000条数据，执行如下的sql：

            select * from t1 straight_join t2 on t1.a = t2.a;

通过explain我们可以得到该sql的执行效率：

对于id相同的sql，执行顺序为从上到下，可看出，t1作为驱动表，t2作为被驱动表。

执行顺序如下：

取表t1中的一行数据
将取的行数据中的a字段拿出来，去表t2中查询
将符合筛选条件的数据拼接在一起
重复步骤1-3，直到表t1的数据被取完

从分析结果可看出，从表t1中把a字段取出来后，在表t2里找数据时会命中索引。因此，对表t1是全表扫描，表t2是命中索引树，总扫描行数计算为：

100(t1全表扫描行数) + 100(t2索引精准获取) = 200

可以用上被驱动表t2的索引，又称之为Index Nested-Loop Join (NLJ)

如果join筛选的条件是在非索引字段b的话，则执行sql：

会发现被驱动表t2的Extra，出现了Using Join buffer(Block Nested-Loop (BNL))的字段。

BNL的执行顺序如下：

对驱动表t1做全表扫描，将扫描结果放在join buffer内
扫描表t2，把t2的每一行都取出来，跟join buffer里的结果做对比
满足条件的数据，会放入结果集等待。

如果join buffer的内存无法一次性放下驱动表的数据怎么办？

join buffer的内存大小通过join_buffer_size参数控制，默认为256K。如果无法一次性放下的话，需要分段放，原来的步骤就变成：

扫描t1, 直到join buffer内存满
扫描t2，和join buffer内的数据做比较
将符合条件的结果放入数据集
清空join buffer，重复步骤1-3，直到t1被全部扫描完。

于是，扫描行数就变为：

100(t1全表扫描) * 1000(t2全表扫描) = 100000

每在表t1取一行数据，都得对表t2做一次全表扫描，因此如果join出现了BNL，是要尽量去避免的。

MySQL自身对join有做什么优化吗？引入batched key access (BKA)和MRR（顺序读盘）

我们执行一条带索引的范围查询语句： select * from t1 where a > 50 and a < 100;

这类型的sql我们都很熟了，会先走普通索引树，将符合条件的主键id都找出来，然后一个个的进行回表查到完整行数据。虽然a的值是按增顺序递增的，从50-100。但id的值很有可能是随机的，就会出现随机访问，性能相对较差

我们表的主键在大多数情况都是按照递增顺序插入的，因此我们可认为，如果按照主键的顺序递增顺序查询的话，对磁盘的读写近似于顺序读，效率远优于随机读

因此，便有了MRR，原来的sql语句的执行顺序变成了：

通过普通索引树找到符合条件的主键id
将id做递增排序
将排序后的id依次去主键索引树查找数据

MRR可通过 set optimizer_switch = 'mrr_cost_based = off' 开启

而BKA策略，就是对NLJ的优化

在被驱动表的索引列和驱动表的数据做查询后，会对获取的主键id做递增排序，再去进行回表补充完整数据。

BKA通过 set optimizer_switch = 'mrr = on,mrr_cost_based = off, batched_key_access = on' 开启

如何避免Join中的BLJ？

通过合理加索引的方式，将BLJ升级成NLJ，例如如下sql:

           select * from t1 join t2 on t1.b = t2.b where t2.b < 1000;

分析结果如下，Extra中出现了Blocking Nested Loop，即用到了BLJ算法

可以在t2表上增设b索引，这样就可以解决问题。但是，如果这是一条很低频的sql，还要为了它，在表t2上新设一个索引，这就很浪费了

不创建索引会面临全表扫描的危险（BLJ），而创建了索引会浪费资源，使用临时表解决

将表t2中满足条件的数据放在临时表内
往临时表的b字段添加索引
让表t1和临时表做join

这样操作，sql就变成：

  create temporary table temp(id int primary key, a int, b int, index(b))engine=innodb;
                insert into temp select * from t2 where b<1000;
                select * from t1 join temp on t1.b = temp.b;

执行结果如下，Extra内为NULL，走了索引用到NLJ算法。

总体来说，无论是加索引，或者是临时表，都是要让其在join的过程中，能够触发被驱动表的索引，避免各种全表扫描的性能消耗问题

下面我们推导一个join搜索行数的公式

驱动表的行数为M，无论有索引，在驱动表上都要做全表扫描

被驱动表的筛选条件列有索引，且表的行数为N

查找树的时间复杂度为O(logN), 因此在有索引的被驱动表上查找一次数据，时间为2logN (考虑了回表)

对于驱动表来说，每获取一行驱动表的数据，都要去被驱动表匹配一次，因此扫描的复杂度为：

M + M × 2 × logN = M(1 + 2logN)

相较于M，logM的涨幅更小（高中的导数知识），M对扫描行数影响更大。因此，我们应当让行数较少的表来做驱动表。（驱动表也必然会全表扫描）

在sql里，驱动表和被驱动表是如何选定的？ A join B, A一定是驱动表吗？B一定是被驱动表吗？选定规则如下：

当join为(inner) join时，MySQL会自动选择（经过筛选条件下后的）表数据量小的表作为驱动表

调换顺序，通过explain的结果可知，首先执行的都是t1，首先执行的表即驱动表。

当join为left join时，MySQL会 自动选择左边的表 作为驱动表，结果如下：

调换顺序，通过explain的结果可知，首先执行的都是左边的表

当join为right join时，MySQL会 自动选择右边的表 作为驱动表，结果如下：

调换顺序，通过explain的结果可知，首先执行的都是右边的表

当sql语句中存在where筛选条件时，情况略复杂，分以下几类

如果where筛选的列有索引，无论是join, left join, right join，都会选择where筛选后数据量较小的表做驱动表

执行如下sql，分析结果如下，原本left join会指定t1做为驱动表，但是经过where条件筛选后的a字段，命中索引，且筛选后的数据量小于t1（50小于100），因此选择t2作为驱动表

将where条件稍微修改，执行如下sql，分析结果如下，由于筛选后的数据量为200，大于t1（500小于100），因此依旧是选择t1作为驱动表

如果where筛选的列没有索引，则原有的join, left join, right join选择驱动表的规则不会发生变化

执行如下sql，b为非索引字段，分析结果如下，原本left join会指定t1做为驱动表，但是经过where条件筛选后的b字段，没有命中索引，即使是筛选后的数据总数小于t1（50<100），依旧选择t2作为驱动表

这里提到了where和join，补充一下select语句关键字的执行顺序，官方提供的标准执行顺序，如下图：

先看from字段，需要查询哪张表
有join会先执行join相关内容
有group by会再执行分组内容
分析select后面需要什么数据，做补充
最后有order by和limit的分组相关内容，放最后执行

补充分析上面的例子3，执行如下两条sql，并分析其执行情况：

                select * from t1 left join t2 on t1.id = t2.id and t2.a < 50;
                select * from t1 left join t2 on t1.id = t2.id where t2.a < 50;

两条sql都采用的是left join，即以左边的表t1为主。对于不满足on后面的筛选条件的表t1数据仍然会保留，而将表t2对应的数据列置为NULL

对于sql（1）来说，两个筛选条件 t1.id = t2.id 和 t2.a < 50 都在on里面，因此执行顺序为：

t1作为驱动表，全表扫描
将t1中的数据逐一取出，去t2表里找寻满足t2.id = t1.id和t2.a < 50的t2表数据
若有，返回完整数据；若无，则将t2.*的数据置为NULL，返回

对于sql（2）来说，两个筛选条件分开，通过explain我们可分析出执行顺序为：

t2作为驱动表，全表扫描
将t2中的数据逐一取出，去t1表里找寻满足t2.id = t1.id的t1表数据。若不满足，则将t2.*的数据置为NULL。（on先于where执行）
获取所有数据（包含t2.*为NULL的），逐一判断where条件中的t2.a < 50。其中 t2.a有值的，只会保留小于50的数据；t2.a == NULL的，将不符合where条件的筛选，会被丢弃。

因此，对于sql（2）来说，所获取的结果已经失去了原来使用left join的初衷（驱动表的数据不能全部保留）。

造成这种现象的原因是什么呢？

通过命令 show warnings; 来查看优化器优化后的sql（2），如下：

仔细对比可看出几个关键点：1. 原先的left join被join替代（不强制走某张表）；2. on被去掉，原先on的条件被合并到where里

因此，如果想要left（right） join的含义，最好将where里的条件也一起放到on当中。

慢查询的分析及优化

下面会举一些我在公司生产环境下碰到的慢查询例子，一些敏感字段已经被我替换掉了~

1. 多张大表的join

一开始多张表的join，在数据量少的时候还感知不到；数据量随着使用时间的增长而不断膨胀，数据量突然暴涨，导致慢sql，最终接口超时。排查后思路如下：

假设存在三张表，分别是emp, emp_log, emp_cert；各自有100W, 200W, 100W数据。三张表仅存在主键索引（id）。

执行如下sql:

执行时间为53秒，除了主键，表的其他字段是没有索引的，没有索引导致的慢查询，我们第一步肯定是要将索引补上，从而避免由于join的数据量呈现指数型暴涨。因此，我们先给emp_log的create_time字段和emp_cert的emp_id加上索引，再执行同样的sql。结果如下：

时间为3.34s，效率已经比没有索引时得到质的提升。通过explain字段进行分析，结果如下：

当id相同时，由上到下依次执行。因此我们可分析出该sql具体的执行顺序为：

先执行emp_log这张表的筛选条件：create_time，由于该列存在索引，会走相应的二级索引树，此时遍历的表行数可以从rows中看出大概有92114行。
存在一张内连接join生成的临时表cert, 其中emp作为驱动表，而emp_cert作为被驱动表。根据上文的分析，驱动表必然会全表扫描，但emp表存在主键索引树，并且连接筛选条件也只取主键，因此MySQL会遍历代价更小的主键索引树，获取emp的id值。
拿着取到的id值，在emp_cert表上做emp_id的等值查询，会走emp_id索引树。
最后，将建立的临时表，和筛选后的emp_log再做join连接。并且筛选条件是or，不会走索引的。此时的数据量，是临时表的行数 * 过滤后的92114条emp_log记录。

3.34s还是太久了，根据我们上面分析的最后一步，or会导致索引失效；并且临时表和emp_log的没有索引，join出来的数据量也是惊人的；我们可根据这两点去进行优化。有两种思路可走：

我们可以用union来替换掉or，把or的两边拆掉，分两个相同的表查询不同的条件，让MySQL强制走两边的索引，然后再取并集。
避免无索引的大数据join，避免临时表和emp_log做连接

因此，可将sql改造如下：

二度优化后执行的时间仅0.14s，通过explain字段进行分析，结果如下：

id越大的数字越先执行，id相同的数字从上到下依次执行。我们可看出，id为1和2的基本为镜像执行，顺序如下：

无论是上面和下面的表，都是先执行emp_log这张表的筛选条件：create_time，这个和优化前是一样的，肯定逃不掉。
随后两个表的两次join，都是直接命中了被驱动表的索引，避免了全表扫描导致的数据量暴涨问题，效率自然就上去了。
最后，将两个不同筛选条件出来的表数据取并集，得到结果。

2. limit查询优化

在实际项目中，往往会碰到分页查询，该查询则是涉及到limit的使用。

常见的limit的sql语句为：

        select * from user order by id limit offset size;

其中offset是查找到的符合条件数据的偏移量，size是该sql语句要取多少条数据。 limit的语句的基本执行顺序如下：

先是计算 offset * size的值，表明这次需要从哪里开始取数据
不断遍历全表or索引树，innodb引擎层获得 offset * size + size条数据，返回给Server层
Server层将前 offset * size条数据丢弃，返回符合条件的数据

可以看出，当offset不为0时，Server层会拿到很多没有用的数据，执行的sql时间也会增加，例如如下sql:

                select * from user order by id limit 900000, 10;

limit 900000,10表明需要取 900000+10条数据，并抛弃前900000条，耗时极大。如何优化？

因为前900000条数据是可以直接丢弃，完全用不到的。但是该sql的select后面跟的是*。因此，我们可以将前900000条特殊处理，只取id，可将sql优化成如下：

 select * from user where id >= (select id from user order by id limit 900000, 1) limit 10;

先通过子查询，将前900000条数据的id单独取出来，丢弃并取最大一位，这样sql就会优化为如下：

                 select * from user where id >= 900001 limit 10;

这样只需要直接走主键的范围查询，并直接取前10条数据即可，实测，性能大概可提升一倍。

当排序的规则不是主键id，而是换成了普通索引列，这时候？

对于非主键索引来说，叶子节点存放的是主键id，因此二者的差距是多了个回表的消耗，如果将分页的sql换成：

                select * from user order by age limit 900000, 10;

当offset如果很大的时候，多了次回表的操作，可能MySQL就不会选择走索引，会放弃遍历索引树，直接选择全表扫描（MySQL在索引的区分度不高时，或者需要做大量回表的情况，优化器可能会直接放弃走索引，认为代价太高）。因此可以将sql修改成：

select * from user join (select id, age from user order by age limit 900000, 10) user2 on user.id = user2.id;

通过子查询创建出一个临时表，只保存id和age（避免回表），并直接在这个子查询中做好分页。随后，在和原始表做join，这时候只要把原始表的id和临时表的id做对应就好。

好像只要涉及到分页，都逃不开需要查一堆数据直接丢弃的问题，当一张表的数据量特别大时，深度分页肯定是躲不开的。

目前为止看到的比较实用的方法是直接做一个预存，将所有的数据通过主键id排序，然后分批做处理（比如10条1页，20条1页这样），将当前批次的最大id作为下一次筛选的id查询

这样将原来的 order by id limit offset size 变为 id > pre_page_start_id，躲开了查一堆数据丢弃的问题，同时将前端的UI改为上下页的形式，如下:

这样就能保证每一批数据以上一页的最大id为起点，无论翻到多少页，查询速度始终稳定。（类似功能还有抖音和快手的短视频功能，只能上划和下划）

（二）MySQL索引篇-2：详解MySQL索引失效、索引实战及慢查询分析

详解MySQL索引失效、实际使用场景，慢查询优化实战

索引失效情景

1. 对索引使用左模糊匹配 or 左右模糊匹配

2. 对索引使用函数计算

3. 对索引隐式类型进行转换

4. 多表联查引起的隐式类型转换

5. 联合索引不满足最左匹配原则

6. 联合索引中出现计算符号

7. where语句中出现or

8. 特殊案例1——or存在走索引可能性

9. 特殊案例2——左（右）模糊匹配不一定会导致索引失效

索引的实际使用场景

1. 排序

2. Count

3. Join

慢查询的分析及优化

1. 多张大表的join

2. limit查询优化