MySQL入门学习(五)

标签：事务 入门 视图 学习 索引 emp MySQL 数据 主键

  在本篇文章中，我们会将MySQL剩余的基础知识讲解完毕，这也就是我们MySQL专栏里的收官之作辣~~~

事务

  在MySQL当中，什么叫做事务呢？一个事务其实就是一个完整的业务逻辑，可以举个生活中常见的例子：

  假设A要向B转账1000元，那么我们需要在数据库中做以下几个步骤：①将A账户的钱减去10000（update语句）②将B账户的钱加上10000（update语句）那么这两个步骤连在一起，就可以作为一个事务。可以看成多个动作的结合，打一套组合拳。

  对于一个事务里的多个动作来说，它们必须同时成功或者同时失败，这样才能保证数据的可靠性和有效性。另外，只有DML语句（insert、delete、update）才有事务这一说法，因为它们能够改变数据，而数据安全是数据库的重中之重，这也是事务存在的意义。因此可以如下定义事务：

  事务=批量的DML语句同时成功，或者同时失败

1. 事务是如何做到多条DML语句同时成功和同时失败

  前一篇文章提到过InnoDB存储引擎，它的最大优势就是支持事务，从而具备了安全的特性，因此想要使用事务，就必须确保表使用的是InnoDB存储引擎，它能提供一组用来记录事务性活动的日志文件。下面举个例子看看事务的具体结构：

    事务开启
    ①insert
    ②insert
    ③insert
    ④delete
    ⑤update
    ⑥…
    事务关闭（提交事务或回滚事务）

  事务开启之后，可以输入多条不同的DML语句，在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中，在次期间，我们可以提交事务，也可以回滚事务。一旦提交事务或者回滚事务，就意味着本次事务的结束，下次使用的时候还需要开启事务。下面我们来解释一下这两个名词：

  提交事务：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中；提交事务标志着事务的结束，并且是一种全部成功的结束。

  回滚事务：将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件；回滚事务标志着事务的结束。并且是一种全部失败的结束。

2. 怎么开启事务，怎么提交事务，怎么回滚事务

  在MySQL当中，默认情况下是支持自动提交事务的，也就是每写一条DML语句之后，就会自动提交一次。然而这种自动提交实际上是不符合我们的开发习惯，因为一个业务通常是需要多条DML语句共同执行才能完成的，为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条就提交一条。

  因此如何关闭这个自动提交呢？使用以下的一条命令即可：

start transaction;

  在此命令之后，我们就可以自己操作数据库中的数据，使用多条DML语句了，那么如何结束事务呢？可以执行以下命令：

commit;//提交事务，标志着前面的所有DML语句同时成功，本次事务结束
rollback;//回滚事务，标志着前面的所有DML语句同时失败，本次事务结束

  不管使用哪种办法结束事务，在一个事务的过程中，里面的DML语句还是能够执行成功的，并且影响到数据库中的数据，只不过在事务结束时通过关键字判断：是不是要将数据库恢复到事务前的状态(rollback)。

3. 事务的4个特性

1. A：原子性(Atomicity)

  说明事务是最小的工作单元，不可再分。

2. C：一致性(Consistency)

  所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。

3. I：隔离性(Isolation)

  隔离性意味着事务必须在不干扰其他进程或事务的前提下独立执行。换言之，在事务或工作单元执行完毕之前，其所访问的数据不能受系统其他部分的影响，一般在多进程或者多线程中提到。。

4. D：持久性(Durability)

  持久性表示在某个事务的执行过程中，对数据所作的所有改动都必须在事务成功结束前保存至某种物理存储设备。这样可以保证，所作的修改在任何系统瘫痪时不至于丢失。

4. 事务的隔离性(四大性质中的重点)

  其实可能大家对隔离性还是没有比较好的理解，我们可以举一个生活中的实际例子进行说明：

• 有一天下午的一点钟，某一银行需要对账了，数据员就需要从数据库里面将数据全部取出来进行汇总。但是！！由于数据过于庞大，假设需要经过2h才能将所有的数据全部导出，那么在一点到三点这个时间段，倘若还有人在银行进行取钱或者存钱，又该怎么办？？这其实就用到了事务的隔离性，因为一边是数据员在取数据，另外一边是窗口在更改数据，两边都对同一份数据进行操作，所以可能会发生意想不到的问题！！

  事务的隔离性有四个级别，我们从低级别到高级别进行一一解释：

