InnoDB引擎表的主键选型 (转)

【导读】

MySQL采用开放可插入式存储引擎架构，提供类似电源插线板的功能，其后接入的存储引擎就类似电器设备，而我们大家常用的存储以MyISAM和InnoDB为主，早期大家主要使用MyISAM引擎支持业务，随MySQL支持业务范围越来越广，存储的数据对企业越来越重要，尤其PC服务器支持的最大内存越来越大，内存的价格也越来越便宜，逐渐采用InnoDB引擎为主.二种风格迥异的存储引擎，各自内部存储算法和数据操纵实现等都竞相不同，另外InnoDB引擎与其他商业数据库产品存储引擎也不太相同，为此我们必须根据使用的存储引擎特点，设计合理的数据存储结构和数据操纵方式。本文将围绕InnoDB存储引擎的主键设计而展开，告诉大家怎样设计表的主键才是合理的做法。

 

讨论InnoDB引擎表的主键选型的要求之前，我们大家先简单温习下InnoDB引擎表的元数据和索引数据存储结构特点。

InnoDB引擎表的数据和索引都是存储在同一个文件中，InnoDB引擎的页大小默认为16K，且页空间使用率为15/16，为此每页能存储的数据量是有限的。主键和数据的关系是数据存储在簇索引的叶子节点中，接下来我们看下主键和数据的组织结构关系，如图1-1：

图 1-1

对于非簇索引，又是如何存储的呢？通过翻译官方文章，得知：每个非簇索引的叶子节点存储的数据都包含簇索引的值，然后通过簇索引的值，可以查找到对应的元数据，我们继续看一下非簇索引的存储结构，以及与簇索引之间的关系，如图1-2：

图 1-2

紧接着，我们来理清楚InnoDB引擎表的簇索引为哪三类：

l 主键也即是我们文章说的簇索引；

l 若表中无主键，但是存在唯一索引，且字段定义为非空，则作为簇索引；

l 表无主键，也无 非空唯一索引，则内部默认隐含性创建一个长度为6个字节的字段作为簇索引字段；

通过上述探讨和分析，弄清楚了数据存储页的空间值和利用率、簇索引和非簇索引的存储结构，以及簇索引和元数据、簇索引和非簇索引的存储关系，以及簇索引为哪三类，那么我们建议大家一定要为InnoDB引擎表创建一个主键，没必要搞一个唯一性索引且字段定义属性非空，而不创建主键的表结构，我们再分析优秀主键具备的素质：值域范围够用且存储长度越短、值的唯一性和易比较性、数值的有序性增加，三个素质的各自优点，单独详细分析。

l 主键的值域够用且长度越短，将产生三个方面的优势

(1). 相同数据行数的表，数据容量越小，占用磁盘空间越少，为此可以节约物理或逻辑IO和减少内存占用；

(2). 普通索引的长度也将更短，可以减少通过普通索引搜索数据的物理或逻辑IO；

(3). 减少索引数据存储的页块裂变，从而提高页的利用率，以及减少物理IO；

l 主键的唯一性

(1). 主键的值要求必须唯一，且非空；

(2). 主键值每次插入或修改，都必须判断是否有相同值，为此减少索引值需要比对的长度，可以提高性能，或者间接地让其转换成比对数值大小的方式，提高其比对判断的效率；

l 主键值插入的有序性，将产生二个方面的优势

(1). 主键存储的块与块之间是有序的，然后块内有也是有序的，主键值的有序插入，可以减少块内的排序，节约磁盘物理IO；

(2). 主键值的有序性，可以提高数据舒顺序取速度，提高服务器的吞吐量，也可以节约物理IO；

上述讨论的数据存储结构及关系，以及主键字段要求的素质等理论知识之外，我们还需要考虑实际生产环境的业务、架构等综合因素，我们实际生产环境可能会使用四类属性作为主键：

(1). 自增序列；

(2). UUID()函数生成的随机值；

(3). 用户注册的唯一性帐号名称，字符串类型，一般长度为：40个字符；

(4). 基于一套机制生成类似自增的值，比如序列生成器；

那么我们接下来，再分析下这四类属性各自作为表主键的优缺点：

(1). 自增序列：从小到大 或从大到小的顺序模式增加新值；数据类型也利于进行主键值比较；存储空间占用也相对最小，一般设置为：4个字节的INT类型或 8个字节的BIGINT类型；若是想进行数据水平拆分的话，也可以借助设置mysqld实例的2个参数：auto_increment_increment 和 auto_increment_offset；另外，唯一缺点就是自增序列是一个表级别的全局锁，在5.0系列大规模并发写的时候，因锁释放机制的问题容易出现瓶颈，但是5.1系列做了改进，基本上不存在此问题；

(2). UUID()函数：值为随机性+固定部分，其值产生是无序的，且同一台服务器上产生的值相同部分为77.8%；产生的值字符个数为36，按utf8编码计算，占用的存储空间为36个字节；对于数据水平拆分支持，无需特殊设置；

