青山~深入理解mysql

----------------
1)脑图整理
2)快速复习
3)生产版本还是以5.7版本
4)带着问题去学习

----------------
1)行锁

-----------------查询语句 + 更新(增删改) 是如何实现的?-----------------
1)一条查询语句是怎么执行的？(什么样的模块(名字)做了什么事情)
    1.首先是建立一个tcp连接。   自带的客户端是用的文件。
            show global status like 'Thread%';

            服务端干掉一个会话，就是干掉一个线程。 
            超过多长时间没通信，就会断开连接，默认是8个小时。
            
            永久生效:
                my.ini
                /etc/my.conf

            有缓存的功能，query_cache_type默认是OFF

            500W条，则是.

            select语句： 
                词法和语法解析--》得到一个数据结构，解析树。 Parser
                预处理器。  pre processor。  语义的解析和权限的解析。

            一条SQL语句是不是只有一种执行方式？
                比如：多条查询索引， 但是我只有一个索引。   optimizer(优化器)

                explain知道进行数据库优化。告诉怎样去执行。

                优化器： 基于成本的优化器。 

                执行计划。

            用户看到的数据结构，被放到哪里了：
                数据是放在存储引擎中的。

                速度 or 持久化
                存放历史数据的，没有修改的需求。
                数据量很大，支持数据的压缩。
                读写并发的业务里面的，读写不干扰，提高数据一致性。

            innoDb：提交回滚，行锁。一致性mvcc：读不干扰写。
            MyISAM：查询。
            Memory： 放内存中。
            CSV：上传，迁移数据。
            Archive：归档。

            为什么存储方式可以改掉？ 因为提供给服务层操作的接口是一模一样的。因此可以改变存储引擎。

            执行器，执行引擎。

            MySql：是C语言写的。

2)一条更新语句是怎么执行的?
    (1)更新：包括了增删改。

    (2)InnoDB： 至少发生一次磁盘IO，才能操作它。

        8k，是否把所有数据加载到服务端的内存中呢？ 但是磁盘操作是很慢的。

        预读取。 采用了局部性原理。就多读取一点。读取一个固定单位的大小。 page页。
            os： 4k
            而innodb：16k， 也就是不管读取多大，都是一次读取16k。
            直接把数据拿到内存里面。
            脏页：内存中的和磁盘上的不一致。

            后台线程：刷脏页(把内存中的刷到硬盘上)。

            Buffer Pool： 80%--》是性能的关键。  缓存、缓冲。
            os里面，也是有这些东西。 内存操作比磁盘操作要快。

            写满了，经过改良的LRU算法。 热区和冷区。
            redolog：重做日志。 加入：崩溃或者断电，则把数据恢复到内存，然后再刷到磁盘。~用于崩溃恢复。

            既然要redolog，那么为何不直接写入到磁盘？~延缓刷盘的时机。
                磁盘不停的转动，页和扇区，才能写入。这样的IO叫做随机IO。
                redolog则是：不需要考虑位置，只用追加覆盖。叫做顺序IO。

            undolog：撤销日志，回滚日志。 记录数据修改之前的状态。如：出现数据主键冲突等，那么就需要恢复数据。

    (3)整体流程：
        从存储引擎拿到数据，返回给server
        server层把page里面 行qingshan--》pyy
        记录undo  redo
        调用存储引擎接口，写入Buffer Pool，然后由后台线程刷新到磁盘。
        commit

    (4)
        双写缓冲： 就是page的备份。

    (5)Server层的日志： BinLog。 也就是会修改数据。   undolog也是逻辑的日志。  redolog：在某个页上，做了什么修改。
            binlog的作用：DDL和DML的语句，属于逻辑日志。
                大小没有限制，可以被追加。
                因此可以做： 基于某个时间点的恢复。 可以被所有存储引擎使用。

                binlog：数据恢复和主从复制。

            1:00，做一个数据的全量备份。
                9:00的时候，删库跑路了。
                    先把数据恢复到1点中。
                    然后解析1点到8点中。
                    把drop去掉。
                    把所有sql语句重播一遍。

            master节点上的binlog开关。
            log dump thread，把biblog发给slave节点，写入到zhongji日志。然后slave上开启SQL Thread执行一遍binlog，就同步了。

            redolog成功，但是binlog没有成功。 那么就会回滚。要不要提交事务，要以binlog为准。

            cannol(relay log)：把自己伪装成从节点。

-----------------索引原理剖析-------------------
1)存储引擎层来实现的。

