MySQL >> 学习笔记-3 : 索引

索引简介

索引的含义

1. 由一列或多列组合而成，并且对值进行排序的一种结构。
2. 创建在表上，作用是提高表中数据的查询速度。
3. 所有数据类型都可以被索引

索引的特点

优点

1. 可以提高检索数据的速度，也是创建索引的主要原因
2. 对于有依赖关系的子表和父表之间的联合查询时，可以提高检索速度
3. 使用分组和排序子句进行数据查询时，可显著节省查询中分组和排序的时间

缺点

1. 创建和维护索引会耗费时间，且耗费时间随数据量增大而增加
2. 索引需要占用物理空间
3. 增加、删除和修改数据时，要动态维护索引，导致数据维护速度降低

索引的分类

普通索引

特点

1. 可以创建在任何数据类型上，创建后可通过索引进行查询。
2. 不附带任何限制条件，其值也由本身字段的完整性约束条件决定。

唯一性索引

特点

1. 创建后限制该索引值必须唯一。
2. 可以快速确定某条记录。
3. 主键就是一种特殊的唯一性索引。

全文索引

特点

1. 可将索引设置成全文索引
2. 只能创建在char，varchar，text类型的字段上
3. 查询数据量大的字符串类型字段时，全文索引可以提高速度
4. 仅支持MyISAM存储引擎上4.仅支持MyISAM存储引擎上

单例索引

特点

1. 在单个字段上创建索引
2. 可以为普通索引、唯一性索引、全文索引、只要保证索引只对应一个字段即可

多列索引

特点

1. 在多个字段上创建索引
2. 可以通过这几个字段进行查询
3. 只有查询条件中使用这些字段中第一个字段时，索引才会被使用

空间索引

特点

1. 只要MyISAM存储引擎支持空间索引
2. 只能建立在空间数据类型上，可以调高系统获取空间数据的效率
3. 空间数据类型有：GEOMETRY, POINT, LINESTRING, POLYGON

索引的设计原则

• 选择唯一性索引「该索引的值是唯一的，可以快速通过索引来确定某条记录」
• 为经常进行需要排序、分组和联合操作的字段建立索引「排序操作会耗费时间，建立索引后有效避免排序操作」
• 为常作为查询条件的字段建立索引「常做查询条件的字段，可建立索引提升查询速度。因为该字段查询速度会影响整个表的查询速度。」
• 限制索引的数目「索引并非越多越好，都需要占用磁盘空间；索引越多对其重构和更新越麻烦，导致更浪费时间」
• 尽量使用数据量少的索引「索引的值很长会影响查询速度；如char(100)比char(10)全文检索浪费时间」
• 尽量使用前缀来索引「索引值过长时，最好使用前缀来索引；如TEXT和BLOB类型的字段，全文索引会很费时，只检索前缀可以提高检索速度。」
• 删除不再使用或很少使用的索引，减少索引对更新操作的影响「表数据被大量更新后；数据的使用方式被改变后」

创建索引

创建索引的三种方式

1. 创建表的时候
2. 已存在的表上创建索引
3. 使用ALTER TABLE创建索引

创建表同时创建索引

语法

CREATE TABLE 表名(
属性名 数据类型 [完整性约束],
属性名 数据类型 [完整性约束],
...
索引名称 [索引别名](属性名 [ASC|DESC])
);

示例

1) 普通索引
CREATE TABLE index(
id INT,
name VARCHAR(20),
sex BOOLEAN,
INDEX(id)
);

2) 唯一性索引
CREATE TABLE index2(
id INT UNIQUE,
name VARCHAR(20),
UNIQUE INDEX index_id(
id ASC)
);

3) 全文索引
CREATE TABLE index3(
id INT,
info VARCHAR(30),
FULLTEXT INDEX index_info(info)
)ENGINE=MyISAM;

4) 单列索引
CREATE TABLE index4(
id INT,
subject VARCHAR(30),
INDEX index_str(subject(10))
);

5) 多列索引
CREATE TABLE index5(
id INT,
name VARCHAR(20),
sex CHAR(4),
INDEX index_ns(name,sex)
);

6) 空间索引
CREATE TABLE index6(
id INT,
space GEOMETRY NOT NULL,
SPATIAL INDEX index_sp(space)
)ENGINE=MyISAM;

在已存在的表上创建索引

语法

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引别名 ON 表名(属性名 [ASC|DESC])

示例

1) 普通索引
CRATE INDEX index_normal ON index1(id);

2) 唯一性索引
CREATE UNIQUE INDEX index_id ON index2(id);

3) 全文索引
CREATE FULLTEXT INDEX index_info ON index3(info(10));

4) 单例索引
CREATE INDEX index_addr ON index4(address(4));

5) 多列索引
CREATE INDEX index_na ON index5(name,address(4));

6) 空间索引
CREATE SPATIAL INDEX index_line ON index6(line));

使用ALTER TABLE语句创建索引

语法

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 (属性名[(长度)] [ASC|DESC])

示例

1) 普通索引
ALTER TABLE example ADD INDEX index_name(name(20));

2) 唯一性索引
ALTER TABLE example ADD UNIQUE INDEX index_id(id);

3) 全文索引
ALTER TABLE example ADD FULLTEXT INDEX index_info(info(10));

4) 单例索引
ALTER TABLE example ADD INDEX index_addr(address(4));

5) 多列索引
ALTER TABLE example ADD INDEX index_na(name,address);

6) 空间索引
ALTER TABLE example ADD SPATIAL INDEX index_line(line);

删除索引

语法

DROP INDEX 索引名 ON 表名;

示例

SHOW CREATE TABLE index1;

问题思考

MySQL中索引、主键和唯一性的区别？

索引建立在一个或者几个字段上，建立了索引后，表中的数据就按照索引的一定规则排列。
主键是表中数据的唯一标识，不同的记录的主键值不同，在建立主键的时候，系统会自动建立一个唯一性索引。
唯一性也是建立在一个或者几个字段上，其目的是为了保证不同记录，具有唯一性字段的值是不同的。

表中建立了索引以后，导入大量数据为什么会很慢？

因为导入大量数据之后，会按照索引进行排序。解决方法：先导入数据后建立索引