Mysql的索引详解

零. 索引类型概述

1. 实际开发中使用的索引种类

  • 主键索引
  • 唯一索引
  • 普通索引
  • 联合索引
  • 全文索引
  • 空间索引

2. 索引的格式类型

  • BTree类型
  • Hash类型
  • FullText类型(全文索引)
  • RTree类型(空间索引)

MySQL 的索引方法,主要包括 BTREE 和 HASH
顾名思义,BTREE 方法,就是通过构建 B+ 树的方法来组织索引结构;而 HASH 方法,就是通过构建哈希表的方法来组织索引结构。

3. 以索引数据存储方式划分

  • 聚集索引
  • 非聚集索引

4. 以技巧划分

  • 覆盖索引
  • 前缀索引

也有人把聚集索引叫做聚簇索引

一、索引介绍

索引是一种数据结构,索引的作用主要是为了提高检索效率,添加一个索引需要选择一个字段作为索引依据 

索引数据格式主要有两种

  1. Hash
  2. B+Tree

在实际开发中,其实用的最多的是B+Tree数据类型,因为hash数据格式是不支持范围查询的,而基于二叉树数据结构的B+Tree可以支持范围查询(注:B+Tree是B树的进化版)

拓展

(1)Mysql索引为什么使用B+Tree数据结构?

从计算机原理出发,是因为索引本身消耗就很大,不可能全部储存到内存中,因此索引往往索引文件的形式存储在磁盘上,那这样话,磁盘IO效率就会起决定性的作用。

(2)全文索引和空间索引是什么?

二、索引类型

1、普通索引

普通索引是最基本的索引,也是最常用的索引,创建只是为了提高查询效率,值可以为空,没有任何限制

alter table 表名 add index 索引名(添加索引的字段)

2、唯一索引

唯一索引和普通索引类似,值也可以为空,唯一区别是添加的字段是唯一性,列值不允许重复

alter table 表名 add unique 索引名(添加索引的字段)

3、主键索引

主键索引是一种特殊的唯一索引, 值不允许为空 和 列值不允许重复,且一张表只允许有一个主键索引

alter table 表名 add primary key (添加索引的字段)

4、联合索引

联合索引,顾名思义,就是多个索引联合在一起组成的一种索引类型。

//索引名可以是index_xx_xx
alter table 表名 add index 索引名(索引1,索引2,索引3)

5. 关于联合索引的拓展

(1)最左前缀匹配原则

建立联合索引时会遵循最左匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配

例如🌰

在user表中为 name、address、phone 三个字段添加联合索引

ALTER TABLE user ADD INDEX index_three(name,address,phoen)

下面三条sql语句都能命中索引

SELCET * FROM user WHERE address =’ 北京’ AND phone = ‘12345’ AND name = ‘张三’;SELCET * FROM user WHERE name = ‘张三’ AND address = ‘北京’;SELCET * FROM user WHERE name = ‘张三’;

 这三条sql语句都会匹配联合索引,按顺序是 (name,address,phone) 、(name,address) 、(name),其实使用联合索引时可以不用按照建索引时候的顺序,如使用and条件,因为执行sql语句时,MYSQL的优化器会自动帮我们调整where条件中的顺序,但是,使用联合索引必须使用建索引的第一个字段name,即最左边的字段,否则索引无效,如下:

SELCET * FROM user WHERE address = ‘北京’;SELCET * FROM user WHERE address = ‘北京’’ AND phone = ‘12345’ ;

 以上联合索引都会失效,可以总结一句话:用到最左边的字段,就算成功

(2)联合主键索引

上面说到了主键索引,其实主键也可以由多个字段组成

🌰借丁奇大佬的例子

DBA 小吕在入职新公司的时候,就发现自己接手维护的库里面,有这么一个表,表结构定义类似这样的:


CREATE TABLE `user` ('a' int(11) NOT NULL,'b' int(11) NOT NULL,'c' int(11) NOT NULL,'d' int(11) NOT NULL,PRIMARY KEY ('a','b'),KEY 'c' ('c'),KEY 'ca' ('c','a'),KEY 'cb' ('c','b')
) ENGINE=InnoDB;

