继上一篇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解释命令,具体用法我会在下一篇文章中介绍。

标签:MySQL 索引