MariaDB-索引没有提高char(255)字段的性能

Question

我正试图在一个只有100万条记录的表上执行这个SQL查询：

SELECT * FROM enty_score limit 100;

它给了我大约600毫秒的结果

一旦我在字段`dim_agg_strategy` char(255) DEFAULT NULL上添加where子句，就需要40秒才能执行：

SELECT * FROM enty_score WHERE dim_agg_strategy='COMPOSITE_AVERAGE_LAKE' limit 100;

我试着创建一个索引，但没有任何改进——执行相同的查询仍然需要40秒：

ALTER TABLE `enty_score` ADD INDEX `dim_agg_strategy_index` (`dim_agg_strategy`);
SELECT INDEX_NAME, COLUMN_NAME, CARDINALITY, NULLABLE, INDEX_TYPE 
FROM information_schema.statistics where INDEX_NAME = 'dim_agg_strategy_index';

INDEX_NAME            |COLUMN_NAME     |CARDINALITY|NULLABLE|INDEX_TYPE|
----------------------+----------------+-----------+--------+----------+
dim_agg_strategy_index|dim_agg_strategy|        586|YES     |BTREE     |

更多信息，我放在where子句中的这一列只包含6个不同的值：

select distinct dim_agg_strategy from enty_score;
dim_agg_strategy         |
-------------------------+
COMPOSITE_AVERAGE        |
COMPOSITE_AVERAGE_ALL    |
COMPOSITE_AVERAGE_LAKE   |
COMPOSITE_AVERAGE_NONLAKE|
NORMALISED_AVERAGE       |
SIMPLE_AVERAGE           |

Answer 1

优化器注意到该索引列几乎没有不同的值。所以它意识到需要大量的行。因此，它决定简单地浏览一下表格，而不必为索引而烦恼。(使用索引需要在索引的BTree和数据的BTree之间来回跳动很多。(

因此，您可以通过指出LIMIT 100来进行反驳。这是一个合理的问题。遗憾的是，这指出了Optimizer中的一个缺陷。

它在之间左右为难

• 忽略索引，如果需要扫描整个表，索引可能是最佳的。注意：如果您需要的100行恰好在表的末尾，就会发生这种情况
• 使用索引，但要支付额外的开销。在这里，它没有意识到100远小于1M，因此提高了指数通常是最佳方法的可能性

让我们试着愚弄它…删除那个索引并添加另一个索引。这次放入2列：

(dim_agg_strategy, xx)

其中CCD_ 3是一些其它列。

(如果这个技巧对你有效，请告诉我。(