MYSQL 阿里的一個sql優化問題

gaopengtttt發表於2017-07-19
MySql阿里優化
原創水平有限，如果有誤請指出

今天研究了一天innodb事物，臨近下班了同事田興春告訴我有個阿里發出來的優化題，並且把建表和語句給我了，並且告訴我語句裡面有隱式轉換
昨天群裡也有人說這道題但是一直沒空看，剛好這會沒事就看了一下，整個指令碼如下：


點選(此處)摺疊或開啟

  1. 資料準備：

  3. create table a (id int auto_increment,seller_id bigint,seller_name varchar(100) collate utf8_bin ,gmt_create varchar(30),primary key(id));

  5. insert into a (seller_id,seller_name,gmt_create) values (100000,'uniqla','2017-01-01');
  6. insert into a (seller_id,seller_name,gmt_create) values (100001,'uniqlb','2017-02-01');
  7. insert into a (seller_id,seller_name,gmt_create) values (100002,'uniqlc','2017-03-01');
  8. insert into a (seller_id,seller_name,gmt_create) values (100003,'uniqld','2017-04-01');
  9. insert into a (seller_id,seller_name,gmt_create) values (100004,'uniqle','2017-05-01');
  10. insert into a (seller_id,seller_name,gmt_create) values (100005,'uniqlf','2017-06-01');
  11. insert into a (seller_id,seller_name,gmt_create) values (100006,'uniqlg','2017-07-01');
  12. insert into a (seller_id,seller_name,gmt_create) values (100007,'uniqlh','2017-08-01');
  13. insert into a (seller_id,seller_name,gmt_create) values (100008,'uniqli','2017-09-01');
  14. insert into a (seller_id,seller_name,gmt_create) values (100009,'uniqlj','2017-10-01');
  15. insert into a (seller_id,seller_name,gmt_create) values (100010,'uniqlk','2017-11-01');
  16. insert into a (seller_id,seller_name,gmt_create) values (100011,'uniqll','2017-12-01');
  17. insert into a (seller_id,seller_name,gmt_create) values (100012,'uniqlm','2018-01-01');
  18. insert into a (seller_id,seller_name,gmt_create) values (100013,'uniqln','2018-02-01');
  19. insert into a (seller_id,seller_name,gmt_create) values (100014,'uniqlo','2018-03-01');
  20. insert into a (seller_id,seller_name,gmt_create) values (100015,'uniqlp','2018-04-01');

  22. insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a;
  23. insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a;
  24. insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a;
  25. insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a;
  26. insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a;
  27. insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a;
  28. insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a;
  29. insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a;
  30. insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a;
  31. insert into a (seller_id,seller_name,gmt_create) select seller_id,seller_name,gmt_create from a;

  33. insert into a (seller_id,seller_name,gmt_create) values (100016,'uniqlq',now());

  35. create table b (id int auto_increment,seller_name varchar(100),user_id varchar(50),user_name varchar(100),sales bigint,gmt_create varchar(30),primary key(id));
  36. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqla','1','a',1,now());
  37. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlb','2','b',3,now());
  38. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlc','3','c',1,now());
  39. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqld','4','d',4,now());
  40. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqle','5','e',5,now());
  41. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlf','6','f',1,now());
  42. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlg','7','g',7,now());
  43. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlh','8','h',1,now());
  44. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqli','9','i',1,now());
  45. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlj','10','j',15,now());
  46. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlk','11','k',61,now());
  47. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqll','12','l',31,now());
  48. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlm','13','m',134,now());
  49. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqln','14','n',1455,now());
  50. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlo','15','o',166,now());
  51. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('niqlp','16','p',15,now());
  52.                                                                                                       

  54. insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
  55. insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
  56. insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
  57. insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
  58. insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
  59. insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
  60. insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
  61. insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
  62. insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
  63. insert into b (seller_name,user_id,user_name,sales,gmt_create) select seller_name,user_id,user_name,sales,gmt_create from b;
  64.  
  65.  
  66.  
  67.  
  68. insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('uniqlq','17','s',109,now());

  70.  



  74. create table c (id int auto_increment,user_id varchar(50),order_id varchar(100),state bigint,gmt_create varchar(30),primary key(id));
  75. insert into c (user_id,order_id,state,gmt_create) values( 21,1,0 ,now() );
  76. insert into c (user_id,order_id,state,gmt_create) values( 22,2,0 ,now() );
  77. insert into c (user_id,order_id,state,gmt_create) values( 33,3,0 ,now() );
  78. insert into c (user_id,order_id,state,gmt_create) values( 43,4,0 ,now() );
  79. insert into c (user_id,order_id,state,gmt_create) values( 54,5,0 ,now() );
  80. insert into c (user_id,order_id,state,gmt_create) values( 65,6,0 ,now() );
  81. insert into c (user_id,order_id,state,gmt_create) values( 75,7,0 ,now() );
  82. insert into c (user_id,order_id,state,gmt_create) values( 85,8,0 ,now() );
  83. insert into c (user_id,order_id,state,gmt_create) values( 95,8,0 ,now() );
  84. insert into c (user_id,order_id,state,gmt_create) values( 100,8,0 ,now() );
  85. insert into c (user_id,order_id,state,gmt_create) values( 150,8,0 ,now() );
  86. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
  87. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
  88. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
  89. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
  90. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
  91. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
  92. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
  93. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
  94. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
  95. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
  96. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
  97. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
  98. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
  99. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;
  100. insert into c (user_id,order_id,state,gmt_create) select user_id,order_id,state,gmt_create from c;

  102.  

  104. insert into c (user_id,order_id,state,gmt_create) values( 17,8,0 ,now() );



  108. 待優化SQL：
  109. select a.seller_id,a.seller_name,b.user_name,c.state
  110. from a,b,c
  111. where a.seller_name=b.seller_name
  112. and b.user_id=c.user_id
  113. and c.user_id=17
  114. and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE)
  115. AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
  116. order by a.gmt_create
