- 作者简介:一名后端开发人员,每天分享后端开发以及人工智能相关技术,行业前沿信息,面试宝典。
- 座右铭:未来是不可确定的,慢慢来是最快的。
- 个人主页:极客李华-CSDN博客
- 合作方式:私聊+
- 这个专栏内容:BAT等大厂常见后端java开发面试题详细讲解,更新数目100道常见大厂java后端开发面试题。
- 我的CSDN社区:https://bbs.csdn.net/forums/99eb3042821a4432868bb5bfc4d513a8
- 微信公众号,抖音,b站等平台统一叫做:极客李华,加入微信公众号领取各种编程资料,加入抖音,b站学习面试技巧
深入理解mysql
数据库基础概念与体系结构
-
数据库基础概念: 数据库是一个结构化的数据集合,可以通过一定的方式进行组织、存储和管理。MySQL 是一个关系型数据库管理系统(RDBMS),它采用表格的形式来存储数据。表格由行和列组成,每行代表一条记录,每列代表一个字段。
-
数据库体系结构: MySQL 的体系结构分为三层:服务器层、存储引擎层和表格定义层。服务器层处理连接、查询解析和执行等请求。存储引擎层负责存储和检索数据。表格定义层包括表的结构定义和元数据。
-
MySQL 的体系结构详解:
-
服务器层: 服务器层是 MySQL 的顶层,负责处理连接、查询解析、优化和执行等任务。其中,连接处理模块负责建立和维护客户端与服务器的连接。查询解析和优化模块负责解析 SQL 查询语句并生成执行计划。执行模块负责执行生成的执行计划,与存储引擎交互获取数据。
-
存储引擎层: 存储引擎层负责实际的数据存储和检索。MySQL 支持多种存储引擎,如 InnoDB、MyISAM 等。每个存储引擎都有其特定的优势和适用场景。例如,InnoDB 支持事务和行级锁,适合事务型应用;MyISAM 性能较好,适合读密集型应用。
-
表格定义层: 表格定义层包括表的结构定义和元数据。表的结构定义包括表的字段、类型、约束等信息。元数据包括数据库、表、字段等的信息。MySQL 使用数据字典来存储这些元数据。
-
-
代码示例与应用场景:
-
创建数据库和表:
CREATE DATABASE IF NOT EXISTS mydatabase; USE mydatabase; CREATE TABLE IF NOT EXISTS users ( id INT PRIMARY KEY, username VARCHAR(255), email VARCHAR(255) );
-
插入数据:
INSERT INTO users (id, username, email) VALUES (1, 'john_doe', 'john@example.com');
-
查询数据:
SELECT * FROM users WHERE id = 1;
-
应用场景: 数据库的基本操作在各种应用场景中都得到应用,如用户管理系统、电子商务平台等。通过建立良好的数据库结构,可以实现高效的数据存储和检索。
-
SQL语言与查询优化
-
SQL基础语法:
SQL(Structured Query Language)是用于管理和操作关系型数据库的标准语言。以下是 SQL 的一些基本语法:
-
SELECT 语句: 用于从数据库中查询数据。
SELECT column1, column2 FROM table WHERE condition;
-
INSERT 语句: 用于向数据库中插入新数据。
INSERT INTO table (column1, column2) VALUES (value1, value2);
-
UPDATE 语句: 用于更新数据库中的数据。
UPDATE table SET column1 = value1 WHERE condition;
-
DELETE 语句: 用于从数据库中删除数据。
DELETE FROM table WHERE condition;
-
-
SQL高级特性:
-
子查询: 在查询中嵌套另一个查询。
SELECT column1 FROM table WHERE column2 = (SELECT MAX(column2) FROM table2);
-
联结: 将两个或多个表格的行组合在一起。
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
-
索引: 用于加速查询操作的数据结构。
CREATE INDEX index_name ON table (column1, column2);
-
-
查询优化技术:
-
执行计划分析: 使用
EXPLAIN
关键字查看 SQL 查询语句的执行计划,帮助理解查询性能。EXPLAIN SELECT * FROM table WHERE column = value;
-
索引优化: 确保表格的查询字段都有合适的索引,避免全表扫描。
CREATE INDEX index_name ON table (column1, column2);
-
使用合适的存储引擎: 不同存储引擎适用于不同的场景,如 InnoDB 适用于事务型应用,MyISAM 适用于读密集型应用。
-
-
代码示例与应用场景:
-
使用子查询进行统计:
SELECT department, AVG(salary) AS avg_salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) GROUP BY department;
-
使用联结查询获取关联数据:
SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
-
创建索引进行查询优化:
CREATE INDEX idx_last_name ON employees (last_name);
-
应用场景: 在复杂的业务系统中,通过巧妙运用 SQL 的高级特性和查询优化技术,可以实现更灵活、高效的数据操作和检索。
-
事务与并发控制
-
事务基础概念:
事务是数据库中的一个操作序列,它要么全部执行成功,要么全部失败回滚,保证数据库的一致性。事务具有四个属性,即 ACID 特性:
- 原子性(Atomicity): 事务是一个原子操作单元,要么全部执行成功,要么全部回滚失败。
- 一致性(Consistency): 事务执行前后数据库的状态保持一致。
- 隔离性(Isolation): 多个事务并发执行时,每个事务都应该感觉不到其他事务的存在。
- 持久性(Durability): 一旦事务提交,其对数据库的修改将是永久性的。
-
事务管理:
-
事务的开始和提交:
START TRANSACTION; -- 事务开始 -- 执行一系列 SQL 操作 COMMIT; -- 事务提交
-
事务的回滚:
START TRANSACTION; -- 执行一系列 SQL 操作 ROLLBACK; -- 事务回滚
-
-
事务隔离级别:
MySQL 支持四个事务隔离级别,分别是 Read Uncommitted、Read Committed、Repeatable Read 和 Serializable。不同的隔离级别在事务并发性和一致性之间做出权衡。
SET TRANSACTION ISOLATION LEVEL Read Committed;
-
并发控制:
- 锁机制: MySQL 使用锁机制来确保事务的隔离性。包括行级锁和表级锁。
- MVCC(多版本并发控制): 允许多个事务并发执行,每个事务看到的数据是数据库在事务开始时的一个快照。
-
代码示例与应用场景:
-
简单事务的应用:
START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; UPDATE accounts SET balance = balance + 100 WHERE user_id = 2; COMMIT;
-
设置事务隔离级别:
SET TRANSACTION ISOLATION LEVEL Read Committed; START TRANSACTION; -- 执行一系列 SQL 操作 COMMIT;
-
使用行级锁进行并发控制:
START TRANSACTION; -- 执行一系列 SQL 操作 COMMIT;
-
应用场景: 事务和并发控制是数据库系统中关键的概念,广泛应用于银行系统、在线支付等对数据一致性和安全性要求极高的场景。
-
索引与性能优化
-
索引基础概念:
索引是一种数据结构,用于加速数据库的查询操作。它类似于书籍的目录,能够快速定位到需要的数据。
-
创建单列索引:
CREATE INDEX index_name ON table (column1);
-
创建多列索引:
CREATE INDEX index_name ON table (column1, column2);
-
-
索引的设计与使用:
- 设计合适的索引: 了解查询需求,设计覆盖查询条件的索引,避免创建过多或不必要的索引。
- 使用合适的数据类型: 索引的效率与数据类型相关,选择适当的数据类型能够提高索引性能。
-
性能优化策略:
- 查询优化器: MySQL 查询优化器负责分析和选择最优的查询执行计划。
- 使用合适的存储引擎: 不同存储引擎适用于不同场景,如 InnoDB 适用于事务型应用,MyISAM 适用于读密集型应用。
-
代码示例与应用场景:
-
创建单列索引:
CREATE INDEX idx_last_name ON employees (last_name);
-
创建多列索引:
CREATE INDEX idx_name_salary ON employees (last_name, salary);
-
查询优化器的使用:
EXPLAIN SELECT * FROM employees WHERE last_name = 'Doe';
-
应用场景: 索引和性能优化是数据库系统中非常重要的方面,合理设计和使用索引可以大幅提高查询效率,特别是在大型数据集的情况下。
-
高可用与备份恢复
-
高可用性概念:
高可用性是指系统能够在面临故障时保持持续可用的能力。在数据库领域,实现高可用性通常涉及到故障切换、主从复制等技术。
-
备份与恢复:
数据库备份是保护数据安全的关键措施,同时备份还可以用于数据恢复。备份可以分为物理备份和逻辑备份。
-
物理备份: 复制数据库文件,例如使用
mysqldump
工具。mysqldump -u username -p --all-databases > backup.sql
-
逻辑备份: 复制数据库的逻辑结构和数据,例如使用
cp
命令复制数据文件。cp -r /var/lib/mysql /backup
-
恢复数据:
mysql -u username -p < backup.sql
-
-
高可用性技术:
-
主从复制: 通过将主数据库的更改同步到从数据库,实现读写分离和故障切换。
-- 主数据库 binlog_format = ROW server_id = 1 log-bin = /var/log/mysql/mysql-bin.log -- 从数据库 server_id = 2 replicate-do-db = mydatabase
-
集群: 使用集群技术实现多台服务器的协同工作,例如 MySQL Cluster、Percona XtraDB Cluster 等。
-
-
代码示例与应用场景:
-
使用主从复制实现读写分离:
-- 主数据库 CREATE USER 'repl'@'slave_ip' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_ip'; -- 从数据库 CHANGE MASTER TO MASTER_HOST = 'master_ip', MASTER_USER = 'repl', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'binary_log_file', MASTER_LOG_POS = binary_log_position;
-
数据库集群的搭建:
-- 使用 Percona XtraDB Cluster 为例 wsrep_cluster_address = gcomm://node1_ip,node2_ip,node3_ip
-
定期备份与恢复策略:
- 定期执行数据库备份,确保数据的安全性。
- 建立完整的备份恢复策略,包括物理备份和逻辑备份的选择。
-
应用场景: 在高流量、高并发的应用场景中,高可用性和备份恢复策略变得至关重要,以保障系统的稳定运行和数据的安全性。
-