MySQL 高级(2): 索引的概念,分类,性能分析

索引优化分析

索引的概念

  • MySQL 官方对索引的定义为:索引(Index)是帮助MySQL 高效获取数据的数据结构。 可以得到索引的本质:索引是数据结构。可以简单理解为排好序的快速查找数据结构。
  • 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。 下图就是一种可能的索引方式示例:
    在这里插入图片描述
    左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址。为了加快 Col2 的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指 针
    • 这样就可以运用二叉查找在一定的复杂度内获取到相应数据,从而快速的检索出符合条件的记录。
    • 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。

优缺点
优势:

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

劣势:

  • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
  • 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的。

索引的结构

我们平时所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然,除了B+树这种数据结构的索引之外,还有哈希索引(Hash Index)等。

Btree 查找树

概述

  • Btree又名 B- 树,是一种平衡的多路查找树,它可以利用多个分支节点(子树节点)来减少查询数据时所经历的节点数,从而达到节省存取时间的目的。
    下图是一个M=4 阶的B树:
    在这里插入图片描述

主要特点

  • 每个节点的key数量小于m个(与m-way相同)
  • 除根节点和叶子节点的其他节点存储key的个数必须大于等于m/2
  • 所有叶子节点都处于同一层,也就是说所有叶节点具有相同的深度h(树的高度,也意味着树是平衡的)

Btree的查找

  • 必须从根节点开始采用二分法查找,所以时间复杂度为O(logn)。

Btree的插入

  • 为了保证树的平衡,如果带插入节点的key数量小于m-1个,则直接插入(不会违反第一条特性),否则,需要将该节点分为两部分,再执行该操作。
    ·在这里插入图片描述
  • 下面是往B树中依次插入6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4 的演示动画:
    在这里插入图片描述

B+Tree 索引

概述

  • 其实B+树与B-树相差并不大,由于 B- 树可能会从最高级的根节点查询到最低级的叶子节点。那我们可以从叶子节点开始查啊,这就产生了B+树结构。
  • 在每一个叶子节点做一个标记,把这些标记存起来,每次查的时候可以在查询根节点的时候从叶子节点也开始查。
  • 示意图如下:
    在这里插入图片描述
    也可以用下面形式表示:
    在这里插入图片描述

下图是B+树的建立过程:

  • 依次插入6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4 的演示动画:
    在这里插入图片描述

B+ 树与 B- 树的区别

  • B- 树的关键字和记录是放在一起的,叶子节点可以看作外部节点,不包含任何信息;B+ 树的非叶子节点中只有关键字和指向下一个节点的索引,记录只放在叶子节点中。
  • 在B-树中,越靠近根节点的记录查找时间越快,只要找到关键字即可确定记录的存在;而B+树中每个记录的查找时间基本是一样的,都需要从根节点走到叶子节点,而且在叶子节点中还要再比较关键字

B+ 树的优势

  • B+树的磁盘读写代价更低:而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据,这样每个节点可容纳的元素个数比B-树多,树高比B-树小,这样带来的好处是减少磁盘访问次数。 尽管B+树找到一个记录所需的比较次数要比B-树多,但是一次磁盘访问的时间相当于成百上千次内存比较的时间,因此实际中B+树的性能可能还会好些。
  • B+树的查询效率更加稳定: B+树的叶子节点使用指针连接在一起,方便顺序遍历(例如查看一个目录下的所有文件,一个表中的所有记录等),这也是很多数据库和文件系统使用B+树的缘故。

聚簇索引和非聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。术语 ‘聚簇’表示数据行和相邻的键值聚簇的存储在一起

  • 如下图,左侧的索引就是聚簇索引,因为数据行在磁盘的排列和索引排序保持一致。
    在这里插入图片描述

聚簇索引的好处:

  • 按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不不用从多个数据块中提取数据,所以节省了大量的 io 操作。

聚簇索引的限制:

  • 对于 mysql 数据库目前只有 innodb 数据引擎支持聚簇索引,而 Myisam 并不支持聚簇索引。
  • 由于数据物理存储排序方式只能有一种,所以每个 Mysql 的表只能有一个聚簇索引。一般情况下就是该表的主键。
  • 为了充分利用聚簇索引的聚簇的特性,所以 innodb 表的主键列尽量选用有序的顺序 id,而不建议用无序的 id,比如 uuid 这种。

时间复杂度(扩展)

