1、索引
1.1 索引的概念 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
●索引是一個排序的列表，在這個列表中存儲著索引的值和包含這個值的數(shù)據(jù)所在行的物理地址（類似于C語言的鏈表通過指針指向數(shù)據(jù)記錄的內(nèi)存地址）。

●使用索引后可以不用掃描全表來定位某行的數(shù)據(jù)，而是先通過索引表找到該行數(shù)據(jù)對應(yīng)的物理地址然后訪問相應(yīng)的數(shù)據(jù)，因此能加快數(shù)據(jù)庫的查詢速度。

●索引就好比是一本書的目錄，可以根據(jù)目錄中的頁碼快速找到所需的內(nèi)容。

●索引是表中一列或者若干列值排序的方法。

●建立索引的目的是加快對表中記錄的查找或排序

1.2 索引的作用
●設(shè)置了合適的索引之后，數(shù)據(jù)庫利用各種快速定位技術(shù)，能夠大大加快查詢速度，這是創(chuàng)建索引的最主要的原因。

●當(dāng)表很大或查詢涉及到多個表時，使用索引可以成千上萬倍地提高查詢速度。

●可以降低數(shù)據(jù)庫的IO成本，并且索引還可以降低數(shù)據(jù)庫的排序成本。

●通過創(chuàng)建唯一(鍵)性索引，可以保證數(shù)據(jù)表中每一行數(shù)據(jù)的唯一性。

●可以加快表與表之間的連接。

●在使用分組和排序時，可大大減少分組和排序的時間。

1.3 索引的副作用
●索引需要占用額外的磁盤空間。
對于 MyISAM 引擎而言，索引文件和數(shù)據(jù)文件是分離的，索引文件用于保存數(shù)據(jù)記錄的地址。
而 InnoDB 引擎的表數(shù)據(jù)文件本身就是索引文件。
●在插入和修改數(shù)據(jù)時要花費更多的時間，因為索引也要隨之變動。

1.4 創(chuàng)建索引的規(guī)則
索引雖可以提升數(shù)據(jù)庫查詢的速度，但并不是任何情況下都適合創(chuàng)建索引。因為索引本身會消耗系統(tǒng)資源，在有索引的情況下，數(shù)據(jù)庫會先進行索引查詢，然后定位到具體的數(shù)據(jù)行，如果索引使用不當(dāng)，反而會增加數(shù)據(jù)庫的負擔(dān)。
●表的主鍵、外鍵必須有索引。因為主鍵具有唯一性，外鍵關(guān)聯(lián)的是子表的主鍵，查詢時可以快速定位

●記錄數(shù)超過300行的表應(yīng)該有索引。如果沒有索引，需要把表遍歷一遍，會嚴重影響數(shù)據(jù)庫的性能。

●經(jīng)常與其他表進行連接的表，在連接字段上應(yīng)該建立索引。

●唯一性太差的字段不適合建立索引。

●更新太頻繁地字段不適合創(chuàng)建索引。

●經(jīng)常出現(xiàn)在 where 子句中的字段，特別是大表的字段，應(yīng)該建立索引。
select name,score from ky19 where id=1

●索引應(yīng)該建在選擇性高的字段上。

●索引應(yīng)該建在小字段上，對于大的文本字段甚至超長字段，不要建索引。

1.5 索引的分類和創(chuàng)建
模板：
mysql -u root -p

create database info;
use info;
create table ky20 (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text);
desc ky20;

insert into ky20 values (1,'zhangsan','123','111111','nanjing','this is vip');
insert into ky20 values (4,'lisi','1234','444444','nanjing','this is normal');
insert into ky20 values (2,'wangwu','12345','222222','benjing','this is normal');
insert into ky20 values (5,'zhaoliu','123456','555555','nanjing','this is vip');
insert into ky20 values (3,'qianqi','1234567','333333','shanghai','this is vip');
select * from ky20;

1.5.1 普通索引

最基本的索引類型，沒有唯一性之類的限制。

●直接創(chuàng)建索引
CREATE INDEX 索引名 ON 表名 (列名[(length)]);
zhangsan?
#(列名(length))：length是可選項。如果忽略 length 的值，則使用整個列的值作為索引。如果指定使用列前的 length 個字符來創(chuàng)建索引，這樣有利于減小索引文件的大小。
#索引名建議以“_index”結(jié)尾。

示例：create index phone_index on ky20 (phone);
select phone from ky20;
show create table ky20;

●修改表方式創(chuàng)建
ALTER TABLE 表名 ADD INDEX 索引名 (列名);

例：alter table ky20 add index id_index (id);
select id from ky20;
select id,name from ky20;

●創(chuàng)建表的時候指定索引
CREATE TABLE 表名 ( 字段1 數(shù)據(jù)類型,字段2 數(shù)據(jù)類型[,...],INDEX 索引名 (列名));

例：create table test(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));
show create table test;

