如何提高mariaDB 10.10.6上的查询性能

bxfogqkk  于 2023-10-20  发布在  其他
关注(0)|答案(4)|浏览(89)

我有一个简单的查询，需要花很多时间来处理，查询非常简单，WHERE子句只有简单的比较器，但查询需要15到20秒在256 GB的服务器上处理。
下面是查询

SELECT ad.*, 
       an.* 
FROM addresses ad 
INNER JOIN announces an ON ad.id = an.address_id 
WHERE ( ad.city = 'city' ) 
ORDER BY an.postedAt DESC LIMIT 0, 100;

关于表结构的一些信息：

  • 所有字段[包括city和inserted_at]都是索引。
  • 每一行都有一堆字段，但没有long varcharblog，主要是int和varchar 2 255.我会说总共有30个领域
  • 表地址大约有500 k行，表通告大约有8 kk行
  • 如果我从这个查询中删除order by，它会像猎豹一样快速运行
  • 这个查询的总结果集（如果我删除limit子句）是600万行。

我试过使用FORCE INDEX并强制它使用posted_at索引，查询时间不到1秒，问题是这个查询是在代码的一部分中动态生成的，我不能触摸，我可以访问数据库并可以创建索引（有没有办法在2个表的2列上创建索引？）设置mysql参数或给予更多内存
下面是explain在这个查询上的结果：

+----+-------------+-------+-------+--------------------------------------------------------------+------------------------+---------+-----------------+--------+--------------------------------------------------------+
| id | select_type | table | type  | possible_keys                                                | key                    | key_len | ref             | rows   | Extra                                                  |
+----+-------------+-------+-------+--------------------------------------------------------------+------------------------+---------+-----------------+--------+--------------------------------------------------------+
| 1  | SIMPLE      | ad    | range | PRIMARY,unique2,idx_city_district,idx_city,index_city_street | idx_city               | 93      | NULL            | 291413 | Using index condition; Using temporary; Using filesort |
| 1  | SIMPLE      | an    | ref   | fk_address_id_announces,index_address_postedAt               | index_address_postedAt | 5       | fisgar_sp.ad.id | 2      |                                                        |
+----+-------------+-------+-------+--------------------------------------------------------------+------------------------+---------+-----------------+--------+--------------------------------------------------------+

下面是SHOW PROFILE在此查询上的结果

+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| Starting                       |  0.000184 |
| Waiting for query cache lock   |  0.000032 |
| starting                       |  0.000030 |
| Checking query cache for query |  0.000160 |
| checking permissions           |  0.000035 |
| Opening tables                 |  0.000070 |
| After opening tables           |  0.000034 |
| System lock                    |  0.000034 |
| table lock                     |  0.000034 |
| Waiting for query cache lock   |  0.000064 |
| init                           |  0.000087 |
| Optimizing                     |  0.000062 |
| Statistics                     |  0.000197 |
| Preparing                      |  0.000114 |
| Creating tmp table             |  0.000095 |
| Sorting result                 |  0.000040 |
| Executing                      |  0.000031 |
| Sending data                   | 18.195285 |
| Creating sort index            |  1.561521 |
| Removing tmp table             |  0.285116 |
| Creating sort index            |  0.000127 |
| End of update loop             |  0.000022 |
| Query end                      |  0.000015 |
| Commit                         |  0.000018 |
| closing tables                 |  0.000016 |
| Unlocking tables               |  0.000013 |
| closing tables                 |  0.000022 |
| Starting cleanup               |  0.000014 |
| Freeing items                  |  0.000023 |
| Updating status                |  0.000093 |
| Reset for next command         |  0.000016 |
+--------------------------------+-----------+
31 rows in set (0.001 sec)

在phpmyadmin中，表声明为9 GB，表地址为250 MB，所以我给出了innodb_buffer_pool_size=32G和sort_buffer_size=16GB，看看它是否会将整个表加载到内存中并在内存中排序，但文件排序一直显示在解释中，查询太慢。
有什么线索或者我可以进一步调查的吗？

