mysql索引内部实现与算法分析

本篇内容主要讲解“mysql索引内部实现与算法分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql索引内部实现与算法分析”吧! 存储引擎从索引的根节点开始进行搜索，通过节点槽中的指针向下层查找，比较节点页的值和要查找的值找到合适的指针进入下层子节点。存储引擎最终要么找到对应的值，要么该记录不存在。
叶子节点的指针指向的是被索引的数据，而不是其他的节点页
索引可以按值查找之外，还可以用于查询中的order by操作(原因：索引树中的节点是有序的)
B+tree索引的限制：
1.如果不是按照索引的最左列开始查找，则无法使用索引2.不能跳过索引中的列3.如果查询中有某个列的范围查找，则其右边所有列都无法使用索引优化查找B+树的插入操作B+树的插入必须保证插入后叶节点中的记录依然排序，插入B+树的三种情况第一种情况，往图中插入28，leaf page和index page都没有满，直接插入第二种情况，往图中插入70，leaf page满了，index page没有满说明：采用旋转操作使得其减少一次页的拆分操作第三种情况，在图中插入95，leaf page和index page都满了说明：B+树为了保持平衡，对新插入的键值可能需要大量的拆分页操作，但是B+树主要用于磁盘，我们应该尽可能减少页的拆分，可以通过旋转功能（leaf page已经满了，但是左右兄弟节点没有满）
B+树的删除操作B+树使用填充因子来控制树的删除变化，50%是填充因子可设的最小资。同样必须保证删除后叶节点中的记录依然排序。删除B+树（根据填充因子的变化来衡量）的三种情况在图中删除70在图中删除25，此时25的兄弟节点的28更新到page index中在图中删除60，此时填充因子小于50%，需要做合并操作B+树索引B+树索引本质是在B+树在数据库中的实现 特点：扇出性数据库中B+树索引分为：聚集索引（clustered index）、辅助聚集索引（secondary index）索引组织表：表中数据按照主键顺序存放堆表：按照插入数据顺序存放，堆表上的索引都是非聚集的，且堆表没有主键聚集索引(每张表只有一个):按照每张表的主键构造一棵B+树，且叶节点存放着整张表的行记录数据，所以聚集索引的叶节点也成了数据页，辅助聚集索引的叶节点上存放的仅仅是键值以及指向数据页的偏移量，不是一个完整行记录
聚集索引不是一种单独的索引类型，而是一种数据存储方式。innodb的聚集索引实际上在同一个结构中保存了B+tree索引和数据行。当表有聚集索引时，数据行实际上是存储在索引的叶子页中
说明：聚集索引的存储在物理上是不连续的，在逻辑上是连续的：1.页通过双向链表链接，页按照主键的顺序排序；2.每个页中的记录也是通过双向链表进行维护，物理存储上可以同样不按照主键存储聚集索引的优点：可以把相关数据保存在一起数据访问更快(聚集索引将索引和数据保存在同一个b+tree中)使用覆盖索引扫描的查询可以直接使用页节点中的主键值聚集索引的缺点： 聚集数据提高了IO性能，如果数据全部放在内存中，则访问的顺序就没那么重要了插入速度严重依赖插入顺序。按主键的顺序插入是速度最快的。但如果不是按照主键顺序加载数据，则需在加载完成后最好使用optimize table重新组织一下表更新聚集索引列的代价很高。因为会强制innod将每个被更新的行移动到新的位置基于聚集索引的表在插入新行，或主键被更新导致需要移动行的时候，可能面临页分裂的问题。页分裂会导致表占用更多的磁盘空间。聚集索引可能导致全表扫描变慢，尤其是行比较稀疏，或由于页分裂导致数据存储不连续的时非聚集索引比想象的更大，因为二级索引的叶子节点包含了引用行的主键列非聚集索引访问需要两次索引查找(非聚集索引中叶子节点保存的行指针指向的是行的主键值)，对于innodb自适应哈希索引可免费主机域名以减少这样的重复工作辅助索引：叶节点包含键值，且每个叶级别的索引行还包含一个书签（相应行数据的聚集索引）辅助索引和聚集索引的关系图说明：辅助索引的存在不影响数据在聚集索引中的组织，所以每张表可以有多个辅助索引原理：通过辅助索引来寻找数据时过程：innodb会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。B+树索引的管理索引的创建和删除的方法：alter table；create /drop index创建主键索引过程：先创建一张新的临时表免费主机域名，然后将数据导入临时表，删除原表，再把临时表重名为原来的表名创建辅助索引过程：在创建过程中不需要重建表，只会对表加上一个S锁，速度极快。通过show index from table_name可以查看表中索引的信息说明：table：索引所在的表名Non_unique:非唯一索引，primary key是0Key_name:索引的名称Seq_in_index:索引中该列的位置Column_name：索引的列Collation:列以什么方式存储在索引中，B+树索引总是A，即排序；如果使用了heap存储引擎，且建立了hash索引，就会显示NULL，因为hash通过hash桶来存放索引数据，而不是对数据进行排序Cardinality:表示索引中唯一值的数目的估计值，Cardinality/表的行数 尽可能接近1，太小则需要重建该索引Sub_part:是否是列的部分被索引，如只索引某一列的前多少字符，如果索引整个列，则该字段为NULLpacked：关键字如何被压缩。没有被压缩则为NULLNULL：是否索引的列含有null值，Index_type:索引的类型，innodb只支持B+索引comment:注释
B+树索引的使用当访问高选择性字段并从表中取出很少一部分行时，就需要对这个字段添加B+树索引注：当取出数据量超过表中数据的20%，优化器就不会使用索引，而是进行全表的扫表。且预估的返回行数的值是不准确的顺序读、随机读、预读取顺序读（sequntial read）：顺序地读取磁盘上的块（block）随机读（random read）：访问的块不是连续的，需要磁盘的磁头不断移动预读取（read ahead）：通过一次IO请求将多个页预读取到缓冲池中随机预读取（random read ahead）：当一个区（64个连续页）中13个页也在缓冲区中，并在LRU列表的前端（即页是被频繁访问），则innodb会将这个区剩余的所有页预读到缓冲区线性预读取（linear read ahead）：基于缓冲池中页的模式，而不是数量。如果一个区中的24个页都被顺序访问了，则innodb会读取下一个区的所有页innodb_read_ahead_threshold参数：表示一个区中的多少页被顺序访问时，innodb才启用预读取。默认值为56，即当一个区中的56个页被顺序访问时，则预读取下个区的所有页联合索引：还是一棵B+树，不同的是联合索引的键值的数量不是1，而是大于等于2哈希算法自适应哈希索引使用的是散列表（hash table）的数据结构哈希表（散列表）：由直接寻址表改进而来
哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效原理：对每一行数据，存储引擎会对所有的索引列计算一个哈希码(很小且不同键值的行计算出的哈希码也不一样)，哈希索引将所有哈希码存储在索引中，同时也保存着每个数据行的指针。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中在mysql中，只有memory引擎显示支持哈希索引(注：为非唯一哈希索引)，NDB支持唯一哈希索引，innodb支持自适应哈希索引
哈希索引的限制：1.哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引的值来避免读取行2.哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序3.哈希索引不支持部分索引匹配查找(因为哈希索引始终是使用索引列的全部内容来计算哈希值的)4.哈希索引只支持等值查询，包括= ，in5.当出现哈希冲突(不同的索引列值却有相同的哈希值)时，存储引擎必须遍历链表中所有的行指针，逐个比较直至找到符合条件的行6.如果哈希冲突很多，则索引维护操作的代价会很高（要避免哈希冲突，必须在where条件中带入哈希值和对应列值）。自适应哈希索引通过Innodb_adaptive_hash_index参数可以开启自适应哈希索引，数据库启动时会自动创建槽数为innodb_buffer_pool_size/256个哈希表可以通过show engine innodb status查看当前自适应哈希索引的使用状况说明：包括哈希索引的大小，使用情况，每秒使用自适应哈希索引搜索的情况注：哈希索引只能用来搜索等值的查询，其他类型不能使用哈希索引到此，相信大家对“mysql索引内部实现与算法分析”有了更深的了解，不妨来实际操作一番吧！这里是云技术网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

相关推荐: ORACLE常见等待事件有哪些

这篇文章主要介绍“ORACLE常见等待事件有哪些”，在日常操作中，相信很多人在ORACLE常见等待事件有哪些问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”ORACLE常见等待事件有哪些”的疑惑有所帮助！接下来，请跟着小编一起来学…