Mysql索引数据结构_索引下推的唯一确定是需要再磁盘上多做数据筛选-CSDN博客

本文链接：https://blog.csdn.net/qq_33271141/article/details/107886849

索引

Hash Mysql使用的数据结构

优点

根据HashCode计算得到存储位置的下标，速度快，时间复杂度O(1)

缺点

Hash结构会造成Hash碰撞，HashMap通过高16位异或低16位减少Hash冲突。
利用Hash存储的话需要将所有的数据文件添加到内存中，比较耗费内存。
Hash等值查询效率快，但不支持范围查询，需要从前往后扫描一遍，时间复杂度O（n）

二叉树与红黑树

缺点

每个节点存放一个数据。而内存从磁盘取回一个大小为4k的整数倍的大小的块，Mysql默认读取16k的大小
节点一多，树的深度变深，io次数会跟随深度变多，影响数据读取的效率。

B树

优点

每个节点能存放多个数据，既符合能存放一块的数据，Mysql中可每个节点能存放16K的数据块
寻找某个元素加载的磁盘块比较少。既IO次数少

寻找28

加载磁盘块1，大小16k。 28位于16 - 24 之间，加载磁盘块3
加载磁盘块3，大小16k。 28位于25 - 31 之间，加载磁盘块8
加载磁盘块8，大小16k。 28存在于磁盘块8。总加载大小为48k

缺点

实际数据占用过大会导致每一层存储的有效数据过少。
不支持兄弟磁盘块之间的读取

假设一个数据大小1k（忽略其他元素），既一个磁盘块能存储16个数据

三层的结构存储 16 * 16 * 16 = 4096。数据量过少

B树数据结构

B+树 Mysql使用的数据结构

优点

主键值出现在多个层次中，作为条件筛选。
非叶子节点不存储实际数据。
兄弟叶子节点使用指针相连接
支持主键的范围查找与分页查找。支持叶子节点的随机查找

非叶子节点不存储实际数据，假设每个指针大小10b，既一个磁盘块16k能存储1600个指针

三层的结构存储 1600 * 1600 * 16= 40960000。超过千万数据

B+树数据结构

Mysql存储引擎

Mysql中有两类索引数据结构：Hash 与 B+树

其中 InnoDB、MyISAM两种存储引擎使用B+树作为索引数据结构

Memory存储引擎使用Hash作为索引数据结构。 InnoDB也可以用Hash索引（InnoDB使用自适应Hash，由Mysql处理，用户无法干预）

InnoDB 聚族索引（索引与数据存放一起）

文件存放格式

xxx.frm		// 存储数据结构
xxx.idb		// 存储实际数据文件  innoDB将索引与数据一起存储

B+树数据存储结构

使用InnoDB作为存储引擎，B+树叶子节点实际数据存放：主键+表中其他字段数据（实际的整行数据）

如果没有主键则使用唯一键，没有唯一键会生成6字节的row_id作为主键

如果以其他字段创建索引，则会再生成一棵B+树，叶子节点存放***主键ID***

MyISAM 非聚族索引（索引不与数据存放一起）

文件存放格式

xxx.frm		// 存储数据结构
xxx.MYD		// MyISAM Data 存储实际数据
xxx.MYI		// MyISAM Index 存储索引

B+树数据存储结构

使用InnoDB作为存储引擎，B+树叶子节点实际数据存放：该数据在表中的地址（实际数据的存放的地址），然后通过地址去访问数据

MyISAM_B+树数据存储结构

Mysql名词

回表

先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。

索引覆盖

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

最左匹配

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

假设有两个字段 name、age，为它们建立组合索引 (name,age)

where name = ? and age = ?		 符合最左匹配原则
where name = ?					符合最左匹配原则
where age = ?					不符合最左匹配原则,age前方需要先经过name
where age = ? and name = ?		 通过mysql优化器调整为where name = ? and age = ?  符合最左匹配原则

索引下推

索引下推的唯一缺点是需要在磁盘上多做数据筛选，由原来放到在内存中筛选（mysql server），现在放到磁盘中筛选。

不过因为索引下推到磁盘筛选后，实际上返回的数据量更加，整理IO量大大减少，反而提升了性能

select * from table where where name = ? and age = ?

没有索引下推

先从存储引擎中拉取数据（根据name筛选的添加）

然后在mysql server(服务层) 根据age来进行数据筛选
有索引下推能减少IO量

直接使用（name，age）从存储引擎中拉取数据

