当前位置： 首页 > news >正文

MySQL 索引

news 2024/10/23 21:59:51

索引概述

官方定义: 一种帮助MySQL提高查询效率的数据结构

  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）

  • 我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引

  • 存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现

image-20220612090553485

索引结构

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

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

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

索引InnoDBMyISAMMemory
B+tree索引支持支持支持
Hash 索引不支持不支持支持
R-tree 索引不支持支持不支持
Full-text5.6版本之后支持支持不支持

Hash表

Hash表，在Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

  • 无法用于排序与分组；
  • 只支持精确查找，无法用于部分查找和范围查找。

image-20220612092245176

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

二叉查找树

可视化工具：B-Tree Visualization (usfca.edu)

图片

二叉树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。

这个特点就是为了保证每次查找都可以这折半而减少IO次数，但是二叉树就很考验第一个根节点的取值，因为很容易在这个特点下出现我们并发想发生的情况“树不分叉了”，这就很难受很不稳定。

图片

显然这种情况不稳定的我们再选择设计上必然会避免这种情况的

平衡二叉树

平衡二叉树是采用二分法思维，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。

使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。查询id=6，只需要两次IO。

图片

就这个特点来看，可能各位会觉得这就很好，可以达到二叉树的理想的情况了。然而依然存在一些问题：

  1. 时间复杂度和树高相关。树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作。树的高度就等于每次查询数据时磁盘 IO 操作的次数。磁盘每次寻道时间为10ms，在表数据量大时，查询性能就会很差。（1百万的数据量，log2n约等于20次磁盘IO，时间20*10=0.2s）
  2. 平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高。

B树：改造二叉树

MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。那如何降低树的高度呢？

假如key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节（8+4*2=16）。

因为在MySQL的InnoDB存储引擎一次IO会读取的一页（默认一页16K）的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建1百万条数据，树的高度只需要2层就可以（1000*1000=1百万），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了。

这种数据结构我们称为B树，B树是一种多叉平衡查找树，如下图主要特点：

  1. B树的节点中存储着多个元素，每个内节点有多个分叉。
  2. 节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
  3. 父节点当中的元素不会出现在子节点中。
  4. 所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

图片

举个例子，在b树中查询数据的情况：

假如我们查询值等于10的数据。查询路径磁盘块1->磁盘块2->磁盘块5。

第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，10<15，走左路，到磁盘寻址磁盘块2。

第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<10，到磁盘中寻址定位到磁盘块5。

第三次磁盘IO：将磁盘块5加载到内存中，在内存中从头遍历比较，10=10，找到10，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

相比二叉平衡查找树，在整个查找过程中，虽然数据的比较次数并没有明显减少，但是磁盘IO次数会大大减少。同时，由于我们的比较是在内存中进行的，比较的耗时可以忽略不计。B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树构建索引可以很好的提升查询的效率。

过程如图：

图片

B树索引查询过程

看到这里一定觉得B树就很理想了，但是前辈们会告诉你依然存在可以优化的地方：

  1. B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
  2. 如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

B+树：改造B树

B+Tree由三部分组成：根root、枝branch以及Leaf叶子，其中root和branch不存储数据，只存储指针地址，数据全部存储在Leaf Node，同时Leaf Node之间用双向链表链接，结构如下：

图片

从上面可以看到，每个Leaf Node是三部分组成的，即前驱指针p_prev，数据data以及后继指针p_next，同时数据data是有序的，默认是升序ASC，分布在B+tree右边的键值总是大于左边的，同时从root到每个Leaf的距离是相等的，也就是访问任何一个Leaf Node需要的IO是一样的，即索引树的高度Level + 1次IO操作。

B+树，作为B树的升级版，在B树基础上，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题

  • B树：非叶子节点和叶子节点都会存储数据。
  • B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。
  • InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
  • 辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找，这个过程也被称作回表。

图片

B+树数据结构

