MySQL函數(shù)sysdate()與now()的區(qū)別測試用例對比

更新時間：2023年12月18日 14:27:35 作者：愛可生開源社區(qū)

這篇文章主要為大家介紹了MySQL函數(shù)sysdate()與now()的區(qū)別測試用例對比詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪

背景

在客戶現(xiàn)場優(yōu)化一批監(jiān)控 SQL 時，發(fā)現(xiàn)一批 SQL 使用 sysdate() 作為統(tǒng)計數(shù)據(jù)的查詢范圍值，執(zhí)行效率十分低下，查看執(zhí)行計劃發(fā)現(xiàn)不能使用到索引，而改為 now() 函數(shù)后則可以正常使用索引，以下是對該現(xiàn)象的分析。

內(nèi)心小 ps 一下：sysdate() 的和 now() 的區(qū)別這是個?問題了。

函數(shù) sysdate 與 now 的區(qū)別

下面我們來詳細(xì)了解一下函數(shù) sysdate() 與 now() 的區(qū)別，我們可以去官方文檔查找他們兩者之間的詳細(xì)說明。

根據(jù)官方說明如下：

now() 函數(shù)返回的是一個常量時間，該時間為語句開始執(zhí)行的時間。即當(dāng)存儲函數(shù)或觸發(fā)器中調(diào)用到 now() 函數(shù)時，now() 會返回存儲函數(shù)或觸發(fā)器語句開始執(zhí)行的時間。
sysdate() 函數(shù)則返回的是該語句執(zhí)行的確切時間。

下面我們通過官方提供的案例直觀展現(xiàn)兩者區(qū)別。

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2023-12-14 15:13:09 |        0 | 2023-12-14 15:13:09 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)
mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2023-12-14 15:13:19 |        0 | 2023-12-14 15:13:21 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

通過上面的兩條 SQL 我們可以發(fā)現(xiàn)，當(dāng) SQL 語句兩次調(diào)用 now() 函數(shù)時，前后兩次 now() 函數(shù)返回的是相同的時間，而當(dāng) SQL 語句兩次調(diào)用 sysdate() 函數(shù)時，前后兩次 sysdate() 函數(shù)返回的時間在更新。

到這里我們根據(jù)官方文檔的說明加上自己的推測大概可以知道，函數(shù)sysdate() 之所以不能使用索引是因為 sysdate() 的不確定性導(dǎo)致索引不能用于評估引用它的表達(dá)式。

測試示例

以下通過示例模擬客戶類似場景。

我們先創(chuàng)建?張測試表，對 create_time 字段創(chuàng)建索引并插入數(shù)據(jù)，觀測函數(shù) sysdate() 和 now() 使?索引的情況。

