MySQL(四)

MySQL基础部分请见 enginelong的博客

事务控制

事务概述

MySQL事务主要用于处理操作数据量大、复杂度高的数据

• 定义
  一件事从开始发生到结束的过程
• 作用
  确保数据操作过程中的安全

事务操作

1. 开启事务

begin; 

2. 开始执行事务中的若干条SQL命令
3. 终止事务，在begin之后可以使用commit提交事务或者使用rollback进行事务回滚

commit; # 事务中SQL命令都执行成功，提交到数据库，结束此次事务
rollback; # 有SQL命令执行失败，回滚到开启事务的初始状态，此次事务结束

【注意】

• 事务操作只是针对数据进行操作，rollback不可以对数据库、数据表结构进行恢复

事务四大特性

• 原子性
  一个事务必须视为一个不可分割的最小工作单元，对于一个事务而言，不可能只执行其中的一部分。整个事务作为一个整体，要么执行成功然后提交，要么全部失败执行回滚操作；
• 一致性
  事务完成时，数据必须处于一致状态，数据的完整性约束没有遭到破坏；
• 隔离性
  数据库允许多个事务同时操作数据进行读或写，相互之间互相独立，隔离性可以防止多个事件并发执行时由于交叉执行导致数据的不一致的问题。
• 持久性
  一旦事务提交，则所做的修改会永久保存到数据库中，即使数据库系统崩溃，修改的数据也不会丢失

事务隔离级别

事务四大特性中的隔离性是在使用事务时最应该得到注意的特性。因为隔离级别不同，带来的操作现象也会有区别

• 隔离级别
  • 读未提交

  • 事务A与事务B，事务A未提交的数据，事务B可以读到，这里读到的数据称为脏数据，这种隔离级别最低，这种级别一般存在于理论中，实际数据库隔离级别一般高于该级别；

  • 读已提交

  • 事务A与事务B，事务A提交的数据，事务B才可以读到，这种隔离级别高于读未提交，以此可以避免出现脏数据，然而会导致不能重复读取；

  • 可重复读

  • 事务A与事务B，事务A提交后的数据，事务B读取不到，因此事务B可以重复读取数据，这种隔离级别高于读已提交，然而，这种模式会导致幻像读；

  • 串行化

  • 事务A与事务B，事务A在操作数据库时，事务B只能排队等待。因此，串行化模式没有上述三种模式的问题，数据最真实，完全隔离。然而，实际数据库中很少使用，因为串行操作降低了数据库的吞吐量，用户体验很差。

数据库优化

设计关系数据库时，选要遵从不同的范式

• 目前关系数据库有六种范式，范式级别越高，数据库冗余越小。然而，范式越高也意味着数据表的划分更细，一个数据库中需要的数据表也越多。此时，多个数据表连接在一起的代价是很大的尤其是当需要连接的数据表非常庞大的时候，这会严重降低数据库的性能。通常，数据库设计会遵循第一、第二、第三范式，以避免数据库操作异常，同时又不至于使表的关系过于复杂。

范式

• 第一范式(1NF)
  数据库中表的每一列都是不可分割的原子数据，不能是集合、数组、记录等组合的数据项。简而言之，数据库中的二维表，每一个元素不可再分；
• 第二范式(2NF)
  数据库中表的每条记录必须可以被唯一区分，所有属性依赖于主属性。即选取一个可以区分每条记录的属性或者属性组，作为唯一标识。简而言之，就是要为数据表设置一个主键；
• 第三范式(3NF)
  在第二范式的基础上要求属性不传递依赖，即每个属性不依赖于其他非主属性。这样要求一个数据表不包含已在其他表中包含的非主关键字信息。简而言之，就是应该合理的使用外键。

MySQL存储引擎

• 定义
  MySQL数据库管理系统中用来处理数据表的处理器
• 基本操作

# 查看所有存储引擎
   show engines;
# 查看已有表的存储引擎
   show create table 表名;
# 创建表指定
   create table 表名(...)engine=MyISAM;
# 已有表指定
   alter table 表名 engine=InnoDB;

• 常用存储引擎及特点

InnoDB

• 支持行级锁，仅对指定的记录进行加锁，这样其它进程还是可以对同一个 表中的其它记录进行操作；
• 支持外键、事务、事务回滚；
• 表字段和索引同存储在一个文件中
  • 表名.frm ：表结构
  • 表名.ibd : 表记录及索引文件

MyISAM

• 支持表级锁,在锁定期间，其它进程无法对该表进行写操作。如果是写锁，则其它进程则读也不允许；

• 表字段和索引分开存储

  • 表名.frm ：表结构
  • 表名.MYI : 索引文件(my index)
  • 表名.MYD : 表记录(my data)

• 如何选择存储引擎？

  • 执行查操作多的表用 MyISAM(使用InnoDB浪费资源)
  • 执行写操作多的表用 InnoDB

字段数据类型选择

• 优先程度 数字 > 时间日期 > 字符串
• 同一级别 占用空间小的 > 占用空间多的

【注意】

• 字符串在查询比较排序时数据处理慢；
• 占用空间少，数据库占用的磁盘块就少，读写处理就更快

• 对数据存储精确不要求时 float > decimal
• 如果很少被查询则推荐使用 TIMESTAMP（因为时间戳以整型存储）

键的设置

• innoDB如果不设置主键，则会默认设置隐含的主键，因此最好自己设置；
• 尽量设置占用空间小的字段为主键；
• 外键的设置用于保持数据完整性，但是会降低数据库导入和操作的效率尤其高并发下，会增加数据库维护的成本；
• 数据表相互关联时建议在关联键上建立索引，以提高查找效率

EXPLAIN语句

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。以此可以协助我们分析查询语句或者表结构的性能瓶颈。通过EXPLAIN命令可以得到：

• 表的读取顺序
• 数据读取操作的操作类型
• 可以使用的索引
• 实际被使用的索引
• 数据表之间的引用
• 每张表被优化器查询的行数

explain select * from 表名 where 条件;

• EXPLAIN主要数据字段解析
  • table：显示这一行的数据属于哪张表
  • type：最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型以此为：system、const、eq_reg、ref、range、index、ALL。一般而言，需要保证查询至少得到range级别，最好达到ref。

type中包含的值：

• system、const： 可以将查询的变量转为常量. 如id=1; id为 主键或唯一键
• eq_ref： 访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或唯一键)
• ref： 访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生
• range： 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时发生的情况
• index： 以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描
• ALL： 全表扫描，应该尽量避免

• possible_keys：显示可能应用在这张表中的索引。如果为空，表示没有可能应用的索引
• key：实际使用的索引。如果为NULL，则没有使用索引
• key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
• rows：MySQL认为必须检索的用来返回请求数据的行数

SQL优化

• 尽量选择占用空间少的数据类型，在where、group by、order by中出现频率最高的字段上建立索引；
• 尽量避免使用select * …; 用具体的字段代替，尽量不要返回使用不到的字段；
• 少使用**like %**查询，否则全表扫描；
• 控制使用自定义函数；
• 单条语句后面添加 limit 1会停止全表扫描；
• where字句中不使用**!=**；
• 尽量避免NULL值判断；
• 尽量避免or连接条件，可以使用union代替；
• 尽量避免使用in、not in

表的拆分

• 垂直拆分
  表中列太多，故将其分为多个表，每个子表是其中的几个列，将经常查询的列放到一起有利于加快查询的速度，blob/text类型字段放到另一个表中；
• 水平拆分
  按记录行拆分，减少每个表的记录量。

MySQL进一步的操作请见enginelong的博客
OK。。。