八七例 Mysql 事务和索引等 · GO100例

[TOC] > # 事务特征和隔离级别 - 数据库事务的四个特征 ACID：原子性、一致性、隔离性、持久性 - 原子性 (Atomicity): 事务中的所有操作是一个不可分割的整体，要么全部成功，要么全部失败并回滚 - 一致性 (Consistency): 事务执行前后，数据库必须保持一致的状态，所有数据应满足约束和规则（如外键、唯一性等。示例：如果下订单时库存不足，订单创建失败，数据库中的库存数据不会被破坏) - 隔离性 (Isolation):事务的中间操作在提交前，对其他事务是不可见的，不同事务之间互不干扰(通过隔离级别设置) - 持久性 (Durability): 一旦事务提交，其对数据库的修改将永久保存，即使系统崩溃或断电也不会丢失。 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | | --- | --- | --- | --- | | 未提交读 | 会 | 会 | 会 | | 已提交读 | 不会 | 会 | 会 | | 可重复读 | 不会 | 不会 | 会 | | 串行化 | 不会 | 不会 | 不会 | > # 脏读 - 事务读取了另一个事务未提交的数据 ``` -- 测试表 CREATE TABLE `s_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名', `age` tinyint(4) NULL DEFAULT NULL COMMENT '年龄', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; INSERT INTO `s_user` VALUES (1, '张三', 11); INSERT INTO `s_user` VALUES (2, '李四', 22); INSERT INTO `s_user` VALUES (3, '王五', 33); ``` ``` -- 事务1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 设置隔离级别未提交读 -- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置隔离级别提交读 -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 设置隔离级别可重复读 -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 设置隔离级别串行化 START TRANSACTION; SELECT age FROM s_user WHERE name = '张三'; SELECT SLEEP(10); SELECT age FROM s_user WHERE name = '张三'; COMMIT; ``` ``` -- 事务2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 设置隔离级别未提交读 -- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置隔离级别提交读 -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 设置隔离级别可重复读 -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 设置隔离级别串行化 START TRANSACTION; UPDATE s_user SET age = 44 WHERE name = '张三'; SELECT SLEEP(10); COMMIT; ``` > # 不可重复读 - 事务多次读取同一条数据时，其它事务修改了这条数据，导致两次读取结果不一致 - READ COMMITTED级别, 一个事务可以读到另一个事务提交的数据 ``` -- 事务1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 设置隔离级别未提交读 -- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置隔离级别提交读 -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 设置隔离级别可重复读 -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 设置隔离级别串行化 START TRANSACTION; SELECT age FROM s_user WHERE name = '李四'; SELECT SLEEP(10); SELECT age FROM s_user WHERE name = '李四'; COMMIT; ``` ``` -- 事务2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 设置隔离级别未提交读 -- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置隔离级别提交读 -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 设置隔离级别可重复读 -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 设置隔离级别串行化 START TRANSACTION; UPDATE s_user SET age = 55 WHERE name = '李四'; COMMIT; ``` > # 幻读 - 事务多次范围内查询时，其它事务对范围内的数据插入、删除或更新, 导致查询结果发生了变化 - 可重复读级别下 InnoDB 通过多版本并发控制 MVCC 和临键锁 Next-Key Lock (间隙锁 Gap Lock + 行锁 Record Lock)解决了幻读问题 ``` -- 事务1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 设置隔离级别未提交读 -- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置隔离级别提交读 -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 设置隔离级别可重复读 -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 设置隔离级别串行化 START TRANSACTION; SELECT `name`, age FROM s_user WHERE age < 100; SELECT SLEEP(10); SELECT `name`, age FROM s_user WHERE age < 100; COMMIT; ``` ``` -- 事务2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 设置隔离级别未提交读 -- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置隔离级别提交读 -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 设置隔离级别可重复读 -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 设置隔离级别串行化 START TRANSACTION; INSERT INTO s_user (`name`, `age`) VALUES ("赵六",66); COMMIT; ``` > # 数据结构和索引类型 | 数据结构 | 索引类型 | 适用场景 | | --- | --- | --- | | B+树 | 主键索引、唯一索引、普通索引、联合索引 | OLTP 场景（范围查询、排序） | | 倒排索引 | 全文索引 | 文本模糊搜索(分词、模糊匹配) | | R-Tree | 空间索引 | 地理空间查询 | > # 查询优化 ``` - 架构上扩展 - 垂直扩展（向上扩展）: 提升单机性能, 提高单台服务器处理能力(增加 CPU、内存或存储等资源) - 水平扩展（向外扩展）: 增加更多服务器节点来分担负载,提高系统的吞吐能力(分库分表分区等) - 分库分表 - 垂直拆分(按业务拆分):根据业务逻辑（如用户表、订单表）把不同的数据表拆分到不同的数据库或表中 - 水平拆分（按数据拆分，分片)：把同一张大表的数据按照一定规则（哈希、范围）拆分到不同的数据库或表中 - 向内扩展：不增加硬件资源，而是优化数据、代码、缓存策略，提高系统效率(数据归档、冷热数据分离、sql优化等) - 维护与监控 - 定期优化表: 使用 OPTIMIZE TABLE/ALTER TABLE ... FORCE 命令定期优化表，重新组织表的存储结构，减少碎片，提高查询效率 (会对表加锁, 在负载低的时候执行) - 更新统计信息: 使用 ANALYZE TABLE 命令更新表的统计信息，帮助优化器生成更优的执行计划 - 监控慢查询: 开启慢查询日志，分析慢查询，找出性能瓶颈，进行针对性优化 - 索引优化 - 哈希索引: 哈希索引需要特定的引擎, 某些字符串的列加上索引查询还是很慢, 可以通过 CRC32(字段列) 做哈希去查询(select * from test_table where name_hash=3413111798 and `name` ='Name_1') - 压缩(前缀)索引: 设置索引长度 - ``` -- 选着合适的长度 select count(DISTINCT LEFT(name_hash,4))/count(*) as name4, count(DISTINCT LEFT(name_hash,5))/count(*) as name5, count(DISTINCT LEFT(name_hash,6))/count(*) as name6, count(DISTINCT LEFT(name_hash,7))/count(*) as name7 from test_table ``` - 组合索引: 要符合最左前缀, 比如a,b,c 对a, ab, abc生效, 单独的b和c无法使用到索引(基数（Distinct 值数量）高的列优先，减少查询扫描的行数;访问频率高的列优先，避免索引无效) - 索引覆盖: 索引包含了查询所需的所有列，查询可直接从索引中读取数据，无需回表（减少 IO） - 延迟关联: 通过先筛选数据后再关联来减少计算量 - ``` -- 分页优化: -- 查询时使用较大的 OFFSET，MySQL 必须扫描从第 1 条记录到指定 OFFSET 的所有记录 select * from test_table order by id limit 5000000, 10 --改成 select * from test_table a INNER JOIN (select id from test_table order by id limit 5000000, 10) b on a.id=b.id; ``` - or - 多列 or（如a=1 or b=2）: 对每列单独创建索引 -> 触发索引合并（Index Merge） - 多列 or 且无法索引合并,单列有索引: UNION ALL（避免去重开销）-> SELECT * FROM t WHERE a=1 UNION ALL SELECT * FROM t WHERE b=2; - join - 嵌套循环连接是 MySQL 中最常用的一种连接方式。在这种连接方式中，对于每一行来自第一个表（称为驱动表），数据库会在第二个表中查找匹配的行。MySQL 会逐行扫描第一个表，对于每一行，它都会遍历第二个表来查找匹配的行 - 选择行数较少或者能够利用索引的表作为驱动表（即最先扫描的表), 减少第二个表的扫描次数 - 子查询: - exists: (外层表数据量大,内层表数据量小)对于外层查询中的每一行，exists子查询只需检查是否存在至少一条满足条件的记录，一旦找到匹配记录就立即返回true，不必扫描整个子查询结果集(一对多的时候, EXISTS是在多的里面找到一条符合, 就执行外部的下一条) - in:(子查询结果集小) 子查询会先生成一个值列表，然后外层查询将某个列的值与该列表中的所有值进行比较。如果列表较大，可能会带来额外的开销 - 子查询优化: 物化表/物化视图, 但是Mysql不支持, 手动模拟创建一张表, 定期使用INSERT/REPLACE或CREATE TABLE ... AS SELECT刷新数据) - 负向查询: !=, <>, NOT IN, NOT LIKE... : 不走索引 - count(*): 用统计信息里的模糊数量 / 放到缓存计数里 - 字段类型不同(隐式类型转换),表字符集不同(隐式字符编码转换),字段使用了函数或表达式,like '%XXX' 会使索引失效 - 当索引扫描行数超过全表的 10%-30% 时，可能放弃索引.索引列的取值重复率太高,统计信息可能不准确或过期，导致MySQL错误估算索引的选择性，进而不使用索引 - USE INDEX (建议使用索引): 建议优化器优先考虑使用指定的索引，但如果优化器认为索引效果不好，它仍可能选择不用 - FORCE INDEX (强制使用索引): 强制优化器必须使用指定的索引，即使优化器认为它不如全表扫描快，也必须使用索引 - explain - possible_keys:可能使用的索引, key:实际使用的索引 - type: 访问方式: all(全表扫描) < index(索引扫描) < range(范围扫描) < ref(索引匹配) < const(单行匹配) < system(只有一行数据) - rows: 预计扫描的行数, filtered: 查询条件过滤后的数据比例 - extra: using index 覆盖索引, using filesort 需要排序, using where 需要额外过滤 ``` ># JOIN 的主要类型 - 内连接（INNER JOIN / JOIN）: 返回两个表中满足连接条件的记录(两个表的交集) - 外连接（OUTER JOIN） - 左外连接（LEFT OUTER JOIN / LEFT JOIN）：返回左表的所有记录以及右表中与之匹配的记录。如果右表中没有匹配，返回的右表列为 NULL - 右外连接（RIGHT OUTER JOIN / RIGHT JOIN）：与左外连接相反，返回右表的所有记录以及左表中与之匹配的记录。如果左表中没有匹配，返回的左表列为 NULL - 全外连接（FULL OUTER JOIN）：返回左右两个表中所有记录，对于没有匹配的部分显示为 NULL（MySQL 不直接支持全外连接，但可以通过 UNION 实现） - 交叉连接（CROSS JOIN）: 返回两个表的笛卡尔积，即所有可能的组合，不需要指定连接条件 --- - 自连接: 同一张表与自身进行连接，常用于比较同一表中不同记录之间的关系。 - 反连接: 返回在一个表中存在而在另一个表中不存在的记录，通常通过 NOT EXISTS 或 NOT IN 实现。 - 半连接: 返回一侧的数据：只返回主表中存在匹配记录的行，常用于存在性检查（例如使用 EXISTS 或 IN），不会将关联表的列合并到结果中。 > # 主备数据同步 - 主备数据如何保持一致: - 业务层策略: 对于需要强一致性的场景，关键的读操作可以直接指向主库，以确保读到的数据是最新的 - 半同步复制：半同步复制通过配置，在主库提交事务时，至少等待一个从库确认接收数据后再返回成功，确保数据同步的可靠性，减少延迟 - 通过对比主库和从库的 binlog 位置: 确保从库的 `Master_Log_File` 和 `Read_Master_Log_Pos` 与主库一致，即从库已经读取并应用了主库的所有 binlog - 延迟监控: Seconds_Behind_Master 字段显示从库延迟的秒数，可以通过该字段监控从库与主库的同步延迟 - 使用 GTID（全局事务 ID): 通过使用全局事务 ID来跟踪主从复制进度，GTID 提供了更高的一致性保证，减少了主备数据同步的延迟误差。GTID 的使用能够确保在复制过程中事务的一致性，避免丢失或重复应用事务 > # 数据一致性 - 强一致性: 任何时刻，所有节点看到的数据都是相同的，读操作能立即看到最新写入的数据(eg:XA) - 有序执行（线性一致性) - 无序执行 (顺序一致性) - 弱一致性: 读操作可能看到旧数据，并不能保证所有节点的数据完全同步(eg:通过Kafka去处理) - 有序执行 (因果一致性) - 无序执行(最终一致性) - Kafka > # 死锁 - 手动解决: 找到sql对应的id, kill掉 - 自动处理死锁 ``` innodb_deadlock_detect = ON //是否开启死锁检测(**实时检测死锁**，一旦发现死锁，会主动**回滚**其中一个事务，另一个事务继续执行) innodb_lock_wait_timeout = 5 //调整超时时间 ``` > # 主键id不连续 | **原因** | **描述** | **示例** | | --- | --- | --- | | **删除操作** | 删除行后，自增值不回退。 | 删除 ID=2 后，下次插入 ID=4 | | **插入失败(单体/多条)** | 插入失败后，自增值不回退。 | 自增 ID 一旦分配不会回滚，即使事务失败 | | **事务回滚** | 事务回滚后，自增值不回退。 | 与插入失败同理，自增 ID 的分配独立于事务提交 | | **手动插入 ID** | 手动指定 ID 后，自增值更新为指定值。 | 若手动插入的 ID 大于当前自增值，后续插入会从该值递增 | | **复制或导入数据** | 导入数据时手动指定 ID，导致不连续。 | 导入 ID=1000-2000 后，下次插入 ID=2001 | | **自增值缓存** | 自增值缓存机制导致未使用的值丢失。 | 服务器崩溃后，未使用的自增值丢失。 | > # 批量数据更新 - ON DUPLICATE KEY UPDATE: 当插入数据遇到唯一键冲突时，执行 UPDATE 操作 - REPLACE INTO重复时删除旧数据并插入新数据。需要覆盖旧数据的场景。(先删除再插入) - INSERT IGNORE: 忽略插入重复数据 > # XA: 两阶段提交(用于分布式事务处理，确保在多个资源上的事务能够作为一个单一的工作单元提交或回滚) - 设置 sync_binlog - 内部XA:用于同一 MySQL 实例内部的事务一致性，比如 InnoDB 存储引擎在提交时，同时需要保证重做日志（redo log）与二进制日志（binlog）的写入一致 - 外部XA: 用于跨多个 MySQL 实例或其他资源管理器的分布式事务，必须由应用层或外部事务管理器主动调用 XA 命令 ``` -- 开启 XA 事务 XA START '唯一id'; -- 执行 SQL 操作 INSERT XXX; UPDATE XXX; -- 结束 XA 事务 XA END '唯一id'; -- 准备提交 XA PREPARE '唯一id'; ( xa recover 查看所有PREPARE状态的XA) -- 回滚事务 XA ROLLBACK '唯一id'; -- 提交事务 XA COMMIT '唯一id'; ``` > # Savepoints：用于在单个事务内部创建标记点，以便可以回滚到这些特定的标记点，提供细粒度的事务控制