mysql - 与 MySQL 5.7.30 相比，MariaDB 10.4.13 性能较慢

Question

在将大型(3+ GB)数据库从 MySQL 数据迁移到 64 位版本的 MariaDB 后，遇到特定查询性能问题。数据库被分析、优化、重建。下面是 MariaDB 的配置、数据库方案和有问题的查询。

非常感谢关于什么/如何/在哪里/何时解决这个问题的建议。

机器参数为:Intel Core i5 CPU @3.6GHz，16GB RAM，Sandisk 512GB SSD，使用 Windows 10 v.1909。

性能较慢的 SQL 查询(10 秒，以前在 MySQL 5.7 上约为 1 秒):

SELECT * FROM (
        SELECT 
      '#AT&T' AS instrument,
      (SELECT '2020-05-21 09:30' AS report_period) report_period,
    #Average price
        (SELECT AVG(avg_price.avg_price) AS avg_price FROM 
        (
          SELECT  AVG(t.CLOSE_PRICE) AS avg_price
          FROM mt4_trades t
          WHERE t.CLOSE_TIME BETWEEN '2020-05-21 09:30' AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND)  AND t.OPEN_TIME > '2012-08-26' 
          AND t.SYMBOL LIKE '#AT&T%' AND t.CMD IN (0,1) 
        UNION ALL
          SELECT  AVG(t.OPEN_PRICE) AS avg_price
          FROM mt4_trades t
          WHERE t.OPEN_TIME BETWEEN '2020-05-21 09:30' AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND) 
          AND t.SYMBOL LIKE '#AT&T%' AND t.CMD IN (0,1)  

        ) avg_price) avg_price,          

      #Total deals value
        (
        SELECT SUM(total_deals_value.total_deals_value) AS total_deals_value FROM   (
          SELECT SUM(t.VOLUME/100.0 * 1  * t.CLOSE_PRICE ) AS total_deals_value
          FROM mt4_trades t
          WHERE t.CLOSE_TIME BETWEEN '2020-05-21 09:30' AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND) AND t.OPEN_TIME > '2012-08-26' 
          AND t.SYMBOL LIKE '#AT&T%'  AND t.CMD IN (0,1) 
        UNION ALL           
          SELECT SUM(t.VOLUME/100.0 * 1  * t.OPEN_PRICE ) AS total_deals_value      
          FROM mt4_trades t
          WHERE t.OPEN_TIME BETWEEN '2020-05-21 09:30' AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND)   
          AND t.SYMBOL LIKE '#AT&T%'  AND t.CMD IN (0,1)  
          ) total_deals_value) AS total_deals_value) result

        LEFT OUTER JOIN   
        (SELECT '#AT&T' AS instrument, @fd_time0 AS fd_time, @fd_price0 AS fd_price, 
          (@fd_volume0/100.0 * 1  * @fd_price0 ) AS fd_volume 
            FROM (
              SELECT @fd_time0 := fd_time AS fd_time, @fd_volume0 := VOLUME AS VOLUME, @fd_price0 := PRICE AS PRICE 
              FROM 
                  (SELECT MIN(t.CLOSE_TIME) AS fd_time, t.VOLUME, t.CLOSE_PRICE AS PRICE FROM mt4_trades t WHERE t.CLOSE_TIME BETWEEN 
                    DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND) AND '2020-05-21 11:30' AND t.OPEN_TIME > '2012-08-26' 
                    AND t.SYMBOL LIKE '#AT&T%' 
                  UNION ALL
                  SELECT MIN(t.OPEN_TIME) AS fd_time, t.VOLUME, t.OPEN_PRICE AS PRICE FROM mt4_trades t WHERE t.OPEN_TIME BETWEEN 
                    DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND) AND '2020-05-21 11:30' 
                    AND t.SYMBOL LIKE '#AT&T%'   
                    ORDER BY fd_time) first_deal WHERE first_deal.fd_time IS NOT NULL ORDER BY first_deal.fd_time ASC LIMIT 1
            ) AS first_deal) temp_result ON temp_result.instrument =  result.instrument 

