MySQL5.7 使用utf8mb4字符集比latin1字符集性能低25%，你敢信？

sysbench使用utf8mb4測驗：

在進一步測驗的時候，我修改了/usr/share/sysbench/oltp_common.lua腳本的如下部分，使之在建立資料庫連接的時候執行"SET NAMES utf8mb4"（用途請參考https://dev.mysql.com/doc/refman/5.7/en/set-names.html），設定字符集為utf8mb4

function thread_init()
   drv = sysbench.sql.driver()
   con = drv:connect()
   con:query("SET NAMES utf8mb4")

照這樣設定之后，character_set_server  &&  database characterset  &&  table characterset  &&  Client characterset 都是utf8mb4了，

測驗結果顯示，my_ismbchar_utf8mb4和my_charpos_mb還是會消耗掉10%以上的CPU資源，而且，只要是character_set_server  ||  table characterset  ||  Client characterset的任何一個使用utf8mb4字符集，都會導致這個問題，

使用utf8字符集進行測驗，也有類似的問題，只不過消耗CPU資源較多的函式是my_ismbchar_utf8和my_charpos_mb，

所以我對這篇博客進行更正，結論是：不是因為character_set_server, table characterset, Client characterset字符集設定不一致導致性能差異，而是因為MySQL5.7在使用utf8/utf8mb4字符集的時候會有很高的額外開銷，

但是，我們不能因為utf8有額外開銷就不用它，對吧？畢竟latin1不支持中文……

因為MySQL8開始默認字符集變成了utf8mb4，所以我也對MySQL 8.0.19進行了測驗，結果顯示不存在這個問題；將字符集改成latin1進行測驗，性能比utf8mb4并沒有提升……

----------------------------------------------------分割線，以下是2020.3.15日的原文，原標題是《MySQL字符集不一致導致性能下降25%，你敢信？》，以上是更新/更正的內容----------------------------------------------------

故事是這樣的：

我在對MySQL進行性能測驗時，發現CPU使用率接近100%，其中80%us, 16%sys,3%wa，iostat發現磁盤iops2000以下，avgqu-sz不超過3，%util最高70%，看來瓶頸不在磁盤IO上面，而在CPU上，sys部分使用率有點高，

于是我果斷使用perf top查看，赫然排在前面的2個，是my_ismbchar_utf8mb4和my_charpos_mb，

my_ismbchar_utf8mb4顧名思義，很明顯是與字符集相關的；my_charpos_mb暫時不清楚，

經驗告訴我，這很不正常！通常來說，消耗CPU最多的應該是資料頁相關的操作才對啊，

我快速打開MySQL internal檔案搜索，沒找到有價值的資訊，

哦，你想要知道這個故事的前情提要？抱歉，我剛剛只說了壓測，按照國際慣例，我這就貼出環境和版本資訊：

硬體：8核16GB，200GB SSD，騰訊云虛擬機
作業系統版本：CentOS release 6.9 (Final)
MySQL版本：5.7.28-log MySQL Community Server (GPL)，二進制方式安裝
MySQL引數：innodb_buffer_pool_size = 10752M
          innodb_flush_log_at_trx_commit = 1
          sync_binlog = 1
          character-set-server = utf8mb4
sysbench版本：1.0.19
sysbench引數：sysbench /usr/share/sysbench/oltp_read_write.lua   --tables=3 --table-size=1000000  --mysql-password=*** --mysql-user=root --mysql-socket=/usr/local/mysql5.7.28/mysql.sock --threads=128 --time=1800 run 

server的字符集是utf8mb4，接下來檢查一下db和表的字符集吧：

 嗯嗯，看起來一切都是那么的正常……

server, DB, table的字符集都一致，現在只剩下sysbench的嫌疑最大！

可是，要怎么檢查sysbench已經連接到MySQL的那些會話的字符集設定呢？

我的sysbench命令沒有顯式地指定字符集；show processlist沒有character_set_client資訊，information_schema庫和mysql庫里面也沒有與character_set_client資訊，

sysbench --help 也沒有字符集相關的選項和引數；https://github.com/akopytov/sysbench/blob/master/src/drivers/mysql/drv_mysql.c  sysbench原始碼中也沒有字符集相關的設定，

看來，sysbench連接MySQL的字符集設定，應該默認是latin1，應該是這里的字符集設定不一致導致的，

BUT，對于技術問題，我不能光靠猜測啊！我一定要刨根問底，查它個水落石出……

原始碼：

吃CPU最多的是my_ismbchar_utf8mb4函式對吧？那就先到原始碼中搜它：

在strings/ctype-utf8.c 中定義的：

static uint
my_ismbchar_utf8mb4(const CHARSET_INFO *cs, const char *b, const char *e)
{
  int res= my_valid_mbcharlen_utf8mb4(cs, (const uchar*)b, (const uchar*)e);
  return (res > 1) ? res : 0;
}

