問題描述

SELECT *FROM `order` WHERE user_id = 1ORDER BY `create_at` DESC LIMIT 12 OFFSET 15000

表中有20萬+數據。現在這條語句查詢需要20秒。 當LIMIT 12 OFFSET 12 或者 24,36等等前面的頁速度都還不錯，但是隨著頁數的增加，也就是OFFSET變化，越大越慢。

表id（主鍵，自增），user_id，create_at都增加了索引。

請問應該如何解決這個問題。

這里的where 條件只列了一個， 實際上可能還有更多的可能性。如果有更多的where又該如何？

orderby 目前肯定是針對已經有索引頁的字段進行排序的，但是也有3個，時間字段。

謝謝。

//ddlCREATE TABLE `foobar` ( `id` int(11) NOT NULL AUTO_INCREMENT, `identdify` bigint(20) DEFAULT ’0’, `type` int(11) DEFAULT ’0’, `status` int(11) DEFAULT ’0’, `way` int(11) DEFAULT ’0’, `api` int(11) DEFAULT ’0’, `node` int(11) DEFAULT ’0’, `apply_by` int(11) DEFAULT ’0’, `apply_at` int(11) DEFAULT ’0’, `create_by` int(11) DEFAULT ’0’, `create_at` int(11) DEFAULT ’0’, `confirm_by` int(11) DEFAULT ’0’, `confirm_at` int(11) DEFAULT ’0’, `check_by` int(11) DEFAULT ’0’, `check_at` int(11) DEFAULT ’0’, `money_1` decimal(20,2) DEFAULT ’0.00’, `money_2` decimal(20,2) DEFAULT ’0.00’, `money_3` decimal(20,2) DEFAULT ’0.00’, `money_4` decimal(20,2) DEFAULT ’0.00’, `money_5` decimal(20,2) DEFAULT ’0.00’, `money_6` decimal(20,2) DEFAULT ’0.00’, `money_7` decimal(20,2) DEFAULT ’0.00’, `money_8` decimal(20,2) DEFAULT ’0.00’, `api_identify` varchar(255) DEFAULT NULL, `client_name` varchar(255) DEFAULT NULL, `remark` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `type` (`type`), KEY `status` (`status`), KEY `way` (`way`), KEY `node` (`node`), KEY `apply_by` (`apply_by`), KEY `apply_at` (`apply_at`), KEY `create_by` (`create_by`), KEY `create_at` (`create_at`), KEY `confirm_by` (`confirm_by`), KEY `create_at_2` (`create_at`)) ENGINE=MyISAM AUTO_INCREMENT=251720 DEFAULT CHARSET=utf8

SELECT count(*) FROM `foobar`;+----------+| count(*) |+----------+| 251719 |+----------+1 row in set (0.00 sec)

SELECT * FROM `foobar` WHERE way = 1 ORDER BY create_at DESC LIMIT 12 OFFSET 20000 // 耗時 25.890 秒EXPLAIN -> SELECT -> * -> FROM -> `foobar` -> WHERE way = 1 -> ORDER BY create_at DESC -> LIMIT 12 OFFSET 20000 G;*************************** 1. row *************************** id: 1 select_type: SIMPLEtable: foobar type: rangepossible_keys: way key: way key_len: 5 ref: NULL rows: 251028Extra: Using index condition; Using filesort1 row in set (0.00 sec)

SELECT * FROM `foobar` WHERE way = 1 AND api = 1 ORDER BY create_at DESC LIMIT 12 OFFSET 20000 //耗時 24.585> EXPLAIN -> SELECT -> * -> FROM -> `foobar` -> WHERE way = 1 -> AND api = 1 -> ORDER BY create_at DESC -> LIMIT 12 OFFSET 20000 G;*************************** 1. row *************************** id: 1 select_type: SIMPLEtable: foobar type: rangepossible_keys: way key: way key_len: 5 ref: NULL rows: 251028Extra: Using index condition; Using where; Using filesort1 row in set (0.00 sec)ERROR: No query specified

問題解答

表結構貼出來，explain 貼出來

看樣子樓主表的 way字段幾乎都是 1 ，api字段幾乎都是 1。。。。。

可以試著加兩個聯合索引

ix_way_create_at (way,create_at)

ix_way_api_create_at (way, api, create_at)

你確定不是內存不夠之類的硬件問題？ 我復現了你的查詢只要0.02秒100w條數據。你可以嘗試 order by id 試試 因為你的create_at其實也是按照這個時間來算的。嘗試之后還有問題發圖發更詳細的數據來分析。