关于mysql:三高Mysql-Mysql索引和查询优化偏实战部分

4次阅读

共计 17568 个字符，预计需要花费 44 分钟才能阅读完成。

实战局部回筛选一些比拟常见的状况，当时强调集体应用的是mysql 8.0.26，所以不同版本如果呈现不同测试后果也不要诧异，新版本会对于过来一些不会优化的查问进行优化。

实战局部承接上一篇文章：三高 Mysql – Mysql 索引和查问优化解说（偏实践局部）– 掘金 (juejin.cn)

这里还是要再啰嗦一遍，所有的数据库和表均来自官网的sakila-db，作为学习和相熟 mysql 数据库操作十分好。

sakila-db 是什么？国外很火的一个概念，指的是国外电影租赁市场外国人应用租赁的形式进行电影的观看，过来非常受外国人的喜爱，这里拿进去介绍是因为后续的内容都用到了这个案例，所以咱们须要提前把相干的环境筹备好，从如下地址进行下载：

下载地址：https://dev.mysql.com/doc/ind…

work-bench 是官网开发的数据库关系图的可视化工具，应用官网案例的具体关系图展现成果如下，通过这些图能够看到 Sakila-db 之间的大抵关系：

work-bench 是免费软件，下载地址如下：

https://dev.mysql.com/downloa…

装置 workbench 和下载 sakila-db 的过程这里不做记录，在运行的时候须要留神先建设一个数据库运行 Sheme 文件，而后执行 data 的 sql 文件，最终借助 navicat 中查看数据和表构造关系：

遇到 where 查问太慢，咱们第一步是须要剖析数据类型的组成以及数据表的设置是否正当，其次咱们能够应用 explain 对于查问语句进行剖析，应用形式非常简略在须要优化的查问语句后面增加 explain 语句，对于所有的查问来说，笼罩索引的查找形式是最优解，因为笼罩索引不须要回表查数据。

笼罩索引：笼罩索引是查问形式，他不是一个索引，指的是在查问返回后果的时候和应用的索引是同一个，这时候能够发现他压根不须要回表，间接查辅助索引树就能够失去数据，所以笼罩索引的查问效率比拟高。

如何应用 sql 语句查看某一个表的建表语句：

答复：应用 show create table 表名称 即可。

那么什么状况下会应用笼罩索引：

查问字段为辅助索引的字段或者聚簇索引的字段。
合乎 最左匹配准则，如果不是最左匹配则不能走索引。

咱们应用下面提到的 sakila-db 进行试验，这里能够应用 inventory 表作为试验，然而这个表须要进行一些调整，上面请看具体的 sql：

