mysql3

mysql3

实际工作中的反范式实现

• 性能提升-冗余、缓存和汇总
  • 冗余（在两张不同表里的字段经常一起出现）
    • 1.比如把产品名称在订单表里再冗余一份
    • 2.订单标号或者订单业务编号在订单详情表里冗余一份
  • 缓存（把经常用的值缓存起来）
    • 1.避免count(*)，查询给用户发送消息的条数，在用户表里新建一个字段，标识用户发送了多少条消息，相当于把发送消息在mysql中缓存起来了，当发现数据库的速度也跟不上了，可以再放在redis里面去
  • 汇总（在业务中经常group by），可以建立一张汇总表，通过定时任务更新
• 性能提升-计数器表
  • 建一张计数器表，有一行字段统计，比如统计网站的点击次数，但是当发现用户点击很火爆，假如不允许使用redis，只允许使用mysql，把用户的数据拆散分段，当增加点击次数的时候任意选择一行加一，这就是写热点的分散
    • 写热点分散还在并发编程中的ConcurrentHashMap、LongAddr，其中LongAddr就是相对于AtomicInteger而言设计出来的，不同的线程写不同的槽位
• 反范式设计-分库分表中的查询
  • 电商系统中的交易数据，是以用户角度保存还是以商家角度保存，实际最好按两种维度都要保存一份，相当于还是进行了冗余（分库分表情况下，数据只有一份，但是保存维度有多个）

字段数据类型优化

基本原则

• 更小的通常更好

  • 存数字有很多类型，在业务允许的范围内，用的越小越好

• 简单就好

  • 1.有一个字段既可以用字符串又可以用数字，此时用整型更好，因为字符串牵扯到字符集和校验规则
  • 2.日期和时间既可以用字符创存放，也可以用mysql内置的时间格式存放，用mysql内建的更好
  • 3.IP地址建议用整型存放，而不是字符串

• 尽量避免NULL

  • 尽量让列not null，如果不是特殊要求，不要把字段设计为允许null，此时mysql很难优化，对索引的统计和处理都会变得复杂，同样不会节省空间，此时mysql需要拿出更大的空间来让这个字段允许保存为null
  • 没有必要把已经是null的字段改成not null，对于这种已经建立好的再修改，并不会带来多大的性能提升，在新设计尽量设计成not null

• 具体字段辨析

  • Int/整数类型

    • tinyint(1字节)、smallint(2字节)、int(4字节)、bigint(8字节)
    • unsigned，有符号和无符号的存储性能一样，只是存储范围不一样，根据业务选择
    • bigint(11)，这种限制宽度没有任何意义，并没有限制这个bigint只放11位，该放多少还是多少，只是对某些客户端而言显示的位数不同，bigint(11)和bigint(20)对于mysql而言没有任何差别

  • 实数类型

    • float（4字节）和double（8字节），是不精确的
    • decimal（允许存放65个数字），是精确的
      • 如果业务是财务、金融类的，需要很精确，就用decimal，如果允许比较小的误差，就用float和double
      • 但是存decimal会很慢，怎么提高速度？当数据量很大时，把decimal转换成bigint，即转换成整数
      • 金融目前万分之一的精度就足够了

  • 字符串类型

    • VARCHAR
      • 存储变长，比定长更省空间，char类型是定长，即使没用完也是会分配的
      • 最长长度不要超过平均长度太多，虽然是变长，导致一个页存放不下，造成页的拆分，造成一定程度性能的下降
      • 多字节，utf-8比较复杂的字符串
      • varchar(10)和varchar(200)，具体定多长？虽然是变长，肯定也是varchar(10)更有优势些，因为varchar(200)消耗的内存更多，另外，对于排序而言，mysql排序一般是放在内存中做的，但是当发现内存不够用时，就会放到磁盘文件中，
    • CHAR
      • 如果字符串很短，就直接用char就可以了，比如两三个字符串，而varchar通常需要1到2字节来保存字符串长度
      • 字符串定长，或者接近某一个长度，比如姓名、性别、身份证号、md5码（也是定长的）
    • BLOB和TEXT类型
      • 都属于字符串，Blob是二进制存储，text是字符串存储，但是本质都是字符串，因为这两个字符串很大，所以mysql做了特殊处理
      • Blob使用要注意，因为特别大，可能会产生性能问题，当表里面有一个字段是Blob时，把这个字段拆分出去，单独放一个表
      • 索引对于Blob的意义并不大
    • 必要时，使用枚举代替字符串
      • 固定的是某几个字符串，则考虑用枚举，mysql处理枚举是非常紧凑的，实际存储的是数字
      • 具体使用可以去mysql官网看，ENUM columns，比如如何声明、如何插入等等

  • 日期和时间类型

    • datetime

      • 8字节

      • 与时区无关

      • 存的范围值比timestamp，1001年到9999年

      • 精度到秒

    • timestamp

      • 4字节

      • 跟时区相关

      • 范围是1970年到2038年

      • 精度到秒，这两种精度都是秒，如果要存更小的精度，转化成bigint，用bigint存

