十九、MySQL 用 limit 为什么会影响性能？

灌入大量数据，共500万：我们知道，当limit offset rows中的offset很大时，会出现效率问题，

mysql> select * from test where val=4 limit 300000,5;
+---------+-----+--------+---------+
| id      | val | source | id      |
+---------+-----+--------+---------+
| 3327622 |   4 |      4 | 3327622 |
| 3327632 |   4 |      4 | 3327632 |
| 3327642 |   4 |      4 | 3327642 |
| 3327652 |   4 |      4 | 3327652 |
| 3327662 |   4 |      4 | 3327662 |
+---------+-----+--------+---------+
5 rows in set (15.98 sec)

为了达到相同的目的，我们一般会改写成如下语句：

mysql> select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;
+---------+-----+--------+---------+
| id      | val | source | id      |
+---------+-----+--------+---------+
| 3327622 |   4 |      4 | 3327622 |
| 3327632 |   4 |      4 | 3327632 |
| 3327642 |   4 |      4 | 3327642 |
| 3327652 |   4 |      4 | 3327652 |
| 3327662 |   4 |      4 | 3327662 |
+---------+-----+--------+---------+
5 rows in set (0.38 sec)

时间相差很明显。

        为什么会出现上面的结果？我们看一下select * from test where val=4 limit 300000,5;的查询过程：

查询到索引叶子节点数据。根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。

类似于下面这张图：

        像上面这样，需要查询300005次索引节点，查询300005次聚簇索引的数据，最后再将结果过滤掉前300000条，取出最后5条。MySQL耗费了大量随机I/O在查询聚簇索引的数据上，而有300000次随机I/O查询到的数据是不会出现在结果集当中的。

面试题：mysql性能优化：单表1400w查询最后十条数据（耗时0.036s）？

1.常规分页

        SELECT * FROM table_name limit 14000000,10//耗时19.426s

2.先查id ，写法很多，看个人习惯

        SELECT * FROM table_name a,(SELECT id FROM table_name limit 14000000,10) b WHERE a.id = b.id //耗时3.068

3.如果你的表有自增id(并且没断层)，就这么写，效率直接起飞

        SELECT * FROM table_name WHERE id> 14000000 LIMIT 10 //耗时0.036

【虽然性能嘎嘎猛，但局限性太大，首先你得是自增id，并且id不能有断层，这对表维护要求很高，每次删除了数据都得清一下旧id，就图个乐吧，量大了老老实实上es、ck，要么就让你的用户接受查询慢一点】