show [global|sesstion] status like '参数';
一些常用的性能参数如下：
Connections:连接MySQL服务器的次数
SHOW STATUS LIKE 'Connections'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Connections | 11 | +---------------+-------+ 1 row in set (0.03 sec)
Uptime: MySQL服务器的上线时间
mysql> SHOW STATUS LIKE 'Uptime'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Uptime | 73573 | +---------------+-------+ 1 row in set (0.00 sec)
Slow_ queries: 慢查询的次数
SHOW STATUS LIKE 'Slow_ queries'; Empty set (0.01 sec) #默认情况下，是不开启的，可以在配置文件中持久化写入，也可以在服务器未宕机时，设置成全局变量
这是对表而言记录行数的变化
Innodb_ rows_ read: Select查询返回的行数
Innodb_row_inserted: 执行INSERT操作插入的行数
Innodb_ rows_updated:执行UPDATE操作更新的行数
Innodb_ rows_deleted:执行DELETE操作删除的行数
这是对于命令的执行数的记录
com_ select: 查询操作的次数
com_ insert: 插入操作的次数，对于批量插入的INSERT操作,只累加一次
com_undate.百新品作的次数
它通常也是我们评价一个查询的执行效率的一个常用指标。这个查询成本对应的是SQL语句所需要读取的页的数量。(如果是顺序IO执行时间差不多，但是如果是随机IO的话，执行时间是差几个数量级的，而且在Innodb中是可以有将微小的页以单向链表的形式连接，如果是双向链表的碎片区IO会变得更大）
#在一个页当中，查询速度是非常的快 mysql> select * from student_info where id=3232; +------+------------+--------+-----------+----------+---------------------+ | id | student_id | name | course_id | class_id | create_time | +------+------------+--------+-----------+----------+---------------------+ | 3232 | 96562 | YqIsLE | 10021 | 10165 | 2022-06-23 23:53:49 | +------+------------+--------+-----------+----------+---------------------+ 1 row in set (0.00 sec) mysql> show status like 'last_query_cost'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | Last_query_cost | 1.000000 | +-----------------+----------+ 1 row in set (0.00 sec) #在查询名字的时候，检索了102884页，速度是350ms mysql> select * from student_info where name='OdaWBS'; +--------+------------+--------+-----------+----------+---------------------+ | id | student_id | name | course_id | class_id | create_time | +--------+------------+--------+-----------+----------+---------------------+ | 777772 | 115657 | OdaWBS | 10032 | 10142 | 2022-06-23 23:54:25 | +--------+------------+--------+-----------+----------+---------------------+ 1 row in set (0.35 sec) mysql> show status like 'last_query_cost'; +-----------------+---------------+ | Variable_name | Value | +-----------------+---------------+ | Last_query_cost | 102883.471561 | +-----------------+---------------+ 1 row in set (0.00 sec)
总结像使用了show status like ''这种的变量查询的一般是记录操作变化的量，就是说一般想查看操作的记录可以使用这个查看
三、定位执行慢的sql：慢查询日志1.在使用前，先查看是否开启了慢查询日志并开启
show variables like '%slow_query_log%'; +---------------------+---------------------------------------+ | Variable_name | Value | +---------------------+---------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/liujiangtao01-slow.log | +---------------------+---------------------------------------+ 2 rows in set (0.00 sec) mysql> set global slow_query_log='ON'; Query OK, 0 rows affected (0.01 sec) mysql> show variables like '%slow_query_log%'; +---------------------+---------------------------------------+ | Variable_name | Value | +---------------------+---------------------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/liujiangtao01-slow.log | +---------------------+---------------------------------------+ 2 rows in set (0.01 sec)
2.long_query_time慢查询时间的记录阈值，默认超过10s的sql语句执行记录是慢查询，记录下来
show variables like '%long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec)
3.将慢查询的阈值设置成1s
mysql> set global long_query_time=1; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like '%long_query_time%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ 1 row in set (0.00 sec) mysql> show variables like '%long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec) #可以发现加了global的查询是在内存中的，而不加的是持久化文件的，可以发现这种是不写入磁盘的
将文件持久化写入磁盘如下的方式相较于前面的命令行方式，可以看作是永久设置的方式。 修改 my.cnf 文件，[mysqld]下增加或修改参数 long_query_time、slow_query_log和 slow_ query_log_file后,然后重启MySQL服务器。
[mysq1d] slow_query_log=ON #开启慢查询日志的开关 slow_query_log_file=/var/lib/mysq1/liu-slow.log#慢查询日志的目录和文件名信息 long_query_time=3 #设置慢查询的阅值为3秒，超出此设定值的SQL即被记录到慢查询日志 log_output=FILE #如果不指定存储路径，慢查询日志将默认存储到MySQL数据库的数据文件夹下为hostname-slow.log。
4.查询当前系统中有多少条慢查询记录
SHOW GLOBAL STATUS LIKE '%Slow_queries%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 0 | +---------------+-------+ 1 row in set (0.00 sec)五、案例演示
1.建表
CREATE TABLE `student`( `id` INT(11) NOT NULL AUTO_INCREMENT, `stuno` INT NOT NULL, `name` VARCHAr(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `classId` INT(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
2.开启函数调用允许
DELIMITER// CREATE PROCEDURE insert_stu1( START INT,max_num INT) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO student (stuno, NAME,age,classId) VALUES ((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000)); UNTIL i = max_num END REPEAT; COMMIT; END // DELIMITER;
3.生成数据
CALL insert_stu1(1000001,4000000);
4.查看是否成功
mysql> use liujiangtao; Database changed mysql> select count(*) from student; +----------+ | count(*) | +----------+ | 4000000 | +----------+ 1 row in set (1.32 sec)
查询记录
mysql> select * from student where stuno = 3444444; +---------+---------+--------+------+---------+ | id | stuno | name | age | classId | +---------+---------+--------+------+---------+ | 2444443 | 3444444 | VFoEcE | 69 | 581 | +---------+---------+--------+------+---------+ 1 row in set (0.74 sec) mysql> select * from student where name = 'VFoEcE'; +---------+---------+--------+------+---------+ | id | stuno | name | age | classId | +---------+---------+--------+------+---------+ | 1850397 | 2850398 | VFoEcE | 68 | 524 | | 2444443 | 3444444 | VFoEcE | 69 | 581 | +---------+---------+--------+------+---------+ 2 rows in set (0.78 sec)
补充说明
除了上述变量，控制慢查询日志的还有一个系统变量: min_ examined_ row_ limit。这个变量的意思是查询扫描过的最少记录数。这个变量和查询执行时间共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过long_query_time 的值，那么，这个查询就被记录到慢查询日志中;反之，则不被记录到慢查询日志中。
mysql> show variables like
-> '%min_examined_row_limit%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| min_examined_row_limit | 0 |
+------------------------+-------+
1 row in set (0.03 sec)
四、mysqldumpslow的使用这个值默认是0,与long_query_time=10合在一起，表示只要查询的执行时间超过10秒钟，哪怕一个记录也没有扫描过，都要被记录到慢查询日志中。你也可以根据需要，通过修改“my.ini"文件，来修改查询时长或者通过SET指令，用SQL语句修改“min_examined_row_limit"的值。
[root@liujiangtao01 ~]# mysqldumpslow -help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
找到文件位置
[root@liujiangtao01 ~]# cd /var/lib/mysql [root@liujiangtao01 mysql]# ll 总用量 1458848 drwxr-x---. 2 mysql mysql 189 6月 14 06:34 atguigudb -rw-r-----. 1 mysql mysql 56 5月 28 05:26 auto.cnf -rw-r-----. 1 mysql mysql 156 5月 28 19:09 binlog.000001 -rw-r-----. 1 mysql mysql 156 5月 28 19:16 binlog.000002 -rw-r-----. 1 mysql mysql 179 5月 28 19:41 binlog.000003 -rw-r-----. 1 mysql mysql 643 5月 28 22:15 binlog.000004 -rw-r-----. 1 mysql mysql 156 5月 30 00:38 binlog.000005 drwxr-x---. 2 mysql mysql 187 6月 25 22:21 #innodb_temp drwxr-x---. 2 mysql mysql 67 6月 26 20:48 liujiangtao -rw-r-----. 1 mysql mysql 1375 6月 26 21:21 liujiangtao01-slow.log drwxr-x---. 2 mysql mysql 143 5月 28 05:26 mysql -rw-r-----. 1 mysql mysql 28311552 6月 26 20:51 mysql.ibd srwxrwxrwx. 1 mysql mysql 0 6月 25 22:21 mysql.sock -rw-------. 1 mysql mysql 5 6月 25 22:21 mysql.sock.lock
使用命令查看
[root@liujiangtao01 mysql]# mysqldumpslow -s t -t 5 /var/lib/mysql/liujiangtao01-slow.log Reading mysql slow query log from /var/lib/mysql/liujiangtao01-slow.log Count: 1 Time=174.97s (174s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@liujiangtao01 call insert_stu1(N,N) Count: 1 Time=170.97s (170s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@liujiangtao01 CALL insert_stu1(N,N) Count: 1 Time=0.85s (0s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost select count(*) from student Count: 1 Time=0.77s (0s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost select * from student where stuno = N Count: 1 Time=0.70s (0s) Lock=0.00s (0s) Rows=2.0 (2), root[root]@localhost select * from student where name = 'S'五、慢查询日志关闭
持久化关闭
[mysq1d] slow_query_log=OFF
关闭全局变量
mysql> set global slow_query_log='OFF'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%slow%'; +---------------------------+---------------------------------------+ | Variable_name | Value | +---------------------------+---------------------------------------+ | log_slow_admin_statements | OFF | | log_slow_extra | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/liujiangtao01-slow.log | +---------------------------+---------------------------------------+ 6 rows in set (0.01 sec)
