MySQL 相关问题集锦, 持续更新

一叶飘零_sweeeet

已于 2023-07-25 20:15:37 修改

阅读量1.4k

点赞数 1

分类专栏：果酱紫 DB mysql 文章标签： mysql 数据库

于 2023-07-25 20:12:01 首次发布

本文链接：https://blog.csdn.net/jam_yin/article/details/131918186

版权

果酱紫同时被 3 个专栏收录

66 篇文章 1 订阅

订阅专栏

mysql

18 篇文章 0 订阅

订阅专栏

9 篇文章 0 订阅

订阅专栏

一.MySQL主键选择：自增整数 vs. UUID

1. 自增整数作为主键

自增整数主键是指在插入新记录时，数据库会自动为主键字段分配一个唯一的整数值。以下是自增整数主键的优点：

性能优化：自增整数主键在性能方面表现出色。由于整数类型的比较和索引效率高，查询速度快，索引占用的空间较小。
顺序性：自增整数主键具有天然的顺序性，新记录的插入不会打破索引的有序性，有助于提高范围查询的效率。
简单性：自增整数主键易于管理和维护，不容易出错。

然而，自增整数主键也存在一些限制：

可预测性：自增整数主键的值是连续增长的，可能会暴露一些敏感信息，如用户注册顺序。
分布式环境下的挑战：在分布式环境中，生成全局唯一的自增ID可能会面临一些挑战，需要额外的处理和同步机制。

2. UUID作为主键

UUID是一种128位的全局唯一标识符，通常由字母和数字组成，具有以下优点：

全局唯一性：UUID具有极高的全局唯一性，可以在分布式环境中生成唯一的标识符，避免了冲突的风险。
随机性：UUID的值是随机生成的，不容易被猜测到，可以提供更好的安全性。
无序性：UUID的值是无序的，插入新记录不会破坏索引的有序性。

然而，UUID主键也存在一些考虑因素：

性能开销：由于UUID是较长的字符串，与整数类型相比，存储和索引的开销较大，可能导致性能下降。
索引碎片：使用UUID作为主键可能导致索引碎片化，影响查询性能。
可读性：UUID是一串字符，不如自增整数主键直观可读。

在选择MySQL主键类型时，需要综合考虑具体的应用场景和需求。

如果性能是关键因素，且没有特殊需求，自增整数主键是一个不错的选择。
如果全局唯一性和安全性更重要，或者在分布式环境中使用，UUID主键是更合适的选择。

最佳实践是根据具体情况权衡利弊，根据应用需求选择合适的主键类型。

3. MySQL使用B+树作为其默认的索引结构，主要是因为B+树具有以下优点：

良好的读写性能：B+树是一种平衡树，具有较低的高度，因此在查找、插入和删除操作上具有较好的性能。B+树的查找复杂度为O(log n)，其中n是索引中的数据量。
顺序访问性能好：B+树的叶子节点使用链表连接，可以支持范围查询和顺序访问，适合于数据库的范围查询操作，如BETWEEN、ORDER BY等。
支持高效的范围查询：B+树的叶子节点之间有序连接，可以很快地找到某个范围内的数据。这对于数据库的范围查询非常重要，例如查找某个时间段内的数据。
支持快速的插入和删除：B+树的平衡性保证了插入和删除操作的效率。当插入新数据时，B+树只需要进行少量的节点分裂操作，而不会像平衡二叉树那样频繁地进行旋转操作。
适合磁盘存储：B+树的节点通常比较大，可以容纳更多的关键字，减少了磁盘I/O次数。B+树的叶子节点之间使用链表连接，可以提高磁盘顺序读取的效率。
支持高并发操作：B+树的读写操作不会对整棵树进行锁定，而是只锁定需要访问的节点，这样可以提高并发操作的性能。

4. 多个索引会有多份数据吗?

在MySQL中，每个索引都需要存储索引的数据结构和索引键的值，但实际数据只存储一份。多个索引不会导致多份数据的冗余存储。

当你在表中创建多个索引时，每个索引都会维护自己的数据结构来支持快速的索引查找。这些数据结构包括B+树或哈希表等，用于存储索引键和指向实际数据的指针或位置信息。

然而，实际的数据仅在表的数据区域存储一次。每个索引的数据结构中存储的是对实际数据的引用，而不是实际数据本身。这样可以节省存储空间，并且在更新数据时不需要同步多个索引的数据。

当你执行查询操作时，MySQL会根据查询条件选择合适的索引进行查找，并使用索引中的键值来定位实际数据的位置。然后，MySQL通过索引的指针或位置信息找到实际数据，并返回给查询结果。

需要注意的是，虽然多个索引不会导致多份数据的冗余存储，但索引的存在会占用额外的存储空间。因此，在创建索引时需要权衡存储空间和查询性能之间的关系，并根据实际情况选择适当的索引策略。

5. 多个索引会有多份数据吗?数据库的隔离级别

假设我们有一个名为 "users" 的表，其中包含用户的姓名和余额两列。

脏读（Dirty Read）：

假设事务A修改了用户的余额，但尚未提交。同时，事务B读取了这个未提交的余额。

