MariaDB / MySQL性能优化Azure磁盘访问

Question

我们有很多的MariaDB，我们最近移到了“用于MariaDB服务器的Azure数据库”。大多数情况下，它可以正常工作，但与在旧服务器上运行相比，我们有时会遇到一些缓慢查询的障碍。让我们把我们的数据库叫做"customerdb“。

例如，我们有两个大表，data_orders和data_orders_cardpayment，这两个表都很大(数据订单还包含顺序的xml表示)。对于在data_orders中有大约900 K行的客户，以下查询如下所示：

explain SELECT SQL_NO_CACHE 
   SUM(T0.GiftCard+T0.CreditNote+T0.Nettbank+T0.Depositum) AS Sum 
FROM DATA_ORDERS_CARDPAYMENT T0 
INNER JOIN DATA_ORDERS T1 ON T0.OrderId = T1.Id 
WHERE T1.IsTransferred = 0 
  AND T1.CountedId = -1 
  AND T1.InstanceCode = 'B';
+------+-------------+-------+-------------+----------------------------------------------+-------------------------+---------+--------------------+------+-------------------------------------------------------+
| id   | select_type | table | type        | possible_keys                                | key                     | key_len | ref                | rows | Extra                                                 |
+------+-------------+-------+-------------+----------------------------------------------+-------------------------+---------+--------------------+------+-------------------------------------------------------+
|    1 | SIMPLE      | T1    | index_merge | PRIMARY,CountedId,InstanceCode,IsTransferred | IsTransferred,CountedId | 1,4     | NULL               |  256 | Using intersect(IsTransferred,CountedId); Using where |
|    1 | SIMPLE      | T0    | ref         | OrderId                                      | OrderId                 | 4       | customerdb.T1.Id   |    1 |                                                       |
+------+-------------+-------+-------------+----------------------------------------------+-------------------------+---------+--------------------+------+-------------------------------------------------------+

所以这只需要检查256行？好的!然后，我在一天开始时运行这个查询，它需要如下所示：

SELECT SQL_NO_CACHE
   SUM(T0.GiftCard+T0.CreditNote+T0.Nettbank+T0.Depositum) AS Sum 
FROM DATA_ORDERS_CARDPAYMENT T0 
INNER JOIN DATA_ORDERS T1 ON T0.OrderId = T1.Id 
WHERE T1.IsTransferred = 0 
  AND T1.CountedId = -1 
  AND T1.InstanceCode = 'B';
+------+
| Sum  |
+------+
|    0 |
+------+
1 row in set (1 min 38.283 sec)

这花了1分38秒。再次运行它，只需0.119秒、0.128秒、0.106秒等等。我在旧数据库"customerdb_moved“上运行了相同的查询，在一天开始时花了2.68秒，之后花了0.07秒(但这是一个非常强大的服务器)。

那么，为什么第一次，而仅仅是第一次，就需要永远这样做呢？由于一些未知的原因，我假设它必须将大部分表数据从磁盘加载到内存中，尽管理论上的索引应该避免这种情况。连续的负荷总是快的。

我能避免磁盘的初始加载吗？如果没有，如何加快Azure数据库中的初始磁盘访问时间？(顺便说一句，目前Azure中数据库服务器的定价层是8 vCores，80 GB内存，292 GB存储，876可用IOPS，内存优化。)

更新，我至少找到了罪魁祸首。解释声明不知怎么搞错了。运行以下代码时：

SELECT COUNT(*) 
FROM data_orders T1 
where T1.IsTransferred = 0 
  AND T1.CountedId = -1 
  AND T1.InstanceCode = 'B';

实际上，它返回了大约5,5k行，因为有一些旧行没有T1.IsTransferred-标志设置。我不知道为什么我的解释返回256行，我仍然认为最初的查询所用的时间应该少于1分38秒，最初是在第一个循环中对这5,5k行进行一天的求和。当将旧行设置为IsTransferred时，今天的查询在“解释”中为T1显示了5行，而来自T1的计数(*)显示了164个实际行，并且最初的第一次查询花费了大约3秒(对于164行的负载仍然很大)，然后对于后续的查询大约花费了0.02。

Answer 1

“使用intersect”通常意味着需要包含上述列的复合索引。

构建综合索引时，从用=或IS NULL测试的列(S)开始。

关于索引构建的更多信息：索引食谱

关于每日摘要-构建和维护一个汇总表

如果这些不能提供足够的帮助，那么可能会有更多晦涩的事情发生；我们需要看到实际的代码和SHOW CREATE TABLE。(更改列名和表名是可以的。)

至于MySQL和MariaDB --优化器在5.6 / 10.0时出现了分歧。它们都有了进一步的改善(只是偶尔的回归)。您可能已经经历过这样一种情况，即10.x添加了Azure还没有相应的优化。