主页 > 电脑硬件  > 

Mysql100道高频面试题

Mysql100道高频面试题
一、基础概念 1. 什么是数据库?DBMS的作用是什么?

数据库:是按照数据结构来组织、存储和管理数据的仓库。它允许用户高效地访问和管理数据。

DBMS(数据库管理系统):是一种软件系统,用于创建和管理数据库。它的作用包括:

数据定义(DDL)

数据操作(DML)

数据查询(DQL)

数据控制(安全性、完整性、并发性等)


2. SQL的全称是什么?它有哪些分类?

SQL全称:Structured Query Language(结构化查询语言)。

分类:

DDL(Data Definition Language):用于定义或修改数据库对象(如表、索引等),例如 CREATE、ALTER、DROP。

DML(Data Manipulation Language):用于操作数据库中的数据,例如 INSERT、UPDATE、DELETE。

DQL(Data Query Language):用于查询数据库中的数据,例如 SELECT。

DCL(Data Control Language):用于控制权限和访问,例如 GRANT、REVOKE。


3. MySQL的默认端口号是多少?如何修改?

默认端口号:3306。

修改方法:

编辑MySQL配置文件 my f 或 my.ini。

找到 [mysqld] 部分,修改或添加 port=新端口号。

重启MySQL服务以使更改生效。


4. MySQL中CHAR和VARCHAR的区别? 特性CHARVARCHAR存储空间固定长度可变长度性能较快(固定长度便于计算)较慢(需要额外存储长度信息)使用场景短且固定长度的数据长度不确定的数据
5. DATETIME和TIMESTAMP类型的区别? 特性DATETIMETIMESTAMP存储范围1000-01-01 00:00:00 到 9999-12-31 23:59:591970-01-01 00:00:01 到 2038-01-19 03:14:07存储大小8字节4字节是否受时区影响不受影响受影响
6. 如何创建一个包含自增主键的表? CREATE TABLE example_table (   id INT AUTO_INCREMENT PRIMARY KEY,   name VARCHAR(50) NOT NULL );
7. TRUNCATE TABLE和DELETE的区别? 特性TRUNCATE TABLEDELETE操作方式清空整个表并重置自增计数器删除指定条件的记录事务支持不支持事务回滚支持事务回滚触发器触发不会触发触发器会触发触发器执行速度快慢
8. 什么是主键(Primary Key)?什么是外键(Foreign Key)?

主键(Primary Key):唯一标识表中每一行数据的字段或字段组合,不允许为空且值必须唯一。

外键(Foreign Key):用于建立和加强两个表之间的关系,确保引用完整性。外键通常指向另一个表的主键。


9. 如何查看当前数据库中的所有表? SHOW TABLES;
10. WHERE和HAVING的区别? 特性WHEREHAVING使用场景过滤行数据(在聚合前)过滤聚合后的结果适用对象单个列聚合函数或分组列查询阶段在GROUP BY之前执行在GROUP BY之后执行 二、SQL语法与操作 1. 写出查询表中前10条记录的SQL语句。 SELECT * FROM table_name LIMIT 10;
2. 如何对查询结果去重(DISTINCT的用法)?

使用 DISTINCT 关键字去除重复值,仅保留唯一值。

SELECT DISTINCT column_name FROM table_name;

示例:查询所有唯一的部门名称。

SELECT DISTINCT department FROM employees;
3. INNER JOIN和LEFT JOIN的区别? 特性INNER JOINLEFT JOIN返回数据只返回两个表中匹配的记录返回左表的所有记录,右表无匹配则为NULL数据完整性不包含不匹配的记录包含左表的所有记录

示例:

-- INNER JOIN 示例 SELECT a.id, a.name, b.department FROM employees a INNER JOIN departments b ON a.department_id = b.id; ​ -- LEFT JOIN 示例 SELECT a.id, a.name, b.department FROM employees a LEFT JOIN departments b ON a.department_id = b.id;
4. 如何实现分页查询?(LIMIT和OFFSET)

使用 LIMIT 和 OFFSET 实现分页查询。

SELECT * FROM table_name LIMIT 每页记录数 OFFSET 起始偏移量;

示例:查询第2页的数据,每页10条记录。

SELECT * FROM table_name LIMIT 10 OFFSET 10;
5. 如何将两个查询结果合并(UNION和UNION ALL)?

UNION:合并两个查询结果并去重。

UNION ALL:合并两个查询结果,不去重。

示例:

-- UNION 示例 SELECT name FROM table1 UNION SELECT name FROM table2; -- UNION ALL 示例 SELECT name FROM table1 UNION ALL SELECT name FROM table2;
6. 如何创建索引?举例说明。

使用 CREATE INDEX 创建索引。

