【MySQL 原理分析】之 Explain & Trace 深入分析全模糊查詢走索引的原理

一、背景

今天，交流群有一位同學提出了一個問題。看下圖：

之後，這位同學确實也發了一個全模糊查詢走索引的例子：

到這我們可以發現，這兩個sql最大的差別是：一個是查詢全字段（select *），而一個隻查詢主鍵（select id）。

此時，又有其他同學講了其他方案：

全文索引這個不用說，那是能讓全模糊查詢走索引的。但是索引覆寫這個方案，我覺得才是符合背景的：

1、因為提問的背景就是模糊查詢字段是普通索引，而普通索引隻查詢主鍵就能用上覆寫索引。

2、并且背景中，就是隻查詢主鍵（ID）就顯示用上索引了。

二、資料準備和場景重制

1、準備表和資料：

建立 user 表，給 phone 字段加了個普通索引：

CREATE TABLE

user

(

id

int(10) unsigned NOT NULL AUTO_INCREMENT,

name

varchar(255) DEFAULT NULL,

age

int(11) DEFAULT NULL,

phone

varchar(11) DEFAULT NULL,

PRIMARY KEY (

id

KEY

index_phone

phone

) USING BTREE COMMENT 'phone索引'

) ENGINE=InnoDB AUTO_INCREMENT=200007 DEFAULT CHARSET=utf8;

準備10萬條資料意思意思：

delimiter ;

CREATE DEFINER=

root

localhost

PROCEDURE

iniData

()

begin

declare i int;

set i=1;

while(i<=100000)do

insert into user(name,age,phone) values('測試', i, 15627230000+i);
set i=i+1;

end while;

end;;

call iniData();

2、執行 SQL ，檢視執行計劃：

explain select * from user where phone like '%156%';

explain select id from user where phone like '%156%';

3、執行結果：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra

1 SIMPLE user

ALL

99927 11.11 Using where

index

index_phone 36

99927 11.11 Using where; Using index

我們可以發現，第二條 SQL 确實是顯示用上了 index_phone 索引。

但是細心的同學可能會發現：possible_keys 竟然為空！有存在某種問題或陰謀。。。

我這裡先說一下 prossible_keys 和 key 的關系：

1、possible_keys 為可能使用的索引，而 key 是實際使用的索引；

2、正常是： key 的索引，必然會包含在 possible_keys 中。

還有存在某種問題或陰謀一點就是：使用索引和不使用索引讀取的行數（rows）竟然是一樣的！

三、驗證和階段性猜想

上面講到，possible_keys 和 key 的關系，那麼我們利用正常的走索引來驗證一下。

下面的 SQL，不是全模糊查詢，而是右模糊查詢，保證是一定走索引的，我們分别看看此時 possible_keys 和 key 的值：

explain select id from user where phone like '156%';

執行結果：

range index_phone index_phone 36

49963 100 Using where; Using index

這裡太明顯了：

1、possible_keys 裡确實包含了 key 裡的索引。

2、并且rows 瞬間降到 49963，整整降了一倍，并且 filtered 也達到了 100。

階段猜想：

1、首先，select id from user where phone like '%156%'; 因為覆寫索引而用上了索引 index_phone。

2、possible_keys 為 null，證明用不上索引的樹形查找。很明顯，select id from user where phone like '%156%'; 即使顯示走了索引，但是讀取行數 rows 和 select * from user where phone like '%156%'; 沒有走索引的 rows 是一樣的。

3、那麼，我們可以猜測到，select id from user where phone like '%156%'; 即使因為覆寫索引而用上了 index_phone 索引，但是卻沒用上樹形查找，隻是正常順序周遊了索引樹。是以說，其實這兩條 SQL 在表字段不多的情況下，查詢性能應該差不了多少。

四、通過 Trace 分析來驗證

我們分别利用 Trace 分析對于這兩個 SQL 優化器是如何選擇的。

1、查詢全字段：

-- 開啟優化器跟蹤

set session optimizer_trace='enabled=on';

select * from user where phone like '%156%';

-- 檢視優化器追蹤

select * from information_schema.optimizer_trace;

下面我們隻看 TRACE 就行了：

