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';
结论:
- 有 or 关键字的 "查询语句",如果想使用 "索引",则 必须有多个 "索引"
- 我们 可以将 or 关键字看做为 "索引(联合索引)" 分割符,or 前面的所有条件列,看做一个 "索引(联合索引)"。or 后面的所有条件列,看做一个"索引(联合索引)"。
- 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 小结
- 整体效率比较:INLJ > BNLJ > SNLJ(索引 > 块 > 简单)嵌套循环查询
- 永远用 小结果集驱动大结果集(其本质就是减少外层循环的数据数量)(度量单位指的是:表行数 * 每行记录的内存大小)
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 关键字,表示 禁止查询优化器对该语句进行优化。
- 为 "被驱动表" 匹配的条件增加索引(减少 "被驱动表" 的循环匹配次数)。
- 增大 join buffer size 的大小(一次缓存的数据越多,那么扫描 "被驱动表" 的次数就越少)。
- 减少 "驱动表" 不必要的字段查询(字段越少,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操作,反而得不偿失。
优化策略:
- 尝试提高 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
- 尝试提高 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 的使用条件
- 如果 表访问的类型为 range,ref,eq_ref 和 ref_or_null 可以使用 ICP
- ICP 适用于 InnoDB 和 MyISAM 表,包括分区表 InnoDB 和 MyISAM 表
- 对于 InnoDB 表,ICP 仅用于 "二级索引"。ICP 的目标是减少全行读取次数,从而减少I/O操作
- 当 SQL 使用覆盖索引时,不支持 ICP 。因为这种情况下使用 ICP 不会减少 I/O
- 相关子查询的条件不能使用 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。"全局唯一" 且 "单调递增"。