CREATE INDEX index_name ON table_name (column_name);

示例:为 employees 表的 name 列创建索引。

CREATE INDEX idx_employee_name ON employees (name);
7. 如何修改表结构(添加/删除列)?

添加列:

ALTER TABLE table_name ADD COLUMN column_name 数据类型;

示例:为 employees 表添加 age 列。

ALTER TABLE employees ADD COLUMN age INT;

删除列:

ALTER TABLE table_name DROP COLUMN column_name;

示例:删除 employees 表的 age 列。

ALTER TABLE employees DROP COLUMN age;
8. 什么是子查询?举例说明。

子查询:嵌套在另一个查询中的查询,通常用于提供条件或过滤数据。

SELECT column_name FROM table_name WHERE column_name IN (子查询);

示例:查询工资高于平均工资的员工。

SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
9. 如何批量插入数据?

使用 INSERT INTO 插入多行数据。

INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4), ...;

示例:向 employees 表批量插入两条记录。

INSERT INTO employees (name, department_id) VALUES ('Alice', 1), ('Bob', 2);
10. BETWEEN和IN的用法区别?

BETWEEN:用于指定一个范围(包括边界值)。

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

示例:查询年龄在20到30之间的员工。

SELECT * FROM employees WHERE age BETWEEN 20 AND 30;

IN:用于指定多个离散值。

SELECT * FROM table_name WHERE column_name IN (value1, value2, value3);

示例:查询部门ID为1、2或3的员工。

SELECT * FROM employees WHERE department_id IN (1, 2, 3); 三、索引与性能优化
1. 什么是索引?为什么需要索引?

索引:是一种特殊的数据结构(如B-Tree),用于快速定位数据,类似于书的目录。

为什么需要索引:

提高查询效率,减少扫描全表的时间。

加速排序和分组操作。

支持唯一性约束。


2. 索引的类型有哪些?(B-Tree、哈希、全文索引) 类型描述B-Tree索引最常用的索引类型,支持范围查询和排序,适合大多数场景。哈希索引使用哈希函数存储键值对,查询速度非常快,但不支持范围查询或部分匹配查询。全文索引专门用于全文搜索,支持复杂的文本匹配(如MySQL中的 FULLTEXT)。
3. 索引的优缺点是什么? 优点缺点提高查询效率增加插入、更新和删除操作的开销减少磁盘I/O占用额外的存储空间支持排序和分组在某些情况下可能导致查询计划变差
4. 什么情况下索引会失效?

索引失效的情况:

使用了函数或表达式操作索引列,例如 WHERE YEAR(date_column) = 2023。

数据类型不匹配,例如字符串与数字比较。

使用了模糊查询前缀通配符,例如 WHERE column LIKE '%value'。

查询条件中使用了 OR 且部分列未被索引。

索引选择性较低(即重复值过多)。


5. 如何查看SQL语句的执行计划(EXPLAIN)?

使用 EXPLAIN 查看SQL语句的执行计划。

EXPLAIN SELECT * FROM table_name WHERE condition;

示例:查看查询的执行计划。

EXPLAIN SELECT * FROM employees WHERE department_id = 1;

关键字段解释:

id:查询的序列号。

select_type:查询类型(简单查询、子查询等)。

table:操作的表名。

type:访问类型(ALL 表示全表扫描,index 表示索引扫描,ref 表示索引查找)。

key:使用的索引名称。

rows:扫描的行数。

Extra:附加信息(如是否使用临时表、文件排序等)。


6. 什么是覆盖索引(Covering Index)?

覆盖索引:当查询的所有列都在索引中时,无需回表查询数据,直接从索引中获取结果。

优点:避免回表操作,显著提高查询性能。

示例:假设有一个复合索引 (department_id, name),以下查询可以使用覆盖索引。

SELECT name FROM employees WHERE department_id = 1;
7. 什么是聚簇索引和非聚簇索引? 类型描述聚簇索引数据行按索引顺序存储在物理磁盘上,每个表只能有一个聚簇索引(通常是主键)。非聚簇索引索引存储的是指向实际数据行的指针,数据行的存储顺序与索引无关。
8. 如何优化慢查询?

优化方法:

分析慢查询日志:启用 slow_query_log,找出执行时间较长的SQL语句。

添加索引:为频繁查询的列创建索引。

优化查询语句:避免不必要的 SELECT *,尽量只查询需要的列。

减少锁竞争:优化事务处理,减少锁等待时间。

分区表:对大表进行分区,减少单次查询的数据量。

缓存结果:使用查询缓存或应用层缓存(如Redis)。


9. 什么是索引下推(Index Condition Pushdown)?

