高并发，高性能，高可用MySQL 实战-01一个sql语句如何执行，如何建表更符合业务

一个sql语句如何执行？

mysql软件架构是怎么样的？

  1. mysql常用的存储引擎
  • Innodb
  • MyISAM
  • Memory
  • Archive
    Innodb是目前最流行的存储引擎，适合各种互联网业务
    查询效率非常高的业务可以考虑MyISAM
    日志信息归档可以考虑Archive,因为有压缩，磁盘利用率非常高
    临时表可以考虑memory，因为是基于内存的
  1. 软件工程中的经典架构
  • 分层架构
  • 事件驱动架构
  • 管道过滤器架构
  • 微核架构
    大型的软件往往不是单一的架构设计，而是多种混合的
  1. mysql tcp的通讯协议
  • tcp三次握手建立链接
  • 认证链接
  • 认证通过后，客户端开始与服务端进行交互
  • 断开mysql链接
  • tcp四次挥手断开tcp链接
  1. 客户端与mysql的连接方式
  • tcp/ip连接
  • 命名管道
  • 共享内存
  • unix域套接字
  1. 执行器的主要作用
  2. 存储引擎的主要作用
    存储引擎的任务是将执行器的指令落实在数据文件上
    不同存储引擎的原理和执行方法有很大的不同

一个sql语句是按照分析、优化、执行、落盘的步骤执行的，mysql8.0之后已经停用了server层的缓存

如何建表更符合业务

什么叫索引组织表

  1. 索引组织表：index organized table
  • 索引组织表不是一种 "组织表"
  • 索引组织表是由索引 "组织起来的" 表
  • InnoDB中表都是根据主键顺序组织存放的
  1. 索引
    索引是数据库中对某一列或多列的值进行预排序的数据结构
    索引可以理解为数据的目录
    InnoDB中，主键是一个特殊的索引字段

总结：InnoDB数据表均为索引组织表，索引组织表中的数据被主键的索引组织起来

B+树的B是什么意思

  1. 主流索引查找算法
  • 线性查找: 时间复杂度O(n), 从第一个数据开始逐个匹配
  • 二分查找：时间复杂度O(logN),拿出有序数列中点位置作为比较对象，根据中点数据大小，选取一半数据作为新的数列，每次可以将数据量减小一半
  • 二叉查找树：时间复杂度O(logN)，使用经典的二叉树数据结构，由根节点开始查找，可能退化为线性查找，最坏时间复杂度是O(n)
  • 平衡二叉树：(avl tree/ balance tree),时间复杂度O(logN) 查找时与二叉查找树相同，增删改时，通过旋转操作，维护树的平衡，AVL树就是平衡二叉树保证不会退化成线性查找。
  • B tree：

    B树特点：
  1. B树是线性数据结构和树的结合，
  2. B树通过多数据节点大大降低了树的高度
  3. B树不需要旋转就可以保证树的平衡
  • B+ tree（B tree + 线性查找）
    因为B树对范围查找的效率非常低，所以就有了B+树，B+树是由B树发展而来的一种数据结构
    B+树的所有数据形成一个线性表
    B+树的特点是所有的数据都在叶子节点，上面的节点值存储索引

    B+树对范围查询非常的高效，因为数据都在叶子节点，只要找到范围的开始值，通过遍历链表的方式就可以找到范围内的值，而且B+树有B树的特点就是一个节点可以存储多个数据

总结：B+树是目前最主流的数据库索引算法，B+树由线性表+二叉树+B树发展而来的，B+树集成了线性表、平衡二叉树的优势
既有查询速度快的优势，又有范围查询效率高的优势，还有磁盘空间利用率高的优势

为什么说InnoDB索引即数据

  1. Innodb使用B+树作为索引的数据结构

  2. B+树的高度一般为2-4层，查找速度非常高

  3. InnoDB的索引分为聚簇索引（主索引）和辅助索引

  4. 聚簇索引

  • 根据表的主键构造一个B+树
  • 叶子节点直接存放行数据，而不是指针
  • 索引组织表中， 数据也是B+树的一部分
  1. 辅助索引
  • 每张表可以有多个辅助索引
  • 叶子节点并不包含行数据
  • 叶子节点记录了行数据的主键，用来指示行数据的位置

