MySQL-进阶篇-数据库索引

XDIGAS

已于 2022-05-04 15:37:54 修改

阅读量638

点赞数

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

于 2022-05-03 10:19:05 首次发布

本文链接：https://blog.csdn.net/qq_40728285/article/details/124551472

版权

MySql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

笔记翻译出处：黑马视频 MySQL-基础篇 MySQL-进阶篇

一、索引概述

1、介绍

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。在无索引情况下，如果查找数据，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为全表扫描，性能很低。

2、特点

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

二、索引结构【不要和索引弄混】

1、概述

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构

索引结构	描述
B+Tree	最常见的索引类型，大部分引擎都支持
Hash索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree（空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用少
Full-tet（全文索引）	是一种通过建立倒赔索引，快速匹配问文档的方式。类似于Lucene，Sole，ES

不同存储引擎对于索引结构的支持情况。

索引	InnoDB	MyISAM	Memory
B+tree 索引	支持	支持	支持
Hash 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text 索引	5.6版本之后支持	支持	不支持

2、二叉树

如果MySQL的索引结构采用二叉树的数据结构，存在以下缺点

3、B-Tree

B-Tree，B树是一种多叉路平衡查找树，类似于二叉树，B树每个节点可以有多个分支，即多叉。以一颗最大度数（max-degree）为5阶的B-Tree为例，那这个B树每个节点最多存储4个key，5个指针（分支）：

插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 。

然后观察一些数据插入过程中，节点的变化情况。

为什么索引不采用B树？

①：B树只适合随机检索，不支持顺序检索

②：B树在一定程度上也提高了磁盘IO性能，但没有解决遍历效率低下的问题。

③：增删文件时，B树需要重新调整树结构。

4、B+Tree

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例

我们可以看到，两部分：

①：绿色框框起来的部分，是 索引部分 ，仅仅起到索引数据的作用，不存储数据。

②：红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据，

总结：B+树内部节点是不保存数据的，只作索引作用，它的叶子节点才保存数据。

5、 Hash

1). 结构

①：哈希索引就是采用一定的Hash算法，将数据库中的数据换算成Hash值，将数据地址存在Hash表里面；

②：如果两个 ( 或多个 ) 键值，映射到一个相同的槽位上，他们就产生了 hash 冲突（也称为 hash 碰撞），可以通过链表来解决

2). 特点

A. Hash 索引只能用于 对等比较 (= ， in) ，不支持范围查询（ between ， > ， < ， ... ）

B. 无法利用索引完成排序操作

C. 查询效率高，通常 ( 不存在 hash 冲突的情况 ) 只需要一次检索就可以了，效率通常要高于 B+tree 索引

3). 存储引擎支持

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

思考题：为什么InnoDB存储引擎选择使用B+Tree索引结构？

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

B：B-Tree，内部和外部节点都会存储数据，导致一页中存储的键值减少，树高度会增加，导致性能降低。

C：相对Hash索引，B+Tree支持范围匹配及排序操作。

三、索引分类

1、概述

注意事项：

①：索引和索引结构，是一对多的关系；【主键索引可以用B+Tree，也可以用Hash，但是不建议用Hash】

②：索引结构，并不要求数据在列存中具备唯一性，唯一特性不是索引结构的事情

在MySQL数据库，将缩影的具体类型主要分为以下几类：主键、唯一、常规、全文索引
索引分类	索引含义	索引特点	建表字段关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULL-TEXT

2、聚焦索引&二级索引

分类	含义	特点	描述
聚集索引（Clustered Index）	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个	如果存在主键，主键索引就是聚集索引。如果不存在主键，将使用第一个唯一（ UNIQUE ）索引作为聚集索引。如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。
二级索引（Secondary Index）	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个	二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询

思考题：InnoDB主键索引的B+Tree高度为多高呐？

假设 :

一行数据大小为1k ，一页中可以存储 16 行这样的数据。 InnoDB 的指针占用 6 个字节的空间，主键即使为bigint ，占用字节数为 8 。

高度为2：

n * 8 + (n + 1) * 6 = 16*1024 , 算出 n 约为 1170

1171* 16 = 18736

也就是说，如果树的高度为2 ，则可以存储 18000 多条记录。

高度为 3 ：

1171 * 1171 * 16 = 21939856

也就是说，如果树的高度为3 ，则可以存储 2200w 左右的记录。

四、索引语法

1、创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;

2、查看索引

SHOW INDEX FROM table_name ;

3、删除索引

DROP INDEX index_name ON table_name ;

五、SQL性能分析

1、SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT 、 UPDATE 、 DELETE 、 SELECT 的访问频次：

# -- session     是查看当前会话 ; 
# -- global      是查询全局数据 ; 
SHOW GLOBAL STATUS LIKE 'Com_______';
# Com_delete: 删除次数 
# Com_insert: 插入次数
# Com_select: 查询次数
# Com_update: 更新次数

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。如果是以增删改为主，我们可以考虑不对其进行索引的优化。如果是以查询为主，那么就要考虑对数据库的索引进行优化了