索引下推:MySQL的一种优化技术,在存储引擎层面过滤掉不符合条件的记录,减少回表次数。

适用场景:当查询条件中包含非索引列时,存储引擎可以直接利用索引过滤部分数据,而不是全部回表。

示例:假设有一个复合索引 (a, b),查询条件为 WHERE a = 1 AND b LIKE 'val%',索引下推允许存储引擎直接过滤 b LIKE 'val%' 的记录。


10. 什么是回表查询?

回表查询:当查询的列不在索引中时,需要通过索引找到主键值,再通过主键到聚簇索引中查找完整数据行。

原因:索引通常只包含部分列,而查询可能需要更多列。

优化方法:尽量使用覆盖索引,减少回表操作。

示例:假设有一个复合索引 (department_id, name),以下查询会导致回表。

SELECT id, name, salary FROM employees WHERE department_id = 1;

因为 salary 不在索引中,必须回表查询完整数据行。

四、事务与锁
1. 什么是事务?事务的ACID特性是什么?

事务:一组逻辑操作单元,这些操作要么全部成功执行,要么全部不执行。

ACID特性:

Atomicity(原子性):事务是一个不可分割的工作单位,要么全部完成,要么全部失败。

Consistency(一致性):事务执行前后,数据库必须保持一致状态。

Isolation(隔离性):多个事务并发执行时,彼此隔离,互不干扰。

Durability(持久性):事务一旦提交,其结果是永久性的,即使系统崩溃也不会丢失。


2. MySQL的默认事务隔离级别是什么?

MySQL默认隔离级别:REPEATABLE READ(可重复读)。


3. 事务隔离级别有哪些?分别解决什么问题? 隔离级别解决的问题可能出现的问题READ UNCOMMITTED无脏读、不可重复读、幻读READ COMMITTED解决脏读不可重复读、幻读REPEATABLE READ解决脏读和不可重复读幻读SERIALIZABLE解决所有并发问题无
4. 什么是脏读、不可重复读、幻读?

脏读:一个事务读取了另一个未提交事务的数据。

不可重复读:一个事务在两次查询中读取到的数据不一致(另一个事务修改并提交了数据)。

幻读:一个事务在两次查询中发现多出了某些记录(另一个事务插入了新记录)。


5. 如何设置事务的隔离级别?

使用 SET TRANSACTION ISOLATION LEVEL 设置当前会话或全局的隔离级别。

-- 设置当前会话的隔离级别 SET TRANSACTION ISOLATION LEVEL 隔离级别; -- 设置全局隔离级别 SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;

示例:将隔离级别设置为 READ COMMITTED。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
6. 什么是共享锁(S锁)和排他锁(X锁)?

共享锁(S锁):允许多个事务同时读取同一资源,但不允许写入。

排他锁(X锁):独占锁,禁止其他事务读取或写入该资源。


7. 什么是死锁?如何避免死锁?

死锁:两个或多个事务互相等待对方释放资源,导致所有事务都无法继续执行。

避免死锁的方法:

尽量减少事务的持有时间。

按固定的顺序加锁。

使用较低的隔离级别。

设置超时机制(如 innodb_lock_wait_timeout)。


8. 什么是行级锁和表级锁?InnoDB支持哪种?

行级锁:锁定表中的特定行,允许多个事务同时操作不同的行。

表级锁:锁定整个表,阻止其他事务对该表的任何操作。

InnoDB支持:行级锁(默认使用B-Tree索引实现)。


9. SELECT ... FOR UPDATE的作用是什么?

作用:对查询的行加排他锁(X锁),防止其他事务修改或删除这些行,直到当前事务提交或回滚。

适用场景:需要确保数据一致性时,例如银行转账。

示例:

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
10. 什么是乐观锁和悲观锁?

乐观锁:假设冲突很少发生,因此不对数据加锁,而是通过版本号或时间戳检查数据是否被修改。如果发生冲突,则重新尝试操作。

优点:性能高,适合低并发场景。

缺点:可能发生多次重试。

悲观锁:假设冲突经常发生,因此在操作前就对数据加锁,防止其他事务修改。

优点:安全性高,适合高并发场景。

缺点:可能导致性能下降或死锁。

示例:

乐观锁:通过版本号实现。

UPDATE accounts SET balance = balance - 100, version = version + 1 WHERE id = 1 AND version = 5;

悲观锁:使用 SELECT ... FOR UPDATE 实现。

五、存储引擎
1. InnoDB和MyISAM的区别? 特性InnoDBMyISAM事务支持支持事务不支持事务锁机制行级锁表级锁崩溃恢复支持崩溃恢复不支持崩溃恢复外键支持支持外键不支持外键存储结构数据和索引存储在一起数据和索引分开存储适用场景高并发、需要事务的场景读密集型、简单查询场景
2. InnoDB为什么推荐使用自增主键?

