项目收获总结--MySQL的知识收获

一、概述

最近几天公司项目开发上线完成，做个收获总结吧~ 今天先记录MySQL的收获和提升。

二、MySQL表分区

项目中遇到数据量过大导致在查询过程中会出现各种超时的情况，当然是可以使用各种中间件比如MyCat，ShardingJDBC 等分库工具来进行分库分表，但在第一阶段做数据库设计时，可以使用MySQL自己的数据分区，暂不做分库操作。

2.1 MySQL数据分区

MySQL的数据分区（Partitioning）是数据库功能，允许将一个表物理地分成多个独立的、更小的、更易于管理的片段，这些片段被称为分区。每个分区在逻辑上都是表的一部分，但在物理存储上，每个分区可以有自己的存储引擎、文件、索引等。
使用分区的有点在于：

性能方面：对于某些查询，尤其是范围查询，分区可以显著提高性能，因为MySQL可以仅扫描需要的数据分区，而不是整个表。
管理方面：可以对单独的分区进行备份、删除或检查，这使得管理大型表变得更加容易。
归档方面：可以将旧数据移动到单独的分区，并轻松地从主表中删除这些分区，从而实现数据的归档。
2.2 MySQL分区方法

MySQL 支持多种分区方法，包括：

RANGE 分区：基于列值的范围进行分区。
LIST 分区：基于列值的列表进行分区。
HASH 分区：基于用户定义的表达式的返回值的哈希值进行分区。
KEY 分区：类似于 HASH 分区，但 MySQL 服务器提供哈希函数。
COLUMNS 分区：是 RANGE 和 LIST 分区的扩展，允许基于多个列的值进行分区。
2.2.1 RANGE分区
定义：基于属于一个给定连续区间的列值，把多行分配给分区。
用途：非常适合于基于时间范围的数据，如日志、交易记录等。
特点：分区键必须是整数、日期或日期时间类型。
     分区表必须至少包含一个RANGE分区。
     每个RANGE分区都定义了一个值的范围，如 PARTITION p0 VALUES LESS THAN (100)。
限制：不支持外键和全文索引。
2.2.2 LIST分区
定义：类似于RANGE分区，但它是基于列值匹配一个离散值集合中的某个值来进行选择。
用途：当数据可以按照某个离散值列表进行分组时，如地域、类别等。
特点：分区键可以是整数或枚举类型。定义时指定一个值列表，如 PARTITION p1 VALUES IN (1, 3, 5)。
限制：与RANGE分区类似，不支持外键和全文索引。
2.2.3 HASH分区
定义：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。
用途：当数据分布需要均匀或随机时，HASH分区可以确保数据在预先确定数目的分区中平均分布。
特点：分区键可以是任何MySQL中的有效表达式，只要它返回非负整数值。
     可通过指定分区数量来控制数据的分布。
限制：不支持外键和全文索引。
2.2.4 KEY分区
定义：类似于HASH分区，但KEY分区的哈希函数是由MySQL服务器提供。
用途：与HASH分区类似，但使用MySQL内部的哈希函数。
特点：分区键可以是一列或多列，但所有列都必须是整数类型。
     MySQL服务器会处理列的哈希值，并将数据分配到不同的分区。
限制：与HASH分区相同，不支持外键和全文索引。
2.2.5 COLUMNS分区
定义：MySQL 5.5及以上版本支持基于多个列的分区，这被称为COLUMNS分区。
用途：允许根据多列的值进行分区，提供了更大的灵活性。
特点：可以使用多个列作为分区键。
     支持RANGE和LIST分区。
限制：与上述分区类型类似的限制。
2.3 MySQL分区示例

假设有一个名为 sales 的表，用于记录销售数据，并且想基于 sale_date 列进行RANGE 分区并且按照每月交易数据分区，当然由于数据增加，是需要动态分区的。

CREATE TABLE sales (  
    sale_id  VARCHAR(100) NOT NULL, 
    sale_name VARCHAR(100) NOT NULL, 
    amount DECIMAL(10, 2) NOT NULL,  
    dsYear VARCHAR(20) NOT NULL
)  
PARTITION BY RANGE COLUMNS(dsYear) (  
    PARTITION p0 VALUES LESS THAN ('202404'),  
    PARTITION p1 VALUES LESS THAN ('202405'),  
    PARTITION p2 VALUES LESS THAN ('202406'),  
    PARTITION p3 VALUES LESS THAN ('202407')  
);

