MySQL使用方法完整指南

一、MySQL 核心概念与架构

1.1 MySQL 体系结构

1. 客户端层
2. ↓
3. 连接层（连接池、线程池）
4. ↓
5. SQL层（解析器、优化器、缓存）
6. ↓
7. 存储引擎层（InnoDB、MyISAM、Memory）
8. ↓
9. 物理存储层

复制代码

1.2 存储引擎对比

特性	InnoDB	MyISAM	Memory	Archive
事务支持	✓	×	×	×
行级锁	✓	×	×	×
外键约束	✓	×	×	×
全文索引	✓ (5.6+)	✓	×	×
数据压缩	✓	✓	×	✓
缓存	数据+索引	仅索引	内存	无
适用场景	事务处理	读密集型	临时表	归档

二、数据库基本操作

2.1 数据库管理

1. -- 查看所有数据库
2. SHOW DATABASES;
4. -- 查看数据库创建语句
5. SHOW CREATE DATABASE database_name;
7. -- 创建数据库（指定字符集）
8. CREATE DATABASE mydb
9. CHARACTER SET utf8mb4
10. COLLATE utf8mb4_unicode_ci;
12. -- 选择数据库
13. USE mydb;
15. -- 修改数据库字符集
16. ALTER DATABASE mydb
17. CHARACTER SET utf8mb4
18. COLLATE utf8mb4_unicode_ci;
20. -- 删除数据库
21. DROP DATABASE mydb;

复制代码

2.2 表管理

1. -- 创建表（完整示例）
2. CREATE TABLE employees (
3. id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
4. emp_no VARCHAR(20) NOT NULL UNIQUE,
5. name VARCHAR(100) NOT NULL,
6. gender ENUM('M', 'F') DEFAULT 'M',
7. birth_date DATE NOT NULL,
8. hire_date DATE NOT NULL,
9. salary DECIMAL(10,2) DEFAULT 0.00,
10. department_id INT UNSIGNED,
11. email VARCHAR(100) UNIQUE,
12. phone VARCHAR(20),
13. address TEXT,
14. created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
15. updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
16. ON UPDATE CURRENT_TIMESTAMP,
18. -- 索引
19. INDEX idx_department (department_id),
20. INDEX idx_name (name),
21. INDEX idx_hire_date (hire_date),
23. -- 外键约束
24. CONSTRAINT fk_department
25. FOREIGN KEY (department_id)
26. REFERENCES departments(id)
27. ON DELETE SET NULL ON UPDATE CASCADE,
29. -- 检查约束（MySQL 8.0.16+）
30. CONSTRAINT chk_salary CHECK (salary >= 0)
31. ) ENGINE=InnoDB
32. DEFAULT CHARSET=utf8mb4
33. COLLATE=utf8mb4_unicode_ci
34. COMMENT='员工信息表';
36. -- 查看表结构
37. DESC employees;
38. DESCRIBE employees;
39. SHOW COLUMNS FROM employees;
41. -- 查看建表语句
42. SHOW CREATE TABLE employees;
44. -- 修改表结构
45. -- 添加列
46. ALTER TABLE employees
47. ADD COLUMN nickname VARCHAR(50) AFTER name;
49. -- 修改列
50. ALTER TABLE employees
51. MODIFY COLUMN phone VARCHAR(30) NOT NULL;
53. -- 重命名列
54. ALTER TABLE employees
55. CHANGE COLUMN phone mobile_phone VARCHAR(30);
57. -- 删除列
58. ALTER TABLE employees
59. DROP COLUMN nickname;
61. -- 添加索引
62. ALTER TABLE employees
63. ADD INDEX idx_email (email(50));
65. -- 添加唯一索引
66. ALTER TABLE employees
67. ADD UNIQUE INDEX uk_emp_no (emp_no);
69. -- 删除索引
70. ALTER TABLE employees
71. DROP INDEX idx_email;
73. -- 重命名表
74. RENAME TABLE employees TO staff;
75. ALTER TABLE staff RENAME TO employees;
77. -- 清空表（更快）
78. TRUNCATE TABLE employees;
80. -- 删除表
81. DROP TABLE employees;

复制代码

三、数据操作（CRUD）

3.1 插入数据