B+树的最底层叶子节点包含了所有的索引项。从图上可以看到，B+树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。

所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系，但是从另一方面来说，由于数据都被放到了叶子节点，放索引的磁盘块锁存放的索引数量是会跟这增加的，相对于B树来说，B+树的树高理论上情况下是比B树要矮的。

也存在索引覆盖查询的情况，在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。

举个例子：等值查询

假如我们查询值等于9的数据。查询路径磁盘块1->磁盘块2->磁盘块6。

第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，9<15，走左路，到磁盘寻址磁盘块2。

第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<9<12，到磁盘中寻址定位到磁盘块6。

第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，在第三个索引中找到9，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。（这里需要区分的是在InnoDB中Data存储的为行数据，而MyIsam中存储的是磁盘地址。）

过程如图：

图片

B+树根据索引等值查询过程

范围查询：

假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。

首先查找值等于9的数据，将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘IO。

查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合筛选条件的数据。

第四次磁盘IO：根据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将data缓存到结果集。

主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。

图片

可以看到B+树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B+树的数据结构。

索引分类

索引分类

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

  1. 主键索引 自动创建

    # 建表 主键自动创建主键索引
create table t_user(id varchar(20) primary key,name varchar(20));
# 查看索引
show index from t_user;

  2. 单列索引(普通索引|单值索引)

    # 建表时创建
create table t_user(id varchar(20) primary key,name varchar(20),key(name)); '注意:随表一起建立的索引索引名同列名一致'# 建表后创建
create index nameindex on t_user(name);# 删除索引
drop index 索引名 on 表名

  3. 唯一索引

    # 建表时创建create table t_user(id varchar(20) primary key,name varchar(20),unique(name));# 建表后创建create unique index nameindex on t_user(name);

  4. 复合索引

    # 建表时创建create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));# 建表后创建create index nameageindex on t_user(name,age);

最左匹配原则

  • 索引可以简单如一个列 (a)，也可以复杂如多个列 (a,b,c,d)，即联合索引。
  • 如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。
  • 因此，列的排列顺序决定了可命中索引的列数。

例子：

  • 如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)

“合并索引”策略简单来讲，就是使用多个单列索引，然后将这些结果用“union或者and”来合并起来

聚集索引&二级索引

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

聚集索引选取规则:

  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

执行如下的SQL语句时，具体的查找过程

image-20220612093429794

具体过程如下:

​ ①. 由于是根据name字段进行查询，所以先根据name='Arm'到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

​ ②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。

​ ③. 最终拿到这一行的数据，直接返回即可。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取 数据的方式，就称之为回表查询。

索引语法

#  创建索引
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执行频率

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

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

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

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有 SQL语句的日志。 MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。

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

通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化。

profile详情

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

SELECT @@have_profiling;

image-20220612094153691

可以看到，当前MySQL是支持 profile操作的，但是开关是关闭的。可以通过set语句在 session/global级别开启profiling：

SET profiling = 1;

-- 查看每一条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 执行计划中各个字段的含义:

字段含义
idselect查询的序列号，表示查询中执行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表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长 度，在不损失精确性的前提下， 长度越短越好 。
rowsMySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值， 可能并不总是准确的。
filtered表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

索引使用

验证索引效率

# 使用主键索引
SELECT * FROM tb_sku where id = 1;

image-20220612135252108

# 不使用索引
SELECT * FROM tb_sku WHERE sn = '100000003145001';

image-20220612135232930

使用索引

create index idx_sku_sn on tb_sku(sn) ;SELECT * FROM tb_sku WHERE sn = '100000003145001';

image-20220612135822881

最左前缀法则

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

image-20220612140950839

show index from `tb_user`;

image-20220612141042241

对于最左前缀法则指的是，查询时，最左变的列，也就是profession必须存在，否则索引全部失效。 而且中间不能跳过某一列，否则该列后面的字段索引将失效。

