示例

表aaaaa中共有2375690数据。

优化前的SQL

SQL执行结果：

SELECTDISTINCT(device_id)uidFROMaaaaaWHEREstatus=0LIMIT88000,1000;1000rowsinset(0.48sec)

SQL执行计划：

MariaDB[star]>explainSELECTsql_no_cacheDISTINCT(device_id)uidFROMaaaaaWHEREstatus=0LIMIT88000,1000;+------+-------------+---------------+------+---------------+------+---------+------+---------+------------------------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+------+-------------+---------------+------+---------------+------+---------+------+---------+------------------------------+|1|SIMPLE|aaaaa|ALL|NULL|NULL|NULL|NULL|2375690|Usingwhere;Usingtemporary|+------+-------------+---------------+------+---------------+------+---------+------+---------+------------------------------+

优化方式

迅速定位起始ID，利用主键索引，加快扫描速度。可以看到，derived中，SQL使用到了覆盖索引进行扫描，虽然还是全表扫，因为只扫描id列，大大降低了扫描的IO耗费，快速定位到了id。

MariaDB[star]>explainSELECTsql_no_cacheDISTINCT(device_id)uidFROMaaaaajoin(selectidfromaaaaalimit88000,1)konstar_device_5.id>=k.idwherestatus=0limit1000;+------+-------------+---------------+-------+---------------+-------------+---------+------+---------+------------------------------------------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+------+-------------+---------------+-------+---------------+-------------+---------+------+---------+------------------------------------------------+|1|PRIMARY||ALL|NULL|NULL|NULL|NULL|88001|Usingtemporary||1|PRIMARY|star_device_5|ALL|PRIMARY|NULL|NULL|NULL|2377112|Rangecheckedforeachrecord(indexmap:0x1)||2|DERIVED|star_device_5|index|NULL|idx_star_id|8|NULL|2377112|Usingindex|+------+-------------+---------------+-------+---------------+-------------+---------+------+---------+------------------------------------------------+

执行结果：

SELECTsql_no_cacheDISTINCT(device_id)uidFROMstar_device_5join(selectidfromstar_device_5limit880000,1)konstar_device_5.id>=k.idwherestatus=0limit1000;1000rowsinset(0.19sec)

随着m的增大和n的增大，两种写法的SQL执行时间会有本质差别。我做了测试，当m值增加到880000时，优化前的SQL需要2分钟，优化后的SQL还是0.1s左右。