共計 11708 個字符，預計需要花費 30 分鐘才能閱讀完成。
這篇文章主要講解了“mysql 數據庫優化方式匯總”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“mysql 數據庫優化方式匯總”吧！
數據庫優化
sql 語句優化
索引優化
加緩存
讀寫分離
分區
分布式數據庫（垂直切分）
水平切分 www.walekan.com/kj/kjpp/1459
MyISAM 和 InnoDB 的區別：
1. InnoDB 支持事務，MyISAM 不支持，對于 InnoDB 每一條 SQL 語言都默認封裝成事務，自動提交，這樣會影響速度，所以最好把多條 SQL 語言放在 begin 和 commit 之間，組成一個事務；
2. InnoDB 支持外鍵，而 MyISAM 不支持。對一個包含外鍵的 InnoDB 表轉為 MYISAM 會失敗；
3. InnoDB 不保存表的具體行數，執行 select count(*) from table 時需要全表掃描。而 MyISAM 用一個變量保存了整個表的行數，執行上述語句時只需要讀出該變量即可，速度很快；
4. Innodb 不支持全文索引，而 MyISAM 支持全文索引，查詢效率上 MyISAM 要高；
5. 鎖機制不同: InnoDB 為行級鎖，myisam 為表級鎖。
注意：當數據庫無法確定，所找的行時，也會變為鎖定整個表。
如：update table set num = 10 where username like“%test%”;
一，SQL 語句性能優化
1，對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
2，應盡量避免在 where 子句中對字段進行 null 值判斷，創建表時 NULL 是默認值，但大多數時候應該使用 NOT NULL，或者使用一個特殊的值，如 0，- 1 作為默 認值。
3，應盡量避免在 where 子句中使用!= 或 操作符，MySQL 只有對以下操作符才使用索引：，=，=，，=，BETWEEN，IN，以及某些時候的 LIKE。
4，應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，可以 使用 UNION 合并查詢：select id from t where num=10 union all select id from t where num=20
5，in 和 not in 也要慎用，否則會導致全表掃描，對于連續的數值，能用 between 就不要用 in 了：Select id from t where num between 1 and 3
6，下面的查詢也將導致全表掃描：select id from t where name like‘%abc%’或者 select id from t where name like‘%abc’若要提高效率，可以考慮全文檢索。而 select id from t where name like‘abc%’才用到索引
7，如果在 where 子句中使用參數，也會導致全表掃描。
8，應盡量避免在 where 子句中對字段進行表達式操作，應盡量避免在 where 子句中對字段進行函數操作
9, 很多時候用 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)
10, 索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過 6 個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。
11, 應盡可能的避免更新 clustered 索引數據列，因為 clustered 索引數據列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引數據列，那么需要考慮是否應將該索引建為 clustered 索引。
12，盡量使用數字型字段，若只含數值信息的字段盡量不要設計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。
13，盡可能的使用 varchar/nvarchar 代替 char/nchar，因為首先變長字段存儲空間小，可以節省存儲空間，其次對于查詢來說，在一個相對較小的字段內搜索效率顯然要高些。
14，最好不要使用”“返回所有：select from t，用具體的字段列表代替“*”，不要返回用不到的任何字段。
15，盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。
16，使用表的別名(Alias)：當在 SQL 語句中連接多個表時, 請使用表的別名并把別名前綴于每個 Column 上. 這樣一來, 就可以減少解析的時間并減少那些由 Column 歧義引起的語法錯誤。
17，使用“臨時表”暫存中間結果
簡化 SQL 語句的重要方法就是采用臨時表暫存中間結果，但是，臨時表的好處遠遠不止這些，將臨時結果暫存在臨時表，后面的查詢就在 tempdb 中了，這可以避免程序中多次掃描主表，也大大減少了程序執行中“共享鎖”阻塞“更新鎖”，減少了阻塞，提高了并發性能。
18，一些 SQL 查詢語句應加上 nolock，讀、寫是會相互阻塞的，為了提高并發性能，對于一些查詢，可以加上 nolock，這樣讀的時候可以允許寫，但缺點是可能讀到未提交的臟數據。使用 nolock 有 3 條原則。查詢的結果用于“插、刪、改”的不能加 nolock！查詢的表屬于頻繁發生頁分裂的，慎用 nolock！使用臨時表一樣可以保存“數據前影”，起到類似 Oracle 的 undo 表空間的功能，能采用臨時表提高并發性能的，不要用 nolock。
19，常見的簡化規則如下：不要有超過 5 個以上的表連接（JOIN），考慮使用臨時表或表變量存放中間結果。少用子查詢，視圖嵌套不要過深, 一般視圖嵌套不要超過 2 個為宜。
20，將需要查詢的結果預先計算好放在表中，查詢的時候再 Select。這在 SQL7.0 以前是最重要的手段。例如醫院的住院費計算。
21，用 OR 的字句可以分解成多個查詢，并且通過 UNION 連接多個查詢。他們的速度只同是否使用索引有關, 如果查詢需要用到聯合索引，用 UNION all 執行的效率更高. 多個 OR 的字句沒有用到索引，改寫成 UNION 的形式再試圖與索引匹配。一個關鍵的問題是否用到索引。
22，在 IN 后面值的列表中，將出現最頻繁的值放在最前面，出現得最少的放在最后面，減少判斷的次數。
23，盡量將數據的處理工作放在服務器上，減少網絡的開銷，如使用存儲過程。存儲過程是編譯好、優化過、并且被組織到一個執行規劃里、且存儲在數據庫中的 SQL 語句，是控制流語言的集合，速度當然快。反復執行的動態 SQL, 可以使用臨時存儲過程，該過程（臨時表）被放在 Tempdb 中。
24，當服務器的內存夠多時，配制線程數量 = 最大連接數 +5，這樣能發揮最大的效率；否則使用 配制線程數量 最大連接數啟用 SQL SERVER 的線程池來解決, 如果還是數量 = 最大連接數 +5，嚴重的損害服務器的性能。
25，查詢的關聯同寫的順序
select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID =‘JCNPRH39681’（A = B ,B =‘號碼’）
select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID =‘JCNPRH39681’and b.referenceid =‘JCNPRH39681’（A = B ,B =‘號碼’，A =‘號碼’）
select a.personMemberID, * from chineseresume a,personmember b where b.referenceid =‘JCNPRH39681’and a.personMemberID =‘JCNPRH39681’（B =‘號碼’，A =‘號碼’）
26，盡量使用 exists 代替 select count(1)來判斷是否存在記錄，count 函數只有在統計表中所有行數時使用，而且 count(1)比 count(*)更有效率。
27，盡量使用“=”，不要使用“”。
28，索引的使用規范：索引的創建要與應用結合考慮，建議大的 OLTP 表不要超過 6 個索引；盡可能的使用索引字段作為查詢條件，尤其是聚簇索引，必要時可以通過 index index_name 來強制指定索引；避免對大表查詢時進行 table scan，必要時考慮新建索引；在使用索引字段作為條件時，如果該索引是聯合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用；要注意索引的維護，周期性重建索引，重新編譯存儲過程。
29，下列 SQL 條件語句中的列都建有恰當的索引，但執行速度卻非常慢：
SELECT * FROM record WHERE substrINg(card_no,1,4)=’5378’(13 秒)
SELECT * FROM record WHERE amount/30 1000（11 秒）
SELECT * FROM record WHERE convert(char(10),date,112)=’19991201’（10 秒）
分析：
WHERE 子句中對列的任何操作結果都是在 SQL 運行時逐列計算得到的，因此它不得不進行表搜索，而沒有使用該列上面的索引；如果這些結果在查詢編譯時就能得到，那么就可以被 SQL 優化器優化，使用索引，避免表搜索，因此將 SQL 重寫成下面這樣：
SELECT * FROM record WHERE card_no like‘5378%’（1 秒）
SELECT * FROM record WHERE amount 1000*30（1 秒）
SELECT * FROM record WHERE date=‘1999/12/01’（1 秒）
30，當有一批處理的插入或更新時，用批量插入或批量更新，絕不會一條條記錄的去更新!
31，在所有的存儲過程中，能夠用 SQL 語句的，我絕不會用循環去實現!
(例如：列出上個月的每一天，我會用 connect by 去遞歸查詢一下，絕不會去用循環從上個月第一天到最后一天)
32，選擇最有效率的表名順序(只在基于規則的優化器中有效)：
oracle 的解析器按照從右到左的順序處理 FROM 子句中的表名，FROM 子句中寫在最后的表 (基礎表 driving table) 將被最先處理，在 FROM 子句中包含多個表的情況下, 你必須選擇記錄條數最少的表作為基礎表。如果有 3 個以上的表連接查詢, 那就需要選擇交叉表 (intersection table) 作為基礎表, 交叉表是指那個被其他表所引用的表.
33，提高 GROUP BY 語句的效率, 可以通過將不需要的記錄在 GROUP BY 之前過濾掉. 下面兩個查詢返回相同結果，但第二個明顯就快了許多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING JOB =’PRESIDENT’
OR JOB =’MANAGER’
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB =’PRESIDENT’
OR JOB =’MANAGER’
GROUP BY JOB
34，sql 語句用大寫，因為 oracle 總是先解析 sql 語句，把小寫的字母轉換成大寫的再執行。
35，別名的使用，別名是大型數據庫的應用技巧，就是表名、列名在查詢中以一個字母為別名，查詢速度要比建連接表快 1.5 倍。
36，避免死鎖，在你的存儲過程和觸發器中訪問同一個表時總是以相同的順序; 事務應經可能地縮短，在一個事務中應盡可能減少涉及到的數據量; 永遠不要在事務中等待用戶輸入。
37，避免使用臨時表，除非卻有需要，否則應盡量避免使用臨時表，相反，可以使用表變量代替; 大多數時候(99%)，表變量駐扎在內存中，因此速度比臨時表更快，臨時表駐扎在 TempDb 數據庫中，因此臨時表上的操作需要跨數據庫通信，速度自然慢。
38，最好不要使用觸發器，觸發一個觸發器，執行一個觸發器事件本身就是一個耗費資源的過程; 如果能夠使用約束實現的，盡量不要使用觸發器; 不要為不同的觸發事件 (Insert，Update 和 Delete) 使用相同的觸發器; 不要在觸發器中使用事務型代碼。
39，索引創建規則：
表的主鍵、外鍵必須有索引；
數據量超過 300 的表應該有索引；
經常與其他表進行連接的表，在連接字段上應該建立索引；
經常出現在 Where 子句中的字段，特別是大表的字段，應該建立索引；
索引應該建在選擇性高的字段上；
索引應該建在小字段上，對于大的文本字段甚至超長字段，不要建索引；
復合索引的建立需要進行仔細分析，盡量考慮用單字段索引代替；
正確選擇復合索引中的主列字段，一般是選擇性較好的字段；
復合索引的幾個字段是否經常同時以 AND 方式出現在 Where 子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復合索引；否則考慮單字段索引；
如果復合索引中包含的字段經常單獨出現在 Where 子句中，則分解為多個單字段索引；
如果復合索引所包含的字段超過 3 個，那么仔細考慮其必要性，考慮減少復合的字段；
如果既有單字段索引，又有這幾個字段上的復合索引，一般可以刪除復合索引；
頻繁進行數據操作的表，不要建立太多的索引；
刪除無用的索引，避免對執行計劃造成負面影響；
表上建立的每個索引都會增加存儲開銷，索引對于插入、刪除、更新操作也會增加處理上的開銷。另外，過多的復合索引，在有單字段索引的情況下，一般都是沒有存在價值的；相反，還會降低數據增加刪除時的性能，特別是對頻繁更新的表來說，負面影響更大。
盡量不要對數據庫中某個含有大量重復的值的字段建立索引。
40，mysql 查詢優化總結：使用慢查詢日志去發現慢查詢，使用執行計劃去判斷查詢是否正常運行，總是去測試你的查詢看看是否他們運行在最佳狀態下。久而久之性能總會變化，避免在整個表上使用 count(*), 它可能鎖住整張表，使查詢保持一致以便后續相似的查詢可以使用查詢緩存
，在適當的情形下使用 GROUP BY 而不是 DISTINCT，在 WHERE, GROUP BY 和 ORDER BY 子句中使用有索引的列，保持索引簡單, 不在多個索引中包含同一個列，有時候 MySQL 會使用錯誤的索引, 對于這種情況使用 USE INDEX，檢查使用 SQL_MODE=STRICT 的問題，對于記錄數小于 5 的索引字段，在 UNION 的時候使用 LIMIT 不是是用 OR。
為了 避免在更新前 SELECT，使用 INSERT ON DUPLICATE KEY 或者 INSERT IGNORE , 不要用 UPDATE 去實現，不要使用 MAX, 使用索引字段和 ORDER BY 子句，LIMIT M，N 實際上可以減緩查詢在某些情況下，有節制地使用，在 WHERE 子句中使用 UNION 代替子查詢，在重新啟動的 MySQL，記得來溫暖你的數據庫，以確保您的數據在內存和查詢速度快，考慮持久連接，而不是多個連接，以減少開銷，基準查詢，包括使用服務器上的負載，有時一個簡單的查詢可以影響其他查詢，當負載增加您的服務器上，使用 SHOW PROCESSLIST 查看慢的和有問題的查詢，在開發環境中產生的鏡像數據中 測試的所有可疑的查詢。
41，MySQL 備份過程:
從二級復制服務器上進行備份。在進行備份期間停止復制，以避免在數據依賴和外鍵約束上出現不一致。徹底停止 MySQL，從數據庫文件進行備份。
如果使用 MySQL dump 進行備份，請同時備份二進制日志文件 – 確保復制沒有中斷。不要信任 LVM 快照，這很可能產生數據不一致，將來會給你帶來麻煩。為了更容易進行單表恢復，以表為單位導出數據 – 如果數據是與其他表隔離的。
當使用 mysqldump 時請使用 –opt。在備份之前檢查和優化表。為了更快的進行導入，在導入時臨時禁用外鍵約束。
為了更快的進行導入，在導入時臨時禁用唯一性檢測。在每一次備份后計算數據庫，表以及索引的尺寸，以便更夠監控數據尺寸的增長。
通過自動調度腳本監控復制實例的錯誤和延遲。定期執行備份。
42，查詢緩沖并不自動處理空格，因此，在寫 SQL 語句時，應盡量減少空格的使用，尤其是在 SQL 首和尾的空格(因為，查詢緩沖并不自動截取首尾空格)。
43，member 用 mid 做標準進行分表方便查詢么？一般的業務需求中基本上都是以 username 為查詢依據，正常應當是 username 做 hash 取模來分表吧。分表的話 mysql 的 partition 功能就是干這個的，對代碼是透明的；
在代碼層面去實現貌似是不合理的。
44，我們應該為數據庫里的每張表都設置一個 ID 做為其主鍵，而且最好的是一個 INT 型的（推薦使用 UNSIGNED），并設置上自動增加的 AUTO_INCREMENT 標志。
45，在所有的存儲過程和觸發器的開始處設置 SET NOCOUNT ON，在結束時設置 SET NOCOUNT OFF。
無需在執行存儲過程和觸發器的每個語句后向客戶端發送 DONE_IN_PROC 消息。
46，MySQL 查詢可以啟用高速查詢緩存。這是提高數據庫性能的有效 Mysql 優化方法之一。當同一個查詢被執行多次時，從緩存中提取數據和直接從數據庫中返回數據快很多。
47，EXPLAIN SELECT 查詢用來跟蹤查看效果
使用 EXPLAIN 關鍵字可以讓你知道 MySQL 是如何處理你的 SQL 語句的。這可以幫你分析你的查詢語句或是表結構的性能瓶頸。EXPLAIN 的查詢結果還會告訴你你的索引主鍵被如何利用的，你的數據表是如何被搜索和排序的……等等，等等。
48，當只要一行數據時使用 LIMIT 1
當你查詢表的有些時候，你已經知道結果只會有一條結果，但因為你可能需要去 fetch 游標，或是你也許會去檢查返回的記錄數。在這種情況下，加上 LIMIT 1 可以增加性能。這樣一樣，MySQL 數據庫引擎會在找到一條數據后停止搜索，而不是繼續往后查少下一條符合記錄的數據。
49, 選擇表合適存儲引擎：
myisam: 應用時以讀和插入操作為主，只有少量的更新和刪除，并且對事務的完整性，并發性要求不是很高的。
Innodb：事務處理，以及并發條件下要求數據的一致性。除了插入和查詢外，包括很多的更新和刪除。（Innodb 有效地降低刪除和更新導致的鎖定）。對于支持事務的 InnoDB 類型的表來說，影響速度的主要原因是 AUTOCOMMIT 默認設置是打開的，而且程序沒有顯式調用 BEGIN 開始事務，導致每插入一條都自動提交，嚴重影響了速度。可以在執行 sql 前調用 begin，多條 sql 形成一個事物（即使 autocommit 打開也可以），將大大提高性能。
50, 優化表的數據類型, 選擇合適的數據類型：
原則：更小通常更好，簡單就好，所有字段都得有默認值, 盡量避免 null。
例如：數據庫表設計時候更小的占磁盤空間盡可能使用更小的整數類型.(mediumint 就比 int 更合適)
比如時間字段：datetime 和 timestamp, datetime 占用 8 個字節，而 timestamp 占用 4 個字節，只用了一半，而 timestamp 表示的范圍是 1970—2037 適合做更新時間
MySQL 可以很好的支持大數據量的存取，但是一般說來，數據庫中的表越小，在它上面執行的查詢也就會越快。
因此，在創建表的時候，為了獲得更好的性能，我們可以將表中字段的寬度設得盡可能小。例如，
在定義郵政編碼這個字段時，如果將其設置為 CHAR(255), 顯然給數據庫增加了不必要的空間，
甚至使用 VARCHAR 這種類型也是多余的，因為 CHAR(6)就可以很好的完成任務了。同樣的，如果可以的話，
我們應該使用 MEDIUMINT 而不是 BIGIN 來定義整型字段。
應該盡量把字段設置為 NOT NULL，這樣在將來執行查詢的時候，數據庫不用去比較 NULL 值。
對于某些文本字段，例如“省份”或者“性別”，我們可以將它們定義為 ENUM 類型。因為在 MySQL 中，ENUM 類型被當作數值型數據來處理，
而數值型數據被處理起來的速度要比文本類型快得多。這樣，我們又可以提高數據庫的性能。
51，字符串數據類型：char，varchar，text 選擇區別
52，任何對列的操作都將導致表掃描，它包括數據庫函數、計算表達式等等，查詢時要盡可能將操作移至等號右邊。
二、索引優化
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. 盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。
三、加緩存
緩存之所以有效，主要是因為程序運行時對內存或者外存的訪問呈現局部性特征，局部性特征為空間局部性和時間局部性兩方面。時間局部性是指剛剛訪問過的數據近期可能再次被訪問，空間局部性是指，某個位置被訪問后，其相鄰的位置的數據很可能被訪問到。而 MySQL 的緩存機制就是把剛剛訪問的數據（時間局部性）以及未來即將訪問到的數據（空間局部性）保存到緩存中，甚至是高速緩存中。從而提高 I / O 效率。
按照緩存讀寫功能的不同，MySQL 將緩存分為 Buffer 緩存和 Cache 緩存。
Buffer 緩存。由于硬盤的寫入速度過慢，或者頻繁的 I /O，對于硬盤來說是極大的效率浪費。那么可以等到緩存中儲存一定量的數據之后，一次性的寫入到硬盤中。Buffer 緩存主要用于寫數據，提升 I / O 性能。
Cache 緩存。Cache 緩存一般是一些訪問頻繁但是變更較少的數據，如果 Cache 緩存已經存儲滿，則啟用 LRU 算法，進行數據淘汰。淘汰掉最遠未使用的數據，從而開辟新的存儲空間。不過對于特大型的網站，依靠這種策略很難緩解高頻率的讀請求，一般會把訪問非常頻繁的數據靜態化，直接由 nginx 返還給用戶。程序和數據庫 I / O 設備交互的越少，則效率越高。
感謝各位的閱讀，以上就是“mysql 數據庫優化方式匯總”的內容了，經過本文的學習后，相信大家對 mysql 數據庫優化方式匯總這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！
