A. sql中一次性查詢10萬條記錄怎麼提高查詢效率
回答他還不簡單 你給他說
您好:首先呢 10萬條數據的表是算是比較小的表了,如果數據量達到千萬和億的話那我可以根據特定欄位設置分區,然後在非分區欄位建立索引,查詢的時候走索引如果就只有10萬數據的話就不用分區了建立索引就可以.設計好表結構後關鍵的是sql的優化.當然那種很簡單的優化,象什麼>=3用>4之類的我就不說了 sql優化比較明顯的就是把結果集最少的塞選條件的表放後面,將中間表作為基礎表.索引上不用運算符等!如果效果不理想就查看執行計劃 分析後進一步優化 估計有些菜比面試官被你一弄他就昏了 然後你可以鄙視下他 就你那水平還考我優化 你自己會多少啊.開玩笑的哈 .和他吹吹得他覺得你比他還牛比 然後你可以給他說說共享池 PGA 數據塊大小啊 操作系統的命中率 和垃圾存儲過程 行了 他不要你都難了 如果你是面試初級程序員的話
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區別
C. 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.盡量避免大事務操作，提高系統並發能力。
D. MySql中Sql的執行過程
如果查詢緩存沒有命中，那麼SQL請求會進入分析器，分析器是用來分辨SQL語句的執行目的，其執行過程大致分為兩步：
表1 語法分析關鍵字然後再通過語法規則解析，判斷輸入的SQL 語句是否滿足MySQL語法，並且生成圖5的語法樹。由SQL語句生成的四個單詞中，識別出兩個關鍵字，分別是select 和from。根據MySQL的語法Select 和 from之間對應的是fields 欄位，下面應該掛接username；在from後面跟隨的是Tables欄位，其下掛接的是userinfo。
優化器的作用是對SQL進行優化，生成最有的執行方案。如圖6所示，前面提到的SQL解析器通過語法分析和語法規則生成了SQL語法樹。這個語法樹作為優化器的輸入，而優化器（黃色的部分）包含了邏輯變換和代價優化兩部分的內容。在優化完成以後會生成SQL執行計劃作為整個優化過程的輸出，交給執行器在存儲引擎上執行。
所處的位置如上圖所示，這節的重點在優化器中的邏輯變換和代價優化上。
邏輯變換也就是在關系代數基礎上進行變換，其目的是為了化簡，同時保證SQL變化前後的結果一致，也就是邏輯變化並不會帶來結果集的變化。其主要包括以下幾個方面：
這樣講概念或許有些抽象，通過圖7 來看看邏輯變化如何在SQL中執行的吧。
如圖7所示，從上往下共有4個步驟：
1. 針對存在的SQL語句，首先通過「否定消除」，去掉條件判斷中的「NOT」。語句由原來的「or」轉換成「and」，並且大於小於符號進行變號。藍色部分為修改前的SQL，紅色是修改以後的SQL。2. 等值傳遞，這一步很好理解分別降」t2.a=9」 和」t2.b=5」分別替換掉SQL中對應的值。3. 接下來就是常量表達式計算，將「5+7」計算得到「12」。4. 最後是常量表達式計算後的化簡，將」9<=10」化簡為」true」帶入到最終的SQL表達式中完成優化。
代價優化是用來確定每個表，根據條件是否應用索引，應用哪個索引和確定多表連接的順序等問題。為了完成代價優化，需要找到一個代價最小的方案。因此，優化器是通過基於代價的計算方法來決定如何執行查詢的（Cost-based Optimization）。簡化的過程如下：
這里將配置操作的代價分為MySQL 服務層和MySQL 引擎層，MySQL 服務層主要是定義CPU的代價，而MySQL 引擎層主要定義IO代價。MySQL 5.7 引入了兩個系統表mysql.server_cost和mysql.engine_cost來分別配置這兩個層的代價。如下：MySQL 服務層代價保存在表server_cost中，其具體內容如下：
由上可以看出創建臨時表的代價是很高的，尤其是內部的myisam或innodb臨時表。MySQL 引擎層代價保存在表engine_cost中，其具體內容如下：
目前io_block_read_cost和memory_block_read_cost默認值均為1，實際生產中建議酌情調大memory_block_read_cost，特別是對普通硬碟的場景。MySQL會根據SQL查詢生成的查詢計劃中對應的操作從上面兩張代價表中查找對應的代價值，並且進行累加形成最終執行SQL計劃的代價。再將多種可能的執行計劃進行比較，選取最小代價的計劃執行。
當分析器生成查詢計劃，並且經過優化器以後，就到了執行器。執行器會選擇執行計劃開始執行，但在執行之前會校驗請求用戶是否擁有查詢的許可權，如果沒有許可權，就會返回錯誤信息，否則將會去調用MySQL引擎層的介面，執行對應的SQL語句並且返回結果。例如SQL：「SELECT * FROM userinfo WHERE username = 'Tom';「假設 「username「 欄位沒有設置索引，就會調用存儲引擎從第一條開始查，如果碰到了用戶名字是」 Tom「， 就將結果集返回，沒有查找到就查看下一行，重復上一步的操作，直到讀完整個表或者找到對應的記錄。需要注意SQL語句的執行順序並不是按照書寫順序來的，順序的定義會在分析器中做好，一般是按照如下順序：
如果命中的記錄比較多，應用會從MySql Server一批批獲取數據
本文從MySQL中SQL語句的執行過程作為切入點，首先介紹了查詢請求的執行流程，其中將MySQL的處理分為MySQL Server層和MySQL存儲引擎層。通過介紹SQL語句的流轉，引出了後面要介紹的5大組件，他們分別是：連接器、查詢緩存、分析器、優化器、執行器。後面的內容中對每個組件進行了詳細的介紹。連接器，負責身份認證和許可權鑒別；查詢緩存，將查詢的結果集進行緩存，提高查詢效率；分析器，對SQL語句執行語法分析和語法規則，生成語法樹和執行計劃；優化器，包括邏輯變換和代價優化；執行器，在檢查用戶許可權以後對數據進行逐條查詢，整個過程遵守SQL語句的執行順序。
E. 北大青鳥java培訓：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的查詢語句的條件順序去匹配合適的索引。