MySQL高级之索引

最新推荐文章于 2023-07-05 10:28:48 发布
最新推荐文章于 2023-07-05 10:28:48 发布
阅读量141 收藏 1
点赞数
分类专栏： MySQL 文章标签： MySQL高级之索引 索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Yearingforthefuture/article/details/117969426
版权

1. 索引概述

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下面的示意图所示 :
在这里插入图片描述
总的来说：

  • 索引是一种数据结构
  • 索引的作用是高效的获取数据

2. 索引怎么高效获取数据

在这里插入图片描述

没有索引的情况

  • 数据从上往下一个个进行查找
  • 在数据量大的时候，造成查询速度慢，效率很慢

在这里插入图片描述

有索引的情况

  • 索引是一种数据结构，采用一种数据结构来表示，二叉树，根据二叉树可以快速查找

3. 索引的优劣势

3.1 优势

1） 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。

2） 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

3.2 劣势

1） 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。

2） 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

4. 索引结构

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

  • BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。（默认索引）
  • HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
  • R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
  • Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。
MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持
索引InnoDB引擎MyISAM引擎Memory引擎
BTREE索引支持支持支持
HASH 索引不支持不支持支持
R-tree 索引不支持支持不支持
Full-text5.6版本之后支持支持不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。

4.1 BTREE 结构

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：

  • 树中每个节点最多包含m个孩子。
  • 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。
  • 若根节点不是叶子节点，则至少有两个孩子。
  • 所有的叶子节点都在同一层。
  • 每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1

以5叉BTree为例，key的数量：公式推导[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4 。当n>4时，中间节点分裂到父节点，两边节点分裂。

插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。
按字母的顺序进行插入
演变过程如下：

1). 插入前4个字母 C N G A
在这里插入图片描述
2). 插入H，n>4，中间元素G字母向上分裂到新的节点
在这里插入图片描述
3). 插入E，K，Q不需要分裂
在这里插入图片描述
4). 插入M，中间元素M字母向上分裂到父节点G

在这里插入图片描述
5). 插入F，W，L，T不需要分裂
在这里插入图片描述
6). 插入Z，中间元素T向上分裂到父节点中
在这里插入图片描述
7). 插入D，中间元素D向上分裂到父节点中。然后插入P，R，X，Y不需要分裂
在这里插入图片描述
8). 最后插入S，NPQR节点n>5，中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂
在这里插入图片描述
到此，该BTREE树就已经构建完成了， BTREE树 和 二叉树 相比， 查询数据的效率更高， 因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。

4.2 B+TREE 结构

B+Tree为BTree的变种，B+Tree与BTree的区别为：

  • n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。

  • B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。

  • 所有的非叶子节点都可以看作是key的索引部分。

在这里插入图片描述
由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。

4.3 MySQL中的B+Tree

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

MySQL中的 B+Tree 索引结构示意图:
在这里插入图片描述

5. 索引分类

1） 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引

2） 唯一索引 ：索引列的值必须唯一，但允许有空值

3） 复合索引 ：即一个索引包含多个列

6. 索引语法

主键是默认有主键索引，一般不需要创建。
索引在创建表的时候，可以同时创建， 也可以随时增加新的索引。
准备环境:

create database demo_01 default charset=utf8mb4;

use demo_01;

