MySQL数据库全面解析：从入门到精通

-- 创建示例数据库
CREATE DATABASE company;
USE company;

-- 创建部门表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL,
    location VARCHAR(100)
) ENGINE=InnoDB;

-- 创建员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE NOT NULL,
    salary DECIMAL(10,2),
    dept_id INT,
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
) ENGINE=InnoDB;

-- 表结构修改高级示例
ALTER TABLE employees
-- 添加新列
ADD COLUMN phone_number VARCHAR(20) AFTER email,
-- 修改列定义
MODIFY COLUMN salary DECIMAL(12,2) NOT NULL DEFAULT 0,
-- 添加索引
ADD INDEX idx_name (first_name, last_name),
-- 添加唯一约束
ADD CONSTRAINT uc_email UNIQUE (email),
-- 修改表选项
ENGINE = InnoDB 
ROW_FORMAT = DYNAMIC
KEY_BLOCK_SIZE = 8;

-- 分区表示例
CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    region VARCHAR(50),
    PRIMARY KEY (sale_id, sale_date)
) PARTITION BY RANGE COLUMNS(sale_date) (
    PARTITION p2023_q1 VALUES LESS THAN ('2023-04-01'),
    PARTITION p2023_q2 VALUES LESS THAN ('2023-07-01'),
    PARTITION p2023_q3 VALUES LESS THAN ('2023-10-01'),
    PARTITION p2023_q4 VALUES LESS THAN ('2024-01-01'),
    PARTITION p2024_other VALUES LESS THAN (MAXVALUE)
);

-- 复杂插入操作
INSERT INTO employees (first_name, last_name, email, hire_date, salary, dept_id)
VALUES 
('张', '三', 'zhangsan@example.com', '2023-01-15', 8000.00, 1),
('李', '四', 'lisi@example.com', '2023-02-20', 7500.00, 1)
ON DUPLICATE KEY UPDATE 
    salary = VALUES(salary),
    hire_date = VALUES(hire_date);

-- 多表更新示例
UPDATE employees e
JOIN departments d ON e.dept_id = d.dept_id
SET e.salary = e.salary * 1.1
WHERE d.location = '北京'
AND e.hire_date < '2023-01-01';

-- 基于子查询的删除
DELETE FROM employees 
WHERE dept_id IN (
    SELECT dept_id FROM departments 
    WHERE location = '上海'
)
AND salary < (
    SELECT AVG(salary) FROM employees
);

-- 窗口函数应用
SELECT 
    emp_id,
    first_name,
    last_name,
    salary,
    dept_id,
    -- 部门内薪资排名
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as dept_rank,
    -- 部门平均薪资
    AVG(salary) OVER (PARTITION BY dept_id) as avg_dept_salary,
    -- 累计分布
    CUME_DIST() OVER (PARTITION BY dept_id ORDER BY salary) as salary_cume_dist
FROM employees
WHERE hire_date > '2020-01-01';

-- 通用表表达式(CTE)递归查询
WITH RECURSIVE employee_hierarchy AS (
    -- 锚点查询(顶层管理者)
    SELECT emp_id, first_name, last_name, manager_id, 1 as level
    FROM employees 
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询(下属员工)
    SELECT e.emp_id, e.first_name, e.last_name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.emp_id
)
SELECT * FROM employee_hierarchy ORDER BY level, emp_id;

-- JSON数据查询
SELECT 
    emp_id,
    first_name,
    JSON_OBJECT(
        'email', email,
        'hireDate', hire_date,
        'salary', salary,
        'department', (SELECT dept_name FROM departments WHERE dept_id = e.dept_id)
    ) as employee_info,
    JSON_EXTRACT(employee_info, '$.salary') as extracted_salary
FROM employees e
WHERE JSON_EXTRACT(employee_info, '$.salary') > 5000;

-- 创建多种索引类型
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    category_id INT,
    price DECIMAL(10,2),
    description TEXT,
    tags JSON,
    fulltext_index VARCHAR(500),
    -- 空间数据(需要GIS扩展)
    location POINT SRID 4326,
    INDEX idx_category_price (category_id, price DESC),
    INDEX idx_name (product_name(100)),
    FULLTEXT idx_fulltext (fulltext_index),
    SPATIAL INDEX idx_location (location)
) ENGINE=InnoDB;

-- 函数索引(MySQL 8.0+)
CREATE INDEX idx_lower_name ON products ((LOWER(product_name)));

