如何在大型数据库中优化此查询？

Question

如何在大型数据库中优化此查询？

4

查询

SELECT id FROM `user_tmp` 
WHERE  `code` = '9s5xs1sy' 
  AND  `go` NOT REGEXP 'http://www.xxxx.example.com/aflam/|http://xx.example.com|http://www.xxxxx..example.com/aflam/|http://www.xxxxxx.example.com/v/|http://www.xxxxxx.example.com/vb/'  
  AND check='done'  
  AND  `dataip` <1319992460
ORDER BY id DESC 
LIMIT 50

MySQL 返回：

Showing rows 0 - 29 ( 50 total, Query took 21.3102 sec) [id: 2622270 - 2602288]

查询用时 21.3102 秒

如果我移除

AND dataip <1319992460

MySQL 将返回

Showing rows 0 - 29 ( 50 total, Query took 0.0859 sec) [id: 3637556 - 3627005]

查询用时0.0859秒

如果没有数据，MySQL将返回

MySQL returned an empty result set (i.e. zero rows). ( Query took 21.7332 sec )

查询用时21.7332秒

解释计划：

  SQL query: Explain SELECT * FROM `user_tmp` WHERE `code` = '93mhco3s5y' AND `too` NOT REGEXP 'http://www.10neen.com/aflam/|http://3ltool.com|http://www.10neen.com/aflam/|http://www.10neen.com/v/|http://www.m1-w3d.com/vb/' and checkopen='2010' and `dataip` <1319992460 ORDER BY id DESC LIMIT 50;
    Rows: 1
    id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
    1   SIMPLE  user_tmp    index   NULL    PRIMARY     4   NULL    50  Using where

数据库使用示例

创建表 user_tmp，如果不存在，其中包含以下字段：id (自增长，int类型)，ip (文本类型，不允许为空)，dataip (长整型，不允许为空)，ref (文本类型，不允许为空)，click (int类型，不允许为空)，code (文本类型，不允许为空)，too (文本类型，不允许为空)，name (文本类型，不允许为空)，checkopen (文本类型，不允许为空)，contry (文本类型，不允许为空)，vOperation (文本类型，不允许为空)，vBrowser (文本类型，不允许为空)，iconOperation (文本类型，不允许为空) 和 iconBrowser (文本类型，不允许为空)。

  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4653425 ;

--

-- 转储表 `user_tmp` 的数据

INSERT INTO `user_tmp` (`id`, `ip`, `dataip`, `ref`, `click`, `code`, `too`, `name`, `checkopen`, `contry`, `vOperation`, `vBrowser`, `iconOperation`, `iconBrowser`) VALUES
(1, '54.125.78.84', 1319506641, 'http://xxxx.example.com/vb/showthread.php%D8%AA%D8%AD%D9%85%D9%8A%D9%84-%D8%A7%D8%BA%D9%86%D9%8A%D8%A9-%D8%A7%D9%84%D8%A8%D9%88%D9%85-giovanni-marradi-lovers-rendezvous-3cd-1999-a-155712.html', 0, '4mxxxxx5', 'http://www.xxx.example.com/aflam/', 'xxxxe', '2010', 'US', 'Linux', 'Chrome 12.0.742 ', 'linux.png', 'chrome.png');

我需要正确的查询和优化数据库的方法。

- 10neen com

1

完整问题及所有相关数据+1。 - Johan

如果您可以将某些字段（例如ip和code）设置为char或varchar类型，那么在这些字段上创建索引（特别是对于所示查询的code字段）可能会有所帮助。当然，我只是猜测基数，所以可能并没有太大帮助。 - Mark Wilkins

为什么checkopen是文本？这个表结构似乎缺少了某些东西。在第一个查询中，有check='done'，而在表结构中没有“check”列，但有包含数字值（我猜是年份）的checkopen列。 - Ivan

"select count(*) from user_tmp" 返回什么？ - mik

显示行 0 - 29 (共 4,653,050 行，查询耗时 0.0002 秒) - 10neen com

5个回答

1

除了主键外，您没有任何索引。您需要在WHERE语句中使用的字段上创建索引。如果您只需要为1个字段或多个字段组合创建索引，则取决于您将针对该表运行的其他SELECT。

