Mysql 索引和优化
推荐网址 https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
Mysql底层原理,索引优化
为什么会快。根本在于 查询和搜索,数据结构,算法时间复杂度大大降低
目录只是一种,数据结构,有序
如果不加:则是到磁盘,一行一行查,比较 ,遍历太多
二叉树当索引缺点:
如果字段列表(id,成绩,排名)是有序,单向增长,那么维护二叉树,等于遍历链表,并且查找也是一行行,没有提升性能。
大名鼎鼎册红黑树:hashmap 在jdk1.8后,底层用红黑树实现 就是二叉平衡树,旋转了,属于二叉树,但是优化了,防止形成链表。
弊端:如果
索引也是存在磁盘上的,每次查找索引,则要访问一次索引文件,多次,效率低,io慢。如果访问几十次磁盘,难以接受。
红黑树弊端:如果数据量太大了,比如几百上千万,2的n次方,数也会达到几十,对于层数大的节点,非常费时(几十次查找比较)
千万级改造:
每个节点 增加容量,多个value 每个再生孩子节点
就是 B树
Data:代表value 元组的标识
注意:虽然扩充每个逻辑节点的容量,但不是无限扩充。
因为:要是将逻辑节点数据全读入内存中查找,千万级别,太大了,几个G
再者 全部一个个查找,慢。而且一张表一般利用20%,少
如果太小:深度太大,慢。如果太大,内存压力大,利用率低,也慢。折中。
Mysql 16k左右
B+树
即便是几千万的数据,也能做到 深度<=4
为什么不像B树,而要把data指针全部放到最深的叶子节点 ?:因为MySQL固定了大小,16K。若放到叶子节点,则非叶子节点可存更多的元素,降低深度。
为什么每个逻辑节点要设为16KB:: 比如整数 1170个元素
再看叶子节点:
一个叶子:16个元素
如果叶子节点全部装满,总共能放多少个元素?1170*1170*16
Mysql如何用B+树,飞速查找元素,定位?
IO读入根节点(主要费时步骤),内存中查找,
到IO孩子节点,,继续 直到叶子节点,IO读入,找到data 定位,找到元组位置,取出所有字段
新建索引,索引方法 的选择:B树,Hash
把某个字段的值给hash函数,hash算法计算出一个值(字符串),对应该元组磁盘文件指针,都被存入索引文件
性能的确很高
实际:1.存在hash冲突 2.最主要:无法支撑范围查找,而集合查找非常常用,比如年龄大于。。。分数大于。。。的多个元组 。没有给出确定的字段值,无法hash计算。而B树则因为大小有序,适合。(99%场景用B树)
用到hash :表数据非常大,>千万,上亿,极少用范围查找,性能要求很高
为什么b+树的每个逻辑叶子节点 要在背后维护一个指针,指向后面的叶子节点?
因为经常会有范围查找;利用B+树有序,查找到叶节点后,只要向后一直取,遍历双向链表就可以 反之如果没有,则每次遍历完一个逻辑叶子节点后,再回到根节点再找麻烦
实际上表中存放的元组,到底放在哪里?
data文件夹下,对应数据库的文件夹,在进入,打开 ;注意不同表的不同存储引擎,文件不同 frm :表结构 MYD:DATA 数据记录 MYI index 表的索引 主键自带索引
存储引擎索引查找:先去MYI, 找到指针,再去MYD用得少
最多的存储引擎 和建表独立的,用户自己通过选项按钮,可选存储引擎
如果是Innodb(最常用)
Ibd存储索引和数据的
区别:数据和索引不分离,全部按照B+结构存
只要找到叶子节点,就可以直接取出整条记录,不用映射IO,更快
聚集索引是什么?就是数据和索引都在一个文件, 叶子节点包含了记录的其它字段值
为什么InnoDB的表必须有主键?且最好为整型,自增
以为他本身要求了必须有B+树来组织记录,需要某个字段当索引元素,构造
如果InnoDB主键不用整型,也不是自增,比如用uuid 唯一识别码,32位16进制的 是字符串
弊端:1.占空间大,索引空间有限 2.字符串要比大小,有序化,非常慢,ascall码比较,一位一位的。 整型更小更快
为什么推荐要自增?因为如果又要插入记录,而记录一定会插入叶子节点中,如果不是自增,回往中间插入,可能会导致叶子节点分裂,向下生成节点。操作复杂,树的变动,旋转。如果自增,显然只需要放到叶子节点双向链表末尾,快速。性能高得多。