继上一篇MySQL索引的优化的文章之后，下面天涯PHP博客再以实例的方式对索引的使用进行相关说明。本文分别以单列索引和组合索引进行举例说明。
下面分别创建三张表，并分别插入1W条简单的数据用来测试，详情如下：
[1] test_a 有主键但无索引
CREATE TABLE `test_a` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`content` text NOT NULL,
`number` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;[2] test_b 有主键和单列索引
CREATE TABLE `test_b` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`content` text NOT NULL,
`number` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `titleIndex` (`title`) USING BTREE,
UNIQUE KEY `numberIndex` (`number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;[3] test_c 有主键和组合索引
CREATE TABLE `test_c` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`content` text NOT NULL,
`number` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `titleNumberIndex` (`title`,`number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;下面按照单列索引和组合索引分别对以上三张表进行查询测试，另外下面的时间都是多次测试取的平均值。
[1] 单列索引
[1.1] 查询指定的字段
[SQL] SELECT * FROM test_a WHERE title = 'title_5000';
[USE] 12ms
[SQL] SELECT * FROM test_b WHERE title = 'title_5000';
[USE] <1ms说明：可以看到未加索引时间为12ms，加索引后小于1ms，还是相差很大的。
[SQL] SELECT * FROM test_a WHERE content = 'content_5000';
[USE] 13ms
[SQL] SELECT * FROM test_b WHERE content = 'content_5000';
[USE] 13ms说明：由于content字段都未加索引，因此时间基本一致。
[1.2] 测试LIKE查询
[SQL] SELECT * FROM test_a WHERE title LIKE '%5000';
[USE] 13ms
[SQL] SELECT * FROM test_b WHERE title LIKE '%5000';
[USE] 13ms说明：两者用时基本一致，因此索引并未命中。
[SQL] SELECT * FROM test_a WHERE title LIKE '5000%';
[USE] 12ms
[SQL] SELECT * FROM test_b WHERE title LIKE '5000%';
[USE] <1ms说明：如果LIKE是前缀匹配则会命中索引，否则不会命中。另外以上的查询结果为空，但是不影响说明索引的作用。
[1.3] 测试OR语句
[SQL] SELECT * FROM test_a WHERE title = 'title_5000' OR content = 'content_5000';
[USE] 13ms
[SQL] SELECT * FROM test_b WHERE title = 'title_5000' OR content = 'content_5000';
[USE] 13ms说明：两者用时基本一致，因此索引并未命中。
[SQL] SELECT * FROM test_a WHERE title = 'title_5000' OR number = '5000';
[USE] 13ms
[SQL] SELECT * FROM test_b WHERE title = 'title_5000' OR number = '5000';
[USE] <1ms说明：如果OR两边的字段都加索引则命中，否则只有一个加索引则不命中。
[1.4] 测试IN语句
[SQL] SELECT * FROM test_a WHERE title IN('title_4999','title_5000','title_5001');
[USE] 12ms
[SQL] SELECT * FROM test_b WHERE title IN('title_4999','title_5000','title_5001');
[USE] <1ms说明：IN语句也是可以命中索引的。
[1.5] 测试(BETWEEN,>,>=,<,<=)语句
[SQL] SELECT * FROM test_a WHERE number <= 5000;
[USE] 14ms
[SQL] SELECT * FROM test_b WHERE number <= 5000;
[USE] 14ms
[SQL] SELECT * FROM test_a WHERE number < 5000;
[USE] 14ms
[SQL] SELECT * FROM test_b WHERE number < 5000;
[USE] 14ms
[SQL] SELECT * FROM test_a WHERE number > 5000;
[USE] 14ms
[SQL] SELECT * FROM test_b WHERE number > 5000;
[USE] 14ms
[SQL] SELECT * FROM test_a WHERE number >= 5000;
[USE] 14ms
[SQL] SELECT * FROM test_b WHERE number >= 5000;
[USE] 14ms
[SQL] SELECT * FROM test_b FORCE INDEX(numberIndex) WHERE number >= 5000;
[USE] 14ms
[SQL] SELECT * FROM test_a WHERE number BETWEEN 4999 AND 5001;
[USE] 11ms
[SQL] SELECT * FROM test_b WHERE number BETWEEN 4999 AND 5001;
[USE] <1ms说明：BETWEEN可以命中索引，其他比较符号未命中索引，强制使用索引效果也不明显，这个应该是与MySQL的索引足够性判断，如果索引大于30%就会使用全表扫描，具体待我查证后再详细介绍。
[1.6] 字段使用函数
[SQL] SELECT * FROM test_a WHERE SUBSTR(title,7,4) = '5000';
[USE] 13ms
[SQL] SELECT * FROM test_b WHERE SUBSTR(title,7,4) = '5000';
[USE] 13ms说明：对字段使用函数则不能命中索引。
[2] 组合索引
[2.1] 测试OR语句
[SQL] SELECT * FROM test_a WHERE title = 'title_5000' OR content = 'content_5000';
[USE] 13ms
[SQL] SELECT * FROM test_c WHERE title = 'title_5000' OR content = 'content_5000';
[USE] 13ms说明：组合索引中的OR语句没有命中索引。
[2.2] 测试AND语句
[SQL] SELECT * FROM test_a WHERE title = 'title_5000' AND number = 5000;
[USE] 12ms
[SQL] SELECT * FROM test_c WHERE title = 'title_5000' AND number = 5000;
[USE] <1ms
[SQL] SELECT * FROM test_a WHERE number = 5000;
[USE] 12ms
[SQL] SELECT * FROM test_c WHERE number = 5000;
[USE] 12ms
[SQL] SELECT * FROM test_a WHERE title = 'title_5000';
[USE] 12ms
[SQL] SELECT * FROM test_c WHERE title = 'title_5000';
[USE] <1ms说明：以上证明了最左前缀匹配原则。
[3] 补充说明
以上的时间都是多次测试的平均值，另外是否使用索引的依据不能单纯以时间来做比对，需要用到EXPLAIN解释命令，具体用法我会在下一篇文章中介绍。