文章目录
- 第一节、索引失效案例
- 1.1 数据准备
- 1.2 全值匹配我最爱
- 1.3 最佳左前缀法则
第一节、索引失效案例
可以从以下维度对数据库进行优化:
- 索引失效、没有充分利用到索引–索引建立
- 关联查询太多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+树获取结果,从而无法使用索引。