mysql 查询一条SQL耗时 1000 多秒，一直sending data

[用户昵称不能为空]

MariaDB [db2_aaaa]> show processlist;
+--------+---------+-----------------+----------+---------+------+----------------+------------------------------------------------------------------------------------------------------+---------+
| Id     | User    | Host            | db       | Command | Time | State          | Info                                                                                                 | Progress |
+--------+---------+-----------------+----------+---------+------+----------------+------------------------------------------------------------------------------------------------------+----------+
| 203223 | f2_aaaa | localhost:57722 | db2_aaaa | Query   |  905 | Sending data   | SELECT `tid`, `thumb`, `uid`, `d2`.`name` AS `d2name`, `c2`.`name` AS `c2name`, `uname`, `title`, `d |    0.000 |
| 203805 | f2_aaaa | localhost:60146 | db2_aaaa | Query   |  574 | Sending data   | SELECT `tid`, `thumb`, `uid`, `d2`.`name` AS `d2name`, `c2`.`name` AS `c2name`, `uname`, `title`, `d |    0.000 |
| 203986 | f2_aaaa | localhost:60934 | db2_aaaa | Query   |  444 | Sending data   | SELECT `tid`, `thumb`, `uid`, `d2`.`name` AS `d2name`, `c2`.`name` AS `c2name`, `uname`, `title`, `d |    0.000 |
| 204126 | root    | localhost       | db2_aaaa | Query   |    0 | NULL           | show processlist                                                                                     |    0.000 |
| 204524 | f2_aaaa | localhost:34878 | db2_aaaa | Query   |   11 | Sorting result | SELECT `tid`, `thumb`, `uid`, `d2`.`name` AS `d2name`, `c2`.`name` AS `c2name`, `uname`, `title`, `d |    0.000 |
| 204546 | f6_bbbb | localhost:34966 | db6_bbbb | Query   |    0 | query end      | UPDATE `zz_daylog_spider` SET `hit`=`hit`+1 WHERE `id`=2838                                         |    0.000 |
+--------+---------+-----------------+----------+---------+------+----------------+------------------------------------------------------------------------------------------------------+----------+
6 rows in set (0.00 sec)



MariaDB [db2_aaaa]> show create table zz_info\G
*************************** 1. row ***************************
       Table: zz_info
Create Table: CREATE TABLE `zz_info` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL COMMENT '信息标题',
  `uid` int(11) NOT NULL DEFAULT '0' COMMENT '发布者UID',
  `uname` varchar(60) DEFAULT NULL COMMENT '发布者登录名',
  `thumb` varchar(255) DEFAULT NULL COMMENT '图片地址',
  `c1` int(11) NOT NULL DEFAULT '0' COMMENT '类目1',
  `c2` int(11) NOT NULL DEFAULT '0' COMMENT '类目2',
  `c3` int(11) NOT NULL DEFAULT '0' COMMENT '类目3',
  `d1` int(11) NOT NULL DEFAULT '0' COMMENT '地区1',
  `d2` int(11) NOT NULL DEFAULT '0' COMMENT '地区2',
  `d3` int(11) NOT NULL DEFAULT '0' COMMENT '地区3',
  `content` text NOT NULL COMMENT '帖子内容',
  `hit` int(11) NOT NULL DEFAULT '1' COMMENT '访问次数',
  `addtime` int(11) NOT NULL COMMENT '发布时间',
  `uptime` int(11) NOT NULL COMMENT '更新时间',
  `status` smallint(6) NOT NULL DEFAULT '1' COMMENT '信息状态，默认1显示中',
  `ip` varchar(16) NOT NULL COMMENT 'IP地址',
  `iploc` varchar(255) NOT NULL COMMENT 'IP归属地',
  PRIMARY KEY (`tid`),
  KEY `idx-info-uid` (`uid`),
  KEY `idx-info-uname` (`uname`),
  KEY `idx-info-status` (`status`),
  KEY `uptime` (`uptime`),
  KEY `idx-info-status-c2` (`status`,`c2`),
  KEY `idx-info-status-d1` (`status`,`d1`),
  KEY `idx-info-status-c1` (`status`,`c1`),
  KEY `idx-info-status-d2` (`status`,`d2`),
  KEY `idx-info-status-c2-d2` (`status`,`c2`,`d2`),
  KEY `idx-info-status-d3` (`status`,`d3`),
  KEY `idx-info-status-c3` (`status`,`c3`)
) ENGINE=InnoDB AUTO_INCREMENT=1664350 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (tid)
PARTITIONS 10 */
1 row in set (0.00 sec)

查询的SQL语句如下：

SELECT `tid`, `thumb`, `uid`, `d2`.`name` AS `d2name`, `c2`.`name` AS `c2name`, 
	`uname`, `title`, `d2`, `c2`, `addtime`, `uptime`,  LEFT(content,220) abstract 
FROM `zz_info` 
LEFT JOIN (SELECT `id`, `name` FROM `zz_area` WHERE `level`=2) `d2` ON  d2.id=d2 
LEFT JOIN (SELECT `id`, `name` FROM `zz_info_cat` WHERE `level`=2) `c2` ON  c2.id=c2  
WHERE (`d1`='9') AND (`status` IN (0, 1, -2)) 
ORDER BY `uptime` DESC
LIMIT 10 OFFSET 1250;