-- 生成列+索引
ALTER TABLE products
ADD COLUMN name_lower VARCHAR(255) AS (LOWER(product_name)) STORED,
ADD INDEX idx_stored_lower (name_lower);

-- 1. 函数操作导致索引失效
SELECT * FROM products WHERE YEAR(create_time) = 2023; -- 索引失效
SELECT * FROM products WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'; -- 有效

-- 2. 隐式类型转换
SELECT * FROM products WHERE product_id = '123'; -- 字符串转数字，可能失效

-- 3. OR条件不当使用
SELECT * FROM products WHERE category_id = 1 OR price > 100; -- 联合索引可能失效

-- 4. LIKE模糊查询
SELECT * FROM products WHERE product_name LIKE '%apple%'; -- 前缀模糊，索引失效
SELECT * FROM products WHERE product_name LIKE 'apple%'; -- 前缀匹配，索引有效

-- 查看和设置隔离级别
SELECT @@transaction_isolation; -- 查看当前隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置会话级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 设置全局级别

-- 不同隔离级别的问题表现
/*
READ UNCOMMITTED: 脏读、不可重复读、幻读
READ COMMITTED: 不可重复读、幻读
REPEATABLE READ: 幻读(MySQL通过Next-Key Lock解决)
SERIALIZABLE: 无并发问题但性能最低
*/

-- 显式锁使用示例
START TRANSACTION;

-- 行级排他锁
SELECT * FROM employees WHERE emp_id = 1 FOR UPDATE;

-- 行级共享锁
SELECT * FROM departments WHERE dept_id = 1 LOCK IN SHARE MODE;

-- 表级锁
LOCK TABLES employees WRITE, departments READ;

-- 执行业务操作
UPDATE employees SET salary = salary * 1.1 WHERE emp_id = 1;

COMMIT;
UNLOCK TABLES; -- 释放表锁

-- 使用EXPLAIN分析查询计划
EXPLAIN FORMAT=JSON
SELECT e.first_name, e.last_name, d.dept_name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 5000
AND d.location = '北京'
ORDER BY e.salary DESC
LIMIT 10;

-- 优化建议输出示例
/*
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "8.56"
    },
    "ordering_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "e",
        "access_type": "range",
        "possible_keys": ["idx_salary","fk_dept"],
        "key": "idx_salary",
        "rows_examined_per_scan": 123,
        "rows_produced_per_join": 45,
        "filtered": "36.59",
        "cost_info": { ... },
        "used_columns": [ ... ],
        "attached_condition": "(`e`.`salary` > 5000)"
      }
    }
  }
}
*/

-- 关键性能参数监控
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- 缓冲池命中率计算
SELECT 
    (1 - (Variable_value / (SELECT Variable_value 
                           FROM information_schema.GLOBAL_STATUS 
                           WHERE Variable_name = 'Innodb_buffer_pool_read_requests'))) * 100 
    AS hit_rate
FROM information_schema.GLOBAL_STATUS 
WHERE Variable_name = 'Innodb_buffer_pool_reads';

-- 连接数监控
SELECT 
    MAX_USED_CONNECTIONS,
    MAX_CONNECTIONS,
    (MAX_USED_CONNECTIONS / MAX_CONNECTIONS) * 100 AS connection_usage_percent
FROM performance_schema.session_connect_attrs;

-- 主库配置
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_row_image = full
gtid_mode = ON
enforce_gtid_consistency = ON

-- 从库配置
[mysqld]
server_id = 2
relay_log = /var/log/mysql/mysql-relay.log
read_only = ON
super_read_only = ON
gtid_mode = ON
enforce_gtid_consistency = ON

-- 创建复制用户
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_AUTO_POSITION = 1;

START SLAVE;

-- 创建最小权限用户
CREATE USER 'app_user'@'192.168.1.%' 
IDENTIFIED WITH caching_sha2_password BY 'secure_password'
REQUIRE SSL
WITH MAX_QUERIES_PER_HOUR 1000
MAX_UPDATES_PER_HOUR 100
MAX_CONNECTIONS_PER_HOUR 100;

-- 授予精确权限
GRANT SELECT, INSERT, UPDATE ON company.employees TO 'app_user'@'192.168.1.%';
GRANT SELECT ON company.departments TO 'app_user'@'192.168.1.%';
GRANT EXECUTE ON PROCEDURE company.update_salary TO 'app_user'@'192.168.1.%';