请记住，REGEXP根本无法使用索引，LIKE仅在不以通配符开头时才能使用索引（因此LIKE'a%' 可以使用索引，但LIKE'% a'不能），大于/小于（<>）通常也不使用索引。

因此，您只剩下code和check字段。我猜许多行将具有相同的check值，因此我会从code字段开始索引。多字段索引只能按定义顺序使用...

想象一下为code，check字段创建的索引。此索引可用于查询（其中WHERE子句包含两个字段），也可用于仅具有code字段的查询，但无法用于仅具有check字段的查询。

使用 ORDER BY id 是否重要？如果不重要，可以省略它，这样可以避免排序操作，从而加快查询速度。

- Marki555

0

你需要像这样添加索引：

ALTER TABLE  `user_tmp` ADD INDEX(`dataip`);

如果您的“dataip”列仅包含唯一值，您可以像这样添加唯一键：

ALTER TABLE  `user_tmp` ADD UNIQUE(`dataip`);

请记住，在大表上添加索引可能需要很长时间，因此在生产服务器上进行操作之前，请务必进行测试。

- mik

修改数据表user_tmp，添加唯一性约束(dataip)；#1062 - 键值'dataip'重复，插入失败。 - 10neen com

好的，你的值不是唯一的；尝试第一个查询 - 它将创建非唯一索引。如果有帮助，请告诉我。 - mik

我尝试了但没有任何结果。code ALTER TABLE user_tmp ADD INDEX(dataip); code 查询结果非常糟糕。 - 10neen com

如果您使用的查询总是具有“code”、“check”和“dataip”的where条件，则将索引扩展以包括所有三个。 - W Devauld

0

您需要按照 where 语句中的相同顺序在字段上创建索引，否则索引将不会被使用。请对 where 子句中的字段进行索引。

- Md Jawed Shamshedi

WHERE子句的顺序并不重要-它只是简单的逻辑计算（a AND b与b AND a相同）。使索引的第一部分成为具有更好基数且可能单独使用的字段。 - Marki555

0

数据ip真的需要是bigint吗？根据MySQL，有符号范围是-9223372036854775808至9223372036854775807（这是一个64位数）。

您需要选择适合工作的正确列类型，并添加正确类型的索引。否则，这些查询将永远无法完成。

- IanNorton

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Boyd Hemphill · Accepted Answer

我假设您正在使用mysql <= 5.1

以上答案可以分为两类： 1. 您正在使用错误的列类型 2. 您需要索引

我将处理每个问题，因为两者都与性能有关，这最终是您提出问题的原因：

列类型对于数据ip问题，bigint/int或int/char之间的区别基本上与您的问题无关。根本问题更多地涉及索引策略。但是，从整体性能考虑，您使用MyISAM作为此表的引擎的事实使我想问您是否真的需要“text”列类型。如果您有短（小于255个字符）的字符列，则将它们变成固定长度列很可能会提高性能。请记住，如果任何一个列是可变长度（varchar、text等），则不值得更改其中任何一个。
垂直分区要记住的事实是，即使您只从磁盘IO和内存的角度请求id列，您也会得到整行数据。由于许多行都是文本，这可能意味着大量的数据。任何不用于查找用户或不经常访问的这些行都可以移动到另一个表中，该表的外键上放置了唯一键，保持1:1的关系。
索引策略最有可能的问题就是索引，如上所述。当前情况之所以会因添加“AND dataip <1319992460”条件而导致问题，是因为它强制进行全表扫描。

如上所述，将所有列放入where子句中的单个复合索引可以帮助优化。无论索引中列的顺序如何，只要它们都出现在where子句中即可。

然而，对于其他查询，顺序可能非常重要。例如，由(colA, colB)组成的索引。具有"where colA ='foo'"条件的查询将使用此索引。但是，具有"where colB = 'bar'"条件的查询将不会使用此索引，因为colB不是索引定义中最左侧的列。因此，如果您有其他查询在某些组合中使用这些列，则值得尽量减少表上创建的索引数量。这是因为每个索引都会增加写入的成本并使用磁盘空间。写入操作昂贵是因为需要进行必要的磁盘活动。不要使它们更昂贵。

如何在大型数据库中优化此查询？

-- 转储表 user_tmp 的数据

-- 转储表 `user_tmp` 的数据