Sql索引详解

一、聚集索引与非聚集索引:

1、聚集索引:
聚集索引的意思可以理解为顺序排列,比如一个主键自增的表即为聚集索引,即id为1的存在于第一条,id为2的存在于第二条…假使数据库中是使用数组来存放的这张表中的数据,那么如果我需要查找第100条,那么直接第一条数据的地址加上100即为第一百条的地址,一次就能查询出来。
因为数据库中的数据只能按照一个顺序进行排列,所以聚集索引一个数据库只能有一个。在mysql中,不能自己创建聚集索引,主键即为聚集索引,如果没有创建主键,那么默认非空的列为聚集索引,如果没有非空的列那么会自动生成一个隐藏列为聚集索引。
所以一般在mysql中,我们创建的主键即为聚集索引,数据是按照我们的主键顺序进行排列。所以在根据主键进行查询时会非常快。

2、非聚集索引:
非聚集索引可以简单理解为有序目录,是一种以空间换取时间的方法。举个例子,在一个user表中,有一个id_num,即身份号,此不为主键id,那么这些数据在存储的时候都是无序的,比如
id为1的id_num为100,id为2的id_num为97,id为3的id_num为98,id为4的id_num为99,id为5的id_num为96。。。id为67的id_num为56。。。
那么如果我要查找id_num为56的人,那么只能一条一条的遍历,n条就需要查询n次,时间复杂度为O(n),这是非常耗费性能的。

所以,现在就需要为id_num增加非聚集索引,添加了非聚集索引后,会给id_num进行排序(内部使用结构为B+树),并且排序后,我只需要查询此目录(即查询B+树),很快就知道为id为56的在数据库中的第67条,而不需要在去遍历表中的所有数据。
所以,在非聚集索引中,不重复的数据越多,那么索引的效率越高。

二、索引的操作:

我们平常在数据库中使用的索引一般非聚集索引,下面介绍其使用方法:

1、创建索引:
1.1、创建普通索引:
模式:
CREATE INDEX 索引名 ON 表名(列名1,列名2,…);
或者
修改表: ALTER TABLE 表名ADD INDEX 索引名 (列名1,列名2,…);
或者
创建表时指定索引:CREATE TABLE 表名 ( […], INDEX 索引名 (列名1,列名 2,…) );

eg:
CREATE INDEX name_index ON index_test(name);
此为在index_test表上的name列上创建一个索引name_index。

测试的表为:
CREATE TABLE index_test (
id INT NOT NULL,
name VARCHAR(50),
idNum INT,
PRIMARY KEY (id)
);

1.2、创建唯一索引:
表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为unique
下面三种模式都可以创建唯一索引:
1、创建索引:CREATE UNIQUE INDEX 索引名 ON 表名(列的列表);
2、在表上增加索引:ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表);
3、创建表时指定索引:CREATE TABLE 表名( […], UNIQUE 索引名 (列的列表) );
eg:
CREATE UNIQUE INDEX id_num_index ON index_test(idNum);
也可以写成下面的形式:
ALTER TABLE index_test ADD UNIQUE id_num_index(idNum);
此为在index_test表的idNum列上创建一个唯一索引id_num_index

在创建了唯一索引之后,列中即不能重复,比如,现在我给表中插入一条重复的值,会报:
Error Code: 1062. Duplicate entry ‘3’ for key ‘id_num_index’
即在id_num_index唯一索引上出现了重复。

2、删除索引:
以下两种模式都可以删除索引:

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name

eg:
DROP INDEX name_index ON index_test;
此为删除在index_test表上的name_index索引

3、查看索引:
SHOW INDEX FROM index_test;
即返回index_test表中的所有索引。

在返回的字段中,

Table:表的名称
Non_unique:是否不唯一,0为唯一,1不为唯一
Key_name:索引的名称
Seq_in_index:索引中的列序列号,从1开始
Column_name:列名称
Collation:列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。
Cardinality:索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。
Sub_part:如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。
Packed:指示关键字如何被压缩。如果没有被压缩,则为NULL。
Null:如果列含有NULL,则含有YES。如果没有,则该列含有NO。
Index_type:用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。
Comment:更多评注。

三、索引的选择原则:

非聚集索引在数据库创建、增加、删除、修改的时候都需要作出相应的修改,所以,使用索引也是有一定的原则,即:

1、较频繁的作为查询条件的字段应该创建索引
2、重复太多的字段不适合单独创建索引,即使频繁作为查询条件
3、不会出现在WHERE子句中的字段不应该创建索引

以下两种情况不建议使用索引:
1、表的记录比较少,比如只有几百,一千条记录,那么没必要建立索引,直接全表查询即可。
2、不重复的字段越多,那么索引的价值越高,查看不重复的字段占总体的比例可以使用下面的sql语句:
SELECT count(DISTINCT(name))/count(*) AS Selectivity FROM index_test;
比如上面这个sql就是判断index_test表中name字段中不重复的值占整体的比例,这个比例应该在(0,1]之间,这个数值越大,越应该使用索引。

阅读更多
文章标签: sql
个人分类: SQL
想对作者说点什么? 我来说一句

sql 索引详解

2012年07月14日 128KB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