mysql索引
索引
mysql索引:
- 在MySQL中,索引是存储引擎实现的,所以没有统一的索引标准,不同存储引擎的索引工作方式也不一样,也不是所有的存储引擎都支持所有类型的索引
- 即使是多个存储引擎都支持同一种类型的索引,他们的底层实现也有可能不一样
mysql 主要的索引类型有:
- 普通索引,允许在定义索引的列中插入重复值和空值
- 唯一索引,允许空值,但列值必须唯一
- 主键索引,不允许空值,一般在建表的时候就建立了主键索引
- 组合索引,索引列组合的值必须唯一
- 全文索引,允许索引列有空值和重复值,效率高于like的模糊查询,但是精度有问题,一般不用
索引的优点(原因):
- 减少了需要扫描的数据量,大大加快了数据的检索速度;
- 索引能帮助服务器避免排序和临时表
- 索引可以把随机i/o变为顺序i/o
- 创建唯一性索引,保证数据库表中每一行数据的唯一性;
- 加速表和表之间的连接;
缺点:
- 索引需要占物理空间。
- 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度,
- 创建和维护索引的时间,会随着数据量的增加而增加,一般来说,一个表的索引不要超过6个
- 对于很小的表,全表扫描可能更高效
为什么要使用联合索引
- 减少开销,建一个联合索引(a,b,c),实际相当于建了(a),(a,b),(a,b,c)三个索引,而每多一个索引,都会增加写操作的开销和磁盘空间的开销,所以使用联合索引会大大的减少开销
- 效率高,索引列越多,通过索引筛选出的数据越少,前提是索引可以生效
常见错误:为每个列创建独立的索引
- 在多个列独立的创建单列索引,大多数情况并不能提高查询性能
- mysql引入了一中索引合并的策略,如果查询能够使用两个单列索引进行扫描,并将结果合并
- 如果出现了这种情况,就说明表的索引建的很糟糕,应该建立一个由相关列的多列索引
- 对多个索引进行合并操作时,需要耗费大量的cpu和内存资源
- 并且优化器不会把这些操作计算到查询成本中,优化器只关心随机页面读取,这样会导致查询的成本被低估,甚至不如做全表扫描
组合索引的最左原则:
-
在检索数据时从联合索引的最左边开始匹配,组合索引的第一个字段必须出现在查询组句中,这个索引才会被用到
-
重复值最少的放在最前面,一次排除的数据就越多,可提高效率
前缀索引
- 当给某一个列字段添加索引时,如果该列字段的字符串值很长时,那么我们创建的索引则会很大且很慢,这个时候如果以索引列开始的部分字符串来建立索引,那么就可以节约索引空间,从而提高索引效率,此时这种类型的索引就叫“前缀索引**”(**索引值重复性越低,查询效率就越高)
- 使用前缀索引时要尽可能降低重复的索引值,否则可能会增加查询的时间
表中数据越大,索引对性能的影响愈发重要
-
mysql中 索引是存储引擎层实现的,所以不同的存储引擎对索引的实现也不一样,常见的数据格式有:
-
b+树:
- innodb和myIsam 存储引擎都支持b+树索引
- 索引的目的就是提高查询效率,而树型结构的查找效率很高,例如二叉查找树,理想情况下每次查找都是在做二分查找
- 因为内存的易失性,通常数据都会存储在外部设备,也就是硬盘中,但和内存相比,从硬盘中读取数据的速度会非常缓慢,所以减少对硬盘的访问次数可以显著提高效率
- 而b-树就是一棵多叉的查找树,它的一个节点可以存储多个键值和数据,相对于平衡二叉树来说,因为每个节点都存储了更多的键值和数据,所以每个节点就能拥有更多的子节点,树的高度会降低很多,也就是减少了磁盘的读取次数,查找效率会更高,一般树的节点最多拥有几个子节点就被称为几阶b树
- 又因为从磁盘中读取数据是按照磁盘块来读取的,并不是一条条的读取,也就是说每次从磁盘中读取到的数据大小其实是相同的,如果能把尽可能多的数据放在一个磁盘块中,一次就能读取更多数据,也就能减少读取磁盘的次数
- b+树就是对b-树的进一步优化,b+树的非叶子节点是不存储数据的,只存储键值,所以一次能读取的数据更多,一个节点能存储的数据也会更多,树的阶数也就更大,对磁盘的读取会更少,效率也会更高
-
哈希索引
- 哈希索引是基于散列表实现的,主要用于memory存储引擎
- 哈希索引是使用索引列的值来计算hashcode值,然后再hashcode值对应位置存储所在行数据的物理位置,访问时根据hashcode值进行精确匹配,效率极高
- 但是精确匹配就需要匹配索引所有列的查询才有效,只支持等值比较,不支持范围查询
- 而且也不是按照索引值顺序排列的,所以不能用于排序,也无法区间快速查找
- 哈希索引只包含哈希值和行指针,没有真正的数据,仍然需要读取对应的行
-
全文索引
- 只能用于innodb和myisam,innodb也是再5.5以后才支持的全文索引,对于文本大对象或者较大的char类型数据,可以使用全文索引,但是全文索引会很浪费时间和空间
- 全文索引查找的不是索引中的值,而是文本中的关键字,类似于搜索引擎做的事,而不是简单的where条件匹配
- 全文索引也只适用于match against 操作
- 在相同的列上建立不同类型的索引不会冲突
b+树和b-树的区别
- b+树因为非叶子节点没有数据,所以查询一个元素必须要从根节点访问到叶子节点,而每一个叶子节点高度是相同的,所以数据的查询效率相等,而b-树有可能在查找途中结束
- b+树的数据会在叶子节点用双向链表连接起来,而且数据是按照顺序排列的,所以b+树分组查找和去重会很高效
- myisam和innodb在b+树索引实现的不同点:myisam,b+树索引叶子节点并没有存储数据,而是存储的数据地址(非聚簇索引)
innodb自适应哈希索引:
- 当innodb发现某些索引值被非常频繁的访问时,就会在原有的b+树索引上,在内存中再构建一个哈希索引
- 这让b+树索引具备了一些哈希索引的优势,可以实现非常快速查找
- 这个过程是自动的,用户无法进行控制 ,但是可以手动关闭这个特性
innodb 聚簇索引和非聚簇索引:
- 聚簇索引将数据存储与索引放在一起,索引的每一个叶子节点保存了主键值、事务id、用于事务和mvcc的回滚指针、以及所有的剩余列,如果主键是一个前缀索引,也会包含完整的主键列
- inndob 使用的聚簇索引,聚簇索引默认是主键,如果表中没有主键,会自动选择一个主键(innodb会选择一个唯一且非空的索引代替,如果没有这样的索引,会定义一个隐式的主键来作为索引,所以聚簇索引是由唯一性的)
- 会将主键组织到一颗b+树中,行数据就存储在叶子节点
- 主键以外的列构建的 B+ 树索引,称为非聚集索引, innodb 非聚簇索引,叶子节点存储的不是行数据,而是该列对应的主键,需要根据主键进行二次查找,好处是innodb在移动行时,不需要更新二级索引中的主键指针
聚簇索引的优缺点:
- 优点:
- 可相关联的数据保存在一起,能够减少从磁盘读取的数据页
- 应为索引和数据保存在一起,所以访问速度更快
- 使用索引覆盖的查询能够直接使用页节点中的主键值
- 缺点:
- 插入速度严重依赖于插入顺序,按照顺序插入是最快的方式,如果不是最好用optimize table 命令重新组织以下表,当然最好避免随机写入
- 更新聚簇索引的代价会很高,会把被更新的行移动到新的位置
- 插入新行时,如果页已满,就会造成页分裂,来容纳改行,导致表占用更多的空间
- 聚簇索引会使全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候
- 二级索引可能会很大,并且二级索引需要查找两次
mylsam 非聚簇索引:
- 将行数据与索引分开存储,索引结构的叶子节点指向了数据对应的地址
- mylsam 使用的就是非聚簇索引,无论是主键索引的b+树,还是非主键索引的b+树,叶子节点都指向真正的数据,索引树也是独立的
innodb主键:
- Innodb主键是聚簇索引,其他的二级索引中必须包含主键列,如果主键很大的话,其他的所有索引都会很大,所以如果表上的索引较多,主键应该尽可能的小
- 主键如果是自增列,可以保证是按照顺序写入,最好避免随机写入,会使得聚簇索引很糟糕,
- 例如使用uuid作为主键是非常差的选择(不仅插入时间会变长,索引也会变得更大,而且会导致更多的也分裂和碎片)
- 不按顺序写,新的主键值不一定比之前的大,所以不能直接的插到索引的最后,而是需要找到合适的位置,并且分配空间,会增加很多额外工作
- 但是对于高并发的表,顺序的主键上界会成为热点,因为所有的插入都发生在这里,会导致资源竞争
- 如果没有定义主键,innodb会选择一个唯一的非空索引替代,如果也没有这样的索引会隐式定义一个主键做聚簇索引,这样的缺点是依赖一个单点的自增值,可能会导致非常高的锁竞争
索引失效场景:
- 使用索引是没有匹配“最左匹配原则”
- 在使用索引前使用了范围查找
- 使用or关键字用不了索引
- 索引列有运算,或者函数运算
- like以%开头
- 产生类型转换
- 如果MySQL预计使用全表扫描要比使用索引快,则不使用索引
索引覆盖:
- 也就是通过索引直接获取列数据,不需要再读取行,只扫描索引,不需要回表好处有:
- 索引条目通常小于数据行大小,只读取索引可以减小数据访问量
- 索引按照列值顺序存储,返回查询比随机从磁盘读取要快的多
- 覆盖索引对innodb特别有用,如果二级索引可以覆盖查询,就不用再对主键索引二次查询
- 不是所有的索引都能称为覆盖索引,覆盖索引必须存储列的值,所以只有B+ 树索引可以
- 想要覆盖索引,就不要使用like,不要使用索引以外的列
innodb只有再访问行的时候才会加锁,索引可以减少访问的行数,从而减少锁的数量,但是如果索引不能有效过滤掉无效的行,再数据返回给服务器层后,mysql才能使用where字句,这时就无法避免的会锁定行
重复索引
-
重复索引是指在相同列上按照相同顺序建立的相同类型的索引,应该避免创建重复索引,发现之后应该立即移除
-
mysql允许在相同的列上创建多个相同索引,虽然会抛出警告,但并不会阻止,
-
MySQL需要单独维护重复的索引,优化器在优化查询的时候,也需要逐个评估,会影响性能和浪费磁盘
冗余索引
- 例如创建了(A,B)索引,在去创建(A)索引就是冗余索引,
- 还有就是创建了(A)索引,再去创建(A,id)索引,也是冗余索引
- 大多数情况下,都不需要冗余索引,应该扩展已有的索引,而不是创建新的索引,但是有的时候扩展索引会导致索引变得很大,从而影响其他使用该索引的性能
至于未使用的索引,完全是累赘,建议删除
索引使用的注意事项
- 在有更多不同值的列上创建索引会更好
- 尽可能的去重用索引,而不是建立大量的索引
- 尽可能把范围查询放到索引列的后面,对于范围查询,没办法使用到范围列后面的索引
limit数据量过大:
-
尽可能的使用索引覆盖扫描,而不是查询所有列,对于偏移量很大的时候,效率提升很明显
-
随着偏移量的增加,需要花费大量的时间来扫描需要丢弃的数据,缓存和提前计算是仅有的策略
-
更好的办法当然是减少用户能够翻到的页数(大量的翻页,没有意义)
维护表:
- 目的:找到并修复损坏的表,维护准确的索引统计信息,减少碎片
- 有可能因为硬件或者其他问题,导致表或索引损坏,会导致很多莫名其妙的问题,如果遇到了不应该出现的问题,可以试试 CHECK TABLE 来检查表是否发生了损坏,并可以使用REPAIR TABLE来修复损坏的表
- 如果存储引擎向优化器提供的扫描行数信息不准确,或者执行计划太复杂导致无法准确的获取各个阶段的匹配行数,优化器就没办法通过索引的形象来估算扫描行数,可以通过 analyze table 来重新生成统计信息
- b+树索引会产生碎片化,可以通过optimize table 命令重新组织以下表
相关文章:
mysql索引
索引 mysql索引: 在MySQL中,索引是存储引擎实现的,所以没有统一的索引标准,不同存储引擎的索引工作方式也不一样,也不是所有的存储引擎都支持所有类型的索引即使是多个存储引擎都支持同一种类型的索引,他…...
Java中怎样将数据对象序列化和反序列化?
程序在运行过程中,可能需要将一些数据永久地保存到磁盘上,而数据在Java中都是保存在对象当中的。那么我们要怎样将对象中的数据保存到磁盘上呢?这时就需要使用Java中的对象序列化。对象的序列化(Serializable)是指将一个Java对象转换成一个I/O流中字节序…...
ffmpeg filter的理解
ffmpeg filter的理解 filter的简介 从整体看,filte rgraph包含filter chain,而filter chain又包含了filter,所以可以分为是三个层次去理解。 filterfilter chainfilter graph filter graph是链接多个filter的有向图。它可以包含循环&#…...