1. -- 插入单条数据
2. INSERT INTO employees (emp_no, name, gender, birth_date, hire_date, salary)
3. VALUES ('EMP001', '张三', 'M', '1990-05-15', '2020-03-01', 15000.00);
5. -- 插入多条数据
6. INSERT INTO employees (emp_no, name, gender, birth_date, hire_date, salary) VALUES
7. ('EMP002', '李四', 'F', '1992-08-20', '2019-07-15', 18000.00),
8. ('EMP003', '王五', 'M', '1988-12-10', '2018-11-01', 22000.00),
9. ('EMP004', '赵六', 'F', '1995-03-25', '2021-02-14', 12000.00);
11. -- 插入查询结果
12. INSERT INTO employee_archive (emp_no, name, hire_date, salary)
13. SELECT emp_no, name, hire_date, salary
14. FROM employees
15. WHERE hire_date < '2020-01-01';
17. -- 替换数据（存在则替换）
18. REPLACE INTO employees (id, emp_no, name)
19. VALUES (1, 'EMP001', '张三新名字');
21. -- 插入时忽略错误
22. INSERT IGNORE INTO employees (emp_no, name)
23. VALUES ('EMP001', '张三');
25. -- 从文件导入数据
26. LOAD DATA INFILE '/path/to/data.csv'
27. INTO TABLE employees
28. FIELDS TERMINATED BY ','
29. ENCLOSED BY '"'
30. LINES TERMINATED BY '\n'
31. IGNORE 1 ROWS
32. (emp_no, name, gender, birth_date, hire_date, salary);

复制代码

3.2 查询数据

1. -- 基本查询
2. SELECT * FROM employees;
3. SELECT id, name, salary FROM employees;
4. SELECT DISTINCT department_id FROM employees;
6. -- 条件查询
7. SELECT * FROM employees WHERE salary > 15000;
8. SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2021-12-31';
9. SELECT * FROM employees WHERE name LIKE '张%'; -- 张开头
10. SELECT * FROM employees WHERE name LIKE '%三%'; -- 包含三
11. SELECT * FROM employees WHERE name LIKE '%三'; -- 三结尾
12. SELECT * FROM employees WHERE email IS NOT NULL;
13. SELECT * FROM employees WHERE department_id IN (1, 2, 3);
14. SELECT * FROM employees WHERE (salary > 10000 AND gender = 'M') OR department_id = 1;
16. -- 排序
17. SELECT * FROM employees ORDER BY salary DESC;
18. SELECT * FROM employees ORDER BY hire_date ASC, salary DESC;
20. -- 限制结果
21. SELECT * FROM employees LIMIT 10; -- 前10条
22. SELECT * FROM employees LIMIT 5, 10; -- 从第6条开始，取10条
23. SELECT * FROM employees LIMIT 10 OFFSET 5; -- 同上
25. -- 分组统计
26. SELECT
27. department_id,
28. COUNT(*) AS emp_count,
29. AVG(salary) AS avg_salary,
30. MAX(salary) AS max_salary,
31. MIN(salary) AS min_salary,
32. SUM(salary) AS total_salary
33. FROM employees
34. WHERE hire_date >= '2020-01-01'
35. GROUP BY department_id
36. HAVING COUNT(*) > 5
37. ORDER BY avg_salary DESC;
39. -- 连接查询
40. -- 内连接
41. SELECT
42. e.name AS employee_name,
43. d.name AS department_name,
44. e.salary
45. FROM employees e
46. INNER JOIN departments d ON e.department_id = d.id
47. WHERE d.name = '技术部';
49. -- 左连接
50. SELECT
51. e.name AS employee_name,
52. d.name AS department_name
53. FROM employees e
54. LEFT JOIN departments d ON e.department_id = d.id;
56. -- 右连接
57. SELECT
58. e.name AS employee_name,
59. d.name AS department_name
60. FROM employees e
61. RIGHT JOIN departments d ON e.department_id = d.id;
63. -- 自连接
64. SELECT
65. e1.name AS employee,
66. e2.name AS manager
67. FROM employees e1
68. LEFT JOIN employees e2 ON e1.manager_id = e2.id;
70. -- 子查询
71. -- WHERE 子查询
72. SELECT * FROM employees
73. WHERE salary > (SELECT AVG(salary) FROM employees);
75. -- FROM 子查询
76. SELECT dept_id, avg_sal FROM (
77. SELECT department_id AS dept_id, AVG(salary) AS avg_sal
78. FROM employees
79. GROUP BY department_id
80. ) AS temp
81. WHERE avg_sal > 15000;
83. -- SELECT 子查询
84. SELECT
85. e.name,
86. e.salary,
87. (SELECT AVG(salary) FROM employees) AS company_avg_salary
88. FROM employees e;
90. -- EXISTS 子查询
91. SELECT * FROM departments d
92. WHERE EXISTS (
93. SELECT 1 FROM employees e
94. WHERE e.department_id = d.id AND e.salary > 20000
95. );
97. -- 联合查询
98. SELECT name, salary, '员工' AS type FROM employees
99. UNION
100. SELECT dept_name, budget, '部门' AS type FROM departments
101. ORDER BY salary DESC;