同一问题可用不同算法解决,而一个算法的质量优劣将影响到算法乃至程序的效率。算法分析的目的在于选择合适算法和改进算法。

  • 时间复杂度是指执行算法所需要的计算工作量,用大 O 表示记为:O(…)
    在这里插入图片描述

索引的分类

单值索引

概念: 即一个索引只包含单个列,一个表可以有多个单列索引。
语法:

  • 和表一起创建:

    CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name
    VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name)
    );
    
  • 单独建表索引:

    CREATE INDEX idx_customer_name ON customer(customer_name);
    

唯一索引

概念: 索引列的值必须唯一,但允许有空值。

  • 和表一起创建

    CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name
    VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name),
    UNIQUE (customer_no)
    );
    
  • 单独建唯一索引:

    CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
    

主键索引

概念: 设定为主键后数据库会自动建立索引,innodb 为聚簇索引。

  • 随表创建

    CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name
    VARCHAR(200),
    PRIMARY KEY(id)
    );
    
  • 单独创建

    ALTER TABLE customer add PRIMARY KEY customer(customer_no);
    
  • 删除建主键索引:

    ALTER TABLE customer drop PRIMARY KEY ;
    
  • 修改建主键索引:

    必须先删除掉(drop)原索引,再新建(add)索引
    

复合索引

概念: 即一个索引包含多个列

  • 随表一起建立

    CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name
    VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name),
    UNIQUE (customer_name),
    KEY (customer_no,customer_name)
    );
    
  • 单独建索引:

    CREATE INDEX idx_no_name ON customer(customer_no,customer_name);
    

基本语法

在这里插入图片描述

索引的创建时机

适合创建的情况

  • 主键自动建立唯一索引;
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其它表关联的字段,外键关系建立索引
  • 单键/组合索引的选择问题, 组合索引性价比更高
  • 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
  • 查询中统计或者分组字段

不适合创建的情况

  • 表记录太少
  • 经常增删改的表或者字段
  • Where 条件里用不到的字段不创建索引
  • 过滤性不好的不适合建索引

Explain 性能分析

概念

使用 EXPLAIN 关键字 可以模拟优化器执行SQL 查询语句,从而知道MySQL 是如何处理你的SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。
用法: Explain+SQL 语句。
Explain 执行后返回的信息:
在这里插入图片描述

实例

首先创建 t1,t2,t3,t4 等 四张表,且插入一些数据:

```sql
CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));
```

1、id

select 查询的序列号,包含一组数字,表示查询中执行 select 子句或操作表的顺序。

  • id 号每个号码,表示一趟独立的查询。一个sql 的查询趟数越少越好。
  • ①id 相同,执行顺序由上至下
    在这里插入图片描述
  • ②id 不同,id 不同,如果是子查询,id 的序号会递增,id 值越大优先级越高,越先被执行
    在这里插入图片描述
  • ③有相同也有不同
    在这里插入图片描述
    id 如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id 值越大,优先级越高,越先执行衍生= DERIVED

2、select_type

  • select_type 代表查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询。
    在这里插入图片描述

3、table

  • 这个数据是基于哪张表的。

4、type

  • type 是查询的访问类型。是较为重要的一个指标,结果值从最好到最坏依次是:
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge> unique_subquery > index_subquery > range > index >ALL,一般来说,得保证查询至少达到 range 级别,最好能达到ref。

在这里插入图片描述
具体描述如下:

类型描述
system表只有一行记录(等于系统表),这是 const 类型的特列,平时不会出现,这个也可以忽略不计
const表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据,所以很快。如将主键置于 where 列表中,MySQL 就能将该查询转换为一个常量。
eq_ref唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
ref非唯一性索引扫描,返回匹配某个单独值的所有行.本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。
range只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引
index出现index是sql使用了索引但是没用通过索引进行过滤,一般是使用了覆盖索引或者是利用索引进行了排序分组。
allFull Table Scan,将遍历全表以找到匹配的行。
index_merge在查询过程中需要多个索引组合使用,通常出现在有 or 的关键字的 sql 中。
ref_or_null对于某个字段既需要关联条件,也需要 null 值得情况下。查询优化器会选择用 ref_or_null 连接查询。
index_subquery利用索引来关联子查询,不再全表扫描。
unique_subquery该联接类型类似于 index_subquery。 子查询中的唯一索引

5、possible_keys

  • 显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。

6、key

  • 实际使用的索引。如果为NULL,则没有使用索引。