原因:

性能优化:InnoDB使用聚簇索引(Clustered Index),数据按主键顺序存储。自增主键能够保证插入时的顺序性,避免页分裂。

高效写入:自增主键减少了随机插入带来的性能开销。

唯一性:确保每行数据都有唯一的标识符。


3. MyISAM适合哪些场景?

适合场景:

以读操作为主的场景。

数据量大但对事务无要求的场景。

全文索引搜索(MyISAM支持全文索引,而InnoDB在MySQL 5.6之前不支持)。


4. 如何查看和修改表的存储引擎?

查看存储引擎:

SHOW TABLE STATUS WHERE Name = 'table_name';

或:

SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_NAME = 'table_name';

修改存储引擎:

ALTER TABLE table_name ENGINE=新引擎;

示例:将表的存储引擎改为InnoDB。

ALTER TABLE my_table ENGINE=InnoDB;
5. InnoDB的行锁是如何实现的?

实现方式:

InnoDB通过B+树索引来实现行锁。

行锁分为两种:

记录锁(Record Lock):锁定索引中的单个记录。

间隙锁(Gap Lock):锁定索引中记录之间的间隙,防止幻读。

行锁依赖于索引,如果查询条件未命中索引,则会升级为表锁。



六、表设计与范式
1. 什么是数据库的三大范式?

第一范式(1NF):每个字段都是不可再分的原子值。

第二范式(2NF):在满足1NF的基础上,消除非主属性对候选键的部分函数依赖。

第三范式(3NF):在满足2NF的基础上,消除非主属性对候选键的传递函数依赖。


2. 什么是反范式化?什么时候需要反范式化?

反范式化:为了提高查询效率,故意打破范式规则,增加冗余数据或重复存储某些字段。

适用场景:

需要频繁进行复杂联表查询的场景。

对读性能要求高而对写性能要求低的场景。

数据仓库或报表系统中。


3. 如何设计一对多、多对多关系表?

一对多关系:

在“多”的一方添加外键指向“一”的一方。

示例:部门和员工(一个部门有多个员工)。

CREATE TABLE departments ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), department_id INT, FOREIGN KEY (department_id) REFERENCES departments(id) );

多对多关系:

创建一个中间表来存储关联关系。

示例:学生和课程(一个学生可以选多门课程,一门课程可以被多个学生选)。

CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE courses ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE student_course ( student_id INT, course_id INT, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES students(id), FOREIGN KEY (course_id) REFERENCES courses(id) );
4. 什么是雪花模型和星型模型?

星型模型:

中心是一个事实表,周围是维度表。

简单直观,适合OLAP分析。

雪花模型:

在星型模型的基础上进一步规范化,将维度表拆分为更小的子维度表。

更节省存储空间,但查询复杂度较高。


5. 如何设计一个支持软删除(逻辑删除)的表?

方法:添加一个标记字段(如 is_deleted 或 deleted_at)来表示是否已删除。

示例:

CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), is_deleted TINYINT DEFAULT 0 COMMENT '0:未删除, 1:已删除', deleted_at DATETIME NULL COMMENT '删除时间' ); -- 查询未删除的用户 SELECT * FROM users WHERE is_deleted = 0; -- 删除用户(逻辑删除) UPDATE users SET is_deleted = 1, deleted_at = NOW() WHERE id = 1; 七、高级功能
1. 什么是存储过程?优缺点是什么?

存储过程:是一组预编译的SQL语句,存储在数据库中,可以通过调用名称执行。

优点:

提高性能(减少客户端与服务器之间的通信)。

增强代码复用性。

提高安全性(通过权限控制访问)。

缺点:

调试困难。

可移植性差(不同数据库的存储过程语法可能不同)。

复杂逻辑难以维护。


2. 什么是触发器?举例说明。

触发器:是一种特殊的存储过程,当特定事件(如 INSERT、UPDATE 或 DELETE)发生时自动执行。

示例:在插入新记录时自动更新统计表。

CREATE TRIGGER after_insert_employee AFTER INSERT ON employees FOR EACH ROW BEGIN UPDATE department_stats SET employee_count = employee_count + 1 WHERE department_id = NEW.department_id; END;
3. 什么是视图?视图和表的区别?

视图:是一个虚拟表,基于SQL查询的结果集定义。视图不存储实际数据,而是从基础表中动态生成。

视图与表的区别:

特性视图表数据存储不存储实际数据存储实际数据更新能力部分视图可更新,复杂视图不可更新可更新使用场景简化查询、保护敏感数据存储原始数据
4. 如何实现数据库的读写分离?

方法:

