MySQL数据库企业级开发技术(上篇)

使用语言 MySQL

使用工具 Navicat Premium 16

代码能力快速提升小方法，看完代码自己敲一遍，十分有用

拖动表名到查询文件中就可以直接把名字拉进来
中括号，就代表可写可不写

1.事务

1.1 需要事务的原因

1.2 事务介绍

1.2.1 事务基础介绍

1.2.2 ACID特性详细剖析

1.3 在MySQL中使用事务

1.3.1 UNDO日志和REDO日志

1.3.2 执行事务的SQL语句语法格式

1.3.3 MySQL数据库通过日志实现事务的流程

1.3.4 设置自动提交关闭或开启

1.4 使用事务的原则

2.索引

2.1 索引简介

2.2 索引的分类

2.2.1 普通索引

2.2.2 唯一索引

2.2.3 主键索引

2.2.4 全文索引

2.3 在MySQL中使用索引

2.3.1 创建索引

2.3.2 创建索引示例

2.3.3 使用注意事项

2.3.4 使用索引经验

2.3.5 查看索引

2.3.6 删除索引

1.事务

1.1 需要事务的原因

在之前默认所有的数据库操作都会被正确执行，这是一种非常理想的情况。对应不是很重要的数据，如日志数据，即使某条日志数据写入数据库失败，对应整个应用来说也不会产生非常严重的影响。但是在某些场景下，数据操作失败会产生非常严重的后果。

例如:与金融相关的应用在任何情况下都不允许出现问题，因为这些数据不是一个数字，而是等值的钱，系统中的钱凭空变多或变少都会造成非常重大的影响。

那么该如何解决这个问题呢?MySQL通过事务机制保证数据的完整性。

1.2 事务介绍

1.2.1 事务基础介绍

事务(Transaction)指将一系列数据操作捆绑成一个整体并统一管理的机制。可以把事务理解成一个操作序列，它包含了一组数据库操作命令，并且把所有的命令作为一个整体向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。因此，事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是作为最小的控制单元被使用的，它特别适用于多用户同时操作的数据库系统。
在使用事务时，要注意事务时作为单个逻辑工作单元执行的一系列操作。这个逻辑工作单元必须有4个特性，即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)及持久性(Durability)，这些特性通常简称ACID。

1.2.2 ACID特性详细剖析

原子性(Atomicity)

事务是一个完整的操作。事务的各元素是不可分的，这种特性也被称为原子性。事务中的所有元素必须作为一个整体提交或回滚(Rollback)。若事务中的任何元素失败，则整个事务失败。
以支付平台转账事务为例，若该事务已提交，则这两个账号的数据会更新；若由于某种因素，事务在成功更新这两个账户之前终止，则不会更新这两个账户的余额，并且会撤销对任何账户余额的修改，也就是说，事务不能被部分提交。
回滚泛指数据库更新失败，返回上一次正确状态的行为(撤销)。数据库中的回滚指在数据库中的数据发生错误时为保证数据库的完整性将数据还原到发生错误以前状态的操作。

一致性(Consistency)

当事务完成时，数据必须处于一致状态。也就是，在事务开始之前，数据库中存储的数据处于一致状态。在正在进行的事务中，数据可能处于不一致的状态，如数据可能部分被修改。然而，当事务成功完成时，数据必须再次恢复已知的一致状态。通过事务对数据所做的修改不能损坏数据，或者是事务不能使数据存储处于不稳定状态。
以支付平台转账事务为例，在事务开启之前，所有账户余额的总额总处于一致状态。在事务进行的过程中，一个账号的余额减少了，而另一个账号余额尚未修改。因此，所有账户余额的总额处于不一致状态。事务完成以后，账号余额的总额再次恢复已知的一致状态。
就是代表两个的执行状态都是一致的，要么都是执行成功，要么都是执行失败

隔离性(Isolation)

对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖或影响其他事务。
就是各事务之间不要连在一起，例如A给B转账的事务不要与A给C转账的事务连在一起

