MySQL性能优化实用技巧：从查询分析到数据安全36条

前天分享了50条MySQL常用脚本，有小伙伴说怎么突然这么卷了？实际上我一直都在学习，只是希望这些知识能被更多人看到。

今天，我们继续分享一系列在MySQL日常开发与运维中非常实用的技巧，覆盖数据操作、性能分析、高级功能及系统维护等多个方面。

1、使用REPLACE INTO来插入或替换数据，避免先删除再插入的操作。

REPLACE INTO table_name (id, name) VALUES (1, 'John');

说明：如果id为1的记录已存在，则该记录将被更新；否则，将插入一条新记录。这种方法避免了先删除再插入的过程。

2、通过SET GLOBAL sql_mode=’’来临时禁用SQL严格模式。

SET GLOBAL sql_mode='';

说明：这将在当前会话中禁用SQL严格模式，使得一些非标准的SQL语句能够执行。

3、利用EXPLAIN语句来分析查询性能，找出潜在的性能瓶颈。

EXPLAIN SELECT * FROM table_name WHERE id = 1;

说明：EXPLAIN语句可帮助你了解查询的执行计划，是MySQL查询优化的核心工具。

4、使用LOAD DATA INFILE快速导入大量数据。

LOAD DATA INFILE 'file_path' INTO TABLE table_name;

说明：通过LOAD DATA INFILE可以快速地将文件中的数据导入数据库表中，适用于大量数据的导入操作，效率远高于INSERT。

5、利用分区表来优化大表的查询性能。

