MySQL索引自己整理的笔记希望能帮到大家

最新推荐文章于 2022-03-13 14:33:51 发布
最新推荐文章于 2022-03-13 14:33:51 发布
阅读量277 收藏
点赞数
分类专栏： Mysql 文章标签： mysql sql 索引 数据库 数据结构
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_44619964/article/details/107006631
版权

文章目录

面试题

MySQL索引

索引为什么不用二叉树

如果索引是自增，就像上图这种情况，树状结构右边的子节点肯定是大于父节点的，那么就跟链表结构没有区别。最终跟全盘扫描没什么区别。还是会从磁盘中一次一次的遍历寻找。

假设我要查找7这个元素那么最多也要查找7次才能找到存储节点的指针信息

变成链表结构效率极低

索引为什么不用红黑树(局部平衡)

在这里插入图片描述

红黑树只能局部平衡，当右边的子节点大于一个节点并且左边没有节点的时候会自动平衡（节点单边增长的元素不能2以上，不然就会平衡）

假设我要查找7这个元素那么最多也要查找4次，比二叉树快多了，但是在大数量中，单一节点上扩张，有点浪费。

数据量越大查找效率越低

结论：从上面得出查询的次数跟树的高度有关，那么如果将高度减少，查询的速度是不是就变快了呢

纵向不能发展，那就横向发展

横向存储的元素越多，树的高度越小

B树

疑问1：为什么不把全部数据都放到一个节点上？

因为假设有几千万的数据都放一个节点上面，从磁盘上读取几个G的内容，内存RAM吃不消，IO慢。

所以节点的大小得合适，mysql对节点得大小设置的是16kb（可以通过show global status like 'Innodb_page_size'）查看。

MySQL不是用的b树，而是对b树做了一点点改造得到了一种叫 b+树的数据结构

b+树

空白格子存储的是下一个节点的磁盘地址

一般主键都是bigint类型占8个字节(8b)

mysql源码对这个空白位置地址分配了大小大概是6个字节(6b)

索引|地址|索引|地址…也就是说mysql配置的默认16kb大概可以放1170个索引在这里

假设叶子节点一个元素占1kb那么就能放16个元素，那么树的高度是3且所有元素都装满了，通过

1170*1170*16就能算出所有的索引元素个数。

B+树比B树叶子节点多一个指针，就是为了支撑范围查找。

B+树叶子节点从左到右依次递增的趋势，每两个节点有一个双向指针，这样就能支持范围查询。如果没有这个指针，那么每次都要从根节点重新开始找。

疑问1：为什么要把子节点的data移到叶子节点上

因为1：一个节点放16kb大小，将data数据移到叶子节点就能放更多的索引了。

疑问2：为什么要有冗余索引？

因为2：冗余加快查找速度，减少内存的消耗。

Hash

通过特定的MySQL的Hash算法就能得出数据的地址信息，但是不使用它，因为它只支持等值查询、无法排序、存在hash冲突虽然MySQL底层解决冲突很好。

MySQL存储引擎

存储引擎是形容表的

MyISAM

.frm(表的框架信息)

.MYD(data表的数据行记录)

.MYI(index存储表的索引字段)

如果字段是索引，那么就先去MYI文件查找索引所在磁盘的哪个文件地址，根据文件地址指针就能定位到MYD。

MyISAM查询数据需要回表

InnoDB

.ibd(index，data，数据文件和索引文件的合并)
在这里插入图片描述

聚集索引（聚簇索引）聚合的意思就是 主键和数据是放在一起的。

innoDB必须要有主键索引，如果没有建主键，它会自动在你唯一字段里变成主键，如果没有唯一的字段，它会自动建一个隐藏的rowId。

疑问1：为什么推荐使用整形的自增主键？

因为1：整型做比较的时候比较快（1<2 abc<dbc）,字符串还需要转成ASCII码去比对，而且整型占用的存储空间更有优势，节约资源。

联合索引

索引最左侧原理，如果第一个字段不是建立索引的第一个字段那么就会失效，比如：

(a,b,c),就只能 a,b,c|a,b|a索引才会生效

Explain

以下通过explain显示出mysql执行的字段内容：

  • id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
  • select_type: SELECT 查询的类型.
  • table: 查询的是哪个表
  • partitions: 匹配的分区
  • type: join 类型
  • possible_keys: 此次查询中可能选用的索引
  • key: 此次查询中确切使用到的索引.
  • ref: 哪个字段或常数与 key 一起被使用
  • rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
  • filtered: 表示此查询条件所过滤的数据的百分比
  • extra: 额外的信息

索引查询失效的几个情况

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

3、组合索引，不是使用第一列索引，索引失效。

4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

5、在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。

6、在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

7、对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）

8、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

经供参考，如有不足错误，请纠正，互相帮助！

