mysql数据结构及索引相关

索引的数据结构

mysql数据库为什么采用b+树作为索引的结构呢？为什么不是hash、b树、二叉树又或者红黑树呢？

Hash

缺点：

1. 利用hash作为存储引擎的索引的话，需要将所有的数据加载到内存中，比较耗费内存。
2. Hash存在碰撞，如果hash算法不够好，存在大量碰撞的话，会造成索引数据倾斜，从而导致查询效率变低
3. 如果是等值查询hash比较快，但是如果是范围查询，则效率就非常低了，所以hash就不太适合了。

二叉树和红黑树

缺点：

无论是二叉树还是红黑树，都会因为数据的增长导致树的深度越来越深，从而导致了io的次数变多，查询效率变低，影响效率；

B树

概念

每一个节点放一整块数据（数据的大小取决于degree），当节点上的数据个数大于degree-1（度、介）的时候，就会进行节点的扩展，向下延伸一个节点。

例如：degree = 4，则每个节点不可以 > 3 ,当 >3的时候，就会向下延伸一个节点。

如图：

                                     

 

这样的方式的话，每一个节点可以放置一页数据，比如 8KB  16KB  32KB等等（4KB的整倍数）。可以减少树的深度，从而减少IO次数。

Mysql中的做法

 

       蓝色：存放的是子节点的磁盘地址信息

       紫色：存放的是表中记录的主见信息

       data：存放的是除主键以外的行数据。

 

根据上图，假设我现在要查询主键ID=28的数据，则：

1. 先拿到根节点，根据28进行匹配，找到了16-34之间的P2。则根据P2对应的磁盘地址，拿到磁盘块3；
2. 根据磁盘块3，找到28应该在 25-31 之间的P2，则根据P2对应的磁盘地址，拿到磁盘块8；
3. 根据磁盘8中，找到与28匹配的数据，返回给客户端，完成查询

 

注意：如果在以上3步中，任何一步找到了匹配的数据，都直接返回数据，不再继续查询，如果在最叶子节点也未能找到数据，则没有匹配数据可查；

 

以上三步，假设每个节点的大小为16KB，则一共用了3次IO 每次16KB，则一共 48KB的IO。

存储量

   假设：

1. 每个节点的大小为16kb，抛出磁盘地址存储和主键存储的大小
2. 每个data中，每条数据大小为 1kb

 

则存储量应该为：

16 * 16 *16 = 4096（粗略算法，实际存储数据的多少应该根据每条数据大小、主键值得占用空间、磁盘块的大小来计算 ）

 

B树缺点

在储存量计算的假设中可以看出，B树在3层的时候4096条记录，很明显，不适用与大数据量，在几百万甚至几千万数据的时候，B树也会出现树的深度越来越深的问题。

B+树

    概念

B+树中的所有叶子节点之间是一种链式环的结构。因此B+树中，可以通过两种方式查询，

1. 根据跟节点，一层一层向下查询
2. 根据叶子节点开始，按照顺序查找。

Mysql中的做法

Mysql中，把子节点的磁盘索引和表中的行记录主键放在了非叶子节点的节点上，这样同样的磁盘块（如：16kb）,则可以存放更多的索引信息，把实际的数据和表中的行记录主键放在了叶子节点上。

 

B+树的层数问题

B+树的层数是3层还是4层？

层数取决于数据量。Degree的大小 = 磁盘块大小 / 索引占用空间的大小，也就是说，同样的深度，索引占用的空间越小，则存储的数据量越多，反之存储量就会越少。

相同的数据量情况下，索引占用空间越小。B+树的层数越少。反之层数越多。

存储量

假设：

1. 每个节点(磁盘块)的大小为16kb
2. 磁盘索引 + 表中的行记录主键 = 10字节

 

则存储量应该为：

根节点的存储量为： 16kb = 16000字节 / 10 字节 = 1600，根节点可以存储1600个索引数据。

第二层节点中的每一个节点也是 16kb = 16000字节 / 10 字节 = 1600