持久性(Durability)

事务的持久性指不管系统是否发生故障，事务处理的结果都是永久的，SQL语句默认也是永久的(可以修改)。
一个事务成功完成之后对于数据库的改变是永久性的，即使系统出现故障也是如此。也就是说，一旦事务被提交，事务的效果会被永久的保留在数据库中。

1.3 在MySQL中使用事务

1.3.1 UNDO日志和REDO日志

在MySQL中使用事务，首先要保证数据表所使用的存储引擎支持数据库。常用的存储引擎InnoDB支持事务操作，而MyISAM不支持事务操作。InnoDB存储引擎主要通过UNDO日志和REDO日志实现对事务的支持。MySQL的另一种存储引擎BDB也支持事务。

任何一种数据库都拥有各种各样的日志，用来记录数据库的运行情况、日常操作、错误信息等，MySQL数据库也不例外。例如，当用户root登录到MySQL服务器时，就会在日志文件里记录该用户的登录时间、执行操作等。为了维护MySQL服务器，经常需要在MySQL中进行日志操作。

UNDO日志

复制事务执行前的数据，用于在事务发生异常时回滚数据

REDO日志

记录在事务执行中每条对数据进行更新的操作。当事务被提交时，该内容被刷新到磁盘中。

1.3.2 执行事务的SQL语句语法格式

在默认设置下，每个SQL语句就是一个事务，即执行SQL语句后自动提交。为了达到将几个操作作为一个整体的目的，需要使用begin或start transaction开启一个事务，或者执行set autocommit=0，禁止当前会话的自动提交后，后面的SQL语句将作为事务中的语句被一同提交。两种实现事务的语法格式如下:

begin或start transition语句标志着事务的起始点，运行其后的SQL语句不会被提交生效，直到运行到commit语句。commit语句标志着一个事务被成功提交。自事务开始至提交语句之间执行的所有数据更新将永久地保存在数据库数据文件中，并释放连接时占用的资源。(注:添加测试数据可以右键表然后点击数据生成，就可以选择生成多少条数据，或者使用set autocommit=0打头)

回滚(撤销)事务的语法格式如下

事务可以被成功提交，也可能因为执行某些语句不成功或其他异常情况而终止。为了保证事务的一致性，要使用rollback语句清除自事务起始点至该语句所做的所有数据更新操作，将数据状态回滚到事务开始之前，并释放由事务控制的资源。(可以返回上一步，但是只能在事务未提交前回滚)

1.3.3 MySQL数据库通过日志实现事务的流程

begin或start transaction语句后面的SQL语句对数据库中数据的更新操作都将记录在事务日志中，直至遇到rollback语句或commit语句。如果数据中的某一项操作失败且执行了rollback语句，那么在开启事务语句之后的所有更新数据都能回滚到事务开始前的的状态，若事务中的所有操作都正确完成，并且使用commit语句向数据库提交更新数据，则此时的数据有处在新的一致状态。

1.3.4 设置自动提交关闭或开启

在之前提到了在MySQL中默认每条SQL语句都是一条事务，执行每条SQL语句后就会被自动提交。MySQL运行修改默认设置——autocommit的值，即在一条SQL语句执行后并不会被自动提交。只有在执行commit语句后才会被提交，或者在执行了rollback语句后进行回滚。

设置自动提交关闭或开启的语法格式如下:

set autocommit=0|1;

将autocommit参数值设置为0时，表示关闭自动提交，将autocommit设置为1时，表示开启自动提交。当执行set autocommit=0后，即关闭自动提交，从下一条SQL语句开始则开启新事务，需执行commit语句或rollback语句，结束该事务。

1.4 使用事务的原则