SQL 查询说明:

为表创建SQL:

CREATE TABLE `mt4_trades` (
`TICKET` INT(11) UNSIGNED NOT NULL,
`LOGIN` INT(11) UNSIGNED NOT NULL,
`SYMBOL` VARCHAR(16) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
`DIGITS` TINYINT(3) UNSIGNED NOT NULL,
`CMD` TINYINT(3) UNSIGNED NOT NULL,
`VOLUME` MEDIUMINT(8) UNSIGNED NOT NULL,
`OPEN_TIME` DATETIME NOT NULL,
`OPEN_PRICE` FLOAT(12,0) NOT NULL,
`SL` FLOAT(12,0) NOT NULL,
`TP` FLOAT(12,0) NOT NULL,
`CLOSE_TIME` DATETIME NOT NULL,
`EXPIRATION` DATETIME NOT NULL,
`REASON` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`CONV_RATE1` FLOAT(12,0) NOT NULL,
`CONV_RATE2` FLOAT(12,0) NOT NULL,
`COMMISSION` FLOAT(12,0) NOT NULL,
`COMMISSION_AGENT` FLOAT(12,0) NOT NULL,
`SWAPS` FLOAT(12,0) NOT NULL,
`CLOSE_PRICE` FLOAT(12,0) NOT NULL,
`PROFIT` FLOAT(12,0) NOT NULL,
`TAXES` FLOAT(12,0) NOT NULL,
`COMMENT` VARCHAR(32) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
`INTERNAL_ID` INT(11) NOT NULL,
`MARGIN_RATE` FLOAT(12,0) NOT NULL,
`TIMESTAMP` INT(11) UNSIGNED NOT NULL,
`MAGIC` INT(11) NOT NULL DEFAULT '0',
`GW_VOLUME` INT(11) NOT NULL DEFAULT '0',
`GW_OPEN_PRICE` INT(11) NOT NULL DEFAULT '0',
`GW_CLOSE_PRICE` INT(11) NOT NULL DEFAULT '0',
`MODIFY_TIME` DATETIME NOT NULL,
PRIMARY KEY (`TICKET`) USING BTREE,
INDEX `INDEX_STAMP` (`TIMESTAMP`, `COMMENT`) USING BTREE,
INDEX `CMD` (`CMD`, `OPEN_TIME`, `CLOSE_TIME`, `LOGIN`, `VOLUME`, `SYMBOL`, `CLOSE_PRICE`) USING 
BTREE
)
COLLATE='utf8_general_ci'
;

MariaDB 的 my.ini

[mysqld]
port= 3306
socket = "C:/xampp/mysql/mysql.sock"
basedir = "C:/xampp/mysql" 
tmpdir = "C:/xampp/tmp" 
datadir = "C:/xampp/mysql/data"
log_error = "mysql_error.log"
pid_file = "mysql.pid"
collation_server=utf8_general_ci
character_set_server=utf8

