软件开发培训班 >> 编程开发 >> MySQL


MySQL数据库10秒内插入百万条数据的实现

棣栧厛鎴戜滑鎬濊�冧竴涓棶棰橈細

瑕佹彃鍏ュ姝ゅ簽澶х殑鏁版嵁鍒版暟鎹簱锛屾甯告儏鍐典竴瀹氫細棰戠箒鍦拌繘琛岃闂紝浠�涔堟牱鐨勬満鍣ㄨ澶囬兘鍚冧笉娑堛�傞偅涔堝浣曢伩鍏嶉绻佽闂暟鎹簱锛岃兘鍚﹀仛鍒颁竴娆¤闂紝鍐嶆墽琛屽憿锛?/p>

Java鍏跺疄宸茬粡缁欎簡鎴戜滑绛旀銆?/p>

杩欓噷灏辫鐢ㄥ埌涓や釜鍏抽敭瀵硅薄锛?code>Statement銆?code>PrepareStatement

鎴戜滑鏉ョ湅涓�涓嬩簩鑰呯殑鐗规�э細

瑕佺敤鍒扮殑BaseDao宸ュ叿绫?(jar鍖?/ Maven渚濊禆) (Maven渚濊禆浠ｇ爜闄勫湪鏂囨湯)(灏佽浠ヤ究浜庝娇鐢?

娉細锛堥噸鐐癸級rewriteBatchedStatements=true锛屼竴娆℃彃鍏ュ鏉℃暟鎹紝鍙彃鍏ヤ竴娆★紒锛?/p> 

public class BaseDao {  //  闈欐�佸伐鍏风被锛岀敤浜庡垱寤烘暟鎹簱杩炴帴瀵硅薄鍜岄噴鏀捐祫婧愶紝鏂逛究璋冪敤
    //    瀵煎叆椹卞姩jar鍖呮垨娣诲姞Maven渚濊禆锛堣繖閲屼娇鐢ㄧ殑鏄疢aven锛孧aven渚濊禆浠ｇ爜闄勫湪鏂囨湯锛?
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
 
    //  鑾峰彇鏁版嵁搴撹繛鎺ュ璞?
    public static Connection getConn() {
        Connection conn = null;
        try {
            //  rewriteBatchedStatements=true,涓�娆℃彃鍏ュ鏉℃暟鎹紝鍙彃鍏ヤ竴娆?
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/million-test?rewriteBatchedStatements=true", "root", "qwerdf");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }
 
    //  閲婃斁璧勬簮
    public static void closeAll(AutoCloseable... autoCloseables) {
        for (AutoCloseable autoCloseable : autoCloseables) {
            if (autoCloseable != null) {
                try {
                    autoCloseable.close();
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
}

鎺ヤ笅鏉ヤ笂鍏抽敭浠ｇ爜鍙婃敞閲婏細

/*  鍥犱负鏁版嵁搴撶殑澶勭悊閫熷害鏄潪甯告儕浜虹殑 鍗曟鍚炲悙閲忓緢澶?鎵ц鏁堢巼鏋侀珮
    addBatch()鎶婅嫢骞瞫ql璇彞瑁呰浇鍒颁竴璧凤紝鐒跺悗涓�娆￠�佸埌鏁版嵁搴撴墽琛岋紝鎵ц闇�瑕佸緢鐭殑鏃堕棿
    鑰宲reparedStatement.executeUpdate() 鏄竴鏉′竴鏉″彂寰�鏁版嵁搴撴墽琛岀殑 鏃堕棿閮芥秷鑰楀湪鏁版嵁搴撹繛鎺ョ殑浼犺緭涓婇潰*/
public static void main(String[] args) {
    long start = System.currentTimeMillis();    //  鑾峰彇绯荤粺褰撳墠鏃堕棿锛屾柟娉曞紑濮嬫墽琛屽墠璁板綍
    Connection conn = BaseDao.getConn();        //  璋冪敤鍒氬垰鍐欏ソ鐨勭敤浜庤幏鍙栬繛鎺ユ暟鎹簱瀵硅薄鐨勯潤鎬佸伐鍏风被
    String sql = "insert into mymilliontest values(null,?,?,?,NOW())";  //  瑕佹墽琛岀殑sql璇彞
    PreparedStatement ps = null;
    try {
        ps = conn.prepareStatement(sql);    //  鑾峰彇PreparedStatement瀵硅薄
        //  涓嶆柇浜х敓sql
        for (int i = 0; i < 1000000; i++) {
            ps.setString(1, Math.ceil(Math.random() * 1000000) + "");
            ps.setString(2, Math.ceil(Math.random() * 1000000) + "");
            ps.setString(3, UUID.randomUUID().toString());  //  UUID璇ョ被鐢ㄤ簬闅忔満鐢熸垚涓�涓蹭笉浼氶噸澶嶇殑瀛楃涓?
            ps.addBatch();  //  灏嗕竴缁勫弬鏁版坊鍔犲埌姝?PreparedStatement 瀵硅薄鐨勬壒澶勭悊鍛戒护涓�?
        }
        int[] ints = ps.executeBatch();//   灏嗕竴鎵瑰懡浠ゆ彁浜ょ粰鏁版嵁搴撴潵鎵ц锛屽鏋滃叏閮ㄥ懡浠ゆ墽琛屾垚鍔燂紝鍒欒繑鍥炴洿鏂拌鏁扮粍鎴愮殑鏁扮粍銆?
        //  濡傛灉鏁扮粍闀垮害涓嶄负0锛屽垯璇存槑sql璇彞鎴愬姛鎵ц锛屽嵆鐧句竾鏉℃暟鎹坊鍔犳垚鍔燂紒
        if (ints.length > 0) {
            System.out.println("宸叉垚鍔熸坊鍔犱竴鐧句竾鏉℃暟鎹紒锛?);
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        BaseDao.closeAll(conn, ps);  //  璋冪敤鍒氬垰鍐欏ソ鐨勯潤鎬佸伐鍏风被閲婃斁璧勬簮
    }
    long end = System.currentTimeMillis();  //  鍐嶆鑾峰彇绯荤粺鏃堕棿
    System.out.println("鎵�鐢ㄦ椂闀?" + (end - start) / 1000 + "绉?);  //  涓や釜鏃堕棿鐩稿噺鍗充负鏂规硶鎵ц鎵�鐢ㄦ椂闀?
}

鏈�鍚庢垜浠繍琛岀湅涓�涓嬫晥鏋滐細

鍢垮樋锛岃繖閲屾椂闀胯秴杩囦簡10绉掞紝璁惧宸偣鎰忔�濓紝甯屾湜鐞嗚В鍝垀

<!--杩炴帴鏁版嵁搴撴墍鐢ㄥ埌鐨刴ysql-connector-java渚濊禆-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.27</version>
</dependency>

PS : 娣讳笂绾跨▼鍚庝細鏇村揩锛屽湪鍚庣画鐨勬枃绔犱腑浼氫綔绀轰緥銆?/p>

鍒版杩欑瘒鍏充簬MySQL鏁版嵁搴?0绉掑唴鎻掑叆鐧句竾鏉℃暟鎹殑瀹炵幇鐨勬枃绔犲氨浠嬬粛鍒拌繖浜?鏇村鐩稿叧MySQL 鎻掑叆鐧句竾鏉℃暟鎹?鍐呭璇锋悳绱㈣剼鏈箣瀹朵互鍓嶇殑鏂囩珷鎴栫户缁祻瑙堜笅闈㈢殑鐩稿叧鏂囩珷甯屾湜澶у浠ュ悗澶氬鏀寔鑴氭湰涔嬪锛?/p>