1.数据结构
内存与磁盘的交互式,有一个最小的逻辑单元,称为页,datapage,一般大小有操作系统决定,一般为 4k 或者 8k,在读取行时,一般是页的整倍数
数据 ==> 组(每个组有 6 条数据) ==> 页
页与数据的条数关系
- 行格式
- int :4b
- varchar:指针
- 以这个指针可以指向另一个区域
- 到时候可以直接预存改地址
- 指针大小固定
- var:固定的字节数
- 根据数据条数计算数组
- 形成一个列表
- 连续空间
- 非连续空间
- 形成一个列表
- 自增 id 可以自己将这些数据自行预存地址
tree 树形结构
二叉树
- binory search tree
- 左边永远小于根节点,右边永远大于根节点
- 时间复杂度:ologn
- 极端条件:递增→链表→过度成 avl 树,
红黑树
- 非严格意义上的平衡树
- 最长路径不超过最短路径的 2 倍即可
AVL 数
- 严格意义上的平衡树
- 长度不超过 1
- avgl 树查找效率高,但插入效率低
- 适用于多读写少的业务 注: 二叉树、红黑树和 avl 树数据越多,树越来越深,查询效率越来越低 优化为有序的多叉树→ B 树
B 树
- degree
- 每一个节点最多放 degree-1 个数据值
- degree= 4时,第 10 个数据会变成 3 层
- degree 大才可以保证树的深度足够低
B+ 树
- 比起 B 树,B+树的叶子可以存储所有的数据,非叶子数据可以存储 k 值。
- 有2个头指针,一个纸指向节点,另一个指向关键字最小的叶子子节点,而且所有叶子子节点(即数据节点)之间是一种链式环结构
- 可对B+树进行两种查找运算,一种是对于主键的范围查找和分页查找,另一种是根节点开始,进行随机查找
- innodb B+树
- 对主键创建索引,然后叶子节点虫存储记录,如果没有主键,则会选择唯一键;若没有唯一键,则会声场一个刘子杰的row-id为主键
- 如果创建索引的键是其他字段,那么在叶子节点中存储是该记录的主键,然后在通过住家索引找到对应的记录,称为回表
- 数据和索引是一个文件
- MyISAM 的 B+树
- 数据和索引分开
hash 表
- 索引的选择时和存储引擎相关的,在MySQL中,memory存储引擎使用的索引是hash表,且innodb支持自适应hash
- hashmap
- 哈希碰撞,必须设计良好的hash算法,避免产生hash冲突问题
- 扰动函数
- 占用内存
- 无法进行范围查找,需要全部匹配,占用资源
2. 索引的优点
- 大大减少了服务器需要扫描的数据
- 帮助服务器避免排序和临时表
- 将随机 io 变为顺序 io
3. 索引的用途
- 快速查找匹配 where 子句的行
- 在 consideration 中消除行,如果可以再多个索引之间进行选择,MySQL 通常会使用找到最少行的索引
- 如果表具有多列索引,则优化器可以使用索引的最左浅醉来查找
- 当有表链接是回收,从跟其他表索引行数据
- 查找特定索引列的最大最小值
- 如果排序或分组是在可以用索引的最左前缀上完成的,则对标进行排序和分组
- 在某些情况下,可以优化查询建所致,而无需查询数据行
4. 索引的分类及原则
4.1 分类
- 主键索引
- 唯一建索引
- 普通索引
- 全文索引
- 类似与 ctrl+f 的全局索引
- lucene
- solr
- es
- 联合索引
- 当所以包含一个或者多个列
- 其他说明
- MySQL 创建索引:是主键或者唯一键
- 若有很多个索引的话出现页分类问题 和页分裂过程和页合并过程
4.2 原则
- 最左左前原则
5. 一些名词
-
回表 innodb 存储引起都是 B+tree,但普通的 B+tree 查找,返回 id 主键,在通过主键赶回所查询的内容
-
覆盖索引 通过普通的 B+tree 返回 id 主键
-
索引采用的数据结构
- 哈希表
- B+Tree
-
索引下推
- 5.7 以后才出现的
- 如
select * from table where name = ? and age = ?;
- 直接从存储引擎拉取数据时,按照 name 和 age 进行判断,将符合条件的结果返回给 MySQL server
- 先根据 name 的值存储引擎中将数据取回来,然后在 server 层做 age 的过滤判断,有了索引下推之后,根据 name 和 age 两个列的值取存储引擎中做数据筛选,不需要在 server 层进行条件过滤
- 即:原来在 server 层做的数据过滤下推到存储引擎中完成
-
谓词下推
select t1.name,t2.name from t1 join t2 0n t1.id = t2.id
- 正常:先把所有表做关联,在从关联号的表中选择需要的四个字段
- 谓词下推:先把两张表需要的四个字段取出,在过表关联
-
最左匹配原则
- 适用于组合索引
- 如
select * from table where name = ? and age = ?;
- select * from table where name = ? and age =? ✔️
- select * from table where name = ? ✔️
- select * from table where age =? ❌
- select * from table where age = ? and name =? ✔️
- 但是优化器会对其进行查询条件优化
- 类似与三级联动,必须先匹配到第一个列,然后再匹配到第二个列,最后匹配到第三个列
-
索引匹配方式
- 全值匹配:和索引中的所有列进行匹配
- 匹配最左前缀:有限匹配前面的几列
- 匹配列前缀: 可以匹配某一列的值的开头部分
- 如 abc% :会触发索引;但%abc 则不会触发索引
- 匹配范围值
- 精确匹配某一列并范围匹配另一列
- 只访问索引的查询:查询的时候值访问索引,不会访问数据行,本质上是覆盖索引
-
哈希索引
- 是基于哈希表实现的,只有精确匹配索引所有列的查询才有效
- Memory 存储引擎支持哈希索引
- 自身值存储对应的哈希值,所以索引的结构十分紧凑,因此速度非常快
- 数据量大的时候,耗费存储
- 限制
- 只包含了哈希值和行指针,而不存储字段值,索引不能使用索引中的值来比较读取行
- 哈希索引数据并不是按照索引值存储的,因此无法排序
- 哈希值不支持部分列匹配查找,哈希索引使用索隐裂的全部内容计算哈希值
- 支持等值比较查询,不支持任何范围查询
- 访问哈希索引的数据非常快,除非有很多哈希冲突,当出现哈希冲突的时候,存储引擎必须遍历表中的所有行指针,进行比较,直到所有符合条件的行
- 哈希冲突比较多的话,维护的代价会比较高
-
组合索引
- 当包含多个列作为索引,需要的是正确的顺序依赖以该索引的查询,同时需要考虑如何更好的满足排序和分组的需要
-
聚簇索引和非聚簇索引
- 聚簇索引:不是单独的索引类型,而是一种数据存储方法,数据行和相邻的键值进程的存储在一起
- 非聚簇索引:数据文件和索引文件分开存放
-
覆盖索引
- 介绍:如果一个索引包含所有需要查询的字段,称为覆盖索引,不是所有类型的索引都可以称为覆盖索引,必须存储索引列的值
- 不同的存储实现覆盖索引的防治不同,不是所有的引擎都支持覆盖索引,Memory 不支持覆盖索引
- 优势:
- 索引条目远远小于行的大小,如果只需要读取覆盖索引,那么 MySQL 就会执行较少数据访问量
- 因为索引是按照值顺序存储的,所以对于 io 密集型的范围查询会比随机从磁盘读取读取每一行的 ip 要少得多
- 一些存储引擎引入 myuissam 在内存中值缓存索引,数据则依赖系统来缓存 ,因此要访问属性就要一次系统调用,可能会导致严重的性能问题
- 用 innodb 的聚集素银,覆盖索引对 innodb 表特别有用
6. MySQL 的调优
- 性能监控
- schema 与数据类型优化
- 执行计划
- 通过索引进行优化
- 查询优化
- 分区表
- 服务器参数设置
- MySQL 集群
- 主从复制
- 读写分离
- 分钟分表
6.1 数据库的构建优化
- 创建索引的时候使用 int 或 varchar
- MySQL 层数:3-4 层足以支撑千万级的数据量
- 创建索引:
- innodb: 主键、唯一键、6 字节的 row-id
- MyISAM:
- 主键要设置自增
- 设计索引的维护过程
- B+树的层数和元素之的添加删除都会影响到整个树的结构
- 如果一个表有多个索引,要如何存储
- 存储一份索引,其他的索引在叶子节点中存储
- 回表的话:最好避免回表
- select id, name, address from table where name = zhangsan; 包含 address 的话需要回表,然后速度慢
- 覆盖索引:当普通的B+TRee中包含要查询的所有字段时,可以不用回表,此时称为覆盖
6.2 系统查询优化
- 当使用该索引列进行查询的时候尽量不要使用表达式,把计算放到业务层而不是数据库层。
- 尽量使用主键查询,而不是其他查询,因此主键查询不会 i** 回表查询。
- 使用前缀索引。
- 使用索引扫描来排序
- union、all、in、or 都可以使用索引,推荐使用 in。
- 范围列可以用到索引:条件范围: <,<=,>,>=,between 等,范围列可以用到索引,但是范围列路面的列无法用到索引,索引最多用于一个范围列
- 强制类型转换会扫描全表
explain select * from table where phone= 1234567
不会触发索引explain select * from table where phone= ”1234567“
会触发索引
- 对于更新十分频繁的字段,数据区分度不高的字段不建议建立索引
- 更新会变更 B+tree,频繁更换字段索引会大大降低数据库性能
- 对于区分不大的属性,建立索引没有意义且无法有效过滤数据
- 一般区分度 80% 以上的时候就可以建立索引,区分度可以使用 count(列名))\count(*)来计算
- 建立索引的列,不可以为 null。否则可能会得到不符个预期的结果。
- 当需要进行表连接的时候,最好不要炒股 3 张表,因为需要 join 的字段,数据类型必须一致
- 推荐使用 limit
- 索引要持久化存储
8. 一些解释
- 存储所存储的是什么数据
- 索引字段 key
- 对应的文件
- 当前文件的偏移量
- 是否存储为 k-v 格式,影响到数据结构的选择
- hive 使用该格式
- 当索引文件变大时,索引效率会急剧降低
- OLAP(联机分析处理)
- 对海量的历史数据进行分析操作,要求产生决策性的影响,不要求在极短时间内返回结果
- 数据仓库
- hive
- OLTP(联机事务处理)
- 为了支撑业务系统的需要,必须在短时间内返回对应的结果
- 关系型数据库
- MySQ
- OLAP(联机分析处理)