一、概述
最近几天公司项目开发上线完成，做个收获总结吧~ 今天先记录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优化器的其他优化策略，比如优化器认为在某些情况下，全表扫描比走索引快，则它就会放弃索引。
六、主从复制
6.1 主从复制优点
（1）高可用性和高容错能力
（2）提高整体性能和吞吐量
（3）数据备份和恢复
6.2 实现主从复制
① 配置并重启主服务，在主服务器的配置文件（my.cnf）中添加以下参数：
[mysqld] server-id = 1 # 设置服务器 ID，每个主服务器和从服务器都必须有唯一的 ID
log_bin = /var/log/mysql/mysql-bin.log # 开启二进制日志，记录数据修改操作
配置完成后，需要重启 MySQL 服务使配置生效。
② 创建用于主从复制的用户
登录到主服务器上，执行以下命令：
CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; -- 替换为实际的用户名和密码
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
③ 查看主服务器状态
在 MySQL 主服务器中，执行以下命令，记录下 File 和 Position 的值，后续用于配置从服务器：
SHOW MASTER STATUS;
④ 配置并重启从服务器
在从服务器的配置文件（my.cnf）中添加以下参数：
[mysqld] server-id = 2 # 设置服务器 ID，每个主服务器和从服务器都必须有唯一的 ID
重启从服务器，让配置生效。
⑤ 在从服务器上设置主服务器信息
登录到从服务器的 MySQL 中，执行以下命令（将 MASTER_HOST、MASTER_USER、MASTER_PASSWORD、MASTER_LOG_FILE 和 MASTER_LOG_POS 替换为对应的值）：
CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl',
MASTER_PASSWORD='password', MASTER_LOG_FILE='binlog_file',
MASTER_LOG_POS=log_file_position;
⑥ 启动从服务器的复制进程
执行以下命令启动从服务器的复制进程：
START SLAVE;
⑦ 检查从服务器的复制状态
执行以下命令，确保 Slave_IO_Running 和 Slave_SQL_Running 的值都为 “YES”：
SHOW SLAVE STATUS \G;
6.3 主从复制原理
MySQL 数据库的主从复制主要是基于 Binary Log（二进制文件，简称 bin log）实现的，它的实现流程如下：
执行流程：
（1）主数据库接收到一个写操作（如 INSERT、UPDATE、DELETE）时，会将这个操作记录到二进制日志（Binary Log）中，将数据修改的操作按顺序记录下来。
（2）从数据库 IO 线程会自动连接主服务，从二进制中读取同步数据，记录到中继日志（Relay Log）中。
（3）从数据库的 SQL 线程会定期从中继日志中获取同步数据，写入到从数据库中。
6.4 Bin Log 日志格式
Binary Log 二级制日志，它总共有以下三种格式（不同的日志格式决定了不同的主从同步效果）：
（1）STATEMENT 格式：
binlog 记录的是执行的 SQL 语句的文本。日志文件通常较小，复制效率较高。但由于数据库环境的差异（如表结构、字符集等），可能会导致数据不一致。
（2）ROW 格式：
binlog 记录的是每一行数据更改的具体内容。能够精确地记录数据的变化，避免 STATEMENT 格式中的环境依赖问题，一致性保证增强。但日志文件大，进行大量数据更新时会导致更高 I/O 开销。
（3）MIXED 格式（混合模式）：
binlog 可以根据具体的 SQL 语句和操作自动选择使用 STATEMENT 或 ROW 格式。结合 STATEMENT 和 ROW 格式的优点，能够在保证一致性的同时尽可能地优化日志大小和复制性能。但需要更复杂的管理和监控。特定情况可能无法达到最优的性能或一致性。
6.5 主从复制模式
MySQL 主要有两种主从复制的模式，分别是异步复制和半同步复制。
（1）异步复制：对延迟和主服务器性能要求较高
MySQL 主从复制中最常见和默认的模式。在异步复制模式中，主服务器将数据修改操作记录到二进制日志（Binary Log）中，并将日志传输给从服务器。从服务器接收到二进制日志后，会异步地应用这些日志进行数据复制。
a.优点： 及时响应给使用者，主服务器不会受到从服务器的影响而等待确认，可以提高主服务器的性能。
b.缺点： 异步复制可能存在数据传输的延迟，且从服务器上的复制过程是不可靠的。如果主服务器故障，尚未应用到从服务器的数据可能会丢失。
（2）半同步复制：对数据一致性和可靠性要求较高
是 MySQL 主从复制中的一种增强模式。在半同步复制模式中，主服务器将数据修改操作记录到二进制日志，并等待至少一个从服务器确认已接收到并应用这些日志后才继续执行后续操作。
a.优点： 提供更高的数据一致性和可靠性，确保至少一个从服务器与主服务器保持同步。如果主服务器故障，已经确认接收并应用到从服务器的数据不会丢失。
b.缺点： 需要等待服务器确认，会增加一定的延迟，可能会影响主服务器的性能。
