MySQL之索引：索引字段的选取

        日常在建数据表的时候，通常会在WHERE，GROUP BY，ORDER BY等常用的字段上建立索引，当有多个字段时候，有一项原则就是该字段的去重后的值个数越多，索引建立的必要性越强。

        这里建立一张数据表，有staff_id和leader_id两个字段，通过EXPLAIN分析可以验证哪个字段更适合做索引。

CREATE TABLE `test_index_distinct_value` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id` int(11) DEFAULT NULL,
  `leader_id` int(11) DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入若干条数据：

通过一条SQL先对比下staff_id和leader_id去重后的值个数与总行数的占比：

SELECT COUNT(DISTINCT(staff_id))/COUNT(*) AS staff_rate,
       COUNT(DISTINCT(leader_id))/COUNT(*) AS leader_rate
FROM test_index_distinct_value

查找结果：

可以看到去重后的数值staff_id要高于leader_id的个数。

下面先在staff_id字段上建立索引，用EXPLAIN分析一条普通的查询SQL语句：

EXPLAIN
SELECT *
FROM test_index_distinct_value
WHERE leader_id=2 AND staff_id=22

拆除staff_id字段上的索引，在leader_id字段上建立，继续用EXPLAIN分析分析上条查询SQL语句：

        可以看出同样使用了索引查找执行同样的SQL语句，在leader_id字段上建立的索引需要扫描3行数据，而在staff_id字段建立索引时，仅需扫描1行数据，验证可去重后数值越多索引效率越高。

        当然这个原则仅仅是作为一项参考，具体的还需要跟对应业务相结合。