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优化无效，可自行分析。
转载地址： http://rdc.taobao.com/blog/cs/?p=406
------------------------------------------------------------------------------------------------
附：什么叫覆盖索引？
覆盖索引是在选择条件和 WHERE 谓词上均满足 SQL 查询的所有列的基础上建立的非聚集索引。覆盖索引可以节省大量的 I/O，因此可极大地改善查询的性能。但是有必要在新建索引（以及与它相关的 B 树索引结构维护，如：数据量增多，更新速度较慢）所需要的代价和覆盖索引所带来的 I/O 性能增益之间进行权衡。如果覆盖索引对于 MySQL上经常运行的查询极其有利，那么创建覆盖索引是值得的。
覆盖索引的示例
Select col1,col3 from table1 where col2 = 'value'.
Create index index_name1 on table1(col2,col1,col3).
index_name1 就是一个覆盖性索引。
覆盖索引(covering index)，MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后再去查询数据，所以那是相当的快！！但是同时也要求所查询的字 段必须被索引所覆盖到，在Explain的时候，输出的Extra信息中如果有“Using Index”，就表示这条查询使用了覆盖索引。
