表數據量影響MySQL索引選擇

来源:https://www.cnblogs.com/clivewang/archive/2018/10/27/9863709.html
-Advertisement-
Play Games

現象 新建了一張員工表，插入了少量數據，索引中所有的欄位均在where條件出現時，正確走到了idx_nap索引，但是where出現部分自左開始的索引時，卻進行全表掃描，與MySQL官方所說的最左匹配原則“相悖”。 數據背景 sql CREATE TABLE ( int(11) NOT NULL AU ...


現象

新建了一張員工表，插入了少量數據，索引中所有的欄位均在where條件出現時，正確走到了idx_nap索引，但是where出現部分自左開始的索引時，卻進行全表掃描，與MySQL官方所說的最左匹配原則“相悖”。

數據背景

CREATE TABLE `staffs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',
  `pos` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間',
  PRIMARY KEY (`id`),
  KEY `idx_nap` (`name`,`age`,`pos`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';

表中數據如下：
id  name    age pos     add_time
1   July    23  dev     2018-06-04 16:02:02
2   Clive   22  dev     2018-06-04 16:02:32
3   Cleva   24  test    2018-06-04 16:02:38
4   July    23  test    2018-06-04 16:12:22
5   July    23  pre     2018-06-04 16:12:37
6   Clive   22  pre     2018-06-04 16:12:48
7   July    25  dev     2018-06-04 16:30:17

Explain語句看下執行計劃

-- 全匹配走了索引
explain select * from staffs where name = 'July' and age = 23 and pos = 'dev';
id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  staffs  NULL    ref idx_nap idx_nap 140 const,const,const   1   100.00  NULL

開啟優化器跟蹤優化過程

-- 左側部分匹配卻沒有走索引，全表掃描
explain select * from staffs where name = 'July' and age = 23;
id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  staffs2 NULL    ALL idx_nap NULL    NULL    NULL    6   50.00   Using where
-- 開啟優化器跟蹤
set session optimizer_trace='enabled=on';
-- 在執行完查詢語句後，在執行以下的select語句可以查看具體的優化器執行過程
select * from information_schema.optimizer_trace;

Trace部分的內容

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `staffs`.`id` AS `id`,`staffs`.`name` AS `name`,`staffs`.`age` AS `age`,`staffs`.`pos` AS `pos`,`staffs`.`add_time` AS `add_time` from `staffs` where ((`staffs`.`name` = 'July') and (`staffs`.`age` = 23))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`staffs`.`name` = 'July') and (`staffs`.`age` = 23))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`staffs`.`name` = 'July') and multiple equal(23, `staffs`.`age`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`staffs`.`name` = 'July') and multiple equal(23, `staffs`.`age`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`staffs`.`name` = 'July') and multiple equal(23, `staffs`.`age`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`staffs`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`staffs`",
                "field": "name",
                "equals": "'July'",
                "null_rejecting": false
              },
              {
                "table": "`staffs`",
                "field": "age",
                "equals": "23",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`staffs`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 6,
                    "cost": 4.3
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_nap",
                      "usable": true,
                      "key_parts": [
                        "name",
                        "age",
                        "pos",
                        "id"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_nap",
                        "ranges": [
                          "July <= name <= July AND 23 <= age <= 23"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 4.61,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`staffs`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                    //可以看到這邊MySQL計算得到使用索引的成本為2.6
                      "access_type": "ref",
                      "index": "idx_nap",
                      "rows": 3,
                      "cost": 2.6,
                      "chosen": true
                    },
                    {
                    //而全表掃描計算所得的成本為2.2
                      "rows_to_scan": 6,
                      "access_type": "scan",
                      "resulting_rows": 6,
                      "cost": 2.2,
                      "chosen": true
                    }
                  ]
                },
                //因此選擇了成本更低的scan
                "condition_filtering_pct": 100,
                "rows_for_plan": 6,
                "cost_for_plan": 2.2,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`staffs`.`age` = 23) and (`staffs`.`name` = 'July'))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`staffs`",
                  "attached": "((`staffs`.`age` = 23) and (`staffs`.`name` = 'July'))"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`staffs`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

增加表數據量

-- 接下來增大表的數據量
INSERT INTO `staffs` (`name`, `age`, `pos`, `add_time`)
VALUES
    ('July', 25, 'dev', '2018-06-04 16:30:17'),
    ('July', 23, 'dev1', '2018-06-04 16:02:02'),
    ('July', 23, 'dev2', '2018-06-04 16:02:02'),
    ('July', 23, 'dev3', '2018-06-04 16:02:02'),
    ('July', 23, 'dev4', '2018-06-04 16:02:02'),
    ('July', 23, 'dev6', '2018-06-04 16:02:02'),
    ('July', 23, 'dev5', '2018-06-04 16:02:02'),
    ('July', 23, 'dev7', '2018-06-04 16:02:02'),
    ('July', 23, 'dev8', '2018-06-04 16:02:02'),
    ('July', 23, 'dev9', '2018-06-04 16:02:02'),
    ('July', 23, 'dev10', '2018-06-04 16:02:02'),
    ('Clive', 23, 'dev1', '2018-06-04 16:02:02'),
    ('Clive', 23, 'dev2', '2018-06-04 16:02:02'),
    ('Clive', 23, 'dev3', '2018-06-04 16:02:02'),
    ('Clive', 23, 'dev4', '2018-06-04 16:02:02'),
    ('Clive', 23, 'dev6', '2018-06-04 16:02:02'),
    ('Clive', 23, 'dev5', '2018-06-04 16:02:02'),
    ('Clive', 23, 'dev7', '2018-06-04 16:02:02'),
    ('Clive', 23, 'dev8', '2018-06-04 16:02:02'),
    ('Clive', 23, 'dev9', '2018-06-04 16:02:02'),
    ('Clive', 23, 'dev10', '2018-06-04 16:02:02');

執行Explain

-- 再次執行同樣的查詢語句，會發現走到索引上了
explain select * from staffs where name = 'July' and age = 23;
id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  staffs  NULL    ref idx_nap idx_nap 78  const,const 13  100.00  NULL

查看新的Trace內容

-- 再看下優化器執行過程
{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `staffs`.`id` AS `id`,`staffs`.`name` AS `name`,`staffs`.`age` AS `age`,`staffs`.`pos` AS `pos`,`staffs`.`add_time` AS `add_time` from `staffs` where ((`staffs`.`name` = 'July') and (`staffs`.`age` = 23))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`staffs`.`name` = 'July') and (`staffs`.`age` = 23))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`staffs`.`name` = 'July') and multiple equal(23, `staffs`.`age`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`staffs`.`name` = 'July') and multiple equal(23, `staffs`.`age`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`staffs`.`name` = 'July') and multiple equal(23, `staffs`.`age`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`staffs`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`staffs`",
                "field": "name",
                "equals": "'July'",
                "null_rejecting": false
              },
              {
                "table": "`staffs`",
                "field": "age",
                "equals": "23",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`staffs`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 27,
                    "cost": 8.5
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_nap",
                      "usable": true,
                      "key_parts": [
                        "name",
                        "age",
                        "pos",
                        "id"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_nap",
                        "ranges": [
                          "July <= name <= July AND 23 <= age <= 23"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 13,
                        "cost": 16.61,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`staffs`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                    //使用索引的成本變為了5.3
                      "access_type": "ref",
                      "index": "idx_nap",
                      "rows": 13,
                      "cost": 5.3,
                      "chosen": true
                    },
                    {
                    //scan的成本變為了6.4
                      "rows_to_scan": 27,
                      "access_type": "scan",
                      "resulting_rows": 27,
                      "cost": 6.4,
                      "chosen": false
                    }
                  ]
                },
                //使用索引查詢的成本更低，因此選擇了走索引
                "condition_filtering_pct": 100,
                "rows_for_plan": 13,
                "cost_for_plan": 5.3,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`staffs`.`age` = 23) and (`staffs`.`name` = 'July'))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`staffs`",
                  "attached": null
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`staffs`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

結論

MySQL表數據量的大小，會影響索引的選擇，具體的情況還是通過Explain和Optimizer Trace來查看與分析。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 初識redis-cluster
    安裝redis 部署集群 參考資料 https://www.cnblogs.com/it-cen/p/4295984.html https://blog.csdn.net/naixiyi/article/details/51346880 https://www.cnblogs.com/Patrick ...
  • Linux基礎命令---mkfs
    mkfs 在磁碟分區上創建ext2、ext3、ext4、ms-dos、vfat文件系統，預設情況下會創建ext2。mkfs用於在設備上構建Linux文件系統，通常是硬碟分區。文件要麼是設備名稱(例如/dev/hda1，/dev/sdb2)，要麼是包含文件系統的常規文件。成功返回0，失敗返回1。 實際 ...
  • TMOUT優化終端超時
    有時候，管理員終端登陸了系統，如果離開沒有退出賬戶，則會有安全隱患存在，因此需要優化終端超時。 設置終端超時： export TMOUT=10 永久生效： echo "export TMOUT=600" >>/etc/profile source /etc/profile 檢查是否生效： ...
  • Hibernate-03
    目的：表操作（表維護） 一、一對一（略過） 二、一對 1、建表原則：在多的一方創建外鍵指向一的一方的外鍵 2、建表：實體中添加 3、操作 1、參數： name屬性:集合屬性名 column屬性: 外鍵列名 class屬性: 與我關聯的對象完整類名 2、級聯操作: cascade save-updat ...
  • spark RDD，reduceByKey vs groupByKey
    Spark 中有兩個類似的api，分別是 reduceByKey 和 groupByKey 。這兩個的功能類似，但底層實現卻有些不同，那麼為什麼要這樣設計呢？我們來從源碼的角度分析一下。 先看兩者的調用順序（都是使用預設的Partitioner，即defaultPartitioner） 所用 spa ...
  • RESET MASTER和RESET SLAVE使用場景和說明
    【前言】在配置主從的時候經常會用到這兩個語句，剛開始的時候還不清楚這兩個語句的使用特性和使用場景。 經過測試整理了以下文檔，希望能對大家有所幫助； 【一】RESET MASTER參數 功能說明：刪除所有的binglog日誌文件，並將日誌索引文件清空，重新開始所有新的日誌文件。用於第一次進行搭建主從庫 ...
  • hadoop2集群環境搭建
    在查詢了很多資料以後，發現國內外沒有一篇關於hadoop2集群環境搭建的詳細步驟的文章。 所以，我想把我知道的分享給大家，方便大家交流。 ...
  • sql server 錯誤日誌errorlog
    一 .概述 SQL Server 將某些系統事件和用戶定義事件記錄到 SQL Server 錯誤日誌和 Microsoft Windows 應用程式日誌中。 這兩種日誌都會自動給所有記錄事件加上時間戳。 使用 SQL Server 錯誤日誌中的信息可以解決SQL Server的相關問題。 查看 SQ ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類