Sql索引详解

我会带着你远行 2022-05-30 05:21 291阅读 0赞

## 一、聚集索引与非聚集索引： ##

**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\]之间，这个数值越大，越应该使用索引。