前言
資料庫一直是筆者比較薄弱的地方,結合自己的使用經驗(python+sqlalchemy)等做個記錄,筆者比較喜歡使用ORM,一直覺得拼sql是一件比較痛苦的事情(主要是不擅長sql),以前維護項的目中也遇到過一些資料庫的坑,比如編碼問題,浮點數精度損失等,防止以後重複踩坑。
1章:使用幫助
#使用mysql內建的說明指令
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)中,從而達到入侵資料庫甚至作業系統的目的。產生原因主要是因為程式堆使用者輸入的資料沒有嚴格的過濾,導致非法資料庫查詢語句的執行,防範措施:
prepareStatement = Bind- variable
,不要使用拼接的sql
使用應用程式提供的轉換函數
自訂函數校驗(表單校驗等等)
8章:SQL Mode及相關問題
set session sql_mode='STRICT_TRANS_TABLES'。設定sql_mode需要應用程式人員來權衡各種得失,做一個合適的選擇。
9章:常用SQL技巧
MAX([DISTINCE] expr), MIN([DISTINCE] expr)
rand()/rand(n)提取隨機行
group by和
with rollup子句做統計
bit group functions做統計
#10章:其他需要注意的問題
使用外鍵要注意的地方:mysql中InnoDB支援對外部關鍵字約束條件的檢查。
11章:SQL最佳化
狀態,所否鎖定表等,可以即時查看SQL執行情況,同時對一些鎖定表操作進行最佳化。
select_type: select類型
table: 輸出結果集的表
type: 表示表的連接類型。當表中僅有一行是type的值為system是最佳的連接類型;當select操作中使用索引進行表連接時type值為ref;當select的表連接沒有使用索引時,經常看到type的值為ALL,表示對該表進行了全表掃描,這時需要考慮透過建立索引來提高表連接效率。
possible_keys: 表示查詢時,可以使用的索引列。
key: 表示使用的索引
key_len: 索引長度
rows: 掃描範圍
Extra:執行情況的說明與描述
4.確定問題,並採取相應優化措施。
索引問題
索引的儲存分類: myisam表的資料檔案和索引檔案自動分開,innodb的資料和索引放在同一個表空間裡面。 myisam和innodb的索引儲存類型都是btree
Mysql如何使用索引: 索引用於快速尋找某個欄位中特定值的行。查詢要使用索引最主要的條件是要在查詢條件中使用索引關鍵子,如果是多列索引,那麼只有查詢條件中使用了多列關鍵字最左邊的前綴時,才可以使用索引,否則將不能使用索引。
檢視索引的使用:Handler_read_key的值代表一行被索引次數,值低表示索引不被常用。 Handler_read_rnd_next值高表示查詢運行低效,應該建立索引補救。 show status like 'Handler_read%';
#兩個簡單實用的最佳化方法
從客戶端(代碼端)角度優化
PROCEDURE ANALYZE()對目前表格類型的判斷提出最佳化建議。實際可以透過統計資訊結合應用實際優化。
create temporary table做統計分析
13章:鎖問題
14章節:最佳化Mysql Server
mysqld --verbose --help
View the actual values of the server parameters: shell> mysqladmin variables or mysql> SHOW VARIABLES
View the server running status values: mysqladmin extended-status or mysql>SHOW STATUS
Chapter 15: I/O Issues
Chapter 16: Application Optimization
Summary
以上是深入淺出的學習Mysql(收藏)的詳細內容。更多資訊請關注PHP中文網其他相關文章!