索引知识总结

索引：

索引是一种提高查询效率的数据结构（B树或者是哈希结构）
索引是创建在数据库表中，是对数据库表中的一列或者多列
值的进行排序的一个结果，好处就是提高查询效率

索引的分类：
普通索引：没有任何限制，可以给任意字段创建普通索引
唯一性索引：使用unique修饰的字段，值不能重复的，主键索引就属于唯一性索引
主键索引：使用primary key修饰的字段自动创建主键索引
单例索引：在一个字段上创建索引
多列索引：在多个字段上创建索引
全文索引：使用fulltext参数可以设置全文索引，只支持char\varchar\text类型的字段上，常用于数据量较大的字符串类型上

索引的创建和删除SQL
一、索引的创建
1、在创建表的时候指定索引字段

create table table_mname (
 id int,
 name varchar(12),
 index(id);
);

2、在已经创建表上添加索引
2.1 create [unique|fulltext|spatial] index idx_id(索引名) on 表名(id(属性名));
2.2 alter table 表名 add [unique|fulltext|spatial] index index_name(属性名);

二、删除索引

drop index index_name on 表名;

索引执行过程分析：

explain select * from Student where Sname like 'zhaolei'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where

possible_keys:表示SQL执行可能会命中的索引有哪些;
key:表示执行过程真正使用的索引名称
rows:表示查询影响的数据行数
当前查询possible_keys\key都为null，则未命中索引
rows=4表示当前查询操作对每一行数据都进行比较

添加索引后，分析执行过程

explain select * from Student where Sname like 'zhaolei'\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: Student
          type: range
 possible_keys: idx_name
           key: idx_name
       key_len: 27
           ref: NULL
          rows: 1
         Extra: Using index condition
 1 row in set (0.01 sec)

通过explain关键分析查询SQL，可以看出当前查询命中索引idx_name
， rows: 1表示通过一条记录就查询到了结果

索引的底层原理

mysql支持两种索引，一种是B树索引，一种是哈希表索引

MYSQL InnoDB存储引擎，基于B-树(实际MYSQL采用的是B+树)的索引结构。
B-树是一种m阶平衡树，叶子节点都在同一层，由于每一个节点存储的数据量比较大，索引整个B-树的层数是非常低的，基本上不超过三层

由于磁盘的读取也是按block块操作的（内存是按page页面操作的），
因此B-树的节点大小一般设置为和磁盘块大小一致，这样一个B-树节点，
就可以通过一次磁盘I/O把一个磁盘块的数据全部存储下来，所以当使用B-树存储索引的时候，磁盘I/O的操作次数是最少的（MySQL的读写效率，主要集中在磁盘I/O上）。

那么MySQL最终为什么要采用B+树存储索引结构呢，那么看看B-树和B+树在存储结构上有什么不同？
1、B-树的每一个节点，存了关键字和对应的数据地址，而B+树的非叶子节点只存关键字，不存数据地址。
因此B+树的每一个非叶子节点存储的关键字是远远多于B-树的，B+树的叶子节点存放关键字和数据，
因此，从树的高度上来说，B+树的高度要小于B-树，使用的磁盘I/O次数少，
因此查询会更快一些。
2、B-树由于每个节点都存储关键字和数据，因此离根节点进的数据，查询的就快，离根节点远的数据，查询的就慢；
B+树所有的数据都存在叶子节点上，因此在B+树上搜索关键字，找到对应数据的时间是比较平均的，没有快慢之分。
3、在B-树上如果做区间查找，遍历的节点是非常多的；B+树所有叶子节点被连接成了有序链表结构，因此做整表遍历和区间查找是非常容易的。

哈希索引当然是由哈希表实现的，哈希表对数据并不排序，因此不适合做区间查找，效率非常低，需要搜索整个哈希表结构。