事务具备ACID的特性，是不是可以尽可能多地在开发过程中使用事务呢？当然不可以，因为为了实现ACID的特性付出了额外的代价。在实际开发过程中，使用事务要遵循以下原则。
事务尽可能简短。事务启动至结束期间会在数据库管理系统中占用大量资源，以保证事务的原子性、一致性、隔离性和持久性。在多用户系统中，较大的事务将会占用系统的大量资源，使系统不堪重负，会影响软件的运行性能，甚至导致系统崩溃。
事务中访问的数据量尽量少。当并发执行事务处理，事务操作的数据量越少，事务之间对操作数据在争夺就越少。
查询数据时尽量不要使用事务。对数据进行浏览查询操作并不会更新数据库的数据，因此，尽量不使用事务查询数据，避免占用过量的系统资源。
在处理事务的过程中，家里不要出现等待用户输入的操作。如果需要等待用户输入数据，那么事务回长时间地占用资源，有可能造成系统阻塞；

2.索引

我们已经回使用SQL进行数据查询，感觉查询速度非常迅速，似乎只要SQL语句开始执行，数据库就能马上返回查询结果。造成这种假象的原因是数据库中的数据量非常小，数据库可以在极短的时间内完成数据的扫描，返回查询结果。但是当数据量十分大时，任何因此全数据表的扫描都会消耗非常多的时间。

2.1 索引简介

为了解决大量数据的查询效率问题，MySQL数据库提供索引来提高数据库的查询效率。索引的目的是使有序的内容在查找时消耗的时间更少，效率更高，索引是数据表中的一列值或多列值进行排序的一种结构。这种结构类似于书籍目录的功能。在一本书中，利用目录可以快速查找所需信息，而无需阅读整本书。在数据库中，索引使数据库程序无需对整张表进行扫描就可以在其中找到所需数据。书中的目录是一个词语列表，注明了包含各词的页码。在数据库中，因为数据被存储在数据表中，索引创建在数据表对象上，由表中的一个字段或多个字段生成的键组成的。这些键被存储在数据结构(B-树或哈希表)中，通过MySQL可以快速、有效的查找与键相关联的字段。根据索引的存储类型，可以将索引分为B-树索引(btree)和哈希索引(HASH)。InnoDB和MyISAN存储引擎支持B-树索引。
通过索引，数据库可以极大地提升数据的查询效率，改善数据库的性能。同时，增删改操作也变慢了
这里讲脚本添加索引，手动添加也是可以的，只需选中表，然后设计表，然后点击上方选项卡的索引选项然后添加索引即可。

2.2 索引的分类

MySQL中常用的索引包括普通索引(index)、唯一索引(unique)、主键索引(primary key)和全文索引(fulltext)。不同类型索引的特点及使用场景各不相同，以商场的会员卡系统的会员表为例，解释不同索引对应的使用场景。会员卡系统的会员表创建代码如下:

2.2.1 普通索引

普通索引使MySQL中的基本索引类型，该类索引对被索引的数据列没有任何限制。普通索引允许在定义索引的列中插入重复值和空值。它的唯一任务是加快对数据的访问速度。因此，应该只为那些常出现在查询条件(where)或排序条件(order by)中的数据列创建索引。

在会员卡系统中，如果在会员姓名列上创建索引，那么索引类型应选择普通索引。

2.2.2 唯一索引

唯一索引不允许两行具有相同的索引值。
若现有数据中存在重复的键值，则大多数数据库不允许创建唯一索引。若已创建唯一索引，则当心数据使表中的键值重复时，数据库也会拒绝接受此数据。但创建了唯一索引的列允许有空值。

在会员卡系统中，如果在会员身份证号列上创建索引，那么索引类型应选择唯一索引。

2.2.3 主键索引

主键索引使一种特殊的唯一索引，不允许有空值。在创建数据库的时候如果指定了主键，数据库系统就会自动创建主键索引。
添加主键索引就是创建主键，因为创建主键时会自动添加主键索引

在会员卡系统中，会员编号是会员表的主键，同时数据库系统也会在此列上创建主键索引。

2.2.4 全文索引

