mysql - 我的 MySQL 表上的索引会提高我的网站性能吗？

Question

我有两个非常简单的表:

CREATE TABLE `tbl_words` (
  `fld_id` int(11) NOT NULL AUTO_INCREMENT,
  `fld_word` varchar(255) DEFAULT NULL,
  `fld_cat_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`fld_id`)
);

这包含一长串单词，其中列出了大约 10,500 个单词。每个词里面还有一个CatID，对应这个表中的一个词类:

CREATE TABLE `j_word_cat` (
  `fld_id` int(11) NOT NULL AUTO_INCREMENT,
  `fld_cat` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`fld_id`)
);

从这些表中获取数据的页面每月大约有 500,000 次页面浏览。表中的数据非常静态，很少(如果有的话)更新。

我发现我正在使用的 MySQL 数据库每天返回相当多的此类错误:

在查询期间与 MySQL 服务器失去连接

我想知道 - 如果我向“tbl_words”表添加一个新索引(例如，一个名为“catID”的索引)并链接到“fld_cat_id”列，性能是否会得到改善。

或者我应该创建一个同时使用两者的索引，例如fld_id 和 fld_cat_id 在一起？

如有任何建议，我们将不胜感激。

谢谢

更新

我的查询非常简单 - 我实际上并没有加入表格。

用户从 j_word_cat 表中选择一个类别，然后该 ID 用于生成一个随机的单词列表，例如，这些是我正在运行的一些常见查询:

-- Category table to populate SELECT drop down list of categories:
SELECT fld_cat, fld_id FROM j_word_cat ORDER BY fld_cat;

-- common word table selects:
-- the NOT IN bit is to exclude one category which I want to exclude by default unless the user actually chooses it
SELECT fld_word FROM tbl_words WHERE fld_cat_id NOT IN (24) ORDER BY RAND() LIMIT 50;

-- note that the value of 15 would vary depending on the category the user selected
-- 15 is just an example

SELECT fld_word FROM tbl_words WHERE fld_cat_id = 15 ORDER BY RAND() LIMIT 50;

SELECT fld_word FROM tbl_words ORDER BY RAND() LIMIT 60;

Answer 1

索引会加速选择并减慢插入和更新，但是我经常处理有几百万条记录的表，我们可以通过适当的索引将查询速度从 4 到 5 分钟减少到不到 1 秒，其中对插入和更新的影响可以忽略不计。

如果您创建了一些索引并想知道它们是否被使用，那么您可以使用 mysql Explain 工具查看您的查询

EXPLAIN SELECT fld_word 
FROM tbl_words WHERE fld_cat_id NOT IN (24) 
ORDER BY RAND() LIMIT 50;

在您的结果中，您将看到可能的索引列表和最终选择的索引。请特别注意“行”列，因为这将显示结果中每个表必须扫描的行数。

最终，您希望在您的服务器上运行零个未索引查询，并且运行的查询应该扫描尽可能少的行。我的建议是在您的 mysql.ini 文件中打开未索引查询日志，并且在合理的地方修复所有查询。

如果您加入或排序或使用它会提高性能，如果您只是选择未排序的信息而不加入则不会。

fld_cat_id 应该被索引

您的主键应该这样声明。如果你想完全优化它们，那么让它们不为空、无符号、auto_increment 并将它们分配为主键。

如果您正在做类似 where fld_word = 'bla' 的事情，那么应该将其与使用的任何连接键一起编入索引。

如果您有一个小型数据库，那么性能的提升可以忽略不计，但是这并不是对好的设计偷懒的理由。

更多阅读:

• > https://stackoverflow.com/a/1252288/243439
• > https://dev.mysql.com/doc/refman/5.0/en/using-explain.html
• > https://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html