MySQL之SQL優(yōu)化實戰(zhàn)記錄
背景
本次SQL優(yōu)化是針對javaweb中的表格查詢做的。
部分網(wǎng)絡(luò)架構(gòu)圖
業(yè)務(wù)簡單說明
N個機臺將業(yè)務(wù)數(shù)據(jù)發(fā)送至服務(wù)器，服務(wù)器程序?qū)?shù)據(jù)入庫至MySQL數(shù)據(jù)庫。服務(wù)器中的javaweb程序?qū)?shù)據(jù)展示到網(wǎng)頁上供用戶查看。
原數(shù)據(jù)庫設(shè)計
- windows單機主從分離
- 已分表分庫，按年分庫，按天分表
- 每張表大概20w左右的數(shù)據(jù)
原查詢效率
3天數(shù)據(jù)查詢70-80s
目標
3-5s
業(yè)務(wù)缺陷
無法使用sql分頁，只能用java做分頁。
問題排查
前臺慢 or 后臺慢
- 如果你配置了druid，可在druid頁面中直接查看sql執(zhí)行時間和uri請求時間
- 在后臺代碼中用System.currentTimeMillis計算時間差。
結(jié)論 ： 后臺慢，且查詢sql慢
sql有什么問題
- sql拼接過長，達到了3000行，有的甚至到8000行，大多都是union all的操作，且有不必要的嵌套查詢和查詢了不必要的字段
- 利用explain查看執(zhí)行計劃，where條件中除時間外只有一個字段用到了索引
備注 ： 因優(yōu)化完了，之前的sql實在找不到了，這里只能YY了。
查詢優(yōu)化
去除不必要的字段
效果沒那么明顯
去除不必要的嵌套查詢
效果沒那么明顯
分解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去執(zhí)行，最終匯總數(shù)據(jù)，***快了20s左右。
- select aa from bb_2018_10_01 left join ... on .. left join .. on .. where ..
將分解的sql異步執(zhí)行
利用java異步編程的操作，將分解的sql異步執(zhí)行并最終匯總數(shù)據(jù)。這里用到了CountDownLatch和ExecutorService，示例代碼如下：
- // 獲取時間段所有天數(shù)
- List<String> days = MyDateUtils.getDays(requestParams.getStartTime(), requestParams.getEndTime());
- // 天數(shù)長度
- int length = days.size();
- // 初始化合并集合，并指定大小，防止數(shù)組越界
- List<你想要的數(shù)據(jù)類型> list = Lists.newArrayListWithCapacity(length);
- // 初始化線程池
- ExecutorService pool = Executors.newFixedThreadPool(length);
- // 初始化計數(shù)器
- 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
- // 將結(jié)果匯總
- list.addAll(查詢結(jié)果);
- } catch (Exception e) {
- logger.error("getTime異常", e);
- } finally {
- latch.countDown();
- }
- }
- });
- }
- try {
- // 等待所有查詢結(jié)束
- latch.await();
- } catch (InterruptedException e) {
- e.printStackTrace();
- }
- // list為匯總集合
- // 如果有必要，可以組裝下你想要的業(yè)務(wù)數(shù)據(jù)，計算什么的，如果沒有就沒了
結(jié)果又快了20-30s
優(yōu)化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"
- # 整個數(shù)據(jù)庫***連接（用戶）數(shù)
- max_connections=1000
- # 每個客戶端連接***的錯誤允許數(shù)量
- max_connect_errors=100
- # 表描述符緩存大小，可減少文件打開/關(guān)閉次數(shù)
- table_open_cache=2000
- # 服務(wù)所能處理的請求包的***大小以及服務(wù)所能處理的***的請求大小(當與大的BLOB字段一起工作時相當必要)
- # 每個連接獨立的大小.大小動態(tài)增加
- max_allowed_packet=64M
- # 在排序發(fā)生時由每個線程分配
- sort_buffer_size=8M
- # 當全聯(lián)合發(fā)生時,在每個線程中分配
- join_buffer_size=8M
- # cache中保留多少線程用于重用
- thread_cache_size=128
- # 此允許應(yīng)用程序給予線程系統(tǒng)一個提示在同一時間給予渴望被運行的線程的數(shù)量.
- thread_concurrency=64
- # 查詢緩存
- query_cache_size=128M
- # 只有小于此設(shè)定值的結(jié)果才會被緩沖
- # 此設(shè)置用來保護查詢緩沖,防止一個極大的結(jié)果集將其他所有的查詢結(jié)果都覆蓋
- query_cache_limit=2M
- # InnoDB使用一個緩沖池來保存索引和原始數(shù)據(jù)
- # 這里你設(shè)置越大,你在存取表里面數(shù)據(jù)時所需要的磁盤I/O越少.
- # 在一個獨立使用的數(shù)據(jù)庫服務(wù)器上,你可以設(shè)置這個變量到服務(wù)器物理內(nèi)存大小的80%
- # 不要設(shè)置過大,否則,由于物理內(nèi)存的競爭可能導致操作系統(tǒng)的換頁顛簸.
- innodb_buffer_pool_size=1G
- # 用來同步IO操作的IO線程的數(shù)量
- # 此值在Unix下被硬編碼為4,但是在Windows磁盤I/O可能在一個大數(shù)值下表現(xiàn)的更好.
- innodb_read_io_threads=16
- innodb_write_io_threads=16
- # 在InnoDb核心內(nèi)的允許線程數(shù)量.
- # ***值依賴于應(yīng)用程序,硬件以及操作系統(tǒng)的調(diào)度方式.
- # 過高的值可能導致線程的互斥顛簸.
- innodb_thread_concurrency=9
- # 0代表日志只大約每秒寫入日志文件并且日志文件刷新到磁盤.
- # 1 ,InnoDB會在每次提交后刷新(fsync)事務(wù)日志到磁盤上
- # 2代表日志寫入日志文件在每次提交后,但是日志文件只有大約每秒才會刷新到磁盤上
- innodb_flush_log_at_trx_commit=2
- # 用來緩沖日志數(shù)據(jù)的緩沖區(qū)的大小.
- innodb_log_buffer_size=16M
- # 在日志組中每個日志文件的大小.
- innodb_log_file_size=48M
- # 在日志組中的文件總數(shù).
- innodb_log_files_in_group=3
- # 在被回滾前,一個InnoDB的事務(wù)應(yīng)該等待一個鎖被批準多久.
- # InnoDB在其擁有的鎖表中自動檢測事務(wù)死鎖并且回滾事務(wù).
- # 如果你使用 LOCK TABLES 指令, 或者在同樣事務(wù)中使用除了InnoDB以外的其他事務(wù)安全的存儲引擎
- # 那么一個死鎖可能發(fā)生而InnoDB無法注意到.
- # 這種情況下這個timeout值對于解決這種問題就非常有幫助.
- innodb_lock_wait_timeout=30
- # 開啟定時
- event_scheduler=ON
被批準多久. # InnoDB在其擁有的鎖表中自動檢測事務(wù)死鎖并且回滾事務(wù). # 如果你使用 LOCK TABLES 指令, 或者在同樣事務(wù)中使用除了InnoDB以外的其他事務(wù)安全的存儲引擎 # 那么一個死鎖可能發(fā)生而InnoDB無法注意到. # 這種情況下這個timeout值對于解決這種問題就非常有幫助. innodb_lock_wait_timeout=30# 開啟定時event_scheduler=ON
根據(jù)業(yè)務(wù)，再加上篩選條件
快4-5s
將where條件中除時間條件外的字段建立聯(lián)合索引
效果沒那么明顯
將where條件中索引條件使用inner join的方式去關(guān)聯(lián)
針對這條，我自身覺得很詫異。原sql,b為索引
- select aa from bb_2018_10_02 left join ... on .. left join .. on .. where b = 'xxx'
應(yīng)該之前有union all，union all是一個一個的執(zhí)行，***匯總的結(jié)果。修改為
- 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
結(jié)果快了3-4s
性能瓶頸
根據(jù)以上操作，3天查詢效率已經(jīng)達到了8s左右，再也快不了了。查看mysql的cpu使用率和內(nèi)存使用率都不高，到底為什么查這么慢了，3天最多才60w數(shù)據(jù)，關(guān)聯(lián)的也都是一些字典表，不至于如此。繼續(xù)根據(jù)網(wǎng)上提供的資料，一系列騷操作，基本沒用，沒轍。
環(huán)境對比
因分析過sql優(yōu)化已經(jīng)ok了，試想是不是磁盤讀寫問題。將優(yōu)化過的程序，分別部署于不同的現(xiàn)場環(huán)境。一個有ssd，一個沒有ssd。發(fā)現(xiàn)查詢效率懸殊。用軟件檢測過發(fā)現(xiàn)ssd讀寫速度在700-800M/s,普通機械硬盤讀寫在70-80M/s。
優(yōu)化結(jié)果及結(jié)論
- 優(yōu)化結(jié)果：達到預期。
- 優(yōu)化結(jié)論：sql優(yōu)化不僅僅是對sql本身的優(yōu)化，還取決于本身硬件條件，其他應(yīng)用的影響，外加自身代碼的優(yōu)化。
小結(jié)
優(yōu)化的過程是自身的一個歷練和考驗，珍惜這種機會，不做只寫業(yè)務(wù)代碼的程序員。希望以上可以有助于你的思考，不足之處望指正。
