如何给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决定不使用它，这可能是因为:

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. MySQL decides there are to many matching rows, and thinks a tablescan is probably faster. If that isn't the case, sometimes an ANALYZE TABLE helps. 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.

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

2010-06-09 02:05:03

其他回答

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

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

不要在MySQL中比较整数和字符串。如果id为int，则删除引号。

2010-06-09 01:47:26

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

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

你说你有一个指数，解释说不是这样的。然而，如果你真的这样做了，下面是如何继续:

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

2010-06-09 02:05:03

如何给MySQL表添加索引?

推荐文章

最新文章

标签