2、慢查询日志

慢查询日志记录了所有执行时间超过指定参数（ long_query_time ，单位：秒，默认 10 秒）的所有SQL语句的日志。

MySQL 的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log【show variables like 'slow_query_log'】。

如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关 
  slow_query_log=1 
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志 
  long_query_time=2

3、profile详情

show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。通过 have_profiling参数，能够看到当前MySQL 是否支持 profile 操作：

# 查询开关是否开着
  SELECT @@have_profiling ;
# 开启开关语句
  SET profiling = 1;

  SELECT * FROM DGS
  SELECT * FROM DGS WHERE ID = 'DDD'

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

4、explain / desc

EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

语法：

# 直接在select语句之前加上关键字 
# explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
  EXPLAIN SELECT * FROM DGS

Explain 执行计划中各个字段的含义：

字段	含义
id	select 查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）
select_type	表示 SELECT 的类型,常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUEERY（SELECT/WHERE之后包含了子查询）等
type	表示连接类型，性能由好到差的连接类型为NULL，system、const、eq_ref、ref、range、index、all。
possible_key	显示可能应用在这张表上的索引，一个或多个。
key	实际使用的索引，如果为NULL，则没有索引
key_len	表示索引中使用的字节数，该键为索引字段最大可能长度，并非实际使用长度，在不损失精确度的前提下，长度越短越好。
rows	MySQL认为必须要执行查询的行数，在Innodb引擎的表中，是一个估计值，可能并不总是精确的。
filtered	表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好

5、索引使用

1). 最左前缀法则

①：概述

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

②：简述

创建索引SQL如下：

# 在 tb_user 表中，建联合索引，这个联合索引涉及到三个字段，顺序分别为：profession，age，status。
CREATE INDEX IDX_USER_PRO_AGE_STA ON TB_USER (PROFESSION,AGE,STATUS);
SHOW INDEX FROM TB_USER ; # 查询该表的索引

对于最左前缀法则指的是，查询时， 索引最左边的列 ，也就是 profession 必须存在 ，否则索引全部失效。

注意：最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段) 必须存在，与我们编写 SQL 时， 条件编写的先后顺序无关。

2). 范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。但是当前索引不会失效

如下：explain select * from tb_user where profession = ' 软件工程 ' and age > 30 and status = '0';

解释：profession 和 age 用到了索引，但是 status 字段是没有走索引的。

当范围查询使用 >= 或 <= 时，会走联合索引

如下：explain select * from tb_user where profession = ' 软件工程 ' and age >= 30 and status = '0';

解释：profession 、 age 和 status都用到了索引

3). 索引失效情况

①：索引列运算

不用substring() 运算时，phone索引会生效，用了之后，索引失效。

select * from tb_user where substring(phone, 10 , 2 ) = '15' ;

②：字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。因为：数据库存在隐式类型转换，索引将失效。

③：模糊查询

如果仅仅是尾部模糊匹配（like 'dgs%'），索引不会失效。如果是头部模糊匹配（like '%dgs'），索引失效。

④：or连接条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

explain select * from tb_user where id = 10 or age = 23 ;

explain select * from tb_user where phone = '17799990017' or age = 23 ;

# 由于 age没有索引 ，所以即使 id、phone有索引 ，索引也会失效。所以需要针对于 age 也要建立索引。

⑤：数据分布影响

因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。

4). SQL提示

SQL 提示，是优化数据库的一个重要手段，简单来说，就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

①：建议使用指定索引

# use index ：建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。
select * from tb_user use index(idx_user_pro) where profession = '软件工程';

# 建议使用profession字段的索引idx_user_pro

②：忽略指定的索引

# ignore index ：忽略指定的索引。
select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

# 忽略profession字段的索引idx_user_pro

③：强制使用索引

# ignore index ：忽略指定的索引。
select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

# 忽略profession字段的索引idx_user_pro

5). 覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

比如：在 tb_user 表中，有联合索引，涉及到三个字段，顺序分别为：profession，age，status。

解释：

# 建议使用 
SELECT profession，age，status  where profession = '软件工程' and age > 30 and status = '0';
# 不建议使用
SELECT *  where profession = '软件工程' and age > 30 and status = '0';

图文解释：

6). 前缀索引

当字段类型为字符串（ varchar ， text ， longtext 等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO ，影响查询效率。此时可以 只将字符串的一部分前缀 ，建立索引，这样可以大大 节约索引空间 ，从而提高索引效率。

# 语法
# create index idx_xxxx on table_name(column(n)) ;
# 示例，建立字段email前5个长度的索引
  create index idx_email_5 on tb_user(email(5));

6、索引设计原则

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