MYSQL索引优化与查询优化

索引优化与查询优化

查询数据占总数据 30% 则MYSQL不会再使用索引。因为使用索引的开销反而更大。

也可以使用 force index(age) 让MySQL强行使用索引查询

explain SELECT * FROM tb_user_copy1 force index(age) where age BETWEEN 1 and 4

前言：

都是有哪些维度可以进行数据库调优？简言之：

  • 索引失效，没有充分利用到索引 -- 索引优化
  • 关联查询太多 JOIN （设计缺陷或不得已的需求） -- SQL 优化
  • 服务器调优及各个参数设置（缓冲，线程数等） -- 调整my.cnf
  • 数据过多 -- 分库分表

关于数据库调优的知识点非常分散。不同 DBMS（数据库管理系统），不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。主要分为三个章节讲解。

虽然 SQL 查询优化技术有很多，但是大方向上完全可以分成 物理查询优化 和 逻辑查询优化 两大块。

  • 物理查询优化：通过 索引 和 表连接方式 等技术进行优化，这里重点需要掌握索引的使用。
  • 逻辑查询优化：通过 SQL 等价变换 提升查询效率，直白一点就是说，换一种查询写法 执行效率可能更高。

1. 数据准备

呵呵哒， 自己想办法吧！ sql 之命由天（查询优化器），不由你。

2. 索引失效案例

MySQL 中 提高性能 的一个最有效的方式是对数据表 创建合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

使用索引可以 "快速地定位" 表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。

  • 如果查询时，没有使用索引 查询语句就会 扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。
  • 大多数情况下都（默认）采用 B+树 来构建索引（innodb存储引擎）。只是空间列类型的索引使用 R-树，并且 MEMORY 表还支持 hash索引。

（天坑呀！）其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？

​ 查询优化器是基于 cost开销（CostBaseOptimizer），它不基于 规则（Rule-BasedOptimizer），也不基于 语义。怎么样开销小就怎么来。另外，SQL 语句是否使用索引，跟数据库版本，数据量，数据选择度都有关系。

注意：这里的 "cost开销" 指的不是 "时间"，而是 "读取数据时的 I/O 和 CPU 的开销"。

2.1 全值匹配我最爱

  • 没有索引时使用的时间
SELECT * FROM base_obj_xld WHERE obj_code = 'xld'; # 0.009
SELECT * FROM base_obj_xld WHERE obj_code = 'xld' AND OBJ_TYPE = 1000; # 0.019
  • 创建 "单列索引"
# 创建索引
CREATE INDEX index_obj_code ON base_obj_xld(obj_code);
# 再次查询
SELECT * FROM base_obj_xld WHERE obj_code = 'xld'; # 0.004
# 注意：这时我们使用到了刚刚创建的索引 index_obj_code（单列索引）
EXPLAIN SELECT * FROM base_obj_xld WHERE obj_code = 'xld';
  • 创建 "联合索引"
# 创建联合索引
CREATE INDEX index_obj_code_tyoe ON base_obj_xld(obj_code,OBJ_TYPE);
# 再次查询
SELECT * FROM base_obj_xld WHERE obj_code = 'xld' AND OBJ_TYPE = 1000; # 0.001
# 注意：这是使用的索引就是 index_obj_code_tyoe（联合索引）
EXPLAIN SELECT * FROM base_obj_xld WHERE obj_code = 'xld' AND OBJ_TYPE = 1000;

这里的 "全值匹配"：指的是查询语句中的过滤字段，能匹配上相应的 "索引（联合索引）"。当查询语句中的所有的过滤字段和索引字段全部都匹配的时候，可以大大的提升查询的效率。

2.2 最佳左前缀法则

在 MySQL 建立联合索引 必须遵守 最佳左前缀匹配原则，即 最左优先，在检索数据时从 联合索引 的最左边开始匹配。

  • 举例1：已 index_obj_code_tyoe 联合索引为例·
# 创建的索引（联合索引）
CREATE INDEX index_obj_code_tyoe ON base_obj_xld(obj_code,OBJ_TYPE);
# 分析 sql 语句
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_TYPE = 1000 AND OBJ_NAME = 'xld';

注意：以上的 sql 语句，无法使用到 索引（联合索引），因为可以看到 index_obj_code_tyoe 最左边的字段是 "obj_code"，而该 sql 语句中 没有使用到该字段 "obj_code"。

  • 举例2：已 index_obj_code_tyoe 联合索引为例
# 分析 sql 语句
EXPLAIN SELECT * FROM base_obj_xld WHERE obj_code = 'xld' AND OBJ_TYPE = 1000 AND OBJ_NAME = 'xld';

此时，该 sql 语句，就 使用上了 索引（联合索引），因为上述中的 sql 语句 使用了 "obj_code" 字段作为过滤条件。

结论：

  • MySQL 可以为多个字段创建索引，一个索引可以包括 16 个字段。
  • 对于多列索引，过滤条件要想使用索引必须按照建立索引时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
  • 如果 查询条件中没有使用到 "联合索引" 字段中的第 1 个字段时，多列（联合）索引不会被使用（索引失效）。

说明：最佳左前缀原则，指的 "联合索引" 中字段的最左边，而 不是查询语句中的最左原则。只要 查询语句中使用了 "联合索引" 字段中的第 1 个字段（和顺序无关），就可以使用该索引。

拓展：Alibaba《java开发手册》

索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引

2.3 主键插入顺序 - （感觉和索引失效没啥关系）

对于一个使用 InnoDB 存储引擎的表来说，在没有显式的创建索引时，表中的数据实际上都是存储在 聚簇索引 的叶子节点中。而 记录又是存储在数据页中 的，数据页和记录又是按照记录 主键值从小到大 的顺序进行排序。

  • 如果我们 插入 的记录是 主键值依次增大 的话，那我们每插满一个数据页就换到下一个数据页继续插（这是最好的，不会损耗性能）
  • 如果我们 插入 的记录是 主键值忽大忽小 的话，就比较麻烦了。例如：某个数据页存储的记录已经满了，它存储的主键值在 1 ~ 100 之间：

这是这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录位移就意味着：性能损耗！所以如果我们想尽量避免这样无谓的性能损耗，最好是让插入的记录是 主键值依次递增。

建议：让（非业务系统表）主键具有 auto_increment，让存储引擎为表成主键（自动递增的）。

2.4 计算，函数，类型转换（自动或手动）导致索引失效

  • 创建索引
ALTER TABLE base_obj_xld ADD INDEX index_obj_code(obj_code);

  • 函数导致索引失效

    • 索引失效
    # 以后我再这样写 剁手
EXPLAIN SELECT * FROM base_obj_xld where LEFT(OBJ_CODE,3) = 'xld';
    • 索引优化生效
    # 这样开心的写 sql 
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_CODE LIKE 'xld%';

  • 计算导致索引失效

    • 索引失效
    # 字段计算，会导致索引失效。不要问为什么，我要是索引，你这样写我也不干
EXPLAIN SELECT * FROM base_obj_xld WHERE obj_type+1 = 10000;
    • 索引优化生效
    EXPLAIN SELECT * FROM base_obj_xld WHERE obj_type = 10000-1;

2.5 类型转换导致索引失效

  • 创建索引
CREATE INDEX index_obj_name ON base_obj_xld(OBJ_NAME); # 索引使用的字段为 字符串类型
  • 索引失效
# 隐式自动转换数据类型
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_NAME = 123;
# 显式手动转换数据类型
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_NAME = CAST('123' AS signed);
  • 索引优化生效
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_NAME = '123';

结论：设计的 实体类属性，一定要与数据字段类型相对应。否则，就会出现类型转换的情况。

注意：在索引时，必须保证 "索引字段" 和 "查询条件" 的数据类型必须一致。

2.6 范围条件右边的列索引失效（部分索引失效）

  • 创建索引（联合索引）
