MYSQL索引-索引的简单应用

最新推荐文章于 2021-06-27 20:47:17 发布

IT求学人

最新推荐文章于 2021-06-27 20:47:17 发布

阅读量250

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/mihoutaoxiaozi/article/details/109462126

版权

MYSQL 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

MYSQL索引-索引的简单应用

一、索引

索引是一种特殊的数据结构，类似于图书的目录，它能够极大地提升数据库的查询效率。如果没有索引，在查询数据时必须扫描表中的所有记录才能找出符合条件的记录，这种全表扫描的查询效率非常低。

类似书本的目录、楼层索引等等，都是索引

二、索引的分类

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定记录。

数据库的索引好比一本书的目录，能够加快数据库的查询速度；

索引是快速搜索的关键，如果不加索引，查找任何一条特定的数据都会进行一次全表扫描。

在这里插入图片描述

三、索引的用法

1、创建索引

创建普通索引：

CREATE INDEX indexName ON tableName(columnName(length));

创建唯一索引：

CREATE UNIQUE INDEX indexName ON tableName(columnName(length));

创建复合索引：

CREATE INDEX indexName ON tableName(columnName1, columnName2, …);

2、删除索引

DROP INDEX [indexName] ON tableName;

3、查看索引

SHOW INDEX FROM tableName;

四、实例

注意：

在这里插入图片描述

1、查看表的索引

show index from mydb\G; (\G格式化输出结果)

在这里插入图片描述

可以看到存在索引 id 主键

2、新增一个索引

create index id_name on employee(name):

show index from employee;

在这里插入图片描述

可以看到表employee新增了一个索引：id_name 对name 的索引

我们通过新建的索引id_name 来查询：

explain select * from employee where name=“张三”\G;

在这里插入图片描述

我们发现加了索引之后，查询类型变了。不再是ALL；且扫描表中的行数为1行，选择行数占比 100%。后面我们和删除name索引后查询做对比！

3、删除一个索引

drop index id_name on employee;

show index from employee;

在这里插入图片描述

可以发现删除后，查看索引就只有一条所以记录了，我们在看看现在通过name来查询的情况！

explain select * from employee where name=“张三”\G;

在这里插入图片描述

可以看到。删除了name列的索引后，查询张三时，扫描了全表扫描，扫描了13次。获取了其中的一条，占比 10%。

查询类型为:type:ALL.查询效率降低了。

从这里可以看到，如果是大体量的数据查询的话，在存在索引的情况下，查询效率会大大提升！

五、复合索引

1、什么是复合索引？

两个或更多个列上的索引被称作复合索引。

利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。
所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

2、复合索引的前导列特性

在MySQL中，如果创建了复合索引(name, salary, dept)，就相当于创建了(name, salary, dept)、 (name, salary)和(name)三个索引，这被称为复合索引前导列特性，因此在创建复合索引时应该将最常用作查询条件的列放在最左边，依次递减。

未使用索引

select * from employee where salary=8800;

select * from employee where dept=‘部门A’;

select * from employee where salary=8800 and dept=‘部门A’;

使用索引 --使用索引后，查询效率提升

select * from employee where name=‘张三’;

select * from employee where name=‘张三’ and salary=8800;

select * from employee where name=‘张三’ and salary=8800 and dept=‘部门A’;

3、复合索引使用

创建复合索引应当包含少数几个列，并且这些列经常在select查询里使用。在复合索引里包含太多的列不仅不会给带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值，其后果是内存溢出和性能降低。

复合索引对排序的优化：

复合索引只对和索引中排序相同或相反的order by 语句优化。
在创建复合索引时，每一列都定义了升序或者是降序。如定义一个复合索引：

CREATE INDEX idx_example
ON table1 (c11 ASC, c12 DESC, c13 ASC) ；

执行查询语句：
1：Select c1, c2, c3 from table1 order by c1 ASC, c2 DESC, c3 ASC
排列顺序和索引顺序相同
2：Select c1, c2, c3 from table1 order by c1 DESC, c2 ASC, c3 DESC
排列顺序和索引顺序相反
查询1，2 都可以别复合索引优化。
如果查询为：
Select c1, c2, c3 from table1 order by c1 ASC, c2 ASC, c3 ASC
排序结果和索引完全不同时，此时的查询不会被复合索引优化。

4、where语句中的使用

在where字句中使用复合索引时，必须满足前导列特性才会被复合索引优化：

在上面的表中：

SELECT * FROM db WHERE c1 = val1
SELECT * FROM db WHERE c1 = val1 and c2 = val2
SELECT * FROM db WHERE c1 = val1 and c2 = val2 AND c3 = val3

会被复合索引优化，而下面的语句，不会被索引优化查询：

SELECT * FROM db WHERE c3 = val3
SELECT * FROM db WHERE c2 = val2
SELECT * FROM db WHERE c2 = val2 and c3=val3

也就是说，索引的组合方式必须是：A、A,B、A,B,C 这样才行！