A. 如何提高上百萬級記錄MySQL資料庫查詢速度
關於mysql處理百萬級以上的數據時如何提高其查詢速度的方法
最近一段時間由於工作需要，開始關注針對Mysql資料庫的select查詢語句的相關優化方法。
由於在參與的實際項目中發現當mysql表的數據量達到百萬級時，普通SQL查詢效率呈直線下降，而且如果where中的查詢條件較多時，其查詢速度簡直無法容忍。曾經測試對一個包含400多萬條記錄（有索引）的表執行一條條件查詢，其查詢時間竟然高達40幾秒，相信這么高的查詢延時，任何用戶都會抓狂。因此如何提高sql語句查詢效率，顯得十分重要。以下是網上流傳比較廣泛的30種SQL查詢語句優化方法：
1、應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。
2、對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
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 『%c%』
若要提高效率，可以考慮全文檢索。
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、盡量避免大事務操作，提高系統並發能力。
B. MySQL大數據量分頁查詢方法及其優化
使用子查詢優化大數據量分頁查詢
這種方式的做法是先定位偏移位置的id，然後再往後查詢，適用於id遞增的情況。
使用id限定優化大數據量分頁查詢
使用這種方式需要先假設數據表的id是連續遞增的，我們根據查詢的頁數和查詢的記錄數可以算出查詢的id的范圍，可以使用 id between and 來查詢：
當然了，也可以使用in的方式來進行查詢，這種方式經常用在多表關聯的情況下，使用其他表查詢的id集合來進行查詢：
但是使用這種in查詢方式的時候要注意的是，某些MySQL版本並不支持在in子句中使用limit子句。
參考 sql優化之大數據量分頁查詢（mysql） - yanggb - 博客園 (cnblogs.com)
C. mysql百萬數據分頁查詢4秒，求教怎麼優化
很多應用往往只展示最新或最熱門的幾條記錄，但為了舊記錄仍然可訪問，所以就需要個分頁的導航欄。然而，如何通過MySQL更好的實現分頁，始終是比較令人頭疼的問題。雖然沒有拿來就能用的解決辦法，但了解資料庫的底層或多或少有助於優化分頁查詢。
我們先從一個常用但性能很差的查詢來看一看。
SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15
這個查詢耗時0.00sec。So，這個查詢有什麼問題呢？實際上，這個查詢語句和參數都沒有問題，因為它用到了下面表的主鍵，而且只讀取15條記錄。
CREATE TABLE city (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
city varchar(128) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
真正的問題在於offset(分頁偏移量)很大的時候，像下面這樣：
SELECT *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
上面的查詢在有2M行記錄時需要0.22sec，通過EXPLAIN查看SQL的執行計劃可以發現該SQL檢索了100015行，但最後只需要15行。大的分頁偏移量會增加使用的數據，MySQL會將大量最終不會使用的數據載入到內存中。就算我們假設大部分網站的用戶只訪問前幾頁數據，但少量的大的分頁偏移量的請求也會對整個系統造成危害。Facebook意識到了這一點，但Facebook並沒有為了每秒可以處理更多的請求而去優化資料庫，而是將重心放在將請求響應時間的方差變小。
對於分頁請求，還有一個信息也很重要，就是總共的記錄數。我們可以通過下面的查詢很容易的獲取總的記錄數。
SELECT COUNT(*)
FROM city;
然而，上面的SQL在採用InnoDB為存儲引擎時需要耗費9.28sec。一個不正確的優化是採用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能夠在分頁查詢時事先准備好符合條件的記錄數，隨後只要執行一句 select FOUND_ROWS(); 就能獲得總記錄數。但是在大多數情況下，查詢語句簡短並不意味著性能的提高。不幸的是，這種分頁查詢方式在許多主流框架中都有用到，下面看看這個語句的查詢性能。
SELECT SQL_CALC_FOUND_ROWS *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
這個語句耗時20.02sec，是上一個的兩倍。事實證明使用 SQL_CALC_FOUND_ROWS 做分頁是很糟糕的想法。
下面來看看到底如何優化。文章分為兩部分，第一部分是如何獲取記錄的總數目，第二部分是獲取真正的記錄。
高效的計算行數
如果採用的引擎是MyISAM，可以直接執行COUNT(*)去獲取行數即可。相似的，在堆表中也會將行數存儲到表的元信息中。但如果引擎是InnoDB情況就會復雜一些，因為InnoDB不保存表的具體行數。
我們可以將行數緩存起來，然後可以通過一個守護進程定期更新或者用戶的某些操作導致緩存失效時，執行下面的語句：
SELECT COUNT(*)
FROM city
USE INDEX(PRIMARY);
獲取記錄
下面進入這篇文章最重要的部分，獲取分頁要展示的記錄。上面已經說過了，大的偏移量會影響性能，所以我們要重寫查詢語句。為了演示，我們創建一個新的表「news」，按照時事性排序(最新發布的在最前面)，實現一個高性能的分頁。為了簡單，我們就假設最新發布的新聞的Id也是最大的。
CREATE TABLE news(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(128) NOT NULL
) ENGINE=InnoDB;
一個比較高效的方式是基於用戶展示的最後一個新聞Id。查詢下一頁的語句如下，需要傳入當前頁面展示的最後一個Id。
SELECT *
FROM news WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage
查詢上一頁的語句類似，只不過需要傳入當前頁的第一個Id，並且要逆序。
SELECT *
FROM news WHERE id > $last_id
ORDER BY id ASC
LIMIT $perpage
上面的查詢方式適合實現簡易的分頁，即不顯示具體的頁數導航，只顯示「上一頁」和「下一頁」，例如博客中頁腳顯示「上一頁」，「下一頁」的按鈕。但如果要實現真正的頁面導航還是很難的,下面看看另一種方式。
SELECT id
FROM (
SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt
FROM news
JOIN (SELECT @cnt:= 0)T
WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage * $buttons
)C
WHERE cnt = 0;
通過上面的語句可以為每一個分頁的按鈕計算出一個offset對應的id。這種方法還有一個好處。假設，網站上正在發布一片新的文章，那麼所有文章的位置都會往後移一位，所以如果用戶在發布文章時換頁，那麼他會看見一篇文章兩次。如果固定了每個按鈕的offset Id，這個問題就迎刃而解了。Mark Callaghan發表過一篇類似的博客，利用了組合索引和兩個位置變數，但是基本思想是一致的。
如果表中的記錄很少被刪除、修改，還可以將記錄對應的頁碼存儲到表中，並在該列上創建合適的索引。採用這種方式，當新增一個記錄的時候，需要執行下面的查詢重新生成對應的頁號。
SET p:= 0;
UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;
當然，也可以新增一個專用於分頁的表，可以用個後台程序來維護。
UPDATE pagination T
JOIN (
SELECT id, CEIL((p:= p + 1) / $perpage) page
FROM news
ORDER BY id
)C
ON C.id = T.id
SET T.page = C.page;
現在想獲取任意一頁的元素就很簡單了：
SELECT *
FROM news A
JOIN pagination B ON A.id=B.ID
WHERE page=$offset;
還有另外一種與上種方法比較相似的方法來做分頁，這種方式比較試用於數據集相對小，並且沒有可用的索引的情況下—比如處理搜索結果時。在一個普通的伺服器上執行下面的查詢，當有2M條記錄時，要耗費2sec左右。這種方式比較簡單，創建一個用來存儲所有Id的臨時表即可(這也是最耗費性能的地方)。
CREATE TEMPORARY TABLE _tmp (KEY SORT(random))
SELECT id, FLOOR(RAND() * 0x8000000) random
FROM city;
ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;
接下來就可以向下面一樣執行分頁查詢了。
SELECT *
FROM _tmp
WHERE OFFSET >= $offset
ORDER BY OFFSET
LIMIT $perpage;
簡單來說，對於分頁的優化就是。。。避免數據量大時掃描過多的記錄。
D. mysql數據量上十萬條後，查詢慢導致伺服器卡有什麼解決辦法
幾方面：
硬體，軟體，以及語言
硬體，是不是抗不住，
軟體，mysql是不是沒有設置好，資料庫設計方面等，
語言，語句寫法。
下面是一些優化技巧。
1.對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
2.應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：select id from t where num is null可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢：select id from t where num=0
3.應盡量避免在 where 子句中使用!=或<>操作符，否則引擎將放棄使用索引而進行全表掃描。
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.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
6.下面的查詢也將導致全表掃描：select id from t where name like '李%'若要提高效率，可以考慮全文檢索。
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 name like 'abc%'
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.盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。
E. 澶勭悊涓婄櫨涓囨潯鐨勬暟鎹搴撳備綍鎻愰珮澶勭悊鏌ヨ㈤熷害_MySQL
1銆佸規煡璇㈣繘琛屼紭鍖栵紝搴斿敖閲忛伩鍏嶅叏琛ㄦ壂鎻忥紝棣栧厛搴旇冭檻鍦 where 鍙 order by 娑夊強鐨勫垪涓婂緩絝嬬儲寮曘
2銆佸簲灝介噺閬垮厤鍦 where 瀛愬彞涓瀵瑰瓧孌佃繘琛 null 鍊煎垽鏂錛屽惁鍒欏皢瀵艱嚧寮曟搸鏀懼純浣跨敤緔㈠紩鑰岃繘琛屽叏琛ㄦ壂鎻忥紝濡傦細
select id from t where num is null
鍙浠ュ湪num涓婅劇疆榛樿ゅ0錛岀『淇濊〃涓璶um鍒楁病鏈塶ull鍊礆紝鐒跺悗榪欐牱鏌ヨ錛
select id from t where num=0
3銆佸簲灝介噺閬垮厤鍦 where 瀛愬彞涓浣跨敤!=鎴栨搷浣滅︼紝鍚﹀垯灝嗗紩鎿庢斁寮冧嬌鐢ㄧ儲寮曡岃繘琛屽叏琛ㄦ壂鎻忋
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銆乮n 鍜 not in 涔熻佹厧鐢錛屽惁鍒欎細瀵艱嚧鍏ㄨ〃鎵鎻忥紝濡傦細
select id from t where num in(1,2,3)
瀵逛簬榪炵畫鐨勬暟鍊礆紝鑳界敤 between 灝變笉瑕佺敤 in 浜嗭細
select id from t where num between 1 and 3
6銆佷笅闈㈢殑鏌ヨ涔熷皢瀵艱嚧鍏ㄨ〃鎵鎻忥細
select id from t where name like '%abc%'
鑻ヨ佹彁楂樻晥鐜囷紝鍙浠ヨ冭檻鍏ㄦ枃媯緔銆
7銆佸傛灉鍦 where 瀛愬彞涓浣跨敤鍙傛暟錛屼篃浼氬艱嚧鍏ㄨ〃鎵鎻忋傚洜涓篠QL鍙鏈夊湪榪愯屾椂鎵嶄細瑙ｆ瀽灞閮ㄥ彉閲忥紝浣嗕紭鍖栫▼搴忎笉鑳藉皢璁塊棶璁″垝鐨勯夋嫨鎺ㄨ繜鍒拌繍琛屾椂錛涘畠蹇呴』鍦ㄧ紪璇戞椂榪涜岄夋嫨銆傜劧鑰岋紝濡傛灉鍦ㄧ紪璇戞椂寤虹珛璁塊棶璁″垝錛屽彉閲忕殑鍊艱繕鏄鏈鐭ョ殑錛屽洜鑰屾棤娉曚綔涓虹儲寮曢夋嫨鐨勮緭鍏ラ」銆傚備笅闈㈣鍙ュ皢榪涜屽叏琛ㄦ壂鎻忥細
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銆佸簲灝介噺閬垮厤鍦╳here瀛愬彞涓瀵瑰瓧孌佃繘琛屽嚱鏁版搷浣滐紝榪欏皢瀵艱嚧寮曟搸鏀懼純浣跨敤緔㈠紩鑰岃繘琛屽叏琛ㄦ壂鎻忋傚傦細
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’鐢熸垚鐨剗d
搴旀敼涓:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate
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銆佸苟涓嶆槸鎵鏈夌儲寮曞規煡璇㈤兘鏈夋晥錛孲QL鏄鏍規嵁琛ㄤ腑鏁版嵁鏉ヨ繘琛屾煡璇浼樺寲鐨勶紝褰撶儲寮曞垪鏈夊ぇ閲忔暟鎹閲嶅嶆椂錛孲QL鏌ヨ㈠彲鑳戒笉浼氬幓鍒╃敤緔㈠紩錛屽備竴琛ㄤ腑鏈夊瓧孌祍ex錛宮ale銆乫emale鍑犱箮鍚勪竴鍗婏紝閭ｄ箞鍗充嬌鍦╯ex涓婂緩浜嗙儲寮曚篃瀵規煡璇㈡晥鐜囪搗涓嶄簡浣滅敤銆
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 錛屼互鎻愰珮閫熷害錛涘傛灉鏁版嵁閲忎笉澶э紝涓轟簡緙撳拰緋葷粺琛ㄧ殑璧勬簮錛屽簲鍏坈reate table錛岀劧鍚巌nsert銆
24銆佸傛灉浣跨敤鍒頒簡涓存椂琛錛屽湪瀛樺偍榪囩▼鐨勬渶鍚庡姟蹇呭皢鎵鏈夌殑涓存椂琛ㄦ樉寮忓垹闄わ紝鍏 truncate table 錛岀劧鍚 drop table 錛岃繖鏍峰彲浠ラ伩鍏嶇郴緇熻〃鐨勮緝闀挎椂闂撮攣瀹氥
25銆佸敖閲忛伩鍏嶄嬌鐢ㄦ父鏍囷紝鍥犱負娓告爣鐨勬晥鐜囪緝宸錛屽傛灉娓告爣鎿嶄綔鐨勬暟鎹瓚呰繃1涓囪岋紝閭ｄ箞灝卞簲璇ヨ冭檻鏀瑰啓銆
26銆佷嬌鐢ㄥ熀浜庢父鏍囩殑鏂規硶鎴栦復鏃惰〃鏂規硶涔嬪墠錛屽簲鍏堝繪壘鍩轟簬闆嗙殑瑙ｅ喅鏂規堟潵瑙ｅ喅闂棰橈紝鍩轟簬闆嗙殑鏂規硶閫氬父鏇存湁鏁堛
27銆佷笌涓存椂琛ㄤ竴鏍鳳紝娓告爣騫朵笉鏄涓嶅彲浣跨敤銆傚瑰皬鍨嬫暟鎹闆嗕嬌鐢 FAST_FORWARD 娓告爣閫氬父瑕佷紭浜庡叾浠栭愯屽勭悊鏂規硶錛屽挨鍏舵槸鍦ㄥ繀欏誨紩鐢ㄥ嚑涓琛ㄦ墠鑳借幏寰楁墍闇鐨勬暟鎹鏃躲傚湪緇撴灉闆嗕腑鍖呮嫭“鍚堣”鐨勪緥紼嬮氬父瑕佹瘮浣跨敤娓告爣鎵ц岀殑閫熷害蹇銆傚傛灉寮鍙戞椂闂村厑璁革紝鍩轟簬娓告爣鐨勬柟娉曞拰鍩轟簬闆嗙殑鏂規硶閮藉彲浠ュ皾璇曚竴涓嬶紝鐪嬪摢涓縐嶆柟娉曠殑鏁堟灉鏇村ソ銆
28銆佸湪鎵鏈夌殑瀛樺偍榪囩▼鍜岃Е鍙戝櫒鐨勫紑濮嬪勮劇疆 SET NOCOUNT ON 錛屽湪緇撴潫鏃惰劇疆 SET NOCOUNT OFF 銆傛棤闇鍦ㄦ墽琛屽瓨鍌ㄨ繃紼嬪拰瑙﹀彂鍣ㄧ殑姣忎釜璇鍙ュ悗鍚戝㈡埛絝鍙戦 DONE_IN_PROC 娑堟伅銆
29銆佸敖閲忛伩鍏嶅ぇ浜嬪姟鎿嶄綔錛屾彁楂樼郴緇熷苟鍙戣兘鍔涖
30銆佸敖閲忛伩鍏嶅悜瀹㈡埛絝榪斿洖澶ф暟鎹閲忥紝鑻ユ暟鎹閲忚繃澶э紝搴旇ヨ冭檻鐩稿簲闇奼傛槸鍚﹀悎鐞嗐
F. mysql 大數據量查詢如何優化，沒辦法去掉<>和like
其實你這個需要程序和資料庫有一致的設計。可考慮分區。
通過電話前綴來分區，以下只是一個形式，不推薦用中文命名，
這樣，只要用戶不填寫電話，那麼前三字元就是xxx，自動會放入 ZFU區。
CREATE TABLE Customer
(
ID INT NOT NULL,
Mobile_PerNO CHAR(3) DEFAULT 'xxx',
Mobile VARCHAR(30)
)
PARTITION BY LIST(Mobile_PerNO)
PARTITION 聯通186 VALUES IN (133),
PARTITION 移動139 VALUES IN (139),
PARTITION 電信133 VALUES IN (133),
PARTITION ZFU VALUES IN (xxx)
);
其次你過於依賴資料庫而成形的程序，用點不客氣的話說，那就是耦合極高的設計。
你其實完全可以在注冊時，寫入驗證，一個手機號就能注冊一次。左右打掉空格，這樣手機上就能建立唯一索引。 使用LIKE 『133%』 至少性能上有一定的飛躍。
至於你的第二個SQL， 在時間和發送號碼上可以建立索引，然後條件上寫入時間。
也可以使用hash方式按照年季度分區。
理論上 性能提升百倍還是一點問題都沒有的。
分區資料 看官方文檔。