MySQL进阶——索引

索引

  • 索引概述

  • 索引结构

  • 索引分类

  • 索引语法

  • SQL性能分析

  • 索引使用

  • 索引设计原则

概述

介绍

  • 索引（Index）是帮助MySQL高效获取数据的数据结构（有序）。

  • 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

image-20240322110910823

优缺点分析

优点

  • 提高数据检索的效率，降低数据库IO成本

  • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点

  • 索引列需要占用空间。

  • 索引大大提高了查询效率，同时也降低了更新表的速度。如：对表进行Insert、update、delete时，效率降低。

索引结构

MySQL的索引是在存储引擎层实现，不同的存储索引有不同的结构。

  • B+Tree索引：常见的索引类型，大部分引擎都支持B+树索引

  • Hash索引：底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询

  • R-tree（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据结构，通常使用较少

  • Full-text（全文索引）：是一种通过建立倒排索引，快速匹配文档的方式，类似Lucene，Solr，Es

image-20240322123207075

B+树索引

普通的B+树：

image-20240322125025385

MySQL索引数据结构对经典的B+树进行了优化。在原本的B+树的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序针织的B+树，提高区间访问性能

image-20240322125339081

Hash索引

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储hash表中。

如果出现了hash冲突，采用链表的方式解决。

image-20240322125601355

Hash索引的特点

  1. Hash索引只能用于对等比较（=，in），不支持范围查询（between,>,<,.....)

  2. 无法利用索引完成排序操作

  3. 查询效率高，通常只需要一次检索就行，效率通常比B+树高。

存储引擎支持

在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+树引擎在指定条件下自动构建的。

经典面试题：为什么InnoDB存储引擎采用B+树索引

  • 相对二叉树，层次更少，搜索效率高。

  • 对于B树，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能下降。

  • 相对Hash索引，B+树支持范围匹配及排序操作。

为什么采用B+树索引，而不采用hash索引？

  1. 范围查询效率： B+树索引支持按范围查询，例如范围查询、排序等，而哈希索引通常只支持精确匹配查询。在数据库中，范围查询是非常常见的操作，因此B+树索引更适合处理这样的场景。

  2. 顺序访问性能： B+树索引在范围查询时可以利用数据的有序性，因此对于顺序访问的效率更高。而哈希索引则不保证数据的有序性，因此无法像B+树索引那样高效地进行顺序访问。

  3. 内存利用率： 哈希索引在进行哈希冲突处理时需要保留额外的空间，这可能会导致在内存利用率上的一定程度的浪费。相比之下，B+树索引可以更好地利用内存空间。

  4. 支持部分索引： B+树索引支持前缀查询，而哈希索引不支持这种操作。在某些情况下，只需要索引字段的前几个字符来进行查询，这时B+树索引会更加高效。

  5. 并发性能： B+树索引通常对并发访问更友好，因为B+树的结构使得并发插入和删除更容易实现。而哈希索引在并发操作时可能需要更多的锁和同步操作。

尽管哈希索引在某些特定场景下可能更快，比如对于单个唯一键的查找，但总体来说，由于B+树索引的优势，MySQL选择采用B+树索引作为主要的索引结构。

索引分类

image-20240322131901752

在InnoDB存储索引中，根据索引的存储形式，可以分为下面两种：

  • 聚集索引：将数据存储与所有放到了一块，索引结构的叶子节点保存了行数据。（必须要，而且只能有一个）

  • 二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键（可以存储多个）

聚集索引的选取规则：

  1. 如果存储主键，主键索引就是聚集索引。

  2. 如果不存在主键，将使用第一个唯一（Unique）索引作为聚集索引。

  3. 如果没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

image-20240322132627629

来一个索引查找示例：

select * from user where name ='Arm';

执行过程（这个过程被称为回表查询）：

  1. 先根据二级索引查到主键值

  2. 再根据主键值去查询想要的数据

思考

image-20240322133219827

索引语法

  • 创建索引

    create[unique|fulltext] index index_name on table_name(index_col_name,...);
