實(shí)踐（2）--MySQL性能優(yōu)化

相關(guān)學(xué)習(xí)推薦：mysql教程
10年的邢臺(tái)縣網(wǎng)站建設(shè)經(jīng)驗(yàn)，針對(duì)設(shè)計(jì)、前端、開(kāi)發(fā)、售后、文案、推廣等六對(duì)一服務(wù)，響應(yīng)快，48小時(shí)及時(shí)工作處理。營(yíng)銷型網(wǎng)站建設(shè)的優(yōu)勢(shì)是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動(dòng)調(diào)整邢臺(tái)縣建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無(wú)論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì)，從而大程度地提升瀏覽體驗(yàn)。成都創(chuàng)新互聯(lián)公司從事“邢臺(tái)縣網(wǎng)站設(shè)計(jì)”,“邢臺(tái)縣網(wǎng)站推廣”以來(lái)，每個(gè)客戶項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。

前言MySQL索引底層數(shù)據(jù)結(jié)構(gòu)與算法MySQL性能優(yōu)化原理-前篇實(shí)踐（1）--MySQL性能優(yōu)化

上一篇《實(shí)踐（1）--MySQL性能優(yōu)化》我們講了數(shù)據(jù)庫(kù)表設(shè)計(jì)的一些原則，Explain工具的介紹、SQL語(yǔ)句優(yōu)化索引的實(shí)踐，本篇繼續(xù)來(lái)聊聊 MySQL 如何選擇合適的索引。

MySQL Trace 工具

MySQL 最終是否選擇走索引或者一張表涉及多個(gè)索引，最終是如何選擇索引，可以使用 trace 工具來(lái)一查究竟，開(kāi)啟 trace工具會(huì)影響 MySQL 性能，所以只能臨時(shí)分析 SQL 使用，用完之后立即關(guān)閉。

案例分析

講 trace 工具之前我們先來(lái)看一個(gè)案例：

# 示例表CREATE TABLE`employees`(`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',`age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',`position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時(shí)間',
PRIMARY KEY (`id`), KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 )ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='員工記錄表'; 
INSERT INTO employees(name,age,position,hire_time)VALUES('ZhangSan',23,'Manager',NOW());INSERT INTO employees(name,age,position,hire_time)VALUES('HanMeimei', 23,'dev',NOW());INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());復(fù)制代碼

MySQL 如何選擇合適的索引

EXPLAIN select * from employees where name > 'a';復(fù)制代碼

如果用name索引需要遍歷name字段聯(lián)合索引樹(shù)，然后還需要根據(jù)遍歷出來(lái)的主鍵值去主鍵索引樹(shù)里再去查出最終數(shù)據(jù)，成本比全表掃描還高，可以用覆蓋索引優(yōu)化，這樣只需要遍歷name字段的聯(lián)合索引樹(shù)就能拿到所有結(jié)果，如下:

EXPLAIN select name,age,position from employees where name > 'a' ;復(fù)制代碼

EXPLAIN select * from employees where name > 'zzz' ;復(fù)制代碼

對(duì)于上面這兩種 name>'a'和 name>'zzz'的執(zhí)行結(jié)果，mysql最終是否選擇走索引或者一張表涉及多個(gè)索引，mysql最終如何選擇索引，我們可以用trace工具來(lái)一查究竟，開(kāi)啟trace工具會(huì)影響mysql性能，所以只能臨時(shí)分析sql使用，用完之后立即關(guān)閉。

trace工具用法開(kāi)啟/關(guān)閉Trace

#開(kāi)啟traceset session optimizer_trace="enabled=on",end_markers_in_json=on;
#關(guān)閉traceset session optimizer_trace="enabled=off";復(fù)制代碼

案例1

執(zhí)行這兩句sql

select * from employees where name >'a' order by position;sELECT * FROM information_schema.OPTIMIZER_TRACE; 
復(fù)制代碼

提出來(lái)trace值，詳見(jiàn)注釋

