如何为 MySQL 表添加索引？

Question

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

SELECT * FROM table WHERE id = '1';

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

SELECT * FROM table WHERE product_id = '1';

该字段之前没有被索引；然而，我添加了一个，所以 mysql 现在索引该字段，但是当我尝试运行上面的查询时，它运行得非常慢。 EXPLAIN 查询显示，当我已经添加了 Product_id 字段时，该字段没有索引，因此查询需要 20 分钟到 30 分钟的时间才能返回一行。

我的完整解释结果是：

| 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，这可能会有所帮助。这可能是问题的根源吗？

Answer 1

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

切勿在 MySQL 中将整数与字符串进行比较。如果 id 为 int，请删除引号。

Answer 2

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

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;

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

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是分配给student_id的索引名称，并创建一个索引，其值必须是唯一的（这里可以接受null）。

全文选项

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

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

如何删除索引？

DROP INDEX `student_index` ON `tbl_student`

如何检查可用索引？

SHOW INDEX FROM `tbl_student`

Answer 5

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

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

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

Answer 6

你说你有一个索引，解释说不然。然而，如果你真的这样做，这就是如何继续：

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

1. 查询中还有另一个索引 MySQL 认为更适合使用，并且它只能使用一个。如果正常的检索方法是通过多于一列的值进行检索，那么解决方案通常是跨多列的索引。
2. MySQL 认为有很多匹配行，并认为表扫描可能更快。如果情况并非如此，有时分析表会有所帮助。
3. 在更复杂的查询中，它决定不使用它，这是基于查询计划中极其智能的深思熟虑的巫毒，由于某种原因，它不符合您当前的要求。

对于(2)或(3)的情况，你可以通过 索引提示语法，但如果这样做，请务必运行一些测试来确定在提示时使用索引是否确实提高了性能。

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;

Answer 8

使用phpmyadmin，MySQL管理的好工具，包括索引