MYSQL 精通索引【快速理解】

目录

1、什么是索引?

2、索引结构

        1.为什么不使用二叉树呢?

        2.B树数据结果

       3.B+树

        4.Hash结构

3、索引语法

        1.创建索引

        2.查看索引

        3.删除索引

4、SQL性能分析

       1.SQL执行频次

        2.慢查询日志

        3.profile详情

        4.EXPLAIN

5、索引规则

        1.最左前缀法则

        2.索引失效情况

        3.SQL提示

        4.索引覆盖&回表查询

        5.前缀索引

        6.单列索引&联合索引

        6、索引设计原则


1、什么是索引?

        索引是一种可以快速查询的有序的数据结构。如果查询没用上索引的话,那么就会出现把表中的每一条数据进行扫描,这样效率太差。

        索引的优点就是查找速度快,并且减少进行IO请求次数,根据索引排序减少与CPU消耗。但是存储索引要空间进行存储,并且增删改的时候效率较低因为要在数据结构上修改节点。

        索引一般是一级索引也叫聚集索引,非聚集索引就是以非主键作为索引生成的索引,一般主键就是聚集索引,没有主键会把第一个非空索引当做聚集索引,如果都没有会生成一个隐藏的rowId作为聚集索引。

2、索引结构

        B+树索引 大部分引擎都支持的数据结构、Hash索引,R-tree索引、Full-Text索引

        目前使用InnoDB树结构那么就主要介绍B+树结构

        1.为什么不使用二叉树呢?

        因为二叉树可能会有链化问题,比如二叉排序树,链化斜树,这样查找层树过多导致查找效率太慢,红黑树保证不会倾斜,但是数据的存储的值每个节点只能存两个,对于存储多个数据,也会导致层次太高查找性能不足。

        2.B树数据结果

   

        5阶数说明有5个子节点,有4个key 5个指针。

       3.B+树

        它的特点是所有数据都会出现在叶子节点,如果数据满的话键值会从中间向上分裂,并且叶子节点会有一根单向链表进行连接。在Mysql中优化了一个点就是多了一个指针形成双向链表在叶子节点,保持查找的顺序,提供区间访问性能。

 目前我们用的是B+树,为什么?因为如果根据B+树的话,非叶子节点存储的就是指针而不是值,因为一个取最多能存1M大小的内容,然后一个页最多存16k的内容,也就是一个区最多能存64页,我们设想如果节点上存键值指针以及数据,那么一个区能存的指针键值就少,那么我树的高度就会变高层级变大,如果是B+树,非叶子节点只存储键值和指针,那么同样空间能存储的键值就多,树的层级变小,查找效率变高。比如这个数是5阶那么可能单数据太大,B树节点可能4个数据加指针加键值就存满了。

        4.Hash结构

确定是不能范围查询、不支持排序功能,好处是块,因为存储的时候会生成一个对应的hash值,对应着该数据的位置,如果查找的时候根据键值通过hash算法就可以找到对应hash值位置的数据 Memory引擎支持该结构。

3、索引语法

        1.创建索引

                CREATE [UNIQUE|FULLTEXT] INDEX index_name on table_name(index_col_name)

        2.查看索引

                SHOW INDEX FROM table_name;

        3.删除索引

                DROP INDEX index_name ON table_name;

4、SQL性能分析

       1.SQL执行频次

                 show global status like 'Com______';

                当前数据库的增删改查的次数的频率

        2.慢查询日志

                查看慢查询日志开关

                show variables like 'slow_query_log';

                在/etc/my.cnf中

                日志存在/var/lib/mysql下如果超过2秒的查询就会存的该日志里

        3.profile详情

                select @@have_profiling; 查询是否支持profile操作

                SETprofiling=1;开启

                通过SHOW profiles;就看到查询的语句对应的耗时时间。

        4.EXPLAIN

                可以看到SQL执行计划,能看到查询语句,可以看到表的查询的连接顺序,还能看到使用的索引。

                EXPLAIN SELECT 字段列表 FROM 表名 WHERE条件 ,意思就是任意查询前加一个EXPLAIN就可以了。

        

                根据查询结果,id值相同的话是执行顺讯是从上到下,id值不同的执行顺序是id值越大越先执行。