一个表中如果有一个主索引，两个辅助索引，那么这张表有三个B+树
所谓的回表就是通过辅助索引查到对应的主索引，在通过主索引查到对应的数据

总结：

  • InnoDB的索引分为聚簇索引（主索引）和辅助索引
  • 同层B+树节点之间为双向链表
  • 在B+树节点之内，数据条目之间为单向链表
  • 所谓索引即数据，就是把数据直接记录在了主索引里

InnoDB的数据表是如何存储的

  1. 逻辑存储结果

  2. 表空间

  • 表空间指的是数据表在硬盘上的存储空间
  • 默认，所有表的数据都存在共享表空间
  • 每个表的数据也可以放在独占表空间（ibd）文件
  1. 段
  • 数据段：B+树的叶子结点
  • 索引段：B+树的非叶子节点
  • InnoDB中，段由存储引擎自动管理
  1. 区
  • 区是由连续页组成的空间，每个页16kb,一共64个页，也就是每个区是1M
  • 一次从磁盘申请4-5个区
  1. 页
  • 页是InnoDB中磁盘读写的最小逻辑单位，默认16kb
  • 一个数据页就是B+树的一个节点
  • 页的大小充分考虑了机械硬派和SSD的最小单元（512B和4KB）

总结：
InnoDB的逻辑存储结构分为表空间、段、区、页、行
InnoDB的逻辑存储结构充分考虑了基于B+树的表结构
InnoDB中的页是InnoDB自身的逻辑概念，与硬件的页无关

InnoDB的数据行长什么样

  1. InnoDB中的变长列
  • 长度不固定的数据类型，VARCHAR VARBINARY BLOB TEXT
  • 变长编码下的CHAR
  1. 行溢出数据
  • 由于InnoDB每个数据页容量有限，导致数据字段也是有限的
  • 当数据字段过大时，InnoDB会使用行溢出机制
  • 行溢出机制会把超长字段放入单独开辟的数据页
  1. InnoDB行记录格式


    InnoDB用的最多的mysql5.7之后的行记录格式 Row Format

总结：

索引有哪些左侧用法（索引的注意事项）

  1. 联合索引
  • 使用两个或两个以上字段生成的索引
  • 联合索引也可以加速最左前缀的查询
  • 联合索引可以代替最左侧字段的单独索引
    使用联合索引一句话："带头大哥不能死，中间兄弟不能丢"
  1. 字符串的前缀索引
  • 如果字符串过长，可以考虑使用前缀索引节约空间
  • 如果前缀区分度太小，可以考虑两种变通方法

  1. 倒叙存储

  2. 新建hash字段

  3. 字符串like模糊查询

  • like %关键字% like %关键字 不会走索引
  • 模糊查询只有 like 关键字%才会走索引
  • 如果mysql数据量特别大，使用模糊查询会把数据库拖垮的话，就可以使用搜索引擎，例如：es

总结：

  1. mysql有很多左侧用法
  2. 联合索引可以代替最左侧字段的单独索引
  3. 字符串的前缀索引可以节约存储空间
  4. 字符串的左模糊可以利用索引，如果是全模糊或者右模糊对性能影响比较大的可以使用搜索引擎es

如何约束数据

  1. primary key, unique key
  • 通过将数据字段设置为索引，约束数据内容
  • primary key 唯一 不为null
  • unique key 唯一
  • 唯一约束插入时的性能开销较大
  1. foreign key外键
  • 外键可以对数据的正确性实现约束

如何使用不存在的数据表-视图

  1. 视图可以在不改变原有数据表的情况下，创建一张虚拟的表
  2. 使用视图时尽量使用merge算法，而且避免无法使用merge的sql,如：union union all 聚合查询，group by having等

总结：

数据库查询效率影响最大的就是磁盘的IO。

posted @ 2022-03-20 15:07  专职  阅读(128)  评论(0)  编辑  收藏  举报