CREATE TABLE `city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(50) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `country` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);

insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');

6.1 创建索引

语法 ：

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)
index_col_name : column_name[(length)][ASC | DESC]

示例 ： 为city表中的city_name字段创建索引 ；
在这里插入图片描述

6.2 查看索引

语法：

show index  from  table_name;

示例：查看city表中的索引信息；
在这里插入图片描述
在这里插入图片描述

6.3 删除索引

语法 ：

DROP  INDEX  index_name  ON  tbl_name;

示例 ： 想要删除city表上的索引idx_city_name，可以操作如下：
在这里插入图片描述

6.4 ALTER命令

1). alter  table  tb_name  add  primary  key(column_list); 

该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL

2). alter  table  tb_name  add  unique index_name(column_list);

这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）

3). alter  table  tb_name  add  index index_name(column_list);

添加普通索引， 索引值可以出现多次。

4). alter  table  tb_name  add  fulltext  index_name(column_list);

该语句指定了索引为FULLTEXT， 用于全文索引

7. 索引的设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

  • 对查询频次较高，且数据量比较大的表建立索引。

  • 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

  • 使用唯一索引，区分度越高，使用索引的效率越高。

  • 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。

  • 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

  • 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

创建复合索引:

CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相当于
对name 创建索引 ;
对name , email 创建了索引 ;
对name , email, status 创建了索引 ;

8. 一张表最多能建多少个索引

innoDB: 最多创建1017列, 最多64个二级索引, 单个索引最多包含16列, 索引最大长度767字节(其实行格式为REDUNDANT,COMPACT最高为767字节,但行格式为DYNAMIC,COMPRESSED最高可达为3072字节), 行大小最大65536字节
mysiam: 最多4096列, 最多64个二级索引, 单个索引最多包含16列, 索引最大长度1000字节, 行大小最大65536字节

内容参考：http://yun.itheima.com/course/579.html?capid=1
仅用于学习！

Java追求者
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySql高级使用
星韵月光的博客
05-19 661
MySQL高级索引什么是索引索引优势劣势索引的结构btree 结构B+TREE 结构MySQL中的B+Tree索引分类索引语法创建索引查看索引索引设计原则视图视图概述创建或者修改视图存储过程和函数创建存储过程调用存储过程查看存储过程删除存储过程语法变量if条件判断传递参数case结构 MySQL高级使用主要有以下几个方面，索引，视图，存储过程、触发器，sql优化等大体内容今天主要说的是索引，视图，存储过程这三个方面。 索引 什么是索引 百度百科对其的定义是： 索引是一种单独的、物理的对数据库表中一列或多列的
尚硅谷mysql高级：索引、优化
08-12
在"尚硅谷mysql高级：索引、优化"这个主题中，我们主要关注两个关键概念：索引和优化，这对于提升数据库性能至关重要。 一、MySQL索引 1. 索引类型：MySQL支持多种索引类型，包括B-Tree（默认索引类型）、Hash、...
BTree和B+Tree详解
我有故人折剑去，斩尽春风不曾归
06-14 5961
B+树索引是B+树在数据库中的一种实现，是最常见也是数据库中使用最为频繁的一种索引。B+树中的B代表平衡(balance)，而不是二叉(binary)，因为B+树是从最早的平衡二叉树演化而来的。在讲B+树之前必须先了解二叉查找树、平衡二叉树(AVLTree)和平衡多路查找树(B-Tree)，B+树即由这些树逐步优化而来。
MySQL数据库索引机制
写博客记录分享学习笔记
07-05 1078
本文介绍了MySQL的索引机制
MySQL高级语句运用
A_Xiao_CaiJI的博客
11-08 1079
通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。
MySQL高级语句的用法
System_TuTu
06-17 1132
select查询sql语句的用法 查询不重复记录 where 有条件的查询 and 且 ； or 或 通配符通常是和 like 一起使用
Mysql高级用法
weixin_42292422的博客
12-17 266
DATAbASE
MYSQL高级用法
black_zt666的博客
11-09 402
高级查询 1.in的用法 select * from member where id in(1,2,3); 2.模糊查询like的用法 3.统计count()的用法 4.去重distinct的用法 5.between的用法 6.分组group by的用法 7.mysql分页 -- 1：掌握in的用法 -- 在。。。里面 -- 字段 in(数据集) -- 查询用户id为1，或者为2，或者为3的用户 -- select * from member where id in(1,2,3..
mysql，innodb索引介绍
05-06
`optimizer_trace`是MySQL 5.6版本后引入的一个高级特性，允许用户获取查询优化器的详细执行计划和成本估算，这对于优化复杂SQL语句非常有价值。然而，由于启用`optimizer_trace`可能会对性能产生约20%的影响，所以...
mysql高级笔记.zip
11-07
"mysql高级笔记.zip"压缩包文件很可能包含了一系列深入探讨MySQL高级特性和最佳实践的教程或文档。从`mysql_advanced-master`这个子文件名来看，我们可以预期这些内容可能组织成一个项目结构，涵盖了多个高级主题。 ...
mysql完整教程
11-03
mysql教程
mysql高级应用思维导图
11-21
mysql高级应用思维导图，很强大哟。 下载了你就知道了
java工程师之MySQL高级
11-27
重点为索引优化事务分库分表等
MySQL高级MySQL高级
最新发布
02-21
在这个“MySQL高级”主题中，我们将深入探讨SQL语句，这是与MySQL交互的基础。 一、SQL基础 SQL（Structured Query Language）是一种用于管理关系数据库的标准语言。在MySQL中，SQL语句主要包括以下类型： 1. **...
Mysql中BTree和B+Tree
Knight
03-24 1979
为啥要使用B+Tree，而不是用普通二叉树和AVL平衡二叉树呢B-Tree树即B树，B即Balanced，平衡的意思。B-Tree即B树，Balance Tree，平衡树。2-3树是最简单的B树结构。B树的阶：节点的最多子节点个数。比如2-3树的阶是3，2-3-4树的阶是4。B树通过重新组织节点，降低树的高度，并且减少IO读写次数来提升效率。关键字集合分布在整颗树中，即叶子节点和非叶子节点都存放数据。
MySQL索引原理及BTree（B-/ Tree）结构详解
uiuan00的专栏
10-23 558
摘要 本文以MySQL数据库为研究对象，讨论与数据库索引相关的一些话题。特别需要说明的是，MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全文索引等等。为了避免混乱，本文将只关注于BTree索引，因为这是平常使用MySQL时主要打交道的索引，至于哈希索引和全文索引本文暂不讨论。 文章主要内容分为三个部分。 第...
Mysql索引的类型（单列索引、组合索引 btree索引 聚簇索引等）
S_ZaiJiangHu的博客
07-16 5560
一、索引的类型 Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。 FULLTEXT 即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。 全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。 HASH 由于HAS
btree索引和hash索引的区别
mr_mablevi的专栏
05-04 1176
在mysql中，大多数索引（如 PRIMARY KEY,UNIQUE,INDEX和FULLTEXT）都是在BTREE中存储，但使用memory引擎可以选择BTREE索引或者HASH索引，两种不同类型的索引各自有其不同的使用范围。 =========以下节选网摘==========Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点
MySQL中级优化教程（六）——表的范式化和反范式化
热门推荐
待到春花烂漫时
06-16 1万+
您可以到这里下载本篇博文所使用的数据库以及优化工具mysqldumpslow和pt-query-digest： https://blog.csdn.net/IT_TIfarmer/article/details/92224646 关于explain语句的结果参数详解也可以参考如上博文。 前言： 范式化是指数据库设计的规范，目前说范式化，一般是指第三设计范式，也就...
mysql高级篇索引优化
09-06
MySQL高级篇索引优化主要涉及MySQL Query Optimizer（查询优化器）和索引的使用。MySQL Query Optimizer是MySQL中负责优化SELECT语句的模块，通过计算分析系统中收集到的统计信息，为客户端请求的Query提供最优的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值