5、索引规则

        1.最左前缀法则

                如果使用了联合索引,那么查询条件使用索引是从最左侧那个索引必须使用,并且不能跳过中间列,如果跳过了那么后面的索引失效也就是当初设置索引的顺序是ABC,可以是AB因为中间没有跳过 。与写的时候的位置无关 ,也就是当初设置索引的顺序是ABC,写的时候只有有ABC就可以无需关系是ABC BCA ACB。 但是如果使用了 > <的查询操作则范围查询右侧索引失效。

        2.索引失效情况

                1.如果对索引进行运算操作,则该索引失效。

                2.字符串类型字段查询要加单引号 否则索引失效

                3.模糊查询的时候,如果头部使用了%模糊匹配,索引失效,如果尾部使用则不会。

                4.用or关键字的时候如果有一侧没有使用索引则索引失效。

                5.优化器如果发现走索引比不走索引还要慢则不用索引,比如本来就需要全表扫描,使用了索引还要走全表扫描性能会差一点。还有就是查询的结果占大部分,那么mysql会认为扫描全表还是快,但是查询如果是占一小小部分则还是会使用索引的。

        3.SQL提示

                某些情况下,对于某个字段,它可能是联合索引,也是单列索引,可以在查询的时候指定使用的是哪种索引,加上use index(索引名)就可以了,当然mysql也有可能不接受这个建议。

也可以用ignore use index(索引名)建议忽略某个索引。

                强制使用force index(索引名)

        4.索引覆盖&回表查询

                二级索引就是叶子节点除了id还有对应的设置的非主键索引

                聚集索引就是叶子节点存储着全部数据

        第一条SQL就是直接按照右侧第一个树(聚集索引)进行搜索,拿到row整行数据,因为是根据主键搜索的。

        第二条SQL是根据第二个树(二级索引)进行搜索的,二级索引叶子节点存储的id和对应的索引值,这样能直接拿到需要的值而不需要再聚集索引中再次查询的就叫索引覆盖

        第三天SQL不像前两条SQL能一次查找直接得出结果,而是要出现先进行聚集索引查找,查找到的数据只有一部分,所以需要回表查询在聚集索引的树中把所有数据查找出来。

        5.前缀索引

                索引由于作为叶子节点中存储的数据,需要占用存储空间,如果对于数据量特别大的数据,并且索引对应列的存储值也比较大的可以用前缀索引,它是指将该数据的的前几位作为索引,这样可以减少索引的存储空间。语法是下面这样。

实际上它是一种时间换空间的方法,因为他是根据前部分的某一段索引再二级索引中查找数据,找到了再去聚集索引中查找数据,找到了还得对比是不是最初的数据因为当初传进来的只是数据的部分值。

        6.单列索引&联合索引

        单列索引:一个索引只包含一个列

        联合索引:一个索引包含多个列

        使用联合索引会减少回表查询 

如果它的键(索引)包含了所需要的查询结果,那么就直接取就可以覆盖索引了,不需要回表查询了。

        6、索引设计原则

                对于查询频繁>100W,在where、group by、order by 后的条件建立索引,对于字符串长的可以用前缀索引,尽量使用联合索引减少单列索引,查询时可以使用索引覆盖。

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

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

相关文章

光驱验证 MD5 校验和

步骤 1&#xff1a;在 Ubuntu 上打包文件并生成 MD5 校验和 打包文件 使用 tar 命令将文件夹打包成 tar.gz 文件&#xff1a; tar -czvf my_files.tar.gz /path/to/folder 生成 MD5 校验和 使用 md5sum 命令生成打包文件的 MD5 校验和&#xff1a; md5sum my_files.tar.g…

《网络数据安全管理条例》将于2025年1月1日起正式施行,从业者应如何解读?

2024年9月&#xff0c;国务院总理李强签署国务院令&#xff0c;公布了《网络数据安全管理条例》&#xff08;以下简称《条例》&#xff09;&#xff0c;该条例将于2025年1月1日起正式施行。 这一条例的出台&#xff0c;标志着我国在网络数据安全领域的管理迈上了新的台阶&#…