查看分区信息：在这里插入图片描述
动态新增分区需要定时任务在月末最后一天，执行SQL：

ALTER TABLE sales ADD PARTITION (partition p4 VALUES LESS THAN ('202408'))

在这里插入图片描述
要对分区表做查询需要增加分区查询条件，和分库是一样的，查询的时候需要命中分库规则，就不会进行全表的扫描。
先使用Navicat16自带的造数据的功能 或者 用存储过程来造数据：

CREATE DEFINER=`root`@`%` PROCEDURE `CreateTestData`()
BEGIN
    DECLARE v_counter INT DEFAULT 1;
    WHILE v_counter <= 1000000 DO
        INSERT INTO sales(sale_id, sale_name, amount,dsYear)
        VALUES(UUID_SHORT(), CONCAT('Data', v_counter), v_counter,'202407');
        SET v_counter = v_counter + 1;
    END WHILE;
END

通过普通查询来看一下查询速度：

SELECT * FROM sales WHERE sale_name = Data1';

在这里插入图片描述

再看下分区查询：

SELECT * FROM sales PARTITION (p3) WHERE sale_name = 'Data1'

在这里插入图片描述
共200w的数据查询速度的差距还是蛮明显。

2.4 注意事项
（1）不是所有的存储引擎都支持分区。MyISAM 和 InnoDB 支持分区，但MEMORY存储引擎不支持。
（2）分区键必须是表的一个列或表达式的组合，且必须是整数类型、返回整数值的表达式或 DATE/DATETIME 列。
（3）分区表可能有一些限制和注意事项，例如，某些类型的索引可能不支持，或者某些查询优化可能不适用于分区表。因此，在决定使用分区之前，要先详细了解这些限制和注意事项。
三、MySQL回表

这个是在项目SQL优化中需要考虑的知识点，其他优化方式就不说啦，从索引覆盖聊起吧。

3.1 索引覆盖(覆盖索引)

数据库中的一种优化手段，当执行一个SQL查询时，如果只需要查询某几个字段的值，并且这几个字段的数据都已经被包含在某一个索引中（不是全表扫描），那么数据库引擎就会直接通过这个索引来取得数据，而无需再回表查询，从而极大减少I/O操作，提高查询效率。
索引覆盖的优点体现出：

（1）减少I/O次数：因为通过覆盖索引可以直接获取数据，所以不需要再回表查询，从而减少I/O次数。
（2）提高查询速度：由于减少I/O操作，查询速度自然也得到提高。
（3）索引的选择性：选择性是指不重复的索引值与数据表的总记录数的比值。选择性越高，通过索引筛选出的数据就越少，从而提高查询效率。

这里就涉及到回表查询或者回表操作。

3.2 SQL回表

SQL回表是指在使用非聚簇索引（也称为辅助索引或二级索引）进行查询时，由于非聚簇索引中只存储索引字段的值和对应的主键（聚簇索引）键值，因此，如果需要获取非索引列的数据，则需要根据主键（聚簇索引）中的键值去查找实际的数据行。这个操作过程被称为回表。
其原理表现在：

非聚簇索引结构：非聚簇索引的叶子节点存储的是（索引列的值，主键的值）。

查询过程：当使用非聚簇索引进行查询时，首先通过非聚簇索引找到满足条件的主键键值。然后，根据这些主键键值，再回到聚簇索引（主键索引）中查找完整的数据行。

例如：
有一个用户表users，包含id（主键）、name和age三个字段，其中在name字段上建立非聚簇索引。
执行查询：

SELECT * FROM users WHERE name='Tom'

会发生回表。因为首先会通过name上的非聚簇索引找到满足条件的id，然后再根据这些id回到聚簇索引中查找完整的用户数据。
而查询：

SELECT id, name FROM users WHERE name='Tom'

不会回表，因为所需的数据都在非聚簇索引中可以找到。
而回表操作会增加I/O次数，从而可能影响查询性能。特别是在大表和复杂查询场景下，回表操作可能成为性能瓶颈，这也是在SQL中减少SELECT *的一个原因。
为减少回表操作，可以考虑将需要查询的字段加入到索引中，形成复合索引（也称为联合索引或覆盖索引）。这样，查询时就可以直接从索引中获取到需要的数据，而无需回表。所以，建立索引的时候，要非常注意，并不是说索引不好，而是说要会加索引。

3.3 索引最左匹配原则

