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

问题描述

问 题

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

解决方案

表结构贴出来，explain 贴出来

看样子楼主表的 way字段几乎都是 1 ，api字段几乎都是 1。。。。。

可以试着加两个联合索引

ix_way_create_at (way,create_at)

ix_way_api_create_at (way, api, create_at)

这篇关于mysql有索引，查询依然非常慢，请问怎么优化？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！