我在系统的实时版本上遇到性能问题，无法在本地重现。
在将我的本地数据库副本与实时数据库副本上的一些 EXPLAIN 结果进行比较时，我注意到多字段索引在实时的某些地方没有使用，而是在本地使用，进一步的调查表明这些索引的基数为直播中为 NULL。
我猜这就是问题所在，但是 NULL 基数是什么意思？它会导致索引不被使用吗？优化可以解决这个问题吗？有没有办法防止它再次发生？我没有对实时 MySQL 数据库的完全访问权限，因此分析和优化超出了我的正常能力。
非常感谢您的回复！
I'm having performance problems on the live version of a system which I can't reproduce locally.
In comparing a few EXPLAIN results on my local copies of the database with the live one, I note that multi-field indexes aren't used in some places on the live but are locally, and further investigation shows that these indexes have a cardinality of NULL on the live.
I'm guessing this is the problem, but what does NULL cardinality mean and will it cause an index not to be used? Will an Optimize fix this and is there a means of preventing it recurring? I don't have full access to the live MySQL database so Analyze and Optimize are outside my normal capabilities.
Many thanks for any replies!
发布评论
评论（2）
当创建（或截断）表并填充数据时，MyISAM 表索引上会出现 NULL 基数。加载后，用户必须执行“分析表 x”以有效获得该表的基数。 InnoDB 表中也存在类似的问题，需要定期执行“分析表 x”以确保最佳索引性能。 MySQL 数据库引擎不会自动更新索引基数，单列主键除外。
非 NULL 基数对于 MySQL 使用该索引至关重要/必需。
有关基数的一些信息：它是字段唯一性的度量。越唯一（值越高），该字段上的索引就越有效，并且触及的记录就越少。 NULL 基数与 0（零）基数不同。
阅读此内容：显示索引
-- JJ --
NULL cardinality on an MyISAM table's index occurs when a table is created (or truncated) and populated with data. AFTER the load the user MUST perform an 'Analyze Table x' to effectively gain cardinality for that table. A similar issue exists in InnoDB tables, where the 'Analyze Table x' needs to be performed periodically to ensure optimal index performance. The MySQL database engine does NOT automatically update index cardinality, with exception of single-column Primary Keys.
Non-NULL cardinality is critical/required for MySQL to utilize that index.
A bit about cardinality: It is a measure of the uniqueness of a field. The more unique (higher value) the more effective an index on that field will be and fewer records will be touched. NULL cardinality is not the same as a 0 (zero) cardinality.
Read This: SHOW INDEX
-- JJ --
根据 fsb 的有用发现，我猜测您正在使用 MyISAM，并建议您转向 InnoDB，多年来它一直被认为是高级表引擎。
InnoDB 的众多功能之一是自动维护索引。
As per fsb's useful find, I will guess that you are using MyISAM, and would suggest that you move to InnoDB, which has been considered a superior table engine for some years now.
One of InnoDB's many features is automatic maintenance of indexes.