玩转MySQL（六）索引_采用name like ... 的方式查找,索引会起作用么。

超详细的Java知识点路线图

---

---

前言

本章我们将学习MySQL中的索引，本文将从索引的作用、索引的分类、创建索引的语法、索引的使用策略以及索引的实现原理等方面带大家了解索引。

索引的作用

索引的作用就是加快查询速度，如果把使用了索引的查询看做是法拉利跑车的话，那么没有用索引的查询就相当于是自行车。目前实际项目中表的数据量越来越大，动辄上百万上千万级别，没有索引的查询会变得非常缓慢，使用索引成为了查询优化的必选项目。

索引的概念

我们去一栋大楼找一家公司，不可能一层层的去找，而是先看楼层索引，然后直接到公司所在楼层就可以了。
数据库索引其实是一种特殊的数据，也保存在数据库文件中，索引数据保存着数据表中实际数据的位置，通过查找索引就能直接找到实际数据的位置，从而加快查找速度。
我们来看一段查询语句：

select * from book where id = 1000000;
1

假设书籍表中有几百万行数据，没索引的查询会遍历前面的100万行数据找到结果，如果我们在id上建立索引，则直接在索引上定位结果，速度要快得多。

索引的优缺点

优点

1. 提高查询速度

缺点

1. 本身也是数据，会占用磁盘空间
2. 索引的创建和维护也需要时间成本
3. 进行删除、更新和插入操作时，因为要维护索引，所以速度会降低。

我们会发现相比优点来说，索引的缺点更多，那么为什么索引还这么重要呢？
因为大多数系统对查询的需求要远远超过其他操作的需求，所以查询性能的提升是首要大事

使用索引的语法

创建索引

1. 建表的同时创建索引

create table 表名
(
字段名 类型,	
...
字段名 类型,
index 索引名称 (字段名)
);
1
2
3
4
5
6
7

2. 建表后添加索引

alter table 表名 add index 索引名(字段名);
1

或

create index 索引名 on 表名(字段名);
1

删除索引

alter table 表名 drop index 索引名;
1

或

drop index 索引名 on 表名;
1

查看表中的索引

show index from 表名;
1

索引的分类

索引按功能分为：

1. 普通索引，在普通字段上建立的索引，没有任何限制
2. 主键索引，创建主键时，自动创建的索引，不能为空，不能重复
3. 唯一索引，建立索引的字段数据必须是唯一的，允许空值
4. 全文索引，在大文本类型（Text）字段上建立的索引
5. 组合索引，组合多个列创建的索引，多个列不能有空值

建表同时添加索引

-- 创建书籍表
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)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

建表后添加索引

-- 建表后添加主键索引
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)；
1
2
3
4
5
6
7
8
9
10

注意：创建组合索引时，要遵循”最左前缀”原则，把最常查询、排序的字段放左边，按重要性依次递减。

索引的使用策略

什么情况下要建立索引

1. 在经常需要查询和排序的字段上建立索引
2. 数据特别多

什么情况下不要建立索引

1. 字段数据存在大量的重复，如：性别
2. 数据很少
3. 经常需要增删改的字段

什么情况下索引会失效

1. 模糊查询时，使用like ‘%张%’会失效，而like ‘张%’不会
2. 使用is null或is not null查询时
3. 使用组合索引时，某个字段为null
4. 使用or查询多个条件时
5. 在函数中使用字段时，如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%';
1
2
3
4

上面代码中，在city表的name字段上建立了索引，使用 'name = ’ 查询时，索引是正常的

使用 name like ‘Lon%’ 查询时，索引也能生效，type为range 范围查询

使用 name like ‘%don%’ 查询时，索引失效了，type为ALL 全表扫描

索引的结构

不同的存储引擎使用不同结构的索引：

1. 聚簇索引，InnoDB支持，索引的顺序和数据的物理顺序一致，类似新华字典中的拼音目录排列和汉字排列顺序一致，聚簇索引一个表中只能有一个。
2. 非聚簇索引，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知识点汇总