#unique和fulltext是可选的关键字，指定索引类型（二选一）
#(index_col_name,...)指的是：包含再索引中的列的列表。
#（可以指定一个或多个列，列名纸巾用逗号分开）

  • 查看索引

    show index from table_name;

  • 删除索引

    drop index index_name on table_name;

SQL性能分析

SQL执行频率

MySQL客户端连接成功后，通过show[session|global] status 命令可以提供服务器状态信息。

通过下面的指令，可以查看当前数据库的Insert、Update、Delete、Select的执行频率。

show global status like '表名模糊信息';

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句。

MySQL的慢查询的日志默认没有开启:

show variables like 'slow_query_log';

要开启的话，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息：

#开启MySQL慢查询日志查询开关
show_query_log=1
#设置慢查询日志的时间为2秒，SQL语句超过2秒，则视为慢查询，记录慢查询日志
long_query_time=2

如果配置完成后，重启MySQL服务器进行测试，查看慢日志文件中记录的信息

/var/lib/mysql/localhost-show.log

profile详情

show profiles 命令能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看的当前MySQL是否支持profile操作：

select @@have_profiling;

默认的profiling是关闭的，可以通过set语句在session/global级别开启profiling：

set profiling =1;

#查询每一条SQL的耗时基本情况
show profiles;
​
#查看指定的query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
​
#查看指定query_id的SQL语句的CPU使用情况
show profile cpu for query query_id;

image-20240322182155054

比如说我们查询profiling之后，要看某条语句的详情：

show profile for query QUery_ID;

image-20240322182313658

explain执行计划

explain或desc命令获取MySQL如何执行Select语句执行过程中如何连接和连接顺序。

语法

#直接在select语句之前加上关键字 explain 或 desc
explain select 字段列表 from 表名 where 条件;

image-20240323105617831

在这些信息中，重点关注的是type，然后再是possible_keys和key

索引使用

创建索引，就是一个将某个字段转换成一个B+树的过程。

可以测试一下在10万条数据中查一条数据，查询时间从二十几秒钟减少到几毫秒。

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。

最左前缀法则：查询从索引的最左列开始，并且不跳过索引的列。

如果跳跃了一列，索引将部分失效（后面的字段索引失效）

索引失效情况

范围索引

联合索引中，出现范围查询（>或<)，范围查询右侧的列索引失效。

如果使用>= 或 <= 的话，索引还是会存在。

索引列运算

不要再索引列上进行运算操作，索引会失效。

字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效。

or连接的条件

用or分隔开的条件，如果or前的条件中的列有索引，而后面列中没有索引，那么涉及的索引都不会被用到。只有两侧都有索引，索引才会生效。

数据分布影响

如果MySQL评估使用索引比全表更慢，不使用索引。（MySQL自动变更的）

SQL提示

SQL提示：是优化数据库的一个重要手段。简单说，就是在SQL语句中加入一些人为的提示，来达到优化操作的目的。

  • use index：只是建议使用指定的索引，查询优化器仍然可以选择其他索引。

    SELECT * FROM users USE INDEX (idx_username) WHERE username = 'some_username';
​

  • force index：会强制使用指定的索引，即使查询优化器认为其他索引更合适。

    SELECT * FROM users FORCE INDEX (idx_username) WHERE username = 'some_username';
​

  • ignore index：告诉 MySQL 查询优化器在执行查询时忽略特定的索引。

    SELECT * FROM users IGNORE INDEX (idx_username) WHERE username = 'some_username';

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在索引中已经全部能找到），减少select *（很容易出现回表查询）

知识补充：

  • using index condition：查找使用了索引，但是需要回表查询数据

  • using where，using index：查询使用到了索引，但是需要的数据都在索引列中找到，所以不需要回表查询数据。

image-20240323222927111

思考面试题
如果存在一张表，有四个字段(id, username, password, status)，由于数据量巨大，需要对下面SQL语句进行优化，该怎么进行设计才是最优方案呢？
select id,username,password from user where username = 'xxx';