• ①读未提交(read uncommitted)

  • 何为读未提交？？
      意思其实就是指事务A可以读取到事务B未提交的数据，按照上面的例子来说，就是数据员在导出数据的时候，将柜台人员正在修改的数据也一并导出了，甚至是柜台人员还未提交的数据！！
  • 这会导致什么问题发生？？
      会出现脏读现象。“脏读”意思就是这个数据是事务当中尚未提交的，从上面的例子来说的话，就是柜台人员可能在操作的时候发现自己操作错误了，之后在三点之后又进行了修改，最后没问题才提交事务B，可是数据员已经将包含错误的数据导出了，这也就是脏读的意思。

• ②读已提交(read committed)

  • 何为读已提交？？
      意思是事务A只能读取到事务B提交之后的数据，这样确实会解决脏读现象，因为读到的数据都是提交过后的。
  • 这还有问题吗？？
      虽然能够解决脏读现象，但是这还会导致“不可重复读”的现象发生。何为“不可重复读”呢？按照上面给的例子，在事务A的执行时间内(也就是下午一点到三点)，事务B可能提交了好几次了，可能往我们的数据库中增加了好几条数据。事务A第一次读的时候可能是3w条数据，第二次读又变成4w条数据，第三次…也就是说，在事务A执行的期间，它所依照的数据库始终是在变化的(被事务B给改变了)，因此导致数据的错误或者混乱，这也就是“不可重复读”的现象。

• ③可重复读(repeatable read)

  • 何为可重复读？？
      事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。按照上面的例子，事务A导出数据库的整个过程，都是按照下午一点那个时候的数据库状态为依据的，即使后面的数据已经被修改了，这就能很好地解决了“不可重复读”的问题，这同样也是MySQL默认的事务隔离级别。

  • 这还有问题吗？？
      会出现幻影读问题，所谓“幻影”，就是“假象”。按照上面的例子来说，就是数据库导出时候的数据已经不是真实的数据了，因为真实的数据是实实在在不断改变的，所以我们读的数据其实都是下午一点这一时刻的幻想数据，不够真实。

• ④序列化/串行化(serializable)

  • 介绍：这是最高隔离级别，效率最低，但是解决了所有的问题。这种隔离级别表示事务排队，不能并发！按照上面给的例子，就是在一点到三点这个时间段，不允许任何人进行存款或者取款，显然不满足现实需求。

  具体使用哪种事务隔离级别，还需要参考实际的要求和情况进行定夺。

5. 事务隔离级别的查看与设置

  当需要查看隔离级别时，运行以下命令：

select @@tx_isolation;
//要是没有更改过，会显示MySQL的事务默认隔离级别(可重复读)
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

  当需要设置隔离级别的时候，参考以下命令即可：

set global transaction isolation level read uncommitted;//读未提交
set global transaction isolation level read committed;//读已提交
set global transaction isolation level repeatable read;//可重复读
set global transaction isolation level serializable;//序列化/串行化

  注意：设置新的隔离级别之后，可能需要重新进入MySQL才可以更新状态。

索引

1.什么是索引？

  索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

  假设有t_user这么张表:

id	name
1	zhangsan
2	lisi
3	wangwu
4	zhaoliu
5	wuqi

  对于如下的一条SQL语句：

select * from t_user where name = 'wuqi';

  这条SQL语句会去name字段上扫描，查询条件是：name=‘wuqi’。如果name字段上没有添加索引，或者说没有给name字段创建索引，那么MySQL会进行全扫描，会将name字段上的每一个值都比对一遍，这样的效率比较低。

  注意以下几点：

    ①MySQL在查询方面主要就是两种方式，一、全表扫描；二、根据索引检索。

    ②在MySQL数据库当中索引也是需要排序的(就和我们查字典的那个字母顺序一样)，并且这个索引的排序和TreeSet数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树！在MySQL当中索引是一个B-Tree数据结构，遵循左小又大原则存放，采用中序遍历方式遍历取数据。