炔活化的生物素化试剂773888-45-2,Alkyne-Biotin,炔基生物素
【产品描述】炔活化的生物素化试剂,可通过铜催化的点击反应与叠氮化物反应,产生稳定的三唑键,生物素炔烃在结构上与生物素炔烃相同。用于通过点击化学制备各种生物素化共轭物的生物素炔烃。Alkyne activated biotinylation reagents can prod…...

了解僵尸网络攻击:什么是僵尸网络,它如何传播恶意软件以及如何保护自己?
进行系统安全安排的专业人员非常了解“僵尸网络”一词。通常用于被劫持的计算机/系统链,如果指示恢复性和健壮的系统,则应很好地理解“僵尸网络”一词,因为它们的错误使用会导致巨大的混乱。 文章目录前言一、僵尸网络定义僵尸网络如何工作&a…...

大学生博主-14天学习挑战赛活动-CSDN
还在为写文没有流量发愁吗?还沉浸在假期中无法恢复状态吗?赶快来参与面向CSDN的大学生博主而举办的活动吧!本次活动为了避免刷量行为,也为了保持公平性,能够选出最优秀的文章,特意邀请了五位在C站具有一定影…...

如何自学芯片设计?
众所周知,芯片设计自学还是比较困难的,更不存在速成的。这里简单说一下学习的规划。 学会相应的知识 无论是科班毕业,还是理工科专业,想要入行IC,那就一定要具备相关的基础知识。尤其是在学校里,学习的很…...

