聊聊什么是索引

作为一名开发，数据库使我们再熟悉不过的了，为了优化查询效率，我们常常会进行一系列的优化，有针对代码级别的优化，也有针对数据库的优化，数据库的优化我们常常会建立索引，那么怎么才能建立合适的索引，建立的索引怎样才能起到优化查询效率的作用，接下来我们一起聊聊索引。

1.首先我们先聊聊什么是索引

　　索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。简单来说索引的出现就是为了提高数据的查询效率，就像书的目录一样。在书籍中，用户不必翻阅完整个书就能根据目录迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

       实现原理是特定字段先执行一次排序，然后每次需要指定数据前，先对从索引中查询，因为索引是特殊的数据结构，所以查询速度非常快

2.索引的特点

      我们要用好索引，那么我们就要先了解它的特点，看看它适用于什么场合，只有更好的了解了它，才能使用好它。

　　　(1)索引的特点：

　　　　　　1.加快数据库的检索速度
                     2.降低了数据库插入、修改、删除等维护任务的速度，实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录，因为对表进行INSERT、UPDATE和DELETE。在更新表时，MySQL数据库不仅要保存数据，还要保存一下索引文件,建立索引会占用磁盘空间的索引文件。
                     3.索引创建在表上，不能创建在视图上
                     4.索引既可以直接创建，也可以间接创建
                     5.可以在优化隐藏中，使用索引
                     6.使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引

          （2）索引优缺点：

优点	缺点
唯一性索可以保证数据库表中每一行数据的唯一性	创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
提升了数据库的检索速度	索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
加速表和表之间的连接，在实现数据的参考完整性方面特别有意义	查询速度的提高是以插入、更新、删除的速度为代价的，当对表中的数据进行INSERT、UPDATE和DELETE的时候，索引也要动态的维护，降低了数据的维护速度
使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能

3.索引的分类：

　(1):直接创建索引和间接创建索引
               直接创建索引： CREATE INDEX mycolumn_index ON mytable (myclumn)
               间接创建索引：定义主键约束或者唯一性键约束，可以间接创建索引
    (2).普通索引和唯一性索引
              普通索引：CREATE INDEX mycolumn_index ON mytable (myclumn)
              唯一性索引：保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用
             CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
    3.单个索引和复合索引
             单个索引：即非复合索引
             复合索引：又叫组合索引，在索引建立语句中同时包含多个字段名，最多16个字段
             CREATE INDEX name_index ON username(firstname,lastname)
    (4).聚簇索引和非聚簇索引(聚集索引，群集索引)
             聚簇索引：物理索引，与基表的物理顺序相同，数据值的顺序总是按照顺序排列
             CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH
             ALLOW_DUP_ROW(允许有重复记录的聚簇索引)
            非聚簇索引：CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

4.索引为什么可以加快查询效率

　　因为索引会将每行的索引列和数据库的物理地址组合成一个最小单元，表中存在几行就生成几个最小单元，然后用B树的数据结构，将最小单元组织起来。
这样，当你使用索引列进行查表时，将不再需要遍历数据表，而是直接查找B树获得该行在系统中的物理地址，直接返回给用户。以上是官方的说法，说的简单点就是索引会创建一个目录指向对应的结果，我们查找的时候只需要翻阅目录即可，不需要再去全文检索，换言之我们想想，在一本书（假设这本书很厚，如果只有一两页就没必要了）里面找出你想看的某一篇文章，是直接翻页找快呢还是通过目录找快呢

　　那么索引是不是越多越好呢，并不是，索引的存在意味着数据库需要为这张表单独的维护一个B树的数据结构。所以增删改的速度会会变慢，查询速度的提高是以插入、更新、删除的速度为代价的，如果给不必要的列增加了索引，那么整体的业务速度会因此而变慢。所以说，索引并不是一定可以使表的查询效率加快，只有合适的索引才能起到最好的效果，只有遇到合适的人，你才会幸福。

　　索引是不是必须由我们自行来声明呢，并不是，大部分的数据库都会自动的将主键primary列维护一个索引数据结构。同时 unique 唯一键也会维护一个索引数据结构。
所以，一旦你声明了一个列是主键或者唯一键，那么数据库底层将自动为之维护一个索引数据结构去加快查表。

　　怎么去选择合适的索引呢，选择索引的时候，索引类型对应数据的特性，而索引方法则对应查找的方法，如果没有建立正确的索引，那么还不如不建立索引，其实大多数情况下，选择普通索引方法即可，但是存在某些特殊的情况，我们需要特定的索引    比如说唯一索引，唯一索引或者说更多的是一种约束，当某一字段具有唯一性质时，选择唯一索引，如主键和外键等。 如果某字段数据比较大，如long，clob，longtext等等字段，并且改动较少，那么可以建立全文索引，全文索引比较消耗内存空间， 索引虽然是一种优化查询效率的方法，但是我们使用时一定要选择正确的索引，不要滥用，滥用索引真的还不如不用索引。