ALTER TABLE base_obj_xld ADD INDEX index_obj_code_type_name(OBJ_CODE,OBJ_TYPE,OBJ_NAME);
  • 范围条件右边的列 - 索引失效
# 此时，我们只使用了联合索引中的 obj_code 和 obj_type 字段，而 obj_name 失效了！！！
EXPLAIN SELECT * FROM base_obj_xld WHERE obj_code = 'xld' AND obj_type > 1000 AND obj_name = 'xld';

此时，我们只使用了联合索引中的 "obj_code" 和 "obj_type" 字段，而 "obj_name" 失效了！！！

因为，在建立索引时 "obj_type" 字段在 "obj_name" 字段前面，而 "obj_type" 字段在 "查询语句" 中是范围查询，所以 "obj_name" 就失效了！！！

  • 范围条件查询 - 索引优化生效

如果想 使用到 "联合索引" 中全部的字段，必须将 "查询语句" 中的范围查询条件字段，放置 "联合索引" 最右边（最后面）

建立有效索引：

ALTER TABLE base_obj_xld ADD INDEX index_obj_code_name_type(OBJ_CODE,OBJ_NAME,OBJ_TYPE);

使用上述 "查询语句" 再次查询：

# 此时，就使用到 "联合索引：index_obj_code_name_type" 中的全部字段了。开心...
EXPLAIN SELECT * FROM base_obj_xld WHERE obj_code = 'xld' AND obj_type > 1000 AND obj_name = 'xld';

在重新建立索引时，将 "obj_type" 字段放置了 "联合索引" 的最右边。此时的 "查询语句" 就能使用到 "索引" 中全部的字段了。

注意：我们可以通过 调整 "联合索引" 中的字段顺序，来控制 "查询语句" 所使用索引字段。

实际开发建议：

在应用开发中，应将范围查询条件放置 where 语句最后。在创建 "联合索引" 时，必须把范围涉及到的字段写在最后面（最右边）。

2.7 不等于（！= 或者 <>）索引失效

  • 创建索引
CREATE INDEX index_obj_code ON base_obj_xld(obj_code);
  • 查询条件不等于 - 索引失效
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_CODE != 'xld';
# 或者
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_CODE <> 'xld';

2.8 is null 可以使用索引，（is not null / not like） 无法使用索引

  • 创建索引
CREATE INDEX index_obj_code ON base_obj_xld(obj_code);
  • is null 使用索引
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_CODE IS NULL;
  • is not null 索引失效
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_CODE IS NOT NULL;
  • not like 索引失效
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_CODE NOT LIKE 'xld%';

结论：

最好在设计数据表的时候就 将字段设置为 not null 约束,并且 给字段设置默认值。比如：int 类型的字段，默认值：0，字符串类型字段，默认值：('')

2.9 like 以通配符 "%" 开头索引失效

在使用 like 关键字进行查询的查询语句中，如果 匹配字符串的第一个字符为 "%"，索引就不会起作用。只有 "%" 不在第一个位置，索引才会起作用

  • 创建索引
CREATE INDEX index_obj_name ON base_obj_xld(obj_name);
  • 使用到索引
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_NAME LIKE 'xld%';

此时，like 模糊条件中第一个位置的字符串不是 "%"，索引生效。

  • 未使用到索引
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_NAME LIKE '%xld%';

此时，like 模糊条件中第一个位置的字符串是 "%"，则索引不生效。

说一说个人的理解：就好比说，有人给你说有近路，但是不告诉你怎么走。你说气人不气人！！！

个人理解：

当 like 的模糊条件中 第一个位置的字符串是 "%" 时，如果使用索引的话，会扫描两次索引，第一次会扫描全部的索引，然后再回表扫描聚簇索引，及其耗费性能。不管怎么样都是要扫描全表，那还是不如直接扫描表来的快。

拓展：Alibab《java开发手册》

【强制】页面搜索严禁 "左模糊" 或者 "全模糊"，如果需要请使用搜索引擎解决。

2.10 OR 前后存在非索引的列，索引失效

在 where 子句中，如果在 or 前的条件列使用了索引，而在 or 后的 条件列没有使用索引，那么 索引会失效。也就是说，or 前后的两个条件中的列都是索引时，查询中才使用索引。

以为 or 的含义就是两个只要满足一个即可，因此，or 前后只有一个条件列使用了索引是没有意义的，只要有一个条件列没有使用索引，就会进行 全表扫描，因此 所有的索引列也会失效。

  • 创建索引（单列索引）
CREATE INDEX index_obj_name ON base_obj_xld(OBJ_NAME);
  • or 关键字索引失效
# 此时 只有一个 obj_name 索引，所以无法使用到 索引
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_CODE = 'xld' OR obj_name = 'xld';

此时，只有一个 "obj_name" 列的索引，而 or 需要前后列都要有索引，所以此时无法使用到 索引。

  • or 关键字索引优化生效

根据上述 "查询语句" 如果想 使用到 "索引"的话，就需要 再建立一个 "索引"。

创建索引：

# 针对上述 "查询语句" 建立优化索引
CREATE INDEX index_obj_code ON base_obj_xld(obj_code);

使用上述 "查询语句" 再次查询：

# 此时就使用了索引，注意：是合并索引哦！
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_CODE = 'xld' OR obj_name = 'xld';

此时，当 or 前后的两个条件中的列都是索引时，索引生效（使用的索引合并）。

思考一下：

在 or 的条件中使用 "联合索引" ，结果是什么样子的呢？

答：也需要两个索引（联合索引），且 索引中的 最左必须是 or 前后条件中的列。

注意：or 关键字，只能使用到 "联合索引" 中的第一个字段，其余字段不会使用，针对 or 关键字进行查询优化，要根据实际情况来。

  • 哈哈哈 - 经过多次测试发现的哦：

创建的索引：

KEY `index_obj_type_code_name` (`OBJ_TYPE`,`OBJ_CODE`,`OBJ_NAME`),
KEY `index_obj_name` (`OBJ_NAME`)

用到了索引

# 此时，用到的索引是 index_obj_type_code_name(联合索引),index_obj_name（单列索引）
EXPLAIN SELECT * FROM base_obj_xld WHERE obj_type = 10000 AND OBJ_CODE = 'xld' OR obj_name = 'xld' ;

没有用到索引（索引失效）

# 此时，一个索引都没有用到
EXPLAIN SELECT * FROM base_obj_xld WHERE obj_type = 10000 AND obj_name = 'xld' OR OBJ_CODE = 'xld';

结论：

  1. 有 or 关键字的 "查询语句"，如果想使用 "索引"，则 必须有多个 "索引"
  2. 我们 可以将 or 关键字看做为 "索引（联合索引）" 分割符，or 前面的所有条件列，看做一个 "索引（联合索引）"。or 后面的所有条件列，看做一个"索引（联合索引）"。
  3. or 关键字使用到 "索引" 数为：（or的个数 + 1）个索引

2.11 字符集不同，导致索引失效。（统一数据库和表的字符集）

统一使用 utf8mb4（5.5.3版本以上支持）兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 在进行比较前需要进行 转换 ，则 会导致索引失效。

那么问题来了：发生了字符集转换 "索引" 为什么会失效呢？

首先是我们要知道 字符集转换是通过什么方式转换的。答案就是：转换函数。我们之前曾说过当 "查询语句" 中使用了函数时，索引就会失效！！！

2.12 练习 与 一般性建议

  • 练习：假设（联合索引）：index（a,b,c）
