开发者空间里玩转MySQL之视图与索引

1 概述

1.1 案例介绍

数据库操作是软件开发的基础核心，MySQL凭借其高效稳定、易用可靠的特点，广泛应用于电商、社交、企业系统等各类业务场景，是开发者必备的关键技能。

通过MySQL查询案例，开发者可学习查询使用、复杂SQL编写（如多表连接、子查询），提升数据库设计与高效查询能力。

1.2 适用对象

• 个人开发者
• 高校学生

1.3 案例时间

本案例总时长预计40分钟。

1.4 案例流程

{{{width="50%" height="auto"}}}

说明：

1. 领取空间开发桌面；
2. 在空间开发桌面终端进入Mysql；
3. 进行数据库的开发者空间进行MySQL的视图与索引操作；

1.5 资源总览

本案例预计花费总计0元。

资源名称	规格	单价（元）	时长（分钟）
华为开发者空间 – 云主机	X86| 2 vCPUs 4GB | Ubuntu 22.04 Server 定制版	0	40

2 数据视图操作

视图(View)是MySQL中的一种虚拟表，其内容由查询定义。视图并不实际存储数据，而是基于一个或多个基表(或视图)的查询结果集。

2.1 视图及其作用

2.1.1 视图的主要作用

• 简化复杂查询：将复杂的SQL查询封装成视图，简化应用层代码
• 数据安全：通过视图限制用户访问基表的敏感列
• 逻辑独立性：应用程序可以不受基表结构变化的影响
• 数据整合：将分散在多个表中的数据整合到一个视图中
• 权限控制：为不同用户提供不同的数据视图

2.1.2 视图特点

• 视图不存储实际数据，只存储定义
• 视图可以嵌套使用（基于其他视图创建）
• 视图查询会被转换为对基表的查询执行
• 某些视图是可更新的（满足特定条件）

2.2 创建视图

视图创建标准语法

