为什么用分布式主键 ID 在传统的单库单表结构时，通常可以使用自增主键来保证数据的唯一性。但在分库分表的情况下，每个表的默认自增步长为 1，这导致了各个库、表之间可能存在重叠的主键范围，从而使得主键字段失去了其唯一性的意义。  为了解决这一问题，我们需要引入专门的分布式 ID 生成器来生成全局唯一的 ID，并将其作为每条记录的主键，以确保全局唯一性。通过这种方式，我们能够有效地避免数据冲突和重复插入的问题，从而保障系统的正常运行。  除了满足唯一性的基本要求外，作为主键 ID，我们还需要关注主键字段的数据类型、长度对性能的影响。因为主键字段的数据类型、长度直接影响着数据库的查询效率和整体系统性能表现，这一点也是我们在选方案时需要考虑的因素。  内置算法 在 ShardingSphere 5.X 版本后进一步丰富了其框架内部的主键生成策略方案。此前仅提供了 UUID 和 Snowflake 两种策略，现在又陆续提供了 NanoID、CosId、CosId-Snowflake 三种策略。下面我们将逐个的过一下。  注意：SQL 中不要主动拼接主键字段（包括持久化工具自动拼接的）否则一律走默认的 Snowflake 策略！！！  ShardingSphere 中为分片表设置主键生成策略后，执行插入操作时，会自动在 SQL 中拼接配置的主键字段和生成的分布式 ID 值。所以，在创建分片表时主键字段无需再设置 自增 AUTO_INCREMENT。同时，在插入数据时应避免为主键字段赋值，否则会覆盖主键策略生成的 ID。  CREATE TABLE `t_order` (  `id` bigint NOT NULL,  `order_id` bigint NOT NULL,  `user_id` bigint NOT NULL,  `order_number` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,  `customer_id` bigint NOT NULL,  `order_date` datetime DEFAULT NULL,  `interval_value` varchar(125) COLLATE utf8mb4_general_ci DEFAULT NULL,  `total_amount` decimal(10,2) NOT NULL,  PRIMARY KEY (`order_id`) USING BTREE) ; UUID 想要获得一个具有唯一性的 ID，大概率会先想到 UUID，因为它不仅具有全球唯一的特性使用还简单。但并不推荐将其作为主键 ID。  UUID 的无序性。在插入新行数据后，InnoDB 无法像插入有序数据那样直接将新行追加到表尾，而是需要为新行寻找合适的位置来分配空间。由于 ID 无序，页分裂操作变得不可避免，导致大量数据的移动。频繁的页分裂会导致数据碎片化（即数据在物理存储上分散分布）。这种随机的 ID 分配过程需要大量的额外操作，导致频繁的对数据进行无序的访问，导致磁盘寻道时间增加。数据的无序性进一步加剧了数据碎片化，降低了数据访问效率。  UUID 字符串类型。字符串比数字类型占用更多的存储空间，对存储和查询性能造成较大的消耗；字符串类型的长度可变，可变长度的数据行会破坏索引的连续性，导致索引查找性能下降。  算法类型：UUID  spring:  shardingsphere:    rules:      sharding:        key-generators:  # 分布式序列算法配置          # UUID生成算法          uu-id-gen:            type: UUID        tables:          t_order:  # 逻辑表名称            actual-data-nodes: db$->{0..1}.t_order_${0..2} # 数据节点：数据库.分片表            database-strategy:  # 分库策略              standard:                sharding-column: order_id                sharding-algorithm-name: t_order_database_mod            table-strategy: # 分表策略              standard:                sharding-column: order_id                sharding-algorithm-name: t_order_table_mod            key-generate-strategy: # 分布式主键生成策略              column: id              keyGeneratorName: uu-id-gen NanoID 或许很多人都不熟悉 NanoID，它是一款用类似 UUID 生成唯一标识符的轻量级库。不过，与 UUID 不同的是 NanoID 生成的字符串 ID 长度较短，仅为 21 位。但仍然不推荐将它作为主键 ID，理由和 UUID 一样。  算法类型：NANOID  spring:  shardingsphere:    rules:      sharding:        key-generators:  # 分布式序列算法配置          # nanoid生成算法          nanoid-gen:            type: NANOID        tables:          t_order:  # 逻辑表名称            actual-data-nodes: db$->{0..1}.t_order_${0..2} # 数据节点：数据库.分片表            key-generate-strategy: # 分布式主键生成策略              column: id              keyGeneratorName: nanoid-gen 定制雪花算法 雪花算法是比较主流的分布式 ID 生成方案，在 ShardingSphere 中的 Snowflake 算法生成的是 Long 类型的 ID，通常作为默认的主键生成策略使用。  内置的雪花算法生成的 ID 主要由时间戳、工作机器 IDworkId、序列号 sequence 三部分组成。  @Override  public synchronized Long generateKey() {      ..........      return ((currentMilliseconds - EPOCH) << TIMESTAMP_LEFT_SHIFT_BITS) | (getWorkerId() << WORKER_ID_LEFT_SHIFT_BITS) | sequence;  } 定制 Snowflake 算法有三个可配置的属性：  worker-id：工作机器唯一标识，单机模式下会直接取此属性值计算 ID，默认是 0；集群模式下则由系统自动生成，此属性无效  max-vibration-offset：最大抖动上限值，范围 [0, 4096)，默认是 1。那么如何理解这个属性呢？ 这个属性是用来控制上边生成雪花 ID 中的 sequence。通过限制抖动范围，同一毫秒内生成的 ID 中引入微小的变化，让数据更均匀地分散到不同的分片上。  private void vibrateSequenceOffset() {    sequenceOffset = sequenceOffset >= maxVibrationOffset ? 0 : sequenceOffset + 1;} 若使用此算法生成值作分片值，建议配置此属性。此算法在不同毫秒内所生成的 key 取模 2^n (2^n 一般为分库或分表数) 之后结果总为 0 或 1。为防止上述分片问题，建议将此属性值配置为 (2^n)-1  max-tolerate-time-difference-milliseconds：最大容忍时钟回退时间（毫秒）。服务器在校对时间时可能会发生时钟回拨的情况（当前时间回退），由于根据时间戳参与计算 ID，这可能导致生成相同的 ID，而这对系统来说是不可接受的。  ShardingSphere 雪花算法针对时钟回拨场景进行了处理，记录最后一次生成 ID 的时间 lastMilliseconds，并与回拨后的当前时间 currentMilliseconds 进行比对。如果时间差超过了设置的最大容忍时钟回退时间，系统将直接抛出异常；如果未超过，则系统会休眠等待两者时间差的时长，核心原则确保不会发放重复的 ID。  @SneakyThrows(InterruptedException.class)private boolean waitTolerateTimeDifferenceIfNeed(final long currentMilliseconds) {    if (lastMilliseconds <= currentMilliseconds) {        return false;    }    long timeDifferenceMilliseconds = lastMilliseconds - currentMilliseconds;    Preconditions.checkState(timeDifferenceMilliseconds < maxTolerateTimeDifferenceMilliseconds,            "Clock is moving backwards, last time is %d milliseconds, current time is %d milliseconds", lastMilliseconds, currentMilliseconds);    Thread.sleep(timeDifferenceMilliseconds);    return true;} 算法类型：SNOWFLAKE  spring:  shardingsphere:    rules:      sharding:        key-generators:  # 分布式序列算法配置          # 雪花ID生成算法          snowflake-gen:            type: SNOWFLAKE            props:              worker-id: # 工作机器唯一标识              max-vibration-offset: 1024 # 最大抖动上限值，范围[0, 4096)。注：若使用此算法生成值作分片值，建议配置此属性。此算法在不同毫秒内所生成的 key 取模 2^n (2^n一般为分库或分表数) 之后结果总为 0 或 1。为防止上述分片问题，建议将此属性值配置为 (2^n)-1              max-tolerate-time-difference-milliseconds: 10 # 最大容忍时钟回退时间，单位：毫秒        tables:          t_order:  # 逻辑表名称            actual-data-nodes: db$->{0..1}.t_order_${0..2} # 数据节点：数据库.分片表            key-generate-strategy: # 分布式主键生成策略              column: id              keyGeneratorName: snowflake-gen CosId CosId 是一个高性能的分布式 ID 生成器框架，Shardingsphere 将其引入到自身的框架内，只简单的使用了 CosId 算法。但目前亲测 5.2.0 版本该算法处于不可用状态！！！我已经给官方提了 issue，看看他们咋回复吧。  CosId 框架内提供了 3 种算法：  SnowflakeId: 单机 TPS 性能：409W/s , 主要解决时钟回拨问题 、机器号分配问题并且提供更加友好、灵活的使用体验。 SegmentId: 每次获取一段 (Step) ID，来降低号段分发器的网络 IO 请求频次提升性能，提供多种存储后端：关系型数据库、Redis、Zookeeper 供用户选择。 SegmentChainId(推荐): SegmentChainId (lock-free) 是对 SegmentId 的增强。性能可达到近似 AtomicLong 的 TPS 性能 12743W+/s。 该算法使用对外提供了两个属性：  id-name：ID 生成器名称。  as-string：是否生成字符串类型 ID，将 long 类型 ID 转换成 62 进制 String 类型（Long.MAX_VALUE 最大字符串长度 11 位），并保证字符串 ID 有序性。  算法类型：COSID  spring:  shardingsphere:    rules:      sharding:        key-generators:  # 分布式序列算法配置          # COSID生成算法          cosId-gen:            type: COSID            props:              id-name: share              as-string: false        tables:          t_order:  # 逻辑表名称            actual-data-nodes: db$->{0..1}.t_order_${0..2} # 数据节点：数据库.分片表            key-generate-strategy: # 分布式主键生成策略              column: id              keyGeneratorName: cosId-gen CosId-Snowflake CosId-Snowflake 是 CosId 框架内提供的 Snowflake 算法，它的实现原理和上边的定制版雪花算法类似，ID 主要也是由时间戳、工作机器 ID、序列号 sequence 三部分组成。同样处理了时钟回拨等问题。  public synchronized long generate() {    long currentTimestamp = this.getCurrentTime();    if (currentTimestamp < this.lastTimestamp) {        throw new ClockBackwardsException(this.lastTimestamp, currentTimestamp);    } else {        if (currentTimestamp > this.lastTimestamp && this.sequence >= this.sequenceResetThreshold) {            this.sequence = 0L;        }        this.sequence = this.sequence + 1L & this.maxSequence;        if (this.sequence == 0L) {            currentTimestamp = this.nextTime();        }        this.lastTimestamp = currentTimestamp;        long diffTimestamp = currentTimestamp - this.epoch;        if (diffTimestamp > this.maxTimestamp) {            throw new TimestampOverflowException(this.epoch, diffTimestamp, this.maxTimestamp);        } else {            return diffTimestamp << (int)this.timestampLeft | this.machineId << (int)this.machineLeft | this.sequence;        }    }} 这个算法提供了两个属性：  epoch：固定的起始时间点，雪花 ID 算法的 epoch 变量值，默认值：1477929600000。用它的目的提高生成的 ID 的时间戳部分的可读性、稳定性和范围限制，使得生成的 ID 更加可靠和易于管理。  as-string：是否生成字符串类型 ID，将 long 类型 ID 转换成 62 进制 String 类型（Long.MAX_VALUE 最大字符串长度 11 位），并保证字符串 ID 有序性。  算法类型：COSID_SNOWFLAKE  spring:  shardingsphere:    rules:      sharding:        key-generators:  # 分布式序列算法配置          # cosId-snowflake生成算法          cosId-snowflake-gen:            type: COSID_SNOWFLAKE            props:              epoch: 1477929600000              as-string: false        tables:          t_order:  # 逻辑表名称            actual-data-nodes: db$->{0..1}.t_order_${0..2} # 数据节点：数据库.分片表            key-generate-strategy: # 分布式主键生成策略              column: id              keyGeneratorName: cosId-snowflake-gen 自定义分布式主键 上边咱们介绍了 ShardingSphere 内提供的 5 种生成主键的 ID 算法，这些算法基本可以满足大部分的业务场景。不过，在某些情况下，我们可能会要求生成的 ID 具有特殊的含义或遵循特定的规则。ShardingSphere 也支持我们自定义生成主键 ID，来满足定制的业务需求。  实现接口 要实现自定义的主键生成算法，首先需要实现 KeyGenerateAlgorithm 接口，并实现内部 4 个方法， 其中有两个方法比较关键：  getType()：我们自定义的算法类型，方便配置使用；  generateKey()：处理主键生成的核心逻辑，我们可以根据业务需求选择合适的主键生成算法，比如美团的 Leaf、滴滴的 TinyId 等。  @Data@Slf4jpublic class SequenceAlgorithms implements KeyGenerateAlgorithm {    // 这个方法用于指定我们自定义的算法的类型。它会返回一个字符串，表示所使用算法的类型，方便在配置和识别时使用。    @Override    public String getType() {        // 返回算法类型表示        return "custom";    }    // 这是生成主键的核心逻辑所在。在这个方法内部，我们可以根据业务需求选择合适的主键生成算法，比如美团的Leaf、滴滴的TinyId等。这个方法的具体实现会根据所选算法的特点和要求来设计    @Override    public Comparable<?> generateKey() {        return null;    }    @Override    public Properties getProps() {        return null;    }    // 这个方法用于初始化主键生成算法所需的资源或配置    @Override    public void init(Properties properties) {    }} 在引入外部的分布式 ID 生成器时，应尽量遵循以下原则：  全局唯一：必须保证 ID 是全局性唯一的，基本要求  高性能：高可用低延时，ID 生成响应要块，否则反倒会成为业务瓶颈  高可用：100% 的可用性是骗人的，但是也要无限接近于 100% 的可用性  好接入：要秉着拿来即用的设计原则，在系统设计和实现上要尽可能的简单  SPI 注册 通过 SPI 方式加载我们自定义的主键算法，需要在 resource/META-INF/services 目录下创建一个文件，文件名为 org.apache.shardingsphere.sharding.spi.KeyGenerateAlgorithm，并将我们自定义的主键算法的完整类路径放入文件内，每行一个。在系统启动时会自动加载到这个文件，读取其中的类路径，然后通过反射机制实例化对应的类，完成主键算法的注册和加载。  resource    |_META-INF        |_services           |_org.apache.shardingsphere.sharding.spi.KeyGenerateAlgorithm 配置使用 上边完成了自定义算法的逻辑，使用上与其他的算法一致。只需将我们刚刚定义的算法类型 custom 配置上即可。  spring:  shardingsphere:    rules:      sharding:        key-generators:  # 分布式序列算法配置          # 自定义ID生成策略          xiaofu-id-gen:            type: custom        tables:          t_order:  # 逻辑表名称            actual-data-nodes: db$->{0..1}.t_order_${0..2} # 数据节点：数据库.分片表            key-generate-strategy: # 分布式主键生成策略              column: id              keyGeneratorName: xiaofu-id-gen 当执行插入操作时，debug 看已经进入到了定义的主键算法内了。   总结 我们介绍了 ShardingSphere 的几种内置主键生成策略以及如何自定义主键生成策略，市面上还有许多优秀的分布式 ID 框架都可以整合进来，但具体选择何种策略还是要取决于自身的业务需求。  ———————————————— 原文作者：&#31243;&#24207;&#21592;&#23567;&#23500; 转自链接：https://learnku.com/articles/86513 版权声明：著作权归作者所有。商业转载请联系作者获得授权，非商业转载请保留以上作者信息和原文链接。 