where语句 索引使用情况
where a =3 索引生效，会使用索引中的 a 列
where a = 3 and b = 5 索引生效，会使用索引中的 a 和 b 两个列
where a = 3 and b = 5 and c = 4 索引生效，会使用索引中的全部列
where b =3 或者 where b = 3 and c = 4 或者 where c = 4 索引失效
where a = 3 and c = 5 索引生效，会使用索引中的 a 列
where a = 3 and b > 4 and c = 5 索引生效，会使用索引中的 a 和 b 两个列，c 列失效
where a is null and b is not null 索引生效，会使用索引中的 a 列
where a <> 3 索引失效
where abs(a) = 3 索引失效
where a = 3 and b like 'kk%' and c = 4 索引生效，会使用索引中的全部列
where a = 3 and b like '%kk' and c = 4 索引生效，会使用索引中的 a 列
where a = 3 and b like ’%kk%‘ and c = 4 索引失效，会使用索引中的 a 列
where a = 3 and b like 'k%kk%' and c = 4 索引生效，会使用索引中的全部列
  • 一般性建议：
    • 对于单列索引，尽量 选择针对当前 query(查询语句) 过滤性更好的索引。
    • 在选择联合索引的时候，当前 query(查询语句) 中过滤性最好的字段在索引字段顺序中，位置越靠前越好
    • 在选择联合索引的时候，尽量选择能够包含当前 query(查询语句) 中的 where 字子句中更多字段的索引
    • 在选择联合索引的时候，如果 某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
  • 总之一句话，编写 SQL 语句时，尽量避免造成索引失效的情况。

3. 多表关联查询优化

  • 重要概念：在进行多表查询的时候，其实底层是一个嵌套循环（n张表 - 1）层循环。
  • 千万，千万，千万注意：多表之间的 关联字段的数据类型必须，必须，必须一致。如果 不一致就会出现隐式的数据类型转换随之 "索引失效"。

举例（没有索引）：a 表（驱动表）有 10 条记录，b 表（被驱动表）有 100 条记录

SELECT * FROM a INNER JOIN b ON a.id = b.a_id;

**此时的 "多表查询语句" 就是一个 1层嵌套循环，执行的次数为 ：（10 * 100 = 1000） 次 **

3.1 外（左，右）连接

驱动表说明：在外（左，右）连接中，那张表在（左，右）边，那张表就是 "驱动表"。其他表为 "被驱动表"。

  • 创建索引：为被驱动表（关联字段）创建索引
ALTER TABLE data_minute_history_xld ADD INDEX index_obj_code(obj_code);

外（左，右）连接，本身 就是要（左，右）表中的全部数据。所以，我们 只需为 "被驱动表" 中的关联字段 创建索引即可。

  • 索引优化生效
EXPLAIN SELECT * FROM base_obj_xld bo LEFT JOIN data_minute_history_xld dm ON bo.OBJ_CODE = dm.OBJ_CODE;

3.2 内连接

驱动表说明：对于内连接来说，由查询优化器决定谁做为 "驱动表"，谁作为 "被驱动表"。

  • 创建索引：为 "关联字段" 创建索引
ALTER TABLE data_minute_history_xld dm ADD INDEX index_obj_code(dm.obj_code);

内连接，没有（左，右）之分。所以，我们 为那张表创建索引都可以。

  • 查询语句
EXPLAIN SELECT * FROM base_obj_xld bo INNER JOIN data_minute_history_xld dm ON bo.OBJ_CODE = dm.OBJ_CODE;

此时的 "驱动表" 为：bo，"被驱动表" 为：dm

  • 删除 dm 表中的索引，为 bo 创建索引
# 删除 dm 索引
DROP INDEX index_obj_code ON data_minute_history_xld;
# 为 bo 创建索引
ALTER TABLE base_obj_xld ADD INDEX index_obj_code(obj_code);
  • 再次查询
EXPLAIN SELECT * FROM base_obj_xld bo INNER JOIN data_minute_history_xld dm ON bo.OBJ_CODE = dm.OBJ_CODE;

此时的 "驱动表" 为：dm，"被驱动表" 为：do

重要结论：对于内连接来说

  • 如果 表的连接条件中只能有一个字段有索引，则该 索引的字段所在的表会被作为 "被驱动表"。
  • 在 两个表的连接条件都存在索引的情况下，会 选择小表作为 "驱动表"（小表驱动大表）。

3.3 join 语句原理

join 方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5 版本之前，MySQL 只支持一种表间关联方式，就是 嵌套循环（Nested Loop Join）。如果关联表的数据量很大，则 join 关联的执行时间会非常长。在 MySQL5.5 以后的版本中，MySQL 通过引入 BNLJ 算法来优化嵌套执行。

3.3.1 驱动表（主表） 和 被驱动表（从表）

说明："驱动表" 和 "被驱动表"，是从 查询优化器优化后，通过 explain 分析工具 "分析的结果" 中看到的（谁在前面谁就是 "驱动表"）。

  • 内连接：

对于内连接来说，查询优化器会根据 "查询语句" 做优化，决定先查那张表。先查询的那张表就是 "驱动表"，反之就是 "被驱动表"。通过 explain 分析工具可以查看。

  • 外连接：

通常，大家会认为，那张表在（左，右）边，那张表就是 "驱动表"，其他表为 "被驱动表"。但是，在有些情况下则不是这样的。

举例：

# 驱动表为 b 被驱动表为 bo
EXPLAIN SELECT * FROM base_obj_xld bo LEFT JOIN base_obj b ON bo.OBJ_CODE = b.OBJ_CODE WHERE bo.OBJ_NAME = b.OBJ_NAME;
# 驱动表为 bo 被驱动表为 b
EXPLAIN SELECT * FROM base_obj_xld bo LEFT JOIN base_obj b ON bo.OBJ_CODE = b.OBJ_CODE  AND bo.OBJ_NAME = b.OBJ_NAME;

个人看法：

可能因为 bo 表是有索引，而 b 表没有索引导致的。不过这情况基本很少见，而且最终的解释权在 "查询优化器" 手中！

通过 explain 查看的结果是：第一句 sql 使用了 bo 表的索引，而第二句 sql 没有使用 bo 表的索引！！！

提示："查询优化器" 在优化没有 where 过滤条件的 "多表查询语句" 时，会在优化后的语句中的末尾加上 "where ture"；

3.3.2 Simple Nested-Loop join（简单 - 嵌套循环连接）

算法相当简单，从表A中取出一条数据 a1，然后遍历表B（全表扫描），将匹配到的数据放到 result... 依次类推，"驱动表A" 中的每一条记录与 "被驱动表B" 的记录进行判断：

可以看到 这种方式效率是非常低的，以上述 "表A" 数据 100条，"表B" 数据 1000 条件计算，则 A表 * B表 = 10万次。开销统计如下：

开销统计 SNLJ（简单 - 嵌套循环连接）
驱动表（外表）扫描次数 1
被驱动表（内表）扫描次数 A：驱动表（A表）where 过滤后的记录数
读取的记录数 A + A * B：驱动表（A表） + 驱动表（A表）where 过滤后的记录数 * 被驱动表（B表）
Join 比较次数 A * B：驱动表（A表）where 过滤后的记录数 被驱动表（B表）*
回表读取记录次数 0（没有使用索引，就没有回表一说）

当然了 MySQL 现在 肯定不会使用这么粗暴的算法来进行多表的连接，所以就出现了后面的两种 针对（简单嵌套循环连接）算法的优化算法。

3.3.3 Index Nested - Loop Join（索引 - 嵌套循环连接）

Index Nested-Loop Join 其优化的思路主要是为了 减少 "被驱动表"（内层表）数据的匹配次数，所以 要求 "被驱动表" 上必须有 "索引" 才行。

通过 "驱动表"（外层表）的匹配条件直接与 "被驱动表"（内层表）的索引进行匹配的方式，从而避免了"被驱动表"（内层表）的 全表扫描，这样就极大的 减少了 "被驱动表"（内层表）的匹配次数。

"驱动表" 中的每条记录通过与 "被驱动表" 的索引进行访问匹配。因为 索引查询的成本是比较固定的，故 MySQL 优化器都倾向与使用记录数少的表作为 "驱动表"（外表）。

