海南城乡建设庁网站,吐鲁番做网站,南昌百度关键词搜索,手机站点cn文章目录 3.MySQL索引及两种索引分类方法3.1索引的概念3.1.1相关定义3.1.2查询例子 3.2索引的底层3.2.1二叉树#xff08;1#xff09;满二叉树#xff08;2#xff09;完全二叉树#xff08;3#xff09;二叉查找树#xff08;4#xff09;二叉平衡树#xff08;AVL1满二叉树2完全二叉树3二叉查找树4二叉平衡树AVL①区分深度和高度②平衡因子③特点 5红黑树BST 3.2.2B树3.2.3B树 3.3索引的类型3.3.1按功能逻辑区分1主键索引——Primary key(column)2唯一索引——Unique(column)3普通索引——Index index_name(column)4全文索引——Fulltext(column)5前缀索引——Key(column_name(prefix_length))6组合索引——Index index_name(column1,column2)7空间索引 3.3.2按底层数据结构区分1聚簇索引非聚簇索引二级索引 3.3.3InnDB存储引擎的两种索引1InnoDB——主键索引聚簇索引2InnDO——辅助索引非聚簇索引二级索引3主键搜索过程 3.3.4MyISAM的索引1MyISAM——主键索引非聚簇索引2MyISAM-辅助索引非聚簇索引 3.4相关概念回表/索引覆盖/索引下推3.4.1回表3.4.2索引覆盖3.4.3索引下推 3.5参考文章链接3.6总结 3.MySQL索引及两种索引分类方法
3.1索引的概念
3.1.1相关定义
索引在MySQL中也叫做“键key”是存储引擎用于快速找到记录的一种数据结构这也是索引最基本的功能。索引优化是查询性能优化最有效的手段。如果想要在一本书中找到某个特定主题一般会先看书的目录找到对应的页码然后直接翻到对应的页码即可查看。在MySQL中存储引擎用类似的方法使用索引首先在索引中找到对应的值然后根据匹配的索引记录找到对应的数据行。简单的说数据库索引类似于书前面的目录能加快数据库的查询速度。
3.1.2查询例子
select name from user where user_id 5 如果user_id列上建有索引则MySQL将使用该索引找到user_id 为 5的行即MySQL现在索引上按值进行查找然后返回包含该值的数据行。索引可以一个或多个列的值如果索引包含多个列那么列的顺序也很重要因为MySQL只能高效地使用最左前缀列。
3.2索引的底层
MySQL默认使用的索引的底层数据结构是 B树以下将介绍二叉树、满二叉树、完全二叉树、二叉查找树、二叉平衡树AVL、红黑树BSTB树、B树
3.2.1二叉树
特点每个节点最多有两个子树的树结构
1满二叉树
特点除了叶子节点外每一个节点都有两个子节点且所有叶子节点都在二叉树的同一高度上
2完全二叉树
特点完全二叉树 除去底层节点后为满二叉树且底层节点依次从左到右分布
3二叉查找树
特点任意一个节点的值大于左子树任意一个节点的值小于右子树任意一个节点的值
4二叉平衡树AVL
①区分深度和高度 深度 根节点深度为0从根节点自顶向下逐层累加 高度 叶子节点高度为0从叶子节点自底向上逐层累加
②平衡因子
平衡因子该节点的左子树深度-右子树深度
③特点
特点树中任一节点的两个子树的平衡因子的绝对值不超过1重要
5红黑树BST 红黑树是一棵自平衡搜索树 规则 根节点总是黑色每个节点只能是红色或黑色红色节点的父或子节点必然是黑色的即两个红色的节点不会相连任意节点到后代NULL节点的每条路径都具有相同数量的黑色节点每个NULL节点都是黑色的 如何保证平衡 左旋/右旋 左旋根节点往左偏转将右子树的第一个节点变成根节点右旋根节点往右偏转将左子树的第一个节点变成根节点 染色更改根子节点的颜色确保满足规则
3.2.2B树
B树即一棵多(m)路平衡查找树 字母m表示阶数一个结点最多有多少个孩子结点同时m必须2一个结点中同时存储关键字、以及指向儿子节点的指针 特点 关键字数据分布在整棵树中即数据均分布在树中任何一个关键字仅且出现在一个节点中
3.2.3B树
基于B树进行改造的树更适合海量数据的存储特点 所有关键字均分布在叶子节点的链表中非叶子节点不存储关键字只存储key以及指向儿子节点的指针叶子节点之间互相串联首尾相连 与B树对比 磁盘读写代价更低查询效率更稳定更便于扫库和区间扫描
3.3索引的类型
3.3.1按功能逻辑区分
1主键索引——Primary key(column) 定义数据列不允许重复不允许为NULL一个表只能有一个主键 举例 ALTER TABLE table_name ADD PRIMARY KEY (column);2唯一索引——Unique(column) 定义索引列中的值必须是唯一的但是允许NULL值。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性而不是为了查询效率。一个表允许多个列创建唯一索引。 举例 ALTER TABLE table_name ADD UNIQUE (column);3普通索引——Index index_name(column) 定义MySQL中基本索引类型没有什么限制允许在定义索引的列中插入重复值和NULL值。一个表允许多个列创建普通索引 举例 ALTER TABLE table_name ADD INDEX index_name (column);4全文索引——Fulltext(column) 定义主要是为了快速检索大文本数据中的关键字的信息。字段长度比较大时如果创建普通索引在进行like模糊查询时效率比较低这时可以创建全文索引基于倒排索引类似于搜索引擎。MyISAM存储引擎支持全文索引InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引 举例 ALTER TABLE table_name ADD FULLTEXT (column);5前缀索引——Key(column_name(prefix_length)) 定义在文本类型如BLOB、TEXT或者很长的VARCHAR列上创建索引时可以使用前缀索引数据量相比普通索引更小可以指定索引列的长度但是数值类型不能指定。 举例 ALTER TABLE table_name ADD KEY(column_name(prefix_length));6组合索引——Index index_name(column1,column2) 定义指多个字段上创建的索引只有在查询条件中使用了创建索引时的第一个字段索引才会被使用。使用组合索引时遵循最左前缀原则。 主键索引、普通索引、唯一索引等都可以使用多个字段形成组合索引 举例 ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )7空间索引
定义MySQL在5.7之后的版本支持了空间索引而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
3.3.2按底层数据结构区分
在前面已经介绍过了MySQL索引默认的底层数据结构为B树还要以下相关索引的特点
它是一棵 BTree每一个 BTree 的节点都是一个「数据页」索引是在存储引擎层实现的所以并没有统一的索引标准即不同存储引擎的索引的工作方式并不一样
1聚簇索引非聚簇索引二级索引
聚簇索引 将数据存储与索引放到了一块索引结构的叶子节点保存了行数据所有字段的值。有且仅有一个规则 依次选择主键索引、唯一索引作为聚簇索引InnoDB自动生成一个rowid作为隐藏的聚簇索引 非聚簇索引 将数据与索引分开存储索引结构的叶子节点指向了数据对应的位置可以存在多个
3.3.3InnDB存储引擎的两种索引
在InnoDB存储引擎中主键索引和辅助索引的索引类型分别为聚簇索引和非聚簇索引
1InnoDB——主键索引聚簇索引 2InnDO——辅助索引非聚簇索引二级索引 3主键搜索过程 整个查询的过程如下
查询 id主键 为 18 的数据SELECT id, name, age WHERE id 18。首先在「根节点节点一」上id 18 落在了 15 id 56 范围之内这样我们就知道了下级节点「非叶子节点节点2-1」的地址。根据【步骤2】得到的「非叶子节点节点2-1」的地址找到对应的「非叶子节点节点2-1」。然后id 18 又落在了 15 id 20 范围之内这样我们就知道了再下一级节点「叶子节点节点3-1」的地址。根据【步骤3】得到的「叶子节点节点3-1」的地址找到对应的「叶子节点节点3-1」。最后在「叶子节点节点3-1」这个节点上找到 id 18 对应的数据 {“id”: 18, “name”: “King”, “age”: 17}
3.3.4MyISAM的索引
在存储引擎为 MyISAM 的表中主键索引和辅助索引的类型都是非聚簇索引。两棵 BTree 的结构完全一致只是存储的内容不同 主键索引 BTree 的节点存储了 「主键」「数据记录的地址」辅助键索引 BTree 存储了 「索引列的值」「数据记录的地址」。 还有一点不同是主键索引中的 key 必须是唯一的而辅助索引中的 key 可以重复。
1MyISAM——主键索引非聚簇索引 2MyISAM-辅助索引非聚簇索引 3.4相关概念回表/索引覆盖/索引下推
3.4.1回表
定义通过二级索引找到对应的主键值再到聚簇索引中查找整行数据的过程
主要发生在通过辅助索引查询的时候通过辅助索引找到 BTree 中的叶子结点但是辅助索引的叶子节点内存储的数据不全只有索引列的值和主键值。我们还需要拿着刚从辅助索引中得到的主键值再去聚簇索引主键索引的叶子节点中去拿到完整的数据全部字段这个过程就叫「回表」。
3.4.2索引覆盖 定义指第一次使用了索引并且找到了全部所有需要返回的数据 例子 使用id查询直接使用聚簇索引返回数据如果返回的列在索引存储的数据没有找到就会触发回表查询 结论避免使用select * 应用解决MySQL超大分页 原 select * from tb_sku limit 900000,10改通过子查询覆盖索引解决在子查询中先通过按 id 排序选择第 9000000 到第 9000009 行的数据并拿到id再通过id由覆盖索引查到详细数据 select *
from tb_sku t(select id from tb_sku order by id limit 9000000,10) a
where t.ida3.4.3索引下推
索引下推严格来说应该叫「索引条件下推」。该功能是 MySQL 数据库 5.6 版本添加的用于优化数据查询默认情况处于开启状态。我们可以通过如下命令来开启和关闭「索引条件下推」功能
开启
SET optimizer_switch index_condition_pushdownon关闭
SET optimizer_switch index_condition_pushdownoff下面通过一个例子来说下什么是「索引条件下推」首先我们假设有这么一张表
表名t_user字段idnamemobile辅助索引name mobile索引名name_mobile_normal
表结构如下
CREATE TABLE t_user (id int(11) NOT NULL AUTO_INCREMENT COMMENT 编号,name varchar(100) COLLATE utf8_bin NOT NULL COMMENT 姓名,mobile varchar(200) COLLATE utf8_bin NOT NULL COMMENT 手机,PRIMARY KEY (id) USING BTREE,KEY name_mobile_normal (name,mobile) USING BTREE
) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8 COLLATEutf8_bin COMMENT用户表;下面让我们来分别看下关闭和开启「索引条件下推」这两种情况下边这个查询语句的执行计划有怎样的不同。
EXPLAIN SELECT * FROM t_user WHERE name A AND mobile LIKE %138关闭「索引条件下推」 开启「索引条件下推」 从上边我们可以看到关闭「索引条件下推」时候 Extra 是 Using where开启「索引条件下推」时候 Extra 是 Using index condition。
当关闭「索引条件下推」的时候MySQL 首先通过索引将匹配 name ‘A’ 的数据都查出来在存储引擎中完成然后再从这些数据中找到与 mobile LIKE ‘%138’ 条件相匹配的数据在 Server 层完成。
当开启「索引条件下推」的时候因为 name 和 mobile 对应的值已经都存储在索引中了也就是说我们可以直接拿到这个值来判断数据是否与查询条件匹配这样就可以直接在存储引擎中完成数据的查询了。
综上所述索引条件下推就是 “过滤的动作 尽量由 下层的存储引擎层 通过 使用索引 来完成而不需要上推到 Server 层进行处理” 实现的主要思路是充分利用索引中存储的数据尽量把根据条件过滤数据的操作交给存储引擎来做这样可以最大限度的减少需要「回表」的数据。
最后还需要注意的是由于该技术基于存储引擎只有特定的存储引擎可以使用。而且条件判断操作必须可以在存储引擎运行比如调用存储过程的条件就不可以因为存储引擎没有调用存储过程的能力。
3.5参考文章链接
MySQL中的 索引、聚簇索引、非聚簇索引、回表、索引覆盖、索引下推 都是啥
3.6总结
MySQL系列第三篇主要介绍了MySQL索引的定义深入理解索引的底层数据结果B树还介绍了其他树的特点最后按功能逻辑和按底层数据结构两种分类方法将索引分别分类其中重点为聚簇索引和非聚簇索引我们需要理解并记住两种索引的区别最后最后介绍了InnoDB和MyiSAM两种引擎下主键索引和辅助索引对应聚簇索引还是非聚簇索引还介绍了回表查询、覆盖索引、以及索引下推等相关概念
