covering index：An index that contains (or “covers”) all the data needed to satisfy a query is called a covering index.

效果：覆盖索引在表结构很庞大的时候（特别是包含了很多text、varchar等），Covering indexes can be a very powerful tool and can dramatically improve performance.

** 具体原因如下：** 一、Index entries are usually much smaller than the full row size, so MySQL can access significantly less data if it reads only the index. This is very important for cached workloads, where much of the response time comes from copying the data. It is also helpful for I/O-bound workloads, because the indexes are smaller than the data and fit in memory better. (This is especially true for MyISAM, which can pack indexes to make them even smaller.) 二、Indexes are sorted by their index values (at least within the page), so I/O-bound range accesses will need to do less I/O compared to fetching each row from a random disk location. For some storage engines, such as MyISAM, you can even OPTIMIZE the table to get fully sorted indexes, which will let simple range queries use completely sequential index accesses. 三、Most storage engines cache indexes better than data. (Falcon is a notable excep- tion.) Some storage engines, such as MyISAM, cache only the index in MySQL’s memory. Because the operating system caches the data for MyISAM, accessing it typically requires a system call. This may cause a huge performance impact, especially for cached workloads where the system call is the most expensive part of data access. 四、Covering indexes are especially helpful for InnoDB tables, because of InnoDB’s clustered indexes. InnoDB’s secondary indexes hold the row’s primary key val- ues at their leaf nodes. Thus, a secondary index that covers a query avoids another index lookup in the primary key.

详细测试如下： 表结构 CREATE TABLE test ( id int(10) unsigned NOT NULL, gid int(10) unsigned NOT NULL default ‘0’, gname varchar(100) NOT NULL default ‘’, gimage varchar(255) NOT NULL default ‘’, gprice int(8) unsigned NOT NULL default ‘0’, sender_uid bigint(20) NOT NULL default ‘0’, sender_name varchar(255) NOT NULL default ‘’, receiver_uid bigint(20) NOT NULL default ‘0’, receiver_name varchar(255) NOT NULL default ‘’, method tinyint(2) unsigned NOT NULL default ‘0’, message text NOT NULL, addtime bigint(13) unsigned NOT NULL default ‘0’, status int(11) NOT NULL default ‘0’, flash_data text, kind tinyint(4) NOT NULL default ‘0’, payway tinyint(4) NOT NULL default ‘0’, is_wap tinyint(1) NOT NULL default ‘0’, flashname varchar(100) NOT NULL default ‘’, hang int(11) NOT NULL default ‘1’, replied int(10) unsigned NOT NULL default ‘0’, record_source int(10) unsigned NOT NULL default ‘0’, PRIMARY KEY (id), ) ENGINE=InnoDB

mysql> select count() from test; +———-+ ‘ count() ‘ +———-+ ‘ 2424284 ‘ +———-+ 1 row in set (0.00 sec)

mysql> select sender_uid from test limit 500000,1; +————+ ‘ sender_uid ‘ +————+ ‘ 225623103 ‘ +————+ 1 row in set (0.37 sec)

mysql> select sender_uid from test limit 700000,1; +————+ ‘ sender_uid ‘ +————+ ‘ 229785203 ‘ +————+ 1 row in set (0.44 sec)

没有加索引 [root]# /etc/init.d/mysql restart Shutting down MySQL.. [ OK ] Starting MySQL. [root]# date;mysql test -e”SELECT sender_uid,receiver_uid FROM test WHERE sender_uid>=225623103 AND sender_uid<=229785203;”>/dev/null;date; Tue Mar 8 11:39:22 CST 2011 Tue Mar 8 11:39:30 CST 2011 [root]# /etc/init.d/mysql restart Shutting down MySQL.. [ OK ] Starting MySQL. [root]# date;mysql test -e”SELECT sender_uid,receiver_uid FROM test WHERE sender_uid>=225623103 AND sender_uid<=229785203;”>/dev/null;date; Tue Mar 8 11:39:42 CST 2011 Tue Mar 8 11:39:50 CST 2011

加了sender_uid,receiver_uid联合索引之后 [root]# /etc/init.d/mysql restart Shutting down MySQL… [ OK ] Starting MySQL. [ OK ] [root]# date;mysql test -e”SELECT sender_uid,receiver_uid FROM test WHERE sender_uid>=225623103 AND sender_uid<=229785203;”>/dev/null;date; Tue Mar 8 11:56:16 CST 2011 Tue Mar 8 11:56:17 CST 2011 [root]# /etc/init.d/mysql restart Shutting down MySQL. [ OK ] Starting MySQL. [ OK ] [root]# [root]# date;mysql test -e”SELECT sender_uid,receiver_uid FROM test WHERE sender_uid>=225623103 AND sender_uid<=229785203;”>/dev/null;date; Tue Mar 8 11:56:24 CST 2011 Tue Mar 8 11:56:25 CST 2011