mysql索引

数据库索引

操作系统磁盘块大小(4k)和内存页(64k)

磁盘IO时间非常长，尽可能减少磁盘io时间（每）

数据库管理系统中一个经过排序的数据结构

目的：使查询变快（空间换时间）

mysql底层用B+tree, mysql会提前加载中间结点（中间结点用来排序，且数量不多）以便于查询

使用B+树的优势：

**1.**单一节点存储更多的元素，使得查询的IO次数更少。

**2.**所有查询都要查找到叶子节点，查询性能稳定。

**3.**所有叶子节点形成有序链表，便于范围查询。

每个结点最多16k数据

（红黑树在大量数据下依然复杂->高度很高）

B+tree

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hFJUStVp-1647847668928)(e174971484669f3a0a08eaf4faf72147.png)]

b+树的度等于关键字的个数

b+树更适合全表扫描（叶子结点有指针，为有序链表，可以直接从叶子结点查询）

b+树和b树比：

• B+树的中间节点没有卫星数据，所以同样大小的磁盘页可以容纳更多的节点元素，这就意味着在数据量相同的情况下，B+树更加的矮胖，因此IO的次数也就较少
• B+树查询必须查找到叶子节点，每一次查找都是稳定的

innodb不支持hash索引，因为hash经过映射后不能范围查询

一个m阶的B树具有如下几个特征：

1.根结点至少有两个子女。

2.每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m

3.每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m

4.所有的叶子结点都位于同一层。

5.每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划

父结点的元素都出现在子节点，因此所有叶子结点包含了全量元素

b+树是红黑树的横向扩展

每一个叶子结点都带有指向下一个结点的指针，形成了一个有序链表

b+树中只有叶子结点带有卫星数据(data)，其余中间结点仅仅是索引

索引分类

普通索引、唯一索引、主键索引、全文索引

存储引擎

存储引擎是修饰数据库表的

平衡结点

左左型 右旋

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aYSFwZmf-1647847668930)(5173ca691dafc3b5fd5f1e2cc6e2ffd9.png)]

右右型 左旋

聚集索引 只有一个

如果没有主键，第一个非空的唯一索引为聚集索引

索引的键值的逻辑顺序与表数据结构的物理存储顺序一致

完整的数据放在哪棵B+树的叶子结点上，那这个索引就叫聚集索引

二级索引 在叶子结点放聚集索引的id

回表 根据二级索引叶子结点上的id在聚集索引中查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oaj5JkZs-1647847668930)(7bb45d935422f1a3b44e50274a250e5f.png)]

1.并不是索引越多越好

2.列的离散度 列的不同值 500W/总行数 1离散度越高 0离散度低

优化器 默认认为 你走索引会更好

3.联合索引的最左匹配原则

单列索引

身份证号 考号 出现的顺序会影响我们的使用

覆盖索引 使用索引的情况 不需要回表 二级索引上的数据就是要查询的数据

索引建立

查询频率高的建立索引

索引不应过多（磁盘和时间都浪费）

散列度过低的字段不作索引

select后不建索引

在where后建索引

随机、无序、频繁更新的值不适合作索引

创建复合索引时避免冗余->有了a,b,c索引 别建a或b或b,c索引

失效:

1.出现函数

2.隐式转换

3.like最左前缀 最左不能加%

4.负向查询no in

explain

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: （非唯一性索引）表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值（返回行数可以是0或null） 返回匹配的所有行（0，多）

eq_ref: （唯一性索引）类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件 返回匹配唯一行数据（有且只有1个，不能多 、不能0）

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

复合索引顺序

where和order by按照复合索引顺序使用，不要跨列或无序使用