一个例子与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结果不同。
SQL1SQL2key: PRIMARYkey: 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优化无效，可自行分析。
?
?
1 楼 nofreezou 2012-03-10 不知您遇到过这个问题没有：A. select z,z_data from tb1 force index idx1 order by z limit 10000,10;
B. select z,z_data from tb1 order by z limit 10000,10;
--
以后两个语句，一个使用了索引idx1，一个没使用索引，貌似导致mysql用了不同的排序方法，虽然2个排序的结果对z来说都是正确的，但附加的数据z_data却表现出不同的结果。。。
--
上面的force index并不需要强制加上去，mysql在面对limit a,b语句事，当a+b不超过某一个值时会使用索引idx1, 而超过该值后却不使用索引idx1(直接全表扫描），这种选择是mysql独立作出的， 这样导致翻页时出现问题(z_data混乱）。。。。
2 楼 丁林.tb 2012-03-22 to :nofreezou
对,确实会这样,为了省一次order by, 我也用force index.. 3 楼 aweber 2012-05-03 请教一个问题：
select f1, f2 from tab where k1=v1 and k2=v2 order by k3 limit 100
对于这样一个普通的查询， 我对order by的执行顺序有点疑惑：
如果mysql通过分析where条件，选择了一个合适的索引， 这个时候有两种情况
1，出现覆盖索引的情况， 就是选择的索引刚好包括了所有需要的字段，不管是select查找的，或者order by 需要的。那直接就可以通过索引来完成。不需要到索引对应的行数据获取其他数据了（这个应该就是你文中提到的不需要访问聚簇索引）。这个很快。
2，虽然选择了一个索引，但是被选择的索引不包括select查找的所有字段，或者不包括order by需要用到的字段。那这种情况下mysql是如何处理的呢？比如这个查询最后mysql使用了k1这个索引。那么还有f1,f2,k2,k3这三个字段信息需要到硬盘中获取。关于这个获取过程，我的理解是可能有下面两种情况。
2.1 mysql通过索引定位到符合条件的行数据指针，通过指针查找到行数据，把行数据中所有where中索引外的其他字段、select查找的字段以及order by使用的字段查找出来，这样就会产生一个结果集， 得到这个结果集之后，在通过where中其他过滤条件进行筛选，然后进行order by， 接着进行limit，也就是说， 这个过程中只有一次通过索引到硬盘找对用行数据的动作，后面的order by, select都是在这个结果集上进行的。
2.2 mysql通过已经选择的索引定位到符合条件的行数据指针， 通过指针查到到行数据，把行数据中where中索引外的其他字段查找出来。得到一个结果集，对结果集进行where中索引外其他条件的筛选，最后得到一个符合所有where条件的结果集，然后获取这个结果集中的所有主键信息，然后通过主键到硬盘中查找行数据， 把select，order by需要用到的字段都查找出来。然后进行order by， limit。这样就是有两次到硬盘找行数据的动作。
不知道高手能不能帮我解惑下， 应该是哪一种比较正确？或者两者都是错误的，那神马才是正确的流程？ 4 楼 丁林.tb 2012-05-03 @3楼
再这个例子里面，基本上是2.2的流程，但实际上没有两次读硬盘。因为在读取f1,f2,k2,k3的时候，就已经把行数据给读入到内存了。所以排序后根据主键再取就是在内存里面。
而2.1的描述更像临时表。
参看一下这篇 http://dinglin.iteye.com/blog/1507941