开销统计 SNLJ（简单 - 嵌套循环连接） INLJ（索引 - 嵌套循环链接）
驱动表（外表）扫描次数 1 1
被驱动表（内表）扫描次数 "驱动表" where 过滤后的记录数 0
读取的记录数 **"驱动表" + "驱动表" where 过滤后的记录数 * "被驱动表" ** A + B(match)："驱动表" 的全部记录数 + "被驱动表" 匹配的记录数
Join 比较次数 **"驱动表" where 过滤后的记录数 * "被驱动表" ** A * B - index(height)："驱动表" where 过滤后的记录数 * "被驱动表" 的索引 B+ 树的层数
回表读取记录次数 0（没有使用索引，就没有回表一说） B(match)：二级索引："被驱动表" 匹配的记录数。聚簇索引：0

如果 "被驱动表" 加了索引，效率是非常高的，但是如果索引不是 "主键索引"，还需进行 "回表" 查询。相比之下如果是 "主键索引"，那么效率会更高。

3.3.4 Block Nested-Loop Join（块 - 嵌套循环连接）

重要概念：块 - 嵌套循环连接 算法是针对 简单 - 嵌套循环连接 算法的 优化。

如果 "被驱动表" 存在索引，那么 会使用 index（索引嵌套循环连接）的方式进行 join，如果 join 的 列没有索引，"被驱动表" 要扫描的次数就太多了。

  • 简单 - 嵌套循环连接的执行原理：

每次访问 "被驱动表" 时，其 表中的记录都会被加载到内存中，然后从 "驱动表" 中取一条与其匹配，匹配 结束后清除内存。第二次：会再从 "驱动表" 中加载一条记录，然后会 再一次把 "被驱动表" 中的记录加载到内存中与其进行匹配，这样 周而复始，大大增加了 IO 的次数。为了 减少 "被驱动表" 的IO次数，就出现了 块 - 嵌套循环连接 的方式。

  • 块 - 嵌套循环连接的执行原理：

不再是逐条获取 "驱动表" 的数据，而 是一块一块（一次多条记录）的获取，所以 引入了 join buffer 缓冲区，将 "驱动表" join 相关的部分数据列（大小受 join buffer 的限制）缓存到 join buffer 缓冲区中，然后全表扫描 "被驱动表" 中的记录加载到内存中，这样的话 "被驱动表" 中的每一条记录就可以一次性和 join buffer 中的所有 "驱动表" 中的记录进行匹配（都是在内存中操作的哦！）。将 简单 - 嵌套循环中的多次比较合并成一次，降低了 "被驱动表" 的访问频率（IO的次数）。

总结一句话就是：将 一多对 模式，优化为了 多对多 的模式。从而减少加载（IO）的次数

  • 注意：

join buffer 缓冲区中 缓存的不只是关联表的列，是 select ~ from 之间所有的列。

在一个有 N 个 join 关联 的 sql 中会分配 N-1 个join buffer缓冲区。所以 查询的时候尽量减少不必要的字段，可以让 join buffer 缓冲区中可以存放更多的列。

**

开销统计 SNLJ（简单 - 嵌套循环连接） BNLJ（块- 嵌套循环链接）
驱动表（外表）扫描次数 1 1
被驱动表（内表）扫描次数 "驱动表" where 过滤后的记录数 A * column_size /  join_buffer_size + 1："驱动表" where 过滤后的记录数 * 查询语句中列的内存大小 / join buffer 的内存大小 + 1
读取的记录数 **"驱动表" + "驱动表" where 过滤后的记录数 * "被驱动表" ** A + B * (A * column_size / join_buffer_size)："驱动表" + "被驱动表" * （"驱动表" where 过滤后的记录数 * 查询语句中列的内存大小 / join buffer 的内存大小）**
Join 比较次数 **"驱动表" where 过滤后的记录数 * "被驱动表" ** B * A："驱动表" where 过滤后的记录数 * "被驱动表"
回表读取记录次数 0（没有使用索引，就没有回表一说） 0（没有使用索引，就没有回表一说）

补充说明：

"驱动表" where 过滤后的记录数的内存大小 * 查询语句中列的内存大小 / join buffer 的内存大小（A_size * column_size / join_buffer_size） 计算的是 "块" 一次可以存储多少条 “驱动表” 中的数据。

举例：

  • A表 （"驱动表" where 过滤后的记录数）为：1000 条。
  • 查询语句中每列的内存大小为：50 个字节。
  • join buffer 的内存大小为：200 个字节。

"块"（join buffer缓存区） 一次可以存储的条数为：1000 * 50 / 200 = 250 条记录。

需要 1000 / 250 = 4 个 "块" 来完成这次的连接查询：公式为："驱动表" where 过滤后的记录数 / （join buffer缓存区）一次可以存储的条数。

  • （join buffer 缓冲区）参数设置：

    • 查看 join buffer 缓冲区的状态（是否开启，默认开启）
    # 通过 查询 optimizer_switch 系统变量
SHOW VARIABLES LIKE '%optimizer_switch%'
# 查看 block_nested_loop 的状态
    • 设置 join buffer 缓冲区的状态：（这里设置的是 会话级别 的）
    # 将 optimizer_switch 中的 block_nested_loop 的状态设置为 off
SET SESSION optimizer_switch = "block_nested_loop=off";
    • 查看 join buffer 缓冲区的大小（默认为 256K）：join_buffer_size 会话/全局 通用系统变量
    SHOW VARIABLES LIKE '%join_buffer_size%'
# 或者
SELECT [@@session | @@global].join_buffer_size
    • 设置 join buffer 缓冲区的大小：（这里设置的是 会话级别 的）
    SET SESSION join_buffer_size = 362144;

    join_buffer_size 的最大值在 32 位系统可以申请 4G ，而在 64 位操作系统下可以申请大于 4G 的 join Buffer 空间（64位 Windows 除外，其最大值被截断为 4G 并发出警告）。

3.3.5 小结

  1. 整体效率比较：INLJ > BNLJ > SNLJ（索引 > 块 > 简单）嵌套循环查询
  2. 永远用 小结果集驱动大结果集（其本质就是减少外层循环的数据数量）（度量单位指的是：表行数 * 每行记录的内存大小）
 select ti.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id <=100; # 推荐
 
 select ti.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id <=100; # 不推荐
 # 分析：
 # 1.有 straight_join 关键字在，查询优化器不会对 sql 语句进行优化。
 # 2.查询中"t1表"只查询了"b"一个字段，而"t2表"查询了"*"（表中所有的字段）。所以不建议 t2 作为 "驱动表"！！！

扩展：straight_join 关键字，表示 禁止查询优化器对该语句进行优化。

  1. 为 "被驱动表" 匹配的条件增加索引（减少 "被驱动表" 的循环匹配次数）。
  2. 增大 join buffer size 的大小（一次缓存的数据越多，那么扫描 "被驱动表" 的次数就越少）。
  3. 减少 "驱动表" 不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）

3.3.6 Hash Join

从 MySQL 的 8.0.20 版本开始将废弃 BNLJ（块 - 嵌套循环连接），因为从 MySQL 8.0.18 版本开始就加入了 hash join 默认都会使用 hash join。

  • Nested - Loop（循环嵌套）：

对于 被连接的数据子集较小的情况，Nested Loop（嵌套循环） 是个较好的选择。

  • Hash Join 是做 大数据集连接 时的常用方式，优化器使用两个表中 较小（相对较小）的表利用 Join Key 在内存中建立 "散列表"，然后，扫描较大的表并探测 "散列表"，找出与 Hash 表匹配的行。

    • 这种方式 适用于较小的表可以完全放于内存中的情况，这样总成本就是访问 两个表的成本之和。

    • 在 表很大的情况下，是不能完全放入内存，这时优化器会将它（"驱动表"）分割成 若干个不同的分区，不能放入内存的部分就 把该分区写入磁盘的临时段，此时 需要有较大的临时段 从而尽量提高 I/O 的性能（减少 I/O 的次数）。

    • 它（hash join）能够很好的工作没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它（hash join）是 join 的重型升降机。Hash join 只能适用于等值连接（如：where a.col1 = b.col2），这是由 Hash 的特点决定的。

      类别 Nested Loop（嵌套循环） Hash Join（哈希连接）
      使用条件 任何条件 等值连接（=）
      相关资源 cpu，磁盘 I/O 内存，临时空间
      特点 当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果 当没有索引或者索引条件模糊时，Hash join（哈希连接） 比 Nested Loop（嵌套循环） 效率高。在数据仓库环境下，如果表的记录数多，效率高。
      缺点 当没有索引或者查询条件限制不够时，效率很低；当表的记录数多时，效率低。 为建立哈希表，需要大量内存。第一次的结果返回较慢。

