惠州网站制作询问薇江苏住房和城乡建设部网站

惠州网站制作询问薇,江苏住房和城乡建设部网站,中职网站建设与管理专业,手机主题wordpress免费下载前言 去年双十一#xff0c;我们的数据库成了最大的瓶颈。一个简单的订单查询需要5秒#xff0c;用户投诉不断。经过系统的优化#xff0c;我们将查询时间降到了50ms以内。 这篇文章分享我们在MySQL优化过程中的实战经验。 一、问题发现#xff1a;慢查询日志 首先…前言去年双十一我们的数据库成了最大的瓶颈。一个简单的订单查询需要5秒用户投诉不断。经过系统的优化我们将查询时间降到了50ms以内。这篇文章分享我们在MySQL优化过程中的实战经验。一、问题发现慢查询日志首先我们开启了MySQL的慢查询日志sql-- 开启慢查询日志 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 记录超过1秒的查询 SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;分析日志后发现最慢的查询sqlSELECT * FROM orders WHERE user_id 12345 AND status pending AND created_at 2024-01-01; -- 执行时间5.2秒 -- 扫描行数2,000,000行二、优化策略一添加索引2.1 问题分析使用EXPLAIN查看执行计划sqlEXPLAIN SELECT * FROM orders WHERE user_id 12345 AND status pending AND created_at 2024-01-01;结果----------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ----------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 2000000 | Using where | -----------------------------------------------------------------------------------------问题typeALL表示全表扫描没有使用索引。2.2 创建复合索引sqlCopy code-- 创建复合索引 CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);再次执行查询sqlEXPLAIN SELECT * FROM orders WHERE user_id 12345 AND status pending AND created_at 2024-01-01;结果------------------------------------------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | range | idx_user_status_created | idx_user_status_created | 18 | NULL | 150 | Using index condition | ------------------------------------------------------------------------------------------------------------------------------结果扫描行数从200万降到150行查询时间从5.2秒降到50ms三、优化策略二避免SELECT *3.1 问题sqlSELECT * FROM orders WHERE user_id 12345;这个查询会返回所有字段包括大字段如description、metadata浪费带宽和内存。3.2 只查询需要的字段sqlSELECT id, user_id, status, total_amount, created_at FROM orders WHERE user_id 12345;结果数据传输量减少70%查询时间从50ms降到15ms四、优化策略三分页优化4.1 问题深度分页sql-- 查询第10000页每页20条 SELECT * FROM orders ORDER BY created_at DESC LIMIT 200000, 20; -- 执行时间3.5秒MySQL需要扫描前200020行然后丢弃前200000行。4.2 使用游标分页sql-- 第一页 SELECT id, user_id, status, created_at FROM orders ORDER BY id DESC LIMIT 20; -- 假设最后一条记录的id是980 -- 第二页 SELECT id, user_id, status, created_at FROM orders WHERE id 980 ORDER BY id DESC LIMIT 20;结果查询时间从3.5秒降到20ms不受页数影响五、优化策略四查询缓存5.1 应用层缓存pythonimport redis import json redis_client redis.Redis(hostlocalhost, port6379, db0) def get_user_orders(user_id): # 尝试从缓存读取 cache_key fuser_orders:{user_id} cached redis_client.get(cache_key) if cached: return json.loads(cached) # 缓存未命中查询数据库 query SELECT id, user_id, status, total_amount, created_at FROM orders WHERE user_id %s ORDER BY created_at DESC LIMIT 20 cursor.execute(query, (user_id,)) orders cursor.fetchall() # 写入缓存过期时间5分钟 redis_client.setex(cache_key, 300, json.dumps(orders)) return orders结果缓存命中率85%平均响应时间从15ms降到2ms六、优化策略五读写分离6.1 架构设计┌─────────────┐ │ 应用服务 │ └──────┬──────┘ │ ┌────────┴────────┐ │ │ 写操作 读操作 │ │ ┌────▼────┐ ┌───▼────┐ │ 主库 │──────▶│ 从库1 │ │ (Master)│ │ (Slave) │ └─────────┘ └────────┘ │ ┌────▼────┐ │ 从库2 │ │ (Slave) │ └─────────┘6.2 代码实现pythonimport pymysql from random import choice # 主库配置 MASTER_CONFIG { host: master-db.example.com, user: root, password: password, database: mydb } # 从库配置 SLAVE_CONFIGS [ { host: slave1-db.example.com, user: root, password: password, database: mydb }, { host: slave2-db.example.com, user: root, password: password, database: mydb } ] def get_master_connection(): return pymysql.connect(**MASTER_CONFIG) def get_slave_connection(): # 随机选择一个从库 config choice(SLAVE_CONFIGS) return pymysql.connect(**config) # 写操作使用主库 def create_order(user_id, amount): conn get_master_connection() cursor conn.cursor() cursor.execute( INSERT INTO orders (user_id, amount) VALUES (%s, %s), (user_id, amount) ) conn.commit() conn.close() # 读操作使用从库 def get_orders(user_id): conn get_slave_connection() cursor conn.cursor() cursor.execute( SELECT * FROM orders WHERE user_id %s, (user_id,) ) orders cursor.fetchall() conn.close() return orders结果主库写入压力降低60%读操作QPS提升3倍七、优化策略六分库分表7.1 问题单表数据过大我们的orders表有5000万条记录即使有索引查询也很慢。7.2 按用户ID分表pythondef get_table_name(user_id): # 按用户ID取模分成16张表 table_index user_id % 16 return forders_{table_index} def create_order(user_id, amount): table_name get_table_name(user_id) query f INSERT INTO {table_name} (user_id, amount, created_at) VALUES (%s, %s, NOW()) cursor.execute(query, (user_id, amount)) def get_user_orders(user_id): table_name get_table_name(user_id) query f SELECT * FROM {table_name} WHERE user_id %s ORDER BY created_at DESC LIMIT 20 cursor.execute(query, (user_id,)) return cursor.fetchall()结果单表数据量从5000万降到300万查询时间从200ms降到10ms八、国际化团队协作在数据库优化过程中我们的DBA团队分布在不同国家。为了确保优化方案和技术文档能够被所有团队成员准确理解我们使用了同言翻译Transync AI来翻译数据库优化报告和技术规范大大提高了跨国团队的协作效率。九、监控和告警9.1 慢查询监控pythonimport time import logging def query_with_monitor(query, params): start_time time.time() cursor.execute(query, params) duration time.time() - start_time # 记录慢查询 if duration 0.1: # 超过100ms logging.warning(f慢查询: {query}, 耗时: {duration}s) return cursor.fetchall()9.2 数据库连接池监控pythonfrom dbutils.pooled_db import PooledDB import pymysql pool PooledDB( creatorpymysql, maxconnections20, mincached5, maxcached10, blockingTrue, hostlocalhost, userroot, passwordpassword, databasemydb ) def get_pool_status(): return { total: pool._maxconnections, active: pool._connections, idle: pool._idle_cache }十、性能对比指标优化前优化后提升平均查询时间5200ms50ms-99%QPS20050002400%数据库CPU85%25%-71%慢查询数量5000/天10/天-99.8%缓存命中率0%85%-十一、最佳实践总结合理使用索引为高频查询字段创建索引**避免SELECT ***只查询需要的字段优化分页使用游标分页代替LIMIT深度分页引入缓存减少数据库访问压力读写分离分散数据库负载分库分表降低单表数据量监控告警及时发现性能问题。十二、常见陷阱陷阱1过度索引sql-- ❌ 不好的做法为每个字段都创建索引 CREATE INDEX idx_user_id ON orders(user_id); CREATE INDEX idx_status ON orders(status); CREATE INDEX idx_created_at ON orders(created_at); CREATE INDEX idx_amount ON orders(amount); -- ✅ 好的做法创建复合索引 CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);陷阱2索引失效sql-- ❌ 使用函数导致索引失效 SELECT * FROM orders WHERE DATE(created_at) 2024-01-01; -- ✅ 改写查询条件 SELECT * FROM orders WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00;陷阱3N1查询问题python# ❌ N1查询问题 orders Order.query.filter_by(user_id123).all() for order in orders: user User.query.get(order.user_id) # 每次都查询数据库 print(user.name) # ✅ 使用JOIN一次性查询 orders db.session.query(Order, User)\ .join(User, Order.user_id User.id)\ .filter(Order.user_id 123)\ .all()十三、工具推荐MySQL Workbench可视化数据库管理Percona ToolkitMySQL性能分析工具集pt-query-digest慢查询日志分析Prometheus Grafana数据库监控Adminer轻量级数据库管理工具十四、结语数据库优化是一个持续的过程需要根据业务场景不断调整。没有银弹只有适合自己业务的方案。希望这篇文章能帮助你解决数据库性能问题。如果你有其他优化经验欢迎在评论区分享