复制代码

3.3 更新数据

1. -- 更新单条
2. UPDATE employees
3. SET salary = salary * 1.1,
4. updated_at = NOW()
5. WHERE id = 1;
7. -- 批量更新
8. UPDATE employees
9. SET salary = salary * 1.05
10. WHERE department_id = 1
11. AND hire_date < '2020-01-01';
13. -- 多表更新
14. UPDATE employees e
15. JOIN departments d ON e.department_id = d.id
16. SET e.salary = e.salary * 1.1
17. WHERE d.name = '技术部';
19. -- 使用子查询更新
20. UPDATE employees
21. SET salary = salary + 1000
22. WHERE id IN (
23. SELECT id FROM (
24. SELECT id FROM employees
25. WHERE performance_score > 90
26. ) AS tmp
27. );

复制代码

3.4 删除数据

1. -- 删除指定数据
2. DELETE FROM employees WHERE id = 1;
4. -- 批量删除
5. DELETE FROM employees WHERE hire_date < '2010-01-01';
7. -- 多表删除
8. DELETE e, d
9. FROM employees e
10. JOIN departments d ON e.department_id = d.id
11. WHERE d.status = 'inactive';
13. -- 使用子查询删除
14. DELETE FROM employees
15. WHERE department_id IN (
16. SELECT id FROM departments WHERE status = 'closed'
17. );
19. -- 清空表（不可恢复）
20. TRUNCATE TABLE employees;

复制代码

四、索引管理

4.1 索引类型

1. -- 主键索引
2. CREATE TABLE users (
3. id INT PRIMARY KEY AUTO_INCREMENT,
4. name VARCHAR(50)
5. );
7. -- 唯一索引
8. CREATE UNIQUE INDEX uk_email ON users(email);
10. -- 普通索引
11. CREATE INDEX idx_name ON users(name);
12. CREATE INDEX idx_name_dob ON users(name, birth_date);
14. -- 全文索引
15. CREATE FULLTEXT INDEX ft_content ON articles(content);
17. -- 空间索引
18. CREATE SPATIAL INDEX sp_location ON locations(coordinates);
20. -- 前缀索引
21. CREATE INDEX idx_name_prefix ON users(name(20));
23. -- 组合索引
24. CREATE INDEX idx_composite ON orders(user_id, order_date, status);

复制代码

4.2 索引管理命令

1. -- 查看索引
2. SHOW INDEX FROM users;
3. SHOW INDEXES FROM users;
4. SHOW KEYS FROM users;
6. -- 分析索引使用
7. ANALYZE TABLE users;
8. EXPLAIN SELECT * FROM users WHERE name = '张三';
10. -- 优化表（重建索引）
11. OPTIMIZE TABLE users;
13. -- 强制使用索引
14. SELECT * FROM users
15. FORCE INDEX (idx_name)
16. WHERE name LIKE '张%';
18. -- 忽略索引
19. SELECT * FROM users
20. IGNORE INDEX (idx_name)
21. WHERE name = '张三';
23. -- 重建索引
24. ALTER TABLE users DROP INDEX idx_name;
25. ALTER TABLE users ADD INDEX idx_name (name);

复制代码

五、事务管理

5.1 事务基本操作

1. -- 开启事务
2. START TRANSACTION;
3. -- 或
4. BEGIN;
5. -- 或
6. BEGIN WORK;
8. -- 提交事务
9. COMMIT;
11. -- 回滚事务
12. ROLLBACK;
14. -- 保存点
15. START TRANSACTION;
16. INSERT INTO table1 VALUES (1, 'test');
17. SAVEPOINT sp1;
18. INSERT INTO table2 VALUES (1, 'test');
19. ROLLBACK TO SAVEPOINT sp1; -- 回滚到保存点
20. COMMIT;
22. -- 设置事务隔离级别
23. SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
24. START TRANSACTION;
25. -- 事务操作
26. COMMIT;

