当前位置： 代码网 > it编程>数据库>Mysql > MySQL 索引、事务与约束操作命令大全

MySQL 索引、事务与约束操作命令大全

2026年04月27日 • Mysql •我要评论
说明：本文涵盖 mysql 中三类重要功能：索引（提高查询性能）、事务（保证数据一致性）、约束（保证数据完整性）。每条命令附语法和实用示例，覆盖 mysql 5.7 和 8.0 的常见特性。一、索引操

说明：本文涵盖 mysql 中三类重要功能：索引（提高查询性能）、事务（保证数据一致性）、约束（保证数据完整性）。每条命令附语法和实用示例，覆盖 mysql 5.7 和 8.0 的常见特性。

一、索引操作

索引用于加速数据检索，但会占用额外存储空间并影响写性能。

1.1 创建索引

1.1.1 使用 create index 语句

语法：

create [unique | fulltext | spatial] index 索引名
    on 表名 (列名 [(长度)] [asc | desc], ...)
    [using {btree | hash}]
    [索引选项];

示例：

-- 普通索引
create index idx_username on users(username);
-- 唯一索引（不允许重复值）
create unique index idx_email on users(email);
-- 全文索引（适用于 myisam 或 innodb mysql 5.6+）
create fulltext index ft_content on articles(title, content);
-- 空间索引（用于地理数据，需 myisam/innodb 支持）
create spatial index idx_location on places(location);
-- 复合索引（多列）
create index idx_name_age on users(last_name, first_name, age);
-- 前缀索引（仅索引字符串的前n个字符）
create index idx_title_prefix on books(title(20));
-- 降序索引（mysql 8.0 真正支持）
create index idx_created_desc on orders(created_at desc);
-- 指定索引类型（btree 是默认）
create index idx_hash on temp_table(id) using hash;

1.1.2 在 create table 时创建索引

create table users (
    id int primary key,
    name varchar(50),
    email varchar(100) unique,
    last_login datetime,
    index idx_last_login (last_login),
    fulltext index ft_name (name)
);

1.1.3 使用 alter table 创建索引

-- 添加主键索引（只能通过 alter 或 create table）
alter table users add primary key (id);
-- 添加唯一索引
alter table users add unique index idx_email (email);
-- 添加普通索引
alter table users add index idx_name (name);
-- 添加全文索引
alter table articles add fulltext index ft_body (body);
-- 添加空间索引
alter table places add spatial index idx_geom (geom);

1.2 查看索引

-- 查看表的所有索引
show index from 表名;
show index from 表名\g   -- 垂直显示更详细
-- 从information_schema中查询
select * from information_schema.statistics 
where table_schema = '数据库名' and table_name = '表名';

1.3 删除索引

-- 使用 drop index 语句
drop index 索引名 on 表名;
-- 使用 alter table 删除
alter table 表名 drop index 索引名;
-- 删除主键索引（特殊）
alter table 表名 drop primary key;
-- 删除外键索引（实际删除的是外键约束，索引可能保留）
alter table 表名 drop foreign key 外键约束名;

示例：

drop index idx_username on users;
alter table users drop index idx_email;
alter table orders drop primary key;   -- 删除主键

1.4 修改索引

mysql 没有直接“修改索引”的命令，通常采用“删除后重建”的方式：

-- 先删除旧索引
drop index idx_old on table_name;
-- 再创建新索引
create index idx_new on table_name (column1, column2);

如果只是想重命名索引（mysql 5.7+ 支持 alter table 重命名）：

alter table 表名 rename index 旧索引名 to 新索引名;

-- 示例
alter table users rename index idx_username to idx_uname;

1.5 索引可见性（mysql 8.0）

可以设置索引对优化器是否可见，而不实际删除索引。

-- 设置索引不可见（优化器忽略该索引）
alter table 表名 alter index 索引名 invisible;

-- 设置索引可见
alter table 表名 alter index 索引名 visible;

-- 示例
alter table users alter index idx_email invisible;

1.6 强制使用或忽略索引（查询提示）

-- 强制使用索引
select * from users force index (idx_username) where username = 'john';

-- 忽略索引
select * from users ignore index (idx_username) where username = 'john';

-- 使用索引（建议，优化器可自行决定）
select * from users use index (idx_username) where username = 'john';

二、事务操作

事务保证一组操作要么全部成功（commit），要么全部失败回滚（rollback），满足 acid 特性。

2.1 事务控制语句

命令说明
start transaction 或 begin显式开启一个新事务
commit提交事务，使更改永久生效
rollback回滚事务，撤销当前事务中的所有更改
savepoint 名称在事务中设置保存点
rollback to savepoint 名称回滚到指定保存点
release savepoint 名称删除保存点
`set autocommit = {01}`禁用(0)/启用(1)自动提交

2.2 自动提交模式

mysql 默认 autocommit = 1，每条 dml 自动提交。要使用多语句事务，需先禁用自动提交或显式 start transaction。

