什么是索引
索引是创建在表上的,是对数据表中一列或多列值进行排序的一种结构。索引能提高数据查询的速度。通过索引,查询数据时可以不必读完记录的所有信息而只是查询索引列。
索引有两种存储类型,包括B型树(BTREE)和哈希(HASH)索引。InnoDB和MyISAM存储引擎都支持BTREE索引,MEMORY存储引擎支持HASH和BTREE索引,默认为前者。
在需要经常使用ORDER BY 、GROUP BY、DISTINCT和UNION等操作的字段,排序字段会浪费很多时间,如果为其建立索引,可以有效地避免排序操作。索引的缺点是创建和维护索引需要耗费时间,耗费时间随着数据量的增加而增加,索引需要占用物理空间。而且向有索引的表中插入记录时,会降低插入数据的速度。因为数据库系统会按照索引进行排序。
索引的分类
- 普通索引
在创建表的时候,加入以下sql语句。
INDEX 别名 (key [长度] [ASC|DESC])
- 唯一性索引
在创建该索引时,限制该索引的值必须是唯一值。主键就是一种特殊的唯一索引
UNIQUE INDEX 别名 (key [长度] [ASC|DESC])
- 全文索引
全文索引只能创建在CHAR、VARCHA或TEXT类型的字段上。在查询数据量较大的字符串类型时,使用该索引能提高查询速度。5.6的MySQL版本的InnoDB支持全文索引。
FULLTEXT INDEX 别名 (key [长度] [ASC|DESC])
- 单列索引
- 多列(联合)索引
在一个表的多个字段上建立一个索引。
例如:INDEX (A,B,C)
只有使用了索引的第一个字段时,索引才会生效。 - 空间索引
使用SPTIAL参数可以设置索引为空间索引。一般很少用到这种索引。
索引操作
在未存在索引的表中添加索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 别名 ON 表名 (key [长度] [ASC|DESC])
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 别名 ON 表名 (key [长度] [ASC|DESC])
- 删除索引
DROP INDEX 索引名 ON 表名
关于查询中的索引使用
可以在查询语句的前面加上EXPLAIN查看语句的执行情况来判读索引是否被使用。
创建如下的测试数据表
CREATE TABLE test(
a int,
b int,
c int,
d int,
index (a),
index (b,c,d),
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
然后在表里插入测试数据
INSERT INTO test (a, b, c, d) VALUES (1, 2, 3, 4),(11, 2, 3, 4),(1, 22, 3, 4),(1, 2, 33, 4),(1, 2, 3, 44);
当使用explain select * from test where a = 1;
可以看到该sql语句使用到了索引。
当使用explain select * from test where b=22;
可以看到该sql语句使用到了索引。
当时用explain select * from test where b=22 and c=3;
也是用到了索引。
但是用到explain select * from test where b=2 or c=3;
时并没有用到索引。
联合索引如果要使用的话,查询语句必须要包含前面的索引字段,否则并不能用到索引,如expalin select * from test where c=33;
就没办法用到索引,因为这个联合索引的顺序是b,c,d。要用c的话就要包含b。