3.5 小结

驱动表说明：在决定哪个表做 "驱动表" 的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是 "驱动表"（小表）。

  • 保证 "被驱动表" 的 join 字段已经创建了索引。
  • 需要 join 时关联的字段，数据类型保持绝对一致。
  • 外（左，右）连接时，请自行选择 小表作为 "驱动表"，大表作为 "被驱动表"。减少外层循环的次数。
  • 内连接时，MySQL 会自动将 小结果集的表选为 "驱动表"。请选择相信 MySQL 的优化策略（就算不相信你也没有办法改）。
  • 能够直接 多表关联的尽量直接关联，不要用 "子查询"。
  • 不建议使用 "子查询"，建议将 "子查询" sql 拆开结合程序多次查询，或使用 join 来代替子查询。
  • 衍生表，临时表，派生表，创建不了索引。

4. 子查询优化

MySQL 从 4.1 版本开始支持 "子查询"，使用 "子查询" 可以进行 select 语句的嵌套查询，即一个 select 查询的结果作为另一个 select 语句的条件。"子查询" 可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

"子查询" 是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，"子查询" 的执行效率不高。原因：

  • 执行 "子查询" 时，MySQL 需要为内层查询语句的查询结果 建立一个 "临时表"，然后外层查询语句从 "临时表" 中查询记录。查询完毕后，再撤销这些 "临时表"。这样会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。
  • "子查询" 的结果集存储的 "临时表"，不论是 "内存临时表" 还是 "磁盘临时表" 都不会存在索引 ，所以查询性能会受到一定的影响。
  • 对于返回结果集比较大的 "子查询"，对查询性能的影响也就越大。

必须的建议：

在 MySQL 中，要尽量使用连接（join）查询来替代子查询。因为连接查询 不需要建立临时表，其 速度比子查询要快，如果查询中使用索引的话，性能就会更好。

  • 举例1（in，exists）：

子查询语句：

EXPLAIN SELECT * 
FROM data_minute_history_xld dm 
WHERE dm.OBJ_CODE in 
(SELECT OBJ_CODE FROM base_obj_xld);

"子查询" 转换为 "关联语句"：

EXPLAIN SELECT * FROM data_minute_history_xld dm INNER JOIN base_obj_xld bo ON dm.OBJ_CODE = bo.OBJ_CODE;
  • 举例2（not in，not exists）：

子查询语句：

# not in
EXPLAIN SELECT * 
FROM data_minute_history_xld dm 
WHERE dm.OBJ_CODE not in 
(SELECT OBJ_CODE FROM base_obj_xld);
# not exists
EXPLAIN SELECT * 
FROM data_minute_history_xld dm 
WHERE NOT EXISTS
(SELECT OBJ_CODE = dm.OBJ_CODE FROM base_obj_xld);

"子查询" 转换为 "关联语句"：

# not in / not exists 
EXPLAIN SELECT * FROM data_minute_history_xld dm LEFT JOIN base_obj_xld bo ON dm.OBJ_CODE = bo.OBJ_CODE
WHERE bo.OBJ_CODE IS NULL;

结论：尽量 不要使用 xx not in 或者 not exists，而是 join xxx on xx where xx is null 替代 。

扩展一下：多表关联查询时 "不同类型的记录"

# 左外连接 - 满连接：10958453
SELECT COUNT(*) FROM data_minute_history_xld dm LEFT JOIN base_obj_xld bo ON dm.OBJ_CODE = bo.OBJ_CODE;

# 左外连接 - dm表，独有的记录：6792422
SELECT COUNT(*) FROM data_minute_history_xld dm LEFT JOIN base_obj_xld bo ON dm.OBJ_CODE = bo.OBJ_CODE
WHERE bo.OBJ_CODE IS NULL;

# 左外连接 - 相同的记录：4166031
SELECT COUNT(*) FROM data_minute_history_xld dm LEFT JOIN base_obj_xld bo ON dm.OBJ_CODE = bo.OBJ_CODE
WHERE bo.OBJ_CODE IS NOT NULL;

（外连接 - 独有的记录 + 外连接 - 相同的记录）= 外连接 - 满连接；（6792422+4166031）= 10958453

5. 排序优化

5.1 排序优化

问：在 where 条件字段上加了 "索引"，但是为什么在 order by 字段上还要加 "索引"呢？

答：

where 条件字段上的 "索引" 是为了加快条件查询的，而 order by 字段上的 "索引" 是为了加快排序的。

在 MySQL 中，支持两种排序方式，分别是 FileSort 和 Index 排序。

  • Index 排序中，"索引" 本身就是有序的，不需要再进行排序，效率更高（这就是天生丽质）。
  • FileSort 排序是在 内存中排序的，占用 CPU 较多。如果结果集数据较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率贼低。（后天的努力）

以上的两种排序很好的说明了一个道理：你后天所有努力的结果，和天生丽质相比，只不过是人家的起点罢了。

优化建议：

  • SQL 中，可以在 where 字句和 order by 字句中使用" 索引"， where 字句中使用 "索引" 是为了 避免全表扫描，而 order by 字句中使用 "索引" 是为了 避免使用 FileSort 排序。但是，有些情况下使用 "全表扫描" 和 "FileSort 排序"，也不一定比 "索引" 慢。
  • 尽量使用 Index 完成 order by 排序。如果 where 和 order by 后面的列（字段）是相同的，那么就使用 "单列索引"；如果不同就使用 "联合索引"（where 后面的字段在前，order by 的在后）。
  • 无法使用 Index 时，需要对 FileSort 方式进行调优。

5.2 案例（测试）

  • 创建索引（联合索引）
ALTER TABLE base_obj_xld ADD INDEX index_obj_code_type(obj_code,obj_type)
  • order by 时不使用 limit，索引失效。
# 排序索引失效
EXPLAIN SELECT * FROM base_obj_xld ORDER BY obj_code,obj_type;
# 排序索引生效
EXPLAIN SELECT * FROM base_obj_xld ORDER BY obj_code,obj_type LIMIT 10;

为什么会这样呢？

答：嘿嘿...，

提示：回表。

  • order by 时顺序错误，索引失效：（使用的还是这个 index_obj_code_type 索引）

索引失效：

# 索引失效 顺序乱了
EXPLAIN SELECT * FROM base_obj_xld ORDER BY obj_type,obj_code LIMIT 10;
# 索引失效 位遵循 最佳左前缀原则
EXPLAIN SELECT * FROM base_obj_xld ORDER BY obj_type LIMIT 10;
# 索引失效 该字段都没有索引
EXPLAIN SELECT * FROM base_obj_xld ORDER BY obj_name LIMIT 10;

索引生效：

# 索引生效 
EXPLAIN SELECT * FROM base_obj_xld ORDER BY obj_code LIMIT 10;
# 索引生效 
EXPLAIN SELECT * FROM base_obj_xld ORDER BY obj_code,obj_type LIMIT 10;
  • order by 时规则不一致，索引失效（顺序错：不索引；排序的方向不一致：不索引）：（使用的还是这个 index_obj_code_type 索引）

索引失效：

