記一次我的 MySQL 調優經歷

若相惜發表於2019-05-27
MySql

某一天突然發現我們的管理後臺的一個請求很慢，介面呼叫時間達到了8 s。很納悶，一個簡單的使用者列表介面，使用者資料才 4k+，還使用了分頁，為什麼會這麼慢呢。

經過除錯發現是 mysql 執行時間太長。這兒我們模擬兩張表和表資料：

create table `users` (
  `id` int(10) unsigned not null auto_increment,
  `name` varchar(255) default 'name',
  `a` varchar(255) default 'aaaaaaaaaaaaaaaa',
  `b` varchar(255) default 'bbbbbbbbbbbbbbbb',
  `c` varchar(255) default 'cccccccccccccccc',
  primary key (`id`)
) engine=innodb;
delimiter ;;
  create procedure usersdata()
  begin
    declare i int;
    set i=1;
    while(i<=4000)do
      insert into users(`name`) values('name');
      set i=i+1;
    end while;
  end;;
delimiter ;
call usersdata();
create table `user_enterprises` (
  `id` int(11) unsigned not null auto_increment,
  `user_id` int(11) default null,
  primary key (`id`)
) engine=innodb;
delimiter ;;
  create procedure enterprisesdata()
  begin
    declare i int;
    set i=1;
    while(i<=4000)do
      insert into user_enterprises(`user_id`) values(i);
      set i=i+1;
    end while;
  end;;
delimiter ;
call enterprisesdata();

把我們需要執行的 sql 列印出來：

select * from `users` left join `user_enterprises` on `users`.`id` = `user_enterprises`.`user_id` order by `users`.`id` desc;

使用 explain 命令：
記一次我的 MySQL 調優經歷

我們看到在 join 的時候使用了 BNL 演算法。它的過程大概是：

  • 首先把 users 表的所有資料加入到 join buffer 中。
  • 掃描整個 user_enterprises 表的每一行資料，與 join buffer 中的 users 資料作對比，將滿足條件的加入結果集。

雖然操作量很大，但都是在記憶體中完成的。
查詢掃描行數：

/* 開啟 optimizer_trace，只對本執行緒有效 */
SET optimizer_trace='enabled=on'; 

/* @a 儲存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 執行語句 */
select * from `users` left join `user_enterprises` on `users`.`id` = `user_enterprises`.`user_id` order by `users`.`id` desc;

/* @b 儲存 Innodb_rows_read 的當前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 計算 Innodb_rows_read 差值 */
select @b-@a;

我們得到的掃描行數是 12000。在執行 join 的時候，掃描行數應該是 4000 + 4000，還有 4000 的掃描行數我推測應該是回表取了資料。

啟用 optimizer_trace 除錯：

/* 開啟 optimizer_trace，只對本執行緒有效 */
set optimizer_trace='enabled=on'; 

/* 執行語句 */
select * from `users` left join `user_enterprises` on `users`.`id` = `user_enterprises`.`user_id` order by `users`.`id` desc;

/* 檢視 OPTIMIZER_TRACE 輸出 */
select * from `information_schema`.`optimizer_trace`;

我們看到使用的排序方法是 rowid 排序，select @@max_length_for_sort_data 的結果為 1024，即參與排序的欄位大於了這個值，mysql 會把排序欄位和主鍵取出來放入 sort buffer，完成排序後回表取資料。在這兒還用到了臨時表。所以大致執行過程應該是 join 之後把資料存在了臨時表，然後使用 rowid 排序。

從上面我們發現這個過程是複雜的，如果在 user_enterprises 表上給 user_id 加上索引。

alter table `user_enterprises` add key `user_id_index` (`user_id`);

再次使用 explain 檢視結果：

記一次我的 MySQL 調優經歷

首先 join 的執行流程發生了變化，大體流程是：

  • 在 users 表裡取出一行資料
  • 根據索引在 user_enterprises 表裡獲取結果，組成結果集

我們發現使用到了索引後，就沒有在 join buffer 裡那些複雜操作了。因為索引的有序性，排序也免了，整個查詢過程所需要的時間也大大減少。

~由此可見索引是多麼的重要啊！！！

本作品採用《CC 協議》，轉載必須註明作者和本文連結

Persevere,Vtr!

相關文章