explain select * from `tb_user` where profession = '软件工程' and age = 31 and status= '0';
# 这两个等价 即where后面的条件没有优先级
explain select * from `tb_user` where  age = 31 and status= '0' and profession = '软件工程';

image-20220612141459432

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

image-20220612141643461

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

image-20220612141755679

只要联合索引最左边的字段 profession存在，索引就会生效，只不 过索引的长度不同。 而且由以上三组测试，我们也可以推测出profession字段索引长度为36、age 字段索引长度为2、status字段索引长度为4。

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

image-20220612141925265

explain select * from tb_user where status = '0';

image-20220612141956231

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

image-20220612142049875

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

范围查询

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

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

image-20220612143455061

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

image-20220612143529510

当范围查询使用>= 或 <= 时，走联合索引了，但是索引的长度为54，就说明所有的字段都是走索引的。所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <

索引失效情况

索引列运算

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

# 索引生效
explain select * from tb_user where phone = '17799990015';# 索引失效
explain select * from tb_user where substring(phone,10,2) = '15';

字符串不加引号

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

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

image-20220612144032520

image-20220612144003304

模糊查询

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

下面查询语句中，都是根据profession字段查询，符合最左前缀法则，联合索引是可以生效的，

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

image-20220612144138620

image-20220612144152761

image-20220612144205607

在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字 前面加了%，索引将会失效。

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;

image-20220612144433673

image-20220612144447576

由于age没有索引，所以即使id、phone有索引，索引也会失效

create index idx_user_age on tb_user(age);explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;

image-20220612144623822

image-20220612144639779

数据分布影响

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

explain select * from tb_user where phone >= '17799990005';explain select * from tb_user where phone >= '17799990015';

image-20220612144922650

image-20220612144950998

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

is null 与 is not null 操作是否走索引

explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;

image-20220612145205250

image-20220612145218585

update tb_user set profession = null;explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;

image-20220612145339154

image-20220612145356884

SQL提示

当单列索引和联合索引都存在时会走哪个索引

drop index idx_user_age on tb_user;
drop index idx_email on tb_user;create index idx_user_pro on tb_user(profession);explain select * from tb_user where profession = '软件工程';

image-20220612145832605

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

  • use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。

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

    • image-20220612145948502

  • ignore index ： 忽略指定的索引。

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

  • force index ： 强制使用索引。

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

覆盖索引

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

# 走联合索引，不需要回表
explain select id, profession from tb_user where profession = '软件工程' and age =31 and status = '0' ;# 走联合索引，不需要回表
explain select id,profession,age, status from tb_user where profession = '软件工程'and age = 31 and status = '0' ;# 走联合索引，由于name不在联合索引中，需要回表
explain select id,profession,age, status, name from tb_user where profession = '软件工程' and age = 31 and status = '0' ;# 走联合索引，需要回表
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

MySQL5.5

image-20220612165415506

Extra含义
Using where; Using Index查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
Using index condition查找使用了索引，但是需要回表查询数据
MySQL8.0

image-20220612150518496

image-20220612150542414

image-20220612150600039

image-20220612150613250

ICP

https://www.jianshu.com/p/31ceadace535

跳表

https://cloud.tencent.com/developer/article/1641469

https://cloud.tencent.com/developer/article/1437313?from=article.detail.1641469

索引跳跃式扫描（INDEX SKIP SCAN）适用于所有类型的复合B树索引（包括唯一性索引和非唯一性索引），它使那些在where条件中没有对目标索引的前导列指定查询条件但同时又对该索引的非前导列指定了查询条件的目标SQL依然可以用上该索引，这就像是在扫描该索引时跳过了它的前导列，直接从该索引的非前导列开始扫描一样（实际的执行过程并非如此），这也是索引跳跃式扫描中"跳跃"（SKIP）一词的含义。

