关于 - 数据库

SQL是数据库操作的的核心语言，语句主要有ddl和dml等。crud的dml语句都会纪录日志
mysql数据库中核心搜索引擎MyISAM与InnoDB 底层结构为b+树

一、B+Tree索引和Hash索引
      Hash索引结构的特殊性，检索效率非常高，索引的检索可以一次定位
      B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问
      Hash本身由于其特殊性，也带来了很多限制：
          1. Hash索引仅仅能满足“=”,“IN”,“<=>”查询，不能使用范围查询
          2. 组合索引中，Hash索引不能利用部分索引键查询 （组合索引中的多个列，Hash是要么全部使用，要么全部不使用）同时不支持最左匹配规则
          3. 无法进行数据的排序操作 （Hash值的大小不定，无法利用索引的数据来进行排序运算）
          4. Hash索引任何时候都不能避免表扫描 （Hash碰撞问题 无法直接得到数据，还得比较，这样效率就降）

二、B树和B+树
      B树：遵循左小右大原则 每个节点都存储key和data，并且叶子节点指针为null 不包含任何关键字信息
      B+树：内部节点不存储数据 只存储索引，数据都存储在叶子节点，内部结点中的key都按照从小到大的顺序排列，每个叶子节点增加一个指向相邻叶子节点的指针
      B+树相对于B树的优势：
      1.内部结点只作索引使用, 去掉了其中指向data record的指针, 使得每个结点中能够存放更多的key， 树的层高能进一步被压缩, 使得检索的时间更短
      2.叶子节点形成了一个有序链表，更加便于查找

三、mysql中常用2种存储引擎 (MyISAM与InnoDB存储引擎)
      MyISAM
     1.表的查询、更新、插入的效率要比InnoDB高，不提供事务支持，不支持外键
     2.只支持表级锁，不存在死锁问题
     3.MyISAM存储了表中的行数记录，执行SELECT COUNT() 的时候可以直接获取到结果，而InnoDB需要扫描全部数据后得到结果
     4.表在大量高并发的读写下会容易出现表损坏的情况，Mysql自带的myisamchk工具可以进行修复
     5.索引都是保存行的地址
     InnoDB
     1.InnoDB支持事务，支持行级锁和表级锁默认为行级锁
     2.InnoDB 中不保存表的具体行数 count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行
     3.索引保存的是主建索引的值
     myisam的索引以表名+.MYI文件分别保存 ，innodb的索引和数据一起保存在表空间里
     写多读少的应用中还是Innodb插入性能更稳定，在并发情况下也能基本，如果是对读取速度要求比较快的应用还是选MyISAM
     MyISAM对系统资源占用很低，但是Innodb对磁盘占用却很高，应该是对事务控制多了很多需要记录的日志

四、union和union all
     union对结果集进行排序运算，删除重复的记录再返回结果
     union all将两个结果合并后就返回
     union在几个步骤基础上还要重新扫表，所以效率没有union all高，具体使用根据业务选择

五、truncate和delete
      delete是数据操作语言（DML）命令；而truncate是数据定义语言（DDL）命令ddl的特点自动commit;所以rollback操作没有效果
      delete命令采用行级锁定，表中的每一行都被锁定以进行删除；
      truncate命令采用表级锁定，锁定了整个表以删除所有记录
     需要注意的是delete没删除一行 都会在事物日志中纪录下来，所以可以回滚。反之 使用truncate不会纪录日志，误删则无法回滚

七、百万级别数据量查询慢
      影响查询速度：
     1.先逐一排查拆分找到最慢的SQL 做对应的sql优化，然后逐一查看它的执行计划，这样就知道问题出在哪儿了
     2.一般索引可以解决
     3.大量的并发：数据连接数被占满（ max_connection默认 100，一般把连接数设置得大一些）
     4.大表带来的问题，修改表结构也会锁表时间较长
        大数据量的表中筛选出来其中一部分数据会产生大量的磁盘 io -> 消耗磁盘性能的计划任务 降低磁盘效率
        大表带来的问题 可以分库分表把一张大表分成多个小表，但是跨分区数据的查询和统计会比较麻烦
     5.大事务 锁定数据太多，回滚时间长，执行时间长 造成大量阻塞和锁超时
        避免一次处理太多数据，可以分批次处理
     影响了MySQL性能：
     1.服务器硬件
     2.服务器系统（系统参数优化）
     3.存储引擎。 MyISAM： 不支持事务，表级锁。 InnoDB: 支持事务，支持行级锁，事务 ACID
     4.数据库参数配置（内存配置相关参数 每个连接 单独 使用的内存，缓存池分配内存）
     性能优化顺序 ：数据库结构设计和sql语句、存储引擎的选择和参数配置、系统选择 硬件升级

