数据库优化

尚能饭否_

已于 2024-03-06 10:57:25 修改

阅读量57

点赞数 1

分类专栏： MySQL 文章标签： mysql

于 2023-05-24 17:16:49 首次发布

本文链接：https://blog.csdn.net/2201_75520484/article/details/130851410

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

utf8和utf8mb4的区别

datetime和timestamp的区别

UUID和自增id的优缺点及应用

UUID的优点：

全局唯一：UUID是全球唯一标识符，几乎可以保证在全球范围内不会出现重复的情况，可以避免分布式系统中的冲突问题。
无需依赖数据库：UUID的生成不依赖于数据库，可以在应用层生成，避免了在插入数据库前需要查询数据库获取下一个自增ID的开销。
安全性：由于UUID的生成算法是随机的，不容易被猜测，因此在一些需要隐藏真实数据ID的场景中有用。

UUID的缺点：

字符串长度较长：UUID通常是128位的字符串（例如UUID v4），相比较自增ID，存储空间更大，可能导致索引和查询效率降低。
查询效率较低：在数据库表中使用UUID作为主键或索引会导致查询效率较低，因为相比较自增ID，UUID是随机的，不容易顺序排列。
不适合聚集索引：在一些数据库中，使用UUID作为主键或索引会导致聚集索引的碎片化，影响性能。

自增ID的优点：

效率高：自增ID是按照递增顺序生成的，对于插入数据、查询数据都有较高的效率。
存储空间小：自增ID通常是整数类型，占用的存储空间较小，特别是对于大量数据的情况下，节省了存储空间。
适合聚集索引：自增ID作为聚集索引可以使数据在物理上存储上更紧凑，提高查询效率。

自增ID的缺点：

不适用于分布式系统：在分布式系统中使用自增ID可能会出现冲突问题，需要引入复杂的机制来确保唯一性。
有限制：自增ID在达到最大值时可能会出现溢出问题，需要谨慎处理。

综合来看，选择使用UUID还是自增ID取决于具体的业务需求和应用场景。在需要全局唯一且不依赖数据库的情况下，UUID是一个不错的选择；而在需要高效率、适合聚集索引和在分布式系统中使用的情况下，自增ID更适合。有时候也可以根据具体需求结合两者的优点来做设计。

生成随机UUID：UUID randomUUID = UUID.randomUUID();

将字符串解析成UUID对象：String uuidString = "c556eef0-97f7-4e94-9b7d-eef9f19edc3f";

UUID uuid = UUID.fromString(uuidString);

获取UUID的最高64位数值部分（用于数据库存储等场景）：

UUID uuid = UUID.randomUUID();

long mostSigBits = uuid.getMostSignificantBits();

获取UUID的最低64位数值部分（用于数据库存储等场景）：

UUID uuid = UUID.randomUUID();

long leastSigBits = uuid.getLeastSignificantBits();

通过字节数组创建UUID：

byte[] bytes = new byte[16]; // 填充字节数组，确保长度为16

UUID uuid = UUID.nameUUIDFromBytes(bytes);

为什么将UUID裁剪成两部分

UUID（Universally Unique Identifier）是一种全球唯一标识符，通常由128位的二进制数字组成，它可以用来唯一标识某个对象或实体。

在某些情况下，我们可能需要将UUID存储到数据库或其他存储系统中。然而，数据库中通常使用64位整数（long类型）等数据类型来存储数据，而UUID是128位的，直接存储整个UUID可能会占用过多的存储空间。

为了节省存储空间并提高性能，一些数据库和存储系统支持将UUID分成两部分，分别存储最高64位和最低64位数值部分。这样做的好处是：

节省存储空间：将UUID拆分成两部分，每部分都使用64位整数来存储，可以节省存储空间。
提高查询性能：在数据库中使用64位整数存储UUID的一部分，可以提高查询性能。比如，使用64位整数作为索引，可以快速查找特定UUID相关的数据。
方便排序：将UUID拆分成两部分后，可以方便地对其中一部分进行排序，这在某些场景下可能很有用。

