Mysql查询语句优化之索引优化案例

 sql优化说到底还是索引的优化,创建索引并使之生效.

   单表优化

CREATE TABLE `student` (
  `id` INT (11) NOT NULL AUTO_INCREMENT,
  `stuName` VARCHAR (60) DEFAULT NULL,
  `age` INT (11) DEFAULT NULL,
  `sex` VARCHAR (30) DEFAULT NULL,
  `gradeName` VARCHAR (60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 14 DEFAULT CHARSET = utf8 ;

INSERT  INTO `student`(`id`,`stuName`,`age`,`sex`,`gradeName`) VALUES (1,'张三',23,'男','一年级'),(2,'王峰',25,'男','二年级'),(3,'王峰',23,'男','一年级'),(4,'王峰',22,'男','三年级'),(5,'王峰',21,'女','一年级'),(6,'王峰',26,'女','二年级'),(7,'王峰',19,'男','三年级'),(8,'王峰',21,'女','二年级'),(9,'王峰',22,'男','一年级'),(10,'王峰',25,'男','二年级'),(11,'张三',21,NULL,'二年级'),(12,'王峰',23,'男','二年级'),(13,'王峰',24,NULL,'二年级');

查询sql

SELECT * FROM  student WHERE sex='男' AND age>22 ORDER BY gradeName

使用Explain分析如下

  根据昨天的学习,很明显发现是全表扫描而且出现了文件内排序，必须优化.

此sql的条件有sex,age用gradeName来排序，因此我决定先给这三个字段建索引，组合索引

再建立了组合索引index1之后，type的类型变为了ref，可是依然出现了文件排序，

另一种尝试,删除index1索引，给sex和gradName建立组合索引index2,结果如下

很明显这次没有出现文件排序,并且type的级别是ref。

   总结：第一次建立索引为什么会出现文件排序？

因为按照BTree索引的工作原理，先排序sex,如果遇到相同的sex则在排序age,如果遇到相同的age，再排序gradName,而我第一次建的索引，在组合索引的中间，age>22又是一个范围值，Mysql无法利用索引再对后面的gradName部分进行检索了。

两张表联查优化