一個在mysql中查詢過慢的問題,我的查詢語句是多表聯合查詢.語句寫法如下.感覺不是很好.能否優化???

問題

創新互聯公司公司2013年成立，先為潁上等服務建站，潁上等地企業，進行企業商務咨詢服務。為潁上企業網站制作PC+手機+微官網三網同步一站式服務解決您的所有建站問題。

我們有一個 SQL，用于找到沒有主鍵 / 唯一鍵的表，但是在 MySQL 5.7 上運行特別慢，怎么辦？

實驗

我們搭建一個 MySQL 5.7 的環境，此處省略搭建步驟。

寫個簡單的腳本，制造一批帶主鍵和不帶主鍵的表：

執行一下腳本：

現在執行以下 SQL 看看效果：

...

執行了 16.80s，感覺是非常慢了。

現在用一下 DBA 三板斧，看看執行計劃：

感覺有點慘，由于 information_schema.columns 是元數據表，沒有必要的統計信息。

那我們來 show warnings 看看 MySQL 改寫后的 SQL：

我們格式化一下 SQL：

可以看到 MySQL 將

select from A where A.x not in (select x from B) //非關聯子查詢

轉換成了

select from A where not exists (select 1 from B where B.x = a.x) //關聯子查詢

如果我們自己是 MySQL，在執行非關聯子查詢時，可以使用很簡單的策略：

select from A where A.x not in (select x from B where ...) //非關聯子查詢:1. 掃描 B 表中的所有記錄，找到滿足條件的記錄，存放在臨時表 C 中，建好索引2. 掃描 A 表中的記錄，與臨時表 C 中的記錄進行比對，直接在索引里比對，

而關聯子查詢就需要循環迭代：

select from A where not exists (select 1 from B where B.x = a.x and ...) //關聯子查詢掃描 A 表的每一條記錄 rA： ? ? 掃描 B 表，找到其中的第一條滿足 rA 條件的記錄。

顯然，關聯子查詢的掃描成本會高于非關聯子查詢。

我們希望 MySQL 能先"緩存"子查詢的結果（緩存這一步叫物化，MATERIALIZATION），但MySQL 認為不緩存更快，我們就需要給予 MySQL 一定指導。

...

可以看到執行時間變成了 0.67s。

整理

我們診斷的關鍵點如下：

\1. 對于 information_schema 中的元數據表，執行計劃不能提供有效信息。

\2. 通過查看 MySQL 改寫后的 SQL，我們猜測了優化器發生了誤判。

\3. 我們增加了 hint，指導 MySQL 正確進行優化判斷。

但目前我們的實驗僅限于猜測，猜中了萬事大吉，猜不中就無法做出好的診斷。

mysql 存儲過程執行太慢怎么優化

1.當我們請求mysql服務器的時候,MySQL前端會有一個監聽,請求到了之后,服務器得到相關的SQL語句,執行之前(虛線部分為執行),還會做權限的判斷

2.通過權限之后,SQL就到MySQL內部,他會在查詢緩存中,看該SQL有沒有執行過,如果有查詢過,則把緩存結果返回,說明在MySQL內部,也有一個查詢緩存.但是這個查詢緩存,默認是不開啟的,這個查詢緩存,和我們的Hibernate，Mybatis的查詢緩存是一樣的,因為查詢緩存要求SQL和參數都要一樣,所以這個命中率是非常低的（沒什么卵用的意思）。

3.如果我們沒有開啟查詢緩存,或者緩存中沒有找到對應的結果,那么就到了解析器,解析器主要對SQL語法進行解析

4.解析結束后就變成一顆解析樹,這個解析樹其實在Hibernate里面也是有的,大家回憶一下,在以前做過Hibernate項目的時候,是不是有個一個antlr.jar。這個就是專門做語法解析的工具.因為在Hibernate里面有HQL，它就是通過這個工具轉換成SQL的,我們編程語言之所以有很多規范、語法,其實就是為了便于這個解析器解析,這個學過編譯原理的應該知道.