公司的同事告诉他说,由于历史原因,这个表需要 a、b 做联合主键,这个小吕理解了。但是,学过本章内容的小吕又纳闷了,既然主键包含了 a、b 这两个字段,那意味着单独在字段 c 上创建一个索引,就已经包含了三个字段了呀,为什么要创建“ca”、“cb”这两个索引?同事告诉他,是因为他们的业务里面有这样的两种语句:

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

对于一般不太了解索引数据结构的人来说,会觉得上面的建的索引是没有问题的,但实际上面有一个错误,索引 “ca” 是多余的

这是为什么?查询 where c = N order by a 难道不需要 “ca” 索引吗?

答案是需要的,不过需要明白的是,索引C是普通索引,C索引树上存放的是联合主键 (ab)的值,所以C索引树叶节点存放的值是(cab),根据最左前缀原则,就没必要再建(ca)索引了。但是(cb)索引还是需要的,在cb索引中,实际上是(cba)。

使用到了最左前缀原则聚集索引相关的知识点

三、聚集索引和非聚集索引

1、非聚集索引

索引和表数据分开存储,就是非聚集索引(也有叫二级索引或辅助索引)。

非聚集索引一般就是指MyIsam引擎的索引,因为MyIsam引擎的索引和数据是分开储存的,如下图显示,MyIsam的索引B+Tree下data域储存的是数据地址,每次查询索引时,都是先从索引树上获取数据地址,在通过地址获取对应的数据 。所以,MyIsam表会有三个文件,分别是索引文件、表结构文件、数据文件。

2、聚集索引

索引和表数据存储在一起,就是聚集索引。

以InnoDB引擎举例,InnoDB引擎的主键是和表数据存储在一起,每个树节点储存每行一行的数据。如果有看过InnoDB引擎的表文件,就会发现,其实InnoDB表数据文件就是主索引文件,如下图的Innodb表数据二叉树显示,B+Tree数结构的主键索引,每个树节点主键下的data域,都是存放对应该主键的行数据。所以,InnoBD表数据文件本身就是一个B+树文件。InnoBD表一共有两个文件,分别是索引文件、表结构文件。

需要注意,InnoDB引擎除了主键索引,其他的索引的data域都是存主键索引的值,使用辅助查询时,是先通过辅助索引获取到主键,在通过主键获取主键索引B+树里的data域里的数据。总结:Innodb引擎表主键索引树叶节点存储:每行的数据;辅助索引树叶节点存的是:字段值和主键值。 

3、实际开发聚集索引的不同

🌰先建个Innodb引擎的user表,表中有两个索引,一个是主键索引(聚集索引)ID,和另一个普通索引(非聚集索引)index_cardId 身份证ID

CREATE TABLE `user` ( `ID` INT NOT NULL AUTO_INCREMENT,  `cardId` VARCHAR(50) NOT NULL,  `name` VARCHAR(20) NOT NULL,`age` VARCHAR(10) NOT NULL,PRIMARY KEY (`ID`),KEY 'index_cardId' ('cardId') USING BTREE
)engine=InnoDB;

假如有一条语句:

SELECT * FROM user WHERE ID = 100;

即用主键的查询方式,搜索了ID这个索引的B+树,只需直接从这颗B+树中找到对应的ID获取到对应行的数据就可以。

如果用 index_cardId 普通索引查询:

SELECT * FROM user WHERE cardId = ‘43212341234’;

则需要搜索可 index_cardId 这个索引树,从index_cardId 这颗索引树上获取到主键索引的值,在通过值从主键索引的B+树上获取对应的数据,也就是说,非主键索引的查询需要多扫描了一棵索引树,这个过程我们称为回表

所以,在实际开发中,我们应尽量使用主键查询

四、覆盖索引

首先要明白,索引的使用过程,假如查询一条sql语句:

select col1 from test where col1=1;

假如test表有1000W条数据,在col1字段建了一个普通索引,然后sql查询时,会通过col1索引筛选出符合条件的200W条数据,而且能在col1索引上获取col1字段的数据,无需回表查询,直接返回,这就是覆盖索引。