-- 列级权限控制
GRANT SELECT (emp_id, first_name, last_name) ON company.employees TO 'report_user'@'%';
GRANT UPDATE (salary) ON company.employees TO 'hr_user'@'internal';

-- 查看权限
SHOW GRANTS FOR 'app_user'@'192.168.1.%';

-- 启用审计日志(企业版功能)
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_format = JSON;
SET GLOBAL audit_log_policy = ALL;

-- 使用通用日志进行审计
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

-- 查询审计日志
SELECT * FROM mysql.general_log 
WHERE event_time > NOW() - INTERVAL 1 HOUR
AND command_type IN ('Query','Execute')
ORDER BY event_time DESC;

-- 高级窗口函数应用
SELECT 
    emp_id,
    first_name,
    last_name,
    salary,
    dept_id,
    -- 计算移动平均
    AVG(salary) OVER (
        PARTITION BY dept_id 
        ORDER BY hire_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_salary,
    -- 首尾值分析
    FIRST_VALUE(salary) OVER (
        PARTITION BY dept_id 
        ORDER BY salary DESC
    ) AS highest_salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY dept_id 
        ORDER BY salary DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_salary,
    -- 分组排名
    NTILE(4) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_quartile
FROM employees
WHERE hire_date > '2018-01-01';

-- 多层CTE嵌套
WITH department_stats AS (
    SELECT 
        dept_id,
        COUNT(*) AS emp_count,
        AVG(salary) AS avg_salary,
        MAX(salary) AS max_salary
    FROM employees
    GROUP BY dept_id
),
company_stats AS (
    SELECT 
        SUM(emp_count) AS total_employees,
        AVG(avg_salary) AS company_avg_salary
    FROM department_stats
),
final_report AS (
    SELECT 
        d.dept_name,
        ds.emp_count,
        ds.avg_salary,
        ds.max_salary,
        (ds.avg_salary - cs.company_avg_salary) AS diff_from_avg,
        RANK() OVER (ORDER BY ds.avg_salary DESC) AS salary_rank
    FROM department_stats ds
    JOIN departments d ON ds.dept_id = d.dept_id
    CROSS JOIN company_stats cs
)
SELECT * FROM final_report ORDER BY salary_rank;

-- 电商核心表结构
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash CHAR(60) NOT NULL, -- bcrypt哈希
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    stock_quantity INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    category_id INT,
    status ENUM('active','inactive','out_of_stock') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT INDEX idx_product_search (name, description),
    INDEX idx_category_status (category_id, status),
    INDEX idx_price_range (price)
) ENGINE=InnoDB;

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    status ENUM('pending','paid','shipped','delivered','cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_orders (user_id, created_at),
    INDEX idx_order_status (status, created_at),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=InnoDB;

-- 分区表：按时间分区管理订单数据
ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 查询优化：使用覆盖索引
EXPLAIN SELECT product_id, name, price 
FROM products 
WHERE category_id = 5 AND status = 'active'
ORDER BY price DESC 
LIMIT 20;

-- 创建覆盖索引
ALTER TABLE products ADD INDEX idx_category_status_price (category_id, status, price DESC);

-- 分页查询优化
SELECT * FROM products 
WHERE product_id < ?  -- 上一页最后一条记录的ID
AND category_id = 5
ORDER BY product_id DESC
LIMIT 20;

-- 批量插入优化
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?); -- 批量插入

-- 使用LOAD DATA INFILE进行大数据量导入
LOAD DATA INFILE '/path/to/order_items.csv'
INTO TABLE order_items
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(order_id, product_id, quantity, price);

-- 关键性能指标查询
SELECT 
    -- 查询性能
    (SELECT COUNT(*) FROM information_schema.PROCESSLIST) AS active_connections,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Threads_connected') AS threads_connected,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_free') AS free_buffer_pages,
    
    -- 缓存命中率
    ROUND(100 - (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') / 
          (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') * 100, 2) AS buffer_pool_hit_rate,
    
    -- 锁等待
    (SELECT COUNT(*) FROM information_schema.INNODB_LOCKS) AS active_locks,
    (SELECT COUNT(*) FROM information_schema.INNODB_LOCK_WAITS) AS lock_waits,
    
    -- 复制状态
    (SELECT Slave_IO_Running FROM information_schema.PROCESSLIST WHERE COMMAND = 'Binlog Dump') AS io_thread_running,
    (SELECT Slave_SQL_Running FROM information_schema.PROCESSLIST WHERE COMMAND = 'Binlog Dump') AS sql_thread_running
FROM DUAL;