深入淺出的學(xué)習(xí)Mysql

前言

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

1章：使用幫助

使用mysql內(nèi)置的幫助命令

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

2章：表類型（存儲(chǔ)引擎）的選擇

最常用的兩種引擎:

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

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

常用環(huán)境:

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

2、InnoDB:用于事務(wù)處理應(yīng)用程序,具有眾多特性,包括 ACID 事務(wù)支持。

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

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

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

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

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

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

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

注意幾個(gè)點(diǎn)：

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

      2.對(duì)貨幣等精度敏感的問(wèn)題，應(yīng)使用定點(diǎn)數(shù)存儲(chǔ)。之前項(xiàng)目踩過(guò)坑，結(jié)果不得不用放大和縮小倍數(shù)的方法解決，比較ugly。

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

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

4章：字符集

一開(kāi)始要選擇合適的字符集，否則后期更換代價(jià)很高。python2中字符集就是個(gè)老大難問(wèn)題，困然很多新手。之前維護(hù)過(guò)的項(xiàng)目使用了msyql默認(rèn)的latin1字符集，導(dǎo)致每次寫(xiě)入的時(shí)候都要對(duì)字符串手動(dòng)encode成utf8。最近用python3.5+flask做項(xiàng)目直接使用utf8，再也沒(méi)碰到過(guò)編碼問(wèn)題：

• 創(chuàng)建數(shù)據(jù)庫(kù)使用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。不用擔(dān)心亂碼問(wèn)題了
  

5章：索引的設(shè)計(jì)和使用

所有mysql列類型都可以被索引，對(duì)相關(guān)列使用索引是提高select操作性能的最佳途徑。索引設(shè)計(jì)的原則:

      1.搜索的索引列，不一定是所要選擇的列。最適合的索引的列是出現(xiàn)在where子句中的列，或連接子句中指定的列，而不是出現(xiàn)在select關(guān)鍵字之后的選擇列表中的列。

      2.使用唯一索引。對(duì)于唯一值的列，索引效果較好，而有多個(gè)重復(fù)值的列，索引效果差。

      3.使用短索引。如果對(duì)字符串列進(jìn)行索引，應(yīng)指定一個(gè)前綴長(zhǎng)度，只要有可能就應(yīng)該這樣做。

      4.利用最左前綴。在創(chuàng)建一個(gè)n列索引時(shí)，實(shí)際上創(chuàng)建了mysql可利用的n個(gè)索引。多列索引可以起到幾個(gè)索引的作用，因?yàn)榭衫盟饕械淖钭筮叺牧屑瘉?lái)匹配行，這樣的列集成為最左前綴。

      5.不要過(guò)度索引。索引會(huì)浪費(fèi)磁盤(pán)空間，降低寫(xiě)入性能。

      6.考慮在列上進(jìn)行的比較類型。

6章：鎖機(jī)制和事務(wù)控制

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

7章：SQL中的安全問(wèn)題

SQL注入：利用某些數(shù)據(jù)庫(kù)的外部接口把用戶數(shù)據(jù)插入到實(shí)際的數(shù)據(jù)庫(kù)操作語(yǔ)音（sql）中，從而達(dá)到入侵?jǐn)?shù)據(jù)庫(kù)甚至操作系統(tǒng)的目的。產(chǎn)生原因主要是因?yàn)槌绦蚨延脩糨斎氲臄?shù)據(jù)沒(méi)有進(jìn)行嚴(yán)格的過(guò)濾，導(dǎo)致非法數(shù)據(jù)庫(kù)查詢語(yǔ)句的執(zhí)行，防范措施：

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

8章：SQL Mode及相關(guān)問(wèn)題

更改默認(rèn)的mysql執(zhí)行模式，比如嚴(yán)格模式下列的插入或者更新不正確時(shí)mysql會(huì)給出錯(cuò)誤，并放棄操作。set session sql_mode='STRICT_TRANS_TABLES'。設(shè)置sql_mode需要應(yīng)用人員權(quán)衡各種得失，做一個(gè)合適的選擇。

9章：常用SQL技巧

1. 檢索包含最大/最小值的行：MAX([DISTINCE] expr), MIN([DISTINCE] expr)
2. 巧用rand()/rand(n)提取隨機(jī)行
3. 利用group by和with rollup子句做統(tǒng)計(jì)
4. 用bit group functions做統(tǒng)計(jì)
   

10章：其他需要注意的問(wèn)題

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

11章：SQL優(yōu)化

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

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

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

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

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

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

索引問(wèn)題

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

兩個(gè)簡(jiǎn)單實(shí)用的優(yōu)化方法

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

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

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

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

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

通過(guò)拆分，提高表的訪問(wèn)效率：這里拆分主要是針對(duì)Myisam類型的表。

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

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

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

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

13章：鎖問(wèn)題

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

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

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

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

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

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

15章：I/O問(wèn)題

磁盤(pán)搜索是巨大的性能瓶頸。

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

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

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

總結(jié)

以上就是關(guān)于mysql的相關(guān)內(nèi)容，希望本文的內(nèi)容對(duì)大家學(xué)習(xí)或者使用mysql能帶來(lái)一定的幫助，如果有疑問(wèn)大家可以留言交流。