更新

添加额外的信息，此查询在特定情况下执行缓慢，其中'city'是代表我们数据库的80%的特定城市，比方说纽约，如果我为一个较小的城市运行它，则查询速度很快。
对我来说发生的是：mysql为地址表使用城市索引，所以加载一堆地址，然后使用它的id来加载通告，这将基本上由表本身的80%组成...然后有这么多的数据，它需要排序，以获得最新的.所以问题就发生了
但是我如何在不触及查询的情况下修复它呢
1.这个查询的总结果集（如果我删除limit子句）是600万行。

  1. postedAt指的是表announce
  2. show create table的结果（省略了一些不相关的列名）
    表addresses：
CREATE TABLE `addresses` 
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s` varchar(255) DEFAULT NULL,
  `n` int(11) DEFAULT NULL,
  `e` varchar(30) DEFAULT NULL,
  `d` varchar(255) DEFAULT NULL,
  `z` varchar(8) DEFAULT NULL,
  `c` varchar(90) DEFAULT NULL,
  `lo` decimal(11,8) DEFAULT 0.00000000,
  `la` double(10,8) DEFAULT 0.00000000,
  `lola` point DEFAULT NULL,
  `p` varchar(300) DEFAULT NULL,
  `city` varchar(30) DEFAULT NULL,
  `inserted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique2` (`c`,`z`,`n`,`e`),
  KEY `lola` (`lola`(25)),
  KEY `idx_c_d` (`city`,`d`),
  KEY `idx_city` (`city`) USING HASH,
  KEY `index_city_s` (`city`,`s`)
) ENGINE=InnoDB AUTO_INCREMENT=44177177 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPACT |


 announces | CREATE TABLE `announces` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` int(1) NOT NULL DEFAULT 1,
  `address_id` int(11) DEFAULT NULL,
  `s` varchar(255) DEFAULT NULL,
  `a` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `d` blob DEFAULT NULL,
  `pt` varchar(50) NOT NULL DEFAULT '0',
  `f` int(2) DEFAULT 0,
  `dt` int(1) NOT NULL DEFAULT 0,
  `bp` int(11) DEFAULT 0,
  `rp` int(11) DEFAULT 0,
  `cn` varchar(100) DEFAULT NULL,
  `cp` int(11) DEFAULT 0,
  `iptu` int(11) DEFAULT 0,
  `s` int(11) DEFAULT 0,
  `br` int(11) DEFAULT 0,
  `brm` int(11) DEFAULT 0,
  `ps` int(11) DEFAULT 0,
  `ta` int(11) DEFAULT 0,
  `ua` int(11) DEFAULT 0,
  `pa` int(11) DEFAULT 0,
  `code` varchar(65) DEFAULT NULL,
  `extra` varchar(150) DEFAULT NULL,
  `pri` tinyint(1) NOT NULL DEFAULT 0,
  `per` mediumtext DEFAULT NULL,
  `postedAt` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `ti` int(11) DEFAULT NULL,
  `inserted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `tpo` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `source` (`source`),
  KEY `fk_address_id_announces` (`address_id`),
  KEY `inserted_at` (`inserted_at`),
  KEY `bp` (`bp`),
  KEY `pt` (`pt`),
  KEY `ua` (`ua`),
  KEY `index_pt_ta` (`pt`,`ta`),
  KEY `pri` (`pri`) USING HASH,
  KEY `index_posted_at` (`postedAt`) USING BTREE,
  KEY `index_address_postedAt` (`address_id`,`postedAt`),
  CONSTRAINT `fk_address_id_announces` FOREIGN KEY (`address_id`) REFERENCES `addresses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=278835392 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=COMPACT |

=========
下面是tmp文件夹

SELECT @@tmpdir;
+----------+
| @@tmpdir |
+----------+
| /tmp     |
+----------+
1 row in set (0.001 sec)