CREATE [OR REPLACE] 
       [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
       VIEW 视图名 [(列名1, 列名2, ...)]
       AS 查询语句
       [WITH [CASCADED | LOCAL] CHECK OPTION];

语法要素说明：

语法组件	可选性	可选值/说明
CREATE [OR REPLACE]	可选	新建或替换已有视图
ALGORITHM =	可选	UNDEFINED(默认)/MERGE(合并查询)/TEMPTABLE(临时表模式)
VIEW 视图名	必选	遵循标识符命名规则
[(列名列表)]	可选	为视图列指定别名，数量必须与查询结果列一致
AS 查询语句	必选	有效的SELECT语句，可包含JOIN/WHERE等子句
WITH CHECK OPTION	可选	CASCADED(级联检查)/LOCAL(本地检查)，确保数据修改符合视图定义的条件

创建语句示例：

-- 学生基本信息视图（简单视图）
CREATE VIEW student_basic_info AS
SELECT 
    s.student_id, u.username, u.real_name, s.age, s.gender, s.department, s.class, s.enrollment_date
FROM 
    student s
JOIN 
user u ON s.user_id = u.user_id;

-- 课程详情视图（带聚合函数），课程的详细信息，包括教师信息和计算出的选课率
CREATE VIEW course_details AS
SELECT 
    c.course_id, c.course_name, c.credits, c.course_capacity, c.current_enrollment, CONCAT(FORMAT((c.current_enrollment/c.course_capacity)*100, 2), '%') AS enrollment_rate, t.real_name AS teacher_name,  t.title AS teacher_title, c.semester, c.college_name, c.description
FROM 
    course c
JOIN 
    teacher t ON c.teacher_id = t.teacher_id
JOIN 
    user u ON t.user_id = u.user_id; 

-- 学生选课成绩视图（多表连接），学生的选课记录和成绩，包括成绩等级转换
CREATE VIEW student_course_grades AS
SELECT 
    s.student_id, u.real_name AS student_name,  s.department AS student_department,
    s.class AS student_class, c.course_id, c.course_name, c.credits, ut.real_name AS teacher_name, e.semester, e.grade,
    CASE 
        WHEN e.grade >= 90 THEN 'A'
        WHEN e.grade >= 80 THEN 'B'
        WHEN e.grade >= 70 THEN 'C'
        WHEN e.grade >= 60 THEN 'D'
        ELSE 'F'
    END AS grade_level,
    e.enrollment_date
FROM 
    enrollment e
JOIN 
    student s ON e.student_id = s.student_id
JOIN 
    user u ON s.user_id = u.user_id
JOIN 
    course c ON e.course_id = c.course_id
JOIN 
    teacher t ON c.teacher_id = t.teacher_id
JOIN 
    user ut ON t.user_id = ut.user_id; 

-- 教师授课统计视图（分组聚合），统计每位教师的授课情况，包括课程数、学生总数等
CREATE VIEW teacher_course_stats AS
SELECT 
    t.teacher_id, u.real_name AS teacher_name, t.title, t.department, COUNT(c.course_id) AS total_courses, SUM(c.current_enrollment) AS total_students, AVG(c.current_enrollment) AS avg_students_per_course, GROUP_CONCAT(DISTINCT c.semester ORDER BY c.semester SEPARATOR ', ') AS teaching_semesters
FROM 
    teacher t
JOIN 
    user u ON t.user_id = u.user_id
LEFT JOIN 
    course c ON t.teacher_id = c.teacher_id
GROUP BY 
    t.teacher_id, u.real_name, t.title, t.department;

-- 热门课程视图（带排序和限制），选课率最高的10门课程
CREATE VIEW popular_courses AS
SELECT 
    c.course_id, c.course_name, c.credits, u.real_name AS teacher_name, c.current_enrollment, c.course_capacity, CONCAT(FORMAT((c.current_enrollment/c.course_capacity)*100, 2), '%') AS enrollment_rate, c.semester, c.college_name
FROM 
    course c
JOIN 
    teacher t ON c.teacher_id = t.teacher_id
JOIN 
    user u ON t.user_id = u.user_id
WHERE 
    c.current_enrollment > 0
ORDER BY 
    (c.current_enrollment/c.course_capacity) DESC
LIMIT 10; 

-- 学生学分统计视图（复杂计算），计算学生的总学分、已获学分、平均成绩等统计信息
CREATE VIEW student_credit_summary AS
SELECT 
    s.student_id, u.real_name AS student_name, s.department, s.class, COUNT(e.course_id) AS total_courses, SUM(c.credits) AS total_credits, SUM(CASE WHEN e.grade >= 60 THEN c.credits ELSE 0 END) AS earned_credits, AVG(e.grade) AS average_grade, MIN(e.grade) AS lowest_grade, MAX(e.grade) AS highest_grade
FROM 
    student s
JOIN 
    user u ON s.user_id = u.user_id
LEFT JOIN 
    enrollment e ON s.student_id = e.student_id
LEFT JOIN 
    course c ON e.course_id = c.course_id
GROUP BY 
s.student_id, u.real_name, s.department, s.class;

-- 学院课程统计视图（分组统计），统计课程数量、学生数量和学分情况
CREATE VIEW college_course_stats AS
SELECT 
    college_name, COUNT(course_id) AS total_courses, SUM(current_enrollment) AS total_students, AVG(credits) AS avg_credits, SUM(credits * current_enrollment) AS total_credit_hours
FROM 
    course
GROUP BY 
    college_name;

操作结果：

注意事项：

• 列名列表需与查询结果列数严格对应
• TEMPTABLE算法视图不可更新
• 使用WITH CHECK OPTION时，所有数据修改必须满足视图的WHERE条件
• 这种结构既保持了与SELECT语句相似的格式规范，又通过表格形式清晰展示了各语法组件的属性和约束条件

2.3 查询视图

查看数据库所有视图：

SHOW FULL TABLES WHERE Table_type = 'VIEW';

基本查询，查询视图与查询普通表语法相同：

SELECT * FROM [view_name] 
[WHERE condition] 
[ORDER BY column] 
[LIMIT number];

查看视图定义：

SHOW CREATE VIEW [view_name];

如：
SHOW CREATE VIEW student_basic_info;

视图查询执行过程：

1、解析器检查视图是否存在

2、获取视图定义并替换视图引用

3、优化器处理合并后的查询

4、执行引擎执行最终查询

语句示例（根据已有视图）：

-- 查询某学生的所有课程成绩
SELECT course_name, grade, grade_level 
FROM student_course_grades 
WHERE student_id = '2120010116';

操作结果：

-- 查询教师授课统计（按学生总数降序）
SELECT teacher_name, title, total_courses, total_students
FROM teacher_course_stats
ORDER BY total_students DESC;
-- 查询学分未达标学生（假设本学期选修课学分要求大于4分）
SELECT student_name, department, total_credits
FROM student_credit_summary
WHERE total_credits < 4
ORDER BY total_credits ASC;

操作结果：

-- 查询各学院课程平均学分：
SELECT college_name, avg_credits
FROM college_course_stats
ORDER BY avg_credits DESC;

操作结果：

-- 查询选课率超过80%的课程（使用计算列条件）
SELECT course_id, course_name, enrollment_rate
FROM course_details
WHERE enrollment_rate > '80.00%';

操作结果：

性能考虑：

• 视图查询最终转换为对基表的查询，性能取决于基表结构和索引
• 复杂视图可能导致性能问题，应避免多层嵌套
• 使用EXPLAIN分析视图查询执行计划

EXPLAIN SELECT * FROM [employee_basic_view];

2.4 更新视图

2.4.1 可更新视图

可更新视图是指允许通过视图对基表执行INSERT、UPDATE和DELETE操作的视图。根据MySQL规范，视图必须满足以下条件才能被更新：

• 视图必须基于单个基表（不能是多表连接或联合查询）；
• 必须包含基表中所有没有默认值的 NOT NULL 列；
• 不能包含以下元素：聚合函数（如 SUM, COUNT），DISTINCT 关键字，GROUP BY 或 HAVING 子句，UNION 或 UNION ALL，子查询，某些连接操作；

查看所有视图信息：

SELECT table_name AS 视图名称,
       is_updatable AS 是否可更新,
       check_option AS 检查选项,
       definer AS 定义者
FROM information_schema.views
WHERE table_schema = DATABASE()
ORDER BY table_name;

检查指定视图是否可更新：

SELECT 
    TABLE_NAME AS view_name,
    IS_UPDATABLE
FROM 
    INFORMATION_SCHEMA.VIEWS
WHERE 
    TABLE_SCHEMA = '你的数据库名'
AND TABLE_NAME = '要检查的视图名';

实际视图是否能用于更新还需要具体分析视图的情况，有几个条件：

• 单表视图：基于单表的简单视图通常可更新，多表连接视图即使显示为可更新，实际操作可能失败
• 列限制：不能更新视图中不包含的基表列（特别是没有默认值的 NOT NULL 列）
• 算法限制：使用 TEMPTABLE 算法的视图不可更新（即使显示为可更新）
• 权限问题：用户需要有基表的 UPDATE 权限

2.4.2 插入数据

语法示例：

INSERT INTO view_name [(column_list)] VALUES (value_list);

语句示例：

-- 创建可插入数据的简单视图
CREATE VIEW `user_insert_view` AS
SELECT 
    user_id,username, real_name, password, role_id
FROM `user`
WHERE 1=0; -- 这个条件确保视图不返回任何数据，只用于插入

-- 使用视图插入数据示例
INSERT INTO `user_insert_view` (user_id, username, real_name, password, role_id) 
VALUES ('u001', 'student1', '张三', 'hashed_password', 2);

操作结果：

注意事项：

• 插入的数据必须满足视图定义中的WHERE条件（如果使用了WITH CHECK OPTION）
• 不能插入基表中NOT NULL但未包含在视图中的列
• 自动递增列需要特殊处理

2.4.3 修改数据

语法示例：

UPDATE view_name 
SET column1 = value1, column2 = value2, ...
[WHERE condition];

语句示例：

-- student_basic_info 视图（可更新）
-- 更新前
SELECT * FROM student WHERE student_id = '2120030104';

-- 更新
UPDATE student_basic_info
SET age = 22
WHERE student_id = '2120030104';

-- 更新后
SELECT * FROM student WHERE student_id = '2120030104';
-- 注意：此视图基于student和user表的连接，但MySQL只允许更新单表视图，实际上只能更新student表或user表中的数据，不能同时更新两个表

-- 不可更新视图及其原因
-- course_details 视图（不可更新）
-- 原因：包含聚合函数(FORMAT)和计算列(enrollment_rate)，无法直接更新，需要更新基表course

-- student_course_grades 视图（不可更新）
-- 原因：多表连接且包含计算列(grade_level)，无法直接更新，需要更新基表enrollment

-- teacher_course_stats 视图（不可更新）
-- 原因：包含GROUP BY和聚合函数(COUNT, SUM, AVG, GROUP_CONCAT)，无法直接更新，需要更新基表teacher或course

-- popular_courses 视图（不可更新）
原因：包含计算列(enrollment_rate)和LIMIT子句，无法直接更新，需要更新基表course

-- student_credit_summary 视图（不可更新）
原因：包含GROUP BY和多个聚合函数，无法直接更新，需要更新基表enrollment

-- college_course_stats 视图（不可更新）
原因：包含GROUP BY和聚合函数，无法直接更新，需要更新基表course

操作结果：

注意事项：

• 更新操作必须满足视图定义条件
• 一次只能更新一个基表的列
• 不能更新计算列或聚合列
• 大多数情况下，应该直接更新基表而不是通过视图更新数据，特别是对于复杂的多表视图

2.4.4 删除数据

语法示例：

DELETE FROM view_name [WHERE condition];

语句示例：

MySQL限制连接视图的删除，因此删除语句需要根据实际视图的结构进行判断删除顺序：

-- 直接删除会报错
DELETE FROM student_basic_info WHERE student_id = '1998098902';
-- ERROR 1395 (HY000): Can not delete from join view 'university.student_basic_info'

注意事项：

• 如果视图包含JOIN，可能无法删除（不能直接从多表连接视图中删除数据）
• 只能删除满足视图定义的行
• 级联删除规则会影响操作结果

2.4.5 复杂场景示例

带WITH CHECK OPTION的视图：

-- 创建视图
CREATE OR REPLACE VIEW cs_students AS
SELECT student_id, user_id, age, gender, department, class
FROM student
WHERE department = '计算机科学学院'
WITH CHECK OPTION;

-- 插入user表数据
INSERT INTO `user` (`user_id`, `username`, `real_name`, `password`, `role_id`, `created_at`) VALUES
(1811118899, 'stu_qin舞yang', '秦舞阳', 'IxR4XoYd', 3, '2018-09-01 08:00:00');

-- 视图插入
INSERT INTO cs_students (student_id, user_id, age, gender, department, class)
VALUES ('S1002', '1811118899', 24, 'F', '数学学院', '数学1班');

多表连接视图的局限性

-- 此视图不可更新（基于多表连接）
CREATE VIEW student_course_info AS
SELECT s.student_id, u.real_name, c.course_name, e.grade
FROM student s
JOIN user u ON s.user_id = u.user_id
JOIN enrollment e ON s.student_id = e.student_id
JOIN course c ON e.course_id = c.course_id;

-- 尝试更新多个表的字段（将失败）
UPDATE student_course_info 
SET real_name = '李四', course_name = '高级数据库' 
WHERE student_id = 'S001';

2.4.6 实际使用注意事项

• 列完整性：视图必须包含基表中所有没有默认值的NOT NULL列；如果基表有自增列，视图可以不包含它
• 计算列和表达式：包含计算列或表达式的视图通常不可更新；
• 权限要求：用户必须对基表有 INSERT 权限；对视图的权限不足以执行插入操作
• WITH CHECK OPTION：使用 WITH CHECK OPTION 可以确保通过视图插入的数据满足视图定义的条件
• 约束/触发器行为：基表上的触发器在通过视图插入数据时仍会触发；外键约束的级联操作会正常执行
• 默认值处理：如果基表列有默认值，视图可以不包含该列；插入时会自动使用默认值
• 视图算法影响：ALGORITHM=TEMPTABLE 的视图通常不可更新；可更新视图通常使用 ALGORITHM=MERGE
• 性能影响：通过视图操作数据比直接操作表有额外开销，大数据量操作建议直接使用基表：对于复杂插入逻辑，考虑使用存储过程替代
• 实践： 明确测试视图的插入功能，不要假设所有视图都可更新

2.5 修改视图定义

基本语法：

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

或者使用 ALTER VIEW 语法：

ALTER VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

适用场景：

• 需求变更：业务需求变化，需要调整视图逻辑
• 性能优化：优化视图查询性能，例如添加索引提示
• 结构调整：增加或删除视图中的列
• 权限调整：通过视图限制用户访问的数据范围

语句示例：

-- 修改 student_basic_info 视图，增加学生邮箱字段
CREATE OR REPLACE VIEW student_basic_info AS
SELECT 
    s.student_id,
    u.username,
    u.real_name,
    u.created_at,  -- 新增字段
    s.age,
    s.gender,
    s.department,
    s.class,
    s.enrollment_date
FROM 
    student s
JOIN 
user u ON s.user_id = u.user_id;

-- 修改 course_details 视图，调整选课率计算逻辑
ALTER VIEW course_details AS
SELECT 
    c.course_id,
    c.course_name,
    c.credits,
    c.course_capacity,
    c.current_enrollment,
    CONCAT(FORMAT((c.current_enrollment/GREATEST(c.course_capacity, 1))*100, 2), '%') AS enrollment_rate,  -- 防止除零错误
    u.real_name AS teacher_name,
    t.title AS teacher_title,
    c.semester,
    c.college_name,
    c.description
FROM 
    course c
JOIN 
    teacher t ON c.teacher_id = t.teacher_id
JOIN 
    user u ON t.user_id = u.user_id;

操作结果：

注意事项：

• 依赖关系：修改视图可能影响依赖该视图的存储过程、函数或其他视图
• 权限检查：需要具有相应的权限（CREATE VIEW 和 DROP 权限）
• 数据一致性：确保修改后的视图逻辑不会导致数据不一致
• 备份：修改前备份视图定义（SHOW CREATE VIEW）

2.6 删除视图

基本语法

DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE];

