Mysql知識匯總筆記

標籤：

一。關於表串連

1. 表串連主要包括外串連和內串連，內串連只是選出串連的兩張表中的相匹配的記錄，但是外串連會選出其它不匹配的記錄。內串連和外串連有不同的應用情境。

2. 左串連是以左邊表中的記錄為準，如果右邊表中沒有和左邊表相匹配的記錄，左邊表的記錄還是會顯示。3. 右串連是以右邊表中的記錄為準，如果左邊表中沒有和右邊表相匹配的記錄，右邊表的記錄還是會顯示。4. 由2和3中可以知道，左串連和右串連是可以互相轉換的 二。關於內建函數，需要用的時候可以查表，盡量使用這些函數，避免自己實現1. 字串函數，concat等2. 數值函數，rand函數3. 日期和時間函數，unix_timestamp(date)和from_unixtime()4. 流程函數，比如if，和casewhen5. 其它常用函數，Ip和long轉換，md5，password函數等 三。關於儲存引擎，根據不同的應用類型選擇合適的儲存引擎1. 常用的儲存引擎包括innodb和myisam，其中myisam主要用於OLAP的應用，innodb主要用於OLTP的應用。2. myisam表檔案：.frm(表定義).myd(儲存資料).myi(儲存索引)3. myisam表支援的儲存格式：    a. 靜態表（每條記錄都是固定長度，儲存非常迅速，容易緩衝，缺點是佔用的空間比動態表多，因為儲存的時候會按照列的寬度定義來補足空格）    b. 動態表（包含變長欄位，記錄不是固定的長度，儲存佔用空間相對少，但是頻繁更新刪除會造成片段，需要執行optimize table）    c. 壓縮表（佔據磁碟空間很小，每個記錄單獨壓縮）4. innodb自動成長列必須是索引，如果是複合式索引，必須是複合式索引的第一列。5. innodb支援外鍵，外鍵約束詳見：6. innodb儲存表和索引有共用資料表空間和多資料表空間儲存的形式。及時在多資料表空間的儲存方式下，共用資料表空間仍然是必須的，innodb會把內部資料詞典和未作日誌放在這個檔案之中。7. 如果應用以讀操作和插入操作為主，只有很少的更新和刪除操作，並且對事務的完整性，並發性要求不高，那麼就選擇myisam引擎。如果對事務的完整性要求比較高，在並發情況下         要求資料的一致性，資料CRUD操作都有，那麼選擇innodb儲存引擎，這樣可以有效降低由於刪除和更新導致的鎖定，確保事務的完整性。 四。關於資料類型1. 少量字串的時候選擇char和varchar，大量文本的時候用text或者blob，blob用來儲存位元據。不過blob和text在刪除操作的時候會留下很大的空洞，以後填入這些空洞的記錄在插入的效能上會有影響。可以用optimize table來整理片段。小技巧是可以使用合成索引（根據大文本來建立一個散列值）提高查詢效能，不過只能應用於精確尋找。2. 浮點數要注意精度和四捨五入的問題。在一些精度要求比較大的應用中應該使用定點數或者字串來儲存資料。3. 根據實際需要來選擇滿足應用的最小儲存的日期類型。datetime類型記錄的年份能夠比較久遠，timestamp能夠和實際時區相對應。 五。關於字元集1. 關於常用的編碼知識，詳見：2. 在選擇資料庫字元集的時候需要根據應用的需求來的ing，是否滿足應用所支援的語言的需求，是否需要處理各種各樣的蚊子，或者發布到不同語言的國家和地區。如果資料庫中已經存在了資料，還需要充分考慮資料庫字元集對已有資料的相容性。比如漢字編碼比較多，那麼可以選擇GBK，因為GBK漢字佔用兩個位元組，UTF8佔用三個位元組，如果僅有少量漢字資料，那麼選擇UTF8好，因為UTF8英文字元只需要一個位元組，其餘的GBK或者UTF16都需要兩個位元組來對西文字元進行編碼。3. mysql儲存資料的字元集和校對規則有4個層級的預設設定：伺服器層級，資料庫層級，表層級和欄位級。分別在不同的地方設定，作用也不同。4. 還需要注意用戶端和伺服器之間互動的字元集和校對規則，mysql提供了三個不同的參數來代表用戶端，串連和返回結果的字元集，通常情況下，這三個字元集應該是一樣。5. 字元集的修改步驟： 六。關於索引1. 索引的列的值波動範圍越大，索引的效果越好。2. 如果對字串列進行索引，應該指定一個前置長度，這樣能夠節省大量的索引空間。3. 利用最左首碼，在建立一個N列的索引時，實際上是建立了mysql可利用的N個索引。多列索引可以起幾個索引的作用，因為可利用索引中最左邊的列集來匹配，這樣的列稱為最左首碼4. 不要過度索引，索引會佔用磁碟空間，降低寫操作的效能，此外，mysql在產生一個執行計畫的時候需要考慮各個索引，這也會花費時間。5. innodb記錄預設會按照主鍵或者唯一索引來儲存記錄，要選擇最常訪問的列作為主鍵，提高查詢效率。innodb的表的普通索引也都會儲存主鍵的索引值，所以主鍵要儘可能選擇較短的資料類型，這樣可以減少索引的磁碟佔用。6. btree索引的介紹：7. 大多數的mysql索引（primarykey，unique，index和fulltext）在btree中儲存。空間列類型的索引使用RTREE，並且memory表還支援hash索引。 七。關於視圖 八。關於預存程序和函數1. 預存程序的詳細文法介紹：2. 預存程序和函數的優勢是可以將資料的處理放在資料庫伺服器上進行，避免將大量的結果集傳輸給用戶端，減少資料的傳輸，但是資料庫伺服器上進行大量的運算也會佔用伺服器的CPU，造成資料庫伺服器的壓力，所以要仔細斟酌。 九。關於觸發器1. 觸發器是與表相關的資料庫物件，在滿足定義條件時出發，並且執行觸發器中定義的語句集合。作用是可以協助應用在資料庫端確保資料的完整性。2. 觸發器的文法詳見： 十。事務和鎖1. mysql支援對myisam的表級鎖定，對innodb的行層級鎖定。當與伺服器的串連被關閉的時候，所有由當前線程鎖定的表都會被解鎖。2. myisam鎖和事務詳細列子：3. 分散式交易： 十一。sql安全和sql mode1. sql注入：程式對使用者輸入的資料沒有進行嚴格的過濾，導致非法資料庫查詢語句的執行，同理的還有xss注入。java中採用prepareStatement來防止sql注入。2. sqlmode：通過設定sql mode，可以完成不同嚴格程度的資料校正，有效保障資料的準確性。 十二。複雜sql的編寫1. groupby和having， with rollup， bit函數，rand()函數 十三。最佳化SQL語句1. show status命令來瞭解當前資料庫的情況，比如查詢更新的比例，事務執行情況，慢查詢的次數，串連次數等2. 慢查詢日誌一定要開啟，通過查看慢查詢日誌來定位執行很慢的sql語句。show processlist命令可以查看當前mysql在進行的線程，包括線程的狀態，是否鎖表。3. 使用explain和desc命令來查詢sql執行計畫。關於explain命令的詳細解釋：4. sql執行使用的索引問題。5. 定期分析表和檢查表，最佳化表。6. 大批量插入資料，先關閉索引更新然後load，再開啟索引。7. innodb類型的表按照主鍵順序儲存，所以匯入資料按照主鍵順序排列可以提高匯入資料的效率。在匯入資料前關閉唯一性校正也可以提高匯入效率。  十四。索引1. myisam儲存引擎的表的資料和索引是分開儲存的，innodb儲存引擎的表的資料和索引時儲存在同一個資料表空間裡的。2. 查詢中使用索引的最主要條件是查詢條件中使用了索引關鍵字，如果是多列索引，那麼只有查詢條件使用了多列關鍵字最左邊的首碼時才可以使用索引，否則不能使用索引，索引具有首碼特性。3. mysqlBTREE索引的詳細解釋：4. 對於like查詢，後面如果是常量並且只有%不在第一個字元，索引才可能被使用5. mysql在以下情況下不會使用索引：比如使用索引比全表掃描更慢，hash索引where條件中不使用=，用or分隔開的條件，如果or前的條件中的列有索引，後面列中沒有，那麼不會用到索引。6. 使用show status like "Handler_read%"查看索引使用方式 十五。最佳化資料庫物件1. 最佳化表的資料類型，在表運行一段時間之後使用函數procedure analyse來對錶進行分析，最佳化表的欄位類型。2. 對錶進行拆分，包括垂直分割和水平分割。垂直分割以後查詢所有資料需要join，水平分割查詢資料需要union。水平分割可以按照時間或者按照某個列3. 設計表的時候可以適當的增加冗餘欄位，這樣可以降低串連操作的需求，不必死守資料庫規範。但是這樣在更新的時候可能會造成資料的不一致，所以需要定期更新或者使用觸發器。 十六。鎖問題1. 鎖衝突是影響資料庫並發訪問效能的一個重要因素。所以理解sql語句執行的時候是否會加鎖以及加什麼鎖很重要。主要用到mysql表所和innodb行鎖。2. mysql鎖分為表級鎖，行級鎖，頁面鎖。innodb支援行級鎖和表級鎖。myisam支援表鎖。表級鎖不會出現死結，但是鎖定粒度大，發生鎖衝突的機率最高，並發度最低。行級鎖開銷大，會出現死結，鎖定粒度最小，發生鎖衝突的機率最低，並發度也最高。3. myisam在執行查詢select前，會自動給涉及的所有表加讀鎖，在執行（update，delete，insert）前，會自動給涉及的表加寫鎖。但是顯式加鎖必須要手動解鎖，在執行lock tables後，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表。myisam總是一次獲得SQL語句鎖需要的全部鎖，所以myisam表不會出現死結。4. myiasm表鎖的例子：5. innodb鎖問題。 十七。事務1. 事務ACID屬性，原子性：事務是一個原子操作單元，其對資料的修改，要麼全都執行，要麼全都不執行。一致性：在事務開始和完成時，資料都必須保持一致狀態。隔離性：資料庫系統提供一定的隔離機制，保證事務在不受外部並行作業影響的獨立環境執行。持久性：事務完成之後，它對於資料的修改是永久性，及時出現系統故障也能夠保持。2. 並發交易處理會提高資料庫系統的事務輸送量，從而可以支援更多的使用者，但是也會帶來一些問題，主要包括：更新丟失，髒讀，不可重複讀取，幻讀。3. 更新丟失是應用的責任，髒讀不可重複讀取以及幻讀，這是資料庫讀一致性的問題，必須由資料庫提供一定的事務隔離機制來解決。資料庫實現事務隔離的方式，基本上分為兩種，一種是加鎖，另外一種是MVCC（不加鎖，通過一定機制產生一個資料請求時間點的一致性資料快照）4. 為了處理隔離與並發的矛盾，有4個交易隔離等級，RU，RC，RR，S。交易隔離等級對應的例子： 十八。備份和恢複1. mysql的主從備份就是slave mysql在不斷的做基於BINLOG的恢複。主從備份有多重層級，比如基於行層級，或者基於sql語句層級，或者兩者兼顧3. 備份分為邏輯備份和物理備份，邏輯備份使用mysqldump，myisam儲存引擎在備份的時候需要加鎖來保證資料的一致性，對於innodb來說通過--single-transaction可以獲得一個快照，這樣使備份的資料能夠保持一致性。恢複很簡單，直接將mysqldump的輸出當成輸入執行即可。可以開啟備份檔案看看內容。4. 表的匯出可以select into outfile，mysqldump也可以。匯入可以用load data infile。 十九。mysql日誌1. mysql有四種日誌，分別是錯誤記錄檔，二進位日誌（binlog日誌），查詢日誌和慢查詢日誌。2. 錯誤記錄檔用於記錄資料庫出現的問題，當系統故障的時候首先要檢查錯誤記錄檔。 3. 二進位日誌使用mysqlbinlog來查看，binlog比較大，需要定期刪除。在主從備份的環境中需要確保從庫已經拷貝了binlog，以免備份發生問題。4. 查詢日誌記錄了用戶端的查詢操作，對於訪問頻繁的系統，此日誌對系統效能的影響比較大。一般關掉5. 慢查詢日誌記錄了包含所有執行時間超過參數long_query_time所設定值的sql語句的日誌。 二十。mysql許可權1. mysql的許可權是通過使用者名稱和ip地址來確定的。mysql資料庫裡面的user，host，db表    參考資料：書籍《深入淺出，mysql資料庫開發，最佳化與管理維護》 

Mysql知識匯總筆記