时间戳

首页 > 解决方案 > 简单查询 MariaDB 变得非常慢

mysql - 简单查询 MariaDB 变得非常慢

问题描述

我有这个简单的查询

SELECT  item_code, stock_value, name, warehouse
    FROM  `tabStock Ledger Entry` sle
    WHERE  posting_date <= '2020-08-01'
      AND  warehouse = 'bom'
    ORDER BY  timestamp(posting_date, posting_time) DESC,
              Creation DESC

1000 条记录几乎需要 3 秒。如果没有 Order By 子句，查询整个表只需不到 1 秒。该表本身目前有 1M+ 记录。

show processlist总是停留在状态创建排序索引上。

解释查询显示：

+------+-------------+-------+------+------------------------------+-----------+---------+-------+--------+----------------------------------------------------+
| id   | select_type | table | type | possible_keys                | key       | key_len | ref   | rows   | Extra                                              |
+------+-------------+-------+------+------------------------------+-----------+---------+-------+--------+----------------------------------------------------+
|    1 | SIMPLE      | sle   | ref  | posting_sort_index,warehouse | warehouse | 563     | const | 127740 | Using index condition; Using where; Using filesort |
+------+-------------+-------+------+------------------------------+-----------+---------+-------+--------+----------------------------------------------------+

表索引如下：

   +-----------------------+------------+-------------------------------+--------------+--------------+--
| Table                 | Non_unique | Key_name                      | Seq_in_index | Column_name  | C
+-----------------------+------------+-------------------------------+--------------+--------------+--
| tabStock Ledger Entry |          0 | PRIMARY                       |            1 | name         | A
| tabStock Ledger Entry |          1 | item_code                     |            1 | item_code    | A
| tabStock Ledger Entry |          1 | parent                        |            1 | parent       | A
| tabStock Ledger Entry |          1 | posting_sort_index            |            1 | posting_date | A
| tabStock Ledger Entry |          1 | posting_sort_index            |            2 | posting_time | A
| tabStock Ledger Entry |          1 | posting_sort_index            |            3 | name         | A
| tabStock Ledger Entry |          1 | voucher_no_voucher_type_index |            1 | voucher_no   | A
| tabStock Ledger Entry |          1 | voucher_no_voucher_type_index |            2 | voucher_type | A
| tabStock Ledger Entry |          1 | warehouse                     |            1 | warehouse    | A
| tabStock Ledger Entry |          1 | warehouse                     |            2 | posting_date | A
+-----------------------+------------+-------------------------------+--------------+--------------+--

为（posting_date，warehouse）或（warehouse，posting_date）添加了索引，但它没有帮助..查询似乎总是使用文件排序

innodb_buffer_pool_size是 2G ..out 4G RAM。我认为它足够了..它不是一个巨型数据库

SHow Create table如下：