-- 查看当前自动提交设置
select @@autocommit;

-- 关闭自动提交（仅当前会话）
set autocommit = 0;

-- 开启自动提交
set autocommit = 1;

2.3 基本事务示例

-- 方式一：使用 start transaction
start transaction;
    update accounts set balance = balance - 100 where id = 1;
    update accounts set balance = balance + 100 where id = 2;
commit;   -- 或 rollback

-- 方式二：使用 begin
begin;
    insert into logs (user_id, action) values (1, '转账');
    savepoint sp1;
    update accounts set balance = balance - 100 where id = 1;
    -- 发现错误，回滚到保存点
    rollback to savepoint sp1;
commit;   -- insert 已提交，update 被撤销

2.4 保存点（savepoint）

start transaction;
    insert into orders (user_id, amount) values (1, 500);
    savepoint after_insert;
    
    update inventory set stock = stock - 5 where product_id = 10;
    -- 库存不足，决定回滚到保存点
    rollback to savepoint after_insert;
    
    -- 可继续其他操作
    insert into order_log (msg) values ('库存不足，未扣减');
    
    -- 释放不再需要的保存点
    release savepoint after_insert;
commit;

2.5 事务隔离级别

隔离级别影响事务并发时的可见性。

查看当前隔离级别：

-- 全局级
select @@global.transaction_isolation;
-- 会话级
select @@transaction_isolation;

设置隔离级别：

-- 设置会话隔离级别
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;  -- mysql 默认
set session transaction isolation level serializable;

-- 设置全局隔离级别（需要 super 权限）
set global transaction isolation level read committed;

2.6 锁语句（辅助事务）

-- 共享锁（读锁）
select * from accounts where id = 1 lock in share mode;

-- 排他锁（写锁）
select * from accounts where id = 1 for update;

-- 表锁（手动）
lock tables accounts write;
-- 执行更新...
unlock tables;

2.7 xa 事务（分布式事务）

xa start 'xid1';        -- 开启 xa 事务
    update accounts set balance = balance - 100 where id = 1;
xa end 'xid1';
xa prepare 'xid1';      -- 准备阶段
xa commit 'xid1';       -- 提交
-- 或 xa rollback 'xid1';

2.8 查看事务信息

-- 查看当前正在运行的事务
select * from information_schema.innodb_transaction;

-- 查看锁信息
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;

-- 查看引擎状态（包含事务详情）
show engine innodb status\g

三、约束操作

约束用于限制表中数据的合法性和完整性。

3.1 约束类型概览

约束类型关键字作用
主键约束primary key唯一标识一行，非空且唯一
外键约束foreign key参照另一表的主键，维护引用完整性
唯一约束unique列值不能重复（可多个 null）
检查约束check限制列值满足条件（mysql 8.0.16+ 完整支持）
非空约束not null列值不能为 null
默认约束default提供默认值

3.2 主键约束（primary key）

创建主键

-- 列级定义
create table t1 (id int primary key, name varchar(50));

-- 表级定义（适合复合主键）
create table t2 (
    user_id int,
    role_id int,
    primary key (user_id, role_id)
);

-- 为已有表添加主键
alter table t1 add primary key (id);

-- 添加自增属性
alter table t1 modify id int auto_increment;

删除主键

alter table t1 drop primary key;

⚠️ 如果主键列是自增列，需先修改列定义去掉 auto_increment。

3.3 外键约束（foreign key）

外键用于维持两张表之间的引用完整性。

创建外键

-- 建表时定义
create table orders (
    order_id int primary key,
    user_id int,
    foreign key (user_id) references users(id)
        on delete cascade on update restrict
);

-- 带约束名
create table orders (
    order_id int primary key,
    user_id int,
    constraint fk_orders_user foreign key (user_id) references users(id)
);

-- 为已有表添加外键
alter table orders add constraint fk_user 
    foreign key (user_id) references users(id) on delete set null;

外键选项：

  • on delete cascade：主表记录删除，子表相关记录自动删除
  • on delete set null：子表外键列设为 null
  • on delete restrict / no action：阻止删除（默认行为）
  • on update cascade：主键更新时同步更新子表外键
  • on update set null：主键更新时子表外键设为 null

查看外键

select * from information_schema.key_column_usage 
where table_name = 'orders' and constraint_name not in ('primary');

-- 或使用 show create table
show create table orders\g

删除外键

-- 必须使用约束名删除
alter table orders drop foreign key fk_orders_user;

-- 如果不知道约束名，可通过 show create table 查看

禁用/启用外键检查（全局会话）

-- 暂时禁用外键检查（可用于重排序表导入）
set foreign_key_checks = 0;

-- 执行数据操作（如 truncate 被引用的表）

-- 恢复检查
set foreign_key_checks = 1;

3.4 唯一约束（unique）

创建唯一约束

-- 列级
create table users (email varchar(100) unique);

