我对 INDEXES 及其在查询中的列放置顺序进行了一些测试。
假设我有这个查询:
EXPLAIN SELECT * FROM `languages`
WHERE
`languages`.`active` = 1
AND `languages`.`code` = 'nl_nl';
和 2 个索引:
-
code
(唯一) -
active
,code
(与 ORDER BY 有关)
EXPLAIN
说可能的钥匙是code,index__active__code
,但奇怪的是,它选择了code
作为钥匙。
这是否真的意味着查询优化器会在我的 WHERE 字段周围忙碌,使它们更好地匹配我的一个或多个 INDEXES?所以它会让我的查询:
SELECT * FROM `languages`
WHERE
`languages`.`code` = 'nl_nl'
AND `languages`.`active` = 1
?
但是,为什么它没有选择获得active, code
指数呢?是因为匹配类型const
吗?还是 UNIQUE 总是受到青睐?
更新:
这对我来说也毫无意义:
EXPLAIN SELECT `id`, `property_id` FROM (`modeldimensions`) WHERE `model_id` = 11040 && `active` = 1;
可能的键是:unique__model_id_property_id,index_model_id__active
但它却选择了unique__model_id_property_id
.
是的,mysql 的优化器会考虑与查询相关的所有索引。它将选择它认为最有效的一种,但可能会弄错。
Mysql 和大多数其他 rdbm 将索引的基数(选择性)视为主要驱动因素。您可以在 information_schema.statistics 表中或使用 show index 命令检查所有索引的基数。
基数基本上是索引中唯一值的数量。基数越多,索引就越好地用于筛选出记录。
不幸的是,基数仅基于估计值,可能不准确。使用analyse table
命令更新索引的基数(如果它偏离)。
如果您认为 mysql 对索引做出了错误的选择,您还可以在查询中使用显式索引提示。
优化程序发现(code)
是唯一的,因此它只需要查看一行。 由于(active, code)
未声明为唯一,因此优化程序假定它可能需要查看多行。
硬币的另一面是InnoDB构建索引的方式。 (该表是InnoDB吗? 请提供SHOW CREATE TABLE
,所以我们不必猜测。 有两个辅助键可供选择。 (这些是次要的,对吗? 在没有匹配的行的情况下选择(active, code)
似乎更有利可图,在这种情况下,无需为*
而访问数据。 但人们通常不会为了一无所获而做SELECT
,所以也许这是一个低优先级。
EXPLAIN FORMAT=JSON SELECT ...
可能会为您提供有关正在使用的"成本模型"的更多线索,因此它决定使用"错误"指数的原因。