mysql优化-分页优化，延迟索引的案例

mysql中大数据量的表分页查询；

按mysql的查询方式，会扫描前面的数据，而不是直接查询相关行的数据；

 

 

mysql> explain select * from  smth limit 90000,10;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | smth  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100074 |      100 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set

从执行计划看，mysql扫描了9929行数据，特别是在大数据量的查询中，这不是一个高效的查询sql,相当于是一个全表扫描的量了；

 

mysql> explain 
select * from  smth where id>99000 limit 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | smth  | NULL       | range | PRIMARY,idver | PRIMARY | 4       | NULL | 1000 |      100 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set

1:如果id是连续 那么比较好办，id是铸件索引；

从执行计划中，不难看出这样的方式是使用到了索引 进行范围查询；

 

但是这样的情况，并不一定使用正式情况，比如数据删掉之后；

 

 

2：通过执行计划，可以看出，查询满足条件的id，使用到了索引覆盖，这里并不需要回行，比较高效的得到了id列表

mysql> explain select  t.* from  smth t  inner join  (select id from  smth limit 9000,10) a on  a.id=t.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL |   9010 |      100 | NULL        |
|  1 | PRIMARY     | t          | NULL       | eq_ref | PRIMARY,idver | PRIMARY | 4       | a.id |      1 |      100 | NULL        |
|  2 | DERIVED     | smth       | NULL       | index  | NULL          | idver   | 9       | NULL | 996754 |      100 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
3 rows in set

 

 

 

从执行计划可以看出，通过索引扫描，索引覆盖的方式很高效的得到了需要查询的数据的id;

通过id主键索引关联得到了相关的行数据，这种方式叫 索引延迟

 

 

3：比较2中的另一个写法

mysql> explain select * from  smth where id in (select id from (select id from  smth limit 80000,10) b) \G; 

+----+--------------+-------------+------------+--------+---------------+------------+---------+--------------+------+----------+-------------+

| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref          | rows | filtered | Extra       |

+----+--------------+-------------+------------+--------+---------------+------------+---------+--------------+------+----------+-------------+

|  1 | PRIMARY      | smth        | NULL       | ALL    | PRIMARY,idver | NULL       | NULL    | NULL         | 9929 |      100 | Using where |

|  1 | PRIMARY      | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 4       | test.smth.id |    1 |      100 | NULL        |

|  2 | MATERIALIZED | <derived3>  | NULL       | ALL    | NULL          | NULL       | NULL    | NULL         | 8010 |      100 | NULL        |

|  3 | DERIVED      | smth        | NULL       | index  | NULL          | idver      | 9       | NULL         | 9929 |      100 | Using index |

+----+--------------+-------------+------------+--------+---------------+------------+---------+--------------+------+----------+-------------+

4 rows in set





mysql> explain select * from  smth where id in(8001,8002,8003,8004,8005,8006,8007,8008,8009,8010);

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | smth  | NULL       | range | PRIMARY,idver | PRIMARY | 4       | NULL |   10 |      100 | Using where |

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

1 row in set

很直观的发现，两种写法的执行计划是不一样的，第一种的子查询属于动态sql，在查询id的时候是使用到了索引，但是在进行数据查询的时候并没有使用索引；