1.5.2 唯一索引

與普通索引類似，但區(qū)別是唯一索引列的每個值都唯一。
唯一索引允許有空值（注意和主鍵不同）。如果是用組合索引創(chuàng)建，則列值的組合必須唯一。添加唯一鍵將自動創(chuàng)建唯一索引。

●直接創(chuàng)建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);

例：UNIQUE
create unique index address_index on ky20 (address);--------
create unique index name_index on ky20 (name); ? ? ? ? ? ? ? ? ? ? ? ?
show create table ky20;

●修改表方式創(chuàng)建

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

例：alter table ky20 add unique cardid_index (cardid);

●創(chuàng)建表的時候指定

CREATE TABLE 表名 (字段1 數(shù)據(jù)類型,字段2 數(shù)據(jù)類型[,...],UNIQUE 索引名 (列名));

例:create table amd2 (id int,name varchar(20),unique id_index (id));
show creat table amd2;

create table test2 (id int,name varchar(40),age int(5),primary key (id));

1.5.3 主鍵索引

是一種特殊的唯一索引，必須指定為“PRIMARY KEY”。
一個表只能有一個主鍵，不允許有空值。 添加主鍵將自動創(chuàng)建主鍵索引。

創(chuàng)建表的時候指定

CREATE TABLE 表名 ([...],PRIMARY KEY (列名));

例：create table test1 (id int primary key,name varchar(20));
create table test2 (id int,name varchar(20),primary key (id));

show create table test1;
show create table test2;

●修改表方式創(chuàng)建

ALTER TABLE 表名 ADD PRIMARY KEY (列名);?

1.5.4 組合索引（單列索引與多列索引)

可以是單列上創(chuàng)建的索引，也可以是在多列上創(chuàng)建的索引。需要滿足最左原則，因為select語句的 where條件是依次從左往右執(zhí)行的，所以在使用select 語句查詢時where條件使用的字段順序必須和組合索引中的排序一致，否則索引將不會生效。

CREATE TABLE 表名 (列名1 數(shù)據(jù)類型,列名2 數(shù)據(jù)類型,列名3 數(shù)據(jù)類型,INDEX 索引名 (列名1,列名2,列名3));

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';

例:create table amd1 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));
show create table amd1;
insert into amd1 values(1,'zhangsan','123123');
nsert into amd1 values(1,'zhangsan','123123');

