⑴ 一條sql執行過長的時間，你如何優化，從哪些方面
1、查看sql是否涉及多表的聯表或者子查詢，如果有，看是否能進行業務拆分，相關欄位冗餘或者合並成臨時表（業務和演算法的優化）
2、涉及鏈表的查詢，是否能進行分表查詢，單表查詢之後的結果進行欄位整合
3、如果以上兩種都不能操作，非要鏈表查詢，那麼考慮對相對應的查詢條件做索引。加快查詢速度
4、針對數量大的表進行歷史表分離（如交易流水表）
5、資料庫主從分離，讀寫分離，降低讀寫針對同一表同時的壓力，至於主從同步，mysql有自帶的binlog實現 主從同步
6、explain分析sql語句，查看執行計劃，分析索引是否用上，分析掃描行數等等
7、查看mysql執行日誌，看看是否有其他方面的問題
個人理解：從根本上來說，查詢慢是佔用mysql內存比較多，那麼可以從這方面去酌手考慮
⑵ sql還能怎麼優化
SQL Server資料庫查詢速度慢的原因有很多，常見的有以下幾種：
1、沒有索引或者沒有用到索引(這是查詢慢最常見的問題，是資料庫設計的缺陷)
2、I/O吞吐量小，形成了瓶頸效應。
3、沒有創建計算列導致查詢不優化。
4、內存不足
5、網路速度慢
6、查詢出的數據量過大（可以採用多次查詢，其他的方法降低數據量）
7、鎖或者死鎖(這也是查詢慢最常見的問題，是程序設計的缺陷)
8、sp_lock,sp_who,活動的用戶查看,原因是讀寫競爭資源。
9、返回了不必要的行和列
10、查詢語句不好，沒有優化
●可以通過以下方法來優化查詢 :
1、把數據、日誌、索引放到不同的I/O設備上，增加讀取速度，以前可以將Tempdb應放在RAID0上，SQL2000不在支持。數據量（尺寸）越大，提高I/O越重要。
2、縱向、橫向分割表，減少表的尺寸(sp_spaceuse)
3、升級硬體
4、根據查詢條件,建立索引,優化索引、優化訪問方式，限制結果集的數據量。注意填充因子要適當（最好是使用默認值0）。索引應該盡量小，使用位元組數小的列建索引好（參照索引的創建）,不要對有限的幾個值的欄位建單一索引如性別欄位。
5、提高網速。
6、擴大伺服器的內存,Windows 2000和SQL server 2000能支持4-8G的內存。
配置虛擬內存：虛擬內存大小應基於計算機上並發運行的服務進行配置。運行 Microsoft SQL Server? 2000時，可考慮將虛擬內存大小設置為計算機中安裝的物理內存的1.5倍。如果另外安裝了全文檢索功能，並打算運行Microsoft搜索服務以便執行全文索引和查詢，可考慮：將虛擬內存大小配置為至少是計算機中安裝的物理內存的3倍。將SQL Server max server memory伺服器配置選項配置為物理內存的1.5倍（虛擬內存大小設置的一半）。
7、增加伺服器CPU個數;但是必須 明白並行處理串列處理更需要資源例如內存。使用並行還是串列程是MSSQL自動評估選擇的。單個任務分解成多個任務，就可以在處理器上運行。例如耽擱查詢 的排序、連接、掃描和GROUP BY字句同時執行，SQL SERVER根據系統的負載情況決定最優的並行等級，復雜的需要消耗大量的CPU的查詢最適合並行處理。但是更新操作UPDATE,INSERT， DELETE還不能並行處理。
8、如果是使用like進行查詢的話，簡單的使用index是不行的，但是全文索引，耗空間。 like ''a%'' 使用索引 like ''%a'' 不使用索引用 like ''%a%'' 查詢時，查詢耗時和欄位值總長度成正比,所以不能用CHAR類型，而是VARCHAR。對於欄位的值很長的建全文索引。
9、DB Server 和APPLication Server 分離；OLTP和OLAP分離
10、分布式分區視圖可用於實現資料庫伺服器聯合體。
聯合體是一組分開管理的伺服器，但它們相互協作分擔系統的處理負荷。這種通過分區數據形成資料庫伺服器聯合體的機制能夠擴大一組伺服器，以支持大型的多層 Web 站點的處理需要。有關更多信息，參見設計聯合資料庫伺服器。（參照SQL幫助文件''分區視圖''）
a、在實現分區視圖之前，必須先水平分區表
b、 在創建成員表後，在每個成員伺服器上定義一個分布式分區視圖，並且每個視圖具有相同的名稱。這樣，引用分布式分區視圖名的查詢可以在任何一個成員伺服器上 運行。系統操作如同每個成員伺服器上都有一個原始表的復本一樣，但其實每個伺服器上只有一個成員表和一個分布式分區視圖。數據的位置對應用程序是透明的。
11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收縮數據和日誌 DBCC SHRINKDB,DBCC SHRINKFILE. 設置自動收縮日誌.對於大的資料庫不要設置資料庫自動增長，它會降低伺服器的性能。
在T-sql的寫法上有很大的講究，下面列出常見的要點：首先，DBMS處理查詢計劃的過程是這樣的：
1、 查詢語句的詞法、語法檢查
2、 將語句提交給DBMS的查詢優化器
3、 優化器做代數優化和存取路徑的優化
4、 由預編譯模塊生成查詢規劃
5、 然後在合適的時間提交給系統處理執行
6、 最後將執行結果返回給用戶。
其次，看一下SQL SERVER的數據存放的結構：一個頁面的大小為8K(8060)位元組，8個頁面為一個盤區，按照B樹存放。
⑶ SQL執行與優化
SQL優化
執行計劃,表關聯查詢順序,優化策略與思路
下面再向前走一些，容我根據自己的認識說一下查詢執行的流程是怎樣的：
1.連接
1.1客戶端發起一條Query請求，監聽客戶端的『連接管理模塊』接收請求
1.2將請求轉發到『連接進/線程模塊』
1.3調用『用戶模塊』來進行授權檢查
1.4通過檢查後，『連接進/線程模塊』從『線程連接池』中取出空閑的被緩存的連接線程和客戶端請求對接，如果失敗則創建一個新的連接請求
2.處理
2.1先查詢緩存，檢查Query語句是否完全匹配，接著再檢查是否具有許可權，都成功則直接取數據返回
2.2上一步有失敗則轉交給『命令解析器』，經過詞法分析，語法分析後生成解析樹
2.3接下來是預處理階段，處理解析器無法解決的語義，檢查許可權等，生成新的解析樹
2.4再轉交給對應的模塊處理
2.5如果是SELECT查詢還會經由『查詢優化器』做大量的優化，生成執行計劃
2.6模塊收到請求後，通過『訪問控制模塊』檢查所連接的用戶是否有訪問目標表和目標欄位的許可權
2.7有則調用『表管理模塊』，先是查看table cache中是否存在，有則直接對應的表和獲取鎖，否則重新打開表文件
2.8根據表的meta數據，獲取表的存儲引擎類型等信息，通過介面調用對應的存儲引擎處理
2.9上述過程中產生數據變化的時候，若打開日誌功能，則會記錄到相應二進制日誌文件中
3.結果
3.1Query請求完成後，將結果集返回給『連接進/線程模塊』
3.2返回的也可以是相應的狀態標識，如成功或失敗等
3.3『連接進/線程模塊』進行後續的清理工作，並繼續等待請求或斷開與客戶端的連接
接下來再走一步，讓我們看看一條SQL語句的前世今生。
首先看一下示例語句
示例語句
執行順序
SQL解析
1. FROM
當涉及多個表的時候，左邊表的輸出會作為右邊表的輸入，之後會生成一個虛擬表VT1。
(1-J1)笛卡爾積
計算兩個相關聯表的笛卡爾積(CROSS JOIN) ，生成虛擬表VT1-J1。
兩次全表掃描
哈希索引,查找復雜度都是 O(1) 。
2. WHERE
對VT1過程中生成的臨時表進行過濾，滿足WHERE子句的列被插入到VT2表中。
注意：
此時因為分組，不能使用聚合運算；也不能使用SELECT中創建的別名；
與ON的區別：
如果有外部列，ON針對過濾的是關聯表，主表（保留表）會返回所有的列；
如果沒有添加外部列，兩者的效果是一樣的；
應用：
對主表的過濾應該放在WHERE；
對於關聯表，先條件查詢後連接則用ON，先連接後條件查詢則用WHERE；
hash join 哈希連接 驅動表和被驅動表都只會訪問0次或1次
應用場景:一個大表一個小表/表上沒有索引/返回結果集比較大
3. GROUP BY
這個子句會把VT2中生成的表按照GROUP BY中的列進行分組。生成VT3表。
注意：
其後處理過程的語句，如SELECT,HAVING，所用到的列必須包含在GROUP BY中，對於沒有出現的，得用聚合函數；
原因：
GROUP BY改變了對表的引用，將其轉換為新的引用方式，能夠對其進行下一級邏輯操作的列會減少；
原作者的理解是：
根據分組欄位，將具有相同分組欄位的記錄歸並成一條記錄，因為每一個分組只能返回一條記錄，除非是被過濾掉了，而不在分組欄位裡面的欄位可能會有多個值，多個值是無法放進一條記錄的，所以必須通過聚合函數將這些具有多值的列轉換成單值；
GROUP BY 重新聚合查詢
4. HAVING
這個子句對VT3表中的不同的組進行過濾，只作用於分組後的數據，滿足HAVING條件的子句被加入到VT4表中。
7.LIMIT
LIMIT子句從上一步得到的VT6虛擬表中選出從指定位置開始的指定行數據。
注意：
offset和rows的正負帶來的影響；
當偏移量很大時效率是很低的，可以這么做：
採用子查詢的方式優化，在子查詢里先從索引獲取到最大id，然後倒序排，再取N行結果集
採用INNER JOIN優化，JOIN子句里也優先從索引獲取ID列表，然後直接關聯查詢獲得最終結果
當前未用到索引,
三次full scan , table1 AS a / table2 AS b / GROUP BY
盡量少做重復的工作
控制同一語句的多次執/減少多次的數據轉換/
杜絕不必要的子查詢和連接表，子查詢在執行計劃一般解釋成外連接，多餘的連接表帶來額外的開銷
關於臨時表和表變數的選擇
臨時表產生使用SELECT INTO和CREATE TABLE + INSERT INTO的選擇，一般情況下，SELECT INTO會比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO會鎖定TEMPDB的系統表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用戶並發環境下，容易阻塞其他進程，所以建議，在並發系統中，盡量使用CREATE TABLE + INSERT INTO，而大數據量的單個語句使用中，使用SELECT INTO。
子查詢的用法
相關子查詢可以用IN、NOT IN、EXISTS、NOT EXISTS引入
NOT IN、NOT EXISTS的相關子查詢可以改用LEFT JOIN代替寫法
如果保證子查詢沒有重復 ，IN、EXISTS的相關子查詢可以用INNER JOIN 代替
IN``的相關子查詢用EXISTS代替
不要用 COUNT (*)的子查詢判斷是否存在記錄，最好用 LEFT` `JOIN 或者EXISTS，比如有人寫這樣的語句：
建立索引後，並不是每個查詢都會使用索引，在使用索引的情況下，索引的使用效率也會有很大的差別。只要我們在查詢語句中沒有強制指定索引，
不要對索引欄位進行運算，而要想辦法做變換
不要對索引欄位進行格式轉換
不要對索引欄位使用函數
不要對索引欄位進行多欄位連接
join關聯查詢的計算是很復雜的，特別是數據量比較大的情況下，實際情況還是拆解較快的
Join拆解的核心就是利用In關鍵字
要麼用空間換時間，要麼用時間換空間
多表連接的連接條件對索引的選擇有著重要的意義，所以我們在寫連接條件條件的時候需要特別注意。
A、多表連接的時候，連接條件必須寫全，寧可重復，不要缺漏。
B、連接條件盡量使用聚集索引
C、注意ON、WHERE和HAVING部分條件的區別
ON是最先執行， WHERE次之，HAVING最後，因為ON是先把不符合條件的記錄過濾後才進行統計，它就可以減少中間運算要處理的數據，按理說應該速度是最快的，WHERE也應該比 HAVING快點的，因為它過濾數據後才進行SUM，在兩個表聯接時才用ON的，所以在一個表的時候，就剩下WHERE跟HAVING比較了
考慮聯接優先順序：
(1)INNER JOIN
(2)LEFT JOIN (註：RIGHT JOIN 用 LEFT JOIN 替代)
(3)CROSS JOIN
索引並不適用於所有情況：a.少量數據；b.頻繁進行改動的欄位，不適合做索引；c.很少使用的欄位，不需要加索引
索引會提高數據查詢效率，但是會降低「增、刪、改」的效率。當不使用索引的時候，我們進行數據的增刪改，只需要操作源表即可，但是當我們添加索引後，不僅需要修改源表，也需要再次修改索引，很麻煩。
先執行順序, 是否走索引, 有無類型轉換
18000 字的SQL優化大全
步步深入：MySQL架構總覽->查詢執行流程->SQL解析順序
MySQL索引總結（4）——btree與hash區別
⑷ 請教：如何優化SQL語句提高運行效率和速度
首先不能用子查詢，這樣會很慢很慢。
用外連接的關聯查詢代替not in
如下：
select a.* from gkbmk a left join gktjk b on a.bmh = b.bmh
where b.bmh is null;
如果不了解外連接，先了解一下吧，呵呵。
索引適當加一點，就可以了，我的句子已經是最佳優化了，你可以在上面加入你需要的條件。
⑸ 如何解決SQL查詢速度太慢
1. 執行計劃中明明有使用到索引，為什麼執行還是這么慢？
2. 執行計劃中顯示掃描行數為 644，為什麼 slow log 中顯示 100 多萬行？
a. 我們先看執行計劃，選擇的索引 「INDX_BIOM_ELOCK_TASK3(TASK_ID)」。結合 sql 來看，因為有 "ORDER BY TASK_ID DESC" 子句，排序通常很慢，如果使用了文件排序性能會更差，優化器選擇這個索引避免了排序。
那為什麼不選 possible_keys:INDX_BIOM_ELOCK_TASK 呢？原因也很簡單，TASK_DATE 欄位區分度太低了，走這個索引需要掃描的行數很大，而且還要進行額外的排序，優化器綜合判斷代價更大，所以就不選這個索引了。不過如果我們強制選擇這個索引（用 force index 語法），會看到 SQL 執行速度更快少於 10s，那是因為優化器基於代價的原則並不等價於執行速度的快慢；
b. 再看執行計劃中的 type:index，"index" 代表 「全索引掃描」，其實和全表掃描差不多，只是掃描的時候是按照索引次序進行而不是行，主要優點就是避免了排序，但是開銷仍然非常大。
Extra:Using where 也意味著掃描完索引後還需要回表進行篩選。一般來說，得保證 type 至少達到 range 級別，最好能達到 ref。
在第 2 點中提到的「慢日誌記錄Rows_examined: 1161559，看起來是全表掃描」，這里更正為「全索引掃描」，掃描行數確實等於表的行數；
c. 關於執行計劃中：「rows：644」，其實這個只是估算值，並不準確，我們分析慢 SQL 時判斷准確的掃描行數應該以 slow log 中的 Rows_examined 為准。
4. 優化建議：添加組合索引 IDX_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID)
優化過程：
TASK_DATE 欄位存在索引，但是選擇度很低，優化器不會走這個索引，建議後續可以刪除這個索引：
select count(*),count(distinct TASK_DATE) from T_BIOMA_ELOCK_TASK;+------------+---------------------------+| count(*) | count(distinct TASK_DATE) |+------------+---------------------------+| 1161559 | 223 |+------------+---------------------------+
在這個 sql 中 REL_DEVID 欄位從命名上看選擇度較高，通過下面 sql 來檢驗確實如此：
select count(*),count(distinct REL_DEVID) from T_BIOMA_ELOCK_TASK;+----------+---------------------------+| count(*) | count(distinct REL_DEVID) |+----------+---------------------------+| 1161559 | 62235 |+----------+---------------------------+
由於有排序，所以得把 task_id 也加入到新建的索引中，REL_DEVID,task_id 組合選擇度 100%：
select count(*),count(distinct REL_DEVID,task_id) from T_BIOMA_ELOCK_TASK;+----------+-----------------------------------+| count(*) | count(distinct REL_DEVID,task_id) |+----------+-----------------------------------+| 1161559 | 1161559 |+----------+-----------------------------------+
在測試環境添加 REL_DEVID，TASK_ID 組合索引，測試 sql 性能：alter table T_BIOMA_ELOCK_TASK add index idx_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID);
添加索引後執行計劃：
這里還要注意一點「隱式轉換」：REL_DEVID 欄位數據類型為 varchar，需要在 sql 中加引號：AND T.REL_DEVID = 000000025xxx >> AND T.REL_DEVID = '000000025xxx'
執行時間從 10s+ 降到 毫秒級別：
1 row in set (0.00 sec)
結論
一個典型的 order by 查詢的優化，添加更合適的索引可以避免性能問題：執行計劃使用索引並不意味著就能執行快。
⑹ mysql資料庫的優化方法
我們都知道，伺服器資料庫的開發一般都是通過java或者是PHP語言來編程實現的，而為了提高我們資料庫的運行速度和效率，資料庫優化也成為了我們每日的工作重點，今天，雲南IT培訓http://www.kmbdqn.cn/就一起來了解一下mysql伺服器資料庫的優化方法。
為什麼要了解索引
真實案例
案例一：大學有段時間學習爬蟲，爬取了知乎300w用戶答題數據，存儲到mysql數據中。那時不了解索引，一條簡單的「根據用戶名搜索全部回答的sql「需要執行半分鍾左右，完全滿足不了正常的使用。
案例二：近線上應用的資料庫頻頻出現多條慢sql風險提示，而工作以來，對資料庫優化方面所知甚少。例如一個用戶數據頁面需要執行很多次資料庫查詢，性能很慢，通過增加超時時間勉強可以訪問，但是性能上需要優化。
索引的優點
合適的索引，可以大大減小mysql伺服器掃描的數據量，避免內存排序和臨時表，提高應用程序的查詢性能。
索引的類型
mysql數據中有多種索引類型，primarykey，unique，normal，但底層存儲的數據結構都是BTREE;有些存儲引擎還提供hash索引，全文索引。
BTREE是常見的優化要面對的索引結構，都是基於BTREE的討論。
B-TREE
查詢數據簡單暴力的方式是遍歷所有記錄;如果數據不重復，就可以通過組織成一顆排序二叉樹，通過二分查找演算法來查詢，大大提高查詢性能。而BTREE是一種更強大的排序樹，支持多個分支，高度更低，數據的插入、刪除、更新更快。
現代資料庫的索引文件和文件系統的文件塊都被組織成BTREE。
btree的每個節點都包含有key，data和只想子節點指針。
btree有度的概念d>=1。假設btree的度為d，則每個內部節點可以有n=[d+1，2d+1)個key，n+1個子節點指針。樹的大高度為h=Logb[(N+1)/2]。
索引和文件系統中，B-TREE的節點常設計成接近一個內存頁大小(也是磁碟扇區大小)，且樹的度非常大。這樣磁碟I/O的次數，就等於樹的高度h。假設b=100，一百萬個節點的樹，h將只有3層。即，只有3次磁碟I/O就可以查找完畢，性能非常高。
索引查詢
建立索引後，合適的查詢語句才能大發揮索引的優勢。
另外，由於查詢優化器可以解析客戶端的sql語句，會調整sql的查詢語句的條件順序去匹配合適的索引。
⑺ 這些SQL優化技巧握在手，面試可以橫著走……
一、SQL執行順序
二、基礎SQL優化
1、查詢SQL盡量不要使用select *，而是具體欄位
1）反例
2）正例
3）理由
2、避免在where子句中使用or來連接條件
查詢id為1或者薪水為3000的用戶：
1）反例
2）正例
使用union all：
分開兩條SQL寫：
3）理由
3、使用varchar代替char
1）反例
2）正例
3）理由
4、盡量使用數值替代字元串類型
5、查詢盡量避免返回大量數據
如果查詢返回數據量很大，就會造成查詢時間過長，網路傳輸時間過長。同時，大量數據返回也可能沒有實際意義。如返回上千條甚至更多，用戶也看不過來。
通常採用分頁，一頁習慣10/20/50/100條。
6、使用explain分析你SQL執行計劃
SQL很靈活，一個需求可以很多實現，那哪個最優呢？SQL提供了explain關鍵字，它可以分析你的SQL執行計劃，看它是否最佳。Explain主要看SQL是否使用了索引。
返回結果：
7、是否使用了索引及其掃描類型
type：
性能排行：
System > const > eq_ref > ref > range > index > ALL
possible_keys：
key：
8、創建name欄位的索引
提高查詢速度的最簡單最佳的方式。
9、優化like語句
模糊查詢，程序員最喜歡的就是使用like，但是like很可能讓你的索引失效。
1）反例
2）正例
3）理由
未使用索引，故意使用sex非索引欄位：
主鍵索引生效：
索引失效，type=ALL，全表掃描：
10、字元串怪現象
1）反例
2）正例
3）理由
為什麼第一條語句未加單引號就不走索引了呢？這是因為不加單引號時，是字元串跟數字的比較，它們類型不匹配，MySQL會做隱式的類型轉換，把它們轉換為數值類型再做比較。
11、索引不宜太多，一般5個以內
12、索引不適合建在有大量重復數據的欄位上
如性別欄位。因為SQL優化器是根據表中數據量來進行查詢優化的，如果索引列有大量重復數據，Mysql查詢優化器推算發現不走索引的成本更低，很可能就放棄索引了。
13、where限定查詢的數據
數據中假定就一個男的記錄。
1）反例
2）正例
3）理由
14、避免在索引列上使用內置函數
業務需求：查詢最近七天內新生兒（用學生表替代下）
給birthday欄位創建索引：
當前時間加7天：
1）反例
2）正例
3）理由
15、避免在where中對欄位進行表達式操作
1）反例
2）正例
3）理由
16、避免在where子句中使用!=或>操作符
應盡量避免在where子句中使用!=或>操作符，否則引擎將放棄使用索引而進行全表掃描。記住實現業務優先，實在沒辦法，就只能使用，並不是不能使用。如果不能使用，SQL也就無需支持了。
1）反例
2）理由
17、去重distinct過濾欄位要少
1）理由
18、where中使用默認值代替null
環境准備：
1）反例
2）正例
3）理由
三、高級SQL優化
1、批量插入性能提升
大量數據提交，上千，上萬，批量性能非常快，mysql獨有。
1）多條提交
2）批量提交
3）理由
2、批量刪除優化
避免同時修改或刪除過多數據，因為會造成cpu利用率過高，會造成鎖表操作，從而影響別人對資料庫的訪問。
1）反例
2）正例
3）理由
3、偽刪除設計
1）商品狀態（state）
2）理由
4、提高group by語句的效率
可以在執行到該語句前，把不需要的記錄過濾掉。
1）反例：先分組，再過濾
2）正例：先過濾，後分組
5、復合索引最左特性
創建復合索引，也就是多個欄位。
滿足復合索引的左側順序，哪怕只是部分，復合索引生效。
沒有出現左邊的欄位，則不滿足最左特性，索引失效。
復合索引全使用，按左側順序出現 name,salary，索引生效。
雖然違背了最左特性，但MYSQL執行SQL時會進行優化，底層進行顛倒優化。
1）理由
6、排序欄位創建索引
什麼樣的欄位才需要創建索引呢？原則就是where和order by中常出現的欄位就創建索引。
7、刪除冗餘和重復的索引
8、不要有超過5個以上的表連接
9、inner join 、left join、right join，優先使用inner join
三種連接如果結果相同，優先使用inner join，如果使用left join左邊表盡量小。
1）理由
10、in子查詢的優化
日常開發實現業務需求可以有兩種方式實現：
如需求：查詢所有部門的所有員工：
假設表A表示某企業的員工表，表B表示部門表，查詢所有部門的所有員工，很容易有以下程序實現，可以抽象成這樣的一個嵌套循環：