相同的数据库结构和索引，但不同的查询速度和解释结果 [英] same database structure and index, but different query speed and explain results

问题描述

我在 2 个独立的云服务器上有 2 个 MySQL 数据库.一个是我的同事设置的，我用相同的结构复制了那个.

I have 2 MySQL databases on 2 separate cloud servers. One is setup by my colleague and I duplicated that one with same structures.

几天前我发现对我的数据库的查询很慢，但在他的数据库中很快.然后他告诉我他为一个表添加了 2 个索引，他为我的数据库添加了相同的 2 个索引，但它不起作用，他不知道为什么.
添加相同索引后explain结果不同.而且我的查询还是很慢.
(0.36s vs >3mins)

Days ago I found a query on my database is slow but fast in his database. Then he told me he added 2 indexes for a table and he added the same 2 indexes for my database but it just didn't work and he didn't know why.
The explain result is different after adding the same indexes. And my query is still slow.
(0.36s vs > 3mins)

你能告诉我出了什么问题并帮助我理解 explain 结果和差异吗?

Could you tell me what's wrong and help me understand the explain result and the differences ?

查询:

select sum(A.money) from 
stat_sword.t_pay_history_real as A, 
inner join
(select uid, reg_srv_id as srv_id 
     from sword_common.t_ids
     where reg_srv_id < 100000
     group by uid having count(uid) > 1) as B 
on A.uid = B.uid and A.srv_id = B.srv_id

表结构:

# Same t_pay_history_real for both databases

mysql> show create table stat_sword.t_pay_history_real \G;
*************************** 1. row ***************************
       Table: t_pay_history_real
Create Table: CREATE TABLE `t_pay_history_real` (
  `time` int(11) NOT NULL,
  `srv_id` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `money` int(11) NOT NULL,
  `item_id` int(11) DEFAULT '0',
  `count` int(11) DEFAULT '0',
  `ingot` int(11) DEFAULT '0',
  `rid` int(11) DEFAULT '0',
  `span_id` int(11) DEFAULT '0',
  `orderid` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`time`,`srv_id`,`uid`,`orderid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

# My sword_common.t_ids
 

mysql>show create table Sword_common.t_ids \G;

mysql> show create table sword_common.t_ids \G;

*************************** 1. row ***************************
       Table: t_ids
Create Table: CREATE TABLE `t_ids` (
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `reg_srv_id` int(11) NOT NULL DEFAULT '0',
  `rid` int(10) unsigned NOT NULL DEFAULT '0',
  `span_id` int(11) DEFAULT '0',
  `cur_srv_id` int(11) DEFAULT '0',
  `reg_time` int(10) unsigned DEFAULT '0',
  `com_time` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`uid`,`reg_srv_id`),
  KEY `idx_rid` (`rid`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

他的剑_common.t_ids

His sword_common.t_ids

mysql> show create table sword_common.t_ids \G;
*************************** 1. row ***************************
       Table: t_ids
Create Table: CREATE TABLE `t_ids` (
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `reg_srv_id` int(11) NOT NULL DEFAULT '0',
  `rid` int(10) unsigned NOT NULL DEFAULT '0',
  `span_id` int(11) DEFAULT '0',
  `cur_srv_id` int(11) DEFAULT '0',
  `reg_time` int(10) unsigned DEFAULT '0',
  `com_time` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`uid`,`reg_srv_id`),
  KEY `inx_rid` (`rid`),
  KEY `inx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

解释上述查询的结果:

在我的数据库上:

+----+-------------+------------+-------+---------------+---------+---------+------+--------+--------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows   | Extra                          |
+----+-------------+------------+-------+---------------+---------+---------+------+--------+--------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |  26778 |                                |
|  1 | PRIMARY     | A          | ALL   | NULL          | NULL    | NULL    | NULL | 162010 | Using where; Using join buffer |
|  2 | DERIVED     | t_ids      | index | NULL          | idx_uid | 4       | NULL | 335827 | Using where; Using index       |
+----+-------------+------------+-------+---------------+---------+---------+------+--------+--------------------------------+

在他的数据库上:

+----+-------------+------------+-------+-------------------------+-------------+---------+--------------------------------------+--------+--------------------------+
| id | select_type | table      | type  | possible_keys           | key         | key_len | ref                                  | rows   | Extra                    |
+----+-------------+------------+-------+-------------------------+-------------+---------+--------------------------------------+--------+--------------------------+
|  1 | PRIMARY     | A          | ALL   | NULL                    | NULL        | NULL    | NULL                                 | 315144 | NULL                     |
|  1 | PRIMARY     | <derived2> | ref   | <auto_key0>             | <auto_key0> | 8       | stat_sword.A.uid,stat_sword.A.srv_id |     10 | Using where; Using index |
|  2 | DERIVED     | t_ids      | index | PRIMARY,inx_rid,inx_uid | inx_uid     | 4       | NULL                                 | 740388 | Using where; Using index |
+----+-------------+------------+-------+-------------------------+-------------+---------+--------------------------------------+--------+--------------------------+

每个表的行数:https://dpaste.com/AZ5KU4JCV

上述查询在他的数据库中占用 0.36s 但在我的数据库中超过 3 mins..

The above query takes 0.36s in his db but more than 3 mins in mine..

为什么 explain 结果和顺序不同，它说的是什么?我刚刚学习了一些关于索引的基础知识，对 explain 结果完全不熟悉.

Why the explain result and order are different and what does it say? I just learnt some basic about index and am not familiar with explain result at all.

此外，如果我通过添加相同的索引成功加快了查询速度，但我失败了，我计划尝试使用 (rid, uid) 的复合索引. (我读错了索引，这完全没用.)

Also, I was planning to try a composite index with (rid, uid) if I succeeded speeding up the query by adding the same indexes but I failed. (I read the indexes wrong and this is totally useless.)

更新:

好的.MySQL 版本是不同的，我的是 Server version: 5.1.73 Source distribution 而他的是 Server version: 5.6.48 MySQL Community Server (GPL)..我会转储从我的数据库中获取数据并暂时尝试他的 MySQL...

Ok. The MySQL verion is different, mine is Server version: 5.1.73 Source distribution and his is Server version: 5.6.48 MySQL Community Server (GPL).. I would dump the data from my DB and try on his MySQL for now...

推荐答案

t_pay_history_real 需要 INDEX(uid, srv_id)

t_ids 可能会得到 INDEX(reg_srv_id, uid)

请注意，您正在运行不同的版本: 表示较新的版本.

Note that you are running different versions: <auto_key0> indicates a newer version.

从 ENGINE=MyISAM 切换到 ENGINE=InnoDB 应该有很多好处.

Switching from ENGINE=MyISAM to ENGINE=InnoDB should be beneficial in many ways.

如果你添加了 INDEX(rid, uid)，删除 INDEX(rid) 是没有用的.但是我认为这个查询不需要它.

If you add INDEX(rid, uid), remove INDEX(rid) as being useless. However I don't see any need for it for this query.

这篇关于相同的数据库结构和索引，但不同的查询速度和解释结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！