最近由于navicat到期了，没续了。打算用用dbeaver。dbeaver是免费和开源（GPL）为开发人员和数据库管理员通用数据库工具。家用完全足够了。但是在配置数据库连接的时候遇到错误：DBeaver连接MySQL提示“Public Key Retrieval is not allowed”。Public Key Retrieval is not allowed：不允许进行公钥检索。在“连接设置”中选择“驱动属性”，将“allowPublicKeyRetrieval”值改为“TRUE”，点击确定，再次连接就可以连接成功了。验证，点击连接成功

你踩过哪些sql查询时索引使用不合理的坑？欢迎畅所欲言

row_number() over(partition by 分组字段order by 排序字段 desc）用于对数据进行分组排序，并对每个组中的数据分别进行编号编号从1开始递增，每个组内的编号不会重复原文链接：https://blog.csdn.net/weixin_43803780/article/details/134685708

1、分组不连续排序（跳跃排序） rank() over(partition by order by )  partition by用于对数据进行分组，它和聚合函数使用group by分组不同的地方在于它能够返回一个分组中的多条记录，而聚合函数一般只返回一条反映统计值的记录。 order by用于对每个分组内的记录进行排序。 有两个相同值都排第二名时，接下来就是第四名（同样是在各个分组内）。 举个例子： 模拟一个场景，有一个比较时髦的学校决定借助大数据技术来提高教学质量，其中就有一张表存放了全校每个学生的考试成绩，按照学期进行分区，创建这张表：  create table t_score (   class string,   name string,   score int ) partitioned by (term string);   insert into t_score partition (term="201702") values ("一班", "小黑", 80), ("一班", "小白", 90), ("一班", "小赤", 100), ("二班", "小橙", 80), ("二班", "小红", 90), ("二班", "小绿", 100), ("三班", "小青", 90), ("三班", "小蓝", 100), ("三班", "小紫", 100); 现在校长想知道在2017年下学期的考试中一年级三个班级的学生考试分数的排名情况：  select *, rank() over (partition by class order by score desc) from t_score where term="201702"; 仔细看下查询结果，我们会发现这样一种情况，三班的排名出现了两个并列第一，然后紧接着就是第三名，没有第二名了，按照我们一般的想法，如果有并列的话那么后面的就会排名提前，使用dense_rank可以实现这个效果。  2、分组连续排序 dense_rank() over(partition by order by ) select *, dense_rank() over (partition by class order by score desc) from t_score where term="201702";  三班的两个相同分数并列第一，然后紧接着就是第二名。 dense的意思是稠密的，dense_rank()稠密意味着生成的排名序列中没有空隙（连续的），而rank()生成的排名序列中可能有空隙（可能是不连续的）。 但是这时候校长不高兴了，他不喜欢这种并列的排名方式，他说要重新制定排名规则：  首先按照成绩排序 成绩相同的不要并列，而是再按照姓名排序，姓氏靠后的认倒霉吧 对于成绩和姓名都完全相同的情况，校长大人没有指定就假装不存在这种情况好啦 没办法，校长最大，只能再改下我们的sql，因为rank在生成排名序列的时候都会出现并列的情况，稀的稠的都不行，所以不能采用rank这种方式了，不过没事我们还有招，还有一个叫做row_number的函数，它不考虑并列的情况，就是单纯的排序，按照顺序挨个的发序号。  3、分组不会出现相同排序 row_number() over(partition by order by ) row_number()不会出现相同排序，就算两条记录参与排序的字段数值一样，排序也是不一样。 select *, row_number() over (partition by class order by score desc, name) from t_score where term="201702";  没有出现并列的情况，最后校长又补充了一个需求，就是不分班级统计排名，而是全年级拉通排名。  4、不分组排序 rank() over(order by )  partition by如果没有指定的话，那么它把整个结果集作为一个分组，即不分组排序  select *, row_number() over (order by score desc, name) from t_score where term="201702";  总结一下：  rank / dense_rank / row_number的语法都是一样的，不同的只是几个特性：  rank / dense_rank / row_number从1开始排序，均返回bigint数据类型字段； rank / dense_rank都考虑了并列的情况，所以序号可能不唯一（所以不要用rank() 和dense_rank()函数来剔重），rank在出现并列之后会不连续，而dense_rank是连续的； row_number不考虑并列的情况，所以序号是唯一的（可以使用row_number()来删除重复数据），并且也不会出现序号不连续。 ————————————————                   原文链接：https://blog.csdn.net/weixin_67601403/article/details/133065452 

partition by 关键字 partition by 在开窗函数中，常用于表示某个分区，规则了数据的范围 order by 关键字 order by 常用于对分区内的数据进行排序，常见的情况下，order by还能规定sql语句的影响范围。 rows between unbounded preceding and current rows 表示受影响范围为从第一行到当前行 若没有rows ... between语句，表示从第一行至最后一行 max() 函数 在max() over()函数中，表示取一个分区内的最大值，与聚合max()不同， 开窗函数的max()将会产生多行结果，并且受到partition by 与 order by 影响 例如，求查询所有选修"英语"的学生成绩与最高分的分数差距，按成绩降序排序  可以按照如下做法  1.对分数进行开窗 max(score) over() max_score  max受窗口函数的分区关键字 partition by 与order by影响，每行的最大值可能会有所不同，去掉关键字后，全局一致。  2.求分数差值，并排序 3.最终sql select     cid,     sid,     score,     max_score - score as score_diff from ( select     cid,     sid,     score,     max(score) over() max_score from SC sc  join Course c on sc.cid = c.cid where c.cname = '英语' )t1 order by score  数据展示 1.在用户商品订单最近一日汇总表中，按照用户id排序，求当前最大的订单下单总金额  select     user_id,     sku_id,     order_total_amount_1d,     max(order_total_amount_1d) over(order by user_id rows between unbounded preceding and current row ) max_price from user_sku__1d 受rows between影响， 最大价格max_price 取决于所在行数。  这里就体现了order by的行数影响，影响的是全局还是到当前行。 ————————————————         原文链接：https://blog.csdn.net/qq_44835418/article/details/135331036 

前期数据准备 # 创建数据库 create database if not exists shopping charset utf8; # 选择数据库 use shopping; # 创建产品表 create table product (     id      int primary key,     name    varchar(20),     price   int,     type varchar(20),     address varchar(20) ); # 插入产品数据 insert into shopping.product(id, name, price, type, address) values     (1,'商品1',200,'type1','北京'),     (2,'商品2',400,'type2','上海'),     (3,'商品3',600,'type3','深圳'),     (4,'商品4',800,'type1','南京'),     (5,'商品5',1000,'type2','成都'),     (6,'商品6',1200,'type3','武汉'),     (7,'商品7',1400,'type1','黑龙江'),     (8,'商品8',1600,'type2','黑河'),     (9,'商品9',1800,'type3','贵州'),     (10,'商品10',2000,'type1','南宁'); 一、PARTITION BY与GROUP BY区别 一、函数类型 group by 是分组函数，partition by是分析函数  二、执行顺序 from > where > group by > having > order，而partition by应用在以上关键字之后，可以简单理解为就是在执行完select之后，在所得结果集之上进行partition by分组  二、查询结果 partition by 相比较于group by，能够在保留全部数据的基础上，只对其某些字段做分组排序，而group by则保留参与分组的字段和聚合函数的结果，类似excel中的透视表  二、PARTITION BY的基本用法 在OVER()中添加PARTITION BY # 查询每种商品的id，name，同类型商品数量 select id,name,count(*) over (partition by type) from product; PARTITION BY传入多列 # 查询每个城市每个类型价格最高的商品名称 select        name,        price,        type,        max(price) over (partition by address,type) as 'max_price' from product; ————————————————                 原文链接：https://blog.csdn.net/feizuiku0116/article/details/126127948 

前言 partition by与group by都是对表中的某维度进行分组。不同的是partition by返回的是分组后的每一条记录，不改变表中数据行数，后续可以做排序、topN等操作；而 group by返回的是分组的聚合值，例如max、sum、avg等值。一、窗口函数 1.基本语法： <窗口函数> over ( partition by<用于分组的列名> order by <用于排序的列名> desc) as "rank_col" 执行顺序为： 1、根据 <用于分组的列名> 进行分组操作（partition by），得到分组结果（中间表）； 2、对结果的每个分组进行组内（desc降序）排序：order by <用于排序的列名>（中间表）； 3、将窗口函数用于上述结果的每个分组（over）：增加组内排序序号列"rank_col"。窗口函数包括rank()，dense_rank()，row_number()等。 以上过程生成了一个分组、组内排序、增加组内排序序号列的结果。  rank()函数：如果有并列名次的行，会占用下一个名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，所以结果是：1，1，1，4. dense_rank()函数：如果有并列的名次，它不会占用下一个名次的位置，比如比如正常排名是1，2，3，4，但是现在前3名是并列的名次，所以结果是：1，1，1，2. row_number()函数：不考虑并列的情况，比如前3名是并列的名次，排名是正常的1，2，3，4.  2.示例 [LC185]. 部门工资前三高的所有员工  公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。编写解决方案，找出每个部门中 收入高的员工 。 输出格式要求如下：  分析： 题目要求是找出 每个部门中 排名前三的员工（partition by 部门），且相同收入水平并列、不占用后续排序位置（dense_rank()）。 写sql前，最好把过程先想清楚，把每个中间子表想清楚，把重要的中间子表可以查出来看看，最后再完善代码，且不要上来就搞代码。思路如下：  1、先把最核心的计算写出来 分组以及组内排序：  select *, dense_rank() over(partition by departmentId order by salary desc) as rank_col from Employee  按分组排序输出了，且增加了排序列rank_col，但是没有限制前三。  2、从上面的结果中，取每组的前三 把上面的结果当作子表查询  select * from( select *, dense_rank() over(partition by departmentId order by salary desc) as rank_col from Employee ) a where a.rank_col <=3 到这里，核心的计算算是完成了，实现了 每个部门中排名前三，且相同收入水平并列、不占用后续排序位置的要求。下一步，要按照规定格式输出。  3、按要求格式输出 继续把上面的结果当作子表查询  select d.name Department, b.name Employee,b.salary Salary  from  (select * from( select *, dense_rank() over(partition by departmentId order by salary desc) as rank_col from Employee ) a where a.rank_col <=3) b left join Department d on b.departmentId = d.id 输出正确，测试通过。  4、sql优化 分组以及组内排序后，直接join，节省一个中间子表  select d.name Department, a.name Employee,a.salary Salary from (select *, dense_rank() over(partition by departmentId order by salary desc) as "rank" from Employee ) a left join Department d on a.departmentId  = d.id where a.rank <4 输出正确，测试通过。 ————————————————  原文链接：https://blog.csdn.net/weixin_43962853/article/details/136317759 

MySQL 是一个开放源码的小型关联式数据库管理系统，开发者为瑞典 MySQL AB 公司。目前 MySQL 被广泛地应用在 Internet 上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。MySQL 的特性有使用 C 和 C++ 编写，并使用了多种编译器进行测试，保证源代码的可移植性。支持 AIX、BSDi、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、NetBSD、OpenBSD、OS/2 Wrap、Solaris、SunOS、Windows 等多种操作系统。为多种编程语言提供了 API。这些编程语言包括 C、C++、C#、Delphi、Eiffel、Java、Perl、PHP、Python、Ruby 和 Tcl 等。支持多线程，充分利用 CPU 资源，支持多用户。优化的 SQL 查询算法，有效地提高查询速度。既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入到其他的软件中。提供多语言支持，常见的编码如中文的 GB 2312、BIG5，日文的 Shift_JIS 等都可以用作数据表名和数据列名。提供 TCP/IP、ODBC 和 JDBC 等多种数据库连接途径。提供用于管理、检查、优化数据库操作的管理工具。可以处理拥有上千万条记录的大型数据库。GROUP BY 子句通常用于聚合函数（例如 SUM，COUNT，AVG 等）的计算，以便将多行数据组合成单个行，并根据聚合结果对数据进行分组。它可以将结果分为不同的组，其中每个组包含具有相同值的一组行，并且可以根据一项或多项列来指定分组。例如：  SELECT country, city, COUNT(*) as count FROM customers GROUP BY country, city 以上 SQL 查询将在 customers 表中将客户按照所在国家和城市进行分组，并计算每个组中客户的数量。  PARTITION BY 子句用于对结果数据集进行分区（分组），然后使用聚合函数（例如 SUM，COUNT，AVG 等）来计算每个分区内的值。与 GROUP BY 不同的是，PARTITION BY 不是仅用于聚合结果的分组策略，而是仅分区未聚合的结果集。这个关键字通常与窗口函数一起使用，对每个分区执行排名、排序和聚合等操作。例如：  SELECT *,        SUM(quantity) OVER (PARTITION BY order_date) as total_quantity FROM orders 以上 SQL 查询将在 orders 表中，将订单按照订单日期进行分区，并计算结果中每个分区的 quantity 列的总和，并将结果添加到每一行。  因此，PARTITION BY 和 GROUP BY 都将数据集分成多个分组，然而，它们的主要区别是 GROUP BY 定义了聚合条件，而 PARTITION BY 是仅用于分组结果集并对每个分区执行排名、排序和聚合等分析函数。 ————————————————                        原文链接：https://blog.csdn.net/gly1653810310/article/details/134185783 

聚合函数sum在窗口函数中，对自身记录以及位于自身以上的数据进行求和，如课程号0002对应的学号0002后面的sum结果就是课程号0002中学号为0001和0002对应的成绩之和，课程号0002对应的学号0003后面的sum结果就是课程号0002中学号0001、0002和0003对应的成绩之和。以上窗口函数，用了rows和preceding这两个关键字，是“之前~行“的意思，在上面也就是之本篇文章主要是以下内容：1.窗口函数：partition by窗口函数 和 group by分组的区别：partition by关键字是分析性函数的一部分，它和聚合函数（如group by）不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录。partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。partition by与group by不同之处在于前者返回的是分组里的每一条数据，并且可以对分组数据进行排序操作。后者只能返回聚合之后的组的数据统计值的记录。partition by相比较于group by，能够在保留全部数据的基础上，只对其中某些字段做分组排序（类似excel中的操作），而group by则只保留参与分组的字段和聚合函数的结果分区函数Partition By的用法_partitionby_惊寂123的博客-CSDN博客1)窗口函数的基本语法如下：<窗口函数> over ( partition by<用于分组的列名> order by <用于排序的列名>)2)以上语法中<窗口函数>的位置，可以放置以下函数：窗口函数是对where或者group by子句处理后的结果进行处理，所以窗口函数原则上只能写上select子句中。2.如何使用窗口函数？1)专用窗口函数rank。若要在每个班级内按成绩排名，则sql语句则为：select *, rank() over (partition by 班级 order by 成绩 desc) as ranking from 班级表;以上sql语句中的select子句，rank是排序的函数，要求是“每个班级内按成绩排名”。这句话分为两部分理解：a)每个班级内：按班级分组partition by用来对表分组，在这个例子中，需要按“班级”进行分组(partition by 班级)b)按成绩排名：order by子句的功能是对分组后的结果进行排序，默认按升序排列，但是在本例中用了desc，表示按降序排序。2）窗口函数已经具备了前几节中group by和order by子句的分组和排序的功能，但仍要用窗口函数是因为，group by分组汇总后改变了表的行数，一行只有一个类别，而partition by和rank函数不会减少原表中的行数。-- group by分组汇总改变行数 select 班级,count(学号) from 班级表 group by 班级 order by 班级;-- partition by分组汇总行数不变 select 学号, count(学号) over (partition by 班级 order by 班级) as current_count from 班级表;“窗口函数”之所以叫“窗口”函数，是因为partition by分组后的结果就称为“窗口”,这里的窗口是表示“范围”的意思。3）窗口函数主要有以下功能：a.同时具备分组和排序的功能b.不减少原表的行数c.语法如下：<窗口函数> over ( partition by<用于分组的列名> order by <用于排序的列名>)3.其他专用窗口函数1)专用窗口函数rank,dense_rank,row_number有什么区别？-- 专用窗口函数rank,dense_rank(),row_number的区别 select *, rank() over (order by 成绩 desc) as ranking, dense_rank() over (order by 成绩 desc) as dese_rank, row_number() over (order by 成绩 desc) as row_num from 班级表;从以上结果来看：rank()函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一个名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，所以结果是：1，1，1，4.dense_rank()函数：这个例子中是5位，5位，5位，6位，也就是如果有并列的名次，它不会占用下一个名次的位置，比如比如正常排名是1，2，3，4，但是现在前3名是并列的名次，所以结果是：1，1，1，2.row_number()函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列的情况，比如前3名是并列的名次，排名是正常的1，2，3，4.最后需要注意的是，以上三个专用窗口函数，函数后面的括号不需要任何参数，保持括号()为空即可。案例1：面试经典排名问题当涉及到排名问题时，可以使用窗口函数，但使用窗口函数前，要注意区份rank()函数、dense_rank()函数以及row_number()函数的区别。例子：编写一个sql查询来实现分数排名，若两个分数相同，则分数排名相同。请注意：平分后下一个名字应该是下一个连续的整数值，换句话说就是，名次之间不该有“间隔”。因此考虑用dense_rank()函数。sql查询语句应为：select *, dense_rank() over (order by 成绩 desc) as dens_rank from 班级表;案例2：面试经典topN问题工作中常会遇到这样的业务问题：找出每个国家中进口最多的产品是哪个？找出每个国家进口贸易前5的商品是什么？诸如此类问题，都是常见的：分组取每组最大值，最小值，每组最大的N条(top N)记录。面对这类问题，我们将通过以下例子给出答案？成绩表里包含了学生的学号，课程号(学生选修课程的课程号),成绩(学生选修该课程取得的成绩).1)分组取每组最大值：按课程号分组取成绩最大值所在行的数据。(由于分组group by和汇总函数得到的是每组的一个值（最大值，最小值或平均值)，而无法得到对应那一行的所有数据，所以group by不可用）。因此我们可以使用关联子查询来实现：select * from score as a where 成绩=(select max(成绩) from score as b where a.课程号=b.课程号);以上查询结果中课程号0001有2行数据是因为最大成绩80有2个。2)分组取每组最小值：按课程号分组取成绩最小值所在行的数据。select * from score as a where 成绩=(select min(成绩) from score as b where a.课程号=b.课程号);3)每组最大的N条记录：案例：现有“进口贸易表”，记录了每个国家各商品的进口额，表内容如下。问题：查找每个国家进口额最大的2个商品。解题思路：1.看到问题中要查找“每个”国家进口额最高的商品。当题目中出现“每个”时，首先要想到分组。这里指每个国家，所以要按国家来分组。2.当表按国家分组后，按进口额降序排列，排在最前面2个就是我们想要查找的进口额最大的2个商品。3.分组排序后，不能减少原表的行数，所以要用窗口函数。4.对比各窗口函数，为不受并列进口额的影响，于是决定用row_number。解题步骤：步骤一：按国家分组(partition by 国家)、并按进口额降序排列(order by 进口额 desc),套入窗口函数后，sql语句为:select *, row_number() over (partition by 国家 order by 进口额 desc) as ranking from 进口贸易表;步骤二：上表中红色框框内的数据，就是每个国家进口额最大的两个商品，也就是题目的解。要想得到只有这些解的答案，只需要提取出“ranking“值小于等于2的数据即可。这时候只需要在之前的sql语句中加入条件子句where就可以了。但是这样就会报错，原因是sql的书写顺序和运行顺序不一致，在运行过程中，select语句是最后运行的。因此不能将sql语句写成如下：select *, row_number() over (partition by 国家 order by 进口额 desc) as ranking from 进口贸易表 where rangking<=2;以上出错原因就是因为我们以为运行顺序是按书写顺序来运行的，这样是不对的，所以运行sql子句的时候，就会出错。运行where ranking的时候，select子句还没有运行，ranking列还未出现。步骤三：这时候只能采用子查询，将第一步得到的查询结果作为一个新表，最后再运行where子句。select * from (select *, row_number() over (partition by 国家 order by 进口额 desc) as ranking from 进口贸易表) as a where rangking<=2;举一反三：经典topN问题：每组最大的N条记录。这类问题既涉及分组，又涉及排序，这时候要用窗口函数来实现，这时候只需要将where子句中的2改成N即可。select * from (select *, row_number() over (partition by 要分组的列名 order by 要排序的列名 desc) as ranking from 表名) as a where rangking<=N;4.聚合函数作为窗口函数聚合函数作为窗口函数和专用窗口函数用法相同，只需要把聚合函数写在窗口函数的位置即可，但聚合函数括号里不能为空，必须写好聚合的列名。select *, sum(成绩) over ( partition by 课程号 order by 学号) as current_sum, avg(成绩) over (partition by 课程号 order by 学号) as current_avg, max(成绩) over (partition by 课程号 order by 学号) as current_max, min(成绩) over (partition by 课程号 order by 学号) as current_min, count(成绩) over (partition by 课程号 order by 学号) as current_count from score;聚合函数sum在窗口函数中，对自身记录以及位于自身以上的数据进行求和，如课程号0002对应的学号0002后面的sum结果就是课程号0002中学号为0001和0002对应的成绩之和，课程号0002对应的学号0003后面的sum结果就是课程号0002中学号0001、0002和0003对应的成绩之和。除此之外，avg()、max()、min()等聚合函数作为窗口函数时，结果都与sum()函数类似。这样使用窗口函数的用处是：聚合函数作为窗口函数，可以在每一行的数据里直观看到，截止到本行数据，统计数据有多少，最大值、最小值是多少等，从而可以看出每一行数据，对整体数据的影响。举例：累计求和问题下表为确诊人数表，包含日期和该日期对应的新增确诊人数，按照日期进行升序排列，查找日期，确诊人数以及对应的累计确诊人数。select 日期,确诊人数, sum(确诊人数) over(order by 日期) as 累计确诊人数 from 确诊人数表;案例：如何在每个组里比较题目：现有进口贸易表，记录了每个国家各商品的进口额，表内容如下：问题：查找单个商品进口额高于该商品平均进口额的国家名单。解题思路：1.查找单个商品高于该商品平均进口额，也就是要在每个商品里比较，这就涉及到分组，而sql中有分组功能的就：group by和窗口函数partition by。2.使用聚合函数avg()求出每个商品的平均进口额后，找出进口额大于平均进口额的数据。并且要求分组后不减少原表的行数。3.由于group by分组汇总后会改变表的行数，一行只有一个类别，而partition by不会减少原表行数，因此用partition by。解题步骤：1.将avg()作为窗口函数，将每个商品的平均进口额求出。select *, avg(进口额) over (partition by 商品编码 ) as 平均进口额 from 进口贸易表;2.在第1步的基础上，筛选出大于平均进口额的数据即可。这时，就需要在上一步的sql语句中加入条件子句where即可。在写sql子句前，要注意sql的书写顺序与运行顺序。select * from (select *, avg(进口额) over (partition by 商品编码 ) as 平均进口额 from 进口贸易表) as b where 进口额>平均进口额;举一反三：查找每个组里大于平均值的数据，可以有两种方法：1）使用以上的窗口函数2）使用关联子查询。5、窗口函数的移动平均select *, avg(成绩) over (order by 学号 rows 2 preceding) as current_avg from score;以上窗口函数，用了rows和preceding这两个关键字，是“之前~行“的意思，在上面也就是之前2行的意思，也就是得到的结果是自身记录及前2行的平均。例如学号0002、课程号0002成绩60的结果为：学号0001课程号0002和学号0001课程号0003以及学号0002、课程号0002对应的三个成绩的平均值。也就是学号0002课程号0002这位以及其前两行同学的平均成绩。想要计算当前行与前n行(共n+1行)的平均时，只有调整rows 与preceding中间的数字即可。这样使用窗口函数注意是可以通过preceding关键字调整作用范围，在以下的场景中非常适用：在公司业绩名单排名中，可以通过移动平均，直观地查看与相邻名次业绩的平均、求和等统计数据。6.窗口函数总结1）注意事项：partition 子句可以省略，省略时就是不指定分组，且窗口函数原则上只能写在select子句中2）窗口函数语法：select * <窗口函数> over (partition by <分组的列名> order by <排序的列名>) as <自己定义的列名> from 从哪张表中查找;其中窗口函数的位置可以放：a.专用窗口函数：rank()、dense_rank()、row_number等。b.聚合函数：sum()、avg()、max()、min()等。3)窗口函数的功能：a.同时具备分组partition by和排序order by的功能。b.不减少原表的行数，所以经常用来在每组内排名。4）窗口函数使用场景：7、触发器触发器概念：触发器是一种特殊的存储过程，它在试图更改触发器所保护的数据时自动执行。触发器与存储过程的异同相同点：1. 触发器是一种特殊的存储过程，触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段。不同点：2. 存储器调用时需要调用SQL片段，而触发器不需要调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动调用。MySQL的触发器_mysql触发器_莱维贝贝、的博客-CSDN博客8.存储过程。MySQL中的存储过程（详细篇）_mysql存储过程学习_普通网友的博客-CSDN博客（学习该博客）1）在工作中经常遇到重复性的工作，这时候就可以把常用的sql写好存储起来，这个过程就是存储过程。这样下次遇到同样的问题，就可以直接使用存储过程了，这样就可以极大地提高工作效率。2）如何使用存储过程？使用存储过程需要先定义存储过程，然后是使用已经定义好的存储过程。a.无参数的存储过程。定义存储过程的语法形式：create procedure 存储过程名称() begin <sql语句> ;end;语法中的begin……end用于表示sql语句的开始和结束。语法中的sql语句就是重复的sql语句。举个例子：查找进口贸易表中的国家名称。sql语句就是：select 国家from 进口贸易表;把这个sql语句放入存储过程的语法里，并给这个存储过程起名叫a_trade1：create procedure a_trade1 () begin select 国家from 进口贸易表;end;在navicat-查询中运行后，建立的存储过程就会出现在以上位置，这样下次就可以用以下的sql语句直接使用了，就不用另外再写一次sql语句了。call 存储过程名();如：call a_trade1 ();b.有参数的存储过程：a.中的存储过程名称后是()，括号里没有参数，当括号有参数时，就是以下的语法：create procedure 存储过程名称(参数1,参数2,…) begin <sql语句>;end;例如：要在进口贸易表中查找指定商品编码的国家有哪些？如果指定商品编码为88，那么sql语句是：select 国家 from 进口贸易表 where 商品编码=88;在实际工作中，有时候并不能一次就能指定国家是哪个，有时候业务需要指定国家为中国，有时候需要指定成美国，这个时候就需要参数，来灵活应对这种情况。这时候把sql放入存储过程就是：create procedure getNum2(num varchar(100)) begin select 国家 from 进口贸易表 where 商品编码=num;end;其中getNum2是存储过程的名称，后面括号里面的num varchar(100)是参数，参数由两部分组成，参数名称是num;参数类型是varchar(100);这里表示字符串类型。存储过程里面的sql语句（where 商品编码=num）使用了这个参数num，这样在使用存储过程时，给定参数值就可以灵活地运用了。比如现在要查商品编码是89的国家名称，那么就可以在使用存储过程的参数来实现了，也就是下面括号里的89.call getNum2(89);c.默认参数的存储过程*前面的存储过程名称后是(参数1,参数2,…)，括号里面只包含了参数的类型和名称，方便调用。其实存储过程还包含了一种情况，就是存在默认参数的情况。in输入参数：参数初始值在存储过程前被指定为默认值，在存储过程中修改该参数的值不能被返回。set @num=0;-- 初始化参数 -- 初始化存储过程 create procedure in1(in num int) begin select num; set num=1; select num; end; -- in参数调用 call in1(@num); select num;out输出参数：参数初始值为空，该值可在存储过程内部被改变，并可返回。set @num=0;-- 初始化参数 -- 初始化存储过程 create procedure out1(out num int) begin select num; set num=1; select num; end; -- out参数调用 call out1(@num); select num;inout输入输出参数：参数初始值在存储过程前被指定为默认值，并且可在存储过程中被改变和在调用完毕后可被返回。set @num=0;-- 初始化参数 -- 初始化存储过程 create procedure inout1(inout num int) begin select num; set num=1; select num; end; -- inout参数调用 call inout1(@num); select num;3）注意事项a.定义存储过程语法里的sql语句代码块必须是完整的sql语句，必须用分号；结尾。create procedure 存储过程名称(参数1,参数2,…) begin <sql语句>;end;复制b.定义不同的存储过程，要用不同的存储过程名称，相同的存储过程名字会引起系统报错。原文链接：https://gitcode.csdn.net/66262c24ff62be264bf046fe.html

