sql索引见解

索引分为聚集索引和非聚集索引，

         聚集索引为主键，没主键则为非空列，没非空列则数据库自动生成隐藏列，且聚集索引由数据库自己创建，程序员无法干涉。

          非聚集索引可以简单理解为有序目录，是一种以空间换取时间的方法。举个例子，在一个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 ONindex_test(idNum);
也可以写成下面的形式：
 ALTER TABLE index_test ADD UNIQUEid_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）。