百木园-与人分享，
就是让自己快乐。
当前位置：百木园 > 野生技术 > 正文

[MySQL] 索引的使用、SQL语句优化策略

2023-01-09 分类：野生技术 阅读(260) 评论(0)

目录

  • 索引
    • 什么是索引
    • 索引的创建与删除
      • 创建索引
      • 删除索引
    • 索引的使用
      • 使用explain分析SQL语句
      • 最佳左前缀
      • 索引覆盖
      • 避免对索引列进行额外运算
  • SQL语句优化
    • 小表驱动大表
      • join buffer
      • in/exists

索引

什么是索引

索引是一种方便我们高效查找某一列或几列数据的一种数据结构，一般是 B+树或者 hash树。想象一下在一个表中有一列是我们经常需要用于作为查询条件的列，也就是它经常出现在 where 子句中，那么如果每次用到它都要顺序遍历全表数据来找到我们所需要的那一行，听着好像效率不太高的样子，所以就出现了索引这个东西。
因为索引一般是使用树这种数据结构来存储的，而树是对排序很友好的一种数据结构，例如一个二叉树，左边都是比根小的而右边都是比根大的，要查找一个数据就很容易。所以有了索引之后就可以增加检索的效率，大大缩短查找时间。

索引的创建与删除

创建索引

可以在创建表的时候一起创建索引，也可以在建完表之后单独创建
在建表的时候创建索引：

