1、InnoDB存儲引擎
創(chuàng)新互聯(lián)公司主營皇姑網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,APP應(yīng)用開發(fā),皇姑h5小程序設(shè)計搭建,皇姑網(wǎng)站營銷推廣歡迎皇姑等地區(qū)企業(yè)咨詢
Mysql版本=5.5 默認的存儲引擎，MySQL推薦使用的存儲引擎。支持事務(wù)，行級鎖定，外鍵約束。事務(wù)安全型存儲引擎。更加注重數(shù)據(jù)的完整性和安全性。
存儲格式 : 數(shù)據(jù)，索引集中存儲，存儲于同一個表空間文件中。
InnoDB的行鎖模式及其加鎖方法： InnoDB中有以下兩種類型的行鎖：共享鎖（讀鎖： 允許事務(wù)對一條行數(shù)據(jù)進行讀?。┖?互斥鎖（寫鎖： 允許事務(wù)對一條行數(shù)據(jù)進行刪除或更新）， 對于update，insert，delete語句，InnoDB會自動給設(shè)計的數(shù)據(jù)集加互斥鎖，對于普通的select語句，InnoDB不會加任何鎖。
InnoDB行鎖的實現(xiàn)方式： InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的，如果沒有索引，InnoDB將通過隱藏的聚簇索引來對記錄加鎖。InnoDB這種行鎖實現(xiàn)特點意味著：如果不通過索引條件檢索數(shù)據(jù)，那么InnoDB將對表中的所有記錄加鎖，實際效果跟表鎖一樣。
（1）在不通過索引條件查詢時，InnoDB會鎖定表中的所有記錄。
（2）Mysql的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果使用相同的索引鍵，是會出現(xiàn)沖突的。
（3）當(dāng)表有多個索引的時候，不同的事務(wù)可以使用不同的索引鎖定不同的行，但都是通過行鎖來對數(shù)據(jù)加鎖。
優(yōu)點：
1、支持事務(wù)處理、ACID事務(wù)特性；
2、實現(xiàn)了SQL標準的四種隔離級別（ 原子性（ Atomicity ）、一致性（ Consistency ）、隔離性（Isolation ）和持續(xù)性（Durability ））；
3、支持行級鎖和外鍵約束；
4、可以利用事務(wù)日志進行數(shù)據(jù)恢復(fù)。
5、鎖級別為行鎖，行鎖優(yōu)點是適用于高并發(fā)的頻繁表修改，高并發(fā)是性能優(yōu)于 MyISAM。缺點是系統(tǒng)消耗較大。
6、索引不僅緩存自身，也緩存數(shù)據(jù)，相比 MyISAM 需要更大的內(nèi)存。
缺點：
因為它沒有保存表的行數(shù)，當(dāng)使用COUNT統(tǒng)計時會掃描全表。
使用場景：
(1)可靠性要求比較高，或者要求事務(wù)；(2)表更新和查詢都相當(dāng)?shù)念l繁，并且表鎖定的機會比較大的情況。
2、 MyISAM存儲引擎
MySQL= 5.5 MySQL默認的存儲引擎。ISAM：Indexed Sequential Access Method(索引順序存取方法)的縮寫，是一種文件系統(tǒng)。擅長與處理，高速讀與寫。
功能：
（1）支持數(shù)據(jù)壓縮存儲,但壓縮后的表變成了只讀表，不可寫；如果需要更新數(shù)據(jù)，則需要先解壓后更新。
（2）支持表級鎖定，不支持高并發(fā)；
（3）支持并發(fā)插入。寫操作中的插入操作，不會阻塞讀操作（其他操作）；
優(yōu)點：
1.高性能讀?。?/p>
2.因為它保存了表的行數(shù)，當(dāng)使用COUNT統(tǒng)計時不會掃描全表；
缺點：
1、鎖級別為表鎖，表鎖優(yōu)點是開銷小，加鎖快；缺點是鎖粒度大，發(fā)生鎖沖動概率較高，容納并發(fā)能力低，這個引擎適合查詢?yōu)橹鞯臉I(yè)務(wù)。
2、此引擎不支持事務(wù)，也不支持外鍵。
3、INSERT和UPDATE操作需要鎖定整個表；
使用場景：
(1)做很多count 的計算；(2)插入不頻繁，查詢非常頻繁；(3)沒有事務(wù)。
InnoDB和MyISAM一些細節(jié)上的差別：
1、InnoDB不支持FULLTEXT類型的索引，MySQL5.6之后已經(jīng)支持（實驗性）。
2、InnoDB中不保存表的 具體行數(shù)，也就是說，執(zhí)行select count() from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數(shù)即可。注意的是，當(dāng)count()語句包含 where條件時，兩種表的操作是一樣的。
3、對于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。
4、DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。
5、LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導(dǎo)入數(shù)據(jù)后再改成InnoDB表，但是對于使用的額外的InnoDB特性（例如外鍵）的表不適用。
6、另外，InnoDB表的行鎖也不是絕對的，如果在執(zhí)行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表。
1．索引概述
利用關(guān)鍵字，就是記錄的部分數(shù)據(jù)（某個字段，某些字段，某個字段的一部分），建立與記錄位置的對應(yīng)關(guān)系，就是索引。索引的關(guān)鍵字一定是排序的。索引本質(zhì)上是表字段的有序子集，它是提高查詢速度最有效的方法。一個沒有建立任何索引的表，就相當(dāng)于一本沒有目錄的書，在每次查詢時就會進行全表掃描，這樣會導(dǎo)致查詢效率極低、速度也極慢。如果建立索引，那么就好比一本添加的目錄，通過目錄的指引，迅速翻閱到指定的章節(jié)，提升的查詢性能，節(jié)約了查詢資源。
2．索引種類
從索引的定義方式和用途中來看：主鍵索引，唯一索引，普通索引，全文索引。
無論任何類型，都是通過建立關(guān)鍵字與位置的對應(yīng)關(guān)系來實現(xiàn)的。索引是通過關(guān)鍵字找對應(yīng)的記錄的地址。
以上類型的差異：對索引關(guān)鍵字的要求不同。
關(guān)鍵字：記錄的部分數(shù)據(jù)（某個字段，某些字段，某個字段的一部分）。
普通索引,index：對關(guān)鍵字沒有要求。
唯一索引,unique index：要求關(guān)鍵字不能重復(fù)。同時增加唯一約束。
主鍵索引,primary key：要求關(guān)鍵字不能重復(fù)，也不能為NULL。同時增加主鍵約束。
全文索引,fulltext key：關(guān)鍵字的來源不是所有字段的數(shù)據(jù)，而是從字段中提取的特別關(guān)鍵詞。
PS：這里主鍵索引和唯一索引的區(qū)別在于：主鍵索引不能為空值，唯一索引允許空值；主鍵索引在一張表內(nèi)只能創(chuàng)建一個，唯一索引可以創(chuàng)建多個。主鍵索引肯定是唯一索引，但唯一索引不一定是主鍵索引。
3.索引原則
如果索引不遵循使用原則，則可能導(dǎo)致索引無效。
（1）列獨立
如果需要某個字段上使用索引，則需要在字段參與的表達中，保證字段獨立在一側(cè)。否則索引不會用到索引, 例如這條sql就不會用到索引:select * from A where id+1=10;
（2）左原則
Like：匹配模式必須要左邊確定不能以通配符開頭。例如:select * from A where name like '%小明%' ，不會用到索引，而select * from A where name like '小明%' 就可以用到索引（name字段有建立索引），如果業(yè)務(wù)上需要用到'%小明%'這種方式,有兩種方法:1.可以考慮全文索引,但mysql的全文索引不支持中文；2.只查詢索引列或主鍵列,例如:select name from A where name like '%小明%' 或 select id from A where name like '%小明%' 或 select id,name from A where name like '%小明%' 這三種情況都會用到name的索引;
復(fù)合索引：一個索引關(guān)聯(lián)多個字段，僅僅針對左邊字段有效果，添加復(fù)合索引時，第一個字段很重要，只有包含第一個字段作為查詢條件的情況才會使用復(fù)合索引(必須用到建索引時選擇的第一個字段作為查詢條件,其他字段的順序無關(guān)),而且查詢條件只能出現(xiàn)and拼接,不能用or,否則則無法使用索引.
（3）OR的使用
必須要保證 OR 兩端的條件都存在可以用的索引，該查詢才可以使用索引。
（4）MySQL智能選擇
即使?jié)M足了上面說原則，MySQL也能棄用索引，例如：select * from A where id 1;這里棄用索引的主要原因：查詢即使使用索引，會導(dǎo)致出現(xiàn)大量的隨機IO，相對于從數(shù)據(jù)記錄的第一條遍歷到最后一條的順序IO開銷，還要大。
4.索引的使用場景
（1）索引檢索：檢索數(shù)據(jù)時使用索引。
（2）索引排序: 如果order by 排序需要的字段上存在索引，則可能使用到索引。
（3）索引覆蓋: 索引擁有的關(guān)鍵字內(nèi)容，覆蓋了查詢所需要的全部數(shù)據(jù)，此時，就不需要在數(shù)據(jù)區(qū)獲取數(shù)據(jù)，僅僅在索引區(qū)即可。覆蓋就是直接在索引區(qū)獲取內(nèi)容，而不需要在數(shù)據(jù)區(qū)獲取。例如: select name from A where name like '小明%';
建立索引索引時，不能僅僅考慮where檢索，同時考慮其他的使用場景。（在所有的where字段上增加索引，就是不合理的）
5.前綴索引
前綴索引是建立索引關(guān)鍵字一種方案。通常會使用字段的整體作為索引關(guān)鍵字。有時，即使使用字段前部分數(shù)據(jù)，也可以去識別某些記錄。就比如一個班級里，我要找王xx，假如姓王的只有1個人，那么就可以建一個關(guān)鍵字為'王'的前綴索引。語法：Index `index_name` (`index_field`(N))使用index_name前N個字符建立的索引。
6.索引失效
（1） 應(yīng)盡量避免在 where 子句中使用 != 或 操作符，否則將引擎放棄使用索引而進行全表掃描；
（2） 應(yīng)盡量避免在 where 子句中使用 or 來連接條件，如果一個字段有索引，一個字段沒有索引，將導(dǎo)致引擎放棄使用索引而進行全表掃描；
（3） 應(yīng)盡量避免在 where 子句中對字段進行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描；
（4）應(yīng)盡量避免在 where 子句中對字段進行表達式操作，這將導(dǎo)致引擎放棄使用索引而進行全表掃描；如select id from t where num/2 = 100；
（5） 應(yīng)盡量避免在where子句中對字段進行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進行全表掃描；如：select id from t where substring(name,1,3) = ’abc’ ；
（6）應(yīng)盡量避免在where子句中對字段進行類型轉(zhuǎn)換，這將導(dǎo)致引擎放棄使用索引而進行全表掃描； 如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號引用起來,如select id from t where id = 1；如果id字段在表設(shè)計中是varchar類型，那么即使id列上存的是數(shù)字，在查詢時也一定要用varchar去匹配，sql應(yīng)改為select id from t where id = '1'；
（7）應(yīng)盡量避免在where子句中單獨引用復(fù)合索引里非第一位置的索引；
join 的兩種算法：BNL 和 NLJ
NLJ（Nested Loop Join）嵌套循環(huán)算法；以如下 SQL 為例：
select * from t1 join t2 on t1.a=t2.a
SQL 執(zhí)行時內(nèi)部流程是這樣的：
1. 先從 t1（假設(shè)這里 t1 被選為驅(qū)動表）中取出一行數(shù)據(jù) X；
2. 從 X 中取出關(guān)聯(lián)字段 a 值，去 t2 中進行查找，滿足條件的行取出；
3. 重復(fù)1、2步驟，直到表 t1 最后一行循環(huán)結(jié)束。
這就是一個嵌套循環(huán)的過程，如果在被驅(qū)動表上查找數(shù)據(jù)時可以使用索引，總的對比計算次數(shù)等于驅(qū)動表滿足 where 條件的行數(shù)。假設(shè)這里 t1、t2都是1萬行，則只需要 1萬次計算，這里用到的是Index Nested-Loops Join（INLJ，基于索引的嵌套循環(huán)聯(lián)接）。
如果 t1、t2 的 a 字段都沒有索引，還按照上述的嵌套循環(huán)流程查找數(shù)據(jù)呢？每次在被驅(qū)動表上查找數(shù)據(jù)時都是一次全表掃描，要做1萬次全表掃描，掃描行數(shù)等于 1萬+1萬*1萬，這個效率很低，如果表行數(shù)更多，掃描行數(shù)動輒幾百億，所以優(yōu)化器肯定不會使用這樣的算法，而是選擇 BNL 算法；
BNLJ（Block Nested Loop Join）塊嵌套循環(huán)算法；
1. 把 t1 表（假設(shè)這里 t1 被選為驅(qū)動表）滿足條件的數(shù)據(jù)全部取出放到線程的 join buffer 中；
2. 每次取 t2 表一行數(shù)據(jù)，去 joinbuffer 中進行查找，滿足條件的行取出，直到表 t2 最后一行循環(huán)結(jié)束。
這個算法下，執(zhí)行計劃的 Extra 中會出現(xiàn) Using join buffer(Block Nested Loop)，t1、t2 都做了一次全表掃描，總的掃描行數(shù)等于 1萬+1萬。但是由于 joinbuffer 維護的是一個無序數(shù)組，每次在 joinbuffer 中查找都要遍歷所有行，總的內(nèi)存計算次數(shù)等于1萬*1萬。另外如果 joinbuffer 不夠大放不下驅(qū)動表的數(shù)據(jù)，則要分多次執(zhí)行上面的流程，會導(dǎo)致被驅(qū)動表也做多次全表掃描。
BNLJ相對于NLJ的優(yōu)點在于，驅(qū)動層可以先將部分數(shù)據(jù)加載進buffer，這種方法的直接影響就是將大大減少內(nèi)層循環(huán)的次數(shù)，提高join的效率。
例如：
如果內(nèi)層循環(huán)有100條記錄，外層循環(huán)也有100條記錄，這樣的話，每次外層循環(huán)先將10條記錄放到buffer中，內(nèi)層循環(huán)的100條記錄每條與這個buffer中的10條記錄進行匹配，只需要匹配內(nèi)層循環(huán)總記錄數(shù)次即可結(jié)束一次循環(huán)（在這里，即只需要匹配100次即可結(jié)束），然后將匹配成功的記錄連接后放入結(jié)果集中，接著，外層循環(huán)繼續(xù)向buffer中放入10條記錄，同理進行匹配，并將成功的記錄連接后放入結(jié)果集。后續(xù)循環(huán)以此類推，直到循環(huán)結(jié)束，將結(jié)果集發(fā)給client為止。
可以發(fā)現(xiàn)，若用NLJ，則需要100 * 100次才可結(jié)束，BNLJ則需要100 / block_size * 100 = 10 * 100次就可結(jié)束,大大減少了循環(huán)次數(shù)。
JOIN 按照功能大致分為如下三類：
JOIN、STRAIGHT_JOIN、INNER JOIN（內(nèi)連接,或等值連接）：取得兩個表中存在連接匹配關(guān)系的記錄。
LEFT JOIN（左連接）：取得左表（table1）完全記錄，即是右表（table2）并無對應(yīng)匹配記錄。
RIGHT JOIN（右連接）：與 LEFT JOIN 相反，取得右表（table2）完全記錄，即是左表（table1）并無匹配對應(yīng)記錄。
注意：mysql不支持Full join,不過可以通過UNION 關(guān)鍵字來合并 LEFT JOIN 與 RIGHT JOIN來模擬FULL join。
mysql 多表連接查詢方式，因為mysql只支持NLJ算法,所以如果是小表驅(qū)動大表則效率更高；反之則效率下降；因此mysql對內(nèi)連接或等值連接的方式做了一個優(yōu)化,會去判斷join表的數(shù)據(jù)行大小,然后取數(shù)據(jù)行小的表為驅(qū)動表。
INNER JOIN、JOIN、WHERE等值連接和STRAIGHT_JOIN都能表示內(nèi)連接，那平時如何選擇呢？一般情況下用INNER JOIN、JOIN或者WHERE等值連接，因為MySQL 會按照"小表驅(qū)動大表的策略"進行優(yōu)化。當(dāng)出現(xiàn)需要排序時，才考慮用STRAIGHT_JOIN指定某張表為驅(qū)動表。
兩表JOIN優(yōu)化
a.當(dāng)無order by條件時，根據(jù)實際情況，使用left/right/inner join即可，根據(jù)explain優(yōu)化 ；
b.當(dāng)有order by條件時，如select * from a inner join b where 1=1 and other condition order by a.col；使用explain解釋語句；
1）如果第一行的驅(qū)動表為a，則效率會非常高，無需優(yōu)化；
2）否則，因為只能對驅(qū)動表字段直接排序的緣故，會出現(xiàn)using temporary，所以此時需要使用STRAIGHT_JOIN明確a為驅(qū)動表，來達到使用a.col上index的優(yōu)化目的；或者使用left join且Where條件中不含b的過濾條件，此時的結(jié)果集為a的全集，而STRAIGHT_JOIN為inner join且使用a作為驅(qū)動表。注：使用STRAIGHT_JOIN雖然不會using temporary，但也不是一定就能提高效率，如果a表數(shù)據(jù)遠遠超過b表，那么有可能使用STRAIGHT_JOIN時比原來的sql效率更低，所以怎么使用STRAIGHT_JOIN，還是要視情況而定。
在使用left join（或right join）時，應(yīng)該清楚的知道以下幾點：
(1). on與 where的執(zhí)行順序
ON 條件（“A LEFT JOIN B ON 條件表達式”中的ON）用來決定如何從 B 表中檢索數(shù)據(jù)行。如果 B 表中沒有任何一行數(shù)據(jù)匹配 ON 的條件,將會額外生成一行所有列為 NULL 的數(shù)據(jù),在匹配階段 WHERE 子句的條件都不會被使用。僅在匹配階段完成以后，WHERE 子句條件才會被使用。它將從匹配階段產(chǎn)生的數(shù)據(jù)中檢索過濾。
所以我們要注意：在使用Left (right) join的時候，一定要在先給出盡可能多的匹配滿足條件，減少Where的執(zhí)行。
(2).注意ON 子句和 WHERE 子句的不同
即使右表的數(shù)據(jù)不滿足ON后面的條件,也會在結(jié)果集拼接一條為NULL的數(shù)據(jù)行,但WHERE后面的條件不一樣,右表不滿足WHERE的條件,左表關(guān)聯(lián)的數(shù)據(jù)也會被過濾掉。
(3).盡量避免子查詢，而用join
往往性能這玩意兒，更多時候體現(xiàn)在數(shù)據(jù)量比較大的時候，此時，我們應(yīng)該避免復(fù)雜的子查詢。
（1）in 和 not in 要慎用，如：select id from t where num in(1,2,3)對于連續(xù)的數(shù)值，能用 between 就不要用 in：select id from t where num between 1 and 3很多時候用 exists 代替 in 是一個好的選擇：select num from a where num in(select num from b)用下面的語句替換：select num from a where exists(select 1 from b where num=a.num)
（2）Update 語句，如果只更改1、2個字段，不要Update全部字段，否則頻繁調(diào)用會引起明顯的性能消耗，同時帶來大量日志。
（3）join語句,MySQL里面的join是用小表去驅(qū)動大表,而由于MySQL join實現(xiàn)的原理就是做循環(huán)，比如left join就是對左邊的數(shù)據(jù)進行循環(huán)去驅(qū)動右邊的表,左邊有m條記錄匹配,右邊有n條記錄那么就是做m次循環(huán),每次掃描n行數(shù)據(jù),總掃面行數(shù)是m*n行數(shù)據(jù)。左邊返回的結(jié)果集的大小就決定了循環(huán)的次數(shù),故單純的用小表去驅(qū)動大表不一定的正確的,小表的結(jié)果集可能也大于大表的結(jié)果集,所以寫join的時候盡可能的先估計兩張表的可能結(jié)果集,用小結(jié)果集去驅(qū)動大結(jié)果集.值得注意的是在使用left/right join的時候,從表的條件應(yīng)寫在on之后,主表應(yīng)寫在where之后.否則MySQL會當(dāng)作普通的連表查詢；
（4）select count(*) from table；這樣不帶任何條件的count會引起全表掃描，并且沒有任何業(yè)務(wù)意義，是一定要杜絕的；
（5）select * from t 這種語句要盡量避免，使用具體的字段代替*，更有實際意義，需要什么字段就返回什么字段；
（6）數(shù)據(jù)量大的情況下，limit要慎用，因為使用limit m，n方式分頁時，mysql每次都是查詢前m+n條，然后舍棄前m條，所以m越大，偏移量越大，性能就越差。比如：select * from A limit 1000000,20這鐘，查詢效率就會非常低，當(dāng)分頁的頁數(shù)大于一定的數(shù)量之后，就可以換種方式來分頁：select * from A a join （select id from A limit 1000000，20） b on a.id=b.id;
鎖是計算機協(xié)調(diào)多個進程或線程并發(fā)訪問某一資源的機制，在數(shù)據(jù)庫中，除傳統(tǒng)的計算資源（CPU、RAM、I/O）爭用外，數(shù)據(jù)也是一種供許多用戶共享的資源，如何保證數(shù)據(jù)并發(fā)訪問的一致性，有效性是所有數(shù)據(jù)庫必須解決的一個問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素，從這個角度來說，鎖對數(shù)據(jù)庫而言是尤其重要，也更加復(fù)雜。MySQL中的鎖，按照鎖的粒度分為:1、全局鎖，就鎖定數(shù)據(jù)庫中的所有表。2、表級鎖，每次操作鎖住整張表。3、行級鎖，每次操作鎖住對應(yīng)的行數(shù)據(jù)。
全局鎖就是對整個數(shù)據(jù)庫實例加鎖，加鎖后整個實例就處于只讀狀態(tài)，后續(xù)的DML的寫語句，DDL語句，已經(jīng)更新操作的事務(wù)提交語句都將阻塞。其典型的使用場景就是做全庫的邏輯備份，對所有的表進行鎖定，從而獲取一致性視圖，保證數(shù)據(jù)的完整性。但是對數(shù)據(jù)庫加全局鎖是有弊端的，如在主庫上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)會受影響，第二如果是在從庫上備份，那么在備份期間從庫不能執(zhí)行主庫同步過來的二進制日志，會導(dǎo)致主從延遲。
解決辦法是在innodb引擎中，備份時加上--single-transaction參數(shù)來完成不加鎖的一致性數(shù)據(jù)備份。
添加全局鎖: flush tables with read lock; 解鎖 unlock tables。
表級鎖,每次操作會鎖住整張表.鎖定粒度大,發(fā)送鎖沖突的概率最高,并發(fā)讀最低,應(yīng)用在myisam、innodb、BOB等存儲引擎中。表級鎖分為: 表鎖、元數(shù)據(jù)鎖(meta data lock, MDL)和意向鎖。
表鎖又分為: 表共享讀鎖 read lock、表獨占寫鎖write lock
語法: 1、加鎖 lock tables 表名 ... read/write
2、釋放鎖 unlock tables 或者關(guān)閉客戶端連接
注意: 讀鎖不會阻塞其它客戶端的讀，但是會阻塞其它客戶端的寫，寫鎖既會阻塞其它客戶端的讀，又會阻塞其它客戶端的寫。大家可以拿一張表來測試看看。
元數(shù)據(jù)鎖,在加鎖過程中是系統(tǒng)自動控制的，無需顯示使用，在訪問一張表的時候會自動加上，MDL鎖主要作用是維護表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動事務(wù)的時候，不可以對元數(shù)據(jù)進行寫入操作。為了避免DML和DDL沖突，保證讀寫的正確性。
在MySQL5.5中引入了MDL，當(dāng)對一張表進行增刪改查的時候，加MDL讀鎖(共享)；當(dāng)對表結(jié)構(gòu)進行變更操作時，加MDL寫鎖(排他).
查看元數(shù)據(jù)鎖:
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;
意向鎖，為了避免DML在執(zhí)行時，加的行鎖與表鎖的沖突，在innodb中引入了意向鎖，使得表鎖不用檢查每行數(shù)據(jù)是否加鎖，使用意向鎖來減少表鎖的檢查。意向鎖分為,意向共享鎖is由語句select ... lock in share mode添加。意向排他鎖ix,由insert，update，delete，select。。。for update 添加。
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock;
行級鎖，每次操作鎖住對應(yīng)的行數(shù)據(jù)，鎖定粒度最小，發(fā)生鎖沖突的概率最高，并發(fā)讀最高，應(yīng)用在innodb存儲引擎中。
innodb的數(shù)據(jù)是基于索引組織的，行鎖是通過對索引上的索引項加鎖來實現(xiàn)的，而不是對記錄加的鎖，對于行級鎖，主要分為以下三類:
1、行鎖或者叫record lock記錄鎖，鎖定單個行記錄的鎖，防止其他事物對次行進行update和delete操作，在RC,RR隔離級別下都支持。
2、間隙鎖Gap lock,鎖定索引記錄間隙(不含該記錄)，確保索引記錄間隙不變，防止其他事物在這個間隙進行insert操作，產(chǎn)生幻讀，在RR隔離級別下都支持。
3、臨鍵鎖Next-key-lock,行鎖和間隙鎖組合，同時鎖住數(shù)據(jù)，并鎖住數(shù)據(jù)前面的間隙Gap,在RR隔離級別下支持。
innodb實現(xiàn)了以下兩種類型的行鎖
1、共享鎖 S: 允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
2、排他鎖 X: 允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)獲得相同數(shù)據(jù)集的共享鎖和排他鎖。
insert 語句 排他鎖 自動添加的
update語句 排他鎖 自動添加
delete 語句 排他鎖 自動添加
select 正常查詢語句 不加鎖 。。。
select 。。。lock in share mode 共享鎖 需要手動在select 之后加lock in share mode
select 。。。for update 排他鎖 需要手動在select之后添加for update
默認情況下，innodb在repeatable read事務(wù)隔離級別運行，innodb使用next-key鎖進行搜索和索引掃描，以防止幻讀。
間隙鎖唯一目的是防止其它事務(wù)插入間隙，間隙鎖可以共存，一個事務(wù)采用的間隙鎖不會阻止另一個事務(wù)在同一間隙上采用的間隙鎖。
關(guān)于mysql中的樂觀鎖和悲觀鎖面試的時候被問到的概率還是比較大的。
mysql的悲觀鎖：
其實理解起來非常簡單，當(dāng)數(shù)據(jù)被外界修改持保守態(tài)度，包括自身系統(tǒng)當(dāng)前的其他事務(wù)，以及來自外部系統(tǒng)的事務(wù)處理，因此，在整個數(shù)據(jù)處理過程中，將數(shù)據(jù)處于鎖定狀態(tài)。悲觀鎖的實現(xiàn)，往往依靠數(shù)據(jù)庫提供的鎖機制，但是也只有數(shù)據(jù)庫層提供的鎖機制才能真正保證數(shù)據(jù)訪問的排他性，否則，即使在自身系統(tǒng)中實現(xiàn)了加鎖機制，也無法保證外部系統(tǒng)不會修改數(shù)據(jù)。
來點實際的，當(dāng)我們使用悲觀鎖的時候我們首先必須關(guān)閉mysql數(shù)據(jù)庫的自動提交屬性，因為MySQL默認使用autocommit模式，也就是說，當(dāng)你執(zhí)行一個更新操作后，MySQL會立刻將結(jié)果進行提交。
關(guān)閉命令為：set autocommit=0;
悲觀鎖可以使用select…for update實現(xiàn)，在執(zhí)行的時候會鎖定數(shù)據(jù)，雖然會鎖定數(shù)據(jù)，但是不影響其他事務(wù)的普通查詢使用。此處說普通查詢就是平時我們用的：select * from table 語句。在我們使用悲觀鎖的時候事務(wù)中的語句例如：
//開始事務(wù)
begin;/begin work;/start transaction; (三選一)
//查詢信息
select * from order where id=1 for update;
//修改信息
update order set name='names';
//提交事務(wù)
commit;/commit work;(二選一)
此處的查詢語句for update關(guān)鍵字，在事務(wù)中只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一條數(shù)據(jù)時會等待其它事務(wù)結(jié)束后才執(zhí)行，一般的SELECT查詢則不受影響。
執(zhí)行事務(wù)時關(guān)鍵字select…for update會鎖定數(shù)據(jù)，防止其他事務(wù)更改數(shù)據(jù)。但是鎖定數(shù)據(jù)也是有規(guī)則的。
查詢條件與鎖定范圍：
1、具體的主鍵值為查詢條件
比如查詢條件為主鍵ID=1等等，如果此條數(shù)據(jù)存在，則鎖定當(dāng)前行數(shù)據(jù)，如果不存在，則不鎖定。
2、不具體的主鍵值為查詢條件
比如查詢條件為主鍵ID1等等，此時會鎖定整張數(shù)據(jù)表。
3、查詢條件中無主鍵
會鎖定整張數(shù)據(jù)表。
4、如果查詢條件中使用了索引為查詢條件
明確指定索引并且查到，則鎖定整條數(shù)據(jù)。如果找不到指定索引數(shù)據(jù)，則不加鎖。
悲觀鎖的確保了數(shù)據(jù)的安全性，在數(shù)據(jù)被操作的時候鎖定數(shù)據(jù)不被訪問，但是這樣會帶來很大的性能問題。因此悲觀鎖在實際開發(fā)中使用是相對比較少的。
mysql的樂觀鎖：
相對悲觀鎖而言，樂觀鎖假設(shè)數(shù)據(jù)一般情況下不會造成沖突，所以在數(shù)據(jù)進行提交更新的時候，才會對數(shù)據(jù)的沖突與否進行檢測，如果發(fā)現(xiàn)沖突，則讓返回用戶錯誤的信息，讓用戶決定如何去做。
一般來說，實現(xiàn)樂觀鎖的方法是在數(shù)據(jù)表中增加一個version字段，每當(dāng)數(shù)據(jù)更新的時候這個字段執(zhí)行加1操作。這樣當(dāng)數(shù)據(jù)更改的時候，另外一個事務(wù)訪問此條數(shù)據(jù)進行更改的話就會操作失敗，從而避免了并發(fā)操作錯誤。當(dāng)然，還可以將version字段改為時間戳，不過原理都是一樣的。
例如有表student，字段：
id,name,version
1 a 1
當(dāng)事務(wù)一進行更新操作：update student set name='ygz' where id = #{id} and version = #{version};
此時操作完后數(shù)據(jù)會變?yōu)閕d = 1,name = ygz,version = 2，當(dāng)另外一個事務(wù)二同樣執(zhí)行更新操作的時候，卻發(fā)現(xiàn)version != 1，此時事務(wù)二就會操作失敗，從而保證了數(shù)據(jù)的正確性。
悲觀鎖和樂觀鎖都是要根據(jù)具體業(yè)務(wù)來選擇使用，本文僅作簡單介紹。
MySQL索引類型包括：
（1）普通索引
這是最基本的索引，它沒有任何限制。它有以下幾種創(chuàng)建方式：
◆創(chuàng)建索引
CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR類型，length可以小于字段實際長度；如果是BLOB和TEXT類型，必須指定 length，下同。
◆修改表結(jié)構(gòu)
ALTER mytable ADD INDEX [indexName] ON (username(length))
◆創(chuàng)建表的時候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); 刪除索引的語法：
DROP INDEX [indexName] ON mytable;
（2）唯一索引
與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創(chuàng)建方式：
◆創(chuàng)建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
◆修改表結(jié)構(gòu)
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
◆創(chuàng)建表的時候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
（3）主鍵索引
它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創(chuàng)建主鍵索引：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); 當(dāng)然也可以用 ALTER 命令。記?。阂粋€表只能有一個主鍵。
（4）組合索引
為了形象地對比單列索引和組合索引，為表添加多個字段：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL ); 為了進一步榨取MySQL的效率，就要考慮建立組合索引。就是將 name, city, age建到一個索引里：
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表時，usernname長度為 16，這里用 10。這是因為一般情況下名字的長度不會超過10，這樣會加速索引查詢速度，還會減少索引文件的大小，提高INSERT的更新速度。
如果分別在 usernname，city，age上建立單列索引，讓該表有3個單列索引，查詢時和上述的組合索引效率也會大不一樣，遠遠低于我們的組合索引。雖然此時有了三個索引，但MySQL只能用到其中的那個它認為似乎是最有效率的單列索引。
CREATE [UNIQUE] INDEX index_name ON table_name(字段 [ASC|DESC]);
UNIQUE --確保所有的索引列中的值都是可以區(qū)分的。
[ASC|DESC] --在列上按指定排序創(chuàng)建索引。
(創(chuàng)建索引的準則：
1.如果表里有幾百行記錄則可以對其創(chuàng)建索引(表里的記錄行數(shù)越多索引的效果就越明顯)。
2.不要試圖對表創(chuàng)建兩個或三個以上的索引。
3.為頻繁使用的行創(chuàng)建索引。
)
示例
create index i_1 on emp(empno asc);
MySQL是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典MySQL AB公司開發(fā)，屬于Oracle旗下產(chǎn)品，是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一。
端口是3306。
表很多時，使用linux腳本，需要根據(jù)需要修改一下：
和創(chuàng)建一樣，可以加上 if exists
可兩篇文章：
如：
用于在已有的表中添加、刪除或修改列。
添加 ADD
或
默認是添加到最后，但可以指定位置。 FIRST ：添加最前
AFTER 字段名 ：添加指定字段之后
例子：
刪除 DROP
修改 MODIFY 主要修改原列的類型或約束條件 同樣可以用 FIRST 和 AFTER 字段名 ，代表的是修改到哪里。
修改字段名 CHANGE
可以把表2的數(shù)據(jù)復(fù)制到表1中，但 不能復(fù)制約束性條件 。
單行
多行，注意 只有一個VALUES ：
不寫 (行1, 行2...) 這一部分的話，默認一一對應(yīng)
除了以上方法外，還可以用SET為每一行附上相應(yīng)的值。
假如沒有篩選的話，就給全部都修改了?？梢杂? WHERE 篩選。
假如 沒有篩選的話，就給全部刪除了 。相當(dāng)于清空。
清空
先把表刪除，然后再建一個。與 DELETE FROM 相比， TRUNCATE 的效率更快，因為 DELETE FROM 是把記錄逐條刪除的。
查詢執(zhí)行的順序
FROM -- WHERE -- SELECT -- GROUP BY -- HAVING -- ORDER BY -- LIMIT
注意
當(dāng)數(shù)據(jù)很大，上百萬的時候，使用LIMIT ... OFFSET ..的方式進行分頁十分浪費資源且耗時長。最好是結(jié)合WHERE使用，如：
REGEXP 使用正則表達進行匹配。 查詢時，需要搭配WHERE或HAVING使用 。
兩個表之間有交集且要用到兩個表的數(shù)據(jù)時，可以使用內(nèi)連接查詢。
LEFT JOIN 關(guān)鍵字從左表（table1）返回所有的行，即使右表（table2）中沒有匹配。如果右表中沒有匹配，則結(jié)果為 NULL。
用法：
RIGHT JOIN 關(guān)鍵字從右表（table2）返回所有的行，即使左表（table1）中沒有匹配。如果左表中沒有匹配，則結(jié)果為 NULL。 把LEFT JOIN的表1、表2調(diào)換順序，就是REGHT JOIN 。
FULL OUTER JOIN 關(guān)鍵字只要左表（table1）和右表（table2）其中一個表中存在匹配，則返回行. 相當(dāng)于結(jié)合了 LEFT JOIN 和 RIGHT JOIN 的結(jié)果。
但 MySQL中不支持 FULL OUTER JOIN 。
即SELECT嵌套。
IN 一個查詢結(jié)果作為另一個查詢的條件。 如：
EXISTS 用于判斷查詢子句是否有記錄，如果有一條或多條記錄存在返回 True，否則返回 False。True時執(zhí)行。 如：
索引的本質(zhì)是一種排好序的數(shù)據(jù)結(jié)構(gòu)。利用索引可以提高查詢速度。
常見的索引有：
MySQL通過外鍵約束來保證表與表之間的數(shù)據(jù)的完整性和準確性。 外鍵的使用條件：
外鍵的好處：可以使得兩張表關(guān)聯(lián)，保證數(shù)據(jù)的一致性和實現(xiàn)一些級聯(lián)操作。
對已有的兩個表增加外鍵 比如：主表為A，子表為B，外鍵為aid，外鍵約束名字為a_fk_b
為子表添加一個字段，當(dāng)做外鍵
為子表添加外鍵約束條件
假如刪除記錄報錯： [Err] 1451 -Cannot deleteorupdatea parent row: aforeignkeyconstraintfails (...)
這是因為MySQL中設(shè)置了foreign key關(guān)聯(lián)，造成無法更新或刪除數(shù)據(jù)?？梢酝ㄟ^設(shè)置 FOREIGN_KEY_CHECKS 變量來避免這種情況。 第一步：禁用外鍵約束，我們可以使用： SETFOREIGN_KEY_CHECKS=0; 第二步：刪除數(shù)據(jù) 第三步：啟動外鍵約束，我們可以使用： SETFOREIGN_KEY_CHECKS=1; 查看當(dāng)前FOREIGN_KEY_CHECKS的值，可用如下命令： SELECT @@FOREIGN_KEY_CHECKS;
使用 UNION 來組合兩個查詢，如果第一個查詢返回 M 行，第二個查詢返回 N 行，那么組合查詢的結(jié)果一般為 M+N 行。
每個查詢必須包含相同的列、表達式和聚集函數(shù)。
默認會去除相同行，如果需要 保留 相同行，使用 UNION ALL 。
只能包含一個 ORDER BY 子句，并且必須位于語句的最后 。
內(nèi)置函數(shù)很多, 見: MySQL 函數(shù)
我們一般使用 START TRANSACTION 或 BEGIN 開啟事務(wù), COMMIT 提交事務(wù)中的命令, SAVEPOINT : 相當(dāng)于設(shè)置一個還原點, ROLLBACK TO : 回滾到某個還原點下
一般的使用格式如下:
開啟事務(wù)時, 默認加鎖
根據(jù)類型可分為共享鎖（SHARED LOCK）和排他鎖（EXCLUSIVE LOCK）或者叫讀鎖（READ LOCK）和寫鎖（WRITE LOCK）。
根據(jù)粒度劃分又分表鎖和行鎖。表鎖由數(shù)據(jù)庫服務(wù)器實現(xiàn)，行鎖由存儲引擎實現(xiàn)。
除此之外,我們可以顯示加鎖
加鎖時, 如果沒有索引，會鎖表，如果加了索引，就會鎖行
InnoDB默認支持行鎖，獲取鎖是分步的，并不是一次性獲取所有的鎖，因此在鎖競爭的時候就會出現(xiàn)死鎖的情況
解決方法:
即ACID特性:
由于并發(fā)事務(wù)會引發(fā)上面這些問題, 我們可以設(shè)置事務(wù)的隔離級別解決上面的問題.
MySQL的默認隔離級別（可重復(fù)讀）
查看當(dāng)前會話隔離級別
方式1
方式2
設(shè)置隔離級別
主從集群的示意圖如下:
主要涉及三個線程： binlog 線程、 I/O 線程和 SQL 線程。
同步流程:
由于MySQL主從集群只會從主節(jié)點同步到從節(jié)點, 不會反過來同步, 所以需要讀寫分離
讀寫分離需要在業(yè)務(wù)層面實現(xiàn) , 寫數(shù)據(jù)只能在主節(jié)點上完成, 而讀數(shù)據(jù)可以在主節(jié)點或從節(jié)點上完成
索引是幫助MySQL高效獲取數(shù)據(jù)的排好序的數(shù)據(jù)結(jié)構(gòu)
MySQL的索引有
推薦兩個在線工具:
簡單來說, B樹是在紅黑樹(一個平衡二叉樹)的基礎(chǔ)上將一個節(jié)點存放多個值, 實現(xiàn)的, 降低了樹的高度, 每個節(jié)點都存放索引及對應(yīng)數(shù)據(jù)指針, 同一層的節(jié)點是遞增的
而B+樹在B樹的基礎(chǔ)上進行優(yōu)化, 非葉子節(jié)點存放 子節(jié)點的開始的索引, 葉子節(jié)點存放索引和數(shù)據(jù)的指針, 且葉子節(jié)點之間有雙向的指針
如下示意圖:
不同的引擎, 主鍵索引存放的數(shù)據(jù)也不一樣, 比如常見的 MyISAM 和 InnoDB
MyISAM 的B+樹葉子節(jié)點存放表數(shù)據(jù)的指針, InnoDB 的B+樹葉子節(jié)點存放處主鍵外的數(shù)據(jù)
其他的:
即多個列組成一個索引, 語法:
由于聯(lián)合索引的B+樹的結(jié)構(gòu), 根據(jù)列建立, 所以我們的查找條件也要根據(jù)索引列的順序( where column1=x, column2=y,columnN... ), 否則會全表掃描
如果你對列進行了 （+，-，*，/，!） , 那么都將不會走索引。
OR 引起的索引失效
OR 導(dǎo)致索引是在特定情況下的，并不是所有的 OR 都是使索引失效，如果OR連接的是 同 一個字段，那么索引 不會失效 ， 反之索引失效 。
這個我相信大家都明白，模糊搜索如果你前綴也進行模糊搜索，那么不會走索引。
這兩種用法，也將使索引失效。另 IN 會走索引，但是當(dāng)IN的取值范圍較大時會導(dǎo)致索引失效，走全表掃描, 見: MySQL中使用IN會不會走索引
不走索引。
走索引。
所以設(shè)計表的時候, 建議不可為空, 而是將默認值設(shè)置為 "" ( NOT NULL DEFAULT "" )
重慶分公司
重慶分公司