MySQL是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的RDBMS (Relational Database Management System，关系数据库管理系统)应用软件之一。 MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL作为网站数据库。与其他的大型数据库例如 Oracle、DB2、SQL Server等相比，MySQL [1] 自有它的不足之处，但是这丝毫也没有减少它受欢迎的程度。对于一般的个人使用者和中小型企业来说，MySQL提供的功能已经绰绰有余，而且由于 MySQL是开放源码软件，因此可以大大降低总体拥有成本。group by是分组函数，partition by是分区函数（像sum()等是聚合函数），注意区分。 1、over函数的写法： over（partition by cno order by degree ） 先对cno 中相同的进行分区，在cno 中相同的情况下对degree 进行排序 2、分区函数Partition By与rank()的用法“对比”分区函数Partition By与row_number()的用法 例：查询每名课程的第一名的成绩 （1）使用rank() SELECT    *  FROM    (select sno,cno,degree,           rank()over(partition by cno order by degree desc) mm            from score)  where mm = 1; 得到结果： （2）使用row_number() SELECT *  FROM   (select sno,cno,degree,        row_number()over(partition by cno order by degree desc) mm         from score)  where mm = 1; 得到结果： （3）rank()与row_number()的区别 由以上的例子得出，在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果。  2、分区函数Partition By与rank()的用法“对比”分区函数Partition By与dense_rank()的用法 例：查询课程号为‘3-245’的成绩与排名 （1） 使用rank() SELECT *  FROM   (select sno,cno,degree,        rank()over(partition by cno order by degree desc) mm         from score)  where cno = '3-245' 得到结果： （2） 使用dense_rank() SELECT *  FROM   (select sno,cno,degree,        dense_rank()over(partition by cno order by degree desc) mm         from score)  where cno = '3-245' 得到结果： （3）rank()与dense_rank()的区别 由以上的例子得出，rank()和dense_rank()都可以将并列第一名的都查找出来；但rank()是跳跃排序，有两个第一名时接下来是第三名；而dense_rank()是非跳跃排序，有两个第一名时接下来是第二名。 ————————————————             原文链接：https://blog.csdn.net/weixin_44547599/article/details/88764558 

