mysql 字段 主键_MySQL 字符串主键和整型主键分析

背景：

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

测试一：

View Code

root@localhost : test 11:32:17>show create tabletest\G;*************************** 1. row ***************************

Table: testCreate Table: CREATE TABLE`test` (

`uid`char(36) NOT NULL DEFAULT '',

`id`int(11) NOT NULLAUTO_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=utf81 row in set (0.00sec)

root@localhost : test 11:32:24>show create tabletest_bak\G;*************************** 1. row ***************************

Table: test_bakCreate Table: CREATE TABLE`test_bak` (

`uid`char(36) NOT NULL DEFAULT '',

`id`int(11) NOT NULLAUTO_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=utf81 row in set (0.00sec)

root@localhost : test 11:32:29>insert into test(uid,name,status) select uuid(),agencyName,status frominfoSort;

Query OK,639759 rows affected (25.02sec)

Records:639759 Duplicates: 0 Warnings: 0root@localhost : test 11:34:21>insert into test_bak(uid,name,status) select uuid(),agencyName,status frominfoSort;

Query OK,639759 rows affected (20.28sec)

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 635787COLUMNS: uid: DATA_MYSQL DATA_NOT_NULLlen 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 3root page3, appr.key vals 635787, leaf pages 4056, size pages 4078FIELDS: uid DB_TRX_ID DB_ROLL_PTR id name statusINDEX: name idx_id, id 0 1388, fields 1/2, uniq 2, type 0root page4, appr.key vals 638241, leaf pages 1834, size pages 1896FIELDS: id uid/*表中定义的二级索引明明只有id，为什么最右边又会出现uid列呢*/

--------------------------------------

TABLE: name test/test_bak, id 0 719, flags 1, columns 7, indexes 2, appr.rows 619056COLUMNS: uid: DATA_MYSQL DATA_NOT_NULLlen 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 3root page3, appr.key vals 619056, leaf pages 4056, size pages 4070FIELDS: id DB_TRX_ID DB_ROLL_PTR uid name statusINDEX: name idx_id, id 0 1390, fields 1/2, uniq 2, type 0root page4, appr.key vals 638241, leaf pages 1834, size pages 1896FIELDS: 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: 0estimatedstatistics indictionary: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: 0estimatedstatistics indictionary: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: 0estimatedstatistics indictionary: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: 0estimatedstatistics indictionary: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: testACreate Table: CREATE TABLE`testA` (

`uid`char(36) NOT NULL DEFAULT '',

`id`int(11) NOT NULLAUTO_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=utf81 row in set (0.00sec)

root@localhost : test 05:32:42>show create tabletestA_bak\G;*************************** 1. row ***************************

Table: testA_bakCreate Table: CREATE TABLE`testA_bak` (

`uid`char(36) NOT NULL DEFAULT '',

`id`int(11) NOT NULLAUTO_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=utf81 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 601311COLUMNS: uid: DATA_MYSQL DATA_NOT_NULLlen 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 3root page3, appr.key vals 601311, leaf pages 4056, size pages 4078FIELDS: uid DB_TRX_ID DB_ROLL_PTR id name statusINDEX: name idx_id, id 0 1392, fields 1/2, uniq 2, type 0root page4, appr.key vals 638241, leaf pages 1834, size pages 1896FIELDS: id uidINDEX: name idx_name, id 0 1393, fields 1/2, uniq 2, type 0root page5, appr.key vals 1355, leaf pages 3590, size pages 4224FIELDS: name uid--------------------------------------

TABLE: name test/testA_bak, id 0 721, flags 1, columns 7, indexes 3, appr.rows 644406COLUMNS: uid: DATA_MYSQL DATA_NOT_NULLlen 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 3root page3, appr.key vals 644406, leaf pages 4056, size pages 4070FIELDS: id DB_TRX_ID DB_ROLL_PTR uid name statusINDEX: name idx_id, id 0 1395, fields 1/2, uniq 2, type 0root page4, appr.key vals 638484, leaf pages 1838, size pages 1961FIELDS: uid idINDEX: name idx_name, id 0 1396, fields 1/2, uniq 2, type 0root page5, appr.key vals 808, leaf pages 2131, size pages 2412FIELDS: 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.00sec)

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: 0estimatedstatistics indictionary: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: 0estimatedstatistics indictionary: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: 0estimatedstatistics indictionary: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: 0estimatedstatistics indictionary: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: 0estimatedstatistics indictionary: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: 0estimatedstatistics indictionary: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

DELIMITER ;

新建表和数据:

View Code

mysql> show create tablet1\G;*************************** 1. row ***************************

Table: t1Create Table: CREATE TABLE`t1` (

`uid`char(36) NOT NULL DEFAULT '',

`id`int(11) NOT NULLAUTO_INCREMENT,

`name`varchar(255) DEFAULT NULL,

`status`tinyint(4) DEFAULT NULL,PRIMARY KEY(`uid`),KEY`idx_id` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00sec)

mysql> show create tablet2\G;*************************** 1. row ***************************

Table: t2Create Table: CREATE TABLE`t2` (

`uid`char(36) NOT NULL DEFAULT '',

`id`int(11) NOT NULLAUTO_INCREMENT,

`name`varchar(255) DEFAULT NULL,

`status`tinyint(4) DEFAULT NULL,PRIMARY KEY(`id`),KEY`idx_uid` (`uid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00sec)

mysql> insert into t1(uid,name,status) select rand_string(36),name,status fromtest;

Query OK,639759 rows affected (42 min 5.18sec)

Records:639759 Duplicates: 0 Warnings: 0mysql> insert into t2(uid,name,status) select rand_string(36),name,status fromtest;

Query OK,639759 rows affected (4 min 7.30sec)

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 627274COLUMNS: uid: DATA_MYSQL DATA_NOT_NULLlen 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 3root page3, appr.key vals 627274, leaf pages 5735, size pages 6592FIELDS: uid DB_TRX_ID DB_ROLL_PTR id name statusINDEX: name idx_id, id 0 27, fields 1/2, uniq 2, type 0root page4, appr.key vals 638241, leaf pages 1834, size pages 1896FIELDS: id uid--------------------------------------

TABLE: name test/t2, id 0 25, columns 7, indexes 2, appr.rows 654546COLUMNS: uid: DATA_MYSQL DATA_NOT_NULLlen 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 3root page3, appr.key vals 654546, leaf pages 4056, size pages 4070FIELDS: id DB_TRX_ID DB_ROLL_PTR uid name statusINDEX: name idx_uid, id 0 29, fields 1/2, uniq 2, type 0root page4, appr.key vals 638598, leaf pages 2588, size pages 2990FIELDS: uid id

表空间差距大小：

View Code

mysql> select (6592+1896)-(4070+2990);+-------------------------+

| (6592+1896)-(4070+2990) |

+-------------------------+

| 1428 |

+-------------------------+

1 row in set (0.00sec)

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: 0estimatedstatistics indictionary: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: 0estimatedstatistics indictionary: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: 0estimatedstatistics indictionary: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: 0estimatedstatistics indictionary: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 639336COLUMNS: uid: DATA_MYSQL DATA_NOT_NULLlen 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 3root page3, appr.key vals 639336, leaf pages 4056, size pages 4078FIELDS: uid DB_TRX_ID DB_ROLL_PTR id name statusINDEX: name idx_id, id 0 32, fields 1/2, uniq 2, type 0root page4, appr.key vals 601915, leaf pages 2575, size pages 2991FIELDS: id uid--------------------------------------

TABLE: name test/t2, id 0 25, columns 7, indexes 2, appr.rows 634266COLUMNS: uid: DATA_MYSQL DATA_NOT_NULLlen 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 3root page3, appr.key vals 634266, leaf pages 4056, size pages 4070FIELDS: id DB_TRX_ID DB_ROLL_PTR uid name statusINDEX: name idx_uid, id 0 29, fields 1/2, uniq 2, type 0root page4, appr.key vals 574545, leaf pages 2588, size pages 2990FIELDS: uid idtable: test/t1, index: idx_id, space id: 10, root page: 4, zip size: 0estimatedstatistics indictionary: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: 0estimatedstatistics indictionary: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: 0estimatedstatistics indictionary: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: 0estimatedstatistics indictionary: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 NULLAUTO_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 0COLUMNS: uid: DATA_MYSQL DATA_NOT_NULLlen 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 3root page3, appr.key vals 0, leaf pages 1, size pages 1FIELDS: id name DB_TRX_ID DB_ROLL_PTR uid statusINDEX: name idx_name_uid, id 0 35, fields 2/3, uniq 3, type 0root page4, appr.key vals 0, leaf pages 1, size pages 1FIELDS: name uid idINDEX: name idx_uid, id 0 36, fields 1/3, uniq 3, type 0root page5, appr.key vals 0, leaf pages 1, size pages 1FIELDS: uid id name

分析: 不清楚点这里

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

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

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

更多的测试信息: