10.4.13的性能比较慢

368yc8dk  于 2021-08-13  发布在  Java
关注(0)|答案(3)|浏览(347)

在将大型（3+gb）数据库从mysql数据迁移到mariadb之后，它在特定的查询性能方面遇到了问题，它是64位版本。对数据库进行分析、优化、重建。下面是mariadb的配置、数据库方案和查询。
非常感谢您对解决这个问题的方法/方式/地点/时间的建议。
机器参数为：intel core i5 cpu@3.6ghz，16gb ram，sandisk 512gb ssd，使用windows 10 v.1909。
性能较慢的sql查询（10秒，在MySQL5.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

sqlmysqlquery-performancemariadbquery-optimization

3条答案

按热度按时间
mqxuamgl

mqxuamgl1#

这真是个疑问。我相信你需要把它分解一下才能了解它的性能。
在我看来，您有两个子查询模式。这里有一个模式

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 )在这些查询模式中。索引范围扫描可能非常有效，但当它们处理多个相等筛选器，然后处理一个范围筛选器时效果最好( time BETWEEN this AND that )
因此，为了优化，我们需要以具有最大选择性的列开始您的多列索引。我们需要为您的查询模式提供复合覆盖索引。
我想你应该试试这个索引作为你的第一个模式。

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

你得到了不可预测的价值量和收盘价。
（如果这是我为我的雇主处理别人钱的问题，我会花几个小时来分析它的正确性。）

赞(0）分享  回复(0）举报  2021-08-13
0pizxfdo

0pizxfdo2#

在最近的mariadb和糟糕的查询中看到了这种行为（对不起，我不会粉饰它——有许多类似这样的子选择的查询只是糟糕的查询），我将在这里做一个猜测（因为您无法提供mysql 5.7中的explain计划）：
切换 semijoin=off 在你的 optimizer_switch 设置并查看它是否选择了一个不那么糟糕的执行计划。
我也忍不住注意到，您正在切换许多配置设置-没有人需要接触这些设置的绝大多数，所以我建议您从一个干净的配置开始，只有 innodb_buffer_pool_size 根据您的内存大小进行适当的设置。

赞(0）分享  回复(0）举报  2021-08-13
pxq42qpu

pxq42qpu3#

不要在结尾使用（m，n） FLOAT 或者 DOUBLE . 既然你有 ,0 ，你不妨用一些 INT 变体。
索引 CMD ( CMD , OPEN_TIME , CLOSE_TIME , LOGIN , VOLUME , SYMBOL , CLOSE_PRICE )--可能列太多了。
我希望你们的table是innodb。
除非你有其他程序占用内存，否则你可以增加内存 innodb_buffer_pool_size .
这种模式通常效率低下：
FROM ( SELECT ... ) JOIN ( SELECT ... ) @fd_time0 --使用@variables是有风险的；优化器可能会带来惊喜；也就是说，您不能依赖于它们的设置和使用时间(他们最终将被禁止。）
这个 UNION ALLs 似乎正好生成了两行，那么将这两个值相加？而不是 UNION 这就形成了一个 SUM ，请执行以下操作：

( SELECT ( SELECT ... ) + ( SELECT ... ) )

这个 WHERE 有4个范围。只有一个可以使用索引：

WHERE  t.CLOSE_TIME BETWEEN ...
                          AND  t.OPEN_TIME > '2012-08-26'
                          AND  t.SYMBOL LIKE '#AT&T%'
                          AND  t.CMD IN (0,1)

我建议您给优化器3个选择：

INDEX(close_time)
    INDEX(OPEN_TIME)
    INDEX(SYMBOL)

如果符号末尾不需要通配符，那么这些索引会更好：

INDEX(SYMBOL, close_time)
INDEX(SYMBOL, OPEN_TIME)

（我的偏好）而不是

t.OPEN_TIME BETWEEN '2020-05-21 09:30'
                AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND

我更喜欢

t.OPEN_TIME >= '2020-05-21 09:30'
    AND t.OPEN_TIME  < '2020-05-21 09:30' + INTERVAL 2 MINUTE
``` `SELECT MIN(t.CLOSE_TIME) AS fd_time, t.VOLUME ...` 会给出错误的数据！！！。看到标签了吗 `[groupwise-maximum]` .
赞(0）分享  回复(0）举报  2021-08-13
我来回答

相关问题

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com