先說明這個優化題目主要考察下面5點：
1、BNL和NJL的區別
2、NJL的實現
3、DBA對於資料分佈的觀察
4、隱式轉換索引不能使用
5、比較字符集不同索引不能使用

一、我們先來分別描述
1、BNL和NJL的區別
這個區別參考我的文章
http://blog.itpub.net/7728585/viewspace-2129502/
(從順序隨機I/O原理來討論MYSQL MRR NLJ BNL BKA )
簡單的說BNL一般用於TYPE=INDEX以及TYPE=ALL的情況，因為被驅動表連線條件沒有索引，而需要join buffer 將驅動表中待連線的
資料取出來(物理/邏輯 讀取)，放到join buffer，主要目的在於減少被驅動表的驅動次數，從而提高效率，因為沒有索引的情況
被驅動表掃描一次實在太慢了，這裡的B就是BLOCK的意思.
而NJL一般用於被驅動表連線條件有索引的情況，通過索引上的ref或者eq_ref(取決於索引是否唯一)就理所當然的快很多很多，這個時候join buffer是不會
使用的，它只需要讀取一條資料(物理/邏輯 讀取)來驅動一次驅動表，因為驅動表連線條件有索引，自然就快了(索引定位回表)
2、NJL的實現
同樣可以參考上面的文章，上面也大概說了一下，就不在廢話了
3、DBA對於資料分佈的觀察
這一點是人為可以達到的，簡單的說比如一個表有100條資料 99條為no=1 1條為no=2，那麼我們
需要對這個有所警覺，如果這個表示用作驅動表那麼no=2的時候效果要遠遠好於no=1。這道題也有
這個因素
明顯and  c.user_id='17' 只有一條資料  
4、隱式轉換索引不能使用
這個不管是MYSQL還是ORACLE都有的問題，
ORACLE會顯示給出來to_char(id)='1'之類的
MYSQL中會有如下類似的警告
| Warning | 1739 | Cannot use ref access on index 'user_id' due to type or collation conversion on field 'user_id'                                                                                                                                             |
| Warning | 1739 | Cannot use range access on index 'user_id' due to type or collation conversion on field 'user_id'    

