视图与索引
视图与索引
想象你经常去图书馆借书，有两种方式找书：第一种是每次都在所有书架上翻找（全表扫描）；第二种是先查索引卡片，直接去对应书架（索引查询）。而图书馆的"推荐书单"就像是视图——它不是真正的书架，而是从不同书架上挑选出来的书的列表，方便你快速找到想要的书。SQL中的视图和索引也是同样的道理。
1. 核心概念
视图（View）— 概念
视图是一个虚拟表，它不存储数据，而是保存了一条SELECT查询语句。每次访问视图时，数据库会执行视图定义的查询，返回结果。
SQL
-- 创建视图：员工信息视图
CREATE VIEW v_employee_info AS
SELECT
e.employee_id,
CONCAT(e.first_name, e.last_name) AS 姓名,
e.salary AS 工资,
d.department_name AS 部门,
d.city AS 城市
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
使用视图就像使用普通表：
SQL
-- 查询视图
SELECT * FROM v_employee_info WHERE 工资 > 10000;
创建视图
SQL
CREATE VIEW 视图名 AS
SELECT 语句;
SQL
-- 创建部门统计视图
CREATE VIEW v_department_stats AS
SELECT
d.department_id,
d.department_name AS 部门,
COUNT(e.employee_id) AS 员工数,
AVG(e.salary) AS 平均工资,
SUM(e.salary) AS 工资总额
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;
修改视图
SQL
-- 方式一：CREATE OR REPLACE（推荐）
CREATE OR REPLACE VIEW v_employee_info AS
SELECT
e.employee_id,
CONCAT(e.first_name, e.last_name) AS 姓名,
e.salary AS 工资,
e.hire_date AS 入职日期,
d.department_name AS 部门
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
-- 方式二：ALTER VIEW
ALTER VIEW v_employee_info AS
SELECT employee_id, first_name, last_name, salary FROM employees;
删除视图
SQL
DROP VIEW IF EXISTS v_employee_info;
💡 提示：删除视图不会影响原始表的数据，视图只是查询的"快捷方式"。
视图的优点
| 优点 | 说明 |
|---|---|
| 简化复杂查询 | 将复杂JOIN封装为视图，使用时只需SELECT * FROM 视图 |
| 数据安全 | 可以只暴露部分列或行给用户，隐藏敏感数据 |
| 逻辑独立 | 修改视图定义不影响使用视图的应用程序 |
| 统一口径 | 确保所有用户使用相同的查询逻辑，避免重复编写 |
视图的缺点
| 缺点 | 说明 |
|---|---|
| 性能开销 | 每次访问都需执行底层查询，复杂视图可能较慢 |
| 更新限制 | 包含JOIN、聚合函数、DISTINCT的视图通常不可更新 |
| 调试困难 | 多层嵌套视图增加排查问题的难度 |
💡 提示：视图本身不存储数据，它是"查询的别名"。底层表数据变化，视图结果也会随之变化。
索引（Index）— 概念
索引是一种数据结构，类似于书的目录，帮助数据库快速定位数据，避免全表扫描。
SQL
-- 没有索引：全表扫描（逐行查找）
SELECT * FROM employees WHERE last_name = '张';
-- 有索引：直接定位（通过索引查找）
CREATE INDEX idx_last_name ON employees(last_name);
SELECT * FROM employees WHERE last_name = '张';
创建索引
SQL
-- 普通索引（允许重复值）
CREATE INDEX idx_column ON 表名(列名);
-- 唯一索引（不允许重复值）
CREATE UNIQUE INDEX idx_column ON 表名(列名);
-- 全文索引（用于全文搜索）
CREATE FULLTEXT INDEX idx_column ON 表名(列名);
-- 复合索引（多列组合）
CREATE INDEX idx_columns ON 表名(列1, 列2);
删除索引
SQL
-- MySQL语法
DROP INDEX idx_column ON 表名;
-- 标准语法
ALTER TABLE 表名 DROP INDEX idx_column;
索引的类型
| 类型 | 关键字 | 说明 | 适用场景 |
|---|---|---|---|
| 普通索引 | INDEX |
最基本的索引，允许重复值 | 频繁查询的列 |
| 唯一索引 | UNIQUE INDEX |
列值必须唯一 | 邮箱、身份证号等 |
| 全文索引 | FULLTEXT INDEX |
支持全文搜索 | 文章内容、产品描述 |
| 复合索引 | INDEX(列1, 列2) |
多列组合索引 | 多条件组合查询 |
复合索引与最左前缀原则
复合索引遵循最左前缀原则：查询条件必须从索引最左列开始匹配，才能使用索引。
SQL
-- 创建复合索引
CREATE INDEX idx_name_age ON employees(last_name, first_name, salary);
-- ✅ 可以使用索引（匹配最左列）
SELECT * FROM employees WHERE last_name = '张';
SELECT * FROM employees WHERE last_name = '张' AND first_name = '伟';
-- ❌ 无法使用索引（跳过了最左列）
SELECT * FROM employees WHERE first_name = '伟';
SELECT * FROM employees WHERE salary > 10000;
💡 提示：设计复合索引时，把查询中最常用的列放在最左边。
索引的代价
| 代价 | 说明 |
|---|---|
| 存储空间 | 索引需要额外的磁盘空间 |
| 写入性能 | INSERT/UPDATE/DELETE需要同步更新索引 |
| 维护成本 | 大量索引会降低写入速度 |
💡 提示：不是越多越好！只为频繁查询且数据量大的列创建索引。小表（几百行）通常不需要索引。
2. 基本语法
视图语法
SQL
-- 创建视图
CREATE [OR REPLACE] VIEW 视图名 AS SELECT语句;
-- 查询视图（与普通表相同）
SELECT * FROM 视图名 WHERE 条件;
-- 删除视图
DROP VIEW [IF EXISTS] 视图名;
💡 提示：视图命名建议以
v_或view_开头，便于区分视图和真实表。
索引语法
SQL
-- 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(列1, 列2, ...);
-- 删除索引
DROP INDEX 索引名 ON 表名;
-- 查看表的索引
SHOW INDEX FROM 表名;
💡 提示：主键（PRIMARY KEY）和唯一约束（UNIQUE）会自动创建索引，无需手动创建。
💡 提示：使用
EXPLAIN查看查询是否使用了索引：EXPLAIN SELECT * FROM employees WHERE last_name = '张';
示例：创建和使用视图（难度⭐）
SQL
-- 创建订单详情视图
CREATE OR REPLACE VIEW v_order_detail AS
SELECT
o.order_id AS 订单号,
CONCAT(e.first_name, e.last_name) AS 负责人,
d.department_name AS 部门,
o.order_date AS 下单日期,
o.total_amount AS 金额,
o.status AS 状态
FROM orders o
LEFT JOIN employees e ON o.employee_id = e.employee_id
LEFT JOIN departments d ON e.department_id = d.department_id;
-- 使用视图查询
SELECT * FROM v_order_detail WHERE 状态 = 'completed';
输出：
TEXT
订单号 | 负责人 | 部门 | 下单日期 | 金额 | 状态
------+--------+--------+-----------+-----------+----------
1001 | 王强 | 销售部 | 2026-05-10 | 2500.00 | completed
1002 | 王强 | 销售部 | 2026-05-15 | 1800.00 | completed
1005 | 赵敏 | 销售部 | 2026-06-10 | 4100.00 | completed
SQL
-- 使用视图统计各部门订单金额
SELECT 部门, COUNT(*) AS 订单数, SUM(金额) AS 总金额
FROM v_order_detail
GROUP BY 部门;
查询思路：
- 视图封装了三表JOIN的复杂逻辑
- 使用时只需简单查询，无需重复编写JOIN
- 视图结果随底层数据变化而自动更新
示例：创建索引优化查询性能（难度⭐⭐）
SQL
-- 查看当前查询的执行计划
EXPLAIN SELECT * FROM employees WHERE last_name = '张';
输出（无索引时）：
TEXT
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
type = ALL表示全表扫描，rows = 6表示扫描了所有行。
SQL
-- 创建索引
CREATE INDEX idx_last_name ON employees(last_name);
-- 再次查看执行计划
EXPLAIN SELECT * FROM employees WHERE last_name = '张';
输出（有索引后）：
TEXT
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+-------+
| 1 | SIMPLE | employees | ref | idx_last_name | idx_last_name | 152 | const | 1 | |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+-------+
type = ref表示使用了索引，rows = 1表示只扫描了1行。
SQL
-- 创建复合索引
CREATE INDEX idx_status_date ON orders(status, order_date);
-- 可以使用索引的查询
EXPLAIN SELECT * FROM orders WHERE status = 'completed' AND order_date > '2026-06-01';
-- 查看表的所有索引
SHOW INDEX FROM employees;
查询思路：
- EXPLAIN的
type列：ALL（全表扫描）→ref（索引引用）说明性能提升 rows列：扫描行数越少越好- 复合索引需要按最左前缀原则使用
3. 常见应用场景
场景一：用视图简化权限管理
SQL
-- 创建只包含非敏感信息的视图
CREATE OR REPLACE VIEW v_employee_public AS
SELECT
employee_id,
CONCAT(first_name, last_name) AS 姓名,
d.department_name AS 部门
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
-- 普通用户只能访问这个视图，看不到工资等敏感信息
SELECT * FROM v_employee_public;
💡 思路：将敏感列（如salary、email）排除在视图之外，通过视图控制数据访问权限。
场景二：用视图实现数据报表
SQL
-- 创建月度销售报表视图
CREATE OR REPLACE VIEW v_monthly_sales AS
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS 月份,
COUNT(*) AS 订单数,
SUM(total_amount) AS 总销售额,
AVG(total_amount) AS 平均订单金额,
COUNT(DISTINCT customer_id) AS 客户数
FROM orders
WHERE status != 'cancelled'
GROUP BY DATE_FORMAT(order_date, '%Y-%m');
-- 查询报表
SELECT * FROM v_monthly_sales ORDER BY 月份;
输出：
TEXT
月份 | 订单数 | 总销售额 | 平均订单金额 | 客户数
--------+--------+---------+-------------+------
2026-05 | 2 | 4300.00 | 2150.00 | 2
2026-06 | 3 | 8250.00 | 2750.00 | 3
❓ 常见问题
Q：视图和临时表有什么区别？ A： 视图不存储数据，每次查询都执行底层SQL，结果始终最新；临时表存储数据快照，查询更快但数据可能过期。视图适合长期使用的查询模板，临时表适合中间计算步骤。
Q：视图可以修改数据吗？ A： 取决于视图定义。包含JOIN、聚合函数、DISTINCT、子查询的视图通常不可更新（只能SELECT）。简单的单表视图（无聚合）可以UPDATE。实际开发中，视图主要用于查询，不建议通过视图修改数据。
Q：索引建得越多越好吗？ A： 不是。索引会占用存储空间，降低INSERT/UPDATE/DELETE的性能。只为频繁查询且区分度高（值种类多）的列创建索引。小表通常不需要索引。
Q：如何判断查询是否使用了索引？ A： 使用
EXPLAIN命令查看执行计划。关注type列：ALL（全表扫描，最慢）→index→range→ref→eq_ref→const（最快）。如果type是ALL且表数据量大，说明需要添加索引。
📖 小节
- 视图是虚拟表，保存SELECT查询，不存储数据，结果始终与底层表同步
- 视图可以简化复杂查询、控制数据权限、统一查询口径
- 包含JOIN/聚合/DISTINCT的视图通常不可更新
- 索引是数据库的"目录"，加速查询但增加存储和写入开销
- 索引类型：普通索引、唯一索引、全文索引、复合索引
- 最左前缀原则：复合索引查询必须从最左列开始匹配
- 使用
EXPLAIN分析查询是否使用了索引
📝 作业
作业1（⭐）：创建一个视图v_high_salary，显示工资高于10000的员工信息（姓名、工资、部门名称），然后用视图查询"技术部"的高薪员工。
作业2（⭐⭐）：创建一个视图v_department_report，包含部门名称、员工数量、平均工资、最高工资、最低工资。然后用视图找出平均工资最高的部门。
作业3（⭐⭐）：为orders表的customer_id列创建索引，用EXPLAIN对比创建前后的查询性能差异。再创建一个orders(status, order_date)的复合索引，测试哪些查询可以使用该索引。
下一课
👉 18-practice-aggregate - 实战：数据分析综合：综合运用聚合函数、分组查询、条件表达式和高级函数，通过实战场景提升数据分析能力！