# 索引失效 obj_code 的方向反了
EXPLAIN SELECT * FROM base_obj_xld ORDER BY obj_code DESC,obj_type ASC LIMIT 10;
# 索引失效 obj_type 的方向反了
EXPLAIN SELECT * FROM base_obj_xld ORDER BY obj_code ASC,obj_type DESC LIMIT 10;
# 索引失效 排序方向不一致，但是字段的顺序错了
EXPLAIN SELECT * FROM base_obj_xld ORDER BY obj_type DESC,obj_code DESC LIMIT 10;

索引生效：

# 索引生效 索引字段的顺序对了，排序方向一致
EXPLAIN SELECT * FROM base_obj_xld ORDER BY obj_code DESC,obj_type DESC LIMIT 10;
EXPLAIN SELECT * FROM base_obj_xld ORDER BY obj_code ASC,obj_type ASC LIMIT 10;
  • 无过滤，不索引
# 索引生效 ，过滤中遵循了 最佳左前缀原则
EXPLAIN SELECT * FROM base_obj_xld  where obj_code = '1000' ORDER BY obj_type;

5.2 小结

小结：

  • 排序时必须遵守 "最佳左前缀原则"。（注意：在编写 sql 语句时，必须将 "联合索引" 中的第一个字段，写在 order by 的最前面）

  • 如果 where 时遵循了 "最佳左前缀原则"，则 order by 能使用 "索引"

  • 排序是不能使用 "索引" 的情况

  • 排序时，字段的排序方向不一致

  • 排序时，没有遵循 "最佳左前缀原则"

  • 排序时，跳过了 "联合索引" 中的中间字段

  • 排序时，使用了不是 "索引" 的字段

  • 所有的排序都是在查询条件过滤之后才执行的。所以，当查询条件可以过滤掉大部分数据时，排序时 - 使用不使用 "索引" 其实对性能不会太大的影响。

  • 创建索引时，当【范围条件】和【group by 和 order by】的字段出现二选一时，优先观察【范围条件】字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先选择【范围条件】字段。反之，亦然。

5.3 filesort 算法：双路排序 和 单路排序

排序的字段若如果不在索引上，则 filesort 会有两种算法：双路排序 和 单路排序

双路排序（慢）：

  • MySQL 4.1 之前是使用 "双路排序"，字面意思 就是两次扫描磁盘，最终得到数据。读取行指针和 order by 的列，对他们进行排序，然后再扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
  • 先从磁盘取排序字段，在 sort_buffer 进行排序，然后再从 磁盘中取去其他字段。

单路排序（快）：

字面意思 就是扫描一次磁盘，最终得到数据。从磁盘读取查询的需求的 所有列，按照 order by 的列在 sort_buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了二次读取数据。并且 把随机IO变成顺序IO，但是它会使用更多的内存空间，因为它把所有列都加载到内存中了。

结论及引申的问题：

  • 由于单路是后出的，总体而言好过双路
  • 但是用单路有问题
    • 在 sort_buffer 中，单路比多路要 "多占用很多空间"，因为单路是把所有字段都取出，所以有可能 取出的数据的总大小超出了 sort_buffer 的容量，导致 每次只能取 sort_buffer  容量大小的数据，进行排序（创建临时文件，多路合并），排完了再取，再排再取... 从而多次 I/O。
    • 单路本身就是想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略：

  1. 尝试提高 sort_buffer_size

说明：不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为 这个参数是针对每个进程的只能在1MB ~ 8MB 之间调整。InnoDB 存储引擎默认值为：1MB。

  • 查看 sort_buffer_size 大小（会话 / 全局的系统变量）
SHOW VARIABLES LIKE '%sort_buffer_size%';
# 或者
SELECT @@session.sort_buffer_size; # 会话
SELECT @@global.sort_buffer_size; # 全局
  • 设置 sort_buffer_size（会话级别）
SET SESSION sort_buffer_size = 1048576; # 设置为 1MB
  1. 尝试提高 max_length_for_sort_data

说明：提高这个参数，会增加用改进算法的概率。

  • 查看 max_length_for_sort_data 大小，默认：4096 字节（会话 / 全局的系统变量）
SHOW VARIABLES LIKE '%max_length_for_sort_data%';
# 或者
SELECT @@session.max_length_for_sort_data; # 会话
SELECT @@global.max_length_for_sort_data; # 全局
  • 设置 max_length_for_sort_data（会话级别）
SET SESSION max_length_for_sort_data = 6144;

变量值设置建议：如果 该变量设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘I/O和低的 cpu 处理器使用率。如果需要返回的列的总长度大于 max_length_for_sort_data，使用双路算法，否则使用单路算法。建议在 1024 ~ 8192 字节之间调整。（"查询的字段" 总长度大于该变量的值时，选择双路算法，反之使用单路算法）

实际开发建议：（有 order by 时，使用 select * 是大忌。最好是只查询需要的字段）

  • 当 "查询的字段" 大小总和小于 max_length_for_sort_data，而且 排序字段不是 text|blob 类型时，会用改进行后的算法 -- 单路排序，否则用老算法 -- 多路排序
  • 两种算法的数据都有可能超出 sort_buffer_size 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，但是用单路排序算法的风险会更大一些，所以要 提高sort_buffer_size。

6. group by 优化

  • group by 使用索引的原则几乎跟 order by 一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。
  • group by 先排序再分组，遵照 "索引" 建的 "最佳左前缀法则"。
  • 当无法使用 "索引" 时，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置。
  • where 效率高于 having，能写在 where 限定的条件就不要写在 having 中了。
  • 减少使用 order by，和业务沟通能不排序就不排序，尽量将排序放到程序端去做。order by，group by，distinct 这些语句较为耗费 cpu 的。
  • 包含了 order by，group by，distinct 这些查询的语句，where 条件过滤出来的结果集请保持在 1000 行以内，否则 SQL 会很慢。

7. 优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能，一个常见又非常头疼的问题就是 limit 2000000,10，此时需要 MySQL 排序前 2000010 记录，仅仅返回 2000000 ~ 2000010 的记录，其他记录不用，查询排序的代价非常大。

SELECT SQL_NO_CACHE * FROM data_minute_history_xld LIMIT 2000000,10; #耗时 6.019秒
# 
EXPLAIN SELECT * FROM data_minute_history_xld LIMIT 2000000,10;

说明： SQL_NO_CACHE 表示不从查询缓存中查询记录。

  • 优化思路一：（尽量使用 "索引"）

在 "索引"上完成排序分页操作，最后根据 "索引" 关联回原表查询所需要的其它列内容。

EXPLAIN SELECT dm.* FROM 
	data_minute_history_xld dm 
LEFT JOIN 
	(SELECT id FROM data_minute_history_xld ORDER BY id LIMIT 2000000,10) dmi
ON dm.id = dmi.id;
  • 优化思路二：

该方案 适用于主键自增的表，可以把 limit 查询转换成某个位置的查询。

EXPLAIN SELECT * FROM data_minute_history_xld WHERE id > 2000000 LIMIT 10;

8.优先考虑覆盖索引

8.1 什么是覆盖索引？

  • 当一个 "索引" 中包含了 "满足查询结果的数据" 就叫做 "覆盖索引"。

"索引" 是高效找到记录的一个方法，同时，"索引" 也可以获取到 "一列" 或 "多列" 的数据。如果可以通过读取 "索引" 就可以得到想要的数据，那就不需要读取一整行了（也就是说 就不需要 "回表" 操作了）

  • 非聚簇复合索引 的一种形式

它包括在 查询里的 select，join 和 where 字句用到的所有列（即建 "索引" 的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是：索引列 + 主键 包含了 select ~ from 之间查询的所有列。

  • 创建索引（联合索引）
CREATE INDEX index_obj_code_name_type ON base_obj_xld(obj_code,obj_name,obj_type);
  • 覆盖索引 - 失效
EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_CODE = 'xld';
# 查询的是 * 无法使用覆盖索引

