Mysql性能优化(五) 索引优化实战多表索引优化

最新推荐文章于 2025-06-23 15:51:06 发布

转载最新推荐文章于 2025-06-23 15:51:06 发布 · 置顶 · 503 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://www.cnblogs.com/cyhbyw/p/8819525.html

Mysql 专栏收录该内容

8 篇文章

订阅专栏

本文探讨了基于主键和非主键的SQL关联查询差异，分析了全表扫描的原因，并通过创建索引优化非主键关联查询，提升数据库性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建员工和部门表：

DROP TABLE IF EXISTS employee;
CREATE TABLE IF NOT EXISTS employee (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(50),
    dept_id INT
);
DROP TABLE if EXISTS department;
create table if NOT EXISTS department (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(50)
);

插入数据：

INSERT INTO employee(name, dept_id) values ('Alice', 1);
INSERT INTO employee(name, dept_id) values ('BOb', 2);
INSERT INTO employee(name, dept_id) values ('David', 3);
INSERT INTO department(name) VALUES('HR');
INSERT INTO department(name) VALUES('RD');
INSERT INTO department(name) VALUES('Sale');

基于主键的关联查询

查询：

SELECT e.* FROM employee e left JOIN department d on e.dept_id = d.id;

Explain分析

分析结果：
employee 表的 type=all 是全表扫描，这是没法避免的，因为查询的（想要的）就是 employee 的所有结果；
department 表的 type=eq_ref 且 Using index，很理想；之所以这样是因为连接条件是 e.dept_id = d.id，d.id 本来就是 department 表的主键；

如果是连表查询且右边表使用的是主键，则可以直接使用到索引，无须优化。

非主键的关联查询

创建表格书籍、订单、顾客

DROP TABLE IF EXISTS customer;
create TABLE IF NOT EXISTS customer(
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(50)
);

DROP TABLE IF EXISTS book;
create TABLE IF NOT EXISTS book(
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(50)
);

DROP TABLE IF EXISTS `order`;
create TABLE IF NOT EXISTS `order`(
    id INT PRIMARY KEY auto_increment,
    customer_id INT,
    book_id INT
);

插入数据

INSERT INTO customer(name) VALUES('Alice');
INSERT INTO customer(name) VALUES('Bob');
INSERT INTO customer(name) VALUES('David');

INSERT INTO book(name) VALUES('Java编程思想');
INSERT INTO book(name) VALUES('Spring实战');
INSERT INTO book(name) VALUES('Mysql技术内幕');

INSERT INTO `order`(customer_id, book_id) VALUES(1, 1);
INSERT INTO `order`(customer_id, book_id) VALUES(1, 2);
INSERT INTO `order`(customer_id, book_id) VALUES(1, 3);
INSERT INTO `order`(customer_id, book_id) VALUES(2, 1);
INSERT INTO `order`(customer_id, book_id) VALUES(2, 2);
INSERT INTO `order`(customer_id, book_id) VALUES(3, 3);

查询顾客的订单

select c.id, c.name, o.id as order_id FROM customer c LEFT JOIN `order` o ON c.id = o.customer_id

Explain分析

两个全是全表扫描，而且还用了连接缓存

优化

先想一想，为什么 ‘基于主键的关联查询’ 能够使用上索引，而 ‘非主键的关联查询’ 就没有使用上呢？很简单，因为主键本身就有索引！所以，也就很容易想到优化点了，即，给右表的连接字段加上索引（这不就是仿照主键的做法嘛）。

CREATE INDEX idx_order_id ON `order`(customer_id);

Explain分析

EXPLAIN select c.id, c.name, o.id as order_id FROM customer c LEFT JOIN `order` o ON c.id = o.customer_id