MySQL面试常考知识点与实战技巧总结

最近参加了一次技术面试，其中MySQL相关的问题占了不少比重。面试结束后，我仔细回顾并梳理了这些问题，把答案和思路整理出来，希望能给大家提供一些参考。下面就来详细说说这些问题。

一、MySQL系统表详解

MySQL的系统表主要存放在information_schema数据库里，它们的作用是提供数据库的元数据信息，简单来说，就是关于数据库本身的一些描述性数据。下面介绍几个常见的系统表及其功能：

TABLES表：这个表记录了所有表的相关信息，像表名、使用的存储引擎、表中的大致行数等。通过它，我们能快速了解数据库里都有哪些表。
COLUMNS表：它存储了表中每一列的详细信息，包括列名、数据类型、是否允许为空值等。这对于查看表的结构非常有用。
SCHEMATA表：提供了数据库的基本信息，比如数据库名、字符集设置等。这些信息有助于我们了解数据库的整体配置。
STATISTICS表：用于记录索引的统计信息，例如索引名、对应的列名以及索引的基数等。了解这些信息可以帮助我们优化查询性能。
PARTITIONS表：如果数据库使用了表分区，这个表就会存储相关的分区信息。
KEY_COLUMN_USAGE表：它主要提供主键和外键约束的列信息，方便我们查看表之间的关联关系。
TRIGGERS表：存放了触发器的详细信息，包括触发器的名称、触发事件等。

这些系统表通常是只读的，主要用于查询数据库的结构和状态。比如，执行SELECT * FROM information_schema.TABLES语句，就能查看数据库中所有表的信息。

二、MySQL货币数据类型的选择

在MySQL里记录货币数据时，DECIMAL（或者NUMERIC）数据类型是比较好的选择。为什么呢？主要有两个原因：
一方面，它的精度很高。DECIMAL(M, N)可以明确指定总位数（M）和小数位数（N），这就避免了像FLOAT或DOUBLE这类浮点数可能出现的精度丢失问题。在货币计算中，精度可是非常重要的，差一分钱都不行。
另一方面，它很适合金融场景。一般货币计算都精确到分，也就是两位小数。像DECIMAL(10, 2)，表示总共有10位数字，其中2位是小数，大多数情况下，这样的设置足以满足需求。例如创建一个订单表时，可以这么定义：

CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, price DECIMAL(10, 2) NOT NULL );

相比之下，FLOAT或DOUBLE就不太推荐，因为浮点数在计算过程中可能会产生舍入误差。而INT类型虽然也能存储货币数据，但需要手动换算单位，比如把元换算成分来存储，使用起来不够直观。

三、InnoDB和MyISAM的差异对比

InnoDB和MyISAM是MySQL中常用的两种存储引擎，它们之间存在不少区别：

事务支持：InnoDB支持ACID事务，这意味着它能保证数据的一致性、原子性、隔离性和持久性，在一些对数据完整性要求较高的场景，比如金融系统，就非常适用。而MyISAM不支持事务。
外键支持：InnoDB支持外键约束，能方便地建立表与表之间的关联关系，保证数据的参照完整性。MyISAM则不具备这个功能。
锁机制：InnoDB采用行级锁，在并发操作时，只锁定正在操作的行，对其他行的影响较小，适合高并发写入的场景。MyISAM使用的是表级锁，在操作时会锁定整个表，并发性能相对较差。
崩溃恢复：InnoDB可以通过日志来恢复数据，在数据库发生崩溃后，能保证数据的完整性。MyISAM则没有这个能力。
全文索引：InnoDB在5.6版本之后才开始支持全文索引，而MyISAM原生就支持。
性能表现：InnoDB在写多读少的场景下表现更优，因为它的行级锁和事务支持能更好地处理并发写入操作。MyISAM则在读多写少的场景中效率更高，比如日志系统这种读操作频繁、写操作较少的场景。
存储结构：InnoDB采用聚簇索引，数据和索引是存储在一起的。MyISAM则是将数据和索引分开存储，属于非聚簇索引。

在选择存储引擎时，如果项目对事务和数据一致性要求高，就选择InnoDB；如果是只读或者写操作很少的场景，MyISAM可能会更高效。

四、提升MySQL INSERT性能的方法

想要提高MySQL中INSERT操作的性能，可以从以下几个方面入手：

批量插入数据：使用INSERT INTO table VALUES (), (), ()这种方式，一次性插入多条数据，这样能减少单次插入操作的开销。例如：

INSERT INTO users (name, age) VALUES ('Alice', 25), ('Bob', 30), ('Charlie', 35);

