一个例子与InnoDB索引的几个概念-阿里云开发者社区

开发者社区> 丁奇> 正文

一个例子与InnoDB索引的几个概念

简介:
+关注继续查看

1、一个简单的sql语句问题

    假设当前我们有一个表记录用户信息,结构如下:

    a)      表结构

CREATE TABLE `u` (

  `id` int(11) NOT NULL DEFAULT ‘0′,

  `regdate` int(1) unsigned,

  …..

  PRIMARY KEY (`id`),

  KEY `regdate` (`regdate`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk

说明:1) 由于需要按照注册时间单独查询,建了一个regdate的索引

            2) 其他信息未列出, 一行长度100字节左右,表行数百万级。 

b)      需求:需要一个语句查出表中id为10000整数倍的记录总数。

 

 

2、常规答案

    一个正常想到的语句是 select sum(id % 10000 = 0) from u; —— (SQL1)

    我们来看这个语句的执行流程:

a)      遍历所有数据,取出id字段

b)      计算id%10000=0的值并通过sum累计。

           在构造的环境中这个语句的执行时间为2.6s.

 

 

3、查的多,查得快

    假设我们同时要查出注册时间在2007年之前的用户总数,我们自然得到这个语句

     select sum(id % 10000 = 0), sum(regdate<1167667200) from sbtest;—-(SQL2)

    执行结果发现这个语句执行时间约0.5s 。 这个语句查的数据结果比SQL1多,但执行时间却降为1/5.

 

 

4、分析 

    可以直接从执行期间的磁盘参数,或者在os/os0file.c中将程序读取的数据量输出结果查看,直观结果是SQL1读取了更多的磁盘数据。

 

问题1:在SQL1执行过程中,遍历所有数据,InnoDB只从磁盘读取了id这个字段,还是全部读入?

    实际上由于id是聚簇索引,并没有一个单独的索引树存id,因此在磁盘上,id索引树的叶节点上就是数据。 InnoDB以page为单位读取,在取id的过程中,必须将所有的数据读入。

    于是我们发现,在SQL1中,我们只需要id字段,而每行额外读入了几百字节的数据。

 

问题2:SQL2避免了读全数据?

    确实如此。

    我们对比两个语句的explain结果, 发现仅有的不同是选用的key结果不同。

SQL1 SQL2
key: PRIMARY key: regdate

    由于regdate是非聚簇(secondary index)索引,单独存于另一棵树。 我们知道使用非聚簇索引时,需要读行数据的时候,需要再到聚簇索引中取得。显然SQL2不会再读一遍全数据(否则性能必然低于SQL1)。

    而其原因是覆盖索引(covering index)。 非聚簇索引的叶节点上是聚簇索引的字段值,需要取数据时,根据这个值再去聚簇索引上取。而这时InnoDB变“聪明”了, 需要取的值只是id,而id作为聚簇索引的key信息,已经得到,不需要再到聚簇索引中读取数据。

    由于regdate索引树上只有regdate和主键(id)的信息,因此数据量远小于全表数据,因此SQL2的读盘量小于SQL1,执行速度快。

 

5、其他 

    这个例子涉及到几个概念, 聚簇索引(cluster index)、非聚簇索引(secondary index), 覆盖索引(covering index),还有磁盘的数据存放。都算是一些基本的内容,却是平时见到的一些优化的理论基础。举几个例子如下:

1)      我们经常被告诫select之后只填最必须的字段

    其中的一个原因是减少网络传输。但不一定能够提升服务器执行性能。比如例子中的表,select  * from u where id = n; 与select user_name from u where id =n一样。

    当然有些时候效果会很理想,比如 select id from u where regdate=xxx 就比select * from u where regdate=xxx快很多,原因已说明。

2)      查询符合条件的第10w个记录开始的10个记录。

    这个例子在其他博文上被多次提及,

select * from t order by a limit 100000, 10; 可以改进为

select * from t where a>=(select a from t order by a limit 100000,1) limit 10;

    在笔者环境中性能提升约1000倍。

    原因即在于, 改进语句中,子查询中的排序只在非聚餐索引a上执行,由于覆盖索引,排序过程不需要访问聚簇索引。实际读读取全数据的只有10条记录,而原语句则需要读所有记录的全数据。

    当然执行排序的过程消耗是一样的。 

 

6、结束

    回到开头,如果只需要查id满足特定条件的记录总数,可以使用select sum(id % 10000 = 0) from u force index (`regdate`);  

    把sum(id %10000=0)换成其他操作对执行效率均没有影响。 

    但若查询内容中出现除id和regdate外的其他字段,则force index优化无效,可自行分析。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
10086 0
1分钟了解MyISAM与InnoDB的索引差异
数据库的索引分为主键索引(Primary Inkex)与普通索引(Secondary Index)。InnoDB和MyISAM是怎么利用B+树来实现这两类索引,其又有什么差异呢?这是今天要聊的内容。
299 0
InnoDB索引概述,二分查找法,平衡二叉树
索引是应用程序设计和开发的一个重要方面。如果索引太多,应用的性能可能会受到影响;如果索引太少,对查询性能又会产生影响。要找到一个合适的平衡点,这对应用的性能至关重要。 如果知道数据的使用,从一开始就应该在需要处添加索引。
1061 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13891 0
MongoDB索引介绍
一、Single Field Indexes 示例文档: { "_id": ObjectId("570c04a4ad233577f97dc459"), "score": 1034, "location": { state: "NY", city: "New York" } } 1、语法: db.
769 0
+关注
丁奇
专注于数据存储系统、MySQL源码研究和改进、MySQL性能优化和功能改进,并设计了阿里云高可靠双通道binlog方案。他在业务场景分析、系统瓶颈分析、性能优化方面拥有丰富的经验。微博@淘宝丁奇。
43
文章
18
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载