深入淺出的學習Mysql（收藏）

前言

資料庫一直是筆者比較薄弱的地方，結合自己的使用經驗(python+sqlalchemy)等做個記錄，筆者比較喜歡使用ORM，一直覺得拼sql是一件比較痛苦的事情(主要是不擅長sql)，以前維護項的目中也遇到過一些資料庫的坑，比如編碼問題，浮點數精度損失等，防止以後重複踩坑。

1章：使用幫助

#使用mysql內建的說明指令

1. msyql> ? data types : 查看数据类型
   mysql> ? int
   mysql> ? create table

2章：表格類型（儲存引擎）的選擇

#最常用的兩種引擎:

1、Myisam是Mysql的預設儲存引擎,當create建立新表時,未指定新表的儲存引擎時,預設使用Myisam。 每個MyISAM 在磁碟上儲存成三個檔案。檔案名稱皆與表名相同,副檔名分別是 .frm (儲存表定義) 、.MYD (MYData,儲存資料)、.MYI (MYIndex,儲存索引)。資料檔案和 索引檔案可以放置在不同的目錄,平均分佈io,獲得更快的速度。

2、InnoDB 儲存引擎提供了具有提交、回溯和崩潰復原能力的交易安全性。但是比較 Myisam 的儲存引擎,InnoDB 寫的處理效率差一些並且會佔用更多的磁碟空間以保留資料和索引。

常用環境:

1、MyISAM: 預設的MySQL 外掛程式儲存引擎, 它是在Web、 資料倉儲和其他應用程式環境下最常
使用的儲存引擎之一

2、InnoDB:用於事務處理應用程式,具有眾多特性,包括ACID 事務支援。

3章：選擇適當的資料類型

#先選擇適當的儲存引擎，依照指定的儲存引擎確定合適的資料類型。

• MyISAM: 最好使用固定長度的資料列來取代可變長度的資料列。

• InnoDB: 建議使用varchar
  

#需要注意的一些資料類型：

1、char與varchar: 儲存和檢索方式不同，最大長度和是否尾部空格保留也不同。 char固定長度，長度不夠用空格填充，取得時如果沒有設定 PAD_CHAR_TO_FULL_LENGTH預設移除尾部空格。
varchar變長字串，檢索時尾部空格會被保留。注意查詢時候不區分大小寫，如果用sqlalchemy區分大小寫不要用func.binary函數。

2、text和blob: text和blob執行大量的更新或刪除的時候會留下很大『空洞』，建議定期用OPTIMIZE TABLE功能對這類表碎片整理。避免檢索大型的blob或text值 。把text和blob列分離到單獨的表格中。

3、浮點數float與定點數decimal:

注意幾個點：

      1.浮點數雖然能表示更大的數據範圍，但是有誤差問題。

      2.對貨幣等精確度敏感的問題，應使用定點數儲存。之前項目踩過坑，結果不得不用放大縮小倍數的方法解決，比較ugly。

      3.程式設計若遇到浮點數，注意誤差問題，盡量避免浮點數比較（比較浮點數需作差小於特定精確度），python3.5中可以這樣比較：float_eq = partial(math.isclose, rel_tol=1e-09, abs_tol=0.0)

      4.注意浮點數中一些特殊值的處理。

4章：字元集

#一開始要選擇適當的字元集，否則後期更換代價很高。 python2中字符集就是個老大難問題，困然很多新手。之前維護過的項目使用了msyql預設的latin1字元集，導致每次寫入的時候都要對字串手動encode成utf8。最近用python3.5+flask做專案直接使用utf8，再也沒碰到過編碼問題：

• 建立資料庫使用utf8，CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;

• sqlalchemy連接url使用mysql://root:root@127.0.0.1:3306/my_db?charset=utf8。不用擔心亂碼問題了
  

#5章：索引的設計與使用

#

所有mysql列類型都可以被索引，對相關列使用索引是提高select操作效能的最佳途徑。索引設計的原則:

      1.搜尋的索引列，不一定是所要選擇的欄位。最適合的索引的列是出現在where子句中的列，或連接子句中指定的列，而不是出現在select關鍵字之後的選擇清單中的列。

      2.使用唯一指標。對於唯一值的列，索引效果較好，而有多個重複值的列，索引效果差。

      3.使用短指標。如果對字串列進行索引，應指定一個前綴長度，只要有可能就應該這樣做。

      4.使用最左字首。在建立n列索引時，實際上建立了mysql可利用的n個索引。多列索引可以起到幾個索引的作用，因為可利用索引中的最左邊的列集來匹配行，這樣的列集成為最左前綴。

      5.請勿過度指標。索引會浪費磁碟空間，降低寫入效能。

      6.考慮列上所進行的比較型別。