ISS(Index skip scan) 可以在查询过滤组合索引不包括最左列的情况下，走索引扫描，而不必要单独建立额外的索引

MySQL8.0.13开始支持 index skip scan 也即索引跳跃扫描。该优化方式支持那些SQL在不符合组合索引最左前缀的原则的情况，优化器依然能组使用组合索引。

# 在mysql8.0之前，如果sql的where条件中不包含最左前缀，则该联合索引不会生效，走 FULL TABLE SCAN，而在后面的版本中则会走 Using where; Using index for skip scan，
# 限制条件：
# 		1.select 选择的字段不能包含非索引字段
# 		2.sql 中不能带 group by或者distinct 语法
# 		3.Skip scan仅支持单表查询，多表关联是无法使用该特性。
# 		4.对于组合索引 ([A_1, …, A_k,] B_1, …, B_m, C [, D_1, …, D_n])，A,D 可以为空，但是B ,C 字段不能为空。
explain select id from tb_user where age = 31;
# 将age字段变成最后一个索引则会走Using where; Using index 
# age 为第二个字段则会走  Using where; Using index for skip scan#该sql不满足第一条，所以会走全文扫描
explain select name from tb_user where age = 31;

image-20220612170126668

image-20220612170141036

image-20220618001917058

image-20220618002006306

表t1的两个字段rank1,rank2。有这样的记录，

image-20220618000843023

select * from t1 where rank2 >400;
# 等价于
select * from t1 where rank1 = 1 and rank2 > 400 union all select * from t1 where rank1 = 5 and rank2 > 400;

MySQL其实内部自己把左边的列做了一次DISTINCT，完了加进去。

**ISS其实恰好适合在这种左边字段的唯一值较少的情况下，效率来的高。**比如性别，状态等等

字段详解

https://mp.weixin.qq.com/s/ygvuP35B_sJAlBHuuEJhfg

  • using where：查询使用索引的情况下，需要回表去查询所需的数据。
  • using index condition：查询使用了索引，但是有部分条件无法使用索引，会根据能用的索引的条件先搜索一遍再匹配无法使用索引的条件
  • using index：查询使用覆盖索引的时候会出现。
  • using index & using where：查询使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据。

https://blog.csdn.net/poxiaonie/article/details/77757471?spm=1001.2101.3001.6650.3&utm_medium=distribute.pc_relevant.none-task-blog-2~default~BlogCommendFromBaidu~default-3-77757471-blog-113349563.pc_relevant_blogantidownloadv1&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~BlogCommendFromBaidu~default-3-77757471-blog-113349563.pc_relevant_blogantidownloadv1&utm_relevant_index=5

https://wwwxz.blog.csdn.net/article/details/113925826?spm=1001.2101.3001.6650.3&utm_medium=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~default-3-113925826-blog-77757471.pc_relevant_downloadblacklistv1&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~default-3-113925826-blog-77757471.pc_relevant_downloadblacklistv1&utm_relevant_index=5

Using index

  • 表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行。
  • 如果同时出现using where，表明索引被用来执行索引键值的查找;
  • 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

image-20220612183133461

image-20220612183144873

Using where

表示MySQL将对storage engine提取的结果进行过滤，过滤条件字段无索引；

explain select id from tb_user where age = 31;   
explain select id from tb_user where profession = '软件工程';
explain select id from tb_user where name = '吕布';
explain select id from tb_user where name = '吕布' and profession = '软件工程';

前缀索引

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

create index idx_xxxx on table_name(column(n));

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

联合索引

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

image-20220612184609742

索引设计原则

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

SQL优化

插入数据

批量插入数据

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

手动控制事务

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

主键顺序插入，性能要高于乱序插入

大批量插入数据

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使 用MySQL数据库提供的load指令进行插入。操作如下：

SET GLOBAL local_infile = 1;load data local infile 'C:/Users/28931/Desktop/sql/tb_sku2.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

主键优化

主键顺序插入的性能是要高于乱序插入的