由于建立索引大部分都不会只是单独的一个字段，所以就有复合索引。
最左匹配原则（Leftmost Prefix Rule） 主要是在使用复合索引（多列索引或多字段索引）时的一个关键理念。这个原则指出，当使用复合索引进行查询时，查询条件应该尽可能地从索引的最左边开始匹配，这样索引才能被有效地使用。

例如：有一个基于(last_name, first_name)的复合索引进行查询时，查询条件必须包含索引的最左边的一列或多列：

last_name：SELECT * FROM employees WHERE last_name = 'Smith';
last_name和first_name：SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

这两个查询可以有效地使用这个索引，因为符合按照复合索引的从左到右顺序写where条件。而对于查询：

first_name：SELECT * FROM employees WHERE first_name = 'John';

则不能有效地使用这个索引，因为它没有包含索引的最左边的列last_name。
创建复合索引时，列的顺序很重要。应该将最常用于查询条件的列放在索引的最左边。
例如：
如果经常基于last_name进行查询，但很少基于first_name进行查询，那么应该创建一个基于(last_name, first_name)的索引，而不是基于(first_name, last_name)的索引。

当然，并不必须始终遵循这个原则。在实际应用中，需要根据查询的需求和数据的分布来决定是否使用复合索引以及索引的列顺序。

四、前缀索引

使用前缀索引查询名称可以实现极佳的SQL优化，具体参考我另一篇帖子：
项目实战-MySQL极佳优化方案—前缀索引

五、MySQL索引失效场景

以下是我在项目中遇到的失效场景

5.1 联合索引不满足最左匹配原则

索引组成：

KEY `union_idx` (`id_no`,`username`,`age`)

SQL：

explain select * from t_user where id_no = '1002';
explain select * from t_user where id_no = '1002' and username = 'Tom2';

explain结果：
在这里插入图片描述
union_idx索引，但只用到了id_no列。而完全失效：

explain select * from t_user where username = 'Tom2' and age = 12;
explain select * from t_user where age = 12;
explain select * from t_user where username = 'Tom2';

在这里插入图片描述

5.2 使用select * 语句
5.3 索引列参与运算
explain select * from t_user where id + 1 = 2 ;

explain结果：
在这里插入图片描述
优化一下：

-- 内存计算，得知要查询的id为1
explain select * from t_user where id = 1 ;
-- 参数侧计算
explain select * from t_user where id = 2 - 1 ;
5.4 索引列参使用函数
explain select * from t_user where SUBSTR(id_no,1,3) = '100';

explain结果：
在这里插入图片描述

5.5 错误的Like使用
explain select * from t_user where id_no like '%00%';

explain结果：
在这里插入图片描述
模糊查询时（like语句），模糊匹配的占位符位于条件的首部索引失效，like 'abc%'就不会。

5.6 类型隐式转换
explain select * from t_user where id_no = 1002;
5.7 使用OR操作
explain select * from t_user where id = 2 or username = 'Tom2';
5.8 两列做比较
explain select * from t_user where id > age;
5.9 不等于比较
explain select * from t_user where id_no <> '1002';
5.10 is not null
explain select * from t_user where id_no is not null;
5.11 not in和not exists
explain select * from t_user where id in (2,3);
explain select * from t_user where id_no in ('1001','1002');
explain select * from t_user u1 where exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);
explain select * from t_user where id_no between '1002' and '1003';
5.12 order by导致索引失效（MySQL8.0.18版本除外）
explain select * from t_user order by id_no ;
5.13 参数不同导致索引失效
explain select * from t_user where create_time > '2024-07-05 09:04:23';

索引失效的特殊情况：MySQL优化器的其他优化策略，比如优化器认为在某些情况下，全表扫描比走索引快，则它就会放弃索引。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/778101.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

项目实战--Spring Boot + Minio文件切片上传下载

项目实战--Spring Boot + Minio文件切片上传下载

1.搭建环境 引入项目依赖 <!-- 操作minio的java客户端--> <dependency><groupId>io.minio</groupId><artifactId>minio</artifactId><version>8.5.2</version> </dependency> <!-- jwt鉴权相应依赖--> &…
阅读更多...
Redis三种模式——主从复制、哨兵模式、集群

Redis三种模式——主从复制、哨兵模式、集群

一、Redis模式 Redis有三种模式&#xff1a;分别是主从同步/复制、哨兵模式、Cluster 主从复制&#xff1a;主从复制是高可用Redis的基础&#xff0c;哨兵和群集都是在主从复制基础上实现高可用的。主从复制主要实现了数据的多机备份&#xff0c;以及对于读操作的负载均衡和简…
阅读更多...
Linux安装达梦

