MySQL索引实战

一、准备测试环境

1.1、创建数据表

/* 员工表 */
CREATE TABLE `emp` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `job` varchar(9) NOT NULL DEFAULT '',
  `hiredate` date NOT NULL, /* 入职日期 */
  `sal_grade` int(8) NOT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4500001 DEFAULT CHARSET=utf8;

/* 工资等级表 */
CREATE TABLE `sal_grade` (
  `grade` int(8) unsigned NOT NULL DEFAULT '0',
  `losal` decimal(17,2) NOT NULL, /* 最低工资 */
  `hisal` decimal(17,2) NOT NULL  /* 最高工资 */
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.2、创建生成数据的函数

DELIMITER $$
/* 创建生成随机数的函数 */
CREATE PROCEDURE `rand_num`() RETURNS int(11)
BEGIN
 DECLARE i INT DEFAULT 0;
 SET i = FLOOR(10+RAND()*100);
 RETURN i;
END $$
DELIMITER ;

DELIMITER $$
/* 创建生成随机字符串的函数 */
CREATE PROCEDURE `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8
BEGIN
 DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0; 
 WHILE i < n DO
   SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
   SET i = i + 1;
   END WHILE;
 RETURN return_str;
END $$
DELIMITER ;

/* 向emp表插入数据的函数 */
DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
 DECLARE i INT DEFAULT 0;
 SET autocommit = 0;
 REPEAT
  SET i = i + 1;
  INSERT INTO emp(`name`, `job`, `hiredate`, `sal_grade` ) VALUES (rand_string(6),'销售', DATE_ADD(CURDATE(), interval -rand_num() day), i%5 + 1);
	INSERT INTO emp(`name`, `job`, `hiredate`, `sal_grade` ) VALUES (rand_string(6),'开发', DATE_ADD(CURDATE(), interval -rand_num() day), i%5 + 1);
	INSERT INTO emp(`name`, `job`, `hiredate`, `sal_grade` ) VALUES (rand_string(6),'产品', DATE_ADD(CURDATE(), interval -rand_num() day), i%5 + 1);
  UNTIL i = max_num END REPEAT;
 COMMIT;
END $$
DELIMITER ;

若执行出错，可尝试逐个创建！

1.3、执行过程生成测试数据

/* 向emp表中插入 1500000 * 3 = 4500000 条数据 */
DELIMITER $$
CALL insert_emp(1,1500000)$$
DELIMITER ;

二、索引实战测试

1、联合索引第一个字段就用范围，MySQL一般不使用索引，而是采取全表扫描的方式。
explain select * from emp where name > 'hu' and age = 70 and job = '开发';
为什么possible_keys字段有联合索引，也就是该SQL语句是可以使用该联合索引的，而MySQL却选择全表扫描呢？
个人猜想是MySQL优化器认为联合索引的第一个字段就使用范围，结果集比较大，而且使用的是SELECT *并不符合覆盖索引的条件，所以需要回表，会导致效率不高，转而采用全表扫描。
既然是因为回表效率低，MySQL才选择全表扫描的，那么我们来测试一下不需要全表扫描的情况，也就是使所查询字段符合覆盖索引的条件。
explain select name,age,job from emp where name > "hu" and age = 70 and job = '开发';
不测不知道，一测吓一跳，果然跟我想的一样。虽然联合索引的第一个字段就使用了范围查找，但是当查询的字段符合覆盖索引的条件、不需要回表操作时，MySQL选择了使用联合索引。
注：key_len = 62，而name字段是varchar(20)，根据3n+2的公式可知，该SQL只使用了联合索引的第一个字段name，而age，job字段都没有用到。

2、联合索引第一个字段用右模糊查询，MySQL一般会使用索引。
explain select * from emp where name like "hu%" and age = 70 and job = '开发';

explain select name, age, job from emp where name like "hu%" and age = 70 and job = '开发';
从上面两图可知，联合索引的第一个字段使用like右模糊查询时，无论是否符合覆盖索引的条件，都会使用索引，而且key_len为96，表明联合索引的三个字段都使用到了。
为什么联合索引的第一个字段使用范围查找且不符合覆盖索引时，MySQL采取全表扫描的方式，而当联合索引第一个字段使用like右模糊查询时就使用索引呢？
个人猜想是查询条件name > 'hu’与name like 'hu%'相比，前者的结果集会比较大，需要回表次数多。
为什么联合索引的第一个字段使用范围查找且符合覆盖索引时，仅使用到了联合索引的第一个字段name，而当联合索引第一个字段使用like右模糊查询时就使用联合索引的全部字段name、age、job呢？
还没想通o(╥﹏╥)o

3、联合索引中，当某个字段使用范围条件时，该字段后面的索引字段将无法使用索引。

1. 联合索引的第一个字段使用范围条件：
   select name, age, job from emp where name > "hu" and age = 20 and job = "产品";

key_len的值为62，表明只使用到了联合索引的第一个字段name。
2. 联合索引的第二个字段使用范围条件：
   explain select name, age, job from emp where name = "hu" and age > 20 and job = "产品";

key_len的值为67，表明使用了联合索引的前两个字段name和age。
3. 联合索引的最后一个字段才使用到范围条件：
   explain select name, age, job from emp where name = "hu" and age = 20 and job > "产品";

key_len的值为96，表明使用了联合索引的全部字段name、age和job。

4、未完待续...