小結(jié)：
組合索引創(chuàng)建的字段順序是其觸發(fā)索引的查詢順序
例如：
--+
| test3 | CREATE TABLE "test3" (
? "id" int(11) NOT NULL,
? "name" varchar(50) DEFAULT NULL,
? "age" int(5) DEFAULT NULL,
? KEY "index_idname" ("id","name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
對以上的表進行select?
select id,name from test3; ? ? ? ? ? ?#會觸發(fā)組合索引
而：
select name,id from test3; ? ? ? ? ? ?#按照索引從左到右檢索的順序，則不會觸發(fā)組合索引

1.5.5 全文索引（FULLTEXT）

適合在進行模糊查詢的時候使用，可用于在一篇文章中檢索文本信息。
在 MySQL5.6 版本以前FULLTEXT 索引僅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 類型的列上創(chuàng)建。每個表只允許有一個全文索引。

●直接創(chuàng)建索引

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

例：select * from member;
create fulltext index remark_index on member (remark);

●修改表方式創(chuàng)建

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);
1
●創(chuàng)建表的時候指定索引

CREATE TABLE 表名 (字段1 數(shù)據(jù)類型[,...],FULLTEXT 索引名 (列名));?
1
#數(shù)據(jù)類型可以為 CHAR、VARCHAR 或者 TEXT

●使用全文索引查詢

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查詢內(nèi)容');

例：select * from member where match(remark) against('this is vip');
or select * from member where remark='this is vip';

1.6 查看索引
show index from 表名;
show index from 表名\G； 豎向顯示表索引信息
show keys from 表名;
show keys from 表名\G;

各字段的含義如下：

Table ? ?表的名稱
Non_unique ? ?如果索引內(nèi)容唯一，則為 0；如果可以不唯一，則為 1。
Key_name ? ?索引的名稱。
Seq_in_index ? ?索引中的列序號，從 1 開始。 limit 2,3
Column_name ? ?列名稱。
Collation ? ?列以什么方式存儲在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（無分類）。
Cardinality ? ?索引中唯一值數(shù)目的估計值。
Sub_part ? ?如果列只是被部分地編入索引，則為被編入索引的字符的數(shù)目(zhangsan)。如果整列被編入索引，則為 NULL。
Packed ? ?指示關(guān)鍵字如何被壓縮。如果沒有被壓縮，則為 NULL。
Null ? ?如果列含有 NULL，則含有 YES。如果沒有，則該列含有 NO。
Index_type ? ?用過的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
Comment ? ?備注。

小結(jié)：
索引分為：
① 普通索引 ：針對所有字段，沒有特殊的需求/規(guī)則
② 唯一索引 : 針對唯一性的字段，僅允許出現(xiàn)一次空值
③ 組合索引 (多列/多字段組合形式的索引)
④ 全文索引（varchar char text）MySQL為了優(yōu)化對文本內(nèi)容搜索的一種機制
⑤ 主鍵索引 ：針對唯一性字段、且不可為空，同時一張表只允許包含一個主鍵索引

創(chuàng)建索引：
① 在創(chuàng)建表的時候，直接指定index
② alter修改表結(jié)構(gòu)的時候，進行add 添加index
③ 直接創(chuàng)建索引index
PS:主鍵索引——》直接創(chuàng)建主鍵即可

1.7 刪除索引
●直接刪除索引

DROP INDEX 索引名 ON 表名;

例：drop index name_index on member;

●修改表方式刪除索引
ALTER TABLE 表名 DROP INDEX 索引名;

例：alter table member drop index id_index;
show index from member;

●刪除主鍵索引

ALTER TABLE 表名 DROP PRIMARY KEY;
?

2、事務(wù)
2.1 MySQL事務(wù)的概念
?MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。比如說，在人員管理系統(tǒng)中， 要刪除一個人員，即需要刪除人員的基本資料，又需要刪除和該人員相關(guān)的信息，如信箱， 文章等等。這樣，這些數(shù)據(jù)庫操作語句就構(gòu)成一個事務(wù)！

●事務(wù)是一種機制、一個操作序列，包含了一組數(shù)據(jù)庫操作命令，并且把所有的命令作為一個整體一起向系統(tǒng)提交或撤銷操作請求，即這一組數(shù)據(jù)庫命令要么都執(zhí)行，要么都不執(zhí)行。

●事務(wù)是一個不可分割的工作邏輯單元，在數(shù)據(jù)庫系統(tǒng)上執(zhí)行并發(fā)操作時，事務(wù)是最小的控制單元。

●事務(wù)適用于多用戶同時操作的數(shù)據(jù)庫系統(tǒng)的場景，如銀行、保險公司及證券交易系統(tǒng)等等。

●事務(wù)是通過事務(wù)的整體性以保證數(shù)據(jù)的一致性。

說白了，所謂事務(wù)，它是一個操作序列，這些操作要么都執(zhí)行，要么都不執(zhí)行，它是一個不可分割的工作單位。

2.2 事務(wù)的ACID概念及特點
ACID，是指在可靠數(shù)據(jù)庫管理系統(tǒng)（DBMS）中，事務(wù)(transaction)應(yīng)該具有的四個特性：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）。這是可靠數(shù)據(jù)庫所應(yīng)具備的幾個特性。

●原子性：指事務(wù)是一個不可再分割的工作單位，事務(wù)中的操作要么都發(fā)生，要么都不發(fā)生。

事務(wù)是一個完整的操作，事務(wù)的各元素是不可分的。
事務(wù)中的所有元素必須作為一個整體提交或回滾。
如果事務(wù)中的任何元素失敗，則整個事務(wù)將失敗。

案例:
A給B轉(zhuǎn)帳100元錢的時候只執(zhí)行了扣款語句，就提交了，此時如果突然斷電，A賬號已經(jīng)發(fā)生了扣款，B賬號卻沒收到加款，在生活中就會引起糾紛。這種情況就需要事務(wù)的原子性來保證事務(wù)要么都執(zhí)行，要么就都不執(zhí)行。
?

●一致性：指在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性約束沒有被破壞。

當(dāng)事務(wù)完成時，數(shù)據(jù)必須處于一致狀態(tài)。
在事務(wù)開始前，數(shù)據(jù)庫中存儲的數(shù)據(jù)處于一致狀態(tài)。
在正在進行的事務(wù)中，數(shù)據(jù)可能處于不一致的狀態(tài)。
當(dāng)事務(wù)成功完成時，數(shù)據(jù)必須再次回到已知的一致狀態(tài)。

?
案例:
對銀行轉(zhuǎn)帳事務(wù)，不管事務(wù)成功還是失敗，應(yīng)該保證事務(wù)結(jié)束后表中A和B的存款總額跟事務(wù)執(zhí)行前一致。
?

●隔離性：指在并發(fā)環(huán)境中，當(dāng)不同的事務(wù)同時操縱相同的數(shù)據(jù)時，每個事務(wù)都有各自的完整數(shù)據(jù)空間。

對數(shù)據(jù)進行修改的所有并發(fā)事務(wù)是彼此隔離的，表明事務(wù)必須是獨立的，它不應(yīng)以任何方式依賴于或影響其他事務(wù)。
修改數(shù)據(jù)的事務(wù)可在另一個使用相同數(shù)據(jù)的事務(wù)開始之前訪問這些數(shù)據(jù)，或者在另一個使用相同數(shù)據(jù)的事務(wù)結(jié)束之后訪問這些數(shù)據(jù)。

●持久性：在事務(wù)完成以后，該事務(wù)所對數(shù)據(jù)庫所作的更改便持久的保存在數(shù)據(jù)庫之中，并不會被回滾。

指不管系統(tǒng)是否發(fā)生故障，事務(wù)處理的結(jié)果都是永久的。
一旦事務(wù)被提交，事務(wù)的效果會被永久地保留在數(shù)據(jù)庫中。

2.3 事務(wù)之間的相互影響
一個事務(wù)的執(zhí)行不能被其他事務(wù)干擾
事務(wù)之間的相互影響分為幾種，分別為?

①、臟讀(讀取未提交數(shù)據(jù))：臟讀指的是讀到了其他事務(wù)未提交的數(shù)據(jù)，未提交意味著這些數(shù)據(jù)可能會回滾，也就是可能最終不會存到數(shù)據(jù)庫中，也就是不存在的數(shù)據(jù)。讀到了并一定最終存在的數(shù)據(jù)，這就是臟讀

案列1
比如事務(wù)B執(zhí)行過程中修改了數(shù)據(jù)X,在未提交前,事務(wù)A讀取了X,而事務(wù)B卻回滾了,這樣事務(wù)A就形成了臟讀。 也就是說,當(dāng)前事務(wù)讀到的數(shù)據(jù)是別的事務(wù)想要修改成為的但是沒有修改成功的數(shù)據(jù)。

②、不可重復(fù)讀(前后多次讀取，數(shù)據(jù)內(nèi)容不一致)：一個事務(wù)內(nèi)兩個相同的查詢卻返回了不同數(shù)據(jù)。這是由于查詢時系統(tǒng)中其他事務(wù)修改的提交而引起的。

案列
事務(wù)A第一次查詢得到一行記錄row1，事務(wù)B提交修改后，事務(wù)A第二次查詢得到row1，但列內(nèi)容發(fā)生了變化。
select * from member;
1 zhangsan ?20分
select * from Member;
1 zhangsan ?30分

③、幻讀(前后多次讀取，數(shù)據(jù)總量不一致)：一個事務(wù)對一個表中的數(shù)據(jù)進行了修改，這種修改涉及到表中的全部數(shù)據(jù)行。同時，另一個事務(wù)也修改這個表中的數(shù)據(jù)，這種修改是向表中插入一行新數(shù)據(jù)。那么，操作前一個事務(wù)的用戶會發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行，就好象發(fā)生了幻覺一樣。

案列
假設(shè)事務(wù)A對某些行的內(nèi)容作了更改，但是還未提交，此時事務(wù)B插入了與事務(wù)A更改前的記錄相同的記錄行，并且在事務(wù)A提交之前先提交了，而這時，在事務(wù)A中查詢，會發(fā)現(xiàn)好像剛剛的更改對于某些數(shù)據(jù)未起作用，但其實是事務(wù)B剛插入進來的，讓用戶感覺很魔幻，感覺出現(xiàn)了幻覺，這就叫幻讀
select * from member；
查詢到了6條記錄

alter table member change

select * from member;
查詢到了10條記錄 （更新了6條數(shù)據(jù)，還有4條數(shù)據(jù)，我沒有更新到）

④、丟失更新：兩個事務(wù)同時讀取同一條記錄，A先修改記錄，B也修改記錄（B不知道A修改過），B提交數(shù)據(jù)后B的修改結(jié)果覆蓋了A的修改結(jié)果。
案列
A ?30 ->40 事務(wù) 先完成
B ?30 ->50 事務(wù) 后完成
B的事務(wù)結(jié)果會覆蓋A的事務(wù)結(jié)果，最終值為50

2.4 Mysql及事務(wù)隔離級別（四種）
（1）read uncommitted（ : 讀取尚未提交的數(shù)據(jù) ：不解決臟讀
允許臟讀，其他事務(wù)只要修改了數(shù)未提交讀）據(jù)，即使未提交，本事務(wù)也能看到修改后的數(shù)據(jù)值。也就是可能讀取到其他會話中未提交事務(wù)修改的數(shù)居。

（2）read committed（提交讀）：讀取已經(jīng)提交的數(shù)據(jù) ：可以解決臟讀
只能讀取到已經(jīng)提交的數(shù)據(jù)。Oracle等多數(shù)數(shù)據(jù)庫默認都是該級別〈不重復(fù)讀)。

