本文作者：小祝特煩惱
背景
本次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
将如上sql分解成若幹個sql去執行，最終彙總資料，最後快了20s左右。
select
将分解的sql異步執行
利用java異步程式設計的操作，将分解的sql異步執行并最終彙總資料。這裡用到了CountDownLatch和ExecutorService，示例代碼如下：
// 擷取時間段所有天數
結果又快了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
應該之前有union all，union all是一個一個的執行，最後彙總的結果。修改為
select
結果快了3-4s
性能瓶頸
根據以上操作，3天查詢效率已經達到了8s左右，再也快不了了。檢視mysql的cpu使用率和記憶體使用率都不高，到底為什麼查這麼慢了，3天最多才60w資料，關聯的也都是一些字典表，不至于如此。繼續根據網上提供的資料，一系列騷操作，基本沒用，沒轍。
環境對比
因分析過sql優化已經ok了，試想是不是磁盤讀寫問題。将優化過的程式，分别部署于不同的現場環境。一個有ssd，一個沒有ssd。發現查詢效率懸殊。用軟體檢測過發現ssd讀寫速度在700-800M/s,普通機械硬碟讀寫在70-80M/s。
優化結果及結論
優化結果：達到預期。
優化結論：sql優化不僅僅是對sql本身的優化，還取決于本身硬體條件，其他應用的影響，外加自身代碼的優化。
小結
優化的過程是自身的一個曆練和考驗，珍惜這種機會，不做隻寫業務代碼的程式員。希望以上可以有助于你的思考，不足之處望指正。