MySQL进阶：（第二篇）索引的结构、分类、语法-EW帮帮网

一、索引概述

介绍：
索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

1.1数据库中查找数据时有索引和没索引对比:

1.2索引优缺点：

优势：

提高数据检索的效率，降低数据库的I0成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势：

索引列也是要占用空间的。
索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

二、索引结构

2.1 简介

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

不同存储引擎对这几种索引的支持：

2.2 二叉树

什么是二叉树？

二叉树（Binary Tree）是由节点组成的集合，这种结构满足以下条件：

每个节点最多有两个子节点，通常分为左子节点和右子节点。
每个节点包含三个部分：一个存储数据的值、一个指向左子节点的引用或指针以及一个指向右子节点的引用或指针。
节点之间通过链接互相连接形成树形结构。

二叉搜索树（Binary Search Tree, BST）：对于树中的每个节点，左子树上所有节点的值都小于它的根节点的值；右子树上所有节点的值都大于它的根节点的值。

解决办法：

2.3 红黑树

红黑树是带有颜色属性（红色或黑色）的二叉查找树，并且满足以下五个特性：

每个节点要么是红色，要么是黑色。
根节点是黑色。
所有叶子节点（外部节点，NIL节点，通常不显示出来）都是黑色的。

注意：叶子节点是指空节点，不显示出来

记忆口诀：左根右，根叶黑，不红红，黑路同

如果一个节点是红色，则它的两个子节点都是黑色。（换句话说，从任一节点到其每个叶子的所有路径都包含相同数量的黑色节点）
从任一节点到其每个叶子的所有简单路径都包含相同数量的黑色节点。

这些规则确保了从根到叶子的最长路径不会超过最短路径的两倍长，因此红黑树大致平衡。

缺点：

红黑树：大数据量情况下，层级较深，检索速度慢

2.4 B-Tree（多路平衡查找树）

以一颗最大度数（max-degree）为5(5阶)的b-tree为例(每个节点最多存储4个key，5个指针)：

具体动态变化的过程可以参考网站：https://www.cs.usfca.edu/~galles/visualization/BTree.htmlhttps://www.cs.usfca.edu/~galles/visualization/BTree.html

2.5 B+Tree

传统B+Tree：

B+ 树是一种自平衡的树数据结构，是 B 树（B-Tree）的一种重要变体。它在现代数据库系统和文件系统中扮演着核心角色，主要用于高效地存储和检索大量有序数据，特别是在磁盘等外部存储设备上。

MySQL对B+Tree做了优化：

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

2.5Hahs

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个(或多个）键值，映射到一个相同的槽位上，任他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

Hash索引特点：

1.Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，<，)
2.无法利用索引完成排序操作
3.查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

存储引擎支持：
在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

2.6为什么InnoDB存储引擎选择使用B+tree索引结构？

相对于二叉树，层级更少，搜索效率高；

对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低;

相对Hash索引，B+tree支持范围匹配及排序操作；

三、索引分类

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引选取规则：
        如果存在主键，主键索引就是聚集索引。
        如果不存在主键，将使用第一个唯一（UNIQUE）索引I作为聚集索引。
        如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询：

四、索引语法

若是不指定索引类别，则默认常规索引

示例：

表结构及数据展示：

建表sql脚本：

create table tb_user(
	id int primary key auto_increment comment '主键',
	name varchar(50) not null comment '用户名',
	phone varchar(11) not null comment '手机号',
	email varchar(100) comment '邮箱',
	profession varchar(11) comment '专业',
	age tinyint unsigned comment '年龄',
	gender char(1) comment '性别 , 1: 男, 2: 女',
	status char(1) comment '状态',
	createtime datetime comment '创建时间'
) comment '系统用户表';


INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');

查看索引语句：