使用读未提交（Read Uncommitted）隔离级别的示例：

-- 事务A
BEGIN TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE name = 'Alice';

-- 事务B
BEGIN TRANSACTION;
SELECT balance FROM users WHERE name = 'Alice';
-- 这里事务B可以读取到事务A尚未提交的余额，从而产生脏读

使用读已提交（Read Committed）隔离级别的示例：

-- 事务A
BEGIN TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE name = 'Alice';
COMMIT;

-- 事务B
BEGIN TRANSACTION;
SELECT balance FROM users WHERE name = 'Alice';
-- 事务B只能读取到已经提交的余额，避免了脏读

不可重复读（Non-repeatable Read）：

假设事务A在某个时间点读取了用户的余额。在此期间，事务B修改了该用户的余额并提交。

使用可重复读（Repeatable Read）隔离级别的示例：

-- 事务A
BEGIN TRANSACTION;
SELECT balance FROM users WHERE name = 'Alice';
-- 记住事务A读取到的余额

-- 事务B
BEGIN TRANSACTION;
UPDATE users SET balance = balance + 50 WHERE name = 'Alice';
COMMIT;

-- 事务A再次读取同一用户的余额
SELECT balance FROM users WHERE name = 'Alice';
-- 事务A读取到的余额与之前不一致，产生了不可重复读

幻读（Phantom Read）：

假设事务A在某个时间点查询了用户的余额范围，得到了一组结果。在此期间，事务B插入了新的用户数据行，使得事务A再次查询相同的范围时发现有额外的行。

使用可重复读（Repeatable Read）隔离级别的示例：

-- 事务A
BEGIN TRANSACTION;
SELECT * FROM users WHERE balance > 1000;
-- 记住事务A查询到的结果集

-- 事务B
BEGIN TRANSACTION;
INSERT INTO users (name, balance) VALUES ('Bob', 2000);
COMMIT;

-- 事务A再次查询相同的范围
SELECT * FROM users WHERE balance > 1000;
-- 事务A发现有额外的行出现，产生了幻读

更新丢失（Lost Update）：

假设有两个事务：事务A和事务B，同时读取并修改同一用户的余额。事务A先读取数据，然后事务B也读取相同的数据。接着，事务A和事务B都进行了修改并提交。

使用锁机制和较高的隔离级别（如可重复读或串行化）的示例：

-- 事务A
BEGIN TRANSACTION;
SELECT balance FROM users WHERE name = 'Alice';
-- 记住事务A读取到的余额

-- 事务B
BEGIN TRANSACTION;
SELECT balance FROM users WHERE name = 'Alice';
-- 记住事务B读取到的余额

-- 事务A和事务B都进行了修改并提交
UPDATE users SET balance = balance - 100 WHERE name = 'Alice';

-- 事务A的提交
COMMIT;

-- 事务B的提交
COMMIT;

-- 由于并发执行，其中一个事务的更新可能会覆盖另一个事务的更新，导致更新丢失

六.数据库默认隔离级别,一定会产生幻读吗,怎么解决

数据库的默认隔离级别可以因数据库管理系统（DBMS）而异。在大多数常见的关系型数据库中，如MySQL和PostgreSQL，默认的隔离级别通常是"可重复读"（Repeatable Read）。

在"可重复读"隔离级别下，数据库使用读取锁（Shared Lock）来确保一个事务读取的数据在事务提交之前不会被其他事务修改。这可以防止脏读和不可重复读，但仍然可能产生幻读。

幻读是指在一个事务中，当多次执行相同的查询时，得到的结果集不一致。它通常发生在一个事务执行范围内有其他事务插入或删除了数据行。在"可重复读"隔离级别下，读取锁只能防止其他事务修改已存在的数据行，而无法防止插入新的数据行。

为了解决幻读问题，可以采取以下方法：

使用更高的隔离级别：将隔离级别提升到"串行化"（Serializable）级别可以完全避免幻读。在"串行化"隔离级别下，事务按顺序执行，每个事务都会获得排他锁（Exclusive Lock），阻止其他事务对数据进行任何修改。但是，这可能会导致并发性能下降，因为事务需要等待其他事务释放锁。
使用行级锁定：某些数据库支持行级锁定（Row-level Locking），可以在对数据行进行修改时获取排他锁，而在读取数据时只获取共享锁。这样可以避免对整个表或范围的数据进行锁定，从而减少锁的冲突和并发性能的降低。
使用乐观并发控制（Optimistic Concurrency Control）：乐观并发控制是一种基于版本的机制，通过在数据行中维护版本号或时间戳来检测并发修改。当一个事务读取数据时，它会记录数据的版本号或时间戳。在提交时，如果检测到数据的版本已经发生变化，事务可以选择重新读取数据并重新执行。这可以避免幻读问题，但需要额外的版本管理机制。
使用范围锁定（Range Locking）：某些数据库支持范围锁定，可以在事务读取数据时锁定一个范围，防止其他事务在该范围内插入或删除数据。这可以有效地解决幻读问题，但需要谨慎使用，以避免锁的冲突和性能下降。