对于mysql中的重复索引，基数可以不同吗？ [英] Can cardinality differ for duplicate indexes in mysql?
问题描述
我有一个具有重复索引的表（同一列被索引两次（ BTREE ））但令人惊讶的是基数不同。为什么会发生这种情况。
I have a table which has duplicate indexes(same column is indexed twice (BTREE)) but surprisingly cardinality is different. Why this is happening.
'ACCENTURE_PASSIVE_CANDIDATES', '1', 'LD_INDEX', '1', 'LOCATION_DISTANCE', 'A', '37876', NULL, NULL, 'YES', 'BTREE', '', ''
'ACCENTURE_PASSIVE_CANDIDATES', '1', 'RS_INDEX', '1', 'RELEVANCY_SCORE', 'A', '21996', NULL, NULL, 'YES', 'BTREE', '', ''
'ACCENTURE_PASSIVE_CANDIDATES', '1', 'score_index', '1', 'RELEVANCY_SCORE', 'A', '146566', NULL, NULL, 'YES', 'BTREE', '', ''
'ACCENTURE_PASSIVE_CANDIDATES', '1', 'location_index', '1', 'LOCATION_DISTANCE', 'A', '172873', NULL, NULL, 'YES', 'BTREE', '', ''
推荐答案
mysql中的基数是估计，mysql根据表使用情况统计估算：
Cardinality in mysql is an estimate, and mysql bases its estimate on table usage statistics:
基数
Cardinality
索引中唯一值的估计值。这是通过运行ANALYZE TABLE或myisamchk -a更新
。基数是基于存储为整数的统计数据计算的基数是
，因此即使对于小表，该值也不一定是
。基数越高，
在连接时MySQL使用索引的几率就越大。
An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.
你可以读更多abot在mysql和innodb的文档中为myisam和innodb表引擎收集的统计信息以及如何配置这些：
You can read more abot the statistics collected for myisam and innodb table engines in mysql's and innodb's documentation and how to configure these:
- InnoDB and MyISAM Index Statistics Collection
- Controlling Optimizer Statistics Estimation
所有统计数据都存储在统计表。
All statistics are stored in the STATISTICS table within information_schema.
索引，那些估计的基数更接近于它们的确切基数（字段内的不同值的数量）创建得更长因此，mysql为他们收集了更多的统计数据，其估计更准确。如果你在这个特定的表上运行 analyze table ，那么重复索引的基数可能会比现在更接近彼此。
The indexes, those estimated cardinality is closer to their exact cardinality (number of distinct values within the field) were created longer time ago, therefore mysql collected more statistics for them and its estimate is more accurate. If you run analyse table on this particular table, probably the cardinalities of the duplicate indexes will be a lot closer to each other, than now.
最大的问题是，为什么你有重复索引？
The huge question is, why do you have duplicate indexes at all?
这篇关于对于mysql中的重复索引，基数可以不同吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