CREATE TABLE `tb` (
  `tid` int(3) NOT NULL,
  `tname` varchar(10) DEFAULT NULL,
  `test_column` int(3) DEFAULT NULL,
  PRIMARY KEY (`tid`),
  KEY `name_index` (`tname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

以上语句创建了一个名为 tb 的表（表创建完成之后可以通过以下SQL语句来查看创建该表所需要的SQL语句：

show create table 表名;

我们创建了 tb 表，并指定了 主键为 tid 字段，在 tname 列创建了一个名为 name_index 的索引，并指定了引擎为 InnoDB、字符编码方式为 utf8mb4。

在建表后通过 alter 语句或 create 语句来创建索引：

alter table 表名 add index 索引名(列1, 列2, 列3...);
create index 索引名 on 表名(列1, 列2, 列3...);

可以对一个或多个列共同添加索引。索引创建完成后可以通过以下语句来查看该表的所有索引信息：

可以看到主键也是一个索引

删除索引

有两种删除索引的方式：

drop index 索引名 on 表名;
alter table 表名 drop index 索引名;

索引的使用

使用explain分析SQL语句

使用索引的时候有几点需要注意的地方来避免让索引失效，要观察索引是否失效可以通过 explain 语句来查看 SQL 语句的执行情况。
image

id
数值越大执行顺序越靠前，数值一样时从上往下顺序执行，在本例中也就是 t2 -> subquery2 -> t1。

select_type
查询类型，取值有SIMPLE（简单查询，不包含子查询或 union）、PRIMARY（主查询，一般出现在有子查询的语句中）等。

table
使用的表，有时候会有一些临时表，比如这里的 subquery2。

type
类型，这个类型和上面的 select_type 不要一样，这个 type 字段可以看成是 SQL 语句执行速度的一个衡量方式，
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说 system 和 const 是达不到的，eq_ref 也是比较困难的，所以我们一般能达到的是 ref、range 和 index，当然这些都是针对有索引的情况来说的，没有索引的话那就只能是 ALL。

possible_keys 和 key
预测会使用的索引和实际使用的索引

extra
一些额外信息，比较常见的几种有

  • using filesort：需要额外一次排序，常见于有 order by的语句中
  • using temporary：用到了临时表，常见于有 group by 的语句中
  • using index：代表使用了索引
  • using where：意味不明

前两种代表性能消耗较大，是我们需要避免的，如果出现了这两个信息说明我们的 SQL 语句需要优化了，using index 意味着性能有所提升，而 using where 的出现好像很难总结出什么规律，一般不太需要关注它。

最佳左前缀

这个是针对复合索引来说的，也就是一个索引中包含多个列的时候。最佳左前缀的意思是我们使用索引的时候要按照复合索引的顺序来使用，不要跨列，也就是说，如果一个索引的定义是（a，b，c，d），那我们使用的时候就要按照 abcd 的顺序来使用，当然 abcd 顺序可以变，sql 优化器会自动调整成合适的顺序，但是不能跨列，比如说可以 abdc，但是如果是 abd 跨了个 c 列，那么索引就会失效。不能跨列的原因跟索引的数据结构有关：
image
图中是由(a, b)两列组成的复合索引，可以看到a是按照顺序排列的，而b看似无序，实际上如果a的值固定，b也是按顺序排列的，所以如果我们跨了 a 列只使用b=...，是无法使用这个索引的。
说到这个使用顺序就要提到 SQL 的解析过程了
编写过程：

select dinstinct  ..from  ..join ..on ..where ..group by ...having ..order by ..limit ..

解析过程：

from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

按照这个解析过程，这样的一条 SQL 语句是符合最佳左前缀的：

select d from tb where a=... and b=... and c=...;

我们同时使用了 abc 这三个字段，并且解析顺序也会是 a -> b -> c -> d
这样的 SQL 语句是不符合最佳左前缀的，它会使得一部分索引失效：

select d from tb where a=... and c=...;

b 列没有使用到，也就是说我们只用了 acd 这三列，跨了 b 列，这条语句会导致 a 后面的索引都失效，也就是只有 a 使用到了索引， c=... 语句并没有使用索引。
举个例子：
image

image

可以看到第二个 SQL 语句中跨了 b2 列，所以 index_b1_b2_b3_b4 部分失效了（索引是否部分失效可以通过 key_len 字段看出来）。

索引覆盖

覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。
尽量不要使用 SELECT *语句，因为这样会发生回表查询不能使用索引覆盖从而导致查询效率低。观察以下两条 SQL 语句，一个是 SELECT * 一个是只选择需要的列：
image

image

可以看到使用SELECT *的语句执行时没有走复合索引（即 index_a4_a6，这是由 a4 和 a6 功能组成的一个复合索引），而是走了 index_a4 这个只有 a4 组成的索引，而使用 SELECT a4, a6的语句则走了复合索引，因为整条SQL 语句就只用到了 a4 和 a6 这两列，这两列在index_a4_a6 存储了，所以不需要回表查询，查一次这个复合索引就可以拿到结果了，而前面的SELECT *语句还需要回表查询那些索引里没有字段。所以说尽量不要使用SELECT *，需要用到什么字段就 select 什么字段，避免索引覆盖失效同时也可以减少 IO 消耗。

避免对索引列进行额外运算

对索引进行额外的运算（加减乘、类型转换等）会导致索引失效：
image

image

可以看到 type 从ref 退化成了 index，并且 row 是 4 说明发生了回表查询（test02 表中一共4条数据）。

SQL语句优化

前面我们已经说了索引的重要性了，所以 SQL 优化的很大一部分就是索引的优化，当然还有一些其他的优化原则，这就是我们本节要讲的东西。

小表驱动大表

这个原则不只是写 SQL 语句需要遵循，我们平时写代码的时候也要尽量遵循这个原则。比如写双层 for 循环的时候，尽量把循环次数小的那个 for 放在外层而循环次数多的放在内层，这样就可以减少从内外侧循环切换的次数，减少一些性能消耗。
举个例子，两个 for 循环，一个要循环10次一个要循环100次，当然不管两个循环怎么组合最终都是一共要循环1000次，但是如果把循环10次的放在外层，那么就从外层循环跳到内层循环的次数就只要10次，反之要100次。所以把循环次数少的那个 for 循环放在外面可以减少栈帧的切换次数从而提升性能。

join buffer

回到 SQL 场景中就是当存在子查询的时候，把数据量大的表放在子查询里而数据量小的表放在主查询里。为什么小表驱动大表效率高呢，这就要提到 SQL 中一个叫做 join_buffer 的东西了。
image
图上的语句执行计划的 extra字段中可以看到Using join buffer BNL，说明该条查询使用了 join buffer，使用的算法是 BNL(Block Nested Loop)，块嵌套循环，这个块指的就是join buffer。看名字就知道join buffer是一个缓存区，和 join有关的缓存区，我们可以使用以下语句来查看这块缓存区的大小：

show variables like \"%join_buffer%\";

image
单位是字节，转化为 KB 差不多是 256KB。
那 BNL 算法又是什么呢，我们来看看MariaDB官网 （MariaDB是MySQL的一个分支，可以完全兼容MySQL）对它的描述：

The algorithm performs a join operation of tables t1 and t2 according to the following schema.
The records of the first operand are written into the join buffer one by one until the buffer is full.
The records of the second operand are read from the base/temporary table one by one. For every read record r2 of table t2 the join buffer is scanned, and, for any record r1 from the buffer such that r2 matches r1 the concatenation of the interesting fields of r1 and r2 is sent to the result stream of the corresponding partial join.
To read the records of t2 a full table scan, a full index scan or a range index scan is performed. Only the records that meet the condition pushed to table t2 are checked for a match of the records from the join buffer.
When the scan of the table t2 is finished a new portion of the records of the first operand fills the buffer and matches for these records are looked for in t2.
The buffer refills and scans of the second operand that look for matches in the join buffer are performed again and again until the records of first operand are exhausted.
In total the algorithm scans the second operand as many times as many refills of the join buffer occur.

这段描述介绍了 BNL算法的工作过程，以t1 left join t2为例，大概流程为：

  • 将t1逐条装入join buffer，直到填满join buffer
  • 逐条读 t2 数据，读一条就扫描一遍join buffer，如果条件匹配就把 select的字段加到结果区里，读取t2的时候可能会执行全表扫描或者是走索引，只有那些符合条件的数据会被拿去和join buffer里的数据做匹配，这的条件我的理解是指on子句里可能写了一些t2的约束条件，这个时候就可以使用索引了，如果有索引的话。
  • 扫描完了一遍t2之后就更新join buffer，从剩下的t1的数据里继续读数据装进来，重复1、2步直到读完t1的数据。
  • 算法扫描t2的次数为 len(t1)/size(join buffer)

从算法扫描的次数的计算公式可以看出，要减少算法扫描的次数，要么让t1为小表，要么增大join buffer的大小，当然这两个方案可以同时使用。
当然可能有的场景下我们就是必须得把大表放在主查询里，因为我们需要的字段在大表里，那么这时候我们就可以使用 exists 和 in 这两个关键词来做一些转换来提升 SQL 语句的效率了：

in/exists

首先说一下 in和exists的区别：

  • in: 先查子查询，查出结果后和主查询做笛卡尔积，子查询只查一次。
    以这条SQL为例我们来看看in语句的执行过程
select * from Country 
where 
  Continent=\'Europe\' and 
  Country.Code in (select City.country 
                   from City 
                   where City.Population>1*1000*1000);

执行过程如图所示，先查子查询，然后和执行条件判断取出复合主查询里的条件的结果
image

  • exists: 先查主查询，然后每次进行主查询的时候都会遍历一遍子查询表，也就是说子查询执行次数为主查询表中的数据量n。

带有 exists/in 关键词的语句我们称为 半连接（semi-join)语句，半连接语句的重点在于我们只关注外层表（驱动表）中的字段，内层子查询只是用来做判断筛选外层表中的字段的，并且就算是子查询中有多行符合条件，我们主查询也只会返回一行。

假设现在t1为小表，t2为大表
小表在外层时：
正例：

select * from t1 where exists(select id from t2 where id=t1.id);

反例：

select * from t1 where id in (select id from t2);

正例之所以是推荐的写法是因为 exists后面跟了大表，此时子查询可以使用 t2 表的 id 列的索引来提高查询效率，外层是一个小表，内层又可以使用索引来加速查询，所以总体效率较高；而反例之所以不推荐是因为 in 后面跟了大表，所以这个大表会被整个查询一遍和t1做笛卡尔积，很显然没有正例的效率高。
大表在外层时：
正例：

select * from t2 where id in (select id from t1);

反例：

select * from t2 where exists(select id from t1 where id=t2.id);

反例exists后跟小表，索引对于小表的查询效率提升不明显。
总结起来就是 in后面跟小表，exists后面跟大表

来源：https://www.cnblogs.com/kirizi/p/17023322.html
本站部分图文来源于网络，如有侵权请联系删除。

未经允许不得转载：百木园 » [MySQL] 索引的使用、SQL语句优化策略
标签：

相关推荐

  • 暂无文章

热门文章