八、表分区
      表分区是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分,从逻辑上看，只有一张表，但是底层却是由多个物理分区组成
      常见的存储引擎MyISAM、InnoDB都支持分区,MySQL数据库支持的分区类型为水平分区（指将同一个表中不同行的记录分配到不同的物理文件中），并不支持垂直分区（指将同一表中不同列的记录分配到不同的物理文件中）
      MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中,MySQL数据库还不支持全局分区
      四种分区类型:
      RANGE分区：允许将数据划分不同范围。例如可以将一个表通过日期年份划分成若干个分区 less than
      LIST分区：允许系统通过预定义的列表的值来对数据进行分割 in。按照List中的值分区，与RANGE的区别是，range分区的区间范围值是连续的
      HASH分区：允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区
                        将数据均匀地分布到预先定义的各个分区中，保证各分区的数据量大致都是一样的,RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保                          存在哪个分区中；而在HASH分区中，MySQL自动完成这些工作
      KEY分区：Hash模式的一种延伸,KEY分区使用MySQL数据库提供的函数进行分区
      数据库的应用分为两类：一类是OLTP（在线事务处理），如Blog、电子商务、网络游戏等；另一类是OLAP（在线分析处理），如数据仓库、数据集市
      OLAP的应用分区是可以很好地提高查询的性能，因为OLAP应用大多数查询需要频繁地扫描一张很大的表
      OLTP的应用通常不可能会获取一张大表10%的数据，大部分都是通过索引返回几条记录即可,根据B+树索引的原理,一般的B+树不分区 需要2~3次的磁盘IO
      如10个分区 每个分区的查询开销为2次IO 则一共需要20次IO 所以OLTP场景中使用分区设计不好会带来严重的性能问题

九、MVCC

    并发访问（读或写）数据库时，对正在事务内处理的数据做多版本的管理。以达到用来避免写操作的阻塞，从而引发读操作的并发问题
    数据库表的隐藏列
    DB_TRX_ID：数据行的版本号、DB_ROLL_PT：删除版本号
    insert : DB_TRX_ID 更新版本号
    delete: DB_ROLL_PT 更新版本号
    update: copy新增一行, DB_TRX_ID 更新版本号
    数据查询规则
    1、查询数据行版本大于或等于当前事务版本的数据行
    2、查找删除版本号为null，或大于当前事务版本号的记录
    undo撤销操作
       undo log指事务开始之前，在操作任何数据之前，首先将需要操作的数据备份到一个地方
    undo Log实现事务原子性：
    事务处理过程中出现了错误或者用户执行了ROLLBACK语句，mysql可以利用Undo Log中的备份将数据恢复到事务开始之前的状态
    undo Log实现多版本并发控制：
       事务未提交之前，Undo保存了未提交之前的版本数据，Undo中的数据可作为数据旧版本快照供其他并发事务进行快照读取
    sql读取的数据是快照版本，也就是历史版本，普通的select就是快照读
    innodb快照读，数据的读取将由cache(事务修改过的数据)+undo buffer(备份原本数据)两部分组成
    Redo Log
       Redo(重做) 以恢复操作为目的
       Redo Log 指事务操作的任何数据，将最新的数据备份到一个地方
    Redo Log持久：
       不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo中
    Redo Log是为了实现事务的持久性而出现的产物
   user -> redo Log(新日志备份) -> 故障未写入 -> mysql启动恢复 || 事务未提交新备份持久化
   user -> Undo Log(旧日志备份) -> rollback回滚旧日志数据

      MVCC每个用户连接数据库时，看到的都是某一特定时刻的数据库快照Undo Log，在B的事务没有提交之前，A始终读到的是某一特定时刻的数据库快照，不会读到B事务中的数据修改情况，直到B事务提交，才会读取B的修改内容
      支持MVCC的数据库，在更新某些数据时，并不对新数据覆盖旧数据，而是标记旧数据是过时的，同时在其他地方新增一个数据版本，写事务会新建一个新的数据版本，而读事务访问的是旧的数据版本，直到写事务提交，读事务才会访问到这个新的数据版本
      保存时比较版本号，如果成功commit，则覆盖原记录；失败则rollback，他就是乐观锁的一整实现方式，就是每行都有版本号，保存时根据版本号决定是否成功
      MVCC最大的好处：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，现阶段几乎所有的RDBMS，都支持了MVCC

十、乐观锁和悲观锁
     悲观锁和乐观锁是数据库用来保证数据并发安全防止更新丢失的两种方法
     悲观锁
       select ... for update语句执行中所有扫描过的行都会被锁上
     例：
        start transaction
        select * from table_name where id =1 for update；
        update tab set name = a where id = 1;
     此时另一个事物执行条件id=1 如果上面sql 没有commit 或 回滚 ，则会一直等待
     乐观锁
         给表加一个版本号字段，是数据库实现乐观锁的一种方式，适合用在取锁失败概率比较小的场景，可以提升系统并发性能