SQL优化
| web | des |
|---|---|
| percona | pt-query-digest |
percona安装
sh
# download
wget https://downloads.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-1.el7.x86_64.rpm
# install dependency
yum install -y perl-DBD-MySQL.x86_64 perl-DBI.x86_64 perl-Time-HiRes.x86_64 perl-IO-Socket-SSL.noarch perl-TermReadKey.x86_64 perl-Digest-MD5
# install
rpm -ivh percona-toolkit-3.0.13-1.el7.x86_64.rpmpercona使用
查询Mysql默认查询时间
sql
show variables like 'long_query_time';
# 设置100毫秒查询时间
set global long_query_time = 0.001;
# show slow log setting
show variables like 'slow_query_log';
# show slow log setting file
show variables like 'slow_query_log_file';
# start slow query log
set global slow_query_log = on;
use imc_db;
select count(*) from imc_db.imc_user;
# 此时已经生成慢查询日志文件 在log目录下
SELECT @@session.transaction_isolation;
# Time: 2025-01-29T13:32:54.479542Z
# User@Host: imooc[imooc] @ [192.168.0.90] Id: 12
# Query_time: 0.000016 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1738157574;mysqldumpslow 的使用
sh
mysqldumpslow /usr/local/mysql/sql_log/slowlog.log
Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), imooc[imooc]@[192.168.0.20]
/* ApplicationName=PyCharm N.N.N */ set global slow_query_log = on
Count: 5 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (5), imooc[imooc]@[192.168.0.20]
SELECT @@session.transaction_isolation
Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (2), imooc[imooc]@[192.168.0.20]
SELECT @@session.transaction_read_onlypt-query-digest 的使用
sh
pt-query-digest slowlog.log
# Query 1: 0.04 QPS, 0.00x concurrency, ID 0x8242DDD855F27A944F73BFBC4EFC5A27 at byte 3582
# Scores: V/M = 0.01
# Time range: 2025-01-29T13:29:47 to 2025-01-29T13:30:36
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 6 2
# Exec time 44 10ms 505us 10ms 5ms 10ms 7ms 5ms
# Lock time 100 10ms 222us 10ms 5ms 10ms 7ms 5ms
# Rows sent 14 2 1 1 1 1 0 1
# Rows examine 0 0 0 0 0 0 0 0
# Query size 10 150 75 75 75 75 0 75
# String:
# Hosts 192.168.0.20
# Users imooc
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms
# 10ms ################################################################
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `imc_db` LIKE 'imc_user'\G
# SHOW CREATE TABLE `imc_db`.`imc_user`\G
# EXPLAIN /*!50100 PARTITIONS*/
/* ApplicationName=PyCharm 2024.3.2 */ select count(*) from imc_db.imc_user\G时间监控mysql
sql
select id, `user`, `host`, DB, command, `time`, state, info from information_schema.PROCESSLIST where time > 60;
+----+-----------------+--------------------+------+---------+------+------------------------+------+
| id | user | host | DB | command | time | state | info |
+----+-----------------+--------------------+------+---------+------+------------------------+------+
| 4 | event_scheduler | localhost | NULL | Daemon | 2418 | Waiting on empty queue | NULL |
| 13 | imooc | 192.168.0.20:58807 | NULL | Sleep | 334 | | NULL |
+----+-----------------+--------------------+------+---------+------+------------------------+------+sql执行计划
explain+SQL语句，ID越大，查询优先值越高。
sql
explain select count(*) from imc_db.imc_user;阻塞
设置数据读取级别
- 顺序读serializable
- 重复读repeatable read
- 读已提交read committed
- 读未提交read uncommited
sql
# 设置读取隔离级别
set session transaction isolation level repeatable read;
#回滚
rollback
#提交
commit查询阻塞
sql
select waiting_pid as 'blocked pid',
waiting_query as 'blocked sql',
blocking_pid as 'running pid',
blocking_query as 'running sql',
wait_age as 'blocked time',
sql_kill_blocking_query as 'info'
where (unix_timestamp() - unix_timestamp(wait_started)) > 30; # where 设置阻塞时间，可以不加，即查询全部死锁
查看和记录死锁
sql
set global innodb_print_all_deadlocks = on;