子查询和join的效率问题,MySQL的Limit有性能问题

问题:

假设：mysql数据库中一个表 5kcrm_crm_product里面有一千多万条数据，其右字段ID是自增长和主键，写一条sql语句，快速的查出第100万以后的100条数据；

首先确认会用到limit

传统limit

select * from 5kcrm_crm_product LIMIT 1000000,100; 

SELECT r.* FROM 5kcrm_crm_product rINNER JOIN( SELECT product_id FROM 5kcrm_crm_product LIMIT 1000000,100 ) p ON r.product_id = p.product_id

可以看到经过优化性能提高了将近几倍。优化原理：

子查询是在索引上完成的，而普通的查询时在数据文件上完成的，通常来说，索引文件要比数据文件小得多，所以操作起来也会更有效率。因为要取出所有字段内容，第一种需要跨越大量数据块并取出，而第二种基本通过直接根据索引字段定位后，才取出相应内容，效率自然大大提升。

因此，对limit的优化，不是直接使用limit，而是首先获取到offset的id，然后直接使用limit size来获取数据。

在实际项目使用，可以利用类似策略模式的方式去处理分页，例如，每页100条数据，判断如果是100页以内，就使用最基本的分页方式，大于100，则使用子查询的分页方式。