淄博高端网站,东莞专业的网络推广,如何在学校网站上做链接,最近国际新闻热点索引分类
索引和数据就是位于存储引擎中#xff1a;
按「数据结构」分类#xff1a;Btree索引、Hash索引、Full-text索引。按「物理存储」分类#xff1a;聚簇索引#xff08;主键索引#xff09;、二级索引#xff08;辅助索引#xff09;。按「字段特性」分类#…索引分类
索引和数据就是位于存储引擎中
按「数据结构」分类Btree索引、Hash索引、Full-text索引。按「物理存储」分类聚簇索引主键索引、二级索引辅助索引。按「字段特性」分类主键索引、唯一索引、普通索引、前缀索引。按「字段个数」分类单列索引、联合索引。
为什么 MySQL InnoDB 选择 Btree 作为索引的数据结构
1、BTree vs B Tree BTree 只在叶子节点存储数据而 B 树 的非叶子节点也要存储数据所以 BTree 的单个节点的数据量更小在相同的磁盘 I/O 次数下就能查询更多的节点。 另外BTree 叶子节点采用的是双链表连接适合 MySQL 中常见的基于范围的顺序查找而 B 树无法做到这一点。
2、BTree vs 二叉树 对于有 N 个叶子节点的 BTree其搜索复杂度为O(logdN)其中 d 表示节点允许的最大子节点个数为 d 个。 在实际的应用当中 d 值是大于100的这样就保证了即使数据达到千万级别时BTree 的高度依然维持在 3~4 层左右也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。 而二叉树的每个父节点的儿子节点个数只能是 2 个意味着其搜索复杂度为 O(logN)这已经比 BTree 高出不少因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。
3、BTree vs Hash Hash 在做等值查询的时候效率贼快搜索复杂度为 O(1)。 Hash需要一次性将数据加载到内存中如果数据比较大则加载时间长而Btree是分节点加载数据 但是 Hash 表不适合做范围查询它更适合做等值的查询这也是 BTree 索引要比 Hash 表索引有着更广泛的适用场景的原因。
聚簇索引主键索引、二级索引辅助索引
主键索引的 BTree 的叶子节点存放的是实际数据所有完整的用户记录都存放在主键索引的 BTree 的叶子节点里 二级索引的 BTree 的叶子节点存放的是主键值而不是实际数据。覆盖索引 在查询时使用了二级索引如果查询的数据能在二级索引里查询的到(也就是查询的数据是主键值)不需要读取索引中的数据只需要查一个 B 树就能找到数据那么就不需要回表这个过程就是覆盖索引。 回表 如果某个查询语句使用了二级索引但是查询的数据不是主键值这时找到对应的叶子节点获取到主键值后需要去聚簇索引中获得数据行就能查询到数据了这个过程就是回表。
InnoDB 在创建聚簇索引时会根据不同的场景选择不同的列作为索引
如果有主键默认会使用主键作为聚簇索引的索引键如果没有主键就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键在上面两个都没有的情况下InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键
一张表只能有一个聚簇索引那为了实现非主键字段的快速搜索就引出了二级索引
字段特性
主键索引
建立在主键字段上的索引通常在创建表的时候一起创建一张表最多只有一个主键索引索引列的值不允许有空值。
唯一索引
建立在 UNIQUE 字段上的索引一张表可以有多个唯一索引索引列的值必须唯一但是允许有空值。
普通索引
就是建立在普通字段上的索引既不要求字段为主键也不要求字段为 UNIQUE。
前缀索引
是指对字符类型字段的前几个字符建立的索引而不是在整个字段上建立的索引前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。使用前缀索引的目的是为了减少索引占用的存储空间提升查询效率。
单列索引和联合索引
建立在单列上的索引称为单列索引比如主键索引建立在多列上的索引称为联合索引
联合索引范围
联合索引查询不代表联合索引中的所有字段都用到了联合索引进行索引查询可能存在部分字段用到联合索引的 BTree部分字段没有用到联合索引的 BTree 的情况。联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引但是在范围查询字段的后面的字段无法用到联合索引。注意对于 、、BETWEEN、like 前缀匹配的范围查询并不会停止匹配
select * from t_table where a 1 and b 2单列索引与联合索引区别
组成方式单列索引只包含一列而联合索引则由多列组成。使用范围单列索引适用于单列查询联合索引适用于多列查询索引大小联合索引的大小通常比单列索引大更新操作联合索引如果更新操作涉及到了索引的任何一列都会导致索引重建单列索引只有更新涉及到了该列才会导致索引重建单列索引适用于单列查询适用于频繁更新的情况而联合索引适用于多列查询适用于读操作多、写操作少的情况
联合索引相比单列索引有什么优点
联合索引在进行查询过程中可能索引列就是我们要查询的数据使用覆盖索引避免了回表操作减少IO次数提高查询性能
最左匹配原则
在使用多列索引时如果查询中包含索引的第一个列则可以利用该索引进行搜索如果查询中不包含索引的第一个列则无法使用该索引。
索引下推原理
一般在联合索引来优化查询但是在某些情况下联合索引并不完全适用于所有的查询条件于是从 MySQL 5.6 之后使用索引下推可以在联合索引遍历过程中对联合索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数。在引擎层判断数据是否合法如果合法直接返回如果不合法继续判断减少回表操作
索引下推原理
截断的字段不会在 Server 层进行条件判断而是会被下推到「存储引擎层」进行条件判断因为 c 字段的值是在 (a, b, c) 联合索引里的然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据无需再回表读取数据来进行判断减少回表次数从而提升了性能。
Innodb的BTree、BTree、二级索引、MyISAM的BTree
Innodb的BTree非叶子节点存放索引值叶子结点存放数值(索引即文件)BTree非叶子结点与叶子结点都存放数据二级索引非叶子结点存放索引值叶子结点存放的是主键值MyISAM的BTree非叶子结点存放索引值叶子结点存放指向数据的指针。索引与文件分开
索引失效
当我们使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效当我们在查询条件中对索引列使用函数就会导致索引失效。当我们在查询条件中对索引列进行表达式计算也是无法走索引的。MySQL 在遇到字符串和数字比较的时候会自动把字符串转为数字然后再进行比较。如果字符串是索引列而条件语句中的输入参数是数字的话那么索引列会发生隐式类型转换由于隐式类型转换是通过 CAST 函数实现的等同于对索引列使用了函数所以就会导致索引失效。联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配否则就会导致索引失效。在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效。
MySQL 使用 like “%x“索引不一定会失效
当表中的字段全部都使用到了索引例如表中只有id和name俩列id为主键索引name为二级索引这张表的字段没有「非索引」字段所以select *相当于 select id,name然后这个查询的数据都在二级索引的 B 树因为二级索引的 B 树的叶子节点包含「索引值主键值」所以查二级索引的 B 树就能查到全部结果了这个就是覆盖索引。但是执行计划里的 type 是 index这代表着是通过全扫描二级索引的 B 树的方式查询到数据的也就是遍历了整颗索引树。
文章总结https://www.xiaolincoding.com/