主库负责写操作,从库负责读操作。

使用主从复制技术同步数据。

在应用层或中间件(如ProxySQL、MaxScale)中实现读写分离。

示例:配置MySQL主从复制后,在应用中指定不同的连接地址。

// 写操作连接主库 $writeConnection = new PDO('mysql:host=master_host;dbname=test', 'user', 'password'); // 读操作连接从库 $readConnection = new PDO('mysql:host=slave_host;dbname=test', 'user', 'password');
5. 什么是分区表?如何按范围分区?

分区表:将一个大表的数据分成多个小块存储,每个块称为一个分区。

按范围分区:根据某个字段的值范围划分分区。

示例:

CREATE TABLE sales ( id INT, sale_date DATE, amount DECIMAL(10, 2) ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023), PARTITION pfuture VALUES LESS THAN MAXVALUE );

八、备份与恢复
1. 逻辑备份和物理备份的区别? 特性逻辑备份物理备份备份内容SQL语句(建表语句、插入语句等)数据文件(如.ibd、.frm等)备份速度较慢较快恢复速度较慢较快可移植性高低使用工具mysqldumpxtrabackup、直接复制文件
2. 如何用mysqldump备份数据库?

备份整个数据库:

mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql

备份多个数据库:

mysqldump -u用户名 -p密码 --databases 数据库1 数据库2 > 备份文件.sql

备份所有数据库:

mysqldump -u用户名 -p密码 --all-databases > 备份文件.sql
3. 什么是Binlog?它的作用是什么?

Binlog:二进制日志,记录了数据库的所有修改操作(如 INSERT、UPDATE、DELETE)。

作用:

数据恢复:通过重放Binlog恢复误删除或损坏的数据。

主从复制:主库将Binlog发送给从库,从库重放日志以保持数据一致性。

审计:记录所有修改操作,便于追踪问题。


4. 如何恢复误删除的数据?

方法:

使用最近的全量备份文件恢复到备份时间点。

使用Binlog恢复从备份时间点到误删除之前的操作。

示例:通过Binlog恢复数据。

mysqlbinlog binlog_file | mysql -u用户名 -p密码 数据库名
5. 什么是主从复制?如何配置?

主从复制:将主库的数据实时同步到从库,用于读写分离、高可用性和灾难恢复。

配置步骤:

主库配置:

开启Binlog:在my f中设置log-bin=mysql-bin。

设置服务器ID:server-id=1。

创建用于复制的用户并授权。

从库配置:

设置服务器ID:server-id=2。

执行CHANGE MASTER TO命令,指定主库信息。

启动复制:START SLAVE;。

示例:从库配置主库信息。

CHANGE MASTER TO MASTER_HOST='主库IP', MASTER_USER='replication_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1234; 九、安全与权限
1. 如何创建用户并授权?

创建用户:

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

授权:

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

示例:创建用户并授予对特定数据库的查询权限。

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password123'; GRANT SELECT ON mydb.* TO 'testuser'@'localhost';
2. GRANT和REVOKE的作用是什么?

GRANT:用于授予用户权限。

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

REVOKE:用于撤销用户权限。

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
3. 如何防止SQL注入攻击?

方法:

使用预处理语句(Prepared Statement)。

对用户输入进行严格校验和转义。

避免在SQL语句中直接拼接字符串。


4. 预处理语句(Prepared Statement)如何防止SQL注入?

预处理语句:将SQL语句和参数分开处理,避免恶意输入被解析为SQL代码。

示例(MySQLi扩展):

$stmt = $mysqli->prepare("SELECT * FROM users WHERE username = ? AND password = ?"); $stmt->bind_param("ss", $username, $password); $stmt->execute();
5. 如何限制用户的登录IP?

在创建用户时指定允许登录的主机地址。

CREATE USER '用户名'@'指定IP' IDENTIFIED BY '密码';

示例:只允许从192.168.1.100登录。

CREATE USER 'testuser'@'192.168.1.100' IDENTIFIED BY 'password123';

十、性能调优与监控
1. 如何开启慢查询日志?

步骤:

编辑MySQL配置文件my f,添加或修改以下内容:

slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow-query.log long_query_time = 2 # 设置慢查询阈值(秒)

重启MySQL服务以使更改生效。


2. SHOW PROCESSLIST的作用是什么?

作用:显示当前MySQL服务器上的线程信息,包括每个线程的状态和执行的SQL语句。

示例:

SHOW PROCESSLIST;

常用字段:

Id:线程ID。

User:用户名。

Host:客户端IP。

db:当前使用的数据库。

Command:线程执行的操作类型。

Time:线程运行时间。

State:线程状态。

Info:执行的SQL语句。


