Feng's Blog

kiwidict.com

0%

Mysql系列之性能优化与问题定位

Posted on Edited on In

Server Status Variables

设置事务自动提交

1
2
3
# 查看是否开启自动提交事务
SHOW VARIABLES LIKE 'AUTOCOMMIT';
select @@AUTOCOMMIT;

设计优化

tinyint和char(1)性能对比

https://blog.csdn.net/yzj5208/article/details/81214780?utm_source=blogxgwz6

布尔类型字段的设计

可以使用bit类型，节约磁盘存储空间。

datetime 和 timestamp 的区别与选择

https://segmentfault.com/a/1190000017393602

锁表、线程溢出常见处理方法

查询当前的线程信息，mysql会把当前正在运行的mysql线程信息实时更新到processlist表。

1
2
3
4
5
6
7
8
9
10
show processlist; # 查询当前100条；等同于select * from information_schema.processlist;
show full processlist; # 查询全部；
# 查询非睡眠状态的连接
select p.*
from information_schema.processlist p
where p.COMMAND != 'Sleep';
# 查询并发最大连接数
show variables like '%max_connections%';
# 查看当前线程连接数量信息 
show status like 'Threads%';

慢查询

1
2
3
4
5
6
# 查找当前正在执行超过1分钟的线程信息
select * from information_schema.processlist where command != 'Sleep' and time > 60 order by time desc;
# 不要轻易kill，注意看是否是system user或者是maxscale等代理监控的用户
# 确定是慢查询的时候，可以执行kill id的操作
# 命令行查看慢查询
mysql -ureader -pread -e 'show full processlist' | grep "select "

参考：https://www.jianshu.com/p/93cae0b3db3b

事务等待超时一般会抛出异常：

1
Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

设置事务超时时间：

1
2
3
4
# 查询事务超时变量值
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
# 设置事务超时时间
SET GLOBAL innodb_lock_wait_timeout=120;

设置线程等待时间

1
2
3
4
SET wait_timeout = 10;
SET global wait_timeout = 10;
show global variables like 'wait_timeout';
show variables like 'wait_timeout';

事务锁、死锁处理方法

1
2
3
select * from information_schema.innodb_trx; # 当前运行的所有事务
select * from information_schema.innodb_locks; # 当前出现的锁
select * from information_schema.innodb_lock_waits; # 锁等待的对应关系

查找一直在锁等待的线程

1
2
3
4
5
select trx_mysql_thread_id from information_schema.innodb_trx it 
JOIN information_schema.INNODB_LOCK_WAITS ilw 
on ilw.blocking_trx_id = it.trx_id;

kill tid;

开启慢查询日志

参考：https://www.cnblogs.com/saneri/p/6656161.html

1
2
3
4
5
slow_query_log 慢查询开启状态，ON开启，OFF关闭
slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
long_query_time 查询超过多少秒才记录
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）
log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据<br>库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需<br>要能够获得更高的系统性能，那么建议优先记录到文件。

生产环境的慢查询日志分析命令mysqldumpslow

1
2
3
4
5
6
7
8
9
10
11
12
比如:
得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
 
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
 
得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
 
另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

各种时间的查询

[参考]https://www.cnblogs.com/lykbk/p/sdsdssdsqwewewew.html( “”)

Problem Solution

Access denied for user ‘root‘@’localhost’ (using password: YES)

  • Open & Edit /etc/my.cnf or /etc/mysql/my.cnf, depending on your distro.
  • Add skip-grant-tables under [mysqld]
  • Restart Mysql
  • You should be able to login to mysql now using the below command mysql -u root -p
  • Run mysql> flush privileges;
  • Set new password by ALTER USER ‘root‘@’localhost’ IDENTIFIED BY ‘NewPassword’;
  • Go back to /etc/my.cnf and remove/comment skip-grant-tables
  • Restart Mysql
  • Now you will be able to login with the new password mysql -u root -p

You can’t specify target table ‘label’ for update in FROM clause

在MySQL中，写SQL语句的时候 ，可能会遇到You can’t specify target table ‘表名’ for update in FROM clause这样的错误，它的意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)，即不能依据某字段值做判断再来更新某字段的值。
————————————————
版权声明：本文为CSDN博主「静远小和尚」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_29672495/article/details/72668008

比如：

1
2
3
4
5
6
update label
set count=(select sum(count)
           from label l
           where (l.label_code = 'Z3' and l.name = '科普与知识')
              or (l.label_code = 'Z5' and l.name = '科技'))
where id = '4028e5f47695023c0176950e54561000';

可以改成：

1
2
3
4
5
6
7
update label
set count=(select sum(count)
           from (select count
                 from label l
                 where (l.label_code = 'Z3' and l.name = '科普与知识')
                    or (l.label_code = 'Z5' and l.name = '科技')) tmp)
where id = '4028e5f47695023c0176950e54561000';