mysql中的索引

1、说明

定义：索引是创建在表上，对数据库一列或多列组合的值进行排序的一种结构，主要是提高对表中的数据的查询速度。
【类比】
数据表类比新华字典，索引好比新华字典中的音序，如果没有音序查找数据要全量查询400多页，如果有音序只需要先遍历10几页音序直接查找，这样提高了查询速度，索引功能类似。

2、索引存储

索引从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。

2.1 索引优缺点

• 优点：
  提高数据检索速度，提高分组排序等耗费时间。
• 缺点：
  索引创建和维护需要耗费时间，随着数据量的增加耗时时间会增加，每插入一条数据就要对该记录按索引排序。

3、索引的分类

• 类型上分：
  （1）普通索引 index
  （2）唯一索引 unique
  （3）全文索引 fulltext
  全文索引目前只有MyISAM存储引擎支持
• 作用字段空间分
  （1）单列索引
  （2）多列索引，多列索引只有使用到索引中的第一个字段时候才生效，其他情况索引会失效。
  （3）空间索引，空间索引的存储引擎必须是MyISAM，且索引字段必须非空。
• 根据中数据的物理顺序与键值的逻辑（索引）顺序关系：
  （1）聚集索引
  （2）非聚集索引

4、索引设计原则

（1）选择唯一性索引
（2）为经常要排序，分组和联合操作的字段建立索引
（3）为经常做为查询条件的字段建立索引
（4）限制索引的数量
（5） 尽量使用数据量少的字段建立索引
（6）删除不再使用和使用量较少的索引

5、索引操作

5.1、 索引创建

（1）建表时候创建索引

CREATE TABLE 表名( 属性名  数据类型 [完整的约束条件],
                  属性名  数据类型 [完整的约束条件],
                  ....
                 [UNIQUE | FULLTEXT | SPATIAL]  INDEX | KEY   别名 （属性名  [(长度)]  [ASC | DESC]）
);

【例子】

create table aa( id int(10) not null primary key,
code varchar(20) not null ,
name varhcar(20) not null,
index (id) 
);

（2）已经存在表索引

• 创建索引create方式，语法：

create [UNIQUE | FULLTEXT | SPATIAL] index 索引名  
on 表名(属性名 [(长度)]  [ASC | DESC]);

• 创建索引alter table方式，语法：

alter table 表名 add [UNIQUE | FULLTEXT | SPATIAL] index 索引名  
on 表名(属性名 [(长度)]  [ASC | DESC]);

5.2、索引的删除

drop index  on 表名；

5.3、禁用/开启 索引

有时候我们会在批量导入（插入）数据时候由于索引会影响到数据插入速度，需要临时禁用，等数据导入完毕之后在开启索引。

• 禁用索引

alter table  表名 disable keys；

• 重新开启索引

alter table 表名 enable keys;

6、查询索引失效

• like语句，索引列"%"开头模糊匹配查询条件，索引不生效。
• 多列索引，第一个索引字段存在时才生效，否则不生效。
• or语句，or前后语句都必须是索引情况索引才生效，否则索引不生效。