+-----------------------+---------------------------------------------------------------
| Table                 | Create Table
+-----------------------+---------------------------------------------------------------
| tabStock Ledger Entry | CREATE TABLE `tabStock Ledger Entry` (
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `creation` datetime(6) DEFAULT NULL,
  `modified` datetime(6) DEFAULT NULL,
  `modified_by` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `owner` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `docstatus` int(1) NOT NULL DEFAULT 0,
  `parent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `parentfield` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `parenttype` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `idx` int(8) DEFAULT NULL,
  `actual_qty` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `warehouse` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `qty_after_transaction` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `fiscal_year` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `company` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `serial_no` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incoming_rate` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `stock_queue` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `item_code` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `voucher_detail_no` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `project` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stock_value_difference` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `stock_uom` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `voucher_type` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `batch_no` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `valuation_rate` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `posting_date` date DEFAULT NULL,
  `voucher_no` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stock_value` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `is_cancelled` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `posting_time` time(6) DEFAULT NULL,
  `outgoing_rate` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `_comments` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `_liked_by` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `_assign` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `_user_tags` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `to_rename` int(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`name`),
  KEY `item_code` (`item_code`),
  KEY `parent` (`parent`),
  KEY `posting_sort_index` (`posting_date`,`posting_time`,`name`),
  KEY `voucher_no_voucher_type_index` (`voucher_no`,`voucher_type`),
  KEY `warehouse` (`warehouse`,`posting_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED
+-----------------------+---------------------------------------------------------------

更多的

我删除了时间戳功能和仅通过发布日期排序..从 mariadb 控制台运行查询..最后只需要 < 2 秒即可加载超过 200.000 个结果行。

但是，如果我从 Web 应用程序运行相同的查询，mariadb-slow.log 会向我显示不同的结果。相同的结果需要 > 4 秒。什么可能导致这种情况？

# User@Host: _5839b22099d630d5[_5839b22099d630d5] @ localhost [127.0.0.1]
# Thread_id: 685  Schema: _5839b22099d630d5  QC_hit: No
# Query_time: 4.101652  Lock_time: 0.000055  Rows_sent: 226657  Rows_examined: 226657
# Rows_affected: 0
SET timestamp=1596384652;
SELECT item_code, stock_value, name, warehouse
                FROM `tabStock Ledger Entry` sle
                WHERE posting_date <= '2020-08-02'  AND warehouse = 'bom'
                ORDER BY posting_date DESC;

用 JSON 解释

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "sle",
      "access_type": "range",
      "possible_keys": [
        "warehouse",
        "posting_date",
        "posting_sort_index",
        "warehouse_2"
      ],
      "key": "warehouse_2",
      "key_length": "567",
      "used_key_parts": ["warehouse", "posting_date"],
      "rows": 126605,
      "filtered": 100,
      "attached_condition": "sle.warehouse <=> 'bom' and sle.posting_date <= '2020-08-02' and sle.warehouse = 'bom'"
    }
  }

标签： mysqlindexingmariadb

解决方案

添加此复合索引：

INDEX(warehouse, posting_date)

（并且，如果您目前有INDEX(warehouse)，请放弃它。）

如需进一步讨论，请提供SHOW CREATE TABLE完整EXPLAIN的文本（不是图像）。

有关索引创建的更多信息：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql

时间：

  • 可能存在缓存差异。运行计时时，运行两次查询。第一个可以做第二个不需要做的 I/O。

  • 当它使用索引时，它需要访问数据的 BTree 1000 次。这些随机提取可能是磁盘命中。

  • 其他... 的值是多少innodb_buffer_pool_size？是表 InnoDB；你有多少内存；表有多大（以 GB 为单位）？

更多的

timestamp(posting_date, posting_time) DESC, Creation DESC

在不是“sargable”。我怀疑这可以更有效地为您提供相同的结果：

posting_date DESC, posting_time DESC, Creation DESC

一般来说，将日期时间分成两列是很笨拙的，而且在需要时从DATETIME(or TIMESTAMP) 中拉出部分要容易得多。我建议将posting_dateand组合posting_time成一个DATETIME(6).

之后，我建议添加

INDEX(warehouse, posting_datetime)

注意： posting_date <= '2020-08-01'包括 8 月 1 日的所有时间。posting_datetime <= '2020-08-01' 仅包括microsecond of Aug 1. If you meant to exclude all of Aug 1, then change<= to<`; 然后两种变体都“正确”工作。

优化器可能比我推荐的以前的索引更喜欢这个。

2G..out of 4G RAM

这是正确的; 确保你没有交换。

1000 条记录

你用 1000 条记录做什么？向网络用户展示的内容很多。


查询全表仅0.1秒无改进

这些令人费解。请提供EXPLAIN FORMAT=JSON SELECT ...- 这应该提供更多信息。

多拍2

Rows_sent：226657 Rows_examined：226657

这两个数字是相等的，因此查询部分几乎没有或根本没有浪费精力。它获取的每一行，它使用。

有些东西还是...

  • 我认为这是在避免这种情况，但是 EXPLAIN 没有那个细节。您能否运行ANALYZE FORMAT=JSON SELECT ...并尝试使用“优化器跟踪”（如果您的版本中可用）。

  • 表相对于 buffer_pool_size 的大小仍有待研究。对于一个 2G 的 buffer_pool 和一个大小可能差不多的表，我担心查询是 I/O 绑定的。

要追逐最后一项，有两种可能性。每个人的目标都是帮助这个查询，而不必帮助任何其他查询。这个查询是否“更重要”？

  • 覆盖指数： INDEX(warehouse, posting_date, item_code, stock_value, name). （如果您尝试此操作，请使用EXPLAIN确认此索引正在使用中。）

  • PRIMARY KEY将和我的索引更改为：

      PRIMARY KEY(warehouse, posting_date, item_code, stock_value, name),
  UNIQUE(name)

当前的 PK（名称）仍然被索引，它的唯一性证实了我的 PK 仍然是唯一的。但它也会“聚集”数据，这样查询就不会在表中跳跃。

推荐阅读