反之,如果在上面的sql语句查询的字段在加一个col2字段

select col1,col2 from test where col1=1;

即使col1索引获取col1字段的值,但是因为没有col2字段的值,所以还需要回表查询,再获取200W条数据字段col2的值,才返回。那这样就不是覆盖索引了。

实际开发中覆盖索引的价值

实际开发中,优化查询的时候是否回表,是优化的重要目标,所以使用索引的时候,应该尽量使sql语句可以从索引中获取到需要的数据,例如上面的例子,可以增加col2字段的索引,这样就可以避免回表查询。

但是,如果查询的字段很多,难道就建很多的索引吗?

要知道,索引也是有开销的,首先,索引是以空间换取时间的,如果索引建的越多,占用的磁盘空间就越多,其次,每次对数据库进行写操作,索引也会进行相应的更新,也会影响数据库写操作的效率,总的来说,浪费了很多服务器的资源,所以索引需要合理的使用,以上的例子为例,我们可以建(co1,col2)的联合索引,这样查询的时候就可以通过(col1,col2)联合直接获取col1和col2字段的数据,直接返回。

覆盖索引是优化索引的重要方式之一

课外拓展-前缀索引

🌰举个例子,先建个表

CREATE TABLE `user` ( `ID` INT NOT NULL AUTO_INCREMENT,  `Name` VARCHAR(50) NOT NULL,  `City` VARCHAR(50) NOT NULL,  `Age` INT NOT NULL,  PRIMARY KEY (`ID`)  
);

为了加快查询速度,我们给这个表加一个联合索引

ALTER TABLE  user ADD INDEX 'name_city_name' (Name(10),City,Age)

仔细看就会发现在Name字段加了10长度限制,这就是前缀索引。Name字段长度是50,为什么用10呢?因为一般情况下名字长度不会超过10,这样不仅加快索引查询速度,还会减少索引文件的大小提高更新数据的效率。 

五、索引使用建议

  • 对较小的数据列使用索引
    上面有提到过,索引文件会存储数据,所以选择数据量少的字段使用索引可以节省不必要的磁盘空间和内存的消耗

  • 对 where、on、group by、order by 使用索引

  • 对较长的字符串使用前缀索引

  • 不要创建过多索引

  • 多使用联合索引
    在阿里云开发手册里,有一条要求是建表索引第一优先是使用联合索引。主要有一下原因:

    (1)减少开销
    例如,建立一个联合索引(col1,col2,col3),实际相当于建(col1),(col2),(col3)三个索引。索引并不建的越多越好,每次对数据库进行写操作的时候,都需要对索引进行更新,所以建一个合理联合索引,可以节省数据库写和磁盘空间的开销。

    (2)覆盖索引
    建了一个联合索引(col1,col2,col3),假如查询sql:select col1,col2,col3 from test where col1=1 and col2=2 ,那么数据库可以通过索引直接获取到数据,无需回表查询,减少了io操作。所以在实际开中,多使用联合索引以达到覆盖索引的效果,是很有效的优化手段之一。

    (3)增加查询效率
    索引的利用效率更高,假如数据表有1000W条数据,有一条sql查询语句:select * from test where col1=1 and col2=2 and col3=3 ,假设可以通过每个索引查询条件筛选10%的数据

    第一种,如果只有一个普通索引col1,只能通过索引筛选出1000W * 10%=100W 条数据,然后在回表查找符合 col2 = 2 和 col3 =3 的数据,这样结果是回表扫描100W条数据。

    第二种,如果是一个联合索引 (col1,col2,col3),能通过索引筛选出 1000W*10%*10%*10% = 1W 条数据,然后在回表查找表其他字段数据,效率的差别可想而知。

六、索引使用的要避免的坑

  • 隐式转换问题(索引字段类型使用不准确,mysql会自动转换字段类型,从而导致索引失效)
  • 对索引进行计算 (例如 age + 10 = 30 )
  • 对索引使用函数运算 ( 例如 LEFT(date,4) )
  • 正则表达式不使用索引(例如模糊查询%xx ,注意, xx% 是走索引的)
  • or条件必须全部字段建立索引(少使用 or 条件)

