explainselectidfrom t_order where create_time between'2019-10-17'and'2019-10-25' limit1000000, 10;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+-----------------+-----------------+---------+------+---------+--------------------------+ | 1 | SIMPLE | t_order | range | idx_create_time | idx_create_time | 5 | NULL | 3775048 | Using where; Using index | +----+-------------+---------+-------+-----------------+-----------------+---------+------+---------+--------------------------+
上述查询字段改为 id 后,执行计划中新增: Extra=Using index,表示走覆盖索引,无需回表,查询速度快了 N 倍。
延迟关联优化
延迟关联优化涉及到了 SQL 优化的两个重要概念:覆盖索引和回表。
通过覆盖索引在辅助索引上完成所有扫描、过滤、排序(利用索引有序)和分页;
最后通过主键回表查询,最大限度减少回表查询的 I/O 次数。
SQL 改造如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
explainselect * from t_order t innerjoin ( selectidfrom t_order where create_time between'2019-10-17'and'2019-10-25' limit1000000, 10 ) e on t.id = e.id;
+----+-------------+---------------+--------+-----------------+-----------------+---------+------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+--------+-----------------+-----------------+---------+------+---------+--------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1000010 | NULL | | 1 | PRIMARY | t | eq_ref | PRIMARY | PRIMARY | 8 | e.id | 1 | NULL | | 2 | DERIVED | t_order | range | idx_create_time | idx_create_time | 5 | NULL | 3775048 | Using where; Using index | +----+-------------+---------------+--------+-----------------+-----------------+---------+------+---------+--------------------------+