{

"steps": [

{
  "join_preparation": {
    "select#": 1,
    "steps": [
      {
        "expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`phone` AS `phone` from `user` where (`user`.`phone` like '%156%')"
      }
    ]
  }
},
{
  "join_optimization": {
    "select#": 1,
    "steps": [
      {
        "condition_processing": {
          "condition": "WHERE",
          "original_condition": "(`user`.`phone` like '%156%')",
          "steps": [
            {
              "transformation": "equality_propagation",
              "resulting_condition": "(`user`.`phone` like '%156%')"
            },
            {
              "transformation": "constant_propagation",
              "resulting_condition": "(`user`.`phone` like '%156%')"
            },
            {
              "transformation": "trivial_condition_removal",
              "resulting_condition": "(`user`.`phone` like '%156%')"
            }
          ]
        }
      },
      {
        "substitute_generated_columns": {
        }
      },
      {
        "table_dependencies": [
          {
            "table": "`user`",
            "row_may_be_null": false,
            "map_bit": 0,
            "depends_on_map_bits": [
            ]
          }
        ]
      },
      {
        "ref_optimizer_key_uses": [
        ]
      },
      {
        "rows_estimation": [
          {
            "table": "`user`",
            "table_scan": {
              "rows": 99927,
              "cost": 289
            }
          }
        ]
      },
      {
        "considered_execution_plans": [
          {
            "plan_prefix": [
            ],
            "table": "`user`",
            "best_access_path": {
              "considered_access_paths": [
                {
                  "rows_to_scan": 99927,
                  "access_type": "scan", // 順序掃描
                  "resulting_rows": 99927,
                  "cost": 20274,
                  "chosen": true
                }
              ]
            },
            "condition_filtering_pct": 100,
            "rows_for_plan": 99927,
            "cost_for_plan": 20274,
            "chosen": true
          }
        ]
      },
      {
        "attaching_conditions_to_tables": {
          "original_condition": "(`user`.`phone` like '%156%')",
          "attached_conditions_computation": [
          ],
          "attached_conditions_summary": [
            {
              "table": "`user`",
              "attached": "(`user`.`phone` like '%156%')"
            }
          ]
        }
      },
      {
        "refine_plan": [
          {
            "table": "`user`"
          }
        ]
      }
    ]
  }
},
{
  "join_execution": {
    "select#": 1,
    "steps": [
    ]
  }
}

]

}

2、隻查詢主鍵

select id from user where phone like '%156%';

下面我們繼續隻看 TRACE 就行了：

{
  "join_preparation": {
    "select#": 1,
    "steps": [
      {
        "expanded_query": "/* select#1 */ select `user`.`id` AS `id` from `user` where (`user`.`phone` like '%156%')"
      }
    ]
  }
},
{
  "join_optimization": {
    "select#": 1,
    "steps": [
      {
        "condition_processing": {
          "condition": "WHERE",
          "original_condition": "(`user`.`phone` like '%156%')",
          "steps": [
            {
              "transformation": "equality_propagation",
              "resulting_condition": "(`user`.`phone` like '%156%')"
            },
            {
              "transformation": "constant_propagation",
              "resulting_condition": "(`user`.`phone` like '%156%')"
            },
            {
              "transformation": "trivial_condition_removal",
              "resulting_condition": "(`user`.`phone` like '%156%')"
            }
          ]
        }
      },
      {
        "substitute_generated_columns": {
        }
      },
      {
        "table_dependencies": [
          {
            "table": "`user`",
            "row_may_be_null": false,
            "map_bit": 0,
            "depends_on_map_bits": [
            ]
          }
        ]
      },
      {
        "ref_optimizer_key_uses": [
        ]
      },
      {
        "rows_estimation": [
          {
            "table": "`user`",
            "table_scan": {
              "rows": 99927,
              "cost": 289
            }
          }
        ]
      },
      {
        "considered_execution_plans": [
          {
            "plan_prefix": [
            ],
            "table": "`user`",
            "best_access_path": {
              "considered_access_paths": [
                {
                  "rows_to_scan": 99927,
                  "access_type": "scan", // 順序掃描
                  "resulting_rows": 99927,
                  "cost": 20274,
                  "chosen": true
                }
              ]
            },
            "condition_filtering_pct": 100,
            "rows_for_plan": 99927,
            "cost_for_plan": 20274,
            "chosen": true
          }
        ]
      },
      {
        "attaching_conditions_to_tables": {
          "original_condition": "(`user`.`phone` like '%156%')",
          "attached_conditions_computation": [
          ],
          "attached_conditions_summary": [
            {
              "table": "`user`",
              "attached": "(`user`.`phone` like '%156%')"
            }
          ]
        }
      },
      {
        "refine_plan": [
          {
            "table": "`user`"
          }
        ]
      }
    ]
  }
},
{
  "join_execution": {
    "select#": 1,
    "steps": [
    ]
  }
}

好了，到這裡我們可以發現，在 Trace 分析裡面，都沒顯示優化器為這兩個 SQL 實際選擇了什麼索引，而隻是顯示了都是用了順序掃描的方式去查找資料。

可能唯一不同點就是：一個使用了主鍵索引的全表掃描，而另外一個是使用了普通索引的全表掃描；但是兩個都沒用上樹形查找，也就是沒用上 B+Tree 的特性來提升查詢性能。

六、最後總結

1、當全模糊查詢的 SQL 隻查詢主鍵作為結果集時，因為覆寫索引，會用上查詢字段對應的索引。

2、即使用上了索引，但是卻沒用上樹形查找的特性，隻是正常的順序周遊。

3、而正常的全表掃描也是主鍵索引的順序周遊，是以說，其實這兩者的性能其實是差不多的。

原文位址

https://www.cnblogs.com/Howinfun/p/12449975.html

【MySQL 原理分析】之 Explain & Trace 深入分析全模糊查詢走索引的原理

繼續閱讀

SQL優化SQL語句優化的目的

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離

【MySQL 原理分析】之 Explain &amp; Trace 深入分析全模糊查詢走索引的原理

繼續閱讀

【MySQL 原理分析】之 Explain & Trace 深入分析全模糊查詢走索引的原理