7、key_len

  • 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长,说明索引使用的越充分
    在这里插入图片描述
    在这里插入图片描述
    计算方法如下:
    • ①先看索引上字段的类型+长度比如int=4 ; varchar(20) =20 ; char(20) =20
    • ②如果是varchar 或者char 这种字符串字段,视字符集要乘不同的值,比如utf-8 要乘3,GBK 要乘2,
    • ③varchar 这种动态字符串要加2 个字节
    • ④允许为空的字段要加1 个字节
      第一组:key_len=age 的字节长度+name 的字节长度=4+1 + ( 20*3+2)=5+62=67
      第二组:key_len=age 的字节长度=4+1=5
    • 通过上面的结果可以得到结论 xxx% 走索引;但是 %xxx% 不走索引

8、ref:

  • 显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。
    在这里插入图片描述

9、rows

  • rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好!
    在这里插入图片描述

10、Extra

其他的额外重要的信息。

信息类型描述
Using filesort说明 mysql 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。
Using temporary使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
Using index代表表示相应的 select 操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!如果同时出现 using where,表明索引被用来执行索引键值的查找;如果没有同时出现 using where,表明索引只是用来读取数据而非利用索引执行查找
Using where表明使用了 where 过滤。
Using join buffer使用了连接缓存。
impossible wherewhere 子句的值总是 false,不能用来获取任何元组。(即后面的条件没有数据满足)
select tables optimized away在没有 GROUPBY 子句的情况下,基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。

热门文章

暂无图片
编程学习 ·

C语言二分查找详解

二分查找是一种知名度很高的查找算法&#xff0c;在对有序数列进行查找时效率远高于传统的顺序查找。 下面这张动图对比了二者的效率差距。 二分查找的基本思想就是通过把目标数和当前数列的中间数进行比较&#xff0c;从而确定目标数是在中间数的左边还是右边&#xff0c;将查…
暂无图片
编程学习 ·

GMX 命令分类列表

建模和计算操作命令&#xff1a; 1.1 . 创建拓扑与坐标文件 gmx editconf - 编辑模拟盒子以及写入子组(subgroups) gmx protonate - 结构质子化 gmx x2top - 根据坐标生成原始拓扑文件 gmx solvate - 体系溶剂化 gmx insert-molecules - 将分子插入已有空位 gmx genconf - 增加…
暂无图片
编程学习 ·

一文高效回顾研究生课程《数值分析》重点

数值分析这门课的本质就是用离散的已知点去估计整体&#xff0c;就是由黑盒子产生的结果去估计这个黑盒子。在数学里这个黑盒子就是一个函数嘛&#xff0c;这门课会介绍许多方法去利用离散点最大化地逼近这个函数&#xff0c;甚至它的导数、积分&#xff0c;甚至微分方程的解。…
暂无图片
编程学习 ·

在职阿里5年,一个28岁女软测工程师的心声

简单的先说一下&#xff0c;坐标杭州&#xff0c;14届本科毕业&#xff0c;算上年前在阿里巴巴的面试&#xff0c;一共有面试了有6家公司&#xff08;因为不想请假&#xff0c;因此只是每个晚上去其他公司面试&#xff0c;所以面试的公司比较少&#xff09; ​ 编辑切换为居中…
暂无图片
编程学习 ·

字符串左旋c语言

目录 题目&#xff1a; 解题思路&#xff1a; 第一步&#xff1a; 第二步&#xff1a; 第三步&#xff1a; 总代码&#xff1a; 题目&#xff1a; 实现一个函数&#xff0c;可以左旋字符串中的k个字符。 例如&#xff1a; ABCD左旋一个字符得到BCDA ABCD左旋两个字符…
暂无图片
编程学习 ·

设计模式--观察者模式笔记

模式的定义与特点 观察者&#xff08;Observer&#xff09;模式的定义&#xff1a;指多个对象间存在一对多的依赖关系&#xff0c;当一个对象的状态发生改变时&#xff0c;所有依赖于它的对象都得到通知并被自动更新。这种模式有时又称作发布-订阅模式、模型-视图模式&#xf…
暂无图片
编程学习 ·

睡觉突然身体动不了,什么是睡眠痽痪症

很多朋友可能有这样的体验&#xff0c;睡觉过程中突然意识清醒&#xff0c;身体却动弹不了。这时候感觉非常恐怖&#xff0c;希望旁边有一个人推自己一下。阳光以前也经常会碰到这样的情况&#xff0c;一年有一百多次&#xff0c;那时候很害怕晚上到来&#xff0c;睡觉了就会出…
暂无图片
编程学习 ·

