MySQL 字符串主键和整型主键分析

谁借莪１个温暖的怀抱￠ 2021-06-24 16:00 540阅读 0赞

**背景：**

工作中需要把UUID的主键改成整型自增的主键，改成自增主键之后会提升很多，但是没有测试。

**测试一：**

root@localhost : test 11:32:17>show create table test\G;
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `uid` char(36) NOT NULL DEFAULT '',
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `status` tinyint(4) DEFAULT NULL,
      PRIMARY KEY (`uid`),  /*uid*/
      KEY `idx_id` (`id`)  /*id,uid*/
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    row in set (0.00 sec)
    
    root@localhost : test 11:32:24>show create table test_bak\G;
    *************************** 1. row ***************************
           Table: test_bak
    Create Table: CREATE TABLE `test_bak` (
      `uid` char(36) NOT NULL DEFAULT '',
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `status` tinyint(4) DEFAULT NULL,
      PRIMARY KEY (`id`), /*id*/
      KEY `idx_id` (`uid`) /*uid,id*/
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    row in set (0.00 sec)
    
    root@localhost : test 11:32:29>insert into test(uid,name,status) select uuid(),agencyName,status from infoSort;
    Query OK, 639759 rows affected (25.02 sec)
    Records: 639759  Duplicates: 0  Warnings: 0
    
    root@localhost : test 11:34:21>insert into test_bak(uid,name,status) select uuid(),agencyName,status from infoSort;
    Query OK, 639759 rows affected (20.28 sec)
    Records: 639759  Duplicates: 0  Warnings: 0

表test是UUID为主键的表，test\_bak是自增ID为主键的表。分析：从表大小，以及插入和查询的性能等方面分析。表大小：

-rw-rw---- 1 mysql mysql 8.5K 2012-09-21 11:32 test_bak.frm
    -rw-rw---- 1 mysql mysql 104M 2012-09-21 11:35 test_bak.ibd
    -rw-rw---- 1 mysql mysql 8.5K 2012-09-21 11:30 test.frm
    -rw-rw---- 1 mysql mysql 104M 2012-09-21 11:34 test.ibd

上面查看表的物理文件大小，2张表大小刚好一样，通过innodb\_table\_monitor来查看他们的具体信息：

TABLE: name test/test, id 0 718, flags 1, columns 7, indexes 2, appr.rows 635787
       COLUMNS: uid: DATA_MYSQL DATA_NOT_NULL len 108; id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name: DATA_VARMYSQL len 765; status: DATA_INT DATA_BINARY_TYPE len 1; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
       INDEX: name PRIMARY, id 0 1387, fields 1/6, uniq 1, type 3
       root page 3, appr.key vals 635787, leaf pages 4056, size pages 4078
       FIELDS:  uid DB_TRX_ID DB_ROLL_PTR id name status
       INDEX: name idx_id, id 0 1388, fields 1/2, uniq 2, type 0
       root page 4, appr.key vals 638241, leaf pages 1834, size pages 1896
       FIELDS:  id uid /*表中定义的二级索引明明只有id，为什么最右边又会出现uid列呢*/
    --------------------------------------
       TABLE: name test/test_bak, id 0 719, flags 1, columns 7, indexes 2, appr.rows 619056
       COLUMNS: uid: DATA_MYSQL DATA_NOT_NULL len 108; id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name: DATA_VARMYSQL len 765; status: DATA_INT DATA_BINARY_TYPE len 1; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
       INDEX: name PRIMARY, id 0 1389, fields 1/6, uniq 1, type 3
       root page 3, appr.key vals 619056, leaf pages 4056, size pages 4070
       FIELDS:  id DB_TRX_ID DB_ROLL_PTR uid name status
       INDEX: name idx_id, id 0 1390, fields 1/2, uniq 2, type 0
       root page 4, appr.key vals 638241, leaf pages 1834, size pages 1896
       FIELDS:  uid id /*表中定义的二级索引明明只有uid，为什么最右边又会出现id列呢*/

对于/**/的内容，最后解释。

**分析：**

通过root page这一行的信息得出结果再一次证实了表大小一样。这里可能产生疑问，为什么UUID主键的表和自增ID的表大小一样呢？INNODB的主键是聚集索引，第二索引都包含主键信息，主键越 大，表的第二索引就越大。理论上说UUID当主键，会使得页面更离散，碎片越多，结果应该是test表比test\_bak的表要大才对? 【UUID是有一定的顺序的，不是完全随机】。从上面的信息中很容易看到（FIELDS行）：test和test\_bak表他们的区别只是uid和id调换了位置，其他值都一样。这就表明了INNODB主键值都包含了一样的信息:主键列、事务ID，回滚指针和其他列的信息。故这2张表的主键占用的页大小一样。第2行的FIELDS信息：表明INNODB非主键索引都包含其主键信息。

**再看看各个索引页面利用率情况：**都一致

table: test/test_bak, index: PRIMARY, space id: 489, root page: 3, zip size: 0
      estimated statistics in dictionary:
        key vals: 652518, leaf pages: 4056, size pages: 4070
      real statistics:
         level 2 pages: pages=1, data=70 bytes, data/pages=0%
         level 1 pages: pages=5, data=56784 bytes, data/pages=69%
            leaf pages: recs=639759, pages=4056, data=61231681 bytes, data/pages=92%
    
      table: test/test_bak, index: idx_id, space id: 489, root page: 4, zip size: 0
      estimated statistics in dictionary:
        key vals: 638241, leaf pages: 1834, size pages: 1896
      real statistics:
         level 2 pages: pages=1, data=350 bytes, data/pages=2%
         level 1 pages: pages=7, data=91700 bytes, data/pages=79%
            leaf pages: recs=639759, pages=1834, data=29428914 bytes, data/pages=97%
    --------------------------------------
      table: test/test, index: PRIMARY, space id: 488, root page: 3, zip size: 0
      estimated statistics in dictionary:
        key vals: 631224, leaf pages: 4056, size pages: 4078
      real statistics:
         level 2 pages: pages=1, data=611 bytes, data/pages=3%
         level 1 pages: pages=13, data=190632 bytes, data/pages=89%
            leaf pages: recs=639759, pages=4056, data=61231681 bytes, data/pages=92%
    
      table: test/test, index: idx_id, space id: 488, root page: 4, zip size: 0
      estimated statistics in dictionary:
        key vals: 638241, leaf pages: 1834, size pages: 1896
      real statistics:
         level 2 pages: pages=1, data=350 bytes, data/pages=2%
         level 1 pages: pages=7, data=91700 bytes, data/pages=79%
            leaf pages: recs=639759, pages=1834, data=29428914 bytes, data/pages=97%

通过 show table status like 来查看这2张表大小也很相近，各个索引的页面利用率也是一样。所以对于这种情况，用字符串做主键还是整型ID做主键其实是一样的，具体看需要用那个字段来检索，通过主键来检索只需要访问一次索引列表就可以，而二级索引则需要2次定位（2次IO）。

**总结:：**

一张表里面有2个索引（一个主键，一个非主键），要是条件允许，他们索引互换，即使是字符串，就像例子里的情况一样\[ 给任意一个或则多个字段（字符串或则整型）加主键或则非主键，另一个（字符串或则ID类型）为非主键或则主键\]。它们占用空间的大小都一样。

\+++++++++++++++++++++++++  
上面说明UUID和自增ID可以任意当主键(不考虑其他情况)  
\+++++++++++++++++++++++++

**测试二：**

一般正常的情况，一张表都会大于2个索引的。所以下面开始再对例子中的表再加一个索引:add index idx\_name(name)。

*************************** 1. row ***************************
           Table: testA
    Create Table: CREATE TABLE `testA` (
      `uid` char(36) NOT NULL DEFAULT '',
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `status` tinyint(4) DEFAULT NULL,
      PRIMARY KEY (`uid`),
      KEY `idx_id` (`id`),/*id,uid*/
      KEY `idx_name` (`name`)/*name,uid*/
    ) ENGINE=InnoDB AUTO_INCREMENT=655351 DEFAULT CHARSET=utf8
    row in set (0.00 sec)
    
    root@localhost : test 05:32:42>show create table testA_bak\G;
    *************************** 1. row ***************************
           Table: testA_bak
    Create Table: CREATE TABLE `testA_bak` (
      `uid` char(36) NOT NULL DEFAULT '',
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `status` tinyint(4) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_id` (`uid`),/*uid,id*/
      KEY `idx_name` (`name`)/*name,id*/
    ) ENGINE=InnoDB AUTO_INCREMENT=655351 DEFAULT CHARSET=utf8
    row in set (0.00 sec)

表大小：

-rw-rw---- 1 mysql mysql 8.5K 2012-09-21 11:45 testA_bak.frm
    -rw-rw---- 1 mysql mysql 140M 2012-09-21 11:47 testA_bak.ibd
    -rw-rw---- 1 mysql mysql 8.5K 2012-09-21 11:44 testA.frm
    -rw-rw---- 1 mysql mysql 168M 2012-09-21 11:46 testA.ibd

字符串主键的表变大了！通过innodb\_table\_monitor来查看他们的具体信息：

TABLE: name test/testA, id 0 720, flags 1, columns 7, indexes 3, appr.rows 601311
      COLUMNS: uid: DATA_MYSQL DATA_NOT_NULL len 108; id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name: DATA_VARMYSQL len 765; status: DATA_INT DATA_BINARY_TYPE len 1; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
      INDEX: name PRIMARY, id 0 1391, fields 1/6, uniq 1, type 3
       root page 3, appr.key vals 601311, leaf pages 4056, size pages 4078
       FIELDS:  uid DB_TRX_ID DB_ROLL_PTR id name status
      INDEX: name idx_id, id 0 1392, fields 1/2, uniq 2, type 0
       root page 4, appr.key vals 638241, leaf pages 1834, size pages 1896
       FIELDS:  id uid
      INDEX: name idx_name, id 0 1393, fields 1/2, uniq 2, type 0
       root page 5, appr.key vals 1355, leaf pages 3590, size pages 4224
       FIELDS:  name uid
    --------------------------------------
    TABLE: name test/testA_bak, id 0 721, flags 1, columns 7, indexes 3, appr.rows 644406
      COLUMNS: uid: DATA_MYSQL DATA_NOT_NULL len 108; id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name: DATA_VARMYSQL len 765; status: DATA_INT DATA_BINARY_TYPE len 1; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
      INDEX: name PRIMARY, id 0 1394, fields 1/6, uniq 1, type 3
       root page 3, appr.key vals 644406, leaf pages 4056, size pages 4070
       FIELDS:  id DB_TRX_ID DB_ROLL_PTR uid name status
      INDEX: name idx_id, id 0 1395, fields 1/2, uniq 2, type 0
       root page 4, appr.key vals 638484, leaf pages 1838, size pages 1961
       FIELDS:  uid id
      INDEX: name idx_name, id 0 1396, fields 1/2, uniq 2, type 0
       root page 5, appr.key vals 808, leaf pages 2131, size pages 2412
       FIELDS:  name id

通过root page 行信息得到：前面2个索引信息大小都几乎一致，新加的name索引差距最大。计算差距多少：

root@localhost : test 05:32:47>select (4078+1896+4224)-(4070+1961+2412);
    +-----------------------------------+
    | (4078+1896+4224)-(4070+1961+2412) |
    +-----------------------------------+
    |                              1755 |
    +-----------------------------------+
    row in set (0.00 sec)
    
    root@localhost : test 05:45:12>select 1755*16/1024;
    +--------------+
    | 1755*16/1024 |
    +--------------+
    |      27.4219 |
    +--------------+
    row in set (0.00 sec)

刚好和表的物理文件一致。

页面利用率：字符串的比整型的要高一点。

table: test/testA, index: PRIMARY, space id: 490, root page: 3, zip size: 0
      estimated statistics in dictionary:
        key vals: 667728, leaf pages: 4056, size pages: 4078
      real statistics:
         level 2 pages: pages=1, data=611 bytes, data/pages=3%
         level 1 pages: pages=13, data=190632 bytes, data/pages=89%
            leaf pages: recs=639759, pages=4056, data=61231681 bytes, data/pages=92%
    
      table: test/testA, index: idx_id, space id: 490, root page: 4, zip size: 0
      estimated statistics in dictionary:
        key vals: 638241, leaf pages: 1834, size pages: 1896
      real statistics:
         level 2 pages: pages=1, data=350 bytes, data/pages=2%
         level 1 pages: pages=7, data=91700 bytes, data/pages=79%
            leaf pages: recs=639759, pages=1834, data=29428914 bytes, data/pages=97%
    
      table: test/testA, index: idx_name, space id: 490, root page: 5, zip size: 0
      estimated statistics in dictionary:
        key vals: 1355, leaf pages: 3590, size pages: 4224
      real statistics:
         level 2 pages: pages=1, data=2796 bytes, data/pages=17%
         level 1 pages: pages=34, data=295652 bytes, data/pages=53%
            leaf pages: recs=639759, pages=3590, data=49716019 bytes, data/pages=84%
    
      table: test/testA_bak, index: PRIMARY, space id: 491, root page: 3, zip size: 0
      estimated statistics in dictionary:
        key vals: 667728, leaf pages: 4056, size pages: 4070
      real statistics:
         level 2 pages: pages=1, data=70 bytes, data/pages=0%
         level 1 pages: pages=5, data=56784 bytes, data/pages=69%
            leaf pages: recs=639759, pages=4056, data=61231681 bytes, data/pages=92%
    
      table: test/testA_bak, index: idx_id, space id: 491, root page: 4, zip size: 0
      estimated statistics in dictionary:
        key vals: 638484, leaf pages: 1838, size pages: 1961
      real statistics:
         level 2 pages: pages=1, data=400 bytes, data/pages=2%
         level 1 pages: pages=8, data=91900 bytes, data/pages=70%
            leaf pages: recs=639759, pages=1838, data=29428914 bytes, data/pages=97%
    
      table: test/testA_bak, index: idx_name, space id: 491, root page: 5, zip size: 0
      estimated statistics in dictionary:
        key vals: 4803, leaf pages: 2131, size pages: 2412
      real statistics:
         level 2 pages: pages=1, data=501 bytes, data/pages=3%
         level 1 pages: pages=11, data=105730 bytes, data/pages=58%
            leaf pages: recs=639759, pages=2131, data=28603972 bytes, data/pages=81%

**总结:**

**      **INNODB表是索引组织的表，主键是聚集索引，非主键索引都包含主键信息。所以主键越小，会让二级索引表空间都减少很多，也能加快写入操作。内存可以缓存更多的数据。

**测试三：**(随即字符串的测试)

网上找到一个随即字符串函数:

set global log_bin_trust_function_creators =1;
    DELIMITER $$  
    CREATE  FUNCTION `rand_string`(n int)  RETURNS varchar(255)  
        BEGIN  
          DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';  
          DECLARE return_str varchar(255) DEFAULT '';  
      
          DECLARE i INT DEFAULT 0;  
      
          WHILE i < n DO  
            SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));  
            SET i = i +1;  
          END WHILE;  
          RETURN return_str;  
       END$$  
    DELIMITER ;

新建表和数据:

mysql> show create table t1\G;
    *************************** 1. row ***************************
           Table: t1
    Create Table: CREATE TABLE `t1` (
      `uid` char(36) NOT NULL DEFAULT '',
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `status` tinyint(4) DEFAULT NULL,
      PRIMARY KEY (`uid`),
      KEY `idx_id` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    row in set (0.00 sec)
    mysql> show create table t2\G;
    *************************** 1. row ***************************
           Table: t2
    Create Table: CREATE TABLE `t2` (
      `uid` char(36) NOT NULL DEFAULT '',
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `status` tinyint(4) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_uid` (`uid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    row in set (0.00 sec)
    
    mysql> insert into t1(uid,name,status) select rand_string(36),name,status from test;
    Query OK, 639759 rows affected (42 min 5.18 sec)
    Records: 639759  Duplicates: 0  Warnings: 0
    
    mysql> insert into t2(uid,name,status) select rand_string(36),name,status from test;
    Query OK, 639759 rows affected (4 min 7.30 sec)
    Records: 639759  Duplicates: 0  Warnings: 0

查看其物理大小:

-rw-rw---- 1 mysql mysql 8.5K 2012-09-22 01:51 t1.frm
    -rw-rw---- 1 mysql mysql 144M 2012-09-22 02:34 t1.ibd
    -rw-rw---- 1 mysql mysql 8.5K 2012-09-22 01:51 t2.frm
    -rw-rw---- 1 mysql mysql 120M 2012-09-22 02:38 t2.ibd

根据innodb\_table\_monitor 信息查看:

--------------------------------------
    TABLE: name test/t1, id 0 24, columns 7, indexes 2, appr.rows 627274
      COLUMNS: uid: DATA_MYSQL DATA_NOT_NULL len 108; id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name: DATA_VARMYSQL len 765; status: DATA_INT DATA_BINARY_TYPE len 1; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; 
      INDEX: name PRIMARY, id 0 26, fields 1/6, uniq 1, type 3
       root page 3, appr.key vals 627274, leaf pages 5735, size pages 6592
       FIELDS:  uid DB_TRX_ID DB_ROLL_PTR id name status
      INDEX: name idx_id, id 0 27, fields 1/2, uniq 2, type 0
       root page 4, appr.key vals 638241, leaf pages 1834, size pages 1896
       FIELDS:  id uid
    --------------------------------------
    TABLE: name test/t2, id 0 25, columns 7, indexes 2, appr.rows 654546
      COLUMNS: uid: DATA_MYSQL DATA_NOT_NULL len 108; id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name: DATA_VARMYSQL len 765; status: DATA_INT DATA_BINARY_TYPE len 1; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; 
      INDEX: name PRIMARY, id 0 28, fields 1/6, uniq 1, type 3
       root page 3, appr.key vals 654546, leaf pages 4056, size pages 4070
       FIELDS:  id DB_TRX_ID DB_ROLL_PTR uid name status
      INDEX: name idx_uid, id 0 29, fields 1/2, uniq 2, type 0
       root page 4, appr.key vals 638598, leaf pages 2588, size pages 2990
       FIELDS:  uid id

表空间差距大小：

mysql> select (6592+1896)-(4070+2990);
    +-------------------------+
    | (6592+1896)-(4070+2990) |
    +-------------------------+
    |                    1428 |
    +-------------------------+
    row in set (0.00 sec)
    
    mysql> select 1428*16/1024;
    +--------------+
    | 1428*16/1024 |
    +--------------+
    |      22.3125 |
    +--------------+
    row in set (0.04 sec)

和物理空间大小非常相近。

继续查看他的索引页的利用率：

table: test/t1, index: PRIMARY, space id: 7, root page: 3, zip size: 0
      estimated statistics in dictionary:
        key vals: 682474, leaf pages: 5735, size pages: 6592
      real statistics:
         level 2 pages: pages=1, data=1457 bytes, data/pages=8%
         level 1 pages: pages=31, data=269545 bytes, data/pages=53%
            leaf pages: recs=639759, pages=5735, data=61231681 bytes, data/pages=65%
    
      table: test/t1, index: idx_id, space id: 7, root page: 4, zip size: 0
      estimated statistics in dictionary:
        key vals: 638241, leaf pages: 1834, size pages: 1896
      real statistics:
         level 2 pages: pages=1, data=350 bytes, data/pages=2%
         level 1 pages: pages=7, data=91700 bytes, data/pages=79%
            leaf pages: recs=639759, pages=1834, data=29428914 bytes, data/pages=97%
    ---        
      table: test/t2, index: PRIMARY, space id: 8, root page: 3, zip size: 0
      estimated statistics in dictionary:
        key vals: 642378, leaf pages: 4056, size pages: 4070
      real statistics:
         level 2 pages: pages=1, data=70 bytes, data/pages=0%
         level 1 pages: pages=5, data=56784 bytes, data/pages=69%
            leaf pages: recs=639759, pages=4056, data=61231681 bytes, data/pages=92%
    
      table: test/t2, index: idx_uid, space id: 8, root page: 4, zip size: 0
      estimated statistics in dictionary:
        key vals: 606895, leaf pages: 2588, size pages: 2990
      real statistics:
         level 2 pages: pages=1, data=650 bytes, data/pages=3%
         level 1 pages: pages=13, data=129400 bytes, data/pages=60%
            leaf pages: recs=639759, pages=2588, data=29428914 bytes, data/pages=69%

通过leaf pages: 行信息看到，他们相当的索引差距还是比较大的。

从上面的这些信息中看到主键和非主键索引大小都不一样，和第一个测试冲突？原因是什么呢？

解释前需要理解INNODB聚集索引的概念以及随机字符串的信息.

**分析：**

上面插入测试数据的时候，随即字符串主键的表插入效率相比有自增主键的表效率慢很多；之所以这样是因为：插入的值会被随机的放入索引中，导致分页，磁盘随机访问，产生聚集索引碎片。  
innodb的索引以B-tree的形式存到各个叶点上（包括data）。索引叶点页的大小默认为16K（会存2行记录），当有索引插入叶节点时，该叶节点至少会保留1/16的空闲空间，用于将来该叶节点的索引更新或是插入。对于顺序写入的索引，索引叶节点可以达到15/16就写满再起另一个页。如果是随机的索引写入，会让叶节点只达到1/2到15/16。当叶节点填充在1/2以下或是被删除到1/2下时，该叶节点可以被继续写入数据继，要是当前页不”够大“则会导致页的分裂，这样就导致存一样的数据需要更多的页。信息中数据都是61231681，而随机主键需要5735，自增主键则只需要4056。

虽然自增主键表的第二索引（随机字段在最左）比随机字符串表的第二索引要大（顺序字段在最左），但是相比整个条件下。主键的随机性能远大于第二索引。

既然是碎片，那就OPTIMIZE TABLE一下，结果惊奇的发现t1的各个信息都和t2的一样。

-rw-rw---- 1 mysql mysql 8.5K 2012-09-22 03:05 t1.frm
    -rw-rw---- 1 mysql mysql 120M 2012-09-22 03:08 t1.ibd
    -rw-rw---- 1 mysql mysql 8.5K 2012-09-22 01:51 t2.frm
    -rw-rw---- 1 mysql mysql 120M 2012-09-22 02:38 t2.ibd
    
    TABLE: name test/t1, id 0 27, columns 7, indexes 2, appr.rows 639336
      COLUMNS: uid: DATA_MYSQL DATA_NOT_NULL len 108; id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name: DATA_VARMYSQL len 765; status: DATA_INT DATA_BINARY_TYPE len 1; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
      INDEX: name PRIMARY, id 0 31, fields 1/6, uniq 1, type 3
       root page 3, appr.key vals 639336, leaf pages 4056, size pages 4078
       FIELDS:  uid DB_TRX_ID DB_ROLL_PTR id name status
      INDEX: name idx_id, id 0 32, fields 1/2, uniq 2, type 0
       root page 4, appr.key vals 601915, leaf pages 2575, size pages 2991
       FIELDS:  id uid
    --------------------------------------
    TABLE: name test/t2, id 0 25, columns 7, indexes 2, appr.rows 634266
      COLUMNS: uid: DATA_MYSQL DATA_NOT_NULL len 108; id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name: DATA_VARMYSQL len 765; status: DATA_INT DATA_BINARY_TYPE len 1; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
      INDEX: name PRIMARY, id 0 28, fields 1/6, uniq 1, type 3
       root page 3, appr.key vals 634266, leaf pages 4056, size pages 4070
       FIELDS:  id DB_TRX_ID DB_ROLL_PTR uid name status
      INDEX: name idx_uid, id 0 29, fields 1/2, uniq 2, type 0
       root page 4, appr.key vals 574545, leaf pages 2588, size pages 2990
       FIELDS:  uid id
       
     
       table: test/t1, index: idx_id, space id: 10, root page: 4, zip size: 0
      estimated statistics in dictionary:
        key vals: 751587, leaf pages: 2575, size pages: 2991
      real statistics:
         level 2 pages: pages=1, data=700 bytes, data/pages=4%
         level 1 pages: pages=14, data=128750 bytes, data/pages=56%
            leaf pages: recs=639759, pages=2575, data=29428914 bytes, data/pages=69%
    
      table: test/t2, index: PRIMARY, space id: 8, root page: 3, zip size: 0
      estimated statistics in dictionary:
        key vals: 619056, leaf pages: 4056, size pages: 4070
      real statistics:
         level 2 pages: pages=1, data=70 bytes, data/pages=0%
         level 1 pages: pages=5, data=56784 bytes, data/pages=69%
            leaf pages: recs=639759, pages=4056, data=61231681 bytes, data/pages=92%
    
      table: test/t2, index: idx_uid, space id: 8, root page: 4, zip size: 0
      estimated statistics in dictionary:
        key vals: 657684, leaf pages: 2588, size pages: 2990
      real statistics:
         level 2 pages: pages=1, data=650 bytes, data/pages=3%
         level 1 pages: pages=13, data=129400 bytes, data/pages=60%
            leaf pages: recs=639759, pages=2588, data=29428914 bytes, data/pages=69%
    
      table: test/test, index: PRIMARY, space id: 4, root page: 3, zip size: 0
      estimated statistics in dictionary:
        key vals: 627675, leaf pages: 4056, size pages: 4078
      real statistics:
         level 2 pages: pages=1, data=611 bytes, data/pages=3%
         level 1 pages: pages=13, data=190632 bytes, data/pages=89%
            leaf pages: recs=639759, pages=4056, data=61231681 bytes, data/pages=92%

**总结:**

**     **随机字符串的主键数据的写入会有很多碎片产生，很多逻辑上相近的页其实分布在磁盘和内存的各个地方。所以在这类表中需要经常OPTIMIZE，不过最好尽量避免这个类型的主键。

**测试四：(二级索引如何保存主键信息)**

**     **上面讲的都是单列索引，要是多列主键和索引会怎么样？

新建立表：

CREATE TABLE `abc` (
      `uid` char(36) NOT NULL DEFAULT '',
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL DEFAULT '',
      `status` tinyint(4) DEFAULT NULL,
      PRIMARY KEY (`id`,`name`),
      KEY `idx_name_uid` (`name`,`uid`),
      KEY `idx_uid` (`uid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

具体信息：

TABLE: name test/abc, id 0 29, columns 7, indexes 3, appr.rows 0
      COLUMNS: uid: DATA_MYSQL DATA_NOT_NULL len 108; id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name: DATA_VARMYSQL DATA_NOT_NULL len 765; status: DATA_INT DATA_BINARY_TYPE len 1; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
      INDEX: name PRIMARY, id 0 34, fields 2/6, uniq 2, type 3
       root page 3, appr.key vals 0, leaf pages 1, size pages 1
       FIELDS:  id name DB_TRX_ID DB_ROLL_PTR uid status
      INDEX: name idx_name_uid, id 0 35, fields 2/3, uniq 3, type 0
       root page 4, appr.key vals 0, leaf pages 1, size pages 1
       FIELDS:  name uid id 
      INDEX: name idx_uid, id 0 36, fields 1/3, uniq 3, type 0
       root page 5, appr.key vals 0, leaf pages 1, size pages 1
       FIELDS:  uid id name

**分析:** 不清楚点[这里][Link 1]

第一行 **FIELDS **列是主键信息**id name**，包含了主键列，事务ID，回滚指针和其他列。

第二行 **FIELDS **列是**idx\_name\_uid** 组合索引，包含了主键的一个列（name字段）。需要把主键信息补全，不需要再包含name字段了，所以他存的信息是 name uid** id**。

第二行 **FIELDS **列是**idx\_uid**组合索引，没有包含任何主键信息。需要把整个多列主键全部追加进来。索引他存的信息是 uid** id name**

更多的测试信息:

[http://dinglin.iteye.com/][http_dinglin.iteye.com]

转自 [https://www.cnblogs.com/zhoujinyi/archive/2012/09/21/2697522.html][https_www.cnblogs.com_zhoujinyi_archive_2012_09_21_2697522.html]

[Link 1]: http://www.penglixun.com/tech/database/will_innodb_store_pk_in_index.html
[http_dinglin.iteye.com]: http://dinglin.iteye.com/
[https_www.cnblogs.com_zhoujinyi_archive_2012_09_21_2697522.html]: https://www.cnblogs.com/zhoujinyi/archive/2012/09/21/2697522.html