（3）repeatable read（可重復(fù)度）：重讀讀?。嚎梢越鉀Q臟讀 和 不可重復(fù)讀 —mysql默認的
可重復(fù)讀。無論其他事務(wù)是否修改并提交了數(shù)據(jù)，在這個事務(wù)中看到的數(shù)據(jù)值始終不受其他事務(wù)影響

（4）serializable：串行化：可以解決 臟讀 不可重復(fù)讀 和 虛讀—相當(dāng)于鎖表
完全串行化的讀，每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞。

mysql默認的事務(wù)處理級別是 repeatable read ，而Oracle和SQL Server是 read committed 。

2.5 事務(wù)隔離級別的作用范圍
事務(wù)隔離級別的作用范圍分為兩種:
全局級:對所有的會話有效
會話級:|只對當(dāng)前的會話有效

· 查詢?nèi)质聞?wù)隔離級別
show global variables like '%isolation%';
SELECT @@global.tx_isolation;

· 查詢會話事務(wù)隔離級別
show session variables like '%isolation%';
SELECT @@session.tx_isolation;?
SELECT @@tx_isolation;

· 設(shè)置全局事務(wù)隔離級別
set global transaction isolation level read committed;

· 設(shè)置會話事務(wù)隔離級別
set session transaction isolation level read committed;

