Mysql - 常见问题与企业级解决方案_mysql中文主键有问题吗-CSDN博客

本文链接：https://blog.csdn.net/yueerba126/article/details/133316530

👀 什么是 UUID？

UUID 是通用唯一识别码的缩写，其目的是让分布式系统中的所有元素，都能具有唯一的辨识信息，而不需要通过中央控制端来做辨识信息的指定。
下面是 UUID 的组成

在这里插入图片描述

-- 数据库中使用 uuid 函数
select uuid();

👀 为什么有人想要使用 UUID？

在数据库集群中，为了避免每个 MySQL 各自生成的主键产生重复，所以有人考虑采用 UUID 方式
在这里插入图片描述
如果采用自增模式，会出现全局 ID 冲突。

👀 UUID 主键的好处

分布式生成主键，减低了全局节点的压力，使得主键生成速度更快
主键全局唯一
跨服务器合并数据很方便，从第 2 条中派生出来的。由于 ID 唯一，所以整合方便。

👀 UUID 主键的缺点

浪费存储空间
- UUID 占用 16 个字节，比 4 字节的 int 类型和 8 字节的 bigint 类型更占用存储空间。
- 在聚簇索引上，非主键列都会携带主键值，所以更占用空间
查询速度慢
- UUID 是字符串，查询速度慢；
- 这个往深了说，还是和 B-Tree 有关。
  1. 不连续性：
    - UUID 通常是随机生成的，因此插入数据库的 UUID 是不连续的。
    - 当使用 B-Tree（或其变体，如 B+Tree）作为索引结构时，不连续的 UUID 插入可能导致频繁的节点分裂和合并，进而导致树的重组。这会影响插入和查询的性能。
  2. 字符串长度：
    - UUID 通常为 36 个字符长度（包含了 4 个短划线），比整数或短字符串占用更多的空间。
    - 更长的字符串意味着 B-Tree 中每个节点可以存储的键值对数量减少，从而增加了树的深度。深度增加导致查询所需的磁盘 I/O 次数增加，从而降低查询速度。
  3. 比较复杂性：
    - 字符串比较通常比整数比较复杂，因为它需要逐字符进行比较。
    - 当 B-Tree 进行查找、插入或删除操作时，需要频繁地进行键值比较。如果键是长字符串（如 UUID），这会增加处理时间。
数据写入随机性很大
- UUID 不是顺序增长，作为主键，数据写入 IO 随机性很大；
- 这个往深了说，还是和 B-Tree 和存储机制有关。

👀 主键自动增长的优点

占用空间少：int 和 BIGINT 类型占用存储空间少
检索速度快：MySQL 检索数字类型速度远快于字符串
IO 写入连续性好：由于主键是顺序增长的，写入连续性好

👀 分布式环境下的主键自动增长

有人想要使用 UUID 作为主键，就是在分布式环境下主键会全局冲突的问题。
比如 MyCat 中间件，就可以生成全局连续的主键值。

总结

无论什么场合，都不推荐使用 UUID 作为数据表的主键；
分布式环境下有类似 MyCat 这样的中间件来生成全局的顺序主键

👀 逻辑删除还是物理删除

物理删除 vs. 逻辑删除

物理删除:

定义: 使用 delete、truncate、drop 语句从硬盘中删除数据。
优点: 可释放存储空间，缩小数据表体积。
缺点:
- 数据恢复困难。可能需要停止数据库，影响业务。
- 恢复方法:
  1. 利用 binlog 日志: 分析日志，找出误删除的记录并重新执行。
  2. 配置同步数据节点: 延时同步，如每小时同步一次，可在延时内恢复部分数据。
- 主键不连续: 物理删除后，主键可能不连续，导致分页查询变慢。

代价：恢复数据困难
代价：主键不连续，注解不连续导致的索引问题，会导致查询慢

逻辑删除:

