mysql分頁的limit最佳化

標籤：des   os   使用   ar   strong   資料   sp   on   c   

1、很多新人都會很納悶，為什麼我建了索引使用分頁還是這麼卡。好，現在讓我們一步一步去找原因吧。

首先limit本身跟索引沒有直接關係。

先建一張商品sku表

create table goods_sku
(
id int(10) unsigned not null auto_increment comment ‘自增ID‘,
goods_id varchar(20) not null comment ‘商品id‘,
sale_status tinyint comment ‘上下架狀態（0下架，1上架）‘,
added_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘上家日期‘,
drop_time timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00‘ COMMENT ‘下架時間‘,
`is_del` tinyint(4) NOT NULL DEFAULT ‘0‘ COMMENT ‘刪除標記(0未刪除 1刪除)‘,
KEY `index_goods_id` (`goods_id`),
KEY `index_sale_status` (`sale_status`),
KEY `index_added_time` (`added_time`),
primary key (id)
) comment = ‘商品SKU表‘ ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> explain select * from goods_sku limit 0,10;
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | goods_sku | ALL | NULL | NULL | NULL | NULL | 107950 | |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)

ps: 因為沒走索引，所以進行了全表掃描，現在是10萬條資料，試想一下100萬的情況下是怎麼樣。這麼簡單的一條sql就會讓你機器卡爆。我現在就想一條資料，使用索引看看

mysql> explain select * from goods_sku where sale_status=1 limit 0,10;
+----+-------------+-----------+------+-------------------+-------------------+---------+-------+---
---+-------------+
| id | select_type | table     | type | possible_keys     | key               | key_len | ref   | ro
ws | Extra       |
+----+-------------+-----------+------+-------------------+-------------------+---------+-------+---
---+-------------+
|  1 | SIMPLE      | goods_sku | ref  | index_sale_status | index_sale_status | 2       | const | 46
25 | Using where |
+----+-------------+-----------+------+-------------------+-------------------+---------+-------+---
---+-------------+
1 row in set (0.10 sec)

雖然走了索引，但是受影響的條數還是4000多條

mysql> explain select * from goods_sku order by id desc limit 0,10;
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | goods_sku | index | NULL          | PRIMARY | 8       | NULL |   10 |       |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

這個受影響的條件為10條，看來limit和order by 聯用可以真正限制輸出的數量，但是order by 後面的欄位一定是建了索引的

mysql分頁的limit最佳化