它本身沒有復雜的邏輯，只是呼叫了my_valid_mbcharlen_utf8mb4，然后對回傳值res 進行判斷，如果>1，就回傳res，否則回傳0，

行，那我再看看my_valid_mbcharlen_utf8mb4吧，

static int
my_valid_mbcharlen_utf8mb4(const CHARSET_INFO *cs __attribute__((unused)),
                           const uchar *s, const uchar *e)
{
  uchar c;

  if (s >= e)
    return MY_CS_TOOSMALL;

  c= s[0];
  if (c < 0xf0)
    return my_valid_mbcharlen_utf8mb3(s, e);

  if (c < 0xf5)
  {
    if (s + 4 > e) /* We need 4 characters */
      return MY_CS_TOOSMALL4;

    /*
省略若干行……
    */

    if (!(IS_CONTINUATION_BYTE(s[1]) &&
          IS_CONTINUATION_BYTE(s[2]) &&
          IS_CONTINUATION_BYTE(s[3]) &&
          (c >= 0xf1 || s[1] >= 0x90) &&
          (c <= 0xf3 || s[1] <= 0x8F)))
      return MY_CS_ILSEQ;

    return 4;
  }

  return MY_CS_ILSEQ;
}

這個函式對輸入的字符進行比對，判斷是utf8mb3還是utf8mb4，utf8mb3？以前沒聽說過啊！上知乎一搜，原來還有這么一段有趣的歷史 ?

不過，僅僅看這個函式的代碼，是不會相信它居然會吃掉7%以上的CPU的，我也不信！

好吧，先做個perf record看看：

#第1步，查看mysqld行程的pid
ps -ef | grep mysqld 

#第2步，將mysqld行程相關的cpu-clock事件及呼叫堆疊記錄起來，默認保存在perf.data檔案中
perf record -e cpu-clock -g  -p 14345 

#第3步，用perf script工具對perf.data進行決議
perf script -i perf.data &> perf.unfold  

#第4步，下載一個集漂亮、強大于一身的工具：
git clone https://github.com/brendangregg/FlameGraph.git


#第5步：將perf.unfold中的符號進行折疊
./FlameGraph/stackcollapse-perf.pl perf.unfold  &> perf.folded

#第6步，生成火焰圖
./FlameGraph/flamegraph.pl perf.folded > perf.svg

效果就是這樣的↓  可以看出，my_ismbchar_utf8mb4占比確實最高，達到了7.47%

去跟蹤呼叫堆疊，可以發現是在sql\sql_lex.cc中的get_text()函式中，呼叫了宏use_mb和my_ismbchar來檢查字符集，

這2個宏同樣都是呼叫ismbchar() - detects whether the given string is a multi-byte sequence，   utf8mb4中的mb，全稱就是multi-byte

static char *get_text(Lex_input_stream *lip, int pre_skip, int post_skip)
{
  uchar c,sep;
  uint found_escape=0;
  const CHARSET_INFO *cs= lip->m_thd->charset();

  lip->tok_bitmap= 0;
  sep= lip->yyGetLast();                        // String should end with this
  while (! lip->eof())
  {
    c= lip->yyGet();
    lip->tok_bitmap|= c;
    {
      int l;
      if (use_mb(cs) &&
          (l = my_ismbchar(cs,
                           lip->get_ptr() -1,
                           lip->get_end_of_query()))) {
        lip->skip_binary(l-1);
        continue;
      }
    }
    if (c == '\\' &&
        !(lip->m_thd->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES))
    {                    // Escaped character
      found_escape=1;
      if (lip->eof())
    return 0;
      lip->yySkip();
    }
// 省略若干行……
  }
  return 0;                    // unexpected end of query
}

 解決方法：

上面說了一大通，可能有點云里霧里，抱歉哈，我能力有限，不能把它解釋得更通俗一些，

簡而言之，就是證明了確實是字符集不一致，導致MySQL在語法決議的時候，對每一個用戶輸入的字符（MySQL關鍵字除外），都要進行若干次字符集檢查，所以才會發生my_ismbchar_utf8mb4吃掉很多CPU資源這樣一個故事 ，

要解決就很簡單啦：保持character_set_server  &&  database characterset  &&  table characterset  &&  Client characterset一致！

我就是因為忽略了sysbench的字符集設定，所以才把自己給坑了，

既然sysbench沒有提供字符集相關的選項和引數，那我就把MySQL的字符集統一成latin1來測吧（也可以去修改sysbench的mysql driver原始碼，讓它支持設定字符集，但是我不擅長C……）

最后總結：

調整字符集之前，QPS最高只能壓到73797，統一字符集之后，QPS達到了98272，  73797/98272*100%=75.09%

再來看看TPS，調整字符集之前，TPS最高只能壓到3689，統一字符集之后，TPS達到了3689，  73797/4913*100%=75.08%

 多么痛的領悟……

標籤：MySQL

上一篇：linux 學習 mysql安裝到連接

下一篇：SQL JOIN用法示例