5.得到解析樹之后,不能馬上執行,這還需要對這棵樹進行預處理,也就是說,這棵樹,我沒有經過任何優化的樹,預處理器會這這棵樹進行一些預處理,比如常量放在什么地方,如果有計算的東西,把計算的結果算出來等等...

6.預處理完畢之后,此時得到一棵比較規范的樹,這棵樹就是要拿去馬上做執行的樹,比起之前的那棵樹,這棵得到了一些優化

7.查詢優化器，是MySQL里面最關鍵的東西,我們寫任何一條SQL,比如SELECT * FROM USER WHERE USERNAME = toby AND PASSWORD = 1,它會怎么去執行?它是先執行username = toby還是password = 1?每一條SQL的執行順序查詢優化器就是根據MySQL對數據統計表的一些信息,比如索引,比如表一共有多少數據,MySQL都是有緩存起來的,在真正執行SQL之前,他會根據自己的這些數據,進行一個綜合的判定,判斷這一次在多種執行方式里面,到底選哪一種執行方式,可能運行的最快.這一步是MySQL性能中,最關鍵的核心點,也是我們的優化原則.我們平時所講的優化SQL,其實說白了,就是想讓查詢優化器,按照我們的想法,幫我們選擇最優的執行方案,因為我們比MySQL更懂我們的數據.MySQL看數據,僅僅只是自己收集到的信息,這些信息可能是不準確的,MySQL根據這些信息選了一個它自認為最優的方案,但是這個方案可能和我們想象的不一樣.

8.這里的查詢執行計劃,也就是MySQL查詢中的執行計劃,比如要先執行username = toby還是password = 1

9.這個執行計劃會傳給查詢執行引擎,執行引擎選擇存儲引擎來執行這一份傳過來的計劃,到磁盤中的文件中去查詢,這個時候重點來了,影響這個查詢性能最根本的原因是什么?就是硬盤的機械運動,也就是我們平時熟悉的IO,所以一條查詢語句是快還是慢,就是根據這個時間的IO來確定的.那怎么執行IO又是什么來確定的?就是傳過來的這一份執行計劃.(優化就是制定一個我們認為最快的執行方案,最節省IO,和執行最快)

10.如果開了查詢緩存,則返回結果給客戶端,并且查詢緩存也放一份。

Mysql某個表有近千萬數據，CRUD比較慢，如何優化？

數據千萬級別之多，占用的存儲空間也比較大，可想而知它不會存儲在一塊連續的物理空間上，而是鏈式存儲在多個碎片的物理空間上。可能對于長字符串的比較，就用更多的時間查找與比較，這就導致用更多的時間。

可以做表拆分，減少單表字段數量，優化表結構。

在保證主鍵有效的情況下，檢查主鍵索引的字段順序，使得查詢語句中條件的字段順序和主鍵索引的字段順序保持一致。

主要兩種拆分 垂直拆分，水平拆分。

垂直分表

也就是“大表拆小表”，基于列字段進行的。一般是表中的字段較多，將不常用的， 數據較大，長度較長（比如text類型字段）的拆分到“擴展表“。 一般是針對 那種 幾百列的大表，也避免查詢時，數據量太大造成的“跨頁”問題。

垂直分庫針對的是一個系統中的不同業務進行拆分，比如用戶User一個庫，商品Product一個庫，訂單Order一個庫。 切分后，要放在多個服務器上，而不是一個服務器上。為什么？ 我們想象一下，一個購物網站對外提供服務，會有用戶，商品，訂單等的CRUD。沒拆分之前， 全部都是落到單一的庫上的，這會讓數據庫的單庫處理能力成為瓶頸。按垂直分庫后，如果還是放在一個數據庫服務器上， 隨著用戶量增大，這會讓單個數據庫的處理能力成為瓶頸，還有單個服務器的磁盤空間，內存，tps等非常吃緊。 所以我們要拆分到多個服務器上，這樣上面的問題都解決了，以后也不會面對單機資源問題。

