可信网站认证服务商,域名解析查询工具,wordpress无法查看发布,做视频直播类型的网站导读假设这个用户中心系统是一个交友平台的一个子系统#xff0c;现在产品想要提供一个按生日区间筛选用户的功能。那么#xff0c;要实现这个功能#xff0c;我们需要写这么一条SQL#xff1a;SELECT 为了保证这条SQL的查询性能#xff0c;我们会给用户表中的birthday字段…导读假设这个用户中心系统是一个交友平台的一个子系统现在产品想要提供一个按生日区间筛选用户的功能。那么要实现这个功能我们需要写这么一条SQLSELECT 为了保证这条SQL的查询性能我们会给用户表中的birthday字段添加索引。乍一看最初的表结构设计好像已经有一个索引index_age_birth这个索引包含了birthday字段是不是可以用这个现成的索引不再另加索引对MySQL有一些基本了解的开发同学知道MySQL InnoDB的索引查找是按照最左前缀匹配原则的即SQL中的Where条件中的字段如果要命中索引必须按照该索引列的顺序逐一比对最后定位查找结果。index_age_birth这个索引列的顺序为明显age在前birthday在后所以不满足最左前缀匹配原则无法命中该索引。所以现在我们必须给birthday这个字段单独加上索引见下图ALTER 这时你可能想到birthday这个字段我们设的默认值为NULL随之会产生一个疑问如果某一个字段为NULL以该字段作为条件进行查询是否会影响我们查询的性能今天这个章节我们就先看看NULL这个值在InnoDB索引结构中是怎么存储的然后结合上面这条select查询SQL看看MySQL又是如何执行这条SQL的最后给到这个问题的答案。存储结构由于之前的用户表记录中没有birthday为NULL的记录为了讲解NULL值对SQL查询性能的影响我先添加一条birthday为NULL的记录如下INSERT 通过《基础篇》中我对InnoDB索引结构的讲解我们知道我加的这个index_birthday索引是一个辅助索引所以我们就来看一下NULL这个值在该辅助索引的结构是什么样的如下图上图就是一颗birthday字段为索引的B-Tree辅助索引的B-Tree结构我在《基础篇》中详细讲解了大家可以对照之前的讲解看下这张图我在这里主要说一下NULL值的位置NULL值被存储到了该辅助索引B-Tree的非叶节点页1、页2和叶子节点页4的最左边。也就说NULL记录总是出现在B-Tree的最左侧。那么针对本章《导读》中的这条select语句MySQL又是如何查找索引的呢为了方便浏览我在这边再贴一下这条SQLSELECT 查找过程在前面的章节我讲解过了MySQL查找辅助索引的整个过程那么结合这个例子我再讲解一下MySQL是如何在index_birthday这个索引中查找[2007-01-022008-08-02]之间的记录的见下图如上图红色箭头部分深度遍历这颗B-Tree页1 - 页3在页1中发现2007-01-02大于2006-07-01所以箭头流向指向页3。页3 - 页6发现2007-01-02位于[2006-07-012007-06-07]之间所以箭头流向指向页6。由于页6为叶子节点而辅助索引B-Tree所有节点内的记录按索引列升序排列叶子节点之间是双向链表叶子节点内记录组成单向链表所以发现页6中第一条大于等于2007-01-02的记录是2007-06-066然后从页6中的2007-06-066后开始顺序遍历2007-06-066和页7的所有记录。发现页7最后一条记录的age的值为2008-02-06小于2008-08-02因此得到所有满足[2007-01-022008-08-02]之间的记录的主键6、8、2、5。最后根据主键6、8、2、5到聚簇索引中查询相应记录即可关于详细查找过程在这里我留一个悬念在《IN字段查询多少个值最合适》这一章节中我会详细讲解。小结通过上述内容的讲解我们知道了一张表中的一条记录中的某个字段a它的值为NULL值同时a字段加了索引那么a字段为NULL的记录一定出现在辅助索引非叶或叶子节点的最左边采用深度遍历查找这条记录效率是最高的。查找a字段不为NULL的记录和NULL记录的数量无关通过辅助索引B-Tree的二分查找是能很快定位到记录的。所以表结构中存在默认值为NULL的字段并不会影响查询的性能。思考假设现在有这么两条记录如下INSERT 如果现在我写了这样一条SQLSELECT 查找这两条记录的过程是怎么样的更多关于MySQL源码的解读内容可以加vx群交流哦或者知乎私信我我都会回复的https://weixin.qq.com/g/AQYAAMmWP-ei65ZsYYGNtPd1Xt4-_tIcJO8jlAYRhlN1U1T0YdxXejTWCvh5X2sE (二维码自动识别)