无需通过在mysql server(服务层) 筛选

索引匹配

全值匹配

全值匹配指的是和索引中的所有列进行匹配

(name,age,pos)组合索引
select * from staffs where name = 'Lin' and age = 23 and pos = 'dev';

匹配最左前缀

只匹配前面的几列

(name,age,pos)组合索引
select * from staffs where name = 'Lin' and age = 23; 
select * from staffs where name = 'Lin;

匹配列前缀

可以匹配某一列的值的开头部分

(name,age,pos)组合索引
select * from staffs where name like 'L%';	索引有效
select * from staffs where name like '%L';	索引失效

匹配范围值

可以查找某一个范围的数据

(name,age,pos)组合索引
select * from staffs where name > 'Lin';   索引有效。  > < 后面的索引失效

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

可以查询第一列的全部和第二列的部分

(name,age,pos)组合索引
select * from staffs where name = 'Lin' and age > 23;   索引有效。  > < 后面的索引失效

只访问索引的查询

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

(name,age,pos)组合索引
select * from staffs where name = 'Lin' and age = 23 and pos = 'dev';  
select * from staffs where pos = 'dev' and name = 'Lin' and age = 23 ;  通过mysql优化器 优化为上一条sql 索引有效

索引失效

like左%

name字段创建索引

select name from name like 'J%'  索引有效
select name from name like '%J'  索引失效

> < 后面索引失效

（name，age，pos）为组合索引

select name from name > 'Lin' 索引有效 本身就是有序排列
select name from name = 'Lin' and age > 20 and pos = 20  pos不走索引,该sql只走(name，age),pos因前面使用>造成后续索引失效

or 字段索引失效

总结：

单列索引中 or 会使用索引。

组合索引中：1、全部列都是组合索引，or走索引。属于特殊情况。 2、部分是组合索引，or失效，直接全表查询

表abc中字段（a、b、c）组合索引(abc)

表abc2中字段（a、b、c、d）组合索引(abc)

表actor中字段 actor_name 是索引列

or走索引 因为表abc三个字段abc全部被组合索引覆盖,无论什么情况都能通过abc索引获取。属于特殊情况
select * from abc where a = 1 or b = 2;	

or不走索引, 表abc2中abcd四个字段没被全部覆盖。需要or直接全表查询，开发中建议使用in 或 union all 替换 or
select * from abc2 where a=1 or b = 2;

or走索引 因为actor_name是一个单列索引 能通过b+树搜索
select * from actor where actor_name = 'Lin' or actor_name  = 'Chen'

隐式类型转换会造成索引失效

phone列 varchar类型
select * from user where phone = 13000001234; 索引失效
select * from user where phone = ‘13000001234’; 索引有效


# 索引优化细节

1. 当使用索引进行查询的时候尽量不用使用表达式，把计算放到业务层而不是数据库层

2. 尽量使用主键查询，而不是使用其他索引，因为主键查询不会触发回表查询

3. 使用前缀索引

4. 使用索引扫描排序

5. union all，in，or都能使用索引，但是推荐使用in

   表actor中     字段 actor_name 是索引列。

   ```
   union all
   select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
   
   in
   select * from actor where actor_id in (1,2);
   
   or  
    select * from actor where actor_id = 1 or actor_id = 2;
   ```

6. 范围列可以使用索引

   范围条件是：`<`、`<=`、` >=、`>`、`between`。

   范围查询可以使用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列。

7. 强制转换类型会转换为全表扫描

   ```
   phone列 varchar类型
   select * from user where phone = 13000001234;   		索引失效
   select * from user where phone = '13000001234';		    索引有效
   ```

8. 更新十分频繁，数据区分不高的字段不宜建立索引
   
   1. 更新会变更b+树，更新频繁的字段建立索引会大大降级数据库性能
   
   2. 类似与这类区分度不大的属性，建立索引没有意义，不能有效的过滤数据
   
   3. 一般区分度在80%以上的时候可以建立索引，区分度可用以下sql计算
   
      ```
      count(distinct(列名)) / count(*)
      ```
      
   
9. 创建索引的列，不允许为null，可能会得到不符合预期的结果

10. 当需要进行表练级的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致

11. 能使用limit的时候尽量使用limit

12. 单表索引建议控制在5个以内

13. 单索引字段数不允许超过5个（组合索引）

14. 创建索引的时候应该避免以下概念

    1. 索引越多越好
    2. 过早优化，在不了解系统的情况下优化