定义: 不真正从硬盘删除数据，而是通过某字段（例如 is_deleted）标记数据已被删除。
优点: 数据恢复简单，只需更改标记。
缺点: 长时间累积可能导致数据表体积增大。
应用: 当数据量大时，可以将逻辑删除的数据转移到历史表。例如，在系统低负载时，通过定时任务将逻辑删除的数据迁移到历史表。

建议:

核心业务表：避免物理删除，采用逻辑删除。如订单、账户、优惠券等，只做状态变更。
历史表制作：可以克隆当前表结构创建历史表，并在原表中加入逻辑删除字段。

👀 千万记录，如何快速分页

💡创建测试表

create table t_test
(
    id     int unsigned primary key not null,
    `name` varchar(200)             not null
)

💡插入测试数据

 @Test
 public void fun2() {
     try (BufferedWriter writer = Files.newBufferedWriter(Paths.get("/Users/mrcode/Desktop/test.sql"))) {
         for (int i = 1; i <= 10000000; i++) {
            // writer.append("insert into t_test(id,`name`) values (" + i + ",'" + UUID.randomUUID().toString() + "');\n");
           // 这是利用工具导入，只输出列值
           writer.append(i + "," + UUID.randomUUID().toString() + "\n");
         }
     } catch (IOException e) {
         e.printStackTrace();
     }
 }

使用 INSERT INTO 语句导入1000万条数据：
- 工具：Navicat
- 耗时：30分钟
- 结果：可能导致数据库过载，崩溃了
- 原因：这种方式是带事务执行的，导致操作特别慢
直接数据导入1000万条数据：
- 工具：DataGrip
- 耗时：4分钟
- 特点：这种方式不带事务导入，效率较高

根据上述描述，直接使用工具导入数据（如DataGrip）显然更为高效。

💡优化办法

✍利用主键索引来加速分页查询；（前提：主键连续）

写法一：

select id,name from t_test where id > 9000000 limit 100

[2020-06-07 20:13:52] 100 rows retrieved starting from 1 in 60 ms (execution: 26 ms, fetching: 34 ms)
可以看到值用了 60 毫秒；

写法二：

select id,name from t_test where id > 9000000 and id <= 9000000+100

[2020-06-07 20:15:33] 100 rows retrieved starting from 1 in 42 ms (execution: 10 ms, fetching: 32 ms)
这个耗时更少。

✍如果主键值不连续，怎么分页？

使用逻辑删除，不会造成主键不连续
利用主键索引加速，再做表连接查询
这个语句是想用子查询先利用主键索引过滤出 100 条数据，再关联出这 100 条的数据。不过这个语句在执行计划里面并不是我们希望的这样去查询的。虽然是这样，但是比 limit 还是要快上几百毫秒

select t.id, t.name from t_test t join(select id from t_test limit 9000000,100) tmp on t.id = tmp.id

[2020-06-07 20:24:02] 100 rows retrieved starting from 1 in 1 s 956 ms (execution: 1 s 935 ms, fetching: 21 ms)

✍其他解决办法

业务上限定不可以查询早期数据，或则不给出直接跳到多少条之后的入口

👀 读多写少和读多写多

💡读多写少

普遍来说，绝大多数的系统都是读多写少的，比如：
- 电商系统：
  - 如：购买商品，大部分的时间都是在浏览商品，只有在下订单的时候才产生写操作。
- 新闻系统
- 论坛系统
- 在线教育系统
这种场景中，使用普通的关系型数据库就可以实现，并发大的话，还可以使用数据库集群。

💡写多读少

比如滴滴打车：下单之后，会将行程信息轨迹不断上传到服务器，进行写入，很少会查询这次行程的所有信息。
还比如：大学食堂系统，大量的刷卡信息被写入，这些信息却很少被查询
那么写多读少的场景使用普通关系型数据库可以吗？

💡写多读少的解决方案

如果是低价值的数据，可以采用 NoSQL 数据库来存储这些数据
- 比如行程坐标，数据量大，但是每一条数据的价值不是很大，采用事务型数据库，由于事务机制，写入就很慢；
- 传统数据使用 MySQL，低价值数据使用 NoSQL
如果是高价值的数据，可以用 TokuDB 来保存
- 由于 NoSQL 大部分都不支持事物，所以需要使用事务型的数据库来写入。
- TokuDB 的写入速度是 InnoDB 的 9~20 倍。
- 传统数据使用 MySQL，高价值写入多使用 TokuDB 来写入。

