MySQL 索引優化案例

2022-08-19 14:04:01

資料準備

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 '入職時間',
 PRIMARY KEY (`id`),
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';

 INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'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());

 ‐‐ 插入一些範例資料
 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('zhuge',i),i,'dev');
 set i=i+1;
 end while;
 end;;
 delimiter ;
 call insert_emp();

如上有 employees 表，有主鍵索引和 (name, age, position ) 聯合索引, 看下面的查詢範例:

聯合索引的首欄位用範圍查詢

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

結論：聯合索引第一個欄位就用範圍查詢不會走索引，mysql內部可能覺得第一個欄位就用範圍，結果集應該很大，回表效率不高，還不如就全表掃描

強制走索引

EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';

結論：雖然使用了強制走索引讓聯合索引第一個欄位範圍查詢也走索引，掃描的行rows看上去也少了點，但是最終查詢效率不一定比全表掃描高，因為回表效率不高，一般不會使用這個手段，除非有證據能證明強制走索引後效率大幅度提高

覆蓋索引優化

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

將 select * 修改為 select name, age, posiion ，只選擇索引中已經存在的列，可以不用回表，所以會利用索引

in和or什麼時候會走索引

在表資料量比較大的情況會走索引，資料量不多的情況下會選擇全表掃描,範例如下:

in 查詢

EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';

用到全部索引 or 查詢

EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';

用到全部索引

下面新建一張 employees_copy 表，結構和 employee 一樣，但資料只有三條, 再執行上面兩個查詢

in 查詢

EXPLAIN SELECT * FROM employees_copy WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';

全表掃描

or查詢

EXPLAIN SELECT * FROM employees_copy WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';

全表掃描

like xx% 一般都會走索引，和資料量無關

大表

EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

小表

EXPLAIN SELECT * FROM employees_copy WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

可以看到，無論表的資料量大小，都會利用索引，為什麼呢？

其實 like 用到了索引下推的優化

索引下推

對於輔助聯合索引，正常情況下按照最左字首原則， SELECT * from employees where name like 'LiLei%' and age = 22 and position = 'dev' 這種情況下只會走name欄位的索引，因為根據name欄位過濾完，得到的索引行裡的age和position是無序的，無法很好的利用索引。

在MySQL5.6之前的版本，這個查詢只能在聯合索引裡匹配到名字是 'LiLei' 開頭的索引，然後拿這些索引對應的主鍵逐個回表，到主鍵索引上找出相應的記錄，再比對age和position這兩個欄位的值是否符合。

MySQL 5.6引入了索引下推優化，可以在索引遍歷過程中，對索引中包含的所有欄位先做判斷，過濾掉不符合條件的記錄之後再回表，可以有效的減少回表次數。使用了索引下推優化後，上面那個查詢在聯合索引裡匹配到名字是 'LiLei' 開頭的索引之後，同時還會在索引裡過濾age和position這兩個欄位，拿著過濾完剩下的索引對應的主鍵id再回表查整行資料。

索引下推會減少回表次數，對於innodb引擎的表索引下推只能用於二級索引，innodb的主鍵索引（聚簇索引）樹葉子節點上儲存的是全行資料，所以這個時候索引下推並不會起到減少查詢全行資料的效果。

為什麼範圍查詢沒有用索引下推優化？

估計應該是Mysql認為範圍查詢過濾的結果集過大，like KK% 在絕大多數情況來看，過濾後的結果集比較小，所以這裡Mysql選擇給 like KK% 用了索引下推優化，當然這也不是絕對的，有時like KK% 也不一定就會走索引下推。

如何選擇索引

先看下面的兩個查詢:

同樣的表，同樣的欄位，因為條件的不同，選擇的索引也不同，MySQL 是如何選擇的呢？

Trace 工具

MySQl 提供了一個工具，可以看到選擇索引的計算過程，用法如下:

mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;  --開啟trace

mysql> select * from employees where name > 'a' order by position;

mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

下面是對 trace 欄位的解析

