Oracle主鍵選擇對插入的影響

壹頁書發表於2014-01-01
Oracle
主鍵一般使用序列或者UUID
使用UUID主要優點是分散式環境下，主鍵不會重複；缺點是效能相對較低。
序列則相反，他在分散式環境下會重複，但是效能較高。

實驗分別測試序列，UUID，使用RAW型別的UUID，測試100W資料插入的時間。
建立實驗所需的表
  1. drop table test_seq purge;
  2. drop table test_uuid purge;
  3. drop table test_uuid_raw purge;

  5. --測試序列
  6. create table test_seq(t_id number(8) primary key );
  7. --測試UUID
  8. create table test_uuid(t_id varchar2(32) primary key);
  9. --測試UUID RAW
  10. create table test_uuid_raw(t_id raw(16) primary key);
  11.  
  12. drop sequence seq;

  14. create sequence seq
  15. increment by 1
  16. start with 1
  17. cache 300;
  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.PreparedStatement;
  4. import java.sql.SQLException;
  5. import java.util.UUID;

  7. import org.apache.commons.codec.DecoderException;
  8. import org.apache.commons.codec.binary.Hex;

  10. public class PrimaryKeyTest {
  11.     private static int _1W = 10000;
  12.     private static int COUNT = _1W * 100;
  13.     private static String URL = "jdbc:oracle:thin:127.0.0.1:1521:orcl";
  14.     private static String USERNAME = "edmond";
  15.     private static String PWD = "edmond";

  17.     private static void insertSeq() throws SQLException {
  18.         System.out.println("測試序列:");
  19.         long start = System.currentTimeMillis();
  20.         long end;
  21.         Connection con = DriverManager.getConnection(URL, USERNAME, PWD);
  22.         con.setAutoCommit(false);
  23.         PreparedStatement ps = con.prepareStatement("insert into test_seq(t_id) values(seq.nextval)");
  24.         for (int i = 0; i < COUNT; i++) {
  25.             if (i == _1W) {
  26.                 end = System.currentTimeMillis();
  27.                 System.out.println("1W:" + (end - start));
  28.             } else if (i == _1W * 10) {
  29.                 end = System.currentTimeMillis();
  30.                 System.out.println("10W:" + (end - start));
  31.             } else if (i == _1W * 20) {
  32.                 end = System.currentTimeMillis();
  33.                 System.out.println("20W:" + (end - start));
  34.             } else if (i == _1W * 50) {
  35.                 end = System.currentTimeMillis();
  36.                 System.out.println("50W:" + (end - start));
  37.             } else if (i == _1W * 80) {
  38.                 end = System.currentTimeMillis();
  39.                 System.out.println("80W:" + (end - start));
  40.             }

  42.             ps.addBatch();
  43.             if (i % (10 * _1W) == 0) {
  44.                 ps.executeBatch();
  45.             }
  46.         }
  47.         ps.executeBatch();
  48.         con.commit();
  49.         ps.close();
  50.         con.close();

  52.         end = System.currentTimeMillis();
  53.         System.out.println("100W:" + (end - start));

  55.     }

  57.     private static void insertUUID() throws SQLException {
  58.         System.out.println("測試UUID:");
  59.         long start = System.currentTimeMillis();
  60.         long end;
  61.         Connection con = DriverManager.getConnection(URL, USERNAME, PWD);
  62.         con.setAutoCommit(false);
  63.         PreparedStatement ps = con.prepareStatement("insert into test_uuid(t_id) values(?)");
  64.         for (int i = 0; i < COUNT; i++) {
  65.             if (i == _1W) {
  66.                 end = System.currentTimeMillis();
  67.                 System.out.println("1W:" + (end - start));
  68.             } else if (i == _1W * 10) {
  69.                 end = System.currentTimeMillis();
  70.                 System.out.println("10W:" + (end - start));
  71.             } else if (i == _1W * 20) {
  72.                 end = System.currentTimeMillis();
  73.                 System.out.println("20W:" + (end - start));
  74.             } else if (i == _1W * 50) {
  75.                 end = System.currentTimeMillis();
  76.                 System.out.println("50W:" + (end - start));
  77.             } else if (i == _1W * 80) {
  78.                 end = System.currentTimeMillis();
  79.                 System.out.println("80W:" + (end - start));
  80.             }

  82.             String id = UUID.randomUUID().toString().replaceAll("-", "");
  83.             ps.setString(1, id);
  84.             ps.addBatch();

  86.             if (i % (10 * _1W) == 0) {
  87.                 ps.executeBatch();
  88.             }
  89.         }
  90.         ps.executeBatch();
  91.         con.commit();
  92.         ps.close();
  93.         con.close();

  95.         end = System.currentTimeMillis();
  96.         System.out.println("100W:" + (end - start));

  98.     }

  100.     private static void insertUUIDRaw() throws SQLException, DecoderException {
  101.         System.out.println("測試UUID RAW:");
  102.         long start = System.currentTimeMillis();
  103.         long end;
  104.         Connection con = DriverManager.getConnection(URL, USERNAME, PWD);
  105.         con.setAutoCommit(false);
  106.         PreparedStatement ps = con.prepareStatement("insert into test_uuid_raw(t_id) values(?)");
  107.         for (int i = 0; i < COUNT; i++) {
  108.             if (i == _1W) {
  109.                 end = System.currentTimeMillis();
  110.                 System.out.println("1W:" + (end - start));
  111.             } else if (i == _1W * 10) {
  112.                 end = System.currentTimeMillis();
  113.                 System.out.println("10W:" + (end - start));
  114.             } else if (i == _1W * 20) {
  115.                 end = System.currentTimeMillis();
  116.                 System.out.println("20W:" + (end - start));
  117.             } else if (i == _1W * 50) {
  118.                 end = System.currentTimeMillis();
  119.                 System.out.println("50W:" + (end - start));
  120.             } else if (i == _1W * 80) {
  121.                 end = System.currentTimeMillis();
  122.                 System.out.println("80W:" + (end - start));
  123.             }

  125.             byte[] data = Hex.decodeHex(UUID.randomUUID().toString().replaceAll("-", "").toCharArray());
  126.             ps.setBytes(1, data);
  127.             ps.addBatch();

  129.             if (i % (10 * _1W) == 0) {
  130.                 ps.executeBatch();
  131.             }
  132.         }
  133.         ps.executeBatch();
  134.         con.commit();
  135.         ps.close();
  136.         con.close();

  138.         end = System.currentTimeMillis();
  139.         System.out.println("100W:" + (end - start));
  140.     }

  142.     public static void main(String[] args) throws ClassNotFoundException, SQLException, DecoderException {
  143.         Class.forName("oracle.jdbc.OracleDriver");
  144.         insertSeq();
  145.         insertUUID();
  146.         insertUUIDRaw();
  147.     }
  148. }
測試結果如下：
1w 10w 20w 50w 80w 100w
序列 939 949 1584 3847 7732 12010
UUID Varchar2 2196 2915 14591 107528 861084 3051716
UUID RAW 607 1237 9302 37817 169935 986043

不同的主鍵方案，不僅對索引維護有效能影響，同時也對索引的使用造成影響。(UUID作為主鍵索引的value佔用空間比序列大很多)。





同樣的查詢需求，都使用了主鍵索引，但是採用UUID作為主鍵，他的物理讀和一致性讀都遠遠超過了採用序列的情況。
所以如果沒有分散式環境的需求(例如層級資料上報),可以儘量採用序列作為主鍵。
單純就插入效能而言，序列>UUID RAW>UUID Varchar2

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1066263/，如需轉載，請註明出處，否則將追究法律責任。

相關文章