這篇文章主要介紹“MySQL INNODB主鍵使用varchar和int的區(qū)別是什么”，在日常操作中，相信很多人在MYSQL INNODB主鍵使用varchar和int的區(qū)別是什么問題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”MYSQL INNODB主鍵使用varchar和int的區(qū)別是什么”的疑惑有所幫助！接下來，請(qǐng)跟著小編一起來學(xué)習(xí)吧！

“專業(yè)、務(wù)實(shí)、高效、創(chuàng)新、把客戶的事當(dāng)成自己的事”是我們每一個(gè)人一直以來堅(jiān)持追求的企業(yè)文化。 成都創(chuàng)新互聯(lián)公司是您可以信賴的網(wǎng)站建設(shè)服務(wù)商、專業(yè)的互聯(lián)網(wǎng)服務(wù)提供商! 專注于做網(wǎng)站、成都網(wǎng)站建設(shè)、軟件開發(fā)、設(shè)計(jì)服務(wù)業(yè)務(wù)。我們始終堅(jiān)持以客戶需求為導(dǎo)向，結(jié)合用戶體驗(yàn)與視覺傳達(dá)，提供有針對(duì)性的項(xiàng)目解決方案，提供專業(yè)性的建議，創(chuàng)新互聯(lián)建站將不斷地超越自我，追逐市場(chǎng)，引領(lǐng)市場(chǎng)！

今天同事討論關(guān)于主鍵使用varchar和int的區(qū)別。

我現(xiàn)在總結(jié)的3個(gè)問題：
1、tablespace中空間浪費(fèi)
   當(dāng)然我們知道使用varchar可能會(huì)導(dǎo)致輔助索引比較大，因?yàn)橛玫絭archar可能存儲(chǔ)的字符較多，同時(shí)
   在行頭也存在一個(gè)可變字段字符區(qū)域(1-2)字節(jié)
   而輔助索引葉子結(jié)點(diǎn)畢竟都存儲(chǔ)了主鍵值，這樣至少會(huì)多varchar數(shù)據(jù)字節(jié)數(shù)量+1(或者2) 字節(jié)- 4(int)字節(jié)空間。
   如果輔助索引比較多空間浪費(fèi)是可想而知的。
2、輔助索引B+樹掃描性能
    由于輔助索引B+樹的空間要求更大，雖然在B+樹層次一般都是3層-4層，索引單值定位I/O消耗并不明顯，如果涉及到
    范圍查詢(比如PAGE_CUR_G),需要訪問的塊就更多，同時(shí)比如例如輔助索引的using index，需要訪問的塊自然
    更多
3、比較更加復(fù)雜
   innodb 在進(jìn)行元組比較的時(shí)候，不管是DML，select都會(huì)涉及到元組的比較，同時(shí)回表的時(shí)候也涉及
   到比較操作。而varchar類型的比較比int類型更為復(fù)雜一些。
那么我們就來分析第三個(gè)問題，第一個(gè)問題和第二個(gè)問題是顯而易見的。
我這里數(shù)據(jù)庫(kù)字符集為latin1\latin1_swedish_ci

其實(shí)在innodb底層進(jìn)行比較的時(shí)候都調(diào)用cmp_data這個(gè)函數(shù)
在innodb中有自己的定義的數(shù)據(jù)類型如下：

點(diǎn)擊(此處)折疊或打開

1. /*-------------------------------------------*/

2. /* The 'MAIN TYPE' of a column */

3. #define DATA_MISSING    0    /* missing column */

4. #define    DATA_VARCHAR    1    /* character varying of the

5. latin1_swedish_ci charset-collation; note

6. that the MySQL format for this, DATA_BINARY,

7. DATA_VARMYSQL, is also affected by whether the

8. 'precise type' contains

9. DATA_MYSQL_TRUE_VARCHAR */

10. #define DATA_CHAR    2    /* fixed length character of the

11. latin1_swedish_ci charset-collation */

12. #define DATA_FIXBINARY    3    /* binary string of fixed length */

13. #define DATA_BINARY    4    /* binary string */

