目录
一、优化概述
二、查询与索引优化分析
1性能瓶颈定位
Show命令
慢查询日志
explain分析查询
profiling分析查询
2索引及查询优化
三、配置优化
1) max_connections
2) back_log
3) interactive_timeout
4) key_buffer_size
5) query_cache_size
6) record_buffer_size
7) read_rnd_buffer_size
8) sort_buffer_size
9) join_buffer_size
10) table_cache
11) max_heap_table_size
12) tmp_table_size
13) thread_cache_size
14) thread_concurrency
15) wait_timeout
一、 优化概述

MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈,CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候,如果应用分布在网络上,那么查询量相当大的时候那么平瓶颈就会出现在网络上,我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。
除了服务器硬件的性能瓶颈,对于MySQL系统本身,我们可以使用工具来优化数据库的性能,通常有三种:使用索引,使用EXPLAIN分析查询以及调整MySQL的内部配置。
二、查询与索引优化分析
在优化MySQL时,通常需要对数据库进行分析,常见的分析手段有慢查询日志,EXPLAIN 分析查询,profiling分析以及show命令查询系统状态及系统变量,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。
1 性能瓶颈定位
Show命令
我们可以通过show命令查看MySQL状态及变量,找到系统的瓶颈:
Mysql> show status ——显示状态信息(扩展show status like 'XXX')
Mysql> show variables ——显示系统变量(扩展show variables like 'XXX')
Mysql> show innodb status ——显示InnoDB存储引擎的状态
Mysql> show processlist ——查看当前SQL执行,包括执行状态、是否锁表等
Shell> mysqladmin variables -u username -p password——显示系统变量
Shell> mysqladmin extended-status -u username -p password——显示状态信息
查看状态变量及帮助:
Shell> mysqld --verbose --help [|more #逐行显示]
比较全的Show命令的使用可参考: http://blog.phpbean.com/a.cn/18/
慢查询日志
慢查询日志开启:
在配置文件my.cnf或my.ini中在[mysqld]一行下面加入两个配置参数
log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=2
注:log-slow-queries参数为慢查询日志存放的位置,一般这个目录要有mysql的运行帐号的可写权限,一般都将这个目录设置为mysql的数据存放目录;
long_query_time=2中的2表示查询超过两秒才记录;
在my.cnf或者my.ini中添加log-queries-not-using-indexes参数,表示记录下没有使用索引的查询。
log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=10
log-queries-not-using-indexes
慢查询日志开启方法二:
我们可以通过命令行设置变量来即时启动慢日志查询。由下图可知慢日志没有打开,slow_launch_time=# 表示如果建立线程花费了比这个值更长的时间,slow_launch_threads
计数器将增加

设置慢日志开启
MySQL后可以查询 long_query_time 的值 。

为了方便测试,可以将修改慢查询时间为5秒。

慢查询分析mysqldumpslow
我们可以通过打开log文件查看得知哪些SQL执行效率低下
[root@localhost mysql]# more
slow-query.log
# Time: 081026 19:46:34
# User@Host: root[root] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961
select count(*) from t_user;
从日志中,可以发现查询时间超过5 秒的SQL,而小于5秒的没有出现在此日志中。
如果慢查询日志中记录内容很多,可以使用mysqldumpslow工具(MySQL客户端安装自带)来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总,显示汇总后摘要结果。
进入log的存放目录,运行
[root@mysql_data]#mysqldumpslow
slow-query.log
Reading mysql slow query log from
slow-query.log
Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql
select count(N) from t_user;
mysqldumpslow命令
/path/mysqldumpslow
-s c -t 10 /database/mysql/slow-query.log
这会输出记录次数最多的10条SQL语句,其中:
-s, 是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙;
-t, 是top n的意思,即为返回前面多少条的数据;
-g, 后边可以写一个正则匹配模式,大小写不敏感的;
例如:
/path/mysqldumpslow
-s r -t 10 /database/mysql/slow-log
得到返回记录集最多的10个查询。
/path/mysqldumpslow
-s t -t 10 -g “left join” /database/mysql/slow-log
得到按照时间排序的前10条里面含有左连接的查询语句。
使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句,对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后,由于日志记录操作,在一定程度上会占用CPU资源影响mysql的性能,但是可以阶段性开启来定位性能瓶颈。
explain分析查询
使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:
– 表的读取顺序
– 数据读取操作的操作类型
– 哪些索引可以使用
– 哪些索引被实际使用
– 表之间的引用
– 每张表有多少行被优化器查询

EXPLAIN字段:
ØTable:显示这一行的数据是关于哪张表的
Øpossible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
Økey:实际使用的索引。如果为NULL,则没有使用索引。MYSQL很少会选择优化不足的索引,此时可以在SELECT语句中使用USE INDEX(index)来强制使用一个索引或者用IGNORE INDEX(index)来强制忽略索引
Økey_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好
Øref:显示索引的哪一列被使用了,如果可能的话,是一个常数
Ørows:MySQL认为必须检索的用来返回请求数据的行数
Øtype:这是最重要的字段之一,显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL
nsystem、const:可以将查询的变量转为常量. 如id=1; id为 主键或唯一键.
neq_ref:访问索引,返回某单一行的数据.(通常在联接时出现,查询使用的索引为主键或惟一键)
nref:访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生
nrange:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西,并且该字段上建有索引时发生的情况 (注:不一定好于index)
nindex:以索引的顺序进行全表扫描,优点是不用排序,缺点是还要全表扫描
nALL:全表扫描,应该尽量避免
ØExtra:关于MYSQL如何解析查询的额外信息,主要有以下几种
nusing index:只用到索引,可以避免访问表.
nusing where:使用到where来过虑数据. 不是所有的where clause都要显示using where. 如以=方式访问索引.
nusing tmporary:用到临时表
nusing filesort:用到额外的排序. (当使用order by v1,而没用到索引时,就会使用额外的排序)
nrange checked for eache record(index
map:N):没有好的索引.

profiling分析查询
通过慢日志查询可以知道哪些SQL语句执行效率低下,通过explain我们可以得知SQL语句的具体执行情况,索引使用等,还可以结合show命令查看执行状态。
如果觉得explain的信息不够详细,可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。
profiling默认是关闭的。可以通过以下语句查看

打开功能: mysql>set profiling=1; 执行需要测试的sql 语句:

mysql> show profiles\G; 可以得到被执行的SQL语句的时间和ID
mysql>show profile for
query 1; 得到对应SQL语句执行的详细信息
Show Profile命令格式:
SHOW PROFILE [type
[, type] … ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type:
ALL
| BLOCK IO
| CONTEXT
SWITCHES
| CPU
| IPC
| MEMORY
| PAGE
FAULTS
| SOURCE
| SWAPS


以上的16rows是针对非常简单的select语句的资源信息,对于较复杂的SQL语句,会有更多的行和字段,比如converting HEAP to MyISAM 、Copying to
tmp table等等,由于以上的SQL语句不存在复杂的表操作,所以未显示这些字段。通过profiling资源耗费信息,我们可以采取针对性的优化措施。
测试完毕以后 ,关闭参数: mysql> set profiling=0
分享到:
相关推荐
人脸识别项目实战
内容概要:本文详细描述了一个完整的Web应用程序的开发过程。该项目主要采用了Hono作为服务器框架,Prisma作为ORM工具,JWT用于认证鉴权,以及一系列现代化的最佳实践确保系统的健壮性和安全性。项目初期构建了基础架构,并设置了必要的依赖和工具。在后端方面涵盖了公共API接口的设计、CRUD增删改查逻辑、用户认证和授权等功能。此外还特别关注到了API的安全保护,如输入输出的校验,跨站请求伪造CSRF的防范,XSS防御等措施;为确保代码的质量引入了代码检测(比如ESLint搭配Prettier),并建立了完善的测试框架以保障后续开发阶段的功能正确。对于可能出现的问题预先定义了一组规范化的异常响应,并提供OpenAPI文档以方便开发者理解和调用。数据存储层面上利用了关系型与非关系型数据库各自的特性,实现了数据的有效组织,最后提供了实用的脚本,可用于种子数据插入以及执行必要的初始化工作。 适合人群:面向具有一定JavaScript/TypeScript开发经验,尤其是Node.js后台服务搭建经验的中级程序员和技术团队。 使用场景及目标:这份材料非常适合那些需要快速建立安全高效的RES
【资源介绍】 1、该资源包括项目的全部源码,下载可以直接使用! 2、本项目适合作为计算机、数学、电子信息等专业的课程设计、期末大作业和毕设项目,也可以作为小白实战演练和初期项目立项演示的重要参考借鉴资料。 3、本资源作为“学习资料”如果需要实现其他功能,需要能看懂代码,并且热爱钻研和多多调试实践。 掌静脉识别算法源码(门禁).zip掌静脉识别算法源码(门禁).zip掌静脉识别算法源码(门禁).zip掌静脉识别算法源码(门禁).zip掌静脉识别算法源码(门禁).zip掌静脉识别算法源码(门禁).zip掌静脉识别算法源码(门禁).zip掌静脉识别算法源码(门禁).zip掌静脉识别算法源码(门禁).zip掌静脉识别算法源码(门禁).zip掌静脉识别算法源码(门禁).zip掌静脉识别算法源码(门禁).zip掌静脉识别算法源码(门禁).zip掌静脉识别算法源码(门禁).zip
手势识别项目实战
(参考GUI)MATLAB BP的交通标志系统.zip
人脸识别项目实战
内容概要:本文详细介绍了 C++ 函数的基础概念及其实战技巧。内容涵盖了函数的基本结构(定义、声明、调用)、多种参数传递方式(值传递、引用传递、指针传递),各类函数类型(无参无返、有参无返、无参有返、有参有返),以及高级特性(函数重载、函数模板、递归函数)。此外,通过实际案例展示了函数的应用,如统计数组元素频次和实现冒泡排序算法。最后,总结了C++函数的重要性及未来的拓展方向。 适合人群:有一定编程基础的程序员,特别是想要深入了解C++编程特性的开发人员。 使用场景及目标:① 学习C++中函数的定义与调用,掌握参数传递方式;② 掌握不同类型的C++函数及其应用场景;③ 深入理解函数重载、函数模板和递归函数的高级特性;④ 提升实际编程能力,通过实例强化所学知识。 其他说明:文章以循序渐进的方式讲解C++函数的相关知识点,并提供了实际编码练习帮助理解。阅读过程中应当边思考边实践,动手实验有助于更好地吸收知识点。
Comsol光学仿真模型:包括纳米球 柱 Mie散射多级分解 ,Comsol光学仿真模型; 纳米球; 柱; Mie散射; 多级分解,Comsol光学仿真模型:纳米结构Mie散射多级分解
永磁同步电机全速域控制高频方波注入法、滑模观测器法SMO、加权切矢量控制Simulink仿真模型 低速域采用高频方波注入法HF,高速域采用滑膜观测器法SMO,期间采用加权形式切 送前方法 1、零低速域,来用无数字滤波器高频方波注入法, 2.中高速域采用改进的SMO滑模观测器,来用的是sigmoid函数,PLL锁相环 3、转速过渡区域采用加权切法 该仿真各个部分清晰分明,仿真波形效果良好内附详细控制方法资料lunwen 带有参考文献和说明文档,仿真模型 ,核心关键词: 1. 永磁同步电机; 2. 全速域控制; 3. 高频方波注入法; 4. 滑模观测器法SMO; 5. 加权切换矢量控制; 6. Simulink仿真模型; 7. 零低速域控制; 8. 中高速域控制; 9. 转速过渡区域控制; 10. 仿真波形效果; 11. 详细控制方法资料; 12. 参考文献和说明文档。,永磁同步电机多域控制策略的仿真研究
基于蜣螂优化算法的无人机三维路径规划【23年新算法应用】可直接运行 Matlab语言 主要内容:读取地形数据,利用蜣螂算法DBO优化三维路径,目标函数为总路径最短,同时不能撞到障碍物,效果如图所示,包括迭代曲线图、三维路径图、二维平面图等等 ,基于蜣螂优化算法;无人机;三维路径规划;总路径最短;障碍物避免;Matlab语言;迭代曲线图;三维路径图;二维平面图,蜣螂算法优化无人机三维路径规划:实时避障、路径最短新应用
清华大学2024年研究生复试上机考试题.zip
南京理工大学研究生入学考试2011年复试上机试题
手势识别项目实战
这里是3501的内容,用于复习资料
异步电动机变压变频调速系统,包含六千多字的文档、框架图、Simulink仿真模型,电力拖动、电机控制仿真设计 仿真模型+报告 开关闭环对比仿真都有,资料如图所见如所得 ,异步电动机;变压变频调速系统;六千字文档;框架图;Simulink仿真模型;电力拖动;电机控制仿真设计;开闭环对比仿真;资料如图。,异步电机控制仿真系统:六千字详解与图解
人脸识别项目实战
手势识别项目实战
人脸识别项目实战
c语言学习
c语言学习