本文共 1812 字,大约阅读时间需要 6 分钟。
mysql的查询,最好是能用上索引 被称为覆盖索引mysql> explain select item_id from sales_order_item order by sku limit 10000,2 -> ;+----+-------------+------------------+-------+---------------+------+---------+------+-------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------------+-------+---------------+------+---------+------+-------+-------------+| 1 | SIMPLE | sales_order_item | index | NULL | sku | 93 | NULL | 10002 | Using index |+----+-------------+------------------+-------+---------------+------+---------+------+-------+-------------+
Extra 显示 Using index 就标示覆盖索引,
遇到负责的条件排序和查询,然后分页,
可以通过覆盖索引,查出来分页的id,然后通过id去inner join
例如:
select * from sales_order_item inner join ( select item_id from sales_order_item order by sku limit 10000,2 ) as x using(item_id);
+----+-------------+------------------+--------+---------------+---------+---------+-----------+--------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------------+--------+---------------+---------+---------+-----------+--------+-------------+| 1 | PRIMARY |子查询使用的是覆盖索引,查询出来需要的id,然后通过id去匹配对应的行,就会加快速度| ALL | NULL | NULL | NULL | NULL | 10002 | NULL || 1 | PRIMARY | sales_order_item | eq_ref | PRIMARY | PRIMARY | 4 | x.item_id | 1 | NULL || 2 | DERIVED | sales_order_item | index | NULL | sku | 93 | NULL | 116180 | Using index |+----+-------------+------------------+--------+---------------+---------+---------+-----------+--------+-------------+
为什么这样快,因为不是覆盖索引,会加载数据部分,而不是只在索引表进行检索,速度会变慢。
转载地址:http://bxdli.baihongyu.com/