当前位置：首页 > 编程日记 > 正文

mysql 树形结构_再读MySQL索引-《高性能MySQL》索引手记

编程日记 2024-02-03 09:30:00

最近工作中经常和MySQL打交道，当数据量小的时候，不同查询方式以及是否使用索引并无大碍，当数据量随着业务的成长急剧加速时，索引的重要性不言而喻。
本篇文章以《高性能MySQL》中的索引章节为基础，加之个人对索引的理解进行记录。
仅做要点上的记录，关于细节问题可自行Google

Mysql索引原理及其优化

一、什么是索引

下面来看来自MySQL官方关于索引的的定义：

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

在上面的定义中，有一个核心的关键点—数据结构，即索引是一种数据结构。

二、索引能带来什么

索引存在的一个重要的价值是帮助我们快速查找到数据，其次索引的值如果是顺序存储的，还可以帮助我们就行ORDER BY操作，而且索引本身是存储数据的，因此有一些查询可以在索引中完成。

总结下来三个关键的点：

帮助我们快速查到数据；
顺序存储的索引，可以帮助我们进行“天然的ORDER BY排序”；
索引本身是存储数据的，有一些查询可以在索引中完成。

下面是来自《高性能MySQL》中关于索引的优点的总结：

减少查询需要扫描的数据量(加快查询速度)
减少服务器的排序操作和创建临时表的操作（加快了GROUP BY）
将服务器的随机IO变为顺序IO（加快查询速度）

凡事有利有弊，我们来看下索引的缺点：

索引本身也是数据，也需要存储，因此会带来额外的存储空间
其次，在插入/更新/删除操作的同时，需要维护索引，因此会带来额外的时间开销。

实际上，在一定的数据范围内(索引数目不多的情况下)，建立索引带来的开销是远小于它带来的好处的，但是我们仍然要防止索引的滥用。

三、索引的类型

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

1. FULLTEXT

即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。

HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

3. BTREE

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

4. RTREE

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找。

三、索引的种类

普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有null）
主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索

四、B+数索引

4.1 B+树结构

B+树的每个中间节点不保存数据,只用来索引,也就意味着所有非叶子节点的值都被保存了一份在叶子节点中，叶子节点之间根据自身的顺序进行了链接。

B+树这样定义做有什么好处呢？

中间节点不保存数据，那么就可以保存更多的索引，减少数据库磁盘IO的次数。
因为中间节点不保存数据，所以每一次的查找都会命中到叶子节点，而叶子节点是处在同一层的，因此查询的性能更加的稳定。
所有的叶子节点按顺序链接成了链表，因此可以方便的进行范围查找。

浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

4.2 查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。.

4.3 B+树的性质

通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。

这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。

4.4 数据库使用索引的工作步骤

在索引的B+树上找到对应的值，并且拿到这条数据在磁盘上的地址；
根据地址去磁盘上查找，拿到该条数据的所有值。

五、索引方式

5.1 前缀索引

如果希望给一个很长的字符串上添加索引，那么可以考虑前缀索引。

前缀索引：在对一个比较长的字符串尽心索引时，可以仅索引开始的一部分字符，这样可以大大的节约索引空间，从而提高索引效率，但是这样会降低索引的选择性。

5.2 联合索引

一般我们都是有对多个列进行索引的需求的,因为查询的需求多种多样.这个时候我们可以选择建立多个独立的索引或者建立一个联合索引。大多数时候都是联合索引更加合适一些。

创建联合索引的语法：

alter table user add index school_age(`field_1`,`field_2`)

使用联合索引的时候,有一个非常重要的因素就是所有的索引列只可以进行最左前缀匹配,例如上面的field_1联合索引，当仅使用field_2作为查询条件的时候是不能使用的。

在不考虑任何查询的情况下,我们应该讲选择性高的列放在联合索引的前面,但是实际上我们更多的是通过查询来反推索引,以使某个固定的查询可以尽可能的命中索引以提高查询速度。毕竟我们建立索引的目的也是为了加快查询的速度.

因此联合索引的优化更多的是根据某个或者某些语句来优化的，不具备一个通用的法则。

最左前缀索引的原理

mysql建立多列索引（联合索引）有最左前缀的原则，即最左优先

5.3 聚簇索引

聚簇索引不是一种索引类型，而是一种存储数据的方式。

Innodb的聚簇索引是在同一个数据结构中保存了索引和数据。

因为数据真正的数据只能有一种排序方式，所以一个表上只能有一个聚簇索引。

Innodb使用主键来进行聚簇索引，没有主键的话就会选择一个唯一的非空索引，如果还还没有，innodb会选择生成一个隐式的主键来进行聚簇索引。

为什么innodb这么执着的需要搞一个聚簇索引呢,因为一个数据表中的数据总得有且只有一种排序方式来存储在磁盘上,因此这是必须的。