💡写多读多

这种场景并不多见，非常特殊。
比如微信、QQ都有离线留言的功能。这两个的软件用户很多，有很多离线消息需要存储，用户上线之后，需要获取离线消息。所以在数据库这里看，就是读多写多的。
传统关系型数据库是没有办法承载这种场景的。可以使用 NoSQL 来存储

💡数据库集群方案缺点

数据库集群的读写速度低于单节点数据库实例
例如：MyCat 通过路由插入到集群上某一个节点上。因为需要做一些额外的操作，因此相比慢。
我们使用他是看中了它的优点

💡数据库集群方案优点

数据库集群能支持更大规模的并发访问，并且存放更多的数据

👀 如何减少并发操作的锁冲突？

把复杂的 SQL 语句，拆分成多条简单的 SQL 语句。
复杂 SQL 语句执行时间长，锁时间长，拆分成简单的语句，每次锁定数据少，锁时间短

您提供了关于在线修改数据库表结构的内容，包括传统的 ALTER TABLE 命令和使用 PerconaTookit 工具的方法。以下是对您提供内容的简化和优化：

👀 在线修改数据库表结构

💡1. 常规做法:

在业务运行过程中随意修改表结构可能导致重大问题。
常规方法是暂停业务，然后进行表结构维护。
- 例如：12306 凌晨0点至早上7点是维护时间。

💡2. `ALTER TABLE` 的缺点:

表级锁: 修改表结构时，会锁定整个表，影响写操作。
修改失败的代价大: 如果修改失败，恢复原结构会非常耗时。

💡3. PerconaTookit 工具:

提供了一个叫做 pt-online-schema-change 的工具来在线修改表结构。
工作原理:
- 复制目标表结构。
- 在新表上进行修改。
- 使用触发器同步新的数据更改。
- 当同步完成后，删除原表并重命名新表。

💡4. 安装:

依赖安装:

yum install -y perl-DBI perl-DBD-mysql perl-IO-Socket-SSL perl-Digest-MD5 perl-TermReadKey

💡5. PerconaTookit 安装:

rpm -ivh percona-toolkit-3.0.13-1.el7.x86_64.rpm
rpm -ivh percona-toolkit-debuginfo-3.0.13-1.el7.x86_64.rpm

💡6. 使用:

注意: 在某些MySQL版本中，可能会遇到与外键重命名相关的问题。确保您的MySQL版本和工具版本兼容。

修改MySQL 8的默认认证方式（此工具不支持MySQL 8新认证）:

alter user 'root'@'%' identified by 'password' password expire never;
alter user 'root'@'%' identified with mysql_native_password by '123456';

执行示例:

pt-online-schema-change --host=192.168.56.101 --port=3306 --user=root --password=123456 --alter "modify name varchar(20) not null comment '收货人'" D=neti,t=t_customer_address --print --execute

pt-online-schema-change用法

pt-online-schema-change OPTIONS DSN

参数有：

--host: 地址
--user: 用户名
--password：密码
--port：端口号
D：逻辑库
t：数据表
--alter：修改语句
--execute：执行修改
--dry-run：测试执行
--print：打印过程

👀 数据库/程序缓存如何选？

🚀 为什么要缓存数据？

📊 硬盘 vs. 内存: 数据库数据存储在硬盘上，而从硬盘读取数据的速度远慢于从内存读取。
⚡ 并发性能: 即使是优化过的数据库，在高并发情况下也可能无法维持快速的响应速度。
💡 解决方案: 通过内存缓存数据，提高数据检索速度。

🧠 数据库缓存 vs. 程序缓存

📁 数据库缓存: 如 MySQL 有自己的缓存机制，但可能不够灵活。
🚀 程序缓存: 如 Redis 和 Memcached 提供更大的控制和灵活性。