2.索引的实现原理(五星重要)

  在MySQL当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。在MyISAM存储引擎中，索引存储在一个.MYI文件中；在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中；在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在MySQL当中都是一个树的形式存在。 下面我们就来分析一下索引的存储原理：

  上图展示的是一棵阶数为4的B树的某一节点，通常系统以“页”的形式将其存储在我们的内存当中。而“页”中的数据又是从多个离散的“磁盘块”中读到的，并且是通过一次I/O请求将这些磁盘块的数据都打包在一张“页”当中。为了更好地控制内存，操作系统采取虚拟内存的办法为每个进程划分空间，这个空间就是以“页”为基本单位的：

  对于一些进程，它可能会分配到更大的内存缓存空间，例如：黑神话·悟空分配到了8张“页”的大小，MySQL5张“页”，4399小游戏才3张“页”。当然，对于某一进程来说，它在内存中的缓存空间往往不能支持它的正常运行，所以需要频繁地置换页(有好多种策略，这里不展开说)，再从磁盘中进行一次请求，将多个离散的磁盘块数据整合到一张“页”里，最后存到内存中，这个过程为一次I/O请求。为什么要将磁盘中的数据搞到内存呢?因为这样的计算机处理数据的速度更快，cpu不能直接访问磁盘里的数据，但是可以访问内存(就是主存)。这些都是《操作系统》的知识，我们这里就不再过多的赘述了。
  通常，B树的一个节点会被当成一张页加载到内存当中，之后通过B树“左小右大”的性质从磁盘里加载另外一个节点的数据，假设一棵B树长这样：

  这张图有点大，要放大才能看到对应的数据和结构，花了我快1h才制作完成呜呜呜~~麻烦点个关注和赞！！！每个节点中的数据就是“索引”，索引下面附带的data就是索引对应的某一条字段的地址或者真实数据。 要是节点里保存的是地址的话，就是外部存储；要是在节点里面直接存储数据的话，就是内联存储，这取决于数据库和文件系统的设置，一般为外部存储。
  假设我们要搜索35这个索引值，可以发现，在第三层就可以查找到对应的节点，也就是经过了3次磁盘的I/O读取，倘若是外部存储，就还需要增加1次I/O得到对应的数据！！
  值得注意的是：在任何数据库当中主键上都会自动添加索引对象，另外在MySQL当中，一个字段上如果有unique约束的话，也会自动创建索引对象。

3.何时添加索引

• 条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
• 条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
• 条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）

  建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

4.索引怎么创建？怎么删除？如何查看？

  创建索引的命令如下：

create index 索引名 on 表名(字段名); //为某一张表中的某一字段添加一个索引

  删除索引的命令如下：

drop index 索引名 on 表名;//将某一张表的某一索引删除

  在MySQL当中，怎么查看一个SQL语句是否使用了索引进行检索，如下操作即可：

explain select * from emp where ename = 'KING'; //在前面加一个explain关键词
//type字段为all 表示全局扫描
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

//添加索引之后查找
create index emp_ename_index on emp(ename);
explain select * from emp where ename = 'KING'; 
//type字段为ref 表示使用索引扫描
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | emp_ename_index | emp_ename_index | 33      | const |    1 | Using where |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+

4.索引会失效吗？什么情况下会失效？

  索引失效的情况有很多，这里我们举例几种常见的失效情况：

• 在‘%’或者’_'开头的模糊查询过程中

select * from emp where ename like '%T';

  ename上即使添加了索引，也不会走索引，因为模糊匹配当中以“%”开头了！所以尽量避免模糊查询的时候以“%”开始，这也是一种优化的手段/策略。

• 使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。所以这就是为什么不建议使用or的原因，但是可以用union进行替代！！

• 使用复合索引的时候，没有使用左侧的列查找，索引失效。当两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。可以参考如下的代码：

create index emp_job_sal_index on emp(job,sal); //只有用job这列进行查找,才会使用索引扫描

• 在where当中索引列参加了运算，索引失效。例如；

create index emp_sal_index on emp(sal);//为sal字段添加索引
explain select * from emp where sal = 800;//检查索引：
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

//失效案例
explain select * from emp where sal+1 = 800;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

• 在where当中索引列使用了函数，这里就不加以演示了，道理和“参与运算”一样。

视图

  何为视图？其实就是站在不同的角度去看待同一份数据。

1.怎么创建视图对象？怎么删除视图对象？

create view 视图名 as (子查询语句); //创建视图对象
drop view 视图名; //删除视图对象

2.视图有什么用
  我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作！（视图的特点：通过对视图的操作，会影响到原表数据。）那么为什么不直接操作原表，而去操作视图呢？它在实际的开发当中究竟有什么用？
  假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？可以把这条复杂的SQL语句以视图对象的形式新建。在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发，并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。举个例子：

//非常复杂的select查询语句
select 
    c.id as customer_id,
    c.name as customer_name,
    c.email as customer_email,
    count(o.id) as order_count,
    sum(o.total_amount) as total_spent,
    max(o.order_date) as last_order_date
from 
    customers c
left join 
    orders o 
on 
    c.id = o.customer_id
