Mysql进阶-索引篇

Mysql进阶

  • 存储引擎
    • 前言
    • 特点
    • 对比
  • 索引
    • 介绍
    • 常见的索引结构
    • 索引分类
    • 索引语法
    • sql分析
    • 索引使用原则
    • 索引失效的几种情况
    • sql提示
    • 覆盖索引
    • 前缀索引
    • 索引设计原则

存储引擎

前言

Mysql的体系结构:
在这里插入图片描述

  • 连接层
    最上层是一些客户端和链接服务,主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
  • 服务层
    第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化,部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如 过程、函数等。
  • 引擎层
    存储擎真正的负责了MySQL中数据的存储和提取,服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能,这样我们可以根据自己的需要,来选取合适的存储引擎。
  • 存储层
    主要是将数据存储在文件系统之上,并完成与存储引擎的交互。

tip:存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。

相关sql语句

# 查看建表语句  默认的存储引擎是InnoDB
show create table [表名]
# 查看存储引擎
show engines;

特点

  • InnoDB(重点)
    在这里插入图片描述
    在这里插入图片描述
  • MyISAM

在这里插入图片描述

  • Memory
    在这里插入图片描述

对比

在这里插入图片描述
存储引擎的选择
在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。

  • InnoDB:
    是Mysq的默认存储引擎,支持事务、外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询之外,还包含很多的更新、删除操作,那么nnoDB存储引擎是比较合适的选择。
  • MyISAM :
    如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储弘擎是非常合适的。(类似MongoDB)
  • MEMORY:
    将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。(类似Redis)

索引

介绍

索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
在这里插入图片描述
优缺点:
在这里插入图片描述
索引结构:
在这里插入图片描述

常见的索引结构

B树(多路平衡查找树)

叶子节点和非叶子节点都存放数据。

在这里插入图片描述
B+树

叶子节点存放数据,非叶子节点存放指针。

在这里插入图片描述

Hash

  1. Hash索引只能用于对等比较(=,in),不支持范围查询 (between,>,<,…)。
  2. 无法利用索引完成排序操作。
  3. 查询效率高,通常只需要一次检索就可以了,效率通常要高于B+tree索引。
    在这里插入图片描述
    面试题

为什么InnoDB存储引擎选择使用B+tree索引结构?

  • 相对于二叉树,层级更少,搜索效率高。
  • 对于B-tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页(页的大小是固定的16kb)中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低。
  • 相对Hash索引,B+tree支持范围匹配及排序操作。

索引分类

在这里插入图片描述
聚集索引(一级索引):叶子节点保存行数据

非聚集索引(二级索引):叶子节点保存关联的主键

在这里插入图片描述
聚集索引选取规则:

  1. 如果存在主键,主键索引就是聚集索引。
  2. 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
  3. 如果表没有主键,或没有合适的唯一索引,则innoDB会自动生成一个rowid作为隐藏的聚集索引。

当查询时,先走二级索引查到主键id,然后根据主键id走聚集索引查到行数据,这种情况称为回表查询,因为回表查询需要走两次索引,所以相对效率较低。

在这里插入图片描述

InnoDB的主键索引高度有多高?

当高度为3时,也能存储2千万条左右的数据。

在这里插入图片描述

索引语法

在这里插入图片描述

sql分析

  • sql的执行频率
# 查看增删改查的频率
show global STATUS LIKE 'Com_______';

在这里插入图片描述

  • 慢查询日志
    在这里插入图片描述
  • profiling详情

开启
在这里插入图片描述
使用
在这里插入图片描述

  • explain执行计划
    在这里插入图片描述
    各字段的含义:
  • id

select查询的序列号,表示查询中执行selet子句或者是操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行)。

  • select type

表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询)等。

  • type

表示连接类型,性能由好到差的连接类型为NULL、system、const、eg ref、ref、range、index、all。

  • possible key

显示可能应用在这张表上的索引,一个或多个。

  • Key

实际使用的索引,如果为NULL,则没有使用索引。

  • Key_len

表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好。

  • rows

MySQL认为必须要执行查询的行数,在innodb引擎的表中,是一个估计值,可能并不总是准确的。

  • filtered

表示返回结果的行数占需读取行数的百分比,filtered 的值越大越好。

索引使用原则

  • 最左前缀法则

如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。如果跳跃某一列,索引将部分失效(后面的字段索引失效),不包含最左索引字段则全部失效(和索引字段的顺利没有关系,存在即可)。

  • 范围查询

联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。(尽量使用>=,<=)

索引失效的几种情况

  • 索引列运算

不要在索引列上进行运算操作,索引将失效。

  • 字符串不加引号

字符串类型字段使用时,不加引号,索引将失效。(存在隐式转换)

  • 模糊查询

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

  • or连接的条件

用or分割开的条件,如果or前面的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到(当or前后条件都有索引时,索引才生效)。

  • 数据分布影响

