MySQL 核心知识：数据类型、存储引擎与索引全解析

最新推荐文章于 2024-12-31 09:23:26 发布

guihong004

最新推荐文章于 2024-12-31 09:23:26 发布

阅读量982

点赞数 10

分类专栏： java面试题文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/guihong004/article/details/144736042

版权

java面试题专栏收录该内容

64 篇文章

订阅专栏

1.MySQL 中 varchar 与 char 的区别？ varchar(30) 中的 30 代表的涵义？

在 MySQL 中，VARCHAR 和 CHAR 都是用于存储字符数据的类型，但它们之间有几个关键的区别：

存储空间:

CHAR 是固定长度的字符类型。当你定义一个 CHAR(n) 列时，MySQL 会分配足够的空间来存储 n 个字符，不论实际插入的数据有多长。如果插入的数据短于 n 个字符，MySQL 通常会在后面用空格填充到指定的长度。
VARCHAR 是可变长度的字符类型。VARCHAR(n) 表示可以存储最多 n 个字符，但它只使用实际所需的空间加上1或2个额外的字节（取决于所需的总长度是否超过255字节）来存储长度信息。

性能:

因为 CHAR 类型的列是固定长度的，所以当表中包含大量 CHAR 类型的列时，可能会导致一些性能上的优势，尤其是在全表扫描的情况下，因为每一行都有固定的宽度，使得读取操作更加快捷。
VARCHAR 类型的列由于长度不固定，在某些情况下可能会有性能上的劣势，特别是在频繁更新的环境中，可能会引起碎片化的问题。

存储内容:

当从 CHAR 类型的列检索数据时，尾部的空格会被去除，除非使用了 CHAR_BINARY 或者设置了 SQL 模式为 PAD_CHAR_TO_FULL_LENGTH。
VARCHAR 类型不会去除尾部的空格。

关于 VARCHAR(30) 中的 30，它表示该字段可以存储的最大字符数是 30。这并不意味着总是占用 30 个字符的空间；而是说该字段能保存的最长字符串为 30 个字符。如果存储的字符串短于 30 个字符，那么只会使用实际需要的空间。例如，如果你插入了一个 10 个字符的字符串，它将只占用 10 个字符的空间加上存储长度所需的额外字节。

2.int(11) 中的 11 代表什么涵义？

在 MySQL 中，INT(11) 的定义中 (11) 并不代表该整数类型的存储空间或取值范围，而是指显示宽度（display width）。这个特性是 MySQL 特有的，并且在很多情况下是一个历史遗留的属性，对实际存储和性能没有影响。

具体来说：

显示宽度：(11) 指定了当使用 ZEROFILL 属性时，MySQL 将用零填充整数左侧，直到达到指定的宽度。例如，如果一个 INT(11) 列有 ZEROFILL 属性，那么数值 42 将被显示为 00000000042。
不带 ZEROFILL 时：如果不使用 ZEROFILL，那么 (11) 实际上没有任何效果。它不会限制列可以存储的最大值或最小值，也不会影响存储大小。MySQL 的 INT 类型总是占用 4 个字节的存储空间，能够存储从 -2147483648 到 2147483647 的值（对于有符号整数），或者从 0 到 4294967295（对于无符号整数）。
因此，在大多数情况下，你可以忽略 (11) 或者其他任何放在 INT 类型后的数字，除非你明确地需要 ZEROFILL 行为。此外，自 MySQL 8.0 起，显示宽度的概念已经被弃用，并且不再推荐使用。

3.为什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢？

SELECT COUNT(*) FROM table 在 InnoDB 表上比 MyISAM 表上慢的原因主要与这两种存储引擎的工作方式和设计哲学有关。

存储结构:

MyISAM: 这种存储引擎在表的元数据中保存了行数，因此当执行 SELECT COUNT(*) FROM table 时，MyISAM 可以直接从这个元数据中读取行数，而不需要扫描整个表。这使得 MyISAM 在这种查询上非常高效。
InnoDB: 相反，InnoDB 是一个事务安全的存储引擎，支持行级锁定、外键约束等功能。为了实现这些特性，InnoDB 使用了一种称为聚簇索引的数据结构，其中行数据是根据主键排序存储的。此外，由于并发控制和事务隔离级别的需求，InnoDB 不会缓存确切的行数。这意味着当你执行 SELECT COUNT(*) FROM table 时，InnoDB 必须实际扫描所有的非删除标记的数据行来计算总数，这显然比从元数据中读取要慢得多。