复制代码

5.2 事务隔离级别

1. -- 查看隔离级别
2. SELECT @@transaction_isolation;
3. SELECT @@global.transaction_isolation;
4. SELECT @@session.transaction_isolation;
6. -- 设置隔离级别
7. -- 1. READ UNCOMMITTED（读未提交）
8. -- 2. READ COMMITTED（读已提交）
9. -- 3. REPEATABLE READ（可重复读）- MySQL默认
10. -- 4. SERIALIZABLE（串行化）
12. -- 会话级别
13. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
15. -- 全局级别
16. SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
18. -- 在my.cnf中设置
19. [mysqld]
20. transaction-isolation = READ-COMMITTED

复制代码

六、存储过程与函数

6.1 存储过程

1. -- 创建存储过程
2. DELIMITER $$
4. CREATE PROCEDURE GetEmployeeCountByDept(
5. IN dept_id INT,
6. OUT emp_count INT
7. )
8. BEGIN
9. DECLARE v_count INT DEFAULT 0;
11. -- 业务逻辑
12. SELECT COUNT(*) INTO v_count
13. FROM employees
14. WHERE department_id = dept_id;
16. -- 输出参数
17. SET emp_count = v_count;
19. -- 返回结果集
20. SELECT * FROM employees
21. WHERE department_id = dept_id;
22. END$$
24. DELIMITER ;
26. -- 调用存储过程
27. CALL GetEmployeeCountByDept(1, @count);
28. SELECT @count;
30. -- 删除存储过程
31. DROP PROCEDURE IF EXISTS GetEmployeeCountByDept;
33. -- 查看存储过程
34. SHOW PROCEDURE STATUS;
35. SHOW CREATE PROCEDURE GetEmployeeCountByDept;

复制代码

6.2 函数

1. -- 创建函数
2. DELIMITER $$
4. CREATE FUNCTION CalculateBonus(
5. salary DECIMAL(10,2),
6. performance_rate DECIMAL(5,2)
7. ) RETURNS DECIMAL(10,2)
8. DETERMINISTIC
9. READS SQL DATA
10. BEGIN
11. DECLARE bonus DECIMAL(10,2);
13. IF performance_rate >= 1.2 THEN
14. SET bonus = salary * 0.3;
15. ELSEIF performance_rate >= 1.0 THEN
16. SET bonus = salary * 0.2;
17. ELSE
18. SET bonus = salary * 0.1;
19. END IF;
21. RETURN ROUND(bonus, 2);
22. END$$
24. DELIMITER ;
26. -- 使用函数
27. SELECT
28. name,
29. salary,
30. CalculateBonus(salary, 1.5) AS bonus
31. FROM employees;
33. -- 查看函数
34. SHOW FUNCTION STATUS;
35. SHOW CREATE FUNCTION CalculateBonus;

复制代码

七、触发器

