1、查询执行速度慢的sql语句
- 查看当前“慢语句”标准：show variables like ‘%long%’
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 10.00000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> show variables like '%slow%';
+---------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/ubuntu-slow.log |
+---------------------------+--------------------------------+
5 rows in set (0.00 sec)
- 开启日志：set global slow_query_log = on;
- 修改时间标准：set long_query_time = 0.5;
- slow_query_log_file为日志文件所在路径；
- 查看日志
root@ubuntu:/var/lib/mysql# cat ubuntu-slow.log
/usr/sbin/mysqld, Version: 5.7.25-0ubuntu0.18.04.2 ((Ubuntu)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 2019-04-24T04:51:29.534395Z
# User@Host: root[root] @ localhost [] Id: 2
# Query_time: 0.000295 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
use demo;
SET timestamp=1556081489;
set global slow_query_log = on;
2、慢语句详情
- explain命令，使用explain命令可以查看到当前语句执行的详细情况；
mysql> explain select * from user \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
- profiles属性：
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.01 sec)
mysql> set profiling = on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------+
| 1 | 0.00293625 | show variables like '%profil%' |
+----------+------------+--------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from user;
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
| id | user_name | sex | password | email | address | fee | age | create_at |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
| 1 | sian | 男 | e10adc3949ba59abbe56e057f20f883e | yusian@163.com | NULL | 1200.00 | 32 | 2019-04-23 15:57:29 |
| 2 | 余西安 | 男 | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com | NULL | 1002.00 | 33 | 2019-04-23 21:00:03 |
| 3 | Jack | 男 | c81e728d9d4c2f636f067f89cc14862c | jack@yusian.com | NULL | 0.00 | 22 | 2019-04-21 20:11:59 |
| 4 | Rose | 女 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | rose@yusian.com | NULL | 0.00 | 18 | 2019-04-22 10:46:41 |
| 5 | Sim | 女 | a87ff679a2f3e71d9181a67b7542122c | sim@yusian.com | NULL | 0.00 | 28 | 2019-04-22 10:46:41 |
| 6 | Tony | 男 | c4ca4238a0b923820dcc509a6f75849b | tony@yusian.com | NULL | 0.00 | 22 | 2019-04-21 20:41:26 |
| 7 | lucy | 男 | c81e728d9d4c2f636f067f89cc14862c | lucy@163.com | NULL | 0.00 | 0 | 2019-04-23 20:13:01 |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
7 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------+
| 1 | 0.00293625 | show variables like '%profil%' |
| 2 | 0.00036200 | select * from user |
+----------+------------+--------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 2;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000142 |
| checking permissions | 0.000011 |
| Opening tables | 0.000015 |
| init | 0.000020 |
| System lock | 0.000010 |
| optimizing | 0.000004 |
| statistics | 0.000013 |
| preparing | 0.000009 |
| executing | 0.000003 |
| Sending data | 0.000091 |
| end | 0.000005 |
| query end | 0.000007 |
| closing tables | 0.000006 |
| freeing items | 0.000016 |
| cleaning up | 0.000012 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
3、查询语句慢，可以通过加索引的方式解决；
- alter table xxx add index(xxxx);
mysql> explain select * from user where age = 32;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> alter table user add index(age);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from user where age = 32;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | age | age | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
4、缓存
- 查询缓存开关：query_cache_type
- show variables like ‘%cache%’可以查看该值
- set global query_cache_type = ON; 打开，或在配置文件中加query_cache_type = ON；重启mysql服务
mysql> show variables like '%cache%';
+--------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| have_query_cache | YES |
| host_cache_size | 279 |
| innodb_disable_sort_file_cache | OFF |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_total_cache_size | 640000000 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| metadata_locks_cache_size | 1024 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| stored_program_cache | 256 |
| table_definition_cache | 1400 |
| table_open_cache | 2000 |
| table_open_cache_instances | 16 |
| thread_cache_size | 8 |
+--------------------------------+----------------------+
24 rows in set (0.00 sec)
- 缓存命中数，Qcache_hits
- show status like ‘%cache%’
mysql> show status like '%cache%';
+--------------------------------+----------+
| Variable_name | Value |
+--------------------------------+----------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Com_assign_to_keycache | 0 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16760152 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 146 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
| Ssl_callback_cache_hits | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_used_session_cache_entries | 0 |
| Table_open_cache_hits | 213 |
| Table_open_cache_misses | 42 |
| Table_open_cache_overflows | 0 |
| Threads_cached | 0 |
+--------------------------------+----------+
25 rows in set (0.01 sec)