我要评论1. 引言
在数据库学习和开发中，掌握从基础表操作到高级性能优化（如索引）的完整 sql 技能至关重要。本文将通过一个连贯的实战案例，手把手演示如何使用 mysql 完成数据库创建、数据表管理、数据插入、表结构修改，并深入讲解索引的创建、管理与核心原理。我们将创建一个名为 abc 的数据库，并在其中管理一个学生信息表 s1，通过具体的 sql 语句讲解每个操作步骤及其背后的设计思考。
2. 创建数据库与选择数据库
首先，我们需要创建一个新的数据库来存放我们的数据表。
-- 创建数据库 create database abc;
执行上述语句后，mysql 会创建一个名为 abc 的数据库。接下来，我们需要告诉 mysql 后续的操作都在这个数据库中进行。
-- 使用数据库 use abc;
use 语句用于选择当前要操作的数据库。执行后，所有未指定数据库的表操作都将默认在 abc 数据库中进行。
3. 创建学生信息表
在操作表之前，一个好的习惯是确保目标表不存在，避免因重复创建而报错。
-- 删除数据表（如果存在） drop table if exists s1;
drop table if exists 是一个安全的做法，它只在表存在时才执行删除操作。
现在，我们来创建核心的学生信息表 s1。
-- 创建学生表
create table s1 (
id int primary key auto_increment comment '编号',
s_name varchar(20) unique not null comment '姓名',
age int not null comment '年龄',
sex enum('男', '女', '未知') default '未知' comment '性别',
s_sno char(12) unique not null comment '学号',
tel char(11) not null comment '手机号',
start_time date default (current_date) comment '入学时间',
status enum('在读', '毕业') default '在读' comment '状态'
);
表结构说明：
id: 主键，自动增长，作为每条记录的唯一标识。s_name: 学生姓名，varchar(20)类型，unique约束确保姓名不重复，not null表示必填。age: 年龄，整型，必填。sex: 性别，使用enum类型限定为‘男’、‘女’、‘未知’三种值，默认值为‘未知’。s_sno: 学号，定长字符串，唯一且必填。tel: 手机号，定长字符串，必填。start_time: 入学时间，日期类型，默认值为系统当前日期。status: 状态，enum类型，表示‘在读’或‘毕业’，默认‘在读’。
4. 查看表结构信息
创建表后，我们可以通过多种方式查看其结构。
1. 查看表的基本字段信息（desc）
-- 查看表结构 desc s1;
desc (或 describe) 命令会以表格形式列出表的所有字段名、类型、是否为空、键信息、默认值和额外说明。
2. 查看字段信息（show columns）
-- 查看字段 show columns from s1;
show columns 与 desc 功能类似，提供了另一种查看表结构的方式。
3. 查看完整的建表语句
-- 查看创建表语句 show create table s1;
这条命令会返回创建该表的完整 sql 语句，包括引擎、字符集等所有细节，对于备份或迁移表结构非常有用。
5. 向表中插入数据
表创建好后，我们需要向其中添加数据。以下是插入8条学生记录的示例：
-- 插入数据 insert into s1 values (null, '张三', 18, '男', '2019001', '13888888888', '2020-09-01', '在读'); insert into s1 values (null, '李四', 19, '女', '2019002', '13888888889', '2020-09-01', '在读'); insert into s1 values (null, '王五', 20, '未知', '2019003', '13888888890', '2020-09-01', '在读'); insert into s1 values (null, '赵六', 21, '男', '2019004', '13888888891', '2020-09-01', '在读'); insert into s1 values (null, '孙七', 22, '女', '2019005', '13888888892', '2020-09-01', '在读'); insert into s1 values (null, '周八', 23, '未知', '2019006', '13888888893', '2020-09-01', '在读'); insert into s1 values (null, '吴九', 24, '男', '2019007', '13888888894', '2020-09-01', '在读'); insert into s1 values (null, '郑十', 25, '女', '2019008', '13888888895', '2020-09-01', '在读');
注意：id 字段传入了 null，由于其是 auto_increment，数据库会自动为其生成递增值。
6. 修改表结构（alter table）
在实际应用中，表结构可能需要调整。alter table 语句用于添加、修改或删除表中的列。
1. 添加新字段
假设我们需要为学生表增加一个“身份证号”字段。
-- 添加身份证号字段 alter table s1 add column id_card char(18) comment '身份证号';
2. 删除字段
如果添加的字段不再需要，可以将其删除。
-- 删除身份证号字段 alter table s1 drop column id_card;
7. 索引（index）详解
7.1 索引是什么
索引是建立在数据表字段上的数据结构（mysql innodb 默认使用 b+tree），相当于书本的目录。
作用：快速定位数据，减少全表扫描，极大提升查询（select）语句的速度。
7.2 索引的优缺点
优点：
- 极大加快数据查询速度：特别是在
where条件筛选、order by排序、group by分组时效率提升显著。
缺点：
- 占用额外磁盘存储空间：索引本身需要存储。
- 降低 dml 性能：进行
insert、update、delete操作时，数据库需要额外维护索引结构，可能导致写入变慢。 - 索引不是越多越好：无用的索引只会增加存储与写入开销，需要根据查询需求合理设计。
7.3 innodb 常见索引分类
主键索引 (primary)
- 特点：唯一、非空；一张表只能有一个主键。
- innodb 特性：innodb 存储引擎中，主键索引即聚簇索引，表数据本身按主键顺序存储在主键索引的叶子节点上。
唯一索引 (unique)
- 特点：索引列的值不能重复，但允许有一个
null值；一张表可以有多个唯一索引。
普通索引 (index)
- 特点：最基础的索引类型，仅用于加速查询。允许重复值和
null值。
联合索引（复合索引）
- 特点：在多个字段组合上创建的索引。
- 核心原则：遵循最左前缀原则。查询条件必须包含联合索引的最左列，才能有效利用该索引。
全文索引 (fulltext)
- 特点：专门用于对长文本内容（如
text类型字段）进行全文检索，可以高效替代低效的like '%keyword%'模糊查询。
7.4 基础语法示例
以下示例基于已添加的 id_card 字段和已有的 s_name、age 字段。
1. 创建普通索引
-- 语法：create index 索引名 on 表名(字段名); create index name on s1(s_name);
2. 创建联合索引
-- 语法：create index 索引名 on 表名(字段名1, 字段名2); create index name_age on s1(s_name, age);
3. 创建全文索引
-- 语法：create fulltext index 索引名 on 表名(字段名); create fulltext index name_fulltext on s1(s_name);
4. 创建唯一索引
-- 语法：create unique index 索引名 on 表名(字段名); create unique index idcard on s1(id_card);
5. 查看索引
-- 语法：show index from 表名; show index from s1;
该命令会显示表上所有索引的名称、类型、关联的字段、唯一性等信息。
6. 删除索引
-- 语法：drop index 索引名 on 表名; drop index idcard on s1;
7.6 索引使用注意事项了解了索引的创建语法后，让我们通过具体的查询示例来展示索引如何提升性能。
示例1：普通索引加速查询
假设我们经常需要按学生姓名查询：
-- 无索引时的查询（全表扫描） explain select * from s1 where s_name = '张三'; -- 创建姓名索引后 create index idx_name on s1(s_name); -- 再次执行相同查询（使用索引） explain select * from s1 where s_name = '张三';
执行计划对比：
- 无索引时：
type为all（全表扫描），rows为表总行数 - 有索引时：
type为ref（索引查找），rows为1（精确匹配）
示例2：联合索引与最左前缀原则
创建联合索引后，观察不同查询条件的效果：
-- 创建联合索引 create index idx_name_age on s1(s_name, age); -- 情况1：使用索引（包含最左列s_name） explain select * from s1 where s_name = '李四' and age = 19; -- 情况2：使用索引（只使用最左列s_name） explain select * from s1 where s_name = '王五'; -- 情况3：索引失效（未包含最左列s_name） explain select * from s1 where age = 20; -- 情况4：部分使用索引（s_name范围查询，age无法使用索引） explain select * from s1 where s_name like '张%' and age = 18;
示例3：唯一索引防止重复数据
-- 尝试插入重复学号（会失败） insert into s1 values (null, '测试', 20, '男', '2019001', '13800000000', '2020-09-01', '在读'); -- 错误：duplicate entry '2019001' for key 's_sno' -- 查看唯一约束错误信息 show errors;
示例4：索引对排序的优化
-- 无索引时的排序（可能使用文件排序） explain select * from s1 order by s_name; -- 创建索引后 create index idx_name on s1(s_name); -- 再次执行排序（可能使用索引排序） explain select * from s1 order by s_name;
示例5：索引失效场景演示
-- 1. 在索引列上使用函数（索引失效） explain select * from s1 where left(s_name, 1) = '张'; -- 2. 隐式类型转换（索引失效） -- 假设tel字段是char(11)，但查询时使用数字 explain select * from s1 where tel = 13888888888; -- 3. 使用or条件（如果or的某一侧没有索引，可能导致全表扫描） explain select * from s1 where s_name = '张三' or age = 18; -- 4. 使用不等于(!=或<>)（可能无法使用索引） explain select * from s1 where s_name != '张三';
示例6：覆盖索引（covering index）
-- 创建覆盖索引 create index idx_covering on s1(s_name, age, sex); -- 查询只涉及索引列，无需回表 explain select s_name, age from s1 where s_name = '赵六' and age > 20; -- extra列显示"using index"表示使用了覆盖索引
示例7：索引选择性测试
-- 查看字段值的分布情况（评估索引效果）
select
count(distinct sex) as sex_distinct_count,
count(distinct age) as age_distinct_count,
count(distinct s_name) as name_distinct_count,
count(*) as total_rows
from s1;
-- 在区分度高的字段上建索引效果更好
-- sex只有3种值，区分度低
-- s_name有8种值（假设不重复），区分度高
示例8：多列索引顺序优化
-- 根据查询频率和选择性决定索引列顺序 -- 假设查询模式1：where age > 20 and sex = '男' -- 假设查询模式2：where sex = '女' and age > 18 -- 创建测试索引 create index idx_age_sex on s1(age, sex); create index idx_sex_age on s1(sex, age); -- 分别测试两个查询 explain select * from s1 where age > 20 and sex = '男'; explain select * from s1 where sex = '女' and age > 18; -- 根据实际查询模式选择更优的索引顺序
示例9：索引维护操作
-- 1. 修改索引名称（mysql 8.0+） alter table s1 rename index idx_name to idx_student_name; -- 2. 禁用/启用索引（某些存储引擎支持） -- alter table s1 disable keys; -- alter table s1 enable keys; -- 3. 分析索引使用情况 analyze table s1; -- 4. 检查索引碎片 show table status like 's1'; -- 关注data_free列，值较大时考虑优化表 optimize table s1;
示例10：实际性能对比测试
-- 准备测试数据（插入更多数据以观察性能差异）
delimiter $$
create procedure insert_test_data()
begin
declare i int default 1;
while i <= 10000 do
insert into s1 values (
null,
concat('测试学生', i),
floor(18 + rand() * 10),
elt(floor(1 + rand() * 3), '男', '女', '未知'),
concat('202400', lpad(i, 4, '0')),
concat('138', lpad(floor(rand() * 100000000), 8, '0')),
date_sub(current_date, interval floor(rand() * 365) day),
elt(floor(1 + rand() * 2), '在读', '毕业')
);
set i = i + 1;
end while;
end$$
delimiter ;
-- 执行存储过程插入测试数据
call insert_test_data();
-- 删除无索引时的查询测试
set profiling = 1;
select * from s1 where s_name like '测试学生%' order by age desc limit 100;
show profiles;
-- 创建索引后的查询测试
create index idx_test_name on s1(s_name);
select * from s1 where s_name like '测试学生%' order by age desc limit 100;
show profiles;
-- 对比查询时间
set profiling = 0;
性能优化建议：
- 使用
explain分析查询执行计划 - 关注
type列：const>ref>range>index>all - 关注
extra列：避免using filesort和using temporary - 定期使用
analyze table更新索引统计信息 - 监控慢查询日志，针对慢查询优化索引
7.5 索引使用注意事项
- 遵循最左前缀原则：对于联合索引
(a, b, c)，查询条件必须包含a，索引才会生效。例如where a=1 and b=2有效，但where b=2则无法使用该索引。 - 避免在索引列上使用函数或运算：例如
where year(create_time) = 2023或where amount * 2 > 100会导致索引失效。 - 注意隐式类型转换：例如字符串字段与数字比较时，可能导致索引失效。
- 选择区分度高的字段建立索引：在性别（只有‘男’、‘女’）这种区分度很低的字段上建立索引，效果甚微。应优先在唯一性高或取值分布广泛的字段上建索引。
- 避免过度索引：每个额外的索引都会增加写操作的开销，定期审查并删除未使用的索引。
- 考虑索引大小：过长的索引字段（如text类型）不适合建索引，可考虑前缀索引。
- 注意null值的影响：包含null值的索引列在查询时可能需要特殊处理。
- 定期维护索引：使用
optimize table或alter table ... rebuild整理索引碎片。## 8. 总结
本文通过一个从零开始的连贯案例，系统演示了 mysql 数据库操作与性能优化的核心流程：
- 环境准备：创建数据库 (
create database) 并切换使用 (use)。 - 表管理：安全删除旧表 (
drop table if exists)、创建新表 (create table) 并查看其结构 (desc,show create table)。 - 数据操作：向表中插入示例数据 (
insert into)。 - 结构维护：使用
alter table动态添加或删除字段，适应业务变化。 - 性能优化：深入理解索引的原理、分类与优缺点，并实践了普通索引、联合索引、全文索引、唯一索引的创建 (
create index)、查看 (show index) 和删除 (drop index)，掌握了索引使用的关键注意事项。
掌握这些从基础到进阶的操作，是进行高效数据库设计、开发和优化的坚实基础。建议读者在 mysql 客户端中亲自执行每一段 sql 代码，并结合实际业务场景思考索引的设计，以加深理解。
以上就是mysql从建表到索引管理的完整指南的详细内容，更多关于mysql从建表到索引管理的资料请关注代码网其它相关文章！
发表评论