工程实践-数据库命名规范

推荐命名规范实践

• 遵守可读性原则

• 必须使用小写字母或数字

  • windows不区分大小写，linux区分大小写

• 不使用复数名词

• 禁用保留字

• 命名能够描述它所表示的对象

  • 业务名称_表的作用
  • 库名和应用名最好要一样

• 索引命名

  • 索引名要体现索引的性质
    • 主键：pk_ + 字段名称
    • 唯一性索引：uk_ + 字段名称
    • 普通索引：idx_ + 字段名称

• 是与否概念的字段，应该使用is_xxx的方式命名

  • 比如是否支付，is_pay

高性能的索引

索引是什么

• MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
  • 索引的本质：索引是数据结构。
  • 索引的作用：高效获取数据。

比喻

• 就像看书通过目录快速定位
• 在图书馆呢？是一种树形结构
  • 1.按题材分
  • 2.按国家分
  • 3.按角色分
  • 4.具体的书籍

MySql中的索引

• 右侧是数据表，一共有两列七条记录，最左边的是数据记录的物理地址，通过索引查找数据

InnoDB存储引擎支持以下几种常见的索引：B+树索引、全文索引、哈希索引(内部)，其中比较关键的是B+树索引

为什么hashmap不适合做mysql索引?

• 只适合做匹配相等，不适合做范围查找，范围查找很麻烦，需要遍历全部的数据，因为通过哈希运算，数据被分配到不同的哈希桶里面
• 不好排序，比如最简单的取模哈希，同一个桶里面可能放的数一个很大，一个很小
• 哈希算法本质是一种压缩算法，其中会出现同一个数进入一个桶，会出现哈希冲突，在数据库里面数据量一大，哈希冲突的概率也就会变大，查找效率也会随之下降

B+Tree

• B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最常用和最为有效的索引。
• B+树是通过二叉查找树，再由平衡二叉树，B树演化而来。

二分查找法

• 时间复杂度为log2(n)

二叉树和二叉查找树

• 根据数组的特性我们知道，有最优的查找效率和空间需求；能够进行有序性相关操作，缺点也很明显，插入和删除操作很慢，所以为了结合链表插入的灵活性和有序数组查找的高效性，我们引入了二叉查找树。

• 为了弥补数据增删慢的弱点，由二叉树演化到二叉查找树

  • 二叉查找树相对于二叉树，不止有左子树和右子树
  • 二叉查找树的左子树的值一定小于右子树，通过中序遍历可以得到二叉查找树的有序数组

平衡二叉树（AVL-树）

• 当没有左子树或者没有右子树时，二叉查找树变成了链表，即由二叉查找退化成遍历

• 平衡二叉树的左右子树的高度差不能超过1，对于不满足这个条件的二叉树，通过左旋和右旋来改造

• 数据估算，1000个数据，二叉树的高度大概是9–10，如果有10000个数据，高度只是27-30倍，即数据量每增加一个0，高度差不多增加3倍

B-Tree，多路搜索树、多叉平衡查找树

• 每一个节点下面允许代多个子节点，通过这种方式，一次磁盘读取，读取到的数据比一次二叉读取要多很多，所以叫做多叉树

• 二叉树的变种

• B树的中间节点除了放关键字、对下一层子节点的引用外，还要放具体的数据

B+Tree

• B+树中间节点是没有存放具体的数据的，只在叶子节点存放具体的数据
• B+树的叶子节点会有指针相连，形成链表
• 叶子节点的数据是有序的，按照从小到大的顺序依次连接起来，这样是很利于范围查询的
• 因为也是平衡树，所以插入和删除后也要进行平衡的维持，这个操作是比较麻烦的
  • 一要保持插入的数据，要保证排列任然有序
  • 二如果叶子节点满了上一层节点没满、叶子节点满了上一层节点也满了，叶子节点没满只有上一层节点满了，***只要一个节点满了，这就会牵扯到页的分裂和合并问题***，
    • 满的那个节点以中间元素为基准拆成两半，同时把这个中间元素提到上一层去，***如果中间元素提到上一层又满了，继续重复这个分裂过程，一直到没有节点满***
    • 另一种节点重组的形式–旋转（为了避免页的分裂），过程更复杂，没有展开讲，只是提到这个点
    • 节点的删除，通过填充因子来控制B+树的删除变化，填充因子是说节点中至少要有50%的数据
    • 假如要删除的数据既存在于叶子节点中，也存在于它的上一层非叶子节点中，常规做法是把叶子节点中右侧的数据元素，替换掉非叶子节点被删除的数据，同时删除掉叶子节点中的删除元素，将右侧元素依次左移
      • 如果仍然是这种情况，删除元素后的叶子节点后的该节点元素个数少于一半时，需要进行页的合并

B*树

• B+树的变种

• 非叶子节点之间也会有指针相连，形成链表