通过中断控制KUKA机器人暂停与再启动的具体方法示例
通过中断控制KUKA机器人暂停与再启动的具体方法示例 中断程序的基本介绍: 当出现例如输入信号变化等事先定义的事件时,机器人控制器中断当前程序,并处理一个已定义好的子程序 由中断而调用的子程序称为中断程序 最多允许同时声明32个中断 同一时间最多允许有16个…...

pandas基本操作
df.head()/tail() 查看头/尾5条数据;df.info 查看表格简明概要;df.dtypes 查看字段数据类型;df.index 查看表格索引;df.columns 查看表格列名;df.values 以array形式返回指定数据的取值;list(dt.groupby(&q…...

论文笔记NeRF: Representing Scenes as Neural Radiance Fields for View Synthesis
NeRF使用神经网络来表示场景。给定一个场景,输入该场景稀疏的视角图片,NeRF可以合成该场景新的视角的图片。 神经辐射场 神经辐射场(neural radiance field,NeRF)使用5D的向量值函数表示一个场景。 输入是连续的5D坐…...

花3个月面过京东测开岗,拿个20K不过分吧?
背景介绍 计算机专业,代码能力一般,之前有过两段实习以及一个学校项目经历。第一份实习是大二暑期在深圳的一家互联网公司做前端开发,第二份实习由于大三暑假回国的时间比较短(小于两个月),于是找的实习是在…...
Leetcode DAY 35:柠檬水找零and根据身高重建队列 and用最少数量的箭引爆气球
860.柠檬水找零 class Solution { public:bool lemonadeChange(vector<int>& bills) {int five 0;int ten 0;for(int i 0; i < bills.size(); i) {if(bills[i] 5) {five;} else if(bills[i] 10) {ten;five--;if(five < 0){return false;}} else {if(ten …...

java-spring_bean实例化
bean是如何创建的实例化bean的三种方式构造方法静态工厂(了解)实例工厂与FactoryBean实例工厂FactoryBeanbean是如何创建的实例化bean的三种方式 构造方法 bean本质上就是对象,创建bean使用构造方法完成 提供可访问的构造方法 public clas…...

微信中如何接入机器人才比较安全(不会收到警告或者f号)之第三步正式接入
大家好,我是雄雄,欢迎关注微信公众号:雄雄的小课堂。 前言 前面两篇文章分别介绍了下chatgpt接入方式: 微信中如何接入chatgpt机器人才比较安全(不会收到警告或者f号)之第一步登录微信 微信中如何接入chatgpt机器人才比较安全(不会收到警告或者f号)之第二步注入dll文件…...

高通平台开发系列讲解(Sensor篇)IAM20680驱动程序的使用
文章目录 一、相关编译宏二、设备树配置三、打开iio自动挂载脚本四、模块加载验证沉淀、分享、成长,让自己和他人都能有所收获!😄 📢本篇文章将介绍 IAM20680 驱动程序。 一、相关编译宏 目录:arch/arm/configs CONFIG_IIO_BUFFER=y CONFIG_IIO_KFIFO_BUF=y CONFIG_II…...
【VictoriaMetrics】VictoriaMetrics集群伪分布式部署(二进制版)
VictoriaMetrics支持单机版以及集群版部署,通常情况下,官方建议使用单节点版本,而不是集群版本,如果摄取速率低于每秒一百万个数据点,单节点版本可以完美地扩展CPU内核、RAM和可用存储空间的数量。与集群版本相比,单节点版本更容易配置和操作,因此在选择集群版本之前要三…...

华为手表开发:WATCH 3 Pro(7)获取电量信息
华为手表开发:WATCH 3 Pro(7)获取电量信息初环境与设备文件夹:文件新增第二页面showBatteryInfo.hmlshowBatteryInfo.js修改首页 -> 新建按钮 “ 跳转 ”index.hmlindex.js 引用包:system.router首页效果点击结果初…...

【数据结构】动态顺序表的接口实现(附图解和源码)
动态顺序表的接口实现(附图解和源码) 文章目录动态顺序表的接口实现(附图解和源码)前言一、定义结构体二、每一个接口的实现原理(附图解)1.初始化顺序表2.增容顺序表3.尾插数据4.删除顺序表信息(…...
L2-003 月饼
月饼是中国人在中秋佳节时吃的一种传统食品,不同地区有许多不同风味的月饼。现给定所有种类月饼的库存量、总售价、以及市场的最大需求量,请你计算可以获得的最大收益是多少。 注意:销售时允许取出一部分库存。样例给出的情形是这样的&#…...
volatile不等于原子操作
volatile作用 确保数据每次都从源头读取,即每次都从内存中读取,不从缓存中读取。 这样做的目的是确保不会被优化 int i 0;int main(int argc, char **argv) {const char *str;if (i 0) {str "hello";} else {str "world";}ret…...
模型参数、模型存储精度、参数与显存
模型参数量衡量单位 M:百万(Million) B:十亿(Billion) 1 B 1000 M 1B 1000M 1B1000M 参数存储精度 模型参数是固定的,但是一个参数所表示多少字节不一定,需要看这个参数以什么…...
c++ 面试题(1)-----深度优先搜索(DFS)实现
操作系统:ubuntu22.04 IDE:Visual Studio Code 编程语言:C11 题目描述 地上有一个 m 行 n 列的方格,从坐标 [0,0] 起始。一个机器人可以从某一格移动到上下左右四个格子,但不能进入行坐标和列坐标的数位之和大于 k 的格子。 例…...
C++ 基础特性深度解析
目录 引言 一、命名空间(namespace) C 中的命名空间 与 C 语言的对比 二、缺省参数 C 中的缺省参数 与 C 语言的对比 三、引用(reference) C 中的引用 与 C 语言的对比 四、inline(内联函数…...
数据链路层的主要功能是什么
数据链路层(OSI模型第2层)的核心功能是在相邻网络节点(如交换机、主机)间提供可靠的数据帧传输服务,主要职责包括: 🔑 核心功能详解: 帧封装与解封装 封装: 将网络层下发…...

Ascend NPU上适配Step-Audio模型
1 概述 1.1 简述 Step-Audio 是业界首个集语音理解与生成控制一体化的产品级开源实时语音对话系统,支持多语言对话(如 中文,英文,日语),语音情感(如 开心,悲伤)&#x…...

前端开发面试题总结-JavaScript篇(一)
文章目录 JavaScript高频问答一、作用域与闭包1.什么是闭包(Closure)?闭包有什么应用场景和潜在问题?2.解释 JavaScript 的作用域链(Scope Chain) 二、原型与继承3.原型链是什么?如何实现继承&a…...

力扣热题100 k个一组反转链表题解
题目: 代码: func reverseKGroup(head *ListNode, k int) *ListNode {cur : headfor i : 0; i < k; i {if cur nil {return head}cur cur.Next}newHead : reverse(head, cur)head.Next reverseKGroup(cur, k)return newHead }func reverse(start, end *ListNode) *ListN…...
【Android】Android 开发 ADB 常用指令
查看当前连接的设备 adb devices 连接设备 adb connect 设备IP 断开已连接的设备 adb disconnect 设备IP 安装应用 adb install 安装包的路径 卸载应用 adb uninstall 应用包名 查看已安装的应用包名 adb shell pm list packages 查看已安装的第三方应用包名 adb shell pm list…...
深度剖析 DeepSeek 开源模型部署与应用:策略、权衡与未来走向
在人工智能技术呈指数级发展的当下,大模型已然成为推动各行业变革的核心驱动力。DeepSeek 开源模型以其卓越的性能和灵活的开源特性,吸引了众多企业与开发者的目光。如何高效且合理地部署与运用 DeepSeek 模型,成为释放其巨大潜力的关键所在&…...

客户案例 | 短视频点播企业海外视频加速与成本优化:MediaPackage+Cloudfront 技术重构实践
01技术背景与业务挑战 某短视频点播企业深耕国内用户市场,但其后台应用系统部署于东南亚印尼 IDC 机房。 随着业务规模扩大,传统架构已较难满足当前企业发展的需求,企业面临着三重挑战: ① 业务:国内用户访问海外服…...