关于mysql:MySQL索引的探索

34次阅读

共计 1583 个字符,预计需要花费 4 分钟才能阅读完成。

Cardinality


Cardinality 数值示意了该索引字段的反复率水平,数值越大代表反复率越低,PRIMIARY 是自增的,可能保障插入 / 更新数据依照磁盘程序进行,然而 id 字段因为没有什么可筛选的含意,不能进行筛选查问。

咱们为 data_unique_key 建设了一个惟一索引,这个属性的值是一些筛选字段的 md5 值,如果是具体查问某一条数据的话,能够 md5 后查问 data_unique_key 为该值,速度很快。

对于反复率十分大的字段,摸索一下为 a 建设索引是否有必要。
a 的 Cardinality 是 21
b 的 Cardinality 是 77
c 的 Cardinality 是 1305

最左匹配准则

ALTER TABLE test ADD INDEX index (a, b, c);

假如当初有一张 test 表,咱们先建设一个联结索引,索引蕴含 a,b,c3 个字段。

咱们建设的索引是 3 个字段联结的,然而查问的时候只用两个字段进行筛选,也会应用这个索引的前两个字段进行筛选。

还是应用两个字段进行筛选,只是用了 c 而不是 b,查问应用了 index 这个索引然而 key_len 是 202,阐明只用了 a 的索引局部。这景象是“最左匹配准则”带来的。也就是说要像最大化利用索引,筛选的时候要依据建设索引的字段程序就行查问,如果 where 等筛选 / 分组 / 排序操作能命中索引的局部程序字段,则也会应用这部分程序字段进行索引。

SELECT 中有索引的字段,优化器会抉择应用索引查问,所以查问进去的后果是以 page_name 排序的。
反之,如果 SELECT 中没有索引字段,或者只有局部蕴含,或者是查问 *,都不会应用索引进行查问

a 是否有必要成为索引字段

SELECT a, e FROM test WHERE a = ‘2’;

(这个工夫应该是利用了缓存的根底上的工夫,第一次进行全表查问的工夫是 7 秒多,同样的 sql 再次查问就变成了 1 + 秒,试过将 query_cache_type 和 query_cache_size 都设置为 0,还是这样,有说法是操作系统级别的缓存是无奈限度的。如果一开始就建设索引,并 SELECT a FROM test,也是六七秒,所以狐疑查问后都会有缓存,那么以下的工夫都是在这个根底上进行截图)

建设一个联结索引

ALTER TABLE test ADD INDEX index(`a, b, c`);
-- SQL1:
SELECT a, e  FROM test WHERE a = '1' ;

-- SQL2:
SELECT a, e FROM test WHERE a = '1' AND b = 2;

-- SQL3:
SELECT a, e FROM test WHERE a = '1' AND data_type = 2 AND c = 'math';

发现了只利用索引的一个字段进行查问,查问破费的工夫比全表查问还要慢很多,然而索引字段利用率大,查问破费的工夫就很少了。

删除下面建设的索引,再建设一个不蕴含 a 的索引

ALTER TABLE test
DROP INDEX index,
ADD INDEX index(b, c);

SQL1 的执行如下:

相当于查问 type 是 all,耗时比之前的蕴含 a 索引的快


同样的 SQL2,能够看到索引利用长度是 1,然而速度是简直一样的,这里能够看进去 a 的反复率太高,是索引这个字段的意义并不大,而且会减少索引 key 的长度,带来 B +Tree 每一个节点的键值存储量缩小,树的构造可能会更深,进而查问更慢。

总结:

  • 联结索引中每一个索引代表的权重是不一样的,比方索引(a, b, c),a 是百,b 是十,c 是个位,那么假如筛选条件是 b 和 c,依据 B +Tree 构造,索引失去的最终是扩散的,所以应用索引的意义就不大,天然就不会应用索引了
  • 建设的索引字段须要充分利用,不失当的应用可能会比全表查问来的更慢
  • 为 Cardinality 很小的字段建设索引的必要性有待商讨,个别状况下反复率很大的字段不须要建设索引。

(以上截图都含糊掉本身数据,用 a,b,c 来代替)

正文完
 0