如何向MySQL表添加索引？

Question

我有一个非常大的MySQL表格，其中包含约150,000行数据。目前，当我尝试运行

SELECT * FROM table WHERE id = '1';

代码因为ID字段是主索引而运行良好。 但是，由于项目的最新发展，我必须通过另一个字段搜索数据库。例如：

SELECT * FROM table WHERE product_id = '1';

该字段之前没有被索引，但现在我已经添加了一个索引，但是当我尝试运行上述查询时，它运行得非常缓慢。使用EXPLAIN查询发现，尽管我已经添加了索引，但product_id字段没有索引，结果查询需要20到30分钟才能返回一行数据。

完整的EXPLAIN结果如下：

| id | select_type | table | type | possible_keys| key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+--------------+------+---------+------+-------+------------------+
|  1 | SIMPLE      | table | ALL  | NULL         | NULL | NULL    | NULL |157211 | Using where |
+----+-------------+-------+------+--------------+------+---------+------+-------+------------------+

值得注意的是，我刚刚查看了一下，ID字段存储为INT类型，而PRODUCT_ID字段存储为VARCHAR类型。这可能是问题的源头吗？

- Michael

---

你能发布完整的 EXPLAIN 结果吗？你确定没有索引吗？还是索引存在，但 MySQL 选择不使用它？ - VoteyDisciple

一张大表将会有1.5亿条记录。一张非常大的表有150亿条记录。一张大小适中的表有15万条记录。供日后参考。 - usumoio

请注意，'OR' 可能会导致 MySql 不使用索引。我有一个查询带有 3 个 OR，每个都匹配了一个索引，并以 15 毫秒的速度运行，但总共需要 25 秒或超时。因此，我将它们分成 3 个查询并使用 UNION 连接在一起，这样在 50 万行数据上也只需 15 毫秒。 - Leif Neland

考虑你正在存储的数据类型。性能可能会根据你比较的数据类型而改变。正如你所说，PRODUCT_ID是一个VARCHAR数据类型，尝试将其更改为INT并对该列进行索引。 - gilbertdim

Answer 1

ALTER TABLE `table` ADD INDEX `product_id_index` (`product_id`)

在MySQL中，永远不要将整数与字符串进行比较。如果id是int类型，请去掉引号。

- zerkms

---

请使用 SHOW INDEXES FROM YOURTABLE 命令查询索引是否已经添加。参考链接：http://dev.mysql.com/doc/refman/5.0/en/show-index.html。 - Timo Huovinen

今天我遇到了Michael描述的完全相同的问题，解决方法是“在MySQL中永远不要将整数与字符串进行比较。”谢谢。 - user12345

@ced：索引未被使用的主要原因是基数(cardinality)以及MySQL认为全表扫描比使用索引更快，或者另一个索引可能更适合。确保定期更新基数（如果需要，使用ANALYZE TABLE），可能需要增加它们的页数以获得更可靠的数字（以计算时间为代价）。如果所有这些都失败了，并且您确实并且非常了解自己的数据，那么可以使用USE / FORCE INDEX，但这意味着您将被迫在此后的每个X个时间段内检查是否效果更好。 - Wrikken

@Ced：是的，当我看到有人评论了一条超过5年的评论时，只是随便回答了一下 :) 但是，总的来说，调试此问题：如果您检查了基数，您会发现您的索引确实仅估计了少量不同的值，因此作为索引使用确实不太可取，考虑到您填充的方式是合理的。 因此，答案更多地涉及如何总体调试而不是仅针对此特定情况 ;) - Wrikken

@Wrikken 是的，我通常会在回复旧评论之前检查用户是否有一些声望点，以查看他们是否容易回答。无论如何，我现在必须检查“carnality”这个东西，因为我不知道它是什么。感谢您的提示。 - Ced

Answer 2

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

- pabloferraz

---

在MySQL中，如果你使用ALTER TABLE tbl ADD INDEX (col)而不是ALTER TABLE tbl ADD INDEX col (col)，那么多次使用ALTER TABLE tbl ADD INDEX (col)将会不断添加名为col_2、col_3等的索引。然而，第二次使用ALTER TABLE tbl ADD INDEX col (col)会报错：ERROR 1061 (42000): Duplicate key name 'col'。 - Abhishek Oza

Answer 3

您可以使用此语法来添加索引并控制索引类型（HASH或BTREE）。

