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部分进行检索了。
两张表联查优化