前言

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

1章：使用幫助

使用mysql內置的幫助命令

1. msyql> ? data types : 查看數(shù)據(jù)類型
2. mysql> ? int
3. mysql> ? create table
   

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

最常用的兩種引擎:

1、Myisam是Mysql的默認存儲引擎,當create創(chuàng)建新表時,未指定新表的存儲引擎時,默認使用Myisam。 每個MyISAM 在磁盤上存儲成三個文件。文件名都和表名相同,擴展名分別是 .frm (存儲表定義) 、.MYD (MYData,存儲數(shù)據(jù))、.MYI (MYIndex,存儲索引)。數(shù)據(jù)文件和 索引文件可以放置在不同的目錄,平均分布io,獲得更快的速度。

2、InnoDB 存儲引擎提供了具有提交、回滾和崩潰恢復能力的事務安全。但是對比 Myisam 的存儲引擎,InnoDB 寫的處理效率差一些并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引。

常用環(huán)境:

1、MyISAM: 默認的 MySQL 插件式存儲引擎, 它是在 Web、 數(shù)據(jù)倉儲和其他應用環(huán)境下最常
使用的存儲引擎之一

2、InnoDB:用于事務處理應用程序,具有眾多特性,包括 ACID 事務支持。

3章：選擇合適的數(shù)據(jù)類型

首先選擇合適的存儲引擎，根據(jù)指定的存儲引擎確定合適的數(shù)據(jù)類型。

• MyISAM: 最好使用固定長度的數(shù)據(jù)列代替可變長度的數(shù)據(jù)列。
• InnoDB: 建議使用varchar
  

需要注意的一些數(shù)據(jù)類型：

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

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

3、浮點數(shù)float與定點數(shù)decimal:

注意幾個點：

      1.浮點數(shù)雖然能表示更大的數(shù)據(jù)范圍，但是有誤差問題。

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

      3.編程如果遇到浮點數(shù)，注意誤差問題，盡量避免浮點數(shù)比較（比較浮點數(shù)需要作差小于一個特定精度），python3.5中可以這么比較：float_eq = partial(math.isclose, rel_tol=1e-09, abs_tol=0.0)

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

4章：字符集

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

• 創(chuàng)建數(shù)據(jù)庫使用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.搜索的索引列，不一定是所要選擇的列。最適合的索引的列是出現(xiàn)在where子句中的列，或連接子句中指定的列，而不是出現(xiàn)在select關鍵字之后的選擇列表中的列。

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

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

      4.利用最左前綴。在創(chuàng)建一個n列索引時，實際上創(chuàng)建了mysql可利用的n個索引。多列索引可以起到幾個索引的作用，因為可利用索引中的最左邊的列集來匹配行，這樣的列集成為最左前綴。

      5.不要過度索引。索引會浪費磁盤空間，降低寫入性能。

      6.考慮在列上進行的比較類型。

6章：鎖機制和事務控制

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

7章：SQL中的安全問題

SQL注入：利用某些數(shù)據(jù)庫的外部接口把用戶數(shù)據(jù)插入到實際的數(shù)據(jù)庫操作語音（sql）中，從而達到入侵數(shù)據(jù)庫甚至操作系統(tǒng)的目的。產(chǎn)生原因主要是因為程序堆用戶輸入的數(shù)據(jù)沒有進行嚴格的過濾，導致非法數(shù)據(jù)庫查詢語句的執(zhí)行，防范措施：

1. prepareStatement = Bind-variable，不要使用拼接的sql
2. 使用應用程序提供的轉換函數(shù)
3. 自定義函數(shù)校驗（表單校驗等）
   

8章：SQL Mode及相關問題

更改默認的mysql執(zhí)行模式，比如嚴格模式下列的插入或者更新不正確時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子句做統(tǒng)計
4. 用bit group functions做統(tǒng)計
   

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

數(shù)據(jù)庫名、表名大小寫問題：不同平臺和系統(tǒng)，是否區(qū)分大小寫是不同的。建議就是始終統(tǒng)一使用小寫名。
使用外鍵需要注意的地方：mysql中InnoDB支持對外部關鍵字約束條件的檢查。

11章：SQL優(yōu)化

優(yōu)化SQL的一般步驟：

      1.使用show status和應用特點了解各種SQL的執(zhí)行頻率，了解各種SQL大致的執(zhí)行比例。比如InnoDB的的參數(shù)Innode_rows_read查詢返回的行數(shù)，Innodb_rows_inserted執(zhí)行insert插入的行數(shù)，Innodb_rows_updated更新的行數(shù)。還有一下幾個參數(shù)：Connections試圖連接mysql服務器嗯出書，Uptime服務器的工作時間，Slow_queries慢查詢的次數(shù)。

      2.定位執(zhí)行效率低的SQL語句。兩種方式：一種是通過慢查詢日志定位執(zhí)行效率低的語句,使用—log-slow-queries[=file_name]選項啟動時，mysqld寫一個包含所有執(zhí)行時間超過long_query_time秒的SQL語句的日志文件。另一種是show processlist查看當前mysql在進行的線程，包括線程的狀態(tài)，所否鎖表等，可以實時查看SQL執(zhí)行情況，同時對一些鎖表操作進行優(yōu)化。

      3.通過EXPLAIN分析低效SQL的執(zhí)行計劃：explain可以知道什么時候必須為表假如索引以得到一個使用索引來尋找記錄的更快的SELECT，以下是EXPLAIN執(zhí)行后得到的結果說明：