2)500W数据索引的性能
    ADD INDEX: 添加索引12s
    没有索引：2s
    有索引：0.04s，因此查询性能提升了50~60倍

把所有字段的值，存到了一个地方。

3)索引到底是什么？    
    Everything： 把xxx放到了某个地方。
    DBMS中经过排序的数据结构。 以协助查询，更新数据库表中的数据结构。

    正常情况下：一条条的对比，如果要检索的，比较靠后，非常慢。

    索引：                      表数据：
        1     页码                    磁盘地址    id   name   gender
        2    页码                  地址页码1
        3    页码
        4    页码
        5    页码

    加了索引， 不仅要维护表数据。 还要维护索引数据。

    索引的类型：
        Normal： 普通的索引。INDEX 或 KEY
        Unique： 唯一索引。  Unique key。
        Full Text：全文索引。如：%青山%，这样效率是很低的，而且导致索引失效。

    主键索引，在唯一所以基础上，添加了什么约束呢？
        作为主键，是不可以为空的。 而其它的，则是：可以有一个为空。

4)B+树有哪些特性？

5)幸运52，有一件商品价格在 1W~3W之间，猜中归你。

6)数据结构的推演
    有序的数组。
        缺点：更新的时候，带来下标的大量挪动。

    链表：
        一个单方向的链表。 有支持2分查找的链表吗？  二叉查找树BST。

        缺点：
            但是如果插入时有序的时候，就退化为斜树。 而我期望是：昨天和右边数目都差不多。 

    那么有没有自带平衡的树吗？平衡二叉树。
        插入：1 2 3 4 5 6--》那么就会发生左旋转。

    211M，光索引的内存就占用了3.41G，因此索引是放在磁盘中的。 放到内存中扛不住。
    page 16kb = 16384bytes，很多歌索引的key
    20bytes。  因此，我们一个AVL树，就设计为16384bytes 这么大小。

    因此：又高又瘦的瘦子--》改为：又矮又胖的胖子。  多路的平衡查找树。 

    只有B树，没有B-树。

    16384/1个字段的长度。。。树的深度大大的降低了。
        用分裂来实现的。 既有合并，又有分裂。

    尽量不要更新索引的字段。 

    同一个page里面，尽量有序的存放。

    身份证后四位：校验位。

    B+树：
        关键字的数量和分叉的数量是N：N
        只有叶子节点才存储数据。
        IO的次数很稳定。
        一个节点能存储的逻辑单元，更多。IO的深度进一步降低了。
        叶子节点，有一个双向的指针。那么就不需要每次遍历从根节点开始了。
        B树能解决的问题，B+都可以解决。
        Between And查找，只需要在叶子节点查找即可。
        where id=1和where id=500W，都是相通的IO次数。

    Memory存储引擎，可以用Hash索引。
        映射+地址转换才能做地址转换。 因此不能做范围查询。 只能做where id= 的查询。
        ID字段重复的值很多的话，

    红黑树5点约束：最长路径不超过最短路径的2倍。
        红黑树比较适配做内存存储结构。
        TreeMap： 一致性的Hash算法。

        因此有的最短路径是4，有的最短路径是8，要经过8次IO，所以不合适。

    不是所有的存储引擎都支持索引。

    frm：元数据定义。

    MyISAM:
    MYD：数据文件
    MYI：索引文件

    索引： B+树的检索 + 磁盘上的检索。

    而INNODB： 索引就是数据，数据就是索引。
        id是：主键索引。 name是普通索引。

        聚集索引：主键索引就是聚集索引。
            逻辑存储顺序和物理存储顺序是一致的话，那么就是聚集索引。

    目录：
        拼音的目录 --》聚集索引。
        笔画的目录
        偏旁的目录

        那么除了聚集索引，其它索引存储的是什么东西呢？

        行溢出。

        主键索引比其它索引要快。

        没有索引，

        rowID

        不可能没有索引。不然就无处安放了。

        page节点发生分裂的时候，那么地址可能会发生变化。 那么就。。不能存地址。

        删除索引这么快： 就是B+树移除了。

        如：在gender上建立索引--》反而速度变慢很多。 没索引2.7s，用了索引，反而20.17s。

        对每一个字段都建立了索引。。。--》副作用？

        联合索引：name+phone：
            谁放前面，是不一样的。
            联合索引的最左匹配原则。 使用联合索引的时候，必须从第一个字段开始，如：abc的书序，

        EXPLAIN

        a、b、c 建立索引--》那么就相当于：建立3个索引。 不需要建立A索引，又建立B索引。。直接A，B，C三个就行了。

        覆盖索引的情况：

        不要用select * 去做查询。查什么就用什么。不然，覆盖索引。

