数据库MySQL02索引

1.介绍

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。
当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。
索引就像书籍的目录，当我们要搜索想看的内容的时候，先从目录搜索，然后找到内容的页数，再翻到相应的页去查看就可以了，比我们一页页翻着查找要快很多，所以索引的核心就是加快SQL的查询。
当我们表中的数据量上万，几十万甚至上百万的时候，一个SQL查询所花费的时间就会很长，那么此时就需要用到索引来加速SQL查询，具体内容请看下面对索引的描述。
但由于索引也是需要存储成索引文件的，因此对于索引的使用，也涉及大量的磁盘I/O操作，如果索引创建过多，使用不当，会造成SQL查询时，进行大量无用的磁盘I/O操作，降低了SQL查询效率，适得其反，因此掌握索引的执行过程和实现原理，非常重要！

2.索引的分类

索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结果。索引的核心是提高查询的速度！
索引的优点： 提高查询效率
索引的缺点： 索引并非越多越好，过多的索引会导致CPU使用率居高不下，由于数据的改变，会造成索引文 件的改动，过多的磁盘I/O造成CPU负荷太重

1.普通索引：没有任何限制条件，可以给任何类型的字段创建普通索引
2.唯一性索引：使用UNIQUE修饰的字段，值不能够重复，主键索引就隶属于唯一性索引
3.主键索引：使用Primary Key修饰的字段会自动创建索引
4.单列索引：在一个字段上创建索引
5.多列索引：在表的多个字段上创建索引
6.全文索引：使用FULLTEXT参数可以设置全文索引，只支持CAHR，VARCHAR和TEXT类型的字段上，常用于数据量较大的字符串类型上，可以提高查询速度；只有MyISAM存储引擎支持。

3.索引的相关操作

3.1创建和删除

创建表的时候指定索引字段：
CREATE TABLE index1(id INT,
name VARCHAR(20),
sex ENUM(‘male’, ‘female’),
INDEX(id));

在已经创建的表上添加索引：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
ON 表名 （属性名 [ASC | DESC]）;

删除索引：
DROP INDEX 索引名 ON 表名;
***用explain查看一下上面SQL语句的执行计划
explain select score from student where name=‘liu’\G

3.2索引的底层实现原理

MySQL支持两种索引，一种的B-树索引，一种是哈希索引，大家知道，B-树和哈希表在数据查询时的效率是非常高的。

这里我们主要讨论一下MySQL InnoDB存储引擎，基于B-树（但实际上MySQL采用的是B+树结构）的索引结构。

3.2.1B-树

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+树所有叶子节点被连接成了有序链表结构，因此做整表遍历和区间查找是非常容易的。

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

B-树的结构图

B+树的结构图如下：

3.3.2索引分类

主键索引，辅助索引，聚集索引，非聚集索引：

1. MyISAM存储引擎 - 主键索引
   MyISAM引擎使用B+树作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图：
   
2. MyISAM存储引擎 - 辅助索引
   在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复，如果给其它字段创建辅助索引，结构图如下：
   
3. InnoDB存储引擎 - 主键索引
   InnoDB存储引擎的主键索引，叶子节点中，索引关键字和数据是在一起存放的，如图：
   
4. InnoDB存储引擎 - 辅助索引
   InnoDB的辅助索引，叶子节点上存放的是索引关键字和对应的主键，如图：
   
   辅助索引的B+树，先根据关键字找到对应的主键，再去主键索引树上找到对应的行记录数据。
   从索引树上可以看到，InnoDB的索引关键字和数据都是在一起存放的，体现在磁盘存储上，例如创建一个user表，在磁盘上只存储两种文件，user.frm（存储表的结构），user.ibd（存储索引和数据）。
   InnoDB的索引树叶节点包含了完整的数据记录，这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

4.索引的设计原则：

从前面的内容可以看到，索引固然很好，但是给表创建过多的索引，效率反而会降低，因此在给表设计索引的时候，需要遵循以下的设计原则：

1.给区分度高的字段创建索引
2.给经常需要排序，分组和多表联合操作的字段创建索引
3.给常作为查询条件的字段创建索引
4.索引的数目不宜过多
5.使用数据量少的索引（如前缀索引，主要针对字符串类型，字符串类型尽量创建前缀索引）
6.对于多列索引，优先指定最左边的列集
7.删除不再使用或者很少使用的索引