我正在处理一个数据库,其中一些查询显示在慢速查询日志中。
共有2张表:
表1是一个包含标准信息的企业表:名称、电话、地址、城市、州、邮政编码等。还有一个类别字段。这个表中有数百万行。
表2是一个类别表。只有几百排。
有问题的查询如下:
# Query_time: 20.446852 Lock_time: 0.000044 Rows_sent: 20 Rows_examined: 11410654
use my_database;
SET timestamp=1331074576;
SELECT table1.id, name, phone, address, city, state, zip
FROM table1
INNER JOIN table2 ON table2.label=table1.category
WHERE state = 'tx' and city = 'San Antonio'
and category.label LIKE 'Health Care & Medical%' group by table1.id limit 0,20;
查询上的EXTENDED EXPLAIN如下所示:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE table1 index indx_state,indx_city,index_category,cat_keywords PRIMARY 4 NULL 5465 946.92 Using where
1 SIMPLE table2 ref category_label category_label 602 my_table.table1.category 1 100.00 Using where; Using index
问题是:这个查询运行需要20秒,显示在慢速查询日志中,加载html页面需要很长时间。
表1中的总记录超过1000万条,但"圣安东尼奥"只有7万条记录。与查询匹配的记录总数(忽略限制)只有几千条。对所有内容都设置了索引,EXPLAIN似乎反映了这一事实。
为什么检查的行显示有1100万?
我觉得这一定是查询拖得太久的部分原因。
一如既往地感谢。。。。
我确实遵循了这篇文章的一些建议,创建了一个关于城市、州的索引。这并没有真正帮助我的表现,但另一件事最终起到了帮助作用。很可能我发现的修复方法也会更有效,在两列上都加一个索引。
然而,解决方案是添加USE INDEX:
http://dev.mysql.com/doc/refman/5.1/en/index-hints.html
通过定义要使用的索引,查询时间从30秒减少到1.5秒。
我不知道为什么它有效,但它确实有效。
似乎需要对state和city字段进行复合索引。
ALTER TABLE table1 ADD INDEX(city, state);
我将城市作为第一个领域,因为我认为它将提供更好的选择性。此外,您还可以在表1上使用查找表和外键,并替换字符串值。不在同一个表中重复使用相同的字符串值将有助于提高性能并减少大小。