— 查看表的索引信息
SHOW INDEX FROM fgedu_students;

— 开启慢查询日志
SET GLOBAL slow_query_log = ‘ON’;
SET GLOBAL slow_query_log_file = ‘/mysql/data/fgedu-slow.log’;
SET GLOBAL long_query_time = 1;

— 优化前：使用SELECT *
SELECT * FROM fgedu_students WHERE age > 18;

— 优化后：只选择需要的列
SELECT id, name, age FROM fgedu_students WHERE age > 18;

— 优化前：使用LIKE ‘%…%’
SELECT * FROM fgedu_students WHERE name LIKE ‘%张%’;

— 优化后：使用全文索引或其他方式
SELECT * FROM fgedu_students WHERE name LIKE ‘张%’;

— 优化前：使用子查询
SELECT * FROM fgedu_students WHERE id IN (SELECT student_id FROM fgedu_courses WHERE course_name = ‘数学’);

— 优化后：使用连接查询
SELECT s.* FROM fgedu_students s JOIN fgedu_courses c ON s.id = c.student_id WHERE c.course_name = ‘数学’;

— 优化前：使用笛卡尔积
SELECT * FROM fgedu_students, fgedu_courses WHERE fgedu_students.id = fgedu_courses.student_id;

— 优化后：使用显式连接
SELECT s.*, c.* FROM fgedu_students s INNER JOIN fgedu_courses c ON s.id = c.student_id;

— 优化连接顺序：小表驱动大表
SELECT * FROM fgedu_courses c JOIN fgedu_students s ON c.student_id = s.id;

— 创建测试表
CREATE TABLE fgedu_employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
department VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL,
hire_date DATETIME NOT NULL
);

— 插入测试数据
INSERT INTO fgedu_employees (name, department, salary, hire_date)
VALUES
(‘张三’, ‘技术部’, 8000.00, ‘2023-01-01’),
(‘李四’, ‘市场部’, 6000.00, ‘2023-02-01’),
(‘王五’, ‘技术部’, 9000.00, ‘2023-03-01’),
(‘赵六’, ‘财务部’, 7000.00, ‘2023-04-01’),
(‘钱七’, ‘技术部’, 8500.00, ‘2023-05-01’);

— 优化前：全表扫描
EXPLAIN SELECT * FROM fgedu_employees WHERE department = ‘技术部’;

— 创建索引
CREATE INDEX idx_department ON fgedu_employees(department);

— 优化后：索引扫描
EXPLAIN SELECT * FROM fgedu_employees WHERE department = ‘技术部’;

— 创建测试表
CREATE TABLE fgedu_departments (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
manager VARCHAR(50) NOT NULL
);

— 插入测试数据
INSERT INTO fgedu_departments (name, manager)
VALUES
(‘技术部’, ‘张三’),
(‘市场部’, ‘李四’),
(‘财务部’, ‘王五’);

— 复杂查询优化
EXPLAIN SELECT e.name, e.salary, d.name AS department_name, d.manager
FROM fgedu_employees e
JOIN fgedu_departments d ON e.department = d.name
WHERE e.salary > 7000
ORDER BY e.salary DESC;

— 创建复合索引
CREATE INDEX idx_salary_department ON fgedu_employees(salary, department);

— 再次查看执行计划
EXPLAIN SELECT e.name, e.salary, d.name AS department_name, d.manager
FROM fgedu_employees e
JOIN fgedu_departments d ON e.department = d.name
WHERE e.salary > 7000
ORDER BY e.salary DESC;

— 创建大表（模拟）
CREATE TABLE fgedu_large_table (
id INT PRIMARY KEY AUTO_INCREMENT,
value1 VARCHAR(100) NOT NULL,
value2 VARCHAR(100) NOT NULL,
value3 VARCHAR(100) NOT NULL,
created_at DATETIME NOT NULL
);

— 插入测试数据（模拟100万条记录）
DELIMITER //
CREATE PROCEDURE insert_test_data()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 1000000 DO INSERT INTO fgedu_large_table (value1, value2, value3, created_at) VALUES (CONCAT('value1_', i), CONCAT('value2_', i), CONCAT('value3_', i), NOW()); SET i = i + 1; END WHILE; END // DELIMITER ; -- 调用存储过程插入数据 CALL insert_test_data(); -- 优化前：全表扫描 EXPLAIN SELECT * FROM fgedu_large_table WHERE created_at > ‘2023-01-01’;

— 创建索引
CREATE INDEX idx_created_at ON fgedu_large_table(created_at);

— 优化后：索引扫描
EXPLAIN SELECT * FROM fgedu_large_table WHERE created_at > ‘2023-01-01’;