　　我们挑主键索引和非主键来理解一下，主键索引，存的是整行数据的值，而非主键索引存的则是主键的值，所以说非主键索引又叫二级索引，假设我们有一张以id为主键test表，里面有id，name，age，sex，wn（工号）等字段，我们在wn字段上建立了索引

1 sql语句是select * from test where id = ？,即主键索引查询，则只需要搜索ID这课B+树；
2 
3 sql语句是select * from test where wn  =？,即普通索引查询，则需要先搜索wn索引这个树找到对应的id，再在ID索引树上搜索一次拿到整行数据。这个过程称为回表。

也就是说，非主键索引的查询需要多扫描一颗索引树。所以，我们在应用中应该尽量使用主键查询。

5.在索引中，只有B+Tree这一种吗?

　　当然不是，数据库还使用bitmap（位图）的方法去实现索引，什么是B+Tree，可以参照一下这篇文章https://www.cnblogs.com/xiaohouye/p/11169098.html，很详细

B+Tree	bitmap
索引顶端是根节点，该结点中包含的是存有指向索引中下一级指针的项，接下来是分枝结点，分支结点中的记录存的是指向下一级的指针，最底层为叶子结点，在叶子结点存有指向表中数据行的索引项。索引项是由三部分组成：索引项头，索引列长度和值，ROWID	位图索引也是一种B-树结构，只是位图索引的叶子结点存的不是ROWID而是每一个键值的位图。 位图索引的叶子结点包含了如下的部分：索引项头，键值，起始ROWID，终止ROWID，位图段
应用范围 适合于联机事务处理系统，因为在联机事务处理系统中DML的操作较频繁。	应用范围 适合于数据仓库系统，因为在数据仓库系统中表一般都较大但是静态的并且查询较为复杂

　　B-树索引和位图索引的主要区别
　　　　1）对于low-cardinality的列使用位图索引要比B-树索引紧凑得多，从而节省了大量的磁盘空间，同时也就减少了I/O，从而达到了提高系统效率的效果；
　　　　2）位图索引所需要的存储空间要比B-树索引小得多，所以Oracle服务器在使用位图索引时将整个位图索引段装入内存中；
　　　　3）B-树索引对关键字的修改相对位图索引来说不算昂贵，在位图索引中修改键值列（索引列）需要使用段一级的锁，而B-树索引使用的是行一级的锁，还有在这种情况下可能要调整位图；
　　　　4）在对位图索引进行逻辑操作时，oracle服务器使用的是位操作，因此位图索引进行逻辑操作的效率是非常高的。

6.索引数据类型的选择

　　（1）越小的数据类型通常更好

                     小的数据类型通常在磁盘,内存和cup缓存中都需要更小的空间,处理起来更快

       （2）简单的数据类型更好

                    整型数据比起字符,处理开销更小,因为字符串的比较复杂,在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间,以及用整型数据存储ip地址

      （3）尽量避免null

                    应该在指定的列设定为NOT NULL,除非你想要存储NULL,在MySQL中,含有控制的列很难进行查询优化,因为他们使得索引,索引的统计信息以及比较运算更加复杂,你可以用0,应该特殊的值或者应该空串代替空值.

7.选择合适的标识符

　　　　1:选择合适的标识符不仅应该考虑到存储类型,而且应该考虑MySQL是怎样运算和比较的

　　　　2:整型，通常作为标识符的最好选择,因为可以更快的处理,而且可以设置为AUTO-INCREMENT

　　　　3:字符串，尽量避免使用字符串作为标识符,它们消耗更多的空间,处理起来比较慢,而且通常来说,字符串是随机的,索引它们在索引中的位置也是随机的,这会导致页面分裂,随机访问磁盘,聚簇索引分裂（对于使用聚簇索引的存储引擎）。

8.索引的维护

　　　　因为索引是跟随着基表的变化发生变化的，但是基表被删除后，索引节点空间不会被释放和重用，如果索引建立在了操作十分频繁的表上，那么磁盘的使用会变得十分庞大，所以，我们要对索引进行维护      

方法一：删除索引，从新创建，drop index，create index。
方法二：

1 ALTER INDEX ind_obj_id REBUILD ; 
2 ALTER INDEX ind_obj_id REBUILD ONLINE; 
3 ALTER INDEX ind_obj_id REBUILD ONLINE NOLOGGING;

方式三： 合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更
好些，不需额外存储空间，代价较低）

1 alter index index_sno coalesce;

可以参考这篇文章，学习一下索引的维护https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_11.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0008170.html