跳到主要内容

MySQL

MySQL 是世界上最受欢迎的开源关系型数据库之一，以其可靠性、易用性和性能著称。

特点​

  • 易于使用和管理
  • 高性能
  • 可靠性强
  • 大规模数据处理能力
  • 跨平台支持

安装​

macOS (使用 Homebrew)​

brew install mysql
brew services start mysql

基本命令​

# 登录 MySQL
mysql -u root -p

# 查看数据库
SHOW DATABASES;

# 选择数据库
USE database_name;

# 查看表
SHOW TABLES;

# 查看表结构
DESCRIBE table_name;

常用 SQL 示例​

1. 数据库操作​

-- 创建数据库
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 删除数据库
DROP DATABASE IF EXISTS myapp;

-- 使用数据库
USE myapp;

2. 表操作​

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash CHAR(60) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- 修改表
ALTER TABLE users 
ADD COLUMN last_login TIMESTAMP NULL,
ADD COLUMN login_count INT DEFAULT 0;

-- 创建外键
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

3. 数据操作​

-- 插入数据
INSERT INTO users (username, email, password_hash)
VALUES 
    ('john_doe', 'john@example.com', 'hashed_password_1'),
    ('jane_doe', 'jane@example.com', 'hashed_password_2');

-- 批量插入
INSERT INTO posts (user_id, title, content)
SELECT 
    id,
    CONCAT('Post by ', username),
    'Sample content'
FROM users;

-- 更新数据
UPDATE users 
SET status = 'inactive',
    login_count = login_count + 1
WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE status = 'banned';

4. 查询示例​

-- 基本联接查询
SELECT 
    u.username,
    p.title,
    p.created_at
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.status = 'active'
ORDER BY p.created_at DESC;

-- 分组统计
SELECT 
    status,
    COUNT(*) as user_count,
    MAX(created_at) as latest_user
FROM users
GROUP BY status
HAVING user_count > 5;

-- 子查询
SELECT username, email
FROM users
WHERE id IN (
    SELECT DISTINCT user_id
    FROM posts
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
);

-- 窗口函数
SELECT 
    username,
    login_count,
    RANK() OVER (ORDER BY login_count DESC) as login_rank
FROM users;

5. 事务处理​

-- 开始事务
START TRANSACTION;

-- 转账操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

-- 如果出错则回滚
ROLLBACK;

Node.js 示例​

const mysql = require('mysql2/promise');

// 创建连接池
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 查询示例
async function getUserPosts(userId) {
  try {
    const [rows] = await pool.query(`
      SELECT u.username, p.title, p.content
      FROM users u
      JOIN posts p ON u.id = p.user_id
      WHERE u.id = ?
    `, [userId]);
    return rows;
  } catch (err) {
    console.error('Error:', err);
    throw err;
  }
}

// 事务示例
async function createUserWithPosts(userData, posts) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    // 插入用户
    const [userResult] = await connection.query(
      'INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)',
      [userData.username, userData.email, userData.passwordHash]
    );

    // 插入文章
    const userId = userResult.insertId;
    for (const post of posts) {
      await connection.query(
        'INSERT INTO posts (user_id, title, content) VALUES (?, ?, ?)',
        [userId, post.title, post.content]
      );
    }

    await connection.commit();
    return userId;
  } catch (err) {
    await connection.rollback();
    throw err;
  } finally {
    connection.release();
  }
}

性能优化技巧​

  1. 索引优化
-- 添加复合索引
ALTER TABLE posts 
ADD INDEX idx_user_created (user_id, created_at);

-- 查看索引使用情况
EXPLAIN SELECT * FROM posts 
WHERE user_id = 1 
AND created_at > '2023-01-01';
  1. 查询优化
-- 使用 EXPLAIN 分析查询
EXPLAIN FORMAT=JSON
SELECT * FROM users 
WHERE email LIKE '%@example.com';

-- 优化分页查询
SELECT * FROM users u
JOIN (
    SELECT id FROM users 
    ORDER BY created_at 
    LIMIT 10 OFFSET 1000000
) sub ON u.id = sub.id;
  1. 配置优化
-- 查看系统变量
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 查看状态
SHOW STATUS LIKE 'Threads_connected';

备份和恢复​

# 备份数据库
mysqldump -u root -p myapp > backup.sql

# 恢复数据库
mysql -u root -p myapp < backup.sql

# 备份特定表
mysqldump -u root -p myapp users posts > tables_backup.sql

常见问题解决​

  1. 连接数问题
-- 查看当前连接
SHOW PROCESSLIST;

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

-- 终止特定连接
KILL CONNECTION_ID;
  1. 死锁处理
-- 查看当前锁
SHOW ENGINE INNODB STATUS;

-- 查看死锁信息
SELECT * FROM information_schema.INNODB_TRX;
  1. 慢查询分析
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- 查看慢查询
SELECT * FROM mysql.slow_log;

主从复制配置​

主服务器配置​

-- 在 my.cnf 中添加：
server-id = 1
log-bin = mysql-bin

-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

从服务器配置​

CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=0;

START SLAVE;