数据库进阶教学——索引

恣睢s

已于 2023-11-15 19:44:40 修改

阅读量83

点赞数 1

分类专栏：数据库文章标签：数据库

于 2023-11-15 19:43:33 首次发布

本文链接：https://blog.csdn.net/weixin_45100742/article/details/134360479

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、索引概述

1、介绍

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

2、演示

无索引
- 假设，要从下表中查找age=45的人。
  - 数据库会从头开始遍历这张表，直到扫描完毕，找出所有age=45的人。（全表扫描）
有索引
- 以二叉树为例，构建一个索引，继续查找age=45的人。
  - 数据库只需要进行二叉树查找，找到age=45结点指向的数据即可。（只需要查找三次）
- 【注】上述二叉树索引结构的只是一个示意图，并不是真实的索引结构。

3、优缺点

优点	缺点
提高数据检索的效率，降低数据库的IO成本。	索引列也是要占用空间的。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

二、索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：
- B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引。
- Hash索引：底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效不支持范围查询。
- R-tree(空间索引)：空间索引是MylSAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
- Full-text(全文索引)：是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene,Solr,ES。

1、B+树

以一颗最大度数为4（4阶）的B+树为例：
特点：
- 非叶子结点只起索引作用，叶子结点才是用来存放数据的。
- 叶子结点形成了一个单向链表。
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
使用B+树的优势：
- 相对于二叉树，层级更少，搜索效率高。
- 对于B树，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
- 相对Hash索引，B+树支持范围匹配及排序操作。

2、Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。
特点：
- Hash索引只能用于对等比较（=），不支持范围查询（<、>）。
- 无法利用索引完成排序操作。
- 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引。

三、索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

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

分类	含义	特点
聚集索引	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

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

聚集索引和二级索引
聚集索引和二级索引如何进行查询？
- 先在二级索引查询到Arm的id，再在聚集索引中利用id查询到其所有信息。
- 利用id查询比利用name查询速度更快，因为name查询需要回表查询。

四、索引语法

1、语法

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name(index_col_name, ... );
/* UNIQUE表示唯一索引，FULLTEXT表示全文索引，不加表示创建一个常规索引 */
/* index_name索引名 */
/* table_name表名 */
/* index_col_name关联的字段名 */

查看索引

SHOW INDEX FROM table name;
或
SHOW INDEX FROM table name\G;  /* 按列展示 */

删除索引
- ```
DROP INDEX index_name ON table_name;
```

2、示例

现有一张数据表account。
查看account表中的索引。
- 或
为account表中的name字段创建索引。（常规索引）
- 再次查看索引。
为account表中的phone字段创建唯一索引。
- 再次查看索引。
删除索引。

五、SQL性能分析

数据库主要针对select查询语句进行优化，所以索引的优化占据了主导地位。下面讲解了四种SQL语句性能分析的工具。

1、SQL执行频率

MySQL客户端连接成功后，通过show [session | global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次。
- ```
SHOW GLOBAL STATUS LIKE 'Com_______';  /* 7个下划线 */
```
- 删除、插入、查询和更新的次数是多少。
当一个数据库是以查询为主时，就需要进行优化了。

2、慢查询日志

具体需要针对哪些select语句进行优化，可以利用数据库的慢查询日志来定位执行效率低的SQL语句。
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启，需要手动开启。

/* 开启慢日志 */
SET GLOBAL slow_query_log = ON;
/* 设置超时时间 */
SET GLOBAL long_query_time = 0.001;

查看慢查询日志是否开启，和存放慢日志的文件。
- ```
show variables like 'slow_query%';
```
查看所设置的超时时间。
- ```
show variables like 'long_query%';
```
当查询时间超过所设定的时间时，慢查询日志会记录本次查询操作。
- 一般日志文件存放在如下位置，可直接以文本形式打开。

3、profile详情

慢查询日志只会记录执行时间超过了预设超时时间的操作。例如，预测超时时间为2s，那么1.8s的操作就不会被记录。
如果一个数据库比较简单，其操作大多数都是1.8s左右，那这些操作性能也相对比较低。此时慢查询日志就不太合适了，可以借助profile详情。
show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作。
- ```
select @@have_profiling;
```
默认profiling是关闭的，可以通过set语句在session/global级别开启profiling。
- ```
set profiling = 1;
```
- 查看是否开启。
  - ```
  select @@profiling;
```

执行一系列的SQL的操作，然后通过如下指令查看指令的执行耗时：

/* 查看每一条SQL的耗时基本情况 */
show profiles;
/* 查看指定query_id的SQL语句各个阶段的耗时情况 */
show profile for query query_id;
/* 查看指定query_id的SQL语句CPU的使用情况 */
show profile cpu for query query_id;

查看每条指令的耗时情况。
查看指定query_id的SQL语句各个阶段的耗时情况。
查看指定query_id的SQL语句CPU的使用情况。

4、explain执行计划

前三种工具都是从时间层面来判断SQL语句的执行性能，而能更加精确地分析SQL语句性能的是explain执行计划。
EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
- ```
/* 直接在select语句之前加上关键字explain/desc */
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
```
explain执行计划各字段含义：
- id
  - select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）。
- select_type
  - 表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等。
- type
  - 表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。
- possible_key
  - 显示可能应用在这张表上的索引，一个或多个。
- Key
  - 实际使用的索引，如果为NULL，则没有使用索引。
- Key_len
  - 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
- rows
  - MySOL认为必须要执行查询的行数，在innodB引擎的表中，是一个估计值，可能并不总是准确的。
- filtered
  - 表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。

六、索引使用

在未建立索引之前，执行如下SQL语句，查看SQL的耗时。
- ```
SELECT* FROM 表名 WHERE 字段 = value;
```

针对字段创建索引

create index 索引名 on 表名(字段);

然后再次执行相同的SQL语句，再次查看SOL的耗时。

七、索引设计原则

针对于数据量较大，且查询比较频繁的表建立索引。
针对于常作为查询条件 (where)、排序 (order by)、分组(group by) 操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

恣睢s

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
数据库进阶教学——索引

数据库进阶教学——索引。索引概述：介绍、演示、优缺点；索引结构：B+树、Hash；索引分类；索引语法：语法、示例；SQL性能分析：SQL执行频率、慢查询日志、profile详情、explain执行计划；索引使用；索引设计原则。
复制链接

扫一扫