合理处理索引：在插入数据之前，可以使用ALTER TABLE table_name DISABLE KEYS命令禁用索引，插入完成后再重建索引。这样可以避免插入过程中频繁更新索引，提高插入速度。
优化事务处理：把多次插入操作放在一个事务中执行，这样可以减少日志同步的开销，提升整体性能。
调整配置参数：
- 增大innodb_buffer_pool_size的值，这个参数决定了InnoDB存储引擎的缓冲池大小，缓冲池可以缓存更多的数据和索引，提高查询性能。建议设置为物理内存的60%-80%。
- 调整innodb_log_file_size，适当增大这个值可以减少日志切换的频率，提升写入性能，一般建议设置在128M-512M之间。
使用高效导入方式：对于大批量数据的导入，LOAD DATA INFILE的效率要远远高于普通的INSERT操作。
使用分区表：如果表的数据量非常大，可以考虑对表进行分区，将数据分散存储，减轻写操作的压力。

五、InnoDB锁机制解析

（一）行锁与表锁的场景

InnoDB默认使用行级锁，但在某些情况下，会升级为表锁：

当执行LOCK TABLES语句或者进行DDL操作，比如ALTER TABLE时，会使用表锁。
如果查询没有命中索引，导致全表扫描，可能会使用表锁，或者产生大量行锁。
当锁冲突过多时，InnoDB为了提高性能，可能会主动将行锁升级为表锁。

（二）InnoDB的锁类型

InnoDB的锁主要有以下几种类型：

记录锁（Record Lock）：它的作用是锁定具体的某一行记录，防止其他事务对这一行进行修改。
间隙锁（Gap Lock）：用于锁定某个范围内的间隙，目的是防止在这个范围内插入新的数据。
邻键锁（Next-Key Lock）：它是记录锁和间隙锁的组合，在默认的范围查询中会使用。

（三）锁的退化情况

邻键锁退化为间隙锁：当查询范围中没有具体的记录命中时，此时只需要防止在这个范围内插入数据，邻键锁就会退化为间隙锁。
邻键锁退化为记录锁：当查询条件精确匹配唯一索引，比如主键时，只需要锁住这一行数据，邻键锁就会退化为记录锁。例如：

SELECT * FROM users WHERE id = 10 FOR UPDATE; -- 记录锁（主键精确匹配） SELECT * FROM users WHERE id > 5 AND id < 10 FOR UPDATE; -- 邻键锁（范围查询）

六、新建MySQL数据库的参数调整

新创建MySQL数据库后，根据硬件资源和业务需求，建议调整以下参数：

innodb_buffer_pool_size：这是InnoDB缓冲池的大小，建议设置为物理内存的60%-80%。缓冲池用于缓存数据和索引，增大这个值可以提高查询性能。
innodb_log_file_size：表示日志文件的大小，一般设置在128M-512M比较合适，可以提升写入性能。
max_connections：这个参数决定了数据库允许的最大连接数，默认值是151，需要根据实际的并发量进行调整。
tmp_table_size和max_heap_table_size：这两个参数分别控制临时表和内存表的大小，建议从64M开始设置。
innodb_flush_log_at_trx_commit：它有两个常见的值可以设置。设为1（默认值）时，每次事务提交都会把日志刷盘，这样安全性高；设为2时，性能会有所提升，但可能会丢失1秒的数据。
character_set_server和collation_server：用于设置数据库的字符集和排序规则，比如常用的utf8mb4字符集。

在my.cnf配置文件中的调整示例如下：

[mysqld] innodb_buffer_pool_size = 2G innodb_log_file_size = 256M max_connections = 500

七、MySQL CPU或IO压力大的问题定位

（一）CPU压力大的排查方法

检查慢查询：开启slow_query_log，通过分析耗时较长的SQL语句，使用EXPLAIN命令查看其执行计划，找出性能瓶颈。
查看连接数：执行SHOW PROCESSLIST命令，检查当前是否有大量活跃的线程，判断是否因为连接过多导致CPU负载过高。
分析锁冲突：使用SHOW ENGINE INNODB STATUS命令查看是否存在死锁或者锁等待的情况，因为锁冲突也可能导致CPU压力增大。
借助系统工具：利用top或htop等系统工具，确认MySQL进程的CPU占用情况。

（二）IO压力大的排查方法

检查磁盘IO：使用iostat或vmstat等工具查看磁盘的读写情况，判断是否存在磁盘I/O瓶颈。
分析日志写入：检查innodb_flush_log_at_trx_commit的设置，如果频繁刷盘，可能会导致IO负载过高。
优化查询语句：未命中索引的查询会导致大量的物理读操作，检查查询语句，优化索引使用情况。
调整缓冲池大小：如果innodb_buffer_pool_size过小，会导致频繁的换页操作，增加IO负担，可以适当增大这个值。

（三）问题定位流程

一般先执行SHOW FULL PROCESSLIST命令，查看当前正在执行的SQL语句。然后使用EXPLAIN命令优化低效的查询，必要时添加索引。最后检查服务器的资源瓶颈，根据情况调整参数或者升级硬件。

通过对这些MySQL知识点的梳理，希望大家能对MySQL有更深入的理解，在实际开发和面试中都能有所帮助。