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

作者：孤风一剑发布：2014-01-27 13:04 栏目：数据库开发点击：1,499次抢沙发

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

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主键：

[sql]

create table sysUserUUID (id TEXT PRIMARY KEY , uName TEXT )

int自增主键：

[sql]

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
json字符串大小	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秒。故而数据大小的区别可以忽略。

6. 附：主要测试类

[java]

package com.zf.uuidtest;
import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
import javax.crypto.interfaces.PBEKey;
import com.example.uuidtest.R;
import com.google.gson.Gson;
import android.os.Bundle;
import android.os.Environment;
import android.os.Handler;
import android.app.Activity;
import android.app.ProgressDialog;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ProgressBar;
import android.widget.TextView;
import android.widget.Toast;
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() {
}
}