在前文 《阿里云自建MySQL的性能测试(多配置对比)》中，我们发现MySQL 8比MySQL5.7的性能低了33%，今天有空，重新探索了下原因(然而并没有彻底解决）。

1 默认参数差异

根据查阅资料，两者主要有以下默认参数差异：

• have_query_cache，8中cache被干没了
• bin-log，8开启，5.7没开启
• innodb_flush_neighbors，8关闭，5.7开启

2 实验1：5.7关闭cache

mysql -pmysql123 -e "SET GLOBAL query_cache_size = 0;"

结果比之前的版本低10%，这样两者差距缩小到30%

SQL statistics:
    queries performed:
        read:                            475426
        write:                           135836
        other:                           67918
        total:                           679180
    transactions:                        33959  (1696.56 per sec.)
    queries:                             679180 (33931.10 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          20.0151s
    total number of events:              33959

Latency (ms):
         min:                                    4.76
         avg:                                   18.85
         max:                                  176.83
         95th percentile:                       36.89
         sum:                               640217.62

Threads fairness:
    events (avg/stddev):           1061.2188/20.12
    execution time (avg/stddev):   20.0068/0.00

3 实验2 MySQL 8关闭bin-log

无差异

SQL statistics:
    queries performed:
        read:                            335706
        write:                           95916
        other:                           47958
        total:                           479580
    transactions:                        23979  (1197.61 per sec.)
    queries:                             479580 (23952.21 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
    total time:                          20.0210s
    total number of events:              23979
Latency (ms):
         min:                                    7.87
         avg:                                   26.70
         max:                                  170.96
         95th percentile:                       46.63
         sum:                               640354.71
Threads fairness:
    events (avg/stddev):           749.3438/24.03
    execution time (avg/stddev):   20.0111/0.01

4 实验2 MySQL 8打开innodb_dedicated_server

这是推荐在独立服务器上，开启的优化参数，是个自适应参数

#!/bin/bash
NAME="mysql"
PUID="1000"
PGID="1000"
MYSQL_ROOT_PASS="mysql123"

VOLUME_DATA="$HOME/docker_data/mysql"
mkdir -p $VOLUME_DATA 

docker ps -q -a --filter "name=$NAME" | xargs -I {} docker rm -f {}
docker run \
    --hostname $NAME \
    --name $NAME \
    --volume "$VOLUME_DATA":/var/lib/mysql \
    --env MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASS \
    --env PUID=$PUID \
    --env PGID=$PGID \
    -p 3306:3306 \
    --detach \
    --restart always \
    mysql:8 \
    --default-authentication-plugin=mysql_native_password \
    --innodb_dedicated_server=on \
    --disable-log-bin

打开后，确实性能有所提升，不过说实话，5.7我们并没有对binlog参数做优化，所以这个影响并不可靠。

SQL statistics:
    queries performed:
        read:                            410214
        write:                           117204
        other:                           58602
        total:                           586020
    transactions:                        29301  (1463.53 per sec.)
    queries:                             586020 (29270.52 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          20.0195s
    total number of events:              29301

Latency (ms):
         min:                                    6.90
         avg:                                   21.85
         max:                                   97.61
         95th percentile:                       38.94
         sum:                               640275.84

Threads fairness:
    events (avg/stddev):           915.6562/25.15
    execution time (avg/stddev):   20.0086/0.00

其他的优化，各种乱试，没有什么结果，最终MySQL 8落后MySQL 5.7 约 15%。

后记，阿里云数据库内核组也发现了8的写入性能差，参见《MySQL · 最佳实践 · 8.0 redo log写入性能问题分析》