七、索引的弊端

不要盲目的创建索引,只为查询操作频繁的列创建索引,创建索引会使查询操作变得更加快速,但是会降低增加、删除、更新操作的速度,因为执行这些操作的同时会对索引文件进行重新排序或更新。但是,在互联网应用中,查询的语句远远大于DML的语句,甚至可以占到80%~90%,所以也不要太在意,只是在大数据导入时,可以先删除索引,再批量插入数据,最后再添加索引。

 

 

 

 

 

 

 

 

 

 

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/99273.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

部门来了个测试开发,听说是00后,上来一顿操作给我看呆了...

公司新来了个同事,听说大学是学的广告专业,因为喜欢IT行业就找了个培训班,后来在一家小公司实习半年,现在跳槽来我们公司。来了之后把现有项目的性能优化了一遍,服务器缩减一半,性能反而提升4倍&#xff01…

不含数字的webshell绕过

异或操作原理 1.首先我们得了解一下异或操作的原理 在php中,异或操作是两个二进制数相同时,异或(相同)为0,不同为1 举个例子 A的ASCII值是65,对应的二进制值是0100 0001 的ASCII值是96,对应的二进制值是 0110 000…

【STM32+ESP8266上云连载①】给ESP8266烧录AT固件

文章目录 一、给NodeMCU烧录固件1.1硬件准备1.2软件准备1.3AT固件下载1.4配置设置1.5开始烧录 二、给ESP8266-01S烧录固件2.1硬件准备2.2AT固件下载2.3连线2.4烧录配置 三、给ESP-12E/F/S单片烧录固件四、指令测试4.1HTTP测试4.2MQTT测试 我在使用ESP8266的时候遇到了一些问题&…

语法篇--XML数据传输格式

一、XML概述 1.1简介 XML,全称为Extensible Markup Language,即可扩展标记语言,是一种用于存储和传输数据的文本格式。它是由W3C(万维网联盟)推荐的标准,广泛应用于各种系统中,如Web服务、数据…

jmeter-results-detail-report_new.xsl文件设置dateReport

<!-- Defined parameters (overrideable) <td bgcolor"#ff00ff"> --> <xsl:param name"showData" select"y"/> <xsl:param name"titleReport" select"测试报告"/> <xsl:param name&…

【UniApp开发小程序】商品详情展示+评论、评论展示、评论点赞+商品收藏【后端基于若依管理系统开发】

文章目录 界面效果界面实现工具js页面日期格式化 后端收藏ControllerServicemapper 评论ControllerServiceMapper 商品Controller 阅读Service 界面效果 【说明】 界面中商品的图片来源于闲鱼&#xff0c;若侵权请联系删除 【商品详情】 【评论】 界面实现 工具js 该工…

Spring中循环依赖解决方案

循环依赖 循环依赖是Spring框架中常见的问题之一&#xff0c;当两个或多个类相互引用对方时&#xff0c;就会出现循环依赖的情况。这种情况下&#xff0c;Spring框架无法确定哪个类应该先实例化和初始化&#xff0c;从而导致异常。常见的解决方法有&#xff1a;构造函数注入、s…

SpringBoot+微信小程序奶茶在线点单小程序系统 附带详细运行指导视频

文章目录 一、项目演示二、项目介绍三、运行截图四、主要代码 一、项目演示 项目演示地址&#xff1a; 视频地址 二、项目介绍 项目描述&#xff1a;这是一个基于SpringBoot微信小程序框架开发的奶茶在线点单小程序系统。首先&#xff0c;这是一个前后端分离的项目&#xff…

基于springboot+vue的博物馆藏品平台(前后端分离)

博主主页&#xff1a;猫头鹰源码 博主简介&#xff1a;Java领域优质创作者、CSDN博客专家、公司架构师、全网粉丝5万、专注Java技术领域和毕业设计项目实战 主要内容&#xff1a;毕业设计(Javaweb项目|小程序等)、简历模板、学习资料、面试题库、技术咨询 文末联系获取 项目介绍…

Harvard transformer NLP 模型 openNMT 简介入门

