MySQL索引类型

作者: 何甜甜在吗 | 来源:发表于2017-12-28 19:24 被阅读0次

MySQL索引及查询优化书目录
MySQL索引知多少
重新学习Mysql数据库4：Mysql索引实现原理
MySql 数据查询优化
Mysql 索引那些事
5.2MySQL创建高性能索引考察点
mysql索引
mysql索引及查询
mysql
MySQL索引简述--BTree索引

面试时曾经被问了mysql的索引类型，我这个宝批龙给达到数据存储方式上了，聚集索引、二级索引、覆盖索引，也慢慢对面试有了感觉，面试可能会给你一个情景，然后深入问里面的知识点

MySQL有多种索引类型，索引是在存储引擎中实现的，不同的存储引擎对同一个索引类型的实现方式不同，不同的存储引擎支持不同的索引类型

（一）B-Tree索引
InnoDB、NDB、MyISAM、Memory都支持B-Tree索引
InnoDB的默认索引类型为B-Tree索引，其底层的数据结构为B+Tree
NDB底层的数据结构为T-Tree
MyISAM、Memory底层的数据结构为B-Tree
B-Tree索引能加快访问数据的速度，因为存储引擎不在需要进行全表扫描。而是从根节点开始遍历，通过比较节点页的值和要查找的值来找到合适的指针进入下层子节点。能进行这样查找的原因是B-Tree对索引列是顺序组织存储的
叶子节点中保存了指向了被索引数据的指针（索引段吧），指针指向数据段
InnoDB存储引擎中，B+Tree索引类型可以分为聚集索引和二级索引
聚集索引和二级索引的唯一区别：叶子节点是否保存所有数据
二级索引叶子节点存放的数据：index + primary
聚集索引会根据每张表的主键构造一棵B+Tree，叶子节点保存所有的数据
（二）哈希索引
对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
只有Memory显示支持哈希索引，并且是支持非唯一哈希索引的，如果多个列的哈希值相同，索引会以链表的方式存放在多个记录指针到同一个哈希条目中，这个和hashmap中处理哈希冲突的方式有点相同。
注：在哈希表中是根据哈希码值的大小进行排序的，而不是根据存储的顺序进行排序的，所以将无法用于排序
哈希索引的缺点：
上面的也是哈希索引的一个缺点
1）哈希索引值只包含哈希值和行指针，不存储字段值，无法避免读取行，当找到所在行以后，会对值进行一个比较，这在产生多个相同哈希值的时候是非常有必要的
2）不支持部分索引列匹配查找
3）只支持等值查找（如in、()、=、<=>），而不支持范围查找
4）产生哈希冲突时，查找效率偏慢，并且维护成本很高
InnoDB有一个特殊的功能叫做自适应哈希索引。当InnoDB注意到某些索引值被使用非常频繁时，它会在内存中基于B-Tree索引之上在创建一个哈希索引。这样将加快查询速度，因为减少了遍历
（三）全文索引
查找的是文本中的关键字，而不是直接比较索引中的值。全文索引更类似于搜索引擎做的事，而不是简单的where条件匹配
MyISAM、InnoDB存储引擎支持全文索引
全文索引支持char、varchar、text、自然语言搜索、布尔搜索
为什么需要有全文索引，因为如果使用B-Tree索引，用like '%%'做范围查找时，将不能使用索引进行查找，数据量大的时候查找效率将特别慢（仅是自己的理解）
这里以mysql官网中的sakila数据库进行演示:

mysql> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8

last_name为索引
对last_name进行一个范围查找

mysql> explain select * from actor where last_name like '%S'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

进行全表扫描查找

使用全文索引，新建了一张数据表

mysql> show create table news\G
*************************** 1. row ***************************
       Table: news
Create Table: CREATE TABLE `news` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `content` (`content`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

在content建立了全文索引
查询语句：

mysql> explain select id from news where match (content) against ('江歌')\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: news
   partitions: NULL
         type: fulltext
possible_keys: content
          key: content
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Ft_hints: sorted
1 row in set, 1 warning (0.03 sec)

使用全文索引的查询语句和普通索引的查询不同
格式为select * from table_name wherematch('index_name') against ('搜索值')
从sql执行情况中看，使用上了索引

（四）空间数据索引
MyISAM支持