qq_1045857717
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
目标检测论文精读（4）－ Fast R-CNN
zhli99的博客
04-27 655
Fast R-CNN阅读重点IntroductionChallengesArchitectureInitializeMini-batch samplingMulti-tast lossTruncated SVDScale invarianceInnovationResultTest on Pascal VOC 2007、2010、2012Training and testing timeTimin...
MySQL 索引整理（一）
weixin_51889618的博客
01-14 109
索引是帮助MySQL高效获取数据的数据结构，其本质是一种数据结构，满足特定的查找算法。引入索引的目的是为了减少查找数据过程中的磁盘IO次数。
MySQL 索引笔记（个人）
小默学习笔记
06-11 200
InnoDB也有hash索引，只不过叫自适应哈希 平衡树，当查询需求和插入需求差不多的时候，就很不友好，这时候就过度到了红黑树 AVL树最长路径只要不超过最短路径的两倍即可 InnoDB 存储引擎加载的时候，默认加载16kb p1、p2、p3 指向其他磁盘块的指针，p4、p5 索引列的值，data 表明当前key对应的整行数据 上面那张图是B树，因为数据存放很占内存，所以，在B+树里面，我们只在叶子节点存放整行数据，其他时候存放指针和索引列的值就行 MySQL里面，3层B+树可以支撑千万级别的并发量 B.
Mysql学习笔记------索引
programmer_hard的博客
02-19 244
索引内容比较多,一次性学不完就分批记录吧. 说起优化sql,很多人第一反应就是添加索引,那索引到底是什么,为什么能优化sql? 这也是我学习索引的目的. 概念: 索引是协助快速查询,更新数据库表中数据的有序的数据结构. Mysql的数据模型: 这也是我今天学习的重点,我一直认为想对一项技术了解的更深入,不妨从它为什么被创造,解决了哪些问题开始了解. 先从有序数组,链表结构说起,这两种结构是我们常...
Mysql索引与B+Tree关系 详解
小哥骑单车
07-07 4123
上一篇： 红黑树详解 目录 1、先来了解几个概念 （1）、操作系统磁盘局部性原理： （2）、这里我们讲一下mysql的int类型字段（考虑数据库索引用int类型）： 2、为什么会有B-tree? B-tree的衍生： 3、B-tree介绍 4、B+tree 5、B+Tree，B-Tree 和 二叉树的区别： 1、先来了解几个概念 （1）、操作系统磁盘局部性...
Mysql索引笔记
眼前一座山，踏平它
09-17 265
1、InnoDB 聚簇索引：索引文件和数据文件在一起 主索引的叶子节点直接保存的数据，辅助索引的叶子节点存储的主键值。 支持事务，行级锁，支持B+tree索引，支持fulltext索引，不支持hash索引 2、MyISAM 非聚簇索引：索引文件和数据文件分开存储 主索引和辅助索引的结构完全相同，叶子节点存储数据的物理地址 不支持事务，表级锁，支持B+tree索引，支持fulltext...
笔记整理-mysql索引
qq_42114504的博客
03-13 1836
MySQL的索引分类 索引分类 1.普通索引index :加速查找 2.唯一索引 主键索引：primary key ：加速查找+约束（不为空且唯一） 唯一索引：unique：加速查找+约束 （唯一） 3.联合索引 -primary key(id,name):联合主键索引 -unique(id,name):联合唯一索引 -index(id,name):联合普通索引 4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。 创建索引 Alter table ‘tablename’add prima
MySQL学习笔记(3)——索引练习
12-14
根据之前的MYSQL学习笔记(2)的学习，通过一些小练习来加深对索引的理解。 建下表并插入数据最后建立相关索引： CREATE TABLE IF NOT EXISTS `test`( id int PRIMARY KEY auto_increment, c1 char(10), c2 char(10), ...
mysql索引笔记1
08-08
MySQL 是一种广泛使用的开源关系型数据库管理系统，其索引机制对于提升查询性能至关重要。本文将深入探讨MySQL中的索引及其相关知识点。 首先，我们来了解索引的优点和缺点。索引如同书籍的目录，能够快速定位到...
MYSQL读书笔记整理
04-12
MySQL数据库是世界上最受欢迎的关系型数据库管理系统之一，尤其在Web应用程序中广泛应用。它的InnoDB存储引擎提供了事务处理、行级锁定以及外键支持等功能，确保了数据的可靠性和并发性。以下是对MySQL，尤其是...
mysql高性能索引读书笔记
11-01
《高性能Mysql》第五章的读书笔记，记录的都是要点，以思维导图的方式呈现。
MySQL实战45讲——索引（笔记）
weixin_42094659的博客
07-21 310
索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。 索引常见模型 哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的值即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。哈希表这种结构适用于只有等值查询的场景； 有序数组在等值查询和范围查询场景中的性能就都非常优秀，但是插入、删除成本较高，有序数组索引只适用于静态存储引擎； 二叉搜索树的特点是：每个
yolo_v1学习笔记
蛋炒饭的博客
11-08 155
yolo_v1学习笔记   注：第一次学习目标检测神经网络，目标检测训练数据的标签与分类任务的不同，有框的位置及大小置信度等参数，不只是简单的类别。 核心思想 将物体检测方法看作一个回归问题，来预测空间分离的边界框和相关类别的概率。 将图片隐式(理论处理上)的分为S*S个网格。 物体的中心落在哪个网格内，哪个网格就负责预测这个物体。 每个网格要预测B个框，C个类别。   这种预测方式导致网格内出...
MySQL索引笔记
qq_44847658的博客
04-12 88
MySQL索引什么是索引？有什么用？一.什么时候要添加索引二.索引的实现原理三.索引的分类 什么是索引？有什么用？ 1.索引就相当于一本书的目录，通过目录可以快速的找到对应的资源 2.就是缩小了扫描的范围 ##注意：索引虽然可以提高检索效率，但不是能随意的添加，因为索引也是数据库当中的对象，也需要数据库不断维护，是有维护成本的。比如，表中的数据经常被修改这样就不适合添加索引，因为数据一旦修改，索...
mysql16kb,MySQL学习(三)
weixin_32073733的博客
03-17 386
索引结构我们都知道MySQL innodb myisam 都得索引结构是用的b+tree 来实现的,但是我们为什么不适用hash表来实现呢?hash表在单独取出数据的时候非常快速,但是不支持范围查找 举个例子来说你存放进去一个数据 根据hash在某个位置,下一次取出的时候,可以直接取出来.但是不能对这个数据进行一个范围查找,因为hash是散列存放的为什么不用平衡二叉树?拒教程讲的是找一次需要进行一...
MYSQL-索引笔记
zongx的博客
11-05 106
1、mysql 的唯一索引要求所有参与的列都不能够为 null 值，如果唯一索引中的任何一个元素含有 null 值，则唯一约束将不起作用。 insert into tb(a,b,c) values (null,null,null); -- ok insert into tb(a,b,c) values (null,null,null); -- still ok insert into tb(a...
mysql 索引- 笔记
weixin_34106122的博客
04-04 73
索引    mysql最常用的索引结构是btree(O(log(n)))，但是总有一些情况下我们为了更好的性能希望能使用别的类型的索引。hash就是其中一种选择，例如我们在通过用户名检索用户id的时候，他们总是一对一的关系，用到的操作符只是=而已，假如使用hash作为索引数据结构的话，时间复杂度可以降到O(1)。不幸的是，目前的mysql版本(5.6)中，hash只支持MEMORY和NDB两种引...
索引笔记（一）
三木
10-10 656
现在的软件系统建设中数据库还是实际上的系统核心，所以对于系统性能的优化也是以优化数据库为主的。而在数据库优化时索引的建立和使用情况尤为重要。上几天在写一个统计用的sql语句时忽然觉得自己对索引的了解很泛泛，所以就萌生了重温索引想法。笔记首先提到了表的存储和读取的简单原理，这有助与后面对索引的了解。然后是索引的作用和相关的一些语法；相关的注意点；基本策略和优化策略；优化的基本原则；特殊形式的索引等等...
《高性能MySQL》读书笔记－－索引
热门推荐
上善若水，水善利万物而不争。
03-06 1万+
索引（在MYSQL中也叫做键&lt;key&gt;），是存储引擎用于快速找到记录的一种数据结构。写在前面：索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在...
创建mysql索性_【MySQL学习笔记】 MySQL 建立索引
最新发布
06-10
在 MySQL 中，可以使用 `CREATE INDEX` 语句来创建索引。具体语法如下： ``` CREATE [UNIQUE] INDEX index_name ON table_name (column_name1 [ASC|DESC], column_name2 [ASC|DESC], ...) ``` 其中： - `index_name`：索引名称，可以自定义命名。 - `table_name`：要在该表上创建索引的表名。 - `column_name1, column_name2, ...`：要在其上创建索引的列名。可以在一个索引中指定多个列。 - `ASC` 和 `DESC`：排序方式，用于指定每个索引列的排序方式。默认情况下，索引是按升序排序的（即 `ASC`）。 如果要创建唯一索引，则在 `CREATE` 关键字后面加上 `UNIQUE` 关键字，如下所示： ``` CREATE UNIQUE INDEX index_name ON table_name (column_name1 [ASC|DESC], column_name2 [ASC|DESC], ...) ``` 执行完毕后，MySQL 将在相应的表上创建相应的索引。注意，创建索引可能会占用较多的磁盘空间，并且在插入、更新和删除数据时可能会影响性能。因此，需要根据具体的业务需求和数据量来决定是否需要创建索引。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值