MySQL学习笔记---索引

最新推荐文章于 2024-10-10 21:13:48 发布

hahalan

最新推荐文章于 2024-10-10 21:13:48 发布

阅读量115

点赞数

分类专栏： MySQL学习笔记文章标签： mysql

本文链接：https://blog.csdn.net/hahalan/article/details/119758599

版权

MySQL学习笔记专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1. 既然索引能够提高查询效率，那是不是越多越好？

2.什么叫回表

3.最左前缀原则

总结

前言

MySQL中，可以分为Server层和存储引擎层。
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。不特殊注明情况下，默认使用的存储引擎是：InnoDB

一、什么是索引

索引，是存储引擎用于快速找到记录的一种数据结构。

二、为什么需要索引

提高数据查询效率。比如书的目录，它就是一个简单的索引，可帮助快速定位到对应章节内容。

如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。

三、索引类型

1.主键索引（primary key）

简称主键。可由一个或多个列组成，用于唯一性标识数据表中的某一条记录。每个表只能存在一个主键。

InnoDB引擎下，如果建立的表没有创建主键，选择首个非空唯一列作为主键。如果不存在这样的列，那么InnoDB会自动创建一个不可见的、bigint unsigned类型的row_id，长度为6字节。每次插入一条数据时都会让全局row_id加一。全局row_id一直涨，直到涨到2的48次幂-1时，这时候再加一就会让低48位的row_id都为0，此时如果再插入一条数据，它拿到的row_id就是0，这样的话就有可能存在主键冲突。所以为了避免这种隐患，每个表都需要一个主键。

主键特点：

不能为空
不能重复
很少改变
经常被检索的列
不是太长的列：因为普通索引叶子节点会存储主键值，如果主键值太长，会增加普通索引的大小

InnoDB引擎下，主键设计通常遵循的原则：

采用一个没有业务用途的自增属性列作为主键
主键字段值总是不更新，只有新增或者删除两种操作
不选择会动态更新的类型，比如当前时间戳等

2.唯一索引（unique key）

唯一索引：索引列的所有值都只能出现一次，即必须唯一。允许为null
主键索引一定是唯一索引，反过来则不成立。唯一索引可以在单张表中存在多个，而主键在一张表中只会有一个，和主键相比，唯一索引允许空值，允许多个字段为null。

3.普通索引（normal）

这是最基本的索引类型，而且它没有唯一性之类的限制。

4.全文索引（full text）

FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
旧版的MySQL的全文索引只能用在MyISAM表格的char、varchar和text的字段上。
不过新版的MySQL5.6.24上InnoDB引擎也加入了全文索引。

四、索引模型/结构

InnoDB使用B+树作为索引模型/结构，每一个索引在InnoDB中对应一棵B+树。

其中，主键索引的叶子节点存储的是整行数据，非主键索引的叶子节点存储的则是主键的值。

主键索引也被称为聚簇索引（clustered index），非主键索引也被称为二级索引（secondary index）。

五、关于索引的一些问题

1. 既然索引能够提高查询效率，那是不是越多越好？

不是。
首先，索引要占用磁盘空间。如果创建的是一些大字段甚至每一种可能列组合的索引，索引文件体积的增长速度远超过数据文件本身。
其次，增删改操作中，过多索引的维护反而会降低性能。在这一过程中，不仅需要更改数据文件，还需要再次将改动写入索引，重新维护索引文件。

2.什么叫回表

从非主键索引树，回到主键索引树搜索的过程叫回表。
减少回表的一个手段是：覆盖索引
比如原sql:select * from tableA where normal_key='3'的检索过程为：先去普通索引树找到主键ID，再去主键树查找数据。
如果只是需要查询主键ID，那么直接改成select ID from tableA where normal_key='3'就不会有回表过程。

3.最左前缀原则

最左前缀的两个最好例子是：联合索引和最左查询
比如创建了联合查询（a,b,c）实际上，相当于创建了索引(a),(a,b),(a,b,c)。联合索引的建立，需要根据查询情景好好考虑字段的优先放置顺序。
比如select * from tableA where key like "ab%"中，如果key建立了索引，那么此时可以用到索引。但是select * from tableA where key like "%ab"则用不到key的索引树。