深入理解C++智能指针——浅析MSVC源码

文章目录unique_ptrshared_ptr 与 weak_ptrstd::bad_weak_ptr 异常std::enable_shared_from_thisunique_ptr unique_ptr 是一个只移型别&#xff08;move-only type&#xff0c;只移型别还有std::mutex等&#xff09;。 结合一下工厂模式&#xff0c;看看其基本用法&#xff…
暂无图片
编程学习 ·

@TableField(exist = false)

TableField(exist false) //申明此字段不在数据库存在&#xff0c;但代码中需要用到它&#xff0c;通知Mybatis-plus在做写库操作是忽略它。,.
暂无图片
编程学习 ·

Java Web day15

第十二章文件上传和下载 一、如何实现文件上传 要实现Web开发中的文件上传功能&#xff0c;通常需要完成两步操作&#xff1a;一.是在Web页面中添加上传输入项&#xff1b;二是在Servlet中读取上传文件的数据&#xff0c;并保存到本地硬盘中。 需要使用一个Apache组织提供一个…
暂无图片
编程学习 ·

【51nod 2478】【单调栈】【前缀和】小b接水

小b接水题目解题思路Code51nod 2478 小b接水 题目 输入样例 12 0 1 0 2 1 0 1 3 2 1 2 1输出样例 6解题思路 可以发现最后能拦住水的都是向两边递减高度&#xff08;&#xff1f;&#xff09; 不管两个高积木之间的的积木是怎样乱七八糟的高度&#xff0c;最后能用来装水的…
暂无图片
编程学习 ·

花了大半天写了一个UVC扩展单元调试工具

基于DIRECTSHOW 实现的&#xff0c;用的是MFC VS2019. 详见&#xff1a;http://www.usbzh.com/article/detail-761.html 获取方法 加QQ群:952873936&#xff0c;然后在群文件\USB调试工具&测试软件\UVCXU-V1.0(UVC扩展单元调试工具-USB中文网官方版).exe USB中文网 USB中文…
暂无图片
编程学习 ·

贪心(一):区间问题、Huffman树

区间问题 例题一&#xff1a;区间选点 给定 N 个闭区间 [ai,bi]请你在数轴上选择尽量少的点&#xff0c;使得每个区间内至少包含一个选出的点。 输出选择的点的最小数量。 位于区间端点上的点也算作区间内。 输入格式 第一行包含整数 N&#xff0c;表示区间数。 接下来 …
暂无图片
编程学习 ·

C语言练习实例——费氏数列

目录 题目 解法 输出结果 题目 Fibonacci为1200年代的欧洲数学家&#xff0c;在他的着作中曾经提到&#xff1a;「若有一只免子每个月生一只小免子&#xff0c;一个月后小免子也开始生产。起初只有一只免子&#xff0c;一个月后就有两只免子&#xff0c;二个月后有三只免子…
暂无图片
编程学习 ·

Android开发(2): Android 资源

个人笔记整理 Android 资源 Android中的资源&#xff0c;一般分为两类&#xff1a; 系统内置资源&#xff1a;Android SDK中所提供的已经定义好的资源&#xff0c;用户可以直接拿来使用。 用户自定义资源&#xff1a;用户自己定义或引入的&#xff0c;只适用于当前应用的资源…
暂无图片
编程学习 ·

零基础如何在短时间内拿到算法offer

​算法工程师是利用算法处理事物的职业 算法&#xff08;Algorithm&#xff09;是一系列解决问题的清晰指令&#xff0c;也就是说&#xff0c;能够对一定规范的输入&#xff0c;在有限时间内获得所要求的输出。 如果一个算法有缺陷&#xff0c;或不适合于某个问题&#xff0c;执…
暂无图片
编程学习 ·

人工智能:知识图谱实战总结

人工智能python&#xff0c;NLP&#xff0c;知识图谱&#xff0c;机器学习&#xff0c;深度学习人工智能&#xff1a;知识图谱实战前言一、实体建模工具Protegepython&#xff0c;NLP&#xff0c;知识图谱&#xff0c;机器学习&#xff0c;深度学习 人工智能&#xff1a;知识图…
暂无图片
编程学习 ·

【无标题】

这里写自定义目录标题欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题&#xff0c;有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注…