第一个叫做列的离散度,我们先来看一下列的离散度的公式:
count(distinct(column_name)) : count(*),列的全部不同值和所有数据行的比例。数据行数相同的情况下,分子越大,列的离散度就越高。
简单来说,如果列的重复值越多,离散度就越低,重复值越少,离散度就越高。
了解了离散度的概念之后,我们再来思考一个问题,我们在name 上面建立索引和在gender 上面建立索引有什么区别。
当我们用在gender 上建立的索引去检索数据的时候,由于重复值太多,需要扫描的行数就更多。例如,我们现在在gender 列上面创建一个索引,然后看一下执行计划。
ALTER TABLE user_innodb DROP INDEX idx_user_gender;
ALTER TABLE user_innodb ADD INDEX idx_user_gender (gender); -- 耗时比较久
EXPLAIN SELECT * FROM `user_innodb` WHERE gender = 0;
show indexes from user_innodb;
而name 的离散度更高,比如“青山”的这名字,只需要扫描一行。
ALTER TABLE user_innodb DROP INDEX idx_user_name;
ALTER TABLE user_innodb ADD INDEX idx_user_name (name);
EXPLAIN SELECT * FROM `user_innodb` WHERE name = 'leon';
查看表上的索引,Cardinality [kɑ:dɪ'nælɪtɪ] 代表基数,代表预估的不重复的值的数量。索引的基数与表总行数越接近,列的离散度就越高。
show indexes from user_innodb;
如果在B+Tree 里面的重复值太多,MySQL 的优化器发现走索引跟使用全表扫描差不了多少的时候,就算建了索引,也不一定会走索引。
https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
这个给我们的启发是什么?建立索引,要使用离散度(选择度)更高的字段。