全文索引的作用是在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引只能在char、varchar或text类型的列上创建，主要用于在大量文本文字中搜索字符串，使用全文索引的效率高于使用SQL的like关键字的效率。MySQL中只有InnoDB和MyISAM存储引擎支持全文索引。
搜索很长一篇文章的时，使用全文索引的效果较好。因此，要依据文本的长度决定是否使用全文索引，对一两行字这样的短文本可以直接使用普通索引。通常在会员卡系统中，根据经验，用户的备注并不会很长，所以备注列使用普通索引也是可以的。

在会员卡系统中，会员备注列如果需要创建索引，那么索引类型应选择全文索引。

2.3 在MySQL中使用索引

2.3.1 创建索引

使用create index语句可以在已经存在的表上添加索引，基本语句如下

create [unique|fulltext|spatial] index index_name on table_name(column_name[length]...);

索引名推荐以‘对应的索引_’开头;

unique\|fulltext\|spaial:分别表示唯一索引、全文索引和空间索引，为可选参数。	spatial为设置空间索引的关键字若创建索引时未指定创建索引的类型，则创建的索引为普通索引。通过create index语句无法创建主键索引，需要使用"alter table tablename ADD primary key(column)"语句创建主键索引
index_name	指定索引名
table_name	指定创建索引的表名
column_name	指定需要创建索引的列
length	指定索引长度、可选参数，只有字符串类型才能指定索引长度

2.3.2 创建索引示例

MySQL还支持其他类型的索引、如复合索引和空间索引等，这些索引适用于不同的使用场景；添加索引应放在添加完成数据之前，不然添加数据之后再添加索引会很慢；数据删除索引不会受到影响。

2.3.3 使用注意事项

使用索引可加快数据检索速度，但没有必要为每个列都建立索引。因为所以自身也需要维护，并占用一定的资源，可以按照下列标准选择建立索引的列。

频繁搜索的列。(写在select后的()中的列)
经常用作查询选择的列。(where条件)
经常用作排序、分组的列。(order by 和 group by)
经常用作连接的列(主键/外键)。(这个你在创建主键时会自动创键主键约束)

注意不要使用下面的列创建索引。

仅包含几个不同值列表的列。(如性别，只有男和女)
表中仅包含几行。为小型数据表创建索引可能不太实用，因为在索引中搜索数据所花的时间比在数据表中逐行搜索数据所花的时间长。

2.3.4 使用索引经验

在SQL语句中，特别是在select语句中正确使用索引可以大大提高查询速度，保证应用程序的运行性能。软件工程师在编写和调试SQL语句时，要具有优化SQL语句的意识。以下经验可以在实际工作中参考:

查询时减少使用*返回全部列(一般企业是禁止使用的)，不要返回不需要的列。
索引应该尽量小，在字节数小的列上建立索引。
where子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前(不然有可能索引无法发挥作用)。
避免在order by子句中使用表达式。
根据业务数据发生频率，定期重新生成或重新组织索引，整理碎片。

2.3.5 查看索引

在MySQL中，可以使用show index语句查看已创建的索引，基本语句如下。

show index from table_name;

示例

运行结果

table 创建索引的表
not_unique: 索引非唯一，1代表非唯一索引，0代表唯一索引。主键索引和唯一索引在这一列上的值都为0
key_name: 索引的名称
Seq_in_index: 该列在索引中的位置。若索引是单列的，则该列值为1，组合索引为每列在索引定义中的顺序
Column_name: 定义索引的列字段
Collation: 索引的排序方法有值'A'(升序)或null(无排序)，全文索引是无排序的
Sub_part: 索引的长度
null: 该列是否能为空值
index_type: 索引的类型

2.3.6 删除索引

删除索引的语法格式如下:

drop index index_name on table_name;

删除viptest表中建立在ramark列上的全文索引。关键代码如下:

索引的删除需要注意

删除表时，该表的所有索引将同时被删除
删除表中的列时，若要删除的列为索引的组成部分，则该列也会从索引中被删除
如果组成索引的所有列都被删除，则整个索引被删除。
删除数据不会影响索引