📚 MySQL 查询缓存

❌ MySQL 8.0+: 此版本不再支持查询缓存。
🚧 早期版本: 提供查询缓存，但当数据库发生变化时，缓存可能会被清空。

🌱 SpringCache 技术

📌 缓存数据: 使用 @Cacheable 注解，指示 Spring 缓存方法的结果。
🗑 清除缓存: 使用 @CacheEvict 注解，指示 Spring 从缓存中删除数据。

🔍 建议:

✅ 适合缓存的数据: 那些不常更改但经常访问的数据。
❌ 不建议缓存的数据: 经常更改或由多个表联接而来的数据。
🔧 更大的控制: 使用 Redis 这样的外部缓存解决方案。

👀 中文分词

📚 中文分词技术

当我们谈论商品搜索时，通常不会在数据库中创建索引，因为这样的搜索效率较低。这引出了中文分词技术的需求。

🤔 为什么要做中文分词？

📝 语言复杂性: 中文是一种结构复杂的语言，对于计算机来说，理解它尤为困难。
🧐 歧义: 有些句子存在多种可能的断句方式，如「兵乓球拍卖完了」。
📌 无歧义句子: 对于语法明确的句子，如「我想买电话」，可以通过中文分词技术提取出关键词「电话」。

🖥️ MySQL 的全文索引

🇬🇧 英文支持: MySQL 对英文的全文检索支持非常好。
🇨🇳 中文问题: 对于中文，MySQL 不能按语义切词，只能按字符切词。

create fulltext index text_title
    on t_sku (title);
    
select id, title, images, price
from t_sku
where match(title) against('小米9');

❗ 全文索引的弊端

📊 存储问题: 中文字段创建全文索引可能会产生大量的切词结果，从而占用大量的存储空间。
⚙️ 维护问题: 更新字段内容后，全文索引不会自动更新，需要定期手动维护。

🚀 专业的全文检索引擎: Lucene

🌐 开源: Lucene 是 Apache 的开源全文检索引擎，支持中文分词。
🔌 插件: Lucene 自带的中文分词插件功能较弱，因此常常需要使用第三方插件，如 hanlp。

在这里插入图片描述

📝 Lucene 注意事项

📋 选择: 不是所有的表都需要放到 Lucene 中。
🧐 策略: 只对需要全文检索的字段使用 Lucene。

🔄 Lucene 与 MySQL 的结合

在这里插入图片描述

Mysql - 常见问题与企业级解决方案

👀 什么是 UUID？

👀 为什么有人想要使用 UUID？

👀 UUID 主键的好处

👀 UUID 主键的缺点

👀 主键自动增长的优点

👀 分布式环境下的主键自动增长

👀 逻辑删除还是物理删除

👀 千万记录，如何快速分页

💡创建测试表

💡插入测试数据

💡优化办法

✍利用主键索引来加速分页查询；（前提：主键连续）

✍如果主键值不连续，怎么分页？

✍其他解决办法

👀 读多写少和读多写多

💡读多写少

💡写多读少

💡写多读少的解决方案

💡写多读多

💡数据库集群方案缺点

💡数据库集群方案优点

👀 如何减少并发操作的锁冲突？

👀 在线修改数据库表结构

💡1. 常规做法:

💡2. ALTER TABLE 的缺点:

💡3. PerconaTookit 工具:

💡4. 安装:

💡5. PerconaTookit 安装:

💡6. 使用:

👀 数据库/程序 缓存如何选？

🚀 为什么要缓存数据？

🧠 数据库缓存 vs. 程序缓存

📚 MySQL 查询缓存

🌱 SpringCache 技术

👀 中文分词

📚 中文分词技术

🤔 为什么要做中文分词？

🖥️ MySQL 的全文索引

❗ 全文索引的弊端

🚀 专业的全文检索引擎: Lucene

📝 Lucene 注意事项

🔄 Lucene 与 MySQL 的结合

💡2. `ALTER TABLE` 的缺点:

👀 数据库/程序缓存如何选？