8.业务场景实践2

予早 2026-04-30 23:53:30

Categories： Tags：

索引应用

索引创建

-- create table时创建索引
CREATE TABLE table_name [col_name data_type] [UNIQUE|FULLTEXT|SPATIAL] [INDEX] [index_name] (col_name [length]) [ASC|DESC]
CREATE TABLE t1 (  id INT NOT NULL,     name CHAR(30) NOT NULL,     UNIQUE INDEX UniqIdx(id) );
-- alter table时创建索引
ALTER TABLE table_name ADD  [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]
-- create index创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name  ON table_name (col_name [length],...) [ASC|DESC]
CREATE UNIQUE INDEX UniqidIdx ON book (bookId);

[!IMPORTANT]
CREATE TABLE tb_user (
`id` BIGINT, 
`name` VARCHAR (50), 
`email` VARCHAR (100), 
`age` INT, 
`created_time` DATETIME, 
`updated_time` DATETIME,
INDEX idx_name (name),
UNIQUE INDEX udx_email (email)
);
上述创建表语句会创建三个索引，idx_name、udx_email、GEN_CLUST_INDEX，SHOW INDEX FROM tb_user;无法查到系统生成的索引，需要使用：
SELECT * 
FROM information_schema.innodb_indexes 
WHERE table_id = (
SELECT table_id 
FROM information_schema.innodb_tables 
WHERE NAME = 'db_service_xxx/tb_user'
);

索引使用场景

select，查询优化为覆盖索引

where，基于索引迅速匹配

group by 或 order by，基于索引最左前缀优化分组和排序

索引失效

索引失效场景：

左规则：索引列使用左模糊匹配或联合索引并未遵循最左前缀匹配
非裸列：索引以非裸列形式参与比较：参与计算、作为函数入参、类型转换（强制或隐式）
非常量：索引与非常量比较：
成本高：索引成本估算高于全表扫描或使用索引也需全表扫描造成索引失效

分析索引是否失效，有一个重要的前提条件，就是是否使用覆盖索引，能使用覆盖索引的情况通常更容易使用索引

场景分析和验证

select version();
-- 8.4.6

创建tb_user表并使用 Navicat 按注释所示规则生成数据。

DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` BIGINT AUTO_INCREMENT COMMENT "自增主键。测试数据分布特点：非空，bigint 自增",
  `company_name` VARCHAR (120) COMMENT "公司名称。测试数据分布特点：中文名称或英文名称，5% NULL 值，随机生成",
  `birthday` DATE COMMENT "生日。测试数据分布特点：取值范围为 1970-01-01 - 2025-12-31，5% NULL 值，随机生成",
  `age` INT COMMENT "年龄。测试数据分布特点：取值范围为 0 - 100，5% NULL 值，随机生成",
  `lucky_number` INT COMMENT "幸运数。取值范围为 0 - 9，95% NULL 值，随机生成",
  `credit_card_visa` CHAR(16) COMMENT "VISA 银行卡号。测试数据分布特点：固定16位数字字符串，5% NULL 值，随机生成",
  `status` INT COMMENT "1-正常，2-限期封禁，3-永久封禁" COMMENT "用户状态。测试数据分布特点：非空，随机生成",
  `is_deleted` TINYINT (1) COMMENT "逻辑删除。测试数据分布特点：非空，随机生成",
  `created_time` DATETIME COMMENT "创建时间。测试数据分布特点：取值范围为 2000-01-01 - 2025-12-31，非空，随机生成",
  `updated_time` DATETIME COMMENT "删除时间。测试数据分布特点：取值范围为 2000-01-01 - 2025-12-31，非空，随机生成",
  PRIMARY KEY (`id`),
  INDEX idx_age (`age`),
  INDEX idx_lucky_number (`lucky_number`),
  INDEX idx_birthday (`birthday`),
  INDEX idx_birth_year ((YEAR(`birthday`))),
  INDEX idx_credit_card_visa (`credit_card_visa`),
  INDEX idx_status_is_deleted_created_time (`status`, `is_deleted`, `created_time`),
  INDEX idx_company_name (`company_name`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

左规则

索引左模糊匹配失效

-- idx_company_name
EXPLAIN SELECT * FROM tb_user WHERE company_name like '%工程%';
EXPLAIN SELECT * FROM tb_user WHERE company_name like '%工程';

联合索引不遵循最左匹配原则失效

-- idx_status_is_deleted_created_time
EXPLAIN SELECT * FROM tb_user WHERE is_deleted = false and created_time >= "2010-01-01 00:00:00"; -- 失效
EXPLAIN SELECT * FROM tb_user WHERE status = 1 and is_deleted = false and created_time >= "2010-01-01 00:00:00"; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE created_time >= "2010-01-01 00:00:00" and is_deleted = false and status = 1; -- 有效

非裸列

索引参与计算失效

-- idx_age
EXPLAIN SELECT * FROM tb_user WHERE age - 5 = 60;
EXPLAIN SELECT * FROM tb_user WHERE age - 5 > 60;

索引作为函数入参失效

-- idx_birthday
EXPLAIN SELECT * FROM tb_user WHERE DAY(birthday) = 6; -- 失效
-- 可以建立函数索引，idx_birth_year
EXPLAIN SELECT * FROM tb_user WHERE YEAR(birthday) = 2000; -- 有效

索引发生类型转换失效

-- idx_credit_card_visa
EXPLAIN SELECT * FROM tb_user WHERE credit_card_visa = 4288432640196911; -- 失效
EXPLAIN SELECT * FROM tb_user WHERE CAST(credit_card_visa AS SIGNED) = 4288432640196911; -- 失效

非常量

-- 包括两列做比较的情况，无论哪种比较，无论是否另外一列为索引列
EXPLAIN SELECT * FROM tb_user WHERE age = lucky_number;
EXPLAIN SELECT * FROM tb_user WHERE age > lucky_number;
EXPLAIN SELECT * FROM tb_user WHERE age >= lucky_number;
EXPLAIN SELECT * FROM tb_user WHERE age < lucky_number;
EXPLAIN SELECT * FROM tb_user WHERE age <= lucky_number;

成本高

-- 索引条件 or 非索引条件失效
EXPLAIN SELECT * FROM tb_user WHERE status = 1 and is_deleted = false or updated_time >= "2020-01-01 00:00:00"; -- 失效
EXPLAIN SELECT * FROM tb_user WHERE status = 1 and is_deleted = false or updated_time = "2020-01-01 00:00:00"; -- 失效


-- 测试比较和分布对失效的影响，idx_age，age 取值范围为 [0,100]，5% NULL，lucky_number 取值范围 [0,9] 95% NULL

EXPLAIN SELECT * FROM tb_user WHERE age = 50; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE age != 50; -- 无效
EXPLAIN SELECT * FROM tb_user WHERE age > 20; -- 无效
EXPLAIN SELECT * FROM tb_user WHERE age >= 20; -- 无效
EXPLAIN SELECT * FROM tb_user WHERE age > 90; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE age >= 90; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE age < 10; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE age <= 10; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE age < 80; -- 无效
EXPLAIN SELECT * FROM tb_user WHERE age <= 80; -- 无效
EXPLAIN SELECT * FROM tb_user WHERE age = NULL; -- 不可能匹配
EXPLAIN SELECT * FROM tb_user WHERE age != NULL; -- 不可能匹配
EXPLAIN SELECT * FROM tb_user WHERE age IS NULL; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE age IS NOT NULL; -- 无效
        
EXPLAIN SELECT * FROM tb_user WHERE lucky_number = 5; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE lucky_number != 5; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE lucky_number > 2; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE lucky_number >= 2; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE lucky_number > 8; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE lucky_number >= 8; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE lucky_number < 2; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE lucky_number <= 2; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE lucky_number < 8; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE lucky_number <= 8; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE lucky_number = NULL; -- 不可能匹配
EXPLAIN SELECT * FROM tb_user WHERE lucky_number != NULL; -- 不可能匹配
EXPLAIN SELECT * FROM tb_user WHERE lucky_number IS NULL; -- 有效
EXPLAIN SELECT * FROM tb_user WHERE lucky_number IS NOT NULL; -- 有效




-- in、exist 受资源限制，MySQL 根据实际情况评估是否走索引
-- 资源不足时失效
SET SESSION range_optimizer_max_mem_size = 4096;
EXPLAIN FORMAT=JSON SELECT * FROM tb_user WHERE credit_card_visa in (
"4310983056222098", "4544477230033842", "4135688111827145", "4391239892946610", "4676648722180867", "4747340267965861",
"4121788262052869", "4232073621890887", "4548999686134275", "4990111938171278", "4533137209486024", "4984823193078513",
"4106995884078169", "4865700157334906", "4490576803339116", "4732735347319285", "4166710925363513", "4704000218734441",
"4876167134235720", "4996326021285777", "4507900561804700", "4126978336448392", "4208236028529784", "4204321939836983",
"4386508113547382", "4072190443027911", "4182736799998050", "4406223091809145", "4241089434857268", "4462915462435951",
"4300701593938931", "4952196684377100"
)

-- 资源足够时走索引
SET SESSION range_optimizer_max_mem_size = 16384;
EXPLAIN FORMAT=JSON SELECT * FROM tb_user WHERE credit_card_visa in (
"4310983056222098", "4544477230033842", "4135688111827145", "4391239892946610", "4676648722180867", "4747340267965861",
"4121788262052869", "4232073621890887", "4548999686134275", "4990111938171278", "4533137209486024", "4984823193078513",
"4106995884078169", "4865700157334906", "4490576803339116", "4732735347319285", "4166710925363513", "4704000218734441",
"4876167134235720", "4996326021285777", "4507900561804700", "4126978336448392", "4208236028529784", "4204321939836983",
"4386508113547382", "4072190443027911", "4182736799998050", "4406223091809145", "4241089434857268", "4462915462435951",
"4300701593938931", "4952196684377100"
)

优化器成本模型

https://dev.mysql.com/doc/refman/8.4/en/cost-model.html

优化器成本模型中包含 SQL 执行过程中各种操作的成本数据，优化器会使用成本模型决策执行方案以生成执行计划。

优化器成本模型中成本数据由两部分组成：

MySQL 内置默认成本常量，在代码中声明后编译而来

成本表，通用成本表mysql.server_cost、具体引擎成本表mysql.engine_cost,

-- 服务器通用操作的优化器成本
SELECT * FROM mysql.server_cost;
-- 特定于具体存储引擎的操作的优化器成本
SELECT * FROM mysql.engine_cost;

可配置的优化器成本模型的工作原理如下：

服务器在启动时将成本模型表读入内存，并在运行时使用内存中的值。表中指定的任何非NULL成本估算都优先于相应的编译默认成本常量。任何NULL估算都指示优化器使用编译默认值。
在运行时，当存储引擎被动态加载或执行FLUSH OPTIMIZER_COSTS语句时，服务器会重新读取成本表。
成本表使服务器管理员能够通过修改表中的条目轻松调整成本估算。通过将条目的成本设置为NULL，也很容易恢复为默认值。优化器使用内存中的成本值，因此对表的更改后应执行FLUSH OPTIMIZER_COSTS才能生效。
客户端会话开始时当前的内存中成本估算会在整个会话期间适用，直至会话结束。特别是，如果服务器重新读取成本表，任何已更改的估算仅适用于后续启动的会话。现有会话不受影响。
成本表特定于给定的服务器实例。服务器不会将成本表的更改复制到副本。

表统计信息

cardinality，基数

表中列包含不同值的数量，可以通过SHOW INDEX FROM tbl_name;查看，该值由统计采样得来。当查询涉及到有关联索引的列时，每列的基数会影响哪种访问方法最有效。

例如，对于具有唯一约束的列，不同值的数量等于表中的行数。如果一个表有一百万行，但某一列只有 10 个不同的值，那么每个值平均出现 100,000 次。因此，像 SELECT c1 FROM t1 WHERE c1 = 50; 这样的查询可能返回 1 行，也可能返回大量行，数据库服务器可能会根据 c1 的基数以不同方式处理该查询。

如果列中的值分布非常不均匀，那么基数可能不是确定最佳查询计划的好方法。例如，SELECT c1 FROM t1 WHERE c1 = x; 在 x=50 时可能返回 1 行，在 x=30 时可能返回一百万行。在这种情况下，你可能需要使用索引提示

选择性，selectivity

数据分布的一个属性，即一列中不同值的数量（其基数）除以表中的记录数，反映列唯一值比例。高选择性意味着列值相对独特，并且可以通过索引高效检索。如果您（或查询优化器）能够预测到WHERE子句中的一个测试仅匹配表中少量（或小比例）的行，那么如果先使用索引对该测试进行评估，整个查询往往会很高效。

平均值组大小，Average Value Group Size

表总行数 / 基数。

MySQL 会以如下方式使用 Average Value Group Size：

用于估算每次 ref 访问需要读取的行数；
用于估算部分连接操作产生的行数，即形如
(...) JOIN tbl_name ON tbl_name.key = expr 的操作。

随着某个索引的 Average Value Group Size 增大，该索引在上述两种用途中的有效性会降低，因为每次查找所对应的平均行数增加了。因此，为了使索引在优化器看来更有用，理想情况下，每个索引值应只对应表中少量的行。

innodb_stats_method

innodb_stats_method 用于控制 InnoDB 中 NULL 的统计方法，不同的设置会直接改变 Average Value Group Size 的计算结果，进而影响优化器判断索引是否“足够有选择性”：

nulls_equal（默认）：所有 NULL 视为同一组，可能高估索引值分布，导致优化器误判索引无效。若某列 NULL 值占 90%，统计信息会将所有 NULL 视为同一组，导致平均行组大小被严重高估，优化器误判索引无效。
nulls_unequal：每个 NULL 视为独立组，可能低估索引选择性，导致优化器错误使用索引。
nulls_ignored：忽略 NULL 值，适用于 <=> 比较操作。

eq_range_index_dive_limit

eq_range_index_dive_limit，当 IN (…) 或 OR 列表元素个数超过该阈值（默认 200）时，优化器不再做 index dive，而是改用索引统计（Index Statistics）估算行数；若统计信息过期或分布极度倾斜，就可能导致优化器误判索引无效

联合索引中，出现范围查询，范围查询右侧索引列失效，由索引下推优化

降低索引IO

索引设计

索引命名规范

单值索引，建议以 idx_ 为开头，字母全部小写。

例如：alter table t1 add key idx_r1(r1)；

组合索引，建议以 dx_multi_ 开头，字母全部小写。

例如：alter table t1 add key idx_multi_1(r1,r2,r3) ;

唯一索引，建议以 udx_ 为开头，字母全部小写；如果是多值唯一索引，则命名方式类似 udx_multi_1 等。

例如：
alter table t1 add unique key udx_f1(r1)；
或者
alter table t1 add key udx_multi_1(r1,r2,r3);

全文索引，建议以 ft_ 开头，字母全部小写，并且建议默认用 ngram 插件。

例如：alter table t1 add fulltext ft_r1(r1) with parser ngram;

前缀索引，建议以 idx_ 开头，以 _prefix 结尾。

例如： alter table t1 add key idx_r1_prefix(r1(10));

函数索引，建议以 idx_func_ 开头，字母全部小写。

例如： alter table t1 add key idx_func_r1((mod(r1,4)));

按需建立，定期清理，限制数量

为经常需要查询、排序、分组和联合操作的字段建立索引，不建立非必要的索引，定期检查和清理收益小的索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。

因此，为这样的字段建立索引，可以提高整个表的查询速度。

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。

如果为其建立索引，可以有效地避免排序操作。

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要原来的索引就可以删除了

数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，

唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，

那可能有人会问，这个比例有什么经验值吗？

使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

当单个索引字段查询数据很多，区分度都不是很大时，则需要考虑建立联合索引来提高查询效率

注意：选择索引的最终目的是为了使查询的速度变快。

优先建立唯一性索引，唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。

如果使用姓名的话，可能存在同名现象，从而降低查询速度。

优化列以优化索引

列可以为整型就不选字符串，整型建立索引更方便

如果索引的值很长，那么查询的速度会受到影响。，能用 CHAR(10) 就不用 CHAR(80)，例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

前缀优于全文

能用前缀就不用全文，如果索引字段的值很长，最好使用值的前缀来索引。

例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。

如果只检索字段的前面的若干个字符，这样可以提高检索速度。

索引重建

索引重建情形

表上频繁发生update,delete操作；
表上发生了alter table ..move操作（move操作导致了rowid变化）

索引重建判断

一般看索引是否倾斜的严重，是否浪费了空间，对索引进行结构分析
```
analyze index index_name validate structure;
```
在相同的session中查询index_stats表
```
select height,DEL_LF_ROWS/LF_ROWS from index_stats;
```
当查询的height>=4（索引的深度，即从根到叶节点的高度）或DEL_LF_ROWS/LF_ROWS>0.2的情况下，就应该考虑重建该索引。

索引重建方法

drop原索引，然后再创建索引：
```
drop index index_name;
create index index_name on table_name (index_column);
```
这种方式相当耗时，一般不建议使用

直接重建索引：

alter index indexname rebuild;
alter index indexname rebuild online;

此方法较快，建议使用。

rebuild是快速重建索引的一种有效的办法，因为它是一种使用现有索引项来重建新索引的方法。如果重建索引时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题。由于新旧索引在建立时同时存在，因此，使用这种重建方法需要有额外的磁盘空间可供临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引移到新的表空间。

rebuild重建索引的过程：

Rebuild以index fast full scan或table full scan方式（采用那种方式取决于cost）读取原索引中的数据来构建一个新的索引，重建过程中有排序操作，rebuild online执行表扫描获取数据，重建过程中有排序的操作；
Rebuild会阻塞DML操作，rebuild online不会阻塞DML操作；
rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表，所有rebuild online时索引的变化都记录在这个表中，当新的索引创建完成后，把这个表的记录维护到新的索引中去，然后drop掉旧的索引，rebuild online就完成了。

重建索引过程中的注意事项：

执行rebuild操作时，需要检查表空间是否足够；
虽然说rebuild online操作允许DML操作，但还是建议在业务不繁忙时间段进行；
Rebuild操作会产生大量Redo Log；
模糊查询语句中如何使用索引？
在MySQL中模糊查询 mobile like ‘%8765’，这种情况是不能使用 mobile 上的索引的，那么如果需要根据手机号码后四位进行模糊查询，可以用一下方法进行改造。

我们可以加入冗余列（MySQL5.7之后加入了虚拟列，使用虚拟列更合适，思路相同），比如 mobile_reverse，内部存储为 mobile 的倒叙文本，如 mobile为17312345678，那么 mobile_reverse 存储 87654321371，为 mobile_reverse 列建立索引，查询中使用语句 mobile_reverse like reverse(’%5678’) 即可。

reverse 是 MySQL 中的反转函数，这条语句相当于 mobile_reverse like ‘8765%’ ，这种语句是可以使用索引的。

左模糊优先考虑走Elasticsearch

数据库整体优化

数据库优化

软优化

1.查询语句优化

一般查询

首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息。

EXPLAIN SELECT * FROM `user`

其中会显示索引和查询数据读取数据条数等信息。

子查询

在MySQL中，尽量使用JOIN来代替子查询。因为子查询需要嵌套查询，嵌套查询时会建立一张临时表，临时表的建立和删除都会有较大的系统开销，而连接查询不会创建临时表，因此效率比嵌套子查询高。

或者将逻辑移交给后端代码完成，压力给到后端。

慢查询

慢查询，查询语句优化的主要目标

关于order by和limit联用

并非是先order by全部排完序，然后limit

实际上进行了优化，使用堆的形式，对于非排序字段会造成顺序的不确定性，每次limit结果不太一样，最好的优化是order by category,id，附加通过主键排序，这样有一个唯一的顺序

https://blog.csdn.net/weixin_39604280/article/details/111689218

mysql数据库中允许select后含有非group by中的列

0000

2. 插入语句优化

针对插入，我们可以通过禁用索引、禁用检查等方式来提高插入速度，在插入之后再启用索引和检查。影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。针对这些情况，可以分别进行优化。

对于MyISAM引擎的表，常见的优化方法如下：

禁用索引

对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕后再开启索引。对于空表批量导入数据，则不需要进行此操作，因为MyISAM引擎的表是在导入数据之后才建立索引的。或者对于innodb而言，主键最好是自增的，b+tree插入更高效，uuid就不是，可以使用雪花算法
禁用唯一性检查

插入数据时，MySQL会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕后再开启。
使用批量插入

插入多条记录时，可以使用一条INSERT语句插入一条记录，也可以使用一条INSERT语句插入多条记录。使用一条INSERT语句插入多条记录的情形如下，而这种方式的插入速度更快。
```
INSERT INTO fruits VALUES ('x1', '101', 'mongo2', '5.7'), ('x2', '101', 'mongo3', '5.7'), ('x3', '101', 'mongo4', '5.7');
```
使用LOAD DATA INFILE批量导入

当需要批量导入数据时，如果能用LOAD DATA INFILE语句，就尽量使用。因为LOAD DATA INFILE语句导入数据的速度比INSERT语句快。

对于InnoDB引擎的表，常见的优化方法如下：

禁用唯一性检查

插入数据之前执行set unique_checks=0来禁止对唯一索引的检查，数据导入完成之后再运行set unique_checks=1。这个和MyISAM引擎的使用方法一样。
禁用外键检查

插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复对外键的检查。
禁用自动提交

插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。

3.合理使用索引

索引是提高数据库查询速度最重要的方法之一，使用索引的三大注意事项包括：

LIKE关键字匹配’%’开头的字符串，不会使用索引；
OR关键字的两个字段必须都是用了索引，该查询才会使用索引；
使用多列索引必须满足最左匹配。

4.重构表，分解表、中间表、冗余字段

分解表：对于字段较多的表，如果某些字段使用频率较低，此时应当将其分离出来从而形成新的表。

中间表：对于将大量连接查询的表可以创建中间表，从而减少在查询时造成的连接耗时。

冗余字段：类似于创建中间表，增加冗余也是为了减少连接查询。

5.分析表、检查表、优化表

分析表主要是分析表中关键字的分布；检查表主要是检查表中是否存在错误；优化表主要是消除删除或更新造成的表空间浪费。

分析表：使用 ANALYZE 关键字，如ANALYZE TABLE user

Op：表示执行的操作；
Msg_type：信息类型，有status、info、note、warning、error；
Msg_text：显示信息。

检查表：使用 CHECK关键字，如CHECK TABLE user [option]。 option 只对MyISAM有效。共五个参数值：

QUICK：不扫描行，不检查错误的连接；
FAST：只检查没有正确关闭的表；
CHANGED：只检查上次检查后被更改的表和没被正确关闭的表；
MEDIUM：扫描行，以验证被删除的连接是有效的，也可以计算各行关键字校验和；
EXTENDED：最全面的的检查，对每行关键字全面查找。

优化表：使用OPTIMIZE关键字，如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志，优化表只对VARCHAR、BLOB和TEXT有效，通过OPTIMIZE TABLE语句可以消除文件碎片，在执行过程中会加上只读锁。

硬优化

基础硬件：CPU、内存、磁盘
数据库参数
架构调整：分库分表、读写分离，缓存集群

基础硬件：CPU、内存、磁盘

配置多核心和频率高的cpu，多核心可以执行多个线程；
配置大内存，提高内存，即可提高缓存区容量，因此能减少磁盘I/O时间，从而提高响应速度；
配置高速磁盘或合理分布磁盘：高速磁盘提高I/O，分布磁盘能提高并行操作的能力。

数据库参数

优化数据库参数可以提高资源利用率，从而提高MySQL服务器性能。MySQL服务的配置参数都在my.cnf或my.ini，下面列出性能影响较大的几个参数：

key_buffer_size：索引缓冲区大小；
table_cache：能同时打开表的个数；
query_cache_size和query_cache_type：前者是查询缓冲区大小，后者是前面参数的开关，0表示不使用缓冲区，1表示使用缓冲区，但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区，2表示在查询中明确指出使用缓冲区才用缓冲区，即SQL_CACHE；
sort_buffer_size：排序缓冲区

架构调整

分库分表、读写分离

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。

另外一个，压力过大把你的数据库给搞挂了怎么办？

所以此时你必须得对系统做分库分表+读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

缓存集群

由缓存集群承载大部分读操作

优化场景

表中包含几千万条数据

建议按照如下顺序进行优化：

优化SQL和索引；
增加缓存，如memcached、redis；
读写分离，可以采用主从复制，也可以采用主主复制；
使用MySQL自带的分区表，这对应用是透明的，无需改代码，但SQL语句是要针对分区表做优化的；
做垂直拆分，即根据模块的耦合度，将一个大的系统分为多个小的系统；
做水平拆分，要选择一个合理的sharding key，为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表。

潜在慢查询拖慢调用链路

优化MySQL的慢查询，可以按照如下步骤进行：

开启慢查询日志：

MySQL中慢查询日志默认是关闭的，可以通过配置文件my.ini或者my.cnf中的log-slow-queries选项打开，也可以在MySQL服务启动的时候使用–log-slow-queries[=file_name]启动慢查询日志。

启动慢查询日志时，需要在my.ini或者my.cnf文件中配置long_query_time选项指定记录阈值，如果某条查询语句的查询时间超过了这个值，这个查询过程将被记录到慢查询日志文件中。

分析慢查询日志：

直接分析mysql慢查询日志，利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句。

常见慢查询优化：

索引没起作用的情况
- 在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。
- MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中的第1个字段时索引才会被使用。
- 查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。
优化数据库结构
- 对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
- 对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。
分解关联查询

很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效。
优化LIMIT分页

当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

批量插入优化

主键优化

页间是双向链表，页内是单链表（？连续内存？？）

B+树结构不变，用几个页看大小？？

页合并

truncate，清除标记位

磁盘碎片整理

就是oracle的高水位和行移动和表收缩，只是mysql自动优化了，oracle要手动

111

SQL 批量后台执行

准备批量 SQL 文件 batch.sql

准备 shell 文件 batch_execute.sh

#!/bin/bash
mysql -u root -p[password] --database=db < batch.sql
# 注意-p一定要和密码连着，密码中有shell特殊字符时加上单引号

授权
```
chmod u+x batch_execute.sh
```

使用 nohup 后台执行脚本

nohup ./batch_execute.sh > batch_execute.log 2>&1 &

SQL 性能分析和优化

SELECT 语句性能分析和优化

优化现有 SELECT 语句

优化现有 UPDATE 语句

111

-- 职员表
drop table sqlexercise.staff;
create table if not exists staff(
id int auto_increment,
sid int(4),
sname varchar(20),
sex numeric(1) not null comment '0:未知 1:男 2:女',
did numeric(4),
leader numeric(4),
hiredate datetime,
primary key(id),
UNIQUE  INDEX  uidx_sid(sid  ASC),
INDEX  idx_sname(sname  ASC),
INDEX  idx_did_leader_hiredate(did,leader,hiredate)
);

也可以手动创建索引

show index from staff

慢查询日志

慢日志中记录所有执行实践超过long_query_time（单位为秒）的所有SQL语句的日志。默认情况下，慢查询日志没有开启，慢查询日志long_query_time默认值为10s。

# m.cnf
-- 设置开启慢查询日志，0表示关闭，1表示开启，默认值为0
slow_query_log=1
-- 设置慢查询阈值，单位为秒，默认值为10
long_query_time=2

profile

-- 查看每一条SQL耗时
show profiles;
-- 查看指定query_id的SQL语句各个阶段耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU使用情况
show profile cpu for query query_id;

explain

EXPLAIN或DESC获取MySQL执行SELECT的执行计划。

explain select * from staff;

全索引扫描

explain select id, leader, hiredate from staff;

explain select did, leader, hiredate from staff order by did, leader;

explain select did, leader, hiredate from staff order by did desc, leader desc;

若一正一反就不可利用索引排序，索引的升降，必须要做file_sort

这个时候需要反向建立索引

create index idx_desc_did_leader on staff(did desc, leader asc);

explain select id, leader, hiredate from staff order by leader, hiredate;

order by优化

using index：通过有序索引顺序扫描直接返回有序数据，无需额外排序，效率高
using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，只要并非using index的排序均为using filesort

order by优化的关键点在于using index。前提是select的字段用到了且没有多余字段，否则filesort。

根据排序字段建立合适索引，多字段排序时，同样遵从最左前缀匹配
尽量使用覆盖索引，可以using index
多字段排序，注意各个字段的升序和降序，然后再创建复合索引
若不可避免出现filesort，大数据量排序时，有可能会数据大小会超出排序缓冲区大小，这时会在磁盘进行外部排序，所以可以适当增加排序缓冲区大小sort_buffer_size，其默认值为256k。

https://blog.csdn.net/weixin_39604280/article/details/111689218

由于order by不保证排序稳定性，所以同一条语句中相同排序字段的数据顺序不一定，这样会导致使用limit时，同一条orderby limit语句返回的数据不一样。解决办法是使用一个额外的字段参与排序保证字段顺序的一致，推荐使用主键。

select * from table order by category, id;

limit优化

limit问题在于limit 2000000, 10，此时需要获取前2000010条数据然后舍弃前2000000条数据，查询的代价非常大。

MySQL 的limit m n工作原理就是先读取前面m+n条记录，然后抛弃前m条，读后面n条想要的，所以m越大，偏移量越大，性能就越差

优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

count优化

https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions.html#function_count

count(expr)返回 SELECT 语句检索的行中 expr 非 NULL 值的数量。

count(field)返回所有数据中满足field列非NULL的总行数

count(1)返回所有数据中满足1非NULL的总行数，由于1总是不等于NULL，故count(1)相当于求全表数据量，实际上count(-1)、count(0)同理，而count(NULL)则为0

count(*)返回表中所有数据总行数

count(1)与count(*)两者功能相同，不同的是，count(1)基于原本函数的作用获取结果，而count(*)则是专用于求表总数据量的语法。

count(*)在实现上与存储引擎有关。

对于MyISAM，由于该存储引擎没有行锁，只有表锁，每次写数据库要对整个表加锁，故其将表中数据总量存储在了表的元数据中，对于MyISAM来说，count(*)效率很高，而count(1)反而需要全表查询。

对于InnoDB，由于该存储引擎中有行锁，其无法直接存储表数据总量，因此每次都需全表扫描，这时count(*)与count(1)并无本质区别，据官方文件所言，两者性能没有任何区别。

所以当我们使用InnoDB时，count(*)和count(1)均可，但由于count(*)为标准语法，建议使用count(*)。

重点：没有索引是表锁

因为name不是唯一的所以在修改的时候锁的是整表，而id是主键约束所以只是行锁

111

行列转换

行转列

以mysql为例，首先，假设我们有一张分数表（tb_score），表中的数据如下图：

可以看出，这里行转列是将原来的subject字段的多行内容选出来，作为结果集中的不同列，并根据userid进行分组显示对应的score。通常，我们有两种方式来实现这种转换。

使用 CASE…WHEN…THEN 语句实现行转列，参考如下代码：
```
SELECT userid, 
SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文', 
SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学', 
SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语', 
SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治'  
FROM tb_score  GROUP BY userid
```
注意，SUM() 是为了能够使用GROUP BY根据userid进行分组，因为每一个userid对应的subject=”语文”的记录只有一条，所以SUM() 的值就等于对应那一条记录的score的值。假如userid =’001’ and subject=’语文’ 的记录有两条，则此时SUM() 的值将会是这两条记录的和，同理，使用Max()的值将会是这两条记录里面值最大的一个。但是正常情况下，一个user对应一个subject只有一个分数，因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函数都可以达到行转列的效果。
使用 IF() 函数实现行转列，参考如下代码：
```
SELECT userid, 
SUM(IF(`subject`='语文',score,0)) as '语文', 
SUM(IF(`subject`='数学',score,0)) as '数学', 
SUM(IF(`subject`='英语',score,0)) as '英语', 
SUM(IF(`subject`='政治',score,0)) as '政治'  
FROM tb_score  GROUP BY userid
```
注意，IF(subject=’语文’,score,0) 作为条件，即对所有subject=’语文’的记录的score字段进行SUM()、MAX()、MIN()、AVG()操作，如果score没有值则默认为0。

KILL LONG TRANSACTION

杀掉运行时间超过指定秒数、仍未提交的 InnoDB 事务

-- 杀掉运行时间超过指定秒数、仍未提交的 InnoDB 事务，防止行锁与 MVCC 历史堆积
-- 参数：p_max_idle 事务最大允许“存活”时间（单位：秒）
DELIMITER $$

CREATE PROCEDURE kill_idle_transactions(IN p_max_idle INT)
BEGIN
  /* 变量：保存需要杀掉的线程 ID */
  DECLARE v_id BIGINT;

  /* 循环控制标志：0-未结束 1-已无数据 */
  DECLARE done INT DEFAULT 0;

  /* 1. 找出所有处于 RUNNING 状态且“存活”时间超过阈值的 InnoDB 事务 */
  DECLARE c CURSOR FOR
    SELECT trx_mysql_thread_id
      FROM information_schema.innodb_trx
     WHERE trx_state = 'RUNNING'
       AND TIMESTAMPDIFF(SECOND, trx_started, NOW()) > p_max_idle;

  /* 2. 当游标无数据可抓时，将 done 设为 1，结束循环 */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  /* 3. 打开游标 */
  OPEN c;

  /* 4. 循环逐条处理 */
  lp: LOOP
    FETCH c INTO v_id;          -- 取一条线程 ID
    IF done THEN LEAVE lp; END IF;  -- 没数据就跳出循环

    KILL v_id;                  -- 强制断开该线程，回滚其事务
  END LOOP lp;

  /* 5. 关闭游标，释放资源 */
  CLOSE c;
END$$

DELIMITER ;

-- 用法示例：杀掉“存活”超过 5 分钟（300 秒）的事务
CALL kill_idle_transactions(300);

需要拥有 PROCESS 和 CONNECTION_ADMIN（或 SUPER）权限。
被杀掉的连接会收到 Lost connection to MySQL server during query 错误，应用端需做好重试/容错。
建议先在业务低峰期试运行，观察对应用的影响；可配合 pt-kill 等工具做更复杂的策略。

show_table_bloat

估算 InnoDB 表膨胀率（Data_free / Data_length），方便挑表做 OPTIMIZE

-- 将语句结束符临时改为 $$，避免存储过程体中的分号被客户端提前解析
DELIMITER $$

-- 创建存储过程：显示指定数据库中每张表的“膨胀”情况
-- 参数：dbname 要检查的数据库名
CREATE PROCEDURE show_table_bloat(IN dbname VARCHAR(64))
BEGIN
  -- 查询 information_schema.tables 获取表空间使用统计
  SELECT
    table_name,                                          -- 表名
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,      -- 数据部分大小（MB）
    ROUND(index_length / 1024 / 1024, 2) AS idx_mb,      -- 索引部分大小（MB）
    ROUND(data_free / 1024 / 1024, 2) AS free_mb,        -- 已分配但未使用的空间（MB）
    ROUND(data_free / data_length * 100, 2) AS bloat_pct -- “空洞”占数据量的百分比
  FROM
    information_schema.tables
  WHERE
    table_schema = dbname      -- 只查看指定库
    AND data_length > 0        -- 排除空表，防止除零
  ORDER BY
    bloat_pct DESC;            -- 按膨胀率从高到低排序，先看到最浪费的表
END$$

-- 恢复默认语句结束符
DELIMITER ;

-- 用法示例：查看 mydb 数据库中各表的膨胀情况
CALL show_table_bloat('db_service_xxx');

data_free 在 MySQL 8.0 且 innodb_file_per_table=ON 时，反映的是 独立表空间文件（*.ibd） 里未使用的碎片；如果表仍在共享系统表空间（ibdata1），则 data_free 指的是整个系统表空间的空闲区域，会看上去“虚高”。
结果仅作参考，实际回收碎片需执行 OPTIMIZE TABLE <表名>（会锁表并重建表）或使用在线工具（如 pt-online-schema-change）。

clean_old_logs

分批删除数据

-- 将语句结束符临时改为 $$，防止过程体中的分号被客户端提前解析
DELIMITER $$

-- 创建存储过程：分批清理过期日志，避免一次性大批量删除造成主从延迟或锁表
-- 参数：
--   p_days  ：保留日志的天数，早于此刻的日志将被删除
--   p_limit ：每批删除的行数，控制事务大小
CREATE PROCEDURE clean_old_logs(IN p_days INT, IN p_limit INT)
BEGIN
    -- 重复执行直到没有满足条件的记录
    REPEAT
        -- 删除早于指定时间的过期日志，每次最多删 p_limit 行
        DELETE FROM log_table
        WHERE create_time < DATE_SUB(NOW(), INTERVAL p_days DAY)
        LIMIT p_limit;

        -- 短暂休眠 0.1 秒，给从库应用日志、缓解 IO 压力
        DO SLEEP(0.1);

    -- 当上一批删除行数为 0 时结束循环（ROW_COUNT() 返回上一次 DML 影响的行数）
    UNTIL ROW_COUNT() = 0 END REPEAT;
END$$

-- 恢复默认语句结束符
DELIMITER ;

-- 用法示例：删除 30 天以前的日志，每批最多删 1000 行
-- CALL clean_old_logs(30, 1000);

使用 pt-archive

copy_table_to_backup

快速备份一张表到备份库

-- 将语句结束符临时改为 $$，防止过程体中的分号被客户端提前解析
DELIMITER $$

-- 存储过程：快速对指定表做“快照”备份
-- 参数：
--   p_src_db  : 源库名
--   p_src_tbl : 源表名
-- 效果：
--   1. 在固定库 backup 下创建与源表结构完全一致的新表，表名带时间戳
--   2. 将源表当前所有数据一次性插入到新表（全表 COPY）
CREATE PROCEDURE copy_table_to_backup(IN p_src_db VARCHAR(64), IN p_src_tbl VARCHAR(64))
BEGIN
    -- 1. 生成备份表名，格式：源表名_YYYYMMDD_HHMMSS
    SET @bak_tbl = CONCAT(p_src_tbl, '_', DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s'));

    -- 2. 拼 CREATE TABLE ... LIKE 语句，实现“零数据”建表并保留索引、默认值等定义
    SET @sql = CONCAT('CREATE TABLE backup.', @bak_tbl, ' LIKE ', p_src_db, '.', p_src_tbl);
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    -- 3. 拼 INSERT ... SELECT 语句，把源表当前数据整体写入备份表
    SET @sql = CONCAT('INSERT INTO backup.', @bak_tbl, ' SELECT * FROM ', p_src_db, '.', p_src_tbl);
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
END$$

-- 恢复默认语句结束符
DELIMITER ;

使用注意

两段过程都是整表 COPY，大表会锁表（5.7）或产生大量 redo/undo（8.0），不适合高峰期。
目标库（backup / archive）必须事先存在，否则 CREATE TABLE 报错。
不会自动清理旧备份，需额外脚本定期 DROP 或 RENAME。
若源表有触发器、外键，记得用 CREATE TABLE ... LIKE 后的二次脚本再处理。

show_disk_usage

查看所有库、表、索引总体磁盘占用

-- 临时更换语句结束符，防止过程体中的分号被提前提交
DELIMITER $$

/*
  存储过程：show_disk_usage
  功能     ：查看实例中每个库的数据+索引占用磁盘空间（近似值）
  返回列   ：
    db_name   : 数据库名
    total_mb  : 该库数据+索引总大小（MB）
    data_mb   : 该库数据部分大小（MB）
    idx_mb    : 该库索引部分大小（MB）
    tables    : 该库下表的数量
  排序     ：按总大小降序，最占空间的库排在最前
  注意     ：
  1. 仅统计 InnoDB/MyISAM 等 engine 实际写入 information_schema 的值；
  2. 对于共享表空间（ibdata1），data_free 不能按库拆分，因此这里只反映“逻辑”大小；
  3. 需要对该实例有 information_schema 查询权限。
*/
CREATE PROCEDURE show_disk_usage()
BEGIN
  SELECT table_schema  AS db_name,                                    -- 库名
         ROUND(SUM(data_length+index_length)/1024/1024, 2) AS total_mb,  -- 数据+索引总和
         ROUND(SUM(data_length)/1024/1024, 2)              AS data_mb,   -- 仅数据
         ROUND(SUM(index_length)/1024/1024, 2)             AS idx_mb,    -- 仅索引
         COUNT(*)                                          AS tables     -- 表个数
  FROM information_schema.tables
  GROUP BY table_schema
  ORDER BY total_mb DESC;   -- 谁最占空间排最前
END$$

DELIMITER ;

-- 调用示例：查看整实例各库磁盘使用
CALL show_disk_usage();

analyze_all_tables

批量给所有表 ANALYZE TABLE（更新统计信息）

DELIMITER $$
CREATE PROCEDURE analyze_all_tables(IN p_schema VARCHAR(64))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_table VARCHAR(64);
  DECLARE cur CURSOR FOR
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = p_schema AND table_type = 'BASE TABLE';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  loop_tables: LOOP
    FETCH cur INTO v_table;
    IF done THEN LEAVE loop_tables; END IF;
    SET @sql = CONCAT('ANALYZE TABLE `', p_schema, '`.`', v_table, '`');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur;
END$$
DELIMITER ;
CALL analyze_all_tables('your_db');

top_processlist

生成「当前连接」资源消耗总览（类似 mytop）

DELIMITER $$
CREATE PROCEDURE top_processlist()
BEGIN
  SELECT id,
         user,
         host,
         db,
         command,
         time AS seconds,
         state,
         LEFT(info, 100) AS sql_preview,
         current_memory AS mem_bytes
  FROM performance_schema.threads t
  JOIN performance_schema.events_statements_current s
    ON t.thread_id = s.thread_id
  ORDER BY time DESC;
END$$
DELIMITER ;
CALL top_processlist();

schema_to_md

把全库表结构一键导出成 Markdown 文档

DELIMITER $$
CREATE PROCEDURE schema_to_md(IN p_db VARCHAR(64))
BEGIN
  SELECT CONCAT('### `', table_name, '`',
                '\n| 字段 | 类型 | 可空 | 默认值 | 备注 |\n| --- | --- | --- | --- | --- |')
  UNION ALL
  SELECT CONCAT('| ', column_name, ' | ', column_type, ' | ',
                is_nullable, ' | ', IFNULL(column_default,''), ' | ', column_comment, ' |')
  FROM information_schema.columns
  WHERE table_schema = p_db
  ORDER BY table_name, ordinal_position;
END$$
DELIMITER ;
CALL schema_to_md('ecommerce');

表IO读写分析

SELECT 
    OBJECT_SCHEMA                          AS table_schema,
    OBJECT_NAME                            AS table_name,
    
    /* 细化读写 */
    COUNT_READ                             AS read_ops,
    COUNT_WRITE                            AS write_ops,
    SUM_TIMER_READ  / 1e12                 AS read_latency_s,
    SUM_TIMER_WRITE / 1e12                 AS write_latency_s,
    SUM_TIMER_WAIT  / 1e12                 AS total_latency_s,
    
    /* 百分比：快速定位热点 */
    ROUND(100 * SUM_TIMER_WAIT / 
          (SELECT SUM(SUM_TIMER_WAIT) FROM performance_schema.table_io_waits_summary_by_table), 2) 
    AS pct_io_time,
    
    /* 平均每次 I/O 延迟，单位毫秒 */
    ROUND((SUM_TIMER_WAIT / (COUNT_READ + COUNT_WRITE)) / 1e6, 3) AS avg_io_ms
FROM 
    performance_schema.table_io_waits_summary_by_table
WHERE 
    OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
ORDER BY 
    total_latency_s DESC;

sys.statement_performance_analyzer

-- 把 performance_schema 里当前时刻的 events_statements_summary_by_digest 表内容复制到 sys.tmp_digests（临时表）里
CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
-- 最慢的 Top10
CALL sys.statement_performance_analyzer(
        'overall',                -- 动作
        NULL,                     -- in_table（或'NOW()'，或自己保存的快照表）
        'with_runtimes_in_95th_percentile');  -- 过滤条件

-- I/O 最多的 Top10
CALL sys.statement_performance_analyzer(
        'overall',
        NULL,
        'with_full_table_scans');

SELECT * FROM information_schema.innodb_trx;-- 当前运行的事务
SELECT * FROM PERFORMANCE_SCHEMA.data_locks;-- 当前持有的锁
SELECT * FROM PERFORMANCE_SCHEMA.data_lock_waits;-- 锁等待关系
  
  
-- 事务中锁等待关系（包含等待者和阻塞者）
SELECT
  r.trx_id AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query AS waiting_query,
  b.trx_id AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query AS blocking_query 
FROM
  PERFORMANCE_SCHEMA.data_lock_waits w
  JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
  JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

-- 索引优化，快速发现问题索引，删除前务必确认 **没有语句显式 FORCE/USE INDEX

-- 1. 查看重复/冗余索引
SELECT * FROM sys.schema_redundant_indexes;

-- 2. 查看从未被用过的索引
SELECT * FROM sys.schema_unused_indexes;

CALL sys.diagnostics ( 1, 1, 'current' );
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;


SELECT * FROM information_schema.routines




-- 查看当前锁等待
SELECT * FROM sys.innodb_lock_waits\G

-- 查看最慢SQL（总耗时）
SELECT * FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;

-- 查看未使用索引
SELECT * FROM sys.schema_unused_indexes;

将一张表的部分数据更新到另一张表，该如何操作呢？

参考答案

可以采用关联更新的方式，将一张表的部分数据，更新到另一张表内。参考如下代码：

update b set b.col=a.col from a,b where a.id=b.id;
update b set col=a.col from b inner join a on a.id=b.id;
update b set b.col=a.col from b left Join a on b.id = a.id;

### 补数据


# 更新数据
UPDATE `gatherone_oms`.`tb_alm_pitch_record` AS t0
JOIN (
  SELECT
    t1.`guidance_id`,
    t2.`account_id`
  FROM
    (
      SELECT DISTINCT
        `guidance_id` COLLATE utf8mb4_0900_ai_ci AS `guidance_id`
      FROM
        `gatherone_oms`.`tb_alm_pitch_record`
    ) t1
    LEFT JOIN (
      select
        `account_id`,
        `guidance_id`
      from
        `gatherone_plateform_data`.`tb_meta_guidance`
      GROUP BY
        `account_id`,
        `guidance_id`
    ) t2 ON t1.`guidance_id` = t2.`guidance_id`
) AS t3 ON t3.`guidance_id` = t0.`guidance_id`
SET
  t0.`account_id` = t3.`account_id`;

导出所有创建索引的语句

 SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ', 'MODIFY COLUMN `', COLUMN_NAME, '` ',
               IF(UPPER(DATA_TYPE) = 'INT', REPLACE(SUBSTRING_INDEX(UPPER(COLUMN_TYPE), ')', 1), 'INT', 'INTEGER'),
                  UPPER(COLUMN_TYPE)), ') UNSIGNED NOT NULL AUTO_INCREMENT;')
 FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = 'rz_gs'
   AND EXTRA = UPPER('AUTO_INCREMENT')
 ORDER BY TABLE_NAME ASC;

README

7.MySQL 集群