create index your_index_name on your_table_name(your_column_name) using HASH;

或者

create index your_index_name on your_table_name(your_column_name) using BTREE;

您可以在这里了解B树和哈希索引之间的区别：http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

- Hieu Vo

---

当我使用show indexes查看时，哈希转换为B树。 - RN Kushwaha

如果我没有指定哈希和B树的默认值会是什么？ - Bhavuk Mathur

@RNKushwaha 因为 InnoDB 和 MyIsam 不支持 HASH，据我所知，只有 Memory 和 NDB 存储引擎支持它。 - Hieu Vo

为避免在相同列上出现重复索引，请勿使用此语句或 ALTER ... ADD INDEX (col)，最好使用 ALTER ... ADD INDEX col (col) 语句。请参见被接受的答案和仅使用 ADD INDEX (col) 的评论。至少，在使用 create ... 时，我可以在同一列上创建多个索引。因此，我猜这个答案也有同样的问题。如果有误，请留言。 - questionto42

Answer 4

可以添加两种类型的索引：当您定义主键时，MySQL默认将其作为索引。

说明

主键作为索引

假设您有一个名为tbl_student的表，并且您想要使用student_id作为主键：

ALTER TABLE `tbl_student` ADD PRIMARY KEY (`student_id`)

上述语句添加了一个主键，这意味着索引值必须是唯一的且不能为NULL。

指定索引名称

ALTER TABLE `tbl_student` ADD INDEX student_index (`student_id`)

上面的语句将创建一个名为student_index的普通索引。

创建唯一索引

ALTER TABLE `tbl_student` ADD UNIQUE student_unique_index (`student_id`)

在这里，student_unique_index是分配给学生ID的索引名称，并且创建了一个必须唯一的值的索引（这里可以接受空值）。

全文选项

ALTER TABLE `tbl_student` ADD FULLTEXT student_fulltext_index (`student_id`)

以上语句将使用student_fulltext_index创建全文索引名称，需要使用MyISAM Mysql引擎。 如何删除索引？

如何删除索引？

DROP INDEX `student_index` ON `tbl_student`

如何检查可用的索引？

SHOW INDEX FROM `tbl_student`

- Jazzzzzz

Answer 5

值得注意的是，多字段索引可以极大地提高查询性能。因此，在上面的示例中，我们假设ProductID是唯一需要查找的字段，但如果查询为ProductID = 1 AND Category = 7，则多列索引将有所帮助。实现这个可以通过以下方式：

ALTER TABLE `table` ADD INDEX `index_name` (`col1`,`col2`)

此外，索引应该与查询字段的顺序相匹配。在我的扩展示例中，索引应该是（ProductID，Category），而不是反过来。

- Antony

---

很好，明确命名索引可以轻松地进行反转。 - Sam Berry

你能引用“索引应该与查询字段的顺序匹配”的来源吗？ - Bishwas Mishra

Answer 6

你说你有一个索引，但解释似乎并不是这样。不过，如果你真的有，那么接下来是如何继续的：

如果你在列上有一个索引，而MySQL决定不使用它，可能是因为：

1. 查询中有另一个索引被MySQL认为更适合使用，并且它只能使用一个。通常的解决方法是创建一个跨多列的索引，如果它们的正常检索方法是通过多个列的值。
2. MySQL决定匹配的行数太多，认为表扫描可能更快。如果情况不是这样，有时运行ANALYZE TABLE会有所帮助。
3. 在更复杂的查询中，根据查询计划中极其智能思考的巫术，它决定不使用它，由于某种原因它就不能满足你当前的要求。

对于（2）或（3），你可以通过索引提示语法来诱使MySQL使用索引，但如果这样做，请确保运行一些测试以确定是否实际上使用索引改善了性能。

- Wrikken

Answer 7

更好的选择是在 CREATE TABLE 查询期间直接添加约束（假设您已经拥有关于表的信息）。

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT NOT NULL,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
    REFERENCES categories(categoryId)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=INNODB;

- Ashwin Balani

Answer 8

使用phpmyadmin，这是一个非常好的工具，用于管理MySQL数据库，包括索引。

- mostapha

---

虽然这个链接可能回答了问题，但最好在此处包含答案的基本部分并提供参考链接。如果链接页面更改，仅有链接的答案可能会失效。-【来自审查】 - Nico Haase