## CUSTOM EDIT
sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,STRICT_TRANS_TABLES
skip_external_locking
skip_name_resolve
max_connections                 = 200
table_open_cache                = 10000
table_definition_cache          = 2000
open_files_limit                = 20000
##MyISAM setting
key_buffer                      = 512M
myisam_sort_buffer_size         = 2M
#
max_allowed_packet              = 16M
max_sort_length                 = 16384
sort_buffer_size                = 1M
net_buffer_length               = 64K
read_buffer_size                = 256K
read_rnd_buffer_size            = 512K
#INNO DB settings
innodb_file_per_table           = 1
innodb_buffer_pool_size         = 4G
innodb_sort_buffer_size         = 16M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size            = 1024M
innodb_log_buffer_size          = 32M
innodb_flush_log_at_trx_commit  = 2
innodb_stats_on_metadata        = 0
innodb_lock_wait_timeout        = 600
innodb_flush_method             = normal
#A minor optimization when writing blocks to disk. Use 0 for SSD drives; 1 for HDD.
innodb_flush_neighbors          = 0
innodb_io_capacity              = 2000
#
innodb_buffer_pool_instances    = 3
innodb_thread_concurrency       = 12
innodb_autoextend_increment     = 64
innodb_read_io_threads          = 16
innodb_write_io_threads         = 16
concurrent_insert               = 2
thread_stack                    = 512K
interactive_timeout             = 600
wait_timeout                    = 600
query_cache_type                = 2
query_cache_limit               = 64M
query_cache_min_res_unit        = 1
query_cache_size                = 16M
thread_cache_size               = 128
low_priority_updates
tmp_table_size                  = 4M
max_heap_table_size             = 4M
bulk_insert_buffer_size         = 256M
group_concat_max_len            = 512K
# Define which query should be considered as slow, in seconds
long_query_time                 = 6
join_cache_level                = 8
# Size limit for the whole join
#join_buffer_space_limit        = 512M
join_buffer_size                = 4M
# Optimizer switches
optimizer_switch                ='orderby_uses_equalities=on'
optimizer_switch                ='mrr=on,mrr_sort_keys=on'
optimizer_switch                ='index_merge_sort_intersection=on'
optimizer_switch                ='optimize_join_buffer_size=on'
optimizer_switch                ='join_cache_bka=on'
optimizer_switch                ='join_cache_hashed=on'
optimizer_switch='in_to_exists=on'
optimizer_switch='join_cache_incremental=on'
#optimizer_switch='loosescan=on'

# Where do all the plugins live
plugin_dir = "C:/xampp/mysql/lib/plugin/" 
server-id   = 1

Answer 1

这是一个很好的查询。我相信您需要对其进行分解才能了解其性能。

在我看来，您有两个子查询模式。这是一种模式

      SELECT something_or_other
        FROM mt4_trades t
       WHERE t.CLOSE_TIME BETWEEN '2020-05-21 09:30' 
                              AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND)  
         AND t.OPEN_TIME > '2012-08-26' 
         AND t.SYMBOL LIKE '#AT&T%'
         AND t.CMD IN (0,1) 

这是另一个

      SELECT something_or_other
        FROM mt4_trades t
       WHERE t.OPEN_TIME BETWEEN '2020-05-21 09:30'
                             AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND) 
        AND t.SYMBOL LIKE '#AT&T%'
        AND t.CMD IN (0,1)  

不幸的是，对于利用索引，您在这些查询模式中没有相等过滤器(WHERE col=val)。索引范围扫描可能非常有效，但当它们处理多个相等过滤器然后处理一个范围过滤器时，它们的效果最好。 (这个和那个之间的时间)

因此，为了优化，我们需要从具有最大选择性的列开始您的多列索引。我们需要compound covering indexes为您的查询模式。

我认为你应该为你的第一个模式尝试这个索引。

CREATE INDEX closedex ON mt4_trades
     (CLOSE_TIME,  CMD, OPEN_TIME, SYMBOL, VOLUME, CLOSE_PRICE, LOGIN)

对于你的第二个模式，它有点简单

CREATE INDEX opendex ON mt4_trades
     (OPEN_TIME,  CMD, SYMBOL, VOLUME, CLOSE_PRICE, LOGIN)

您需要这两个索引，因为(我猜)您最具选择性的列是 CLOSE_TIME 和 OPEN_TIME。您还应该尝试将 CMD 放在这些索引的第一位；也许 MariaDB 知道如何有效地为 CMD IN (0,1) 使用索引。

关键是让查询规划器能够仅从索引满足查询，而不必跳回表。

如果您可以将 SYMBOL LIKE 'value%' 更改为 SYMBOL = 'value' 并且您的应用程序仍能正常工作，请这样做。然后将 SYMBOL 放在索引的第一位；这是平等匹配。

(重要说明:在您的查询中，如

SELECT MIN(t.CLOSE_TIME) AS fd_time, t.VOLUME, t.CLOSE_PRICE AS PRICE

您将获得不可预测的 VOLUME 和 CLOSE_PRICE 值。

(如果这是我为我的雇主处理其他人的钱的查询，我会花几个小时来分析它的正确性。)