MySQL索引问题

Question

我创建了下表:

 CREATE TABLE citycodes
    (
     city VARCHAR(30),
     code VARCHAR(10),
     INDEX idx_code (code),
     INDEX idx_city (city)
    ) ENGINE=MyISAM;

我知道由于缺少索引，此查询的性能会很差。

SELECT city FROM citycodes WHERE code = 37040

但我通过这样做改进了选择:我在城市列中添加了一个主键。我应该像这样运行查询吗:

SELECT city, code FROM citycodes WHERE code = "37040" AND city LIKE "C%"

任何提示将不胜感激。谢谢

Answer 1

这里有一些加快该表性能的提示:

city VARCHAR(30),

您可能希望考虑将此 VARCHAR 更改为 CHAR 字段。数据库的大小会有所增加，但除非你的数据库中包含世界上的每个大都市、城镇和泥屋村庄，否则它不会在存储上产生超过几个 MiB 的差异，但可能会 increase indexing performance by up to 20% .

code VARCHAR(10),

在您的示例中，您提供的代码是数字，那么为什么不使用 INT(或其他整数数据类型)呢？结果，您将看到性能的另一个显着提升。

INDEX idx_code (code),
INDEX idx_city (city)

将您的 PRIMARY KEY 放在将被更频繁查询的列上(或者，如果有疑问，使用较小的数字字段 code)。请注意，这意味着该列中的值必须是 UNIQUE。如果您要查询其他字段(即，根据名称确定城市代码)，也请向该字段添加一个 KEY。

) ENGINE=MyISAM;

MyISAM 确实可以用于此目的，因为该表可能是读取密集型的，不需要锁定，并且(如果您注意到我上面的评论)将具有固定长度的行。

这是修改后的表定义:

CREATE TABLE citycodes (
    code INT PRIMARY KEY,
    city CHAR(30),
    KEY(city)
) ENGINE = MyISAM;

最后但同样重要的是，考虑您的算法:

这是一个非常简单的键/值表，而且可能很小。如果您关心这里的性能，也许更谨慎的问题是，“为什么这个表的性能对我的应用程序如此重要？”

查看您的算法，查看您进行的查询的类型和数量。例如，即使您将此查询优化为在一半时间内运行，如果您在循环中运行 SELECT 数千次，它仍然会非常慢。优化查询的最佳方法是更改​​您的算法，这样您根本就不必运行查询。 (或者只运行一次，并将完整的结果缓存在您的应用程序中，在您的语言的 native 数据结构中访问速度会快得多)。

如果您很少直接查询此表，而是在 JOIN 中提取列，您可以考虑对表架构进行非规范化，以仅使用 city在需要它的其他表中命名。除非绝对必要，否则我不推荐这样做，因为它可能会显着增加其他表的存储需求，并且如果您的城市之一更改了名称，则需要大量的 UPDATE 查询。但是，de-normalization如果仔细考虑，仍然可以是一个有效的设计决策。