示例

DROP VIEW IF EXISTS user_insert_view;

注意事项

• 删除视图不会影响基表数据
• 检查是否有其他对象依赖该视图
• 在事务中删除视图可以回滚
• 考虑权限影响，删除后需要重新授权

3 表索引操作

索引是数据库中一种特殊的数据结构，它类似于书籍的目录，能够帮助数据库系统快速定位到表中的特定数据。

3.1 索引及其作用

作用：

• 加速数据检索：显著提高SELECT查询速度
• 优化排序操作：加速ORDER BY、GROUP BY操作
• 保证数据唯一性：唯一索引确保列值的唯一性
• 加速表连接：提高多表连接查询性能

应用场景：

• 频繁作为查询条件的列
• 经常用于表连接的列
• 经常需要排序或分组的列
• 具有高选择性的列（列中不同值多）

3.2 创建索引

3.2.1 使用CREATE INDEX语句创建索引

语法示例：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名
    [USING {BTREE | HASH}]
    ON 表名 (列名1 [(长度)], 列名2 [(长度)], ...)
    [ALGORITHM = {DEFAULT | INPLACE | COPY}]
    [LOCK = {DEFAULT | NONE | SHARED | EXCLUSIVE}]
    [COMMENT '注释内容']
[VISIBLE | INVISIBLE];

