我有以下表格:
|ELEMENTS|
------------
|id_element|
|id_catalog|
|value|
|CATALOG|
------------
|id_catalog|
|catalog_name|
|show|
|status|
我尝试添加不同的索引(几种变体):
1) ELEMENT: pair(id_element, id_catalog) and id_element and id_catalog
2) ELEMENT: pair(id_element, id_catalog) and id_element
3) ELEMENT: pair(id_element, id_catalog) and id_catalog
4) ELEMENT: id_element and id_catalog
1) CATALOG: pair(show, status) and id_catalog
2) CATALOG: id_catalog and show and status
执行跟随选择:
SELECT DISTINCT `id_element` FROM `ELEMENTS`
WHERE (id_catalog IN (SELECT `id_catalog` FROM `CATALOG` WHERE status=1 AND show = 1)) limit 10
如果有一些行,那么它工作得很快。但如果它是空的,它需要超过4秒。
同时,"SELECT
id_catalog FROM
catalog WHERE status=1 AND show = 1
"工作得很快,既有一些行,也有空的。
ELEMENTS表中有100000条记录CATALOG表中有15000条记录
我也试过"加入",但这比以前花了更多的时间。
为什么空查询能工作这么长时间,我应该怎么做才能提高速度?
以下是解释答案:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | 'PRIMARY', |'ELEMENTS' | 'index' | '' | null | null | null | 270044 | 'Using where; Using temporary'
2 | 'DEPENDENT SUBQUERY' | 'CATALOG' | 'unique_subquery' | 'PRIMARY,pair,id_catalog' | 'PRIMARY' | '4' | 'func' | 1 | 'Using where'
我想索引CATALOG(status,show)
可以快速回答子选择。
然后对ELEMENTS(id_catalog)进行一些索引可以加快主要问题的答案。
也许这取决于这些列的统计数据:如果它们没有足够的选择性,那么最终会有很多行。
当使用上面的两个索引时,你能显示EXPLAIN
的输出吗?
为什么不简单地编写一个联接来帮助优化器完成它的工作?
SELECT DISTINCT id_element
FROM elements JOIN catalog ON elements.id_catalog=catalog.id_catalog
WHERE status=1 AND show = 1
LIMIT 10
(未经测试)
出现问题的原因是,您要为每个请求提取整个目录数据库,并查找元素和目录之间的每一个匹配项。如果MySQL找到了10个条目,它就会退出,但如果它从未找到它们,它将继续检查整个数据库。我会使用EXISTS
查询来尝试提高一些性能。
SELECT DISTINCT(e.id_element)
FROM ELEMENTS e
WHERE EXISTS (
SELECT *
FROM CATALOG c
WHERE c.id_catalog = e.id_catalog
AND c.status = 1
AND c.show = 1)
LIMIT 10;
这将通过在内部查询上强制使用LIMIT 1
来减少MySQL为每个元素查找目录所花费的时间,但当可能没有匹配项时,您总是会面临搜索时间过长的风险。
我会把这些指数放在那里:
CREATE INDEX idx_element_1 ON ELEMENT (id_catalog);
CREATE INDEX idx_catalog_1 ON CATALOG (status, show);
还有这些,尽管查询可能不需要它们(这些可能是主键,除非您有重复项):
CREATE INDEX idx_element_2 ON ELEMENT (id_element);
CREATE INDEX idx_catalog_2 ON CATALOG (id_catalog);
您能删除其他索引并创建这些索引,然后返回查询结果吗?
Thx对所有人。我通过表反规范化来解决它。因为这个数据块中有太多的数据被分离了。我决定把它放在一张桌子上。现在它运行得很完美。现在查询总是需要0.03秒。