數據庫業務層面的拆分，和服務的“治理”，“降級”機制類似，也能對不同業務的數據分別的進行管理，維護，監控，擴展等。 數據庫往往最容易成為應用系統的瓶頸，而數據庫本身屬于“有狀態”的，相對于Web和應用服務器來講，是比較難實現“橫向擴展”的。 數據庫的連接資源比較寶貴且單機處理能力也有限，在高并發場景下，垂直分庫一定程度上能夠突破IO、連接數及單機硬件資源的瓶頸。

水平分表

針對數據量巨大的單張表（比如訂單表），按照某種規則（RANGE,HASH取模等），切分到多張表里面去。 但是這些表還是在同一個庫中，所以庫級別的數據庫操作還是有IO瓶頸。不建議采用。

水平分庫分表

將單張表的數據切分到多個服務器上去，每個服務器具有相應的庫與表，只是表中數據集合不同。 水平分庫分表能夠有效的緩解單機和單庫的性能瓶頸和壓力，突破IO、連接數、硬件資源等的瓶頸。

水平分庫分表切分規則

1. RANGE

從0到10000一個表，10001到20000一個表；

2. HASH取模

一個商場系統，一般都是將用戶，訂單作為主表，然后將和它們相關的作為附表，這樣不會造成跨庫事務之類的問題。 取用戶id，然后hash取模，分配到不同的數據庫上。

3. 地理區域

比如按照華東，華南，華北這樣來區分業務，七牛云應該就是如此。

4. 時間

按照時間切分，就是將6個月前，甚至一年前的數據切出去放到另外的一張表，因為隨著時間流逝，這些表的數據 被查詢的概率變小，所以沒必要和“熱數據”放在一起，這個也是“冷熱數據分離”。

分庫分表后面臨的問題

事務支持

分庫分表后，就成了分布式事務了。如果依賴數據庫本身的分布式事務管理功能去執行事務，將付出高昂的性能代價； 如果由應用程序去協助控制，形成程序邏輯上的事務，又會造成編程方面的負擔。

跨庫join

只要是進行切分，跨節點Join的問題是不可避免的。但是良好的設計和切分卻可以減少此類情況的發生。解決這一問題的普遍做法是分兩次查詢實現。在第一次查詢的結果集中找出關聯數據的id,根據這些id發起第二次請求得到關聯數據。

跨節點的count,order by,group by以及聚合函數問題

這些是一類問題，因為它們都需要基于全部數據集合進行計算。多數的代理都不會自動處理合并工作。解決方案：與解決跨節點join問題的類似，分別在各個節點上得到結果后在應用程序端進行合并。和join不同的是每個結點的查詢可以并行執行，因此很多時候它的速度要比單一大表快很多。但如果結果集很大，對應用程序內存的消耗是一個問題。

數據遷移，容量規劃，擴容等問題

來自淘寶綜合業務平臺團隊，它利用對2的倍數取余具有向前兼容的特性（如對4取余得1的數對2取余也是1）來分配數據，避免了行級別的數據遷移，但是依然需要進行表級別的遷移，同時對擴容規模和分表數量都有限制。總得來說，這些方案都不是十分的理想，多多少少都存在一些缺點，這也從一個側面反映出了Sharding擴容的難度。

ID問題

一旦數據庫被切分到多個物理結點上，我們將不能再依賴數據庫自身的主鍵生成機制。一方面，某個分區數據庫自生成的ID無法保證在全局上是唯一的；另一方面，應用程序在插入數據之前需要先獲得ID,以便進行SQL路由.

一些常見的主鍵生成策略

UUID

使用UUID作主鍵是最簡單的方案，但是缺點也是非常明顯的。由于UUID非常的長，除占用大量存儲空間外，最主要的問題是在索引上，在建立索引和基于索引進行查詢時都存在性能問題。

Twitter的分布式自增ID算法Snowflake

