mysqll索引实验

什么是数据库索引？

索引是对数据库表中一列或多列的值进行 排序 的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

简单来说，索引就是一种排序的数据结构，数据库中的数据无序，但是这种结构是有序的，这种有序的结构指向数据库中的数据，使得数据在逻辑上是有序的(但是实际的存储仍然是无序的)。

利用这种逻辑上的 有序性 ，可以 更快的进行查询 ，否则必须进行 全表扫描 。

全表扫描的时间复杂度是O(n),这是一个看起来还不错的复杂度。然而数据库中的数据往往是存储在外存储器上的，并且这些数据也无法一次性全部调入内存，那么如果全表扫描必然出现多次的访问外存操作，这是一个极度耗时的操作，把这些数据调入内存所花费的时间甚至比在内存中扫描这些数据花费的时间 多得多 。这使得O(n)复杂度的 时间规模 在这里已经不适用了！！！

利用 索引 这种有序结构不仅可以减少比较次数(不再是全表扫描)，而且还可以减少访问外存的次数，这样一来， 时间大大缩短 。

下面来做试验。

现在有一张信息表user，表结构为：

name,id,age,position

其中name代表用户名字，id代表用户账户，age代表用户年龄，position代表用户职位。

这张表有100000条数据。

现在我们 不加索引 查询年龄为30岁的人数。

结果：

select count(*) from user where age = 32

受影响的行: 0

时间: 0.015s

然后添加索引：

CREATE INDEX myIndex ON user(age)

再查询一次：

发现时间为原来的 1/15 ！！！

这大大加快了查询的速度！！！

不过索引也并非全是优点。

为了维护索引的有序性，在添加或者删除数据的时候会造成很大的时间损耗。

比如我们现在插入一条数据

**insert into user(name,age,position) VALUES('未命名',29,'老师')

受影响的行: 1

时间: 0.094s**

现在我们删除索引再添加数据：

删除索引：ALTER TABLE user DROP INDEX myIndex

插入数据：

insert into user(name,age,position) VALUES('未命名',20,'老师')

受影响的行: 1

时间: 0.016s

可以发现有索引的时候插入数据的耗时非常大，并且这是只有两个索引的时候(主键索引和刚才添加的age列的索引)如果索引较多，那么耗时则会更大！！！

最后，索引实际上是为了查询优化而诞生的技术，它可以大大减少查询的时间，但是也会大大增加增删改的时间，因此并不是建立索引就一定能使得系统性能得到提升，因为系统的时间不仅取决于查询的时间，也取决于增删改的时间。

一般来说，不应该创建索引的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少,不利于使用索引。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。