【Mysql】MySQL查詢最佳化-explain

小亮520cl發表於2015-10-28
MySqlAI

一、MySQL 查詢最佳化器是如何工作的

        MySQL 查詢最佳化器有幾個目標,但是其中最主要的目標是儘可能地使用索引,並且使用最嚴格的索引來消除儘可能多的資料行。最終目標是提交 SELECT 語句查詢資料行,而不是排除資料行。最佳化器試圖排除資料行的原因在於它排除資料行的速度越快,那麼找到與條件匹配的資料行也就越快。如果能夠首先進行最嚴格的測試,查詢就可以執行地更快。

        EXPLAIN 的每個輸出行提供一個表的相關資訊,並且每個行包括下面的列:

項 說明
id          MySQL Query Optimizer 選定的執行計劃中查詢的序列號。表示查詢中執行 select 子句或操作表的順序,id值越大優先順序越高,越先被執行。id 相同,執行順序由上至下。

  

select_type 查詢型別 說明
SIMPLE 簡單的 select 查詢,不使用 union 及子查詢
PRIMARY 最外層的 select 查詢
UNION UNION 中的第二個或隨後的 select 查詢,不 依賴於外部查詢的結果集
DEPENDENT UNION UNION 中的第二個或隨後的 select 查詢,依 賴於外部查詢的結果集
SUBQUERY 子查詢中的第一個 select 查詢,不依賴於外 部查詢的結果集
DEPENDENT SUBQUERY 子查詢中的第一個 select 查詢,依賴於外部 查詢的結果集
DERIVED 用於 from 子句裡有子查詢的情況。 MySQL 會 遞迴執行這些子查詢, 把結果放在臨時表裡。
UNCACHEABLE SUBQUERY 結果集不能被快取的子查詢,必須重新為外 層查詢的每一行進行評估。
UNCACHEABLE UNION UNION 中的第二個或隨後的 select 查詢,屬 於不可快取的子查詢


項 說明
table  輸出行所引用的表

         

type 重要的項,顯示連線使用的型別,按最 優到最差的型別排序 說明
system  表僅有一行(=系統表)。這是 const 連線型別的一個特例。
const  const 用於用常數值比較 PRIMARY KEY 時。當 查詢的表僅有一行時,使用 System。
eq_ref  const 用於用常數值比較 PRIMARY KEY 時。當 查詢的表僅有一行時,使用 System。
ref  連線不能基於關鍵字選擇單個行,可能查詢 到多個符合條件的行。 叫做 ref 是因為索引要 跟某個參考值相比較。這個參考值或者是一 個常數,或者是來自一個表裡的多表查詢的 結果值
ref_or_null  如同 ref, 但是 MySQL 必須在初次查詢的結果 裡找出 null 條目,然後進行二次查詢。
index_merge  說明索引合併最佳化被使用了。
unique_subquery  在某些 IN 查詢中使用此種型別,而不是常規的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery  在 某 些 IN 查 詢 中 使 用 此 種 類 型 , 與 unique_subquery 類似,但是查詢的是非唯一 性索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range  只檢索給定範圍的行,使用一個索引來選擇 行。key 列顯示使用了哪個索引。當使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 運算子,用常量比較關鍵字列時,可 以使用 range。
index  全表掃描,只是掃描表的時候按照索引次序 進行而不是行。主要優點就是避免了排序, 但是開銷仍然非常大。
all  最壞的情況,從頭到尾全表掃描。


項 說明
possible_keys  指出 MySQL 能在該表中使用哪些索引有助於 查詢。如果為空,說明沒有可用的索引。

        

項 說明
key  MySQL 實際從 possible_key 選擇使用的索引。 如果為 NULL,則沒有使用索引。很少的情況 下,MYSQL 會選擇最佳化不足的索引。這種情 況下,可以在 SELECT 語句中使用 USE INDEX (indexname)來強制使用一個索引或者用 IGNORE INDEX(indexname)來強制 MYSQL 忽略索引

         

項 說明
key_len  使用的索引的長度。在不損失精確性的情況 下,長度越短越好。

         

項 說明
ref  顯示索引的哪一列被使用了


