mysql性能剖析与优化1

学习目标:

• 了解mysql的索引和什么是慢查询
• 掌握如何配置mysql的my.cnf文件
• 观测都市商城的数据库性能,找到性能不足的语句
• Jmeter的jdcb协议

select count(*) from sq_orderform;
select ID from sq_orderform;
-- id主键自带唯一性索引   0.58s
select * from sq_orderform;
--会查询所有的列,速度最慢    8s  
 
select addTime from sq_orderform;    4s
 
select ID from sq_orderform where id=32768;
有where条件速度相当更快
 

和主键都是有索引的,相对没有索引的字段搜索起来更快

有where条件的比没有的搜索更快

查询的列更少的比查询多列的更快

匹配搜索的比准确搜索的更慢

mysql table locks 临时性锁表(绿色) 等待式锁表(黄色)

查询是可以异步的(多人一起进行查询) 增删改只能一个人来操作,事务在进行增删改时mysql会临时性锁表,等这个操作完成或回滚后,才能继续运行,所以临时性的锁表是正常的

慢查询

超过一定时间,用户无论容忍,

看项目在做业务时,看mysql slow queries是否出现很慢的情况,高的峰值代表查询时间较长,需要去优化

top看cpu的时候,最大是400,使用其他的看最大是100

多久算慢查询,去mysql的my.cnf文件去配置定义

配置my.cnf

 
vim /etc/my.cnf,如果服务器上没有这个文件,新建一个(有的mysql版本没有这个文件)
log_output=table,file  选择记录慢查询的方式,table表示记录在某张表,file表示记录在文件
slow_query_log=1    是否打开慢查询日志,1打开,0关闭
slow_query_log_file=/var/log/slow_log   记录慢查询的日志的位置
long_query_time=1   慢查询的阈值,单位为秒     超过这个值就算慢查询
log_queries_not_using_indexes=1   是否记录未使用索引的情况,0代表不记录,1代表记录
max_connections=512     最大连接数,有默认值.线程连接数,项目经理和数据架构师工程师来定
配置完成之后,重启mysql
service mysqld restart  有的linux用的是service mysql restart,有的是systemctl restart mysqld
 
 
加入慢查询没生效,碰到这个报错
 
liup@ubuntu:~$ mysql
Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
 
‘/etc/my.cnf’ is ignored ，文件权限被设置成 777，因安全问题导致被 MySQL 忽视.
 
大概意思是权限全局可写，任何一个用户都可以写。mysql担心这种文件被其他用户恶意修改，所以忽略掉这个配置文件。导致无法进入，这也是mysql的安全机制之一。所以我们必须得改一些权限。设置其他用户不可写。
 
shell如下
 
chmod 644 /etc/my.cnf
 
然后执行：
 
service mysql restart
 
 

去navicat里临时性配置慢查询

#检查是否开启
show variables like '%slow%';
#开启慢查询的日志
set global slow_query_log=ON;
#查看慢查询的日志是否写在指定文件中,需要指定慢查询的输出日志格式为文件
show variables like '%log_output%';
#日志格式输出为设置的文件和表
set global  log_output='FILE,table';
#查看慢查询的阈值,即如果执行语句的时间超过该阈值则为慢查询语句,默认为10s
show variables like 'long_query_time';
#如果值设置为ON，则会记录所有没有利用索引的查询,前提是slow_query_log=ON
show variables like 'log_queries_not_using_indexes';
#测试慢查询,把之间设置为比long_query_time大
select sleep(11);
#查询当日的慢查询的语句
select * from mysql.slow_log where date(start_time)='2023-05-14' order by start_time desc;

看有哪些数据是慢查询

-- date(start_time)慢查询的发生时间 select * from mysql.slow_log where date(start_time)='2022-05-13' order by start_time desc;

慢查询调优

查询的属性(type)

CONST>EQ_REF>REF>RANGE>INDEX>ALL

CONST:查询索引字典,且表中只有一条数据
 
EQ_REF:主键或唯一索引
 
REF:非唯一索引
 
RANGE:索引的范围查询
 
INDEX:索引
 
ALL:全表扫描

优化sql

创建外键
 
alter table sq_orderform add constraint fk98D986FB1e208F02 foreign key(user_id) references sq_user(id) on delete restrict on update restrict;
 
删除外键约束(需要删外键名称和删除外键索引)

alter table sq_orderfrom drop foreigh key fk98D986FB1e208F02;

alter table sq_orderfrom drop index key fk98D986FB1e208F02;

修改mysql端口号:

vi /etc/my.cnf

加一行:

port=端口号

重启mysql

修改mysql之后,tomcat 的配置文件也要修改

修改jdbcproperties的mysql端口号

mysqlbase.port=新端口号

重启tomcat