6章：鎖定機制與交易控制

#InnoDB引擎提提供行級鎖定，支援共用鎖定和排他鎖兩種鎖定模式，以及四種不同的隔離等級。 mysql透過AUTOCOMIT, START TRANSACTIONS, COMMIT和ROLLBACK等語句支援本地事務。

7章節：SQL中的安全性問題

#SQL注入：利用某些資料庫的外部介面把使用者資料插入到實際的資料庫操作語音（sql）中，從而達到入侵資料庫甚至作業系統的目的。產生原因主要是因為程式堆使用者輸入的資料沒有嚴格的過濾，導致非法資料庫查詢語句的執行，防範措施：

1. prepareStatement = Bind- variable，不要使用拼接的sql

2. 使用應用程式提供的轉換函數

3. 自訂函數校驗（表單校驗等等）
   

8章：SQL Mode及相關問題

##更改預設的mysql執行模式，例如嚴格模式下列的插入或更新不正確時mysql會給予錯誤，並放棄操作。

set session sql_mode='STRICT_TRANS_TABLES'。設定sql_mode需要應用程式人員來權衡各種得失，做一個合適的選擇。

9章：常用SQL技巧

1. #擷取包含最大/最小值的行：

   MAX([DISTINCE] expr), MIN([DISTINCE] expr)

2. 巧用

   rand()/rand(n)提取隨機行

3. 利用

   group by和with rollup子句做統計

4. 用

   bit group functions做統計
   

#10章：其他需要注意的問題

資料庫名稱、表名大小寫問題：不同平台和系統，是否區分大小寫是不同的。建議就是始終統一使用小寫名。

使用外鍵要注意的地方：mysql中InnoDB支援對外部關鍵字約束條件的檢查。

11章：SQL最佳化

最佳化SQL的一般步驟：

      1.使用show status和應用特徵了解各種SQL的執行頻率，並了解各種SQL大致的執行比例。例如InnoDB的的參數Innode_rows_read查詢傳回的行數，Innodb_rows_inserted執行insert插入的行數，Innodb_rows_updated更新的行數。還有幾個參數：Connections試著連接mysql伺服器嗯出書，Uptime伺服器的工作時間，Slow_queries慢查詢的次數。

      2.定位執行效率較低的SQL語句。兩種方式：一種是透過慢查詢日誌定位執行效率低的語句,使用—log-slow-queries[=file_name]選項啟動時，mysqld寫一個包含所有執行時間超過long_query_time秒的SQL語句的日誌檔案。另一種是show processlist查看目前mysql在進行的線程，包括線程的

狀態，所否鎖定表等，可以即時查看SQL執行情況，同時對一些鎖定表操作進行最佳化。

      3.透過EXPLAIN分析低效率SQL的執行計畫：explain可以知道何時必須為表假如索引以獲得一個使用索引來尋找記錄的更快的SELECT，以下是EXPLAIN執行後得到的結果說明：

• select_type: select類型

• table: 輸出結果集的表

• type: 表示表的連接類型。當表中僅有一行是type的值為system是最佳的連接類型；當select操作中使用索引進行表連接時type值為ref；當select的表連接沒有使用索引時，經常看到type的值為ALL，表示對該表進行了全表掃描，這時需要考慮透過建立索引來提高表連接效率。

• possible_keys: 表示查詢時，可以使用的索引列。

• key: 表示使用的索引

• key_len: 索引長度

• rows: 掃描範圍

• Extra:執行情況的說明與描述

• 
  

     4.確定問題，並採取相應優化措施。

索引問題

1. 索引的儲存分類: myisam表的資料檔案和索引檔案自動分開，innodb的資料和索引放在同一個表空間裡面。 myisam和innodb的索引儲存類型都是btree

