MySQL中的SQL優(yōu)化實(shí)戰(zhàn)記錄

這篇文章主要介紹“MySQL中的SQL優(yōu)化實(shí)戰(zhàn)記錄”，在日常操作中，相信很多人在MySQL中的SQL優(yōu)化實(shí)戰(zhàn)記錄問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對(duì)大家解答”MySQL中的SQL優(yōu)化實(shí)戰(zhàn)記錄”的疑惑有所幫助！接下來，請(qǐng)跟著小編一起來學(xué)習(xí)吧！

背景

本次SQL優(yōu)化是針對(duì)javaweb中的表格查詢做的。

部分網(wǎng)絡(luò)架構(gòu)圖

業(yè)務(wù)簡單說明

N個(gè)機(jī)臺(tái)將業(yè)務(wù)數(shù)據(jù)發(fā)送至服務(wù)器，服務(wù)器程序?qū)?shù)據(jù)入庫至MySQL數(shù)據(jù)庫。服務(wù)器中的javaweb程序?qū)?shù)據(jù)展示到網(wǎng)頁上供用戶查看。

原數(shù)據(jù)庫設(shè)計(jì)

• windows單機(jī)主從分離

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

• 每張表大概20w左右的數(shù)據(jù)

原查詢效率

3天數(shù)據(jù)查詢70-80s

目標(biāo)

3-5s

業(yè)務(wù)缺陷

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

問題排查

前臺(tái)慢 or 后臺(tái)慢

• 如果你配置了druid，可在druid頁面中直接查看sql執(zhí)行時(shí)間和uri請(qǐng)求時(shí)間

• 在后臺(tái)代碼中用System.currentTimeMillis計(jì)算時(shí)間差。

結(jié)論 ： 后臺(tái)慢，且查詢sql慢

sql有什么問題

• sql拼接過長，達(dá)到了3000行，有的甚至到8000行，大多都是union all的操作，且有不必要的嵌套查詢和查詢了不必要的字段

• 利用explain查看執(zhí)行計(jì)劃，where條件中除時(shí)間外只有一個(gè)字段用到了索引

備注 ： 因優(yōu)化完了，之前的sql實(shí)在找不到了，這里只能YY了。

查詢優(yōu)化

去除不必要的字段

效果沒那么明顯

去除不必要的嵌套查詢

效果沒那么明顯

分解sql