参数说明：

参数	作用	允许值
索引类型	决定索引的性质（唯一、全文、空间等）	UNIQUE：创建唯一索引，确保索引列的值唯一 FULLTEXT：创建全文索引，用于全文搜索 SPATIAL：创建空间索引，用于地理空间数据类型 默认：创建普通索引
索引名	自定义名称，需在表内唯一。	自定义
索引方法	选择索引的存储结构（B+树或哈希）。	USING BTREE：使用B+树索引（默认） USING HASH：使用哈希索引（仅适用于MEMORY表）
表名	指定要创建索引的表。	按需
列名	指定要索引的列，支持单列或多列（复合索引），字符串列可指定前缀长度。	(长度)：指定前缀索引的长度（仅适用于字符串类型）
算法选项	选择创建索引的算法，影响锁表时间和性能。	ALGORITHM = DEFAULT：默认算法，由MySQL自动选择 ALGORITHM = INPLACE：在线创建索引，尽量减少锁表时间 ALGORITHM = COPY：复制表数据并重建索引，适用于大表
锁选项	控制创建索引时的锁级别，影响并发操作。	LOCK = DEFAULT：默认锁级别，由MySQL自动选择 LOCK = NONE：无锁，允许并发读写 LOCK = SHARED：共享锁，允许并发读，禁止写 LOCK = EXCLUSIVE：排他锁，禁止并发读写
注释	为索引添加注释，便于维护。	COMMENT '注释内容'：为索引添加注释，便于维护
可见性	控制索引是否对查询优化器可见，用于测试或逐步替换索引。	VISIBLE：索引可见（默认） INVISIBLE：索引不可见，查询优化器会忽略该索引