group by 
    c.id, c.name, c.email;

  但是这个查询的结果我们在之后还要用，所以就可以创建一个视图对象：

create view 
	customer_order_summary
as
	select 
	    c.id as customer_id,
	    c.name as customer_name,
	    c.email as customer_email,
	    count(o.id) as order_count,
	    sum(o.total_amount) as total_spent,
	    max(o.order_date) as last_order_date
	from 
	    customers c
	left join 
	    orders o 
	on 
	    c.id = o.customer_id
	group by 
	    c.id, c.name, c.email;

  那么在日后使用的时候，直接使用这个视图对象就ok，例如：

select 
	* 
from 
	customer_order_summary //非常简介
where 
	total_spent > 1000
order by 
	last_order_date desc;

  因此，可以将视图看作成中间变量，将查询的结果寄存在这个变量当中，日后在重复使用的时候能够简化过程。

导出、导入数据数据

  要将MySQL当中的数据导出的时候，可以使用以下命令：

mysqldump 数据库名 >导出文件地址 -u用户名 -p密码; //导出文件地址的文件要以.sql为后缀
mysqldump 数据库名 表名 >导出文件地址 -u用户名 -p密码; //导出某一数据库中的表

  如何将我们的数据导入呢？可以使用source关键词：

source 文件地址;

数据库设计三范式

  数据库设计范式，顾名思义就是数据库表的设计依据，教你怎么进行数据库表的设计。

1. 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

  下面看个例子：

学生编号	姓名	联系方式
1001	zhangsan	[email protected],1359999999
1002	lisi	[email protected],13699999999
1003	wangwu	[email protected],13488888888

  上面的表不满足第一范式，因为①没有主键；②联系方式还可以分为邮箱地址和电话，不满足原子性。所以，要满足第一范式，就要改成如下的格式：

学生编号(PK)	姓名	邮箱	电话
1001	zhangsan	[email protected]	1359999999
1002	lisi	[email protected]	13699999999
1003	wangwu	[email protected]	13488888888

2. 第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。

  所谓完全依赖，就是即使主键是由多个字段构成的复合主键，每一个非主键字段需要依赖这整个复合主键，不能依赖其中的部分字段。举个例子：

学号(PK)	课程号(PK)	成绩
123111	1111	90
123112	1112	70
123113	1113	80

  在上表当中，(学号，课程号)组成了复合主键，也只有当这两个值都确定的情况下，成绩才能确定，因此这个表是满足第二范式的。下面举个反例：

球员编号(PK)	比赛编号(PK)	姓名	年龄	比赛时间	比赛场地	得分
…	…	…	…	…	…	…

  在上面的表当中，(球员编号，比赛编号)组成了符合主键，其他的非主键字段还存在部分依赖的关系，如下：

• “球员编号”字段可以直接确定“姓名”和“年龄”两个字段，即(球员编号)—>(姓名，年龄)
• “比赛编号”字段可以直接确定“比赛时间”和“比赛场地”两个字段，即(比赛编号)—>(比赛时间，比赛场地)

  由于非主键字段并非是完全依赖主键的，所以不满足第二范式，但是可以进行如下改正，将其改成三张表的形式：

表名	字段
球员表	球员编号(PK)，姓名，年龄
赛程表	比赛编号(PK)，比赛时间，比赛场地
球员比赛表	球员编号(PK)，比赛编号(PK)，得分

3. 第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

  所谓的直接依赖，就是不能出现非主键字段又去依赖某一个非主键字段，要求非主键字段都是必须一个个独立的。下面还是举个例子：

球员编号(PK)	姓名	球队名称	球队教练
…	…	…	…

  可以发现，上面的“球队教练”字段是依赖于“球队名称”这个字段的，一旦“球队名称”确定了，也可以确定“球队教练”，这就未达到非主键字段独立的效果。是故，可以进行如下改正：

表名	字段
球员表	球员编号(PK)，姓名，球队名称
球队表	球队名称(PK)，球队主教练

4.关于三范式

  数据库设计三范式是理论上的，实践和理论有的时候有偏差。最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。因为在SQL当中，表和表之间连接次数越多，效率越低（笛卡尔积）。有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，SQL语句的编写难度也会降低。面试的时候把这句话说上：他就不会认为你是初级程序员了！

  关于MySQL的学习，我们就到这里辣，完结咯！！撒花~~

标签：事务,入门,视图,学习,索引,emp,MySQL,数据,主键
From： https://blog.csdn.net/bling_bling666/article/details/143984341