如何给MySQL表添加索引?

我有一个非常大的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，这可能会有所帮助。这可能是问题的根源吗?

当前回答

可以添加两种类型的索引:当你定义一个主键时，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`

2016-10-24 17:36:24

其他回答

更好的选择是在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;

2020-08-13 11:00:54

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

2022-01-17 07:05:11

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

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

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

2015-08-18 11:15:25

您可以使用此语法添加索引并控制索引的类型(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

2014-05-22 11:08:56

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

2013-05-03 15:52:07

如何给MySQL表添加索引?

推荐文章

最新文章

标签