这是一个真实表格的复制。假设我有以下代码:
CREATE TABLE `testTable` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`col` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `testTable` (col) VALUES (NULL), ('a'), (NULL), ('b'), (NULL), ('c'), (NULL), ('d'), (NULL), ('e'), (NULL), ('f');
ALTER TABLE `testTable` ADD INDEX (`col`);
OPTIMIZE TABLE `testTable`;
SHOW INDEX FROM `testTable`;
我理解
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| testTable | 0 | PRIMARY | 1 | id | A | 12 | NULL | NULL | | BTREE | | |
| testTable | 1 | col | 1 | col | A | 12 | NULL | NULL | YES | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
为什么col
的基数是12而不是7?有7个唯一值,为什么所有的NULL都被单独计算?这会增加索引的大小吗?只要我使用空字符串而不是NULL值,基数就会下降。哪一个是首选值?