MySQL笔记6

事务
1、什么是事务？    
    一个事务其实就是一个完整的业务逻辑。
    是一个最小的工作单元，不可再分，和java中的同步代码块类似。
    什么是一个完整的业务逻辑？
        假设转账，从A账户向B账户中转账10000
        将A账户的钱减去10000，
        将B账户的钱加上10000，
        这就是一个完整的业务逻辑。
        
        以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分（原子性）。
2、只有DML：数据操作语言，才会有数据这么一说，其他语句和事务无关。
    insert 
    delete
    update
    只有以上的三个语句和事务有关系。
    因为只有以上三个语句是数据库中对数据进行增、删、改的。
    只要你的操作一旦涉及到数据的增、删、改那么久一定要考虑安全问题。
    就像java中的synchronized关键字一样，多线程对共享数据进行修改必须保证数据的安全。
    数据安全第一位。
3、假如说所有的业务，只有一条DML语句就能完成，还有必要存在事务机制吗？
    没有必要。
4、事务是怎样做到多条DML语句同时成功和同时失败的？
    InnoDB存储引擎：提供了一组用来记录事务性活动的日志文件。
    事务开启了：
        insert
        insert
        delete
        update
        update
        update
    事务结束了！
    在事务的执行过程中，每一条DML语句的操作，都会记录到“事务性活动的日志”当中。
    在事务的执行过程中，我们可以提交事务，也可以回滚事务。
5、提交事务？
    清空记录事务性活动的日志文件，将数据彻底持久化到数据库表中。
    提交事务标志着，事务的结束，并且是一种全部成功的结束。
   回滚事务？
    将之前所有的DML操作全部撤销，并且清空记录事务性活动的日志文件。
    回滚事务标志着事务的结束。并且是一种全部失败的结束。
6、怎么提交事务？怎么回滚事务？
    提交事务：commit;语句
    回滚事务：rollback;语句（回滚最远只能混滚到上一次的提交点）
    事务对应的英语单词是：transaction
7、测试一下在MySQL当中默认的事务行为是怎样的？
    MySQL默认情况下是支持自动提交事务的。（自动提交）
    什么是自动提交？
        每执行一条DML语句则提交一次。
8、怎么将MySQL的自动提交机制关闭？
    先执行start transaction;
    这行指令执行代表开启事务，关闭MySQL的事务自动提交机制。
    等待执行commit;或者rollback;这个事务就结束了。
    其实没有执行commit;的时候查询表中的数据就已经更改了，但是可以通过rollback滚回去。
    执行了commit;语句，rollback就只能滚到commit;执行的地方了。
    也就是说：一但commit或者rollback执行了，start transaction;开启事务，就失效了
9、事务包括四个特性：
    原子性：（A）
        说明事务是最小的工作单元，不可再分。    
    一致性：(C)
        所有事务要求在同一个事务当中，所有的操作必须同时成功或者同时失败，保证数据的一致性。
    隔离性：(I)
        A事务和B事务之间具有一定的隔离。
        A事务在操作一张表，B事务也在操作这张表会怎样？？？
    持久性：(D)
        事务最终结束的一个保障，事务提交，就相当于没有保存到硬盘上的数据保存到硬盘上！

事务的隔离性
1、隔离性是事务的四大特性之一。
2、A教室和B教室之间中间有一道墙，这道墙可以很厚，也可以很薄。这就是事务的隔离级别。
    这道墙越厚，表示隔离级别就越高。
3、事务和事务之间的隔离级别有哪些呐？4个级别
    读未提交：read uncommitted（最低的隔离级别）《没有提交就读到了》
        事务A可以读取到事务B未提交的数据
        这种隔离级别存在的问题就是“脏读（dirty read）现象”！我们称读到了脏数据。
        这种隔离级别一般都是理论上的，大都数数据库隔离级别都是二档起步。
    读已提交：read committed《提交之后才能读到》
        事务A只能读取到事务B事务提交之后的数据。
        这种隔离级别解决了“脏读(dirty read)现象”。
        这种隔离及类别存在什么问题？不可重复读取数据。
        什么是不可重复读取数据？
            在数据开启之后，第一次读取到的数据是3条，当前事务还没结束，
            可能第二次读取的时候，读取到的数据就是4条，3不等于4，称为不可重复读取数据。
        这种隔离级别是比较真是的数据，每一次读到的数据绝对是真是的数据。
        Oracle数据库默认的隔离级别就是“读已提交”
    可重复读：repeatable read《别的事务提交之后也读不到，永远读取的都是当前事务刚开启事务时的数据》
        事务A开启之后，不管是多久，每一次在事务A中读取到的数据是一致的。
        即使事务B已经将数据修改并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。
        不管其他事务做了怎样的修改，读取的永远都是当前(A)事务开启时的数据库中的数据.
        可重复读解决了什么问题？
            解决了“不可重复读”问题。
        可重复读存在社么问题？
            可能会存在幻影读。
            每一次读取到的数据都是幻象。不够真实！
        MySQL中默认的书屋隔离级别时：可重复读。
    序列化/串行化：serializable（最高的隔离级别）
        这是最高隔离级别，效率最低。解决了所有问题。
        这种隔离级别表示事务排队，不能并发！排队执行，类似于Java中的synchronized。
        每一次读取到的数据都是最真实的，并且效率数最低的。