事务和一致性:

InnoDB 设计用来保证高并发环境下的数据一致性和完整性，它实现了 MVCC（多版本并发控制），这意味着每个事务都能看到它开始时的一致性视图。当进行 COUNT(*) 操作时，InnoDB 需要确保结果符合当前事务的快照，这可能需要更多的工作，比如忽略其他未提交事务的影响或考虑已删除但尚未提交的删除操作。

优化建议:

如果你需要频繁地进行 COUNT(*) 查询，并且对结果的精确度要求不是非常高，可以考虑使用近似值或者维护一个单独的计数器表来记录行数的变化。
对于 InnoDB 表，如果你确实需要快速获取行数估计，MySQL 5.7 及以上版本提供了 INFORMATION_SCHEMA.TABLES 表中的 TABLE_ROWS 列，它可以给出一个大致的行数估计，但是请注意这个数值并不是总是准确的，尤其是在高并发写入的情况下。

综上所述，由于设计上的差异，特别是对于事务安全和支持并发的需求，InnoDB 在处理 SELECT COUNT(*) FROM table 查询时通常会比 MyISAM 慢。

4.说说 InnoDB 与 MyISAM 有什么区别？

InnoDB 和 MyISAM 是 MySQL 数据库中两种常用的存储引擎，它们在设计、性能特点和适用场景上有显著的区别。以下是 InnoDB 和 MyISAM 之间的一些主要区别：

事务支持

InnoDB: 支持事务处理（ACID），提供了对数据库操作的原子性、一致性、隔离性和持久性的保证。这意味着可以使用 BEGIN, COMMIT 和 ROLLBACK 来管理事务。
MyISAM: 不支持事务处理。它提供的是简单的表级锁定机制，适用于那些不需要复杂事务管理的应用。

锁定机制

InnoDB: 支持行级锁定（Row-level locking），这意味着并发性能更好，尤其是在写密集型应用中，因为不同行上的多个写入操作不会互相阻塞。
MyISAM: 使用表级锁定（Table-level locking），任何对表的写入操作都会锁住整个表，阻止其他会话对该表进行读或写的操作，这可能会影响高并发环境下的性能。

外键约束

InnoDB: 支持外键约束（Foreign Key Constraints），有助于维护数据完整性和参照完整性。
MyISAM: 不支持外键约束，因此在涉及多表关系时需要应用程序层面来确保数据的一致性。

索引类型

InnoDB: 使用聚簇索引（Clustered Index），将主键和数据行存储在一起，对于主键查询非常高效；同时也支持非聚簇索引（Secondary Index）。
MyISAM: 使用非聚簇索引，索引和数据是分开存储的，虽然这也使得 MyISAM 在某些情况下可以更快速地执行全表扫描。

存储结构

InnoDB: 表的数据和索引通常存储在一个共享的表空间（tablespace）中，也可以配置为每个表有自己的文件（通过 innodb_file_per_table 配置选项）。
MyISAM: 每个表都有自己独立的数据 (.MYD) 和索引 (.MYI) 文件。

其他特性

InnoDB: 提供了崩溃恢复能力、自动修复功能，并且对大容量数据有更好的优化。
MyISAM: 更适合于以读取为主的工作负载，特别是在大量读操作而很少有写操作的情况下表现良好。此外，MyISAM 对全文索引的支持优于早期版本的 InnoDB（MySQL 5.6 及之前）。

性能与内存使用

InnoDB: 因为其事务安全特性和复杂的内部机制，可能会比 MyISAM 占用更多的系统资源，如 CPU 和内存。
MyISAM: 相对来说更轻量级，在某些特定条件下（例如只读或很少更新的表）可能具有更好的性能。

总的来说，选择哪种存储引擎取决于具体的应用需求、数据访问模式以及性能考虑。现代应用更多地倾向于选择 InnoDB，因为它提供了更高级别的数据完整性和可靠性保障。

5.MySQL 索引类型有哪些？

MySQL 支持多种索引类型，每种类型都有其特定的用途和性能特点。以下是 MySQL 中常见的索引类型：

B-Tree 索引

这是 MySQL 中最常用的索引类型，默认情况下，当创建索引时（例如通过 CREATE INDEX 或定义表结构时使用 KEY 或 INDEX），都会创建 B-Tree 索引。
B-Tree 索引适用于大多数类型的查询，包括范围查询、等值匹配和排序操作。InnoDB 和 MyISAM 存储引擎都支持这种索引类型。

哈希 (Hash) 索引