explain：

+------+-------------+--------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------+----------------------+-------+-----------------------------+
| id   | select_type | table        | type   | possible_keys                                                                                                                                           | key                | key_len | ref                  | rows  | Extra                       |
+------+-------------+--------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------+----------------------+-------+-----------------------------+
|    1 | SIMPLE      | zz_info     | range  | idx-info-status,idx-info-status-c2,idx-info-status-d1,idx-info-status-c1,idx-info-status-d2,idx-info-status-c2-d2,idx-info-status-d3,idx-info-status-c3 | idx-info-status-d1 | 6       | NULL                 | 18913 | Using where; Using filesort |
|    1 | SIMPLE      | zz_area     | eq_ref | PRIMARY,idx-area-level                                                                                                                                  | PRIMARY            | 4       | db2_dalu.zz_info.d2 |     1 | Using where                 |
|    1 | SIMPLE      | zz_info_cat | eq_ref | PRIMARY,idx-info-cat-level                                                                                                                              | PRIMARY            | 4       | db2_dalu.zz_info.c2 |     1 | Using where                 |
+------+-------------+--------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------+----------------------+-------+-----------------------------+
3 rows in set (0.00 sec)



*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: zz_info
         type: range
possible_keys: idx-info-status,idx-info-status-c2,idx-info-status-d1,idx-info-status-c1,idx-info-status-d2,idx-info-status-c2-d2,idx-info-status-d3,idx-info-status-c3
          key: idx-info-status-d1
      key_len: 6
          ref: NULL
         rows: 18913
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: zz_area
         type: eq_ref
possible_keys: PRIMARY,idx-area-level
          key: PRIMARY
      key_len: 4
          ref: db2_dalu.zz_info.d2
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: zz_info_cat
         type: eq_ref
possible_keys: PRIMARY,idx-info-cat-level
          key: PRIMARY
      key_len: 4
          ref: db2_dalu.zz_info.c2
         rows: 1
        Extra: Using where
3 rows in set (0.04 sec)

单表数据20w的时候还没出现过，现在数据量50w的时候，查询就一直卡死，查不出来。是建表的问题吗？

[LongRui888]

语句改成这样： SELECT `tid`, `thumb`, `uid`, `d2`.`name` AS `d2name`, `c2`.`name` AS `c2name`, `uname`, `title`, `d2`, `c2`, `addtime`, `uptime`, LEFT(content,220) abstract FROM `zz_info` LEFT JOIN `zz_area` `d2` ON d2.id=zz_info.d2 and d2.`level`=2 LEFT JOIN `zz_info_cat` c2 on ON c2.id=zz_info.c2 and c2.`level`=2 WHERE (`d1`='9') AND (`status` IN (0, 1, -2)) ORDER BY `uptime` DESC LIMIT 10 OFFSET 1250; 然后试试创建这个索引： d1，status，uptime desc

[用户昵称不能为空]

引用 1 楼 yupeigu 的回复:

语句改成这样： SELECT `tid`, `thumb`, `uid`, `d2`.`name` AS `d2name`, `c2`.`name` AS `c2name`, `uname`, `title`, `d2`, `c2`, `addtime`, `uptime`, LEFT(content,220) abstract FROM `zz_info` LEFT JOIN `zz_area` `d2` ON d2.id=zz_info.d2 and d2.`level`=2 LEFT JOIN `zz_info_cat` c2 on ON c2.id=zz_info.c2 and c2.`level`=2 WHERE (`d1`='9') AND (`status` IN (0, 1, -2)) ORDER BY `uptime` DESC LIMIT 10 OFFSET 1250; 然后试试创建这个索引： d1，status，uptime desc

现在这个表已经创建了数十个索引了，status、d1-status、d2-status、d3-status、c1-status、c2-status、c3-status、uptime …… 还有排序规则是 按照主键进行排序 order by 主键 desc，如果是这样是否创建索引，那还需要再加一个 主键-status 索引了。 每次更新 status 都特别卡，十多秒。这样应该不合理吧，困惑

[zjcxc]

从条件看， d1=， status 是 in 多个值，所以索引的顺序应该是 d1, status， 最后加个排序，也就是1楼建议的才是最合理的

[zjcxc]

其他的索引可以考虑删除一些，比如有 status 打头的索引，通常单独的 status 列索引可以删除，因为只查 status 的话， 以 status 打并没有的索引也可以适用这种情况，只是效率略差一点而已，索引少的时候两个无所谓，多时应该考虑删除一个

[用户昵称不能为空]

引用 4 楼 zjcxc--个人微信公共号同名 的回复:

其他的索引可以考虑删除一些，比如有 status 打头的索引，通常单独的 status 列索引可以删除，因为只查 status 的话， 以 status 打并没有的索引也可以适用这种情况，只是效率略差一点而已，索引少的时候两个无所谓，多时应该考虑删除一个

是服务器问题，配置太差。性能差