1. -- 创建触发器
2. DELIMITER $$
4. CREATE TRIGGER before_employee_update
5. BEFORE UPDATE ON employees
6. FOR EACH ROW
7. BEGIN
8. -- 记录修改历史
9. INSERT INTO employee_history (
10. employee_id,
11. old_salary,
12. new_salary,
13. change_time
14. ) VALUES (
15. OLD.id,
16. OLD.salary,
17. NEW.salary,
18. NOW()
19. );
21. -- 自动更新修改时间
22. SET NEW.updated_at = NOW();
23. END$$
25. DELIMITER ;
27. -- 创建审计触发器
28. CREATE TRIGGER audit_employee_changes
29. AFTER INSERT OR UPDATE OR DELETE ON employees
30. FOR EACH ROW
31. BEGIN
32. DECLARE action_type VARCHAR(10);
34. IF INSERTING THEN
35. SET action_type = 'INSERT';
36. INSERT INTO audit_log (
37. table_name,
38. record_id,
39. action,
40. old_data,
41. new_data,
42. changed_by,
43. change_time
44. ) VALUES (
45. 'employees',
46. NEW.id,
47. action_type,
48. NULL,
49. JSON_OBJECT(
50. 'name', NEW.name,
51. 'salary', NEW.salary
52. ),
53. USER(),
54. NOW()
55. );
56. ELSEIF UPDATING THEN
57. SET action_type = 'UPDATE';
58. INSERT INTO audit_log (
59. table_name,
60. record_id,
61. action,
62. old_data,
63. new_data,
64. changed_by,
65. change_time
66. ) VALUES (
67. 'employees',
68. OLD.id,
69. action_type,
70. JSON_OBJECT(
71. 'name', OLD.name,
72. 'salary', OLD.salary
73. ),
74. JSON_OBJECT(
75. 'name', NEW.name,
76. 'salary', NEW.salary
77. ),
78. USER(),
79. NOW()
80. );
81. ELSEIF DELETING THEN
82. SET action_type = 'DELETE';
83. INSERT INTO audit_log (
84. table_name,
85. record_id,
86. action,
87. old_data,
88. new_data,
89. changed_by,
90. change_time
91. ) VALUES (
92. 'employees',
93. OLD.id,
94. action_type,
95. JSON_OBJECT(
96. 'name', OLD.name,
97. 'salary', OLD.salary
98. ),
99. NULL,
100. USER(),
101. NOW()
102. );
103. END IF;
104. END$$
106. DELIMITER ;
108. -- 查看触发器
109. SHOW TRIGGERS;
110. SHOW CREATE TRIGGER before_employee_update;
112. -- 删除触发器
113. DROP TRIGGER IF EXISTS before_employee_update;

复制代码

八、用户与权限管理

8.1 用户管理

1. -- 创建用户
2. CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
3. CREATE USER 'username'@'%' IDENTIFIED BY 'password'; -- 允许远程
4. CREATE USER 'username'@'192.168.1.%' IDENTIFIED BY 'password'; -- 指定IP段
6. -- 修改用户名
7. RENAME USER 'old_user'@'localhost' TO 'new_user'@'localhost';
9. -- 修改密码
10. ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';
12. -- MySQL 8.0+
13. ALTER USER 'username'@'localhost'
14. IDENTIFIED WITH mysql_native_password BY 'password';
16. -- 删除用户
17. DROP USER 'username'@'localhost';
19. -- 查看用户
20. SELECT user, host FROM mysql.user;
21. SELECT * FROM mysql.user WHERE user = 'username';
23. -- 查看当前用户
24. SELECT CURRENT_USER();
25. SELECT USER();

复制代码

8.2 权限管理

1. -- 授予权限
2. -- 语法：GRANT 权限 ON 数据库.表 TO 用户@主机
4. -- 授予所有权限
5. GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
7. -- 授予数据库所有权限
8. GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'localhost';
10. -- 授予特定表权限
11. GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.employees TO 'user'@'localhost';
13. -- 授予列级权限
14. GRANT SELECT (id, name), UPDATE (name) ON mydb.employees TO 'user'@'localhost';
16. -- 授予存储过程权限
17. GRANT EXECUTE ON PROCEDURE mydb.GetEmployeeCount TO 'user'@'localhost';
19. -- 创建角色
20. CREATE ROLE 'read_only', 'read_write', 'admin';
22. -- 为角色授权
23. GRANT SELECT ON mydb.* TO 'read_only';
24. GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'read_write';
25. GRANT ALL PRIVILEGES ON *.* TO 'admin';
27. -- 将角色授予用户
28. GRANT 'read_only' TO 'user'@'localhost';
29. SET DEFAULT ROLE 'read_only' TO 'user'@'localhost';
31. -- 查看权限
32. SHOW GRANTS FOR 'user'@'localhost';
33. SELECT * FROM mysql.user WHERE user = 'username';
34. SELECT * FROM mysql.db WHERE user = 'username';
36. -- 查看当前用户权限
37. SHOW GRANTS;
39. -- 撤销权限
40. REVOKE INSERT ON mydb.* FROM 'user'@'localhost';
42. -- 刷新权限
43. FLUSH PRIVILEGES;

复制代码

九、备份与恢复

9.1 备份方法