14. #define DATA_BLOB    5    /* binary large object, or a TEXT type;

15. if prtype & DATA_BINARY_TYPE == 0, then this is

16. actually a TEXT column (or a BLOB created

17. with < 4.0.14; since column prefix indexes

18. came only in 4.0.14, the missing flag in BLOBs

19. created before that does not cause any harm) */

20. #define    DATA_INT    6    /* integer: can be any size 1 - 8 bytes */

21. #define    DATA_SYS_CHILD    7    /* address of the child page in node pointer */

22. #define    DATA_SYS    8    /* system column */

我們熟悉的int類型屬于DATA_INT而varchar屬于DATA_VARCHAR，rowid屬于DATA_SYS
在函數(shù)cmp_data根據(jù)各種類型的不同進(jìn)行了不同比較的方式，這里就將int和varchar
判斷的方式進(jìn)行說明：
1、innodb int類型比較
實(shí)際上是在cmp_data中進(jìn)行了大概的方式如下

點(diǎn)擊(此處)折疊或打開

1. if (len) {

2. #if defined __i386__ || defined __x86_64__ || defined _M_IX86 || defined _M_X64

3. /* Compare the first bytes with a loop to avoid the call

4. overhead of memcmp(). On x86 and x86-64, the GCC built-in

5. (repz cmpsb) seems to be very slow, so we will be calling the

6. libc version. http://gcc.gnu.org/bugzilla/show_bug.cgi?id=43052

7. tracks the slowness of the GCC built-in memcmp().

10. We compare up to the first 4..7 bytes with the loop.

11. The (len & 3) is used for "normalizing" or

12. "quantizing" the len parameter for the memcmp() call,

13. in case the whole prefix is equal. On x86 and x86-64,

14. the GNU libc memcmp() of equal strings is faster with

15. len=4 than with len=3.

18. On other architectures than the IA32 or AMD64, there could

19. be a built-in memcmp() that is faster than the loop.

20. We only use the loop where we know that it can improve

21. the performance. */

22. for (ulint i = 4 + (len & 3); i > 0; i--) {

23. cmp = int(*data1++) - int(*data2++);

24. if (cmp) {

25. return(cmp);

26. }

29. if (!--len) {

30. break;

31. }

32. }

33. my_strnncollsp_simple

36. if (len) {

37. #endif /* IA32 or AMD64 */

38. cmp = memcmp(data1, data2, len);

41. if (cmp) {

42. return(cmp);

43. }

46. data1 += len;

47. data2 += len;

48. #if defined __i386__ || defined __x86_64__ || defined _M_IX86 || defined _M_X64

49. }

50. #endif /* IA32 or AMD64 */

51. }

54. cmp = (int) (len1 - len2);

57. if (!cmp || pad == ULINT_UNDEFINED) {

58. return(cmp);

59. }

可以看到整個(gè)方式比較簡(jiǎn)潔，對(duì)于我們常用的x86_64模型并沒有直接使用memcpy進(jìn)行而是
進(jìn)行了優(yōu)化在注釋中也有說明，才出現(xiàn)了for (ulint i = 4 + (len & 3); i > 0; i--)
部分，如果是IA32 or AMD64則直接使用memcpy進(jìn)行比較。感興趣的可以仔細(xì)閱讀一下

2、innodb varchar類型比較
實(shí)際上這個(gè)比較會(huì)通過cmp_data->cmp_whole_field->my_strnncollsp_simple調(diào)用最終調(diào)用
my_strnncollsp_simple完成，而比如order by 會(huì)調(diào)用my_strnxfrm_simple他們都在一個(gè)
文件中。
下面是整個(gè)my_strnncollsp_simple函數(shù)

點(diǎn)擊(此處)折疊或打開

