第五、查詢優化 1、查詢慢的原因：網絡因素、CPU、IO、上下文切換、系統調用、生成統計信息、鎖等待時間等 2、優化數據庫訪問：查詢性能地下的原因是訪問的數據太多，某些查詢不可避免的需要篩選大量的數據，可以通過減少訪問數據量的方式優化；1、確認 應用程序是否在檢索大量超過需要的數據；2、確認mysql服務器層是否在分析大量超過需要的數據行 3、執行過程優化： 1、查詢緩存：在解析一個查詢語句之前，如果查詢緩存是打開的，那么mysql會優先檢查這個查詢是否命中查詢緩存中的數據，如果查 詢恰好命中了查詢緩存，那么會在返回結果之前會檢查用戶權限，如果權限沒有問題，那么mysql會跳過所有的階段，就直接從緩存中拿到結果并返回給 客戶端； 2、查詢優化處理：mysql查詢完緩存之后會經過以下幾個步驟：解析SQL、預處理、優化SQL執行計劃，這幾個步驟出現任何的錯誤，都可能會終止查詢； 1、語法解析器和預處理：mysql通過關鍵字將SQL語句進行解析，并生成一顆解析樹，mysql解析器將使用mysql語法規則驗證和解析查詢，例如驗證使用使用了錯誤的關鍵字或者順序是否正確等等，預處理器會進一步檢查解析樹是否合法，例如表名和列名是否存在，是否有歧義，還會驗證權限等等 2、查詢優化器： 1、select count() from table;show status like 'last_query_cost'; 可以看到這條查詢語句大概需要做1104個數據頁才能找到對應的數據，這是經過一系列的統計信息計算來的(每個表或者索引的頁面個數；索引的基數；索引和數據行的長度；索引的分布情況) 2、很多時候mysql會選擇錯誤的執行計劃(統計信息不準確(InnoDB因為其mvcc的架構，并不能維護一個數據表的行數的精確統計信息 )；執行計劃的成本估算不等同于實際執行的成本(有時候某個執行計劃雖然需要讀取更多的頁面，但是他的成本卻更小，因為如果這些頁面都是順序讀或者這些頁面都已經在內存中的話，那么它的訪問成本將很小，mysql層面并不知道哪些頁面在內存中，哪些在磁盤，所以查詢之際執行過程中到底需要多少次IO是無法得知的)；mysql的最優可能跟你想的不一樣(mysql的優化是基于成本模型的優化，但是有可能不是最快的優化)；mysql不考慮其他并發執行的查詢；mysql不會考慮不受其控制的操作成本(執行存儲過程或者用戶自定義函數的成本))； 3、優化器的優化策略：1、靜態優化，直接對解析樹進行分析，并完成優化；2、動態優化，動態優化與查詢的上下文有關，也可能跟取值、索引對應的行數有關；3、mysql對查詢的靜態優化只需要一次，但對動態優化在每次執行時都需要重新評估 4、優化器的優化類型：重新定義關聯表的順序(數據表的關聯并不總是按照在查詢中指定的順序進行，決定關聯順序時優化器很重要的功能)；將外連接轉化為內連接，內連接的效率要高于外連接；使用等價變換規則，mysql可以使用一些等價變化來簡化并規劃表達式；優化count、min、max索引和列是否可以為空通常可以幫助mysql優化這類表達式：例如，要找到某一列的最小值，只需要查詢索引的最左端的記錄即可，不需要全文掃描比較；預估并轉化為常數表達式，當mysql檢測到一個表達式可以轉化為常數的時候，就會一直把該表達式作為常數進行處理；索引覆蓋掃描，當索引中的列包含所有查詢中需要使用的列的時候，可以使用覆蓋索引；子查詢優化，mysql在某些情況下可以將子查詢轉換一種效率更高的形式，從而減少多個查詢多次對數據進行訪問，例如將經常查詢的數據放入到緩存中；等值傳播：如果兩個列的值通過等式關聯，那么mysql能夠把其中一個列的where條件傳遞到另一個上：explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;這里使用film_id字段進行等值關聯，film_id這個列不僅適用于film表而且適用于film_actor表explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500 and film_actor.film_id > 500; 3、關聯查詢：join的實現原理，Simple Nested-Loop Join，r表示驅動表、s表示驅動表，從圖中可以看出從r中拿到數據匹配s開銷很大

