Mysql测试开发相关

Mysql

从测试的角度，该如何看待索引

测试离不开:测试、验收、质量保证、发版、线上监控、优化改进等这么几个方面，从服务端出发或者更深层次的数据存储系统- 验证数据业务性，性能指标等，以及通过业务逻辑对数据库的增删改查操作是否存在隐藏的bug，安全性是否可靠等

既然要对一个东西动手了，就需要了解这个东西是有哪些结构组成，结构之间又是如何互相影响的，他的实现机制是什么等，通过何种方式，何种策略来看待这个东西是否符合我的要求，基于我的要求下，他运转是否够流畅

    业务场景：
        千亿级数据的验收工作
    测试需求：
        1. 数据的有效性，真实性，完整性
        2. SQL语句的正确性(通过接口调用是否返回正确数据)
        3. 查询数据的响应时间(索引 优化 分布式 缓存 分区分表等)
    测试范围：
        测试库， 线上库
        1. 主机host 
        2. 库名 表名
        3. mysql
        4. redis缓存
    测试周期：
        1. 
    验收标准：
        1. 
    发版标准
        1. 
    应急机制：
        1. block
        2. 人力问题
    测试准备
        1. 测试策略制定
        2. 测试工具选取，测试脚本准备

物理位置: 内存，磁盘

磁盘IO与预读

预读：不仅把当前磁盘地址的数据加载到内存，同时也把相邻的数据加载到内存缓冲区

磁盘IO次数<=B+树中从根节点一直到叶子结点整个过程中查询到节点数

正是基于磁盘IO预读机制的前提，数据库可以采用索引机制快速查询出数据

创建主键,就是整个表就变成了一个索引。没错， 再说一遍， 整个表变成了一个索引，也就是所谓的「聚集索引」。

这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置

表转化成平衡树结构

索引的负担

索引能让数据库查询数据的速度上升， 而使写入数据的速度下降，原因很简单的，

因为平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构

如何优化数据库查询，加快验证效率

聚簇索引

什么是聚集索引？(索引页？数据页？ Key pointer-page offset)

1. 聚集索引就是按照每张表的主键构造一颗B+树，并且叶结点存放着整张表的行记录数据
2. 因此也让聚集索引的叶结点成为数据页
3. 这个特性决定了索引组织表中数据也是索引的一部分(索引页和数据页在一起，找到索引就找到他的位置)
4. 同B+树数据结构一样，每个数据页都通过一个双向链表进行链接
5. 能够在叶结点上直接找到数据
6. 定义了逻辑上的顺序(不是物理上的顺序)，能够特别快的访问针对范围值的查询

该索引中键值的逻辑顺序决定了表中相应的物理顺序

所以要主键，没有主键，innodb会默认的创建一个字增的id

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，同一个结构中保存了索引和数据行

当表有聚簇索引时，他的数据行实际上存放在索引的叶子页中

非聚集索引

该索引的逻辑顺序与磁盘上的物理存储顺序不同，一个表中可以拥有多个非聚集索引

普通索引

唯一索引

全文索引

1. 查找正文内容需要先通过页码查找
2. 叶子结点为索引结点
3. 不会影响表的物理存储顺序，写入顺序由时间决定
4. 一个表最多能有250个非聚集索引，索引列可能是多个

数据 和 索引 存在两个地方

同样采用平衡树(B+树)作为索引的数据结构

索引树结构中各节点的值来自于表中的索引字段

如果给表中多个字段加上索引，那么就会出现多个独立的索引，每个索引之间（非聚集索引）互相之间不存在关联

每次给字段建一个索引，字段中的数据就会被复制一份出来，用于生成索引

非聚集索引和聚集索引的区别

通过聚集索引可以查到需要查找 的数据，

而通过非聚集索引可以找到记录对应的主键值,再使用主键值通过聚集索引查找到需要的数据

聚集索引，叶子结点直接包含卫星数据

在非聚集索引中，叶子结点带有指向卫星数据的指针

1. 索引

索引，是数据库中专门用于帮助用户快速查询数据(不当操作，反而会影响性能)的一种数据结构，根据目录查找到数据的存放位置，

它包含一个表中某些列的值以及记录对应的地址,避免全表扫描

在索引页中找到对应的值，然后根据匹配的索引记录找到对应的数据行

msyql> selct first_name from sakia.actor WHERE actor_id = 5(如果actor_id不是索引键)

索引可以包含一个或多个列，如果索引包含多个列，列的顺序也非常重要，

mysql只能高效地使用索引的最左前缀列

底层数据结构：

为何不使用其他的数据结构

二分查找：

前提：数据为有序的

a 链表

链表的查询速度O(N)，线性结构，如果是最后一个需遍历N次；

b 数组

索引存在于磁盘中，当索引非常大的时候，几个G无法一次加载到内存中

c 二叉树

所以最坏的情况下磁盘IO的次数由树的高度来决定，要减少磁盘的IO数，就必须降低树的高度

d 二叉查找树

