When I execute the same select clause on the same table both in MySQL8.0 and MySQL5.7, I find the execution time is not what I expect.
The same query clause takes more time in 8.0 than that in 5.7
A simple table contains a json type and other simple data types.
CREATE TABLE student (
auto_id INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
stu_info JSON,
stu_id INT(10) UNSIGNED,
stu_num VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COMMENT='JSON performance test';
Create table both in MySQL5.7 and MySQL8.0
Both databases run on machines with the same configuration (4-core; 8000MB RAM; 100GB storage)
Insert 100000 entries into both of them, and the data looks like below:
+---------+---------------------------------+--------+----------+
| auto_id | stu_info | stu_id | stu_num |
+---------+---------------------------------+--------+----------+
| 1 | {"Age": 20, "Name": "abc1def"} | 1 | abc1def |
| 2 | {"Age": 51, "Name": "abc2def"} | 2 | abc2def |
| 3 | {"Age": 100, "Name": "abc3def"} | 3 | abc3def |
| 4 | {"Age": 87, "Name": "abc4def"} | 4 | abc4def |
| 5 | {"Age": 45, "Name": "abc5def"} | 5 | abc5def |
| 6 | {"Age": 49, "Name": "abc6def"} | 6 | abc6def |
| 7 | {"Age": 40, "Name": "abc7def"} | 7 | abc7def |
| 8 | {"Age": 61, "Name": "abc8def"} | 8 | abc8def |
| 9 | {"Age": 26, "Name": "abc9def"} | 9 | abc9def |
| 10 | {"Age": 20, "Name": "abc10def"} | 10 | abc10def |
...........................................................
+---------+---------------------------------+--------+----------+
When I commencing a query below in both of the mysql db:
SELECT auto_id, stu_info, stu_id, stu_num
FROM student
WHERE JSON_UNQUOTE(JSON_EXTRACT(stu_info,'$.Name')) IN ('abc1def','abc2def','abc3def','abc4def','abc5def','abc6def','abc7def','abc8def','abc9def','abc10def');
I have the same result BUT DIFFERENT EXECUTION TIME: MySQL8.0 is slower than 5.7
IN 8.0
mysql> SELECT auto_id, stu_info, stu_id, stu_num
-> FROM student
-> WHERE JSON_UNQUOTE(JSON_EXTRACT(stu_info,'$.Name')) IN ('abc1def','abc2def','abc3def','abc4def','abc5def','abc6def','abc7def','abc8def','abc9def','abc10def');
+---------+---------------------------------+--------+----------+
| auto_id | stu_info | stu_id | stu_num |
+---------+---------------------------------+--------+----------+
| 1 | {"Age": 84, "Name": "abc1def"} | 1 | abc1def |
| 2 | {"Age": 51, "Name": "abc2def"} | 2 | abc2def |
| 3 | {"Age": 51, "Name": "abc3def"} | 3 | abc3def |
| 4 | {"Age": 54, "Name": "abc4def"} | 4 | abc4def |
| 5 | {"Age": 88, "Name": "abc5def"} | 5 | abc5def |
| 6 | {"Age": 54, "Name": "abc6def"} | 6 | abc6def |
| 7 | {"Age": 62, "Name": "abc7def"} | 7 | abc7def |
| 8 | {"Age": 29, "Name": "abc8def"} | 8 | abc8def |
| 9 | {"Age": 80, "Name": "abc9def"} | 9 | abc9def |
| 10 | {"Age": 63, "Name": "abc10def"} | 10 | abc10def |
+---------+---------------------------------+--------+----------+
10 rows in set (0.10 sec)
IN 5.7
mysql> SELECT auto_id, stu_info, stu_id, stu_num
-> FROM student
-> WHERE JSON_UNQUOTE(JSON_EXTRACT(stu_info,'$.Name')) IN ('abc1def','abc2def','abc3def','abc4def','abc5def','abc6def','abc7def','abc8def','abc9def','abc10def');
+---------+---------------------------------+--------+----------+
| auto_id | stu_info | stu_id | stu_num |
+---------+---------------------------------+--------+----------+
| 1 | {"Age": 20, "Name": "abc1def"} | 1 | abc1def |
| 2 | {"Age": 51, "Name": "abc2def"} | 2 | abc2def |
| 3 | {"Age": 100, "Name": "abc3def"} | 3 | abc3def |
| 4 | {"Age": 87, "Name": "abc4def"} | 4 | abc4def |
| 5 | {"Age": 45, "Name": "abc5def"} | 5 | abc5def |
| 6 | {"Age": 49, "Name": "abc6def"} | 6 | abc6def |
| 7 | {"Age": 40, "Name": "abc7def"} | 7 | abc7def |
| 8 | {"Age": 61, "Name": "abc8def"} | 8 | abc8def |
| 9 | {"Age": 26, "Name": "abc9def"} | 9 | abc9def |
| 10 | {"Age": 20, "Name": "abc10def"} | 10 | abc10def |
+---------+---------------------------------+--------+----------+
10 rows in set (0.07 sec)
And when the size of IN list grows, the time gap grows as well.
When the data size grows to 1 million, the MySQL8.0 takes 900ms to execute the query but MySQL5.7 only takes 600ms. Technically speaking, shouldn't it be the other way around？
FURTHER INFORMATION:
SHOW VARIABLES LIKE '%log_bin%'
IN 8.0
mysql> show variables like '%log_bin%';
+----------------------------------+---------------------------------------------+
| Variable_name | Value |
+----------------------------------+---------------------------------------------+
| forbid_remote_change_sql_log_bin | OFF |
| log_bin | ON |
| log_bin_basename | /data1/mysql_root/log/20477/mysql-bin |
| log_bin_index | /data1/mysql_root/log/20477/mysql-bin.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+----------------------------------+---------------------------------------------+
IN 5.7
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------+
| log_bin | ON |
| log_bin_basename | /data1/mysql_root/log/20169/mysql-bin |
| log_bin_index | /data1/mysql_root/log/20169/mysql-bin.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------------------------+
SHOW VARIABLES LIKE '%flush%'
IN 8.0
mysql> show variables like '%flush%';
+------------------------------------+----------+
| Variable_name | Value |
+------------------------------------+----------+
| binlog_max_flush_queue_time | 0 |
| flush | OFF |
| flush_time | 0 |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbors | 0 |
| innodb_flush_redo_using_fdatasync | OFF |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
| innodb_idle_flush_pct | 100 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
+------------------------------------+----------+
IN 5.7
mysql> show variables like '%flush%';
+--------------------------------+----------+
| Variable_name | Value |
+--------------------------------+----------+
| binlog_max_flush_queue_time | 0 |
| flush | OFF |
| flush_time | 0 |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbors | 0 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
+--------------------------------+----------+
AND both of them turn binlog on.