记住/tmp是指容器的结构
运行so和docker的hd是ssd
OPTIMIZER_TRACE的结果可以在链接https://pastecode.dev/s/BhuNr863yZ中看到
除了这个json之外，对于MISSING_BYTES_BEYOND_MAX_MEM_SIZE，查询返回0和0| INSUFFICIENT_PRIVILEGES|

说明：我可能需要一点时间来更新这个问题的最新信息，但这并不是因为我没有投入精力，是因为所有这些都需要在生产环境中进行测试活跃用户。因此，所有额外的信息，你们提供我在这里我需要等待几个小时后，所以我可以测试和保证，我的测试不会影响当前用户，也不是在系统中的其他负载将影响我的测试。话虽如此，我真的不明白为什么有些人投票关闭这个问题，你可耻！

MariaDB [(none)]> SELECT @@max_heap_table_size, @@tmp_table_size;
+-----------------------+------------------+
| @@max_heap_table_size | @@tmp_table_size |
+-----------------------+------------------+
|              16777216 |       2147483648 |
+-----------------------+------------------+
1 row in set (0.001 sec)

如果没有解释，下面的查询运行时间不到1秒。

MariaDB [fisgar_sp]> EXPLAIN SELECT ad.*, ad.* FROM announces an FORCE INDEX (index_posted_at) INNER JOIN addresses ad ON ad.id = an.address_id WHERE ( ad.city = 'New York' ) ORDER BY postedAt DESC LIMIT 0, 100;
+------+-------------+-------+--------+--------------------------------------------------------------+-----------------+---------+-------------------------+------+-------------+
| id   | select_type | table | type   | possible_keys                                                | key             | key_len | ref                     | rows | Extra       |
+------+-------------+-------+--------+--------------------------------------------------------------+-----------------+---------+-------------------------+------+-------------+
|    1 | SIMPLE      | an    | index  | NULL                                                         | index_posted_at | 5       | NULL                    | 200  | Using where |
|    1 | SIMPLE      | ad    | eq_ref | PRIMARY,unique2,idx_city_d,idx_city,index_city_s | PRIMARY         | 4       | fisgar_sp.an.address_id | 1    | Using where |
+------+-------------+-------+--------+--------------------------------------------------------------+-----------------+---------+-------------------------+------+-------------+
2 rows in set (0.001 sec)

根据@user1191247对SET STATEMENT max_heap_table_size=4294967295, tmp_table_size=4294967295 FOR SELECT的建议...工作得很好，查询从22秒到2秒，所以我可以调整那些特定的tcp设置，以获得巨大的改善
我感谢所有帮助我的人，特别是@user1191247，如果他愿意写一个答案，我可以接受这个答案

mariadb

4条答案

按热度按时间
2ul0zpep

2ul0zpep1#

显然，最好的选择是改进查询。延迟加载版本应该快得多，因为临时表的大小只是它的一小部分。
性能问题是由于必须将一个巨大的临时表写入磁盘，尽管由于某种原因它没有显示在SHOW PROFILE中。如果不能修改查询，最好的选择是确保临时表保留在内存中。
通过增加tmp_table_size和max_heap_table_size的大小，我们可以停止将临时表写入磁盘。
正如danblack在评论中建议的那样，您可以使用SET STATEMENT some_var=some_val FOR your query ...（参见SET STATEMENT）对单个查询执行此操作。通过为这两个变量设置不同的大小，您可以了解临时表使用了多少内存。
MariaDB上的最大值为-

  • max_heap_table_size是4294966272（4GiB -1 KiB）
  • tmp_table_size是4294967295（4GiB - 1B）

通过使用不同的@tmp_size值运行以下查询，您可以大致计算出临时表的大小。

SET @tmp_size = 4 * 1024 * 1024 * 1024; --   4GiB
-- SET @tmp_size = 2 * 1024 * 1024 * 1024; --   2GiB
-- SET @tmp_size =     1024 * 1024 * 1024; --   1GiB
-- SET @tmp_size =      512 * 1024 * 1024; -- 512MiB
-- SET @tmp_size =      256 * 1024 * 1024; -- 256MiB

