sqlite采用UUID主键和int型自增主键性能对比测试

浏览数：34 / 时间：2015年06月12日

最近在工作中，遇到到了sqlite主键类型选择的问题，于是就做个测试程序来测试sqlite采用UUID主键和int型自增主键性能对比。(本人时间有限，没有做多次测试，希望有人能帮着多测试一下)。

未经本人同意严禁转载,转载请表明出处：http://blog.csdn.net/allenzhangfan/article/details/18809807

1.sqlite采用UUID主键的优点和缺点。

优点：安卓端sqlite表的主键和服务器端数据库表的主键可以保持一致，不用做复杂的业务处理，开发效率高；数据安全性较好，用户无法从id估算该表的数据量。
缺点：UUID字符串长度太长，加大网络数据传输数据量。

2.sqlite采用int型自增主键的优点和缺点。

优点：int型主键字符串长度短，网络传输数据量不大。
缺点：安卓端sqlite表的主键与服务器端数据库表的主键不可能一致，需要做复杂的业务处理，开发效率低；做表关联需要insert后返回主键值的时候需要多一次select max（id）的操作。

3.测试环境

测试手机 三星Galaxy S3（i9300）
安卓版本 Android 4.3 （CM10.2）
软件环境 已ROOT，杀掉全部后台服务

测试用例sqlite表结构
UUID主键：
create table sysUserUUID (id TEXT PRIMARY KEY , uName TEXT )


int自增主键：
create table sysUserInt (id INTEGER PRIMARY KEY AUTOINCREMENT, uName TEXT )

转换json工具包：gson-2.2.4.jar

4.测试结果

操作 UUID主键 int自增主键
插入数据耗时 100条 1582ms 1583ms
500条 10308ms 9496ms
读取数据耗时 100条 19ms 25ms
500条 58ms 59ms
json字符串大小 100条 6.14kb 2.62kb
500条 31.14kb 13.95kb


5. 结论

根据测试数据我们可以得出以下结论:
1.sqlite只是简单的文件型数据库，没有对任何类型的主键进行优化，所以采用UUID和int自增主键没有本质上的性能差距，而我们测试中UUID比int主键稍微慢一点的原因我认为是uuid字符串太长。
2.用json传输数据时，每100条uuid的数据会比int型的大3.4kb左右，考虑到本测试中int型主键都是从1开始的，而实际应用中可能大于10000，故而他们的差距应该比3.4kb要小；如果再考虑做表关联时需要传输复合主键，那么int型的大小应该再大一些。考虑到一般读取数据不会超过100条，这个差距即使在2G的网络环境下对响应时间的影响也不到0.5秒。故而数据大小的区别可以忽略。
未经本人同意严禁转载,转载请表明出处：http://blog.csdn.net/allenzhangfan/article/details/18809807

6. 附：主要测试类

/**
 * 未经本人同意严禁转载,转载请表明出处：http://blog.csdn.net/allenzhangfan/article/details/18809807
 * @author [email protected]
 *
 */
public class MainActivity extends Activity {

	TextView tvUUIDTitle;
	TextView tvIntTitle;
	EditText etTestIntCount;
	EditText etTestUUIDCount;
	Button btnStartTestUUID;
	Button btnStartTestInt;
	TextView tvUUIDRs;
	TextView tvIntRs;
	ProgressBar pbInProgress;
	Button btnStartLoadTestUUID;
	Button btnStartLoadTestInt;

	int intTestCnt;
	int UUIDTestCnt;
	long intTestCost;
	long UUIDTestCost;

	File UUIDFile;
	File intFile;

	DatabaseUtil db;
	static final int TEST_INT_MSG = 11110;
	static final int LOAD_INT_MSG = 41110;
	static final int TEST_UUID_MSG = 11111;
	static final int LOAD_UUID_MSG = 41111;
	static final int DONE_CNT_MSG = 21111;

	static String APP_DIR = "test_sqlite";

	int doneCnt;

