在大型数据库中优化SQL查询？

Question

查询

SELECT *
FROM user_ip_tmp
WHERE too = 'http://example.com/'
AND contry != 'CN'
AND contry != 'TW'
ORDER BY id DESC
LIMIT 50 

MySQL 返回：

Showing rows 0 - 29 ( 50 total, Query took 11.9276 sec) [id: 3452538 - 3448824]

如果我删除 ORDER BY id DESC。

Showing rows 0 - 29 ( 50 total, Query took 0.0033 sec)

解释计划： count：

SELECT count( * )
FROM user_ip_tmp

数据库使用的示例

CREATE TABLE IF NOT EXISTS `user_ip_tmp` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `ip` varchar(20) NOT NULL,
  `dataip` bigint(20) NOT NULL,
  `ref` text NOT NULL,
  `click` int(20) NOT NULL,
  `code` varchar(17) NOT NULL,
  `too` text NOT NULL,
  `checkopen` varchar(17) NOT NULL,
  `contry` text NOT NULL,
  `vOperation` text NOT NULL,
  `vBrowser` text NOT NULL,
  `iconOperation` text NOT NULL,
  `iconBrowser` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ip` (`dataip`),
  KEY `ip` (`checkopen`),
  KEY `ip` (`code`),
  KEY `ip` (`too`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5426268 ;

我希望得到正确的查询和优化数据库的方法，以便使用 ORDER BY id DESC 进行排序。

- 10neen com

Answer 1

了解您的数据分布情况将非常有趣。您可以在帖子中添加以下查询结果吗？（无需图片，纯文本即可）。

SELECT count(*) FROM user_ip_tmp WHERE too = 'http://example.com/' AND contry != 'CN' AND contry != 'TW'; 

SELECT count(*) FROM user_ip_tmp WHERE too = 'http://example.com/'; 

此外，您可以测试一下这个替代方案的性能吗？编辑：子查询的别名。

SELECT sub.* FROM
(SELECT *
FROM user_ip_tmp
WHERE too = 'http://example.com/'
AND contry != 'CN'
AND contry != 'TW'
) sub
ORDER BY sub.id DESC
LIMIT 50

编辑 如果可以添加和尝试索引，则可以尝试以下任一方法（或两者都尝试，看哪个更好）

CREATE INDEX index_name ON `user_ip_tmp` (`too`, `id`);
CREATE INDEX index_name ON `user_ip_tmp` (`too`, `contry`, `id`);

- bpgergo

---

SELECT count(*) FROM user_ip_tmp WHERE too = 'http://xxx.com/' AND contry != 'CN' AND contry != 'TW';42171SELECT count(*) FROM user_ip_tmp WHERE too = 'http://xxx.com/';42879` SELECT * FROM (SELECT * FROM user_ip_tmp WHERE too = 'http://xxx.com/' AND contry != 'CN' AND contry != 'TW' ) AS temp ORDER BY id DESC LIMIT 50无法工作，显示问题 ＃1248-每个派生表都必须有自己的别名 - 10neen com

是的，我忘了给在from子句中使用的子查询起别名。我编辑了我的答案并添加了它，谢谢。 - bpgergo

服务器在执行 CREATE INDEX index_name ON user_ip_tmp (too, id) 后停止工作了。我认为这是因为有超过 600 万条记录，数据库达到了 1 GB 的容量限制。 - 10neen com

@10neencom，如果“text”字段没有定义“长度”，那么在创建索引时可能会导致服务器崩溃。我建议对“too” URL的前200个字符进行索引，然后再对ID进行索引。 - DRapp

Answer 2

您可以使用以下代码创建索引：

 id, too and contry

- altmannmarcelo

---

我认为这不会解决问题 我认为问题出在其他地方 我不知道为什么使用ORDER BY id DESC会导致查询量增加 - 10neen com