下面给出问题的结果：

  • 我们可以通过对username和password两个字段设置联合索引实现最优方案。

  • 因为使用了这种方案，我们可以直接进行一次的辅助索引就可以拿到username和password。一般查询的时候还会遵循最左前缀法则，一般查询的都是username，索引也不会失效。

前缀索引

当字段类型为字符串（varchar、text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。

此时，可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法
create index idx_xxxx on table_name(column(n));

这里的n代表的是：选取前多少个字符作为索引。

前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值。

索引选择性越高，查询效率越高。唯一索引选择性是1，这是最好的索引选择性，性能也是最好的。

select count(distinct email) / count(*) from user;
select count(distinct substring(emial,1,5)) / count(*) from user;

如果前缀索引的索引性小于1，表示可能出现重复情况，那如果出现重复情况，会把其全部返回。

就是说，当通过前缀索引找到目标值的时候，还会比较后一个的数据，看看前缀是否相同，如果相同，还会继续比较，继续返回。

单列索引与联合索引

  • 单列索引：一个索引只包含单个列

  • 联合索引：一个索引包含多个列

使用情况

如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引，而不是单例索引。

比如说这样一种情况：

还是之前那个问题：
如果存在一张表，有四个字段(id, username, password, status)，由于数据量巨大，需要对下面SQL语句进行优化，该怎么进行设计才是最优方案呢？
select id,username,password from user where username = 'xxx';

如果我们只通过单列索引，那么还会需要进行回调查询。

如果是多列索引，则没有回调查询了。

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

索引设计原则

  1. 针对数据量大的、且查询比较频繁的表建立索引。

  2. 针对常作为查询条件（where）、order by（group by）操作的字段建立索引

  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

  4. 如果字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引

  5. 尽量使用联合索引，减少单列索引。查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率

  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价越大，会影响增删改的效率

  7. 如果索引列不能存储NULL值，在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含NULL值时，可以更好的确定哪个索引最有效的用于查询

总结

  1. 索引概述：索引是高效获取数据的数据结构

  2. 索引结构：B+树 和 Hash

  3. 索引分类： 正常的索引：主键索引、唯一索引、常规索引、全文索引 InnoDB中根据索引结构分类：聚集索引（存储的行数据）、二级索引

  4. 索引语法：

    #创建索引
create[unique] index index_name on table_name(column)
​
#查看索引
show index from table_name()
​
#删除索引
drop index index_name on table_name

  5. SQL性能分析：执行频次、慢查询日志、profile、explain

  6. 索引使用：

    1、联合索引：最左前缀法则，避免使用>、<
​
2、索引失效：不进行函数运算、字符串不加引号、like模糊匹配%加前面导致失效、or连接条件两侧有索引才会让索引生效、数据分布影响
3、SQL提示：提示使用/忽略/强制使用某个索引
​
4、覆盖索引：去避免回表查询
​
5、前缀索引
​
6、单列/联合索引

  7. 索引设计原则

    表
字段
索引

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

Redis 教程系列之缓存雪崩，击穿，穿透(十四)

Redis 教程系列之缓存雪崩，击穿，穿透(十四)

一，缓存雪崩 当大量缓存数据在同一时间过期（失效）或者 Redis 故障宕机时，如果此时有大量的用户请求，都无法在 Redis 中处理，于是全部请求都直接访问数据库，导致数据库的压力骤增，严重的会造成数据库宕机，从而形成一系列连锁反应，造成整个系统崩溃，这就是缓存雪崩的问…
阅读更多...
AJAX介绍使用案例

AJAX介绍使用案例

文章目录 一、AJAX概念二、AJAX快速入门1、编写AjaxServlet&#xff0c;并使用response输出字符&#xff08;后台代码&#xff09;2、创建XMLHttpRequest对象&#xff1a;用于和服务器交换数据 & 3、向服务器发送请求 & 4、获取服务器响应数据 三、案例-验证用户是否存…
阅读更多...
ubuntu上一款好用的串口工具screen

