問題描述

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

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

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

請(qǐng)問應(yīng)該如何解決這個(gè)問題。

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

orderby 目前肯定是針對(duì)已經(jīng)有索引頁的字段進(jìn)行排序的，但是也有3個(gè)，時(shí)間字段。

謝謝。

//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 // 耗時(shí) 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 //耗時(shí) 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

問題解答

表結(jié)構(gòu)貼出來，explain 貼出來

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

可以試著加兩個(gè)聯(lián)合索引

ix_way_create_at (way,create_at)

ix_way_api_create_at (way, api, create_at)

你確定不是內(nèi)存不夠之類的硬件問題？ 我復(fù)現(xiàn)了你的查詢只要0.02秒100w條數(shù)據(jù)。你可以嘗試 order by id 試試 因?yàn)槟愕腸reate_at其實(shí)也是按照這個(gè)時(shí)間來算的。嘗試之后還有問題發(fā)圖發(fā)更詳細(xì)的數(shù)據(jù)來分析。