项目网址&#xff1a; OpenNMT - Open-Source Neural Machine Translation logo&#xff1a; 一&#xff0c;从应用的层面先跑通 Harvard transformer GitHub - harvardnlp/annotated-transformer: An annotated implementation of the Transformer paper. ​git clone https…

Android Studio导入项目需要做的一些配置

点击项目结构 选择本地安装的SDK、NDK目录 选择java版本 重新加载项目 Clean Project Rebuild Project 选择要构建的版本 可选debug和release 打包apk安装包 打包完成&#xff0c;就可以安装到安卓手机了

matlab 计算点云平均密度

目录 一、算法原理二、代码实现三、结果展示四、C++版计算结果本文由CSDN点云侠原创,爬虫自重。如果你不是在点云侠的博客中看到该文章,那么此处便是不要脸的爬虫。 一、算法原理 采样设备不同、设备距离场景远近不同,会使点云密度产生差异。现有的对点云密度的估算方法有基…

「UG/NX」Block UI 体收集器BodyCollector

✨博客主页何曾参静谧的博客📌文章专栏「UG/NX」BlockUI集合📚全部专栏「UG/NX」NX二次开发「UG/NX」BlockUI集合「VS」Visual Studio「QT」QT5程序设计「C/C+&#

js中的正则表达式(一)

目录 1.什么是正则表达式 2.正则表达式在JavaScript中的使用场景: 3.正则表达式的语法&#xff1a; 1.什么是正则表达式 正则表达式(Regular Expression&#xff09;是用于匹配字符串中字符组合的模式。在JavaScript中&#xff0c;正则表达式也是对象通常用来查找、替换那些符…

TCP/IP协议组

TCP/IP通信协议是目前最完整、使用最广泛的通信协议。它的魅力在于可使不同硬件结构、不同操作系统的计算机相互通信。TCP/IP协议既可用于广域网&#xff0c;也可用于局域网&#xff0c;它是Internet/Intranet的基石。TCP/IP通信协议事实上是一组协议。 TCP/IP协议可分为5层也可…

阿里云ECS服务器企业级和共享型介绍_企业级常见问题解答FAQ

阿里云企业级服务器是什么&#xff1f;企业级和共享型有什么区别&#xff1f;企业级服务器具有独享且稳定的计算、存储、网络资源&#xff0c;如ECS计算型c6、通用型g8等都是企业级实例&#xff0c;阿里云百科分享什么是企业级云服务器、企业级实例的优势、企业级和共享型云服务…

限制 el-input 输入 emoji

1. 电脑如何输入 emoji 表情 ? 快捷键 win; 或 win. 2. 代码实现 <template><el-input v-model"input" placeholder"请输入内容" input"inputChange"></el-input> </template><script> export default {name: D…

最小化安装移动云大云操作系统--BCLinux-R8-U8-Server-x86_64-230802版

CentOS 结束技术支持&#xff0c;转为RHEL的前置stream版本后&#xff0c;国内开源Linux服务器OS生态转向了开源龙蜥和开源欧拉两大开源社区&#xff0c;对应衍生出了一系列商用Linux服务器系统。BC-Linux V8.8是中国移动基于龙蜥社区Anolis OS 8.8版本深度定制的企业级X86服务…

Springboot 自定义 Mybatis拦截器,实现 动态查询条件SQL自动组装拼接(玩具)

前言 ps&#xff1a;最近在参与3100保卫战&#xff0c;战况很激烈&#xff0c;刚刚打完仗&#xff0c;来更新一下之前写了一半的博客。 该篇针对日常写查询的时候&#xff0c;那些动态条件sql 做个简单的封装&#xff0c;自动生成&#xff08;抛砖引玉&#xff0c;搞个小玩具&a…

SpringBoot---内置Tomcat 配置和切换

&#x1f600;前言 本篇博文是关于内置Tomcat 配置和切换&#xff0c;希望你能够喜欢 &#x1f3e0;个人主页&#xff1a;晨犀主页 &#x1f9d1;个人简介&#xff1a;大家好&#xff0c;我是晨犀&#xff0c;希望我的文章可以帮助到大家&#xff0c;您的满意是我的动力&#x…