数据库索引

超有韧性的猿媛

于 2014-07-27 12:02:05 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库项目总结

本文链接：https://blog.youkuaiyun.com/liusong0605/article/details/38168531

数据库同时被 2 个专栏收录

20 篇文章

订阅专栏

项目总结

12 篇文章

订阅专栏

一、索引的概念

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。换句话说就是索引是数据结构。

我们知道，数据库查询是数据库的最主要功能之一，例如下面的SQL语句：

SELECT * FROMmy_table WHERE col2 = '77'

我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linearsearch），遍历“my_table”然后逐行匹配“col2”的值是否是“77”，这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binarytree search）等。

如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织）。

所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引.

[推荐看下原文：http://blog.linezing.com/?p=798#nav-2-1]

二、使用索引的好处

对大数据量的表进行查询时，第一种方式是全表搜索，将所有记录一一取出，和查询条件进行一一比对，然后返回满足条件的记录，缺点是耗时大，有大量磁盘I/O操作。第二种方式是建立索引，在索引中找到符合查询条件的索引值，最后通过保存在索引中的rowid快速找到表中对应的记录。

使用索引的好处，举个书上的例子(例子比较简单直观啊，实际中比这情况要多要复杂)：

t1、t2、t3三个表，每张表中的字段分别为C1、C2、C3。
每个表中值都相同，1000条数据，值为1——1000的数字。

Select C1 ，C2 ，C3 from t1， t2，t3 where C1=C2and C2 = C3；
查询结果为1000行，查询次数为1000 * 1000 * 1000

如果对每个表进行索引，利用索引的查询处理如下：
(1) 从t1表中选择一行，查看该行的数据
(2) 使用表t2上的索引，直接定位t2中与t1的值匹配的行。
使用表t3上的索引，直接定位t3中与t1的值匹配的行。
(3) 从t1表中选择第二行，依次重复……
查询结果为1000行，查询次数为1000 * 1 * 1

索引带来的好处：

加快对表中记录的查找或排序速率(对于使用分组和排序的sql语句)；

通过创建唯一索引，保证数据库表中每一行数据不重复；

加速表和表之间的连接；

索引带来的负面：

创建和维护索引需要耗费时间，且时间随着数据量的增加而增加。

索引需要占用物理空间。聚集索引需要的空间更大。

对表中的数据进行增删改时，索引也需要动态的维护，降低了数据的维护速度。

三、建立索引

索引建立在数据库表的某些列上面。那么，在哪些列上建立索引，哪些列上不能建立索引？
建立索引的列：
主键列
外键列

经常需要排序的列

经常使用在where子句中的列

经常需要根据范围进行搜索的列

无需索引的列：
查询中很少使用的列————增大空间需求、降低系统维护速度
只有很少数据值的列(例如性别)——搜索的数据行的比例很大，索引不能明显加快检索速度
存储大数据类型的列——数据量大或者这些列的取值很少
修改远远大于检索的列——修改性能和检索性能是互相矛盾的。增加索引，提高检索性能，降低修改性能

上面这些原则或者经验，为我们建立索引提供了参考，其实，这些也都是根据索引是为了提高查询操作的效率而来的，需要建立索引的列都是查询时经常作为条件的，不需要建立索引的列都是和查询操作联系不紧密的。