MySql索引简介

时间:2023-03-08 16:47:37
MySql索引简介

从“找”到B+树

  1. 索引是用来查找的。
  2. 折半查找是一种很优秀的方式。适合于 范围查找,固有缺点就是需要元素是有序的。二叉搜索树就是对折半查找的一种基础的实现。
  3. 但二叉搜索树当遇到特殊数据的时候,会出现“偏坠”,影响性能。所以就出现了平衡二叉树,来保证在各种情况下,折半查找的效率稳定。
  4. 平衡二叉树还有问题,那就是当大量数据时树高会迅速增高,需要多次切换树节点,所以并不适合外存,所以就出现了树高低,树节点信息多的多叉树-B树。

B+树

MySql索引简介

如何使用B+树查找数据

每一个索引都是一棵B+树

当索引中包含多个字段时,一个记录块中包含多个字段。第一个字段相同时,按照第二个字段排序,以此类推。

这种情况下,怎么使用索引?(最左前缀原则)

同理当字段为字符串时,可以将字符串看成一个记录块中包含多个字符。这种情况下怎么使用索引?(前缀索引)

Page结构

MySql索引简介

MySql索引简介

MySql索引简介

MySql索引简介

MySql索引简介

聚簇索引

MySql索引简介

主键索引上 有行

辅助索引上有 该索引值和主键  所以找到该索引位置后还要通过主键值到主索引中查找

索引覆盖现象

辅助索引上的值就够了 不用在到聚簇索引上找了

那样是否可以在辅助索引上将所有要用到的字段都加上来呢?

常见规则解释

1、索引的数量要控制:

(1) 单张表中索引数量不超过5个

(2) 单个索引中的字段数不超过5个

(3) 对字符串使⽤用前缀索引

(4) 建议优先考虑前缀索引,必要时可添加伪列并建立索引

2、主键准则

(1) 表必须有主键

(2) 不使用更新频繁的列作为主键

(3) 尽量不选择字符串列作为主键

(4) 不允许UUID MD5 HASH这些作为主键

(5) 默认使⽤非空的唯一键作为主键

(6) 建议选择自增

3、重要的SQL必须被索引,比如:

(1) UPDATE、DELETE语句的WHERE条件列

(2) ORDER BY、GROUP BY、DISTINCT的字段

4、多表JOIN的字段注意以下:

(1) 区分度最大的字段放在前面

(2) 核⼼SQL优先考虑覆盖索引

(3) 避免冗余和重复索引

(4) 索引要综合评估数据密度和分布以及考虑查询和更新比例

5、索引禁忌

(1) 不在低基数列上建立索引,例如“性别”

(2) 不在索引列进行数学运算和函数运算

6、尽量不使用外键

(1) 外键用来保护参照完整性,可在业务端实现

(2) 对父表和子表的操作会相互影响,降低可用性

7、索引命名:

非唯一索引必须以 idx_字段1_字段2命名,唯一所以必须以uniq_字段1_字段2命名,索引名称必须全部小写

8、新建的唯一索引必须不能和主键重复

9、索引字段的默认值不能为NULL,要改为其他的default或者空。NULL非常影响索引的查询效率

10、反复查看与表相关的SQL,符合最左前缀的特点建立索引。多条字段重复的语句,要修改语句条件字段的顺序,为其建立一条联合索引,减少索引数量

11、能使用唯一索引就要使用唯一索引,提高查询效率

12、使用EXPLAIN判断SQL语句是否合理使用索引,尽量避免extra列出现:Using File Sort,Using Temporary

13、 SQL变更需要确认索引是否需要变更并通知DBA