MySQL进阶_查询优化和索引优化

第一节、索引失效案例

可以从以下维度对数据库进行优化：

• 索引失效、没有充分利用到索引–索引建立
• 关联查询太多JOIN (设计缺陷或不得已的需求)–SQL优化
• 服务器调优及各个参数设置(缓冲、线程数等）–调整my.cnf
• 数据过多–分库分表

虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。

• 物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
• 逻辑查询优化就是通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。

其实，用不用索引，最终都是优化器说了算。优化器是基于cost开销（通过JSON格式可以看到开销数据）的，它不是基于规则，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

EXPLAIN可以输出四种格式： 传统格式 ， JSON格式 ， TREE格式 以及可视化输出 。用户可以根据需要选择适用于自己的格式。

1.1 数据准备

创建两个表，通过函数和存储过程填充数据。

CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

1.2 全值匹配我最爱

// 查询语句
SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classid=4 AND NAME= 'abcd';// 分别建立不同的索引
CREATE INDEX idx_age ON student (age) ; // 索引1
CREATE INDEX idx_age_classid oN student (age,classid); // 索引2
CREATE INDEX idx_age_classid_name ON student (age,classid,NAME); // 索引3

当数据量非常大时，发现使用索引3的执行时间最短。也就是说，尽量将WHERE后的字段都建立索引（如果有多个，建立联合索引）。

1.3 最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name = 'abcd';

针对于上面的查询语句，虽然已经创建了索引idx_age_classid_name，但是WHERE后没有使用age，所以无法使用此索引。这一点可以从索引数据结构的角度来解释，创建索引idx_age_classid_name时，B+树首先根据age来排序，如果age相同，再根据classid，如果classid相同，再根据name。而WHERE后没有使用age，所以无法从此B+树获取结果，从而无法使用索引。