MySQL使用(三)

目录

• MySQL使用(三)
  • 事务(Transaction)
    • • 事务的原则：
    • 事务语法
  • 索引
    • 索引分类
    • 索引的使用
    • 索引原则
  • 数据库用户管理
  • 数据库备份
  • 数据库设计
    • 三大范式

MySQL使用(三)

事务(Transaction)

• 要么都成功，要么都失败

• 将一组SQL放在一个批次中去执行

事务的原则：

• **ACID原则：原子性(Atomicity)，一致性(Consistency)，隔离性(Isolation)，持久性(Durability) **

• 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。

• 一致性：一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。

• 隔离性：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。

• 持久性：在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚(Rollback)。

• 脏读：读取未提交数据

• 不可重复读：前后多次读取，数据内容不一致

• 幻读：前后多次读取，数据总量不一致

• 事务隔离级别：

隔离级别	脏读	不可重复读	幻读
读未提交(Read uncommitted)	×	×	×
读已提交(Read committed)	√	×	×
可重复读(Repeatable read)	√	√	×
可串行化(Serializable)	√	√	√

• 大多数数据库系统默认的隔离级别，如Oracle和SQL Server，为读已提交
• MySQL的默认事务隔离级别为可重复读，即只允许add，不允许update

事务语法

• MySQL默认开启事务自动提交

  • SET autocommit = 0; 关闭自动提交

• 事务开启：START TRANSACTION

• 事务提交：COMMIT

• 事务回滚：ROLLBACK

• 保存点：SAVEPOINT 保存点名

  • ROLLBACK TO SAVEPOINT 保存点名
  • RELEASE SAVEPOINT 保存点名

• 事务步骤：

  1. 关闭自动提交

  2. 开启事务

  3. 成功提交/失败回滚

  4. 开启自动提交

索引

索引(index)是帮助MySQL高效获取数据的数据结构。

索引分类

• 主键索引：PRIMARY KEY
  • 唯一的标识，不可重复，只能有一个字段作为主键
• 唯一索引：UNIQUE KEY
  • 避免重复的字段出现
• 常规索引：KEY / INDEX
  • 默认的索引
• 全文索引：FULLTEXT
  • 在特定的数据库引擎上才有
  • 快速定位数据

索引的使用

• ALTER TABEL tableName ADD INDEX indexName(columName)

索引原则

• 索引不是越多越好
• 不要对进程变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上

MySQL索引原理

数据库用户管理

• 用户表：mysql.user
• 创建用户：CREATE USER 用户名 IDENTIFIED BY '密码'
• 修改密码：SET PASSWORD FOR 用户名 = PASSWORD('密码')
• 重命名：RENAME USER 原名字 TO 新名字
• 用户授权：GRANT ALL PRIVILEGES ON 库.表 TO 用户名
• 查看权限：SHOW GRANTS FOR 用户名
• 撤销权限：REVOKE ALL PRIVILEGES ON 库.表 FROM 用户名
• 删除用户：DROP USER 用户名

数据库备份

• 直接拷贝物理文件
• 在可视化工具中手动导出
• 使用命令行导出：mysqldump -h主机 -u用户名 -p密码 库名 表名 > 导出地址
  • 在mysql中source *.sql导入

数据库设计

三大范式

• 第一范式(1NF)：要求数据库表的每一列都是不可分割的原子数据项
• 第二范式(2NF)：在1NF的基础上，非码属性必须完全依赖于候选码
  • 需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关
• 第三范式(3NF)：在2NF的基础上，任何非主属性不依赖于其他非主属性
  • 需要确保数据表中的每一列都和主键直接相关，而不能间接相关