这也是innodb推荐我们使用自增主键的原因，因为自增主键自增且连续，在插入的时候只需要不断的在数据后面追加即可。设想一下使用UUID来作为主键，那么每一次的插入操作,都需要找到当前主键在已排序的主键中的位置，然后插入,并且要移动该主键后的数据，以使得数据和主键保持相同的顺序,这无疑是代价非常高的。

聚簇索引

就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的记录数据；
聚簇索引的叶子节点称为数据页，聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。

辅助索引：

非主键索引，叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数的主键索引值

5.4 覆盖索引

当一个索引包含（或者说是覆盖）需要查询的所有字段的值时，我们称之为覆盖索引。

三种理解：

查询的数据列只用从索引中就能够得到，不必从数据表中读取
查询列要被所使用的索引覆盖
索引是高效找到行的一个方式，当能通过检索索引就可以读取到想要的数据，那就不需要再到数据表中读取行了。
如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫覆盖索引
是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）

5.5 及时删除冗余和重复的索引

有一些索引从未在查询中使用过，却白白增加数据插入时的开销，对于这种索引应该及时删除。

https://www.dkcj.cn/info/1269.html

下午就要考试啦~~附上自己做的考试范围

第一章测试是否有必要有。毕竟成本低什么是软件的质量？（测试检测的是软件的质量，那么是什么软件的质量） 外部质量：用户可感知的，[功能、可靠、易用、效率] 内部质量：代码风格、内聚性、耦合性…

编程日记2024/02/03 09:00:00

Unity 创建2D平台游戏开发学习教程

了解如何使用C#在Unity中创建您的第一款2D平台游戏你会学到什么使用Unity创建2D奥运会使用可脚本化的对象和单一模式使用良好的编程实践创造武器和射弹使用可脚本化的对象和委托模式创建强大且通用的人工智能创造具有多重能力的角色创建级别组件 MP4 |视频:h264&…

编程日记2024/02/03 08:30:00

Solr和lucene

1. Solr 是什么？ Solr它是一种开放源码的、基于 Lucene Java 的搜索服务器，易于加入到 Web 应用程序中。Solr 提供了层面搜索(就是统计)、命中醒目显示并且支持多种输出格式（包括XML/XSLT和JSON等格式）。它易于安装和配置&#xf…

编程日记2024/02/03 08:00:00

django 链接地址匹配流程

前提： 代码结构步骤一： 下面为某个网页的链接地址 <body> {% if latest_article_list %}<ul>{% for article in latest_article_list %}<li><a href"/blog/p/{{ article.id }}/">{{ article.title }} </a><…

编程日记2024/02/03 07:30:00

【Datawhale-Python】Task1

1.环境搭建略（见之前博客） 2.python初体验 ①print（） 略 ②input（） Python3.x 中 input() 函数接受一个标准输入数据，返回为 string 类型。 >>> ainput() 2 >>> a 23.…

编程日记2024/02/03 07:00:00

Android高手速成

第一部分个性化控件(View) http://www.cnblogs.com/huwei0814/p/3796659.html 第二部分工具库 http://www.cnblogs.com/huwei0814/p/3796670.html 第三部分优秀项目 http://www.cnblogs.com/huwei0814/p/3796673.html 第四部分开发工具及测试工具 http:/…

编程日记2024/02/03 06:30:00

Texlive安装与环境变量配置

下载安装：https://www.jianshu.com/p/25896cc05cb4 去官网下载速度很快环境变量配置：https://www.jianshu.com/p/f8f6c255098c

编程日记2024/02/03 06:00:00

2018-3-7论文网络评论中非结构化信息的表示与研究--------实验分析

数据来源： （1）电子商务网站提供的网路评论 （2）专门提供网络评论交流平台的网络 （3）文章中使用的数据为网上某商城关于“某品牌中的一种型号纸尿裤（价格75RMB）”的2526…

编程日记2024/02/03 05:30:00

3dmax Vray建筑可视化入门学习教程

面向初学者的3Ds Max Vray最佳Archviz可视化课程从安装到最终图像的一切都将从头开始教授，不需要任何经验大小解压后：3.25G 时长4h 6m 1280X720 MP4 语言：英语中英文字幕（根据原英文字幕机译更准确） 课程获取&…

编程日记2024/02/03 05:00:00

javascript与浏览器学习（一）

待学习………… 20160421 标题：JavaScript中浏览器兼容问题博客地址：http://www.cnblogs.com/DF-fzh/p/5408241.html 简单学习。转载于:https://www.cnblogs.com/Tpf386/p/5416390.html

编程日记2024/02/03 04:30:00

idea运行jsp显示源码_基于jsp+mysql+Spring+mybatis的SSM在线个人PC电脑商城平台网站系统...

