对于慢sql的分析步骤:
1) desc|explain sql 查看执行计划, 对于type很慢的, 分析是否建立了对应字段的索引
2) 进行排除法, 把子查询抽离出来, 单独执行,定位慢查询是哪个子查询导致的. 专门进行优化.
脚本:
CREATE TABLE stu (
id BIGINT(20) PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
NAME VARCHAR(50) NOT NULL DEFAULT '' COMMENT '名称',
phone BIGINT(20) NULL COMMENT '电话',
UNIQUE KEY idx_phone (phone)
)ENGINE INNODB DEFAULT CHARSET utf8 COMMENT '测试_学生表';
CREATE TABLE course (
id BIGINT(20) PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
NAME VARCHAR(50) NOT NULL DEFAULT '' COMMENT '名称'
)ENGINE INNODB DEFAULT CHARSET utf8 COMMENT '测试_课程表';
CREATE TABLE stu_course (
id BIGINT(20) PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
stu_id BIGINT(20) NOT NULL COMMENT '学生id',
cous_id BIGINT(20) NOT NULL COMMENT '课程id',
score INT(11) NOT NULL DEFAULT 0 COMMENT '分数',
KEY idx_stu_id (stu_id),
KEY idx_cous_id (cous_id)
)ENGINE INNODB DEFAULT CHARSET utf8 COMMENT '测试_学生课程关联表';
1. OR 变 UNION ALL
前:
SELECT id FROM stu WHERE id=10 OR id=20
后:
SELECT id FROM stu WHERE id=10
UNION ALL
SELECT id FROM stu WHERE id=20
2. IN (子查询) 变 INNER JOIN
前:
SELECT id FROM stu WHERE id IN (SELECT stu_id FROM stu_course)
后:
SELECT id FROM stu s
INNER JOIN (SELECT stu_id FROM stu_course) tmp ON s.id = tmp.stu_id
3. NOT IN (子查询) 变 LEFT JOIN
前:
SELECT id FROM stu WHERE id NOT IN (SELECT stu_id FROM stu_course)
后:
SELECT id FROM stu s
LEFT JOIN (SELECT stu_id FROM stu_course) tmp ON s.id = tmp.stu_id
WHERE tmp.stu_id IS NULL
4. IN 变 EXISTS
前:
SELECT id FROM stu WHERE id IN (SELECT stu_id FROM stu_course)
后:
SELECT id FROM stu s WHERE EXISTS (SELECT sc.stu_id FROM stu_course sc WHERE sc.stu_id = s.id)