• 將union all的操作分解，例如(一個(gè)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分解成若干個(gè)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í)間段所有天數(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);        // 初始化計(jì)數(shù)器        CountDownLatch latch = new CountDownLatch(length);        // 查詢每天的時(shí)間并合并        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ù)，計(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 #跳過所有錯(cuò)誤 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"   # 整個(gè)數(shù)據(jù)庫***連接（用戶）數(shù) max_connections=1000 # 每個(gè)客戶端連接***的錯(cuò)誤允許數(shù)量 max_connect_errors=100 # 表描述符緩存大小，可減少文件打開/關(guān)閉次數(shù) table_open_cache=2000 # 服務(wù)所能處理的請(qǐng)求包的***大小以及服務(wù)所能處理的***的請(qǐng)求大小(當(dāng)與大的BLOB字段一起工作時(shí)相當(dāng)必要)   # 每個(gè)連接獨(dú)立的大小.大小動(dòng)態(tài)增加 max_allowed_packet=64M # 在排序發(fā)生時(shí)由每個(gè)線程分配 sort_buffer_size=8M # 當(dāng)全聯(lián)合發(fā)生時(shí),在每個(gè)線程中分配  join_buffer_size=8M # cache中保留多少線程用于重用 thread_cache_size=128 # 此允許應(yīng)用程序給予線程系統(tǒng)一個(gè)提示在同一時(shí)間給予渴望被運(yùn)行的線程的數(shù)量. thread_concurrency=64 # 查詢緩存 query_cache_size=128M # 只有小于此設(shè)定值的結(jié)果才會(huì)被緩沖   # 此設(shè)置用來保護(hù)查詢緩沖,防止一個(gè)極大的結(jié)果集將其他所有的查詢結(jié)果都覆蓋 query_cache_limit=2M # InnoDB使用一個(gè)緩沖池來保存索引和原始數(shù)據(jù) # 這里你設(shè)置越大,你在存取表里面數(shù)據(jù)時(shí)所需要的磁盤I/O越少.   # 在一個(gè)獨(dú)立使用的數(shù)據(jù)庫服務(wù)器上,你可以設(shè)置這個(gè)變量到服務(wù)器物理內(nèi)存大小的80%   # 不要設(shè)置過大,否則,由于物理內(nèi)存的競爭可能導(dǎo)致操作系統(tǒng)的換頁顛簸.   innodb_buffer_pool_size=1G # 用來同步IO操作的IO線程的數(shù)量 # 此值在Unix下被硬編碼為4,但是在Windows磁盤I/O可能在一個(gè)大數(shù)值下表現(xiàn)的更好.  innodb_read_io_threads=16 innodb_write_io_threads=16 # 在InnoDb核心內(nèi)的允許線程數(shù)量.   # ***值依賴于應(yīng)用程序,硬件以及操作系統(tǒng)的調(diào)度方式.   # 過高的值可能導(dǎo)致線程的互斥顛簸. innodb_thread_concurrency=9  # 0代表日志只大約每秒寫入日志文件并且日志文件刷新到磁盤.   # 1 ,InnoDB會(huì)在每次提交后刷新(fsync)事務(wù)日志到磁盤上 # 2代表日志寫入日志文件在每次提交后,但是日志文件只有大約每秒才會(huì)刷新到磁盤上 innodb_flush_log_at_trx_commit=2 # 用來緩沖日志數(shù)據(jù)的緩沖區(qū)的大小.   innodb_log_buffer_size=16M # 在日志組中每個(gè)日志文件的大小.   innodb_log_file_size=48M # 在日志組中的文件總數(shù).  innodb_log_files_in_group=3 # 在被回滾前,一個(gè)InnoDB的事務(wù)應(yīng)該等待一個(gè)鎖被批準(zhǔn)多久.   # InnoDB在其擁有的鎖表中自動(dòng)檢測(cè)事務(wù)死鎖并且回滾事務(wù).   # 如果你使用 LOCK TABLES 指令, 或者在同樣事務(wù)中使用除了InnoDB以外的其他事務(wù)安全的存儲(chǔ)引擎   # 那么一個(gè)死鎖可能發(fā)生而InnoDB無法注意到.   # 這種情況下這個(gè)timeout值對(duì)于解決這種問題就非常有幫助.  innodb_lock_wait_timeout=30 # 開啟定時(shí) event_scheduler=ON

被批準(zhǔn)多久. # InnoDB在其擁有的鎖表中自動(dòng)檢測(cè)事務(wù)死鎖并且回滾事務(wù). # 如果你使用 LOCK  TABLES 指令, 或者在同樣事務(wù)中使用除了InnoDB以外的其他事務(wù)安全的存儲(chǔ)引擎 # 那么一個(gè)死鎖可能發(fā)生而InnoDB無法注意到. #  這種情況下這個(gè)timeout值對(duì)于解決這種問題就非常有幫助. innodb_lock_wait_timeout=30#  開啟定時(shí)event_scheduler=ON

根據(jù)業(yè)務(wù)，再加上篩選條件

快4-5s

將where條件中除時(shí)間條件外的字段建立聯(lián)合索引

效果沒那么明顯

將where條件中索引條件使用inner join的方式去關(guān)聯(lián)

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

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

應(yīng)該之前有union all，union all是一個(gè)一個(gè)的執(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)達(dá)到了8s左右，再也快不了了。查看mysql的cpu使用率和內(nèi)存使用率都不高，到底為什么查這么慢了，3天最多才60w數(shù)據(jù)，關(guān)聯(lián)的也都是一些字典表，不至于如此。繼續(xù)根據(jù)網(wǎng)上提供的資料，一系列騷操作，基本沒用，沒轍。

環(huán)境對(duì)比

因分析過sql優(yōu)化已經(jīng)ok了，試想是不是磁盤讀寫問題。將優(yōu)化過的程序，分別部署于不同的現(xiàn)場(chǎng)環(huán)境。一個(gè)有ssd，一個(gè)沒有ssd。發(fā)現(xiàn)查詢效率懸殊。用軟件檢測(cè)過發(fā)現(xiàn)ssd讀寫速度在700-800M/s,普通機(jī)械硬盤讀寫在70-80M/s。

優(yōu)化結(jié)果及結(jié)論

• 優(yōu)化結(jié)果：達(dá)到預(yù)期。

• 優(yōu)化結(jié)論：sql優(yōu)化不僅僅是對(duì)sql本身的優(yōu)化，還取決于本身硬件條件，其他應(yīng)用的影響，外加自身代碼的優(yōu)化。

到此，關(guān)于“MySQL中的SQL優(yōu)化實(shí)戰(zhàn)記錄”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！