左子树的键值小于根的键值，右子树的键值大于根子树(有可能变成线性的)

e 平衡二叉树

1. 符合二叉树的定义
   1. 满足任何结点的左右两个子树的高度最大差为1
   2. 维护平衡二叉树的成本高，需要左旋右旋等,平衡二叉树多用于内存结构中，维护开销小

B树

顺序组织存储的，索引很适合范围查找，所以可以用order by和group by操作

索引中存储了实际的列值

索引对多个值进行排序的依据是定义索引时列的顺序

选择合适的索引列顺序：

不考虑排序和分组时，将选择性最高的列放在最前面通常是很好的(用于WHER优化)

考虑因素:WHERE字句中的排序，分组和范围条件

1. 全值匹配：和索引中的所有列进行匹配
2. 匹配最左前缀：使用索引的第一列
3. 匹配范围值
4. 只访问索引的查询

劣势 索引不生效的情况

1. 如果不是按照索引的最左列开始查找，则无法使用索引。
2. 不能跳过索引列

1. B+树
   B+树索引分为聚集索引和辅助聚集索引，内部都是B+树，高度平衡
   聚集索引和非聚集索引不同的是，叶结点是否是一整行的信息

   B+树和B树对比：https://www.jianshu.com/p/1f2560f0e87f
   非叶子结点的子树指针与关键字个数相同
   每一个叶子结点都包含下一个叶子结点都指针
   B+树的中间结点没有卫星数据，所以同样大小的磁盘页可以容纳更多的结点元素
   B+树的查询必须最终查找到叶子结点(稳定的查找)，而B-树只要匹配到元素即可(所以查找性能并不稳定)
   IO次数更少,查询性能稳定,范围查询简便

卫星数据:指的是索引元素所指向的数据记录,B树无论叶子结点和中间结点都包含卫星数据

索引的分类,高性能索引策略

1. 普通索引

2. 唯一索引

3. 全文索引

4. 单列索引

5. 多列索引

6. 空间索引

7. 主键索引

8. 组合索引

9. 前缀索引和索引选择性
   通过LEFT函数，截取字符串的长度与完整字符串数据做比对
   Select count(*) as cnt ,LEFT(city,7) as pref FROM sakila.city_demo GROUP BY pref ORDER BY DESC LIMIT 10;
   GROUP BY:
   对列字段进行分组
   group by 常规用法配合聚合函数(max ...),利用分组信息(分组的中间过程是？)进行统计，以及配合having进行分组筛选后过滤
   **中间过程 ：
   1. 将具有相同分组字段的记录归并成了一条记录，

   1. 剩下的那些不存在与GROUP BY语句后面作为分组依据的字段就有可能出现多个值
      1. 所以需要聚合函数把这些多值的列转化成单值，放在对应的数据格中

   group by实际上也同样会进行排序操作，与order by相比，groupby只是多了排序后的分组操作
   返回集字段(select应该选哪个些):

   1. 这些字段要么就要包含在GROUP BY语句的后面，作为分组的依据，
   2. 要么就要被包含在聚合函数中

   实现方式：

   1. 松散索引扫描实现
   2. 紧凑索引
      扫描索引时，读取所有满足条件的索引键，然后再根据恶的数据来完成
   3. 临时表

索引的类型

索引的类型

索引是在存储引擎层而不是服务层实现的，所以不同的存储引擎的索引的工作方式不一样，底层实现也不尽相同

InnoDB使用B+树

MyISAM使用B树

1. 哈希索引
   1. 哈希索引(hash index)基于hash表实现，只有匹配索引所有列的查询才有效
      如何工作：
   2. 对于每一行数据，存储引擎会对所有的索引列计算一个哈希码(hash code)
   3. 哈希索引将索引的哈希码存储在索引中，同时哈希表中保存指向每个数据行的指针
      哈希索引的限制：
   4. 哈希索引只包含哈希值和行指针，而不存储字段值
   5. 哈希索引数据并不是按照索引值顺序存储的，索引无法用于排序
   6. 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容
      来计算哈希值
      例如在数据列(A,B)上建立哈希索引，如果查询只有数据列A，则无法使用该索引
   7. 哈希索引只支持等值比较查询，不支持范围查询
   8. 访问哈希索引的数据非常快，除非有很多哈希冲突，有冲突，存储引擎必须遍历索引的行指针，逐行进行比较
      InnoDB引擎有一个特殊功能叫做“自适应哈希索引”，当InnoDB注意到某些索引使用频繁，会再创建一个Hash索引
2. 全文索引
   全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值
   适合做match against操作，而不是where条件操作

索引的优点

1. 索引大大减少了服务器需要扫描的数据量
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机IO变为顺序IO

数据的三星标准：

1. 数据将相关的记录放到一起则获得一星
   1. 索引中的数据顺序和查找中的排列顺序一致则获得二星
   2. 索引中的列包含了查询中需要的全部列则获得三星

https://zhuanlan.zhihu.com/p/21956773