应用场景：

• 表已经存在且需要优化查询性能的场景
• 查询条件中同时使用多个列的场景，可创建复合索引
• 较长的字符串列（如地址、描述），可创建前缀索引节省空间
• 可确保列值唯一性的场景，可创建唯一索引确保数据唯一性
• 可支持模糊查询和关键词搜索的场景，可创建全文索引支持文本搜索
• 地图、位置服务等可处理地理空间数据的场景，可创建空间索引优化地理空间数据查询
• 测试新索引效果的场景，可创建不可见索引测试效果

通过 CREATE INDEX 语句，可以在现有表上灵活地创建索引，优化查询性能并满足不同场景。

语句示例：

-- 在 user 表的 username 列上创建普通索引，用于加速登录账号查询：
CREATE INDEX idx_username
    ON `user` (username)
    USING BTREE
COMMENT '用于加速登录账号查询';

-- 在 student 表的 department 列上创建前缀索引（前 10 个字符），用于加速院系查询：
CREATE INDEX idx_department
    ON `student` (department(10))
    USING BTREE
    COMMENT '用于加速院系查询';
-- 在 course 表的 course_name 和 semester 列上创建复合索引，用于加速课程名称和学期查询：
CREATE INDEX idx_course_name_semester
    ON `course` (course_name, semester)
    USING BTREE
    COMMENT '用于加速课程名称和学期查询';