7)where、join、order by

8)索引个数不要过度，不然。。很多空间浪费。

9)散列度低的，不适合丛索引

10)无序的，频繁更新的不适合建立索引。

11)隐身类型转换，导致索引失效。

12)%xxx, 导致索引失效。 而xxxx%则可以用的高索引。

13)Optimizer是基于cost的优化器。 CPU的开销。  内存的IO的开销。

14)RBO: 胸有成竹的老司机。   CBO：实时收集当前的路况，通过一系列的计算，根据计算出来的cost更小的去执行SQL语句。

-----------------事务和锁实现的机制--------------
1)事务： 并发的性能和也保证了数据的一致性。
    (1)什么是数据库事务？
        在什么场景里面需要开启事务？
            加Trxxx注解，或者配置切面。

            下单。
            金融场景里面：
                转账： 一个账户的余额减少，另外一个余额增加。
                接续换乘：ABC 3个换乘。  只有C买到是没有用的。

            只要涉及到修改，就需要开启事务了。 而查询什么的，都不需要开启事务。

            天选之子： InnoDb

    (2)数据库的4大特性：
        ACID：
            原子性(保证一个完整的整体，高中化学学过)。 借贷平衡。
                d1
                d2
                d3:失败了，那么把d1,d2变为失败呢？~ undolog 撤销日志，回滚日志。   
                     再加上: redolog。~~实现断点，重启后，把磁盘数据恢复到数据库。 再刷盘。

            持久性：
                提交成功了，那么不会因为断电、崩溃等变为commit之前的状态。

            隔离性：
                DML: 这么多人在操作表格数据，我们都在操作，如果出现冲突的时候，需要相互透明一样。

            一致性：
                靠前面的3个来实现的。引入事务，想要达到的目的就是一致性。
                主键不能冲突。
                用户自定义一致性：

    (3)什么时候才会出现一个事务呢？
        开启，并自动提交了事务。 这样才会写到磁盘中。

        autocommit

        update student set sname='xxx';  ~~完蛋了。

        AOP 或 @Transaction： 发了一个指令。

        begin  和 start Transaction

        rollback 和 commit时，就结束了一个事务。

    (4)事务的锁，什么时候会释放？ 
        事务结束的时候，会释放。   
        在链接结束的时候，事务也结束了，锁也被释放。  
        应用程序崩溃，锁就也释放了，不可能一直持有。

    (5)事务编号，能够递增。
            如果没有隔离性的时候，

            事务并发和事务隔离级别。

    (6)普通的查询不需要事务。

2)事务隔离级别：
    1.脏读： 前后的读取，因为中间别人修改导致。

    2.不可重复读：一次事务，前后读取到的数据不一致。  被别人修改了。
        update、delete

    3.幻读：因为被人插入，导致前后查询数量不一致。
        insert

    串行化。

    事务的3大问题其实都是数据读一致性的问题。
    必须由数据库提供一定的事务隔离机制来解决。    

    RU 

    RC：解决1 

    RR：解决2 

    Serialzable

3)Mysql的InnoDB：
    RC

4)那么InnoDB是如何解决幻读问题的呢？
    LBCC： 在读数据之前，在其它事务对数据进行加锁。
        这样保证读取到最新的数据。
        但是读写不应该冲突的。
        除非强行需要：一定要读取到最新的数据，才加锁。

        多版本的并发控制： 把之前读取到的数据存储起来。

    MVCC：只在RC和RR中使用(在一个数据库事务里面查询，不管怎么查询，都能得到一样的结果)
        读不阻塞写。

        每一行有3个隐藏字段。
            ROW_ID

            事务的编号。
            指向回滚的指针~undo log

        mvcc实现原理：
            只能查询到自己事务的修改 和 之前已经commit的事务。 看不到事务ID比自己大的。~~读一致性。

            事务的编号。

            ReadView：记录活跃的ID。 往前找，根据事务ID对比，找到一个符合标准的。

    锁的类型：
        行锁、表锁

        乐观锁、悲观锁

        死锁

        间隙锁、记录锁、插入意向锁、临键锁

        排它锁、共享锁、意向锁(提高加表锁的效率)、自增锁 

        一个订单里面，有很多个商品(加了共享锁后，其它人没办法修改)。
            order_info 订单表 id=1001
            order_detail 订单明细表 id=1001

        锁的作用：

    行锁住了什么结构来实现资源的竞争？
        Java中是一个Object对象。

        id=1锁住了行锁。  
            但是id=3，却是无法操作。
            而且insert操作也是无法操作。

            感觉像是：锁住了整个表了一样。

        锁定的同一行资源，因此锁住的是索引。   

        InnoDB是通过锁住索引来实现的。

        索引和数据是一个东西，不可能没有索引。 聚集索引。

        没有命中索引，做了全表扫描。 rowid。

        二级 索引和聚集索引的关系：

    行锁的算法。 什么样的锁锁住了什么样的范围？

    GapLock是：阻塞插入的。 

