mysql性能——2慢查询
一、mysql数据库优化
1）最大连接数查询
mysql> show variables like '%max_connections%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 10000 | +-----------------+-------+ 1 row in set (0.00 sec)
2）最大连接数修改
方法1）可以在/etc/my.cnf里面设置数据库的最大连接数 [mysqld] max_connections = 10000
方法2）mysql>set global max_connections = 5000;
最大可设置16384,超过没用
二、简单上手版包含全流程
从开启慢sql记录》输出log》分析。直接分析慢sql，不用看其他步骤。
进入mysql命令下： // 查询是否开启慢查询日志 ON OR OFF 1）show variables like "slow_query_log"; // 开启慢查询 2）set global slow_query_log=on; //超过3秒钟查询记录到慢查询日志中，注意修改之后需要重新打开一个会话再查询修改结果 3）set global long_query_time = 3; //查看当前慢查询日志输出的格式:---可以是FILE（存储在数数据库的数据文件中的hostname.log），也可以是TABLE（存储在数据库中的mysql.general_log） 4）show variables like "%log_output%"; 进入shell命令下： //安装perl是mysqldumpslow的一个依赖 5）yum install perl //运行mysqldumpslow 查看结果 后面不需要加冒号 ”;“ 否则报错 mysqldumpslow –s c –t 10 /usr/local/mysql/data/sql-2-slow.log
三、开启慢查询
简介：MySQL中的日志包括：错误日志、二进制日志、通用查询日志、慢查询日志等等。这里主要介绍下比较常用的两个功能：通用查询日志和慢查询日志。
- 1）通用查询日志：记录建立的客户端连接和执行的语句。
- 2）慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或者不使用索引的查询
1）查询所有相关的变量（可略过到第2步）
条件可以是"%quer%" 或 "%log%" 可以查看相关命令。
mysql> show variables like "%quer%"; +----------------------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------------+--------------------------------------+ | binlog_rows_query_log_events | OFF | | ft_query_expansion_limit | 20 | | have_query_cache | YES | | log_queries_not_using_indexes | OFF | | log_throttle_queries_not_using_indexes | 0 | | long_query_time | 10.000000 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | slow_query_log | OFF | | slow_query_log_file | /usr/local/mysql/data/sql-1-slow.log | +----------------------------------------+--------------------------------------+ 15 rows in set (0.01 sec)
2）开启、查询通用日志
mysql> show variables like "%general%"; +------------------+---------------------------------+ | Variable_name | Value | +------------------+---------------------------------+ | general_log | OFF | | general_log_file | /usr/local/mysql/data/sql-1.log | +------------------+---------------------------------+ 2 rows in set (0.00 sec) mysql> set global general_log = on; Query OK, 0 rows affected (0.03 sec) mysql> show variables like "%general%"; +------------------+---------------------------------+ | Variable_name | Value | +------------------+---------------------------------+ | general_log | ON | | general_log_file | /usr/local/mysql/data/sql-1.log | +------------------+---------------------------------+ 2 rows in set (0.01 sec)
3）通过命令开启慢查询日志（重启服务失效）
// 查询是否开启慢查询日志 ON OR OFF show variables like "slow_query_log"; // 开启慢查询 set global slow_query_log=on; // 指定慢查询日志的存储位置 目录要具有写权限 set global slow_query_log_file = '/home/mysql/sql_log/mysql-slow.log'; // 把没有使用索引的sql记录到慢查询日志中 如果table的数据量小，不建议开启该条设置，因为会记录所有没有用到索引的查询，会给日志填充很多垃圾信息（注意：如果只是将log_queries_not_using_indexes设置为ON，而将slow_query_log设置为OFF，此时该设置也不会生效，即该设置生效的前提是slow_query_log的值设置为ON），一般在性能调优的时候会暂时开启。 set global log_queries_not_using_indexes = on; // 超过1秒钟查询记录到慢查询日志中，注意修改之后不会变，需要重新打开一个会话查询，再查询就改变了。实际已经改变了。 set global long_query_time = 3;
4) 通过my.cof开启慢查询日志(原理和用命令一样)，不过配置完成需要重启MySQL（永久有效）
注：MySQL配置文件在Windows下叫my.ini；在Linux下叫my.cnf，文件位于/etc/my.cnf。
方式一：未实验，暂不确定，一般做性能临时生效即可。
目录要具有写权限 log-slow-queries = '/home/mysql/sql_log/mysql-slow.log' // 在windows的情况下需要绝对路径 log-slow-queries = 'C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-slow.log' long_query_time = 1 log-queries-not-using-indexes = on
方式二：未实验，暂不确定
//这里的内容都没试验，在my.cnf增加或修改下面二行。
slow_query_log =1
slow_query_log_file=/tmp/mysql_slow.log
5）修改输出日志类型
//查看当前慢查询日志输出的格式:---可以是FILE（存储在数数据库的数据文件中的hostname.log），也可以是TABLE（存储在数据库中的mysql.general_log） show variables like "%log_output%"; //设置通用日志输出为表方式： 这样会输出到mysql库下的 set global log_output="TABLE"; //设置通用日志输出为文件方式： 推荐为FILE文件； set global log_output="FILE"; //设置通用日志输出为表和文件方式： set global log_output="FILE,TABLE"; //（注意：上述命令只对当前生效，当MySQL重启失效，如果要永久生效，需要配置my.cnf）
6） 如果输出日志类型为Table，则会输出到mysql库下的slow_log表下。
mysql> show databases; +-------------------------------+ | Database | +-------------------------------+ | information_schema | | mysql | | mysql_innodb_cluster_metadata | | performance_schema | | rdc_manager | | sys | +-------------------------------+ 6 rows in set (0.00 sec) mysql> use mysql; Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.00 sec)
四、慢查询日志内容简介
慢查询日志所包含的内容 // 执行SQL的主机信息：ROOT用户在本地所执行的查询 # User@Host: root['root'] @ localhost [] // SQL的执行信息 查询时间、锁定时间、发送行数、扫描行数 # Query_time:0.000024 Lock_time:0.0000000 Rows_sent:0 Rows_examined:0 // SQL执行时间 时间戳 # SET timestamp = 1402389328; // SQL的内容 # select CONCAT('storage engine:',@@storage_engine) as INFO;
五、慢查询日志分析工具
官方文档：https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html
工具1：mysqldumpslow(mysl官方工具) 一般集成在MySQL安装包中/bin
工具2：pt-query-digest工具
帮助显示的方法
[root@sql-2 bin]# 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
-s, 是表示按照何种方式排序， c: 访问计数 l: 锁定时间 r: 返回记录 t: 查询时间 al:平均锁定时间 ar:平均返回记录数 at:平均查询时间 -t, 是top n的意思，即为返回前面多少条的数据； -g, 后边可以写一个正则匹配模式，大小写不敏感的； 比如 得到返回记录集最多的10个SQL。 mysqldumpslow -s r -t 10 /database/mysql/mysq_slow.log 得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 /database/mysql/mysq_slow.log 得到按照时间排序的前10条里面含有左连接的查询语句。 mysqldumpslow -s t -t 10 -g “left join” /mysql/mysq_slow.log 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。 mysqldumpslow -s r -t 20 /mysqldata/mysql/mysq-slow.log | more
关于慢查询日志的表中的数据个文本中的数据格式分析：
Count: 1 Time=4.64s (4s) Lock=0.58s (0s) Rows=0.0 (0), root[root]@rdc-manager2 DELETE FROM `sessions` WHERE `expires` < N Count: 6 Time=3.83s (23s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@rdc-manager2 UPDATE `authorization` SET `user_id` = 'S', `refresh_token` = 'S', `expiry_date` = 'S', `device_id` = 'S' WHERE user_id = 'S' AND device_id = 'S'
------------------------------------------------------------------------------------------
上述中的参数含义如下：
Count:6 语句出现了6次；
Time=3.83s (23s) 执行最长时间为3.83s，累计总耗费时间23s；
Lock=0.0s（0） 等待锁最长时间为0s，累计等待锁耗费时间为0s；
Rows=2194.9（9097604） 发送给客户端最多的行数为2194.9，累计发送给客户端的函数为90976404
----------------------------------
Count: 15 Time=3.60s (53s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@rdc-manager2 INSERT INTO `sessions` (`session_id`, `expires`, `data`) VALUES ('S', N, 'S') ON DUPLICATE KEY UPDATE `expires` = VALUES(`expires`), `data` = VALUES(`data`) Count: 7 Time=3.48s (24s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@rdc-manager2 UPDATE `user` SET last_login_time = 'S'WHERE id = 'S' Count: 6 Time=3.41s (20s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@rdc-manager2 INSERT INTO `log` SET `company_id` = 'S', `operator_id` = 'S', `category` = N, `type` = N, `log` = 'S', `create_date` = 'S' Count: 5 Time=3.02s (15s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@rdc-manager2 UPDATE `device` SET `user_id` = 'S', `company_id` = 'S', `model` = 'S', `platform` = N, `operating_system` = 'S', `operating_system_architecture` = N, `client_version` = 'S', `is_disabled` = N, `is_deleted` = N, `is_activated` = N, `last_login_time` = 'S' WHERE id = 'S' AND user_id = 'S' Count: 5 Time=2.45s (12s) Lock=0.00s (0s) Rows=1.0 (5), root[root]@rdc-manager2 SELECT * FROM device WHERE id = 'S' AND user_id in ( SELECT id FROM user WHERE (is_deleted = N AND (login_name = 'S' OR email = 'S' OR mobile = 'S')) AND company_id in (SELECT id FROM company WHERE domain_name = 'S') ) Count: 8 Time=2.40s (19s) Lock=0.00s (0s) Rows=1.0 (8), root[root]@rdc-manager2 SELECT * FROM `user` WHERE (is_deleted = N AND (login_name = 'S' OR email = 'S' OR mobile = 'S')) AND company_id in (SELECT id FROM company WHERE domain_name = 'S') Count: 1 Time=2.03s (2s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost select sleep(N)
慢查询的日志记录myql.slow_log表中，格式如下：
慢查询的日志记录到hostname.log文件中，格式如下：
在MySQL中有一个变量专门记录当前慢查询语句的个数：
输入命令：show global status like ‘%slow%’;
mysql> show global status like '%slow%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | Slow_launch_threads | 0 | | Slow_queries | 54 | +---------------------+-------+ 2 rows in set (0.00 sec)
显示当前数据库中与版本号 show variables like "%version%";
mysql> show variables like "%version%"; +--------------------------------------------------+------------------------------+ | Variable_name | Value | +--------------------------------------------------+------------------------------+ | group_replication_allow_local_lower_version_join | OFF | | innodb_version | 5.7.19 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1 | | version | 5.7.19-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | linux-glibc2.12 | +--------------------------------------------------+------------------------------+ 9 rows in set (0.00 sec)
浙公网安备 33010602011771号