ubuntu上一款好用的串口工具screen

看名字&#xff0c;你猜他是什么&#xff1f; 安装 sudo apt install screen 然后将USB串口接到虚拟机&#xff0c;执行dmesg命令查看串口设备名&#xff1a; 测试&#xff1a; sudo screen /dev/ttyUSB0 115200确实很简单。
阅读更多...
【C++】list类（使用方法和模拟实现）

【C++】list类（使用方法和模拟实现）

一、标准库中的list类 1.1 list类介绍 1.2 list的常用接口 1.2.1 常用的构造函数 1.2.2 容量操作接口 &#xff08;1&#xff09;size &#xff08;2&#xff09;empty &#xff08;3&#xff09;resize 1.2.3 访问和遍历 &#xff08;1&#xff09;迭代器 &#xff…
阅读更多...
DC-DC教程，真不错！

DC-DC教程，真不错！

大家好&#xff0c;我是记得诚。 交流群读者分享了一个DC-DC的文档&#xff0c;内容还挺好&#xff0c;分享给大家。 文章原链接&#xff1a;DC-DC教程&#xff0c;真不错&#xff01;&#xff0c;可以获取完整的文档。 推荐阅读&#xff1a; 硬件工程师如何零基础入门&#…
阅读更多...
数据结构：堆和二叉树遍历

数据结构：堆和二叉树遍历

堆的特征 1.堆是一个完全二叉树 2.堆分为大堆和小堆。大堆&#xff1a;左右节点都小于根节点 小堆&#xff1a;左右节点都大于根节点 堆的应用&#xff1a;堆排序&#xff0c;topk问题 堆排序 堆排序的思路&#xff1a; 1.升序排序&#xff0c;建小堆。堆顶就是这个堆最小…
阅读更多...
【算法】五道大学生必备平价精致小众松弛感宝藏好题平替

【算法】五道大学生必备平价精致小众松弛感宝藏好题平替

【算法】五道大学生必备平价精致小众松弛感宝藏好题平替x ​ 刚学了Java就想用来写算法题的我&#xff1a; ​ 借着几道算法题&#xff0c;熟悉一下Java中Stack类&#xff0c;String类的用法。 925.长按键入 原题链接&#xff1a; 925. 长按键入 ​ 用来测试与练习String类自带…
阅读更多...
php闭包应用

php闭包应用

laravel 路由 bingTo 把路由URL映射到匿名回调函数上&#xff0c;框架会把匿名回调函数绑定到应用对象上&#xff0c;这样在匿名函数中就可以使用$this关键字引用重要的应用对象。Illuminate\Support\Traits\Macroable的__call方法。 自己写一个简单的demo: <?php <?…
阅读更多...
遍历目录下的某个文件并删除

遍历目录下的某个文件并删除

目录 需求 编写过程 演示 需求 大家在学习时可能会有一个自己的小目录&#xff0c;里面放着各种奇葩代码&#xff0c;有天突然发现&#xff0c;没有空间了&#xff0c;这时候发现遗留了很多的可执行文件&#xff0c;大大的浪费了我们的空间&#xff0c;但是由于层数深&#…
阅读更多...
基于SSM的宠物领养平台的设计与实现

基于SSM的宠物领养平台的设计与实现

基于SSM的宠物领养平台的设计与实现 获取源码——》公主号&#xff1a;计算机专业毕设大全 获取源码——》公主号&#xff1a;计算机专业毕设大全
阅读更多...
React腳手架已經創建好了，想使用Vite作為開發依賴

React腳手架已經創建好了，想使用Vite作為開發依賴

使用Vite作為開發依賴 安裝VITE配置VITE配置文件簡單的VITE配置項更改package.json中的scripts在根目錄中添加index.html現在可以瀏覽你的頁面了 安裝VITE 首先&#xff0c;在現有的React項目中安裝VITE npm install vite --save-dev || yarn add vite --dev配置VITE配置文件 …
阅读更多...
JavaEE--小Demo--数据库建立

JavaEE--小Demo--数据库建立

