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

发布时间：2020-12-12 23:55:41 所属栏目：百科来源：网络整理

导读：最近在工作中，遇到到了sqlite主键类型选择的问题，于是就做个测试程序来测试sqlite采用UUID主键和int型自增主键性能对比。(本人时间有限，没有做多次测试，希望有人能帮着多测试一下)。未经本人同意严禁转载,转载请表明出处：http://blog.csdn.net/allenzh

测试手机三星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 allenzhangfan@gmail.com  *  */ 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 = 22222; 	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," 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"," 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,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() {  	}  }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

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

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

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

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

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