003 mysql索引理解

目录

一索引数据结构

二叉树

红黑树

B-Tree 多叉平衡树

B+Tree(B-Tree变种 mysql底层)  多叉平衡树

存储引擎

MyISAM存储引擎（非聚集索引）

InnoDB存储引擎（聚集索引）

问答：

---

一索引数据结构

1二叉树 2红黑树 3Hash表 4B-Tree

二叉树

弊端：二叉树查找退化成一个链表

查6的时候，得查六次

单边增长的情况，不适合作为索引最终的底层数据结构 ，所以Mysql选择是B树

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

红黑树

jdk1.8 hashmap 数组+链表+红黑树

红黑树--二叉平衡树 树的高度差不多，查6的时候查了3次，比起二叉树减半

弊端：数据量太多，树的高度很高，性能低

B-Tree 多叉平衡树

树的高度越小，查找次数越少

1叶节点具有相同的深度，叶节点的指针为空

2所有索引元素不重复

3节点中的数据索引从左到右递增排列

 

 

B+Tree(B-Tree变种 mysql底层)  多叉平衡树

1非叶子节点不存数据，只存储索引（冗余），可以放更多的索引

2叶子节点包含所有索引字段

3叶子节点用指针连接，提高区间访问的性能

c0l=30 先把根节点load到内存，进行内存做随机查找，比IO块

mysql底层第一层（非叶节点）大小16kb，一次性加载完毕

SHOW GLOBAL STATUS	LIKE 'Innodb_page_size';Innodb_page_size=16384

主键索引 bigInt(8 bit)   索引（8 bit）+指针(底层分配6 bit  15—16之间的地址)成对出现

16kb/14bit=1170

叶子节点没有指针，有data，假设一个占据1kb，大概存储16个索引元素，则叶子节点1170*1170*16=21902400；

mysql索引节点是常驻内存，两次IO查找；

存储引擎

mysql存储引擎，数据库表级别的，表的存储引擎可以自己选择

默认是存在mysql根目录的，data下面

MyISAM存储引擎（非聚集索引）

MyISAM索引文件和数据文件是分离的

非聚集在两个文件中查找数据

frm:表结构文件 

MYD:数据文件

MYI：索引 index

InnoDB存储引擎（聚集索引）

聚集索引，叶节点包含了完整的数据记录

聚集索引在一个文件中查找

frm：表结构 

idb:索引和数据

用b+tree

 

问答：

为什么innodb表必须有主键，并且推荐使用整型的自增主键？索引存在磁盘上，mysql用ssd

innodb底层的开发存储引擎的时候，数据必须有b+tree的聚集索引进行组织，一般用主键b+tree组织我们的数据

没有主键，没有索引，怎么组织数据？mysql自己选择不重复的一个字段作为索引，进行组织数据，如果没有的话，会自己建立一个隐藏列，可能是整数，隐藏列组织所有数据。

使用整型--uuid作为id是误用，字符串类型不自增，字符串得转为asc码，字节占用多

索引从根节点开始出发，中间有很多比对，比大小，整型对比快于字符串，节约资源

自增？叶子节点用指针连接

hash表：innodb一般不支持，用的少  对每个元素进行hash进行运算 散列值，进行定位到行记录，不做磁盘IO

  范围查找：如果  where col5>6 and col2>89,不是顺序排序，hash不支持范围查找 MD5是hash算法

b+tree查找col>20结果集：根节点定位，先找到20，把20的右边全部load到内存，根据指针（双向）所有元素找出来，指针是双向的

b -tree没有叶子指针，每个都从根节点定位查找；b+tree根节点有双向指针，插入数据的时候会维护双向指针；

为什么用自增？如下图非自增插入索引

先插入8，再插入7，原来的一个节点分裂成两个节点，整个树还有个平衡，尽量不要用uuid 分库分表：redis，雪花算法

为什么非主键索引的叶子节点存储的是主键值？一致性和节省存储空间

联合（复合）索引的底层存储结构？

索引要从左到右依次递增，无序排序不行；

几个字段组成，放在key里面，data放数据 ;

先比较第一个字段10002,10004比较排序，按照第一个排序，如果第一个都相等，联合索引会第二个字段排序，依次类推；越在前面越优先；

最左前缀原则：第三条查询语句用到索引，

age排在前面也可以用索引，mysql执行会优化，把name排在前面，尽量推荐按照顺序来写；

跳过某个字段，索引失效原因：跳过第一个字段，直接比较第二个字段，导致无序排序