2025后端面试题(拟)-05(Mysql基础篇)
请解释 SELECT、INSERT、UPDATE、DELETE 等 SQL 命令的语法和用途。
SELECT 查询语句
语法：
SELECT [DISTINCT] column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column_name]
[HAVING condition]
[ORDER BY column_name [ASC|DESC]]
[LIMIT number];用途：从数据库中检索数据
示例：
-- 查询所有员工信息
SELECT * FROM employees;
-- 查询特定列
SELECT first_name, last_name FROM employees WHERE department = 'IT';
-- 带排序和限制
SELECT product_name, price FROM products ORDER BY price DESC LIMIT 10;INSERT 插入语句
语法：
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);用途：向表中插入新记录
示例：
-- 插入单条记录
INSERT INTO customers (name, email, phone)
VALUES ('张三', 'zhangsan@example.com', '13800138000');
-- 插入多条记录
INSERT INTO products (name, price, stock)
VALUES
('商品A', 99.99, 100),
('商品B', 199.99, 50),
('商品C', 299.99, 25);UPDATE 更新语句
语法：
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;用途：修改表中现有记录
示例：
-- 更新单个字段
UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Engineering';
-- 更新多个字段
UPDATE orders
SET status = 'Shipped', shipping_date = CURRENT_DATE
WHERE order_id = 12345;DELETE 删除语句
语法：
DELETE FROM table_name
WHERE condition;用途：从表中删除记录
示例：
-- 删除特定记录
DELETE FROM customers
WHERE last_purchase_date < '2020-01-01';
-- 删除所有记录（慎用！）
DELETE FROM temp_logs;重要注意事项
WHERE 子句：UPDATE 和 DELETE 语句中的 WHERE 子句非常重要，缺少它可能导致更新或删除整个表的数据
事务控制：在执行修改数据的语句(INSERT/UPDATE/DELETE)前，考虑使用事务：
BEGIN TRANSACTION; -- 执行SQL语句 COMMIT; -- 或 ROLLBACK;性能考虑：大量数据操作时，批量操作通常比单条操作更高效
安全考虑：在生产环境执行 DELETE 或 UPDATE 前，建议先用 SELECT 验证 WHERE 条件是否正确
如何使用 JOIN 操作符连接多个表
1. INNER JOIN (内连接)
语法：
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;用途：返回两个表中匹配的行
示例：
-- 获取订单及其客户信息
SELECT orders.order_id, customers.customer_name, orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;2. LEFT JOIN (左连接)
语法：
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;用途：返回左表所有行，即使右表没有匹配
示例：
sql
-- 获取所有客户及其订单(包括没有订单的客户)
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
3. RIGHT JOIN (右连接)
语法：
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;用途：返回右表所有行，即使左表没有匹配
示例：
-- 获取所有产品及其订单(包括未被订购的产品)
SELECT products.product_name, orders.order_id
FROM orders
RIGHT JOIN products ON orders.product_id = products.product_id;4. FULL OUTER JOIN (全外连接)
语法：
SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;用途：返回两个表中所有行，无论是否匹配
示例：
-- 获取所有客户和所有产品，显示匹配的订单
SELECT customers.customer_name, products.product_name, orders.quantity
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id
FULL OUTER JOIN products ON orders.product_id = products.product_id;多表连接
语法：
SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column
JOIN table3 ON table1.column = table3.column;示例：
-- 获取订单详情，包括客户和产品信息
SELECT
orders.order_id,
customers.customer_name,
products.product_name,
order_items.quantity,
products.price * order_items.quantity AS total_price
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id;自连接 (Self Join)
用途：表与自身连接
示例：
-- 查找同一部门的员工对
SELECT
a.employee_name AS employee1,
b.employee_name AS employee2,
a.department
FROM employees a
INNER JOIN employees b ON a.department = b.department AND a.employee_id < b.employee_id;连接条件的高级用法
多条件连接：
SELECT * FROM table1 JOIN table2 ON table1.col1 = table2.col1 AND table1.col2 = table2.col2;非等值连接：
-- 查找价格在特定范围内的产品 SELECT products.name, price_ranges.range_name FROM products JOIN price_ranges ON products.price BETWEEN price_ranges.min_price AND price_ranges.max_price;
JOIN 性能优化建议
- 确保连接列有索引
- 只选择需要的列，避免
SELECT * - 在 WHERE 子句中添加额外的过滤条件
- 考虑表的大小，从小表连接大表通常更高效
- 对于复杂查询，考虑使用临时表或视图
实际应用场景
-- 电商系统：获取用户购物车内容
SELECT
u.username,
p.product_name,
p.price,
c.quantity,
p.price * c.quantity AS subtotal
FROM users u
JOIN cart c ON u.user_id = c.user_id
JOIN products p ON c.product_id = p.product_id
WHERE u.user_id = 123;请解释 SQL 中的聚合函数（如 COUNT、SUM、AVG 等）的使用。
1. COUNT() - 计数
用途：计算行数或非NULL值的数量
语法：
SELECT COUNT(column_name) FROM table_name;示例：
-- 计算员工总数
SELECT COUNT(*) AS total_employees FROM employees;
-- 计算有电话号码的客户数量
SELECT COUNT(phone) AS customers_with_phone FROM customers;
-- 计算不同部门的数量
SELECT COUNT(DISTINCT department) AS unique_departments FROM employees;2. SUM() - 求和
用途：计算数值列的总和
语法：
SELECT SUM(column_name) FROM table_name;示例：
-- 计算所有订单的总金额
SELECT SUM(amount) AS total_sales FROM orders;
-- 计算某产品的总销售额
SELECT SUM(quantity * price) AS total_revenue
FROM order_details
WHERE product_id = 101;3. AVG() - 平均值
用途：计算数值列的平均值
语法：
SELECT AVG(column_name) FROM table_name;示例：
-- 计算员工平均工资
SELECT AVG(salary) AS avg_salary FROM employees;
-- 计算某产品平均评分(四舍五入到2位小数)
SELECT ROUND(AVG(rating), 2) AS avg_rating
FROM product_reviews
WHERE product_id = 205;4. MAX() - 最大值
用途：找出列中的最大值
语法：
SELECT MAX(column_name) FROM table_name;示例：
-- 找出最高工资
SELECT MAX(salary) AS highest_salary FROM employees;
-- 找出最近订单日期
SELECT MAX(order_date) AS latest_order FROM orders;5. MIN() - 最小值
用途：找出列中的最小值
语法：
SELECT MIN(column_name) FROM table_name;示例：
-- 找出最低产品价格
SELECT MIN(price) AS lowest_price FROM products;
-- 找出最早雇佣日期
SELECT MIN(hire_date) AS first_hire FROM employees;组合使用聚合函数
-- 统计销售数据
SELECT
COUNT(*) AS total_orders,
SUM(amount) AS total_sales,
AVG(amount) AS avg_order_value,
MAX(amount) AS largest_order,
MIN(amount) AS smallest_order
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';与 GROUP BY 结合使用
用途：按指定列分组后应用聚合函数
语法：
SELECT column1, AGG_FUNC(column2)
FROM table_name
GROUP BY column1;示例：
-- 按部门统计员工数量和平均工资
SELECT
department,
COUNT(*) AS employee_count,
ROUND(AVG(salary), 2) AS avg_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department;
-- 按月统计销售额
SELECT
EXTRACT(MONTH FROM order_date) AS month,
SUM(amount) AS monthly_sales
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY month;HAVING 子句
用途：对分组结果进行筛选(类似于WHERE但用于聚合函数)
示例：
-- 找出销售额超过10000的客户
SELECT
customer_id,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 10000
ORDER BY total_spent DESC;
-- 找出平均评分低于3的产品
SELECT
product_id,
AVG(rating) AS avg_rating
FROM product_reviews
GROUP BY product_id
HAVING AVG(rating) < 3;注意事项
- NULL值处理：聚合函数通常忽略NULL值(COUNT(*)除外)
- 性能考虑：在大表上使用聚合函数可能消耗资源
- 精确度问题：AVG()等函数可能导致浮点数精度问题
- 分组选择：SELECT中的非聚合列通常应包含在GROUP BY中
实际应用案例
-- 电商销售分析报表
SELECT
p.category,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(od.quantity) AS total_units_sold,
SUM(od.quantity * p.price) AS total_revenue,
ROUND(SUM(od.quantity * p.price) / SUM(od.quantity), 2) AS avg_unit_price
FROM products p
JOIN order_details od ON p.product_id = od.product_id
JOIN orders o ON od.order_id = o.order_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY p.category
HAVING SUM(od.quantity * p.price) > 5000
ORDER BY total_revenue DESC;请解释 MySQL 中常见的数据类型（如 INT、VARCHAR、TEXT、DATE 等）。
数值类型
1. 整数类型
| 类型 | 存储空间 | 有符号范围 | 无符号范围 | 用途 |
|---|---|---|---|---|
| TINYINT | 1字节 | -128 到 127 | 0 到 255 | 小范围数值(如状态码) |
| SMALLINT | 2字节 | -32,768 到 32,767 | 0 到 65,535 | 中等范围数值 |
| MEDIUMINT | 3字节 | -8,388,608 到 8,388,607 | 0 到 16,777,215 | 中等范围数值 |
| INT | 4字节 | -2^31 到 2^31-1 | 0 到 2^32-1 | 标准整数(最常用) |
| BIGINT | 8字节 | -2^63 到 2^63-1 | 0 到 2^64-1 | 大范围数值(如主键ID) |
示例：
CREATE TABLE users (
user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
age TINYINT UNSIGNED,
login_count MEDIUMINT UNSIGNED DEFAULT 0
);2. 浮点数类型
| 类型 | 存储空间 | 说明 |
|---|---|---|
| FLOAT | 4字节 | 单精度浮点数，约7位精度 |
| DOUBLE | 8字节 | 双精度浮点数，约15位精度 |
| DECIMAL(M,D) | 变长 | 精确小数，M是总位数，D是小数位数 |
示例：
CREATE TABLE products (
price DECIMAL(10,2), -- 总10位，2位小数
weight FLOAT,
tax_rate DOUBLE
);字符串类型
1. 短字符串
| 类型 | 最大长度 | 特点 |
|---|---|---|
| CHAR(n) | 255字符 | 固定长度，适合存储定长数据 |
| VARCHAR(n) | 65,535字符 | 可变长度，节省空间 |
示例：
CREATE TABLE employees (
employee_code CHAR(6), -- 固定6位员工代码
name VARCHAR(50), -- 可变长度姓名
email VARCHAR(100)
);2. 长文本类型
| 类型 | 最大长度 | 特点 |
|---|---|---|
| TINYTEXT | 255字节 | 短文本 |
| TEXT | 65,535字节 | 标准文本(约64KB) |
| MEDIUMTEXT | 16,777,215字节 | 中等长度文本(约16MB) |
| LONGTEXT | 4,294,967,295字节 | 长文本(约4GB) |
示例：
CREATE TABLE articles (
title VARCHAR(200),
content TEXT,
summary TINYTEXT
);3. 二进制数据
| 类型 | 最大长度 | 用途 |
|---|---|---|
| BINARY(n) | 255字节 | 固定长度二进制数据 |
| VARBINARY(n) | 65,535字节 | 可变长度二进制数据 |
| BLOB | 65,535字节 | 二进制大对象(如图片) |
| LONGBLOB | 4GB | 超大二进制对象 |
示例：
CREATE TABLE documents (
filename VARCHAR(100),
file_data LONGBLOB
);日期和时间类型
| 类型 | 格式 | 范围 | 用途 |
|---|---|---|---|
| DATE | YYYY-MM-DD | 1000-01-01 到 9999-12-31 | 仅日期 |
| TIME | HH:MM:SS | -838:59:59 到 838:59:59 | 时间值 |
| DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 到 9999-12-31 23:59:59 | 日期和时间 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC | 时间戳，自动更新 |
| YEAR | YYYY | 1901 到 2155 | 年份 |
示例：
CREATE TABLE events (
event_name VARCHAR(100),
start_date DATE,
start_time TIME,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);枚举和集合类型
1. ENUM (枚举)
用途：只能存储预定义值中的一个
示例：
CREATE TABLE shirts (
size ENUM('XS', 'S', 'M', 'L', 'XL') -- 只能选择其中一个
);2. SET (集合)
用途：可以存储预定义值中的多个(用逗号分隔)
示例：
CREATE TABLE pizza_orders (
toppings SET('pepperoni', 'mushrooms', 'olives', 'onions') -- 可选择多个
);选择数据类型的建议
- 最小化原则：选择能满足需求的最小数据类型
- 固定长度 vs 可变长度：CHAR适合定长数据(如MD5哈希)，VARCHAR适合变长数据
- 数值精度：需要精确计算时使用DECIMAL，科学计算可用FLOAT/DOUBLE
- 日期选择：通常DATETIME足够，需要时区支持或自动更新用TIMESTAMP
- 大文本存储：TEXT类型适合长文本，但避免在WHERE子句中频繁使用
实际应用示例
CREATE TABLE ecommerce_orders (
order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_id INT UNSIGNED NOT NULL,
order_status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled'),
total_amount DECIMAL(12,2) UNSIGNED,
payment_method VARCHAR(20),
shipping_address TEXT,
order_notes TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX (customer_id),
INDEX (order_status)
);请比较 InnoDB 和 MyISAM 存储引擎的优缺点。
核心特性对比
| 特性 | InnoDB | MyISAM |
|---|---|---|
| 事务支持 | 支持(ACID 兼容) | 不支持 |
| 锁机制 | 行级锁 | 表级锁 |
| 外键约束 | 支持 | 不支持 |
| 崩溃恢复 | 有崩溃后修复能力 | 无自动修复，易损坏 |
| 全文索引 | MySQL 5.6+ 支持 | 支持 |
| 存储限制 | 64TB | 256TB |
| 缓存机制 | 缓冲池(缓存数据和索引) | 仅缓存索引 |
| COUNT(*) 性能 | 较慢(需要全表扫描) | 极快(存储行数计数器) |
| 压缩表 | 不支持 | 支持 |
InnoDB 优势详解
1. 事务处理能力
- 完全支持 ACID(原子性、一致性、隔离性、持久性)特性
- 支持 COMMIT 和 ROLLBACK 操作
- 适合需要事务保证的应用(如银行系统、电商订单)
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT; -- 只有两条都成功才会提交2. 行级锁定
- 只锁定需要修改的行，其他行仍可访问
- 高并发环境下性能更好
- 减少锁争用，提高多用户并发性能
3. 外键约束
- 强制保持数据引用完整性
- 自动检查外键关系，防止孤立记录
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);4. 崩溃恢复
- 使用写前日志(WAL)机制
- 崩溃后能自动恢复数据一致性
- 减少数据损坏风险
5. 缓冲池优化
- 将表和索引数据缓存在内存缓冲池中
- 减少磁盘I/O，提高查询性能
- 自适应哈希索引加速查找
MyISAM 优势详解
1. 全文本搜索
- 内置全文索引功能(MySQL 5.6前优于InnoDB)
- 适合内容搜索应用
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(100),
body TEXT,
FULLTEXT (title, body)
) ENGINE=MyISAM;
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('MySQL');2. COUNT(*) 性能
- 存储表的总行数计数器
- 无需扫描表即可获取准确行数
- 适合需要频繁统计总数的场景
3. 表压缩
- 支持压缩表格式(myisampack工具)
- 减少存储空间占用
- 对只读或很少更新的数据特别有效
4. 简单查询性能
- 无事务开销
- 表结构简单
- 在简单查询场景下可能更快
性能对比场景
适合 InnoDB 的场景：
- 需要事务支持(金融系统、订单处理)
- 高并发写操作(社交网络、论坛)
- 数据完整性要求高(外键约束)
- 经常更新的表(用户账户信息)
适合 MyISAM 的场景：
- 读密集型应用(数据仓库、报表系统)
- 不需要事务的简单应用(博客系统)
- 需要全文搜索(MySQL 5.6前版本)
- 只读或极少更新的表(归档数据)
实际应用建议
现代MySQL默认选择：MySQL 5.5+ 默认使用InnoDB，大多数情况下是更好的选择
混合使用场景：
-- 同一个数据库中使用不同引擎 CREATE TABLE user_transactions ( id INT PRIMARY KEY AUTO_INCREMENT, amount DECIMAL(10,2) ) ENGINE=InnoDB; CREATE TABLE web_logs ( id INT PRIMARY KEY AUTO_INCREMENT, access_time DATETIME, url VARCHAR(255) ) ENGINE=MyISAM;迁移考虑：
- MyISAM 到 InnoDB 迁移需考虑事务支持和外键约束
- 使用
ALTER TABLE table_name ENGINE=InnoDB转换引擎
监控与调优：
- InnoDB 需关注缓冲池大小(innodb_buffer_pool_size)
- MyISAM 需关注键缓存大小(key_buffer_size)
最新发展
- MySQL 8.0 中 InnoDB 性能大幅提升，已支持:
- 更好的全文搜索功能
- 空间数据类型索引
- 改进的并行查询
- MyISAM 已不再积极开发，新功能主要针对InnoDB
如何选择合适的存储引擎？
总结决策树
是否需要事务支持？
├── 是 → 选择 InnoDB
└── 否 →
├── 是否高并发写入？
│ ├── 是 → 选择 InnoDB
│ └── 否 →
│ ├── 是否需要全文搜索(MySQL <5.6)？
│ │ ├── 是 → 选择 MyISAM
│ │ └── 否 →
│ │ ├── 是否只读/极少更新？
│ │ │ ├── 是 → 考虑 MyISAM
│ │ │ └── 否 → 选择 InnoDB
│ └── 其他情况 → 默认选择 InnoDB在现代MySQL环境中，除非有特定需求(如遗留系统兼容性或特殊功能需求)，否则推荐优先使用InnoDB引擎。
请解释索引的作用和类型（如主键索引、唯一索引、普通索引等）。
一、索引的核心作用
1. 加速数据检索
- 避免全表扫描，快速定位数据
- 将随机I/O变为顺序I/O（B+树特性）
- 典型场景：WHERE条件、JOIN连接、ORDER BY排序
-- 无索引：全表扫描
SELECT * FROM users WHERE username = 'john_doe';
-- 有索引：快速定位
CREATE INDEX idx_username ON users(username);2. 保证数据唯一性
- 唯一索引防止重复值插入
- 主键索引标识唯一记录
-- 防止重复邮箱
CREATE UNIQUE INDEX idx_email ON users(email);3. 优化排序和分组
- 避免filesort临时表排序
- 加速GROUP BY操作
-- 使用索引排序
SELECT * FROM products ORDER BY category_id;4. 实现表连接优化
- 外键关联时快速匹配记录
- 减少嵌套循环连接次数
-- 订单关联用户
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'US';二、索引类型详解
1. 按功能分类
(1) 主键索引（PRIMARY KEY）
- 特点：
- 每表只能有一个
- 不允许NULL值
- 物理上按主键顺序存储数据（聚簇索引）
创建方式：
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ); -- 或后期添加 ALTER TABLE users ADD PRIMARY KEY (id);
(2) 唯一索引（UNIQUE）
- 特点：
- 允许NULL值（但NULL也只能出现一次）
- 可多个列组合
- 确保列值唯一性
创建方式：
CREATE TABLE employees ( emp_id INT PRIMARY KEY, email VARCHAR(100), UNIQUE INDEX idx_email (email) ); -- 多列唯一 ALTER TABLE rooms ADD UNIQUE idx_floor_room (floor_num, room_num);
(3) 普通索引（INDEX）
- 特点：
- 最基本的索引类型
- 无唯一性约束
- 可加速查询和排序
创建方式：
CREATE INDEX idx_lastname ON customers(last_name); -- 多列组合索引 CREATE INDEX idx_name_age ON students(name, age);
(4) 全文索引（FULLTEXT）
- 特点：
- 专为文本搜索设计
- 支持MATCH...AGAINST语法
- InnoDB(5.6+)和MyISAM支持
创建方式：
CREATE TABLE articles ( id INT PRIMARY KEY, title VARCHAR(200), content TEXT, FULLTEXT (title, content) ); -- 搜索示例 SELECT * FROM articles WHERE MATCH(title, content) AGAINST('database optimization');
(5) 空间索引（SPATIAL）
- 特点：
- 专为地理数据设计
- 支持GIS数据类型(GEOMETRY, POINT等)
- 仅MyISAM支持(5.7+ InnoDB也支持)
创建方式：
CREATE TABLE locations ( id INT PRIMARY KEY, name VARCHAR(50), position POINT, SPATIAL INDEX(position) ) ENGINE=MyISAM;
2. 按数据结构分类
(1) B-Tree索引
- 特点：
- 默认索引类型
- 适合精确匹配和范围查询
- 支持排序
适用操作：
=, >, <, BETWEEN, LIKE 'prefix%'
(2) Hash索引
- 特点：
- Memory引擎默认
- 精确匹配极快
- 不支持范围查询
限制：
-- 仅Memory引擎显式支持 CREATE TABLE lookup ( id INT, INDEX USING HASH (id) ) ENGINE=MEMORY;
(3) R-Tree索引
- 特点：
- 用于空间数据
- 支持空间关系计算
操作示例：
SELECT * FROM areas WHERE MBRContains(geom, Point(1,1));
三、索引的物理实现
1. 聚簇索引 vs 非聚簇索引
| 特性 | 聚簇索引 | 非聚簇索引 |
|---|---|---|
| 存储方式 | 索引即数据(主键组织表) | 独立存储(指向数据行) |
| 数量 | 每表一个 | 每表多个 |
| 速度 | 主键查询最快 | 需要回表查询 |
| 代表 | InnoDB的主键 | 二级索引 |
2. 覆盖索引优化
- 索引包含查询所需全部字段时
避免回表操作，极大提升性能
-- 创建覆盖索引 CREATE INDEX idx_cover ON orders(user_id, order_date, amount); -- 查询可被索引覆盖 SELECT user_id, order_date FROM orders WHERE user_id = 100 AND order_date > '2023-01-01';
四、多列组合索引策略
1. 最左前缀原则
- 索引(a,b,c)可支持：
- WHERE a=1
- WHERE a=1 AND b=2
- WHERE a=1 AND b=2 AND c=3
- 不支持：
- WHERE b=2
- WHERE c=3
- WHERE b=2 AND c=3
2. 索引列顺序建议
- 高选择性列在前（区分度高的列）
- 常用条件列在前
- 等值查询列在前，范围列在后
-- 好例子：user_id选择性高且常用
CREATE INDEX idx_optimal ON orders(user_id, status, create_time);
-- 反例：status选择性低且放前面
CREATE INDEX idx_bad ON orders(status, user_id);五、索引使用注意事项
1. 适合创建索引的场景
- WHERE条件频繁使用的列
- JOIN关联字段
- ORDER BY/GROUP BY字段
- 高选择性字段(区分度>10%)
2. 应避免索引的情况
- 频繁更新的列（导致索引维护开销）
- 数据量小的表（全表扫描更快）
- 低选择性字段（如性别、状态标志）
- TEXT/BLOB大字段（考虑前缀索引）
3. 索引使用陷阱
隐式类型转换：
-- phone是varchar时，数字查询无法用索引 SELECT * FROM users WHERE phone = 123456;函数操作：
-- 索引失效 SELECT * FROM logs WHERE DATE(create_time) = '2023-01-01'; -- 优化写法 SELECT * FROM logs WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02';前导通配符：
-- 无法使用索引 SELECT * FROM products WHERE name LIKE '%apple%'; -- 可以使用索引 SELECT * FROM products WHERE name LIKE 'apple%';
如何创建和删除索引？
1. 查看索引
SHOW INDEX FROM table_name;
-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE username = 'test';2. 维护索引
-- 重建索引（InnoDB）
ALTER TABLE orders DROP INDEX idx_name;
ALTER TABLE orders ADD INDEX idx_name (name);
-- 优化表（MyISAM）
OPTIMIZE TABLE large_table;3. 删除索引
DROP INDEX idx_name ON table_name;
-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;实践建议
监控索引使用：
-- 检查未使用的索引 SELECT * FROM sys.schema_unused_indexes;复合索引测试：
-- 测试不同列顺序的性能 CREATE INDEX idx_test1 ON table(a,b,c); CREATE INDEX idx_test2 ON table(b,a,c);定期维护：
- 删除冗余索引
- 重建碎片化索引
- 更新统计信息
使用索引提示：
-- 强制使用特定索引 SELECT * FROM users USE INDEX (idx_email) WHERE email LIKE 'a%';
正确使用索引可以使查询性能提升几个数量级，但需要平衡查询速度与写入开销。建议通过EXPLAIN分析查询计划，持续监控和优化索引策略。
请解释如何通过索引优化查询性能。
一、索引优化基础原则
1. 选择性原则
- 高选择性列优先：选择区分度高的列建索引（如用户ID比性别更适合）
计算公式：选择性 = 不重复值数量 / 总记录数
-- 计算email字段的选择性 SELECT COUNT(DISTINCT email)/COUNT(*) FROM users;
2. 覆盖索引策略
使索引包含查询所需全部字段，避免回表操作
-- 原始查询（需要回表） SELECT username, email FROM users WHERE age > 20; -- 优化方案：创建覆盖索引 CREATE INDEX idx_age_cover ON users(age, username, email);
3. 最左前缀匹配
- 组合索引必须从左到右使用
- 建立(a,b,c)索引相当于建立了(a)、(a,b)、(a,b,c)三个索引
二、查询模式与索引设计
1. 等值查询优化
-- 等值条件放在组合索引左侧
CREATE INDEX idx_status_created ON orders(status, created_at);
-- 高效查询
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2023-01-01';2. 范围查询优化
范围列应放在组合索引最右侧
-- 反例：范围列在中间导致后续索引失效 CREATE INDEX idx_bad_range ON sales(region, sale_date, product_id); -- 正例：范围列放最后 CREATE INDEX idx_good_range ON sales(region, product_id, sale_date);
3. 排序优化
- 为ORDER BY字段建立索引
排序方向需一致
-- 需要索引支持的排序 SELECT * FROM products ORDER BY category_id, price DESC; -- 对应索引 CREATE INDEX idx_sort ON products(category_id, price DESC);
4. 分组优化
- GROUP BY实质是先排序后分组
为GROUP BY字段建立索引
-- 优化分组查询 SELECT department_id, COUNT(*) FROM employees GROUP BY department_id; -- 对应索引 CREATE INDEX idx_dept ON employees(department_id);
三、高级优化技巧
1. 索引合并优化
MySQL可自动合并多个单列索引
-- 现有索引：idx_name(name), idx_age(age) SELECT * FROM users WHERE name = 'John' OR age = 30;
2. 索引条件下推(ICP)
5.6+版本特性，将WHERE条件推到存储引擎层
-- 组合索引(a,b) SELECT * FROM table WHERE a = 1 AND b LIKE '%test%';
3. 松散索引扫描
跳过组合索引前缀直接访问后续列
-- 索引(a,b) SELECT DISTINCT b FROM table WHERE a = 1;
4. 函数索引(8.0+)
为表达式计算结果建立索引
-- 为JSON字段建立索引 CREATE TABLE users ( id INT PRIMARY KEY, profile JSON, INDEX idx_name ((CAST(profile->>'$.name' AS CHAR(30)))) ); -- 为计算列建立索引 CREATE TABLE products ( id INT PRIMARY KEY, price DECIMAL(10,2), tax_rate DECIMAL(5,2), total_price DECIMAL(10,2) AS (price * (1 + tax_rate)), INDEX idx_total (total_price) );
四、实战优化案例
案例1：电商订单查询优化
-- 原始慢查询
SELECT o.order_id, o.amount, u.username
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.status = 'paid'
AND o.create_time BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY o.amount DESC
LIMIT 100;
-- 优化方案：
-- 1. 订单表索引
CREATE INDEX idx_order_optim ON orders(status, create_time, amount, user_id);
-- 2. 用户表索引
CREATE INDEX idx_user_id ON users(user_id, username);案例2：社交平台好友动态
-- 原始查询
SELECT p.post_id, p.content, p.create_time, u.username
FROM posts p
JOIN users u ON p.user_id = u.user_id
WHERE p.user_id IN (
SELECT friend_id FROM user_relations
WHERE user_id = 123 AND relation_type = 'friend'
)
ORDER BY p.create_time DESC
LIMIT 20;
-- 优化方案：
-- 1. 关系表索引
CREATE INDEX idx_relations ON user_relations(user_id, relation_type, friend_id);
-- 2. 帖子表索引
CREATE INDEX idx_posts ON posts(user_id, create_time DESC);
-- 3. 用户表索引
CREATE INDEX idx_users ON users(user_id, username);五、索引优化检查清单
必要性检查：
- 查询是否真的需要优化？
- 表数据量是否足够大？
索引设计验证：
- 是否遵循最左前缀原则？
- 组合索引列顺序是否合理？
- 是否考虑了排序和分组需求？
查询重写评估：
- 能否使用覆盖索引？
- 是否有隐式类型转换？
- 能否避免前导通配符？
维护计划：
- 是否有未使用的索引需要删除？
- 索引碎片是否需要整理？
- 统计信息是否最新？
记住：索引不是越多越好，需要平衡查询性能与写入开销。