比如這裡的
c.user_id=17  
而
user_id 是varchar型別不是int型別
又比如這裡的
 a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE) 
AND  DATE_ADD(NOW(), INTERVAL 600 MINUTE)  
這裡
gmt_create varchar(30) 居然也是varchar 擦！！
5、比較字符集不同索引使用異常
這個關於字串的比較問題我已經在文章裡面有所描述
http://blog.itpub.net/7728585/viewspace-2141914/
簡單的說這裡
a.seller_name=b.seller_name  
a.seller_name 比較字符集是utf8_bin 區分大小寫
而
b.seller_name 是不區分大小寫的這是預設的。
他們之間做join必然被驅動表用不到索引使用異常。(innodb 可以icp)
也會有類似如下的警告：
 Cannot use ref access on index 'seller_name' due to type or collation conversion on field 'seller_name'  

二、優化原則問題
我們知道基本所有的語句執行演算法邏輯都在MYSQL層次，INNODB只是負責將資料通過幾種方式
(PAGE_CUR_G，PAGE_CUR_GE，PAGE_CUR_L，PAGE_CUR_LE)掃描出來，遞送給MYSQL層次進行處理，這之間存在掃描拿到
innodb record-->innodb tuple-->mysql record的一個轉換的過程，這個步驟大部分被標記為sending data過程
(update/delete為updating)，那麼我們就有必要減少中間結果集的產生，來減少整個從innodb拿資料到MYSQL層的
整個資料量。這裡以NJL的優化原則為列解釋，因為這道題就是這個目的

1、減少NJL驅動結果集的資料
這事顯而易見的，減少驅動次數自然就減少了資料在innodb和mysql之間的傳遞
2、被驅動表的索引唯一性要儘量好
這個問題稍微難理解一點，但是仔細想一下也沒什麼，如果被驅動表索引唯一性更好，那麼通過索引回表的次數就少了，
這裡可以通過rows和filter進行大概判斷，大概是因為他們本來就不準。
曾經我們就有一個列子也是同事田興春和我一起看的。一個被驅動表有兩個連結條件，一個索引唯一性很差，而唯一性好的連線
列上沒有索引，我們在唯一性好的列上建立了索引效能馬上提升了。

三、關於本題

我們還是先避免c.user_id=17 隱試轉換將17改為'17',a.gmt_create沒有必要改他，原因後面會說
select a.seller_id,a.seller_name,b.user_name,c.state   
from  a,b,c
where  a.seller_name=b.seller_name  
and    b.user_id=c.user_id   
and  c.user_id='17'  
and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE) 
AND  DATE_ADD(NOW(), INTERVAL 600 MINUTE)  
order  by  a.gmt_create 

然後我們看一下執行計劃
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  16108 |    11.11 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  16173 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 359382 |     1.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.02 sec)

這裡使用BNL，這事正常的連線條件沒有任何索引，同時我們開始觀察資料發現這道題
c表最後插入了
insert into c (user_id,order_id,state,gmt_create)  values( 17,8,0 ,now() );
b表最後插入了
insert into b (seller_name,user_id,user_name,sales,gmt_create) values ('uniqlq','17','s',109,now());
a表最後插入了
insert into a (seller_id,seller_name,gmt_create) values (100016,'uniqlq',now());
我們可以發現整個語句不管a,b,c表資料量有多大，整個連線下來只有一條資料，這也是我說的DBA對於資料分佈觀察的問題
，按照最優化的方法通過c表c.user_id='17'過濾後得到一個驅動結果集(實際上這裡b表也可以MYSQL自動轉換了)只有一條資料
然後連線b表（b.user_id=c.user_id）自然中間驅動結果集也只有一條資料，最後通過（a.seller_name=b.seller_name）連線
a表自然就只有一條資料了
and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE) 
AND  DATE_ADD(NOW(), INTERVAL 600 MINUTE)  
order  by  a.gmt_create 
這兩個都不用管它了。

按照這個思想。
我們可以先在c.user_id建立一個索引，意圖在於通過索引過濾掉 c.user_id='17'， b.user_id建立索引意圖在於NJL被驅動表使用索引而不是全表的BNL
執行計劃變為:

