代码如下 | 复制代码 |
CREATE TABLE `message` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `user_id` int(11) NOT NULL, `content` text COLLATE utf8_unicode_ci NOT NULL, `create_time` int(11) NOT NULL, `thumbs_up` int(11) NOT NULL DEFAULT '0', /* 投票数 */ PRIMARY KEY (`id`), KEY `thumbs_up_key` (`thumbs_up`,`id`) ) ENGINE=InnoDB mysql> show table status like 'message' G Engine: InnoDB Version: 10 Row_format: Compact Rows: 50000040 /* 5千万 */ Avg_row_length: 565 Data_length: 28273803264 /* 26 GB */ Index_length: 789577728 /* 753 MB */ Data_free: 6291456 Create_time: 2009-04-20 13:30:45 |
代码如下 | 复制代码 |
SELECT count(*) FROM message |
代码如下 | 复制代码 |
SELECT * FROM message ORDER BY id DESC LIMIT 0, 20
ORDER BY id DESC LIMIT 0, 20
ORDER BY id DESC LIMIT 20, 20
ORDER BY id DESC LIMIT 40, 20
|
代码如下 | 复制代码 |
mysql> explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20G ***************** 1. row ************** id: 1 select_type: SIMPLE table: message type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 10020 Extra: 1 row in set (0.00 sec) |
WHERE id< 100 /* last_seen */ ORDER BY id DESC LIMIT $page_size /* 没有偏移 */
WHERE id > 98 /* last_seen */ ORDER BY id ASC LIMIT $page_size /* 没有偏移 */
代码如下 | 复制代码 |
mysql> explain SELECT * FROM message WHERE id < '49999961' ORDER BY id DESC LIMIT 20 G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: message type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL Rows: 25000020 /* 忽略这里 */ Extra: Using where 1 row in set (0.00 sec) |
代码如下 | 复制代码 |
99
99 98 第一页 98 98 98
98 97 第二页 97 10 |
代码如下 | 复制代码 |
WHERE thumbs_up< 98 ORDER BY thumbs_up DESC /* 结果将返回重复的记录 */ |
代码如下 | 复制代码 |
WHERE thumbs_up <= 98 AND <额外的条件> ORDER BY thumbs_up DESC |
代码如下 | 复制代码 |
SELECT thumbs_up, id FROM message ORDER BY thumbs_up DESC, id DESC LIMIT $page_size +-----------+----+ | thumbs_up | id | +-----------+----+ | 99 | 14 | | 99 | 2 | | 98 | 18 | | 98 | 15 | | 98 | 13 | +-----------+----+ |
代码如下 | 复制代码 |
SELECT thumbs_up, id FROM message WHERE thumbs_up <= 98 AND (id < 13 OR thumbs_up< 98) ORDER BY thumbs_up DESC, id DESC LIMIT $page_size +-----------+----+ | thumbs_up | id | +-----------+----+ | 98 | 10 | | 98 | 6 | | 97 | 17 | |
代码如下 | 复制代码 |
SELECT * FROM message WHERE thumbs_up <= 98 AND (id < 13 OR thumbs_up < 98) ORDER BY thumbs_up DESC, id DESC LIMIT 20 |
代码如下 | 复制代码 |
SELECT m2.* FROM message m1, message m2 WHERE m1.id = m2.id AND m1.thumbs_up <= 98 AND (m1.id <13 OR m1.thumbs_up< 98) ORDER BY m1.thumbs_up DESC, m1.id DESC LIMIT 20; |
代码如下 | 复制代码 |
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: m1 type: range possible_keys: PRIMARY,thumbs_up_key key: thumbs_up_key /* (thumbs_up,id) */ key_len: 4 ref: NULL Rows: 25000020 /* 忽略这里 */ Extra: Using where; Using index /* Cover 译:Cover就是说所需要的数据之从索引里获取就可以满足了 */ *************************** 2. row *************************** id: 1 select_type: SIMPLE table: m2 type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: forum.m1.id rows: 1 Extra: |