2020年8月13日星期四

MySQL 的B+树索引.

一、B+树索引概述

索引是应用程序设计和开发的一个重要方面。若索引太多,应用程序的性能可能会受到影响(需维护索引的结构和数据);而索引太少,对查询性能又会产生影响。

二叉树,左子树的键值总是小于根的键值,右子树的键值总是大于根的键值。

平衡二叉树(AVL树),任何节点的两个子树的高度最大差为 1。平衡二叉树的查询速度很快,但是维护一棵平衡二叉树的代价是非常大的。通常来说,需要 1 次或多次左旋和右旋来得到插入或更新后树的平衡性。

B+ 树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树,B+ 树中的 B 不是代表二叉(binary),而是代表平衡(balance)。在 B+ 树中,所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上,由各叶子节点指针进行连接,叶子节点之间组成一个双向链表。

B+ 树索引的本质就是 B+ 树在数据库中的实现,但是 B+ 索引在数据库中有一个特点是高扇出性(数据库分区),因此在数据库中,B+ 树的高度一般都在 2-4 层,这也就是说查找某一键值的行记录时最多只需要 2 到 4 次IO。

数据库中的 B+ 树索引可以分为 聚集索引和辅助索引。

B+ 树索引并不能找到一个给定键值的具体行。B+ 树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存,再在内存中查找,最后得到要查找的数据。

二、索引创建和查看

索引的创建和删除可以通过两种方法,一种是 ALTER TABLE,另一种是 CREATE/DROP INDEX。用户可以设置对整个列的数据进行索引,也可以只索引一个列的开头部分数据。

## 添加索引alter table <table_name> add index <index_name>(column_list);alter table <table_name> add unique(column_list);alter table <table_name> add primary key(column_list);## 删除索引alter table <table_name> drop index <index_name>;alter table <table_name> drop primary key;
## 添加索引create index <index_name> on <table_name>(column_list);create unique index <index_name> on <table_name>(column_list);## 删除索引drop index <index_name> on <table_name>;

索引的查看,可以使用命令 SHOW INDEX。

SHOW INDEX FROM <table_name>


Non_unique 表示是否非唯一的索引;Column_name 表示索引列的名称;Seq_in_index 表示索引中该列的位置;Collation 表示列以什么方式存储在索引中,可以是 A 或 NULL,B+ 树索引总是 A,即排序的;Sub_part 是否是列的部分被索引,如果是整个列,则该字段显示为 NULL;Packed 关键字如何被压缩;Null 是否索引中的列含有 NULL 值;Index_type 索引的类型。

Cardinality 非常关键的值,表示索引中唯一值的数目的估计值,优化器会根据这个值来判断是否使用这个索引。这个值并不是实时更新的,如果需要实时更新 Cardinality 的信息,可以使用 ANALYZE TABLE 命令。建议在非高峰时间,对应用程序下的几张核心表做 ANALYZE TABLE 操作,这能使优化器和索引更好的工作(除了 ANALYZE TABLE 外,还有 SHOW TABLE STATUS、SHOW INDEX 以及访问 INFORMATION SCHEMA 架构下的表 TABLES 和 STATISTICS 都会去重新计算 Cardinality 值)。

MySQL 对于主键索引的创建会采用临时表的方式,首先会创建一张带有主键索引的临时表,然后把原表中数据导入到临时表,接着删除原表,最好把临时表重命名为原表名,这部分操作会导致数据库不可用,因此建议在创建表的时候就定义好主键!

MySQL 对于辅助索引的创建支持 FIC —— Fast Index Creation(快速索引创建)方式,其会对创建索引的表加上一个 S 锁,不需要建立临时表。

MySQL 5.6 版本开始支持 Online DDL(在线数据定义)操作,其允许辅助索引创建的同时,还允许其他诸如 INSERT、UPDATE、DELETE 这类 DML 操作,其原理是将 DML 操作日志写入到一个缓存中,待完成索引创建后再将缓存应用到表上,以此达到数据的一致性,这个缓存的大小由参数 innodb_online_alter_log_max_size 控制,默认的大小为 128MB。

AlTER TABLE <tbl_name> [index_type](index_col_name) ALGORITHM [=] {DEFAULT|INPLACE|COPY}LOCK [=] { DEFAULT| NONE| SHARED| EXCLUSIVE }

ALGORITHM 制定了创建和删除索引的算法,COPY 选择创建临时表的方式;INPLACE 表示创建和删除索引不需要创建临时表;DEFAULT 会根据参数 old_alter_table 来判断是使用 INPLACE 算法还是 COPY 算法,该参数的默认值为 OFF,表示采用 INPLACE 方式。

LOCK 指定了创建和删除索引的时候添加锁的情况,NONE 表示不添加任何的锁;SHARE 表示添加 S 锁;EXCLUSIVE 表示添加 X 锁;DEFAULT 会根据并发性执行一个锁升级的过程,先判断是否可以使用 NONE 模式,若不能,再判断是否可以使用 SHARED 模式,否则将使用 EXCLUSIVE 模式。

三、联合索引

联合索引是指对表上的多个列进行索引。从本质上来说,联合索引也是一棵B+ 树。那么什么时候会使用到联合索引呢?"WHERE a= xxx and b=xxx" 和 "WHERE a= xxx" 都能使用到联合索引,但是"WHERE b= xxx"则使用不到这个索引,因为叶子节点上的 b 值是无序的,这也是人们常说的 —— 最左前缀匹配。除此之外,因为联合索引已经对键值进行了排序处理,因此对于索引列的排序操作也能使用到索引。

四、覆盖索引

覆盖索引是指从索引中就可以得到查询的记录,而不需要查询聚集索引中的整行记录的所有信息,因此可以减少大量的 IO 操作。比如只查询索引列的信息。

对于统计问题而言,在同时存在辅助索引和聚集索引的情况下,InnoDB 存储引擎会优先使用辅助索引来进行统计,因为辅助索引远小于聚集索引(辅助索引不需要维护整行记录的全部信息)。

此外,在通常情况下,诸如(a,b)的联合索引,一般是不可以选择列 b 作为查询条件。但是如果是统计操作,并且是覆盖索引的,则优化器会进行选择。

五、其他

当访问的数据占整个表中数据的蛮大一部分时(一般是20%左右),即使存在可以使用的辅助索引,优化器仍然会选择通过聚集索引来查找数据,因为顺序读要远大于离散读。这是由当前传统机械硬盘的特性所决定的,即利用顺序读来替换随机读的查找。可以使用关键字 FORCE INDEX 来强制使用某个索引。

Multi-Range Read 优化是 MySQL 5.6 开始支持的一种索引优化方式,目的是为了减少磁盘的随机访问,并且将随机访问转化为较为顺序的数据访问,这对于 IO-bound 类型的 SQL 查询语句可带来性能极大的提升,适用于 range、ref、eq_ref 类型的查询。

Index Condition Pushdown(索引下推) 优化是 MySQL 5.6 开始支持的一种索引优化方式,默认开启,使用 SET optimizer_switch = 'index_condition_pushdown=off'; 可以将其关闭。ICP 优化可以有效的提高查询效率,适用于 range、ref、eq_ref、ref_or_null 类型的查询。

MySQL 的B+树索引.Payoneer跨境电商竞化论厦门站之"探索"助力产业带抱团出海,新型贸易平台虚位以待东西网Tagboard佛系老板的运营智慧,稳稳赚大钱Facebook因监管者疑虑推迟在欧盟发布约会服务火车上有没有插座?广珠城轨新会站在哪里?春节去哪泡温泉好?

没有评论:

发表评论