MySQL學習筆記(一)表類型的選擇
啊感覺自己真是到處挖坑,之前說的SSM做的項目還沒寫完,Hibernate和Java SE的更是遙遙無期,還有一個JVM在等著我,這裡又要開一個MYSQL的坑。
SSM和Hibernate的可能暫時不會寫了,因為我工程實踐是用的HIbernate,目前還沒有別的問題,而且我感覺自學框架來的效率太低,自己花了一兩天搞明白的東西,到了公司里學,可能就5分鐘。圖什麼呢。會用基本的就行了,不想深究,想多看一些底層的東西了。
本系列參考書籍《深入淺出MySQL 資料庫開發、優化與管理維護(第二版)》,這本書講的很淺,我打算草草翻一翻,然後找一本深入一點的書,再回顧一下課上的知識。
應該還會有一個回看系列。
和大多數資料庫不同,MySQL中有一個存儲引擎的概念,針對不同的存儲需求可以選擇最優的存儲引擎。
MySQL5.0支持的存儲引擎包括MyISAM、InnoDB,BDB,MEMORY,MERGE,等等,其中,InnoDB和BDB提供事務安全表,其他存儲引擎都是非事務安全表。在MySQL5.5之前默認的存儲引擎是MyISAM,之後的改成了InnoDB。可以使用以下命令查看當前默認引擎:
mysql> show ENGINES Gn
在創建表的時候,我們就可以選擇引擎,比如:
mysql> create table MyISAMTable(n -> i bigint(20) not null auto_increment,n -> primary key(i)n -> )engine=myisam default charset=utf8;nQuery OK, 0 rows affected (0.02 sec)n
也可以修改一個表的現有引擎
alter table MyISAMTable engine = innodb;n
MyISAM
發音為 "my-z[ei]m";
不支持事務,不支持外鍵,突出一個快。
每個MyISAM表,在磁碟上存儲3個文件,文件名和表名相同,但擴展名分別是
.fm 存儲表定義
.MYD 存儲數據
.MYI 存數索引
可以在創建表的時候通過DATA DIRECTORY 、INDEX DIRECTORY語句指定,可以不同。路徑需要是絕對路徑,而且需要有訪問許可權。
MyISAM表支持3中不同的存儲格式,分別是:
靜態(固定長度)表;
動態表;
壓縮表;
靜態是默認的,長度不足的會在後面補足空格(這裡與書上剛好相反),超過的後面被截斷。而且空格將會被保留。(也與書上不一致。)
insert into myisamtable values(abcd),(abcdef),( abc );n
結果為:
mysql> select * from myisamtable;n+---+-------+n| i | name |n+---+-------+n| 1 | abcd |n| 2 | abcde |n| 3 | abc |n+---+-------+n3 rows in set (0.00 sec)n
InnoDB
提供了事務安全,具有提交、回滾、崩潰恢復能力。相對於MyISAM,寫的效率差一些,並會佔用更多的磁碟空間以保留數據和索引。
1,自動增長列
自動增長列可以手工插入,但如果是空或者0,則實際插入的將是自動增漲後的值。
可以通過
ALTER TABLE tableName AUTO_INCREMENT=n;
來設定自動增長的初始值。此時插入數據,如下:
mysql> select * from innodb_t;n+------+------+------+n| id | d1 | d2 |n+------+------+------+n| 1000 | 1 | NULL |n| 1001 | 2 | NULL |n| 1002 | 3 | NULL |n+------+------+------+n3 rows in set (0.00 sec)n
但這個設置是保存在內存中的,如果在這個設置被使用前,資料庫重新啟動,則需要再設置一次。
可以使用函數LAST_INSERT_ID()來查詢當前線程最後插入記錄使用的值。如果一次插入多條記錄,那麼返回的是第一條記錄使用的自動增長值。本例中,我使用INSERT INTO innodb_t (id1) values (1), (2), (3);插入數據,再執行select LAST_INSERT_ID(),返回的就是1000,而非1002.
在InnoDB中,自動增長列必須是索引,如果是組合索引,則必須是組合索引的第一列。但對於MyISAM表,自動增長可以使組合索引的其他列。
2,外鍵約束
這是InnoDB獨有的。在創建外鍵時,要求父表必須由對應的索引,字表在創建外鍵時也會自動創建對應的索引。
在創建這種約束時,有幾種策略,可以指定在刪除、更新父表時,對字表進行的操作。包括RESTRICT,CASCADE,SET NULL、NO ACTION。
其中RESTRICT和NO ACTION相同,是指限制在字表有關聯記錄的情況下,父表不能更新。而CASCADE表示父表在更新和刪除的時候,更新或者刪除字表對應記錄。SET NULL表示附表在更新或者刪除的時候,,字表的對應欄位被SET NULL。
這裡你是可以組合使用,比如,ON DELETE RESTRICT ON UPDATE CASCADE。意思就是,刪除時,禁止操作,更新時,也更新子表。
這裡我想起用Hibernate做項目的時,Hibernate也有自己的CASCADE策略,Hibernate的CASCADE策略會在你試圖保存一個子表記錄時,檢查父表,如果沒有,自動插入。其實這樣有時候不是你希望的效果。
而且Hibernate不支持更新主鍵。切記。
3,存儲方式
分為共享表空間存儲,和多表空間存儲。我感覺這裡現在看的意義不大,以後再說吧。
MEMORY與MERGE,迅速說一下,不浪費時間,以後有需求再了解:
MEMORY:所有的數據保存在RAM中,在需要快速定位記錄和其他類似數據的環境下,可提供幾塊的訪問。缺陷是對錶的大小有限制,而且要確保表的數據可以恢復。
MERGE:用於將一系列等同的MyISAM表一邏輯方式組合在一起,並作為一個對象引用他們。優點在於可以突破對單個MyISAM表大小的限制,並通過不同的表分布在多個磁碟,可以有效地改善MERGE表的訪問效率。對於諸如數據倉儲等VLDM環境十分適合。
最後,想說一下對於char和varchar類型,表引擎的選擇,
首先這倆是不同,char類型是定長的,處理速度比VARCHAR快很多,但缺點是浪費存儲空間,而且程序需要對尾部空格進行處理。但隨著MySQL不斷升級,對於VARCHAR的性能也在不斷提高。
MyISAM:建議使用char。
MEMORY:不管是char還是varchar,都被當做char處理。
InnoDB:建議使用VARCHAR類型。
希望對你有幫助。
推薦閱讀:
※PebblesDB讀後感
※想讓前輩們給點建議,想用erlang實現一個簡單的分散式緩存資料庫?
※QCon 2017分享總結——分散式系統設計的幾點思考
※[跟吉姆一起讀LevelDB]2.LSM Tree與leveldb::DB::Open操作(1)
※redis4.0、codis、阿里雲redis 3種redis集群對比分析