2. Mysql如何使用索引: 索引用於快速尋找某個欄位中特定值的行。查詢要使用索引最主要的條件是要在查詢條件中使用索引關鍵子，如果是多列索引，那麼只有查詢條件中使用了多列關鍵字最左邊的前綴時，才可以使用索引，否則將不能使用索引。

3. 檢視索引的使用：Handler_read_key的值代表一行被索引次數，值低表示索引不被常用。 Handler_read_rnd_next值高表示查詢運行低效，應該建立索引補救。 show status like 'Handler_read%';

#兩個簡單實用的最佳化方法

• # #定期分析表：ANALYZE TABLE, CHECK TABLE, CHECKSUM TABLE

• 使用OPTIMIZE table;

  
  

從客戶端（代碼端）角度優化

1. 使用持久的

   連接資料庫以避免連接開銷。程式碼中我們一般使用連線池

2. 檢查所有的插敘確實使用了必要的索引。

3. 避免在頻繁更新的表上執行複雜的select查詢，以避免與鎖定表有關的由於讀取，寫入衝突發生的問題。

4. 充分利用預設值，只有插入值不同於預設值才明確插入值。減少mysql需要做的語法分析從而提高插入速度。

5. 讀寫分離提高效能

6. 表格字段盡量不用自增長

   變數，防止高並發情況下該字段自增影響效率，推薦透過應用實現欄位的自增。

12 章: 最佳化資料庫物件

優化表的資料類型：

PROCEDURE ANALYZE()對目前表格類型的判斷提出最佳化建議。實際可以透過統計資訊結合應用實際優化。

透過拆分，提高表格的存取效率：這裡拆分主要是針對Myisam類型的表。

• 縱向分割：依照應用程式存取的頻度，將表中經常存取的欄位和不常存取的欄位分割成兩個表，經常存取的欄位盡量是定長的。

• 橫向分割：依照應用情況，有目的地將資料橫向分割成幾個表格或透過分割區分到多個分割區中，這樣可以有效避免Myisam表的讀取取和更新導致的鎖定問題。

  
  

逆規範化：標準化設計強調獨立性，資料盡可能少冗餘，更多冗餘意味著佔用更多實體空間，同事也對資料維護和一致性檢查帶來問題。適當冗餘可以減少多表訪問，查詢效率明顯提高，這種情況可以考慮適當地透過冗餘來提高效率。

使用冗餘統計表：使用

create temporary table做統計分析

選擇更適合的表格類型：1.如果應用出現比較嚴重的鎖衝突，請考慮是否刻意更改儲存引擎到InnoDB,行鎖定機制可以有效減少鎖定衝突出現。 2.如果應用查詢操作很多，且對事務完整性要求不嚴格，可以考慮使用Myisam。

13章：鎖問題

#取得鎖的等待情況：table_locks_waited和table_locks_immediate狀態變數來分析系統上的表鎖定爭奪。檢查Innode_row_lock分析行鎖的爭奪情況。

14章節：最佳化Mysql Server

#查看Mysql Server目前參數

1. 查看伺服器參數預設值：

   mysqld --verbose --help

2. View the actual values ​​of the server parameters: shell> mysqladmin variables or mysql> SHOW VARIABLES

3. View the server running status values: mysqladmin extended-status or mysql>SHOW STATUS
   

##Important parameters that affect Mysql performance

1. key_buffer_size: key

   Cache

2. table_cache: The number of caches opened in the database

3. innode_buffer_pool_size: The memory buffer for caching InnoDB data and indexes Size

4. innodb_flush_log_at_trx_commit: It is recommended to set it to 1. When each transaction is committed, the log buffer is written to the log file, and the log file is refreshed by disk operations.

Chapter 15: I/O Issues

Disk search is a huge performance bottleneck.

1. Use disk arrays or virtual file volumes to distribute I/O

2. Use Symbolic Links to distribute I/O

   
   

Chapter 16: Application Optimization

1. Use connection pool: The cost of establishing a connection is relatively high. Establish a connection pool to improve access performance.

2. Reduce access to Mysql: 1. Avoid repeated retrieval of agreed data. 2 Use mysql query cache

3. Add cache layer

4. Load balancing: 1. Use mysql to copy and offload query operations. 2Distributed database

   Architecture

Summary

以上是深入淺出的學習Mysql（收藏）的詳細內容。更多資訊請關注PHP中文網其他相關文章！