三高Mysql - Mysql索引和查询优化(偏实战部分)(下)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 三高Mysql - Mysql索引和查询优化(偏实战部分)(下)

松散索引和紧凑索引


关于松散索引和紧凑索引可以看下面两个文档对比参考阅读:

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对于多列联合索引分组查询进一步优化,提供了松散索引和紧凑索引多概念,


松散索引在官方有下面的定义:

  1. 当彻底使用索引扫描实现group by操作的时候,只需要使用部分的索引列就可以完成操作
  2. 虽然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这样的前缀索引是不适用的。

如果想要判定查询是否使用松散索引可以根据explainextra内容是否为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会把这个操作放到硬盘或者内存完成。

排序的基本步骤和原理

对于涉及排序的语句,它的大致工作原理如下:

  1. 选取查询字段,根据where进行条件查询。
  2. 查询结果集生成sort_buffer,如果内存不够,需要在硬盘建立中间表进行排序。
  3. 将中间表根据Order 字段进行排序。
  4. 回表生成完整结果集,组装返回结果。


中间结果集特点


如果中间表比较小则放到内存中,判定什么时候会存在于内存中Mysql提供了sort_buffer_size的参数,它负责控制中间结果集的大小,如果优化内存需要调整降低这个参数值,但是如果想要优化查询的时间,则需要调大这个参数。

回表生成完整结果集

回表生成完整结果集这个操作其实也不是总是执行的,会根据会话参数max_length_for_sort_data进行判断,如果当前查询小于这个数值,会生成一个全字段中间表结果可以直接从全字段中间表获取,但是如果大于这个数值那么就只会生成排序字段+主键中间表(类似二级索引),所以这时候显然查找一遍是无法找到的,需要回表才能完成操作。


需要注意排序字段+主键中间表看起来像是二级索引但是实际上和二级索引完全没有关系,只是一个简单列表需要反复去主表获取数据。


总结:全字段中间表>max_length_for_sort_data>排序字段+主键中间表,数值并不是越大越好越大越影响查询效率。

排序查询优化点

根本问题在于排序的结果是中间结果集,虽然结果集可以在内存中处理,但是他有最为本质的问题那就是中间表不存在索引并且导致索引失效,所以为了让中间表可以走索引我们可以使用索引覆盖的方式。


优化手段:索引覆盖,也是最高效的处理方式。索引覆盖可以跳过生成生成中间结果集,直接输出查询结果。


  1. order by的字段为索引(或者联合索引的最左边)。
  2. 其他字段(条件、输出)均在上述索引中。
  3. 索引覆盖可以跳过中间结果集,直接输出查询结果。


什么是索引覆盖?


覆盖索引:覆盖索引是查询方式而不是一个索引,指的是一个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


总结:提升排序查询速度

  1. order by字段增加索引,或者where字段使用索引,让查询可以走覆盖索引的方式。
  2. 调整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的内部处理,比如使用下面的方式进行处理:

  1. 查询数据表总数 total。
  2. total范围内,随机选取一个数字r。
  3. 执行下列的SQL:


select title,description from film limit r,1;


小结:

  1. order by rand() limit 这个查询的效率极其低下,因为他需要生成两次中间表才能获取结果,谨慎使用此函数。
  2. 解决方案有两种:
  • 临时解决方案:在主键的最大值和最小值中选取一个。
  • 好理解的方式处理:业务代码加limit处理
  1. 优点:在不改变业务的情况下直接通过调整SQL
    缺点:模板代码比较难以记忆,并且并不是万能的,因为可能不给你相关权限
  2. 建议使用业务逻辑代码处理不使用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的语句的执行顺序如下:

  1. 先按照列查找出所有的语句,如果有where语句则根据where查找出数据
  2. 查找数据并且加入结果集直到查找到(x+y)条数据为止。
  3. 丢弃掉前面的x条,保留y条。
  4. 返回剩下的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数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
10天前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
|
11天前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
15天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
|
20天前
|
搜索推荐 关系型数据库 MySQL
mysql like查询优化
通过合理的索引设计、使用全文索引、优化查询结构以及考虑分片和分区表,可以显著提高MySQL中 `LIKE`查询的性能。针对不同的应用场景选择合适的优化策略,能够有效地提升数据库查询效率,减少查询时间。希望这些方法和技巧能帮助您优化MySQL数据库中的模糊查询。
86 4
|
23天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
119 1
|
24天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
53 0
|
14天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
29 1
|
16天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
30 4
|
1月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
64 3
Mysql(4)—数据库索引
|
25天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
77 2

热门文章

最新文章

下一篇
无影云桌面