3. 如何监控MySQL的QPS和TPS?

QPS(Queries Per Second):每秒查询数。

TPS(Transactions Per Second):每秒事务数。

计算公式:

QPS = (Com_select + Com_insert + Com_update + Com_delete) / 时间间隔

TPS = (Com_commit + Com_rollback) / 时间间隔

示例:

SHOW GLOBAL STATUS LIKE 'Com_%';
4. 什么是连接池?为什么需要连接池?

连接池:一组预先创建好的数据库连接,供应用程序重复使用,减少频繁创建和销毁连接的开销。

优点:

提高性能:减少连接建立和断开的时间。

控制资源:限制最大连接数,防止资源耗尽。

简化管理:统一管理连接生命周期。


5. 如何优化大表查询?

优化方法:

添加索引:为查询条件中的列创建合适的索引。

分区表:将大表按某些字段分区,减少扫描的数据量。

分页查询:通过LIMIT和OFFSET实现分页查询。

延迟加载:仅查询必要的列,避免SELECT *。

缓存结果:对于不常变化的数据,使用应用层缓存(如Redis)。

水平拆分:将大表拆分为多个小表,分散数据压力。

十一、JDBC与Java集成
1. JDBC连接MySQL的步骤是什么?

步骤:

加载驱动程序:通过Class.forName()加载MySQL驱动。

Class.forName("com.mysql.cj.jdbc.Driver");

建立连接:使用DriverManager.getConnection()方法连接数据库。

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname", "username", "password");

执行SQL语句:创建Statement或PreparedStatement对象,执行查询或更新操作。

处理结果:如果执行的是查询操作,使用ResultSet处理返回的结果。

关闭资源:关闭ResultSet、Statement和Connection。


2. Statement和PreparedStatement的区别? 特性StatementPreparedStatementSQL注入安全性不安全(容易受到SQL注入攻击)安全(防止SQL注入)性能较低(每次都需要编译SQL)较高(预编译SQL)参数化支持不支持支持
3. 如何通过JDBC实现事务管理?

步骤:

禁用自动提交模式:conn.setAutoCommit(false);

执行多个SQL操作。

如果所有操作成功,提交事务:conn mit();

如果发生异常,回滚事务:conn.rollback();

最后恢复自动提交模式:conn.setAutoCommit(true);

示例:

try { conn.setAutoCommit(false); // 执行SQL操作 stmt.executeUpdate("INSERT INTO table_name ..."); stmt.executeUpdate("UPDATE table_name ..."); conn mit(); } catch (SQLException e) { conn.rollback(); } finally { conn.setAutoCommit(true); }
4. 什么是数据库连接泄漏?如何避免?

连接泄漏:指应用程序未正确关闭数据库连接,导致连接被占用而无法释放。

避免方法:

确保在finally块中关闭所有资源(如ResultSet、Statement、Connection)。

使用连接池管理数据库连接。

使用try-with-resources语法(Java 7及以上)自动关闭资源。


5. 常用的JDBC连接池有哪些?(如HikariCP、Druid)

常用连接池:

HikariCP:高性能、轻量级的连接池,适合大多数场景。

Druid:阿里巴巴开源的连接池,功能丰富,支持监控和统计。

C3P0:老牌连接池,稳定但性能稍逊。

DBCP:Apache提供的连接池,但性能不如HikariCP和Druid。



十二、实战应用题
1. 设计一个用户表(包含字段:用户ID、用户名、注册时间、最后登录时间)。 CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, register_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, last_login_time DATETIME NULL );
2. 查询每个部门薪资最高的员工(表:员工ID、部门ID、薪资)。 SELECT department_id, employee_id, salary FROM employees e1 WHERE salary = ( SELECT MAX(salary) FROM employees e2 WHERE e1.department_id = e2.department_id );
3. 如何实现乐观锁?(如版本号机制)

实现方式:

在表中添加一个version字段。

更新数据时,检查当前版本号是否与预期一致。如果一致,则更新数据并将版本号加1;否则,抛出异常。

示例:

UPDATE employees SET salary = 8000, version = version + 1 WHERE employee_id = 1 AND version = 5;
4. 如何统计某张表的总行数?COUNT(*)和COUNT(1)的区别?

统计总行数:

SELECT COUNT(*) FROM table_name;

区别:

COUNT(*):统计所有行数,包括NULL值。

COUNT(1):等价于COUNT(*),通常用于优化器的性能测试。

结论:两者在现代数据库中几乎没有性能差异,推荐使用COUNT(*)以提高可读性。


5. 如何删除重复数据(保留一条)?

方法:使用临时表或子查询删除重复数据。

示例:删除employees表中重复的name,保留一条。