2.6 事務(wù)控制語句

BEGIN 或 START TRANSACTION：顯式地開啟一個事務(wù)。

COMMIT 或 COMMIT WORK：提交事務(wù)，并使已對數(shù)據(jù)庫進行的所有修改變?yōu)橛谰眯缘摹?/p>

ROLLBACK 或 ROLLBACK WORK：回滾會結(jié)束用戶的事務(wù)，并撤銷正在進行的所有未提交的修改。

SAVEPOINT S1：使用 SAVEPOINT 允許在事務(wù)中創(chuàng)建一個回滾點，一個事務(wù)中可以有多個 SAVEPOINT；“S1”代表回滾點名稱。

ROLLBACK TO [SAVEPOINT] S1：把事務(wù)回滾到標記點。

create database SCHOOL;
use SCHOOL;
create table info( ?
id int(10) primary key not null, ?
name varchar(40), ?
money double ?
);

insert into info values(1,'A',1000); ?
insert into info values(2,'B',1000);?
select * from info;

2.6.1 測試提交事務(wù)
?begin;
update info set money= money - 100 where name='A';
select * from info;

commit;
quit

mysql -u root -p
use SCHOOL;
select * from info;

2.6.2 測試回滾事務(wù)

begin;
update info set money= money + 100 where name='A';
select * from info;

rollback;
quit
mysql -u root -p
use SCHOOL;
select * from info;

2.6.3 測試多點回滾

begin;
update info set money= money + 100 where name='A';
select * from info;
SAVEPOINT S1;
update info set money= money + 100 where name='B';
select * from info;
SAVEPOINT S2;
insert into info values(3,'C',1000);

select * from info;
ROLLBACK TO S1;
select * from info;

2.6.4 使用 set 設(shè)置控制事務(wù)

SET AUTOCOMMIT=0; ? ? ? ? ? ? ? ? ? ? ? ?#禁止自動提交
SET AUTOCOMMIT=1; ? ? ? ? ? ? ? ? ? ? ? ?#開啟自動提交，Mysql默認為1
SHOW VARIABLES LIKE 'AUTOCOMMIT'; ? ? ? ?#查看Mysql中的AUTOCOMMIT值

如果沒有開啟自動提交，當(dāng)前會話連接的mysql的所有操作都會當(dāng)成一個事務(wù)直到你輸入rollback|commit;當(dāng)前事務(wù)才算結(jié)束。當(dāng)前事務(wù)結(jié)束前新的mysql連接時無法讀取到任何當(dāng)前會話的操作結(jié)果。
如果開起了自動提交，mysql會把每個sql語句當(dāng)成一個事務(wù)，然后自動的commit。
當(dāng)然無論開啟與否，begin; commit|rollback; 都是獨立的事務(wù)。

use SCHOOL;
select * from info;
SET AUTOCOMMIT=0;
SHOW VARIABLES LIKE 'AUTOCOMMIT';
update info set money= money + 100 where name='B';
select * from info;
quit

mysql -u root -p
use SCHOOL;
select * from info;

3、MySQL 存儲引擎
3.1 存儲引擎概念介紹
MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲在文件中，每一種技術(shù)都使用不同的存儲機制、索引技巧、鎖定水平并最終提供不同的功能和能力，這些不同的技術(shù)以及配套的功能在MySQL中稱為存儲引擎
存儲引擎是MySQL將數(shù)據(jù)存儲在文件系統(tǒng)中的存儲方式或者存儲格式

3.2 MySQL常用的存儲引擎
· MyISAM
· InnoDB

MySQL數(shù)據(jù)庫中的組件，負責(zé)執(zhí)行實際的數(shù)據(jù)I/O操作
MySQL系統(tǒng)中，存儲引擎處于文件系統(tǒng)之上，在數(shù)據(jù)保存到數(shù)據(jù)文件之前會傳輸?shù)酱鎯σ?，之后按照各個存儲引擎的存儲格式進行存儲