简介很多时候，我们都使用group by 进行分组，count(*)进行统计，两者结合可以进行聚合统计。假设我们有这样一张煤矿数据库表table name: coalmine columns: id(煤矿ID, bigint), prod_status(生产状态,varchar), prod_capacity(产能,decimal)需求：统计各生产状态的煤矿数量学过SQL的人一眼就看出来，这是一个非常基础的问题。我们只需要按照prod_status进行分组进行聚合统计即可。大致可以写如下的sql：select prod_status as name, count(*) as num from coalmine group by name;可以得到如下的输出：name | num 停产 377 停建 360 关闭 31 准备 1 在建 89 正在复产 3 生产 463 生产/在建 15 生产/试运转 12 试运转 1非常完美，我们得到了我们想要的数据。但是现在有了新的需求：统计产能在30以下，30~90，90以上的煤矿数量有多少。现在我们遇到了难题，因为产能字段(prod_capacity)是一个数值，同时统计的依据是一个区间，我们不能单纯的将其作为group by的对象进行操作。select prod_capacity as name, count(*) as num from coalmine group by name;这样做的结果，只是按数值进行分组统计。那么，该怎么办呢？区间统计（解法一）有点基础的读者不难看出，我们可以使用mysql关键字 sum 以及 if 进行操作，大致可以写出如下的SQL。select sum(if(c.prod_capacity is null or c.prod_capacity < 30, 1, 0)) as less30, sum(if(c.prod_capacity >= 30 and c.prod_capacity <= 90, 1, 0)) as between39, sum(if(c.prod_capacity > 90, 1 , 0)) as gather90 from coalmine c inner join enterprise e on c.enterprise_id = e.id;c.prod_capacity is null 可以认为prod_capacity字段为空时，认为煤矿的产能低于30.sum 以及 if 的使用方法可参阅网上教程。执行完毕后，我们可以得到如下的结果：less30 | between39 | than90 1033 330 25看起来似乎很美好，只不过没有使用分组排序稍有欠缺，导致最终结果是以一行的方式呈现，这回导致我们在应用程序里面进行实体映射（例如mybatis）时，只能使用扁平结构进行对应（例如Map），这和统一的分组映射实体出现矛盾。当然对于解决问题的结果来说这是无伤大雅的，最终我们还会讲最完美的解法，在此之前，我们先看另一种解决方案。区间统计（解法二）mysql有众多函数可以帮助我们完成各种各样的任务，只要我们仔细研究，很多冗余的SQL可以简化的漂亮，关于区间统计，其实还有专门的处理函数，他们分别是 interval 以及 ele。我们来看看他们的用法：INTERVAL(N,N1,N2,N3,...) INTERVAL()函数进行比较列表(N1，N2，N3等等)中的N值。该函数如果N<N1返回0，如果N<N2返回1，如果N<N3返回2 等等。如果N为NULL，它将返回-1。列表值必须是N1<N2<N3的形式才能正常工作。 ELT(N,str1,str2,str3,...) 如果N= 1，返回str1，如果N= 2，返回str2，等等。如果N小于1或大于参数个数，返回NULL。ELT()是FIELD()反运算。基于此，我们可以写出更漂亮的SQLselect elt(interval(c.prod_capacity,0,30,90, 100000), 'less30', 'between39', 'than90') as name, count(*) from coalmine c group by name;但显然，查询的结果受限非常之大，interval是半区间方式，（即大于等于前者小于后者），这样会导致运用场景非常之有限。当然可以通过其他方式进行优化，但是已经如使用sum、if方式来得方便灵活。但前者也有问题，就是查询的结果并不是多条记录展示，这样在很多业务系统中，进行bean映射的时候，只能采取hashmap方式进行结果映射。显然其原理还是分组统计，我们希望结果是以多行的形式展示。那么，该如何办到呢？区间统计（解法三）可以看到，既然分组的逻辑是一种if else形式的，我们可不可以在mysql里找到这种逻辑的关键字呢？显然是有的，那便是 case语句。以下是其官方文档:Syntax: CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END 或者 CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END金风玉露一相逢，这便是我们要的东西，仔细琢磨一番，可以写出如下的SQLselect case when c.prod_capacity is null or c.prod_capacity < 30 then 'less30' when c.prod_capacity >= 30 and c.prod_capacity <=90 then 'less39' when c.prod_capacity > 90 then 'than90' end as name, count(*) as num from coalmine c group by name;返回结果如下所示：name | num less30 1033 less39 330 than90 25结语可以看到，使用case关键字不仅得到了我们想要的结果形式，同时他提供了更灵活的处理逻辑，不论是区间分组亦或是其他的非正常方式，我们都可以定义自己的处理逻辑，将业务上需要归为一组的数据输出(then)为同样的值，然后进行分组。当然，也许还有更完美的解决方案，不知君是否有所考虑呢？欢迎讨论。原文链接：https://zhuanlan.zhihu.com/p/163452689

