mysql数据库基础知识

MySQL 数据库基础知识

一、‌核心概念‌

1. ‌数据库（Database）‌

   • 结构化数据集合，用于存储和管理数据。
   • MySQL 是‌关系型数据库（RDBMS）‌，基于表（Table）结构组织数据。

2. ‌表（Table）‌

   • 由行（Row）和列（Column）组成，每列定义数据类型（如整数、字符串）。
   • 示例：用户表 users 包含 id、name、email 等字段。

3. ‌SQL（Structured Query Language）‌

   • 用于操作数据库的标准语言，支持数据查询、插入、更新、删除等操作。

二、‌MySQL 体系结构‌

1. ‌服务端与客户端‌

   • ‌服务端‌：mysqld 进程，处理数据存储和请求。
   • ‌客户端‌：通过命令行（mysql）、图形工具（如 Workbench）或应用程序连接服务端。

2. ‌存储引擎‌

   • ‌InnoDB‌（默认引擎）：支持事务、行级锁、外键约束，适合高并发写入。
   • ‌MyISAM‌：不支持事务，表级锁，适合读多写少场景。

3. ‌数据库文件‌

   • 数据文件：.ibd（InnoDB 表数据）、.MYD/.MYI（MyISAM 数据/索引）。
   • 配置文件：my.cnf（Linux）或 my.ini（Windows）。

三、‌基础操作‌

1. ‌数据库管理‌

   sqlCopy Code
   -- 创建数据库
   CREATE DATABASE testdb;

   -- 删除数据库
   DROP DATABASE testdb;

   -- 选择数据库
USE testdb;


2. ‌表操作‌

   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;


3. ‌数据操作（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;


四、‌数据类型‌

1. ‌常用类型‌

   • ‌整数‌：INT（4字节）、BIGINT（8字节）。
   • ‌浮点数‌：FLOAT、DOUBLE。
   • ‌字符串‌：VARCHAR(255)（可变长度）、CHAR(10)（固定长度）。
   • ‌日期时间‌：DATE、DATETIME、TIMESTAMP。
   • ‌布尔值‌：BOOLEAN（实际存储为 TINYINT(1)）。

2. ‌特殊类型‌

   • ENUM：枚举值（如 ENUM('男', '女')）。
   • JSON：存储 JSON 格式数据（MySQL 5.7+）。

五、‌索引与优化‌

1. ‌索引类型‌

   • ‌主键索引（PRIMARY KEY）‌：唯一且非空，加速查询。
   • ‌唯一索引（UNIQUE）‌：确保列值唯一。
   • ‌普通索引（INDEX）‌：加速非唯一列查询。

2. ‌创建索引‌

   sqlCopy Code
   -- 单列索引
   CREATE INDEX idx_name ON users(name);

   -- 联合索引
CREATE INDEX idx_name_email ON users(name, email);


3. ‌优化原则‌

   • 避免全表扫描：使用 WHERE 条件过滤数据。
   • 索引覆盖：查询字段尽量包含在索引中。
   • 避免过度索引：索引会降低写入速度。

六、‌事务与锁‌

1. ‌事务特性（ACID）‌

   • ‌原子性（Atomicity）‌：事务操作要么全部成功，要么全部失败。
   • ‌一致性（Consistency）‌：事务前后数据完整性不变。
   • ‌隔离性（Isolation）‌：并发事务互不干扰。
   • ‌持久性（Durability）‌：事务提交后数据永久保存。

2. ‌事务控制‌

   sqlCopy Code
   START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 提交事务
-- ROLLBACK; -- 回滚事务


3. ‌隔离级别‌

   • READ UNCOMMITTED：可能读取未提交数据（脏读）。
   • READ COMMITTED（默认）：避免脏读，但可能不可重复读。
   • REPEATABLE READ：避免不可重复读。
   • SERIALIZABLE：完全串行化，避免幻读。

七、‌备份与恢复‌

1. ‌逻辑备份‌

   bashCopy Code
   # 导出整个数据库
   mysqldump -u root -p testdb > testdb.sql

   # 导入数据
mysql -u root -p testdb < testdb.sql


2. ‌物理备份‌

   • 直接复制数据文件（需停止服务或使用 FLUSH TABLES WITH READ LOCK）。

3. ‌二进制日志（Binlog）‌

   • 记录所有数据变更，用于增量恢复。
   bashCopy Code
   mysqlbinlog binlog.000001 | mysql -u root -p


八、‌安全与权限‌

1. ‌用户管理‌

   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'

2. ‌密码安全‌

   • 使用强密码（字母+数字+符号）。
   • 定期更新密码：ALTER USER 'user1'@'localhost' IDENTIFIED BY 'new_password';

九、‌常见问题与技巧‌

1. ‌字符集与编码‌

   • 推荐使用 utf8mb4 支持 Emoji 和所有 Unicode 字符。
   sqlCopy Code
   ALTER DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


2. ‌慢查询优化‌

   • 开启慢查询日志：
     iniCopy Code
     # my.cnf 配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2


3. ‌连接池与性能‌

   • 使用连接池（如 HikariCP、DBCP）减少频繁连接开销。

‌附：学习路径建议‌

1. 掌握基本 SQL 语法和表设计（主键、外键、范式）。
2. 学习索引优化和 EXPLAIN 分析执行计划。
3. 实践事务管理、备份恢复和主从复制（Replication）。
4. 进阶：高可用架构（如 MHA、InnoDB Cluster）、分库分表（Sharding）。