3.3 MyISAM
MyISAM不支持事務(wù)，也不支持外鍵約束，只支持全文索引，數(shù)據(jù)文件和索引文件是分開保存的

訪問速度快，對事務(wù)完整性沒有要求
MyISAM 適合查詢、插入為主的應(yīng)用場景

MyISAM在磁盤上存儲成三個文件，文件名和表名都相同，但是擴展名分別為：
.frm 文件存儲表結(jié)構(gòu)的定義
數(shù)據(jù)文件的擴展名為 .MYD (MYData)
索引文件的擴展名是 .MYI (MYIndex)

表級鎖定形式，數(shù)據(jù)在更新時鎖定整個表
數(shù)據(jù)庫在讀寫過程中相互阻塞: ————》串行操作，按照順序操作，每次在讀或?qū)懙臅r候會把全表鎖起來
會在數(shù)據(jù)寫入的過程阻塞用戶數(shù)據(jù)的讀取
也會在數(shù)據(jù)讀取的過程中阻塞用戶的數(shù)據(jù)寫入
特性：數(shù)據(jù)單獨寫入或讀取，速度過程較快且占用資源相對少

MyIsam 是表級鎖定，讀或?qū)憻o法同時進行
好處是：分開執(zhí)行時，速度快、資源占用相對較少（相對）

3.3.1 MyISAM 表支持 3 種不同的存儲格式?

（1）靜態(tài)(固定長度)表
靜態(tài)表是默認的存儲格式。靜態(tài)表中的字段都是非可變字段，這樣每個記錄都是固定長度的，這種存儲方式的優(yōu)點是存儲非常迅速，容易緩存，出現(xiàn)故障容易恢復(fù)；缺點是占用的空間通常比動態(tài)表多。
固定長度10
存儲非常迅速，容器緩存，故障之后容易恢復(fù)
id（5） ?char(10)
000000001

（2）動態(tài)表
動態(tài)表包含可變字段（varchar），記錄不是固定長度的，這樣存儲的優(yōu)點是占用空間較少，但是頻繁的更新、刪除記錄會產(chǎn)生碎片，需要定期執(zhí)行 OPTIMIZE TABLE 語句或 myisamchk -r 命令來改善性能，并且出現(xiàn)故障的時候恢復(fù)相對比較困難。

（3）壓縮表?
壓縮表由 myisamchk 工具創(chuàng)建，占據(jù)非常小的空間，因為每條記錄都是被單獨壓縮的，所以只有非常小的訪問開支。

3.3.2 MyISAM適用的生產(chǎn)場景

公司業(yè)務(wù)不需要事務(wù)的支持?
單方面讀取或?qū)懭霐?shù)據(jù)比較多的業(yè)務(wù)
MyISAM存儲引擎數(shù)據(jù)讀寫都比較頻繁場景不適合
使用讀寫并發(fā)訪問相對較低的業(yè)務(wù)
數(shù)據(jù)修改相對較少的業(yè)務(wù)
對數(shù)據(jù)業(yè)務(wù)一致性要求不是非常高的業(yè)務(wù)
服務(wù)器硬件資源相對比較差

MyIsam：適合于單方向的任務(wù)場景、同時并發(fā)量不高、對于事務(wù)要求不高的場景

3.4 InnoDB
3.4.1 InnoDB特點

支持事務(wù)，支持4個事務(wù)隔離級別（數(shù)據(jù)不一致問題）??
MySQL從5.5.5版本開始，默認的存儲引擎為 InnoDB
5.5 之前是myisam （isam） 默認

讀寫阻塞與事務(wù)隔離級別相關(guān)
能非常高效的緩存索引和數(shù)據(jù)
表與主鍵以簇的方式存儲
支持分區(qū)、表空間，類似oracle數(shù)據(jù)庫（5.5 ——》5.6 和5.7 第三代數(shù)據(jù)庫8.0后版本）
支持外鍵約束，5.5前不支持全文索引，5.5后支持全文索引

對硬件資源要求還是比較高的場合
行級鎖定，但是全表掃描仍然會是表級鎖定（select ），如
update table set a=1 where user like ‘%lic%’;

InnoDB 中不保存表的行數(shù)，如 select count(*) from table; 時，InnoDB 需要掃描一遍整個表來計算有多少行，但是 MyISAM 只要簡單的讀出保存好的行數(shù)即可。需要注意的是，當(dāng) count(*)語句包含 where 條件時 MyISAM 也需要掃描整個表
對于自增長的字段，InnoDB 中必須包含只有該字段的索引，但是在 MyISAM 表中可以和其他字段一起建立組合索引

清空整個表時，InnoDB 是一行一行的刪除，效率非常慢。MyISAM 則會重建表（truncate）

死鎖????
MyISAM :表級鎖定
innodb :行級鎖定

當(dāng)兩個請求分別訪問/讀取2行記錄，同時又需要讀取對方的記錄數(shù)據(jù)，因為（行鎖的限制）而造成了阻塞的現(xiàn)象

