MySQL中事务、索引和数据库管理设计

1. 事务和索引事务ACID原则特性英文通俗解释原子性Atomicity事务是最小单位不可分割。要么全成功要么全失败一致性Consistency执行前后数据总量不变转账A 扣钱 B 加钱 总金额不变隔离性Isolation多个事务同时执行互不干扰持久性Durability事务提交后数据永久修改断电 / 崩溃也不会丢失三大读问题解释脏读一个事务读到了另一个事务未提交的数据对方回滚后数据无效。 读了未提交数据不可重复读同一个事务内两次查询同一行数据结果不一样被其他事务修改了。 数据被修改幻读同一个事务内两次查询的总条数不一样被其他事务新增 / 删除了数据。 数据条数变了事务及索引举例-- mysql 是默认开启事务自动提交的SETautocommit0-- 关闭SETautocommit1-- 开启-- 手动处理事务SETautocommit0-- 关闭自动提交(一般不用,保持默认)-- 事务开启(这步会临时改变事务提交状态)STARTTRANSACTION-- 标记一组事务的开始,从这之后的 sql 都在同一组事务内-- 提交: 持久化(成功!)COMMIT-- 回滚: 回到原来的样子(失败!)ROLLBACK-- 事务结束SETautocommit1-- 开启自动提交(一般不用,保持默认)/* 索引分类: 1. 主键索引(唯一非空) | primary key 2. 唯一索引(字段值唯一) | unique index 3. 常规索引(加速查询) | index 4. 全文索引(长文本模糊查询搜索)| fulltext index */-- create index 索引名 on 表(字段)CREATEINDEXid_user_nameONuser(name)-- 使用select查找对应字段的数据速度更快CREATEUNIQUEINDEXid_user_nameONuser(name)-- alter table 表 add index 索引名(字段)ALTERTABLEuserADDINDEXid_user_name(name)ALTERTABLEuserADDUNIQUEINDEXid_user_name(name)ALTERTABLEuserADDPRIMARYKEYid_user_name(name)ALTERTABLEuserADDFULLTEXTINDEXid_user_name(name)-- explain 分析查询的方式EXPLAINSELECT*FROMuserWHEREname小青EXPLAINSELECT*FROMuserWHEREid4DROPINDEXid_user_nameONuser-- 删除索引(除了主键)SHOWINDEXFROMuser-- 展示表的全部索引2. 数据库管理和设计2.1.数据备份在终端/cmd上执行的命令# 1. 备份单个数据库mysqldump-uroot-p密码 数据库名保存路径/文件名.sql# 示例备份 test_db 库mysqldump-uroot-pRootwen test_dbD:/mysql_back/test_db_20260328.sql# 2. 备份单张表mysqldump-uroot-p密码 库名 表名路径/表备份.sql# 3. 备份所有数据库mysqldump-uroot-p密码 --all-databasesD:/all_back.sql2.2.数据导入在终端/cmd上执行的命令mysql-uroot-p密码 数据库名保存路径/文件名.sql mysql-uroot-p密码 库名 表名路径/表备份.sql或在MySQL命令行中执行的命令-- 先清空库/新建库USEtest_db;SOURCE D:/mysql_back/test_db_20260328.sql;2.3.修改密码USEmysql-- 先切换库SETPASSWORDnewRootwen-- 修改自己的密码ALTERUSERrootlocalhostIDENTIFIEDBYRootwen-- 修改其他用户密码2.4.三大范式-- 数据规范化/* 第一范式(1NF) 原子性: 保证每一列不可再分 第二范式(2NF) 前提: 满足第一范式 每张表只描述一件事情 第三范式(3NF) 前提: 满足第一和第二范式 确保数据表中的每一列数据都和主键直接相关,而不能间接相关 */-- 规范性 和 性能的问题/* 一般关联查询的表不得超过三张表; 考虑成本和用户体验,性能更加重要; 因此优先注重性能后,再适当考虑规范性! 具体表现为故意增加一些冗余字段,可变为单表查询. */