1. /*

2.   Compare strings, discarding end space

5.   SYNOPSIS

6.     my_strnncollsp_simple()

7.     cs    character set handler

8.     a    First string to compare

9.     a_length    Length of 'a'

10.     b    Second string to compare

11.     b_length    Length of 'b'

12.     diff_if_only_endspace_difference

13.        Set to 1 if the strings should be regarded as different

14.                         if they only difference in end space

17.   IMPLEMENTATION

18.     If one string is shorter as the other, then we space extend the other

19.     so that the strings have equal length.

22.     This will ensure that the following things hold:

25.     "a" == "a "

26.     "a\0" < "a"

27.     "a\0" < "a "

30.   RETURN

31.     < 0    a < b

32.     = 0    a == b

33.     > 0    a > b

34. */

37. int my_strnncollsp_simple(const CHARSET_INFO *cs, const uchar *a,

38.                           size_t a_length, const uchar *b, size_t b_length,

39.                           my_bool diff_if_only_endspace_difference)

40. {

41.   const uchar *map= cs->sort_order, *end;

42.   size_t length;

43.   int res;

46. #ifndef VARCHAR_WITH_DIFF_ENDSPACE_ARE_DIFFERENT_FOR_UNIQUE

47.   diff_if_only_endspace_difference= 0;

48. #endif

51.   end= a + (length= MY_MIN(a_length, b_length));

52.   while (a < end)

53.   {

54.     if (map[*a++] != map[*b++])

55.       return ((int) map[a[-1]] - (int) map[b[-1]]);

56.   }

57.   res= 0;

58.   if (a_length != b_length)

59.   {

60.     int swap= 1;

61.     if (diff_if_only_endspace_difference)

62.       res= 1; /* Assume 'a' is bigger */

63.     /*

64.       Check the next not space character of the longer key. If it's < ' ',

65.       then it's smaller than the other key.

66.     */

67.     if (a_length < b_length)

68.     {

69.       /* put shorter key in s */

70.       a_length= b_length;

71.       a= b;

72.       swap= -1; /* swap sign of result */

73.       res= -res;

74.     }

75.     for (end= a + a_length-length; a < end ; a++)

76.     {

77.       if (map[*a] != map[' '])

78. return (map[*a] < map[' ']) ? -swap : swap;

79.     }

80.   }

81.   return res;

82. }

其中*map= cs->sort_order比較關(guān)鍵這是內(nèi)存中已經(jīng)存儲(chǔ)好的字符集的順序，
循環(huán)進(jìn)行
map[*a++] != map[*b++]
*a++和*b++ 會(huì)得到的字符集編碼，然后在整個(gè)排序好的字符數(shù)組中找，
則得到了實(shí)際字符集編碼進(jìn)行比較，不管是比較的復(fù)雜度還是需要比較的
長(zhǎng)度 varchar很可能都遠(yuǎn)遠(yuǎn)大于int類型，下面是打印cs->sort_order這片
內(nèi)存區(qū)域前128字節(jié)得到的結(jié)果，
(gdb) x/128bx 0x258b000
0x258b000 :          0x00    0x01    0x02    0x03    0x04    0x05    0x06    0x07
0x258b008 :        0x08    0x09    0x0a    0x0b    0x0c    0x0d    0x0e    0x0f
0x258b010 :       0x10    0x11    0x12    0x13    0x14    0x15    0x16    0x17
0x258b018 :       0x18    0x19    0x1a    0x1b    0x1c    0x1d    0x1e    0x1f
0x258b020 :       0x20    0x21    0x22    0x23    0x24    0x25    0x26    0x27
0x258b028 :       0x28    0x29    0x2a    0x2b    0x2c    0x2d    0x2e    0x2f
0x258b030 :       0x30    0x31    0x32    0x33    0x34    0x35    0x36    0x37
0x258b038 :       0x38    0x39    0x3a    0x3b    0x3c    0x3d    0x3e    0x3f
0x258b040 :       0x40    0x41    0x42    0x43    0x44    0x45    0x46    0x47
0x258b048 :       0x48    0x49    0x4a    0x4b    0x4c    0x4d    0x4e    0x4f
0x258b050 :       0x50    0x51    0x52    0x53    0x54    0x55    0x56    0x57
0x258b058 :       0x58    0x59    0x5a    0x5b    0x5c    0x5d    0x5e    0x5f
0x258b060 :       0x60    0x41    0x42    0x43    0x44    0x45    0x46    0x47
0x258b068 :      0x48    0x49    0x4a    0x4b    0x4c    0x4d    0x4e    0x4f
0x258b070 :      0x50    0x51    0x52    0x53    0x54    0x55    0x56    0x57
0x258b078 :      0x58    0x59    0x5a    0x7b    0x7c    0x7d    0x7e    0x7f
而從內(nèi)存的地址0x258b000我們也能看到他確實(shí)是存在于堆內(nèi)存空間中，它是一片堆內(nèi)存區(qū)域。

