本文详细探讨了SQL查询优化，包括单表优化、两表和三表连接优化。通过实例分析了如何选择合适的索引策略，如在WHERE条件后添加索引，以及在连接操作中决定驱动表和被驱动表的重要性。还讨论了IN与EXISTS的区别，强调了数据量在选择优化策略中的关键作用。
CREATE TABLE articie
(
id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
author_id INT(10) NOT NULL,
category_id INT(10) NOT NULL,
views INT(10) NOT NULL,
comments INT(10) NOT NULL,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL
);
INSERT INTO articie ( author_id, category_id, views, comments, title, content ) VALUES
( 1, 1, 1, 1, '1', '1' ),
( 2, 2, 2, 2, '2', '2' ),
( 3, 3, 3, 3, '3', '3' );
单表优化
需要被优化的 SQL ：
-- 查询筛选后一条数据
EXPLAIN
SELECT id, author_id
FROM articie
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC
LIMIT 1
可以看出这个 sql 非常的差，首先走了全表扫描，并且进行了文件排序。所以优化是必须的
首先看下这张表的索引：
SHOW INDEX FROM articie
只有一个主键索引，那么就需要开始增加索引了。
优化1
一般优化都是在 where 后面在常用的列上增加索引，并且与索引该怎么加。上面需求的在指定的条件筛选后，并且取一条。那这里就增加一个聚合索引，顺序为(注意顺序，聚合索引顺序在查时候很重要)：
category_id、comments 、views
CREATE INDEX idx_articie_ccv ON articie( category_id, comments, views );
再查发现类型级别为 range，并且使用了索引 idx_articie_ccv，但是还是出现了 Using filesort 外部排序，依旧不够理想
还是出现 Using filesort 原因是，首先聚合索引在查询时候的顺序没有问题，问题出现在了 comments > 1 条件中，在使用范围区间时候后面的都不会再走索引了，所以只有 category_id = 1 AND comments > 1 这2个列走了索引 。排序时候没有走索引，出现了文件排序。
优化2
这里考虑到使用了范围筛选，后面的都会失效，这里干脆索引顺序组合就使用 category_id, views
-- 删除索引
DROP INDEX idx_articie_ccv ON articie;
-- 创建新索引
CREATE INDEX idx_articie_ccv ON articie( category_id, views );
再次查询发现级别为 ref，理想状态。使用了索引并且没有出现 filesort
两表优化
测试sql
CREATE TABLE IF NOT EXISTS teacher
(
t_id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(50) NOT NULL,
school_id INT -- 学校id
);
-- 书籍
CREATE TABLE IF NOT EXISTS student
(
s_id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
s_t_id INT(10) NOT NULL,
school_id INT -- 学校id
);
INSERT INTO teacher( t_name , school_id ) VALUES ( 'haha1', 1 );
INSERT INTO teacher( t_name , school_id ) VALUES ( 'haha2', 1 );
INSERT INTO teacher( t_name , school_id ) VALUES ( 'haha3', 1 );
INSERT INTO teacher( t_name , school_id ) VALUES ( 'haha4', 1 );
INSERT INTO teacher( t_name , school_id ) VALUES ( 'haha5', 1 );
INSERT INTO teacher( t_name , school_id ) VALUES ( 'haha6', 1 );
INSERT INTO teacher( t_name , school_id ) VALUES ( 'haha7', 1 );
INSERT INTO teacher( t_name , school_id ) VALUES ( 'haha8', 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 1 , 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 2 , 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 3 , 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 4 , 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 5 , 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 6 , 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 7 , 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 8 , 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 9 , 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 10, 2 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 11, 2 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 12, 2 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 13, 2 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 1, 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 2, 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 3, 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 4, 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 5, 1 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 14, 2 );
INSERT INTO student ( s_t_id , school_id ) VALUES ( 15, 2 );
查询：
-- 一个学校的学生老师，不用管查询的SQL逻辑性，主要看执行计划与怎么优化
EXPLAIN SELECT * FROM teacher a INNER JOIN student b ON ( a.school_id = b.school_id )
发现 sql 挺不理想的，走了全表扫描
在左连接时候，该在左边表加索引还是右边表加还是都加索引，既然不确定那就都试试
试验1 - 右边加
CREATE INDEX idx_student_school_id ON student( school_id );
从执行计划发现， student 走了索引，并且使用索引的是 teacher 表的 school_id 列。而且级别是 ref 级别
试验2 - 左边加
-- 删除上个索引
DROP INDEX idx_student_school_id ON student;
-- 左边加索引
CREATE INDEX idx_teacher_school_id ON teacher( school_id );
发现 2 张表都没有走索引都是全表扫描
试验3 - 两边都加
-- 创新创建 2 个索引
DROP INDEX idx_teacher_school_id ON teacher;
DROP INDEX idx_student_school_id ON student;
CREATE INDEX idx_teacher_school_id ON teacher( school_id );
CREATE INDEX idx_student_school_id ON student( school_id );
发现执行计划与 右边加是一样的
总结
右链接省略… 从实验可以发现
左连接在右边加
右连接在左边加
例如左连接，左连接在右边加，这是因为左连接的特性决定的。left join 左边一定有，条件主要用于如果从右边搜索行。
三表优化
-- 删除上面模拟的索引
DROP INDEX idx_teacher_school_id ON teacher;
DROP INDEX idx_student_school_id ON student;
CREATE INDEX idx_student_school_id ON student( school_id );
CREATE INDEX idx_desk_school_id ON desk( school_id );
-- 查询
EXPLAIN
SELECT *
FROM teacher a INNER JOIN student b ON ( a.school_id = b.school_id )
INNER JOIN desk c ON ( b.school_id = c.school_id )
在没有索引状态下，为全表扫描
优化：在后面2个表添加索引
CREATE INDEX idx_student_school_id ON student( school_id );
CREATE INDEX idx_desk_school_id ON desk( school_id );
可以发现使用了索引，级别都还是 ref
IN 与 EXISTS
驱动表 与 被驱动表
在了解 In 与 Exists 时候需要明白 驱动表 与 被驱动表的含义。在使用 join 连接时候，需要2个结果集做联合，其中一个结果集作为基础数据向另一个集合去查找匹配数据。例如以下：
for ( 5条 ){
for(10条){
}
}
外层的 for 作为驱动表，作为基础结果集，然后向内循环匹配。
在sql 优化时候，有个永远以小表驱动大表，因为这里面有一个连接次数的概念。那么在 inner、left、right 时候，哪些是驱动表哪个是被启动表？想知道哪个是驱动表那就贼简单，看下执行计划就知道（id 列执行顺序）。例如以下：
可以看出 teacher 就是驱动表。
左连接
左表是驱动表，右表是被驱动表（不管是否存在索引）
右链接
右表时驱动表，左表是驱动表（不管是否存在索引）
内连接
当一张表A加上索引，另一张表B上没有索引，那么存在索引的表会作为被驱动表。
很容易理解，因为每次都要从驱动表中取出一条记录，知道遍历完整张表，就是全表查询。为了发挥索引的作用，所以将有索引表作为被驱动表时，可以根据驱动表取出的记录，走索引进行查询
当两张表都加上索引的情况，也只会有被驱动表会走索引。所以mysql会选择数据量比较小的表作为被驱动表，减少查询次数
示例测试
A表140多条数据（小表），B 表 20w 数据（大表）
select * from A a left join B b on ( a.code = b.code )
执行时间 7s
select * from B b left join A a on ( a.code = b.code )
执行时间 19s
for ( 140条 ){
for(20w){
}
}
大表驱动小标要通过20w次连接
小表驱动大表，只需要通过 140 次就可以
如果数据量差不多，选择谁都无所谓。
总结
- 以小表驱动大表
- 给被驱动表建立索引
IN 与 Exists
网上说 exists 比 in 好，其实这样说不对。in 与 exists 的区别就在与驱动与被驱动方，以小表驱动大表的原则去决定使用 in 还是 exists。
IN
EXPLAIN
SELECT * FROM teacher a WHERE a.school_id IN ( SELECT school_id FROM student )
等价于
for( SELECT school_id FROM student ){
for ( SELECT * FROM teacher a WHERE a.school_id = student.school_id ){
....
}
}
以上是以 SELECT school_id FROM student 为驱动表，然后再执行括号外部。所以子查询内部先执行作为驱动表。
exists
EXPLAIN
SELECT * FROM teacher a
WHERE EXISTS ( SELECT 1 FROM student b WHERE a.school_id = b.school_id )
等价于
for (SELECT * FROM teacher a) {
for (SELECT 1 FROM student b WHERE a.school_id = b.school_id) {
.....
}
}
以上是以 SELECT * FROM teacher a 作为驱动，先执行外部的，所以外部作为驱动表。
总结
select * from A where in/exists ( select x from B )
IN：适合 B 表数据比 A 表少
EXISTS：适合 A 表数据比 B 表少
所以需要明白数据量，然后决定使用哪个
总结
此篇主要讲解在 单表时候，建立复合索引时候需要注意索引顺序不能断，如果断了后面的就不走索引了。在双表 与 多表时候，建立索引需要根据连接条件，在被驱动方建立索引。
在 in 与 exists 选择时候，需要明白你的 数据集 中哪个数据比较少，如果括号内部比较少，使用in。如果括号内部比较多，使用 exists
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