【MMIN】缺失模态想象网络用于不确定缺失模态的情绪识别

代码地址&#xff1a;https://github.com/AIM3RUC/MMIN abstract&#xff1a; 在以往的研究中&#xff0c;多模态融合已被证明可以提高情绪识别的性能。然而&#xff0c;在实际应用中&#xff0c;我们经常会遇到模态丢失的问题&#xff0c;而哪些模态会丢失是不确定的。这使得…

【Java Web】监听器类型及其使用

文章目录 监听器使用监听器类型ServletContextListenerHttpSessionListenerServletRequestListenerServletContextAttributeListenerHttpSessionAttributeListenerServletRequestAttributeListenerHttpSessionBindingListener 监听器&#xff08;Listener&#xff09;组件用于监…

conda创建 、查看、 激活、删除 python 虚拟环境

1、创建 python 虚拟环境 ,假设该环境命名为 “name”。 conda create -n name python3.11 2、查看 python 虚拟环境。 conda info -e 3、激活使用 python 虚拟环境。 conda activate name 4、删除 python 虚拟环境 conda remove -n name --all ​​ 助力快速掌握数据集…

LaTeX之四:如何兼容中文(上手中文简历和中文论文)、在win/mac上安装新字体。

改成中文版 如果你已经修改了.cls文件和主文档&#xff0c;但编译后的PDF仍然显示英文版本&#xff0c;可能有以下几个原因&#xff1a; 编译器问题&#xff1a;确保你使用的是XeLaTeX或LuaLaTeX进行编译&#xff0c;因为它们对Unicode和中文支持更好。你可以在你的LaTeX编辑器…

视频遥控打药履带机器人技术详解

视频遥控打药履带机器人技术是一种集成了遥控操作、视频监控和履带行走系统的现代化农业植保技术。以下是对该技术的详细解析&#xff1a; 一、技术概述 视频遥控打药履带机器人主要由履带行走系统、药箱、喷雾系统、遥控系统以及视频监控系统等部分组成。通过遥控操作&#…

BB1-NHS ester被用于将各种生物活性分子与蛋白质或其他生物大分子进行共轭连接,2082771-52-4

CAS号&#xff1a;2082771-52-4 中文名&#xff1a;BB1-琥珀酰亚胺酯&#xff0c;BB1-活性酯 英文名&#xff1a;BB1-NHS ester&#xff0c;或BB1-Succinimidyl Ester 分子式&#xff1a;C32H32N6O4 分子量&#xff1a;564.63 纯度&#xff1a;≥95% 供应商&#xff1a;陕…

MongoDB在现代Web开发中的应用

&#x1f493; 博客主页&#xff1a;瑕疵的CSDN主页 &#x1f4dd; Gitee主页&#xff1a;瑕疵的gitee主页 ⏩ 文章专栏&#xff1a;《热点资讯》 MongoDB在现代Web开发中的应用 MongoDB在现代Web开发中的应用 MongoDB在现代Web开发中的应用 引言 MongoDB 概述 定义与原理 发展…

激光雷达不够用,怎么办?Ubuntu如何用一个激光雷达实现两个激光雷达的扫描点云效果?点云配准ICP,点云拼接、话题转换、ROS重录制bag包。

1.首先至少得有一个激光雷达&#xff0c;如果没有的话&#xff0c;可以考虑花呗买一个&#xff0c;毕竟研究这东西&#xff0c;有个实物还是比较稳妥&#xff0c;这里我选择的是Livox Mid-360,哈哈哈&#xff0c;公司的&#xff0c;大概长这样&#xff1a; 2. 比如我们想要用激…

STM32H743ZIT6+LWIP+MPU+CUBEMX,通过stm32cubemx完成初始化,ping包亲测没问题,带解释!!

文章耗时两个月&#xff0c;原来写了一半&#xff0c;后来遇到其他项目&#xff0c;中间自己重新画了一块电路板。终于把初始化功能实现了&#xff0c;网上的教程能用的确实凤毛麟角&#xff01; 一、MPU配置详解 个人对stm32H7的MPU属于新接触&#xff0c;为了弄懂&#xff0…