1. # 1. mysqldump 逻辑备份
2. # 备份整个数据库
3. mysqldump -u root -p --all-databases > backup.sql
5. # 备份指定数据库
6. mysqldump -u root -p mydb > mydb_backup.sql
8. # 备份指定表
9. mysqldump -u root -p mydb employees departments > tables_backup.sql
11. # 备份结构
12. mysqldump -u root -p --no-data mydb > mydb_structure.sql
14. # 备份数据
15. mysqldump -u root -p --no-create-info mydb > mydb_data.sql
17. # 压缩备份
18. mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz
20. # 2. 二进制日志备份
21. # 查看当前二进制日志
22. SHOW BINARY LOGS;
23. SHOW MASTER STATUS;
25. # 备份二进制日志
26. mysqlbinlog mysql-bin.000001 > binlog_backup.sql
28. # 3. 物理备份（InnoDB）
29. # 使用Percona XtraBackup
30. innobackupex --user=root --password=xxx /backup/

复制代码

9.2 恢复方法

1. # 恢复整个数据库
2. mysql -u root -p < backup.sql
4. # 恢复指定数据库
5. mysql -u root -p mydb < mydb_backup.sql
7. # 恢复压缩备份
8. gunzip < mydb_backup.sql.gz | mysql -u root -p mydb
10. # 恢复二进制日志
11. mysqlbinlog mysql-bin.000001 | mysql -u root -p
13. # 从特定位置恢复
14. mysqlbinlog --start-position=107 mysql-bin.000001 | mysql -u root -p

复制代码

十、性能优化

10.1 查询优化

1. -- 使用EXPLAIN分析查询
2. EXPLAIN SELECT * FROM employees WHERE name = '张三';
4. -- 更详细的执行计划
5. EXPLAIN FORMAT=JSON
6. SELECT * FROM employees WHERE name = '张三';
8. -- 分析查询性能
9. EXPLAIN ANALYZE
10. SELECT * FROM employees WHERE name = '张三';
12. -- 优化建议
13. -- 1. 避免SELECT *
14. SELECT id, name, salary FROM employees;
16. -- 2. 使用索引覆盖
17. CREATE INDEX idx_covering ON employees(department_id, salary, name);
18. SELECT department_id, salary, name FROM employees
19. WHERE department_id = 1;
21. -- 3. 避免在WHERE子句中使用函数
22. -- 不推荐
23. SELECT * FROM employees WHERE YEAR(hire_date) = 2023;
24. -- 推荐
25. SELECT * FROM employees
26. WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';
28. -- 4. 使用LIMIT
29. SELECT * FROM employees ORDER BY id LIMIT 1000;
31. -- 5. 批量操作
32. INSERT INTO employees (name, salary) VALUES
33. ('张三', 10000),
34. ('李四', 12000),
35. ('王五', 15000);

复制代码

10.2 索引优化

1. -- 查看索引使用情况
2. SELECT
3. table_name,
4. index_name,
5. stat_value * @@innodb_page_size / 1024 / 1024 AS index_size_mb
6. FROM mysql.innodb_index_stats
7. WHERE database_name = 'mydb';
9. -- 查找未使用的索引
10. SELECT
11. object_schema,
12. object_name,
13. index_name
14. FROM performance_schema.table_io_waits_summary_by_index_usage
15. WHERE index_name IS NOT NULL
16. AND count_star = 0
17. AND object_schema NOT IN ('mysql', 'sys', 'performance_schema');
19. -- 删除冗余索引
20. -- 查找(a,b)和(a)的索引，删除(a)
21. SELECT
22. a.TABLE_SCHEMA,
23. a.TABLE_NAME,
24. a.INDEX_NAME AS redundant_index,
25. b.INDEX_NAME AS dominant_index
26. FROM information_schema.STATISTICS a
27. JOIN information_schema.STATISTICS b
28. WHERE a.TABLE_SCHEMA = b.TABLE_SCHEMA
29. AND a.TABLE_NAME = b.TABLE_NAME
30. AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
31. AND a.COLUMN_NAME = b.COLUMN_NAME
32. AND a.INDEX_NAME != b.INDEX_NAME
33. AND a.NON_UNIQUE = 1
34. AND b.NON_UNIQUE = 1
35. AND a.INDEX_NAME LIKE 'idx%';

复制代码

十一、监控与诊断

11.1 状态查看

1. -- 查看服务器状态
2. SHOW STATUS;
3. SHOW GLOBAL STATUS;
4. SHOW SESSION STATUS;
6. -- 查看变量
7. SHOW VARIABLES;
8. SHOW GLOBAL VARIABLES LIKE '%buffer%';
10. -- 查看进程列表
11. SHOW PROCESSLIST;
12. SHOW FULL PROCESSLIST;
14. -- 查看锁信息
15. SHOW ENGINE INNODB STATUS;
16. SELECT * FROM information_schema.INNODB_LOCKS;
17. SELECT * FROM information_schema.INNODB_LOCK_WAITS;
19. -- 查看表状态
20. SHOW TABLE STATUS LIKE 'employees';
21. ANALYZE TABLE employees; -- 更新统计信息