下面是varchar比較的調(diào)用棧幀以備后用

#0  my_strnncollsp_simple (cs=0x2d4b9c0, a=0x7fff57a71f93 "gaopeng", a_length=7, b=0x7fffbd7e807f "gaopeng", b_length=7, diff_if_only_endspace_difference=0 '\000')

1.     at /root/mysql5.7.14/percona-server-5.7.14-7/strings/ctype-simple.c:165

2. #1  0x0000000001ab8ec2 in cmp_whole_field (mtype=1, prtype=524303, a=0x7fff57a71f93 "gaopeng", a_length=7, b=0x7fffbd7e807f "gaopeng", b_length=7)

3.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/rem/rem0cmp.cc:374

4. #2  0x0000000001aba827 in cmp_data (mtype=1, prtype=524303, data1=0x7fff57a71f93 "gaopeng", len1=7, data2=0x7fffbd7e807f "gaopeng", len2=7)

5.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/rem/rem0cmp.cc:468

6. #3  0x0000000001ab9a05 in cmp_dtuple_rec_with_match_bytes (dtuple=0x7fff48ed3280, rec=0x7fffbd7e807f "gaopeng", index=0x7fff48ec78a0, offsets=0x7fff57a6bc50,

7.     matched_fields=0x7fff57a6bf80, matched_bytes=0x7fff57a6bf78) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/rem/rem0cmp.cc:880

8. #4  0x0000000001a87fe2 in page_cur_search_with_match_bytes (block=0x7fffbcceafc0, index=0x7fff48ec78a0, tuple=0x7fff48ed3280, mode=PAGE_CUR_GE,

9.     iup_matched_fields=0x7fff57a6cdf8, iup_matched_bytes=0x7fff57a6cdf0, ilow_matched_fields=0x7fff57a6cde8, ilow_matched_bytes=0x7fff57a6cde0, cursor=0x7fff57a713f8)

10.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/page/page0cur.cc:850

11. #5  0x0000000001c17a3e in btr_cur_search_to_nth_level (index=0x7fff48ec78a0, level=0, tuple=0x7fff48ed3280, mode=PAGE_CUR_GE, latch_mode=1, cursor=0x7fff57a713f0,

12.     has_search_latch=0, file=0x2336938 "/root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/btr/btr0cur.cc", line=5744, mtr=0x7fff57a70ee0)

13.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/btr/btr0cur.cc:1478

14. #6  0x0000000001c222bf in btr_estimate_n_rows_in_range_low (index=0x7fff48ec78a0, tuple1=0x7fff48ed3280, mode1=PAGE_CUR_GE, tuple2=0x7fff48ed32e0, mode2=PAGE_CUR_G,

15.     nth_attempt=1) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/btr/btr0cur.cc:5744

16. #7  0x0000000001c22a09 in btr_estimate_n_rows_in_range (index=0x7fff48ec78a0, tuple1=0x7fff48ed3280, mode1=PAGE_CUR_GE, tuple2=0x7fff48ed32e0, mode2=PAGE_CUR_G)

17.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/btr/btr0cur.cc:6044

18. #8  0x00000000019b3e0e in ha_innobase::records_in_range (this=0x7fff48e7e3b0, keynr=1, min_key=0x7fff57a71680, max_key=0x7fff57a716a0)

19.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:13938

20. #9  0x0000000000f6ed5b in handler::multi_range_read_info_const (this=0x7fff48e7e3b0, keyno=1, seq=0x7fff57a71b90, seq_init_param=0x7fff57a71850, n_ranges_arg=0,