-- 在 enrollment 表的 grade 列上创建降序索引，用于加速成绩排序查询：
CREATE INDEX idx_grade
    ON `enrollment` (grade DESC)
    USING BTREE
COMMENT '用于加速成绩排序查询';

3.2.2 使用ALTER TABLE语句创建索引

语法示例：

ALTER TABLE 表名
    ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名
    [USING {BTREE | HASH}]
    (列名1 [(长度)], 列名2 [(长度)], ...)
    [ALGORITHM = {DEFAULT | INPLACE | COPY}]
    [LOCK = {DEFAULT | NONE | SHARED | EXCLUSIVE}]
    [COMMENT '注释内容']
[VISIBLE | INVISIBLE];

参数说明、应用场景同3.2.1；

通过 ALTER TABLE 语句，可以在现有表上灵活地创建索引，优化查询性能并满足不同场景。

语句示例：

-- 在 teacher 表的 department 列上创建普通索引，用于加速院系查询：
ALTER TABLE `teacher`
    ADD INDEX idx_department (department)
    USING BTREE
COMMENT '用于加速院系查询';

-- 在 course 表的 teacher_id 列上创建索引，用于加速授课教师查询：
ALTER TABLE `course`
    ADD INDEX idx_teacher_id (teacher_id)
    USING BTREE
