MySQL之SQL最佳化實戰記錄

作者：小祝特煩惱 

來自：https://my.oschina.net/xiaozhutefannao/blog/2243432

背景

本次SQL最佳化是針對javaweb中的表格查詢做的。

部分網路架構圖

業務簡單說明

N個機臺將業務資料傳送至伺服器，伺服器程式將資料入庫至MySQL資料庫。伺服器中的javaweb程式將資料展示到網頁上供使用者檢視。

原資料庫設計

• windows單機主從分離

• 已分表分庫，按年分庫，按天分表

• 每張表大概20w左右的資料

原查詢效率

3天資料查詢70-80s

標的

3-5s

業務缺陷

無法使用sql分頁，只能用java做分頁。

問題排查

前臺慢 or 後臺慢

• 如果你配置了druid，可在druid頁面中直接檢視sql執行時間和uri請求時間

• 在後臺程式碼中用System.currentTimeMillis計算時間差。

結論 ： 後臺慢，且查詢sql慢

sql有什麼問題

• sql拼接過長，達到了3000行，有的甚至到8000行，大多都是union all的操作，且有不必要的巢狀查詢和查詢了不必要的欄位

• 利用explain檢視執行計劃，where條件中除時間外只有一個欄位用到了索引

備註 ： 因最佳化完了，之前的sql實在找不到了，這裡只能YY了。

查詢最佳化

去除不必要的欄位

效果沒那麼明顯

去除不必要的巢狀查詢

效果沒那麼明顯

分解sql

• 將union all的操作分解，例如(一個union all的sql也很長)

select aa from bb_2018_10_01 left join ... on .. left join .. on .. where ..
union all
select aa from bb_2018_10_02 left join ... on .. left join .. on .. where ..
union all
select aa from bb_2018_10_03 left join ... on .. left join .. on .. where ..
union all
select aa from bb_2018_10_04 left join ... on .. left join .. on .. where ..

將如上sql分解成若干個sql去執行，最終彙總資料，最後快了20s左右。

select aa from bb_2018_10_01 left join ... on .. left join .. on .. where ..

將分解的sql非同步執行