python制作一个简单的端口扫描器,用于检测目标主机上指定端口的开放状态

import argparse # 用于解析命令行参数 from socket import * # 导入 socket 库的所有内容&#xff0c;用于网络通信 from threading import * # 导入 threading 库的所有内容&#xff0c;用于多线程操作 # 创建一个信号量&#xff0c;初始值为 1&#xff0c;用于线程同步&…

网络基础Linux(整理)

计算机网络背景 网络发展 独立模式: 计算机之间相互独立; 网络互联: 多台计算机连接在一起, 完成数据共享; 局域网LAN: 计算机数量更多了, 通过交换机和路由器连接在一起; 广域网WAN: 将远隔千里的计算机都连在一起; 所谓 "局域网" 和 "广域网" 只是一个…

我的第一个PyQt5程序

PyQt5的开发环境配置完成之后&#xff0c;开始编写第一个PyQt5的程序。 方法一&#xff1a;使用将.ui转换成.py文件的方法 import sys from FirstPyQt import Ui_MainWindow from PyQt5.QtWidgets import *#QtCore,QtGui,QtWidgets # from QtTest import Ui_MainWindow#导入Q…

面试:TCP、UDP如何解决丢包问题

文章目录 一、TCP丢包原因、解决办法1.1 TCP为什么会丢包1.2 TCP传输协议如何解决丢包问题1.3 其他丢包情况&#xff08;拓展&#xff09;1.4 补充1.4.1 TCP端口号1.4.2 多个TCP请求的逻辑1.4.3 处理大量TCP连接请求的方法1.4.4 总结 二、UDP丢包2.1 UDP协议2.1.1 UDP简介2.1.2…

Vue全栈开发旅游网项目(11)-用户管理前端接口联调

联调基本步骤 1.阅读接口文档 2.配置接口地址 3.使用axios获取数据 4.将数据设置到模型层 1.发送验证码联调 1.1 配置接口地址 文件地址&#xff1a;src\utils\apis.js //系统相关的接口 const SystemApis {sliderListUrl:apiHost"/system/slider/list/",//发送…

【相关分析方法】MATLAB计算滑动时滞相关系数

【相关分析方法】MATLAB计算滑动时滞相关系数 1 滑动时滞相关系数2 MATLAB代码2.1 函数代码2.2 案例参考滑动时滞相关系数(Moving Time-Lagged Cross-Correlation, TLCC) 是一种常用于分析两个时间序列之间的滞后关系的工具。它可以帮助我们确定一个时间序列相对于另一个时间…

llama-cpp模型轻量化部署与量化

一、定义 定义配置环境遇到的问题&#xff0c;交互模式下模型一直输出&#xff0c;不会停止模型量化Qwen1.5-7B 案例demo 二、实现 定义 主要应用与cpu 上的部署框架。由c完成。配置环境 https://github.com/ggerganov/llama.cpp https://github.com/echonoshy/cgft-llm/blo…

MySQl基础----Linux下数据库的密码和数据库的存储引擎(内附 实操图和手绘图 简单易懂)

绪论​ 涓滴之水可磨损大石&#xff0c;不是由于他力量强大&#xff0c;而是由于昼夜不舍地滴坠。 只有勤奋不懈地努力&#xff0c;才能够获得那些技巧。 ——贝多芬。新开MySQL篇章&#xff0c;本章非常基础&#xff0c;但同时需要一定的Linux基础&#xff0c;所以假若你没学习…

Qwen2-VL:发票数据提取、视频聊天和使用 PDF 的多模态 RAG 的实践指南

概述 随着人工智能技术的迅猛发展&#xff0c;多模态模型在各类应用场景中展现出强大的潜力和广泛的适用性。Qwen2-VL 作为最新一代的多模态大模型&#xff0c;融合了视觉与语言处理能力&#xff0c;旨在提升复杂任务的执行效率和准确性。本指南聚焦于 Qwen2-VL 在三个关键领域…