现在我们有一张简易的用户表,目前有测试数据119万条。今天闲来无事,所以想测试一下WHERE条件中一个字段或者多个字段(排序)，之间的多个主键索引联合索引的比较。一切以你自己的环境和数据量为参考。以下情况仅为dodo的个人电脑环境下面的测试。欢迎拍砖和指引。另外此处的为MyISAM的引擎。

//查看一下默认的表结构,主键自增,主键索引,没有其他索引
SHOW CREATE TABLE user_list;

CREATE TABLE `user_list` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(20) NOT NULL COMMENT '昵称',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `type` tinyint(3) unsigned NOT NULL COMMENT '用户类型1-6',
  `sex` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '用户性别 0未知 1男 2女',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1199797 DEFAULT CHARSET=utf8mb4

//查看一下记录数目--共119万+
SELECT count(*) FROM user_list;
1199796
//时间: 0.001s

基本情况介绍结束了,首先我们来测试对比一下,WHERE字段中只有一个查询字段的情况下.这个时候我们可以用主键索引或普通索引.

//默认情况下,不在type字段上建索引的情况下
SELECT SQL_NO_CACHE FROM user_list WHERE type=3;
//EXPLAIN索引 type:ALL rows:1199796 Extra:Using where
//受影响的行: 0
//时间: 0.413s


//主键索引的情况下 PRIMARY KEY (`id`,`type`)
SELECT SQL_NO_CACHE * FROM user_list WHERE type=3;
//EXPLAIN索引 type:ALL rows:1199796 Extra:Using where
//受影响的行: 0
//时间: 0.406s



//普通索引的情况下 PRIMARY KEY (`id`), KEY `idx_type` (`type`)
SELECT SQL_NO_CACHE * FROM user_list WHERE type=3;
//EXPLAIN索引 type:ref rows:135070 Extra:
//受影响的行: 0
//时间: 0.899s

//结论有点神奇:排除数据层面的影响，没有建索引和建了主键索引的情况下，差距不大。有主键索引的略占优，优势不大。
//然而建了普通索引的，查询用到了所以，查询记录也少了，但是查询时间反而翻倍了。比较疑惑。

接下来我们来考量测试联合索引的方式的性能。我们以type和sex作为两个必须的查询条件。

//比较建索引字段前后是否有影响到结果 KEY `idx_type_cate` (`type`,`sex`)
SELECT SQL_NO_CACHE * FROM user_list WHERE type=3 AND sex=2;
SELECT SQL_NO_CACHE * FROM user_list WHERE sex=2 AND type=3;
//EXPLAIN索引 type:ref rows:60937 Extra:
//受影响的行: 0
//时间: 0.316s

//结论：两者之间的在查询语句WHERE的条件上面前后顺序不一样,不影响索引的使用和查询扫描的行数，以及查询响应的结果。
//建联合索引的时候建议把离散度大的放在前面，这样可以减少扫描的行数。


//分开建索引,非联合索引 KEY `idx_type` (`type`),KEY `idx_sex` (`sex`)
SELECT SQL_NO_CACHE * FROM user_list WHERE type=3 AND sex=2;
//EXPLAIN索引 type:ref rows:60937 Extra:Using where  possible_keys:idx_type,idx_sex
//受影响的行: 0
//时间: 0.776s

SELECT SQL_NO_CACHE * FROM user_list WHERE sex=2 AND type=3;
//EXPLAIN索引 type:ref rows:60937 Extra:Using where  possible_keys:idx_type,idx_sex
//受影响的行: 0
//时间: 0.752s


结论:当要查询两个字段的时候请一定要建联合索引，且根据离散度不同，离散度大的排在前面。

PS:刚刚咨询了一下同事，猜测是type字段才6个值，离散度太低，造成的测试不太精确。明天我会给phone字段加索引测试。

友情链接：探索MySQL索引(单表百万)优劣对比主键索引VS普通索引

不要在一件别扭的事上纠缠太久。纠缠久了，你会烦，会痛，会厌，会累，会神伤，会心碎。实际上，到最后，你不是跟事过不去，而是跟自己过不去。