通用企业手机网站模板,人才网网站模板,网站备案用户名忘了怎么办,厦门网站建设方案服务SQL性能优化
在日常开发中#xff0c;MySQL性能优化是一项必不可少的技能。本文以具体案例为主线#xff0c;结合实际问题#xff0c;探讨如何优化插入、排序、分组、分页、计数和更新等操作#xff0c;帮助你实现数据库性能的飞跃。 一、索引设计原则
索引是MySQL优化的…SQL性能优化
在日常开发中MySQL性能优化是一项必不可少的技能。本文以具体案例为主线结合实际问题探讨如何优化插入、排序、分组、分页、计数和更新等操作帮助你实现数据库性能的飞跃。 一、索引设计原则
索引是MySQL优化的核心合理的索引设计能显著提升性能。以下为关键原则 二、insert 优化
2.1 批量插入
与单条插入相比批量插入大幅减少网络开销和SQL解析成本
-- 单条插入
INSERT INTO users (name, age) VALUES (Alice, 25);-- 批量插入
INSERT INTO users (name, age) VALUES (Alice, 25), (Bob, 30), (Charlie, 35);建议尽量使用批量插入以提升效率。 2.2 手动控制事务
将多条INSERT语句包裹在事务中减少频繁提交的开销
START TRANSACTION;
INSERT INTO users (name, age) VALUES (Alice, 25);
INSERT INTO users (name, age) VALUES (Bob, 30);
COMMIT;2.3 大批量数据插入
对于大批量数据使用LOAD DATA语句导入效率更高
-- 1. 客户端连接服务端时加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 2. 设置全局参数local_infile为1开启从本地加载文件导入数据的开关
set global local_infile 1;
-- 3. 执行load指令将准备好的数据加载到表结构中
LOAD DATA INFILE /root/mysql-data/data.csv
INTO TABLE users
FIELDS TERMINATED BY ,
LINES TERMINATED BY \n;
或
load data local infile /root/mysql-data/data.sql into table users fields terminated by , lines terminated by \n;三、order by 优化
MySQL排序主要有两种方式
Using filesort通过临时表排序性能较低。Using index利用索引直接排序性能较高。
优化策略
创建索引为ORDER BY字段创建索引让MySQL直接使用索引排序。
-- 创建索引
CREATE INDEX idx_age ON users (age);-- 优化查询
SELECT * FROM users ORDER BY age;原则 order by优化原则: A. 根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则。B. 尽量使用覆盖索引。C. 多字段排序, 一个升序一个降序此时需要注意联合索引在创建时的规则ASC/DESC。D. 如果不可避免的出现filesort大数据量排序时可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。 四、group by 优化
分组查询GROUP BY可以通过索引来提升性能。索引需满足最左前缀法则
-- 无索引时的查询
EXPLAIN SELECT id, age, phone FROM tb_user ORDER BY age ASC, phone DESC;-- 添加索引后优化
CREATE INDEX idx_user_age_phone_aa
ON tb_user (age ASC, phone DESC);EXPLAIN SELECT id, age, phone FROM tb_user ORDER BY age ASC, phone DESC;最左前缀法则如果联合索引的前几个字段包含分组字段则查询效率更高。 五、limit 优化
分页查询中偏移量越大性能越低。以下是优化思路
5.1 常规查询低效
SELECT * FROM users LIMIT 100000, 10;5.2 优化方式使用覆盖索引和子查询
-- 优化分页查询
SELECT * FROM users
WHERE id (SELECT id FROM users ORDER BY id LIMIT 100000, 1)
LIMIT 10;覆盖索引只返回查询字段所需的索引数据避免访问表的其他列提高效率。 六、count() 优化
6.1 常见计数方式及性能分析
计数方式含义效率COUNT(字段)遍历表并检查字段是否为NULL。较低COUNT(主键)遍历表并直接计数主键行。较高COUNT(1)遍历表但不取值仅直接按行计数。高COUNT(*)专门优化不取值仅直接按行计数。与COUNT(1)效率相当 建议优先使用COUNT(*)性能最优且语义清晰。 七、update 优化
7.1 行锁与表锁
MySQL的InnoDB存储引擎采用行锁但当索引失效时会升级为表锁严重影响性能。例如
-- 锁定单行高效
UPDATE course SET name JavaEE WHERE id 1;-- 锁定全表低效
UPDATE course SET name SpringBoot WHERE name PHP;说明第二条语句因未命中索引行锁升级为表锁。 7.2 优化策略
确保查询条件命中索引。避免一次更新过多行。
-- 添加索引优化
CREATE INDEX idx_name ON course (name);
UPDATE course SET name SpringBoot WHERE name PHP;总结
MySQL性能优化的核心在于充分利用索引、批量操作和事务控制同时针对具体场景调整策略。希望本文的案例和原则能帮助你在实际开发中事半功倍
