中文字幕一区二区三区四区,欧美日韩亚洲视频一区,中文精品久久

前言

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

1章：使用幫助

使用mysql內置的幫助命令

msyql> ? data types : 查看數(shù)據(jù)類型
mysql> ? int
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)的目的。產生原因主要是因為程序堆用戶輸入的數(shù)據(jù)沒有進行嚴格的過濾，導致非法數(shù)據(jù)庫查詢語句的執(zhí)行，防范措施：

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

8章：SQL Mode及相關問題

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

9章：常用SQL技巧

檢索包含最大/最小值的行：MAX([DISTINCE] expr), MIN([DISTINCE] expr)
巧用rand()/rand(n)提取隨機行
利用group by和with rollup子句做統(tǒng)計
用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的表連接沒有使用索引時，經?？吹絫ype的值為ALL，表示對該表進行了全表掃描，這時需要考慮通過創(chuàng)建索引提高表連接效率。
possible_keys: 表示查詢時，可以使用的索引列。
key：表示使用的索引
key_len: 索引長度
rows: 掃描范圍
Extra: 執(zhí)行情況的說明和描述

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

索引問題

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

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