一篇带给你MySQL索引知识详解
发布时间：2022-09-15 15:11:24 所属栏目：MySql教程 来源：
导读： 通过本篇文章，我们可以收获：
1、熟悉MySQL索引的基础知识：
2、如何提高开发、DBA和QA 在项目过程中关于 Mysql 索引相关操作的技术分析能力。
一、背景
分享这篇文章的目的：
1、熟悉MySQL索引的基础知识：
2、如何提高开发、DBA和QA 在项目过程中关于 Mysql 索引相关操作的技术分析能力。
一、背景
分享这篇文章的目的：
|
通过本篇文章，我们可以收获： 1、熟悉MySQL索引的基础知识： 2、如何提高开发、DBA和QA 在项目过程中关于 Mysql 索引相关操作的技术分析能力。 一、背景 分享这篇文章的目的：提升开发、DBA、QA在项目过程中关于提测 sql 和 sql 变更中关于添加、修改、删除索引合理性的分析能力。 二、MySQL索引1、概念说明 简单来说，索引的出现其实就是为了提高数据查询的效率，在表数据量较大时，索引的重要性尤为突出，可以理解为索引就像书的目录一样。 例如：一本1000页的书，如果你想快速找到其中某个知识点，如果不按照目录来查找，直接一页页翻开查找，无疑效率是十分低下的。 类比于数据库的表而言，索引其实就是它的“目录”。 2、常见索引模型哈希表 哈希表是一种以键-值(Key-Value)格式存储数据的结构，通过输入待查找的 Key值，就可以找到该 Key 对应的 Value。 哈希的思想比较简单，将值放在数组里，再使用哈希函数将输入的 Key 值换算成一个确定位置的值，最后把 Value 放在数组的这个确定的位置。 因为多个输入的 Key 值在使用哈希函数进行换算时，会出现多个 Key 换算出来是同一个值的情况，如下图中的 id1 和 idn 换算的结果都为：x，这种情况下哈希表给出的处理方案是拉出一个链表。 例如，现有一张用户表信息，需要根据用户 id 来查找用户 name，对应的哈希索引示意图如下： 这时当你要查 id1 对应的名字是什么，处理步骤是： 首先，将 id1 通过哈希函数算出 x。 然后，按照顺序遍历，找到 User1，即可查询到对应的 name 名称。 注意： 图中的 id 的值并不是有序递增的，这样做的好处是增加新的 User 时速度比较快，只需要往后追加。 但缺点也很明显，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。因为需要进行全表扫描一遍。 小结： 哈希表这种结构适用于只有等值查询的场景，比如一些NoSQL(非关系型数据库)引擎。 有序数组 有序数组在等值查询和范围查询场景中的性能是十分优秀的。 还是上面的根据用户 id 来查找用户 name 的例子，如果使用有序数组来实现的话，对应的示意图如下： 假设这里的 id 没有重复，数组就是按照 id 递增的顺序进行保存的，这时如果你要查 id2 对应的名字，用二分法就可以快速得到，这个时间复杂度是O(log(N))。这种索引结构能很好的支持范围查询 。 例如你想要查询 [idm, idn] 区间的 User 的 name 信息，可以先用二分法找到 idm，如果不存在 idm，就去寻找大于 idm 的第一个 User，然后依次向右遍历，直至查询到第一个大于 idn 的 id 号，退出循环。 注意： 单从查询效率来看，有序数组就是最好的数据结构了。思考一个问题，当这种数据结构在遇到更新数据(插入或删除)时，会怎样? 比如你删除或插入一条记录，就会非常麻烦，因为插入数据需要将后半部分的数据往后挪动一个位置，删除数据需要将后半部分的数据往前挪动一个位置，成本太高了。 小结： 有序数组索引只适用于静态存储引擎，适合存储不会再修改的数据。 二叉搜索树 如果还是用上面使用 id 来查询 name 的例子，来看下使用二叉搜索树的数据结构来实现，对应的示意图如下： 二叉搜索树的特点： 父节点左子树所有结点的值小于父节点的值，右子树所有结点的值大于父节点的值。 如果你要查id2的信息话，按照图中的搜索顺序就是按照UserA—>UserC—>UserF—>User2这个路径得到，这个时间复杂度是O(log(N))。 树有二叉，也可以有多叉，多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右是递增的。 二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。原因是索引不仅存在内存中，也要写到磁盘上。 3、InnoDB索引模型 在 Mysql 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即使用不同的存储引擎，其对应索引的工作方式并不一样。 InnoDB存储引擎在Mysql数据库中使用最为普遍，下面来看下InnoDB的索引模型。 在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表，且数据都是存储在B+树中的。 为什么使用的是B+树，而不是其他的数据索引模型呢? (1) 减少磁盘IO次数 B+树的数据结构模型将所有数据都放到叶子节点，且叶子节点形成一个列表(可以做范围查询)，非叶子节点只放键值，这样每个数据叶中可存放的有效数据就多了，可以有效减少磁盘IO次数。 (2) 每次查询的时间复杂度是固定的 在B+树中，由于分支节点只是叶子节点的索引，所以对于任意关键字的查找都必须从根节点走到分支节点，所有关键字查询路径长度相同，每次查询的时间复杂度是固定的。但是在B树中，其分支节点上也保存有数据，对于每一个数据的查询所走的路径长度是不一样的，所以查询效率也不一样。 (3) 遍历效率更高 由于B+树的数据都存储在叶子节点上，分支节点均为索引，方便扫库，只需扫一遍叶子即可。但是B树在分支节点上都保存着数据，要找到具体的顺序数据，需要执行一次中序遍历来查找。所以B+树更加适合范围查询的情况，在解决磁盘IO性能的同时解决了B树元素遍历效率低下的问题。 在Innodb中，Mysql中的数据是按照主键的顺序来存放的。那么聚簇索引就是按照每张表的主键来构造一颗B+树，叶子节点存放的就是整张表的行数据。由于表里的数据只能按照一颗B+树排序，因此一张表只能有一个聚簇索引。 在Innodb中，聚簇索引默认就是主键索引。 假如表没有设定主键，则按照下列规则来创建聚簇索引。 例如现有一个主键列为id的user表MySQL 索引，表中有字段 t 和 name，并且在 t 上有索引。 建表语句如下： create table user( id int primary key, t int not null, name varchar(16), index (t))engine=InnoDB; 非聚簇索引联合索引 使用多个列字段建立的索引，称为联合索引，也叫组合索引。 联合索引为：(t,name)。 其建表语句如下： create table user( id int primary key, t int not null, name varchar(16), index(t), index(t,name) )engine=innodb; 说到联合索引，一定要谈谈最左匹配原则。 所谓最左匹配原则指的就是如果 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询(>、100属于范围查询，将后面d的索引匹配给中断了。 前缀索引 当索引列的字符比较多时，索引很大且速度很慢，此时可以优化索引列，只索引列开始的部分字符串，以此节约索引空间，提高索引效率。 前缀索引的使用原则是：降低重复的索引值。 从上表可以发现 st_num 字段前7位都是重复的，都是以0102021开头的。 如果使用前1-7位字符来做前缀索引就会出现大量索引值重复的情况。 此时索引值重复性高，查询效率低下，不符合前缀索引的原则，因此可以依据具体需求来决定使用前8-10位字符来做前缀索引。 前缀索引创建方式如下： create table `student` ( `st_num` varchar(255) not null, `sex` int(10) not null, `name` varchar(255) not null, index (st_num(8)) )engine=InnoDB; 普通索引 如下user建表语句中的 t 就是一个普通索引，普通索引与主键索引的区别在于，普通索引的叶子节点存放的不是行数据，而是主键值。(在索引原理中会详细说明)。 例如现有一个主键列为id的user表，表中有字段 t 和 name，并且在 t 上有索引。 建表语句如下： create table user( id int primary key, t int not null, name varchar(16), index (t))engine=InnoDB; 例如： select * from user where t=100; 这个查询sql会通过 t 这个普通索引在自身的 B+ 树上找到对应主键：1，然后再使用1在主键索引所在的B+树上查询出真实表的行数据后返回结果，这个操作被称为回表。 唯一索引 与普通索引类似，不同点在于唯一索引的索引列的值必须唯一，但允许有空值，这点与主键不同(主键索引列的值唯一，但不能为空)。 如果是多个字段组成的联合索引，则列值的组合必须唯一，创建方法与普通索引类似。 全文索引 5.6版本之后InnoDB存储引擎开始支持全文索引，Mysql允许在char、varchar、text类型上建立全文索引。 （编辑：拼字网 - 核心网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