mysql数据库基础知识
MySQL 数据库基础知识
一、核心概念
-
数据库(Database)
- 结构化数据集合,用于存储和管理数据。
- MySQL 是关系型数据库(RDBMS),基于表(Table)结构组织数据。
-
表(Table)
- 由行(Row)和列(Column)组成,每列定义数据类型(如整数、字符串)。
- 示例:用户表
users
包含id
、name
、email
等字段。
-
SQL(Structured Query Language)
- 用于操作数据库的标准语言,支持数据查询、插入、更新、删除等操作。
二、MySQL 体系结构
-
服务端与客户端
- 服务端:
mysqld
进程,处理数据存储和请求。 - 客户端:通过命令行(
mysql
)、图形工具(如 Workbench)或应用程序连接服务端。
- 服务端:
-
存储引擎
- InnoDB(默认引擎):支持事务、行级锁、外键约束,适合高并发写入。
- MyISAM:不支持事务,表级锁,适合读多写少场景。
-
数据库文件
- 数据文件:
.ibd
(InnoDB 表数据)、.MYD
/.MYI
(MyISAM 数据/索引)。 - 配置文件:
my.cnf
(Linux)或my.ini
(Windows)。
- 数据文件:
三、基础操作
-
数据库管理
sqlCopy Code-- 创建数据库
CREATE DATABASE testdb;-- 删除数据库
DROP DATABASE testdb;-- 选择数据库
USE testdb;
-
表操作
sqlCopy Code-- 创建表
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE
);-- 删除表
DROP TABLE users;-- 修改表结构
ALTER TABLE users ADD COLUMN age INT;
-
数据操作(CRUD)
sqlCopy Code-- 插入数据
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');-- 查询数据
SELECT * FROM users WHERE id = 1;-- 更新数据
UPDATE users SET email = 'alice_new@example.com' WHERE id = 1;-- 删除数据
DELETE FROM users WHERE id = 1;
四、数据类型
-
常用类型
- 整数:
INT
(4字节)、BIGINT
(8字节)。 - 浮点数:
FLOAT
、DOUBLE
。 - 字符串:
VARCHAR(255)
(可变长度)、CHAR(10)
(固定长度)。 - 日期时间:
DATE
、DATETIME
、TIMESTAMP
。 - 布尔值:
BOOLEAN
(实际存储为TINYINT(1)
)。
- 整数:
-
特殊类型
ENUM
:枚举值(如ENUM('男', '女')
)。JSON
:存储 JSON 格式数据(MySQL 5.7+)。
五、索引与优化
-
索引类型
- 主键索引(PRIMARY KEY):唯一且非空,加速查询。
- 唯一索引(UNIQUE):确保列值唯一。
- 普通索引(INDEX):加速非唯一列查询。
-
创建索引
sqlCopy Code-- 单列索引
CREATE INDEX idx_name ON users(name);-- 联合索引
CREATE INDEX idx_name_email ON users(name, email);
-
优化原则
- 避免全表扫描:使用
WHERE
条件过滤数据。 - 索引覆盖:查询字段尽量包含在索引中。
- 避免过度索引:索引会降低写入速度。
- 避免全表扫描:使用
六、事务与锁
-
事务特性(ACID)
- 原子性(Atomicity):事务操作要么全部成功,要么全部失败。
- 一致性(Consistency):事务前后数据完整性不变。
- 隔离性(Isolation):并发事务互不干扰。
- 持久性(Durability):事务提交后数据永久保存。
-
事务控制
sqlCopy CodeSTART TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 提交事务
-- ROLLBACK; -- 回滚事务
-
隔离级别
READ UNCOMMITTED
:可能读取未提交数据(脏读)。READ COMMITTED
(默认):避免脏读,但可能不可重复读。REPEATABLE READ
:避免不可重复读。SERIALIZABLE
:完全串行化,避免幻读。
七、备份与恢复
-
逻辑备份
bashCopy Code# 导出整个数据库
mysqldump -u root -p testdb > testdb.sql# 导入数据
mysql -u root -p testdb < testdb.sql
-
物理备份
- 直接复制数据文件(需停止服务或使用
FLUSH TABLES WITH READ LOCK
)。
- 直接复制数据文件(需停止服务或使用
-
二进制日志(Binlog)
- 记录所有数据变更,用于增量恢复。
bashCopy Codemysqlbinlog binlog.000001 | mysql -u root -p
八、安全与权限
-
用户管理
sqlCopy Code-- 创建用户
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';-- 授权
GRANT SELECT, INSERT ON testdb.* TO 'user1'@'localhost';-- 撤销权限
REVOKE INSERT ON testdb.* FROM 'user1'@'localhost';-- 删除用户
DROP USER 'user1'@'localhost' -
密码安全
- 使用强密码(字母+数字+符号)。
- 定期更新密码:
ALTER USER 'user1'@'localhost' IDENTIFIED BY 'new_password';
九、常见问题与技巧
-
字符集与编码
- 推荐使用
utf8mb4
支持 Emoji 和所有 Unicode 字符。
sqlCopy CodeALTER DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
- 推荐使用
-
慢查询优化
- 开启慢查询日志:
iniCopy Code
# my.cnf 配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
- 开启慢查询日志:
-
连接池与性能
- 使用连接池(如 HikariCP、DBCP)减少频繁连接开销。
附:学习路径建议
- 掌握基本 SQL 语法和表设计(主键、外键、范式)。
- 学习索引优化和 EXPLAIN 分析执行计划。
- 实践事务管理、备份恢复和主从复制(Replication)。
- 进阶:高可用架构(如 MHA、InnoDB Cluster)、分库分表(Sharding)。