點選(此處)摺疊或開啟

  1. mysql> desc select a.seller_id,a.seller_name,b.user_name,c.state from a,b,c where a.seller_name=b.seller_name and b.user_id=c.user_id and c.user_id='17' and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE) AND

  3.  DATE_ADD(NOW(), INTERVAL 600 MINUTE) order by a.gmt_create;
  4. +----+-------------+-------+------------+------+---------------+---------+---------+-------+-------+----------+----------------------------------------------------+
  5. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  6. +----+-------------+-------+------------+------+---------------+---------+---------+-------+-------+----------+----------------------------------------------------+
  7. | 1 | SIMPLE | b | NULL | ref | user_id | user_id | 153 | const | 1 | 100.00 | Using temporary; Using filesort |
  8. | 1 | SIMPLE | c | NULL | ref | user_id | user_id | 153 | const | 1 | 100.00 | Using index condition |
  9. | 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 16108 | 1.11 | Using where; Using join buffer (Block Nested Loop) |
  10. +----+-------------+-------+------------+------+---------------+---------+---------+-------+-------+----------+----------------------------------------------------+
顯然這裡b.user_id=c.user_id  and  c.user_id='17' 有一個轉換如下我們通過sql trace可以看到

 "resulting_condition": "((`a`.`seller_name` = `b`.`seller_name`) and (`c`.`user_id` = '17') and (`a`.`gmt_create` between (now() + interval -(600) minute) and (now() + interval 600 minute)) and multiple equal(`b`.`user_id`, `c`.`user_id`))"
我們可以注意這裡的
multiple equal(`b`.`user_id`, `c`.`user_id`))"
這實際上進行了轉換 因為顯然的b.user_id='17'是成立的

剩下的就是解決a表的BNL問題。我們不能讓a表進行type=ALL 全表掃描，從而加快速度
我們在a.seller_name和b.seller_name建立索引執行計劃變成了

點選(此處)摺疊或開啟

  1. mysql> desc select a.seller_id,a.seller_name,b.user_name,c.state from a,b,c where a.seller_name=b.seller_name and b.user_id=c.user_id and c.user_id='17' and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE) AND

  3.  DATE_ADD(NOW(), INTERVAL 600 MINUTE) order by a.gmt_create;
  4. +----+-------------+-------+------------+------+---------------------+-------------+---------+--------------------+------+----------+----------------------------------------------+
  5. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  6. +----+-------------+-------+------------+------+---------------------+-------------+---------+--------------------+------+----------+----------------------------------------------+
  7. | 1 | SIMPLE | b | NULL | ref | user_id,seller_name | user_id | 153 | const | 1 | 100.00 | Using where; Using temporary; Using filesort |
  8. | 1 | SIMPLE | c | NULL | ref | user_id | user_id | 153 | const | 1 | 100.00 | Using index condition |
  9. | 1 | SIMPLE | a | NULL | ref | seller_name | seller_name | 303 | test.b.seller_name | 947 | 11.11 | Using index condition; Using where |
  10. +----+-------------+-------+------------+------+---------------------+-------------+---------+--------------------+------+----------+----------------------------------------------+
  11. 3 rows in set, 2 warnings (0.00 sec)

這個時候我們看起來使用到了索引，但是這是ICP的功勞，我們看警告
 Cannot use ref access on index 'seller_name' due to type or collation conversion on field 'seller_name'    
這也就是我說的比較字符集不同索引使用異常，為了消除這個問題我們不得不更改a表seller_name的比較字符集

最後我們得到執行計劃

點選(此處)摺疊或開啟

  1. +----+-------------+-------+------------+------+---------------------+-------------+---------+--------------------+------+----------+----------------------------------------------+
  2. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  3. +----+-------------+-------+------------+------+---------------------+-------------+---------+--------------------+------+----------+----------------------------------------------+
  4. | 1 | SIMPLE | b | NULL | ref | user_id,seller_name | user_id | 153 | const | 1 | 100.00 | Using where; Using temporary; Using filesort |
  5. | 1 | SIMPLE | c | NULL | ref | user_id | user_id | 153 | const | 1 | 100.00 | Using index condition |
  6. | 1 | SIMPLE | a | NULL | ref | seller_name | seller_name | 303 | test.b.seller_name | 1 | 11.11 | Using where |
  7. +----+-------------+-------+------------+------+---------------------+-------------+---------+--------------------+------+----------+----------------------------------------------+