DELETE FROM employees WHERE id NOT IN ( SELECT MIN(id) FROM employees GROUP BY name ); 十三、场景分析题
1. 某查询突然变慢,可能的原因是什么?

可能原因:

索引失效:查询条件变化导致索引无法使用。

锁等待:高并发场景下事务锁导致阻塞。

表数据增长:数据量增大导致全表扫描耗时增加。

慢查询日志未优化:复杂查询未优化或缺少索引。

硬件资源不足:CPU、内存或磁盘I/O瓶颈。

主从同步延迟:读取从库时数据未及时更新。


2. 高并发场景下如何避免超卖问题?

方法:

库存预扣:下单时先锁定库存,减少超卖风险。

分布式锁:使用Redis等工具实现分布式锁,确保同一商品的库存操作互斥。

乐观锁:通过版本号或库存字段进行CAS(Compare And Swap)操作。

队列限流:将请求放入消息队列处理,避免瞬间流量过大。


3. 如何设计一个点赞功能的数据库表?

设计方案:

用户表:存储用户信息。

CREATE TABLE users ( user_id BIGINT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE );

点赞表:记录用户对目标对象的点赞关系。

CREATE TABLE likes ( like_id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, -- 点赞用户ID target_id BIGINT NOT NULL, -- 被点赞对象ID(如文章ID) target_type VARCHAR(20) NOT NULL, -- 被点赞对象类型(如"article"、"comment") created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE (user_id, target_id, target_type) );
4. 订单表数据量过大,如何优化?

优化方法:

分区表:按时间范围或订单状态分区,减少单次查询的数据量。

水平拆分:将订单表按某些字段(如用户ID、地区)拆分为多个子表。

归档旧数据:将历史订单迁移到归档表或冷存储中。

索引优化:为常用查询字段添加合适的索引。

缓存热点数据:使用Redis缓存高频访问的订单信息。


5. 主从同步延迟导致数据不一致,如何解决?

解决方案:

优化SQL:减少大事务和复杂查询,降低延迟。

半同步复制:启用半同步复制,确保主库写入后从库立即同步。

只读从库:避免在从库上执行写操作,防止冲突。

延迟复制:设置从库延迟一段时间同步,便于恢复误操作。

监控延迟:定期检查主从延迟情况,及时发现问题。



十四、进阶问题
1. 什么是MVCC(多版本并发控制)?

定义:MVCC是一种并发控制机制,允许事务读取数据的“快照”,而无需加锁。

工作原理:

每个事务都有一个唯一的事务ID。

数据行包含多个版本,每个版本标记创建和删除的事务ID。

事务根据自己的ID判断是否能看到某个版本的数据。

优点:

提高并发性能,减少锁冲突。

支持可重复读隔离级别。


2. InnoDB的Redo Log和Undo Log的作用?

Redo Log:

作用:记录事务对数据页的修改,用于崩溃恢复。

机制:事务提交前,先将修改写入Redo Log,确保数据持久化。

Undo Log:

作用:记录事务修改前的数据版本,用于回滚和MVCC。

机制:事务修改数据时,生成旧版本数据存入Undo Log,供其他事务读取。


3. 什么是间隙锁(Gap Lock)?

定义:间隙锁锁定索引中的间隙,防止其他事务插入新记录。

作用:

防止幻读(Phantom Read)。

在REPEATABLE READ隔离级别下自动启用。

示例:

SELECT * FROM employees WHERE age BETWEEN 20 AND 30 FOR UPDATE;

上述查询会锁定age在20到30之间的所有间隙,防止其他事务插入符合条件的新记录。


4. 什么是覆盖索引(Covering Index)?

定义:当查询的所有列都在索引中时,MySQL可以直接从索引中获取结果,而无需回表查询数据。

优点:减少IO操作,提高查询性能。

示例:

CREATE INDEX idx_name_salary ON employees (name, salary); SELECT name, salary FROM employees WHERE name = 'Alice';

上述查询可以完全依赖索引idx_name_salary,无需回表。


5. 如何实现分布式ID生成?(如雪花算法)

雪花算法(Snowflake):

原理:生成64位整数ID,结构如下:

1位:符号位(固定为0)。

41位:时间戳(毫秒级)。

10位:机器ID(区分不同服务器)。

12位:序列号(每毫秒内生成的序号)。

优点:

唯一性:结合时间戳和机器ID保证全局唯一。

高性能:无锁操作,支持高并发。

实现方式:

使用开源库(如Twitter Snowflake)。

自定义实现,分配机器ID并管理序列号。

十五、开放性问题
1. 你如何设计一个数据库的索引策略?

设计原则:

分析查询需求:根据常用查询条件选择合适的字段创建索引。

覆盖索引:尽量使用覆盖索引减少回表操作。

