如何给MySQL表添加索引?

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

SELECT * FROM table WHERE id = '1';

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

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

704367 次浏览
小开 
ALTER TABLE `table` ADD INDEX `product_id_index` (`product_id`)

永远不要在MySQL中比较integer和strings。如果id为int，则删除引号。

小开

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

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

  1. 查询中还有一个MySQL认为更适合使用的索引，它只能使用其中一个。解决方案通常是跨多列的索引，如果它们的正常检索方法是通过多个列的值。
  2. MySQL认为匹配的行太多了，认为表罐可能更快。如果不是这样，有时ANALYZE TABLE会有所帮助。
  3. 在更复杂的查询中，它决定不使用它，这是基于在查询计划中非常聪明的、经过深思熟虑的巫术，出于某种原因，这并不符合您当前的需求。

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

小开 
ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);
小开

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

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

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

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

小开

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

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

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


提交答案