数据库的索引详解

什么是索引

索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息

SQL的主流索引结构有B+树以及Hash结构。

索引为何会加快查询速度

索引提供只想存储 在表的指定列中的数据值的指针 ，然后根据指定的排序对这些指针排序，数据使用索引以找到特定值，根据指针找到包含该值的行，这样可以使对应于表的sql语句执行的更快，可快速访问数据表中的特定信息。

索引分类

<1>从物理结构上 分为两类：聚集索引和非聚集索引。聚集索引以及非聚集索引用的是B+树索引

聚集索引

• 一个表中只能有一个聚集索引（聚簇索引）
• 物理存储顺序按照索引顺序，即数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同；
• 插入数据速度慢（时间花费在物理存储位置；先找到位置再插入），查询速度快；
• 索引的叶子节点就是对应的数据节点

非聚集索引

• 索引的逻辑顺序与磁盘上行的物理存储顺序不同
• 一个表中可以拥有多个非聚集索引。
• 非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块

<2>从应用上分 ，分为  主键索引、唯一索引、全文索引和组合索引。

1.普通索引

是最基本的索引，它没有任何限制

2.唯一索引

索引列的值必须唯一，但允许有空值。

3.主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

4.组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用

5.全文索引

char、varchar，text 列上可以创建全文索引

什么情况下不创建索引

• 查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
• 很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
• 当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系
• 定义为text和image和bit数据类型的列不应该增加索引

索引的缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。使写入数据的速度下降
• 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。