索引的类型：
B-Tree索引：
使用B-Tree这一数据结构做为支撑的索引。
B树，多路平衡查找树，B+树，叶子节点之间有指针的多路平衡查找树。
每个叶子节点存储数据，非叶子节点存储下一层节点的指针，实际上在经过非叶子节点的时候，就发现非叶子节点里面存储的其实是下级节点的范围，所以是很方便做范围查询的。
能加快查询的速度，能做范围查询。
- 全值匹配
- 匹配最左前缀
- 匹配列前缀
- 匹配范围值
- 精确匹配某一列并范围匹配另外一列
实战：
mysql> show create table person; +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | person | CREATE TABLE `person` ( `A` int(50) NOT NULL, `B` int(50) NOT NULL, `C` int(50) NOT NULL, `version` varchar(20) NOT NULL, PRIMARY KEY (`version`), KEY `A` (`A`,`B`,`C`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
- 全值匹配(多列一起使用)
mysql> explain select * from person where a = 12 and b = 129 and c= 12995; +----+-------------+--------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | person | NULL | ref | A | A | 12 | const,const,const | 1 | 100.00 | Using index | +----+-------------+--------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
- 匹配做左前缀使用最左列
mysql> explain select * from person where a = 12; +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | person | NULL | ref | A | A | 4 | const | 1100 | 100.00 | Using index | +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
- 匹配列前缀
mysql> explain select * from person where a like '12%'; +----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | person | NULL | index | A | A | 12 | NULL | 98829 | 11.11 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
- 匹配范围值
mysql> explain select * from person where a > 12 and a < 14; +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | person | NULL | range | A | A | 4 | NULL | 1100 | 100.00 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
- 精确匹配某一列并范围匹配某一行
mysql> explain select * from person where a =12 and b >14; +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | person | NULL | range | A | A | 8 | NULL | 1100 | 100.00 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
- 覆盖索引
mysql> explain select a,b,c from person where a =12 and b >14; +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | person | NULL | range | A | A | 8 | NULL | 1100 | 100.00 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
多列索引请看多列索引相关的实战
hash索引
特点：只能精确匹配值，不能做排序
hash索引工作的原理，将表里面做hash索引的列，每个数据都计算hash存储到一个map中，在使用这个列做等值查询的时候，先计算等式右边的hash值，然后通过hash值去map里面找到对应的数据，如果出现hash冲突，那么再去对比真正的数据。
mysql> show create table testhash;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| testhash | CREATE TABLE `testhash` (
`fname` varchar(50) NOT NULL,
`lname` varchar(50) NOT NULL,
KEY `fname` (`fname`) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into testhash values ('Arjen', 'lentz');
Query OK, 1 row affected (0.00 sec)
mysql> insert into testhash values ('Baron', 'schwartz') ,('Peter','zaitsv'),('Vadim', 'Tkachenko');hash索引实战
mysql> explain select * from testhash where fname = 'Peter'; +----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | 1 | SIMPLE | testhash | NULL | ref | fname | fname | 52 | const | 2 | 100.00 | NULL | +----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from testhash order by 'Peter'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | testhash | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
发现hash索引做等值查询的时候可以使用索引，但是使用他作为排序字段的时候就不能使用
mysql> explain select * from testhash where fname like 'Peter%'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | testhash | NULL | ALL | fname | NULL | NULL | NULL | 4 | 25.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select fname from testhash where fname like 'Peter%'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | testhash | NULL | ALL | fname | NULL | NULL | NULL | 4 | 25.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
发现hash索引无法做范围查询，因为计算hash的时候都是全字计算的
hash冲突
hash冲突越厉害，删除效率就越低。
自适应hash
在B-Tree索引上面如果一个数据被频繁查询，那么这个数据将生成一个类似hash索引的结构，这个是一个内部实现逻辑。
mysql> show variables like '%hash%'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | innodb_adaptive_hash_index | ON | | innodb_adaptive_hash_index_parts | 8 | | metadata_locks_hash_instances | 8 | +----------------------------------+-------+ 3 rows in set (0.01 sec)
手动的hash索引
对于需要频繁对比的字符串，并且区分度比较大的情况下可以手动维护一个hash列来做hash索引。
建表和触发器
mysql> show create pseudohash; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pseudohash' at line 1 mysql> show create table pseudohash; +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | pseudohash | CREATE TABLE `pseudohash` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `url` varchar(255) NOT NULL, `url_src` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show triggers; +--------------------+--------+------------+---------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +--------------------+--------+------------+---------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+----------------------+----------------------+--------------------+ | dohash_ins | INSERT | dohash | begin set new.url_crc=crc32(new.url); end | BEFORE | 2021-05-06 13:26:07.53 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | dev@% | utf8mb4 | utf8mb4_general_ci | latin1_swedish_ci | | dohash_upd | UPDATE | dohash | begin set new.url_crc=crc32(new.url); end | BEFORE | 2021-05-06 13:26:07.55 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | dev@% | utf8mb4 | utf8mb4_general_ci | latin1_swedish_ci | | pseudohash_crc_ins | INSERT | pseudohash | begin set new.url_src = crc32(new.url); end | BEFORE | 2021-05-09 00:16:24.57 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@% | utf8 | utf8_general_ci | latin1_swedish_ci | | pseudohash_crc_upd | UPDATE | pseudohash | begin set new.url_src = crc32(new.url); end | BEFORE | 2021-05-09 00:17:21.71 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@% | utf8 | utf8_general_ci | latin1_swedish_ci | +--------------------+--------+------------+---------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+----------------------+----------------------+--------------------+ 4 rows in set (0.00 sec)
插入，修改
mysql> insert into pseudohash(url) values ('http://www.baidu.com')
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from pseudohash;
+----+----------------------+------------+
| id | url | url_src |
+----+----------------------+------------+
| 1 | http://www.baidu.com | 3500265894 |
+----+----------------------+------------+
1 row in set (0.01 sec)
mysql> update pseudohash set url = 'http://localhost:8080' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from pseudohash;
+----+-----------------------+------------+
| id | url | url_src |
+----+-----------------------+------------+
| 1 | http://localhost:8080 | 1929295430 |
+----+-----------------------+------------+
1 row in set (0.00 sec)注意
- hash索引应该是短的字符串，如果说太长了耗费磁盘空间存储数据
- 查询的时候应该带上原来的值，而不是直接使用hash之后的值去查询，如果只仅仅使用hash之后的值去做查询，那么冲突之后，将会出现多个值。
mysql> insert into pseudohash(url) values ('codding'), ('gnu');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from pseudohash;
+----+-----------------------+------------+
| id | url | url_src |
+----+-----------------------+------------+
| 1 | http://localhost:8080 | 1929295430 |
| 2 | codding | 1774765869 |
| 3 | gnu | 1774765869 |
+----+-----------------------+------------+
3 rows in set (0.00 sec)
mysql> select * from pseudohash where url_src = crc32('gnu')
-> ;
+----+---------+------------+
| id | url | url_src |
+----+---------+------------+
| 2 | codding | 1774765869 |
| 3 | gnu | 1774765869 |
+----+---------+------------+
2 rows in set (0.00 sec)
mysql> select * from pseudohash where url_src = crc32('gnu') and url = 'gnu';
+----+-----+------------+
| id | url | url_src |
+----+-----+------------+
| 3 | gnu | 1774765869 |
+----+-----+------------+
1 row in set (0.00 sec)以上 codding 和 gnu 的crc32计算出来的值是一样的，如果只使用url_src去过滤，就会出现两个值，这个就是hash冲突，解决方法就是在对比的时候加上url。
空间索引
MyISAM支持空间索引，用来存储地理数据，从所有的维度存储索引，可以使用各种维度去查询地理数据。
全文索引
特殊类型的索引，查找文本的关键字，适用于match against.
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
