我有一个非常大的 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,这可能会有所帮助。这可能是问题的根源吗?
EXPLAIN
结果吗?你确定没有没有索引吗?还是索引在那里,但 MySQL 选择不使用它?
ALTER TABLE `table` ADD INDEX `product_id_index` (`product_id`)
永远不要在 MySQL 中比较 integer
和 strings
。如果 id
是 int
,则删除引号。
ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);
ALTER TABLE tbl ADD INDEX (col)
而不是 ALTER TABLE tbl ADD INDEX col (col)
,那么多次使用 ALTER TABLE tbl ADD INDEX (col)
将不断添加名为 col_2
,col_3
,... 的索引。而使用 ALTER TABLE tbl ADD INDEX col (col)
第二次,将给出 ERROR 1061 (42000): Duplicate key name 'col'
。
您可以使用此语法添加索引并控制索引的类型(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
ALTER ... ADD INDEX (col)
,最好使用 ALTER ... ADD INDEX col (col)
语句,请参阅已接受的答案和仅使用 ADD INDEX (col)
的注释下的注释。至少,在使用 create ...
时,我可以在同一列上创建多个索引。因此,我想这个答案也有同样的问题。如果这是错误的,请发表评论。
可以添加两种类型的索引:当你定义一个主键时,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`
值得注意的是,多个字段索引可以极大地提高您的查询性能。所以在上面的例子中,我们假设 ProductID 是唯一要查找的字段,但是如果查询说 ProductID = 1 AND Category = 7 那么多列索引会有所帮助。这是通过以下方式实现的:
ALTER TABLE `table` ADD INDEX `index_name` (`col1`,`col2`)
此外,索引应与查询字段的顺序相匹配。在我的扩展示例中,索引应该是 (ProductID,Category) 而不是相反。
the index should match the order of the query fields
的来源吗?
你说你有一个索引,解释说不然。但是,如果你真的这样做,这是如何继续:
如果你在列上有一个索引,而 MySQL 决定不使用它,可能是因为:
MySQL 认为更适合使用的查询中还有另一个索引,它只能使用一个。如果它们的正常检索方法是按多于一列的值,则解决方案通常是跨越多列的索引。 MySQL 决定有很多匹配的行,并认为 tablescan 可能更快。如果不是这种情况,有时分析表会有所帮助。在更复杂的查询中,它决定不使用它基于查询计划中极其聪明的深思熟虑的巫术,由于某种原因不符合您当前的要求。
在 (2) 或 (3) 的情况下,您可以通过 index hint sytax 诱使 MySQL 使用索引,但如果这样做,请务必运行一些测试以确定它是否真的提高了使用索引的性能,因为您提示它.
更好的选择是在 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 管理工具,包括索引
不定期副业成功案例分享
SHOW INDEXES FROM YOURTABLE
dev.mysql.com/doc/refman/5.0/en/show-index.html 检查索引是否已添加Never compare integer to strings in mysql
为什么不呢?在这种情况下,它不会自动将字符串转换为数字吗?mysql
处理它,为什么还要费心适应传递的变量的类型...