江苏建设电子信息网站,洛阳建网站公司,垂直网站建设规模,手机网站素材2019独角兽企业重金招聘Python工程师标准 问题#xff1a;SQL查询慢怎么办#xff1f; 优化手段#xff0c;加索引。 索引是帮助MYSQL高效的获取数据的排好序的数据结构。 问题#xff1a;索引结构为什么使用Btree而不使用二叉树#xff0c;红黑树或者HASH结… 2019独角兽企业重金招聘Python工程师标准 问题SQL查询慢怎么办 优化手段加索引。 索引是帮助MYSQL高效的获取数据的排好序的数据结构。 问题索引结构为什么使用Btree而不使用二叉树红黑树或者HASH结构 二叉树的特性右边子节点的值大于父节点且左边子节点的值小于父节点 二叉树使用二叉树来做索引的数据结构当索引值递增的时候二叉树也会不断地增加右子节点 那么在查找索引的时候所做的IO操作等同于没有索引逐行查找数据的IO操作。 红黑树平衡二叉树。随着数据量增大树的高度也会变高。2的N次方数量量假设100万那么N至少也是50那么树的高度也是50如果数据在子叶节点上那么至少要经过50次的IO操作才能找到数据。 HASH散列表结构key不适合排序而且数据是散列的分布的不利于IO读写。 BTree一个节点上横向扩展。即一个节点上可以存储多个元素Degree且每个元素都可以有子节点叫做多叉树。 度Degree-节点的数据存储个数叶节点具有相同深度叶节点的指针为空节点中的数据KEY从左至右递增排列 以5阶B树举例 BTreeBtree变种 非叶子节点不存储data只存储key可以增大度Degree-节点的数据存储个数叶子节点不存储指针叶子节点增加了顺序访问指针提高区间访问的性能以5阶B树举例 问题Btree和BTree的区别 Btree每个节点都包含了key和data而Btree只有叶子节点保存了key和data其他节点只保存了key方便增大度的个数Btree的叶子节点之间 增加了指针提高了查询区间的性能不需要像Btree一样每次都从根节点开始查找Btree中父节点和叶子节点关键字的字段值不会冗余而Btree会冗余的存储关键的索引值即父节点和叶子结点都存在相同的关键值 MyISAM索引实现非聚集 MyISAM索引文件和数据文件是分离的。它是针对建表的而不是针对数据库的既同一个数据库可以多种类型的表myISAM和innoDB。 在mysql的文件件中会发现一个表有三个文件 test_table_myisam.frm // 存储表的结构表的定义test_table_myisam.MYD // 存储表的数据test_table_myisam.MYI // 存储表的索引文件myisam存储引擎是非聚集索引所以在MYI的文件中以Btree的数据结构存储了表的索引信息而索引内容中的data是指向数据的在MYD文件中的地址。 在myisam存储引擎中主键索引使用了Btree而辅助索引非主键索引也同样根据字段的值使用了Btree结构来存储。 innoDB索引实现聚集 表数据文件本身就是按BTree组织的一个索引结构文件 聚集索引 定义叶节点包含了完整的数据记录为什么innoDB表必须有主键并且推荐使用整形的自增主键为什么非主键索引结构叶子节点存储的是主键值一致性和节省存储空间在mysql的文件件中会发现一个表有两个文件 test_table_myisam.frm // 存储表的结构表的定义test_table_myisam.ibd // 存储表的索引数据在innoDB存储引擎中辅助索引、非主键索引都不是聚集索引。 问题为什么Btree比Btree快 树的高度h影响了查找效率因为从根节点开始每次查找下一个节点都可能需要一次IO读写。所以增加非叶子节点的度可以有效提高查询效率。而CPU从磁盘上读取数据到内存中最小单元是页一页读取的最小数据是4KB而CPU一次IO读取的数据是一页的整数倍且最大值也有限制。而mysql在底层设置的大小是16KB。所以BTREE要把数据放在叶子节点所以非叶子几点就可以增加更多的节点数。而Btree节点和数据是保存在一起的所以非叶节点的节点数要比Btree少树的高度就比Btree高。 问题为什么MYSQL页文件默认的配置是16KB 我们假设一行的数据是1K那我们一页的数据就能存储16行数据也就是一个叶节点可以存储16条记录再来看非叶节点假设ID是bigint类型那么长度为8B指针大小在InnoDB源码中为646B一共就是14B那么一页里面就可以存储16K/141170个主键指针 那么一颗高度为2的B树能存储的数据为1170*1618720条一颗高度为3的B树可以存储1170*1170*1621902400千万条 原因就是16KB就足够应对千万条表记录了。 问题为什么innoDB表必须有主键并且推荐使用整形的自增主键 使用innoBD存储引擎时要建立主键索引如果我们没有主动创建索引那么innoDB会自动帮我们建立主键索引。innoDB会在我们创建的表里找一列唯一的可以代表主键的字段来创建主键索引如果没有这样的字段innoDB会默认加一列字段来创建主键索引。有点类似oracle中的rowId。为什么要推荐整形而且自增的字段当做主键为什么建议使用整形类型因为如果使用uuid作为主键索引uuid类型比整数类型大为了方便IO操作一次读取4个页的数据mysql默认16KB一页是4KB意味索引的非叶子节点上的关键字要比使用整数类型的关键字数量小那么Btree的高度就可能增加那么读取叶子节点上的数据的IO操作次数就增加了。那么查找的效率就低了。为什么要自增因为当新纪录插入时主键自增的话在Btree中插入节点比较方便而使用uuid为主键新纪录的uuid不一定比前面的uuid大可能会将新纪录插在靠前面的叶子节点中叶子节点满了会取中间的关键字向上存放到父节点中可能会造成树的分裂微观上性能就比自增的差。 转载于:https://my.oschina.net/xiaoyoung/blog/3041322