	List<SysUserUUID> sysUserUUIDs;
	List<SysUserInt> sysUserInts;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		db = new DatabaseUtil(this);
		db = db.open();
		db.executeSql("drop table if exists sysUserInt");
		db.executeSql("drop table if exists sysUserUUID");
		db.executeSql("create table sysUserInt (id INTEGER PRIMARY KEY AUTOINCREMENT, uName TEXT )");
		db.executeSql("create table sysUserUUID (id TEXT PRIMARY KEY , uName TEXT )");
		initView();
	}

	Handler handler = new Handler() {
		public void handleMessage(android.os.Message msg) {
			pbInProgress.setVisibility(View.GONE);
			switch (msg.what) {
			case DONE_CNT_MSG:

				break;

			case TEST_INT_MSG:
				String intRs = "插入耗时 " + intTestCost + "ms";
				tvIntRs.setText(intRs);
				btnStartLoadTestInt.setEnabled(true);
				break;
			case TEST_UUID_MSG:
				String uuidRs = "插入耗时 " + UUIDTestCost + "ms";
				tvUUIDRs.setText(uuidRs);
				btnStartLoadTestUUID.setEnabled(true);
				break;

			case LOAD_INT_MSG:
				String rs1 = tvIntRs.getText().toString() + ", 读取耗时 "
						+ intLoadCost + "ms, json文件大小"+FileUtils.getFormatSize(intFile.length());
				tvIntRs.setText(rs1);
				break;
			case LOAD_UUID_MSG:
				String rs = tvUUIDRs.getText().toString() + ", 读取耗时 "
						+ UUIDLoadCost + "ms, json文件大小"+FileUtils.getFormatSize(UUIDFile.length());
				tvUUIDRs.setText(rs);

				break;

			default:
				break;
			}
		};
	};

	void initView() {
		tvUUIDTitle = (TextView) findViewById(R.id.tvUUIDTitle);
		tvIntTitle = (TextView) findViewById(R.id.tvIntTitle);
		etTestIntCount = (EditText) findViewById(R.id.etTestIntCount);
		etTestUUIDCount = (EditText) findViewById(R.id.etTestUUIDCount);
		tvUUIDRs = (TextView) findViewById(R.id.tvUUIDRs);
		tvIntRs = (TextView) findViewById(R.id.tvIntRs);
		btnStartTestUUID = (Button) findViewById(R.id.btnStartTestUUID);
		btnStartTestInt = (Button) findViewById(R.id.btnStartTestInt);
		btnStartLoadTestUUID = (Button) findViewById(R.id.btnStartLoadTestUUID);
		btnStartLoadTestInt = (Button) findViewById(R.id.btnStartLoadTestInt);
		pbInProgress = (ProgressBar) findViewById(R.id.pbInProgress);

		btnStartTestInt.setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View v) {
				pbInProgress.setVisibility(View.VISIBLE);
				intTestCnt = Integer.parseInt(etTestIntCount.getText()
						.toString());
				new Thread() {
					public void run() {
						try {
							long start = new Date().getTime();
							for (int i = 0; i < intTestCnt; i++) {
								StringBuilder sb = new StringBuilder(
										"insert into sysUserInt (uName) values (‘")
										.append("name").append(i).append("‘)");
								db.executeSql(sb.toString());
							}
							long end = new Date().getTime();
							intTestCost = end - start;
							handler.sendEmptyMessage(TEST_INT_MSG);
						} catch (Exception ex) {
							ex.printStackTrace();
						}
					};
				}.start();
			}
		});

		btnStartTestUUID.setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View v) {
				pbInProgress.setVisibility(View.VISIBLE);
				UUIDTestCnt = Integer.parseInt(etTestUUIDCount.getText()
						.toString());
				new Thread() {
					public void run() {
						try {
							long start = new Date().getTime();
							for (int i = 0; i < UUIDTestCnt; i++) {

								StringBuilder sb = new StringBuilder(
										"insert into sysUserUUID (id,uName) values (‘")
										.append(UUID.randomUUID())
										.append("‘,‘").append("name").append(i)
										.append("‘)");
								db.executeSql(sb.toString());
							}
							long end = new Date().getTime();
							UUIDTestCost = end - start;
							handler.sendEmptyMessage(TEST_UUID_MSG);
						} catch (Exception ex) {
							ex.printStackTrace();
						}
					};
				}.start();

			}
		});

		btnStartLoadTestUUID.setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View arg0) {
				pbInProgress.setVisibility(View.VISIBLE);
				UUIDTestCnt = Integer.parseInt(etTestUUIDCount.getText()
						.toString());
				new Thread() {
					public void run() {
						try {
							long start = new Date().getTime();
							String sql = "select * from sysUserUUID order by id limit "
									+ UUIDTestCnt + " offset 0 ";
							Cursor cr = db.mDb.query("sysUserUUID",
									new String[] { "id", "uName" }, null, null,
									null, null, " id limit " + UUIDTestCnt
											+ " offset 0 ");
							sysUserUUIDs = new ArrayList<SysUserUUID>();
							while (cr.moveToNext()) {
								SysUserUUID sysUserUUID = new SysUserUUID();
								sysUserUUID.setId(cr.getString(0));
								sysUserUUID.setuName(cr.getString(1));
								sysUserUUIDs.add(sysUserUUID);
							}

							if (Environment.getExternalStorageState().equals(
									Environment.MEDIA_MOUNTED)) {
								String dirStr = Environment
										.getExternalStorageDirectory()
										.getPath()
										+ File.separator + APP_DIR;
								UUIDFile = new File(dirStr + File.separator
										+ "uuid.json");
								if (!UUIDFile.exists()) {
									File dir = new File(UUIDFile.getParent());
									dir.mkdirs();
									UUIDFile.createNewFile();
								}
								Gson gson = new Gson();
								FileUtils.writeFile(UUIDFile,
										gson.toJson(sysUserUUIDs).toString());
							} else {
								Toast.makeText(MainActivity.this, "sd卡错误", 1)
										.show();
							}
							long end = new Date().getTime();
							UUIDLoadCost = end - start;
							handler.sendEmptyMessage(LOAD_UUID_MSG);
						} catch (Exception e) {
							e.printStackTrace();
						}
					};
				}.start();
			}
		});
		
		btnStartLoadTestInt.setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View arg0) {
				pbInProgress.setVisibility(View.VISIBLE);
				intTestCnt = Integer.parseInt(etTestIntCount.getText()
						.toString());
				new Thread() {
					public void run() {
						try {
							long start = new Date().getTime();
							String sql = "select * from sysUserInt order by id limit "
									+ intTestCnt + " offset 0 ";
							Cursor cr = db.mDb.query("sysUserInt",
									new String[] { "id", "uName" }, null, null,
									null, null, " id limit " + intTestCnt
											+ " offset 0 ");
							sysUserInts = new ArrayList<SysUserInt>();
							while (cr.moveToNext()) {
								SysUserInt sysUserInt = new SysUserInt();
								sysUserInt.setId(cr.getInt(0));
								sysUserInt.setuName(cr.getString(1));
								sysUserInts.add(sysUserInt);
							}

							if (Environment.getExternalStorageState().equals(
									Environment.MEDIA_MOUNTED)) {
								String dirStr = Environment
										.getExternalStorageDirectory()
										.getPath()
										+ File.separator + APP_DIR;
								intFile = new File(dirStr + File.separator
										+ "int.json");
								if (!intFile.exists()) {
									File dir = new File(intFile.getParent());
									dir.mkdirs();
									intFile.createNewFile();
								}
								Gson gson = new Gson();
								FileUtils.writeFile(intFile,
										gson.toJson(sysUserInts).toString());
							} else {
								Toast.makeText(MainActivity.this, "sd卡错误", 1)
										.show();
							}
							long end = new Date().getTime();
							intLoadCost = end - start;
							handler.sendEmptyMessage(LOAD_INT_MSG);
						} catch (Exception e) {
							e.printStackTrace();
						}
					};
				}.start();
			}
		});
	}

	long UUIDLoadCost;
	long intLoadCost;

	void testIntId() {

	}

	void testUUIDId() {

	}

}













          

            

              
              郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
            

          


          


          

            标签： 
            android
            
            安卓
            
            int
            
            ie
            
            text
            
            string
            
            file
            
            class
            
            ip
            
            r
            
            ff
            
            android
            
            安卓
            
            int
            
            ie
            
            text
            
            string
            
            file
            
            class
            
            ip
            
            r
            
            ff
            
            
            
          


          

            

              
相关文章

              
    
                
              

            

            

              
随机文章

              
            

          



          

            

              
您可能还喜欢

              
    
                
              

            


            

              
您可能还喜欢