避免过度索引:过多索引会增加写操作的开销,需权衡读写性能。

复合索引:优先考虑使用复合索引(按查询频率和顺序排列字段)。

定期优化:通过EXPLAIN分析查询计划,删除无用索引。

唯一性约束:为需要保证唯一性的字段创建唯一索引。


2. 遇到过哪些MySQL性能问题?如何解决的?

常见问题及解决方法:

慢查询:通过slow_query_log定位慢SQL,优化查询语句或添加索引。

锁等待:检查死锁日志,调整事务隔离级别或优化SQL逻辑。

主从延迟:优化大事务,启用半同步复制或调整Binlog格式。

高内存使用:调整缓冲区大小(如innodb_buffer_pool_size),释放无用连接。

IO瓶颈:使用SSD硬盘,优化Redo Log配置(如innodb_flush_log_at_trx_commit)。


3. 如何保证数据库的高可用性?

方法:

主从复制:实现数据冗余,确保主库故障时可以从库接管。

双主复制:两个主库互为主备,提高可用性。

分布式数据库:使用分片技术分散数据压力。

备份与恢复:定期全量备份,结合Binlog实现增量备份。

监控与告警:实时监控数据库状态,快速响应异常。


4. 分库分表的常见方案有哪些?

分库分表策略:

水平分片:按业务逻辑或数据特征(如用户ID、地区)将数据分布到多个库或表中。

哈希分片:使用哈希函数分配数据。

范围分片:按时间范围或数值区间划分。

垂直分片:将不同业务模块拆分到不同的数据库中。

混合分片:结合水平和垂直分片,适应复杂场景。


5. 什么是CAP定理?MySQL如何取舍?

CAP定理:

C(一致性):所有节点数据一致。

A(可用性):每个请求都能收到非错误的响应。

P(分区容错性):系统在部分节点失效时仍能正常运行。

结论:在一个分布式系统中,最多只能同时满足两个特性。

MySQL的取舍:

MySQL倾向于CP,即在分区容错性和一致性之间权衡。

主从复制中,默认采用异步复制,牺牲部分可用性以保证一致性。



十六、压轴难题
1. 什么是脑裂问题?如何避免?

脑裂问题:

在分布式系统中,当网络分区发生时,集群中的节点可能无法感知彼此状态,导致多个节点都认为自己是主节点。

避免方法:

仲裁机制:引入第三方仲裁器(如ZooKeeper),确保只有一个主节点。

多数派选举:要求超过半数的节点同意才能选举出主节点。

心跳检测:定期检查节点状态,及时发现并隔离异常节点。


2. 如何实现跨库事务(XA事务)?

XA事务:

是一种分布式事务协议,通过两阶段提交(Prepare和Commit)保证多库操作的一致性。

实现步骤:

各个数据库支持XA协议。

开启事务:XA START transaction_id。

提交准备:XA PREPARE transaction_id。

确认提交:XA COMMIT transaction_id。

如果失败,执行回滚:XA ROLLBACK transaction_id。


3. 什么是AP和CP的权衡?(如MySQL vs NoSQL)

AP系统(NoSQL):

强调可用性和分区容错性,允许一定程度的数据不一致。

示例:MongoDB、Cassandra。

CP系统(MySQL):

强调一致性和分区容错性,可能牺牲部分可用性。

示例:MySQL、PostgreSQL。

权衡:

AP适合高并发、弱一致性的场景(如社交应用)。

CP适合强一致性和事务需求高的场景(如金融系统)。


4. 如何设计一个支持千万级数据量的订单系统?

设计方案:

分库分表:按用户ID或订单时间范围进行分片。

分布式ID生成:使用雪花算法生成全局唯一订单ID。

缓存热点数据:使用Redis缓存高频访问的订单信息。

异步处理:将订单创建、支付等操作放入消息队列,减少数据库压力。

归档旧数据:定期将历史订单迁移到冷存储中。

读写分离:通过主从复制实现读写分离,提升查询性能。


5. MySQL 8.0有哪些新特性?

主要特性:

窗口函数:支持OVER()子句,便于复杂数据分析。

Common Table Expressions (CTE):支持递归查询。

JSON增强:提供更多JSON操作函数。

不可见索引:允许临时禁用索引而不删除。

角色管理:简化权限管理。

改进的优化器:更高效的查询计划。

数据字典:使用InnoDB存储元数据,提高可靠性。

标签:

Mysql100道高频面试题由讯客互联电脑硬件栏目发布,感谢您对讯客互联的认可,以及对我们原创作品以及文章的青睐,非常欢迎各位朋友分享到个人网站或者朋友圈,但转载请说明文章出处“Mysql100道高频面试题