索引的各种操作

索引

最左匹配：
所谓最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL
语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。

假设，我们对(a,b)字段建立一个索引，也就是说，你where后条件为

a = 1
a = 1 and b = 2

是可以匹配索引的。但是要注意的是~你执行

b= 2 and a =1

也是能匹配到索引的，因为Mysql有优化器会自动调整a,b的顺序与索引顺序一致。 相反的，你执行

b = 2

就匹配不到索引了。 而你对(a,b,c,d)建立索引,where后条件为

a = 1 and b = 2 and c > 3 and d = 4

那么，a,b,c三个字段能用到索引，而d就匹配不到。因为遇到了范围查询！

SELECT * FROM table WHERE a > 1 and b = 2;

如何建立索引?

如果此题回答为对(a,b)建立索引，那都可以回去等通知了。
此题正确答法是，对(b,a)建立索引。如果你建立的是(a,b)索引，那么只有a字段能用得上索引，毕竟最左匹配原则遇到范围查询就停止匹配。
如果对(b,a)建立索引那么两个字段都能用上，优化器会帮我们调整where后a,b的顺序，让我们用上索引。

聚集索引与非聚集索引

• 聚集索引：

聚集索引的意思可以理解为顺序排列，比如一个主键自增的表即为聚集索引，即id为1的存在于第一条，id为2的存在于第二条…假使数据库中是使用数组来存放的这张表中的数据，那么如果我需要查找第100条，那么直接第一条数据的地址加上100即为第一百条的地址，一次就能查询出来。

• 非聚集索引：

非聚集索引可以简单理解为有序目录，是一种以空间换取时间的方法。举个例子，在一个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条，而不需要在去遍历表中的所有数据。

所以，在非聚集索引中，不重复的数据越多，那么索引的效率越高。

创建索引

• 创建普通索引：

三种模式：

CREATE INDEX 索引名 ON 表名(列名1，列名2,…);

修改表: ALTER TABLE 表名 ADD INDEX 索引名 (列名1，列名2,…);

创建表时指定索引：CREATE TABLE 表名 ( […], INDEX 索引名 (列名1，列名 2,…) );

• 创建唯一索引：

表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique

三种模式：

创建索引：CREATE UNIQUE INDEX 索引名 ON 表名(列的列表);

在表上增加索引：ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表);

创建表时指定索引：CREATE TABLE 表名( […], UNIQUE 索引名 (列的列表) );

在创建了唯一索引之后，列中即不能重复，比如，现在我给表中插入一条重复的值，会报错：

Error Code: 1062. Duplicate entry ‘3’ for key ‘id_num_index’

即在id_num_index唯一索引上出现了重复。

删除索引

以下两种模式都可以删除索引：

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

查看索引

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

Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。

Null：如果列含有NULL，则含有YES。如果没有，则该列含有NO。

Index_type：用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

索引的选择原则

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

较频繁的作为查询条件的字段应该创建索引

不重复的字段越多，那么索引的价值越高，这个比例应该在(0,1]之间，这个数值越大，越应该使用索引。

• 以下情况不建议使用索引：

表的记录比较少，比如只有几百，一千条记录，那么没必要建立索引，直接全表查询即可。

不会出现在WHERE子句中的字段不应该创建索引

重复太多的字段不适合单独创建索引，即使频繁作为查询条件