項 說明
rows  MYSQL 認為必須檢查的用來返回請求資料的行數


      extra 中出現以下 2 項意味著 MYSQL 根本不能使用索引,效率會受到重大影響。應儘可能對此進行最佳化。         

extra 項 說明
Using filesort  表示 MySQL 會對結果使用一個外部索引排序,而不是從表裡按索引次序讀到相關內容。可能在記憶體或者磁碟上進行排序。MySQL 中無法利用索引完成的排序操作稱為“檔案排序”
Using temporary  表示 MySQL 在對查詢結果排序時使用臨時表。常見於排序 order by 和分組查詢 group by。

      using index  表示最佳化器進行了覆蓋索引的操作
     using index Condition  表示使用了ICP最佳化查詢
     using MRR    表示使用了MRR最佳化查詢


  1. 下面來舉一個例子來說明下 explain 的用法。

  3.        先來一張表:

  5. CREATE TABLE IF NOT EXISTS `article` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  6. `author_id` int(10) unsigned NOT NULL,
  7. `category_id` int(10) unsigned NOT NULL,
  8. `views` int(10) unsigned NOT NULL,
  9. `comments` int(10) unsigned NOT NULL,
  10. `title` varbinary(255) NOT NULL,
  11. `content` text NOT NULL,
  12. PRIMARY KEY (`id`)
  13. );
  14.       再插幾條資料:

  16. INSERT INTO `article`
  17. (`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
  18. (1, 1, 1, 1, '1', '1'),
  19. (2, 2, 2, 2, '2', '2'),
  20. (1, 1, 3, 3, '3', '3');

  22.        需求:
  23.        查詢 category_id 為 1 且 comments 大於 1 的情況下,views 最多的 article_id。


  26.        先查查試試看:

  28. EXPLAIN
  29. SELECT author_id
  30. FROM `article`
  31. WHERE category_id = 1 AND comments > 1
  32. ORDER BY views DESC
  33. LIMIT 1\G
  34.        看看部分輸出結果:

  36. *************************** 1. row ***************************
  37.            id: 1
  38.   select_type: SIMPLE
  39.         table: article
  40.          type: ALL
  41. possible_keys: NULL
  42.           key: NULL
  43.       key_len: NULL
  44.           ref: NULL
  45.          rows: 3
  46.         Extra: Using where; Using filesort
  47. 1 row in set (0.00 sec)

  49.        很顯然,type 是 ALL,即最壞的情況。Extra 裡還出現了 Using filesort,也是最壞的情況。最佳化是必須的。


  52.        嗯,那麼最簡單的解決方案就是加索引了。好,我們來試一試。查詢的條件裡即 where 之後共使用了 category_id,comments,views 三個欄位。那麼來一個聯合索引是最簡單的了。

  54. ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `views` );

  56.        結果有了一定好轉,但仍然很糟糕:

  58. *************************** 1. row ***************************
  59.            id: 1
  60.   select_type: SIMPLE
  61.         table: article
  62.          type: range
  63. possible_keys: x
  64.           key: x
  65.       key_len: 8
  66.           ref: NULL
  67.          rows: 1
  68.         Extra: Using where; Using filesort
  69. 1 row in set (0.00 sec)

  71.        type 變成了 range,這是可以忍受的。但是 extra 裡使用 Using filesort 仍是無法接受的。但是我們已經建立了索引,為啥沒用呢?這是因為按照 BTree 索引的工作原理,先排序 category_id,如果遇到相同的 category_id 則再排序 comments,如果遇到相同的 comments 則再排序 views。當 comments 欄位在聯合索引裡處於中間位置時,因comments > 1 條件是一個範圍值(所謂 range),MySQL 無法利用索引再對後面的 views 部分進行檢索,即 range 型別查詢欄位後面的索引無效。
  72.        

  74.        那麼我們需要拋棄 comments,刪除舊索引:

  76.  DROP INDEX x ON article;
  77.       然後建立新索引:

  79. ALTER TABLE `article` ADD INDEX y ( `category_id` , `views` ) ;
  80.       接著再執行查詢:

  82. *************************** 1. row ***************************
  83.            id: 1
  84.   select_type: SIMPLE
  85.         table: article
  86.          type: ref
  87. possible_keys: y
  88.           key: y
  89.       key_len: 4
  90.           ref: const
  91.          rows: 1
  92.         Extra: Using where
  93. 1 row in set (0.00 sec)

  95.       可以看到,type 變為了 ref,Extra 中的 Using filesort 也消失了,結果非常理想。

再來看一個多表查詢的例子。

      首先定義 3個表 class 和 room。 

 


	
	
    
		
		
  1. 
			CREATE TABLE IF NOT EXISTS `class` ( 
		
    2. 

		
  2. 
			`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
		
    3. 

		
  3. 
			`card` int(10) unsigned NOT NULL, 
		
    4. 

		
  4. 
			PRIMARY KEY (`id`) 
		
    5. 

		
  5. 
			); 
		
    6. 

		
  6. 
			CREATE TABLE IF NOT EXISTS `book` ( 
		
    7. 

		
  7. 
			`bookid` int(10) unsigned NOT NULL AUTO_INCREMENT, 
		
    8. 

		
  8. 
			`card` int(10) unsigned NOT NULL, 
		
    9. 

		
  9. 
			PRIMARY KEY (`bookid`) 
		
    10. 

		
  10. 
			); 
		
    11. 

		
  11. 
			CREATE TABLE IF NOT EXISTS `phone` ( 
		
    12. 

		
  12. 
			`phoneid` int(10) unsigned NOT NULL AUTO_INCREMENT, 
		
    13. 

		
  13. 
			`card` int(10) unsigned NOT NULL, 
		
    14. 

		
  14. 
			PRIMARY KEY (`phoneid`) 
		
    15. 

		
  15. 
			) engine = innodb; 
		
    16. 

		
  16. 
			     然後再分別插入大量資料。插入資料的php指令碼：
		
    17. 

		
  17. 
			<?php
		
    18. 

		
  18. 
			$link = mysql_connect("localhost","root","870516"); 
		
    19. 

		
  19. 
			mysql_select_db("test",$link); 
		
    20. 

		
  20. 
			for($i=0;$i<10000;$i++) 
		
    21. 

		
  21. 
			{
		
    22. 

		
  22. 
			    $j = rand(1,20); 
		
    23. 

		
  23. 
			    $sql = " insert into class(card) values({$j})"; 
		
    24. 

		
  24. 
			    mysql_query($sql); 
		
    25. 

		
  25. 
			}
		
    26. 

		
  26. 
			for($i=0;$i<10000;$i++) 
		
    27. 

		
  27. 
			{
		
    28. 

		
  28. 
			    $j = rand(1,20); 
		
    29. 

		
  29. 
			    $sql = " insert into book(card) values({$j})"; 
		
    30. 

		
  30. 
			    mysql_query($sql); 
		
    31. 

		
  31. 
			}
		
    32. 

		
  32. 
			for($i=0;$i<10000;$i++) 
		
    33. 

		
  33. 
			{
		
    34. 

		
  34. 
			    $j = rand(1,20); 
		
    35. 

		
  35. 
			    $sql = " insert into phone(card) values({$j})"; 
		
    36. 

		
  36. 
			    mysql_query($sql); 
		
    37. 

		
  37. 
			}
		
    38. 

		
  38. 
			mysql_query("COMMIT"); 
		
    39. 

		
  39. 
			?> 
		
    40. 

		
  40. 
			
		
    41. 

		
  41. 
			然後來看一個左連線查詢: 
		
    42. 

		
  42. 
			explain select * from class left join book on class.card = book.card\G
		
    43. 

		
  43. 
			     分析結果是: 
		
    44. 

		
  44. 
			*************************** 1. row *************************** 
		
    45. 

		
  45. 
			           id: 1
		
    46. 

		
  46. 
			  select_type: SIMPLE
		
    47. 

		
  47. 
			        table: class
		
    48. 

		
  48. 
			         type: ALL
		
    49. 

		
  49. 
			possible_keys: NULL
		
    50. 

		
  50. 
			          key: NULL
		
    51. 

		
  51. 
			      key_len: NULL
		
    52. 

		
  52. 
			          ref: NULL
		
    53. 

		
  53. 
			         rows: 20000
		
    54. 

		
  54. 
			        Extra: 
		
    55. 

		
  55. 
			*************************** 2. row *************************** 
		
    56. 

		
  56. 
			           id: 1
		
    57. 

		
  57. 
			  select_type: SIMPLE
		
    58. 

		
  58. 
			        table: book
		
    59. 

		
  59. 
			         type: ALL
		
    60. 

		
  60. 
			possible_keys: NULL
		
    61. 

		
  61. 
			          key: NULL
		
    62. 

		
  62. 
			      key_len: NULL
		
    63. 

		
  63. 
			          ref: NULL
		
    64. 

		
  64. 
			         rows: 20000
		
    65. 

		
  65. 
			        Extra: 
		
    66. 

		
  66. 
			2 rows in set (0.00 sec) 
		
    67. 

		
  67. 
			       顯然第二個 ALL 是需要我們進行最佳化的。
		
    68. 

		
  68. 
			       
		
    69. 

		
  69. 
			       建立個索引試試看: 
		
    70. 

		
  70. 
			ALTER TABLE `book` ADD INDEX y ( `card`); 
		
    71. 

		
  71. 
			*************************** 1. row *************************** 
		
    72. 

		
  72. 
			           id: 1
		
    73. 

		
  73. 
			  select_type: SIMPLE
		
    74. 

		
  74. 
			        table: class
		
    75. 

		
  75. 
			         type: ALL
		
    76. 

		
  76. 
			possible_keys: NULL
		
    77. 

		
  77. 
			          key: NULL
		
    78. 

		
  78. 
			      key_len: NULL
		
    79. 

		
  79. 
			          ref: NULL
		
    80. 

		
  80. 
			         rows: 20000
		
    81. 

		
  81. 
			        Extra: 
		
    82. 

		
  82. 
			*************************** 2. row *************************** 
		
    83. 

		
  83. 
			           id: 1
		
    84. 

		
  84. 
			  select_type: SIMPLE
		
    85. 

		
  85. 
			        table: book
		
    86. 

		
  86. 
			         type: ref
		
    87. 

		
  87. 
			possible_keys: y
		
    88. 

		
  88. 
			          key: y
		
    89. 

		
  89. 
			      key_len: 4
		
    90. 

		
  90. 
			          ref: test.class.card
		
    91. 

		
  91. 
			         rows: 1000
		
    92. 

		
  92. 
			        Extra: 
		
    93. 

		
  93. 
			2 rows in set (0.00 sec) 
		
    94. 

		
  94. 
			
		
    95. 

		
  95. 
			       可以看到第二行的 type 變為了 ref,rows 也變成了 1741*18,最佳化比較明顯。這是由左連線特性決定的。LEFT JOIN 條件用於確定如何從右表搜尋行,左邊一定都有,所以右邊是我們的關鍵點,一定需要建立索引。
		
    96. 

		
  96. 
			       刪除舊索引: 
		
    97. 

		
  97. 
			DROP INDEX y ON book; 
		
    98. 

		
  98. 
			       建立新索引。
		
    99. 

		
  99. 
			ALTER TABLE `class` ADD INDEX x ( `card`); 
		
    100. 

		
  100. 
			結果
		
    101. 

		
  101. 
			*************************** 1. row *************************** 
		
    102. 

		
  102. 
			           id: 1
		
    103. 

		
  103. 
			  select_type: SIMPLE
		
    104. 

		
  104. 
			        table: class
		
    105. 

		
  105. 
			         type: ALL
		
    106. 

		
  106. 
			possible_keys: NULL
		
    107. 

		
  107. 
			          key: NULL
		
    108. 

		
  108. 
			      key_len: NULL
		
    109. 

		
  109. 
			          ref: NULL
		
    110. 

		
  110. 
			         rows: 20000
		
    111. 

		
  111. 
			        Extra: 
		
    112. 

		
  112. 
			*************************** 2. row *************************** 
		
    113. 

		
  113. 
			           id: 1
		
    114. 

		
  114. 
			  select_type: SIMPLE
		
    115. 

		
  115. 
			        table: book
		
    116. 

		
  116. 
			         type: ALL
		
    117. 

		
  117. 
			possible_keys: NULL
		
    118. 

		
  118. 
			          key: NULL
		
    119. 

		
  119. 
			      key_len: NULL
		
    120. 

		
  120. 
			          ref: NULL
		
    121. 

		
  121. 
			         rows: 20000
		
    122. 

		
  122. 
			        Extra: 
		
    123. 

		
  123. 
			2 rows in set (0.00 sec) 
		
    124. 

		
  124. 
			基本無變化。
		
    125. 

	


	

		  
	


	
    
		
		
  1. 
			然後來看一個右連線查詢: 
		
    2. 

		
  2. 
			explain select * from class right join book on class.card = book.card; 
		
    3. 

		
  3. 
			
		
    4. 

		
  4. 
			      分析結果是: 
		
    5. 

		
  5. 
			*************************** 1. row *************************** 
		
    6. 

		
  6. 
			           id: 1
		
    7. 

		
  7. 
			  select_type: SIMPLE
		
    8. 

		
  8. 
			        table: book
		
    9. 

		
  9. 
			         type: ALL
		
    10. 

		
  10. 
			possible_keys: NULL
		
    11. 

		
  11. 
			          key: NULL
		
    12. 

		
  12. 
			      key_len: NULL
		
    13. 

		
  13. 
			          ref: NULL
		
    14. 

		
  14. 
			         rows: 20000
		
    15. 

		
  15. 
			        Extra: 
		
    16. 

		
  16. 
			*************************** 2. row *************************** 
		
    17. 

		
  17. 
			           id: 1
		
    18. 

		
  18. 
			  select_type: SIMPLE
		
    19. 

		
  19. 
			        table: class
		
    20. 

		
  20. 
			         type: ref
		
    21. 

		
  21. 
			possible_keys: x
		
    22. 

		
  22. 
			          key: x
		
    23. 

		
  23. 
			      key_len: 4
		
    24. 

		
  24. 
			          ref: test.book.card
		
    25. 

		
  25. 
			         rows: 1000
		
    26. 

		
  26. 
			        Extra: 
		
    27. 

		
  27. 
			2 rows in set (0.00 sec) 
		
    28. 

		
  28. 
			
		
    29. 

		
  29. 
			最佳化較明顯。這是因為 RIGHT JOIN 條件用於確定如何從左表搜尋行,右邊一定都有,所以左邊是我們的關鍵點,一定需要建立索引。
		
    30. 

		
  30. 
			       刪除舊索引: 
		
    31. 

		
  31. 
			DROP INDEX x ON class; 
		
    32. 

		
  32. 
			       建立新索引。
		
    33. 

		
  33. 
			ALTER TABLE `book` ADD INDEX y ( `card`); 
		
    34. 

		
  34. 
			結果
		
    35. 

		
  35. 
			*************************** 1. row *************************** 
		
    36. 

		
  36. 
			           id: 1
		
    37. 

		
  37. 
			  select_type: SIMPLE
		
    38. 

		
  38. 
			        table: class
		
    39. 

		
  39. 
			         type: ALL
		
    40. 

		
  40. 
			possible_keys: NULL
		
    41. 

		
  41. 
			          key: NULL
		
    42. 

		
  42. 
			      key_len: NULL
		
    43. 

		
  43. 
			          ref: NULL
		
    44. 

		
  44. 
			         rows: 20000
		
    45. 

		
  45. 
			        Extra: 
		
    46. 

		
  46. 
			*************************** 2. row *************************** 
		
    47. 

		
  47. 
			           id: 1
		
    48. 

		
  48. 
			  select_type: SIMPLE
		
    49. 

		
  49. 
			        table: book
		
    50. 

		
  50. 
			         type: ALL
		
    51. 

		
  51. 
			possible_keys: NULL
		
    52. 

		
  52. 
			          key: NULL
		
    53. 

		
  53. 
			      key_len: NULL
		
    54. 

		
  54. 
			          ref: NULL
		
    55. 

		
  55. 
			         rows: 20000
		
    56. 

		
  56. 
			        Extra: 
		
    57. 

		
  57. 
			2 rows in set (0.00 sec) 
		
    58. 

		
  58. 
			
		
    59. 

		
  59. 
			基本無變化。 最後來看看 inner join 的情況: 
		
    60. 

		
  60. 
			explain select * from class inner join book on class.card = book.card; 
		
    61. 

		
  61. 
			
		
    62. 

		
  62. 
			      結果: 
		
    63. 

		
  63. 
			*************************** 1. row *************************** 
		
    64. 

		
  64. 
			           id: 1
		
    65. 

		
  65. 
			  select_type: SIMPLE
		
    66. 

		
  66. 
			        table: book
		
    67. 

		
  67. 
			         type: ALL
		
    68. 

		
  68. 
			possible_keys: NULL
		
    69. 

		
  69. 
			          key: NULL
		
    70. 

		
  70. 
			      key_len: NULL
		
    71. 

		
  71. 
			          ref: NULL
		
    72. 

		
  72. 
			         rows: 20000
		
    73. 

		
  73. 
			        Extra: 
		
    74. 

		
  74. 
			*************************** 2. row *************************** 
		
    75. 

		
  75. 
			           id: 1
		
    76. 

		
  76. 
			  select_type: SIMPLE
		
    77. 

		
  77. 
			        table: class
		
    78. 

		
  78. 
			         type: ref
		
    79. 

		
  79. 
			possible_keys: x
		
    80. 

		
  80. 
			          key: x
		
    81. 

		
  81. 
			      key_len: 4
		
    82. 

		
  82. 
			          ref: test.book.card
		
    83. 

		
  83. 
			         rows: 1000
		
    84. 

		
  84. 
			        Extra: 
		
    85. 

		
  85. 
			2 rows in set (0.00 sec) 
		
    86. 

		
  86. 
			      刪除舊索引: 
		
    87. 

		
  87. 
			DROP INDEX y ON book; 
		
    88. 

		
  88. 
			
		
    89. 

		
  89. 
			      結果
		
    90. 

		
  90. 
			*************************** 1. row *************************** 
		
    91. 

		
  91. 
			           id: 1
		
    92. 

		
  92. 
			  select_type: SIMPLE
		
    93. 

		
  93. 
			        table: class
		
    94. 

		
  94. 
			         type: ALL
		
    95. 

		
  95. 
			possible_keys: NULL
		
    96. 

		
  96. 
			          key: NULL
		
    97. 

		
  97. 
			      key_len: NULL
		
    98. 

		
  98. 
			          ref: NULL
		
    99. 

		
  99. 
			         rows: 20000
		
    100. 

		
  100. 
			        Extra: 
		
    101. 

		
  101. 
			*************************** 2. row *************************** 
		
    102. 

		
  102. 
			           id: 1
		
    103. 

		
  103. 
			  select_type: SIMPLE
		
    104. 

		
  104. 
			        table: book
		
    105. 

		
  105. 
			         type: ALL
		
    106. 

		
  106. 
			possible_keys: NULL
		
    107. 

		
  107. 
			          key: NULL
		
    108. 

		
  108. 
			      key_len: NULL
		
    109. 

		
  109. 
			          ref: NULL
		
    110. 

		
  110. 
			         rows: 20000
		
    111. 

		
  111. 
			        Extra: 
		
    112. 

		
  112. 
			2 rows in set (0.00 sec) 
		
    113. 

		
  113. 
			      建立新索引。
		
    114. 

		
  114. 
			ALTER TABLE `class` ADD INDEX x ( `card`); 
		
    115. 

		
  115. 
			結果
		
    116. 

		
  116. 
			*************************** 1. row *************************** 
		
    117. 

		
  117. 
			           id: 1
		
    118. 

		
  118. 
			  select_type: SIMPLE
		
    119. 

		
  119. 
			        table: class
		
    120. 

		
  120. 
			         type: ALL
		
    121. 

		
  121. 
			possible_keys: NULL
		
    122. 

		
  122. 
			          key: NULL
		
    123. 

		
  123. 
			      key_len: NULL
		
    124. 

		
  124. 
			          ref: NULL
		
    125. 

		
  125. 
			         rows: 20000
		
    126. 

		
  126. 
			        Extra: 
		
    127. 

		
  127. 
			*************************** 2. row *************************** 
		
    128. 

		
  128. 
			           id: 1
		
    129. 

		
  129. 
			  select_type: SIMPLE
		
    130. 

		
  130. 
			        table: book
		
    131. 

		
  131. 
			         type: ALL
		
    132. 

		
  132. 
			possible_keys: NULL
		
    133. 

		
  133. 
			          key: NULL
		
    134. 

		
  134. 
			      key_len: NULL
		
    135. 

		
  135. 
			          ref: NULL
		
    136. 

		
  136. 
			         rows: 20000
		
    137. 

		
  137. 
			        Extra: 
		
    138. 

		
  138. 
			2 rows in set (0.00 sec) 
		
    139. 

		
  139. 
			綜上所述,inner join 和 left join 差不多,都需要最佳化右表。而 right join 需要最佳化左表。 
		
    140. 

		
  140. 
			如果不用左右內的連線方式就用where a.id=b.id，就和我做的那個oracle的實驗差不多了，就是象在外間那邊加上索引一樣的搞法！ 
		
    141. 

		
  141. 
			  
		
    142. 

		
  142. 
			我們再來看看三表查詢的例子
		
    143. 

		
  143. 
			新增一個新索引：
		
    144. 

		
  144. 
			ALTER TABLE `phone` ADD INDEX z ( `card`); 
		
    145. 

		
  145. 
			ALTER TABLE `book` ADD INDEX y ( `card`); 
		
    146. 

		
  146. 
			explain select * from class left join book on class.card=book.card left join phone on book.card = phone.card; 
		
    147. 

		
  147. 
			*************************** 1. row *************************** 
		
    148. 

		
  148. 
			           id: 1
		
    149. 

		
  149. 
			  select_type: SIMPLE
		
    150. 

		
  150. 
			        table: class
		
    151. 

		
  151. 
			         type: ALL
		
    152. 

		
  152. 
			possible_keys: NULL
		
    153. 

		
  153. 
			          key: NULL
		
    154. 

		
  154. 
			      key_len: NULL
		
    155. 

		
  155. 
			          ref: NULL
		
    156. 

		
  156. 
			         rows: 20000
		
    157. 

		
  157. 
			        Extra: 
		
    158. 

		
  158. 
			*************************** 2. row *************************** 
		
    159. 

		
  159. 
			           id: 1
		
    160. 

		
  160. 
			  select_type: SIMPLE
		
    161. 

		
  161. 
			        table: book
		
    162. 

		
  162. 
			         type: ref
		
    163. 

		
  163. 
			possible_keys: y
		
    164. 

		
  164. 
			          key: y
		
    165. 

		
  165. 
			      key_len: 4
		
    166. 

		
  166. 
			          ref: test.class.card
		
    167. 

		
  167. 
			         rows: 1000
		
    168. 

		
  168. 
			        Extra: 
		
    169. 

		
  169. 
			*************************** 3. row *************************** 
		
    170. 

		
  170. 
			           id: 1
		
    171. 

		
  171. 
			  select_type: SIMPLE
		
    172. 

		
  172. 
			        table: phone
		
    173. 

		
  173. 
			         type: ref
		
    174. 

		
  174. 
			possible_keys: z
		
    175. 

		
  175. 
			          key: z
		
    176. 

		
  176. 
			      key_len: 4
		
    177. 

		
  177. 
			          ref: test.book.card
		
    178. 

		
  178. 
			         rows: 260
		
    179. 

		
  179. 
			        Extra: Using index
		
    180. 

		
  180. 
			3 rows in set (0.00 sec) 
		
    181. 

		
  181. 
			
		
    182. 

		
  182. 
			後 2 行的 type 都是 ref 且總 rows 最佳化很好,效果不錯。
		
    183. 

		
  183. 
			       MySql 中的 explain 語法可以幫助我們改寫查詢,最佳化表的結構和索引的設定,從而最大地提高查詢效率。當然,在大規模資料量時,索引的建立和維護的代價也是很高的,往往需要較長的時間和較大的空間,如果在不同的列組合上建立索引,空間的開銷會更大。因此索引最好設定在需要經常查詢的欄位中。
		
    184. 

		
  184. 
			
		
    185.



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29096438/viewspace-1817422/，如需轉載，請註明出處，否則將追究法律責任。

相關文章