怎么解決死鎖
show?

企業(yè)選擇存儲引擎依據(jù)
業(yè)務(wù)場景如果并發(fā)量大，什么并發(fā)量大，讀寫的并發(fā)量大，那我們建議使用innoDB 如果單獨的寫入或是插入單獨的查詢，那我們建議使用沒有INNODB

表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低；
行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高；?

MyISAM不支持事務(wù)，也不支持外鍵約束，只支持全文索引，數(shù)據(jù)文件和索引文件是分開保存的
?需要考慮每個存儲引擎提供了哪些不同的核心功能及應(yīng)用場景

? 支持的字段和數(shù)據(jù)類型
?所有引擎都支持通用的數(shù)據(jù)類型
?但不是所有的引擎都支持其它的字段類型，如二進制對象

? 鎖定類型:不同的存儲引擎支持不同級別的鎖定
?表鎖定: MyISAM 支持
?行鎖定: InnoDB 支持

索引的支持
?建立索引在搜索和恢復(fù)數(shù)據(jù)庫中的數(shù)據(jù)時能顯著提高性能
?不同的存儲弓|擎提供不同的制作索引的技術(shù)
?有些存儲弓|擎根本不支持索引

事務(wù)處理的支持
?提高在向表中更新和插入信息期間的可靠性
?可根據(jù)企業(yè)業(yè)務(wù)是否要支持事務(wù)選擇存儲引擎

3.5 查看系統(tǒng)支持的存儲引擎
show engines;

3.6 查看表使用的存儲引擎
方法一
show table status from 庫名 where name='表名'\G；

例：show table status from SCHOOL where name='info'\G;

方法二
use 庫名;
show create table 表名;

例：use SCHOOL;
show create table info;

3.7 修改存儲引擎

方法一：通過 alter table 修改
use 庫名;
alter table 表名 engine=MyISAM;

例：use SCHOOL;
alter table info engine=myisam;
show create table info;

方法二：通過修改 /etc/my.cnf 配置文件，指定默認存儲引擎并重啟服務(wù)
quit
vim /etc/my.cnf
[mysqld]
default-storage-engine=INNODB

systemctl restart mysqld.service
修改完記得重啟mysql服務(wù)
#注意：此方法只對修改了配置文件并重啟mysql服務(wù)后新創(chuàng)建的表有效，已經(jīng)存在的表不會有變更。

方法三：通過 create table 創(chuàng)建表時指定存儲引擎
use 庫名;
create table 表名(字段1 數(shù)據(jù)類型,...) engine=MyISAM;

例：mysql -u root -p
use SCHOOL;
create table hellolic (name varchar(10),age char(4)) engine=myisam;

3.8 擴展

1、索引——》mysql 優(yōu)化
在合適的字段上創(chuàng)建索引

在有索引的情況下，數(shù)據(jù)庫會先進行索引查詢，然后定位到具體的數(shù)據(jù)行沒有有索引的情況下掃描全表來定位某行的數(shù)據(jù)
)一個排序的列表，在這個列表中存儲著索引的值和包含這個值的數(shù)據(jù)所在行的物理地址

使用索引后，是先通過索引表找到該行數(shù)據(jù)對應(yīng)的物理地址然后訪問相應(yīng)的數(shù)據(jù)，因此能加快數(shù)據(jù)庫的查詢速度。

2、事務(wù)特性(ACID)
事務(wù)ACID
原子性 (一個整體，要么都執(zhí)行要么都不執(zhí)行)
一致性 要求事務(wù)前后數(shù)據(jù)的完整和一致
隔離性 要求多個事務(wù)之間不相互影響依賴（4個影響，4個隔離級別)
持久性 當(dāng)事務(wù)提交后將永久保存，不可再回滾

3、數(shù)據(jù)不一致的情況（四種）
（1）read uncommitted（未提交讀） : 讀取尚未提交的數(shù)據(jù) ：不解決臟讀
允許臟讀，其他事務(wù)只要修改了數(shù)據(jù)，即使未提交，本事務(wù)也能看到修改后的數(shù)據(jù)值。也就是可能讀取到其他會話中未提交事務(wù)修改的數(shù)居。

（2）read committed（提交讀）：讀取已經(jīng)提交的數(shù)據(jù) ：可以解決臟讀
只能讀取到已經(jīng)提交的數(shù)據(jù)。Oracle等多數(shù)數(shù)據(jù)庫默認都是該級別〈不重復(fù)讀)。

（3）repeatable read（可重復(fù)度）：重讀讀?。嚎梢越鉀Q臟讀 和 不可重復(fù)讀 —mysql默認的
可重復(fù)讀。無論其他事務(wù)是否修改并提交了數(shù)據(jù)，在這個事務(wù)中看到的數(shù)據(jù)值始終不受其他事務(wù)影響

