成都商务网站建设,网站免费网站免费陪玩,python网站开发基础,wordpress关键词内链图片文章目录 前言一、mysql的索引是什么#xff1f;1.1 索引的结构#xff1a;1.2 b树特性#xff1a;1.3 b树每个节点的结构#xff1a;1.4 b树 键值的大小排序#xff1a;1.4 b树 存储#xff08;InnoDB#xff09;#xff1a; 二、索引类型2.1 主要的索引类型#xff… 文章目录 前言一、mysql的索引是什么1.1 索引的结构1.2 b树特性1.3 b树每个节点的结构1.4 b树 键值的大小排序1.4 b树 存储InnoDB 二、索引类型2.1 主要的索引类型2.2 聚集索引和非聚集索引2.3 复合索引2.3.1 复合索引说明2.3.2 复合索引最左匹配 2.4 前缀索引2.4.1 前缀索引说明和设置2.4.2 否会得到与查询条件不相同的数据2.4.3 前缀索引长度的设置 2.5 索引的回表2.5.1 为什么会产生回表2.5.2 非聚集索引叶子节点为什么不存整条数据内容 2.6 索引下推Index Condition Pushdown2.7 多个索引怎么协同工作2.8 适合做索引的列 三、索引失效的场景总结参考 前言
在使用Mysql时 当表中数据量增加需要对表中的列建立索引以增加检索速度那么Mysql 中的索引究竟是什么它为什么能增加检索速度索引都有哪些类型我们应该为哪些列创建索引索引的失效场景有哪些本文进行探究。 一、mysql的索引是什么
1.1 索引的结构
mysql 的索引本质上是一棵b 树其实一个索引就对应一棵b 树当进行查询的时候可以通过查询的字段遍历这颗b树找到需要需要的数据,从而避免扫描对比表中的所有该列数据要检索的数据少了那么io就不需要那么多次对cpu和内存的消耗就少检索效率就会提高
1.2 b树特性
所有叶子节点中包含了全部键值的信息且叶子节点本身依键值大小从小到大顺序链接。所有非叶子节点可以看成是索引部分节点中的键值是为了引导搜索方向。
1.3 b树每个节点的结构
在B树索引中一个节点主要包含以下几个部分
键值键值是用于对数据进行排序和查询的关键字存储在节点中。在非叶子节点键值用于指导搜索方向在叶子节点键值就是实际的索引值。指针指针用于指向其他节点。在非叶子节点指针指向键值对应的子节点在叶子节点指针指向数据记录或者指向下一个叶子节点。
所以对于B树索引的一个节点来说如果我们将其看做一个结构体那么其基本结构可以简化为
struct BTreeNode {int keys[m]; // 键值数组struct BTreeNode *children[m1]; // 孩子节点指针数组struct Record *records[m]; // 只在叶子节点中使用记录指针数组指向具体的数据记录struct BTreeNode *next; // 只在叶子节点中使用指向下一个叶子节点 链表结构
};这里m代表了B树的阶数最多含有的子树个数或者说是分支因子每个节点最多含有的键的个数。这个结构是理论上的B树节点的主要组成部分。在实际的数据库系统中B树的实现会更为复杂会有更多的控制信息比如节点类型根节点、内部节点或叶子节点、键值数量、父节点指针等。
1.4 b树 键值的大小排序
MySQL在比较B树索引的键值时会根据字段的数据类型使用相应的比较规则。 数值类型对于整数、实数这样的数值类型MySQL直接比较其数值大小。 字符串类型对于char、varchar、text这样的字符串类型MySQL通常进行字典排序dictionary order。这种比较方式会考虑字符的字节编码值而且通常是区分大小写的。具体的比较方式也会受到字符集和排序规则collation的影响。 日期时间类型对于date、datetime、timestamp这样的日期时间类型MySQL会转换成对应的内部格式进行比较。日期时间类型的内部格式设计成了可以直接进行数值比较的。 枚举和集合类型对于enum和set这样的类型MySQL会将其转换为数值或字符串进行比较。 二进制类型对于binary、varbinary、blob这样的二进制类型MySQL会按照二进制值进行比较。
1.4 b树 存储InnoDB
InnoDB存储引擎以Index page索引页为存储单位每个索引页的大小默认为16KB。
索引的节点包括根节点、中间节点、叶子结点都存储在索引页中。每个索引节点会有自己的节点头信息如记录类型、记录头信息、事务ID、回滚指针和真正的行记录等等。
对于B树索引非叶子节点存储的是键值子节点的指针叶子节点存储的是键值行数据。如果是主键索引聚集索引那么叶子节点直接存储整行数据。如果是非主键索引第二索引叶子节点存储的行数据实际上是主键的值。
每个索引页按照上述结构进行组织通过一个页目录来快速定位和访问记录。页与页之间通过双向链表连在一起保证了范围查找示可以按页顺序进行读取提高效率。
二、索引类型
2.1 主要的索引类型 B树索引MySQL默认的索引类型适用于全值匹配和范围查询。B树索引有聚集索引主键索引和非聚集索引二级索引之分。 哈希索引根据哈希函数对键值计算出一个哈希值通过哈希值进行查询。哈希索引只支持等值查询不支持范围查询。MySQL的内存型存储引擎MEMORY可以使用哈希索引。 空间索引用于对地理空间数据进行索引比如经纬度。MySQL的MyISAM存储引擎和InnoDB存储引擎支持空间索引。 全文索引用于对文本内容进行索引提供了对全文内容进行搜索的功能。MySQL的MyISAM存储引擎在很早就支持全文索引InnoDB存储引擎在5.6版本后开始支持全文索引。 索引组合/复合索引针对多个字段同时创建一个索引。在查询时可以用来优化对多个字段的组合查询。 唯一索引索引列的值必须唯一但允许有空值。 主键索引是一种特殊的唯一索引不允许有空值。一个表只能有一个主键。
2.2 聚集索引和非聚集索引
(1) 存储内容上的区别
聚集索引在聚集索引的叶子节点中直接包含了数据行的内容。也就是说一棵聚集索引树可以完整的获取表中的所有内容因此一个表只能有一个聚集索引。非聚集索引非聚簇索引的叶子节点并不包含数据行的全部内容而是存储了对应行数据在聚集索引中的键值一般是主键。当数据库根据非聚集索引查找数据时在非聚集索引中找到对应主键值后再在聚沼索引中查找对应的数据行这个过程通常叫做回表。
(2) 存储方式的区别
聚集索引聚集索引将数据行的存储和索引放在一起数据行的存储顺序与键值在索引树中的逻辑顺序一致所以适合查找范围数据和排序。非聚簇索引非聚簇索引与数据行存储分离优点是在进行全表扫描的时候可以跳过那些不需要的数据提高检索效率。
(3) 性能上的区别
聚集索引因为索引和数据存储在一起故而查找速度通常较快尤其对于基于主键的查找和范围查找。非聚簇索引通常需要额外的一次查询即回表因此查询性能通常不如聚集索引。但是非聚簇索引在某些场景下可能会更高效比如查询只需要使用非聚簇索引即可获取的列时。
2.3 复合索引
2.3.1 复合索引说明
复合索引也被称为联合索引或者多列索引是指基于表中两个或两个以上的列创建的索引。复合索引的顺序很重要因为MySQL只能高效地使用复合索引的最左前缀也就是说查询只能使用复合索引从左到右的一个或多个索引列。
2.3.2 复合索引最左匹配
MySQL 的复合索引是 B 树数据结构它是根据复合索引中的所有字段构建出一棵 B 树。
假设我们有一个复合索引 (A,B,C)那么 B 树的索引顺序会依次按照 A、B、C 的顺序排列。也就是说B 和 C 的排序都依赖于 A 的值类似的C 的排序依赖于 A 和 B 的值。
因此当我们进行查询时必须先通过最左边的索引字段 A 去找到正确的 B 范围再通过 B 找到 C这样才能保证找到的数据是的。如果我们直接用 B 或者 C 来查找由于 B 和 C 的排序依赖于 A 的值所以不能保证我们找到正确的范围这样数据就会出错。
这就是为什么我们在使用复合索引时只能使用索引的最左前缀。如果不遵守这个原则那复合索引可能就无法发挥它应有的效果。
2.4 前缀索引
2.4.1 前缀索引说明和设置
前缀索引一般应用于CHAR, VARCHAR, BLOB, TEXT等类型的字段特别是在这些字段长度比较长的情况下。完全索引这些长字段对存储和性能都是一种负担所以我们可以只索引这些字段值的前缀部分。
创建前缀索引的语法如下 ALTER TABLE tbl_name ADD INDEX index_name (column_name(length));前缀索引可以尽可能减少索引的存储空间但也有一些限制比如它不能被用于 ORDER BY 和 GROUP BY 除非是ORDER BY和GROUP BY的字段列表和索引的前缀一致。同时在使用前缀索引时也应该注意选择合适的前缀长度使得前缀索引的选择性独立值与总行数的比例尽可能的高查询效果才能更好。
2.4.2 否会得到与查询条件不相同的数据
在 MySQL 中前缀索引是根据指定的前缀长度来对字段值创建索引。因此在一些特定情况下是有可能出现你所说的多个不同的列值被映射到同一个索引前缀上的情况。然而这并不会导致查询结果与查询条件不符。
当进行查询时MySQL 首先会使用前缀索引来定位到可能的数据位置这个过程可能会返回多个结果包括你说的那些被映射到同一个索引前缀上的值。然而这时 MySQL 并不会直接返回这些结果它会再次对这些结果应用 WHERE 子句中的过滤条件也就是你的查询条件。只有满足你的查询条件的结果才会被最终返回。
因此即使在使用前缀索引时多个不同的列值被映射到同一个索引前缀上你最终得到的查询结果也仍然会是符合你的查询条件的数据。
2.4.3 前缀索引长度的设置
选择性索引的唯一值数量/表的总行数如果一个表有1000行而某个索引列有500个不同的值那么这个索引的选择性就是500/1000 0.5。在创建前缀索引时前缀的长度影响这个索引的选择性。过短的前缀可能导致选择性低因为相同前缀的值可能非常多过长的前缀则可能降低性能并增加存储需求因为索引变大了。因此尽量选择能保持较高选择性即索引的值比较独立的前缀长度很重要。 选择性的值介于 0 和 1 之间
如果选择性接近 0表示索引列中重复值较多比如性别字段只有 ‘男’,女’两个值那么索引的选择性就很低。如果选择性接近 1则表示索引列的值几乎唯一比如身份证号、邮箱等这类索引的效率通常非常高。 可以通过应用 SUBSTR() 函数来模拟前缀索引
SELECT COUNT(DISTINCT SUBSTR(column_name, 1, prefix_length)) FROM your_table;
将 “your_table” 替换为你的表名将 “column_name” 替换为你打算创建索引的列名将 “prefix_length” 替换为你想要测试的前缀长度。
这个查询可以统计给定前缀长度下的独立值数量可以尝试不同的前缀长度来找出最佳的前缀索引长度。 MySQL 官方并没有给出关于前缀索引的选择性的具体建议值因为这取决于你的具体使用场景和表的数据分布。官方的一般建议是创建索引时应尽量保持较高的选择性。
2.5 索引的回表
2.5.1 为什么会产生回表
在数据库中“回表” 是指通过非聚集索引查询数据时因为非聚集索引并不包含全部需要的数据一次查询不能够得到结果需要再通过聚集索引查询一次数据。
通常情况下非聚集索引的叶子节点存储的是聚集索引通常是主键的值而不是整行数据当我们的查询需要获取的数据不在非聚集索引中时MySQL就需要再通过聚集索引查询一次数据这就是回表。
回表通常会导致额外的IO操作因此在查询性能优化时尽量通过选择合适的索引来避免回表操作。例如覆盖索引Covering Index就是一种很好的避免回表的方法覆盖索引的列含了查询所需要的全部列因此查询可以直接在索引上完成无需再次查询数据表。
2.5.2 非聚集索引叶子节点为什么不存整条数据内容
每个非聚集索引叶子节点 如果都存整条数据行内容会占用大量空间当进行数据修改时会产生大量的修改维护成本增高所以mysql 在非聚集索引叶子节点值存储数据的主键id
2.6 索引下推Index Condition Pushdown
在使用索引查询时如果没有索引下推MySQL Server 通常会先读取索引然后通过索引找到符合条件的行的地址再根据地址将这一行数据完整地读取到内存中然后对这一行数据执行 WHERE 子句中的其他部分进行筛选。
而如果使用了索引下推MySQL Server 在读取索引的时候就会去检查行数据是否符合 WHERE 子句中的其他筛选条件如果不符合就跳过这一行不再读取这一行的数据。这样可以减少磁盘 I/O从而提升查询性能。
简单说索引下推就是把 WHERE 子句中的过滤条件尽可能地下推到存储引擎层的索引查询过程中这样存储引擎在扫描索引的时候就能直接过滤掉一些不需要的数据减少数据的访问量从而提升查询效率Mysql 默认也开启了这个功能。
2.7 多个索引怎么协同工作
假如对一张表的 name、phone 和 email 分别创建索引 MySQL 在查询过程中一次只能使用一个索引。尽管你为 name、phone 和 email 字段都创建了索引但是在执行如下查询时
SELECT * FROM table WHERE name John AND phone 12345 AND email johnexample.comMySQL 优化器的决策通常会选择一个最优的索引选择覆盖记录最少的索引来执行查询而不是同时使用多个索引。
然而在某些情况下MySQL 可以使用索引合并Index Merge的策略来优化查询。索引合并是在 MySQL 5.1 以后的版本中引入的优化策略它可以在同一张表的多个索引上查找行然后合并结果。有两种主要的索引合并策略
AND 合并先分别从两个索引找出满足条件的记录RowID然后取交集。OR 合并先分别从两个索引找出满足条件的记录RowID然后取并集。
需要注意的是尽管索引合并可以在一次查询中使用多个索引但是它的效率并不一定会比使用单一索引高因为它需要额外的步骤来合并结果。而且索引合并不总是可用的是否使用索引合并由 MySQL 优化器决定。
2.8 适合做索引的列
我们知道并不是所有的列都可以去创建索引
可以在 WHERE 子句中经常使用的列 加速对这些列的数据查询ORDER BY 和 GROUP BY 语句中使用的列可以建立索引以提高排序的速度连接表的时候经常用作连接条件的列建立索引加速数据表的连接
并非所有列都适合建立索引
数据重复度Cardinality高的列比如性别、是否默认等这类字段即使加了索引也不会提高查询速度列重复性越低离散度越高越适合做索引离散度公式: count(distinct(column_name)): count(*)对于大文本字段建立索引可能会消耗较多存储空间可以考虑使用全文索引或者其它方式如果一个表很小或者对一张表的查询并不频繁索引可能带来的效益非常有限不建议使用无序的列建立索引因为可能会频繁造成索引页的修改和分离索引并不是无限制的过多的索引会占用额外的磁盘空间增、删、改三个操作的性能也会有所下降因为每次 DML 操作都需要维护索引。所以创建索引需要平衡查询性能和存储及维护成本
三、索引失效的场景
在MySQL中以下情况可能会导致索引失效 使用 ! 或 操作符时索引可能不会被使用这将返回所有与操作数不匹配的行可能导致全表扫描。 对于列进行表达式运算将可能导致其索引失效比如对列进行函数运算如 SELECT * FROM table WHERE YEAR(date) 2021使用函数会导致MySQL无法有效地使用索引因为索引是根据列的原始值排序的 使用 NOT IN 和 NOT EXISTS 时索引将失效可能导致全表扫描。 不精确的查询例如使用 LIKE 操作符做模糊查询时如果通配符在开头那么索引将不会被使用如 SELECT * FROM table WHERE name LIKE ‘%abc’这构成了一个范围查询因为MySQL不知道一个字符串的开始应该是什么。 如果列类型是字符串那么查询时必须带引号否则索引无法生效例如应该是 WHERE name ‘abc’ 而不是 WHERE name abc 如果对某个列做了NULL的查询索引也会失效索引不包含NULL值。 如果MySQL估计全表扫描比索引更快时例如某些小表或者大部分情况下某个索引的值是一样的那么索引也可能失效。 在使用前缀索引的情况下如果查询列中超过前缀长度的部分索引将失效索引是按照前缀创建的如果查询超出了前缀长度则无法利用索引。 总结
本文讲述了mysql 索引的类型索引的具体实现以及索引的失效场景欢迎各位 码友 进行交流指正。
参考
1 数据结构在线模拟