目录 实验准备 本次所要新建的文件 实验步骤 step1-demo.sql 1.在resources文件夹下新建demo.sql文件 2.打开此目录&#xff0c;并运行命令提示符 3.打开数据库mysql -uroot -p 4.创建数据库create database demo; 5.使用数据库use demo; 6.导入数据source demo.sql;…
阅读更多...
Prometheus+Grafana 监控Tongweb7（by lqw）

Prometheus+Grafana 监控Tongweb7（by lqw）

文章目录 1.准备工作2.Tongweb7部署3.Prometheus部署4.上传jar包并配置Tongweb75.Prometheus配置6.安装和配置Grafana 1.准备工作 本次参考&#xff1a;Prometheus监控Tongweb容器 1.使用虚拟机ip&#xff1a;192.168.10.51&#xff08;tongweb&#xff09;&#xff0c;192.1…
阅读更多...
算法系列--哈希表

算法系列--哈希表

&#x1f495;"白昼之光&#xff0c;岂知夜色之深。"&#x1f495; 作者&#xff1a;Mylvzi 文章主要内容&#xff1a;算法系列–哈希表 今天为大家带来的是算法系列--哈希表 1.两数之和 链接: https://leetcode.cn/problems/two-sum/submissions/515941642/ 分析…
阅读更多...
《C++ Primer 第五版 中文版》第12章 动态内存【阅读笔记 + 个人思考】

《C++ Primer 第五版 中文版》第12章 动态内存【阅读笔记 + 个人思考】

《C Primer 第五版 中文版》第12章 动态内存【阅读笔记 个人思考】 12.1 动态内存与智能指针12.1.1 shared_ptr类 静态内存包括&#xff1a;初始化只读数据段&#xff0c;初始化读写数据段&#xff0c;未初始化数据和常量数据段。 详细在下面博客总结&#xff1a; Linux系统下…
阅读更多...
吴恩达机器学习-可选实验室：Softmax函数

吴恩达机器学习-可选实验室：Softmax函数

文章目录 CostTensorflow稀疏类别交叉熵或类别交叉熵祝贺 在这个实验室里&#xff0c;我们将探索softmax函数。当解决多类分类问题时&#xff0c;该函数用于Softmax回归和神经网络。 import numpy as np import matplotlib.pyplot as plt plt.style.use(./deeplearning.mplstyl…
阅读更多...
【数据结构】顺序表和链表详解顺序表和链表的实现

【数据结构】顺序表和链表详解顺序表和链表的实现

主页&#xff1a;醋溜马桶圈-CSDN博客 专栏&#xff1a;数据结构_醋溜马桶圈的博客-CSDN博客 gitee&#xff1a;mnxcc (mnxcc) - Gitee.com 目录 1.线性表 1.1 顺序表 1.1.1 概念及结构 1.1.2 静态顺序表 1.1.3 动态顺序表 1.2 链表 1.2.1 链表的概念及结构 1.2.2 链表…
阅读更多...
力扣HOT100 - 1. 两数之和

力扣HOT100 - 1. 两数之和

解题思路&#xff1a; 解法一&#xff1a;暴力 class Solution {public int[] twoSum(int[] nums, int target) {int n nums.length;for (int i 0; i < n; i)for (int j i 1; j < n; j) {if (target nums[i] nums[j])return new int[] { i, j };}return new int[…
阅读更多...
pcl利用kdtree计算点云密度

pcl利用kdtree计算点云密度

点云密度挺需要的,很多时候需要知道点云密度才能进行下一步 这里利用kdtree计算点云密度 代码 结果 这里单位写错了&#xff0c;抱歉
阅读更多...
Personal Website

Personal Website

Personal Website Static Site Generators hexo hugo jekyll Documentation Site Generator gitbook vuepress vitepress docsify docute docusaurus Deployment 1. GitHub Pages 2. GitLab Pages 3. vercel 4. netlify Domain 域名注册 freessl 域名解析域名…
阅读更多...
最新文章

Copyright @ 2022~2023