(3). 使用用户注册的帐号名称，字符串类型，其值的产生依赖用户输入，为此数据基本上为无序增加，字符串的长度也是不定的，只能通过前段技术控制最短最大长度值的限制，对水平拆分支持，无需做特殊设置；

(4). 序列生成器的架构，类似自增序列，不过需要借助额外的开发工作量，以及提供一个第三方的服务，可以规避自增序列的字增全局锁的问题，提高并发，对数据水平拆分可以更好地支持；

(5). 双主复制架构的概率性碰到的场景：主服务器的数据执行成功，而没有复制到在线备用服务器时，出问题的概率确实存在，其他类型的做法，也必须人工干涉解决，都无简单且合理的自动化办法，以上四种办法都无法规避；

通过四种属性值作为主键的优缺点分析，以及对比前面我们阐述的主键需要的优秀素质，若是不考虑水平拆分的问题，带来额外设置上的麻烦，则自增序列是最佳的主键字段选择；用户的注册帐号本身要求唯一性且非空的场景下，则可以作为主键字段的选择；若是考虑水平拆分的问题，则采用自增序列生成器的架构，非常易用和可靠的实现方式，产生的值是最佳主键字段的选择；

【结束】

使用什么类型的字段属性作为主键，最关键核心的要考虑存储引擎：如何存储元数据、如何检索元数据、如何维护其内部的索引组织结构，以及我们要实现的业务是什么，最后共同决定我们，如何设计一张用于存储数据的表，以及决定操纵数据的SQL语句如何编写，再结合业务特点就决定了我们的索引如何创建，建议大家多关注InnoDB引擎内部实现原理和机制，可以阅读官方提供的一个文档InnoDB引擎内部实现，以及多分析和关注业务特点。

附录：java自增序列生成器

我们在做数据库设计的时候，一般都会为数据库设计一个主键，来方便查询，更多的时候这个主键是代理主键，也就是说并没有实际意义。所以通常我们会把这个主键设计为自增的方式。比如Mysql中，我们可以设置为：autoIncresement, Oracle中可以利用Sequence..... 但是有些数据库是不支持这种自增的方式的，也就是说：自增必须我们自己通过程序来实现，比如sybase。 那么这种我们通过程序来控制自增长的过程，就叫序列键生成。简单的说：用一张表来记录其它表的主键的当前值，进来通过程序来达到自增的目的。

 

除了数据库不支持自增方式以后，还有另外一种情况会使用到序列键生成，那就是多张表的代理主键的值不能重复时。比如现在有2张表：T1,T2，他们的主键分别是pk1,pk2.如果说我们的业务要求pk1,pk2两列的值不能出现重复时，那么用我们以往的自增方式显然是办不到的，这个时候就需要生成序列键。

 

好了进入正题，我们先建立一张表，SQL语句如下：

 

