前言
本章我们将学习MySQL中的索引,本文将从索引的作用、索引的分类、创建索引的语法、索引的使用策略以及索引的实现原理等方面带大家了解索引。
索引的作用
索引的作用就是加快查询速度,如果把使用了索引的查询看做是法拉利跑车的话,那么没有用索引的查询就相当于是自行车。目前实际项目中表的数据量越来越大,动辄上百万上千万级别,没有索引的查询会变得非常缓慢,使用索引成为了查询优化的必选项目。
索引的概念
我们去一栋大楼找一家公司,不可能一层层的去找,而是先看楼层索引,然后直接到公司所在楼层就可以了。
数据库索引其实是一种特殊的数据,也保存在数据库文件中,索引数据保存着数据表中实际数据的位置,通过查找索引就能直接找到实际数据的位置,从而加快查找速度。
我们来看一段查询语句:
select * from book where id = 1000000;
假设书籍表中有几百万行数据,没索引的查询会遍历前面的100万行数据找到结果,如果我们在id上建立索引,则直接在索引上定位结果,速度要快得多。
索引的优缺点
优点
- 提高查询速度
缺点
- 本身也是数据,会占用磁盘空间
- 索引的创建和维护也需要时间成本
- 进行删除、更新和插入操作时,因为要维护索引,所以速度会降低。
我们会发现相比优点来说,索引的缺点更多,那么为什么索引还这么重要呢?
因为大多数系统对查询的需求要远远超过其他操作的需求,所以查询性能的提升是首要大事
使用索引的语法
创建索引
- 建表的同时创建索引
create table 表名
(
字段名 类型,
...
字段名 类型,
index 索引名称 (字段名)
);
- 建表后添加索引
alter table 表名 add index 索引名(字段名);
或
create index 索引名 on 表名(字段名);
删除索引
alter table 表名 drop index 索引名;
或
drop index 索引名 on 表名;
查看表中的索引
show index from 表名;
索引的分类
索引按功能分为:
- 普通索引,在普通字段上建立的索引,没有任何限制
- 主键索引,创建主键时,自动创建的索引,不能为空,不能重复
- 唯一索引,建立索引的字段数据必须是唯一的,允许空值
- 全文索引,在大文本类型(Text)字段上建立的索引
- 组合索引,组合多个列创建的索引,多个列不能有空值
建表同时添加索引
-- 创建书籍表
create table tb_book
(
-- 创建主键索引
id int primary key,
-- 创建唯一索引
title varchar(100) unique,
author varchar(20),
content Text,
time datetime,
-- 普通索引
index ix_title (title),
-- 全文索引
fulltext index ix_content(content),
-- 组合索引
index ix_title_author(title,author)
);
建表后添加索引
-- 建表后添加主键索引
ALTER TABLE tb_book ADD PRIMARY KEY pk_id(id);
-- 建表后添加唯一索引
ALTER TABLE tb_book ADD UNIQUE index ix_title(title);
-- 建表后添加全文索引
ALTER TABLE tb_book ADD FULLTEXT index ix_content(content);
-- 查询时使用全文索引
SELECT * FROM tb_book MATCH(content) ANGAINST(‘胜利’);
-- 建表后添加组合索引
ALTER TABLE tb_book ADD INDEX ix_book(title,author);
注意:创建组合索引时,要遵循”最左前缀”原则,把最常查询、排序的字段放左边,按重要性依次递减。
索引的使用策略
什么情况下要建立索引
- 在经常需要查询和排序的字段上建立索引
- 数据特别多
什么情况下不要建立索引
- 字段数据存在大量的重复,如:性别
- 数据很少
- 经常需要增删改的字段
什么情况下索引会失效
- 模糊查询时,使用like ‘%张%’会失效,而like ‘张%’不会
- 使用is null或is not null查询时
- 使用组合索引时,某个字段为null
- 使用or查询多个条件时
- 在函数中使用字段时,如where year(time) = 2019
Explain语句
在调试查询语句时,要多用explain来查看索引使用情况
create index ix_city_name on city(name);
explain select * from city where name = 'London';
explain select * from city where name like 'Lon%';
explain select * from city where name like '%don%';
上面代码中,在city表的name字段上建立了索引,使用 'name = ’ 查询时,索引是正常的
使用 name like ‘Lon%’ 查询时,索引也能生效,type为range 范围查询
使用 name like ‘%don%’ 查询时,索引失效了,type为ALL 全表扫描
索引的结构
不同的存储引擎使用不同结构的索引:
- 聚簇索引,InnoDB支持,索引的顺序和数据的物理顺序一致,类似新华字典中的拼音目录排列和汉字排列顺序一致,聚簇索引一个表中只能有一个。
- 非聚簇索引,MyISAM支持,索引顺序和数据的物理顺序不一致,类似新华字典中的偏旁部首目录和汉字排列顺序不一致,非聚簇索引表可以有多个。
索引的数据结构
索引的数据结构常用的是:B-Tree和B+Tree
B-Tree
B-Tree的数据结构如下,是一种平衡搜索多叉树,每个节点由key和data组成,key是索引的键,data是键对应的数据,在节点的两边是两个指针,指向另外的索引位置,而所有的键都是排序过的,这样在搜索索引时,可以使用二分查找,速度比较快,时间复杂度是h*log(n),h是树的高度,B-Tree是一种比较高效的搜索结构。
B+Tree
B+Tree的数据结构如下,是BTree的升级版,区别是非叶子节点不在存储具体的数据,只保存索引的键,数据保存到叶子节点中,并且叶子节点中没有指针只有键和数据。
B+Tree的优点是:搜索效率更高,因为非叶子节点中没有保存数据,就可以保存更多的键,每一层的键越多,树的高度就会减少,这样查询速度就会提升。
总结
索引是提高查询速度的重要手段,本章我们学习了索引的分类和创建语法,以及使用索引的策略,不是所有的表都适合创建索引,最后我们还学习了索引的内部结构,这样大家对索引会有一个基本的认识。
觉得本文对你有用的话,麻烦点个赞:)
大家如果需要学习其他Java知识点,戳这里 超详细的Java知识点汇总