MYSQL数据库性能优化

从 配置、SQL、连接、存储引擎、程序、操作系统 六个方向排查SQL查询过程先查缓冲池 若在则直接读取反之则查询磁盘并将磁盘数据放到缓冲池以供下次查询使用[TOC]1.缓冲池优化·增加缓存提高查询速度#查询缓存大小showvariableslikeinnodb_buffer_pool_size#设置缓存(1G1073741824字节)setglobalinnodb_buffer_pool_size1073741824#注意上面设置方式重启后会失效**#永久设置方式修改my.ini文件中的 innodb_buffer_pool_size 值大小记得重启服务**大小设置建议物理内存的80%2. 慢SQL-- 1. 查看占用内存最高的执行语句SELECTquery,exec_count,total_latency,avg_examined_rowsFROMsys.statement_analysisORDERBYtotal_latencyDESCLIMIT10;# 查询正在执行的、消耗资源较多的 SQLSELECT*FROMsys.statement_analysisORDERBYtotal_latencyDESCLIMIT10;-- 2. 查看哪些线程当前正在占用大量内存SELECTthread_id,processlist_id,current_used_mem_mbFROMsys.memory_thread_by_current_bytesWHEREcurrent_used_mem_mb50;-- 查找占用超过50MB的线程--- 定位正在运行的“慢”过程 如果内存正在飙升立即查看当前正在运行的 SQL-- 查看当前正在执行的线程观察 Time 和 InfoSHOWFULLPROCESSLIST;--关注点 Time 很长且 Command 为 Query 的语句以及 State 显示为 Copying to tmp table 或 Sending data 的语句。#慢SQL开启-- 开启慢查询日志功能SETGLOBALslow_query_logON;-- 设置慢查询的判定阈值单位秒建议先设为 1 或 2视业务情况而定SETGLOBALlong_query_time1;-- 设置日志输出路径通常在数据目录下-- SET GLOBAL slow_query_log_file /var/lib/mysql/mysql-slow.log;#验证SHOWVARIABLESLIKEslow_query_log;SHOWVARIABLESLIKElong_query_time;永久开启修改 MySQL 的配置文件my.cnf 或 my.ini[mysqld]# 开启慢查询日志slow_query_log1# 设置慢查询时间阈值超过此时间会被记录long_query_time1# 日志文件路径确保 MySQL 用户对该路径有写权限slow_query_log_file/var/lib/mysql/mysql-slow.log# 记录未使用索引的查询可选建议开启log_queries_not_using_indexes13. 排查手段# 查看操作系统内存占用1.使用top或htop查看 RES(常驻内存)和 SHR(共享内存)2.观察free-m注意 buff/cache 是否过高。MySQL 依赖系统缓存这部分高通常是正常的#查看 MySQL 内部内存分配-- 查看各内存分类的占用情况SELECT*FROMsys.memory_global_by_current_bytes;-- 查看哪些线程占用了较多内存SELECT*FROMsys.memory_thread_by_current_bytes;#排查配置问题-- 查看所有相关内存参数SHOWVARIABLESWHEREVariable_nameIN(innodb_buffer_pool_size,innodb_log_buffer_size,sort_buffer_size,join_buffer_size,read_buffer_size,max_connections);核算逻辑全局内存 innodb_buffer_pool_size innodb_log_buffer_size …单连接内存 sort_buffer_size join_buffer_size read_buffer_size …总预估内存 全局内存 (max_connections × 单连接内存)注意 不能超过了服务器物理内存的 80%[TOC]################# 优先排查 ######################实时“抓现行”#这条命令能列出当前 MySQL 实例中所有正在运行的线程SHOW FULL PROCESSLIST;核心列Time该线程执行了多久单位秒。如果数值很大且状态不是 Sleep说明该 SQL 执行时间过长State线程当前正在做什么重点关注 Sending data可能正在从磁盘读取大批量数据、Copying to tmp table正在创建临时表极度消耗内存、Sorting result正在排序消耗 CPU 和内存info当前执行的完整 SQL 语句。如果语句很长FULL 关键字能保证你看到完整内容而不是被截断。典型场景如果你发现某一行的 Time 很高且 State 为 Sending data这通常就是那个正在“吃掉”内存的 SQL。查历史帐你可以运行以下语句查看前 10 条最耗资源的 SQLSELECTquery,exec_count,-- 执行次数total_latency,-- 总耗时avg_examined_rows,-- 平均扫描行数重点关注rows_sent_avg,-- 平均返回行数tmp_tables,-- 使用临时表的次数内存杀手disk_tmp_tables-- 使用磁盘临时表的次数FROMsys.statement_analysisORDERBYtotal_latencyDESCLIMIT10;[TOC]4.max_connections最大连接数-- 当前连接数SHOWSTATUSLIKEThreads_connected;-- 当前正在执行的连接SHOWSTATUSLIKEThreads_running;-- 历史最大连接数SHOWSTATUSLIKEMax_used_connections;-- 最大连接数配置SHOWVARIABLESLIKEmax_connections;-- 是否发生连接数耗尽SHOWSTATUSLIKEConnection_errors_max_connections;-- 查看所有连接SHOWFULLPROCESSLIST;-- 按状态统计连接SELECTCOMMAND,COUNT(*)FROMinformation_schema.PROCESSLISTGROUPBYCOMMAND;-- 按用户统计连接SELECTUSER,COUNT(*)FROMinformation_schema.PROCESSLISTGROUPBYUSER;#1.查看当前连接数,Threads_connected:表示当前有多少个客户端连接到 MySQLSHOW STATUS LIKEThreads_connected;#2.再看真正执行 SQL 的连接SHOW STATUS LIKEThreads_running;# Threads_running 4#说明#58 个连接#真正工作的只有 4 个#其它 54 个都是 Sleep这是正常现象#3.查看历史最大连接数SHOW STATUS LIKEMax_used_connections;# Max_used_connections 100 表示自 MySQL 启动以来最多只用过 126 个连接。#4.再看SHOW VARIABLES LIKEmax_connections;#max_connections 10001000↓ 历史最高126配置过大。#5.看连接是否打满SHOW STATUS LIKEConnection_errors_max_connections;#6.查看连接详情SHOW FULL PROCESSLIST;