虽然将UUID拆分成两部分可以节省存储空间和提高性能，但也需要注意不同系统之间的兼容性问题。在使用UUID存储到数据库或其他存储系统时，需要确保数据的读取和处理方式在各个系统中是一致的。

OPTIMIZE TABLE

OPTIMIZE TABLE 是 MySQL 中用于优化表的语句。执行这个语句时，MySQL 会重新组织表的存储结构，回收未使用的空间，提高表的性能。具体来说，OPTIMIZE TABLE 可以执行以下操作：

重建表索引： 移除索引中的碎片，提高索引的性能。
优化表碎片： 释放表中的未使用空间，减小表的物理大小。
更新统计信息： 更新表的统计信息，以便查询优化器能够更好地选择执行计划。

语法： OPTIMIZE TABLE users;

执行这个语句后，MySQL 将尝试对 users 表进行优化。这在以下情况下可能是有益的：

表中有大量的删除或更新操作，导致表碎片增多。
表中的数据发生较大变化，导致索引不再有效。
表的物理大小变得较大，想要释放未使用的空间。

请注意，在执行 OPTIMIZE TABLE 时，可能会锁定表，因此在生产环境中需要谨慎操作，以避免对业务造成影响。在 MySQL 5.6.17 之后的版本中，OPTIMIZE TABLE 操作是在线的，不会锁定整个表。

总体而言，定期执行 OPTIMIZE TABLE 可以帮助维护表的性能，并确保数据库系统在处理大量变动的数据时能够保持高效。

SQL优化策略

避免使用 SELECT *。这有助于减小数据传输量，提高查询效率。
表中的列使用合适的索引。索引可以大幅提升查询性能，尤其是在大型表上。 -- 查询时使用索引，在age列创建索引 CREATE INDEX idx_age ON users (age); SELECT username FROM users WHERE age > 18;
尽量避免在 WHERE 子句中对列进行函数操作，在 WHERE 子句中使用函数可能导致索引失效，影响性能。
谨慎使用SELECT DISTINCT， 可能导致查询性能下降，因为数据库需要对整个结果集进行去重。
避免过度使用子查询，过度使用子查询可能导致性能下降。在可能的情况下，尽量使用连接（JOIN）操作。
在分页查询时，使用 LIMIT 和 OFFSET 来限制返回的结果集，而不是获取全部数据再进行分页。
对于频繁执行相同查询的场景，考虑使用缓存来减轻数据库负担。
对于频繁更新、删除或插入操作的表，定期进行表优化可以提高查询性能。
通过使用连接池，可以减少数据库连接的开销，提高性能

左模糊

左模糊查询（以通配符 % 在字符串的左侧进行匹配）可能会影响数据库索引的性能，特别是在大型数据集上。

索引利用： 通常情况下，数据库系统可以有效地利用索引来加速右模糊查询（以 % 在字符串的右侧进行匹配）。例如，如果你在一个列上创建了索引，然后执行了一个查询 column LIKE 'prefix%'，数据库可以使用索引快速定位到第一个匹配前缀的位置，并继续向后扫描直到找到所有匹配的行。
左模糊查询的性能： 但是，左模糊查询则不太可能有效地利用索引。因为 % 通配符出现在字符串的开头，数据库无法使用索引直接定位到匹配的起始位置，而是需要遍历整个索引，对每一行进行匹配。这会导致索引扫描的开销变得非常高，尤其是当数据集很大时。
优化方法： 如果你需要执行左模糊查询，并且查询的列经常被用作条件进行查询，你可能需要考虑创建适当的索引。例如，可以考虑使用逆序索引，或者考虑使用全文搜索引擎（如 Elasticsearch）来处理模糊查询。

总的来说，左模糊查询可能会影响数据库索引的性能，尤其是在大型数据集上。因此，在设计数据库索引时，需要根据实际情况考虑查询模式和数据分布，以确保索引能够有效地支持常见的查询操作。