视图和索引这两个词在数据库复习里几乎总是一起出现但它们解决的问题其实完全不同视图管的是“怎么把复杂的查询逻辑藏起来、把敏感的数据隔离掉”索引管的是“怎么让数据库在海量数据里快速找到你想要的记录”。很多人复习到这里容易滑过去觉得“视图不就是虚拟表嘛索引不就是加个目录嘛”结果一到实操就露馅创建视图报权限不足不知道找谁要权限索引建了一堆查询还是慢甚至因为二级索引更新的锁顺序踩出死锁。这篇复习笔记我把创建和管理视图、索引这条线完整梳理一遍从语法到权限、从执行计划到锁细节最后附上一组自测题帮你把这一节真正吃透。1. 先想清楚视图和索引分别是在解决什么问题1.1 视图的本质把查询逻辑“存”起来视图在数据库里是一个虚拟表它不真正存储数据只是保存了一段 SELECT 语句。每次你查视图数据库都会把视图定义的 SQL 拿出来和你的查询条件合并再去执行真正的查询。这个机制带来的第一个好处是封装。业务里如果有 20 张表的关联查询你不可能让每个报表开发都自己写一遍这 20 张表的 JOIN。你只需要建一个视图把关联逻辑写一次后面所有人查询视图就行。第二个好处是安全。底层表有手机号、身份证这种敏感列不给业务用户开表的 SELECT 权限只给他一个不包含敏感列的视图权限数据就隔离了。第三个好处是稳定。底层表结构改动时只要视图的定义没变应用代码就不用动。这里要注意一个常见误区视图本身不占额外存储普通视图所以它不会让查询更快反而可能因为多了一层 SQL 解析和改写带来一点点额外开销。真正能让查询加速的是视图背后的基表上有没有合适的索引。关于“视图能不能加快查询速度”这个问题后面我单独展开说。1.2 索引的本质物理存储上的有序结构索引不是“题目的目录”那么简单的类比。数据库索引是一棵独立的 B 树它把某一列或某几列的值按顺序组织起来叶子节点指向数据所在的位置。有了这棵树查询从“逐行扫描”变成“沿着树查找”复杂度从 O(n) 降到 O(log n)。B 树的底层结构决定了索引的两个使用特点最左前缀匹配联合索引 (a, b, c) 可以加快 a、ab、abc 查询但单纯查 b 或 c 用不上。有序性索引天然支持排序和范围查询所以 ORDER BY、BETWEEN、、 这类操作也能受益。同样要记住索引不是免费的。每一棵索引就是一份额外的存储结构写入一行数据时所有索引都要跟着更新。索引越多写入越慢磁盘占用越大。数据库里没有“越多越好”这回事只有“为查询设计”的原则。1.3 为什么复习时要把这两个概念放在一起视图和索引经常被放在同一章是因为它们都作用于“不改业务代码”这个前提。你的应用发出去一条 SQL是视图帮你挡掉了底层复杂性是索引帮你撑住了执行速度。一个管的是逻辑层一个管的是物理层。面试和考试特别喜欢把两者结合起来问比如“视图查询慢怎么优化”“能不能通过给视图创建索引来加速”所以复习的时候把它们放在一起对比记忆效果是最好的。2. 视图的创建与管理从语法到权限的完整链路2.1 CREATE VIEW 基础语法与第一个例子创建视图的 SQL 很简单核心是保存一段 SELECTCREATE [OR REPLACE] VIEW view_name [(列1, 列2, ...)] AS select_statement [WITH CHECK OPTION];OR REPLACE 表示如果视图存在就替换避免先 DROP 再 CREATE 的麻烦。后面的列名列表可写可不写不写的话直接用 SELECT 里的列名。我们来做一个最典型的例子。假设有客户表和订单表业务上需要一个“订单汇总”查询CREATE OR REPLACE VIEW v_order_summary AS SELECT o.id AS order_id, c.name AS customer_name, c.phone, o.status, o.total_amount, o.created_at FROM orders o JOIN customers c ON c.id o.customer_id WHERE o.status 4;创建之后你要查某个客户的全部有效订单直接SELECT * FROM v_order_summary WHERE customer_name 张三;这个视图把 JOIN 条件和过滤逻辑都封装好了应用侧永远只需要面对一个扁平结构。自己在本地库动手试的时候建议先用一个小库把两三个表建出来再建视图验证一遍比直接看语法印象深刻得多。2.2 “创建视图权限不足”排查一次权限问题热搜词里的“创建视图权限不足”非常典型。MySQL 里创建视图需要两类权限全局或库级的 CREATE VIEW 权限对视图定义中引用的所有基表以及函数、存储过程的 SELECT 权限也就是说就算你被允许查 orders 表但你所在的账号没有 CREATE VIEW 权限照样建不了视图。授权通常是 DBA 来做命令长这样GRANT CREATE VIEW ON shop.* TO app_user%; GRANT SELECT ON shop.orders TO app_user%; GRANT SELECT ON shop.customers TO app_user%; FLUSH PRIVILEGES;还有一个容易被忽略的权限点视图定义里的 DEFINER。MySQL 视图有一个 definer 属性表示“以哪个用户身份来执行视图里的查询”。如果你是用 root 创建的视图但业务账号只有 SELECT 权限只要业务账号有视图本身的 SELECT 权限一般也能查询成功但如果业务账号连视图的 SELECT 权限都没有就会直接报没有权限。排查这类问题时顺序是先确认账号有没有视图的 SELECT 权限再确认视图的 DEFINER最后确认基表权限。2.3 可更新视图与 WITH CHECK OPTION别让视图“装”不下数据视图能不能 INSERT、UPDATE、DELETE答案是可以但有严格限制。只有满足下面这些条件的视图才叫“可更新视图”FROM 子句只引用一个表没有多表 JOIN查询里没有 DISTINCT、GROUP BY、聚合函数、UNION、子查询视图的列不是常量或表达式一旦你的视图里含有 JOIN、聚合、DISTINCT它就只能查询不能更新。我见过不少人把多表 JOIN 视图当成更新入口一执行 UPDATE 就报错这是复习阶段最容易踩的坑。即使视图可更新默认情况下你通过视图插入的数据是可以“跳出”视图范围的。举个例子CREATE OR REPLACE VIEW v_pending_orders AS SELECT id, customer_id, status, total_amount, created_at FROM orders WHERE status 0;这个视图只显示待支付订单。如果你往里面插入一条 status 1 的记录没有 WITH CHECK OPTION 的话插入会成功但这条记录在视图里根本看不到造成“通过视图操作但数据不在视图可见范围内”的混乱。加上 WITH CHECK OPTION 之后数据库强制校验插入或更新的数据必须满足视图 WHERE 条件不满足就报错。CREATE OR REPLACE VIEW v_pending_orders AS SELECT id, customer_id, status, total_amount, created_at FROM orders WHERE status 0 WITH CHECK OPTION;这个特性在分渠道审核、状态机流转这类业务里非常实用相当于数据库层面给你加了一道规则约束。2.4 视图能加快查询速度吗普通视图与物化视图回到那个高频问题。普通视图也就是直接保存 SELECT 语句的视图查询时数据库仍然要执行底层 SQL所以从性能角度讲它几乎不会加快查询甚至因为多一层 SQL 改写复杂视图还可能更慢。如果你的视图查得慢本质是基层 SQL 慢优化方法是去优化基表的索引和 SQL而不是给视图本身动刀。真正能加速的是物化视图。物化视图会把查询结果预先计算好、物理存储下来查询直接读结果速度飞快。物化视图的代价是占用真实的存储空间而且需要刷新策略ON COMMIT 或定期刷新来保证数据不陈旧。这是 Oracle 和 PostgreSQL 支持的功能MySQL 8.0 原生没有物化视图只能靠定时任务预计算到实体表来模拟。复习阶段把这个区别记清楚考试题里“视图能否加速查询”这类判断题就稳了。3. 索引创建策略选类型、定字段、控空间3.1 索引类型选型对照创建索引之前先想清楚用哪种索引不同索引的约束和适用场景差别很大索引类型关键字特点典型场景主键索引PRIMARY KEY一个表只能有一个InnoDB 中数据按主键顺序组织每张业务表的唯一标识唯一索引UNIQUE列值不能重复可加速等值查询手机号、身份证、订单号普通索引INDEX只加速查询无唯一约束经常出现在 WHERE 和 JOIN 条件里的列组合索引INDEX(a,b,c)多列组合遵循最左前缀多条件组合查询全文索引FULLTEXT支持文本关键词搜索文章内容、搜索词库选型时最忌讳的是“看到哪列出现在 WHERE 里就给哪列建普通索引”。如果查询条件总是 multi-column 组合建多个单列索引往往不如建一个组合索引因为优化器通常只会选其中一个最优索引剩下的列还得回表过滤。3.2 创建索引的两种常用写法MySQL 里创建索引有两种等价写法CREATE INDEX idx_status ON orders(status); ALTER TABLE orders ADD INDEX idx_status (status);要建唯一索引在 INDEX 前加 UNIQUE。如果你还要设置前缀长度针对长字符串列可以写成ALTER TABLE orders ADD INDEX idx_phone (phone(11));这种写法只会把 phone 前 11 个字符放进索引能明显节省索引空间。另外 MySQL 8.0 还支持隐藏索引标记为 INVISIBLE 的索引优化器默认不采用适合你需要删除一个索引但又不敢直接删的场景可以先把索引隐藏观察一段时间没有性能回退再彻底删除。ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;这个功能在生产环境里非常实用属于“软删除索引”的安全操作。3.3 组合索引与最左前缀规则组合索引是索引设计里的重头戏。假设你有一张订单表业务查询经常按 customer_id 和 status 两个条件同时过滤就可以建立ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status);这条组合索引能加速以下查询WHERE customer_id 100WHERE customer_id 100 AND status 1ORDER BY customer_id但以下查询用不上这个组合索引WHERE status 1跳过了左边的 customer_idWHERE status 1 AND customer_id 100顺序反了优化器一般会调整顺序MySQL 8.0 在某些条件下会出现 Index Skip Scan索引跳跃扫描允许跳过最左列直接使用第二列但它的效率依赖数据分布不能当默认性能保障来依赖。复习时记住一句话“联合索引的组合顺序永远把区分度最高、最常用于等值筛选的列放最前面。”3.4 索引表空间InnoDB 里索引到底存在哪索引表空间这个概念初学的人容易被绕晕。InnoDB 的存储结构是聚簇索引组织表数据本身按照主键聚簇索引的顺序存放在表空间里每张表的二级索引是独立的 B 树树的叶子节点存储的是主键值而不是指向行的物理地址。所以主键索引的叶子节点存的是整行数据主键索引就是数据的物理存储结构二级索引的叶子节点存的是主键值通过二级索引查询数据时先找到主键再回表去主键索引里取整行MySQL 默认开启独立表空间每张表对应一个 .ibd 文件数据和索引都存放在里面。要查一张表的数据量和索引量可以看SELECT TABLE_NAME, ROUND((DATA_LENGTH INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb, ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb, ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA shop AND TABLE_NAME IN (orders, customers);如果需要更细的索引级别统计MySQL 8.0 可以查 mysql.innodb_index_statsSELECT index_name, stat_name, stat_value FROM mysql.innodb_index_stats WHERE table_name orders AND stat_name size;生产环境里如果一个表的索引占的空间甚至比数据还大就要警惕了要么索引建太多要么字段过长没做前缀索引。4. 索引执行细节回表、锁窗口与覆盖索引4.1 二级索引回表的完整路径一条 SQL 走二级索引时数据库内部其实做了两步。比如SELECT id, name, phone FROM customers WHERE phone 13800001111;如果 phone 上有二级索引执行器会在 phone 的 B 树里定位到 13800001111 这个叶子节点拿到对应的主键 id然后拿着这个 id 去主键索引聚簇索引里再查一次整行。这个过程就叫回表。回表不是免费的每一条匹配记录都要额外一次“主键查找”。如果查询命中了 100 条记录就要回表 100 次。于是就有了覆盖索引的概念如果二级索引的叶子节点里已经包含了查询需要的所有列那就不用回表了。比如上面的 SQL 只查 id、name、phone如果建立索引 (phone, name)此时索引里既有 phone 又有 nameid 又是主键查询所需的三个列都能从索引页拿到执行计划 Extra 里会显示 Using index性能就快很多。4.2 通过二级索引更新时的锁顺序与死锁窗口热搜词里那句话值得反复琢磨“MySQL 通过二级索引更新时先锁二级索引项再回表锁主键这个时间窗口容易形成交叉。”我先解释什么是锁顺序问题。InnoDB 的行锁加在索引记录上。你用一条 UPDATE 更新数据如果执行计划走的是二级索引InnoDB 会先给命中的二级索引记录加锁再去主键索引里给对应行加锁。如果两个事务都做更新但各自加锁的顺序相反就可能出现互相等待事务 A通过二级索引 phone 定位到记录先锁 phone 索引项再回表锁主键 id10事务 B通过主键 id10 更新同一条记录先锁主键 id10再去更新 phone 列需要锁 phone 索引项A 持有 phone 索引锁等着 id 主键锁B 持有 id 主键锁等着 phone 索引锁两个事务就死锁了。MySQL 检测到死锁后会回滚其中一方错误代码是 1213。面对这类死锁常见处理思路有三种保持加锁顺序一致。比如业务代码里统一先查主键、再通过主键更新让所有 UPDATE 都从聚簇索引开始加锁缩小事务范围不要在一个大事务里做大量跨索引更新合理设计索引更新语句尽量走唯一索引或主键减少二级索引上的锁等待这个知识点在小公司日常开发里不容易遇到但几乎一定出现在面试问“你怎么排查死锁”这个环节。能把二级索引先锁、再回表锁主键这个时间窗口讲清楚面试官就知道你是真跑过生产问题的人。4.3 覆盖索引让查询绕过回表覆盖索引的设计目标是“查询列全部包含在索引中”。回到订单场景假设联合索引是 (customer_id, status, created_at)EXPLAIN SELECT customer_id, status, created_at FROM orders WHERE customer_id 100 AND status 1;因为查询的列恰好都在联合索引里执行计划会显示 Using index不需要回表。但如果 SELECT 里多一个 total_amount联合索引里没有这个列数据库就必须回表取整行Extra 里就不会出现 Using index而会显示 Using index condition 或者干脆只有 Where。所以设计覆盖索引时一个惯用思路是把查询中高频出现的、区分度适中、长度可控的列有选择地放进组合索引里让常用 SQL 走“索引全包”的路径。不过要注意不要为了覆盖索引把所有列都塞进索引否则索引体积膨胀写入性能下降得很厉害。4.4 索引失效不是玄学最常见的 5 个场景索引建了但查询还是慢通常就是索引失效。下面这些场景是复习考试和实际排障里最高频的场景示例失效原因对索引列使用函数WHERE DATE(created_at) 2024-01-01created_at 的值被函数改写B 树无法按原值定位隐式类型转换WHERE phone 13800001111phone 是字符串列传了数字MySQL 会隐式转换LIKE 左模糊WHERE name LIKE %张%无法利用树的顺序定位OR 连接非索引列WHERE id 1 OR status 0优化器没法把两条分支都用索引合并常退化为全表扫描对索引列做计算WHERE total_amount * 0.9 100列本身参与表达式运算其中隐式类型转换是线上最容易出现的问题。字符串列与数字常量比较时MySQL 会把字符串转成数字索引列的原始值被转换后就无法命中索引。排查方式很简单执行 EXPLAIN 时看 key 列是不是空type 是不是 ALL。5. 实操复盘订单场景下的视图与索引改造5.1 场景建模与初始 DDL光背理论没用我建议你自己建一张订单表完整走一遍。下面是一个极简但完整的场景CREATE DATABASE IF NOT EXISTS shop; USE shop; CREATE TABLE customers ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, phone VARCHAR(20) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB; CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_id INT NOT NULL, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已发货 3已完成 4已取消, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ) ENGINEInnoDB;再往里插入一些测试数据数据量不需要很大几千行就能看出 EXPLAIN 的差别。5.2 用视图统一多表查询销售和客服需要同时看到客户信息和订单信息不能让他们直接操作两张表创建视图CREATE OR REPLACE VIEW v_order_summary AS SELECT o.id AS order_id, c.name AS customer_name, c.phone, o.status, o.total_amount, o.created_at FROM orders o JOIN customers c ON c.id o.customer_id WHERE o.status 4;这个视图把 JOIN 条件、状态过滤都封装好了。业务账号只需要拿一个 SELECT 权限就能做日常查询。注意因为这里涉及多表 JOIN这个视图是不可更新的所以报表人员只能查不能改这正好符合我们对权限最小化的预期。查询视图SELECT * FROM v_order_summary WHERE customer_id 2;细心的你会发现问题视图定义里没有暴露 customer_id但 SELECT 里却能直接筛。这是因为 MySQL 会做视图合并把视图定义 SQL 与外部 SQL 合并成一条底层 SQL 执行外部 WHERE 能直接推到基表上。这也是复习时理解视图性能的关键能合并的视图普通视图性能损耗其实很小。5.3 EXPLAIN 定位慢查询加组合索引假设业务方反馈“查某个客户某个状态的订单很慢”我们先执行 EXPLAINEXPLAIN SELECT * FROM orders WHERE customer_id 2 AND status 1;在没加组合索引之前执行计划大概率是------------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | ALL | fk_customer | NULL | NULL | NULL | 5000 | 10.00 | Using where | ------------------------------------------------------------------------------------------------type 是 ALL说明全表扫描。接着我们创建组合索引ALTER TABLE orders ADD INDEX idx_customer_status_created (customer_id, status, created_at);再执行一次 EXPLAIN-------------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | -------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | ref | idx_customer_status_created | idx_customer_status_created | 4 | const | 20 | 33.33 | Using index condition | --------------------------------------------------------------------------------------------------------------------------------------------------type 从 ALL 变成 refkey 变成了新索引rows 从 5000 降到 20这个量级的差距在真实大数据量下就是毫秒和秒的区别。5.4 用覆盖索引进一步压榨性能再看一个高频统计场景运营要看“某个客户已经支付和已发货的订单数”。如果只查三个列EXPLAIN SELECT customer_id, status, created_at FROM orders WHERE customer_id 2 AND status IN (1, 2);因为查询列全部在联合索引 idx_customer_status_created 中执行计划会出现 Using index也就是覆盖索引扫描连回表都省掉了。实际业务中覆盖索引对报表类查询的提升非常明显一个 300ms 的查询降到 3ms 往往就是这样一个改造。但压榨性能要适可而止。如果索引列太多、太长插入和更新成本反而会把收益吞掉所以要记录线上慢查询只看真正高频的 SQL不要“面面俱到”。6. 复习题速记题库向6.1 基础概念自测标题里既然带着“题库”那这一节就按复习自测题来收尾。先尝试自己默写再看答案。视图和物化视图的核心区别是什么创建视图需要哪些权限哪些视图是可更新视图普通视图为什么不加快查询速度二级索引的叶子节点存储的到底是什么为什么会有回表联合索引 (a, b, c) 能否加速 WHERE b ? 和 WHERE c ? 的查询什么是覆盖索引它的判断依据是什么什么是最左前缀原则在 InnoDB 中主键索引和二级索引在存储结构上有何不同什么是死锁二级索引更新时的锁顺序为什么容易引发死锁简要参考答案视图保存查询逻辑物化视图保存查询结果创建视图需要 CREATE VIEW 权限和基表 SELECT 权限单表、无聚合、无 DISTINCT、无 UNION 的视图才可更新普通视图不存储数据每次仍执行底层 SQL二级索引叶子存储主键值查到主键后还要到聚簇索引取整行所以回表b 和 c 单独查询默认用不上除非 MySQL 8.0 的 skip scan覆盖索引指索引包含查询所需全部列EXPLAIN 出现 Using index联合索引从左到右查询条件必须包含最左列才有效主键索引叶子存整行二级索引叶子存主键两个事务以相反顺序加锁二级索引项和主键项时形成循环等待。6.2 综合场景题有一段 SQL 经常慢SELECT id, customer_id, status FROM orders WHERE customer_id 1001 AND created_at 2025-01-01 ORDER BY created_at DESC;当前索引只有主键。请问你会建什么索引加上索引后这条 SQL 能否避免回表如果业务还要在 WHERE 里加一个 status 1索引顺序需不需要调整参考答案建议建 (customer_id, created_at, status) 组合索引。查询列 id、customer_id、status 都在这条索引或主键里所以能覆盖避免回表。如果加入 status 1可以把顺序设计为 (customer_id, status, created_at)这样既能等值过滤 status又能用 created_at 做范围和排序。这个场景就是“等值字段放前范围和排序字段放后”的典型运用。6.3 实际排障思路清单真到了生产环境排查别一上来就瞎猜。我自己常用的排查顺序是先看慢查询日志拿到具体的慢 SQL用 EXPLAIN 看执行计划确认 type、key、rows 三个字段对比 possible_keys 和 key判断优化器有没有选错索引确认查询条件里有没有函数、隐式转换、左模糊这类索引失效写法确认索引的区分度比如一个 status 列只有 0、1、2 三种值单独建索引意义不大如果索引没问题再考虑是否统计信息过期执行 ANALYZE TABLE 刷新统计信息最后才考虑加索引、改 SQL、或者用覆盖索引去优化这套流程能覆盖绝大多数“SQL 慢”的问题。复习的时候把它背下来考试写步骤题可以直接套用。我个人复习这个章节时最大的体会是不要拿着概念背一定要在真实表上建视图、做 EXPLAIN。把一次慢查询从 200 毫秒优化到 2 毫秒之后你对索引的理解会比看十遍书都深。另外一个实操小技巧是所有 DDL 先在一个测试环境执行一遍记录执行前和执行后的执行计划差异然后保存到你的笔记里这比任何题库都更有用。到面试的时候你能把二级索引回表、死锁时间窗口、覆盖索引这三件事串成一个完整故事讲出来这一章就算真正过关了。