论mysql5.7.13性能优化之索引优化

本篇文章是使用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秒

• 结果