（4）serializable：串行化：可以解決 臟讀 不可重復(fù)讀 和 虛讀—相當(dāng)于鎖表
完全串行化的讀，每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞。

mysql默認的事務(wù)處理級別是 repeatable read ，而Oracle和SQL Server是 read committed 。
?

3.9 MyISAM 和 INNODB小結(jié)
- InnoDB支持事物，而MylSAM不支持事物。
- lnnoDB支持行級鎖，而MylSAM支持表級鎖.
- InnoDB支持MVCC,而MlSAM不支持。
- lnnoDB支持外鍵。而MyISAM不支持。
- lnnoDB全文索引，而MylSAM支持。

INNODB
支持事務(wù)讀寫并發(fā)外鍵5.5版本以后支持全文索引行級鎖定（在用like模糊匹配全表掃描時會表級鎖定)對硬件資源要求較高，特別是內(nèi)存高可以提高緩存能力
三個文件存儲.frm(表結(jié)構(gòu)) .ibd (表數(shù)據(jù)文件同時也是索引文件) db.opt (表屬性文件)適合場景有事務(wù)要求的一致性要求高的讀寫并發(fā)量高的
MyISAM
不支持事務(wù)外鍵表級鎖定
讀寫會相互阻塞支持全文索引資源消耗較低
三個文件存儲 .frm(表結(jié)構(gòu)).MYI(索引文件）.MYD(數(shù)據(jù)文件)三種存儲格式靜態(tài)表動態(tài)表壓縮表
適合場景單獨的讀取或插入

5、你們公司用哪種存儲引擎
這是高級開發(fā)者面試時經(jīng)常被問的問題。實際我們在平時的開發(fā)中，經(jīng)常會遇到的，在用SQLyog等工具創(chuàng)建表時，就有一個引擎項要你去選。如下圖：

Mysql的存儲引擎有這么多種，實際我們在平時用的最多的莫過于InnoDB和MyISAM了。

所有如果面試官問道m(xù)ysql有哪些存儲引擎，你只需要告訴這兩個常用的就行。

那他們都有什么特點和區(qū)別呢？
MyISAM和InnoDB的特點：

MyISAM ：默認表類型，它是基于傳統(tǒng)的ISAM類型，ISAM是Indexed Sequential Access Method (有索引的順序訪問方法) 的縮寫，它是存儲記錄和文件的標準方法。不是事務(wù)安全的，而且不支持外鍵，如果執(zhí)行大量的select，insert MyISAM比較適合。

InnoDB ：支持事務(wù)安全的引擎，支持外鍵、行鎖、事務(wù)是他的最大特點。如果有大量的update和insert，建議使用InnoDB，特別是針對多個并發(fā)和QPS較高的情況。注： 在MySQL 5.5之前的版本中，默認的搜索引擎是MyISAM，從MySQL 5.5之后的版本中，默認的搜索引擎變更為InnoDB。

MyISAM和InnoDB的區(qū)別：

InnoDB支持事務(wù)，MyISAM不支持。對于InnoDB每一條SQL語言都默認封裝成事務(wù)，自動提交，這樣會影響速度，所以最好把多條SQL語言放在begin和commit之間，組成一個事務(wù)；
InnoDB支持外鍵，而MyISAM不支持。
InnoDB是聚集索引，使用B+Tree作為索引結(jié)構(gòu)，數(shù)據(jù)文件是和（主鍵）索引綁在一起的（表數(shù)據(jù)文件本身就是按B+Tree組織的一個索引結(jié)構(gòu)），必須要有主鍵，通過主鍵索引效率很高。MyISAM是非聚集索引，也是使用B+Tree作為索引結(jié)構(gòu)，索引和數(shù)據(jù)文件是分離的，索引保存的是數(shù)據(jù)文件的指針。主鍵索引和輔助索引是獨立的。
InnoDB不保存表的具體行數(shù)，執(zhí)行select count(*) from table時需要全表掃描。而MyISAM用一個變量保存了整個表的行數(shù)，執(zhí)行上述語句時只需要讀出該變量即可，速度很快。
Innodb不支持全文索引，而MyISAM支持全文索引，查詢效率上MyISAM要高；5.7以后的InnoDB支持全文索引了。
InnoDB支持表、行級鎖(默認)，而MyISAM支持表級鎖。
InnoDB表必須有主鍵（用戶沒有指定的話會自己找或生產(chǎn)一個主鍵），而Myisam可以沒有。
Innodb存儲文件有frm、ibd，而Myisam是frm、MYD、MYI。
Innodb：frm是表定義文件，ibd是數(shù)據(jù)文件。

Myisam：frm是表定義文件，myd是數(shù)據(jù)文件，myi是索引文件。文章來源地址http://www.zghlxwxcb.cn/news/detail-730864.html

到了這里，關(guān)于MySQL索引、事務(wù)、事務(wù)與存儲引擎的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！