CREATETABLE `id_gen` (
 `GEN_KEY` varchar(20) NOTNULL,
 `GEN_VALUE` int(11) DEFAULT 0,
PRIMARYKEY (`GEN_KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATETABLE `id_gen` (
 `GEN_KEY` varchar(20) NOTNULL,
 `GEN_VALUE` int(11) DEFAULT 0,
 PRIMARYKEY (`GEN_KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `id_gen` (
  `GEN_KEY` varchar(20) NOT NULL,
  `GEN_VALUE` int(11) DEFAULT 0,
  PRIMARY KEY (`GEN_KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

 

我们要主键的 keyname与它当前的value保存到这个表中，每次我们取值之前，先update一下value，再取出来value。有的人会说：为什么我们每次不先取value再更新列？现在试想一下，如果当你取完值以后，系统被中断了，这时你还没有来得及更新value，那么下次取的到仍就是同一个值，如果上一个值已经被正确使用了，那么就出现了重复值，与我们设计的初衷就不符了。如果先更新，再取值的话，算被中断，顶多是浪费了一个值而已。 从上面的分析，我们不难看出，这个序列键生成器，在整个系统中只需要一个。那么，很快我们就想到了单例模式，那么我们就开始设置我们的生成器。

 

 

 

package com.pattern.id.generator;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

publicclass KeyGenerator {

privatestatic KeyGenerator keyGen = new KeyGenerator();

private KeyGenerator() {
 };

/**
 * 工厂方法
 *
 * @return
 */
publicstatic KeyGenerator getInstance() {
return keyGen;
 }

/**
 * 获取下一个value
 *
 * @return
 */
publicsynchronizedint getNextKey(String keyName) {
return getNextKeyFromDB(keyName);
 }

privateint getNextKeyFromDB(String keyName) {
 Connection conn = null;
try {
 Class.forName("com.mysql.jdbc.Driver");
 conn = DriverManager
 .getConnection("jdbc:mysql://localhost:3306/frameworkdb?user=root&password=123456");
 String updateSql = "update id_gen set GEN_VALUE = GEN_VALUE+1 where GEN_KEY = ? ";
 PreparedStatement upateStmt = conn.prepareStatement(updateSql);
 upateStmt.setString(1, keyName);
 upateStmt.execute();
 String sql = "select GEN_VALUE from id_gen where GEN_KEY = ? ";
 PreparedStatement stmt = conn.prepareStatement(sql);
 stmt.setString(1, keyName);
 ResultSet rs = stmt.executeQuery();
while (rs.next()) {
return rs.getInt(1);
 }

 } catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
 e.printStackTrace();
 } catch (SQLException e) {
// TODO Auto-generated catch block
 e.printStackTrace();
 }finally{
try {
 conn.close();
 } catch (SQLException e) {
// TODO Auto-generated catch block
 e.printStackTrace();
 }
 }
return0;
 }

}

 

 

package com.pattern.id.generator;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

publicclass KeyGenerator {

 privatestatic KeyGenerator keyGen = new KeyGenerator();

 private KeyGenerator() {
 };

 /**
 * 工厂方法
 *
 * @return
 */
 publicstatic KeyGenerator getInstance() {
 return keyGen;
 }

 /**
 * 获取下一个value
 *
 * @return
 */
 publicsynchronizedint getNextKey(String keyName) {
 return getNextKeyFromDB(keyName);
 }

 privateint getNextKeyFromDB(String keyName) {
 Connection conn = null;
 try {
 Class.forName("com.mysql.jdbc.Driver");
 conn = DriverManager
 .getConnection("jdbc:mysql://localhost:3306/frameworkdb?user=root&password=123456");
 String updateSql = "update id_gen set GEN_VALUE = GEN_VALUE+1 where GEN_KEY = ? ";
 PreparedStatement upateStmt = conn.prepareStatement(updateSql);
 upateStmt.setString(1, keyName);
 upateStmt.execute();
 String sql = "select GEN_VALUE from id_gen where GEN_KEY = ? ";
 PreparedStatement stmt = conn.prepareStatement(sql);
 stmt.setString(1, keyName);
 ResultSet rs = stmt.executeQuery();
 while (rs.next()) {
 return rs.getInt(1);
 }

 } catch (ClassNotFoundException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 }finally{
 try {
 conn.close();
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 }
 }
 return0;
 }

}

package com.pattern.id.generator;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class KeyGenerator {

	private static KeyGenerator keyGen = new KeyGenerator();

	private KeyGenerator() {
	};

	/**
	 * 工厂方法
	 * 
	 * @return
	 */
	public static KeyGenerator getInstance() {
		return keyGen;
	}

	/**
	 * 获取下一个value
	 * 
	 * @return
	 */
	public synchronized int getNextKey(String keyName) {
		return getNextKeyFromDB(keyName);
	}

	private int getNextKeyFromDB(String keyName) {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			 conn = DriverManager
					.getConnection("jdbc:mysql://localhost:3306/frameworkdb?user=root&password=123456");
			String updateSql = "update id_gen set GEN_VALUE = GEN_VALUE+1 where GEN_KEY = ? ";
			PreparedStatement upateStmt = conn.prepareStatement(updateSql);
			upateStmt.setString(1, keyName);
			upateStmt.execute();
			String sql = "select GEN_VALUE from id_gen where GEN_KEY = ? ";
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setString(1, keyName);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return 0;
	}

}

 

 

 

 

<p>关于上面的代码，我不想做过多解释，都是JDBC的基础知识，关于JDBC资源，应该一层层来，我这里写示例代码就直接性关闭Connection。现在我们来写个测试类，来测试一下，
看我们这个类是否能正常工作。</p><p>
</p><p></p><pre name="code"class="java">package com.pattern.id.generator;

publicclass ClientTest {

/**
 * @param args
 */
publicstaticvoid main(String[] args) {
 KeyGenerator gen = KeyGenerator.getInstance();
int value = gen.getNextKey("test");
 System.out.println(value);
 }

}

<p>关于上面的代码，我不想做过多解释，都是JDBC的基础知识，关于JDBC资源，应该一层层来，我这里写示例代码就直接性关闭Connection。现在我们来写个测试类，来测试一下，
看我们这个类是否能正常工作。</p><p>
</p><p></p><pre name="code"class="java">package com.pattern.id.generator;

publicclass ClientTest {

 /**
 * @param args
 */
 publicstaticvoid main(String[] args) {
 KeyGenerator gen = KeyGenerator.getInstance();
 int value = gen.getNextKey("test");
 System.out.println(value);
 }

}

<p>关于上面的代码，我不想做过多解释，都是JDBC的基础知识，关于JDBC资源，应该一层层来，我这里写示例代码就直接性关闭Connection。现在我们来写个测试类，来测试一下，
看我们这个类是否能正常工作。</p><p>
</p><p></p><pre name="code" class="java">package com.pattern.id.generator;

public class ClientTest {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		KeyGenerator gen = KeyGenerator.getInstance();
		int value = gen.getNextKey("test");
		System.out.println(value);
	}

}