21.     bufsz=0x7fff57a71780, flags=0x7fff57a71784, cost=0x7fff57a71d10) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:6440

22. #10 0x0000000000f70662 in DsMrr_impl::dsmrr_info_const (this=0x7fff48e7e820, keyno=1, seq=0x7fff57a71b90, seq_init_param=0x7fff57a71850, n_ranges=0,

23.     bufsz=0x7fff57a71d70, flags=0x7fff57a71d74, cost=0x7fff57a71d10) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:7112

24. #11 0x00000000019be22f in ha_innobase::multi_range_read_info_const (this=0x7fff48e7e3b0, keyno=1, seq=0x7fff57a71b90, seq_init_param=0x7fff57a71850, n_ranges=0,

25.     bufsz=0x7fff57a71d70, flags=0x7fff57a71d74, cost=0x7fff57a71d10) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:21351

26. #12 0x000000000178c9e4 in check_quick_select (param=0x7fff57a71e30, idx=0, index_only=false, tree=0x7fff48e700e0, update_tbl_stats=true, mrr_flags=0x7fff57a71d74,

27.     bufsize=0x7fff57a71d70, cost=0x7fff57a71d10) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/opt_range.cc:10030

28. #13 0x0000000001783305 in get_key_scans_params (param=0x7fff57a71e30, tree=0x7fff48e70058, index_read_must_be_used=false, update_tbl_stats=true,

29.     cost_est=0x7fff57a74190) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/opt_range.cc:5812

30. #14 0x000000000177ce43 in test_quick_select (thd=0x7fff4801f4d0, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false,

31.     interesting_order=st_order::ORDER_NOT_RELEVANT, tab=0x7fff48eacf20, cond=0x7fff48eacd50, needed_reg=0x7fff48eacf60, quick=0x7fff57a744c8)

32.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/opt_range.cc:3066

33. #15 0x000000000158b9bc in get_quick_record_count (thd=0x7fff4801f4d0, tab=0x7fff48eacf20, limit=18446744073709551615)

34.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_optimizer.cc:5942

35. #16 0x000000000158b073 in JOIN::estimate_rowcount (this=0x7fff48eac980) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_optimizer.cc:5689

36. #17 0x00000000015893b5 in JOIN::make_join_plan (this=0x7fff48eac980) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_optimizer.cc:5046

37. #18 0x000000000157d9b7 in JOIN::optimize (this=0x7fff48eac980) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_optimizer.cc:387

38. #19 0x00000000015fab71 in st_select_lex::optimize (this=0x7fff48aa45c0, thd=0x7fff4801f4d0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:1009

39. #20 0x00000000015f9284 in handle_query (thd=0x7fff4801f4d0, lex=0x7fff48021ab0, result=0x7fff48aa5dc8, added_options=0, removed_options=0)

40.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:164

41. #21 0x00000000015ac159 in execute_sqlcom_select (thd=0x7fff4801f4d0, all_tables=0x7fff48aa54b8) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5391

42. #22 0x00000000015a4774 in mysql_execute_command (thd=0x7fff4801f4d0, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:2889

43. #23 0x00000000015ad12a in mysql_parse (thd=0x7fff4801f4d0, parser_state=0x7fff57a76600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836

44. #24 0x00000000015a0fe9 in dispatch_command (thd=0x7fff4801f4d0, com_data=0x7fff57a76d70, command=COM_QUERY)

45.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447

46. #25 0x000000000159fe1a in do_command (thd=0x7fff4801f4d0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010

47. #26 0x00000000016e1d6c in handle_connection (arg=0x6320740) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312

48. ---Type to continue, or q to quit---

49. #27 0x0000000001d723f4 in pfs_spawn_thread (arg=0x6320530) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188

50. #28 0x0000003ca62079d1 in start_thread () from /lib64/libpthread.so.0

51. #29 0x0000003ca5ee8b6d in clone () from /lib64/libc.so.6

到此，關(guān)于“MYSQL INNODB主鍵使用varchar和int的區(qū)別是什么”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！