mysql優化代碼（優化mysql的方法）

本文目錄一覽：

1、mysql優化
2、mysql如何優化以下語句，查詢耗時太久了？
3、MySQL常用優化方案
4、幫我優化一段mysql的查詢代碼啊！

mysql優化

1，sql的編譯順序

sql 編譯順序 from… on… join… where… order by… group by… having… select…

2，查看sql語句性能:

explain 查詢sql語句

3，優化

(1). 最佳作前綴，使用索引順序(按編譯順序)與定義索引時順序一致，若該欄位有跳過、反序，該欄位及後面欄位索引失效

(2). where條件中一切不是=的操作大概率會使索引失效，包括in、!=、、is null、計算、函數等等

(3). 查詢欄位與條件欄位不一致時使用子查詢，避免臨時表出現

(4). 若用了複合索引，盡量使用全部索引欄位

(5). 能不查詢多欄位時，盡量使用索引覆蓋

(6). 使用like模糊查詢時，按關鍵字左匹配，即『x%』，若使用』%x%』，索引失效

(7). or會使全部索引失效

(8). 盡量不要導致類型轉換，否則索引失效

(9). 使用order by時，根據表中數據量調整單路還是雙路查詢，也可以調整buffer區大小：如set_max_length_for_sort_data = 1024 (單位byte)

(10). 避免使用select *…

(11). 分頁偏移量大時，盡量使用子查詢 select * from tab where id=(select id from tab limit 100000,1) limit 100;

mysql如何優化以下語句，查詢耗時太久了？

一般進行性能分析，分如下三步：

首先需要使用慢查詢日誌功能，去獲取所有查詢時間比較長的SQL語句

其次查看執行計劃查看有問題的SQL的執行計劃 explain

最後可以使用show profile查看有問題的SQL的性能使用情況

慢查詢日誌分析

首先我們要使用慢查詢日誌，因為它收集了查詢時間比較長的SQL語句，但使用之前必須開啟慢查詢日誌，在配置文件my.cnf（一般為/etc/my.cnf）中的[mysqld] 增加如下參數：

slow_query_log=ONlong_query_time=3slow_query_log_file=/var/lib/mysql/slow-log.log複製代碼

增加這些參數之後，重啟MySQL，可以進行查詢慢查詢日誌是否開啟。

1. 任何地方都不要使用 select * from t，用具體的欄位列表代替「*「，不要返回用不到的任何欄位。

2. 索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。

3. 並不是所有索引對查詢都有效，SQL是根據表中數據來進行查詢優化的，當索引列有大量數據重複時，SQL查詢可能不會去利用索引，如一表中有欄位sex，male、female幾乎各一半，那麼即使在sex上建了索引也對查詢效率起不了作用。

4. 盡量使用數字型欄位，若只含數值信息的欄位盡量不要設計為字元型，這會降低查詢和連接的性能，並會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字元串中每一個字元，而對於數字型而言只需要比較一次就夠了。

5. 儘可能的使用 varchar 代替 char ，因為首先變長欄位存儲空間小，可以節省存儲空間，其次對於查詢來說，在一個相對較小的欄位內搜索效率顯然要高些。

6. 如果使用到了臨時表，在存儲過程的最後務必將所有的臨時表顯式刪除，先 truncate table ，然後 drop table ，這樣可以避免系統表的較長時間鎖定。

7. 對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where和order by相關的列上建立索引。

8. 應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描。

例如： select * from t where num is null

我們可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢：select * from t where num=0。

MySQL常用優化方案

語句執行後，會顯示三個欄位： Query_ID（執行ID） | Duration（持續時間）| Query（查詢語句）；

拿到後Query_ID後，可執行 show profile for query Query_ID ，查看詳細的準備時間，執行時間、執行結束（ preparing、executing、end ）等。

顯示用戶正在運行的線程，需要注意的是，除了 root 用戶能看到所有正在運行的線程外，其他用戶都只能看到自己正在運行的線程，看不到其它用戶正在運行的線程。除非單獨個這個用戶賦予了PROCESS 許可權。

解析語句，查詢是否命中索引，及，命中何種索引，用以判斷是否符合我們的預期。

select_type 常見類型：

(1) SIMPLE(簡單SELECT，不使用UNION或子查詢等)

(2) PRIMARY(子查詢中最外層查詢，查詢中若包含任何複雜的子部分，最外層的select被標記為PRIMARY)

(3) UNION(UNION中的第二個或後面的SELECT語句)

(4) SUBQUERY(子查詢中的第一個SELECT，結果不依賴於外部查詢)

table 常見類型：

顯示這一行的數據是關於哪張表的.

有時不是真實的表名字,看到的是derivedx(x是個數字,我的理解是第幾步執行的結果)

type 常見類型：

對錶訪問方式，表示MySQL在表中找到所需行的方式，又稱「訪問類型」。

常用的類型有： ALL、index、range、 ref、eq_ref、const、system、NULL （從左到右，性能從差到好）

possible_keys

指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢使用（該查詢可以利用的索引，如果沒有任何索引顯示 null）

該列完全獨立於EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。

如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣，創造一個適當的索引並且再次用EXPLAIN檢查查詢

key

key列顯示MySQL實際決定使用的鍵（索引），必然包含在possible_keys中

如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度，非實際長度，為最大可能長度。

註：不損失精確性的情況下，長度越短越好。

ref

列與索引的比較，表示上述表的連接匹配條件，即哪些列或常量被用於查找索引列上的值。

rows

估算出結果集行數，表示MySQL根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數；

extra

該列包含MySQL解決查詢的詳細信息,有以下幾種情況：

(1).Distinct

一旦MYSQL找到了與行相聯合匹配的行，就不再搜索了

(2).Not exists

MYSQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行，就不再搜索了

(3).Range checked for each

Record（index map:#）

沒有找到理想的索引，因此對於從前面表中來的每一個行組合，MYSQL檢查使用哪個索引，並用它來從表中返回行。這是使用索引的最慢的連接之一

(4).Using filesort

看到這個的時候，查詢就需要優化了。MYSQL需要進行額外的步驟來發現如何對返回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行；

(5).Using temporary

看到這個的時候，查詢需要優化了。這裡，MYSQL需要創建一個臨時表來存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上；

(6).Using index

列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對錶的全部的請求列都是同一個索引的部分的時候。

(7).Using where

使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，並且連接類型ALL或index，這就會發生，或者是查詢有問題。

幫我優化一段mysql的查詢代碼啊！

這種怎麼可能優化的了！欄位內容有中文沒？如果沒有中文，可以把相關欄位設置fulltext全文索引，然後用match(….)against(…..)語法查找；如果欄位里有中文，OTL，用lucene吧。

別問我具體代碼怎麼寫，我只點到為止，自己網上搜相關內容吧。

原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/247870.html