我有一个非常大的MySQL表,大约有150,000行数据 . 目前,当我尝试并运行时
SELECT * FROM table WHERE id = '1';
代码运行正常,因为ID字段是主索引 . 但是,最近对于项目的开发,我必须通过另一个字段搜索数据库 . 例如
SELECT * FROM table WHERE product_id = '1';
此字段以前没有编入索引,但是,我已将其添加为索引,但是当我尝试运行上述查询时,结果非常慢 . EXPLAIN查询显示,当我已经添加了一个索引时,product_id字段没有索引,因此查询从20分钟到30分钟的任何位置返回单行 .
我的完整EXPLAIN结果是:
| 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,这可能会有所帮助 . 这可能是问题的根源吗?
6 回答
你说你有一个索引,解释说不然 . 但是,如果你真的这样做,这就是如何继续:
如果列上有索引,并且MySQL决定不使用它,那可能是因为:
MySQL认为查询中有另一个索引更适合使用,它只能使用一个索引 . 如果它们的常规检索方法的值超过一列,则解决方案通常是跨越多列的索引 .
MySQL决定有很多匹配的行,并认为tablescan可能更快 . 如果不是这种情况,有时候
ANALYZE TABLE
有帮助 .在更复杂的查询中,它决定不在查询计划中基于非常智能的深思熟虑的巫术使用它,由于某种原因它不符合您当前的要求 .
在(2)或(3)的情况下,您可以通过index hint sytax哄骗MySQL使用索引,但是如果这样做,请确保运行一些测试以确定它是否实际上提高了使用索引时的性能 .
您可以使用此语法添加索引并控制索引的类型(HASH或BTREE) .
您可以在此处了解BTREE和HASH索引之间的差异:http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html
值得注意的是,多个字段索引可以显着提高您的查询性能 . 因此,在上面的示例中,我们假设ProductID是唯一要查找的字段,但是查询说ProductID = 1 AND Category = 7然后多列索引有帮助 . 这是通过以下方式实现的:
此外,索引应与查询字段的顺序匹配 . 在我的扩展示例中,索引应该是(ProductID,Category),而不是相反 .
永远不要在MySQL中将
integer
与strings
进行比较 . 如果id
是int
,请删除引号 .可以添加两种类型的索引:当您定义主键时,MySQL默认将其作为索引 .
Explanation
Primary key as index
假设您有一个
tbl_student
表,并且您希望student_id
作为主键:上面的语句添加了一个主键,这意味着索引值必须是唯一的,不能为NULL .
Specify index name
上面的语句将创建一个带有
student_index
name的普通索引 .Create unique index
这里,
student_unique_index
是分配给student_id的索引名称,并创建一个索引,其值必须是唯一的(此处可以接受null) .Fulltext option
上面的语句将使用
student_fulltext_index
创建全文索引名称,您需要MyISAM Mysql Engine .How to remove indexes ?
How to check available indexes?