深圳网站建设怎样选,网店推广方案,延吉网站优化,汕头网站建设推广平台深入浅出索引
索引的常见模型
索引模型#xff1a;是哈希表、有序数组和搜索树。
区别#xff1a;
哈希表是一种以键 - 值#xff08;key-value#xff09;存储数据的结构#xff0c;我们只要输入待查找的值即 key#xff0c;就可以找到其对应的值即 Value。哈希的思…深入浅出索引
索引的常见模型
索引模型是哈希表、有序数组和搜索树。
区别
哈希表是一种以键 - 值key-value存储数据的结构我们只要输入待查找的值即 key就可以找到其对应的值即 Value。哈希的思路很简单把值放在数组里用一个哈希函数把 key 换算成一个确定的位置然后把 value 放在数组的这个位置。哈希表这种结构适用于只有等值查询的场景比如 Memcached 及其他一些 NoSQL 引擎。
有序数组在等值查询和范围查询场景中的性能就都非常优秀。
N 叉树由于在读写上的性能优点以及适配磁盘的访问模式已经被广泛应用在数据库引擎中了。
数据库底层存储的核心就是基于这些数据模型的。每碰到一个新数据库我们需要先关注它的数据模型
InnoDB 的索引模型
在 InnoDB 中表都是根据主键顺序以索引的形式存放的这种存储方式的表称为索引组织表。又因为前面我们提到的InnoDB 使用了 B 树索引模型所以数据都是存储在 B 树中的。
每一个索引在 InnoDB 里面对应一棵 B 树。
假设我们有一个主键列为 ID 的表表中有字段 k并且在 k 上有索引。
主键索引的叶子节点存的是整行数据。在 InnoDB 里主键索引也被称为聚簇索引clustered index。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里非主键索引也被称为二级索引secondary index。
根据上面的索引结构说明我们来讨论一个问题基于主键索引和普通索引的查询有什么区别
如果语句是 select * from T where ID500即主键查询方式则只需要搜索 ID 这棵 B 树如果语句是 select * from T where k5即普通索引查询方式则需要先搜索 k 索引树得到 ID 的值为 500再到 ID 索引树搜索一次。这个过程称为回表。
也就是说基于非主键索引的查询需要多扫描一棵索引树。因此我们在应用中应该尽量使用主键查询。
索引维护
B 树为了维护索引有序性在插入新值的时候需要做必要的维护。如果插入新的行 ID 值为 700则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400就相对麻烦了需要逻辑上挪动后面的数据空出位置。
而更糟的情况是如果 R5 所在的数据页已经满了根据 B 树的算法这时候需要申请一个新的数据页然后挪动部分数据过去。这个过程称为页分裂。在这种情况下性能自然会受影响。
除了性能外页分裂操作还影响数据页的利用率。原本放在一个页的数据现在分到两个页中整体空间利用率降低大约 50%。
当然有分裂就有合并。当相邻两个页由于删除了数据利用率很低之后会将数据页做合并。合并的过程可以认为是分裂过程的逆过程。
基于上面的索引维护过程说明我们来讨论一个案例 你可能在一些建表规范里面见到过类似的描述要求建表语句里一定要有自增主键。当然事无绝对我们来分析一下哪些场景下应该使用自增主键而哪些场景下不应该。 自增主键是指自增列上定义的主键在建表语句中一般是这么定义的 NOT NULL PRIMARY KEY AUTO_INCREMENT。
插入新记录的时候可以不指定 ID 的值系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。
也就是说自增主键的插入数据模式正符合了我们前面提到的递增插入的场景。每次插入一条新记录都是追加操作都不涉及到挪动其他记录也不会触发叶子节点的分裂。
而有业务逻辑的字段做主键则往往不容易保证有序插入这样写数据成本相对较高。
除了考虑性能外我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段比如字符串类型的身份证号那应该用身份证号做主键还是用自增字段做主键呢
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键那么每个二级索引的叶子节点占用约 20 个字节而如果用整型做主键则只要 4 个字节如果是长整型bigint则是 8 个字节。
显然主键长度越小普通索引的叶子节点就越小普通索引占用的空间也就越小。
所以从性能和存储空间方面考量自增主键往往是更合理的选择。
有没有什么场景适合用业务字段直接做主键的呢还是有的。比如有些业务的场景需求是这样的
只有一个索引该索引必须是唯一索引。
这就是典型的 KV 场景。
由于没有其他索引所以也就不用考虑其他索引的叶子节点大小的问题。
“尽量使用主键查询”原则直接将这个索引设置为主键可以避免每次查询需要搜索两棵树。
在下面这个表 T 中如果我执行 select * from T where k between 3 and 5需要执行几次树的搜索操作会扫描多少行
mysql create table T ( ID int primary key, k int NOT NULL DEFAULT 0, s varchar(16) NOT NULL DEFAULT , index k(k)) engineInnoDB; insert nto T values (100,1, aa),(200,2,bb),(300,3,cc),(500,5,ee),(600,6,ff),(700,7,gg);这条 SQL 查询语句的执行流程
在 k 索引树上找到 k3 的记录取得 ID 300再到 ID 索引树查到 ID300 对应的 R3在 k 索引树取下一个值 k5取得 ID500再回到 ID 索引树查到 ID500 对应的 R4在 k 索引树取下一个值 k6不满足条件循环结束。
在这个过程中回到主键索引树搜索的过程我们称为回表。可以看到这个查询过程读了 k 索引树的 3 条记录步骤 1、3 和 5回表了两次步骤 2 和 4。
覆盖索引
如果执行的语句是 select ID from T where k between 3 and 5这时只需要查 ID 的值而 ID 的值已经在 k 索引树上了因此可以直接提供查询结果不需要回表。也就是说在这个查询里面索引 k 已经“覆盖了”我们的查询需求我们称为覆盖索引。
由于覆盖索引可以减少树的搜索次数显著提升查询性能所以使用覆盖索引是一个常用的性能优化手段。
需要注意的是在引擎内部使用覆盖索引在索引 k 上其实读了三个记录R3~R5对应的索引 k 上的记录项但是对于 MySQL 的 Server 层来说它就是找引擎拿到了两条记录因此 MySQL 认为扫描行数是 2。
索引字段的维护总是有代价的。因此在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这正是业务 DBA或者称为业务数据架构师的工作。
最左前缀原则
B 树这种索引结构可以利用索引的“最左前缀”来定位记录。
为了直观地说明这个概念我们用nameage这个联合索引来分析。
可以看到索引项是按照索引定义里面出现的字段顺序排序的。
当你的逻辑需求是查到所有名字是“张三”的人时可以快速定位到 ID4然后向后遍历得到所有需要的结果。
不只是索引的全部定义只要满足最左前缀就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段也可以是字符串索引的最左 M 个字符。
在建立联合索引的时候如何安排索引内的字段顺序。
评估标准是索引的复用能力。因为可以支持最左前缀所以当已经有了 (a,b) 这个联合索引后一般就不需要单独在 a 上建立索引了。因此第一原则是如果通过调整顺序可以少维护一个索引那么这个顺序往往就是需要优先考虑采用的。
查询条件里面只有 b 的语句是无法使用 (a,b) 这个联合索引的.
这时候,要考虑的原则就是空间了。比如上面这个市民表的情况name 字段是比 age 字段大的 那我就建议你创建一个name,age) 的联合索引和一个 (age) 的单字段索引。
索引下推
不符合最左前缀的部分会怎么样呢
以市民表的联合索引name, age为例。如果现在有一个需求检索出表中“名字第一个字是张而且年龄是 10 岁的所有男孩”。那么SQL 语句是这么写的
mysql select * from tuser where name like 张 % and age10 and ismale1;这个语句在搜索索引树的时候只能用 “张”找到第一个满足条件的记录 ID3。当然这还不错总比全表扫描要好。
然后呢
当然是判断其他条件是否满足。
在 MySQL 5.6 之前只能从 ID3 开始一个个回表。到主键索引上找出数据行再对比字段值。
而 MySQL 5.6 引入的索引下推优化index condition pushdown) 可以在索引遍历过程中对索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数。