第三层节点的每一个节点存储量应该为：16kb / 1kb（每条数据大小） = 16

 

所以，B+树的存储量应该为 1600*1600 * 16 = 40960000（粗略算法，实际存储数据的多少应该根据每条数据大小、主键值得占用空间、磁盘块的大小来计算 ）

 

 

不同存储引擎在叶子节点上存放的数据

innoDB

    在叶子节点data上直接放置的是行数据

    注意：

1. innoDB是通过b+树的结构对主键创建索引的，如果没有主键，则取唯一键，如果唯一键也没有，则会生成一个6字节的row_id（对外是不可见）作为主键。
2. 如果创建索引的键为其他列（非主键），那么在叶子节点中存储的是该记录的主键，先通过索引键拿到记录的主键，然后在通过记录主键查询到对应的数据记录。
3. 数据是跟主键索引绑定在一起的，其他索引绑定的都是主键的值。
4. 一个索引对应一棵树。
5.  

 MyISAM

    在叶子节点data上放的是一个磁盘地址，然后根据磁盘地址拿到数据行

回表

因为数据据是跟主键索引绑定在一起的，其他索引绑定的都是主键的值，没个列的索引都独立形成一棵树，也就是说，有多个少索引，就有多少个树。

 

所以如果创建索引的键为其他列（非主键），那么在叶子节点中存储的是该记录的主键，先通过索引键拿到记录的主键，然后在通过记录主键查询到对应的数据记录。

 

举例：EATE

CREATE TABLE T
(
    id   INT PRIMARY KEY,
    k    INT NOT NULL,
    NAME VARCHAR(16),
    INDEX (k)
) ENGINE = INNODB;

多岁的 TABLE T
如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表；

    id   INT PRIMARY KEY,

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询    k

 

最左匹配

联合索引

索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树

 

假如创建一个（a,b)的联合索引，那么它的索引树是这样的

 

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

 

同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的

最左匹配原则

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)后续的索引就会停止匹配。

假设现在有一个组合索引：name,age

ALTER  TABLE t ADD INDEX index_name_age (NAME,age)

 

查询条件如下：

1. where name = ? and age = ?
2. where name = ?
3. where age = ?
4. where age = ? and name = ?

 

在上述的查询条件中，1、2、4是会触发索引查询的，1、2肯定会触发索引查询，3不会触发索引查询，因为不符合最左匹配原则，4在查询的时候，mysql的优化器会自动将两个查询条件的顺序换成  name = ? and age = ? 所以4也可以触发索引查询。

聚簇索引和非聚簇索引

聚簇索引

不是单独的索引类型，数据跟索引存储在一起的，叫做聚簇索引

 

非聚簇索引

数据文件跟索引文件分开存放的叫做非聚簇索引

 

检测是否用到索引

语法：

explain select * from tableName

 

那上面联合索引name,age 的四个查询条件来做实验

1. where name = ? and age = ?
2. where name = ?

3. where age = ?

4. where age = ? and name = ?

     key：表示的是，索引名称

     key_len： 表示索引长度

索引匹配方式

全值匹配

 

匹配最左前缀

匹配列前缀

匹配某一列开头的部分，注意，%号不可在左边。

explain select * from t where age = 18 and name like 'n%';

匹配范围查询

可以匹配某一个范围的值

select * from t where age = 18 and name > ‘n’

匹配范围查询

可以匹配某一个范围的值

select * from t where age = 18 and name > ‘n’

需要注意的是，如果联合索引在查询的时候，遇到范围查询(>、<、between、like)后续的索引就会停止匹配。

 

select * from t where  name > ‘n’ and  age = 5

上图中，其实只用到了name作为匹配，并未用age作为匹配

精确匹配某一列并范围匹配另一列

select * from t where name='name5' and age > 5;

先用name匹配一列，然后在用age匹配范围值

只访问索引查询

查询的时候，只需要访问索引，不需要访问数据行，本质上就是覆盖索引