Linux安装达梦

文章目录 前言一、docker安装1.下载镜像2.导入镜像3.生成容器 二、ios安装1.环境准备2.iso安装3.配置实例4.注册服务5.启停服务 总结 前言 公司要求我将数据从oracle迁移到达梦数据库&#xff0c;这个国产数据库以前没用过&#xff0c;所以记录一下这次的安装过程。 一、docke…
阅读更多...
Bahdanau 注意力中上下文变量 ′的公式解释

Bahdanau 注意力中上下文变量 ′的公式解释

公式 (10.4.1) 是 Bahdanau 注意力模型中的一个关键公式&#xff0c;用于计算在解码时间步 ( t’ ) 的上下文变量 (\mathbf{c}_{t’})&#xff1a; [ \mathbf{c}{t’} \sum{t1}^T \alpha(\mathbf{s}_{t’ - 1}, \mathbf{h}_t) \mathbf{h}_t ] 下面对公式进行详细解释&#x…
阅读更多...
7月6日 VueConf 技术大会即将在深圳举办

7月6日 VueConf 技术大会即将在深圳举办

7月6日&#xff0c;VueConf 2024 即将在深圳召开&#xff0c;本次大会正值 Vue.js 十周年&#xff0c;旨在聚焦 Vue.js 社区的成员&#xff0c;分享最新的技术动态、经验以及创新实践。 本次参与 VueConf 大会的是来自全球 Vue.js 核心团队成员、行业专家及前端开发者。其中&a…
阅读更多...
排序——数据结构与算法 总结8

排序——数据结构与算法 总结8

目录 8.1 排序相关概念 8.2 插入排序 8.2.1 直接插入排序&#xff1a; 8.2.2 折半插入排序&#xff1a; 8.2.3 希尔排序&#xff1a; 8.3 交换排序 8.3.1 冒泡排序&#xff1a; 8.3.2 快速排序&#xff1a; 8.4 选择排序 8.4.1 简单选择排序 8.4.2 堆排序 8.5 归并…
阅读更多...
基于LabVIEW的设备安装螺栓连接设计

基于LabVIEW的设备安装螺栓连接设计

介绍了一种基于LabVIEW的辅助设备安装螺栓连接设计案例。通过LabVIEW软件&#xff0c;实现了从螺栓规格预估、强度校核到物料选用的整个流程的软件化&#xff0c;提高了设计效率和安装可靠性。 项目背景 在轨道车辆设备安装中&#xff0c;螺栓连接作为一种常见的紧固方式&…
阅读更多...
SpringBoot 中的参数校验：构建健壮应用的基石

SpringBoot 中的参数校验：构建健壮应用的基石

前言 在开发Web应用时&#xff0c;处理用户输入是不可避免的一环。然而&#xff0c;用户输入往往充满不确定性&#xff0c;可能是格式不正确、类型不匹配&#xff0c;甚至包含恶意内容。为了确保应用的稳定性和安全性&#xff0c;对输入参数进行有效校验显得尤为重要。Spring …
阅读更多...
Python中解决os.listdir命令读取文件乱序问题方法

Python中解决os.listdir命令读取文件乱序问题方法

Python中使用对话框批量打开文件时出现乱序问题的解决方法 一、问题描述二、os.listdir读取文件乱序问题解决方法 欢迎学习交流&#xff01; 邮箱&#xff1a; z…1…6.com 网站&#xff1a; https://zephyrhours.github.io/ 一、问题描述 有时候为了方便&#xff0c;我们在进…
阅读更多...
MySQL之备份与恢复(五)

MySQL之备份与恢复(五)

备份与恢复 备份数据 符号分隔文件备份 可以使用SQL命令SELECT INTO OUTFILE以符号分隔文件格式创建数据的逻辑备份。(可以用mysqldump的 --tab选项导出到符号分隔文件中)。符号分隔文件包含以ASCII展示的原始数据&#xff0c;没有SQL、注释和列名。下面是一个导出为逗号分隔…
阅读更多...
vb.netcad二开自学笔记3：启动与销毁

vb.netcad二开自学笔记3：启动与销毁