Index Nested-Loop Join要求非驅動表上有索引，可以通過索引來減少比較，加速查詢；在查詢時，驅動表(r)會根據關聯字段的索引進行查詢，當在索引上找到符合的值，在進行回表查詢

Block Nested-Loop Join如果有索引會選取第二種方式進行join如果沒有索引才會使用Block Nested-Loop Join； (1)Join Buffer會緩存所有參與查詢的列而不是只有Join的列。 (2)可以通過調整join_buffer_size緩存大小 (3)join_buffer_size的默認值是256K，join_buffer_size的最大值在MySQL 5.1.22版本前是4G-1，而之后的版本才能在64位操作系統下申請大于4G的Join Buffer空間。 (4)使用Block Nested-Loop Join算法需要開啟優化器管理配置的optimizer_switch的設置block_nested_loop為on，默認為開啟。 show variables like '%optimizer_switch%'

4、排序優化：無論如何排序都是一個成本很高的操作，所以從性能的角度出發，應該盡可能避免排序或者盡可能避免對大量數據進行排序。 推薦使用利用索引進行排序，但是當不能使用索引的時候，mysql就需要自己進行排序，如果數據量小則再內存中進行，如果數據量大就需要使用磁盤，mysql中稱之為filesort。如果需要排序的數據量小于排序緩沖區(show variables like '%sort_buffer_size%';),mysql使用內存進行快速排序操作，如果內存不夠排序，那么mysql就會先將樹分塊，對每個獨立的塊使用快速排序進行排序，并將各個塊的排序結果存放再磁盤上，然后將各個排好序的塊進行合并，最后返回排序結果。 排序算法：兩次傳值排序，第一次數據讀取是將需要排序的字段讀取出來，然后進行排序，第二次是將排好序的結果按照需要去讀取數據行。 這種方式效率比較低，原因是第二次讀取數據的時候因為已經排好序，需要去讀取所有記錄而此時更多的是隨機IO，讀取數據成本會比較高 兩次傳輸的優勢，在排序的時候存儲盡可能少的數據，讓排序緩沖區可以盡可能多的容納行數來進行排序操作。單次傳輸排序：先讀取查詢所需要的所有列，然后再根據給定列進行排序，最后直接返回排序結果，此方式只需要一次順序IO讀取所有的數據，而無須任何的隨機IO，問題在于查詢的列特別多的時候，會占用大量的存儲空間，無法存儲大量的數據。當需要排序的列的總大小超過max_length_for_sort_data定義的字節，mysql會選擇雙次排序，反之使用單次排序，當然，用戶可以設置此參數的值來選擇排序的方式。 4、優化特定類型的優化： 1、優化count查詢：沒有where條件時，count()才是最快的，在myisam的count中；使用近似值，在某些應用場景中，不需要完全精確的值，可以參考使用近似值來代替，比如可以使用explain來獲取近似的值；其實在很多OLAP的應用中，需要計算某一個列值的基數，有一個計算近似值的算法叫hyperloglog。更復雜的優化：一般情況下，count()需要掃描大量的行才能獲取精確的數據，其實很難優化，在實際操作的時候可以考慮使用索引覆蓋掃描，或者增加匯總表，或者增加外部緩存系統。 2、優化關聯查詢：確保on或者using子句中的列上有索引，在創建索引的時候就要考慮到關聯的順序，當表A和表B使用列C關聯的時候，如果優化器的關聯順序是B、A，那么就不需要再B表的對應列上建上索引，沒有用到的索引只會帶來額外的負擔，一般情況下來說，只需要在關聯順序中的第二個表的相應列上創建索引。確保任何的groupby和order by中的表達式只涉及到一個表中的列，這樣mysql才有可能使用索引來優化這個過程。 3、優化子查詢：子查詢的優化最重要的優化建議是盡可能使用關聯查詢代替 4、優化limit分頁：在很多應用場景中我們需要將數據進行分頁，一般會使用limit加上偏移量的方法實現，同時加上合適的orderby 的子句，如果這種方式有索引的幫助，效率通常不錯，否則的化需要進行大量的文件排序操作，還有一種情況，當偏移量非常大的時候，前面的大部分數據都會被拋棄，這樣的代價太高。要優化這種查詢的話，要么是在頁面中限制分頁的數量，要么優化大偏移量的性能。(優化此類查詢的最簡單的辦法就是盡可能地使用覆蓋索引，而不是查詢所有的列)； 5、優化union查詢：mysql總是通過創建并填充臨時表的方式來執行union查詢，因此很多優化策略在union查詢中都沒法很好的使用。經常需要手工的將where、limit、order by等子句下推到各個子查詢中，以便優化器可以充分利用這些條件進行優化；除非確實需要服務器消除重復的行，否則一定要使用union all，因此沒有all關鍵字，mysql會在查詢的時候給臨時表加上distinct的關鍵字，這個操作的代價很高。 第六、分區表 1、分區表應用場景：表非常大以至于無法全部都放在內存中，或者只在表的最后部分有熱點數據，其他均是歷史數據；分區表的數據更容易維護(批量刪除大量數據可以使用清除整個分區的方式；對一個獨立分區進行優化、檢查、修復等操作)；分區表的數據可以分布在不同的物理設備上，從而高效地利用多個硬件設備；可以使用分區表來避免某些特殊的瓶頸(innodb的單個索引的互斥訪問；ext3文件系統的inode鎖競爭)；可以備份和恢復獨立的分區。 2、分區表的權限：一個表最多只能有1024個分區，在5.7版本的時候可以支持8196個分區；在早期的mysql中，分區表達式必須是整數或者是返回整數的表達式，在mysql5.5中，某些場景可以直接使用列來進行分區；如果分區字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進來；分區表無法使用外鍵約束。 3、分區表原理：分區表由多個相關的底層表實現，這個底層表也是由句柄對象標識，我們可以直接訪問各個分區。存儲引擎管理分區的各個底層表和管理普通表一樣(所有的底層表都必須使用相同的存儲引擎)，分區表的索引知識在各個底層表上各自加上一個完全相同的索引。從存儲引擎的角度來看，底層表和普通表沒有任何不同，存儲引擎也無須知道這是一個普通表還是一個分區表的一部分。 select查詢 當查詢一個分區表的時候，分區層先打開并鎖住所有的底層表，優化器先判斷是否可以過濾部分分區，然后再調用對應的存儲引擎接口訪問各個分區的數據 insert操作 當寫入一條記錄的時候，分區層先打開并鎖住所有的底層表，然后確定哪個分區接受這條記錄，再將記錄寫入對應底層表 delete操作 當刪除一條記錄時，分區層先打開并鎖住所有的底層表，然后確定數據對應的分區，最后對相應底層表進行刪除操作

4、分區表類型：范圍分區；列表分區；列分區；hash分區；key分區；子分區。 5、如何使用分區表：全量掃描數據，不要任何索引，使用簡單的分區方式存放表，不要任何索引，根據分區規則大致定位需要的數據為止，通過使用where條件將需要的數據限制在少數分區中，這種策略適用于以正常的方式訪問大量數據；索引數據，并分離熱點，如果數據有明顯的熱點，而且除了這部分數據，其他數據很少被訪問到，那么可以將這部分熱點數據單獨放在一個分區中，讓這個分區的數據能夠有機會都緩存在內存中，這樣查詢就可以只訪問一個很小的分區表，能夠使用索引，也能夠有效的使用緩存。

總結

以上是生活随笔為你收集整理的mysql中出现 xC4 xDD_mysql数据库调优总结(二)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。