Mysql索引 – 索引的类型

Posted on 2018-01-18 16:06 白羊先生ZZ 阅读(…) 评论(…) 编辑 收藏

  • 索引的类型

    • B-Tree索引

      • B-Tree 索引 通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。
      • B-Tree 索引 能够加快访问数据的速度，存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始搜索。
      • B-Tree 索引 适用于全键值、键值范围或键前缀查找（最左前缀原则）。
    • 哈希索引

      • 哈希索引 基于哈希表实现，只有精确匹配索引所有列的查询才有效。
      • 哈希索引 是Memory引擎表的默认索引类型，但Memory同时也支持B-Tree索引。
      • 哈希索引 自身只需存储对应的哈希值和行指针，而不存储字段值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。
      • 哈希索引 数据并不是按照索引值顺序存储的，所以无法用于排序。
      • 哈希索引 不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如数据列（A，B）上建立索引，如果查询只有数据列A，则无法使用该索引。
      • 哈希索引 不支持任何范围查询，如WHERE score > 60。
      • 哈希索引 只支持等值比较查询，包括=、IN()、<=>（注意<>和<=>是不同的操作）。
      • 介绍一个使用场景：如需要存储大量的URL，并需要根据URL进行搜索查找。如果使用B-Tree来存储URL，存储的内容就会非常大，因为URL本身很长。
        • 创建表

          1 mysql> CREATE TABLE TB3 (
2     -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
3     -> url VARCHAR(255) NOT NULL,
4     -> url_crc INT UNSIGNED NOT NULL DEFAULT 0,
5     -> PRIMARY KEY(id),
          6               -> KEY IDX(url_crc)
7     -> );
        • 创建触发器

          mysql> DELIMITER //
mysql> CREATE TRIGGER TB3_CRC_INS BEFORE INSERT ON TB3 FOR EACH ROW BEGIN SET NEW.url_crc=CRC32(NEW.url);
          -> END;
          -> //
mysql> CREATE TRIGGER TB3_CRC_UPD BEFORE UPDATE ON TB3 FOR EACH ROW BEGIN SET NEW.url_crc=CRC32(NEW.url);
          -> END;
          -> //
mysql> DELIMITER ;
        • 插入或更新数据
        •  1 mysql> INSERT INTO TB3(url) VALUES('http://www.mysql.com');
 2 mysql> SELECT * FROM TB3;
 3 +----+----------------------+------------+
 4 | id | url                  | url_crc    |
 5 +----+----------------------+------------+
 6 |  1 | http://www.mysql.com | 1560514994 |
 7 +----+----------------------+------------+
 8 
 9 mysql> UPDATE TB3 SET url="https://www.mysql.com" WHERE id=1;
10 mysql> SELECT * FROM TB3;
11 +----+-----------------------+------------+
12 | id | url                   | url_crc    |
13 +----+-----------------------+------------+
14 |  1 | https://www.mysql.com | 1053537447 |
15 +----+-----------------------+------------+

          查询（可以看出ref: const，已经是最好的级别了），有同学问为什么在WHERE条件中不直接使用一个url_crc作为筛选条件，因为一旦出现哈希冲突，另一个字符串的哈希值也恰好一样的时候，只是用url_crc来来查询是无法工作的，所以要避免冲突问题，必须在WHERE中带入哈希值和对应的列值。

           1 mysql> EXPLAIN SELECT * FROM TB3 WHERE url_crc=CRC32('https://www.mysql.com') AND url="https://www.mysql.com"\G
 2 *************************** 1. row ***************************
 3            id: 1
 4   select_type: SIMPLE
 5         table: TB3
 6    partitions: NULL
 7          type: ref
 8 possible_keys: IDX
 9           key: IDX
10       key_len: 4
11           ref: const
12          rows: 1
13      filtered: 100.00
14         Extra: Using where

          PS：如果采用这种方式，不要使用SHA1()和MD5()作为哈希函数，因为这两个函数计算出来的哈希值是非常长的字符串，会浪费大量空间，比较时也会更慢。 但如果数据表非常大，CRC32会出现大量的哈希冲突，可以自己实现一个简单的64位哈希函数，如SELECT CONV(RIGHT(MD5(“https://www.mysql.com”), 16), 16, 10) AS HASH64;

    • 空间数据索引

      •  MyISAM表支持空间索引，可以用作地理数据存储。
      •  MySQL的GIS支持并不完善，所以大部分人都不会使用该特性。
      •  空间索引会从所有维度来索引数据，和B-Tree不同，这类索引无须前缀查询。
      •  必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。
    • 全文索引

      •  全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。
      •  全文索引使用与MATCH AGAINST操作，而不是普通的WHERE条件操作。
    • 其他索引

      •  TokuDB 使用分形树索引，既有B-Tree的很多优点，又避免了B-Tree的一些缺点。
      •  ScaleDB 使用Patricia tries。
      •  InfiniDB 和 Infobright 使用了一些特殊的数据结构来优化某些特殊的查询。

 

版权声明：本文为benny-peng原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/benny-peng/p/8310720.html