MySQL 高级(3): 索引优化（关联，排序，分组，案例）

准备

• 【优化总结口诀】
  全值匹配要记牢，最左前缀不能忘；
  带头大哥不能死，中间兄弟不能断；
  索引列上少计算，范围之后全失效；
  Like百分写最右，覆盖索引不写星；
  不等空值还有or，索引失效要少用；
  引号不可丢，丢了就失效。

• 在讲解之前，先创建两个表：

  CREATE TABLE IF NOT EXISTS `class` (
  	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
  	`card` INT(10) UNSIGNED NOT NULL, 
  	PRIMARY KEY (`id`)
  );
  CREATE TABLE IF NOT EXISTS `book` (
  	`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  	`card` INT(10) UNSIGNED NOT NULL, 
  	PRIMARY KEY (`bookid`)
  );
  

• 插入数据：

  INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  INSERT INTO class(card) VALUES(FLOOR(1 + (RAND