MySQL索引篇

诗这样的

已于 2024-04-22 09:54:10 修改

阅读量217

点赞数

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

于 2023-05-12 00:01:13 首次发布

本文链接：https://blog.csdn.net/m0_72406127/article/details/130613661

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

索引概述(为什么添加索引？)

索引结构

为什么InnoDB存储引擎选择使用B+tree索引结构？

查看SQL的执行频率(为SQL优化提供支撑)

慢查询日志：

profile详情：（了解，开发中很少用）

索引概述(为什么添加索引？)

无索引：查找时需要全表扫描，效率太低
有索引：查找时通过B+Tree查找时效率高，
索引的优势：
- 提高数据检索的效率，降低数据库的IO成本
- 通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗
索引的劣势：
- 索引列会占用磁盘空间，索引大大提高了查询效率，同时也降低更新表的速度，
- 如对表进行DML语句操作时效率降低(但在实际开发中，相对较少执行此操作)

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

BTree索引结构(多路平衡查找树)：B-树索引是一种平衡树结构，它通过将索引键值和指针存储在树节点中，支持快速的查找、插入和删除。B-树索引适用于构建在内存或磁盘上的通用索引，如MySQL中的InnoDB存储引擎。
以一颗最大度数(max-dergree)为5(5阶)的BTree为例(每个节点最多存储4个key,5个指针)；

B+Tree索引结构：B+tree则是一种优化的Btree，它主要用于文件系统和数据库系统中。B+tree的节点只存储关键字和指向下一个叶节点的指针，而其他节点信息都存储在叶节点中。这种设计可以减小内存占用，同时提高查询效率。B+tree的叶节点形成一个有序链表，按照关键字的顺序排列。这样可以很容易地进行范围查询，而不需要进行全局的遍历。
- 在MySQL中对B+Tree进行了优化，在原B+Tree的基础上，增加有一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高了访问区间的性能。（循环双向链表）

B+Tree相对于BTree的区别：
①所有的数据都会出现在叶子节点

②叶子节点形成一个单向链表

Hash索引结构：Hash索引是一种通过哈希算法来实现的索引结构，适用于等值查询，但对于范围查询、排序等操作的效率不高，因此在MySQL中主要用于存储MEMORY引擎表。
- 哈希索引就是采用一定的hash算法，将键值转换成新的hash值，映射到对应的槽位上，然后存储在hash表中。
- 如果两个(或多个)键值，映射到一个相同的槽位上，就产生了哈希冲突(也成为哈希碰撞)，可以通过链表来解决

Hash索引的特点：

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

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

③查询效率高，通常只需要一次检索就可以了，效率通常要高于B+Tree索引

R-Tree索引结构：R-Tree索引是一种用于空间数据类型的索引结构，可以用于存储GIS数据、地图数据等有空间属性的数据，在MySQL中主要用于存储InnoDB引擎表中的空间数据类型。
Fulltext索引结构：Fulltext索引是一种用于全文搜索的索引结构，主要用于存储MyISAM引擎表中的文本类型的数据，可以支持文本分词、模糊查询等功能。

这些索引结构各有优缺点，可以根据实际需求选择合适的索引类型和结构，以提高MySQL数据库的查询效率。

动态演化数据结构的网站：Data Structure Visualization (usfca.edu)

为什么InnoDB存储引擎选择使用B+tree索引结构？

相比于二叉树，层级更少，搜索效率高；
对于Btree ，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少要同样保存大量数据，只能增加树的高度，导致性能降低；
相对Hash索引，B+tree支持范围匹配及排序操作;

索引分类：

按功能划分：

主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；
唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
全文索引：它查找的是文本中的关键词，主要用于全文检索。

按索引的存储形式：

聚集索引(Clustered Index):

将数据存储与索引放到一块，索引结构的叶子节点保存了行数据必须有，而且只有一个。（默认主键索引是聚集索引）
二级索引(Secondary Index)也称为辅助索引或非聚集索引:

将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个。

聚集索引的选取规则：

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

索引语法：


