松散索引和紧凑索引
关于松散索引和紧凑索引可以看下面两个文档对比参考阅读：
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
建议读者多读一读官方文档加深这两个概念理解。
order by如何优化？
什么是中间结果集？
对于常规的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表的字段一致。
这个就偷懒不实验了，绝大多数情况下表的字符集编码格式只要跟随表级别基本不会出现不一致的问题......
order by rand()原理
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 替代。
总结
本节内容针对了一些实战过程中可能经常遇到的一些问题处理进行阐述，其中稍微有些难度的部分在索引下推和紧凑索引部分，这些特性
