如何向 MySQL 表添加索引？

How do I add indices to MySQL tables?

提问by Michael

I've got a very large MySQL table with about 150,000 rows of data. Currently, when I try and run

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

SELECT * FROM table WHERE id = '1';

the code runs fine as the ID field is the primary index. However, for a recent development in the project, I have to search the database by another field. For example:

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

SELECT * FROM table WHERE product_id = '1';

This field was not previously indexed; however, I've added one, so mysql now indexes the field, but when I try to run the above query, it runs very slowly. An EXPLAIN query reveals that there is no index for the product_id field when I've already added one, and as a result the query takes any where from 20 minutes to 30 minutes to return a single row.

该字段以前未编入索引；但是，我添加了一个，因此 mysql 现在对该字段进行索引，但是当我尝试运行上述查询时，它运行得非常缓慢。EXPLAIN 查询显示，当我已经添加了一个 product_id 字段时，没有索引，因此查询需要 20 分钟到 30 分钟的任何时间来返回单行。

My full EXPLAIN results are:

我的完整解释结果是：

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

It might be helpful to note that I've just taken a look, and ID field is stored as INT whereas the PRODUCT_ID field is stored as VARCHAR. Could this be the source of the problem?

注意到我刚刚看过可能会有所帮助，ID 字段存储为 INT 而 PRODUCT_ID 字段存储为 VARCHAR。这可能是问题的根源吗？

回答by zerkms

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

Never compare integerto stringsin MySQL. If idis int, remove the quotes.

永远不要比较integer，以strings在MySQL。如果id是int，删除引号。

回答by pabloferraz

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

回答by Hieu Vo

You can use this syntax to add an index and control the kind of index (HASH or BTREE).

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

create index your_index_name on your_table_name(your_column_name) using HASH;
or
create index your_index_name on your_table_name(your_column_name) using BTREE;

You can learn about differences between BTREE and HASH indexes here: http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

您可以在此处了解 BTREE 和 HASH 索引之间的差异：http: //dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

回答by Antony

It's worth noting that multiple field indexes can drastically improve your query performance. So in the above example we assume ProductID is the only field to lookup but were the query to say ProductID = 1 AND Category = 7 then a multiple column index helps. This is achieved with the following:

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

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

Additionally the index should match the order of the query fields. In my extended example the index should be (ProductID,Category) not the other way around.

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

回答by Jazzzzzz

Indexes of two types can be added: when you define a primary key, MySQL will take it as index by default.

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

Explanation

解释

Primary key as index

主键作为索引

Consider you have a tbl_studenttable and you want student_idas primary key:

考虑你有一个tbl_student表，你想student_id作为主键：

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

Above statement adds a primary key, which means that indexed values must be unique and cannot be NULL.

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

Specify index name

指定索引名称

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

Above statement will create an ordinary index with student_indexname.

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

Create unique index

创建唯一索引

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

Here, student_unique_indexis the index name assigned to student_id and creates an index for which values must be unique (here null can be accepted).

这里，student_unique_index是分配给 student_id 的索引名称，并创建一个索引，其值必须是唯一的（这里可以接受 null）。

Fulltext option

全文选项

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

Above statement will create the Fulltext index name with student_fulltext_index, for which you need MyISAM Mysql Engine.

上面的语句将创建带有 的全文索引名称student_fulltext_index，为此您需要 MyISAM Mysql Engine。

How to remove indexes ?

如何删除索引？

DROP INDEX `student_index` ON `tbl_student`

How to check available indexes?

如何检查可用索引？

SHOW INDEX FROM `tbl_student`

回答by Wrikken

You say you have an index, the explain says otherwise. However, if you really do, this is how to continue:

你说你有一个索引，解释说不是。但是，如果你真的这样做了，这就是继续的方法：

If you have an index on the column, and MySQL decides not to use it, it may by because:

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

1. There's another index in the query MySQL deems more appropriate to use, and it can use only one. The solution is usually an index spanning multiple columns if their normal method of retrieval is by value of more then one column.
2. MySQL decides there are to many matching rows, and thinks a tablescan is probably faster. If that isn't the case, sometimes an ANALYZE TABLEhelps.
3. In more complex queries, it decides not to use it based on extremely intelligent thought-out voodoo in the query-plan that for some reason just not fits your current requirements.

1. 查询中还有一个 MySQL 认为更适合使用的索引，它只能使用一个。如果通常的检索方法是按多于一列的值，则解决方案通常是跨多列的索引。
2. MySQL 决定有很多匹配的行，并认为 tablescan 可能更快。如果不是这种情况，有时会有所ANALYZE TABLE帮助。
3. 在更复杂的查询中，它决定不使用它基于查询计划中极其智能的深思熟虑的伏都教，由于某种原因不符合您当前的要求。

In the case of (2) or (3), you could coax MySQL into using the index by index hint sytax, but if you do, be sure run some tests to determine whether it actually improves performance to use the index as you hint it.

在 (2) 或 (3) 的情况下，您可以通过索引提示 sytax 诱使MySQL 使用索引，但如果您这样做，请确保运行一些测试以确定在您提示时使用索引是否确实提高了性能.