mysql联合主键顺序_探索MySQL索引（单表百万）优劣对⽐
主键索引，联合索引（顺序）...
现在我们有⼀张简易的⽤户表,⽬前有测试数据119万条。今天闲来⽆事，所以想测试⼀下WHERE条件中⼀个字段或者多个字段(排序)，之间的多个主键索引联合索引的⽐较。⼀切以你⾃⼰的环境和数据量为参考。以下情况仅为dodo的个⼈电脑环境下⾯的测试。欢迎拍砖和指引。另外此处的为MyISAM的引擎。
//查看⼀下默认的表结构，主键⾃增，主键索引，没有其他索引
githubSHOW 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
send是什么意思中文//查看⼀下记录数⽬--共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;
mysql语句顺序//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:
//受影响的⾏: 0piper派酱的真人照片
//时间: 0.899s
//结论有点神奇：排除数据层⾯的影响，没有建索引和建了主键索引的情况下，差距不⼤。有主键索引的略占优，优势不⼤。
//然⽽建了普通索引的，查询⽤到了所以，查询记录也少了，但是查询时间反⽽翻倍了。⽐较疑惑。数控加工中心编程自学
接下来我们来考量测试联合索引的⽅式的性能。我们以type和sex作为两个必须的查询条件。
//⽐较建索引字段前后是否有影响到结果 KEY `idx_type_cate` (`type`,`sex`)
面试官问redis一般用来做什么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字段加索引测试。
不要在⼀件别扭的事上纠缠太久。纠缠久了，你会烦，会痛，会厌，会累，会神伤，会⼼碎。实际上，到最后，你不是跟事过不去，⽽是跟⾃⼰过不去。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。