About Slow query of MySQL 5.7 - hanywd123456’s blog

[root@node1 ~]# mysql --auto-rehash -u root -p'RootRoot!1' test_db　★接続
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26 MySQL Community Server (GPL)　★5.7.26

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \! ls -l;　★OSコマンドを実行してみる
合計 16
-rw-r--r--. 1 root root 2065 7月 22 16:32 addressbook.sql
-rw-------. 1 root root 1886 1月 9 2019 anaconda-ks.cfg
-rw-r--r--. 1 root root 1917 1月 9 2019 initial-setup-ks.cfg
drwxr-xr-x. 2 root root 4096 6月 19 14:25 mysql
drwxr-xr-x. 2 root root 20 7月 17 11:17 test
drwxr-xr-x. 2 root root 6 1月 9 2019 ダウンロード
drwxr-xr-x. 2 root root 6 1月 9 2019 テンプレート
drwxr-xr-x. 2 root root 6 1月 9 2019 デスクトップ
drwxr-xr-x. 2 root root 6 1月 9 2019 ドキュメント
drwxr-xr-x. 2 root root 6 1月 9 2019 ビデオ
drwxr-xr-x. 2 root root 6 1月 9 2019 音楽
drwxr-xr-x. 2 root root 6 1月 9 2019 画像
drwxr-xr-x. 2 root root 6 1月 9 2019 公開
mysql> show variables like '%secur%';　★secure_file_privパラメータを確認する
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.01 sec)

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 | /var/lib/mysql/node1-slow.log |　★
+----------------------------------------+-------------------------------+
15 rows in set (0.01 sec)

mysql> set global slow_query_log=1;　★
Query OK, 0 rows affected (0.00 sec)

mysql> set global long_query_time=0.1;　★
Query OK, 0 rows affected (0.00 sec)

mysql> set global log_queries_not_using_indexes=1;　★
Query OK, 0 rows affected (0.00 sec)

mysql> set global slow_query_log_file ='/usr/local/var/mysql/slow_query.log';　★slow_query_log_fileパラメータをセットする際にエラーになった
ERROR 1231 (42000): Variable 'slow_query_log_file' can't be set to the value of '/usr/local/var/mysql/slow_query.log'
mysql> \! ls -ld /usr/local/var/mysql/
drwxr-xr-x. 2 mysql mysql 6 7月 23 11:23 /usr/local/var/mysql/
mysql> \! touch /usr/local/var/mysql/slow_query.log　★ファイルを作成する
mysql> \! ls -l /usr/local/var/mysql/
合計 0
-rw-r--r--. 1 root root 0 7月 23 11:47 slow_query.log
mysql> \! chown mysql:mysql /usr/local/var/mysql/slow_query.log　★
mysql> \! chmod 755 /usr/local/var/mysql/slow_query.log　★十分の権限を与える
mysql> \! ls -l /usr/local/var/mysql/
合計 0
-rwxr-xr-x. 1 mysql mysql 0 7月 23 11:47 slow_query.log
mysql> set global slow_query_log_file ='/usr/local/var/mysql/slow_query.log';
ERROR 29 (HY000): File '/usr/local/var/mysql/slow_query.log' not found (Errcode: 13 - Permission denied)　★それでもエラー
mysql> show variables like '%secur%';
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.01 sec)

mysql> \! ls -l /var/lib/mysql-files/
合計 18512
-rwxr-xr-x. 1 mysql mysql 18951360 7月 23 10:29 KEN_ALL_UTF8.CSV
-rw-r-----. 1 mysql mysql 179 7月 23 11:39 slow_query.log
mysql> \! rm -rf /var/lib/mysql-files/slow_query.log
mysql> set global slow_query_log_file ='/var/lib/mysql-files/slow_query.log';　★secure_file_priv配下へ変更することで、設定可能になる
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@node1 ~]# mysql --auto-rehash -u root -p'RootRoot!1' test_db　★再度ログイン(そうでもしないと、long_query_timeが変更されたかを確認することはできません。)
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.26 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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 | ON |　★OK
| log_throttle_queries_not_using_indexes | 0 |
| long_query_time | 0.100000 |　★OK
| 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 |　★NG
| slow_query_log_file | /var/lib/mysql-files/slow_query.log |　★OK
+----------------------------------------+-------------------------------------+
15 rows in set (0.01 sec)

mysql> set global slow_query_log=1;　★もう一度、セット
Query OK, 0 rows affected (0.00 sec)

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 | ON |
| log_throttle_queries_not_using_indexes | 0 |
| long_query_time | 0.100000 |
| 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 | ON |　★OKになった
| slow_query_log_file | /var/lib/mysql-files/slow_query.log |
+----------------------------------------+-------------------------------------+
15 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM zipcode WHERE city='渋谷区' AND prefecture='東京都';　★索引が存在しないテーブルzipcodeでクエリを実行する
+----------+
| COUNT(*) |
+----------+
| 73 |
+----------+
1 row in set (0.27 sec)　★

mysql> \! cat /var/lib/mysql-files/slow_query.log
/usr/sbin/mysqld, Version: 5.7.26 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2019-07-23T02:50:13.178454Z
# User@Host: root[root] @ localhost Id: 4
# Query_time: 0.268895 Lock_time: 0.000324 Rows_sent: 1 Rows_examined: 124271
use test_db;
SET timestamp=1563850213;
SELECT COUNT(*) FROM zipcode WHERE city='渋谷区' AND prefecture='東京都';
mysql> show create table addressbook \G　★もっと試してみる
*************************** 1. row ***************************
Table: addressbook　★テストテーブルでは
Create Table: CREATE TABLE `addressbook` (
`id` int(3) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`address` varchar(20) DEFAULT NULL,
`telephone` varchar(13) DEFAULT NULL,
PRIMARY KEY (`id`)　★索引がある
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select count(name) from addressbook;　★索引を使わないクエリであれば
+-------------+
| count(name) |
+-------------+
| 27 |
+-------------+
1 row in set (0.00 sec)　★0.1を超えていない

mysql> \! cat /var/lib/mysql-files/slow_query.log
/usr/sbin/mysqld, Version: 5.7.26 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2019-07-23T02:50:13.178454Z
# User@Host: root[root] @ localhost Id: 4
# Query_time: 0.268895 Lock_time: 0.000324 Rows_sent: 1 Rows_examined: 124271
use test_db;
SET timestamp=1563850213;
SELECT COUNT(*) FROM zipcode WHERE city='渋谷区' AND prefecture='東京都';
# Time: 2019-07-23T02:56:52.763902Z
# User@Host: root[root] @ localhost [] Id: 4
# Query_time: 0.002047 Lock_time: 0.000297 Rows_sent: 1 Rows_examined: 27
SET timestamp=1563850612;
select count(name) from addressbook;　★
mysql> select count(id) from addressbook;　★索引を使うクエリであれば
+-----------+
| count(id) |
+-----------+
| 27 |
+-----------+
1 row in set (0.00 sec)　★0.1を超えていない