为什么需要索
当数据库表中数据越来越大时,检索数据是非常耗时的。这是就可以使用索引来提高查询数据的速度,索引就类似于字典的目录项,可以将查询的速度提高好几个数量级别。
索引的实现原理
索引的目的就是提高查询效率,通过不断低缩小范围最终得到想要的结果。mysql的索引的实现基础是B+Tree,关于B+Tree的特点如下:
- 是多叉树了,同时每个节点可以包含更多的节点,这样做有两个原因,一个是降低树的高度。另外一个是将数据范围变为多个区间,区间越多,数据检索越快。
- 每个节点不再只是存储一个key了,可以存储多个key;
- 非叶子节点存储key,叶子节点存储key和数据。
- 叶子节点两两相连,为顺序查询提供了帮助。
下面一个例子理解:
如上图,每个节点可以存储多个key,当需要查找28时,在第一层查找时,发现28在20~40之间,根据指针到第二层查询,发现在25~30之间,再根据指针到下层查找,即找到了28所对应的值。在mysql中根据操作系统的局部预读的特性,再一次io中就可以查询相邻的数据,这样能提高查询的效率,如上图在读取到26~28的内容的同时根据块指针也读取32~34的内容。mysql中的索引远比这复杂。通过以上的小列子,你可能感觉不到索引的用处,假设有1百万条数据,如果没有索引,那么查询数据就有可能参数1百万次的IO操作,但是有索引则数据所在块进行IO读取,有可能就只有3次IO操作,这样效率就会大大提高。
索引的类型
那么来看看索引有哪些类型?
- 普通索引 index 普通的列
- 主键索引 primary key 值不能为空
- 唯一索引 unique 值可以为空
- 组合索引:普通联合索引 index(id,name)、主键联合索引 primary key(id,name)、唯一联合索引unique(id,name)
- 全文索引 fulltext,适用于搜索长字符串时
索引的两大类型
hash类型的索引:查询单条快,范围查询慢
btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)
#不同的存储引擎支持的索引类型也不一样
- InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
- MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
- Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
- NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;
- Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;
索引的创建与使用
创建索引可以在创建表时添加,也可在已有表上创建
1.创建表时添加索引
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
2.存在表中添加索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ;
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;
3.删除索引
DROP INDEX 索引名 ON 表名称;
ALTER 表名称 DROP INDEX 索引名;
假设你不知道怎么建索引,也不要着急,你可以在mysql服务端使用 help create index命令,就能列出示例。
实测索引
1.准备测试表
create table test(
id int,
name varchar(20),
gender char(4)
);
2.创建存储过程添加数据
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into test values(i,concat('test',i),'male');
set i=i+1;
end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
#调用存储过程
call auto_insert();
3.查询数据看耗时
不加索引:
加索引:
create index id_index on test(id);
对比时间可以发现,加索引比无索引的耗时整整提高了263倍,极大的提高查询效率。
索引使用的原则
为数据表添加索引虽然能够提高查询速度,但是也不是随便添加,需要遵循以下原则:
- 常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引;
- 索引列不能参与计算,这是因为b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大;
- 最左前缀匹配,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整;
- 主键自动建立唯一索引;
- 作为排序的列要建立索引;
- 高并发条件下倾向组合索引;
- 经常增删改的列不要建立索引;
- 有大量重复的列不建立索引;
- 表记录太少不要建立索引;
- 索引的长度越小越好,如int
索引失效的情况:
-
在组合索引中不能有列的值为NULL,如果有,那么这一列对组合索引就是无效的;
-
在一个SELECT语句中,索引只能使用一次,如果在WHERE中使用了,那么在ORDER BY中就不要用了;
-
LIKE操作中,'%aaa%'不会使用索引,也就是索引会失效,但是‘aaa%’可以使用索引;
-
在索引的列上使用表达式或者函数会使索引失效,例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users where adddate<’2007-01-01′。
-
在查询条件中使用正则表达式时,只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
-
在查询条件中使用<>会导致索引失效。
-
在查询条件中使用IS NULL会导致索引失效。
-
在查询条件中使用OR连接多个条件会导致索引失效,这时应该改为两次查询,然后用UNION ALL连接起来。
-
尽量不要包括多列排序,如果一定要,最好为这队列构建组合索引;
索引的优缺点
- 优点
- 提高了检索速度
- 缺点
- 索引增加了额外的开销;
- 对update、delete、insert有一定的影响,若加索引的列进行了update、delete、insert操作,数据库会重新对索引排序,增加了额外的时间,降低了性能。添加索引的列尽量不要进行update、delete、insert。