#创建索引：
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...);
#查看索引：
SHOW INDEX FROM        table_name;
#删除索引：
DROP INDEX index_name ON table_name;

SQL性能分析

查看SQL的执行频率(为SQL优化提供支撑)

通过 show [session|global] status 命令可以提供服务器状态信息。

通过如下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次

SHOW GLOBAL STATUS LIKE 'Com_______';

慢查询日志：

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

MySQL 的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息：

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

profile详情：（了解，开发中很少用）

show profile 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过hava_profiling参数，能够看到当前MySQL是否支持profile操作：
```
SELECT @@have_profiling;
```

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling;

SET profiling = 1;

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

#查看每一条SQL的耗时基本情况
show profiles;

#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

#查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

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，则没有索引。
rows
- MySQL认为必须要执行查询的行数，在InnoDB引擎的表中，是一个估计值，可能并不准确
filtered
- 表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

索引使用

验证索引效率

耗时11.03秒

在未建立索引之前，执行以下SQL语句，查看SQL耗时。

SELECT * FROM tb_sku WHERE sn = '100000003145001';

针对字段创建索引

create index idx_sku_sn on tb_sku(sn);

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

SELECT * FROM tb_sku WHERE sn = '100000003145001';

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果不存在创建时的最左列，索引将全部失效 //注意：与字段查询的位置无关
如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

在tb_user表将profession,age,status创建联合索引

create index idx_user_pro_age_sta on tb_user(profession,age,status);

后三个行是联合索引，（idx_user_pro_age_sta）,最左列是profession

执行下面SQL语句

explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0' ;

索引生效(key): idx_user_pro_age_sta

索引长度(key_len): 54

总索引长度为54

执行下面SQL语句

 explain select * from tb_user where profession = '软件工程' and age = 31 ;

索引生效(key): idx_user_pro_age_sta

索引长度(key_len): 49

跳跃了status列，计算得status的索引长度为5

执行下面SQL语句

explain select * from tb_user where profession = '软件工程' ;

索引生效(key): idx_user_pro_age_sta

索引长度(key_len): 47

跳跃了age列和status列，计算得age的索引长度为2

执行下面SQL语句

 explain select * from tb_user where age = 31 and status = '0';

由于在联合索引创建中最左边的列是profession，且在select语句中不存在profession列，不满足最左查询法则，因此是全表查询，索引失效

执行下面SQL语句

explain select * from tb_user where profession = '软件工程' and status = '0' ;

索引生效(key): idx_uesr_pro_age_sta

索引长度(key_len): 47 //由上面分析说明没有执行profession 之后的字段，跳跃了age列，联合索引部分生效

思考题：执行下面的SQL语句

explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程';

是否会使用联合索引？如果使用联合索引那么索引长度是多少？

索引生效(key): idx_uesr_pro_age_sta

索引长度(key_len): 54

说明在select 语句中联合索引的字段位置改变，不会影响索引的使用，存在即合理！

索引失效的情况
1.范围查询

联合索引中，出现范围查询(>,<),范围查询右侧的列索引失效

执行下面SQL语句

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

索引生效(key): idx_uesr_pro_age_sta

索引长度(key_len): 49

由于status的索引长度为5，说明未执行范围查询age后面的status索引,范围查询右侧的列索引失效

规避方法：在不影响业务逻辑的条件下，使用 >= 或 <= 进行范围查询

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

2.索引列运算

不要在索引列上进行运算操作，否则索引将失效

3.字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

在单个索引中，索引失效

在联合索引中，索引将部分失效（符合最左前缀法则）

例如上述SQL语句中

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

status = 0

key_len : 49

说明status 索引失效

4.模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效

例如执行下面SQL语句

explain select * from tb_user where profession like '软件%';

尾部模糊匹配，索引正常执行

explain select * from tb_user where profession like '%工程';

头部模糊匹配，索引失效

5.or连接的条件

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

执行下面SQL语句

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

id 的是主键索引，age是联合索引，在满足最左前缀原则的前提下，age索引失效

所以涉及到的索引都失效