• select_type: select類型
• table: 輸出結果集的表
• type: 表示表的連接類型。當表中僅有一行是type的值為system是最佳的連接類型；當select操作中使用索引進行表連接時type值為ref；當select的表連接沒有使用索引時，經(jīng)?？吹絫ype的值為ALL，表示對該表進行了全表掃描，這時需要考慮通過創(chuàng)建索引提高表連接效率。
• possible_keys: 表示查詢時，可以使用的索引列。
• key： 表示使用的索引
• key_len: 索引長度
• rows: 掃描范圍
• Extra: 執(zhí)行情況的說明和描述

      4.確定問題，并采取相應優(yōu)化措施。

索引問題

1. 索引的存儲分類: myisam表的數(shù)據(jù)文件和索引文件自動分開，innodb的數(shù)據(jù)和索引放在同一個表空間里面。myisam和innodb的索引存儲類型都是btree
2. Mysql如何使用索引: 索引用于快速查找某個列中特定值的行。查詢要使用索引最主要的條件是要在查詢條件中使用索引關鍵子，如果是多列索引，那么只有查詢條件中使用了多列關鍵字最左邊的前綴時，才可以使用索引，否則將不能使用索引。
3. 查看索引的使用情況：Handler_read_key的值代表一個行被索引次數(shù)，值低表示索引不被經(jīng)常使用。Handler_read_rnd_next值高意味著查詢運行低效，應該建立索引補救。show status like 'Handler_read%';

兩個簡單實用的優(yōu)化方法

• 定期分析表：ANALYZE TABLE, CHECK TABLE, CHECKSUM TABLE
• 使用OPTIMIZE table;
  

從客戶端（代碼端）角度優(yōu)化

1. 使用持久的連接數(shù)據(jù)庫以避免連接開銷。代碼中我們一般使用連接池
2. 檢查所有的插敘確實使用了必要的索引。
3. 避免在頻繁更新的表上執(zhí)行復雜的select查詢，以避免與鎖表有關的由于讀，寫沖突發(fā)生的問題。
4. 充分利用默認值，只有插入值不同于默認值才明確插入值。減少mysql需要做的語法分析從而提高插入速度。
5. 讀寫分離提高性能
6. 表字段盡量不用自增長變量，防止高并發(fā)情況下該字段自增影響效率，推薦通過應用實現(xiàn)字段的自增。

12章: 優(yōu)化數(shù)據(jù)庫對象

優(yōu)化表的數(shù)據(jù)類型：PROCEDURE ANALYZE()對當前表類型的判斷提出優(yōu)化建議。實際可以通過統(tǒng)計信息結合應用實際優(yōu)化。

通過拆分，提高表的訪問效率：這里拆分主要是針對Myisam類型的表。

• 縱向拆分：按照應用訪問的頻度，將表中經(jīng)常訪問的字段和不經(jīng)常訪問的字段拆分成兩個表，經(jīng)常訪問的字段盡量是定長的。
• 橫向拆分：按照應用情況，有目的地將數(shù)據(jù)橫向拆分成幾個表或者通過分區(qū)分到多個分區(qū)中，這樣可以有效避免Myisam表的讀取和更新導致的鎖問題。
  

逆規(guī)范化：規(guī)范化設計強調獨立性，數(shù)據(jù)盡可能少冗余，更多冗余意味著占用更多物理空間，同事也對數(shù)據(jù)維護和一致性檢查帶來問題。適當冗余可以減少多表訪問，查詢效率明顯提高，這種情況可以考慮適當通過冗余提高效率。

使用冗余統(tǒng)計表：使用create temporary table做統(tǒng)計分析

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

13章：鎖問題

獲取鎖的等待情況：table_locks_waited和table_locks_immediate狀態(tài)變量來分析系統(tǒng)上的表鎖定爭奪。檢查Innode_row_lock分析行鎖的爭奪情況。

14章：優(yōu)化Mysql Server

查看Mysql Server當前參數(shù)

1. 查看服務器參數(shù)默認值：mysqld --verbose --help
2. 查看服務器參數(shù)實際值：shell> mysqladmin variables or mysql> SHOW VARIABLES
3. 查看服務器運行狀態(tài)值：mysqladmin extended-status or mysql>SHOW STATUS
   

影響Mysql性能的重要參數(shù)

1. key_buffer_size: 鍵緩存
2. table_cache: 數(shù)據(jù)庫中打開的緩存數(shù)量
3. innode_buffer_pool_size: 緩存InnoDB數(shù)據(jù)和索引的內存緩沖區(qū)的大小
4. innodb_flush_log_at_trx_commit: 推薦設成1，在每個事務提交時，日志緩沖被寫到日志文件，對日志文件做到磁盤操作的刷新。

15章：I/O問題

磁盤搜索是巨大的性能瓶頸。

1. 使用磁盤陣列或虛擬文件卷分布I/O
2. 使用Symbolic Links分布I/O
   

16章：應用優(yōu)化

1. 使用連接池：建立連接代價比較高，通過建立連接池提高訪問性能。
2. 減少對Mysql的訪問：1.避免對同意數(shù)據(jù)重復檢索。2使用mysql query cache
3. 增加cache層
4. 負載均衡：1.利用mysql復制分流查詢操作。2分布式數(shù)據(jù)庫架構

總結

以上就是關于mysql的相關內容，希望本文的內容對大家學習或者使用mysql能帶來一定的幫助，如果有疑問大家可以留言交流。

最新評論