Imports Autodesk.AutoCAD.ApplicationServicesImports Autodesk.AutoCAD.EditorInputImports Autodesk.AutoCAD.RuntimePublic Class WellcomCADImplements IExtensionApplicationPublic Sub Initialize() Implements IExtensionApplication.InitializeMsgBox("net程序已…
阅读更多...
ePTFE膜（膨体聚四氟乙烯膜）应用前景广阔 本土企业技术水平不断提升

ePTFE膜（膨体聚四氟乙烯膜）应用前景广阔 本土企业技术水平不断提升

ePTFE膜&#xff08;膨体聚四氟乙烯膜&#xff09;应用前景广阔 本土企业技术水平不断提升 ePTFE膜全称为膨体聚四氟乙烯膜&#xff0c;指以膨体聚四氟乙烯&#xff08;ePTFE&#xff09;为原材料制成的薄膜。ePTFE膜具有耐化学腐蚀、防水透气性好、耐候性佳、耐磨、抗撕裂等优…
阅读更多...
【深度学习】-WASB-调试说明

【深度学习】-WASB-调试说明

要改这么几个地方&#xff1a; 代码仓库&#xff1a;/Desktop/code/python_project/WASB-SBDT-main/ 篮球数据集xx_xx_11.xml只保留最后一个11.xml 并把11下直接放置11 video&#xff1a; 这里的东西被我改了&#xff0c;要以仓库为准
阅读更多...
git pull拉取显示Already up-to-date，但文件并没有更新

git pull拉取显示Already up-to-date，但文件并没有更新

1、问题&#xff1a; 使用git pull拉取远程仓库代码&#xff0c;显示更新成功&#xff08;Already up-to-date&#xff09;&#xff0c;但是本地代码没有更新 这是因为本地有尚未提交的更改&#xff0c;和远程代码有冲突导致无法更新 2、解决方法&#xff1a; 可以使用git s…
阅读更多...
Fastjson首字母大小写问题

Fastjson首字母大小写问题

1、问题 使用Fastjson转json之后发现首字母小写。实体类如下&#xff1a; Data public class DataIdentity {private String BYDBSM;private String SNWRSSJSJ;private Integer CJFS 20; } 测试代码如下&#xff1a; public static void main(String[] args) {DataIdentit…
阅读更多...
多个tomcat同时使用 不设置CATALINA_HOME环境变量

多个tomcat同时使用 不设置CATALINA_HOME环境变量

通常一台服务器只使用一个tomcat&#xff0c;设置一个CATALINA_HOME的环境变量。但有些时候需要一台服务器启动多个tomcat&#xff0c;那就不能设置CATALINA_HOME了&#xff01;因为会串~ 我们可以在对应tomcat的startup.bat启动脚本中&#xff0c;加入对应的CATALINA_HOME。 …
阅读更多...
Raylib 坐标系

Raylib 坐标系

draftx 符号调整为正数 发现采样坐标系原点0&#xff0c;0 在左上角&#xff0c;正方向 右&#xff0c;下 绘制坐标系 原点0&#xff0c;0 在左下角&#xff0c;正方向 右&#xff0c;上 拖拽可得 #include <raylib.h> // 重整原因&#xff1a;解决新函数放大缩小之下…
阅读更多...
Appium+python自动化（四十一）-Appium自动化测试框架综合实践 - 即将落下帷幕（超详解）

Appium+python自动化（四十一）-Appium自动化测试框架综合实践 - 即将落下帷幕（超详解）

1.简介 今天我们紧接着上一篇继续分享Appium自动化测试框架综合实践 - 代码实现。到今天为止&#xff0c;大功即将告成&#xff1b;框架所需要的代码实现都基本完成。 2.data数据封装 2.1使用背景 在实际项目过程中&#xff0c;我们的数据可能是存储在一个数据文件中&#x…
阅读更多...
智慧交通运行监测与应急指挥中心方案

智慧交通运行监测与应急指挥中心方案

建设目标 建立感知层数据的实时采集以及数据处理&#xff0c;实现监测预警自动化和智能化&#xff1b;推动交通运输数据资源开放共享&#xff0c;打破数据资源壁垒&#xff0c;与城市各部门数据建立共享交换机制&#xff0c;实现应急指挥的协同化&#xff1b;充分运用大数据、互…
阅读更多...
新产品或敏捷项目过程 SOP，附带流程图及流程规范

新产品或敏捷项目过程 SOP，附带流程图及流程规范

一、项目启动 项目背景和目标明确 市场调研结果分析&#xff0c;确定新产品的需求和市场机会。制定明确的项目目标&#xff0c;包括产品特性、上市时间、预期收益等。 组建项目团队 确定项目经理、产品经理、开发人员、测试人员、市场人员等角色。明确各成员的职责和权限。 项目…
阅读更多...
最新文章

Copyright @ 2022~2023