青山~深入理解mysql

2024-03-28 16:32

本文主要是介绍青山~深入理解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份简历
  逃避技术的思路。
  技术没有那么高的时候,是不一样的。当你到山顶的时候,你就没有那么多问题了。

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

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


 

这篇关于青山~深入理解mysql的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/856121

相关文章

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

【前端学习】AntV G6-08 深入图形与图形分组、自定义节点、节点动画(下)

【课程链接】 AntV G6:深入图形与图形分组、自定义节点、节点动画(下)_哔哩哔哩_bilibili 本章十吾老师讲解了一个复杂的自定义节点中,应该怎样去计算和绘制图形,如何给一个图形制作不间断的动画,以及在鼠标事件之后产生动画。(有点难,需要好好理解) <!DOCTYPE html><html><head><meta charset="UTF-8"><title>06

认识、理解、分类——acm之搜索

普通搜索方法有两种:1、广度优先搜索;2、深度优先搜索; 更多搜索方法: 3、双向广度优先搜索; 4、启发式搜索(包括A*算法等); 搜索通常会用到的知识点:状态压缩(位压缩,利用hash思想压缩)。

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

深入探索协同过滤:从原理到推荐模块案例

文章目录 前言一、协同过滤1. 基于用户的协同过滤(UserCF)2. 基于物品的协同过滤(ItemCF)3. 相似度计算方法 二、相似度计算方法1. 欧氏距离2. 皮尔逊相关系数3. 杰卡德相似系数4. 余弦相似度 三、推荐模块案例1.基于文章的协同过滤推荐功能2.基于用户的协同过滤推荐功能 前言     在信息过载的时代,推荐系统成为连接用户与内容的桥梁。本文聚焦于

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间

MySQL高性能优化规范

前言:      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后,总结出了这篇! 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀,备份

【生成模型系列(初级)】嵌入(Embedding)方程——自然语言处理的数学灵魂【通俗理解】

【通俗理解】嵌入(Embedding)方程——自然语言处理的数学灵魂 关键词提炼 #嵌入方程 #自然语言处理 #词向量 #机器学习 #神经网络 #向量空间模型 #Siri #Google翻译 #AlexNet 第一节:嵌入方程的类比与核心概念【尽可能通俗】 嵌入方程可以被看作是自然语言处理中的“翻译机”,它将文本中的单词或短语转换成计算机能够理解的数学形式,即向量。 正如翻译机将一种语言