问题描述
我在 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)
(我读错了索引，这完全没用.)
(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.
这篇关于相同的数据库结构和索引，但不同的查询速度和解释结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！