世界最流行的开源关系型数据库
MySQL是最流行的开源关系型数据库管理系统（RDBMS），由瑞典MySQL AB公司开发，目前属于Oracle旗下产品。
视图、存储过程、触发器、游标支持
InnoDB成为默认引擎，半同步复制
JSON支持，多源复制，性能大幅提升
窗口函数、CTE、JSON改进、数据字典
# Ubuntu/Debian
sudo apt update
sudo apt install mysql-server
# CentOS/RHEL
sudo yum install mysql-server
# 启动服务
sudo systemctl start mysql
sudo systemctl enable mysql
# 安全配置
sudo mysql_secure_installation# 使用 Homebrew
brew install mysql
# 启动服务
brew services start mysql
# 登录
mysql -u root -p[mysqld]
# 基本设置
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
# 字符集
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
# 日志
log-error=/var/log/mysql/error.log
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
# 性能优化
innodb_buffer_pool_size=1G
max_connections=200
query_cache_size=64M配置文件位置：/etc/mysql/my.cnf 或 /etc/my.cnf
-- 创建数据库
CREATE DATABASE IF NOT EXISTS testdb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
-- 使用数据库
USE testdb;
-- 创建表
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
age INT CHECK (age >= 0 AND age <= 150),
status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_username (username),
INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN email VARCHAR(150);
ALTER TABLE users DROP COLUMN phone;
-- 删除表
DROP TABLE IF EXISTS users;-- 插入数据
INSERT INTO users (username, email, password_hash, age)
VALUES
('john', 'john@example.com', 'hash123', 25),
('jane', 'jane@example.com', 'hash456', 30);
-- 查询数据
SELECT * FROM users;
SELECT id, username, email FROM users WHERE age > 25;
SELECT username, COUNT(*) as count FROM users GROUP BY username;
-- 更新数据
UPDATE users
SET email = 'newemail@example.com'
WHERE username = 'john';
-- 删除数据
DELETE FROM users WHERE status = 'banned';
-- 事务
START TRANSACTION;
INSERT INTO users (username, email, password_hash, age)
VALUES ('test', 'test@example.com', 'hash789', 28);
UPDATE users SET age = 29 WHERE username = 'test';
COMMIT;
ROLLBACK; -- 回滚-- INNER JOIN（内连接）
SELECT u.username, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- LEFT JOIN（左连接）
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- RIGHT JOIN（右连接）
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- 子查询
SELECT * FROM users
WHERE id IN (
SELECT DISTINCT user_id FROM orders
WHERE amount > 1000
);-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_name_age ON users(lastname, firstname, age);
-- 查看索引
SHOW INDEX FROM users;
-- 分析索引使用
EXPLAIN SELECT * FROM users WHERE username = 'john';
-- 删除索引
DROP INDEX idx_username ON users;💡 索引最佳实践：
-- 事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 显式事务
START TRANSACTION;
-- 业务逻辑
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 保存点
START TRANSACTION;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
SAVEPOINT sp1;
INSERT INTO order_items (order_id, product_id) VALUES (1, 101);
ROLLBACK TO SAVEPOINT sp1;
COMMIT;-- 创建存储过程
DELIMITER //
CREATE PROCEDURE get_user_orders(
IN user_id INT,
OUT order_count INT
)
BEGIN
SELECT COUNT(*) INTO order_count
FROM orders
WHERE user_id = user_id;
SELECT * FROM orders WHERE user_id = user_id;
END //
DELIMITER ;
-- 调用存储过程
CALL get_user_orders(1, @count);
SELECT @count;
-- 创建函数
DELIMITER //
CREATE FUNCTION calculate_total_price(
price DECIMAL(10,2),
quantity INT,
tax_rate DECIMAL(5,4)
) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE total DECIMAL(10,2);
SET total = price * quantity * (1 + tax_rate);
RETURN total;
END //
DELIMITER ;-- 创建触发器
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
END //
DELIMITER ;
-- 创建视图
CREATE VIEW active_users AS
SELECT id, username, email, created_at
FROM users
WHERE status = 'active';
-- 使用视图
SELECT * FROM active_users;[mysqld]
# 内存配置
innodb_buffer_pool_size = 4G # 建议设为内存的70-80%
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
# 连接配置
max_connections = 500
wait_timeout = 300
interactive_timeout = 300
# 查询缓存
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M
# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
# 二进制日志
log_bin = /var/log/mysql/bin-log
binlog_format = ROW
expire_logs_days = 7-- 查看连接状态
SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST;
-- 查看表状态
SHOW TABLE STATUS LIKE 'users';
-- 查看慢查询
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;
-- 查看执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 25;
-- 查看锁状态
SHOW ENGINE INNODB STATUS;
-- Performance Schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC LIMIT 10;-- 用户表
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(20),
password_hash VARCHAR(255) NOT NULL,
status ENUM('active', 'inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_email (email)
) ENGINE=InnoDB;
-- 商品表
CREATE TABLE products (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0,
category_id BIGINT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_category (category_id),
FULLTEXT idx_name (name)
) ENGINE=InnoDB;
-- 订单表
CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
status ENUM('pending', 'paid', 'shipped', 'completed') DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_user (user_id),
INDEX idx_status (status),
FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;
-- 订单项表
CREATE TABLE order_items (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id),
INDEX idx_order (order_id)
) ENGINE=InnoDB;主服务器配置 (my.cnf)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
sync-binlog = 1从服务器配置 (my.cnf)
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
read-only = 1-- 主服务器上创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-- 从服务器上配置主从关系
CHANGE MASTER TO
MASTER_HOST='192.168.1.100',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1234;
-- 启动复制
START SLAVE;
-- 查看复制状态
SHOW SLAVE STATUS\G# mysqldump 完整备份
mysqldump -u root -p --single-transaction \
--routines --triggers testdb > backup.sql
# 备份特定表
mysqldump -u root -p testdb users products > tables.sql
# 只备份结构
mysqldump -u root -p --no-data testdb > structure.sql
# 只备份数据
mysqldump -u root -p --no-create-info testdb > data.sql
# 恢复数据
mysql -u root -p testdb < backup.sql
# 增量备份
mysqlbinlog --start-datetime="2024-01-01 00:00:00" \
--stop-datetime="2024-01-02 00:00:00" \
/var/log/mysql/bin-log.000001 > incr_backup.sql
# 热备份（Percona XtraBackup）
xtrabackup --backup --target-dir=/backup/
xtrabackup --prepare --target-dir=/backup/
xtrabackup --copy-back --target-dir=/backup/