這下一切都正常了，Using index condition ICP沒有了，只有一個where了這個where顯然是
a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE) 
AND  DATE_ADD(NOW(), INTERVAL 600 MINUTE)  
至於
Using temporary; Using filesort
我們可以不理他了一條資料而已

至此優化結束。
優化後profile
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000169 | 0.000000 |   0.000000 |
| checking permissions | 0.000005 | 0.000000 |   0.000000 |
| checking permissions | 0.000006 | 0.000000 |   0.000000 |
| checking permissions | 0.000005 | 0.000000 |   0.000000 |
| Opening tables       | 0.000026 | 0.000000 |   0.000000 |
| init                 | 0.000055 | 0.000000 |   0.000000 |
| System lock          | 0.000013 | 0.000000 |   0.000000 |
| optimizing           | 0.000018 | 0.000000 |   0.000000 |
| statistics           | 0.000118 | 0.000000 |   0.000000 |
| preparing            | 0.000022 | 0.000000 |   0.000000 |
| Creating tmp table   | 0.000030 | 0.000000 |   0.000000 |
| Sorting result       | 0.000007 | 0.000000 |   0.000000 |
| executing            | 0.000003 | 0.000000 |   0.000000 |
| Sending data         | 0.000101 | 0.000000 |   0.000000 |
| Creating sort index  | 0.000027 | 0.000000 |   0.000000 |
| end                  | 0.000004 | 0.000000 |   0.000000 |
| query end            | 0.000059 | 0.001000 |   0.000000 |
| removing tmp table   | 0.000096 | 0.000000 |   0.000000 |
| query end            | 0.000004 | 0.000000 |   0.000000 |
| closing tables       | 0.000008 | 0.000000 |   0.000000 |
| freeing items        | 0.000018 | 0.000000 |   0.000000 |
| cleaning up          | 0.000022 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+


這是之前的profile


+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000226 | 0.000000 |   0.000000 |
| checking permissions | 0.000011 | 0.000000 |   0.000000 |
| checking permissions | 0.000006 | 0.000000 |   0.000000 |
| checking permissions | 0.000014 | 0.000000 |   0.000000 |
| checking permissions | 0.000006 | 0.000000 |   0.000000 |
| checking permissions | 0.000004 | 0.000000 |   0.000000 |
| checking permissions | 0.000007 | 0.000000 |   0.000000 |
| Opening tables       | 0.000039 | 0.000000 |   0.000000 |
| init                 | 0.000238 | 0.001000 |   0.000000 |
| System lock          | 0.000029 | 0.000000 |   0.000000 |
| optimizing           | 0.000118 | 0.000000 |   0.000000 |
| statistics           | 0.000176 | 0.000000 |   0.000000 |
| preparing            | 0.000112 | 0.000000 |   0.000000 |
| Creating tmp table   | 0.000052 | 0.000000 |   0.000000 |
| Sorting result       | 0.000019 | 0.000000 |   0.000000 |
| executing            | 0.000005 | 0.000000 |   0.000000 |
| Sending data         | 0.231418 | 0.230965 |   0.000000 |
| Creating sort index  | 0.000055 | 0.000000 |   0.000000 |
| end                  | 0.000006 | 0.000000 |   0.000000 |
| query end            | 0.000012 | 0.000000 |   0.000000 |
| removing tmp table   | 0.000005 | 0.000000 |   0.000000 |
| query end            | 0.000004 | 0.000000 |   0.000000 |
| closing tables       | 0.000011 | 0.000000 |   0.000000 |
| freeing items        | 0.000347 | 0.000000 |   0.000000 |
| cleaning up          | 0.000015 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+
很顯然這裡Sending data太久，也就是我前面說的innodb和mysql之間資料互動的問題。

作者微信:

MYSQL 阿里的一個sql優化問題




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

相關文章