索引是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键。尤其当表中的数据量越来越大时,索引对性能的影响愈发重要。
在数据库中,对字段键立索引可以大大提高查询速度。
如何创建索引和索引的类型:
1.普通索引:
最基本的索引,没有任何限制。
创建表的同时创建索引:
create table test(
id char(6) not null primary key,
name varchar(20),
city varchar(30),
time int(10),
INDEX index_name(name(length))
);
直接创建索引:
CREATE INDEX index_name ON table(column(length))
修改表结构的方式添加索引:
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
删除索引:
DROP INDEX index_name ON table_name;
2.唯一索引:
与普通索引类似,但索引列子必须唯一,允许有空值。如果是组合索引,列值的组合必须唯一。
创建表的同时创建索引:
create table test(
id char(6) not null primary key,
name varchar(20),
city varchar(30),
time int(10),
UNIQUE index_name(name(length))
);
直接创建索引:
CREATE UNIQUE INDEX index_name ON table(column(length))
修改表结构的方式添加索引:
ALTER TABLE table_name ADD UNIQUE index_name ON (column(length))
3.全文索引:
全文索引是一种特殊类型的索引,它查找的是文本中的关键词,而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样。他有许多需要注意的细节,如停用词,词干和复数,布尔搜索等。全文索引更类似与搜索引擎做的事情,而不是简单的where条件匹配。
创建表的同时创建索引:
create table test(
id char(6) not null primary key,
name varchar(20),
city varchar(30),
time int(10),
FULLTEXT index_name(city)
);
直接创建索引:
CREATE FULLTEXT INDEX index_name ON table(column)
修改表结构的方式添加索引:
ALTER TABLE table_name ADD FULLTEXT index_name(column)
4.单列索引和组合索引:
单列索引:一个索引只包含一个列,一个表可以有多个单列索引
组合索引:一个组合索引包含两个或两个以上的列。例如:建立组合索引(username,city,age),相当于分别建立了三组组合索引(username,city,age),(username,city),(username) 因为MySQL只能高效的使用索引的最左前缀列。
5.主键索引:
特殊的唯一索引,不允许有空值。
在MySQL中,索引是在存储引擎层而不是服务器层实现的。
并没有统一标准:不同存储引擎的索引的工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引。
一般来说,在where和join中出现列需要建立索引,也不完全如此,因为MySQL只对<,<=,=,>,>=,between, in 和 like 才使用索引。
索引的优缺点:
优:
- 索引大大减少了服务器需要扫描的数据量
- 帮助服务器避免排序和临时表
索引可以将随机I/O变为顺序I/O
缺:虽提高了查询速度,但会降低更新表的速度(不仅要保存数据,还要保存一下索引文件)
- 会占用磁盘空间的索引文件
索引注意事项:
- 索引不会包含有NULL值的列
- 使用短索引
- 索引列排序
- like语句操作(“admin%”会使用索引,“%admin”不会使用索引)
- 不要在列上进行计算
- 不使用 not in 和 < > 操作