摘抄 mysql 相關資訊

標籤：

　　1,Myisam 在磁碟上，將資料存放區為3個檔案。第一個是表結構檔案，它的名字以表的名字開始，為.frm檔案；第二個檔案是資料檔案，副檔名為.MYD；第三個是索引檔案，副檔名為.MYI。
Myisam儲存引擎最大的特點是表級鎖、不支援事務和全文索引，適合一些cms內容管理系統作為後台資料庫使用，但是在大並發，重負荷生產系統上，表鎖的特性顯得力不從心。
　　2，InnoDb 提供了提交，復原，崩潰恢複能力的事務安全（ACID相容）儲存引擎。行鎖。支援外鍵（FOREIGN KEY）. 為處理巨大資料量時擁有最大效能設計的。它的CPU效率可能是其他基於磁碟的關係型資料庫引擎不能匹配的。InnoDB在一個資料表空間中儲存它的表和索引。InnoDB表可以是任何尺寸的，即使在檔案尺寸限制為2GB 的作業系統上。
　　3，Myisam 表級鎖，加鎖塊；不會出現死結；鎖定粒度大，發生鎖衝突的機率最高，並發度最低。
　　　  innodb 行級鎖，加鎖慢；會出現死結；鎖定粒度最小，發生鎖的衝突機率最低，並發度最高。
　　4，對myisam表的讀操作（加讀鎖 lock table tablename read;），不會堵塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放後，才會執行其他進程的寫操作。
　　對myisam的寫操作（加寫鎖），會阻塞其他今天對同一表的讀和寫操作，只有當寫鎖釋放後，才會執行其他進程的讀寫操作。
　　5，innodb 儲存引擎室通過給索引上的索引項目加鎖來實現的，這就意味著：只有通過索引條件檢索資料，innodb才會使用行級鎖，否則，使用表鎖。

myisam與innodb之間的主要區別：
　　Myisam是非事務安全型的，而innodb是事務安全型的，也就是ACID事務支援；
　　myisam是表級鎖，鎖開銷最小，而innodb支援行級鎖定，鎖管理開銷大，支援更好的並發寫操作；
　　myisam支援全文索引，而innodb不支援全文索引，但在最新的5.6版本中已提供支援；
　　myisam相對簡單，管理方便，因此在效率上要優於innodb，小型應用可以考慮使用myisam；
　　myisam表式儲存成檔案的形式，在跨平台的資料轉移中使用myisam儲存會省不少的麻煩；innodb採用資料表空間來管理資料；
　　innodb表比myisam表更安全，可以保證在資料不丟失的情況下，切換非事務表到事務表；

　　Myisam 儲存引擎的讀鎖跟寫鎖是互斥的，讀寫操作室串列的。試想一下，一個進程請求某個myisam表的讀鎖，同時另一個進程也請求同一張表的寫鎖，mysql該如何處理呢？答案是 寫進程先獲得鎖；不僅如此，即使讀請求先到鎖等待隊列，寫請求後到，寫鎖也會插到讀鎖請求之前；因為mysql認為寫請求一般要比讀請求重要。這也是Myisam表不適合有大量更新操作和查詢操作應用的原因。因為大量的更新操作會造成查詢操作很難獲得讀鎖。從而可能永遠堵塞。
　　Innodb 用於交易處理應用程式，具有眾多特性，包括支援ACID事務、行鎖等。如果應用中需要執行大量讀寫操作，則應該使用innodb，這樣可以提高多使用者並行作業的效能。對應myiam引擎，在mysql5.5 版本裡，oracle公司支援的已經很少，以後記憶體資料庫是一種趨勢，所以建議優先選擇innodb引擎。

開啟慢日誌：
　　在my.cnf設定檔裡面，加入以下參數：
　　slow_query_log = 1
　　slow_query_log_file = mysql.slow
　　long_query_time = 2;//2s以上的sql被記錄
　　使用 mysqldumpslow 命令取出好事最長的前10條慢sql
　　mysqldumpslow -s t -t 10 slow.log

sql語句最佳化：
　　1，盡量避免使用子查詢，改用連表
　　2，like“%xxx%” 是無法使用索引的
　　　　a句：select count(1) from table where name like ‘%xxx%‘;
　　　　b句：select count(1) from table table_alias join
　　　　　　(select key from table where name like ‘%xxx%‘) table_alias_2 on a.key = b.key
　　　　b句比a句效率高。
　　3，limit 分頁最佳化
　　　　a句：select * from tablename limit 99999,10;
　　　　b句：select * from tablename order by key limit 99999,10;
　　　　c句：select * from tablename where key > 999999 order by key limit 99999,10;
　　　　c > b > a;

