快捷導(dǎo)航

MYSQL大表加索引的實現(xiàn)

更新時間：2023年05月29日 10:22:35 作者：千云

本文主要介紹了MYSQL大表加索引的實現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

起因是這樣的，有一張表存在慢sql，查詢耗時最多達(dá)到12s，定位問題后發(fā)現(xiàn)是由于全表掃描導(dǎo)致，需要對字段增加索引，但是表的數(shù)據(jù)量600多萬有些大，網(wǎng)上很多都說對大表增加索引可能會導(dǎo)致鎖表，查閱了一些資料，可以說網(wǎng)上說了很多，但是都很籠統(tǒng)，聽別人說不如自己去驗證，于是開啟了驗證之旅

首先新建一張表test_page1

CREATE TABLE `test_page1`  (
  `id` int(11)  NULL,
  `username` int(252) not  NULL,
  `password` int(252)  NULL,
  `create_time` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci not NULL ,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`create_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1000001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

第二步，像表中干他個600w條數(shù)據(jù)這一步網(wǎng)上有很多教程，有通過sql直接在mysql客戶端插入數(shù)據(jù)，還有通過代碼插入數(shù)據(jù)的，最初為了方便，我是想再mysql客戶端直接通過存儲過程插入數(shù)據(jù)，但是插入速度十分感人

果斷放棄，畢竟600w條，不想等到猴年馬月，于是就選擇用代碼的方式插入，其實就是多費(fèi)了一些力氣而已，上代碼，開整

public class Connect {
    //    導(dǎo)入驅(qū)動jar包或添加Maven依賴（這里使用的是Maven，Maven依賴代碼附在文末）
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //  獲取數(shù)據(jù)庫連接對象
    public static Connection getConn() {
        Connection conn = null;
        try {
            //  rewriteBatchedStatements=true,一次插入多條數(shù)據(jù)，只插入一次
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/xxx?rewriteBatchedStatements=true", "root", "xxx");
        } 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();
                }
            }
        }
    }
}

public class InsertData {
    private static ThreadPoolExecutor getDefaultThreadPool() {
        ThreadPoolExecutor result = new ThreadPoolExecutor(0, 1000, 1, TimeUnit.SECONDS, new SynchronousQueue<>());
        result.setThreadFactory(new ThreadFactory() {
            @Override
            public Thread newThread(Runnable r) {
                return new Thread(r, "deterministic runner thread");
            }
        });
        return result;
    }
    /*  因為數(shù)據(jù)庫的處理速度是非常驚人的 單次吞吐量很大 執(zhí)行效率極高
    addBatch()把若干sql語句裝載到一起，然后一次送到數(shù)據(jù)庫執(zhí)行，執(zhí)行需要很短的時間
    而preparedStatement.executeUpdate() 是一條一條發(fā)往數(shù)據(jù)庫執(zhí)行的 時間都消耗在數(shù)據(jù)庫連接的傳輸上面*/
    public static void main(String[] args) {
            for (int j = 0; j < 100; j++) {
                long start = System.currentTimeMillis();    //  獲取系統(tǒng)當(dāng)前時間，方法開始執(zhí)行前記錄
                Connection conn = Connect.getConn();        //  調(diào)用剛剛寫好的用于獲取連接數(shù)據(jù)庫對象的靜態(tài)工具類
                String sql = "insert into test_page1 values(null,?,?,?,NOW())";  //  要執(zhí)行的sql語句
                PreparedStatement ps = null;
                getDefaultThreadPool().execute(() -> {
                    try {
                    PreparedStatement finalPs = conn.prepareStatement(sql);
                        //  不斷產(chǎn)生sql
                        for (int i = 0; i < 20000; i++) {
                            finalPs.setString(1, Math.ceil(Math.random() * 1000000) + "");
                            finalPs.setString(2, Math.ceil(Math.random() * 1000000) + "");
                            finalPs.setString(3, UUID.randomUUID().toString());  //  UUID該類用于隨機(jī)生成一串不會重復(fù)的字符串
                            finalPs.addBatch();  //  將一組參數(shù)添加到此 PreparedStatement 對象的批處理命令中。
                        }
                        int[] ints = new int[0];//   將一批命令提交給數(shù)據(jù)庫來執(zhí)行，如果全部命令執(zhí)行成功，則返回更新計數(shù)組成的數(shù)組。
                        ints = finalPs.executeBatch();
                        //  如果數(shù)組長度不為0，則說明sql語句成功執(zhí)行，即數(shù)據(jù)添加成功！
                        if (ints.length > 0) {
                            System.out.println("數(shù)據(jù)添加成功??！");
                        }
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }finally {
                        Connect.closeAll(conn, ps);  //  調(diào)用剛剛寫好的靜態(tài)工具類釋放資源
                    }
                  });
                long end = System.currentTimeMillis();  //  再次獲取系統(tǒng)時間
                System.out.println("所用時長:" + (end - start) / 1000 + "秒");  //  兩個時間相減即為方法執(zhí)行所用時長
            }
    }
}

代碼之所以快，很大的原因是由與代碼開啟了多線程，異步插入，但在實際執(zhí)行過程中，也會出現(xiàn)問題，比如把插入的數(shù)據(jù)量搞太大導(dǎo)致了OOM，這個可以修改本地的JVM，另一種就是同時插入太多，數(shù)據(jù)庫連接不夠了，導(dǎo)致報錯，但這都不是重點，因為我們的重點是大表加索引。代碼執(zhí)行后20分鐘內(nèi)，插入了600w條數(shù)據(jù)。

這時候就開始我們的驗證表演了。

首先，說一下網(wǎng)上描述的大表加索引會出現(xiàn)的問題

如果在執(zhí)行事務(wù)的時候，如果存在目標(biāo)表的慢sql，這時對目標(biāo)表增加索引，會導(dǎo)致目標(biāo)表被鎖，進(jìn)入Waiting for table metadata lock狀態(tài)，進(jìn)入Waiting for table metadata lock狀態(tài)后不能讀也不能寫
加索引屬于DDL操作，DDL操作執(zhí)行的時候，會對表加鎖

然后開始我的嘗試先對表加個索引,用時15.19s

alter table test_page1 add index create_time_index(create_time)

然后我們開啟事務(wù)，并對該表執(zhí)行個慢查詢，并對表新建一個索引

BEGIN;
select * from test_page1 where username = 852;

alter table test_page1 add index create_time_index(create_time)

這個慢查詢有8s，足夠出現(xiàn)問題了，很有信心

然而，并沒有出現(xiàn)期望的結(jié)果，涼涼，難道網(wǎng)上說的都是假的，本身不存在這種情況，苦思之下，似乎找到問題我是通過dbveaer來執(zhí)行的sql，同事執(zhí)行兩個sql是在兩個tab頁上執(zhí)行，會不會是雖然在dbveaer的兩個tab頁同時執(zhí)行，但是dbveaer還是一個一個排隊執(zhí)行的sql呢？我想大概率是這樣

我又通過dbeaver新建一個數(shù)據(jù)庫連接，讓開啟事務(wù)，并對該表執(zhí)行個慢查詢和對表增加索引在兩個連接執(zhí)行，這時執(zhí)行show processlist命令，終于復(fù)現(xiàn)了

加索引命令的進(jìn)程進(jìn)入了Waiting for table metadata lock狀態(tài)網(wǎng)上說Waiting for table metadata lock狀態(tài)后不能讀也不能寫，是不是這樣呢？，來執(zhí)行下查詢，暢通無阻，所以說網(wǎng)上是錯誤的，是可以讀的，那能不能寫呢，我們執(zhí)行下sql

insert into test_page1(id,username,password,create_time,update_time) values(null,1,2,'6144423733',NOW());

報錯了，死鎖了Deadlock found when trying to get lock; try restarting transaction，這就驗證了無法進(jìn)行寫操作

那Waiting for table metadata lock狀態(tài)會持續(xù)到什么時候呢，在驗證過程中，發(fā)現(xiàn)了兩種方式第一種，事務(wù)提交后，鎖狀態(tài)取消第二種，這種比較神奇，就是剛剛操作過的，對表進(jìn)行插入操作，這個時候會報錯，但是報錯后，mysql會自動殺掉事務(wù)進(jìn)程并解鎖（這真的很神奇），但是事實就是這樣，很糟心。

還有另外一個點要驗證就是加索引屬于DDL操作，DDL操作執(zhí)行的時候，會對表加鎖，之前我理解錯了，以為加鎖是表鎖，會鎖表的數(shù)據(jù)，但是執(zhí)行ddl操作時是不會組織數(shù)據(jù)的寫入的，但是另一個連接去執(zhí)行DDL操作會進(jìn)入等待狀態(tài)，這就是多，DDL操作的確會加鎖，但是他鎖的不是數(shù)據(jù)而是表結(jié)構(gòu)。

經(jīng)過一番蠻長的論證，終于驗證了什么情況下加索引會鎖表，為什么有時候加索引時間會很長，加字段時間會很長，所以，大家加索引最好選擇選擇在一個業(yè)務(wù)低峰期加，另外，要注意優(yōu)化系統(tǒng)，減少系統(tǒng)中慢sql的出現(xiàn)，這樣會降低鎖表的可能性。

另外如果表被鎖住，處于Waiting for table metadata lock狀態(tài)，這時候我們也可以通過殺掉線程id的方式來解鎖，執(zhí)行show processlist命令，找到線程id，執(zhí)行kill +id，也能完成解鎖。