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

当前回答

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

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

2010-06-09 01:47:26

其他回答

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

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

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

2015-08-18 11:15:25

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

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

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

2010-06-09 01:47:26

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

如果你在列上有一个索引，而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

如何给MySQL表添加索引?

推荐文章

最新文章

标签