MySQL之慢查询日志和通用查询
扫码查看
MySQL中的日志包括：错误日志、二进制日志、通用查询日志、慢查询日志等等。这里主要介绍下比较常用的两个功能：通用查询日志和慢查询日志。

1、通用查询日志：记录建立的客户端连接和执行的语句。

2、慢查询日志：记录所有执行时间超过longquerytime秒的所有查询或者不使用索引的查询

慢查询日志可以帮助我们知道哪些SQL语句执行效率低下,所以使用慢查询日志在我们日常开发中是十分有用的.

　　一. 慢查询日志开启

　　　　

检查是否开启

show variables like '%slow%';
MySQL之慢查询日志和通用查询-LMLPHP

　　参数说明:　　

　　（1）slow_query_log 的值为ON为开启慢查询日志，OFF则为关闭慢查询日志。

　　（2）slow_query_log_file 的值是记录的慢查询日志到文件中（注意：默认名为主机名.log，慢查询日志是否写入指定文件中，需要指定慢查询的输出日志格式为文件，相关命令为：show variables like ‘%log_output%’；去查看输出的格式）。

　　（3）long_query_time 指定了慢查询的阈值，即如果执行语句的时间超过该阈值则为慢查询语句，默认值为10秒。

　　（4）log_queries_not_using_indexes 如果值设置为ON，则会记录所有没有利用索引的查询（注意：如果只是将log_queries_not_using_indexes设置为ON，而将slow_query_log设置为OFF，此时该设置也不会生效，即该设置生效的前提是slow_query_log的值设置为ON），一般在性能调优的时候会暂时开启。

　　如果是关闭的情况下,slow_query_log会是OFF,这个时候进行开启

如果没有开启，也可以在运行时开启这个参数。说明是动态参数

set global slow_query_log=ON;
　　永久开启,修改配置文件:

Windows: 修改配置文件\mysql\bin\mysql.ini 添加到[mysqld]中

慢查询日志

slow_query_log =

long_query_time =

slow_query_log_file = F:\MySQL\log\mysqlslowquery.log
　　　　　　Linux下的设置: 同上,只不过,修改的配置文件是my.cnf文件

　　　　　　　　有时候会找不到该文件: 使用命令进行查找

mysql --help|grep 'my.cnf'

ps aux|grep mysql|grep 'my.cnf'

locate my.cnf
　　设置输出日志:

慢查询日志输出到表中（即mysql.slow_log）

set globallog_output=’TABLE’;

慢查询日志仅输出到文本中(即：slow_query_log_file指定的文件)

setglobal log_output=’FILE’;

慢查询日志同时输出到文本和表中

setglobal log_output=’FILE,TABLE’;
MySQL之慢查询日志和通用查询-LMLPHP

可以看到，不管是表还是文件，都具体记录了：是那条语句导致慢查询（sql_text），该慢查询语句的查询时间（query_time），锁表时间（Lock_time），以及扫描过的行数（rows_examined）等信息

在MySQL中有一个变量专门记录当前慢查询语句的个数：

输入命令：show global status like ‘%slow%’;

　　二.设置慢查询时间

设置慢查询记录查询耗时多长的SQL,这里演示用100毫秒

set long_query_time = 0.1;
　　

　　这个时候已经设置完成,可以进行测试:

用SQL试一下。这里休眠500毫秒

select sleep(0.5)
　　

　　最终会在你的slow_query_log_file对应的文件中查看记录下的慢查询SQL

MySQL之慢查询日志和通用查询-LMLPHP

　　三. 通用查询日志

　　在学习通用日志查询时，需要知道两个数据库中的常用命令：

　　1） show variables like ‘%general%’;

　　　　可以查看，当前的通用日志查询是否开启，如果general_log的值为ON则为开启，为OFF则为关闭（默认情况下是关闭的）。

　　2） show variables like ‘%log_output%’;

　　　　查看当前慢查询日志输出的格式，可以是FILE（存储在数数据库的数据文件中的hostname.log），也可以是TABLE（存储在数据库中的mysql.general_log）

　　　　问题：如何开启MySQL通用查询日志，以及如何设置要输出的通用日志输出格式呢？

开启通用日志查询： set global general_log=on;

关闭通用日志查询： set global general_log=off;

设置通用日志输出为表方式： set global log_output=’TABLE’;

设置通用日志输出为文件方式： set global log_output=’FILE’;

设置通用日志输出为表和文件方式：set global log_output=’FILE,TABLE’;
　　（注意：上述命令只对当前生效，当MySQL重启失效，如果要永久生效，需要配置 my.cnf）

　　my.cnf文件的配置如下：

　　general_log=1 #为1表示开启通用日志查询，值为0表示关闭通用日志查询

　　log_output=FILE,TABLE#设置通用日志的输出格式为文件和表