{  "steps": [
    {      "join_preparation": { --第一階段：SQL準(zhǔn)備階段        "select#": 1,        "steps": [
          {            "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {      "join_optimization": { --第二階段：SQL優(yōu)化階段        "select#": 1,        "steps": [
          {            "condition_processing": { --條件處理              "condition": "WHERE",              "original_condition": "(`employees`.`name` > 'a')",              "steps": [
                {                  "transformation": "equality_propagation",                  "resulting_condition": "(`employees`.`name` > 'a')"
                },
                {                  "transformation": "constant_propagation",                  "resulting_condition": "(`employees`.`name` > 'a')"
                },
                {                  "transformation": "trivial_condition_removal",                  "resulting_condition": "(`employees`.`name` > 'a')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {            "table_dependencies": [ --表依賴詳情
              {                "table": "`employees`",                "row_may_be_null": false,                "map_bit": 0,                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {            "rows_estimation": [ --預(yù)估表的訪問(wèn)成本
              {                "table": "`employees`",                "range_analysis": {                  "table_scan": { --全表掃描                    "rows": 3,  --掃描行數(shù)                    "cost": 3.7 --查詢成本
                  } /* table_scan */,                  "potential_range_indexes": [ --查詢可能使用的索引
                    {                      "index": "PRIMARY",   --主鍵索引                      "usable": false,                      "cause": "not_applicable"
                    },
                    {                      "index": "idx_name_age_position", --輔助索引                      "usable": true,                      "key_parts": [                        "name",                        "age",                        "position",                        "id"
                      ] /* key_parts */
                    },
                    {                      "index": "idx_age",                      "usable": false,                      "cause": "not_applicable"
                    }
                  ] /* potential_range_indexes */,                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,                  "group_index_range": {                    "chosen": false,                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,                  "analyzing_range_alternatives": { --分析各個(gè)索引使用成本                    "range_scan_alternatives": [
                      {                        "index": "idx_name_age_position",                        "ranges": [                          "a < name"    --索引使用范圍
                        ] /* ranges */,                        "index_pes_for_eq_ranges": true,                        "rowid_ordered": false, --使用該索引獲取的記錄是否按照主鍵排序                        "using_mrr": false,                        "index_only": false,    --是否使用覆蓋索引                        "rows": 3,  --索引掃描行數(shù)                        "cost": 4.61, --索引使用成本                        "chosen": false, --是否選擇該索引                        "cause": "cost"
                      }
                    ] /* range_scan_alternatives */,                    "analyzing_roworder_intersect": {                      "usable": false,                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {            "considered_execution_plans": [
              {                "plan_prefix": [
                ] /* plan_prefix */,                "table": "`employees`",                "best_access_path": { --最優(yōu)訪問(wèn)路徑                  "considered_access_paths": [ --最終選擇的訪問(wèn)路徑
                    {                      "rows_to_scan": 3,                      "access_type": "scan",    --訪問(wèn)類型：為sacn，全表掃描                      "resulting_rows": 3,                      "cost": 1.6,                      "chosen": true,   --確定選擇                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,                "condition_filtering_pct": 100,                "rows_for_plan": 3,                "cost_for_plan": 1.6,                "sort_cost": 3,                "new_cost_for_plan": 4.6,                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {            "attaching_conditions_to_tables": {              "original_condition": "(`employees`.`name` > 'a')",              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,              "attached_conditions_summary": [
                {                  "table": "`employees`",                  "attached": "(`employees`.`name` > 'a')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {            "clause_processing": {              "clause": "ORDER BY",              "original_clause": "`employees`.`position`",              "items": [
                {                  "item": "`employees`.`position`"
                }
              ] /* items */,              "resulting_clause_is_simple": true,              "resulting_clause": "`employees`.`position`"
            } /* clause_processing */
          },
          {            "reconsidering_access_paths_for_index_ordering": {              "clause": "ORDER BY",              "index_order_summary": {                "table": "`employees`",                "index_provides_order": false,                "order_direction": "undefined",                "index": "unknown",                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {            "refine_plan": [
              {                "table": "`employees`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {      "join_execution": {   --第三階段：SQL執(zhí)行階段                                              "select#": 1,        "steps": [
          {            "filesort_information": [
              {                "direction": "asc",                "table": "`employees`",                "field": "position"
              }
            ] /* filesort_information */,            "filesort_priority_queue_optimization": {              "usable": false,              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,            "filesort_execution": [
            ] /* filesort_execution */,            "filesort_summary": {              "rows": 3,              "examined_rows": 3,              "number_of_tmp_files": 0,              "sort_buffer_size": 200704,              "sort_mode": "<sort_key, packed_additional_fields>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}復(fù)制代碼

結(jié)論：全表掃描的成本低于索引掃描，所以MySQL最終選擇全表掃描。

案例2

select * from employees where name > 'zzz' order by position;SELECT * FROM information_schema.OPTIMIZER_TRACE; 
復(fù)制代碼

結(jié)論：查看trace字段可知索引掃描的成本低于全表掃描，所以MySQL最終選擇索引掃描。

常見(jiàn)SQL深入優(yōu)化Order by與 Group by優(yōu)化案例1

EXPLAIN select * from employees where name = 'ZhangSan' and position = 'dev' order by age復(fù)制代碼

分析：

利用最左前綴法則：中間字段不能斷，因此查詢用到了 name索引，從 key_len = 74 也能看出，age 索引列用在排序過(guò)程過(guò)程中，因?yàn)?Extra 字段里沒(méi)有 using filesort。

案例2

EXPLAIN select * from employees where name = 'ZhangSan' order by position復(fù)制代碼

分析：

從 explain 的執(zhí)行結(jié)果來(lái)看：key_len = 74，查詢使用了 name 索引，由于用了 position 進(jìn)行排序，跳過(guò)了 age，出現(xiàn)了 Using filesort。

案例3

EXPLAIN select * from employees where name = 'ZhangSan' order by age,position復(fù)制代碼

分析：

查詢只用到索引name，age 和 position 用于排序，無(wú)Using filesort。

案例4

EXPLAIN select * from employees where name = 'ZhangSan' order by position,age復(fù)制代碼

分析：

和案例3中explain的執(zhí)行結(jié)果一樣，但是出現(xiàn)了Using filesort，因?yàn)樗饕膭?chuàng)建順序?yàn)?name,age,position, 但是排序的時(shí)候 age 和 position 顛倒位置了。

案例5

EXPLAIN select * from employees where name = 'ZhangSan' and age = 18 order by position,age復(fù)制代碼

分析：

與案例4對(duì)比，在Extra中并未出現(xiàn)** Using filesort**，因?yàn)?age 為常量，在排序中被優(yōu)化，所以索引未顛倒，不會(huì)出現(xiàn) Using filesort。

案例6

EXPLAIN select * from employees where name = 'ZhangSan' order by age asc, position desc;復(fù)制代碼

分析：

雖然排序的字段列與索引順序一樣，且 order by默認(rèn)升序，這里 position desc變成列降序，導(dǎo)致與索引的排序方式不同，從而產(chǎn)生 Using filesort。MySQL8 以上版本有降序索引可以支持該種查詢方式。

案例7

EXPLAIN select * from employees where name in ('ZhangSan', 'hjh') order by age, position;復(fù)制代碼

分析：

對(duì)于排序來(lái)說(shuō)，多個(gè)相等條件也是范圍查詢。

案例8

EXPLAIN select * from employees where name > 'a' order by name;復(fù)制代碼

可以用覆蓋索引優(yōu)化

EXPLAIN select name,age,position from employees where name > 'a' order by name;復(fù)制代碼

優(yōu)化總結(jié)MySQL支持兩種方式的排序 filesort和 index。Using index 是指MySQL 掃描索引本身完成排序。index 效率高，filesort 效率低。order by 滿足兩種情況會(huì)使用 Using index.order by 語(yǔ)句使用索引最左前例。使用 where 子句與 order by 子句條件列組合滿足索引最左前例。盡量在索引列上完成排序，遵循索引建立（索引創(chuàng)建的順序）時(shí)的最左前綴法則。如果 order by 的條件不在索引列上，就會(huì)產(chǎn)生 Using filesort。能用覆蓋索引盡量用覆蓋索引。group by 和 order by 很類似，其實(shí)質(zhì)是先排序后分組，遵循索引創(chuàng)建順序的最左前綴法則。對(duì)于 group by 的優(yōu)化如果不需要排序的可以加上 order by null禁止排序。注意：where 高于 having，能寫(xiě)在 where 中的限定條件就不要去 having 限定了。Using filesort文件排序原理filesort文件排序方式單路排序：是一次性取出滿足條件行的所有字段，然后在 sort buffer中進(jìn)行排序；用 trace 工具可以看到 sort_mode 信息里顯示 < sort_key, additional_fields > 或者 < sort_key, packed_additional_fields >。雙路排序（又叫回表排序模式）：是首先根據(jù)相應(yīng)的條件取出相應(yīng)的排序字段和可以直接定位運(yùn)行數(shù)據(jù)的行ID，然后在 sort buffer 中進(jìn)行排序，排序完后需要再次取回其它需要的字段；用 trace 工具可以看到 sort_mode 信息里顯示 < sort_key, rowid >

MySQL 通過(guò)比較系統(tǒng)變量 max_length_for_sort_data(默認(rèn)1024字節(jié)) 的大小和需要查詢的字段總大小來(lái)判斷使用那種排序模式。

如果max_length_for_sort_data比查詢的字段的總長(zhǎng)度大，那么使用單路排序模式；如果max_length_for_sort_data比查詢字段的總長(zhǎng)度小，那么使用雙路排序模式。驗(yàn)證各種排序方式

EXPLAIN select * from employees where name = 'ZhangSan' order by position;復(fù)制代碼

查看下這條sql對(duì)應(yīng)trace結(jié)果如下(只展示排序部分):

set session optimizer_trace="enabled=on",end_markers_in_json=on; #開(kāi)啟traceselect * from employees where name = 'ZhangSan' order by position;select * from information_schema.OPTIMIZER_TRACE;復(fù)制代碼

"join_execution": { --SQL執(zhí)行階段
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": { --文件排序信息
              "rows": 1, --預(yù)計(jì)掃描行數(shù)
              "examined_rows": 1, --參數(shù)排序的行
              "number_of_tmp_files": 0, --使用臨時(shí)文件的個(gè)數(shù)，這個(gè)只如果為0代表全部使用的sort_buffer內(nèi)存排序，否則使用的磁盤(pán)文件排序
              "sort_buffer_size": 200704, --排序緩存的大小
              "sort_mode": "<sort_key, packed_additional_fields>" --排序方式，這里用的單路排序
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */復(fù)制代碼

修改系統(tǒng)變量 max_length_for_sort_data(默認(rèn)1024字節(jié)) ，employees 表所有字段長(zhǎng)度總和肯定大于10字節(jié)

set max_length_for_sort_data = 10; 
select * from employees where name = 'ZhangSan' order by position;select * from information_schema.OPTIMIZER_TRACE;復(fù)制代碼

trace排序部分結(jié)果:

"join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 1,
              "examined_rows": 1,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 53248,
              "sort_mode": "<sort_key, rowid>" --排序方式，這里用餓的雙路排序
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */   
復(fù)制代碼

單路排序的詳細(xì)過(guò)程：

從索引 name 找到第一個(gè)滿足 name='ZhangSan' 條件的主鍵 id；根據(jù)主鍵id取出整行，取出所有字段的值，存入sort_buffer中；從索引name找到下一個(gè)滿足 name='ZhangSan' 條件的主鍵 id;重復(fù)步驟2、3直到不滿足 name='ZhangSan'；對(duì) sort_buffer 中的數(shù)據(jù)按照字段 position 進(jìn)行排序；返回結(jié)果給客戶端

雙路排序的詳細(xì)過(guò)程：

從索引 name 找到第一個(gè)滿足 name='ZhangSan' 的主鍵id；根據(jù)主鍵id取出整行，把排序字段 position 和主鍵id 這兩個(gè)字段放到 sort_buffer 中；從索引 name 取下一個(gè)滿足 name='ZhangSan' 記錄的主鍵id；重復(fù)步驟3、4直到不滿足 name='ZhangSan'；對(duì) sort_buffer 中的字段 position 和主鍵id按照 position 進(jìn)行排序；遍歷排序好的 id 和字段 position，按照 id 的值回到原表中取出所有的字段的值返回給客戶端。

對(duì)比兩個(gè)排序模式，單路排序會(huì)把所有需要查詢的字段都放到 sort_buffer 中，而雙路排序只會(huì)把主鍵和需要排序的字段放到 sort_buffer 中進(jìn)行排序，然后再通過(guò)主鍵回到原表查詢需要的字段。

如果MySQL排序內(nèi)存配置的比較小并且沒(méi)有條件繼續(xù)增加了，可以適當(dāng)把 max_length_for_sort_data配置小點(diǎn)，讓優(yōu)化器選擇使用雙路排序算法，可以在 sort_buffer 中一次排序更多的行，只是需要再根據(jù)主鍵回到原表取數(shù)據(jù)。

如果MySQL排序內(nèi)存有條件可以配置比較大，可以適當(dāng)增大 max_length_for_sort_data的值，讓優(yōu)化器優(yōu)先選擇全字段排序（單路排序），把需要的字段放到 sort_buffer 中，這樣排序后就會(huì)直接從內(nèi)存里返回查詢結(jié)果了。

所以，MySQL 通過(guò) max_length_for_sort_data這個(gè)參數(shù)來(lái)控制排序，在不同場(chǎng)景使用不同的排序模式，從而提升排序效率。

注意：如果全部使用sort_buffer 內(nèi)存排序一般情況下效率會(huì)高于磁盤(pán)文件排序，但不能因?yàn)檫@個(gè)就隨便增大 sort_buffer（默認(rèn)1M），MySQL很多參數(shù)設(shè)置都做過(guò)優(yōu)化的，不要輕易調(diào)整。

分頁(yè)查詢優(yōu)化

在這我們先往 employess插入一些測(cè)試數(shù)據(jù)

drop procedure if exists insert_emp; 
delimiter ;; 
create procedure insert_emp()begin
    declare i int; 
    set i=1; 
    while(i<=100000) do
        insert into employees(name,age,position) values(CONCAT('hjh',i),i,'dev');        set i=i+1; 
    end while;end;;
delimiter ; 
call insert_emp();復(fù)制代碼

很多時(shí)候我們業(yè)務(wù)系統(tǒng)實(shí)現(xiàn)分頁(yè)功能可能會(huì)用如下SQL實(shí)現(xiàn)

select * from employees limit 10000,10;復(fù)制代碼

表示從表 employees 中取出從 10001 行開(kāi)始的 10 行記錄?？此浦徊樵兞?10 條記錄，實(shí)際這條 SQL 是先讀取 10010 條記錄，然后拋棄前 10000 條記錄，然后讀到后面 10 條想要的數(shù)據(jù)。因此要查詢一張大表比較靠后的數(shù)據(jù)，執(zhí)行效率是非常低的。

常見(jiàn)的分頁(yè)場(chǎng)景優(yōu)化技巧根據(jù)自增且連續(xù)的主鍵排序的分頁(yè)查詢根據(jù)非主鍵字段排序的分頁(yè)查詢案例1: 根據(jù)自增且連續(xù)的主鍵排序的分頁(yè)查詢

首先來(lái)看一個(gè)根據(jù)自增且連續(xù)主鍵排序的分頁(yè)查詢的例子：

select * from employees limit 9000,5;復(fù)制代碼

該 SQL 表示查詢從第 9001開(kāi)始的五行數(shù)據(jù)，沒(méi)添加單獨(dú) order by，表示通過(guò)主鍵排序。我們?cè)倏幢?employees ，因?yàn)橹麈I是自增并且連續(xù)的，所以可以改寫(xiě)成按照主鍵去查詢從第 9001開(kāi)始的五行數(shù)據(jù)，如下:

select * from employees where id > 9000 limit 5;復(fù)制代碼

查詢結(jié)果是一致的，我們?cè)賹?duì)比一下執(zhí)行計(jì)劃：

EXPLAIN select * from employees limit 9000,5;復(fù)制代碼

EXPLAIN select * from employees where id > 9000 limit 5;復(fù)制代碼

顯然改寫(xiě)后的 SQL 走了索引，而且掃描的行數(shù)大大減少，執(zhí)行效率更高。但是，這條改寫(xiě)的 SQL 在很多場(chǎng)景并不實(shí)用，因?yàn)楸碇锌赡苣承┯涗洷粍h后，主鍵空缺，導(dǎo)致結(jié)果不一致，如下圖試驗(yàn)所示(先刪除一條前面的記錄，然后再測(cè)試原 SQL 和優(yōu)化后的 SQL):

兩條 SQL 的結(jié)果并不一樣，因此，如果主鍵不連續(xù)，不能使用上面描述的優(yōu)化方法。

另外如果原SQL是order by 非主鍵的字段，按照上面說(shuō)餓的方法改寫(xiě)會(huì)導(dǎo)致兩條SQL的結(jié)果不一致。所以這種改寫(xiě)得滿足以下兩個(gè)條件：

主鍵自增且連續(xù)結(jié)果是按照主鍵排序的案例2: 根據(jù)非主鍵字段排序的分頁(yè)查詢

再看一個(gè)根據(jù)非主鍵字段排序的分頁(yè)查詢，SQL 如下:

select * from employees ORDER BY name limit 9000,5;復(fù)制代碼

EXPLAIN select * from employees ORDER BY name limit 90000,5;復(fù)制代碼

發(fā)現(xiàn)并沒(méi)有使用 name 字段的索引(key 字段對(duì)應(yīng)的值為 null)，具體原因上前面講過(guò) : 掃描整個(gè)索引并查找到?jīng)]索引的行(可能要遍歷多個(gè)索引樹(shù))的成本比掃描全表的成本更高，所以優(yōu)化器放棄使用索引。知道不走索引的原因，那么怎么優(yōu)化呢? 其實(shí)關(guān)鍵是讓排序時(shí)返回的字段盡可能少，所以可以讓排序和分頁(yè)操作先查出主鍵，然后根據(jù)主鍵查到對(duì)應(yīng)的記錄，SQL 改寫(xiě)如下：

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;復(fù)制代碼

需要的結(jié)果與原 SQL 一致，執(zhí)行時(shí)間減少了一半以上，我們?cè)賹?duì)比優(yōu)化前后sql的執(zhí)行計(jì)劃:

EXPLAIN select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;復(fù)制代碼

原 SQL 使用的是 filesort 排序，而優(yōu)化后的 SQL 使用的是索引排序。

Join關(guān)聯(lián)查詢優(yōu)化

#示例表CREATE TABLE `t1` (    `id` INT (11) NOT NULL AUTO_INCREMENT,    `a` INT (11) DEFAULT NULL,    `b` INT (11) DEFAULT NULL,
    PRIMARY KEY (`id`),    KEY `idx_a` (`a`)
) ENGINE = INNODB AUTO_INCREMENT = 10001 DEFAULT CHARSET = utf8;CREATE TABLE t2 LIKE t1;復(fù)制代碼

往t1表插入1萬(wàn)行記錄，往t2表插入100行記錄

#t1 1萬(wàn)條記錄drop procedure if exists insert_emp_t1; 
delimiter ;; 
create procedure insert_emp_t1()begin
    declare i int; 
    set i=1; 
    while(i<=10000) do
        insert into t1(a,b) values(i,i);        set i=i+1; 
    end while;end;;
delimiter ; 
call insert_emp_t1();

#t2 100條記錄drop procedure if exists insert_emp_t2; 
delimiter ;; 
create procedure insert_emp_t2()begin
    declare i int; 
    set i=1; 
    while(i<=100) do
        insert into t2(a,b) values(i,i);        set i=i+1; 
    end while;end;;
delimiter ; 
call insert_emp_t2();復(fù)制代碼

MySQL 的表關(guān)聯(lián)常見(jiàn)有兩種算法Nested-Loop Join 算法Block Nested-Loop Join 算法案例1：嵌套循環(huán)連接 Nested-Loop Join（NLJ）算法

一次一行循環(huán)地從第一張表（稱為驅(qū)動(dòng)表）中讀取行，在這行數(shù)據(jù)中取到關(guān)聯(lián)字段，根據(jù)關(guān)聯(lián)字段在另一張表（被驅(qū)動(dòng)表）里取出滿足條件的行，然后取出兩張表的結(jié)果合集。

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;復(fù)制代碼

從執(zhí)行計(jì)劃中可以看到這些信息:

驅(qū)動(dòng)表是 t2，被驅(qū)動(dòng)表是 t1。先執(zhí)行的就是驅(qū)動(dòng)表(執(zhí)行計(jì)劃結(jié)果的id如果一樣則按從上到下順序執(zhí)行sql);優(yōu)化器一般會(huì)優(yōu)先選擇小表做驅(qū)動(dòng)表。所以使用 inner join 時(shí)，排在前面的表并不一定就是驅(qū)動(dòng)表。使用了 NLJ 算法。一般 join 語(yǔ)句中，如果執(zhí)行計(jì)劃 Extra 中未出現(xiàn) Using join buffer 則表示使用的 join 算法是 NLJ。

上面SQL的大致流程如下：

從表 t2 中讀取一行數(shù)據(jù)；從第1步的數(shù)據(jù)中，取出關(guān)鍵字字段 a，到表 t1 中查找；取出表 t1 中滿足條件的行，跟 t2 中獲取到的結(jié)果合并，作為結(jié)果返回給客戶端；重復(fù)上面 3 步。

整個(gè)過(guò)程會(huì)讀取 t2 表的所有數(shù)據(jù)（掃描100行），然后遍歷這每行數(shù)據(jù)中字段 a 的值，根據(jù) t2 表中的 a 的值索引掃描 t1 表中對(duì)應(yīng)的行（掃描 100次 t1 表的索引，1次掃描可以認(rèn)為最終只掃描 t1 表一行完整數(shù)據(jù)，也就是總共 t1 表也掃描了100行）。因此整個(gè)過(guò)程掃描了 200 行。

如果被驅(qū)動(dòng)表的關(guān)聯(lián)字段沒(méi)有索引，使用NLJ算法性能會(huì)比較低（下面有詳細(xì)解釋），MySQL 會(huì)選擇 Block Nested-Loop Join 算法。

案例2：基于塊的嵌套循環(huán)連接 Block Nested-Loop Join（BNL）算法

把驅(qū)動(dòng)表的數(shù)據(jù)讀入到 join_buffer 中，然后掃描被驅(qū)動(dòng)表，把被驅(qū)動(dòng)表每一行取出來(lái)跟 join_buffer 中的數(shù)據(jù)做對(duì)比。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;復(fù)制代碼

Extra 中的Using join buffer (Block Nested Loop)說(shuō)明該關(guān)聯(lián)查詢使用的是 BNL 算法。

上面sql的大致流程如下:

把 t2 的所有數(shù)據(jù)放入到 join_buffer 中把表 t1 中每一行取出來(lái)，跟 join_buffer 中的數(shù)據(jù)做對(duì)比返回滿足 join 條件的數(shù)據(jù)

整個(gè)過(guò)程對(duì)表 t1 和 t2 都做了一次全表掃描，因此掃描的總行數(shù)為10000(表 t1 的數(shù)據(jù)總量) + 100(表 t2 的數(shù)據(jù)總量) = 10100。并且 join_buffer 里的數(shù)據(jù)是無(wú)序的，因此對(duì)表 t1 中的每一行，都要做 100 次判斷，所以內(nèi)存中的判斷次數(shù)是 100 * 10000= 100 萬(wàn)次。

被驅(qū)動(dòng)表的關(guān)聯(lián)字段沒(méi)索引為什么要選擇使用 BNL 算法而不使用 Nested-Loop Join 呢?

如果上面第二條sql使用 Nested-Loop Join，那么掃描行數(shù)為 100 * 10000 = 100萬(wàn)次，這個(gè)是磁盤(pán)掃描。

很顯然，用BNL磁盤(pán)掃描次數(shù)少很多，相比于磁盤(pán)掃描，BNJ 的內(nèi)存計(jì)算會(huì)快得多。

因此MySQL對(duì)于被驅(qū)動(dòng)表的關(guān)聯(lián)字段沒(méi)索引的關(guān)聯(lián)查詢，一般都會(huì)使用 BNL 算法。如果有索引一般選擇 NLJ 算法，有索引的情況下 NLJ 算法比 BNL算法性能更高。

對(duì)于關(guān)聯(lián)SQL的優(yōu)化關(guān)聯(lián)字段加索引，讓mysql做join操作時(shí)盡量選擇NLJ算法小表驅(qū)動(dòng)大表，寫(xiě)多表連接sql時(shí)如果明確知道哪張表是小表可以用straight_join寫(xiě)法固定連接驅(qū)動(dòng)方式，省去mysql優(yōu)化器自己判斷的時(shí)間

straight_join解釋

straight_join功能同join類似，但能讓左邊的表來(lái)驅(qū)動(dòng)右邊的表，能改變優(yōu)化器對(duì)于聯(lián)表查詢的執(zhí)行順序。

比如 : select * from t2 straight_join t1 on t2.a = t1.a;代表制定mysql選擇 t2 表作為驅(qū)動(dòng)表。

straight_join只適用于inner join，并不適用于left join，right join。(因?yàn)閘eft join，right join已經(jīng)代表指定了表的執(zhí)行順序)盡可能讓優(yōu)化器去判斷，因?yàn)榇蟛糠智闆r下mysql優(yōu)化器是比人要聰明的。使用straight_join一定要慎重，因為部分情況下人為指定的執(zhí)行順序并不一定會(huì)比優(yōu)化引擎要靠譜。in 和 exsits 優(yōu)化

原則：小表驅(qū)動(dòng)大表，即小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集。

in：當(dāng)B表的數(shù)據(jù)集小于A表的數(shù)據(jù)集時(shí)，in優(yōu)于exists

select * from A where id in(select id from B) 
#等價(jià)于:for(select id from B){    select * from A where A.id = B.id
}復(fù)制代碼

exists：當(dāng)A表的數(shù)據(jù)集小于B表的數(shù)據(jù)集時(shí)，exists優(yōu)于in

將主查詢A的數(shù)據(jù)，放到子查詢B中做條件驗(yàn)證，根據(jù)驗(yàn)證結(jié)果(true或false)來(lái)決定主查詢的數(shù)據(jù)是否保留

select * from A where exists (select 1 from B whereB.id=A.id) 
#等價(jià)于:for(select * from A){    select * from B where B.id = A.id
}

#A表與B表的ID字段應(yīng)建立索引復(fù)制代碼

EXISTS (subquery)只返回TRUE或FALSE,因此子查詢中的SELECT * 也可以用SELECT 1替換,官方說(shuō)法是實(shí)際執(zhí)行時(shí)會(huì) 忽略SELECT清單,因此沒(méi)有區(qū)別；EXISTS子查詢的實(shí)際執(zhí)行過(guò)程可能經(jīng)過(guò)了優(yōu)化而不是我們理解上的逐條對(duì)比；EXISTS子查詢往往也可以用JOIN來(lái)代替，何種最優(yōu)需要具體問(wèn)題具體分析；Count(*)查詢優(yōu)化

臨時(shí)關(guān)閉mysql查詢緩存，為了查看sql多次執(zhí)行的真實(shí)時(shí)間。

set global query_cache_size=0;set global query_cache_type=0;復(fù)制代碼

EXPLAIN select count(1) from employees; 
EXPLAIN select count(id) from employees;EXPLAIN select count(name) from employees; 
EXPLAIN select count(*) from employees;復(fù)制代碼

四個(gè)sql的執(zhí)行計(jì)劃一樣，說(shuō)明這四個(gè)sql執(zhí)行效率應(yīng)該差不多，區(qū)別在于根據(jù)某個(gè)字段count不會(huì)統(tǒng)計(jì)字段為null值的數(shù)據(jù)行。

為什么mysql最終選擇輔助索引而不是主鍵聚集索引?

因?yàn)槎?jí)索引相對(duì)主鍵索引存儲(chǔ)數(shù)據(jù)更少，檢索性能應(yīng)該更高

常見(jiàn)的優(yōu)化方法如下：查詢MySQL自己維護(hù)的總行數(shù)show table status將總數(shù)維護(hù)到Redis里增加計(jì)數(shù)表查詢MySQL自己維護(hù)的總行數(shù)

對(duì)于myisam存儲(chǔ)引擎的表做不帶where條件的count查詢性能是很高的，因?yàn)閙yisam存儲(chǔ)引擎的表的總行數(shù)會(huì)被 mysql存儲(chǔ)在磁盤(pán)上，查詢不需要計(jì)算。

對(duì)于innodb存儲(chǔ)引擎的表mysql不會(huì)存儲(chǔ)表的總記錄行數(shù)，查詢count需要實(shí)時(shí)計(jì)算。

show table status

如果只需要知道表總行數(shù)的估計(jì)值可以用如下sql查詢，性能很高

將總數(shù)維護(hù)到Redis里

插入或刪除表數(shù)據(jù)行的時(shí)候同時(shí)維護(hù)redis里的表總行數(shù)key的計(jì)數(shù)值(用incr或decr命令)，但是這種方式可能不準(zhǔn)，很難保證表操作和redis操作的事務(wù)一致性。

增加計(jì)數(shù)表

插入或刪除表數(shù)據(jù)行的時(shí)候同時(shí)維護(hù)計(jì)數(shù)表，讓他們?cè)谕粋€(gè)事務(wù)里操作。

想了解更多編程學(xué)習(xí)，敬請(qǐng)關(guān)注php培訓(xùn)欄目！

當(dāng)前標(biāo)題：實(shí)踐（2）--MySQL性能優(yōu)化
分享網(wǎng)址：http://www.rwnh.cn/article0/cpgjoo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供商城網(wǎng)站、用戶體驗(yàn)、關(guān)鍵詞優(yōu)化、網(wǎng)站改版、自適應(yīng)網(wǎng)站、營(yíng)銷型網(wǎng)站建設(shè)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

中文字幕日韩精品一区二区免费_精品一区二区三区国产精品无卡在_国精品无码专区一区二区三区_国产αv三级中文在线

實(shí)踐（2）--MySQL性能優(yōu)化