在分布式系統中，需要生成全局UID的場合還是比較多的，twitter的snowflake解決了這種需求，實現也還是很簡單的，除去配置信息，核心代碼就是毫秒級時間41位 機器ID 10位 毫秒內序列12位。

跨分片的排序分頁

一般來講，分頁時需要按照指定字段進行排序。當排序字段就是分片字段的時候，我們通過分片規則可以比較容易定位到指定的分片，而當排序字段非分片字段的時候，情況就會變得比較復雜了。為了最終結果的準確性，我們需要在不同的分片節點中將數據進行排序并返回，并將不同分片返回的結果集進行匯總和再次排序，最后再返回給用戶。

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

根據所描述的問題，可嘗試在mms_profitcenter 的FOrderID ，FSuffix列上建立索引，再查詢試試。 下面提供30種mysql常用優化方法供參考：

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

2.應盡量避免在 where 子句中使用!=或操作符，否則將引擎放棄使用索引而進行全表掃描。

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

select id from t where num is null

可以在num上設置默認值0，確保表中num列沒有null值，然后這樣查詢：

select id from t where num=0

4.應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10

union all

select id from t where num=20

5.下面的查詢也將導致全表掃描：

select id from t where name like '%abc%'

若要提高效率，可以考慮全文檢索。

6.in 和 not in 也要慎用，否則會導致全表掃描，如：

select id from t where num in(1,2,3)

對于連續的數值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7.如果在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變量，但優化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：

select id from t where num=@num

可以改為強制查詢使用索引：

select id from t with(index(索引名)) where num=@num

8.應盡量避免在 where 子句中對字段進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where num/2=100

應改為:

select id from t where num=100*2

9.應盡量避免在where子句中對字段進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where substring(name,1,3)='abc'--name以abc開頭的id

select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id

應改為:

select id from t where name like 'abc%'

select id from t where createdate='2005-11-30' and createdate'2005-12-1'

10.不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。

11.在使用索引字段作為條件時，如果該索引是復合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用，并且應盡可能的讓字段順序與索引順序相一致。

12.不要寫一些沒有意義的查詢，如需要生成一個空表結構：

select col1,col2 into #t from t where 1=0

這類代碼不會返回任何結果集，但是會消耗系統資源的，應改成這樣：

create table #t(...)

13.很多時候用 exists 代替 in 是一個好的選擇：

select num from a where num in(select num from b)

用下面的語句替換：

select num from a where exists(select 1 from b where num=a.num)

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

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

16.應盡可能的避免更新 clustered 索引數據列，因為 clustered 索引數據列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引數據列，那么需要考慮是否應將該索引建為 clustered 索引。

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

18.盡可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長字段存儲空間小，可以節省存儲空間，其次對于查詢來說，在一個相對較小的字段內搜索效率顯然要高些。

19.任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。

20.盡量使用表變量來代替臨時表。如果表變量包含大量數據，請注意索引非常有限（只有主鍵索引）。

21.避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。

22.臨時表并不是不可使用，適當地使用它們可以使某些例程更有效，例如，當需要重復引用大型表或常用表中的某個數據集時。但是，對于一次性事件，最好使用導出表。

23.在新建臨時表時，如果一次性插入數據量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數據量不大，為了緩和系統表的資源，應先create table，然后insert。

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

25.盡量避免使用游標，因為游標的效率較差，如果游標操作的數據超過1萬行，那么就應該考慮改寫。

26.使用基于游標的方法或臨時表方法之前，應先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。

27.與臨時表一樣，游標并不是不可使用。對小型數據集使用 FAST_FORWARD 游標通常要優于其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數據時。在結果集中包括“合計”的例程通常要比使用游標執行的速度快。如果開發時間允許，基于游標的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。

28.在所有的存儲過程和觸發器的開始處設置 SET NOCOUNT ON ，在結束時設置 SET NOCOUNT OFF 。無需在執行存儲過程和觸發器的每個語句后向客戶端發送 DONE_IN_PROC 消息。

29.盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。

30.盡量避免大事務操作，提高系統并發能力。