SET STATEMENT max_heap_table_size=@tmp_size, tmp_table_size=@tmp_size FOR 
SELECT ad.*, 
       an.* 
FROM addresses ad 
INNER JOIN announces an ON ad.id = an.address_id 
WHERE ( ad.city = 'city' ) 
ORDER BY an.postedAt DESC LIMIT 0, 100;

请注意，对于非常大的max_heap_table_size，如果查询需要的值超过这个值，那么将导致将内存中的临时表复制到磁盘的额外开销。

赞(0）分享  回复(0）举报  2023-10-20
holgip5t

holgip5t2#

延迟加载。SELECT *可以搬运很多东西;让我们避免在只有100个被交付时拖走大约600万行。

SELECT  d2.*, n2.*
    FROM ( SELECT d1.id AS d1_id,
                  n1.id AS n1_id
               FROM addresses d1 
               JOIN announces n1 ON d1.id = n1.address_id 
               WHERE ( d1.city = 'city' ) 
               ORDER BY n1.postedAt DESC LIMIT 0, 100;
         ) x
    JOIN addresses d2  d1.id = x.d1_id
    JOIN announces n2  n2.id = x.d2_id

并且具有

addresses:  INDEX(city, id)
announces:  INDEX(postedAt. address_id,  id)
announces:  INDEX(address_id, postedAt,  id)

然后运行EXPLAIN SELECT ...。我认为n1和d1都会说“使用索引。这将给予改进性能的线索。
其他一些问题：

  • KEY idx_city ( city ) USING HASH--这将默默地变成`BTree。
  • 如果您还拥有INDEX(city, xx)，则不需要INDEX(city)。对于另一个表中的index_address_postedAt也是如此。
  • double(10,8)的（m，n）会起反作用，并在未来版本中删除;建议您切换到DECIMAL(10,8)或简单的DOUBLE。
赞(0）分享  回复(0）举报  2023-10-20
bfhwhh0e

bfhwhh0e3#

排序总是一个棘手的问题，特别是当一个特定的值代表80%的行时，正如您所提到的。在这种情况下，MySQL倾向于更喜欢表扫描（顺序的）而不是索引查找（随机的-随机扫描行的子集可能有意义，但80%对于随机读取来说是相当多的）。
话虽如此，你已经设法找到了解决方案（在posted_at索引上强制索引）。如果不能修改应用程序，可以使用负载均衡器，它允许重写通过它的查询。可以做到这一点的解决方案之一是ProxySQL。使用它，您可以动态地修改查询，并添加FORCE INDEX语句，这将加快查询速度。
当然，引入另一个软件会增加开销，这不是你可以轻易决定的事情，但是如果你的测试认为使用ProxySQL是可行的，你最终会在你的工具箱中找到一个工具，它将使你能够完全控制流量和进入数据库的SQL，无论你是否可以修改应用程序。
下面是一些你可能会发现有用的资源：
Query rewrite with ProxySQL: use case scenario - ProxySQL
ProxySQL: How To Rewrite A Query

赞(0）分享  回复(0）举报  2023-10-20
khbbv19g

khbbv19g4#

查询面临性能问题，需要执行排序操作。不能直接修改查询。
对于优化查询您需要在优化工作，因为：
1.您需要对索引进行优化，并且您不需要为地址表创建另一个索引，因为您已经为城市列创建了索引。
1.你需要在mysql的配置上下功夫，需要增加innodb_buffer_pool_size和sort_buffer_size。
1.表优化：正在为排序创建临时表。您可以通过调整tmp_table_size和max_heap_table_size配置变量来优化它。
我希望这将帮助您最小化查询性能。

赞(0）分享  回复(0）举报  2023-10-20
我来回答

相关问题

    查看更多

    热门标签

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

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com