CREATE TABLE `inventory_test` (
  `inventory_id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `film_id` smallint unsigned NOT NULL,
  `store_id` tinyint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  -- KEY `idx_fk_film_id` (`film_id`),
  KEY `idx_store_id_film_id` (`store_id`,`film_id`)
  -- CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  -- CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

咱们将原始的 sql 建表语句只保留一个辅助索引，比方在下面的语句中删除了 idx_fk_film_id 索引，上面删除这个索引之后的试验成果：

explain select * from inventory_test where film_id = 1;
-- 案例 1. 不合乎最左准则不走索引 
-- 1  SIMPLE  inventory_test    ALL          1  100.00  Using where

explain select * from inventory_test where store_id = 1;
-- 案例 2: 应用了辅助索引（联结索引）：-- 1  SIMPLE  inventory_test    ref  idx_store_id_film_id  idx_store_id_film_id  1  const  1  100.00  

explain select inventory_id,film_id,store_id from inventory_test where store_id = 1;
-- 案例 3:  失常应用索引 
-- 1 SIMPLE  inventory_test    ref  idx_store_id_film_id  idx_store_id_film_id  1  const  1  100.00  Using index

explain select film_id,store_id from inventory_test where store_id = 1;
-- 案例 4:  笼罩索引 
-- 1  SIMPLE  inventory_test    ref  idx_store_id_film_id  idx_store_id_film_id  1  const  1  100.00  Using index

explain select film_id,store_id from inventory_test where film_id = 1;
-- 案例 5: 失常应用索引，然而 type 存在区别 
-- 1  SIMPLE  inventory_test    index  idx_store_id_film_id  idx_store_id_film_id  3    1  100.00  Using where; Using index

explain select inventory_id,film_id,store_id from inventory_test where film_id = 1;
-- 案例 6: 应用索引返回后果，然而 type 存在区别 
-- 1  SIMPLE  inventory_test    index  idx_store_id_film_id  idx_store_id_film_id  3    1  100.00  Using where; Using index

explain select inventory_id,film_id,store_id from inventory_test where store_id = 1;
-- 案例 7: 笼罩索引 
-- 1  SIMPLE  inventory_test    ref  idx_store_id_film_id  idx_store_id_film_id  1  const  1  100.00  Using index

案例 1 和案例 2 是较为典型的 索引最左匹配准则 的谬误应用反面教材，也是很多老手建设索引然而可能用错的陷阱之一，最左匹配准则指的是 where 条件须要从建设索引的最左列开始进行搜寻，能够看到这里的星号和建表的时候字段的程序是一样的，也就是 inventory_id，film_id,store_id，last_update，所以是尽管是select * 然而是失常走索引的。

（理论干活时候千万不要这么做，这里是为了演示偷懒而已）

不必星号我应用乱序的列查问会怎么样，其实这时候如果你把查问列的数据换一下会 ….. 没啥影响，随便调换查问列程序仍然能够走索引。

接下来是案例 3 – 案例 7 的几个查问，这几个查问用意解释的是针对笼罩索引应用的细节问题，在下面的测试案例语句当中能够看到案例 4 因为查问的后果和 where 条件都是应用了索引的，所以最终 mysql 应用了残缺的笼罩索引，同时合乎联结索引的最左匹配准则，所以查问的效率达到了 ref 级别（这个级别临时简略了解就是十分快就行）。

接着案例 5 又把 where 条件换了一下，能够看到尽管还是走了索引，然而效率一下子就低了下来，因为他不合乎最左匹配准则，另外这个案例 5 的查问级别能够了解为它须要把整个辅助索引也就是联结索引的树扫完再去进行 where 筛选，效率天然就不如间接检索排序索引值快了，然而 index 这个级别还是比 ALL 这个龟速快不少。

了解了下面的这一层意思，再来了解案例 6 和 7 就很简略了，能够看到只多了一个主键列查问。

这里读者可能会感觉你这下面不是说返回后果全是索引列才会笼罩么，怎么退出了主键列还是见效呢？主键不是在聚簇索引上嘛不是须要回表么？其实这两个问题很好答复，因为辅助索引当中 key 存储的的确是索引列的值，然而他的索引值放的是主键 ID，当 mysql 在搜寻索引列的时候发现这里多了一个列，然而又发现这个列是主键，所以最初发现能够间接通过联结索引间接返回后果不须要回表，所以这样笼罩索引的条件同样是成立的。

如果读者不分明查问 explain 后果列代表的含意，能够参考上面的内容比照：

id: 首先，一个 select 就会呈现一个 id, 通常在简单的查问外面会蕴含多张表的查问，比方 join, in 等等
select_type：这个示意的是查问的类型
table：表名称
partitions：这个示意表空间，分区的概念
type : 比方查问的优化等级, const, index, all，别离代表了聚簇索引，二级索引(辅助索引)，全表扫描的查问搜寻形式
Possiblekeys：和 type 一样确定拜访形式，确定有哪些索引能够抉择，
key：确定有哪些能够提供抉择，同时提供索引的对应长度
key_len：示意的是索引的长度
ref：等值匹配的时候呈现的一些匹配的相干信息
Rows：预估通过所索引或者别的形式读取多少条数据
filtered：通过搜寻条件过滤之后的残余数据百分比。
extra：额定的信息不重要，次要用于用户断定查问走了什么索引。

总结

通过下面的案例咱们能够从上面的角度思考来如何晋升索引查问速度：

应用 笼罩索引 查问形式提高效率，再次强调笼罩索引不是索引是优化索引查问一种形式。
如果数据不只应用索引列那么就构不成笼罩索引。
能够优化 sql 语句或者优化联结索引的形式进步笼罩索引的命中率。

这里波及一个索引基数（cardinality）的问题，索引基数是什么，其实就是利用算法和概率学统计的形式确定最优化的索引计划，这个值能够通过 show index from 表名 的形式进行获取，比方上面的 200 和 121 就是 索引基数（cardinality）。

因为索引基数的存在如果索引不合乎咱们到应用预期能够尝试强制应用某索引。

> show index from actor;
actor  0  PRIMARY  1  actor_id  A  200        BTREE      YES  
actor  1  idx_actor_last_name  1  last_name  A  121        BTREE      YES

索引基数的定义官网文档的介绍：

上面一坨货色简略来说就是 mysql 会依据基数的数值依据肯定的算法抉择应用索引，然而有时候如果查问不能合乎预期要求就须要强制应用索引了。

表列中不同值的数量。当查问援用具备关联索引的列时，每列的基数会影响最无效的拜访办法。
例如，对于具备惟一束缚的列，不同值的数量等于表中的行数。如果一个表有一百万行，但特定列只有 10 个不同的值，
则每个值（均匀）呈现 100,000 次。SELECT c1 FROM t1 WHERE c1 = 50 等查问因而可能会返回 1 行或大量行，
并且数据库服务器可能会依据 c1 的基数以不同形式解决查问。

如果列中的值散布十分不平均，则基数可能不是确定最佳查问打算的好办法。例如，SELECT c1 FROM t1 WHERE c1 = x;
当 x=50 时可能返回 1 行，当 x=30 时可能返回一百万行。在这种状况下，您可能须要应用索引提醒来传递无关哪种
查找办法对特定查问更无效的倡议。

基数也能够利用于多个列中存在的不同值的数量，例如在复合索引中。
参考：列、复合索引、索引、索引提醒、长久统计、随机潜水、选择性、惟一束缚。

原文：The number of different values in a table column. When queries refer to columns that have an 
associated index, the cardinality of each column influences which access method is most 
efficient. For example, for a column with a unique constraint, the number of different 
values is equal to the number of rows in the table. If a table has a million rows but 
only 10 different values for a particular column, each value occurs (on average) 100,000 times.
 A query such as SELECT c1 FROM t1 WHERE c1 = 50; thus might return 1 row or a huge number of 
 rows, and the database server might process the query differently depending on the cardinality 
 of c1.

If the values in a column have a very uneven distribution, the cardinality might not be 
a good way to determine the best query plan. For example, SELECT c1 FROM t1 WHERE c1 = x;
 might return 1 row when x=50 and a million rows when x=30. In such a case, you might need 
 to use index hints to pass along advice about which lookup method is more efficient for a 
 particular query.

Cardinality can also apply to the number of distinct values present in multiple columns, 
as in a composite index.

See Also column, composite index, index, index hint, persistent statistics, random dive,
 selectivity, unique constraint.

如何让 sql 强制应用索引

能够应用 from 表之后接条件语句：force index(索引) 的形式进行解决，应用强制索引的状况比拟少，除非优化器真的抉择了不合乎预期的优化规定并且重大影响查问性能，应用强制索引的案例如下：

select * from actor force index(idx_actor_last_name);

count 函数不必多介绍，作用是查问后果的行数，然而须要留神优化器在处理过程中会 比对并且排除掉后果为 null 的值 的数据，这意味着在行数很大的时候如果应用不正确 count 会因为比对数据操作进而升高查问效率。

所以这里咱们只有记住一个特定的规定，那就是只有是波及行数的查问，那就应用 select(*)，起因仅仅是 mysql 官网针对这个做了专门的优化，也不须要去纠结为什么官网要给select(*) 做优化，只能说 约定大于配置，上面是常见的查问性能之间的比照：

count(非索引字段)：实践上是最慢的，因为对于每一行后果都要判断是否为 null。
count(索引字段)：尽管走了索引，然而仍然须要对每一行后果判断是否为 null。
count(1)：尽管不波及字段了，然而这种形式仍然须要对 1 进行判断是否为 null。
count(*)：Mysql 官网进行优化，查问效率最快，只须要记住这种形式即可。

索引下推实现版本为 Mysql5.6 以上。

作用：实质上是为了缩小辅助索引（或者说二级索引）回表次数 的一种优化伎俩。

案例：请看上面的建表语句，这里比拟要害的是建设了 store_id 和film_id的联结索引。

以上面的 SQL 语句为例，如果是 5.6 之前的版本尽管他是笼罩索引的查问形式但却是 不能应用索引 的，数据进过索引查找之后尽管 store_id 是程序排序的然而 film_id 是乱序的，在索引检索的时候因为没有方法程序扫描（如果不分明索引组织构造能够多看几遍 B + 树索引结构）它须要一行行应用主键回表进行查问，查问理论须要应用每一行的 inentory_id 回表 4 次去匹配 film_id 是否为 3。

select * from inventory_3 where store_id in (1,2) and film_id = 3;

依照人的思维看起来是很不合理的，因为咱们能够发现依照失常的逻辑有一种搜寻的办法是通过“跳跃“索引的形式进行扫描，当扫描到索引列如果不符合条件，则间接跳跃索引到下一个索引列，有点相似咱们小时候”跳房子“形式来寻找本人须要的沙袋（索引数据）。

那么索引下推是如何解决下面这种状况的呢？尽管 film_id 是没有方法程序扫描的也不合乎索引的排列规定，然而发现能够依据遍历 film_id 汇总索引之后再回表查呀！比方依据查问条件搜寻遍历找到 film= 3 之后再依据二级索引列对应的主键去查主索引，这时候只须要一次回表就能够查到数据，此时本来应该依据每个二级索引的主键值进行回表变为遍历索引并找到索引值之后再回表，最终达到缩小回表次数的成果，这也是后面为什么说索引下推是为了缩小了回表的次数的答案。

索引下推的开启和敞开能够参考如下命令：

-- 索引下推变量值：mysql> select @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

-- 敞开索引下推
set optimizer_switch='index_condition_pushdown=off';
-- 开启索引下推
set optimizer_switch='index_condition_pushdown=on';

对于涣散索引和紧凑索引能够看上面两个文档比照参考浏览：

MySql 中文文档 – 8.2.1.15 GROUP BY 最佳化 | Docs4dev

MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.17 GROUP BY Optimization

涣散索引和紧凑索引的概念不是特地好了解，涣散索引和紧凑索引实际上就是当 MySQL 利用索引扫描来实现 GROUP BY 的时候，并不需要扫描所有满足条件的索引键即可实现操作得出后果，仅仅解决的状况细节不同。

过来 Mysql 对于 group by 操作是构建长期表并且在长期表上操作，在应用索引的状况下，分组查问是能够走索引的：

explain select last_name from actor  GROUP BY last_name
-- 1    SIMPLE    actor        index    idx_actor_last_name    idx_actor_last_name    182        200    100.00    Using index

因为 group by 操作和order by 操作不走索引的时候可能会产生长期表，同时group by 操作领有和order by 相似的排序操作，有时候咱们分组查问不止一个字段，所以可能会呈现多列索引状况，所以此时 mysql 对于多列联结索引分组查问进一步优化，提供了涣散索引和紧凑索引多概念，

涣散索引在官网有上面的定义：

当彻底应用索引扫描实现 group by 操作的时候，只须要应用局部的索引列就能够实现操作
尽管 Btree 的二级索引外部是排序并且要求索引是程序拜访的，然而对于 group by 最大的优化是扫描这种顺序索引的时候where 条件没必要齐全贴合所有索引 key，

下面定义有两个个关键词：彻底和不齐全 ，where 条件没必要齐全贴合索引键。为了更好了解咱们这里应用了官网给的例子，假如在 tablet1(c1,c2,c3,c4) 上有一个索引idx(c1,c2,c3)。涣散索引扫描拜访办法可用于以下查问：

-- 能够不应用所有索引字段，能够走联结索引
SELECT c1, c2 FROM t1 GROUP BY c1, c2;
-- 去重操作外部也会进行隐式的分组行为
SELECT DISTINCT c1, c2 FROM t1;
-- 分组的极值查问能够应用涣散索引，因为 c2 和 c1 仍然有序
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
-- 分组前的 where 条件
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
-- 对于 c3 的极值操作仍然和 c1,c2 形成索引
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
-- 反对范畴查问的同时走涣散索引
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
-- 最初一列等值查问仍然能够视为涣散索引
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;    
-- 涣散索引能够作用于上面的查问
SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

涣散索引须要满足上面的条件：

分组查问是单表查问
group by的条件必须同一个索引顺序索引的间断地位。
group by的同时只能应用 max 或者 min 两个聚合函数（然而在 5.5 之后，新增了更多函数反对）。
如果利用 group by 以外字段条件必须用 常量模式 存在。
必须应用残缺的索引值，也就意味着 like 这样的前缀索引是不实用的。

如果想要断定查问是否应用涣散索引能够依据 explain 的extra内容是否为 Using index for group-by 确认。

上面咱们用更理论 SQL 来介绍，假如在 tablet1(c1,c2,c3,c4)上有一个索引idx(c1,c2,c3)。涣散索引扫描拜访办法可用于以下查问：


-- 自我试验：涣散索引
EXPLAIN SELECT COUNT(DISTINCT film_id, store_id), COUNT(DISTINCT store_id, film_id) FROM inventory_test;
-- 1  SIMPLE  inventory_test    range  idx_store_id_film_id  idx_store_id_film_id  3    4  100.00  Using index for group-by (scanning)
-- 自我试验：涣散索引
EXPLAIN SELECT COUNT(DISTINCT store_id), SUM(DISTINCT store_id) FROM inventory_test;
-- 1  SIMPLE  inventory_test    range  idx_store_id_film_id  idx_store_id_film_id  1    4  100.00  Using index for group-by (scanning)

-- 然而如果查问的不是同一个索引，不满足最左准则是不走涣散索引的，而是走更快的索引扫描：EXPLAIN SELECT COUNT(DISTINCT store_id), SUM(DISTINCT store_id) FROM inventory_test;
EXPLAIN SELECT COUNT(DISTINCT film_id), SUM(DISTINCT film_id) FROM inventory_test;
-- 1    SIMPLE    inventory_test        range    idx_store_id_film_id    idx_store_id_film_id    1        4    100.00    Using index for group-by (scanning)
-- 1    SIMPLE    inventory_test        index    idx_store_id_film_id    idx_store_id_film_id    3        3    100.00    Using index

紧凑索引

和涣散索引区别的是紧凑索引应用前提是必须是 全索引扫描 或者 范畴索引扫描，当涣散索引没有失效时使得group by 仍然有可能防止创立长期表，紧凑索引须要读取所有满足条件的索引键才会工作，而后依据读取的数据实现group by 操作。

为了使紧凑索引查问这种办法见效在查问中的所有列都要有 恒定的相等条件 ，比方必须GROUP BY 键之前或之间的局部键。

在紧凑索引扫描形式下，先对索引执行 范畴扫描（range scan），再对后果元组进行分组。为了更好的了解，能够看一下相干的案例：

在 GROUP BY 中存在一个缺口，然而它被条件 c2='a' 所笼罩。

SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

GROUP BY没有以键的第一局部开始，然而有一个条件为这部分提供了一个常数。

SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

咱们依照官网给的案例试验一下，首先是表构造，咱们在上面表中建设联结索引：

CREATE TABLE `inventory_test` (
  `inventory_id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `film_id` smallint unsigned NOT NULL,
  `store_id` tinyint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  KEY `idx_store_id_film_id` (`store_id`,`film_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

上面是集体应用紧凑索引的案例，当 where 条件是常量值并且是针对索引的常量值的时候，group by就能够走索引，然而如果 where 条件是非索引字段仍然须要全表扫描，留神这里 group 的字段并不是依照联结索引的最左前缀解决的仍然能够走索引，这就是 mysql 对于分组操作的一系列优化了。

-- 紧凑索引
EXPLAIN select count(*),max(film_id),sum(film_id), avg(film_id) from inventory_test where store_id = 1 GROUP BY film_id;
-- 1  SIMPLE  inventory_test    ref  idx_store_id_film_id  idx_store_id_film_id  1  const  1  100.00  Using index

EXPLAIN select count(*),max(film_id),sum(film_id), avg(film_id) from inventory_test where last_update > '2022-02-02 23:20:45' GROUP BY film_id;
-- 1  SIMPLE  inventory_test    ALL  idx_store_id_film_id        3  33.33  Using where; Using temporary

EXPLAIN select count(*),max(film_id),sum(film_id), avg(film_id) from inventory_test where last_update = '2022-02-02 23:20:45' GROUP BY film_id;
-- 1  SIMPLE  inventory_test    ALL  idx_store_id_film_id        3  33.33  Using where; Using temporary

倡议读者多读一读官网文档加深这两个概念了解。

什么是两头后果集？

对于惯例的 sort 语句，因为须要对于搜寻的后果依照某一字段进行大小排序，而为了让这个操作顺利完成，mysql 会把这个操作放到硬盘或者内存实现。

排序的根本步骤和原理

对于波及排序的语句，它的大抵工作原理如下：

选取查问字段，依据 where 进行条件查问。
查问后果集生成sort_buffer，如果内存不够，须要在硬盘建设两头表进行排序。
将两头表依据Order 字段进行排序。
回表生成残缺后果集，组装返回后果。

两头后果集特点

如果两头表比拟小则放到内存中，断定什么时候会存在于内存中 Mysql 提供了 sort_buffer_size 的参数，它负责管制两头后果集的大小，如果优化内存须要调整升高这个参数值，然而如果想要优化查问的工夫，则须要调大这个参数。

回表生成残缺后果集

回表生成残缺后果集这个操作其实也不是总是执行的，会依据会话参数 max_length_for_sort_data 进行判断，如果以后查问小于这个数值，会生成一个 全字段两头表 后果能够间接从全字段两头表获取，然而如果大于这个数值那么就只会生成 排序字段 + 主键两头表（相似二级索引），所以这时候显然查找一遍是无奈找到的，须要回表能力实现操作。

须要留神 排序字段 + 主键两头表 看起来像是二级索引然而实际上和二级索引齐全没有关系，只是一个简略列表须要重复去主表获取数据。

总结：全字段两头表 >max_length_for_sort_data> 排序字段 + 主键两头表，数值并不是越大越好越大越影响查问效率。

排序查问优化点

基本问题在于排序的后果是两头后果集，尽管后果集能够在内存中解决，然而他有最为实质的问题那就是 两头表不存在索引 并且导致索引生效，所以为了让两头表能够走索引咱们能够应用 索引笼罩 的形式。

优化伎俩：索引笼罩，也是最高效的解决形式。索引笼罩能够跳过生成生成两头后果集，间接输入查问后果。

order by 的字段为索引（或者联结索引的最右边）。
其余字段（条件、输入）均在上述索引中。
索引笼罩能够跳过两头后果集，间接输入查问后果。

什么是索引笼罩？

笼罩索引：笼罩索引是 查问形式 而不是一个索引，指的是一个 sql 语句中包含查问条件和返回后果均合乎索引应用条件，当然在 Mysql5.6 之后减少索引下推，满足下推条件的也能够走笼罩索引。

比方上面的语句并不会生成两头后果集并且能够无效利用索引：

explain select film_id, title from film order by title;
-- 1    SIMPLE    film        index        idx_title    514        1000    100.00    Using index

总结：晋升排序查问速度

给 order by 字段减少索引，或者 where 字段应用索引，让查问能够走笼罩索引的形式。
调整 sort_buffer_size 大小，或者调整 max_length_for_sort_data 的大小，让排序尽量在内存实现。

通过上面的案例能够得悉，如果咱们对于索引的字段进行了相似函数的操作那么 mysql 会放弃应用索引，另外一种状况是日期函数比方 month()函数也会使得索引生效。

小贴士：很多人认为函数操作是那些 sum()，count()函数，实际上对于字段的 加减乘除 操作都能够认为是函数操作，因为底层须要调用计算机的寄存器实现相干指令操作。另外这里须要和签名的索引下推和涣散紧凑索引做辨别，涣散和紧凑索引针对分组操作索引优化，索引下推到了 5.6 才被正式引入。大多数旧版本的 mysql 零碎是没法享受应用函数操作同时还能走索引的。

-- sql1：对于索引字段进行函数操作
EXPLAIN SELECT
  title 
FROM
  film   
WHERE
  title + '22' = 'ACADEMY DINOSAUR' 
  AND length + 11 = 86;
  -- 1  SIMPLE  film    ALL          1000  100.00  Using where
  
-- sql2：如果对于其余字段应用函数操作，然而索引字段不进行 函数操作仍然能够走索引
EXPLAIN SELECT
  title 
FROM
  film 
WHERE
  title  = 'ACADEMY DINOSAUR' 
  AND length + 11 = 86;
  -- 1  SIMPLE  film    ref  idx_title  idx_title  514  const  1  100.00  Using where

工夫函数如何优化：

咱们要如何优化工夫函数呢？有一种比拟笨的形式是应用 between and 代替，比方要搜寻 5 月份，就应用 5 月的第一天到 5 月的最初一天，具体的优化案例如下：

explain select last_update from payment where month(last_update) =2;
-- last_update 须要手动创立索引
-- 1  SIMPLE  payment    ALL          16086  100.00  Using where

如果须要优化下面的后果，咱们能够应用其余的形式替换写法：

explain select * from payment where last_update between '2006-02-01' and '2006-02-28';
-- 1  SIMPLE  payment    ALL  idx_payment_lastupdate        16086  50.00  Using where

这里很奇怪，咋和下面说的不一样呢？其实是因为 last_update 这个字段应用的数据类型是 timestamp，而 timestamp 在进行搜寻的时候因为优化器的判断会放弃应用索引！所以解决办法也比较简单： 应用 force index 让 SQL 强制应用索引。

explain select  * from payment force index(idx_payment_lastupdate) where last_update between '2006-02-01' and '2006-02-28' ;
-- 1  SIMPLE  payment    range  idx_payment_lastupdate  idx_payment_lastupdate  5    8043  100.00  Using index condition

这里通过试验发现如果字段是 datetime，就能够间接用 Between and 索引，对于工夫戳类型并没有试验，仅从现有的表设计来看后果如下：

-- 优化后
-- 1  SIMPLE  rental    range  rental_date  rental_date  5    182  100.00  Using index condition
explain select * from rental where rental_date between '2006-02-01' and '2006-02-28';

-- 1  SIMPLE  rental    ALL          16008  100.00  Using where
explain select * from rental where  month(rental_date) =2;

字符和数字比拟：

字符和数字比拟也是会呈现函数转化的同样会导致索引生效，所以在等式匹配的时候须要确保被比拟的类型左右两边统一，另外如果无奈批改查问能够应用 cast 函数进行补救，比方像上面这样解决。

select * from city where cast(city_id as SIGNED int) = 1;

隐式字符编码转化：

如果两个表字段的编码不一样，也会呈现索引生效的问题，因为底层须要对于编码进行转化，解决形式也比较简单，在比拟的时候，同时尽量比拟字符串保障编码统一。那么假如两张表比拟的时候，那个表的字段须要转化呢，比方 A 表的 utf8 和 B 表 utf8mb4，A 表中字段须要和 B 表字段进行比拟的时候，须要将 A 表的字段转为和 B 表的字段统一。

这个就偷懒不试验了，绝大多数状况下表的字符集编码格局只有追随表级别根本不会呈现不统一的问题 ……

select tilte, desciption from film order by rand() limit 1;
-- EXPLAIN select title, description from film order by rand() limit 1;
-- 1  SIMPLE  film    ALL          1000  100.00  Using temporary; Using filesort

rand()函数是非常消耗数据库性能的函数，在日常应用过程中咱们可能遇到须要长期获取一条数据的状况，这时候就有可能会应用 rand() 函数，上面是 rand() 函数的执行原理：

创立一个长期表，长期表字段为rand、title、description。
从长期表中获取一行，调用 rand()，把后果和数据放入长期表，以此类推。
针对长期表，把 rand 字段 + 行地位（主键）放入到sort_buffer。

能够看到这里最大的问题是呈现了 两次两头后果集。

针对此问题能够应用上面的长期计划进行解决，这个长期计划能够看作是把 rand()外部的工作拆开来进行解决，也是在不改变业务的状况下一种比拟“笨”的解决形式：

select max(film_id),min(film_id) into @M,@N from film;
set @x=FLOOR((@M-@N+1) * rand() + @N);
EXPLAIN select title,description from film where film_id >= @X limit 1;

其余解决形式是应用业务和逻辑代码代替 sql 的外部解决，比方应用上面的形式进行解决：

查问数据表总数 total。
total 范畴内，随机选取一个数字 r。
执行下列的 SQL：

select title,description from film limit r,1;

小结：

order by rand() limit 这个查问的效率极其低下，因为他须要生成两次两头表能力获取后果，审慎应用此函数。
解决方案有两种：
- 长期解决方案：在主键的最大值和最小值中选取一个。
- 好了解的形式解决：业务代码加 limit 解决
长处：在不扭转业务的状况下间接通过调整 SQL

毛病：模板代码比拟难以记忆，并且并不是万能的，因为可能不给你相干权限
倡议应用业务逻辑代码解决不应用 rand()函数。

再次留神这里试验的时候应用的数据库版本为8.0.26。

咱们首先来看一下《高性能 Mysql 第三版》241-242 页怎么说的，作者应用的也是 sakila 表，举荐的形式是应用 提早关联 的办法，比方把上面的 sql 进行优化：

-- 优化前
select film_id,description from film order by title limit 50,5;

-- 优化后
select film_id,description from film inner join (select film_id from film order by title limit 50, 5) as lim using(film_id)

第二种形式是当 id 合乎某种排序规定并且业务刚好合乎的时候能够应用 between ...and 代替

select * from film where film_id between 46 and 50 order position;

最初还有一种形式是利用排序的个性将数据排序之后获取后面的行即可：

select * from film where film_id order position desc limit 5;

以上是对于《高性能 Mysql 第三版》局部的介绍。上面来看下咱们是否还有其余的方法？

深分页问题不论是面试还是日常开发中常常会遇到的问题，这和 limit 的语法个性无关，能够看上面的内容：

select * from film limit x,y;

limit 的语句的执行程序如下：

先依照列查找出所有的语句，如果有 where 语句则依据 where 查找出数据
查找数据并且退出后果集直到查找到（x+y）条数据为止。
抛弃掉后面的 x 条，保留 y 条。
返回剩下的 y 条数据。

针对 limit 咱们有上面的优化和解决计划：

1. 简略优化：

如果主键是 int 自增并且主键是逻辑合乎业务自增的，那么咱们能够应用上面的语句进行优化：

select * from film where id >= 10000 limit y;

2. 子查问优化：

自查问的优化形式是缩小回表次数的一种形式，咱们能够应用自查问的形式，因为不同业务之间存在不同的解决形式，这里给一个大抵的解决模板：

select * from film where ID in (select id from film where title = 'BANG KWAI') limit 10000,10

这样解决过后有两个长处：

查问转为搜寻索引列，并且不须要磁盘 IO。
尽管应用的是子查问，然而因为搜寻的是索引列，所以效率还是比拟高的。

3. 提早关联

和《高性能 Mysql》的形式一样，其实就是子查问形式的一种优化版本，优化的思路也是把过滤数据变为走索引之后在进行排除，因为上文曾经介绍过这里就不再赘述了。

总结：

对于深分页的问题咱们个别有上面的优化思路：

如果主键合乎自增或者合乎业务排序，能够间接通过id>xxx 而后 limit 搜寻数据。
如果通过排序能够正确搜寻相干数据，则能够间接排序之后取条数即可。
提早关联，提早关联有两种形式，第一种是应用 in 的子查问，第二种是应用 inner join，实质都是通过索引列的形式防止大数据的查找，同时转变为查索引的形式。
如果能够确认范畴，应用 between and 代替。

本节内容针对了一些实战过程中可能常常遇到的一些问题解决进行论述，其中略微有些难度的局部在索引下推和紧凑索引局部，这些个性

MySql 中文文档 – 8.2.1.15 GROUP BY 最佳化 | Docs4dev

MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.17 GROUP BY Optimization

正文完

mysql

发表至： mysql

2022-04-06

0

关于mysql:MySQL学习笔记10表空间回收

关于mysql:容器化-ClickHouse-on-K8s-部署篇建议收藏

关于mysql:MySQL-用户管理之-GRANT-授权

关于mysql:MySQL学习笔记5-约束

关于workflow:数据分析师干了专业数仓工程师的活自如是怎么做到的

关于mysql:三高Mysql-Mysql索引和查询优化偏实战部分

三高 Mysql – Mysql 索引和查问优化（偏实战局部）

前置筹备

sakila-db

work-bench

注释局部

where 查问太慢怎么办？

如何确认抉择用什么索引？

count()慢的起因是什么？

索引下推

涣散索引和紧凑索引

order by 如何优化？

函数操作索引生效的问题

order by rand()原理

分页查问慢怎么办？

总结

参考资料

站内搜索