规避方法：在or后的字段创建一个索引

create index idx_user_age on tb_user(age);

然后再次执行

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

索引生效

6.数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引

如果一个表中的一组数据分布不均匀，比如其中一个值有大量重复，而其他值很少出现，那么使用该索引可能不会有效地缩小查询范围，导致索引失效，直接进行全表扫描。

SQL提示

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

在某字段有多个索引的情况下，通过人为提示，让MySQL执行此索引

例如对tb_uesr表中profession 字段再创建一个常规索引

create index idx_user_pro on tb_user(profession);

use index:

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

建议MySQL使用此索引，MySQL优化器还要对此index进行评估

ignore index:

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

让MySQL忽略此索引

force index:

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

强制MySQL使用此索引

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经能够全部找到），减少select * 。

执行以下SQL语句

explain select id,profession,age,status from tb_user where professino = '软件工程' and age =  31 and status = '0';

explain select id,profession,age,status,name from tb_user where professino = '软件工程' and age =  31 and status = '0';

注意：

using index condition : 查找使用了索引，但是需要回表查询数据

using where;using index : 查找使用了索引，但是需要的数据都能在索引列中能找到，所以不需要回表查询数据

在第一个查询语句中，不需要回表查询，因为建立了profession,age,status 的联合索引，只需要通过二级索引就可以找到

在第二个查询语句中，需要回表查询，因为name 字段，需要通过二级索引(回表查询)找到 id(聚集索引)对应的row中，再在row中查找name字段。

即查找的字段在二级索引中没有的，就需要回表查询。

分别执行以下三个SQL语句

select * from tb_user where id = 2;
select id,name from tb_user where name = 'Arm';
select id,name,gender from tb_user where name = 'Arm';

覆盖索引：直接对聚集索引查找返回row

覆盖索引：不需要回表，一次索引扫描就执行完成

在这条select语句中需要查询的gender不在辅助索引(二级索引)中，需要通过id值，进行回表查询得到row，再从row中找到name字段

前缀索引

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

语法

create index idx_xxx on table_name(column(n));    //idx_xxx创建的前缀索引名称，column是创建索引的列名，n是截取的前缀长度。

前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

例如计算tb_user表中的email字段合适的前缀长度

select count(distinct email / count(*)) from tb_user;

select count(distinct substring(email, 1, 5)) / count(*) from tb_user;

执行后可知，截取五个字符可基本满足需求

所以创建的前缀索引的长度为5

create index idx_email_5 on tb_user(email(5));

在实际开发中，在处理较长的字符串时，根据业务需求，如果需要的选择性较高，选取选择性为1的前缀索引；如果选择性的要求不高，可以减少前缀的长度，节省空间，平衡选择性与前缀长度的关系

前缀索引查询流程

通过前缀索引查询后，找到对应的id 值，回表查询row 值当下前缀索引对应的实际的字符串长度，如果相等返回row，否则返回null

单列索引与联合索引
单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。

已知 tb_user 表中创建了 idx_user_phone 和 idx_user_name 单列索引。

执行以下SQL语句

explain select id,phone,name from where phone = '17799990010' and name = '韩信';

possible_keys(可能使用的索引) : idx_user_phone ，idx_user_name

key(实际使用的索引): idx_user_phone

在查询时 name 字段需要通过二级索引进行回表查询（效率比较低，建议创建 phone 和 name 的联合索引）

create unique index idx_user_phone_name on tb_user (phone,name);

通过SQL提示，让MySQL执行此联合索引

explain select id,phone,name from use index(idx_user_phone_name) where phone = '17799990010' and name = '韩信';

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

索引设计原则

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

诗这样的

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL索引篇

无索引：查找时需要全表扫描，效率太低有索引：查找时通过B+Tree查找时效率高，索引的优势：提高数据检索的效率，降低数据库的IO成本通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗索引的劣势：索引列会占用磁盘空间，索引大大提高了查询效率，同时也降低更新表的速度，如对表进行DML语句操作时效率降低(但在实际开发中，相对较少执行此操作)
复制链接

扫一扫