{
  "steps": [
    {
      "join_preparation": {    //第一階段：SQL準備階段，格式化sql
        "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優化階段
        "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": [    //預估表的存取成本
              {
                "table": "`employees`",
                "range_analysis": {
                  "table_scan": {     //全表掃描情況
                    "rows": 10123,    //掃描行數
                    "cost": 2054.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 */
                    }
                  ] /* 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": {    //分析各個索引使用成本
                    "range_scan_alternatives": [
                      {
                        "index": "idx_name_age_position",
                        "ranges": [
                          "a < name"      //索引使用範圍
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,    //使用該索引獲取的記錄是否按照主鍵排序
                        "using_mrr": false,
                        "index_only": false,       //是否使用覆蓋索引
                        "rows": 5061,              //索引掃描行數
                        "cost": 6074.2,            //索引使用成本
                        "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": {    //最優存取路徑
                  "considered_access_paths": [   //最終選擇的存取路徑
                    {
                      "rows_to_scan": 10123,
                      "access_type": "scan",     //存取型別：為scan，全表掃描
                      "resulting_rows": 10123,
                      "cost": 2052.6,
                      "chosen": true,            //確定選擇
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 10123,
                "cost_for_plan": 2052.6,
                "sort_cost": 10123,
                "new_cost_for_plan": 12176,
                "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",
              "steps": [
              ] /* steps */,
              "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執行階段
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
// 結論：全表掃描的成本低於索引掃描，所以mysql最終選擇全表掃描

mysql> select * from employees where name > 'zzz' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

# 檢視trace欄位可知索引掃描的成本低於全表掃描，所以mysql最終選擇索引掃描

mysql> set session optimizer_trace="enabled=off";    //關閉trace

深入優化

order by 和 group by

order by 和 group by 也會遵循左字首法則, 如下例子：

根據左字首法則，用到了 name 欄位的索引，同時使用 age 欄位用來排序，因為 extra 種沒有 filesort

order by 或者 group by 用到的索引不會參與到 key_len 的計算，索引 key_len 仍然只是 74，即 name欄位的長度

再看下面一個例子:

where 條件是name 排序欄位是 position 跳過了age欄位，所以只能用 name 索引，無法利用 position 索引進行索引排序，用到是檔案排序

再看第三個例子:

使用name條件查詢，同時使用 age position 雙欄位排序，沒有跳過聯合索引的欄位. 所以可以用索引排序

然後顛倒一下排序順序，先position 再 age：

發現此時只能檔案排序了

再看下面的例子

雖然排序欄位與索引欄位不一樣，但仍然是索引排序，因為查詢條件中用到是（name， age）索引，排序中用到是 position 索引，並沒有顛倒順序。所以還是索引排序

如果一個正序一個倒序呢？

雖然排序欄位與索引欄位順序相同，但是 age 是正序， position 是倒敘，導致與索引的排序方式不同，無法利用索引。從而發生了檔案排序， Mysql8以上版本有降序索引可以支援該種查詢方式。

先 in 查詢:

對於排序來說，多個相等條件也是範圍查詢, 無法利用索引排序

先範圍查詢:

這裡發生了全表掃描，沒有任何索引，排序自然也無法利用索引了，可以使用覆蓋索引優化:

優化總結

MySQL支援兩種方式的排序filesort和index，Using index是指MySQL掃描索引本身完成排序。index效率高，filesort效率低。

2、order by滿足兩種情況會使用Using index。

order by語句使用索引最左前列。
使用where子句與order by子句條件列組合滿足索引最左前列。
儘量在索引列上完成排序，遵循索引建立（索引建立的順序）時的最左字首法則。
如果order by的條件不在索引列上，就會產生Using filesort。
能用覆蓋索引儘量用覆蓋索引
group by與order by很類似，其實質是先排序後分組，遵照索引建立順序的最左字首法則。對於group by的優化如果不需要排序的可以加上order by null禁止排序。注意，where高於having，能寫在where中的限定條件就不要去having限定了。

Using filesort檔案排序原理詳解

單路排序模式；

是一次性取出滿足條件行的所有欄位，然後在sort buffer中進行排序；用trace工具可以看到sort_mode資訊裡顯示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >

雙路排序（又叫回表排序模式）

是首先根據相應的條件取出相應的排序欄位和可以直接定位行資料的行 ID，然後在 sort buffer 中進行排序，排序完後需要再次取回其它需要的欄位；用trace工具可以看到sort_mode資訊裡顯示< sort_key, rowid >

MySQL 通過比較系統變數 max_length_for_sort_data(預設1024位元組) 的大小和需要查詢的欄位總大小來判斷使用哪種排序模式。

如果欄位的總長度小於max_length_for_sort_data ，那麼使用單路排序模式；
如果欄位的總長度大於max_length_for_sort_data ，那麼使用雙路排序模·式。

分頁優化

常規的limit分頁

有如下查詢語句

select * from employees limit 10000,10;

該sql並不是只查詢了10條，而是查詢了10010條，然後把前10000條結果給捨棄掉, 因此要查詢一個大表靠後的內容，執行效率是非常低的

優化

根據主鍵排序

上面的下面的sql語句沒有指定排序方式，預設使用ID排序。當使用ID排序時，我們可以使用下面的優化。

select * from employees where id > 90000 limit 5;

如果id是連續自增的，和limit 90000,5 結果沒有差別，是 90001 ~ 90005 的資料。

但是如果在90000之前刪除了一條資料，結果就不一樣了，id > 90000 limit 5 的結果是 90001 ~ 90005，但是limit 90000, 5 的結果是 90002 ~ 90006，很明顯 90002 ~ 90006 才是符合我們直覺的。所以這個優化只能限制與排序條件是連續的。如果id不是自增的呢？會出現什麼情況，假如 90000 這條資料有兩個，limit 90000, 5 的結果是 90000 ~ 90004，而 id > 90000 limit 5 的結果仍是 90001 ~ 90005，會把 id= 90000 的資料漏掉一條。

所以這個優化只能用於排序的欄位是連續自增的，並且不能重複

非主鍵排序的優化

有如下查詢語句

EXPLAIN select * from employees ORDER BY name limit 90000,5;

發現並沒有用上name的索引，因為 select * ,掃描聯合索引時，無法的到全部資料，需要回表，成本比全表掃描更高，所以優化器放棄使用索引。

可以使用索引覆蓋的方法，使用分頁查詢僅僅找到少量的主鍵，然後在使用主鍵查詢整行資料，如下:

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

看下執行計劃：

原 SQL 使用檔案排序，優化後的使用索引排序

表關聯優化

先造一些資料:

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 DEFAULT CHARSET=utf8;

create table t2 like t1;

-- 插入一些範例資料
-- 往t1表插入1萬行記錄
drop procedure if exists insert_t1; 
delimiter ;;
create procedure insert_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_t1();

-- 往t2表插入100行記錄
drop procedure if exists insert_t2; 
delimiter ;;
create procedure insert_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_t2();

新建 t1 t2 表，結構一樣，都在a欄位上有索引，b欄位沒有索引，t1表有 10000 行記錄，t2表只有100條記錄。

常見的表關聯演演算法

內嵌迴圈連線演演算法 Nested-Loop Join
基於塊的巢狀迴圈連線演演算法 Block Nested-Loop Join

內嵌迴圈連線演演算法

一次一行迴圈地從第一張表（稱為驅動表）中讀取行，在這行資料中取到關聯欄位，根據關聯欄位在另一張表（被驅動表）裡取出滿足條件的行，然後取出兩張表的結果合集。

一般關聯欄位有索引的時候使用這種演演算法, 範例:

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

從執行計劃中可以看到這些資訊：

驅動表是 t2，被驅動表是 t1。先執行的就是驅動表；優化器一般會優先選擇小表做驅動表，用where條件過濾完驅動表，然後再跟被驅動表做關聯查詢。所以使用 inner join 時，排在前面的表並不一定就是驅動表
當使用left join時，左表是驅動表，右表是被驅動表，當使用right join時，右表時驅動表，左表是被驅動表
使用了 NLJ演演算法。一般 join 語句中，如果執行計劃 Extra 中未出現 Using join buffer 則表示使用的 join 演演算法是 NLJ。

上面sql的大致流程如下：

從表 t2 中讀取一行資料（如果t2表有查詢過濾條件的，用先用條件過濾完，再從過濾結果裡取出一行資料）；
從第 1 步的資料中，取出關聯欄位 a，到表 t1 中查詢；
取出表 t1 中滿足條件的行，跟 t2 中獲取到的結果合併，作為結果返回給使用者端；
重複上面 3 步。

整個過程會讀取 t2 表的所有資料(掃描100行)，然後遍歷這每行資料中欄位 a 的值，根據 t2 表中 a 的值索引掃描 t1 表中的對應行(掃描100次 t1 表的索引，1次掃描可以認為最終只掃描 t1 表一行完整資料，也就是總共 t1 表也掃描了100行)。因此整個過程掃描了 200 行

基於塊的巢狀迴圈演演算法

當關聯欄位沒有沒有索引的時候會使用這種演演算法

把驅動表的資料讀入到 join_buffer 中，然後掃描被驅動表，把被驅動表每一行取出來跟 join_buffer 中的資料做對比。

如下:

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

Extra 中的Using join buffer (Block Nested Loop)說明該關聯查詢使用的是 BNL 演演算法。

上面sql的大致流程如下：

把 t2 的所有資料放入到 join_buffer 中
把表 t1 中每一行取出來，跟 join_buffer 中的資料做對比
返回滿足 join 條件的資料

整個過程對錶 t1 和 t2 都做了一次全表掃描，因此掃描的總行數為10000(表 t1 的資料總量) + 100(表 t2 的資料總量) = 10100。並且 join_buffer 裡的資料是無序的，因此對錶 t1 中的每一行，都要做 100 次判斷，所以記憶體中的判斷次數是 100 * 10000= 100 萬次。

這個例子裡表 t2 才 100 行，要是表 t2 是一個大表，join_buffer 放不下怎麼辦呢？·

join_buffer 的大小是由引數 join_buffer_size 設定的，預設值是 256k。如果放不下表 t2 的所有資料話，策略很簡單，就是分段放。

比如 t2 表有1000行記錄， join_buffer 一次只能放800行資料，那麼執行過程就是先往 join_buffer 裡放800行記錄，然後從 t1 表裡取資料跟 join_buffer 中資料對比得到部分結果，然後清空 join_buffer ，再放入 t2 表剩餘200行記錄，再次從 t1 表裡取資料跟 join_buffer 中資料對比。所以就多掃了一次 t1 表。

為什麼要使用 BNLJ 演演算法呢?

如果上面第二條sql使用 Nested-Loop Join，那麼掃描行數為 100 * 10000 = 100萬次，這個是磁碟掃描。

很顯然，用BNL磁碟掃描次數少很多，相比於磁碟掃描，BNL的記憶體計算會快得多。

因此MySQL對於被驅動表的關聯欄位沒索引的關聯查詢，一般都會使用 BNL 演演算法。如果有索引一般選擇 NLJ 演演算法，有索引的情況下 NLJ 演演算法比 BNL演演算法效能更高

對於關聯sql的優化

關聯欄位加索引，讓mysql做join操作時儘量選擇NLJ演演算法，驅動表因為需要全部查詢出來，所以過濾的條件也儘量要走索引，避免全表掃描，總之，能走索引的過濾條件儘量都走索引
小表驅動大表，寫多表連線sql時如果明確知道哪張表是小表可以用straight_join寫法固定連線驅動方式，省去mysql優化器自己判斷的時間

straight_join解釋：straight_join功能同join類似，但能讓左邊的表來驅動右邊的表，能改表優化器對於聯表查詢的執行順序。

比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql選著 t2 表作為驅動表。

straight_join只適用於inner join，並不適用於left join，right join。（因為left join，right join已經代表指定了表的執行順序）
儘可能讓優化器去判斷，因為大部分情況下mysql優化器是比人要聰明的。使用straight_join一定要慎重，因為部分情況下人為指定的執行順序並不一定會比優化引擎要靠譜。

小表的定義

在決定哪個表做驅動表的時候，應該是兩個表按照各自的條件過濾，過濾完成之後，計算參與 join 的各個欄位的總資料量，資料量小的那個表，就是“小表”，應該作為驅動表。不單單是表的總資料量

in和exsits優化

原則：小表驅動大表，即小的資料集驅動大的資料集

in：當B表的資料集小於A表的資料集時，in優於exists

select * from A where id in (select id from B)
#等價於： 　　
for(select id from B){
    select * from A where A.id = B.id
}

exists：當A表的資料集小於B表的資料集時，exists優於in

將主查詢A的資料，放到子查詢B中做條件驗證，根據驗證結果（true或false）來決定主查詢的資料是否保留

select * from A where exists (select 1 from B where B.id = A.id)
#等價於: 
for(select * from A){
  select * from B where B.id = A.id 
} 
#A表與B表的ID欄位應建立索引

關於Exists

EXISTS (subquery)只返回TRUE或FALSE,因此子查詢中的SELECT * 也可以用SELECT 1替換,官方說法是實際執行時會忽略SELECT清單,因此沒有區別
EXISTS子查詢的實際執行過程可能經過了優化而不是我們理解上的逐條對比
EXISTS子查詢往往也可以用JOIN來代替，何種最優需要具體問題具體分析

count 查詢優化

有下面四條查詢語句：

EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;

只有 count(欄位名) 不會把該欄位為null 計入總數

其實上面四條的查詢計劃都一樣，效率上沒有太大的差別

當欄位有索引

count(*)≈count(1)>count(欄位)>count(主鍵 id)

欄位有索引，count(欄位)統計走二級索引，二級索引儲存資料比主鍵索引少，所以count(欄位)>count(主鍵 id)

當欄位沒有索引

count(*)≈count(1)>count(主鍵 id)>count(欄位)

欄位沒有索引count(欄位)統計走不了索引，count(主鍵 id)還可以走主鍵索引，所以count(主鍵 id)>count(欄位)

count(1)

count(1)跟count(欄位)執行過程類似，不過count(1)不需要取出欄位統計，就用常數1做統計，count(欄位)還需要取出欄位，所以理論上count(1)比count(欄位)會快一點。

count(*)

count(*) 是例外，mysql並不會把全部欄位取出來，而是專門做了優化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常數)來替代 count(*)。

為什麼對於count(id)，mysql最終選擇輔助索引而不是主鍵聚集索引？因為二級索引相對主鍵索引儲存資料更少，檢索效能應該更高，mysql內部做了點優化(應該是在5.7版本才優化)。

常見優化方法

自己維護的總行數

show table status 如果只需要知道表總行數的估計值可以用如下sql查詢，效能很高 show table status like 'employee'

將總數維護到Redis裡插入或刪除表資料行的時候同時維護redis裡的表總行數key的計數值(用incr或decr命令)，但是這種方式可能不準，很難保證表操作和redis操作的事務一致性

索引設計原則

索引設計原則：

1、程式碼先行，索引後上

等到主體業務功能開發完畢，把涉及到該表相關sql都要拿出來分析之後再建立索引。

2、聯合索引儘量覆蓋條件

比如可以設計一個或者兩三個聯合索引(儘量少建單值索引)，讓每一個聯合索引都儘量去包含sql語句裡的where、order by、group by的欄位，還要確保這些聯合索引的欄位順序儘量滿足sql查詢的最左字首原則。

3、不要在小基數欄位上建立索引

索引基數是指這個欄位在表裡總共有多少個不同的值，比如一張表總共100萬行記錄，其中有個性別欄位，其值不是男就是女，那麼該欄位的基數就是2。

如果對這種小基數位段建立索引的話，還不如全表掃描了，因為你的索引樹裡就包含男和女兩種值，根本沒法進行快速的二分查詢，那用索引就沒有太大的意義了。

一般建立索引，儘量使用那些基數比較大的欄位，就是值比較多的欄位，那麼才能發揮出B+樹快速二分查詢的優勢來。

4、長字串我們可以採用字首索引

儘量對欄位型別較小的列設計索引，比如說什麼tinyint之類的，因為欄位型別較小的話，佔用磁碟空間也會比較小，此時你在搜尋的時候效能也會比較好一點。

當然，這個所謂的欄位型別小一點的列，也不是絕對的，很多時候你就是要針對varchar(255)這種欄位建立索引，哪怕多佔用一些磁碟空間也是有必要的。

對於這種varchar(255)的大欄位可能會比較佔用磁碟空間，可以稍微優化下，比如針對這個欄位的前20個字元建立索引，就是說，對這個欄位裡的每個值的前20個字元放在索引樹裡，類似於 KEY index(name(20),age,position)

此時你在where條件裡搜尋的時候，如果是根據name欄位來搜尋，那麼此時就會先到索引樹里根據name欄位的前20個字元去搜尋，定位到之後前20個字元的字首匹配的部分資料之後，再回到聚簇索引提取出來完整的name欄位值進行比對。

但是假如你要是order by name，那麼此時你的name因為在索引樹裡僅僅包含了前20個字元，所以這個排序是沒法用上索引的， group by也是同理

5、where與order by衝突時優先where

在where和order by出現索引設計衝突時，到底是針對where去設計索引，還是針對order by設計索引？到底是讓where去用上索引，還是讓order by用上索引?

一般這種時候往往都是讓where條件去使用索引來快速篩選出來一部分指定的資料，接著再進行排序。

因為大多數情況基於索引進行where篩選往往可以最快速度篩選出你要的少部分資料，然後做排序的成本可能會小很多。

舉個例子

有 employees表，name, age, sex, position 列，有聯合索引（name, age, sex, position）,

sex : 性別，取值0 或1

有如下查詢: select id from employees where name = 'zhangsan' and age = 18 and position = 'dev' 因為跳過了 sex 欄位，position 無法利用索引

因為 sex 只有兩個取值，我們在查詢語句上把 sex 的值全部列舉出來，如下:

select id from employees where name = 'zhangsan' and age = 18 and sex in (0, 1) and position = 'dev'

這樣一來就可以利用全部索引了。

再舉個例子

加入我們要查詢最近一週登入的使用者，首先想到的是 last_login_time > {一週之前的時間}

這是一個範圍查詢，在後面的所有欄位便無法利用索引了，我們可以再設計一個欄位，recent_login_flag(tinyint) 標識是否最近登入過。用定時任務定期更新該欄位的值。這樣就由範圍查詢變成了等值查詢，資料可能不是太及時變化，就看業務是否允許了。

總之就是想辦法最大限度的利用索引。

到此這篇關於MySQL 索引優化案例的文章就介紹到這了,更多相關MySQL 索引內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！