数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表 (index organized table IOT)。

image-20220612190000878

行数据，都是存储在聚集索引的叶子节点上的

image-20220612190020358

在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。 那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储 到下一个页中，页与页之间会通过指针连接。

页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行 溢出)，根据主键排列。

主键顺序插入效果

从磁盘中申请页， 主键顺序插入

image-20220612190141341

第一个页没有满，继续往第一页插入

image-20220612190158605

当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接

image-20220612190218425

当第二页写满了，再往第三页写入

image-20220612190241835

主键乱序插入效果

1#,2#页都已经写满了，存放了如图所示的数据

image-20220612190310344

此时再插入id为50的记录

image-20220612190332786

索引结构的叶子节点是有顺序的。按照顺序，应该存储在47之后。

image-20220612190349484

但是47所在的1#页，已经写满了，存储不了50对应的数据了。 那么此时会开辟一个新的页3#。

image-20220612190410122

但是并不会直接将50存入3#页，而是会将1#页后一半的数据，移动到3#页，然后在3#页，插入50。

image-20220612190427147

移动数据，并插入id为50的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1#的下一个 页，应该是3#， 3#的下一个页是2#。 所以，此时，需要重新设置链表指针。

image-20220612190440911

上述的这种现象，称之为 "页分裂"，是比较耗费性能的操作。

页合并

image-20220612190506729

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间 变得允许被其他记录声明使用。

image-20220612190524271

当我们继续删除2#的数据记录

image-20220612190539717

当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前 或后）看看是否可以将两个页合并以优化空间使用。

image-20220612190556770

删除数据，并将页合并之后，再次插入新的数据21，则直接插入3#页

image-20220612190611867

这个里面所发生的合并页的这个现象，就称之为 "页合并"。

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

order by 优化

MySQL的排序，有两种方式：

Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序 操作时，尽量要优化为 Using index。

  • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
  • 尽量使用覆盖索引。
  • 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
  • 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

group by 优化

如果仅仅根据age分组，就会出现 Using temporary ；而如果是根据 profession,age两个字段同时分组，则不会出现 Using temporary。原因是因为对于分组操作， 在联合索引中，也是符合最左前缀法则的。

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：

​ A. 在分组操作时，可以通过索引来提高效率。

​ B. 分组操作时，索引的使用也是满足最左前缀法则的。

limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

优化思路: 一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

update优化

# 当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。
update course set name = 'javaEE' where id = 1;# 当我们开启多个事务，在执行下面的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能大大降低。
update course set name = 'SpringBoot' where name = 'PHP' ;

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁 升级为表锁 。


http://www.mrgr.cn/news/57109.html

相关文章：

  • C#学习笔记（五）
  • Selenium自动化测试工具
  • 数据通信与网络课程展示图谱问答展示系统
  • Java岗临近面试，如何短期突击通过？
  • 力扣143.重排链表
  • k8s集群安装
  • Linux下的基本指令
  • 隐式类型转换
  • 借助keras的层知识理解理解神经网络层的构成相关概念
  • SchoolWeb1--基于课堂教学所汲取的知识点1
  • 【python】ord() chr()
  • 基于Java+Springboot+Vue开发的旅游景区管理系统
  • MySQL-日志
  • Android Studio Gradle版本、插件以及Android API对应关系（持续更新）
  • faiss向量数据库实现rag
  • 搭建知识库：快消行业中知识管理的重要性
  • 奖金居然高达十几万美金！最大素数到底有啥用？
  • RTOS之队列
  • 看完这篇，轻松搞定JavaScript复杂的问题
  • 基于Word2Vec和LSTM实现微博评论情感分析
  • 给c++小白的教程11：优化（1）
  • Django自定义过滤器
  • ffmpeg环境
  • 拍摄照片（鸿蒙系统01）
  • D46【python 接口自动化学习】- python基础之类
  • stl（1）pair