运行环境: 最好是java jdk 1.8，我们在这个平台上运行的。其他版本理论上也可以。IDE环境： Eclipse,Myeclipse,IDEA都可以tomcat环境： Tomcat 7.x,8.x,9.x版本均可硬件环境： windows 7/8/10 1G内存以上主要功能说明： 管…

编程日记2024/02/03 04:00:00

Dosbox+Masm汇编语言

一篇很不错的教程: https://bingyishow.top/Technical-article/54.html

编程日记2024/02/03 03:30:00

hypervisor简介

什么是hypervisorhypervisor为操作系统提供独立的虚拟硬件平台，而虚拟硬件平台反过来又提供对底层机器的虚拟的完整访问。hypervisor之于操作系统类似于操作系统之于进程。虚拟化和hypervisor关于什么是虚拟化，请参考我的另一篇博客《虚拟化技术介绍》&a…

编程日记2024/02/03 03:00:00

非结构化信息-》半结构化-》结构化-》关联数据体系-》数据挖掘-》故事化呈现-》决策导向

非结构化数据介绍来源： 非结构化信息_百度百科 https://baike.baidu.com/item/%E9%9D%9E%E7%BB%93%E6%9E%84%E5%8C%96%E4%BF%A1%E6%81%AF/2002521?fraladdin 总的来说，目前非结构化数据越来越多，怎么提取其中价值信息？&#xff…

编程日记2024/02/03 02:30:00

元宇宙:基础-虚拟现实栈开发和虚拟土地

元宇宙——如何使用NFTs构建虚拟角色、虚拟环境、空间特征、虚拟土地的沉浸式购买你会学到什么元宇宙:从虚拟现实到虚拟双胞胎的基本概念元宇宙:全栈开发，包括头像、传送、互操作性和隐私元宇宙:如何建立元宇宙(地平线市场)景观，娱乐，游…

编程日记2024/02/03 02:00:00

oracle数据库连接设置配置文件

转载于:https://www.cnblogs.com/Ly426/p/5416588.html

编程日记2024/02/03 01:30:00

python3.7.1安装教程详细_CentOS 7 安装python3.7.1的方法及注意事项

安装wget yum -y install wget 创建一个download目录用于下载各种安装包 mkdir download 切换到刚创建的download目录中 cd download 下载python3.7.1 解压 tar -zxvf Python-3.7.1.tgz 切换到刚刚解压得到得文件夹 cd Python-3.7.1 安装配置 ./configure 注意，我这…

编程日记2024/02/03 01:00:00

Docker部署SpringBoot项目详细部署过程

Docker可比喻成一个装应用的容器，将应用及其依赖文件、数据等打包在容器内，直接运行容器即可把应用运行起来，而无需关心环境配置问题。本文记录个人学习Docker的总结内容，安装、配置和部署等内容，在过程中，应注意命令不要写错，加上Docker插件等问题，若出现理解不到位的地方，请多指出。

编程日记2024/02/03 00:30:01

简单图文配置golang+vscode【win10/centos7+golang helloworld+解决install failed等情况】

博客目录（阅读时间：10分钟）一.win100.系统环境1. win10配置golang环境①下载相关软件②创建gowork工作空间③配置环境变量（GOPATHPATH）④验证环境配置结果2. win10配置vscode go环境①安装vscode的go插件②尝试运行③v…

编程日记2024/02/03 00:30:00

安卓画板学习笔记

2019独角兽企业重金招聘Python工程师标准>>> 代码片段 package com.tang.paint;import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream;import android.app.Activity; import android.app.ActionBar; import android.app.Fr…

编程日记2024/02/03 00:00:00

2018-3-7 Hadoop简介1（名字的由来，以及基本的结构）

问题一：什么是云计算？ 实际上是提供的一种服务，这个服务的特点是？ 主要是按需分配云计算_百度百科 https://baike.baidu.com/item/%E4%BA%91%E8%AE%A1%E7%AE%97/9969353?fraladdin 什么是大数据&#xff1f…

编程日记2024/02/02 23:30:00

MAYA 2022基础入门学习教程

编程日记2024/02/02 23:00:00

[Ubuntu 22.04] Docker安装及使用

容器的生命周期由用户控制，用户可以选择手动删除容器或让其保留在系统中以供之后使用。选项允许你在容器内部创建一个交互式的终端会话，使你可以像在本地终端一样与容器进行交互。你可以在容器内执行命令，查看输出并输入命令。镜像拉取完成后，可以使用以下命令创建并启动一个基于 Ubuntu 20.04 镜像的容器。列出所有正在运行的容器，并显示它们的容器ID、镜像、命令、创建时间、状态等信息。以下命令可以中止容器，改命令将向容器发送一个停止信号，使其正常停止并退出。这将显示所有容器的列表，包括正在运行的和已停止的容器。

编程日记2024/02/02 22:30:03