5）
    RC  MVCC(不加锁的select)   LOCK(no Gap)
        RC每一次查询之前，都建立快照

    RR  MVCC(不加锁的select)   LOCK(增删改查。。。Gap) 
        第一次 

神兽无缘无故被回收。

-------------------------------------------------------------------------------

1)性能优化思路
    (1)案例
        2W4的数据，查询需要90s
        50W的数据，查询将近2s

    (2)连接数量不够的问题。
        maxConnections：
            151， 应用非常多时，可以增大。
            timeout： 及时回收， 默认28800

    (3)池化的技术：长连接保存到connection pool里面
        SpringB 2.x Kikari(光速，用汇编语言)

        2003年，淘宝和雅虎竞争的时候。
        c3p0->老牌
        Druid-》阿里，用的最多的的--》监控而生。
        MyBatis： 自带一个连接池。

        Buffer Pool： 最多占据到80%物理空间
        page

    (4)查询到的结果，直接写到k,v里面~架构层面的优化。

    (5)单台数据库优化不过来。
        主从集群。
        不同节点一致性的问题~主从复制(binlog，从而把解析得到SQL执行一遍)
        读写分离：master写。  slave负责读。~ 基于主从的，读写分离(写代码或者中间件)。
        一张表上亿的数据，单表查询性能下降~分库分表(业务主题相关的表，分到不同数据库。 垂直的分解应用。  水平分表：QQ10亿用户)。

    (6)DBA：消耗10条很长的SQL语句(开启慢查询的日志)
        slow_query
        long_query
 
        mysqldumpslow -s t -t 10 -g 'select' /var/lib/mysql/localhost-slow.log

        show processlist;

    (7)explain: 模拟sql语句在服务端的执行过程

        Update 和 Insert

        课程--》老师--》老师联系方式：  
            查询mysql课程的老师的手机号： 先大后小
            查询ID为2，或者联系表ID为3的老师： 从上往下（join AND--》得到中间结果）： 小表驱动大表的思想

    (8)SubQuery

2)工具

3)type但对单表查询
    range: 针对range的范围查询

4)目标：让查询用到索引

5)不用：触发器、视图、外键
    性能问题： 应该在程序中做，而不要去让mysql做
    可维护性差：不知道有触发器，不知道有外键。 数据库应该只做到存储数据。

6)
    表拆分：
        (1)100+个字段

            商户信息表--》
                基础信息表
                解散信息表(资金往来，开户地址等)
                联系人信息表
                附件信息表

        (2)
            合同表id   客户id   --》客户姓名。   违背了范式，但是减少了关联查询，提升了性能，这是值得的。

7)充值到余额宝、花呗  300  --》30
    不用银联卡。。 是因为是 阿里自己本地的数据。 不用调用API。   扣除自己钱，到支付宝在银行的另外一个账户。

    降级：00：00  只能查询今天的交易。

    不能查：2020.1~2020.9 只支持按月份查询：
        分表了！ 没有把历史记录放到一张表中。

    删除。 逻辑删除。  
        但是不让查询3年前的交易 --。要到柜台申请。

    预售：
        一个月价格更低，那么就退还差价：
            预售分流。

    业务层面：
        秒杀。

    降级、限流、MQ流量削峰，应用负载均衡。
        预约导致应用崩溃。

    别人百万并发。为啥自己几十并发就扛不住了呢？

-----------------------------
1)没有用到的原因是：只做一些传统的小项目。 

2)当被人给你一个很好的内推机会。 创业机会时，你能否把握住？ 如果那时你才意识到，是不是已经晚了？ 

3)学到最后，全是：计算机硬件和网络的事情。

4)回到老家，还是一线城市？

5)企业内推？不合适。 
    硬性条件被筛选掉了。

6)
  高项
  PMP： 只有1个岗位，却有200份简历
  逃避技术的思路。
  技术没有那么高的时候，是不一样的。当你到山顶的时候，你就没有那么多问题了。

  技术，职业能力失去了。多少公司要接纳你？
  出差的问题。

  技术最终可能出来做讲师了。