EXPLAIN SELECT obj_code,obj_name,obj_type,OBJ_FULL_NAME FROM base_obj_xld WHERE OBJ_CODE = 'xld';
# 查询的字段 "OBJ_FULL_NAME" 不是索引字段，无法使用覆盖索引

  • 覆盖索引 - 生效（查询的字段 都是索引字段，可以使用覆盖索引）

    EXPLAIN SELECT obj_code,obj_name,obj_type FROM base_obj_xld WHERE OBJ_CODE = 'xld';
    • 在可以使用覆盖索引的情况下 - 使用不等于 - "索引" 是生效的
    EXPLAIN SELECT obj_code,obj_name,obj_type FROM base_obj_xld WHERE OBJ_CODE != 'xld';
    • 在可以使用覆盖索引的情况下 - like 以通配符 "%" 开头 - "索引"是生效的
    EXPLAIN SELECT obj_code,obj_name,obj_type FROM base_obj_xld WHERE OBJ_CODE LIKE '%xld';

8.2 覆盖索引的利弊

8.2.1 好处：

  • 避免的 Innodb 表进行 "索引" 后的 "回表" 操作

Innodb 是以 "聚簇索引" 的顺序来存储的，对于 Innodb 来说，"二级索引" 在叶子节点中所保存的是记录的 "主键信息"，如果是用 "二级索引" 查询数据，在查找到相应的 "主键信息" 后，还需通过 "主键信息" 进行 "回表" 操作，获取真实需要的数据。

在 "覆盖索引" 中， "二级索引" 可以获取到我们所要的数据，就避免了 "回表" 操作，减少了 IO 操作，提升了查询效率。

  • 可以 把随机IO 变成 顺序IO 加快查询效率

由于 "覆盖索引" 是按照一定的顺序存储的，在IO密集型的范围查找中，要比随机从磁盘读取每一行的数据IO要少的多，因此在查询时使用 "覆盖索引" 可以把磁盘的 随机读取的IO 转变成"索引查询"的 顺序IO。

  • 由于 "覆盖索引" 可以减少 B+ 树的搜索次数，显著的提升了查询性能，所以使用 "覆盖索引" 是一个常用的性能优化手段。

8.2.2 弊端：

  • 索引字段的维护 总是有代价的。因此，在建立冗余"索引"来支持 "覆盖索引" 是就需要权衡考虑了。
  • 对 sql 语句有了极高要求，如果 sql 语句不规范，导致 "覆盖索引" 失效，此时你还建立了冗余的"索引"，得不偿失呀！

个人建议：

对于不经常 "增-删-修" 且数据量贼大的表来说，可以建立相应的 "覆盖索引 " 来优化查询的效率。

例如：地图中的管线，管段，管点表，物联网中的 "位号表" 以及常用系统表：地理区域表，系统字典表，新增多修改少的大数据量表等...

9. 如何给字符串添加索引 - 前缀索引

9.1 前缀索引

  • 什么是前缀索引？

前缀索引就是 在创建字符类型的索引时，使用该字段一定的长度而创建的 "索引"（一颗B+树）

  • 创建前缀索引
ALTER TABLE base_obj_xld ADD INDEX index_obj_code(obj_code(100));
  • 使用前缀索引
EXPLAIN SELECT * FROM base_obj_xld WHERE obj_code = 'xld';
# 此时，使用了前缀索引 使用前缀索引的 长度为：303 = 100 * 3个字节 + (2个可变字符串的字节 + 1个是null的字节)

注意：

  • 使用 "前缀索引" 可以大幅度的减少 "索引" 占用的存储空间。但是，由于 "索引" 文件中存储的数据较少，可能 会增加 "回表" 的次数。
  • 所以，选择合适的的（前缀索引）长度 就尤为的重要，既可以节省空间，又不会增加额外的查询成本。

那么如何确定 "前缀索引" 的长度呢？

答：可以通过计算字段的 "区分度" 的方式选择合适的长度（值越大区分度越好）

  • 区分度计算公式：select count(distinct left(字段,长度))
  • 例如：
SELECT  
	COUNT(DISTINCT LEFT(OBJ_CODE,50)), 
	COUNT(DISTINCT LEFT(OBJ_CODE,60)), 
	COUNT(DISTINCT LEFT(OBJ_CODE,70)), 
	COUNT(DISTINCT LEFT(OBJ_CODE,80))
FROM base_obj_xld;

9.2 前缀索引对覆盖索引的影响

# 无法使用 覆盖索引
EXPLAIN SELECT obj_code FROM base_obj_xld ;

"前缀索引" 只是使用了字段数据中的一部分，是无法作为整个列的数据的。"前缀索引" 中的数据不是完整的。

9.3 其它方式：倒序存储 / 使用 hash 字段

  • 倒序存储

例如：手机号码前面的很多位都是为了区分号码归属地的，所以如果对电话号码设置 "前缀索引"，就 "会多次回表"。解决这种问题的思路是，使用 "倒序存储"。
将电话号码倒序存储（将字段 "倒序存储"，通过改变字符串的顺序增大 "区分度"）。

EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_CODE = REVERSE('xld');

该方式需要考虑到使用 reverse 函数的消耗。

  • 使用hash字段

例如：业务中需要使用身份证号，且会有与之相关的大量查询，你可以在表中添加一个（身份证号的 hash值）字段，并建立索引；

在 MySQL 中，crc 函数 可以进行哈希计算，我们要在查询和数据插入的时候使用到它。另外，考虑到哈希冲突，在查询语句中需要添加判断。具体查询语句如下

EXPLAIN SELECT * FROM base_obj_xld WHERE OBJ_CODE_HASH = CRC32('xld') AND obj_code = 'xld';

这样，索引的长度变为 4 个字节，小了很多。

9.4 倒序存储 和 hash 字段的异同

相同：

  • 都无法支持范围查询。

差异：

  • 哈希方法会额外添加一个字段，而 "倒序存储" 不需要（当然，你需执行字符串翻转这样的骚操作）
  • 两者都会对 CPU 产生消耗，但是具体来看，reverse 函数的消耗比 crc 更小。
  • 使用哈希的查询性能更加稳定，而 "倒序存储" 依然可能出现后缀大量重复的情况 。

9.5 总结：

  • 直接创建 "完整索引"，比较占用空间。
  • 创建 "前缀索引"，节省空间，但是会增加查询次数，无法使用覆盖索引的优化。
  • "倒序存储"，对字符倒序存储，然后再使用 "前缀索引"，绕过前缀 "区分度过低" 的问题。
  • 创建哈希字段索引，查询性能稳定，需要额外消耗，不支持范围扫描。

10. 索引条件下推（ICP）

10.1 使用前后对比

Index Condition Pushdown（ICP）是 MySQL 5.6 中新特性，是一种在存储引擎层使用索引过滤数据的优化方式。

  • 如果 没有使用 ICP，存储引擎会遍历索引以定位基表（聚簇索引）中的行，并将它们返回给 MySQL 服务器，由 MySQL 服务器评估 where 后面的条件是否保留行。
  • 启用 ICP 后，如果部分 where 条件可以使用索引中的列进行筛选，则 MySQL 服务器会把这部分 where 条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从基表（聚簇索引）中读取行。
    • 好处：ICP 可以减少存储引擎访问基表（聚簇索引）的次数和 MySQL 服务器访问存储引擎的次数（可以减少"回表"的次数）。
    • 但是，ICP 的 加速效果 取决于在存储引擎内通过 ICP筛选 掉的数据的比例。

索引条件下推就是：尽可能的利用 "索引过滤数据"，从而减少 "回表" 的次数。

个人理解：

不使用 ICP：如果不使用 ICP，查询时只会使用一次索引过滤条件，后续的过滤条件，只能会在 "回表" 之后过滤。假设第一次索引过滤后有 100 条数，那么就需要 "回表" 100次，在 "回表" 后执行后面的过滤条件。