mysql> create table t1(
    ->   id int primary key auto_increment,
    ->   create_time datetime default current_timestamp,
    ->   uname varchar(20),
    ->   key idx_create_time(create_time)
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1(id) values(null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> insert into t1(id) values(null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t1;
+----+---------------------+-------+
| id | create_time         | uname |
+----+---------------------+-------+
|  1 | 2023-12-14 15:34:30 | NULL  |
|  2 | 2023-12-14 15:34:30 | NULL  |
|  3 | 2023-12-14 15:34:30 | NULL  |
|  4 | 2023-12-14 15:34:37 | NULL  |
|  5 | 2023-12-14 15:34:37 | NULL  |
|  6 | 2023-12-14 15:34:37 | NULL  |
+----+---------------------+-------+
6 rows in set (0.00 sec)

先來看看函數(shù) sysdate() 使?索引的情況?？梢园l(fā)現(xiàn) possible_keys 和 key 均為 NULL，確實使?不了索引。

mysql> explain select * from t1 where create_time<sysdate()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

再來看看函數(shù) now() 使?索引的情況，可以看到 key 使?到了 idx_create_time 這個索引。

mysql> explain select * from t1 where create_time<now()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: idx_create_time
          key: idx_create_time
      key_len: 6
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

示例詳解

下面我們進(jìn)一步通過 trace 去分析優(yōu)化器對于函數(shù) now() 和 sysdate() 具體是如何去優(yōu)化的。

函數(shù) sysdate() 部分關(guān)鍵 trace 輸出

"rows_estimation": [                  
## 估算使用各個索引進(jìn)行范圍掃描的成本
    {
      "table": "`t1`",
      "range_analysis": {
        "table_scan": {
          "rows": 6,
          "cost": 2.95
        },
        "potential_range_indexes": [
          {
            "index": "PRIMARY",
            "usable": false,
            "cause": "not_applicable"
          },
          {
            "index": "idx_create_time",
            "usable": true,
            "key_parts": [
              "create_time",
              "id"
    ............................................
        "setup_range_conditions": [
        ],
        "group_index_range": {
          "chosen": false,
          "cause": "not_group_by_or_distinct"
        },
        "skip_scan_range": {
          "chosen": false,
          "cause": "disjuntive_predicate_present"
        }
    ............................................
  "considered_execution_plans": [       
  ## 對比各可行計劃的代價，選擇相對最優(yōu)的執(zhí)行計劃
    {
      "plan_prefix": [
      ],
      "table": "`t1`",
      "best_access_path": {
        "considered_access_paths": [
          {
            "rows_to_scan": 6,
            "access_type": "scan",
            "resulting_rows": 6,
            "cost": 0.85,
            "chosen": true
          }
        ]
      },
      "condition_filtering_pct": 100,
      "rows_for_plan": 6,
      "cost_for_plan": 0.85,
      "chosen": true
    ............................................

函數(shù) now() 部分關(guān)鍵 trace 輸出

"rows_estimation": [                  
## 估算使用各個索引進(jìn)行范圍掃描的成本
  ............................................
      "analyzing_range_alternatives": {
        "range_scan_alternatives": [
          {
            "index": "idx_create_time",
            "ranges": [
              "NULL < create_time < '2023-12-14 15:48:39'"
            ],
            "index_dives_for_eq_ranges": true,
            "rowid_ordered": false,
            "using_mrr": false,
            "index_only": false,
            "in_memory": 1,
            "rows": 6,
            "cost": 2.36,
            "chosen": true
          }
        ],
  ............................................
      },
      "chosen_range_access_summary": {
        "range_access_plan": {
          "type": "range_scan",
          "index": "idx_create_time",
          "rows": 6,
          "ranges": [
            "NULL < create_time < '2023-12-14 15:48:39'"
          ]
        },
        "rows_for_plan": 6,
        "cost_for_plan": 2.36,
        "chosen": true
 .............................................
 "considered_execution_plans": [      
 ## 對比各可行計劃的代價，選擇相對最優(yōu)的執(zhí)行計劃                            
  {
    "plan_prefix": [
    ],
    "table": "`t1`",
    "best_access_path": {
      "considered_access_paths": [
        {
          "rows_to_scan": 6,
          "access_type": "range",
          "range_details": {
            "used_index": "idx_create_time"
          },
          "resulting_rows": 6,
          "cost": 2.96,
          "chosen": true
        }
      ]
    },
    "condition_filtering_pct": 100,
    "rows_for_plan": 6,
    "cost_for_plan": 2.96,
    "chosen": true
 .............................................

通過上述 trace 輸出，我們可以發(fā)現(xiàn)對于函數(shù) now()，優(yōu)化器在 rows_estimation 時即估算使用各個索引進(jìn)行范圍掃描的成本這一步時可以將 now() 的值轉(zhuǎn)換為一個常量，最終在 considered_execution_plans 這一步去對比各可行計劃的代價，選擇相對最優(yōu)的執(zhí)行計劃。而通過函數(shù) sysdate() 時則無法做到該優(yōu)化，因為 sysdate() 是動態(tài)獲取的時間。

總結(jié)

通過實際驗證執(zhí)行計劃和 trace 記錄并結(jié)合官方文檔的說明，我們可以做以下理解。

函數(shù) now() 是語句一開始執(zhí)行時就獲取時間（常量時間），優(yōu)化器進(jìn)行 SQL 解析時，已經(jīng)能確認(rèn) now() 的具體返回值并可以將其當(dāng)做一個已確定的常量去做優(yōu)化。
函數(shù) sysdate() 則是執(zhí)行時動態(tài)獲取時間（為該語句執(zhí)行的確切時間），所以在優(yōu)化器對 SQL 解析時是不能確定其返回值是多少，從而不能做 SQL 優(yōu)化和評估，也就導(dǎo)致優(yōu)化器只能選擇對該條件做全表掃描。

以上就是MySQL函數(shù)sysdate()與now()的區(qū)別測試用例對比的詳細(xì)內(nèi)容，更多關(guān)于MySQL函數(shù)sysdate now區(qū)別的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: