mysql有索引，查询依然非常慢，请问怎么优化？

问题描述

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其实也是按照这个时间来算的。尝试之后还有问题发图发更详细的数据来分析。