COMMENT '用于加速授课教师查询';

-- 在 enrollment 表的 student_id 和 course_id 列上创建复合索引，用于加速学生和课程查询：
ALTER TABLE `enrollment`
    ADD INDEX idx_student_course (student_id, course_id)
    USING BTREE
COMMENT '用于加速学生和课程查询';

-- 在 role 表的 role_name 列上创建唯一索引，确保角色类型唯一：
ALTER TABLE `role`
    ADD UNIQUE INDEX idx_role_name (role_name)
    USING BTREE
COMMENT '确保角色类型唯一';

3.2.3 使用CREATE TABLE语句创建索引

语法示例：

CREATE TABLE 表名 (
    列名1 数据类型 [列约束],
    列名2 数据类型 [列约束],
    ...
    [CONSTRAINT 约束名] [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名
    [USING {BTREE | HASH}]
    (列名1 [(长度)], 列名2 [(长度)], ...)
    [COMMENT '注释内容']
    [VISIBLE | INVISIBLE]
);

参数说明：

参数	作用	允许值
索引类型	决定索引的性质（唯一、全文、空间等）。	UNIQUE：创建唯一索引，确保索引列的值唯一 FULLTEXT：创建全文索引，用于全文搜索 SPATIAL：创建空间索引，用于地理空间数据类型 默认：创建普通索引
索引名	自定义名称，需在表内唯一。	自定义
索引方法	选择索引的存储结构（B+树或哈希）。	USING BTREE：使用B+树索引（默认） USING HASH：使用哈希索引（仅适用于MEMORY表）
表名	指定要创建的表。	按需
列名	指定要索引的列，支持单列或多列（复合索引），字符串列可指定前缀长度。	(长度)：指定前缀索引的长度（仅适用于字符串类型）
注释	为索引添加注释，便于维护。	COMMENT '注释内容'：为索引添加注释，便于维护
可见性	控制索引是否对查询优化器可见，用于测试或逐步替换索引。	VISIBLE：索引可见（默认）。 INVISIBLE：索引不可见，查询优化器会忽略该索引

应用场景：

• 创建新表时直接定义索引：在创建表的同时定义索引，避免后续使用 CREATE INDEX 或 ALTER TABLE 语句
• 需要确保列值唯一性的场景：可在创建表时直接定义唯一索引
• 需要支持全文搜索的场景：可在创建表时直接定义全文索引
• 需要处理地理空间数据的场景：可在创建表时直接定义空间索引
• 需要优化查询性能的场景：可在创建表时直接定义普通索引或复合索引
• 需要测试索引效果的场景：可在创建表时定义不可见索引

通过 CREATE TABLE 语句，可以在创建表的同时定义索引，简化后续操作并确保表结构与索引设计的完整性。

语句示例：

-- 在之前已有创表语句中已有体现
CREATE TABLE `enrollment` (
    `enrollment_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '选课记录唯一标识符（自增长整型）',
    `student_id` VARCHAR(50) NOT NULL COMMENT '关联学生表的外键，指定选课学生',
    `course_id` VARCHAR(50) NOT NULL COMMENT '关联课程表的外键，指定选修课程',
    `semester` VARCHAR(20) NOT NULL COMMENT '选课学期（需与课程学期一致）',
    `grade` DECIMAL(5,2) COMMENT '课程成绩（选修后填写，如85.5）',
    `enrollment_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '选课时间（自动记录选课时刻）',
    UNIQUE KEY `student_course_semester` (`student_id`, `course_id`, `semester`) COMMENT '唯一约束：同一学生同一课程同一学期只能选一次',
    FOREIGN KEY (`student_id`) REFERENCES `student`(`student_id`),
    FOREIGN KEY (`course_id`) REFERENCES `course`(`course_id`)
) ENGINE=InnoDB COMMENT='学生选课记录及成绩表';

-- 查看表结构
SHOW create table enrollment;

3.3 查看表上建立的索引

语法示例：

-- 查看表的所有索引
SHOW INDEX FROM student;

-- 查看索引基本信息
SHOW INDEXES FROM course;

-- 从information_schema查看索引详情
SELECT 
    index_name AS 索引名称,
    column_name AS 列名,
    index_type AS 索引类型,
    non_unique AS 是否唯一
FROM 
    information_schema.STATISTICS
WHERE 
    table_schema = DATABASE() 
    AND table_name = 'student';

实践建议：

• 定期检查索引使用情况
• 关注Cardinality值，过低可能索引效果不好
• 检查是否有重复或冗余索引

3.4 删除索引

3.4.1 使用DROP INDEX语句删除索引

语法示例：

DROP INDEX 索引名
    ON 表名
    [ALGORITHM = {DEFAULT | INPLACE | COPY}]
    [LOCK = {DEFAULT | NONE | SHARED | EXCLUSIVE}];

参数说明：

参数	作用
索引名	要删除的索引名称
表名	索引所属的表名
ALGORITHM	指定删除索引的算法： DEFAULT：默认算法，由 MySQL 自动选择 INPLACE：在线删除索引，尽量减少锁表时间 COPY：复制表数据并删除索引，适用于大表
LOCK	控制删除索引时的锁级别 DEFAULT：默认锁级别，由 MySQL 自动选择 NONE：无锁，允许并发读写 SHARED：共享锁，允许并发读，禁止写 EXCLUSIVE：排他锁，禁止并发读写

语句示例：

-- 删除普通索引
DROP INDEX idx_department ON student;

注意事项：

• 需要指定表名
• 删除索引前确认没有关键查询依赖该索引

3.4.2 使用ALTER TABLE语句删除索引

语法示例：

ALTER TABLE 表名
    DROP INDEX 索引名
    [ALGORITHM = {DEFAULT | INPLACE | COPY}]
    [LOCK = {DEFAULT | NONE | SHARED | EXCLUSIVE}];

参数说明同3.4.1；

语句示例：

-- 删除普通索引
ALTER TABLE teacher DROP INDEX idx_age;

-- 删除主键索引
ALTER TABLE role DROP PRIMARY KEY;

-- 删除外键约束及索引
ALTER TABLE enrollment DROP FOREIGN KEY idx_student_course;
ALTER TABLE enrollment DROP INDEX idx_grade;

结果类似3.4.1，这里不再操作（外键索引比较重要，验证删除后也可重新加回来）；

应用场景：

• 需要同时修改表结构和删除索引
• 需要删除主键或外键约束
• 在一条语句中执行多个操作

3.4.3 总结

方法	语法	适用场景
DROP INDEX	DROP INDEX 索引名 ON 表名	适用于单独删除索引的场景，语法简洁
ALTER TABLE	ALTER TABLE 表名 DROP INDEX 索引名	适用于在修改表结构时删除索引，适合与其他表操作结合使用

注意事项：

• 删除主键索引：如果删除的是主键索引，需要使用 ALTER TABLE 语句，例如：ALTER TABLE `user` DROP PRIMARY KEY;
• 锁级别：在删除索引时，选择合适的锁级别（如 LOCK = NONE）可以减少对业务的影响。
• 备份：在删除索引前，建议备份数据或测试删除操作的影响。

3.5 实际使用注意事项

• 不要过度索引，每个索引都会增加写操作的开销
• 优先考虑选择性高的列建立索引
• 考虑复合索引的顺序（最常用列在前）
• 定期分析索引使用情况，删除未使用的索引