索引细节

索引细节

 

什么是索引

索引就是加快检索表中数据的方法，就是为了不想扫描整个表。

 

 

索引的优点

 

1. 大大加快查找数据的速度

2. 建立唯一索引，保证数据表中每一行数据唯一性

3. 进行分组和排序检索时，可以明显地减少时间

 

 

索引的缺点

 

1. 降低增删改的速度

2. 创建和维护索引，需要消耗我们的维护时间

3. 索引还是需要占用到一定的物理空间的

 

 

索引的分类

 

按索引方法分

 

• B-Tree索引
• Hash索引

按索引类型分

 

1. 普通索引

普通创建：

CREATE INDEX indexName ON mytable(username(length));

修改表创建：

ALTER mytable ADD INDEX [indexName] ON (username(length)) 

创建表时创建

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   ); 

 

 

2. 唯一索引

保证唯一性，但允许有空值

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

 

 

3. 主键索引

保证唯一性，不允许有空值，作为主键，有时我们还会加上自增

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID)   );

 

 

4. 全文索引

用于全文搜索，索引类型选择Full Text

 

 

5. 组合索引

为了进一步榨取MySQL的效率，就要考虑建立组合索引

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

 

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age   usernname,city   usernname  为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，

 

 

索引的另一种分类

 

 1. 直接创建索引和间接创建索引

直接创建索引： CREATE INDEX mycolumn_index ON mytable (myclumn)

间接创建索引：定义主键约束或者唯一性键约束，可以间接创建索引

 

 

2. 普通索引和唯一索引

普通索引：CREATE INDEX mycolumn_index ON mytable (myclumn)

唯一性索引：保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用

CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

 

 

3. 单个索引和组合索引

单个索引：即非复合索引

复合索引：又叫组合索引，在索引建立语句中同时包含多个字段名，最多16个字段

CREATE INDEX name_index ON username(firstname,lastname)

 

 

4. 聚簇索引和非聚簇索引(聚集索引，群集索引)

聚簇索引：物理索引，与基表的物理顺序相同，数据值的顺序总是按照顺序排列

CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH ALLOW_DUP_ROW(允许有重复记录的聚簇索引)

非聚簇索引：CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

 

 

什么时候用索引

 

1. where 或 join 用到的字段加索引

注意MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引（like的时候关键字前面不能用%）

索引有效：

SELECT * FROM mytable WHERE username like'admin%'

索引无效：

SELECT * FROM mytable WHEREt Name like'%admin'

 

 

2. 不建议加索引的情况

1）重复且分布平均的字段，像sex这样的字段，只有男和女，就不需要加索引

2）数据太少，如果只有几行数据，就不要索引了

3）经常插入修改数据的，也不建议加索引

 

 

 

3. 当字段数据更新频率较低，查询使用频率较高并且存在大量重复值是建议使用聚簇索引

 

4. 经常同时存取多列，且每列都含有重复值可考虑建立组合索引

 

 

参考：

http://www.jb51.net/article/49346.htm

http://blog.sina.com.cn/s/blog_6a6eb42d0100kmyz.html

http://blog.csdn.net/xyh94233/article/details/6935669