如果MySQL评估使用索引比全表更慢,则不使用索引,如对索引字段进行null值判断(name is null || name is not null)当数据库中大部分的值都是有值,那么 is null 索引生效,not null 索引失效。

sql提示

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

# 建议mysql使用这个索引,最后索引的选择还是由mysql决定
use index:
# 举个例子
explain select* from tb_user use index(idx_user_pro) where profession = '软件工程
# 忽略索引
ignore index:
# 强制使用这个索引
force index:

覆盖索引

尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到),减少select *,避免回表查询。

可通过explain查看Extra
在这里插入图片描述

前缀索引

当字段类型为字符串(varchar,text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘0,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。

在这里插入图片描述

索引设计原则

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

  2. 针对于常作为查询条件 (where)、排序(order by)、分组 (group by) 操作的字段建立索引。

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

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

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

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

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

后记
👉👉💕💕美好的一天,到此结束,下次继续努力!欲知后续,请看下回分解,写作不易,感谢大家的支持!! 🌹🌹🌹

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

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

相关文章

C语言例题38、有n个人围成一圈,顺序排号。从第一个人开始报数(从1到3报数),凡报到3的人退出圈子,最后留下来的是原来第几号人员?

#include <stdio.h> #define MAX_CALLER 3void main() {int j 0;int p_total;//人数int p_caller 0;//每3人循环计数&#xff1a;1,2,3int p_exit 0; //退出游戏的人数int people[255] {0};//参与游戏人员名单printf("请输入参与游戏人数&#xff1a;");s…

CCF-Csp算法能力认证,202206-1归一化处理(C++)含解析

前言 推荐书目&#xff0c;在这里推荐那一本《算法笔记》&#xff08;胡明&#xff09;&#xff0c;需要PDF的话&#xff0c;链接如下 「链接&#xff1a;https://pan.xunlei.com/s/VNvz4BUFYqnx8kJ4BI4v1ywPA1?pwd6vdq# 提取码&#xff1a;6vdq”复制这段内容后打开手机迅雷…

Macbook pnpm 安装 node-sass 报错(node-gyp)

换了 Macbook M3 Pro 后安装项目依赖时报错&#xff0c;提示 node-sass 安装出错。 &#xff08;此外&#xff0c;ValueError: invalid mode: rU while trying to load binding.gyp 也是类似原因。只需要确保 node-gyp 运行条件就可以&#xff09; 原因是 node-gyp 运行环境缺…

手写SpringBoot核心功能流程

本文通过手写模拟实现一个简易版的Spring Boot 程序&#xff0c;让大家能以非常简单的方式知道Spring Boot大概的工作流程。 工程依赖 创建maven工程&#xff0c;并创建两个module springboot模块&#xff1a;手写模拟springboot框架的源码实现 test模块&#xff1a;业务系统…

提升工作效率,用ONLYOFFICE打造高效团队协作环境

作为一名深耕技术领域已有六七年的开发者&#xff0c;同时又是断断续续进行技术创作将近六年的一个小小作者&#xff0c;我在工作和日常生活中&#xff0c;使用过各色各样的软件。 而在最近几年&#xff0c;一款名为ONLYOFFICE的开源办公套件逐渐走进并融入我的工作与生活&…

使用Vue连接Mqtt实现主题的订阅及消息发布

效果如下&#xff1a; 直接贴代码&#xff0c;本地创建一个html文件将以下内容贴入即可 <!DOCTYPE html> <html lang"zh-CN"> <head><meta charset"UTF-8"><meta name"viewport" content"widthdevice-width, …

为什么职场关系越来越冷漠?

不知道从什么时候开始&#xff0c;我们的职场关系变得越来越冷漠了。 早上上班打卡的时候&#xff0c;一个个都低着头&#xff0c;眼神紧紧盯着手机&#xff0c;生怕错过什么重要的信息&#xff1b; 下班后大家一哄而散&#xff0c;各自抱着手机“享受”生活&#xff0c;谁也…

如何添加、编辑、调整WordPress菜单

我们最近在使用WordPress建站建设公司网站。我们是使用的hostease的主机产品建设的WordPress网站。在建设网站使用遇到了一些WordPress菜单使用方面的问题。好在hostease提供了不少帮助。 下面把WordPress菜单使用心得分享一下。 本文将详细介绍WordPress菜单的各种功能&#x…

Total Store Orderand(TSO) the x86 MemoryModel

一种广泛实现的内存一致性模型是总store顺序 (total store order, TSO)。 TSO 最早由 SPARC 引入&#xff0c;更重要的是&#xff0c;它似乎与广泛使用的 x86 架构的内存一致性模型相匹配。RISC-V 还支持 TSO 扩展 RVTSO&#xff0c;部分是为了帮助移植最初为 x86 或 SPARC 架…

1-3ARM_GD32点亮LED灯

简介&#xff1a; 最多可支持 112 个通用 I/O 引脚(GPIO)&#xff0c;分别为 PA0 ~ PA15&#xff0c;PB0 ~ PB15&#xff0c;PC0 ~ PC15&#xff0c;PD0 ~ PD15&#xff0c;PE0 ~ PE15&#xff0c;PF0 ~ PF15 和 PG0 ~ PG15&#xff0c;各片上设备用其来实现逻辑输入/输出功能。…

使用DBeaver连接postgreSql提示缺少驱动

重新安装电脑之后用dbeaver链接数据库的时候&#xff0c;链接PG库一直提示缺少驱动&#xff0c;当选择下载驱动的时候又非常非常慢经常失败&#xff0c;尝试了一下更改源然后下载库驱动就非常快了&#xff0c;当然也包括dbeaver的自动更新。 方法&#xff1a;点击菜单栏【窗口…

霸榜!近期不容错过的3个AI开源项目,来了

在人工智能领域的迅速发展下&#xff0c;各种AI开源项目如雨后春笋般涌现&#xff0c;今天就来为大家介绍近期三个热门的AI开源项目&#xff0c;它们不仅技术前沿&#xff0c;而且非常实用&#xff0c;对于技术爱好者和业界专家来说&#xff0c;绝对不容错过。 一键创作漫画和视…

基于无监督学习算法的滑坡易发性评价的实施(k聚类、谱聚类、Hier聚类)

基于无监督学习算法的滑坡易发性评价的实施 1. k均值聚类2. 谱聚类3. Hier聚类4. 基于上述聚类方法的易发性实施本研究中的数据集和代码可从以下链接下载: 数据集实施代码1. k均值聚类 K-Means 聚类是一种矢量量化方法,最初来自信号处理,旨在将 N 个观测值划分为 K 个聚类,…

生信分析进阶2 - 利用GC含量的Loess回归矫正reads数量

在NGS数据比对后&#xff0c;需要矫正GC偏好引起的reads数量误差可用loess回归算法&#xff0c;使用R语言对封装的loess算法实现。 在NIPT中&#xff0c;GC矫正对检测结果准确性非常重要&#xff0c;具体研究参考以下文章。 Noninvasive Prenatal Diagnosis of Fetal Trisomy…

向量数据库:PGVector

一、PGVector 介绍 PGVector 是一个基于 PostgreSQL 的扩展插件&#xff0c;为用户提供了一套强大的向量存储和查询的功能&#xff1a; 精确和近似最近邻搜索单精度&#xff08;Single-precision&#xff09;、半精度&#xff08;Half-precision&#xff09;、二进制&#xff…

【代码随想录——栈与队列】

1.栈和队列理论基础 栈和队列的原理大家应该很熟悉了&#xff0c;队列是先进先出&#xff0c;栈是先进后出。 2.用栈实现队列 type MyQueue struct {head []intheadSize intstore []intstoreSize int }func Constructor() MyQueue {return MyQueue{head : make([]int,100),h…

AI智剪新风尚:一键操作,批量视频剪辑轻松入门

随着科技的飞速进步&#xff0c;人工智能(AI)已逐渐渗透到我们生活的各个领域&#xff0c;其中&#xff0c;AI视频剪辑技术的出现&#xff0c;为视频制作带来了革命性的变革。如今&#xff0c;一键操作、批量处理的AI智剪正成为视频剪辑的新风尚&#xff0c;让剪辑工作变得前所…

品牌舆情监测工作要怎么做?

一个负面舆论的传播&#xff0c;可能在短时间内对企业品牌形象造成巨大损害&#xff0c;甚至引发舆情危机。因此&#xff0c;如何有效地进行品牌舆情监测&#xff0c;成为企业不可忽视的问题。伯乐网络传媒多年网络公关、舆情监测经验&#xff0c;今天就来给大家分享一下。 一、…

【半个月我拿下了软考证】软件设计师高频考点--系统化教学-网络安全

&#x1f468;‍&#x1f4bb; 收录于专栏&#xff1a;软件设计师考点暴击 ⭐&#x1f170;️进入狂砍分⭐ ⭐软件设计师高频考点文档&#xff0c; ⭐软件设计师高频考点专栏 ⭐软件设计师高频考点⭐ &#x1f3b6;&#xff08;A) 考点1&#xff0c;网络攻击 理解记忆 &#…

Kubernetes——基础认识

目录 一、简介 1.Kubernetes是什么 2.Kubernetes特性 2.1自我修复 2.2弹性伸缩 2.3自动部署和回滚 2.4服务发现和负载均衡 2.5机密和配置管理 2.6存储编排 2.7批量处理 二、Kubernetes架构与组件 1.Master 1.1Kube-ApiServer 1.2Kube-Scheduler调度器 1.3Kube-C…