MySQL详解之索引

一、索引概述

        索引与书的目录非常相似，由数据表中一列或者多列组合而成，创建索引的目的是为了优化数据库查询速度， 索引是提高性能最常用的工具。其中，用户创建的索引指向数据库中具体数据所在位置。当用户通过索引查询数据库中的数据时，不需要遍历所有数据库中的所有数据，从而提高查询效率。所有的MySQL的列类型都可以被索引，对相关列使用索引是提高select操作性能的最佳途径，不同的存储引擎定义了每一个表的最大索引数量和最大索引长度，所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节。索引有两种存储类型：B型树（B TREE）索引（B TREE索引又分为聚簇索引和辅助索引）和哈希（HASH）索引，B型树索引为系统默认的索引存储类型。InnoDB（聚簇索引）和MyISAM（辅助索引）存储引擎支持B类型索引，MEMORY存储引擎支持哈希类型索引。

二、索引的作用

1、索引的优点

        数据库对象索引其实与书目录非常相似，主要是为了提高从表中检索数据的速度，提高系统的性能， 其优点如下：
1>通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
2>可以大大加快数据的检索速度；
3>加速表与表之间的连接，特别是在实现数据的参照完整性方面；
4>在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间；
5>通过使用索引，可以在查询过程中使用优化隐藏器，提高系统的性能。

2、索引的缺点

        索引有上述优点，但是也会存在如下不足：
1>创建索引和维护索引要耗费时间，且时间会随着数据量的增加而增加；
2>索引需要占用物理空间，除了数据表占数据空间之外，每一个索引还要占用一定的物理空间，如果要建立聚簇索引那么需要的空间就会更大。（注：聚簇索引确定表中数据的物理顺序，它规定数据在表中的物理存储顺序，因此一个表只能包含一个聚簇索引。有关聚簇索引的解释可参照博客：https://blog.csdn.net/lm1060891265/article/details/81482136）
3>当对表中的数据进行添加、删除和修改时，索引也需要动态的维护，如此便会降低数据的维护速度。
        由于向有些索引的表中插入记录时，数据系统会按照索引进行排序，这样便会降低插入记录的速度。所以，可以先删除表中的索引，插入数据完成后再创建索引。

3、索引的特征

        索引的两个特征：唯一性索引和复合索引。唯一性索引保证在索引列中的全部数据是唯一的，不会包含冗余数据，如果表中已经有了一个主键约束或者唯一性键约束，那么当创建表或者修改表时，MySQL会自动创建一个唯一性索引。（注意：如果必须保证唯一性，那么应该创建主键约束或者唯一性键约束，而不是创建一个唯一性索引）
        当在表中创建主键约束或者唯一性键约束时，MySQL会自动创建一个唯一性索引，如果表中已经包含数据，那么当创建索引时，MySQL会检查表中已有数据的冗余性，每当使用插入语句插入数据或者使用修改语句修改数据时，MySQL会检查数据的冗余性，如果有冗余值，那么会取消该语句的执行，并且返回一个错误信息。确保表中的每一行数据都有一个唯一值，这样可以确保每一个实体都可以唯一确认，只可能在可以保证实体完整性的列上创建唯一性索引。 例如，不能在人事表中的姓名列上创建唯一性索引，因为可能出现相同的名字。
        复合索引就是