【问题来源】深圳容大【问题简要】一通通话，经过座席的转换/咨询/会议后，几条记录可以通过callid关联吗【问题类别】CC-DIS【AICC解决方案版本】22.100[问题描述]您好，我通过座席A拨打座席B，再由座席B咨询转换给座席C，座席C接听后挂断，A座席工号:107,B座席工号:110,C座席:20213，测试时间点:拨打电话:2024-04-15 16:09:32，转接时间点:2024-04-15 16:10:58。tcurrentbilllog表共有9条数据（见附件话单原数据），好像单凭callid字段不能判断出它们之间是属于一通通话，有没有什么方式可以把这几条数据关联起来？agentgateway-rest.log日志在附件中。

 如果把查询看作是一个任务，那么它由一些列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数。通常来说，查询的生命周期大致可以按照顺序来看：从客户端到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，其中包括大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。上述操作会在网络、CPU计算、生成统计信息和执行计划、锁等待（互斥等待）等操作上花费时间，尤其是向底层存储引擎检索数据的调用操作。根据存储引擎的不同，可能还会产生大量的上下文切换以及系统调用。  一、是否请求了不需要的数据 查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效查询可以通过如下两个步骤来分析总是有效： 【1】确定应用程序是否在检索大量超过需要的数据。意味着访问了太多的行或者太多的列。 【2】确定 MySQL 服务器是否在分析大量超过需要的数据行。  有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给 MySQL 服务器带来额外的负担，并增加网络开销【应用服务器和数据库不再同一台服务器上】另外也会消耗应用服务器的 CPU和内存资源。通常企业不允许使用 SELECT * 语句进行查询。  二、是否扫描了额外的记录 在确定查询只返回需要的数据以后，接下来应该查看查询是否扫描了过多的数据。对于 MySQL，最简单的衡量查询开销的三个指标是响应时间、扫描的行数、返回的行数：这三个指标都会记录到慢日志【SHOW VARIABLES LIKE “%slow%”;】中。 【1】响应时间： 服务时间和排队时间之和，服务时间是指数据库处理这个查询真正花费的时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间（等待I/O操作或锁，等等）。遗憾的是无法将响应时间细分到上面这些部分。 【2】扫描的行数和返回的行数： 分析查询时，查看该查询扫描的行数是非常有帮助的。但并不是所有的行的访问代价都是相同的。较短的行访问速度快，内存中的行也比磁盘中的行的访问速度要快很多。理想情况下扫描的行数和返回的行数应该是相同的。但这种情况并不多。例如在做一个关联查询时，服务器必须要扫描多行才能生成结果集中的一行。扫描的行数对返回的行数的比率通常很小，以便在1:1和10:1之间，不过有时候这个值也可能非常非常大。 【3】扫描的行数和访问类型： 在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。MySQL 有好几种查询方式可以查找并返回一行结果。有些访问方式可能需要扫描多行才能返回一行结果，也有些访问方式可能无需扫描就能返回结果。在EXPLAN 语句中的 type 列反映了访问类型。从全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等。速度从慢到快，扫描的行数也是从多到少。如果查询没有办法找到合适的访问类型，那么解决的最好办法就是添加一个合适的索引。索引让 MySQL 以最高效、扫描行数最少的方式找到需要的记录。 【4】如果发现查询需要扫描大量的数据但只返回少数行： 通常可以使用如下技巧去优化它：①、使用索引覆盖扫描，把所有需要的列都放到索引中，这样存储引擎无需回表获取对应行就可以返回结果了。②、改变表结构。例如使用单独的汇总表。③、重写这个复杂的查询，让 MySQL 优化器能够以更优化的方式执行这个查询。  三、一个复杂查询 OR 多个简单查询 有时候，可以将查询转换一种写法让其返回一样的结果，但是性能更好。但也可以通过修改应用代码，用另一种方式完成查询，达到最后的目的。  设计查询的时候需要考虑一个重要问题，是否需要将一个复杂的查询分成多个简单的查询。在传统的实现中，总是强调需要数据库层完成尽可能多的工作，这样做逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。对于MySQL 并不适用，MySQL 从设计上让连接和断开连接都是轻量级， 在返回一个小的查询结果很高效。现在的网络速度比以前也快很多，无论是宽带还是延迟。即使一个通用的服务器上，也能够运行每秒超过10万的查询。  四、切分查询 有时候对于一个大查询我们需要 “分而治之” 将大查询切分成小查询，每个查询功能完全一样，只是完成一小部分，每次只返回一小部分查询结果。删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次性锁住很多数据、占满整个事务日志，耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE 切分成多个较小的查询可以尽可能小地影响 MySQL 性能，同时还可以减少 MySQL 的复制延迟。一秒删除一万行数据一般来说是一个比较高效而且对服务器影响也比较小的做法。如果每次删除数据后，都暂停一会儿再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大降低删除时锁的持有时间。  五、分解关联查询 很多高性能的应用都会对关联查询进行分解。可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。如下：  SELECT * FROM teacher t JOIN student s ON t.id = s.t_id JOIN class c ON t.id = c.t_id WHERE t.name='Li'; --拆分后 SELECT * FROM teacher t WHERE t.name='Li'; SELECT * FROM student s WHERE s.id = 12; SELECT * FROM class c WHERE c.id IN (13,45,65); 1 2 3 4 5 6 7 8 用分解关联查询的方式重构查询有如下的优势： 【1】让缓存的效果更高，许多应用程序可以方便地缓存单表查询对应的结果对象。例如，上面的 teacher 已经被缓存了，那么应用就跳过了第一个查询，再例如，应用程序中已经缓存了 ID 为 12、45 的内容，那么第三个查询的 IN() 中就可以少几个 ID。另外，对于MySQL 的查询缓存来说，如果关联中某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。 【2】将查询分解后，执行单个查询就可以减少锁的竞争。 【3】在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。 【4】查询本身效率也可能有所提升。这个例子中，使用 IN() 代替关联查询，可以让 MySQL 按照ID 顺序进行查询，这可能比随机的关联要更高效。 【5】可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。这样的重构还可能会减少网络和内存的消耗。 【6】这样做相当于在应用中实现了哈希关联，而不是使用 MySQL 的嵌套循环关联。某些场景哈希关联效率要高很多。  六、UNION 的限制 MySQL 无法将外层限制条件延续到内层，这使得原本可以返回部分结果的条件无法应用到内部查询的优化上。如果希望 UNION 的各个子句根据 LIMIT 只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在 UNION 的各个子句中分别使用这些子句。例如，想将两个子查询结果联合起来，然后再取前20条记录，那么MySQL 会将两个表都存放到同一个临时表中，然后再取出前20行记录：  --UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL (SELECT first_name,last_name FROM people_A ORDER BY last_name) UNION ALL (SELECT first_name,last_name FROM people_B ORDER BY last_name) LIMIT 20; 1 2 3 4 5 这条查询将会把 people_A 中的所有记录和 people_B 的所有记录放在一个临时表中，然后再从临时表中取出前20条。可以通过在 UNION 的两个子查询中分别加上一个 LIMIT 20来减少临时表中的数据：  (SELECT first_name,last_name FROM people_A ORDER BY last_name LIMIT 20) UNION ALL (SELECT first_name,last_name FROM people_B ORDER BY last_name LIMIT 20) LIMIT 20; 1 2 3 4 现在中间的临时表只会包含40条记录，除了性能考虑之外，在这里还需要注意一点，从临时表中取出数据的顺序并不是一定的，所以如果想获得正确的顺序，还需要加上一个全局的 ORDER BY 和 LIMIT 操作。  MySQL 总是通过创建并填充临时表的方式来执行 UNION 查询。除非确定需要服务器消除重复的行，否则就一定要使用 UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，这会导致给整个临时表做唯一性检查。代价非常高。就是有 ALL 关键字，MySQL 仍然会使用临时表存储结果。事实上，MySQL 总是把结果放入临时表，然后再读出来，再返回给客户端。  七、优化 COUNT() 查询 COUNT() 可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL）。如果在COUNT() 的括号中制定了列或者表达式，则统计的就是这个表达式有值的结果数。COUNT()的另一个作用是统计行数，当MySQL确认括号内的表达式值不可能为空的时候，实际上就是在统计行数。最简单的就是当我们使用 COUNT(*) 的时候，这种情况它会忽略所有的列直接统计所有的行数。  MyISAM 的 COUNT() 函数总是非常快，前提是没有任何 WHERE 条件。因为无需实际计算表的行数。MySQL 可以利用存储引擎的特性直接获取这个值。如果 MySQL 知道某个列 col 不可能为 NULL 值，那么内部会将 COUNT(col) 转换成COUNT(*)。  【简单优化】 有时候可以使用 MyISAM 在 COUNT(*) 全表非常快的这个特性，来加速一些特定条件的 COUNT() 查询。比如：  SELECT COUNT(*) FROM  city WHERE ID>5; 1 通过 SHOW STATUS 的结果可以看到该查询需要扫描 5000行数据。如果将条件反转，先查找ID小于等于5的城市，然后用总城市减就能获得同样的结果，却可以将扫描数减少到5行以内。  --ID 是索引，所以会去前5行数据 SELECT (SELECT COUNT(*) FROM city)-COUNT(*) FROM  city WHERE ID<=5; 1 2 通常来说，COUNT() 都需要扫描大量的行才能获得精准的结果，因为是很难优化的。在MySQL 层面还能做的就只有索引覆盖扫描了。如果还不够，就需要考虑修改应用的架构，可以增加汇总表，或者增加类似 memcached 缓存系统。  八、优化 LIMIT 分页 在进行分页操作的时候，通常会使用 LIMIT 加上偏移量的办法实现，同时加上合适的 ORDER BY 子句。如果有对应的索引效率会不错，否则，MySQL 要做大量的文件排序操作。有一个问题，当偏移量非常大的时候，例如 LIMIT 10 000,20 这样的查询，这是需要查询10 020条记录然后只返回 20条，前面的10 000条记录都将被抛弃，这样代价太高。优化此类分页查询的最简单办法就是尽可能地使用覆盖索引扫描，而不是查询所有列。对于偏移量大的时候，这样做的效率会提升非常大。例如：  SELECT id,description FROM tab ORDER BY title LIMIT 10000,20; --使用覆盖索引优化后的语句如下： SELECT f.id,f.description FROM tab f  INNER JOIN (SELECT id FROM tab ORDER BY title LIMIT 10000,20) t USING(id); 1 2 3 4 5 这里的 “延迟关联” 将大大提升查询效率，它让 MySQL 扫描尽量少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的 LIMIT 子句。  九、排序优化 排序是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。如果数据量小于 “排序缓冲区” 则在内存中排序，如果数据量大于 “排序缓冲区” 则使用磁盘进行排序 。MySQL 将这一过程统称为 “文件排序：filesort”（前提没有使用索引）。 MySQL 使用内存进行 “快速排序” 操作。如果内存不够排序，那么 MySQL 会先将数据分块，对每个队列的块使用 “快速排序” 进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并（merger），最后返回排序结果。  【1】两次传输排序（旧版本使用）： 读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。需要进行两次传输，既需要从数据表中读取两次数据。第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这会产生大量的随机 I/O，所以两次数据传输的成本非常高。 【2】单次传输排序（新版本使用）： 先读取排序所需要的列，然后再根据给定的列进行排序，最后直接返回排序结果。因为不需要从数据表中读取两次数据，对于I/O 密集型的应用，这样做的效率高了很多。另外，相比两次传输排序，这个算法只需要一次顺序 I/O 读取所有的数据，而无需任何随机 I/O。缺点是，如果需要返回的数据非常多，非常大，会额外占用大量空间，而这些列对排序本身并没有任何作用。很难说那个算法效率高，当查询需要所有列的总长度不操作参数 max_length_for_sort_data 时，MySQL 使用单次传输排序，可以通过调整该参数来影响 MySQL 排序算法的选择。  MySQL 在进行文件排序的时候需要使用的临时存储空间可能会比想象的要大得多。在关联查询需要排序时，会分为两种情况来处理这样的文件排序。如果 ORDER BY 子句中的所有列都来自关联的一个表，那么 MySQL 在关联处理第一个表的时候就进行了文件排序。使用 EXPLAN 查看时，看到 Extra 字段会有 “Using filesort” 。另一种情况是 MySQL 都会先将结果存放在一张临时表中，然后在所有关联都结束后，再进行文件排序。EXPLAN 结果是 “Using temporary；Using filesort”，如果包含 LIMIT 的话，LIMIT 也会在排序之后应用。在 MySQL5.6 之后。当使用 LIMIT 子句时，MySQL 不会对所有结果进行排序，而是根据实际情况，选择抛弃不满足条件的结果，然后进行排序。  十、查询状态 在分析查询性能的时候，对于一个 MySQL 连接来说，可以通过查看它的状态来观察它正在做什么。最简单的方式是 SHOW FULL PROCESSLIST 命令，该命令返回结果中的 Command 列表示当前的状态。在一个查询的生命周期中，状态会变化多次。MySQL 官方手册中对这些状态值的含义有最权威的解释，如下： 【1】Sleep： 线程正在等待客户端发送新的请求； 【2】Query： 线程正在执行查询或将结果发送给客户端； 【3】Locked： 在 MySQL 服务器层，该线程正在等待表锁。InnoDB的行锁并不会体现在线程状态中； 【4】Analyzing and statistics： 线程正在收集存储引擎的统计信息，并生成查询的执行计划。 【5】Copying to tmp table [on disk]： 线程正在执行查询，将结果都复制到一个临时表，这种状态一般要么再过 GROUP BY，要么是文件排序操作，或者是 UNION 操作。“on disk” 标记，表示 MySQL 正在讲一个内存临时表放到磁盘上。 【6】Sorting result： 线程正在对结果进行排序； 【7】Sending data： 表示多种情况，线程可能在多种状态之间传送数据。 ————————————————                              版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。                          原文链接：https://blog.csdn.net/zhengzhaoyang122/article/details/136972698 

 大家好，三月的合集又来了，本次涵盖了java，mysql，spirngboot，oracle，nginx，webpack，css,python,mongoDB,devops,golang诸多内容供大家学习。 1.Python中数据解压缩的技巧分享【转】 https://bbs.huaweicloud.com/forum/thread-0274147063634001023-1-1.html  2.CSS如何设置背景模糊周边有白色光晕(解决方案)【转】 https://bbs.huaweicloud.com/forum/thread-02121147063476589021-1-1.html  3.CSS实现渐变式圆点加载动画【转】 https://bbs.huaweicloud.com/forum/thread-0274147063231589021-1-1.html  4. Nginx access.log日志详解及统计分析小结【转】 https://bbs.huaweicloud.com/forum/thread-0274147062581229019-1-1.html  5.Webpack部署本地服务器的方法【转】 https://bbs.huaweicloud.com/forum/thread-02110147062400691022-1-1.html  6.Nginx漏洞整改实现限制IP访问&隐藏nginx版本信息【转】 https://bbs.huaweicloud.com/forum/thread-0273147062364276013-1-1.html  7.Nginx加固的几种方式(控制超时时间&限制客户端下载速度&并发连接数)【转】 https://bbs.huaweicloud.com/forum/thread-02109147062305049017-1-1.html  8.Nginx配置http和https的实现步骤【转】 https://bbs.huaweicloud.com/forum/thread-0274147061484998017-1-1.html  9.MongoDB内存过高问题分析及解决【转】 https://bbs.huaweicloud.com/forum/thread-0276147061392824019-1-1.html  10.Oracle数据库中字符串截取最全方法总结【转】 https://bbs.huaweicloud.com/forum/thread-0240147061327119025-1-1.html  11.MySQL数据库如何克隆(带脚本)【转】 https://bbs.huaweicloud.com/forum/thread-0294147061178556014-1-1.html  12.mysql5.6建立索引报错1709问题及解决【转】 https://bbs.huaweicloud.com/forum/thread-0240147061135949024-1-1.html  13.修改Mysql索引长度限制解决767 byte限制问题【转】 https://bbs.huaweicloud.com/forum/thread-0240147060706517023-1-1.html  14.SQL实现模糊查询的四种方法小结【转】 https://bbs.huaweicloud.com/forum/thread-02121147060465820020-1-1.html  15.MySql查询中按多个字段排序的方法【转】 https://bbs.huaweicloud.com/forum/thread-0240147060411844022-1-1.html  16. Devops-01-devops 是什么？【转】 https://bbs.huaweicloud.com/forum/thread-02127146828359305034-1-1.html  17.使用 Java 在Excel中创建下拉列表【转】 https://bbs.huaweicloud.com/forum/thread-0297146827870477028-1-1.html  18.管理与控制平面设计 https://bbs.huaweicloud.com/forum/thread-0239146030318575010-1-1.html  19.cisco https://bbs.huaweicloud.com/forum/thread-0292146030276181005-1-1.html  20. NSX-V整体架构 https://bbs.huaweicloud.com/forum/thread-02127146028999196007-1-1.html  21.从NVP到NSX https://bbs.huaweicloud.com/forum/thread-0279146028887825006-1-1.html  22.【监控】spring actuator源码速读-转载 https://bbs.huaweicloud.com/forum/thread-0239145954118788007-1-1.html  23.SpringCloud-RabbitMQ消息模型-转载 https://bbs.huaweicloud.com/forum/thread-0282145954036229004-1-1.html  24.【Golang入门教程】Go语言变量的声明-转载 https://bbs.huaweicloud.com/forum/thread-0279145953985911003-1-1.html  25. Spring Boot 3核心技术与最佳实践-转载 https://bbs.huaweicloud.com/forum/thread-0239145953918959006-1-1.html