怎么在网站上做游戏代练,wordpress 主题窜改,鄂州网站建设企业推广,做农业的公司管理网站目录 一、索引
1.MySQL是如何让实现的索引机制#xff1f;
2.InnoDB索引与MyISAM索引实现的区别是什么#xff1f;
3.一个表中如果没有创建索引#xff0c;那么还会创建B树吗#xff1f;
4.说一下B树索引实现原理#xff08;数据结构#xff09;
5.聚簇索引与非聚簇…目录 一、索引
1.MySQL是如何让实现的索引机制
2.InnoDB索引与MyISAM索引实现的区别是什么
3.一个表中如果没有创建索引那么还会创建B树吗
4.说一下B树索引实现原理数据结构
5.聚簇索引与非聚簇索引b树实现有什么区别
6.说一下B树中聚簇索引的查找匹配逻辑
7.说一下B树中非聚簇索引的查找匹配逻辑
8.平衡二叉树红黑树B树和B树的区别是什么都有哪些应用场景
9.一个b树中大概能存放多少条索引记录
10.使用B树存储的索引crud执行效率如何
11.什么是自适应哈希索引
12.什么是2-3树 2-3-4树
13.为什么官方建议使用自增长主键作为索引说一下自增主键和字符串类型主键的区别和影响
15.索引的优缺点是什么
16.使用索引一定能提升效率吗
17.如果是大段文本内容如何创建优化索引
18.什么是聚簇索引
19.一个表中可以有多个非聚簇索引吗
20.聚簇索引与非聚集索引的特点是什么
21.CRUD时聚簇索引与非聚簇索引的区别是什么
22.非聚簇索引为什么不存数据地址值而存储主键
23.什么是回表操作
24.什么是覆盖索引
25.非聚集索引一定回表查询吗?
26.为什么要回表查询直接存储数据不可以吗
27.如果把一个 InnoDB 表的主键删掉是不是就没有主键就没办法进行回表查询了
28.什么是联合索引组合索引/复合索引
29.复合索引创建时字段顺序不一样使用效果一样吗
30.什么是唯一索引
31.唯一索引是否影响性能
32.什么时候使用唯一索引
33.什么时候适合创建索引什么时候不适合创建索引
34.什么是索引下推
35.有哪些情况会导致索引失效
36.为什么LIKE以%开头索引会失效
37.一个表有多个索引的时候能否手动选择使用哪个索引
38.如何查看一个表的索引
39.能否查看到索引选择的逻辑是否使用过optimizer_trace
40.多个索引优先级是如何匹配的
41.使用Order By时能否通过索引排序
42.通过索引排序内部流程是什么
43.什么是双路排序和单路排序
44.group by 分组和order by在索引使用上有什么区别
45.如果表中有字段为null又被经常查询该不该给这个字段创建索引
46.有字段为null索引是否会失效 一、索引
1.MySQL是如何让实现的索引机制
MySQL中索引分为三类B树索引、Hash索引、全文索引
MySQL通过B树索引实现了索引机制。B树是一种平衡的多路搜索树它能够快速地找到目标数据。在MySQL中B树索引是一种常见的索引类型它能够加快数据的查找速度提高数据库的性能。
MySQL中的B树索引是一种层级结构的索引它将数据按照键值排序存储在索引树中。每个节点包含多个键值和指向子节点的指针。根节点存储在内存中而叶子节点存储在磁盘上。当查询数据时MySQL会首先搜索根节点然后根据指针逐级向下搜索直到找到目标数据。
B树索引的优点是能够快速地查找数据因为它的高度通常很低每次查找只需要几次磁盘I/O操作。此外B树索引还支持范围查询、排序和唯一性约束等功能。MySQL通过B树索引实现了高效的索引机制能够提高数据库的性能和查询速度。
2.InnoDB索引与MyISAM索引实现的区别是什么 MyISAM的索引方式都是非聚簇的与InnoDB包含1个聚簇索引是不同的。 在InnoDB存储引擎中我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录而在MyISAM中却需要进行一次回表操作意味着MyISAM中建立的索引相当于全部都是二级索引 。 InnoDB的数据文件本身就是索引文件而MyISAM索引文件和数据文件是分离的 索引文件仅保存数据记录的地址。 MyISAM的表在磁盘上存储在以下文件中 *.sdi描述表结构、*.MYD数据*.MYI索引 InnoDB的表在磁盘上存储在以下文件中 .ibd表结构、索引和数据都存在一起 InnoDB的非聚簇索引data域存储相应记录主键的值 而MyISAM索引记录的是地址 。换句话说InnoDB的所有非聚簇索引都引用主键作为data域。 MyISAM的回表操作是十分快速的因为是拿着地址偏移量直接到文件中取数据的反观InnoDB是通过获取主键之后再去聚簇索引里找记录虽然说也不慢但还是比不上直接用地址去访问。 InnoDB要求表必须有主键 MyISAM可以没有 。如果没有显式指定则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列则MySQL自动为InnoDB表生成一个隐含字段作为主键这个字段长度为6个字节类型为长整型。 InnoDB的辅助索引data域存储相应记录主键的值而不是地址。 InnoDB的数据文件本身就是主索引文件。 MyISAM的索引和数据是分开存储的。
3.一个表中如果没有创建索引那么还会创建B树吗
会
如果我们在创建表时没有创建索引那么系统会自动为我们生成一个B树。
如果在创建表的时候指定了主键比如ID那么B树就会以该主键去创建它的叶子节点索引。
如果没有指定主键那么系统会默认为我们生成一个B树使用隐式的row_ID作为主键。这个row ID是系统自动生成的只供MySQL的内部使用。
4.说一下B树索引实现原理数据结构
B树索引实现原理主要基于B树这种数据结构它是为磁盘或其他直接访问辅助设备而设计的一种平衡的多路查找树。在数据库和文件系统中B树被用作索引结构以加快数据的查找速度。
以下是B树索引实现原理的详细说明 数据结构特点 多路平衡B树允许每个节点有多个子节点且所有叶子节点都位于同一层这有助于保持树的平衡从而确保查询性能的稳定。节点结构非叶子节点内部节点存储键值和指向子节点的指针但不存储实际的数据记录。叶子节点存储键值、数据记录的指针以及指向下一个叶子节点的指针。这种设计使得范围查询更加高效。磁盘友好由于B树的节点大小与磁盘块大小相近因此可以充分利用磁盘的I/O操作减少磁盘访问次数。 索引构建 当向B树中插入新数据时会按照键值的大小在树中找到合适的位置进行插入。如果需要会进行节点的分裂以保持树的平衡。删除数据时同样需要维护树的平衡可能涉及节点的合并操作。 查询操作 从根节点开始根据键值在内部节点中进行查找沿着指针指向的子节点继续查找直到到达叶子节点。在叶子节点中找到与查询键值匹配的记录或者确定该键值不存在于树中。由于叶子节点之间通过指针相连范围查询可以通过遍历叶子节点来实现非常高效。 更新操作 当数据记录发生更新时B树索引需要同步更新。如果更新涉及键值的变化可能需要在树中进行相应的插入或删除操作以保持索引的一致性。 优化 为了进一步提高性能B树索引还可以采用一些优化技术如预取技术、缓存机制等以减少磁盘I/O次数提高查询速度。
案例演示
假设有一个表index_demo表中有2个INT类型的列1个CHAR(1)类型的列c1列为主键 CREATE TABLE index_demo(c1 INT,c2 INT,c3 CHAR(1),PRIMARY KEY(c1)) ;
index_demo表的简化的行格式示意图如下 我们只在示意图里展示记录的这几个部分 record_type表示记录的类型 0是普通记录、 2是最小记录、 3 是最大记录、1是B树非叶子节点记录。 next_record表示下一条记录的相对位置我们用箭头来表明下一条记录。 各个列的值这里只记录在 index_demo 表中的三个列分别是 c1 、 c2 和 c3 。 其他信息除了上述3种信息以外的所有信息包括其他隐藏列的值以及记录的额外信息。
将其他信息项暂时去掉并把它竖起来的效果就是这样 把一些记录放到页里的示意图就是这里一页就是一个磁盘块代表一次IO MySQL InnoDB的默认的页大小是16KB因此数据存储在磁盘中可能会占用多个数据页。如果各个页中的记录没有规律我们就不得不依次遍历所有的数据页。如果我们想快速的定位到需要查找的记录在哪些数据页中我们可以这样做 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值 给所有的页建立目录项
以页28为例它对应目录项2 这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值 5。我们只需要把几个目录项在物理存储器上连续存储比如数组就可以实现根据主键值快速查找某条记录的功能了。比如查找主键值为 20 的记录具体查找过程分两步 先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中因为 12 ≤ 20 209 对应页9。 再到页9中根据二分法快速定位到主键值为 20 的用户记录。
至此针对数据页做的简易目录就搞定了。这个目录有一个别名称为索引 。
InnoDB中的索引方案
我们新分配一个编号为30的页来专门存储目录项记录页10、28、9、20专门存储用户记录 目录项记录和普通的用户记录的不同点 目录项记录 的 record_type 值是1而 普通用户记录 的 record_type 值是0。 目录项记录只有主键值和页的编号两个列而普通的用户记录的列是用户自己定义的包含很多列另外还有InnoDB自己添加的隐藏列。
现在查找主键值为 20 的记录具体查找过程分两步 先到页30中通过二分法快速定位到对应目录项因为 12 ≤ 20 209 就是页9。 再到页9中根据二分法快速定位到主键值为 20 的用户记录。 更复杂的情况如下
我们生成了一个存储更高级目录项的 页33 这个页中的两条记录分别代表页30和页32如果用户记录的主键值在 [1, 320) 之间则到页30中查找更详细的目录项记录如果主键值 不小于320 的话就到页32中查找更详细的目录项记录。这个数据结构它的名称是 B树 。 5.聚簇索引与非聚簇索引b树实现有什么区别
聚簇索引
特点 索引和数据保存在同一个B树中 页内的记录是按照主键的大小顺序排成一个单向链表 。 页和页之间也是根据页中记录的主键的大小顺序排成一个双向链表 。 非叶子节点存储的是记录的主键页号。 叶子节点存储的是完整的用户记录。
优点 数据访问更快 因为索引和数据保存在同一个B树中因此从聚簇索引中获取数据比非聚簇索引更快。 聚簇索引对于主键的排序查找和范围查找速度非常快。 按照聚簇索引排列顺序查询显示一定范围数据的时候由于数据都是紧密相连数据库可以从更少的数据块中提取数据节省了大量的IO操作 。
缺点 插入速度严重依赖于插入顺序 按照主键的顺序插入是最快的方式否则将会出现页分裂严重影响性能。因此对于InnoDB表我们一般都会定义一个自增的ID列为主键。 更新主键的代价很高 因为将会导致被更新的行移动。因此对于InnoDB表我们一般定义主键为不可更新。
限制 只有InnoDB引擎支持聚簇索引MyISAM不支持聚簇索引。 由于数据的物理存储排序方式只能有一种所以每个MySQL的表只能有一个聚簇索引。 如果没有为表定义主键InnoDB会选择非空的唯一索引列代替。如果没有这样的列InnoDB会隐式的定义一个主键作为聚簇索引。 为了充分利用聚簇索引的聚簇特性InnoDB中表的主键应选择有序的id不建议使用无序的id比如UUID、MD5、HASH、字符串作为主键无法保证数据的顺序增长。
非聚簇索引
二级索引、辅助索引
聚簇索引只能在搜索条件是主键值时才发挥作用因为B树中的数据都是按照主键进行排序的如果我们想以别的列作为搜索条件那么需要创建非聚簇索引。
例如以c2列作为搜索条件那么需要使用c2列创建一棵B树如下所示
这个B树与聚簇索引有几处不同 页内的记录是按照从c2列的大小顺序排成一个单向链表 。 页和页之间也是根据页中记录的c2列的大小顺序排成一个双向链表 。 非叶子节点存储的是记录的c2列页号。 叶子节点存储的并不是完整的用户记录而只是c2列主键这两个列的值。
一张表可以有多个非聚簇索引 6.说一下B树中聚簇索引的查找匹配逻辑
在B树中聚簇索引的查找逻辑如下
从根节点开始查找与B树中的其他查找操作一样聚簇索引的查找也是从B树的根节点开始的。根据主键定位到叶子节点在B树中非叶子节点存储了键值和指向子节点的指针。查找过程中会根据主键的值在非叶子节点中进行比较并沿着指针指向的子节点继续查找直到到达叶子节点。在叶子节点中查找数据记录叶子节点存储了主键、数据记录的指针以及指向下一个叶子节点的指针。当查找到达叶子节点时会根据主键的值在叶子节点中进行匹配。如果找到了匹配的主键那么就可以通过该叶子节点中存储的数据记录指针定位到实际的数据记录。范围查询优化由于聚簇索引将数据记录与主键物理地存储在一起因此在进行范围查询时可以通过遍历叶子节点中的指针来高效地获取满足条件的数据记录。这种顺序访问的特性使得聚簇索引在范围查询方面具有优势。
需要注意的是聚簇索引的查找性能与B树的平衡状态有关。如果B树的高度过高或者节点分裂过多可能会导致查找性能下降。因此在实际应用中需要根据数据的分布和查询需求来合理地构建和维护聚簇索引。 7.说一下B树中非聚簇索引的查找匹配逻辑
B树中非聚簇索引的查找匹配逻辑主要涉及到两个步骤首先在B树中定位到相应的叶子节点然后再根据叶子节点中存储的信息去访问实际的数据记录。以下是详细的查找逻辑 定位叶子节点 从B树的根节点开始根据非聚簇索引的键值通常是某个列的值或者多个列的组合在B树中进行查找。在查找过程中会沿着B树的内部节点非叶子节点向下遍历根据键值的大小比较来确定进入哪个子树。最终会定位到包含目标键值的叶子节点。这个过程类似于二分查找每次比较都会缩小查找范围直到找到匹配的叶子节点或者确定不存在匹配的键值。 访问数据记录 当定位到叶子节点后需要根据叶子节点中存储的信息去访问实际的数据记录。与聚簇索引不同非聚簇索引的叶子节点并不直接存储数据记录本身而是存储了指向数据记录的指针或者主键值。如果是指针则可以直接根据指针去访问存储数据记录的物理位置如果是主键值则需要根据主键值去聚簇索引中查找对应的数据记录。在访问数据记录时可能还需要进行额外的磁盘I/O操作因为数据记录可能并不与叶子节点连续存储在一起。
需要注意的是非聚簇索引的查找性能受到多个因素的影响包括B树的高度、节点的分裂情况、磁盘I/O操作的效率等。此外由于非聚簇索引需要额外的存储空间来存储索引信息和指针或主键值因此在使用非聚簇索引时需要权衡其带来的查询性能提升和存储空间的开销。
8.平衡二叉树红黑树B树和B树的区别是什么都有哪些应用场景
平衡二叉树 基础数据结构 左右平衡 高度差大于1会自旋 每个节点记录一个数据
平衡二叉树AVL
AVL树全称G.M. Adelson-Velsky和E.M. Landis这是两个人的人名。
平衡二叉树也叫平衡二叉搜索树Self-balancing binary search tree又被称为AVL树 可以保证查询效率较高。
具有以下特点 它是一棵空树或它的左右两个子树的高度差的绝对值不超过1 并且左右两个子树都是一棵平衡二叉树。
AVL的生成演示AVL Tree Visualzation
AVL的问题
众所周知IO操作的效率很低在大量数据存储中查询时我们不能一下子将所有数据加载到内存中只能逐节点加载一个节点一次IO。如果我们利用二叉树作为索引结构那么磁盘的IO次数和索引树的高度是相关的。平衡二叉树由于树深度过大而造成磁盘IO读写过于频繁进而导致效率低下。
为了提高查询效率就需要 减少磁盘IO数 。为了减少磁盘IO的次数就需要尽量降低树的高度 需要把原来“瘦高”的树结构变的“矮胖”树的每层的分叉越多越好。针对同样的数据如果我们把二叉树改成 三叉树 上面的例子中我们将二叉树变成了三叉树降低了树的高度。如果能够在一个节点中存放更多的数据我们还可以进一步减少节点的数量从而进一步降低树的高度。这就是多叉树。
普通树的问题 左子树全部为空从形式上看更像一个单链表不能发挥BST的优势。 解决方案平衡二叉树(AVL)
红黑树 hashmap存储 两次旋转达到平衡 分为红黑节点
在这个棵严格的平台树上又进化为“红黑树”{是一个非严格的平衡树 左子树与右子树的高度差不能超过1}红黑树的长子树只要不超过短子树的两倍即可 当再次插入7的时候这棵树就会发生旋转
B 树和 B 树的差异 B树中非叶子节点的关键字也会同时存在在子节点中并且是在子节点中所有关键字的最大值或最小。 B树中非叶子节点仅用于索引不保存数据记录跟记录有关的信息都放在叶子节点中。而B树中 非叶子节点既保存索引也保存数据记录 。 B树中所有关键字都在叶子节点出现叶子节点构成一个有序链表而且叶子节点本身按照关键字的大小从小到大顺序链接。
9.一个b树中大概能存放多少条索引记录 真实环境中一个页存放的记录数量是非常大的默认16KB假设指针与键值忽略不计或看做10个字节数据占 1 kb 的空间 如果B树只有1层也就是只有1个用于存放用户记录的节点最多能存放 16 条记录。 如果B树有2层最多能存放 1600×1625600 条记录。 如果B树有3层最多能存放 1600×1600×1640960000 条记录。 如果存储千万级别的数据只需要三层就够了
B树的非叶子节点不存储用户记录只存储目录记录相对B树每个节点可以存储更多的记录树的高度会更矮胖IO次数也会更少。
10.使用B树存储的索引crud执行效率如何
B树作为数据库索引结构在CRUD操作中表现出较高的执行效率。然而需要注意的是索引的性能还受到其他因素的影响如数据库的设计、查询的复杂性、硬件性能等。因此在实际应用中需要根据具体情况来选择合适的索引策略和优化方法。
Create创建 当向数据库表中插入新数据时B树索引也需要相应地更新。B树通过分裂节点来保持平衡并确保新插入的数据能够被高效地索引。虽然插入操作可能会涉及到节点的分裂和索引的更新但由于B树的高度通常较低特别是当数据量很大时这些操作通常可以较快地完成。Read读取 读取操作是B树索引最擅长的操作之一。由于B树中的所有数据都存储在叶子节点中并且叶子节点之间通过指针相连因此范围查询和点查询都可以非常高效地执行。数据库系统可以通过从根节点开始沿着B树结构快速定位到包含所需数据的叶子节点。此外由于B树的高度相对较低查找过程中需要访问的节点数量较少从而减少了磁盘I/O操作的次数进一步提高了读取操作的效率。Update更新 更新操作通常涉及查找要更新的数据记录的位置然后修改该记录的值。由于B树索引可以快速定位到数据记录的位置因此更新操作通常可以较快地完成。但是如果更新涉及到主键的修改那么索引结构也需要相应地更新这可能会涉及到更多的操作。在某些情况下如果更新的数据量很大或者更新模式具有特定的规律可能还需要对B树索引进行重建或优化以维持其性能。Delete删除 删除操作同样需要利用B树索引来快速定位到要删除的数据记录。一旦找到记录就可以从B树中删除相应的键值对。如果删除操作导致节点变得过于稀疏B树可能会进行合并操作来保持树的平衡。删除操作可能会对B树的结构产生一定的影响但由于B树的高度较低这些影响通常不会太大。此外定期的索引维护和优化操作可以帮助保持B树索引的性能。
11.什么是自适应哈希索引
自适应哈希索引是Innodb引擎的一个特殊功能当它注意到某些索引值被使用的非常频繁时会在内存中基于B-Tree所有之上再创建一个哈希索引这就让B-Tree索引也具有哈希索引的一些优点比如快速哈希查找。这是一个完全自动的内部行为用户无法控制或配置。
使用命令 SHOW ENGINE INNODB STATUS \G ;
查看INSERT BUFFER AND ADAPTIVE HASH INDEX
12.什么是2-3树 2-3-4树
多叉树multiway tree允许每个节点可以有更多的数据项和更多的子节点。2-3树2-3-4树就是多叉树多叉树通过重新组织节点减少节点数量增加分叉减少树的高度能对二叉树进行优化。
2-3树 下面2-3树就是一颗多叉树 2-3树具有如下特点 2-3树的所有叶子节点都在同一层。 有两个子节点的节点叫二节点二节点要么没有子节点要么有两个子节点。 有三个子节点的节点叫三节点三节点要么没有子节点要么有三个子节点。 2-3树是由二节点和三节点构成的树。 对于三节点的子树的值大小仍然遵守 BST 二叉排序树的规则。
2-3-4树 13.为什么官方建议使用自增长主键作为索引说一下自增主键和字符串类型主键的区别和影响 自增主键能够维持底层数据顺序写入 读取可以由b树的二分查找定位 支持范围查找范围数据自带顺序
字符串无法完成以上操作
14.使用int自增主键后 最大id是10删除id 10和9再添加一条记录最后添加的id是几删除后重启mysql然后添加一条记录最后id是几
删除之后 如果重启会从最大的id开始递增 如果没重启会延续删除之前最大的id开始递增
15.索引的优缺点是什么
优点
聚簇主键索引 顺序读写 范围快速查找 范围查找自带顺序
非聚簇索引 条件查询避免全表扫描scan 范围排序分组查询返回行id排序分组后再回表查询完整数据有可能利用顺序读写 覆盖索引不需要回表操作
索引的代价
索引是个好东西可不能乱建它在空间和时间上都会有消耗 空间上的代价
每建立一个索引都要为它建立一棵B树每一棵B树的每一个节点都是一个数据页一个页默认会占用 16KB 的存储空间一棵很大的B树由许多数据页组成那就是很大的一片存储空间。 时间上的代价
每次对表中的数据进行 增、删、改 操作时都需要去修改各个B树索引。而增、删、改操作可能会对节点和记录的排序造成破坏所以存储引擎需要额外的时间进行一些记录移位、页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引每个索引对应的B树都要进行相关的维护操作会给性能拖后腿。
B 树和 B 树都可以作为索引的数据结构在 MySQL 中采用的是 B 树。
但B树和B树各有自己的应用场景不能说B树完全比B树好反之亦然。
16.使用索引一定能提升效率吗
使用索引并不一定能提升效率这取决于多种因素。索引确实能够加速数据的检索速度但同时也会带来一些额外的开销。以下是关于使用索引对效率影响的一些关键点 查询选择性索引对于具有高选择性的查询即返回结果集很小的查询非常有效。然而对于低选择性的查询返回大量数据的查询索引可能并不总是能提高性能因为数据库系统仍然需要读取和处理大量的数据行。 插入、更新和删除的开销索引会增加插入、更新和删除操作的开销因为每次数据变动时相关的索引也需要进行相应的更新。如果表中的数据变动非常频繁而查询性能并不是瓶颈那么过多的索引可能会降低整体的性能。 索引维护索引需要定期进行维护以确保其性能和准确性。这包括重建或重新组织索引以及处理索引碎片等问题。这些维护操作可能会消耗系统资源并可能影响正常的数据库操作。 覆盖索引如果一个查询只需要访问索引中的信息而不需要访问实际的数据行即索引覆盖了查询那么这种查询通常会非常快。但是如果查询需要访问的数据不在索引中那么数据库系统还需要回表查找数据这可能会降低查询性能。 硬件和存储索引的性能也受到硬件和存储系统的影响。例如如果磁盘I/O成为瓶颈那么即使使用了索引查询性能也可能无法显著提升。 查询优化器的选择数据库系统的查询优化器会根据统计信息和查询条件选择是否使用索引。有时即使存在可用的索引优化器也可能选择不使用它而是选择全表扫描或其他更高效的执行计划。
17.如果是大段文本内容如何创建优化索引 B 树和 B 树都可以作为索引的数据结构**在 MySQL 中采用的是 B 树。**
第一种方式是分表存储然后创建索引
第二是使用es为大文本创建索引
18.什么是聚簇索引
参考05
聚簇索引是一种特殊的索引结构在关系型数据库中用于重新组织磁盘上的实际数据使数据按照指定的一个或多个列的值进行排序。由于聚簇索引的索引页面指针指向数据页面使用聚簇索引查找数据通常比使用非聚簇索引更快。每张表只能建一个聚簇索引并且建聚簇索引需要至少相当于该表120%的附加空间以存放该表的副本和索引中间页。
聚簇索引的底层原理是将数据行按照聚簇索引的键值顺序进行排序并且在索引叶子节点中存储完整的数据行。这种结构使得基于聚簇索引的查询更加高效因为可以通过一次索引查找获得完整的数据行减少了磁盘的随机读取。
聚簇索引的优点包括提高查询性能、减少磁盘空间的使用以及支持范围查询。然而它也有一些缺点比如数据的插入和更新操作可能较慢因为数据行的物理位置可能需要重新排序。此外聚簇索引对于主键的排序查找和范围查找速度非常快但如果插入数据时不按照主键的顺序进行可能会出现页分裂严重影响性能。 19.一个表中可以有多个非聚簇索引吗
聚簇索引只能有一个
非聚簇索引可以有多个
20.聚簇索引与非聚集索引的特点是什么
参考05
21.CRUD时聚簇索引与非聚簇索引的区别是什么 聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多因为插入要保证主键不能重复 聚簇索引范围排序查找效率高因为是有序的 非聚簇索引访问需要两次索引查找第一次找到主键值第二次根据主键值找到行数据
22.非聚簇索引为什么不存数据地址值而存储主键
因为聚簇索引中有时会引发分页操作、重排操作数据有可能会移动
23.什么是回表操作
回表操作是指在数据库查询过程中当数据库引擎无法直接从索引中获取所需的数据需要通过索引查找到数据行的位置然后再去实际的数据表中获取完整的数据行的过程。这个过程称为回表操作。回表操作会增加额外的IO开销和查询时间因为需要额外的步骤去获取完整的数据行。通常情况下回表操作发生在查询语句中需要返回的列不在覆盖索引中的情况下。为了减少回表操作的发生可以使用覆盖索引或者调整查询语句的列选择。
例id age name sex age - index select * from user where age 20 ;
第一次 取回id第二次回表根据id拿到完整数据。 24.什么是覆盖索引
覆盖索引是指在数据库表上建立的一种特殊索引包含了查询所需的所有列而不仅仅是用于筛选和排序的列。当查询语句中的列都包含在覆盖索引中时数据库引擎可以直接从索引中获取数据而不需要再去访问实际的数据行从而提高查询性能和减少IO开销。覆盖索引通常用于查询中只需要返回索引列的情况可以有效地减少数据库的查询时间。
例id age name sex
age - index
select * from user where age 20 ;
第一次 取回id第二次回表根据id拿到完整数据
age,name - index
select age from user where age 20 and name like张% ;
覆盖索引不会回表查询查询效率也是比较高的。 25.非聚集索引一定回表查询吗?
不一定只要b树中包含的字段创建索引的字段覆盖包含想要select 的字段那么就不会回表查询了。
26.为什么要回表查询直接存储数据不可以吗
为了控制非聚簇索引的大小
27.如果把一个 InnoDB 表的主键删掉是不是就没有主键就没办法进行回表查询了
不是InnoDB会生成rowid辅助回表查询
28.什么是联合索引组合索引/复合索引
在数据库表上同时对多个列建立索引。当查询语句中涉及到组合索引或复合索引中的多个列时数据库引擎可以利用这个索引来快速定位到符合条件的数据行。
为c2和c3列建立联合索引如下所示
c2c3 - index
c3,c2 - index
where c3?
全职匹配
最左前缀 29.复合索引创建时字段顺序不一样使用效果一样吗
在创建复合索引时字段的顺序是会影响索引的效果的。具体来说复合索引中字段的顺序会影响索引的覆盖性和查询效率。 覆盖性当查询语句中的列顺序与复合索引中的列顺序完全一致时可以利用覆盖索引的优势避免回表操作从而提高查询效率。 查询效率复合索引中字段的顺序也会影响查询的效率。通常情况下将查询频率高、区分度高的列放在复合索引的前面可以更好地利用索引的优势提高查询效率。
因此虽然复合索引中字段的顺序不同可能会对索引的效果产生影响但具体的影响效果还是要结合具体的查询场景和数据特点来决定。在创建复合索引时需要根据实际情况选择合适的字段顺序以达到最佳的查询性能。
30.什么是唯一索引 随表一起创建索引 单独建创索引
31.唯一索引是否影响性能
唯一索引在某些情况下可能会对性能产生一定的影响但通常情况下这种影响是可以忽略的。唯一索引的主要作用是确保列或列组的数值在表中是唯一的这可以防止出现重复数据。在插入、更新和删除数据时数据库引擎需要检查唯一索引以确保数据的唯一性。
因为唯一索引需要额外的检查和维护操作所以在数据的写入操作如插入、更新、删除时可能会稍微影响性能特别是在大量数据的情况下。然而在大多数情况下这种影响是可以忽略的而且唯一索引可以提高数据的完整性和查询效率。
总的来说唯一索引对性能的影响是较小的而其带来的数据完整性和查询效率的提升通常是值得的。在设计数据库时根据具体的需求和场景合理使用唯一索引可以提高系统的性能和数据质量。
32.什么时候使用唯一索引
业务需求唯一字段的时候一般不考虑性能问题
【强制】业务上具有唯一特性的字段即使是多个字段的组合也必须建成唯一索引。 说明不要以为唯一索引影响了 insert 速度这个速度损耗可以忽略但提高查找速度是明 显的另外即使在应用层做了非常完善的校验控制只要没有唯一索引根据墨菲定律必 然有脏数据产生。
33.什么时候适合创建索引什么时候不适合创建索引
适合创建索引 频繁作为where条件语句查询字段 关联字段需要建立索引 排序字段可以建立索引 分组字段可以建立索引(因为分组前提是排序) 统计字段可以建立索引如.count(),max()
不适合创建索引 频繁更新的字段不适合建立索引 where分组排序中用不到的字段不必要建立索引 可以确定表数据非常少不需要建立索引 参与mysql函数计算的列不适合建索引
创建索引时避免有如下极端误解
1宁滥勿缺。认为一个查询就需要建一个索引。
2宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。
3抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。 34.什么是索引下推
5.6之前的版本是没有索引下推这个优化的
Using index condition叫作 Index Condition Pushdown Optimization 索引下推优化 如果没有索引下推ICP那么MySQL在存储引擎层找到满足content1 z条件的第一条二级索引记录。主键值进行回表返回完整的记录给server层server层再判断其他的搜索条件是否成立。如果成立则保留该记录否则跳过该记录然后向存储引擎层要下一条记录。 如果使用了索引下推ICP那么MySQL在存储引擎层找到满足content1 z条件的第一条二级索引记录。不着急执行回表而是在这条记录上先判断一下所有关于idx_content1索引中包含的条件是否成立也就是content1 z AND content1 LIKE %a是否成立。如果这些条件不成立则直接跳过该二级索引记录去找下一条二级索引记录如果这些条件成立则执行回表操作返回完整的记录给server层。
总结
未开启索引下推 根据筛选条件在索引树中筛选第一个条件 获得结果集后回表操作 进行其他条件筛选 再次回表查询
开启索引下推在条件查询时当前索引树如果满足全部筛选条件可以在当前树中完成全部筛选过滤得到比较小的结果集再进行回表操作 35.有哪些情况会导致索引失效 计算、函数导致索引失效 -- 显示查询分析EXPLAIN SELECT * FROM emp WHERE emp.name LIKE abc%;EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) abc; --索引失效LIKE以%_ 开头索引失效 拓展Alibaba《Java开发手册》 【强制】页面搜索严禁左模糊或者全模糊如果需要请走搜索引擎来解决。 EXPLAIN SELECT * FROM emp WHERE name LIKE %ab%; --索引失效不等于(! 或者)索引失效 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name abc ;EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name abc ; --索引失效IS NOT NULL 失效 和 IS NULL EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效
注意当数据库中的数据的索引列的NULL值达到比较高的比例的时候即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引此时type的值是range范围查询 -- 将 id20000 的数据的 name 值改为 NULLUPDATE emp SET name NULL WHERE id 20000;-- 执行查询分析可以发现 IS NOT NULL 使用了索引-- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效由查询优化器的算法决定EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL 类型转换导致索引失效 EXPLAIN SELECT * FROM emp WHERE name123; EXPLAIN SELECT * FROM emp WHERE name 123; --索引失效 复合索引未用左列字段失效 如果mysql觉得全表扫描更快时数据少;
36.为什么LIKE以%开头索引会失效
当使用LIKE操作符进行模糊查询时如果通配符%出现在搜索字符串的开头即%xxx那么数据库索引通常会失效。这是因为通配符%在搜索字符串的开头会导致数据库无法有效地利用索引进行搜索。
数据库索引通常是按照排序顺序存储的如果搜索字符串的开头包含通配符%数据库引擎无法确定索引的排序顺序无法有效地使用索引进行搜索。在这种情况下数据库引擎通常会放弃使用索引而是进行全表扫描来找到匹配的数据这会导致查询效率大大降低。
为了避免索引失效可以考虑使用其他方法来优化模糊查询比如使用全文搜索引擎或者将数据预处理为适合索引的形式。另外可以考虑使用反向索引或者倒序存储数据来避免在搜索字符串开头使用通配符%时索引失效的问题。例
id,name,age
name 创建索引
select * from user where name like %明
typeall
select name,id from user where name like %明
typeindex
张明
(name,age)
其实并不会完全失效覆盖索引下会出现typeindex表示遍历了索引树再回表查询
覆盖索引没有生效的时会直接typeall
没有高效使用索引是因为字符串索引会逐个转换成accii码生成b树时按首个字符串顺序排序类似复合索引未用左列字段失效一样跳过开始部分也就无法使用生成的b树了
37.一个表有多个索引的时候能否手动选择使用哪个索引
不可用手动直接干预只能通过mysql优化器自动选择
38.如何查看一个表的索引 39.能否查看到索引选择的逻辑是否使用过optimizer_trace 40.多个索引优先级是如何匹配的 主键唯一索引匹配 全值匹配单值匹配 最左前缀匹配 范围匹配 索引扫描 全表扫描
一般性建议
Ø 对于单键索引尽量选择过滤性更好的索引例如手机号邮件身份证
Ø 在选择组合索引的时候过滤性最好的字段在索引字段顺序中位置越靠前越好。
Ø 选择组合索引时尽量包含where中更多字段的索引
Ø 组合索引出现范围查询时尽量把这个字段放在索引次序的最后面
Ø 尽量避免造成索引失效的情况
41.使用Order By时能否通过索引排序
在使用ORDER BY子句进行排序时数据库引擎通常可以通过索引来加快排序的过程但并不是所有情况下都能通过索引排序。具体取决于以下几个因素 是否使用了覆盖索引如果查询中包含在索引中的列并且这些列是ORDER BY子句中指定的列那么数据库引擎可以使用覆盖索引来避免对实际数据行的访问从而通过索引完成排序操作。 索引的排序顺序如果索引的排序顺序与ORDER BY子句一致那么数据库引擎可以直接使用索引进行排序。如果排序顺序不一致数据库引擎可能需要额外的排序操作导致无法通过索引排序。 索引的选择性索引的选择性指索引中不同值的数量与总行数的比率。如果索引的选择性较低即索引中的值重复性较高数据库引擎可能会选择不使用索引进行排序。
42.通过索引排序内部流程是什么
当数据库引擎需要通过索引来排序数据时通常会遵循以下内部流程 查询优化器选择使用索引排序在执行查询之前查询优化器会评估是否可以使用索引来排序数据。如果查询中包含ORDER BY子句并且索引可以支持排序操作优化器会选择使用索引排序。 访问索引并获取排序结果数据库引擎会通过索引访问数据并按照索引中的排序顺序获取数据。如果使用覆盖索引可以直接通过索引获取排序结果而不需要访问实际的数据行。 排序操作如果索引无法完全满足排序需求比如索引的排序顺序与ORDER BY子句不一致数据库引擎可能会进行额外的排序操作。这时数据库引擎会将获取的数据进行排序以满足ORDER BY子句的要求。 返回排序结果最终数据库引擎会返回排序后的结果集给用户。如果排序操作完全通过索引完成可以提高查询性能如果需要额外的排序操作可能会增加一定的开销。
通过索引排序的内部流程包括选择使用索引、访问索引获取数据、排序操作和返回排序结果。通过合理设计索引和查询语句可以提高排序操作的效率和性能。 43.什么是双路排序和单路排序
单路排序一次取出所有字段进行排序内存不够用的时候会使用磁盘
双路排序取出排序字段进行排序排序完成后再次回表查询所需要的其他字段
果不在索引列上filesort有两种算法 mysql就要启动双路排序和单路排序
双路排序慢
Select id,age,name from stu order by name;
Ø MySQL 4.1之前是使用双路排序字面意思就是两次扫描磁盘最终得到数据 读取行指针和order by列对他们进行排序然后扫描已经排序好的列表按照列表中的值重新从列表中读取对应的数据输出
Ø 从磁盘取排序字段在buffer进行排序再从磁盘取其他字段。
Ø 取一批数据要对磁盘进行两次扫描众所周知I\O是很耗时的所以在mysql4.1之后出现了第二种改进的算法就是单路排序。
单路排序快
从磁盘读取查询需要的所有列按照order by列在buffer对它们进行排序然后扫描排序后的列表进行输出 它的效率更快一些避免了第二次读取数据。并且把随机IO变成了顺序IO但是它会使用更多的空间 因为它把每一行都保存在内存中了。
结论及引申出的问题
但是用单路有问题
在sort_buffer中单路比多路要多占用很多空间因为单路是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量导致每次只能取sort_buffer容量大小的数据进行排序创建tmp文件多路合并排完再取sort_buffer容量大小再排……从而多次I/O。
单路本来想省一次I/O操作反而导致了大量的I/O操作反而得不偿失。
优化策略
Ø 增大sort_buffer_size参数的设置
Ø 增大max_length_for_sort_data参数的设置
Ø 减少select 后面的查询的字段。 禁止使用select *
提高Order By的速度
1. Order by时select * 是一个大忌。只Query需要的字段 这点非常重要。在这里的影响是
l 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时会用改进后的算法——单路排序 否则用老算法——多路排序。
l 两种算法的数据都有可能超出sort_buffer的容量超出之后会创建tmp文件进行合并排序导致多次I/O但是用单路排序算法的风险会更大一些所以要提高sort_buffer_size。
2. 尝试提高 sort_buffer_size
l 不管用哪种算法提高这个参数都会提高效率当然要根据系统的能力去提高因为这个参数是针对每个进程connection的 1M-8M之间调整。 MySQL5.7和8.0InnoDB存储引擎默认值是1048576字节1MB。
SHOW VARIABLES LIKE %sort_buffer_size%;
3. 尝试提高 max_length_for_sort_data
l 提高这个参数 会增加用改进算法的概率。
SHOW VARIABLES LIKE %max_length_for_sort_data%;
#5.7默认1024字节
#8.0默认4096字节
l 但是如果设的太高数据总容量超出sort_buffer_size的概率就增大明显症状是高的磁盘I/O活动和低的处理器使用率。如果需要返回的列的总长度大于max_length_for_sort_data使用双路算法否则使用单路算法。1024-8192字节之间调整
44.group by 分组和order by在索引使用上有什么区别
group by 使用索引的原则几乎跟order by一致 唯一区别 group by 先排序再分组遵照索引建的最佳左前缀法则 group by没有过滤条件也可以用上索引。Order By 必须有过滤条件才能使用上索引。
45.如果表中有字段为null又被经常查询该不该给这个字段创建索引
应该创建索引使用的时候尽量使用is null判断。 IS NOT NULL 失效 和 IS NULL EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效
注意当数据库中的数据的索引列的NULL值达到比较高的比例的时候即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引此时type的值是range范围查询 -- 将 id20000 的数据的 name 值改为 NULLUPDATE emp SET name NULL WHERE id 20000;-- 执行查询分析可以发现 IS NOT NULL 使用了索引-- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效由查询优化器的算法决定EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL
46.有字段为null索引是否会失效
不一定会失效每一条sql具体有没有使用索引 可以通过trace追踪一下
最好还是给上默认值
数字类型的给0字符串给个空串“”
不一定会失效每一条sql具体有没有使用索引 可以通过trace追踪一下
最好还是给上默认值
数字类型的给0字符串给个空串“”
参考上一题
