本篇文章是使用MySQL5.7.17版本!
$ mysql --version
mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper
- 查看MySQL是否开启慢查询:
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 | OFF |
| slow_query_log_file | /var/lib/mysql/773786ec64cf-slow.log |
+---------------------------+--------------------------------------+
5 rows in set (0.01 sec)
如图上,是没有开启慢查询日志!
- 开启MySQL慢查询:
在/etc/mysql/mysql.conf.d/mysqld.cnf
中的[mysqld]
中加入如下代码:
slow_query_log=On #开启慢查询
slow_query_log_file=/var/log/mysql/mysql_slow_query.log #定义慢查询日志的路径
slow_launch_time=1 #查过多少秒的查询算是慢查询,我这里定义的是1秒
log_queries_not_using_indexes=ON #记录下没有使用索引的query
重启MySQLservice mysql restart
,再次查看慢查询状态:
mysql> show variables like '%slow%';
+---------------------------+-------------------------------------+
| Variable_name | Value |
+---------------------------+-------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 1 |
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/mysql_slow_query.log |
+---------------------------+-------------------------------------+
- 监测慢查询日志的变化:
sudo tail -f /var/log/mysql/mysql_slow_query.log
分别创建两个库
test_slow
和test_slow_2
,在这两个库中分别添加表test_finas
并插入677598
条数据!
test_slow.test_finas
没有索引字段
test_slow_2.test_finas
索引字段:date
、country
、title
、status
效果对比:
1. test_slow_2.test_finas 有索引[67W条数据]
mysql> desc select * from test_slow_2.test_finas where date=1489334400\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_finas
partitions: NULL
type: ref
possible_keys: date_index
key: date_index
key_len: 4
ref: const
rows: 120
filtered: 100.00
Extra: NULL
mysql> select * from test_slow_2.test_finas where date=1489334400;
...
120 rows in set (0.01 sec)
我们可以看到这次查询命中了索引date_index
,扫描了120行数据。查询时长0.01秒
插入4倍的数据[270W条数据]:
mysql> insert into test_slow_2.test_finas select * from test_slow_2.test_finas;
Query OK, 677568 rows affected (4 min 36.66 sec)
Records: 677568 Duplicates: 0 Warnings: 0
...
mysql> insert into test_slow_2.test_finas select * from test_slow_2.test_finas;
Query OK, 1355136 rows affected (18 min 12.04 sec)
Records: 1355136 Duplicates: 0 Warnings: 0
...
mysql> select count(*) from test_slow_2.test_finas;
+----------+
| count(*) |
+----------+
| 2710272 |
+----------+
1 row in set (2.03 sec)
我们可以看到插入4倍的数据是非常慢的。执行时长:22分48.70秒!不过实际过程中这么疯狂的插入数据还是少的
再次执行查询:
mysql> desc select * from test_slow_2.test_finas where date=1489334400\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_finas
partitions: NULL
type: ref
possible_keys: date_index
key: date_index
key_len: 4
ref: const
rows: 480
filtered: 100.00
Extra: NULL
mysql> select * from test_slow_2.test_finas where date=1489334400;
...
480 rows in set (0.03 sec)
我们可以看到这次查询命中了索引date_index
,扫描了480行数据。查询时长0.03秒
2. test_slow.test_finas 没有索引[67W条数据]
mysql> desc select * from test_slow.test_finas where date=1489334400\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_finas
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 673272
filtered: 10.00
Extra: Using where
mysql> select * from test_slow.test_finas where date=1489334400;
...
120 rows in set (0.67 sec)
我们可以看到这次查询没有索引,扫描了673272行数据。查询时长0.67秒
插入4倍数据[270W条数据]:
mysql> insert into test_slow.test_finas select * from test_slow.test_finas;
Query OK, 677568 rows affected (20.83 sec)
Records: 677568 Duplicates: 0 Warnings: 0
...
mysql> insert into test_slow.test_finas select * from test_slow.test_finas;
Query OK, 1355136 rows affected (48.58 sec)
Records: 1355136 Duplicates: 0 Warnings: 0
...
mysql> select count(*) from test_slow.test_finas;
+----------+
| count(*) |
+----------+
| 2710272 |
+----------+
1 row in set (1.61 sec)
再次执行查询:
mysql> desc select * from test_slow.test_finas where date=1489334400\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_finas
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2625947
filtered: 10.00
Extra: Using where
mysql> select * from test_slow.test_finas where date=1489334400;
...
480 rows in set (3.02 sec)
我们可以看到这次查询没有索引,扫描了2625947行数据。查询时长3.02秒
- 结果
表 | 记录数 | 索引 | 动作 | 执行时长 |
---|---|---|---|---|
test_slow.test_finas | 67W | 无 | 条件查询 | 0.67秒 |
test_slow_2.test_finas | 67W | date、title、country | 条件查询 | 0.01秒 |
test_slow.test_finas | 270W | 无 | 条件查询 | 3.02秒 |
test_slow_2.test_finas | 270W | date、title、country | 条件查询 | 0.03秒 |