MySQL索引优化实例说明
下面分别创建三张表，并分别插入1W条简单的数据用来测试，详情如下：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=0 DEFAULT CHARSET=utf8;
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=0 DEFAULT CHARSET=utf8;
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=0 DEFAULT CHARSET=utf8;
下面按照单列索引和组合索引分别对以上三张表进行查询测试，另外下面的时间都是多次测试取的平均值。
单列索引
查询指定的字段
SELECT * FROM test_a WHERE> 12ms
SELECT * FROM test_b WHERE> 13ms
SELECT * FROM test_b WHERE> 13ms
说明：两者用时基本一致，因此索引并未命中。
SELECT * FROM test_a WHERE> 12ms
SELECT * FROM test_b WHERE> 13ms
SELECT * FROM test_b WHERE> 13ms
说明：两者用时基本一致，因此索引并未命中。
SELECT * FROM test_a WHERE> 13ms
SELECT * FROM test_b WHERE> 12ms
SELECT * FROM test_b WHERE> ,>=,= 5000;
14ms
SELECT * FROM test_b FORCE INDEX(numberIndex) WHERE number >= 5000;
14ms
SELECT * FROM test_a WHERE number BETWEEN 4999 AND 5001;
11ms
SELECT * FROM test_b WHERE number BETWEEN 4999 AND 5001;
13ms
SELECT * FROM test_c WHERE> 13ms
说明：组合索引中的OR语句没有命中索引。
测试AND语句
SELECT * FROM test_a WHERE> 12ms
SELECT * FROM test_c WHERE> 12ms
SELECT * FROM test_c WHERE> 或
页:
[1]