MySQL之事务和索引

视图

概念 ：就是有一张或者多张表组织成的结果形成的一张虚拟表，仅仅是为了更便捷的查看数据而产生的，无法使用sql语句做增删改的操作！（不推荐使用）
语句结构：
       create view 视图名 as SQL语句;

触发器

概念 ： 对表数据的增删改自动触发的功能（增前，增后，改前，改后，删前，删后）

1.关键字：trigger

创建触发器 ： create trigger 触发器的名字 before/after 增/删/改 on for each row
             begin
                   sql语句;
             end   
'''
触发器内部sql语句的结尾需要用分号，但是分号在sql语句是默认结束的意思 
为了完整的写完触发器，需要临时修改sql语句的结束符
关键字：delimiter &&
       sql语句
       delimiter;
'''  
# 触发器结束以后 需要在end后面把结束符修改回来  delimiter ;
删除触发器 drop tirgger 触发器的名字

事务

四大特性ACID

概念 ：多个操作同时进行，要么都成功，要么都失败，这也称之为事务的原子性
关键字 ：transaction 

# 事务的四大特性（ACID） :
                         A.原子性 ：要么都成功，要么都失败。
                         C.一致性 ：事务执行接收之后，数据库的完整性不被破坏
                         I.隔离性 ：多个事务之间相互隔离，互不干扰，事务提交前对其他事务不可见
                         D.持久性 ：事务一旦提交，对数据库的改变就是永久的
						 

相关代码

   '''
     事务的相关代码：
         1.start transaction 开启事务
         2.commit 事务的提交  （没有执行commit的数据是没有刷新到硬盘的）
         3.rollback 事务的回滚（一旦事务提交失败，我们需要将数据回滚到提交前的状态）
         4.savepoint 保留点(为了支持回退部分事务处理，必须在事务处理块中放置合适的占位符，这样就可以回退到保留点)      创建占位符：savepoint sp01；
           回退到占位符：rollback to sp01；
           保留点会在rollback或者commit提交后自动释放

     '''

  事务的四大并发：
      1.脏读（读未提交）
      2.不可重复读（读已提交）
      3.可重复读（幻读）
      4.串行化

