索引-00 不为人知的秘密

最新推荐文章于 2022-07-08 14:08:21 发布

y_index

最新推荐文章于 2022-07-08 14:08:21 发布

阅读量162

点赞数

本文链接：https://blog.csdn.net/y_index/article/details/100555642

版权

1、强制使用索引

EXPLAIN  SELECT
	sum(if(type = 1,amount,0)) - sum(if(type = 2,amount,0)) as amount
FROM
	integral_wallet_seq force index (cust_id_index,inte_name_index)
WHERE
	
  inte_name = 'USDT' and cust_id = '511f3aa1256243c4a9dbab84cb8f9a8e'

2、index_merge（mysql之前只能用一个索引）

为什么会有index merge

我们的 where 中可能有多个条件(或者join)涉及到多个字段，它们之间进行 AND 或者 OR，那么此时就有可能会使用到 index merge 技术。index merge 技术如果简单的说，其实就是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。
MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。

index merge: 同一个表的多个索引的范围扫描可以对结果进行合并，合并方式分为三种：union, intersection, 以及它们的组合(先内部intersect然后在外面union)。

官方文档给出了四个例子：



SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key=30;

SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1=t1.some_col;

SELECT * FROM t1, t2 WHERE t1.key1=1 AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);

index merge 算法根据合并算法的不同分成了三种：intersect, union, sort_union.

2. index merge 之 intersect

简单而言，index intersect merge就是多个索引条件扫描得到的结果进行交集运算。显然在多个索引提交之间是 AND 运算时，才会出现 index intersect merge. 下面两种where条件或者它们的组合时会进行 index intersect merge:
1) 条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)

key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

2) 主键上的任何范围条件

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;

SELECT * FROM tbl_name WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;
上面只说到复合索引，但是其实单字段索引显然也是一样的。比如 select * from tab where key1=xx and key2 =xxx; 也是有可能进行index intersect merge的。另外上面两种情况的 AND 组合也一样可能会进行 index intersect merge.

3. index merge 之 union

简单而言，index uion merge就是多个索引条件扫描，对得到的结果进行并集运算，显然是多个条件之间进行的是 OR 运算。

下面几种类型的 where 条件，以及他们的组合可能会使用到 index union merge算法：

1) 条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)

2) 主键上的任何范围条件

3) 任何符合 index intersect merge 的where条件；

上面三种 where 条件进行 OR 运算时，可能会使用 index union merge算法。

例子：

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;

SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR (key3='foo' AND key4='bar') AND key5=5;

第一个例子，就是三个单字段索引进行 OR 运算，所以他们可能会使用 index union merge算法。

第二个例子，复杂一点。(key1=1 AND key2=2) 是符合 index intersect merge; (key3=’foo’ AND key4=’bar’) AND key5=5 也是符合index intersect merge，所以二者之间进行 OR 运算，自然可能会使用 index union merge算法。

4. index merge 之 sort_union

.(多个条件扫描进行 OR 运算，但是不符合 index union merge算法的，此时可能会使用 sort_union算法)

官方文档给出了两个例子：

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;

但是显然：因为 key_col2 不是复合索引的第一个字段，对它进行 OR 运算，是不可能使用到索引的。所以这两个例子应该也是错误的，它们实际上并不会进行 index sort_union merge算法。

5. index merge的局限

(x AND y) OR z = (x OR z) AND (y OR z)

(x OR y) AND z = (x AND z) OR (y AND z)

如果我们的条件比较复杂，用到多个 and / or 条件运算，而MySQL没有使用最优的执行计划，那么可以使用上面的两个等式将条件进行转换一下。