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

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

表中有20万+数据。现在这条语句查询需要20秒。 当LIMIT 12 OFFSET 12 或者 24,36等等前面的页速度都还不错,但是随着页数的增加,也就是OFFSET变化,越大越慢。

表id(主键,自增),user_id,create_at都增加了索引。

  1. 请问应该如何解决这个问题。

  2. 这里的where 条件只列了一个, 实际上可能还有更多的可能性。如果有更多的where又该如何?

  3. orderby 目前肯定是针对已经有索引页的字段进行排序的,但是也有3个,时间字段。

谢谢。

补充

//ddl
CREATE 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: SIMPLE
        table: foobar
         type: range
possible_keys: way
          key: way
      key_len: 5
          ref: NULL
         rows: 251028
        Extra: Using index condition; Using filesort
1 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: SIMPLE
        table: foobar
         type: range
possible_keys: way
          key: way
      key_len: 5
          ref: NULL
         rows: 251028
        Extra: Using index condition; Using where; Using filesort
1 row in set (0.00 sec)

ERROR: No query specified
回复
阅读 4.3k
2 个回答

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

宣传栏