隔离等级

  隔离等级：
       1.读未提交 read uncommitted
           其他事务可以读取事务已经修改的但未提交的数据，这种也称之为脏读(不正确的数据）
            # 看了还未提交的数据 脏读
                                           
       2.读已提交 read committed  （大多数数据库默认的隔离级别）
           事务a在读取一个数据，事务b也在读取这个数据并修改提交(其他事务不可见)，那么a读取到的数据可能是不一致的，这种又称之为不可重复读    
           # 数据已经修改了 但是看到的是未修改前的数据  数据不一致 
                                      
       3.可重复读 repeatable read
            可以解决脏读问题，但是出现幻读问题
            一个事务在读取数据的时候，另一个事务在这段数据插入了新的数据并提交，当再次查看这段数据的时候，会产生幻行        # 查看数据，被插入新数据，再看数据出现了幻读现象（例如，刚看的时候是3条数据，刷新一下变5条了）
             '''INNODB通过多版本并发控制(MVCC)和间隙锁策略解决此问题'''

       4.串行化 serializable
              强制事务串行，很少使用该等级                        
                                                                         
      ps: 设置隔离等级：set transaction isolation level 级别

事务日志

   事务日志 ：1.先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝
             2.提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性
               回滚行记录到某个特定版本，用来保证事务的原子性、一致性
             3.先写日志，再写磁盘，只有日志写入成功，才算事务提交成功
             4.在执行事务的过程中,使用顺序 l/O，效率比随机 I/O 快
             5.定期将内存中修改的数据刷新到磁盘中

MVCC多版本并发控制

# 是MySQL中基于乐观锁理论实现隔离级别的方式，用于实现已提交读和可重复读隔离级别的实现，也称为多版本数据库
MVCC多版本并发控制中，读操作可以分为两类：
    1.快照读（非锁定读） ：读的是记录的可见版本，不用加锁，例如select查询就是快照读
    2.当前读：读取的是记录的最新版本，并且返回当前读的记录 ，例如insert，delete 等读的都是最新数据
    
    在已提交读隔离级别，每次select查询时都会生成新的快照读
    在可重复读隔离级别，事务第一次select查询时会生成一个当前事务的全局快照读，并且只生成一次
    
    # 多版本并发控制的思想就是保存数据的历史版本，通过对数据行的多个版本管理来实现数据库的并发控制。这样就可以通过比较版本号决定数据是否显示出来，读取数据的时候不需要加锁也可以保证事务的隔离效果。
  
 InnoDB 会根据以下两个条件检查每行记录：
     1.只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务        读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
     2.行的删除版本要么未定义，要么大于当前事务版本号。可以确保事务读取到的行，在事务开始之前未被删除。
 只有符合上述两个条件的记录，才能返回作为查询结果。

存储过程（类似于python里面的函数）

语法结构

1.创建存储过程的语法结构： 
   create procedure 名字（参数1，参数2）
    delimiter 临时结束符
    begin
         sql语句;
    end 临时结束符
    delimiter ; 修改回来

2.调用创建过程：
    关键字 call 创建过程的名字()

3.参数：
     in 只可以传参 不可以返回
     out 可以返回
     inout 可以传入 也可以被返回
    
    eg:
       create porcedure t1(
           in id int,
           out name str,
           out res str  # res 需要提前定义  set @res=10
        )
       delimiter &&
       begin
            select * from 表名 ;
            set res=0;   # 用来记录传入的参数是否执行  
        end &&
        delimiter ;
        
        调用：call t1(@res)
        查看res： select @res

内置函数

只可以在MySQL里面使用！！
1.用 help 函数名 查看帮助信息
2.常见内置函数： 移除指定字符 Trim LTrim RTrim
                大小写转换 Lower Upper 
                获取左右起始指定个数字符 Left Right
                返回读音相似值(英文有效) Soundex
                日期格式 date_format
                其他日期相关函数   adddate  增加日期
                                  addtime  增加时间
                                  datediff  计算两个日期时间差

date_format相关操作：

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')   # Aug 31 2022 11:45 PM
DATE_FORMAT(NOW(),'%m-%d-%Y')            # 8-20-2022
DATE_FORMAT(NOW(),'%d %b %y')            # 31 Aug 22
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')      # 31 Aug 2022 15:25:46.635

流程控制

if语法结构

if语法结构：
         delimiter &&
         create procedure 函数名（）
         begin
             declare i int default 0；
             if i=1 then
                 select 1;
             elseif =2 then
                 select 2;
             else
                  select 3
             end if ;
          end &&
          delimiter ;

while语法结构

while语法结构：
           delimiter &&
           create procedure 函数名（）
           begin
                declare num int;
                set num =10;
                while num<10 do
                      select num
                      set num =num+1
                 end while;
           end &&
           delimiter ;

索引

1.索引是为了加快数据的查找，在mysql中索引也称为’键‘，是存储引擎为了快速找到数据的一种数据结构
可以加快查询速度的键：primary key ，unique key ，index key（只能加快查询速度，没有其他功能）
# 索引的优缺点：
  '''
  优点：加快数据的查询，降低数据库的IO成本
        通过索引列对数据进行排序，降低CPU的消耗
  缺点：索引是一张表里面存放了主键和索引字段，也要占用内存空间
       降低表的更新速度 ，更新表时，mysql不仅要保存数据，还要保存索引文件
  '''
   聚集索引：就是指表的主键 primary key
   辅助索引: 就是除了主键外的其他索引，unique key 和index key，查找数据的时候都要经过主键才能查找数据
   覆盖索引：在辅助索引中就找到了需要查询的数据

# 索引建立在表的列（字段）上  在where的字段后面加上索引才会加快查询速度

索引底层原理 （用的树）

二叉树概念 ：就是有两个分支的树，树是一种数据结构（主要为了优化数据查询，用最短的时间查到想要查询的数据）

#基于二叉树优化之后的树：
  B树:又称B-树/多叉树，除了叶子节点外，其他节点只能有两个分叉，所有节点都可以存放完整的数据(每个数据有固定大小)
        缺点：数据块是有固定大写，存储的数据不够多，存储的数据越多树的高度会越来越高，查找的时间会越来越长
        
  B+树：只在叶子节点存放数据，其他节点存放主键或索引键，层级更少，在B树基础上为叶子节点增加链表指针，非叶子节点作为叶子节点的索引
        
  B*树：在b+树基础上为其他节点也增加通道，减少查询时间

慢查询优化

# 什么是慢查询 就是在日志中记录运行比较慢的SQL语句，是指mysql记录所有执行超过long_query_time参数设定的时间阈 值的SQL语句查询 可以找出执行较慢的sql语句进行优化，提高运行速度

sql语句执行慢的原因：偶尔慢 ：db在刷新脏页，慢查询日志记录满了，内存不够，mysql关闭或者空闲的时候
                   一直慢：索引没有设计好，SQL语句没写好，mysql选错了索引

慢查询基本步骤

语法结构：explain sql语句
基本步骤：1.where 条件单表查询 先从数据最小的表开始查询 （配置慢查询日志）
         2.explain 查看执行计划，是否与预期一致
         3.order by limit排序一下，让排序的表优先查
         4.加索引时参照建索引的原则
         5.观察结果，超出预期从1开始继续分析
# sql不同的语句书写方式对于效率往往有本质的差别
            

常见索引扫描类型

1.index  ：index和all的区别，index只遍历索引树
2.range ：（一般到达这个级别即可） 索引扫描范围，只扫描带有between或者where子句中<,>查询
3.ref ：使用非唯一索引扫描或者唯一索引前缀扫描 #（根据字段的前n个字符创建的索引）
4.eq_ref ：类似于ref，区别就在于扫描的我唯一索引
5.const ：当mysql对某部分进行优化，并转换为常量时，使用这些语句
6.system ：当mysql对某部分进行优化，并转换为常量时，使用这些语句
7.null ：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

常见的慢查询优化

1.索引没有起到作用的情况（没有索引）:
              —使用like关键字查询，如果匹配的字符串第一个关键字符号为‘%’，那么索引不会起作用
              —使用多列查询语句，一个索引包含了16个字段，只有索引使用了这些字段中的第一个字段，才会起作用
2.优化数据库的表结构:
              —将字段很多的表，拆解成多个表
              —对于需要联合查询的表，可以建立中间表以提高查询效率