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

背景：

工作中需要把UUID的主键改成整型自增的主键，虽然知道INNODB的一些特性，改成自增主键之后会提升很多，但是没有测试。在测试过程中<左兴宇>给了很多帮助，非常感谢。

测试一：

View Code

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
1 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
1 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为主键的表。分析：从表大小，以及插入和查询的性能等方面分析。表大小：

View Code

-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非主键索引都包含其主键信息。

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

View Code

  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)。

View Code

*************************** 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
1 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
1 row in set (0.00 sec)

表大小：

View Code

-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索引差距最大。计算差距多少：

View Code

root@localhost : test 05:32:47>select (4078+1896+4224)-(4070+1961+2412);
+-----------------------------------+
| (4078+1896+4224)-(4070+1961+2412) |
+-----------------------------------+
|                              1755 |
+-----------------------------------+
1 row in set (0.00 sec)

root@localhost : test 05:45:12>select 1755*16/1024;
+--------------+
| 1755*16/1024 |
+--------------+
|      27.4219 |
+--------------+
1 row in set (0.00 sec)

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

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

View Code

  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 ;

新建表和数据:

View Code

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
1 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
1 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

查看其物理大小:

View Code

-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

表空间差距大小：

View Code

mysql> select (6592+1896)-(4070+2990);
+-------------------------+
| (6592+1896)-(4070+2990) |
+-------------------------+
|                    1428 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select 1428*16/1024;
+--------------+
| 1428*16/1024 |
+--------------+
|      22.3125 |
+--------------+
1 row in set (0.04 sec)

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

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

View Code

  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的一样。

View Code

-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，不过最好尽量避免这个类型的主键。

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

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

新建立表：

View Code

 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

分析: 不清楚点这里

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

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

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

更多的测试信息:

http://dinglin.iteye.com/

posted @ 2012-09-21 22:03 jyzhou 阅读(21317) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

DBA's Record

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

公告