mip网站模板,wordpress转载文章,软件设计师中级考什么,做个公司网站需要什么钱本文详细介绍MySQL的慢查询相关概念#xff0c;分析步骤及其优化方案等。 文章目录 什么是慢查询日志#xff1f;慢查询日志的相关参数如何启用慢查询日志#xff1f;方式一#xff1a;修改配置文件方式二#xff1a;通过命令动态启用 分析慢查询日志方式一#xff1a;直… 本文详细介绍MySQL的慢查询相关概念分析步骤及其优化方案等。 文章目录 什么是慢查询日志慢查询日志的相关参数如何启用慢查询日志方式一修改配置文件方式二通过命令动态启用 分析慢查询日志方式一直接查看日志文件方式二使用EXPLAIN分析查询 常见的慢查询优化1. 数据类型优化2. 索引优化3. SQL 查询优化4. 分库分表 慢查询日志的适用场景慢查询日志的优缺点总结 什么是慢查询日志
慢查询日志是MySQL提供的一种日志记录机制用于记录执行时间超过指定阈值long_query_time的SQL语句。通过慢查询日志可以识别和优化性能较差的SQL查询是数据库性能调优的重要工具。
关键点 默认阈值long_query_time 默认值为 10秒表示运行时间超过10秒的SQL会被记录。默认状态MySQL 默认未开启慢查询日志需要手动启用。日志存储方式支持存储为文件或表。
慢查询日志的相关参数
MySQL慢查询日志的核心参数及其含义如下 启用和路径配置 slow_query_log是否开启慢查询日志1 表示开启0 表示关闭。slow-query-log-file日志文件路径和名称MySQL 5.6及以上版本。log-slow-queries旧版MySQL 5.6以下的日志存储路径参数。 时间阈值 long_query_time慢查询的时间阈值单位是秒。运行时间超过该阈值的查询将被记录到慢查询日志中。 其他参数 log_queries_not_using_indexes未使用索引的查询也会记录到慢查询日志中帮助识别潜在的索引问题可选。log_output定义日志的存储方式 FILE将日志写入文件默认。TABLE将日志记录到 mysql.slow_log 表中。FILE,TABLE同时使用文件和表存储。 如何启用慢查询日志
方式一修改配置文件
打开 MySQL 配置文件my.cnf 或 my.ini。添加以下配置slow_query_log 1
slow_query_log_file /path/to/mysql-slow.log
long_query_time 2
log_queries_not_using_indexes 1
log_output FILE重启 MySQL 服务以生效。
方式二通过命令动态启用
使用 MySQL 提供的全局变量来开启慢查询日志
SET GLOBAL slow_query_log ON;
SET GLOBAL long_query_time 2;
SET GLOBAL log_queries_not_using_indexes 1;
SET GLOBAL log_output FILE;注意动态配置的参数在重启后失效需将参数写入配置文件以持久化。 分析慢查询日志
方式一直接查看日志文件
慢查询日志文件以文本格式存储可以使用 cat、tail 或日志分析工具查看。
方式二使用EXPLAIN分析查询
EXPLAIN 命令用于模拟优化器的查询执行计划帮助分析SQL语句的性能问题。 例如
EXPLAIN SELECT * FROM res_user ORDER BY modifiedtime LIMIT 0,1000;EXPLAIN列说明 table查询涉及的表。type访问类型从高到低依次为const、eq_ref、ref、range、index、ALL。rows预计扫描的行数。key使用的索引。Extra补充信息比如是否使用了临时表或文件排序。 type 的类型和效率 ALL全表扫描效率最低。index全索引扫描。range索引范围扫描。ref非唯一索引扫描或唯一索引前缀扫描。eq_ref唯一索引扫描效率较高。const/system常量查询效率最高。 常见的慢查询优化
优化 MySQL 的慢查询是提升数据库性能的关键环节。以下是常见的慢查询优化方法按步骤和具体技术进行详细介绍
1. 数据类型优化
使用占用空间更小的字段类型 优先使用 TINYINT、SMALLINT而非 INT。固定长度的字符串使用 CHAR而非 VARCHAR。使用 TIMESTAMP 而非 DATETIME减少存储空间。 TIMESTAMP 占用 4 字节而 DATETIME 占用 8 字节。TIMESTAMP 的时间范围为 1970-2038而 DATETIME 为 1000-9999TIMESTAMP 更节省空间并且在 UTC 时间格式下自动处理时区转换。 精度要求较高时使用 DECIMAL 或 BIGINT 如果需要精确的数字存储特别是涉及到小数的场景使用 DECIMAL 类型而非 FLOAT 或 DOUBLE。例如对于要求两位小数的金额字段可以将值乘以 100 保存为 BIGINT。
2. 索引优化
索引是优化慢查询最常见和高效的方法。以下是索引优化的几种方式 创建适合的索引 对 WHERE 子句中频繁使用的列建立索引。对 GROUP BY、ORDER BY 和 JOIN 操作中涉及的列建立索引。 CREATE INDEX idx_column_name ON table_name(column_name);联合索引 如果查询中涉及多个条件可以创建联合索引。注意最左前缀原则。 CREATE INDEX idx_multi_columns ON table_name(column1, column2);覆盖索引 通过索引覆盖查询的所有字段减少回表操作。 SELECT col1, col2 FROM table_name WHERE col1 1;避免冗余索引 合理设计索引避免不必要的重复索引。例如 (a, b) 的索引已经可以覆盖 a 的查询没必要再单独为 a 创建索引。 3. SQL 查询优化
优化 SQL 查询语句本身是提高性能的重要手段。 避免 SELECT * 只查询必要的字段减少数据传输量。 SELECT col1, col2 FROM table_name WHERE condition;避免子查询改用 JOIN 子查询在某些情况下会导致性能下降特别是嵌套子查询。 -- 子查询
SELECT * FROM table_name WHERE col1 IN (SELECT col1 FROM other_table);-- 改为 JOIN
SELECT t1.* FROM table_name t1 JOIN other_table t2 ON t1.col1 t2.col1;合理使用 LIMIT 对分页查询尽量使用 LIMIT 游标id n的方法减少使用LIMIT OFFSET 的方式尤其是当 偏移量OFFSET非常大时。 LIMIT OFFSET 的性能瓶颈 数据库需要从头开始扫描跳过 OFFSET 指定的记录。偏移量越大查询耗时越长。即使只返回少量数据数据库仍需加载并跳过大量无关记录。 示例-- 查询第 1000000 页每页 10 条记录
SELECT * FROM orders ORDER BY id DESC LIMIT 1000000, 10;数据库会先找到前 1000000 条记录跳过它们然后再返回第 1000000 条后的 10 条记录。随着 OFFSET 增大性能会急剧下降。 优化方案使用 LIMIT 游标id n 通过游标条件 id n可以直接定位到需要的记录避免跳过大量无关记录。 示例 假设表 orders 中的主键是 id查询从第 1000000 条开始的 10 条记录 -- 优化后的查询
SELECT * FROM orders WHERE id 1000000 ORDER BY id ASC LIMIT 10;通过 id 1000000 确定游标位置直接从符合条件的记录开始扫描。查询性能与 OFFSET 无关扫描范围大大缩小。 避免函数操作 不要在 WHERE 子句中对列使用函数会导致索引失效。 SELECT * FROM table_name WHERE DATE(column_name) 2023-01-01; -- 慢SELECT * FROM table_name WHERE column_name 2023-01-01 AND column_name 2023-01-02; -- 快减少 OR 的使用 OR 通常会导致全表扫描可以用 UNION 或 IN 代替。 -- 原始查询使用 OR可能导致全表扫描
SELECT * FROM table_name WHERE col1 1 OR col1 2;-- 优化方式 1使用 IN能够高效利用单列索引
SELECT * FROM table_name WHERE col1 IN (1, 2);-- 优化方式 2使用 UNION将查询拆分成两个独立的部分
(SELECT * FROM table_name WHERE col1 1)
UNION
(SELECT * FROM table_name WHERE col1 2);优化 LIKE 查询 LIKE 查询如果以 % 开头会导致全表扫描因为无法使用索引。可以优化为前缀匹配或使用全文索引。 示例 -- 非优化前缀为 %无法使用索引
SELECT * FROM table_name WHERE col1 LIKE %keyword%;-- 优化前缀匹配能够使用索引
SELECT * FROM table_name WHERE col1 LIKE keyword%;-- 使用全文索引适用于大文本字段
ALTER TABLE table_name ADD FULLTEXT(col1);
SELECT * FROM table_name WHERE MATCH(col1) AGAINST(keyword);4. 分库分表
分库分表是一种应对大规模数据存储和高并发访问的解决方案。 何时分库分表 根据《阿里巴巴 Java 开发手册》的建议单表行数超过 500 万行或单表容量超过 2GB 时考虑分库分表。 分库分表的好处 提升查询效率通过拆分单表或数据库将数据分散到多个存储节点上减少单节点的存储和查询压力。提升并发性能多个节点可以同时处理查询或写入操作分担压力。减少锁冲突分库分表后每个表的并发操作减少减少锁等待和冲突。 分库分表的方式 垂直拆分按功能分库 按业务模块划分数据库将不同的业务表存储在不同的库中。 库1用户数据users, profiles
库2订单数据orders, order_items
库3商品数据products, categories水平拆分按数据分片分库分表 将单表数据按照一定规则如用户 ID、订单 ID 等拆分到多个表或库中。 范围分片根据 ID 范围分配数据。orders_0: ID 1-10000
orders_1: ID 10001-20000哈希分片对分片键取模将数据分散到多个库或表中。-- 按订单 ID 取模分表
SELECT * FROM orders_hash WHERE MOD(order_id, 4) 0;分库分表的注意事项 尽量在当前架构下优化数据库性能例如升级硬件、迁移历史数据。分片键的选择要能有效分散数据同时能支持大部分查询需求。使用分布式中间件如 ShardingSphere、MyCAT来管理分库分表后的复杂性。
慢查询日志的适用场景
数据库性能调优 找出执行较慢的查询优化索引设计或SQL语句。 排查系统瓶颈 通过 log_queries_not_using_indexes 找出未使用索引的查询优化数据访问路径。 数据模型优化 分析慢查询日志可以评估表设计、字段类型是否合理。 慢查询日志的优缺点 优点 帮助识别性能瓶颈。提供查询优化的方向。支持将日志存储为表便于后续分析。 缺点 开启后可能对性能产生一定影响尤其是高并发场景。日志文件可能过大需要定期清理。 总结
慢查询日志是性能调优的重要工具通过合理的日志配置和日志分析可以有效发现并优化SQL查询性能问题。然而在高并发环境下应根据需求合理开启并定期清理日志避免对数据库性能造成额外负担。