CREATE TABLE sales (
id INT NOT NULL,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (2010),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

说明：通过分区表可以将大表拆分为更小的部分，提高查询效率，尤其是针对时间范围查询时效果显著。

6、使用批量插入来提高插入数据的效率。

INSERT INTO table_name (id, name) VALUES
(1, '张三'),
(2, '李四'),
(3, '王五');

说明：一次性插入多行数据可以减少网络往返和SQL解析的开销，极大地提高插入效率。

7、利用用户自定义变量简化复杂查询。

SET @min_salary = 50000;
SELECT name, salary FROM employees WHERE salary > @min_salary;

说明：通过自定义变量可以在查询中使用变量，简化复杂查询的编写和维护。

8、通过分析表的索引使用情况来优化查询性能。

SHOW INDEX FROM table_name;

说明：查看表的索引情况可以帮助你评估哪些索引被查询使用，哪些索引可以优化或重建。

9、使用MySQL事件调度器来执行定时任务。

CREATE EVENT my_event
ON SCHEDULE EVERY 1 HOUR
DO
UPDATE table_name SET status = ‘expired’
WHERE expiration_date < NOW();

说明：通过事件调度器可以定时执行MySQL语句，用于自动化执行数据清理、报表生成等任务。

10、利用全文索引提高搜索效率。

CREATE FULLTEXT INDEX idx_title ON articles(title);
SELECT * FROM articles
WHERE MATCH(title) AGAINST(‘search keyword’);

说明：全文索引可加速对文本内容的模糊搜索，相比 LIKE ‘%keyword%’ 有质的飞跃。

11、使用ON DUPLICATE KEY UPDATE来插入新数据或更新现有数据。

INSERT INTO table_name (id, name) VALUES (1, '张三') ON DUPLICATE KEY UPDATE name = '张三';

说明：如果插入数据的主键或唯一键已存在，将会执行更新操作而不是插入新数据，常用于“有则更新，无则插入”的场景。

12、利用VALUES()函数来在INSERT语句中引用插入的值。

INSERT INTO table_name (id, name) VALUES (1, 'Alice'), (2, VALUES(name));

说明：VALUES()函数可以引用同一INSERT语句中正在插入的列值，方便实现一次插入多个记录且其中某些值相同的需求。

13、使用SHOW PROFILE来查看查询的性能分析结果。

SET PROFILING = 1;
SELECT * FROM table_name;
SHOW PROFILES;

说明：SHOW PROFILE可以显示查询执行的详细性能信息（如各阶段耗时），帮助深入优化查询。

14、利用TRIGGERS来触发特定操作。

CREATE TRIGGER before_insert_trigger
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
SET NEW.date_created = NOW();
END;

说明：TRIGGERS可用于在特定事件（INSERT/UPDATE/DELETE）发生时自动执行一些操作，如自动填充时间戳、维护审计日志等。

15、使用MEMORY引擎来创建内存表，加快某些小型数据的访问速度。

CREATE TABLE memory_table ENGINE=MEMORY AS SELECT * FROM table_name;

说明：MEMORY引擎将表完全保存在内存中，适用于缓存或临时性数据的查询，速度极快，但服务器重启后数据会丢失。

16、使用ROW_NUMBER()函数模拟MySQL中的行号功能。

SELECT
    @rownumber := @rownumber + 1 AS rownumber,
    col1, col2
FROM
    table_name, (SELECT @rownumber := 0) AS t;

说明：通过设置用户变量并自增来模拟行号功能，可以在结果集中为每行分配一个唯一的序号。

17、利用IGNORE选项忽略插入数据中的重复值。

INSERT IGNORE INTO table_name (id, name) VALUES (1, '张三'), (1, '李四');

说明：当插入数据中有重复的主键或唯一键值时，使用IGNORE选项可以忽略重复值而继续插入其他数据，避免语句因错误而中断。

18、使用BIN()函数对字段进行二进制字符串转换。

SELECT BIN(10); -- 输出 ‘1010’

说明：BIN()函数可以将数字转换为二进制字符串表示形式。

19、利用GROUP_CONCAT()函数将多行数据合并成一行并用特定分隔符分隔。

SELECT id, GROUP_CONCAT(name SEPARATOR ‘, ‘) AS names FROM table_name GROUP BY id;

说明：GROUP_CONCAT()函数可以将分组内的多行数据合并成一行，并通过指定的分隔符进行连接，常用于生成逗号分隔的列表。

20、使用JSON数据类型来存储和查询JSON格式的数据。

CREATE TABLE json_data (
id INT PRIMARY KEY,
data JSON
);
INSERT INTO json_data (id, data) VALUES (1, ‘{“name”: “张三”, “age”: 30}');
SELECT data->’$.name’ AS name FROM json_data WHERE id = 1;

说明：JSON数据类型适用于存储和处理具有半结构化格式的数据，提供了方便的JSON路径查询和函数操作。

21、使用CHECK约束实现数据完整性验证。

CREATE TABLE employees (
id INT PRIMARY KEY,
    age INT,
CHECK (age >= 18)
);

说明：CHECK约束可确保插入或更新的数据符合特定条件（如年龄大于等于18），从数据库层面保证数据完整性。

22、利用EXISTS子查询来判断子查询是否返回结果。

SELECT * FROM table_name t WHERE EXISTS (SELECT 1 FROM other_table o WHERE o.id = t.id);

说明：利用EXISTS子查询可以根据子查询是否返回结果来筛选主查询的结果集，通常用于关联性检查。

23、使用RAND()函数生成随机数。

SELECT RAND(); -- 返回一个0到1之间的随机数

说明：RAND()函数可用于生成随机数，适用于需要随机排序、抽样等场景。

24、利用ENUM数据类型限制列的取值范围。

CREATE TABLE students (
id INT PRIMARY KEY,
    gender ENUM(‘Male’, ‘Female’)
);

说明：ENUM数据类型可以定义列的可选取值列表，严格限制输入的数据必须在预定义的范围内。

25、使用DATE_FORMAT()函数将日期格式化为指定的字符串形式。

SELECT DATE_FORMAT(‘2024-04-19 12:00:00’, ‘%Y-%m-%d’); -- 输出 ‘2024-04-19’

说明：DATE_FORMAT()函数可以将日期时间类型格式化为指定的字符串形式，方便进行日期显示和报表处理。

26、利用SUBSTRING_INDEX函数提取字符串中的子串。

SELECT SUBSTRING_INDEX(‘apple,banana,orange’, ‘,’, 2);
-- 输出 ‘apple,banana’

说明：SUBSTRING_INDEX函数可以根据指定的分隔符，提取字符串中第N次出现分隔符之前或之后的所有字符。

27、使用CONVERT()函数进行数据类型转换。

SELECT CONVERT(‘123’, SIGNED);
-- 将字符串’123’转换为有符号整数

说明：CONVERT()函数可用于将数据从一种类型显式转换为另一种类型，确保运算或比较的正确性。

28、使用CASE语句实现条件逻辑。

SELECT
CASE
WHEN marks >= 90 THEN ‘A’
WHEN marks >= 80 THEN ‘B’
ELSE ‘C’
END AS grade
FROM student_marks;

说明：CASE语句可根据条件执行不同的逻辑，返回不同的值，类似于编程语言中的if-else或switch语句。

29、利用HAVING子句过滤聚合函数结果。

SELECT department, AVG(salary) as avg_salary FROM employees GROUP BY department HAVING avg_salary > 50000;

说明：HAVING子句用于在执行GROUP BY分组后，对聚合函数（如AVG, SUM）的结果进行过滤，而WHERE子句用于分组前的行过滤。

30、使用LIMIT和OFFSET分页查询结果集。

SELECT * FROM products LIMIT 10 OFFSET 20;

说明：LIMIT用于限制结果集返回的行数，OFFSET用于指定起始位置（跳过多少行），是实现分页查询的基础。

31、使用TRANSACTION来确保一组SQL操作要么全部执行成功，要么都不执行。

START TRANSACTION;
INSERT INTO table1 (col1, col2) VALUES (val1, val2);
UPDATE table2 SET col3 = val3 WHERE col4 = val4;
COMMIT;

说明：TRANSACTION（事务）可以保证一组SQL操作的原子性，如果其中任何一个操作失败，则整个操作将回滚，数据保持一致状态。

32、使用临时表临时存储中间结果。

CREATE TEMPORARY TABLE temp_table AS SELECT * FROM orders WHERE order_date = ‘2024-01-01’;

说明：临时表仅在当前数据库会话中存在，会话结束时自动删除，适合存储复杂的查询中间结果，避免重复计算。

33、使用存储点在事务中实现部分回滚。

START TRANSACTION;
INSERT INTO table1 VALUES (1, ‘A’);
SAVEPOINT sp1;
INSERT INTO table2 VALUES (2, ‘B’);
ROLLBACK TO sp1;
COMMIT;

说明：存储点（Savepoint）允许你将一个长事务分段，并可以回滚到某个存储点，而不必回滚整个事务，提供了更灵活的错误处理机制。

34、使用MySQL的自动递增主键来自动生成唯一标识符。

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(50)
);

说明：将主键字段设置为AUTO_INCREMENT，数据库会自动为每行新数据生成一个唯一且递增的ID，简化了应用层ID生成逻辑。

35、使用备份和恢复策略来保障数据的安全性和可用性。

mysqldump -u[username] -p [database_name] > backup.sql
mysql -u[username] -p [database_name] < backup.sql

说明：定期进行逻辑备份（如使用mysqldump）并测试恢复流程，是防止数据丢失或损坏、保障业务连续性的重要措施。

36、监控数据库的系统资源利用情况。

SHOW GLOBAL STATUS LIKE ‘Threads_connected’;
SHOW ENGINE INNODB STATUS;

说明：监控连接数、InnoDB状态等系统资源利用情况，可以帮助你发现潜在的性能瓶颈（如连接池耗尽、锁争用），并及时调整配置以优化数据库性能。

掌握这些技巧，能让你在开发和管理MySQL数据库时更加得心应手。无论是优化一个慢查询，还是设计一个健壮的数据存储方案，这些点滴的知识积累都将发挥作用。技术之路在于持续学习和实践，希望这些分享能对你有所帮助。