4、查看当前的隔离级别
    select @@tx_isolation
    mysql>  select @@tx_isolation;
    +-----------------+
    | @@tx_isolation  |
    +-----------------+
    | REPEATABLE-READ |  //可以发现MySQL的默认事务隔离级别时repeatable-read，可重复读
    +-----------------+
5、怎样设置全局的隔离级别：
    set global transation isolation level 隔离级别;
    然后退出MySQL客户端在重新进一下。
    查询当前事务隔离级别：
    mysql> select @@tx_isolation;
    +------------------+
    | @@tx_isolation   |
    +------------------+
    | READ-UNCOMMITTED |
    +------------------+
6、实验：
    可以打开两个dos，来模拟并行的两个事务。
        
索引
1、什么是索引？
    索引在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
    一张表的一个字段可以添加一个索引，当然多个字段联合起来也可以添加一个索引。
    索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
2、mysql在数据方面，查询一张表的时候有两种检索方式：
        第一种方式：全表扫描
        第二种方式：根据索引检索（效率很高）

    索引为什么可以提高检索效率呢？
        其实最根本的原理是缩小了扫描的范围，缩小扫描区间，进行区间查找。
    
    索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中
    的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改
    这样就不适合添加索引，因为数据一旦修改，索引需要重新排序（因为排序了才会有区间查找这一说），进行维护。
    建议通过主键去查询，建议通过unique约束的字段来查询。

    添加索引是给某一个字段，或者说某些字段添加索引。

    select ename,sal from emp where ename = 'SMITH';
    当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。
    当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。
3、在MySQL当中索引也是需要排序的，并且这个排序和TreeSet数据结构相同。
   TreeSet底层是一个自平衡二叉树！
   MySQL索引也是采用B-Tree这种数据结构。
   最寻左小右大方式存放，采用“中序遍历方式”取数据。
4、主键和具有unique约束的字段自动会添加索引。
5、什么时候考虑给字段添加索引？（满足什么条件）
    * 数据量庞大。（根据客户的需求，根据线上的环境）
    * 该字段很少的DML操作。（因为字段进行修改操作，索引也需要重新排序）
    * 该字段经常出现在where子句中。（经常根据哪个字段查询）
6、怎么创建索引对象？怎么删除索引对象？
    创建索引对象：
        create index 索引名称 on 表名(字段名); 【多个字段之间使用逗号分割】
    删除索引对象： 
        drop index 索引名称 on 表名;        
7、索引的实现原理？
    通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，
    最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率
    是最高的。
        select ename from emp where ename = 'SMITH';
        通过索引转换为：
        select ename from emp where 物理地址 = 0x3;【直接通过物理地址去取数据，不用再扫描这张表了】
8、可以通过“explain 查询语句”，查看当前的查询语句是否使用了索引进行查找：
    type=ALL代表全扫面
    type=ref代表索引扫描
9、索引的分类？索引是各种数据库进行优化的重要手段。优化时优先考虑的就是索引。
    单一索引：给单个字段添加索引
    复合索引: 给多个字段联合起来添加1个索引
    主键索引：主键上会自动添加索引
    唯一索引：有unique约束的字段上会自动添加索引
10、索引什么时候失效？
    第一种情况：    
        select ename from emp where ename like '%A%';
        模糊查询的时候，第一个通配符使用的是%或者_，这个时候索引是失效的。    
        ename上即使添加了索引也不会走索引，因为模糊匹配当中以“%”开头了！没有办法通过索引进行检索。
        因为dbms查找的时候在比对的时候必须知道第一个字母是啥，才可以在二叉树上进行比对。
        尽量避免模糊查询的时候以%或者_开始。
    第二种情况：
        使用or的时候，如果使用or那么要求or两边的条件字段都要有索引，才会走索引。
    第三种情况：
        使用符合索引的时候，没有使用左侧的列查找，索引失效。
    第四种情况：
        索引列参加了运算。
    第五种情况：
        在where当中索引列使用了函数。
        
视图（view）
1、什么是视图？
    view：站在不同的角度去看待同一份数据。
2、怎样创建视图对象？
        create view 视图名 as 查询语句;  把“查询语句”的查询结果当作一个视图来看待
   怎么删除视图对象？
        drop view 视图名;
3、用视图做什么？
    我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作。
4、mysql视图没有空间。MySQL视图是一种虚拟存在的表,并不实际存在于数据库中,不分配空间。
5、可以说视图就是原表的一个引用，视图中的数据更新了，原表中的数据也会更新，原表中的数据更新了，视图中的数据也会更新。

DBA常用命令
1、数据的导入和导出
    导出:
        注意在windows的dos命令窗口中执行这条指令：
            mysqldump 数据库名 [表名] > 文件名 ;  【省略表名表示导出数据库中所有的表,文件名都是带路径的】
     导入：
        source sql文件名;【文件名都是带路径的】
        
数据库设计三范式
1、什么是数据库设计范式？
    数据库表的设计依据。
    数据库范式可以避免数据冗余,减少数据库的存储空间,并且减轻维护数据完整性的成本。
2、数据库范式共有几个？
    第一范式：强调属性的原子性约束，要求属性具有原子性，不可再分解。
    第二范式：建立在第一范式的基础之上，强调记录的唯一性约束，数据表必须有一个主键，并且没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。    
    第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不能产生传递依赖。
3、多对多怎么设计？
    “多对多”三张表，两个外键。
   一对多怎么设计？
    “一对多”两张表，多的表加外键。
   一对一怎么设计？
    “一对一”字段太多，拆成多张表，“加外键+外键字段（unique）”。