使用 ICP：当使用 ICP 时，查询时会尽可能的使用索引过滤，直至没有可用的索引过滤条件。这样就最大限度的利用了索引。每使用一次索引过滤，就会减少一定数量的 "回表" 次数。

10.2 索引条件下推（ICP）的开启/关闭

**默认情况下启用索引条件下推。可以 通过设置系统变量 optimizer_switch 控制：index_condition_pushdown （默认：开启） **

  • 查看索引条件下推状态：optimizer_switch（会话/全局系统变量）
SHOW VARIABLES like '%optimizer_switch%';
# 结果中 index_condition_pushdown 的状态；
  • 开启索引条件下推
# 会话级别
SET optimizer_switch = 'index_condition_pushdown=on';
# 全局级别
SET GLOBAL optimizer_switch = 'index_condition_pushdown=on';
  • 关闭索引条件下推
# 会话级别
SET optimizer_switch = 'index_condition_pushdown=off';
# 全局级别
SET GLOBAL optimizer_switch = 'index_condition_pushdown=off';
  • 通过 on_icp(people) 不使用索引条件下推
EXPLAIN SELECT /*+ on_icp(people)*/ * FROM base_obj_xld WHERE OBJ_CODE > 'z' AND OBJ_CODE LIKE '%a%';

当使用索引条件下推时，explain 语句输出结果中 Extra 列内容显示为 Using index condition

10.3 ICP 的使用条件

  1. 如果 表访问的类型为 range，ref，eq_ref 和 ref_or_null 可以使用 ICP
  2. ICP 适用于 InnoDB 和 MyISAM 表，包括分区表 InnoDB 和 MyISAM 表
  3. 对于 InnoDB 表，ICP 仅用于 "二级索引"。ICP 的目标是减少全行读取次数，从而减少I/O操作
  4. 当 SQL 使用覆盖索引时，不支持 ICP 。因为这种情况下使用 ICP 不会减少 I/O
  5. 相关子查询的条件不能使用 ICP

11. 普通索引 vs 唯一索引

... 不知一提

12. 其他查询优化策略

12.1 exists 和 in 的区分

问：在那种情况下应该使用 exists ，那种情况下应该用 in。选择的标准是看能否使用表的索引吗？

答：索引只是前提，选择那个方式还是要看表的大小。可以使用 小表驱动大表 的标准来选择。这样效率是最高的。

  • "驱动表" 为小表时，选择 exists。
  • "被驱动表" 为小表时，选择 in。
  • 永远 选择数量小的表为：外层循环表。尽可能的 减少外层循环的次数。

注意：不要太担心，"查询优化器" 会帮助我们作出最好的选择哦！！！

12.2 count(*)与count(具体字段)效率

问：在 MySQL 中 统计数据表的行数（count 函数），可以使用三种方式：select count(*)，select count(1) 和 select count(具体字段)，这三者之间的查询效率怎么样呢?

答：

环节1：count(*) 和 count(1) 都是对所有结果进行 count，两者本质上并没有区别，执行的效率上基本是相等。

环节2：

  • 如果是 MyISAM 存储引擎，统计数据表的行数只需要 O(1) 的复杂度，这是因为每张 MyISAM 的数据表都 有一个 meta 信息存储了 row_count 值。
  • 如果是 InnoDB 存储引擎，因为 InnoDB 支持事务，采用行级锁和 MVCC 机制，无法使用 row_count 变量，因此需要采用 扫描全表，是 O(n) 的复杂度，循环 + 计数的方式完成统计。

环节3：

  • 在 InnoDB 引擎中，如果 采用 count(具体字段) 来统计数据行数，要尽量采用"二级索引"。因为主键采用的索引是 "聚簇索引"，"聚簇索引" 包含的信息多，明显会大于 "二级索引"。对于 count(*) 和 count(1) 来说，系统会 自动 采用占用空间更小的 "二级索引" 来进行统计。
  • 如果有多个 "二级索引"，会 使用 key_len 小的 "二级索引" 进行扫描。当 没有 ”二级索引“ 时，会采用 "主键索引" 进行统计。

12.3 关于 select(*)

在表查询中，建议 明确字段（select <字段列表>），不要使用 "*" 作为查询的字段列表。

原因：

  • MySQL 在解析的过程中，会通过 查询数据字典（系统表） 将 "*" 按序转换成所有列名，这会大大的耗费资源和时间。
  • 无法使用 "覆盖索引"

12.4 limit 1 对优化的影响

对于 扫描全表 的情况，如果你可以 确定结果集只有一条，那么加上 limit 1 的时候，当 找到一条结果后就不会继续扫描了，会加快查询速度。

如果数据表已经对 字段建立了 "唯一索引"，当可以 通过索引进行查询时，就不会 扫描全表，那就不需要 limit 1了。

总结一句话：在 全表扫描 的情况下使用 limit 才有意义，如果是 使用索引的情况下 使用 limit 只不过是井上添花罢了。

12.5 多使用 commit（事务提交）

只要有可能， 在程序中尽量多使用 commit，这样程序的性能会得到提高（commit 之后会释放资源）。

commit（事务提交）之后释放的资源：

  • 回滚段上用于恢复数据的信息
  • 被程序语句获得的锁（事务锁）
  • redo / undo log buffer 中的空间
  • 管理上述 3 种资源中的内部花费

13. 淘宝数据库，主键如何设计的？

  • 聊一个实际问题：淘宝的数据库，主键是如何设计的？

大部分人的回答都是：用 8 个字节 bigint 做主键，而不要用 int。（不全对）。这样的回答，只站在了数据库这一层，而没有 从业务的角度 思考主键。

  • 主键就是一个自增ID吗？使用自增做主键，架构设计上可能 连及格都拿不到。

13.1 自增 ID 的问题

自增 ID 做主键，除了简单易懂之外，其他都是缺点。

  • 可靠性不高

存在自增 ID 回溯的问题（服务器重启后，会重新计算自增的值），8.0 才修复。

  • 安全性不高

对外暴露的接口可以非常容易猜测对应的信息。比如： /user/1 这样的接口。

  • 性能差

自增 ID 的性能较差，需要在数据库服务器端生成。

  • 交互多

必须执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，这就需要多一次的网络交互。

  • 局部唯一性（不全局唯一）

最重要的一点，自增 ID 是局部唯一，只在当前表中是唯一的，不是全系统唯一。对于分布式系统来说，简直就是噩梦。

13.2 业务字段做主键

不建议使用业务有关的字段做主键。

对应业务来说，我们谁也无法预测在项目的整个生命周期中，某个业务字段会因为项目的业务需求而重复，或者重用之类的情况出现。

经验之谈：

刚开始使用 MySQL 时，很多人都很容易犯的错误是喜欢用业务字段做主键，想当然的认为了解业务需求，但实际情况往往出乎意料，而 更改主键的成本是非常高的。

13.3 淘宝的主键设计

订单号是 19 位长度，最后 5 位都是一样的，前面的 14 位是的 单调递增。

淘宝的订单表主键设计：

订单主键id = 时间 + 去重字段 + 用户id后 n 位尾号

例如：202207038134508113

13.4 推荐的主键设计

  • 非核心业务：对应表的主键可以使用自增 ID。比如：日志，字典，监控表等....
  • 核心业务：主键设计至少应该是 "全局唯一" 且是 "单调递增"。"全局唯一" 保证在各个系统之间都是唯一的，"单调递增" 是希望插入时不影响数据性能。

uuid 作为主键 ID

在 MySQL 中获取改进后的 uuid

SELECT UUID(),uuid_to_bin(UUID()),uuid_to_bin(UUID(),TRUE)

通过函数 uuid_to_bin(uuid(),true) 将 uuid 转化为 有序的uuid。"全局唯一" 且 "单调递增"。

posted @ 2023-11-18 15:09  小林当  阅读(178)  评论(0)  编辑  收藏  举报