数据库：MySQL索引详解

最新推荐文章于 2024-07-16 16:20:54 发布

硕～

最新推荐文章于 2024-07-16 16:20:54 发布

阅读量451

点赞数

分类专栏：数据库文章标签：索引数据库 mysql 恰饭经验分享

本文链接：https://blog.csdn.net/qq_42441693/article/details/104951510

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

文章目录

一、索引问题

我们之前对索引操作有过介绍：如果不了解，可以先看看这篇文章。
数据库：Mysql架构、索引、锁机制、事务、存储引擎

今天我们再来看一看它们，这里有几个问题：

1.1索引查询与非索引查询

索引查询：这是我们在id字段建立了一个索引，create index 表名 on 表名.字段名；索引的创建就是在MyISAM表中建立一个B+树这种数据结构，只需要在B+树的叶子上存储数据的地址。通过索引项拿到索引值，拿索引值再去叶子节点上拿到数据的地址获取完整的数据。
在这里插入图片描述
非索引查询：顺序遍历，O(n)。如果我们使用这条SQL语句：select * from stu where name = “张三”;此时表中只给id字段建立了索引，如何处理name这个字段。只能通过顺序遍历，通过叶子结点找到数据，将数据的name取出来再与"张三比较"，没有用到索引。

1.2辅助索引与主索引

我们采用InnoDB的方式在id上已经建立了一个索引，现在我们在name上再建立一个索引，假设id字段建立的索引为i1，name字段建立的索引为i2，那么会有什么问题？
InnoDB中存储数据的索引称为主索引，除了主索引以外的索引称为辅助索引。 基于name字段建立的新索引，在叶子结点上不能存放数据了，否则会出现：①数据冗余；②会导致处理数据出现错误。 例如：update stu set age = xx where name = “xxx”；在id中存放一份数据，在name中也存放一份数据，按name来找，修改的是name备份中的数据，id中的数据没有发生变化。

InnoDB主索引： 存储数据的索引。
InnoDB辅助索引： 在叶子结点除了储存索引值，还存储了主索引的索引值，辅助索引会遍历2次。
1.查询辅助索引，找主索引的索引值。
2.查询主索引，找到索引值。
在这里插入图片描述
InnoDB中的主索引与辅助索引我们了解了，那么我们来看一看MyISAM中的主索引与辅助索引。
MyISAM主索引： 索引值不重复的索引。
MyISAM辅助索引： 索引值允许重复，辅助索引只查询一次。

例如：我们这里的id字段与id-card字段建立的索引就是主索引。
在这里插入图片描述

1.3索引的优化

为什么建一张表时不能让系统自动给所有字段添加索引？
索引好处能提高查询效率，但是所有也有弊端。索引本质为一个数据结构，数据结构占空间，还要占用一定空间来管理这个字段。假如我们给每个字段都创建一个索引，若某个字段查询次数较少时，占用空间，效率并没有提高。这就需要对索引进行优化了。

索引的优化：
1.哪种情况下应该建立索引？
在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

2.哪种情况下不应该建立索引？
第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求;第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度;第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少;第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

二、索引使用

2.1索引分类

1.普通索引： 这是最基本的索引，它没有任何限制， MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。
2.唯一索引： 与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。
3.全文索引(FULLTEXT)： MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
4.单列索引、多列索引： 多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。
5.组合索引(最左前缀原则): 平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。

2.2索引创建

1.直接创建索引
create index index_name on tablename(column_name);

2.建表时指定索引
create table tablename(index index_name(column_name));

3.修改添加索引
alter table tablename ADD index index_name on column_name;

索引命令的使用这篇文章给的很全,就不写了：MySQL添加索引命令

2.2使用实例

1.先建立一张表：
我们先建立一张表并查看它的信息，再插入两条数据。存储引擎的设立是基于表的，一个库下可能有很多表，每个表用的存储引擎可能不同；索引的设立是基于字段的。
在这里插入图片描述
2.查看索引：show index from 表名；
我们还没有创建索引，查看一下索引。test表中基于id已经存在了一个主键索引是系统创建的。

3.查看执行时是否使用了索引：explain select * from表名 where 字段名 = 字段；
我们可以使用explain语句查看这条sql语句执行时是否使用了索引。我们发现使用了主键索引。
在这里插入图片描述
这个查询则没有使用到索引，非索引查询。

4.创建索引：create index 索引名 on 表名(字段名)；