导航在左侧的网站欣赏,选择适合的配色方案和风格,网站备案会检查空间,广州哪里有做网站推广本篇笔记是个人整理的索引知识总结#xff0c;刚开始有点乱#xff0c;后续会一直边学边整理边总结
索引#xff08;index#xff09;是帮助MySQL高效获取数据的数据结构(有序)。就好比索引就是数据的目录 索引结构 Btree索引,Hash索引,Full-text索引#xff0c;R-tree(空…本篇笔记是个人整理的索引知识总结刚开始有点乱后续会一直边学边整理边总结
索引index是帮助MySQL高效获取数据的数据结构(有序)。就好比索引就是数据的目录 索引结构 Btree索引,Hash索引,Full-text索引R-tree(空间索引
创建的主键索引和二级索引默认使用的是BTree索引。Btree是一种多叉树叶子结点才存放数据非叶子结点只存放索引有根节点和其他不是叶子结点的结点而且每个结点里的数据是按主键顺序存放的。
每一层 父结点的索引值 都会出现在下层子结点的索引值中
因此在叶子结点中包括了所有的索引值信息
并且每个叶子结点都有两个指针分别指向下一个叶子结点和上一个叶子结点形成双向链表 物理存储 聚集索引二级索引 有了主键索引为什么还要有二级索引
二级索引里面他存储的是主键值所以先去二级索引里面查找这个主键值然后再通过这个主键值去一级索引查找他的实际数据因为一级索引主键索引里面存储的是主键数据 主键索引的Btree叶子结点 存放的是实际数据所有完整的用户存放在主键索引的Btree的叶子结点里 二级索引的Btree的叶子结点 存放的是主键值而不是实际数据 回表是什么 这种先到二级索引中查找数据找到主键值然后再到聚集索引中根据主键值获取 数据的方式就称之为回表查询。 具体过程如下 : ① . 由于是根据 name 字段进行查询所以先根据 nameArm 到 name 字段的二级索引中进行匹配查 找。但是在二级索引中只能查找到 Arm 对应的主键值 10 。 ② . 由于查询返回的数据是 * 所以此时还需要根据主键值 10 到聚集索引中查找 10 对应的记录最 终找到 10 对应的行 row 。 ③ . 最终拿到这一行的数据直接返回即可。 字段特性主键唯一普通前缀索引
主键索引就是建立在主键字段上的索引通常在创建表的时候一起创建 一张表最多只有一个主键索引 唯一索引建立在union字段上的索引一张表可以有多个唯一索引 每个唯一索引可以用于不同的列或列的组合确保这些列或列组的值唯一 索引列的值必须唯一这意味着在索引列中不能存在两个完全相同的值但是允许有空值 普通索引建立在普通字段上的索引 不要求字段为主键 也不要求字段为unique 前缀索引使用它的目的是 减少索引占用的存储空间 提升查询效率 字段个数分类单列索引联合索引
单列就是建立在单列上的索引 比如主键
联合就是建立在多列 联合索引通过将多个字段组合成一个索引 联合索引 他的非叶子结点用两个字段的值作为Btree的key值当在联合索引查询数据的时候先按product_no字段比较
这里不懂什么意思了 联合索引范围查询
他里面有一些特殊情况不是查询过程中使用了联合索引查询查询就代表联合索引中的所有字段 都用到了联合索引进行索引查询就是不能以偏盖全 联合索引存在最左匹配原则最左匹配原则就是按照最左优先的方式进行索引的匹配。
使用联合索引的时候 不遵循这个原则那就会失效就不能利用索引快速查询了 看这个例子第一个例子是可以匹配上联合索引的第二个例子匹配不上联合索引。
因为第一个是先按a排序a相同再按b排序b相同再按c排序。
第二个直接跳过a直接跳过那就没有最左优先就不符合最左匹配原则那联合索引就会失效。 b和c是全局无序局部相对有序的意思就是b,c这个在局部是有序的但他前面的a没有那就全局无序 看图看第二个字段a,b,o,I,I,m,p,g,w他没有按26个字母顺序排序的他是无序的。在a的基础上建立索引 a是全局有序的122345678 b是全局无序的12782381052直接执行where b 2无法利用联合索引
利用索引的前提索引里的key是有序的
无序你怎么查乱查要是数据那么多直接扔进去查效率会很低 联合索引范围查询
就上面说的那个特殊情况不是查询过程中使用了联合索引查询查询就代表联合索引中的所有字段 都用到了联合索引进行索引查询就是不能以偏盖全也可能存在部分字段用到联合索引的Btree部分字段没有用到 这特殊情况 发生在范围查询。
就联合索引的最左匹配原则会一直向右匹配就是从最左依次向右直到遇到 范围查询 就会停止匹配。
就是说范围查询的字段可以用到联合索引比如a1但是在范围查询字段的后面的字段就无法用到联合索引比如b2 联合索引二级索引先按照a字段的值排序的符合a
这个又是什么玩意 额Q1这条语句他a1这个字段用到了联合索引b2没有用到。
在a1条件的二级索引记录的范围里b字段的值是无序的 这段话说明了为什么在这个查询中b 字段的条件无法利用联合索引来加速查询我的理解 联合索引会按照索引列的顺序对数据进行排序。
比如有一个表中有两个字段a和b你对这两个字段建立了联合索引(a, b)那么数据库会首先根据a字段的值进行排序
如果a字段的值相同再根据b字段的值进行排序。 索引扫描与查询条件
假设你查询a 1的记录数据库可以快速找到第一个满足a 1的记录并从该位置开始顺序扫描其他记录。
由于数据在a字段上是排序的数据库可以高效地定位到第一个符合条件的记录。
b字段的无序性
在a 1的查询范围内b字段的值是无序的。即使你在查询中增加条件b 2也无法进一步缩小扫描的范围。
这是因为虽然a字段在索引中是有序的但在a 1的范围内b字段的值并不是按照b字段的顺序排列的。 就是说这个b2与这个a1无瓜。。。 因此在这种情况下b字段的索引无法被有效利用数据库仍然需要扫描所有a 1范围内的记录检查每条记录的b值是否等于2。 数据库首先根据a 1的条件找到满足该条件的记录也就是上面的三条记录
a 5, b 8
a 6, b 10
a 7, b 5
但是b字段的值在这些记录中是无序的8、10、5这个一看既不是连续的也不是什么升序降序。因此尽管我们加了b 2的条件但是 但是 b字段在满足a1的这些记录中是无序的上面说过 即使 你加了这个什么b2的条件数据库还是要逐条扫描 这些记录来检查b字段的值 具体来说数据库会这样执行查询
首先定位到第一个满足 a 1 的记录a 5, b 8。
然后继续顺序扫描接下来的记录a 6, b 10 和 a 7, b 5。
每扫描到一条记录数据库都会检查 b 字段的值是否等于 2。
因为b字段的值是无序的数据库无法跳过任何记录所以必须扫描所有满足a1的记录。 因为b字段的值是无序的数据库无法跳过任何记录为什么
因为在联合索引中数据首先根据第一个字段如 a 字段排序而第二个字段如 b 字段只在前一个字段相同的情况下才有序。这个上面有一直在说 当你在联合索引 (a, b) 上执行查询时如果你的查询条件是 a 1 AND b 2数据库会首先利用 a 字段来定位满足 a 1 的记录。然而由于联合索引首先是根据 a 字段排序的所以在 a 1 的范围内b 字段的值并没有按照特定顺序排列。这意味着数据库无法预先知道哪些记录的 b 字段等于 2因此它无法直接跳到这些记录。 就是说b2他不在范围查询a1的这个范围内所以就无法直接跳到这些记录 具体原因如下
索引的有序性
联合索引 (a, b) 按照 a 字段的顺序排列记录。如果 a 字段的值不同b 字段的顺序是无关紧要的因此在不同的 a 值下b 字段的值不会有序排列。a都不相同了那b更无所谓了
全扫描的必要性
当数据库在满足 a 1 的记录中查找 b 2 时因为 b 字段在这些记录中是无序的数据库无法通过索引直接跳过不符合条件的记录。它必须逐条扫描每一条满足 a 1 的记录检查 b 字段是否等于 2。
缺少有序条件
如果 b 字段的值在 a 1 的范围内是有序的数据库就可以利用二分查找等高效算法直接跳过不符合 b 2 条件的记录。但是由于无序性数据库只能通过顺序扫描来找到匹配的记录。
举个例子
假设联合索引 (a, b) 中有如下记录
a b
5 8
6 10
7 5
8 2
9 3
当你查询 a 5 AND b 2 时数据库会先定位到 a 6 开始的记录。因为 b 字段在这些记录中是无序的数据库必须检查 a 6、a 7、a 8 的所有记录直到找到 b 2 的记录在 a 8 时。就是在a5之后找找到与b2对应的那个a 如果 b 字段在 a 5 范围内是有序的数据库就可以直接跳到 b 2 的记录而不必扫描之前的所有记录。但因为无序性数据库没有办法这样做只能逐条检查。 执行计划中的key_len
通过key_len我们可以知道优化器具体使用了多少个字段的搜索条件来形成扫描区间的边界条件
如果 key_len显示使用了两个字段如 a 和 b这意味着查询条件中的这两个字段都被用来形成索引扫描的边界条件进一步缩小扫描范围提高查询效率。 对这里举的这个例子的理解
首先前提是a和b都是int类型的非null的字段。
联合索引idx_ab 是一个联合索引包含了两个字段 a 和 b。
key_len这是一个表示数据库查询计划中索引长度的参数。对于 int类型的字段通常占用 4 个字节。如果字段允许为 NULL则需要增加 1 个字节用于存储 NULL 的信息所以可能占用 5 个字节。
这里查询显示key_len为4字节意味着在这个查询中数据库仅使用了联合索引的a字段
a是int占用4字节的空间b也使用索引那么总的就是8个字节但是这里key_len只是4字节按照最左前缀匹配原则所以只使用了a字段 来到Q2语句Q2语句和Q1语句唯一的区别就是大于和大于等于的区别
在之前a1 的时候b2是无序的但是现在a1,这里面包含了a1对于符合a1 夜间的二级索引记录的范国里b字段的值是「有序」的(因为对于联合索引是先按照 a字段的值排序然后在a字段的值相同的情况下再按照b字段的值进行排序联想到这个最左匹配原则去思考问题 Q2这条语句a1和b2都用到了联合索引
这里是一些分析 a字段的索引利用
定位首条记录当你使用 a 1的条件时数据库可以利用 a 字段在联合索引中的排序顺序直接跳到符合条件的第一条记录然后沿着索引链表逐条扫描直到遇到不满足 a 1 的记录为止。 b字段的索引利用
有序性对于 a 值相同的记录b字段是有序的。这意味着当你在 a 1 的范围内查询 b 2 时数据库可以通过索引快速定位到满足 a 1 AND b 2 的第一条记录而不必扫描所有 a 1 的记录。这就是为什么 b 字段的条件在这种情况下也能利用联合索引进行优化。 对于a值相同的记录b字段是有序的理解
拿这张表来看联合索引a,b
(1, 1)
(1, 2)
(1, 3)
(2, 1)
(2, 2)
(3, 1)
对于a1的记录b字段的值是按照去123的顺序排列的诠释了对于a值相同的记录b字段是有序的确实效率高了很多诶 为什么是这样的呢
因为 联合索引在创建时会按照多个字段的顺序进行排序和存储 当你创建一个联合索引 (a, b)时数据库会先按照第一个字段 a对数据进行排序。
在 a 值相同的情况下数据库会再根据第二个字段 b 进行排序。因此对于 a 值相同的所有记录b字段的值是按照升序或降序排列的。 这里同样使用上面的例子只不过这里key_len为8个字节也就是a和b字段都用到了联合索引进行索引查询 Q3这条语句Q4这条语句昨晚看了之后 发现其实只要是范围查询和这个范围里面有和b值相等的那两者都用到了联合索引 总结
联合索引的最左匹配原则在遇到范围查询什么,的时候就会停止匹配mlgb的意思就是范围查询的字段可以用到联合索引但是在范围查询的后面的字段无法用到联合索引
注意对于 、、BETWEEN、like 前缀匹配的范围查询并不会停止匹配 索引下推
可以在联合索引遍历过程中
对联合索引中包含的字段先做判断
直接过滤掉不满足条件对记录减少回表次数
回表是什么
指数据库查询中使用二级索引的时候数据库需要通过索引找到匹配的记录然后再去主表中找到完整的数据行。大概就是先去二级索引找主键值然后在去主键索引中找实际数据 回表的流程如下
1 通过索引查询数据库使用二级索引 查找到满足条件的记录
2 回表查找如果查询的字段 不在索引里数据库就需要通过索引中的行标识符比如主键 去查找对应的完整数据行
3 返回结果 索引区分度
区分度又是什么
简单了解一下 区分度 就是 衡量一个索引列或多列组合在数据库表中能够区分不同数据行的能力
越靠前的字段被用于索引过滤的概率越高。
实际开发中建立联合索引时要把区分度大的字段排在前面他越有可能被更多的SQL使用q 联合索引进行排序的例子
给定的 SQL 语句是
SELECT * FROM orders WHERE status 1 ORDER BY create_time ASC;
初步分析
status 列用于筛选数据。
create_time 列用于对结果集进行排序。
假设 orders 表的数据量较大如果没有索引或者只给 status 列单独建立索引数据库在执行这条查询时会有以下问题
筛选效率虽然 status 上的索引可以加速筛选但筛选出来的数据依然需要排序。
排序开销MySQL 可能会使用 filesort文件排序来对结果进行排序这个过程可能比较耗费资源尤其是在数据量大的情况下。
如何优化
最佳做法是为 status 和 create_time 列创建一个联合索引。避免mysql数据库发生文件排序 1 减少 filesort
联合索引可以利用索引的有序性使得在 status 1 的前提下数据已经按照 create_time 排序从而避免了额外的排序操作。
这样查询在提取数据时数据已经按照 create_time 有序不需要再进行额外的排序即不会出现 Using filesort。
2 覆盖索引的潜力
如果在查询中你只选择了一些字段如 SELECT status, create_time FROM orders WHERE status 1 ORDER BY create_time ASC联合索引不仅能加速查询还可以作为覆盖索引避免回表操作进一步提升效率。
实施步骤
你可以通过以下 SQL 语句创建联合索引
CREATE INDEX idx_status_create_time ON orders(status, create_time);
这条命令会在 status 和 create_time 列上创建一个联合索引。
查询执行计划
使用联合索引后你可以通过 EXPLAIN 来查看查询计划确保 Extra 列没有出现 Using filesort并且索引已经被正确使用。
EXPLAIN SELECT * FROM orders WHERE status 1 ORDER BY create_time ASC;
如果优化成功你会看到索引 idx_status_create_time 被使用且没有文件排序查询效率将显著提升。 什么时候需要/不需要创建索引
万事有利必有弊 什么时候适用索引
字段有唯一性限制的比如商品编码
经常用于 where查询条件的字段这样能够提高整个表的查询速度如果查询条件不是一个字段可以建立联合索引。
经常用于group by 和 order by的字段这样能够提高整个表的查询速度如果查询条件不是一个字段可以建立联合索引。
什么时候不需要创建索引
where 条件group by,order by里用不到的字段索引的价值是快速定位起不到定位的字段通常不需要创建索引因为索引会占用空间
重复数据过多的字段不需要创建索引比如在一个表中的性别字段只有男和女全是重复的男和女建立索引干嘛你一艘女只要性别是女的都全出来了这样只会增加数据库的开销
表数据太少的时候不需要创建索引
经常更新的字段不用创建索引因为经常更新他很不稳定的你建立了索引你也搜不到他由于要维护Btree的有序性那么就需要频繁的重建索引不添加索引反而能保持数据库的高效性 有什么优化索引的方法
前缀索引优化
意思就是使用某个字段中 字符串的前几个字符建立索引完整索引需要存储字段的每一个只这在长字段比如邮件地址URL等情况下会占用大量空间。通过前缀索引只存储字段的前一部分比如前10哥字符这样会减少索引的占用空间。 例如一个 VARCHAR(255) 的字段完整索引可能占用 255 字节而如果你只索引前 10 个字符则只占用 10 字节。这种方式可以减少索引文件的大小使数据库管理更为高效。 前缀索引的适用场景
1 长字符串字段比如URL电子邮件地址等
2 非精确匹配 比如查找某个字符串开头的匹配项 前缀索引的局限性
1 order by无法使用前缀索引
什么意思呢就是在sql查询中如果使用了这个order by子句对某个字段进行排序而这个字段 他的索引是前缀索引数据库是无法直接利用这个索引完成排序操作的
比如有一个电子邮件字段email并为其10个字符创建了前缀索引。查询的时候你想对email排序
select * from users order by email;
这里索引不包含完整的email字段值所以就无法使用前缀索引 2 无法把前缀索引用作覆盖索引
覆盖索引就是当查询所需的所有数据都可以从索引中获取那数据库就不用再去读取表中的数据了啊。。。都已经可以在索引中获取数据了查毛的表啊多次一举。 但是前缀索引只包含字段的前一部分前缀如果查询需要访问字段的完整值或其他字段那数据库就不能仅仅依靠前缀索引来完成查询了。必须要回到表里去查询来获取完整的数据 再来一次回表到底是啥意思
查询的完整数据不在索引里所以必须“回”到数据表中找
比如你有一个包含用户信息的表users:有idname和email字段。然后执行
SELECT id, name FROM users WHERE email exampleexample.com;
1 索引查找
数据库首先会利用 email 字段的索引快速找到 email exampleexample.com 的那条记录。
索引中通常只包含被索引字段的值这里是 email和一部分其他信息如行指针或主键。 2 回表
但是查询还需要返回 id 和 name 字段而这些字段的数据不在 email 索引里。
因此数据库需要根据索引提供的指针或主键回到 users 表中找到这一行完整的数据获取 id 和 name 的值。 而二叉树的每个父节点的儿子节点个数只能是2个意味着其搜索复杂度为o(1ogN)这已经比 BTree 高出不少因此二又树检索到目标数据所经历的磁盘 /0 次数要更多 覆盖索引优化
覆盖索引就是一种通过索引就可以获取查询所需要的所有字段数据的方法从而避免回表操作提高查询效率。 假设你有一个商品表 products表中有以下字段
product_id商品ID
name商品名称
price价格
description描述
其他字段
如果执行以下查询
select name,price from products where product_id 123;
为了避免回表用索引覆盖就是以这三个字段创建一个联合索引
create index idx_product_info on products (product_id,name,price); 查询过程
当你运行 SELECT name, price FROM products WHERE product_id 123; 时数据库可以直接在索引 idx_product_info 中找到 product_id、name 和 price因为这些字段已经被包含在了索引中。
因为查询需要的所有字段name 和 price都在索引里数据库无需再去数据表中查找也就是避免了回表。
减少I/O操作
回表意味着数据库需要再做一次磁盘读取操作来获取完整的数据行而覆盖索引避免了这种情况。索引可显著减少I/O操作加快查询速度
主键索引最好是自增的
比如第一条数据的主键是1第二条是2依次类推。 innooDB聚簇索引的结构
主键 默认是 聚簇索引二级索引这意味着数据行的物理存储顺序与主键索引的顺序一致数据被存放在 B 树的叶子节点上 为什么这么做呢有什么好处
1 数据写入效率高
每插入一条新记录都是追加操作不需要重新移动数据就是追加到后面的操作而不是随便找个地方插进去这样写入就大大减少了磁盘I/O操作 2 避免页分裂
如果使用非自增主键因为每次插入主键的索引值都是随机的每次 插入新数据的时候他就随机乱插然后不得不移动其他数据甚至需要从一个页面复制数据到另外一个页面这就是页分裂。
页分裂还有可能造成大量的内存碎片导致索引结构不紧凑从而影响效率。 使用非自增主键如随机生成的字符串或 UUID会导致以下问题
插入效率低因为新数据需要插入到索引的中间位置这会导致大量的数据移动。
频繁的页分裂随机插入的数据容易导致页面被分裂进一步增加了磁盘 I/O 和内存碎片。
索引不再紧凑频繁的插入和页分裂会使索引结构散乱查询效率下降。
唉也就是上面一段所说的那个意思 主键字段的长度不要太大哦因为 主键字段的长度越小意味着二级索引的叶子结点越小占用的空间越小 索引最好设置为not null
为什么呢
1 索引列存在null null使索引选择和优化更复杂 2 null占用额外的存储空间因为他没啥意义没意义的东西能扔则扔 防止索引失效
这里说一下索引失效的情况 索引存储结构长什么样
MySQL 默认的存储引擎是 InnoDBBtree 作为索引 的 结构 这里涉及到为什么MySQL喜欢B树 怎样的索引的数据结构是好的
要设计一个适合 MySQL 索引的数据结构至少满足以下要求
能在尽可能少的磁盘的 I/O 操作中完成查询工作
要能高效地查询某一个记录也要能高效地执行范围查找 什么是二分查找
其他复杂的解释不要看了ok大概就是 每次都把查询的范围减半然后看你 要的数据在哪一边然后就省去了查询另一边的时间。时间复杂度就降了啊干什么事情不就是一个原则图省事 二分查找树
二叉查找树他的特点就是一个节点的左子树的所有节点 都小于这个节点 右子树的所有节点都大于这个节点 看图 这他喵原理就跟二分查找一样 有一个极端情况
要是你每一次都是插入二叉树中最大的元素那你是不是要把它放到右子树一个连着一个的那是不是就成了链条去了 由于树是存储在磁盘中的访问每个节点都对应一次磁盘 I/O 操作假设一个节点的大小「小于」操作系统的最小读写单位块的大小也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数所以树的高度越高就会影响查询性能。 这段话就解释了为什么选Btree作为索引而不是 二叉树三个比较之一 二叉查找树由于存在退化成链表的可能性会使得查询操作的时间复杂度从 O(logn)降低为 O(n)。
而且会随着插入的元素越多树的高度也变高意味着需要磁盘 IO 操作的次数就越多这样导致查询性能严重下降再加上不能范围查询所以不适合作为数据库的索引结构。 什么是自平衡二叉树
接上面二叉树会变成链表的问题现在则有平衡二叉树主要是在二叉查找树的基础上增加了一些条件约束每个节点的左子树和右子树的高度差不能超过 1。 不管平衡二叉查找树还是红黑树都会随着插入的元素增多而导致树的高度变高这就意味着磁盘 I/O 操作次数多会影响整体数据查询的效率 树的节点 越多的时候并且树的分叉树M越大的时候M叉树的高度会远小于二叉树的高度。
就你看这张图就行 B树又他喵的是什么
先分清楚
叶子节点在树中没有任何子节点的节点在最底层非叶子节点是在树中至少有一个子节点的节点 和B树差异的点
1 叶子节点存放实际数据索引记录非叶子节点只存放索引没有记录 2 非叶子节点中有多少个子节点就有多少个索引每一个非叶子节点都有一个索引 3 索引都会在叶子节点出现叶子节点之间构成一个有序链表 4 非叶子节点的索引也会同时存在在子节点中并且是在子节点中所有索引的最大或最小 对数据结构分类中的问题剖析
为什么 MySQL InnoDB 选择 Btree 作为索引的数据结构
BTree 相比于 B 树、二叉树或 Hash 索引结构的优势在哪儿 BTree vs B Tree
B树只在叶子结点存储数据B树非叶子结点也要存储数据所以 B树的单个结点的数据量更小在相同磁盘I/O次数下就能查询更多的结点 Btree他的叶子结点采用的是双向链表连接双向链表就很容易的进行范围的顺序查找 BTree vs 二叉树
二叉树他只有两叉而两叉的话他的层次就会很高而每一次去读取一个结点就要进行一次磁盘I/O操作磁盘的I/O操作呢比较耗时。而Btree呢他是一棵多叉树他的高度依然保持在3-4层左右也就是说一次数据查询操作只需要做3-4次的磁盘I/O操作就能查询到目标数据。此外二叉树在极端情况下可能退化为链表从而导致性能下降。 在B树中所有实际数据都存储在叶子节点中非叶子节点只存储索引信息。因此非叶子节点占用的空间较小可以存储更多的索引信息这样在内存中可以加载更多的索引减少磁盘I/O操作提高查找效率。
由于非叶子节点只存储索引而不存储实际数据因此它们占用的空间较少允许在内存中存储更多的索引。这样可以减少磁盘I/O操作提高查询速度同时更高效地利用内存空间。 BTree vs Hash
Hash在做等值查询的时候效率贼快但他不适合做范围查询他更适合做等值的查询。 说一说InnoDB的底层索引原理 按物理存储分类
主键索引 二级索引 主键索引 的Btree的叶子结点 存放的是实际数据
二级索引 的Btree的叶子结点 存放的是主键值