复制代码

11.2 性能监控

1. -- 慢查询日志
2. -- 在my.cnf中配置
3. slow_query_log = 1
4. slow_query_log_file = /var/log/mysql/slow.log
5. long_query_time = 2
6. log_queries_not_using_indexes = 1
8. -- 查看慢查询
9. SHOW VARIABLES LIKE 'slow_query_log%';
10. SHOW VARIABLES LIKE 'long_query_time';
12. -- 使用performance_schema
13. SELECT * FROM performance_schema.events_statements_summary_by_digest
14. ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
16. -- 查看等待事件
17. SELECT
18. event_name,
19. count_star,
20. sum_timer_wait/1000000000 as total_wait_sec,
21. avg_timer_wait/1000000000 as avg_wait_sec
22. FROM performance_schema.events_waits_summary_global_by_event_name
23. WHERE count_star > 0
24. ORDER BY sum_timer_wait DESC LIMIT 10;

复制代码

十二、高级功能

12.1 窗口函数

1. -- 排名函数
2. SELECT
3. name,
4. salary,
5. department_id,
6. ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
7. RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_num,
8. DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank_num
9. FROM employees;
11. -- 聚合窗口函数
12. SELECT
13. name,
14. salary,
15. department_id,
16. AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
17. SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS running_total,
18. FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_max_salary
19. FROM employees;
21. -- 移动平均
22. SELECT
23. order_date,
24. amount,
25. AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
26. FROM orders;

复制代码

12.2 JSON操作

1. -- 创建JSON字段
2. CREATE TABLE products (
3. id INT PRIMARY KEY,
4. name VARCHAR(100),
5. attributes JSON,
6. metadata JSON
7. );
9. -- 插入JSON数据
10. INSERT INTO products VALUES (
11. 1,
12. '手机',
13. '{"brand": "Apple", "color": "black", "storage": 128}',
14. '{"tags": ["new", "hot"], "rating": 4.5}'
15. );
17. -- 查询JSON
18. SELECT
19. name,
20. attributes->'$.brand' AS brand,
21. JSON_EXTRACT(attributes, '$.color') AS color,
22. metadata->'$.rating' AS rating
23. FROM products;
25. -- JSON路径查询
26. SELECT *
27. FROM products
28. WHERE JSON_EXTRACT(attributes, '$.brand') = 'Apple';
30. -- 更新JSON
31. UPDATE products
32. SET attributes = JSON_SET(attributes, '$.price', 6999)
33. WHERE id = 1;
35. -- JSON函数
36. SELECT
37. JSON_TYPE(attributes) AS type,
38. JSON_LENGTH(attributes) AS length,
39. JSON_KEYS(attributes) AS keys,
40. JSON_VALID(attributes) AS is_valid
41. FROM products;

复制代码

十三、维护命令

13.1 数据库维护

1. -- 优化表
2. OPTIMIZE TABLE employees;
3. OPTIMIZE LOCAL TABLE employees; -- 在线优化
5. -- 修复表
6. REPAIR TABLE employees;
7. REPAIR TABLE employees QUICK;
9. -- 检查表
10. CHECK TABLE employees;
11. CHECK TABLE employees FAST;
12. CHECK TABLE employees CHANGED;
14. -- 分析表
15. ANALYZE TABLE employees;
16. ANALYZE LOCAL TABLE employees;
18. -- 更新统计信息
19. ANALYZE TABLE employees UPDATE HISTOGRAM ON salary WITH 100 BUCKETS;
21. -- 重建表
22. ALTER TABLE employees ENGINE=InnoDB;

复制代码

13.2 系统维护

1. # 导出数据
2. mysqldump -u root -p mydb employees > employees.sql
4. # 导入数据
5. mysql -u root -p mydb < employees.sql
7. # 批量执行SQL
8. mysql -u root -p -e "SHOW DATABASES; SHOW TABLES;"
10. # 定时备份脚本
11. #!/bin/bash
12. BACKUP_DIR="/backup/mysql"
13. DATE=$(date +%Y%m%d_%H%M%S)
14. mysqldump -u root -p'password' --all-databases | gzip > $BACKUP_DIR/backup_$DATE.sql.gz
15. # 保留最近7天备份
16. find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

