数据库索引的分类，类型，索引的优缺点，索引的优化

一、数据索引的分类

1、按表列属性分类

单列索引(主键索引,唯一索引,普通索引)和多列索引（组合索引），全文索引

• 主键索引
• 唯一索引
• 普通索引
• 多列索引
• 全文索引

2、按数据结构分类

• 1） B+tree索引
  b+tree基于平衡二叉树的一种多路平衡查找树，所有记录都按照顺序存放在叶子节点中，各个叶子节点直接通过链表相连。与b树不同的是：非叶子节点只存储键值信息。所有叶子节点之间都有一个链指针。数据记录都存放在叶子节点中。
• 2）hash索引
  基于hash表结构实现的索引，mysql中只有MEMORY/HEAP和NDB存储引擎支持;InnoDB引擎支持自适应hash索引，但是是数据库自身创建使用的，而不能进行人为定义。当二级索引被频繁的访问时，便会自动创建自适应哈希索引;通过 命令SHOW ENGINE INNODB STATUS可查看自适应hash索引的使用情况;通过 命令SHOW VARIABLES LIKE ‘%ap%hash_index’ 查看是否打开自适应hash索引对比：
  由于hash索引是比较其hash值，hash索引只能进行等值查找而不能进行范围查找hash索引无法进行排序：原因同上不支持最左匹配原则，复合索引时合并一起计算hash值hash索引的检索效率很高可以一次定位，但是当发生大量hash碰撞的时候，链表变长，hash索引效率上是不如b+tree的由于存在hash碰撞的问题，当需要获得总数时候，hash 索引在任何时候都不能避免表扫描
• 3）T-tree索引
• 4）R-tree索引

3、按存储结构分类

• 1）聚簇索引(聚集索引)
  InnoDB的聚簇索引实际上是在同一个BTree结构中同时存储了索引和整行数据，通过该索引查询可以直接获取查询数据行。
  聚簇索引不是一种单独的索引类型，而是一种数据的存储方式，聚簇索引的顺序，就是数据在硬盘上的物理顺序。
  在mysql通常聚簇索引是主键的同义词，每张表只包含一个聚簇索引(其他数据库不一定)。
• 2）辅助索引(非聚集索引，次级索引，二级索引)
  非聚集索引在BTree的叶子节点中保存了索引列和主键。如果查询列不在该索引内，只能查到其主键值，还需要回表操作查询聚簇索引进行查询。
  聚簇索引的优点：
  可以把相关数据保存在一起，如：实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少量的数据页就能获取某个用户全部邮件，如果没有使用聚集索引，则每封邮件都可能导致一次磁盘IO。
  数据访问更快，聚集索引将索引和数据保存在同一个btree中，因此从聚集索引中获取数据通常比在非聚集索引中查找要快。
  使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
  聚簇索引的缺点：
  聚簇数据最大限度地提高了IO密集型应用的性能，但如果数据全部放在内存中，则访问的顺序就没有那么重要了，聚集索引也没有什么优势了
  插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到innodb表中速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表
  更新聚集索引列的代价很高，因为会强制innodb将每个被更新的行移动到新的位置。
  基于聚集索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题，当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间。
  聚集索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
  二级索引可能比想象的更大，因为在二级索引的叶子节点包含了引用行的主键列。
  二级索引访问需要两次索引查找，而不是一次。

二、索引的优缺点

1、优点：创建索引可以大大提高系统的性能。

第一、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 
第二、可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。 
第三、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 
第四、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 
第五、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
 也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点， 但是，为表中的每一个列都增加索引，是非常不明智的。

2、缺点

第一、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 
第二、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。 
第三、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

三、索引的优化方法