• 117680

    文章

  • 803

    评论

  • 12

    友链

  • 最近新加了换肤功能,大家多来逛逛吧~~~~
  • 喜欢这个网站的朋友可以加一下QQ群,我们一起交流技术。

MySQL索引模型

撸了今年阿里、腾讯和美团的面试,我有一个重要发现.......>>

数据库索引的出现其实就是为了提高数据查询的效率,就像书的目录一样。

索引的常见模型

哈希表

数组加链表实现,添加新数据较快,但是由于不是有序的,所以区间查询速度是很慢的。

哈希表适用于只有等值查询的场景,比如Memcached及其他NOSQL引擎。

有序数组

有序数组是讲索引字段有序的存放在数组中,所以在等值查询和范围查询场景中的性能就都非常优秀。

但是有序数组在插入更新时就比较麻烦,需要移动后面所有记录,成本太高;所以有序数组索引只适用于静态存储引擎。

二叉搜索树

二叉树的查询和更新复杂度均为O(log(N));假如一棵 100 万节点的平衡二叉树,树高 20,一次查询可能需要访问 20 个数据块。

所以大多数数据存储引擎采用N叉树

MySQL的索引

在MySQL中,索引是在存储引擎层实现的。Mysql索引使用的数据结构主要有BTree索引哈希索引。对于哈希索引来说,底层的数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能最快;其余大部分场景,建议选择BTree索引。

MyISAM中的索引

B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候,首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB中的索引

表都是根据主键顺序以索引的形式存放的,其表数据文件本身就是按B+Tree组织的一个索引结构,树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。 非主键索引的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引(secondary index)。

根据非主键索引查询到主键,再根据主键找到记录的过程我们称为回表;基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。

在根据主索引搜索时,直接找到key所在的节点即可取出数据;在根据辅助索引查找时,则需要先取出主键的值,在走一遍主索引。 因此,在设计表的时候,不建议使用过长的字段作为主键,也不建议使用非单调的字段作为主键,这样会造成主索引频繁分裂。

覆盖索引

SQL只需要通过索引就可以返回查询所需要的数据,而不必通过二级索引查到主键之后再去查询数据。

对于以上这张表,我们查询SELECT uid FROM group_user WHERE gid = 2 ORDER BY create_time ASC limit 10

首先,该语句ORDER BY 使用了Using filesort文件排序,查询效率低;其次,查询字段不在索引上,没有使用覆盖索引,需要通过索引回表查询;也有数据分布的原因。

解决方案:ALERT table group_user ADD INDEX idx_uid_gid_ctime (uid, gid, create_time)

由于只需查询uid字段,添加一个联合索引便可以避免回表和文件排序,利用覆盖索引提升查询速度,同时利用索引完成排序。

最左前缀原则

B+ 树这种索引结构,可以利用索引的“最左前缀”,来定位记录。

在建立联合索引的时候,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的;如果既有联合查询又要各自查询,那么第二考虑的就是空间原则了。

索引下推

以市民表的联合索引(name, age)为例。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是 10 岁的所有男孩”。

在MySQL5.6之前,根据最左前缀原则这个语句在搜索索引树的时候,只能用name字段索引,InnoDB 并不会去看 age 的值。

而 MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。


695856371Web网页设计师②群 | 喜欢本站的朋友可以收藏本站,或者加入我们大家一起来交流技术!

0条评论

Loading...


自定义皮肤 主体内容背景
打开支付宝扫码付款购买视频教程
遇到问题联系客服QQ:419400980
注册梁钟霖个人博客