复制代码

十四、安全最佳实践

14.1 安全设置

1. -- 删除匿名用户
2. DELETE FROM mysql.user WHERE User = '';
3. FLUSH PRIVILEGES;
5. -- 限制root远程登录
6. DELETE FROM mysql.user WHERE User = 'root' AND Host NOT IN ('localhost', '127.0.0.1');
7. FLUSH PRIVILEGES;
9. -- 创建应用程序专用用户
10. CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPassword123!';
11. GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'192.168.1.%';
13. -- 定期更改密码
14. ALTER USER 'app_user'@'192.168.1.%' PASSWORD EXPIRE INTERVAL 90 DAY;
16. -- 查看用户权限
17. SHOW GRANTS FOR 'app_user'@'192.168.1.%';
19. -- 设置密码策略
20. SET GLOBAL validate_password.policy = 2; -- STRONG
21. SET GLOBAL validate_password.length = 12;
22. SET GLOBAL validate_password.number_count = 2;
23. SET GLOBAL validate_password.special_char_count = 1;

复制代码

十五、故障排查

15.1 常见问题解决

1. -- 1. 连接数过多
2. SHOW PROCESSLIST;
3. SHOW STATUS LIKE 'Threads_connected';
4. SHOW VARIABLES LIKE 'max_connections';
6. -- 临时增加连接数
7. SET GLOBAL max_connections = 1000;
9. -- 2. 死锁检查
10. SHOW ENGINE INNODB STATUS;
11. SELECT * FROM information_schema.INNODB_LOCKS;
12. SELECT * FROM information_schema.INNODB_LOCK_WAITS;
14. -- 3. 慢查询分析
15. SHOW VARIABLES LIKE 'slow_query_log%';
16. SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;
18. -- 4. 表损坏修复
19. CHECK TABLE employees;
20. REPAIR TABLE employees;
22. -- 5. 重置root密码
23. # 停止MySQL服务
24. # 启动跳过授权
25. mysqld_safe --skip-grant-tables &
26. # 修改密码
27. ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
28. # 重启服务

复制代码

十六、实用脚本示例

16.1 监控脚本

1. -- 数据库健康检查
2. SELECT
3. VARIABLE_NAME,
4. VARIABLE_VALUE,
5. CASE
6. WHEN VARIABLE_NAME IN ('uptime', 'threads_connected')
7. THEN VARIABLE_VALUE
8. WHEN VARIABLE_NAME = 'max_connections'
9. THEN CONCAT(VARIABLE_VALUE, ' (当前使用率: ',
10. ROUND((SELECT VARIABLE_VALUE
11. FROM information_schema.GLOBAL_STATUS
12. WHERE VARIABLE_NAME = 'Threads_connected') / VARIABLE_VALUE * 100, 2), '%)')
13. ELSE VARIABLE_VALUE
14. END AS status
15. FROM information_schema.GLOBAL_STATUS
16. WHERE VARIABLE_NAME IN (
17. 'uptime',
18. 'threads_connected',
19. 'max_connections',
20. 'innodb_buffer_pool_pages_free',
21. 'questions',
22. 'slow_queries'
23. )
24. UNION ALL
25. SELECT
26. 'buffer_pool_hit_rate',
27. '',
28. CONCAT(
29. ROUND((1 -
30. (SELECT VARIABLE_VALUE
31. FROM information_schema.GLOBAL_STATUS
32. WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
33. (SELECT VARIABLE_VALUE
34. FROM information_schema.GLOBAL_STATUS
35. WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
36. ) * 100, 2), '%'
37. )
38. FROM DUAL;

复制代码

总结

使用建议：

1. 开发规范：

   • 使用InnoDB存储引擎
   • 为每张表设置主键
   • 使用utf8mb4字符集
   • 为频繁查询的列创建索引
   • 避免在数据库中存储大文件

2. 性能优化：

   • 定期分析慢查询
   • 监控连接数和缓冲区使用
   • 定期优化和修复表
   • 使用连接池

3. 安全建议：

   • 使用强密码
   • 定期备份
   • 限制远程访问
   • 定期审计权限

4. 维护计划：

   • 每日监控关键指标
   • 每周备份
   • 每月性能分析
   • 每季度安全审计