一、索引简介
1. 索引概述
创建索引的目的是为了优化数据库的查询速度，不添加索引的情况下需要遍历所有数据才能进行删、查、改等操作。
2. 索引存储类型
| 存储类型 | 支持的存储引擎 |
|---|---|
| B型树(BTREE)索引 | InnoDB、MyISAM |
| 哈希(HASH)索引 | MEMORY |
3. 索引的优缺点
3.1 优点
- 加快数据检索速度。
3.2 缺点
- 增加创建索引、维护索引的时间。
- 索引需要占据物理空间。
- 增加索引时，会提高检索性能，但会降低修改性能；减少索引相反。
4. 索引的特征
4.1 唯一性索引
保证唯一性，可以创建主键约束或唯一性约束，而不能只单单创建唯一性索引。
4.2 复合索引
复合索引是一个索引创建在两个列或者多个列上，搜索时，以这几个列作为关键值。
- 最多可以把16个列合并成一个单独的复合索引。
- 复合索引中，所有的列必须来自同一个表，不能跨表创建复合索引。
- 复合索引中，列的排序顺序是很重要的，不能调换。
5. 索引分类
text、image、bit数据类型的列不应增加索引。
5.1 普通索引
普通索引可以创建在任何数据类型中，其值的约束由字段本身的完整性约束条件决定。
5.2 唯一性索引(UNIQUE)
设置索引唯一。主键就是一种特殊的唯一性索引。
5.3 全文索引(FULLTEXT)
全文索引只能创建在CHAR、VARCHAR、TEXT类型的字段上，全文索引可以提高对于数据量较大的字符串类型的查询速度。
数据库从3.23.23版的MyISAM搜索引擎开始支持全文索引，直到5.6版，InnoDB也支持全文索引。
5.4 单列索引
在单列上创建索引。
5.5 多列索引
在多列上创建索引。
5.6 空间索引(SPATIAL)
空间索引只能创建在空间数据类型上，目前只有MyISAM搜索引擎支持空间检索，而且检索字段不能为null。MySQL中的控件数据类型包括GEOMETRY、POINT、LINESTRING、POLYGON等。
二、索引管理
1. 创建索引
-- 创建表时
create table table_name (
...
[UNIQUE | FULLTEXT] INDEX | key [index_name] (state [(length)] [ASC|DESC])
);
-- 创建表后
create [UNIQUE | FULLTEXT] INDEX index_name on table_name(state[(length)] [ASC | DESC]);
alter table table_name add [UNIQUE | FULLTEXT] INDEX|KEY [index_name] (state[(length)] [ASC|DESC]);
2. 查看索引
-- 法一
show index from table_name[ from db_name];
-- 法二
MySQL show -k db_name table_name;
| 返回的字段 | 说明 |
|---|---|
| table | 表名称 |
| Non_unique | 索引是否包含重复词 |
| key_name | 索引名称 |
| Seq_in_index | 索引中的列序号 |
| Column_name | 列名称 |
| collation | 列在索引中的存储方式 |
| cardinality | 索引中唯一值的数目的估计值 |
| sub_part | 部分被编入索引?数目:NULL |
| packed | 关键字如何被压缩 |
| null | 列中含有NULL为YES，否则为NO |
| Index_type | 索引方法，如Btree、FullText、Hash、Rtree |
| comment | 评注 |
3. 删除索引
-- 法一
drop index index_name on table_name;
-- 法二
alter table table_name drop index index_name;