利用java非同步程式設計的操作，將分解的sql非同步執行並最終彙總資料。這裡用到了CountDownLatch和ExecutorService，示例程式碼如下：

    // 獲取時間段所有天數
        List<String> days = MyDateUtils.getDays(requestParams.getStartTime(), requestParams.getEndTime());
        // 天數長度
        int length = days.size();
        // 初始化合併集合，並指定大小，防止陣列越界
        List list = Lists.newArrayListWithCapacity(length);
        // 初始化執行緒池
        ExecutorService pool = Executors.newFixedThreadPool(length);
        // 初始化計數器
        CountDownLatch latch = new CountDownLatch(length);
        // 查詢每天的時間併合並
        for (String day : days) {
            Map<String, Object> param = Maps.newHashMap();
            // param 組裝查詢條件

            pool.submit(new Runnable() {
                @Override
                public void run() {
                    try {
                        // mybatis查詢sql
                        // 將結果彙總
                        list.addAll(查詢結果);
                    } catch (Exception e) {
                        logger.error("getTime異常", e);
                    } finally {
                        latch.countDown();
                    }
                }
            });
        }


        try {
            // 等待所有查詢結束
            latch.await();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        // list為彙總集合
        // 如果有必要，可以組裝下你想要的業務資料，計算什麼的，如果沒有就沒了

結果又快了20-30s

最佳化MySQL配置

以下是我的配置示例。加了skip-name-resolve,快了4-5s。其他配置自行斷定

   [client]
port=3306
[mysql]
no-beep
default-character-set=utf8
[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin 
slave-skip-errors=all #跳過所有錯誤
skip-name-resolve

port=3306
datadir="D:/mysql-slave/data"
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

log-output=FILE
general-log=0
general_log_file="WINDOWS-8E8V2OD.log"
slow-query-log=1
slow_query_log_file="WINDOWS-8E8V2OD-slow.log"
long_query_time=10

# Binary Logging.
# log-bin

# Error Logging.
log-error="WINDOWS-8E8V2OD.err"


# 整個資料庫最大連線（使用者）數
max_connections=1000
# 每個客戶端連線最大的錯誤允許數量
max_connect_errors=100
# 表描述符快取大小，可減少檔案開啟/關閉次數
table_open_cache=2000
# 服務所能處理的請求包的最大大小以及服務所能處理的最大的請求大小(當與大的BLOB欄位一起工作時相當必要)  
# 每個連線獨立的大小.大小動態增加
max_allowed_packet=64M
# 在排序發生時由每個執行緒分配
sort_buffer_size=8M
# 當全聯合發生時,在每個執行緒中分配 
join_buffer_size=8M
# cache中保留多少執行緒用於重用
thread_cache_size=128
# 此允許應用程式給予執行緒系統一個提示在同一時間給予渴望被執行的執行緒的數量.
thread_concurrency=64
# 查詢快取
query_cache_size=128M
# 只有小於此設定值的結果才會被緩衝  
# 此設定用來保護查詢緩衝,防止一個極大的結果集將其他所有的查詢結果都改寫
query_cache_limit=2M
# InnoDB使用一個緩衝池來儲存索引和原始資料
# 這裡你設定越大,你在存取表裡面資料時所需要的磁碟I/O越少.  
# 在一個獨立使用的資料庫伺服器上,你可以設定這個變數到伺服器物理記憶體大小的80%  
# 不要設定過大,否則,由於物理記憶體的競爭可能導致作業系統的換頁顛簸.  
innodb_buffer_pool_size=1G
# 用來同步IO操作的IO執行緒的數量
# 此值在Unix下被硬編碼為4,但是在Windows磁碟I/O可能在一個大數值下表現的更好. 
innodb_read_io_threads=16
innodb_write_io_threads=16
# 在InnoDb核心內的允許執行緒數量.  
# 最優值依賴於應用程式,硬體以及作業系統的排程方式.  
# 過高的值可能導致執行緒的互斥顛簸.
innodb_thread_concurrency=9

# 0代表日誌只大約每秒寫入日誌檔案並且日誌檔案掃清到磁碟.  
# 1 ,InnoDB會在每次提交後掃清(fsync)事務日誌到磁碟上
# 2代表日誌寫入日誌檔案在每次提交後,但是日誌檔案只有大約每秒才會掃清到磁碟上
innodb_flush_log_at_trx_commit=2
# 用來緩衝日誌資料的緩衝區的大小.  
innodb_log_buffer_size=16M
# 在日誌組中每個日誌檔案的大小.  
innodb_log_file_size=48M
# 在日誌組中的檔案總數. 
innodb_log_files_in_group=3
# 在被回滾前,一個InnoDB的事務應該等待一個鎖被批准多久.  
# InnoDB在其擁有的鎖表中自動檢測事務死鎖並且回滾事務.  
# 如果你使用 LOCK TABLES 指令, 或者在同樣事務中使用除了InnoDB以外的其他事務安全的儲存引擎  
# 那麼一個死鎖可能發生而InnoDB無法註意到.  
# 這種情況下這個timeout值對於解決這種問題就非常有幫助. 
innodb_lock_wait_timeout=30
# 開啟定時
event_scheduler=ON

根據業務，再加上篩選條件

快4-5s

將where條件中除時間條件外的欄位建立聯合索引

效果沒那麼明顯

將where條件中索引條件使用inner join的方式去關聯

針對這條，我自身覺得很詫異。原sql,b為索引

select aa from bb_2018_10_02 left join ... on .. left join .. on .. where b = 'xxx'

應該之前有union all，union all是一個一個的執行，最後彙總的結果。修改為

select aa from bb_2018_10_02 left join ... on .. left join .. on .. inner join
(
    select 'xxx1' as b2
    union all
    select 'xxx2' as b2
    union all
    select 'xxx3' as b2
    union all
    select 'xxx3' as b2
) t on b = t.b2

結果快了3-4s

效能瓶頸

根據以上操作，3天查詢效率已經達到了8s左右，再也快不了了。檢視mysql的cpu使用率和記憶體使用率都不高，到底為什麼查這麼慢了，3天最多才60w資料，關聯的也都是一些字典表，不至於如此。繼續根據網上提供的資料，一系列騷操作，基本沒用，沒轍。

環境對比

因分析過sql最佳化已經ok了，試想是不是磁碟讀寫問題。將最佳化過的程式，分別部署於不同的現場環境。一個有ssd，一個沒有ssd。發現查詢效率懸殊。用軟體檢測過發現ssd讀寫速度在700-800M/s,普通機械硬碟讀寫在70-80M/s。

最佳化結果及結論

• 最佳化結果：達到預期。

• 最佳化結論：sql最佳化不僅僅是對sql本身的最佳化，還取決於本身硬體條件，其他應用的影響，外加自身程式碼的最佳化。

小結

最佳化的過程是自身的一個歷練和考驗，珍惜這種機會，不做只寫業務程式碼的程式員。希望以上可以有助於你的思考，不足之處望指正。如轉載，請標明作者。

---

●編號430，輸入編號直達本文

●輸入m獲取文章目錄

推薦↓↓↓

 

Web開發

更多推薦《18個技術類微信公眾號》

涵蓋：程式人生、演演算法與資料結構、駭客技術與網路安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。