-- 表级命名
create table users (
    email varchar(100),
    constraint uk_email unique (email)
);

-- 复合唯一约束
create table user_roles (
    user_id int,
    role_id int,
    unique key uk_user_role (user_id, role_id)
);

-- 为已有表添加
alter table users add unique index uk_email (email);
-- 或
alter table users add constraint uk_username unique (username);

删除唯一约束

-- 唯一约束会自动创建同名索引，通过删除索引即可
alter table users drop index uk_email;

3.5 检查约束（check）

mysql 8.0.16+ 完整支持 check；早期版本解析但不执行。

创建 check

-- 列级 check
create table products (
    price decimal(10,2) check (price > 0),
    quantity int check (quantity >= 0)
);

-- 表级 check（可命名）
create table employees (
    id int,
    salary decimal(10,2),
    constraint chk_salary check (salary >= 0)
);

-- 跨列 check
create table reservations (
    start_date date,
    end_date date,
    check (start_date < end_date)
);

-- 为已有表添加 check
alter table users add constraint chk_age check (age between 0 and 150);

删除 check

alter table users drop constraint chk_age;

查看 check 约束

select * from information_schema.check_constraints 
where constraint_schema = '数据库名' and table_name = '表名';

3.6 非空约束（not null）

添加非空

-- 建表时
create table t (id int not null);

-- 修改已有列
alter table users modify email varchar(100) not null;

删除非空（允许 null）

alter table users modify email varchar(100) null;

3.7 默认约束（default）

设置默认值

-- 建表时
create table t (status varchar(20) default 'active');

-- 修改已有列默认值（两种方法）
alter table users alter column status set default 'inactive';
-- 或
alter table users modify status varchar(20) default 'inactive';

-- 设置表达式默认值（mysql 8.0+）
create table t (created_at datetime default (now()));

删除默认值

alter table users alter column status drop default;

3.8 查看所有约束

-- 通过 information_schema 查看表的所有约束
select constraint_name, constraint_type 
from information_schema.table_constraints 
where table_schema = '数据库名' and table_name = '表名';

3.9 修改约束的其他方式

由于没有直接的“修改约束”命令，通常需要删除后重建。例如修改 not null 到 null 允许，或修改 check 条件。

-- 修改 check：先删除，再添加
alter table t drop constraint chk_old;
alter table t add constraint chk_new check (new_condition);

3.10 完整的约束管理示例

-- 创建带所有约束类型的表
create table employees (
    id int primary key auto_increment,
    emp_no varchar(20) not null unique,
    name varchar(100) not null,
    age int default 25 check (age >= 18 and age <= 65),
    dept_id int,
    salary decimal(10,2) check (salary >= 0),
    email varchar(100),
    created_at datetime default current_timestamp,
    constraint fk_dept foreign key (dept_id) references departments(id)
);

-- 添加新约束
alter table employees add constraint chk_salary_positive check (salary > 0);
alter table employees modify email varchar(100) not null;

-- 删除约束
alter table employees drop foreign key fk_dept;
alter table employees drop constraint chk_salary_positive;
alter table employees modify email varchar(100) null;

-- 查看最终约束
select constraint_name, constraint_type 
from information_schema.table_constraints 
where table_name = 'employees';

四、综合应用场景示例

4.1 索引与事务配合

-- 开启事务，在更新前检查索引使用情况
start transaction;
    -- 使用排他锁锁定待更新行
    select * from accounts where account_no = 'a123' for update;
    
    -- 确保 account_no 列有唯一索引
    create unique index idx_acc_no on accounts(account_no);
    
    update accounts set balance = balance - 500 where account_no = 'a123';
    update accounts set balance = balance + 500 where account_no = 'b456';
commit;

4.2 约束与事务保证数据一致性

start transaction;
    -- 检查外键关系
    set foreign_key_checks = 0;   -- 临时关闭（谨慎）
    delete from departments where id = 10;
    -- 实际应使用 on delete cascade 或先删除子表记录
    set foreign_key_checks = 1;
commit;

五、最佳实践与注意事项

方面建议
索引避免过多索引（影响写性能）；复合索引遵循最左前缀原则；使用 explain 分析查询；定期清理无用索引。
事务尽量短小，避免长事务；合理选择隔离级别（通常 read committed 或 repeatable read）；使用 savepoint 实现部分回滚；避免在事务中进行用户交互或网络调用。
约束优先使用数据库约束而非应用层校验；外键会带来额外开销，但能保证引用完整性；check 在 mysql 8.0 之前不生效，注意兼容性；修改约束前备份数据。

以上内容涵盖了 mysql 中索引、事务和约束的所有核心操作命令，包括创建、查看、删除、修改及管理语句，并附有详细示例。根据实际业务场景灵活运用即可。更多相关mysql 索引、事务与约束操作内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

赞 (0)

相关文章:

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：
最近更新的文章
推荐阅读
大家感兴趣的文章
Copyright © 2017-2026  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱：2386932994@qq.com