mysql基础—索引
发布时间：2022-12-10 11:33:33 所属栏目：MySql教程 来源：
导读： 针对不同的应用场景创建合适的索引对于优化mysql查询速度非常关键，今天一起来学习一下mysql索引相关的原理性内容。
mysql索引的数据结构
如图是以二叉搜索树为数据结构建立起的索引，这样的
mysql索引的数据结构
如图是以二叉搜索树为数据结构建立起的索引，这样的
|
针对不同的应用场景创建合适的索引对于优化mysql查询速度非常关键，今天一起来学习一下mysql索引相关的原理性内容。 mysql索引的数据结构 如图是以二叉搜索树为数据结构建立起的索引，这样的索引数据结构树高是O(log2n), 我们知道mysql数据记录和索引树节点都是存在磁盘上的，mysql磁盘IO的次数是和索引树高成正比的。一张有1千万条记录的表索引树的树高大约是23，也就是近乎23次磁盘IO，以二叉搜索树为数据结构的索引树显然是不合适的。 B树 mysql并没有使用二叉搜索树组织索引，而是基于B树，B树是由二叉搜索树演变的，有以下几个特点： mysql创建表时创建索引_mysql表索引_mysql创建表索引语句 由于B树在一个节点可以存储更多的key，因此B树索引树树高比二叉搜索树要低很多。但直接拿B树做索引还存在一些问题，比如SQL中的范围查询，直接用B树索引就无法很好的支持。 2. B+树 B+树满足以下性质： 相比于B树B+增加了两个比较重要关键的性质（加黑的两条） “非叶子节点不再存储数据，数据统一存储在叶子节点”可以减小非叶子索引节点的大小，让一个磁盘页存储尽可能多的索引key，降低树高。 “叶子节点之间增加链表”可以使索引很好的支持SQL中的范围查询。 在总结为什么B+树索引效率高的之前回顾一下我们学操作系统时所学过的两个小知识：程序访问局部性原理和磁盘预读。 3. 为什么B+树索引效率高 InnoDB和MyISAM存储引擎索引实现的差异 mysql常用的存储引擎主要是InnoDB和MyISAM，两种引擎分别有不同的特点。比如InnoDB支持事务，行锁，而MyISAM不支持事务，支持表级别锁等等。在两种存储引擎在索引实现上也存在一定的差异，下面我们一起来看下两种存储引擎在索引实现上差别在哪儿。 存储引擎对于主键字段和非主键字段在建立索引时会有不同，在分析存储引擎索引实现时需要分别看看它们的主键索引和普通索引(非主键索引)的差异。 1.MyISAM索引实现 主键索引是在数据表主键上建立的索引，MyISAM主键索引有以下几个特点： (1). 基于B+树实现 (2). 叶节点存储的是数据的地址 (3). 独立连续的区域存储数据行 辅助索引是在非主键字段上建立的索引： (1). 基于B+树实现 (2). 叶节点存储的是数据的地址 (3). 独立连续的区域存储数据行 可以看到MyISAM的主键索引和辅助索引在实现上是没有差别的，仅仅把索引的列改成对应的索引字段。 2.InnoDB索引实现 (1) 基于B+树实现 (2) 叶节点存储的是数据行 (1)基于B+树实现 (2)叶节点存储的是对应的主键 InnoDB辅助索引叶子节点存储的是记录对应的主键，然后利用主键去索引对应的记录。也就是说利用辅助去索引记录一定会用到InnoDB的主键索引，InnoDB一定会建立一个主键索引，不论表中是否指定了主键。 3.MyISAM和InnoDB索引差异总结 联合索引和最左前缀匹配 前面两部分介绍的内容都是基于一个字段上的索引，mysql还支持建立联合索引，联合索引是在多个字段上建立起一个索引。例如在下面这个表上建一个的联合索引： 联合索引索引树的构建与索引字段的顺序有关。联合索引虽然是多个字段共同构成索引，但索引树和单列索引一样，仍然是一个，其实单列索引也是一种特殊的联合索引。例子中，联合索引最左索引字段是col3，因此索引树以col3字段构建，对于col3相同的字段再以col2字段做索引。逻辑类似于：group by col3 , clo2。 最左前缀匹配原则：在mysql上建立联合索引会遵循最左匹配原则，即在建立索引时会以最左字段作为主索引，同时where查询条件中也须包含索引最左字段才能利用到联合索引。 我们通过一个例子对where查询条件中必须包含索引最左字段进行说明。 在tb_user表上建立了三列联合索引，最左字段为name。 查询条件中包含name字段 mysql创建表时创建索引_mysql表索引_mysql创建表索引语句 可以看到当我们在查询条件中不包含name(建立联合索引的最左字段)字段时mysql表索引，查询语句是没有用到联合索引的，包含name字段时查询语句就能够用到联合索引"ids_name_addr_pwd"。 建立一个联合索引，实际上相当于建立了（col1）、（col1,col2）,(col1,col2,col3)三个索引，相比于建立多个单个索引而言使用联合索引可以大大减小维护索引的开销 （编辑：云计算网_梅州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
浙公网安备 33038102330479号