　　　　a句：select * from tablename order by createdata asc limit 332344,10;
　　　　　　最佳化思路：先取出332344行後面的一條記錄id，然後採用內串連的方法取出10條
　　　　b句：select a.* from tablename a
　　　　　　　　join
　　　　　　(select id from tablename order by createdata acs limit 332344,1) b
　　　　　　　　on
　　　　　　a.id >= b.id
　　　　　　limit 10;
　　4，or 條件陳述式最佳化
　　　　tmp 表 有2個欄位，name，age都加入了索引
　　　　　　select * from tmp where age = 43 or name = ‘xxx‘;
　　　　是不會使用到任何索引的，最佳化（採用 union all）：
　　　　select * from tmp where age = 43
　　　　　　union all
　　　　select * from tmp where name = ‘xxx‘;
　　5，where 條件後的欄位使用sql函數後，無法使用索引
　　6，using filesort 的最佳化方法，就是該排序欄位，加入聯合索引，如果只是單獨的一個欄位排序，那麼將這個欄位跟主鍵建立聯合索引；如果有其他的條件，那麼跟那個條件一起建立聯合索引。（order by ;group by ;）

my.cnf 最佳化
　　對應 per_thread_buffers,可以將其理解為Oracal的PGA，為每個串連到mysql的使用者指派的記憶體，其包含如下幾個參數：
　　　　1，read_buffer_size

　　　　　　該參數用於表的順序掃描，表示每個線程分配的緩衝區大小。比如，在進行全表掃描時，Mysql會按照資料的儲存順序依次讀取資料區塊，每次讀取的資料區塊首先會暫存在read_buffer_size中，當buffer空間被寫滿或者全部資料讀取完畢後，再將buffer中的資料返回給上層調用者，以提高效率。預設 128kb，這個參數不要設定過大，一般在128kb-256kb

　　　　2,read_rnd_buffer_size

　　　　　　該參數用於表的隨機讀取，表示每個線程分配的緩衝區大小。比如，按照一個停用字詞段做order by 排序操作時，就會利用這個緩衝區來暫存讀取的資料，預設256kb,這個參數不要設定過大，一般在128-256kb
　　　　3,sort_buffer_size

　　　　　　在表進行order by ;group by 排序操作時，由於排序的欄位沒有索引，會出現using filesort,為了提高效能，可用此參數增加每個線程分配的緩衝區大小，預設2Mb，這個參數不要設定過大，一般在128-256kb即可。另外，一般出現using filesort的時候，要通過增加索引解決。
　　　　4，thread_stack
　　　　　　該參數表示每個線程的堆棧大小，預設 192kb,如果是 64位作業系統，設定為 256kb，不要過大。
　　　　5，join_buffer_size
　　　　　　表進行join串連操作時，如果關聯的欄位沒有索引，會出現 Using join buffer，為了提高效能，可用此參數增加每個線程分配的緩衝區大小，預設128kb，不要過大，一般在128-256kb之間。一般出現 Using join buffer，要用增加索引來解決
　　　　6，binlog_cache_size
　　　　　　一般來說，如果資料庫中沒有什麼大事務，寫入也不是特別頻繁，將其設定為1-2mb,是一個合適的選擇。如果有很大的事務，可用適當增加這個緩衝值。
　　　　7，max_connectons
　　　　　　最大串連數，預設100，一般設定為 521-1000即可。

　　global_buffers最佳化：
　　　　　對應gblobal_buffers，可用理解為Oracle的SGA，用於記憶體中緩衝從資料檔案中檢索出來的資料區塊，可用大大提高查詢和更新資料的效能。主要參數如下：
　　　　1，innodb_buffer_pool_size
　　　　　　這個參數是innodb儲存引擎的核心參數，預設128MB，這個參數要設定為實體記憶體的60%-70%；
　　　　2，innodb_additional_mem_pool_size
　　　　　　該參數用來儲存資料字典資訊和其他內部資料結構。表越多，需要在這裡分配的記憶體越多。如果Innodb用光了這個池內的記憶體。Innodb開始從作業系統分配記憶體，並且王mysql錯誤記錄檔中寫警告資訊。預設為8M，一般設定為16M即可
　　　　3，innodb_log_buffer_size
　　　　　　交易記錄使用的緩衝區。Innodb再寫交易記錄的時候，為了提高效能，先將資訊寫入 innodb log buffer 中，當滿足innodb_flush_log_trx_commit參數鎖設定的相應條件時，再將日誌寫入檔案中。預設為8MB,一般設定為 16-64MB。
　　　　4，key_buffer_size
　　　　　　該參數用來緩衝myisam儲存引擎的索引參數。mysql5.5中，預設為innodb儲存引擎，所以這個參數可用設定小寫，64M即可
　　　　5，query_cache_siez
　　　　　　緩衝select語句和結果集大小的參數
　　注意，per_thread_buffers 記憶體設定 + global_buffers 設定不能大於實際實體記憶體，否則並發量很高時，會造成內容溢出，系統死機。

 

摘抄 mysql 相關資訊