MYSQL使用基礎(chǔ)、進(jìn)階分享

MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB公司開(kāi)發(fā)，屬于Oracle旗下產(chǎn)品，是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一。

成都網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)公司！專注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)公司、微信開(kāi)發(fā)、小程序制作、集團(tuán)成都企業(yè)網(wǎng)站定制等服務(wù)項(xiàng)目。核心團(tuán)隊(duì)均擁有互聯(lián)網(wǎng)行業(yè)多年經(jīng)驗(yàn)，服務(wù)眾多知名企業(yè)客戶；涵蓋的客戶類型包括：成都房屋鑒定等眾多領(lǐng)域，積累了大量豐富的經(jīng)驗(yàn)，同時(shí)也獲得了客戶的一致稱揚(yáng)！

端口是3306。

表很多時(shí)，使用linux腳本，需要根據(jù)需要修改一下：

和創(chuàng)建一樣，可以加上 if exists

可兩篇文章：

如：

用于在已有的表中添加、刪除或修改列。

添加 ADD

或

默認(rèn)是添加到最后，但可以指定位置。 FIRST ：添加最前

AFTER 字段名 ：添加指定字段之后

例子：

刪除 DROP

修改 MODIFY 主要修改原列的類型或約束條件 同樣可以用 FIRST 和 AFTER 字段名 ，代表的是修改到哪里。

修改字段名 CHANGE

可以把表2的數(shù)據(jù)復(fù)制到表1中，但 不能復(fù)制約束性條件 。

單行

多行，注意 只有一個(gè)VALUES ：

不寫(xiě) (行1, 行2...) 這一部分的話，默認(rèn)一一對(duì)應(yīng)

除了以上方法外，還可以用SET為每一行附上相應(yīng)的值。

假如沒(méi)有篩選的話，就給全部都修改了?？梢杂? WHERE 篩選。

假如 沒(méi)有篩選的話，就給全部刪除了 。相當(dāng)于清空。

清空

先把表刪除，然后再建一個(gè)。與 DELETE FROM 相比， TRUNCATE 的效率更快，因?yàn)? DELETE FROM 是把記錄逐條刪除的。

查詢執(zhí)行的順序

FROM -- WHERE -- SELECT -- GROUP BY -- HAVING -- ORDER BY -- LIMIT

注意

當(dāng)數(shù)據(jù)很大，上百萬(wàn)的時(shí)候，使用LIMIT ... OFFSET ..的方式進(jìn)行分頁(yè)十分浪費(fèi)資源且耗時(shí)長(zhǎng)。最好是結(jié)合WHERE使用，如：

REGEXP 使用正則表達(dá)進(jìn)行匹配。 查詢時(shí)，需要搭配WHERE或HAVING使用 。

兩個(gè)表之間有交集且要用到兩個(gè)表的數(shù)據(jù)時(shí)，可以使用內(nèi)連接查詢。

LEFT JOIN 關(guān)鍵字從左表（table1）返回所有的行，即使右表（table2）中沒(méi)有匹配。如果右表中沒(méi)有匹配，則結(jié)果為 NULL。

用法：

RIGHT JOIN 關(guān)鍵字從右表（table2）返回所有的行，即使左表（table1）中沒(méi)有匹配。如果左表中沒(méi)有匹配，則結(jié)果為 NULL。 把LEFT JOIN的表1、表2調(diào)換順序，就是REGHT JOIN 。

FULL OUTER JOIN 關(guān)鍵字只要左表（table1）和右表（table2）其中一個(gè)表中存在匹配，則返回行. 相當(dāng)于結(jié)合了 LEFT JOIN 和 RIGHT JOIN 的結(jié)果。

但 MySQL中不支持 FULL OUTER JOIN 。

即SELECT嵌套。

IN 一個(gè)查詢結(jié)果作為另一個(gè)查詢的條件。 如：

EXISTS 用于判斷查詢子句是否有記錄，如果有一條或多條記錄存在返回 True，否則返回 False。True時(shí)執(zhí)行。 如：

索引的本質(zhì)是一種排好序的數(shù)據(jù)結(jié)構(gòu)。利用索引可以提高查詢速度。

常見(jiàn)的索引有：

MySQL通過(guò)外鍵約束來(lái)保證表與表之間的數(shù)據(jù)的完整性和準(zhǔn)確性。 外鍵的使用條件：

外鍵的好處：可以使得兩張表關(guān)聯(lián)，保證數(shù)據(jù)的一致性和實(shí)現(xiàn)一些級(jí)聯(lián)操作。

對(duì)已有的兩個(gè)表增加外鍵 比如：主表為A，子表為B，外鍵為aid，外鍵約束名字為a_fk_b

為子表添加一個(gè)字段，當(dāng)做外鍵

為子表添加外鍵約束條件

假如刪除記錄報(bào)錯(cuò)： [Err] 1451 -Cannot deleteorupdatea parent row: aforeignkeyconstraintfails (...)

這是因?yàn)镸ySQL中設(shè)置了foreign key關(guān)聯(lián)，造成無(wú)法更新或刪除數(shù)據(jù)?？梢酝ㄟ^(guò)設(shè)置 FOREIGN_KEY_CHECKS 變量來(lái)避免這種情況。 第一步：禁用外鍵約束，我們可以使用： SETFOREIGN_KEY_CHECKS=0; 第二步：刪除數(shù)據(jù) 第三步：?jiǎn)?dòng)外鍵約束，我們可以使用： SETFOREIGN_KEY_CHECKS=1; 查看當(dāng)前FOREIGN_KEY_CHECKS的值，可用如下命令： SELECT @@FOREIGN_KEY_CHECKS;

使用 UNION 來(lái)組合兩個(gè)查詢，如果第一個(gè)查詢返回 M 行，第二個(gè)查詢返回 N 行，那么組合查詢的結(jié)果一般為 M+N 行。

每個(gè)查詢必須包含相同的列、表達(dá)式和聚集函數(shù)。

默認(rèn)會(huì)去除相同行，如果需要 保留 相同行，使用 UNION ALL 。

只能包含一個(gè) ORDER BY 子句，并且必須位于語(yǔ)句的最后 。

內(nèi)置函數(shù)很多, 見(jiàn): MySQL 函數(shù)

我們一般使用 START TRANSACTION 或 BEGIN 開(kāi)啟事務(wù), COMMIT 提交事務(wù)中的命令, SAVEPOINT : 相當(dāng)于設(shè)置一個(gè)還原點(diǎn), ROLLBACK TO : 回滾到某個(gè)還原點(diǎn)下

一般的使用格式如下:

開(kāi)啟事務(wù)時(shí), 默認(rèn)加鎖

根據(jù)類型可分為共享鎖（SHARED LOCK）和排他鎖（EXCLUSIVE LOCK）或者叫讀鎖（READ LOCK）和寫(xiě)鎖（WRITE LOCK）。

根據(jù)粒度劃分又分表鎖和行鎖。表鎖由數(shù)據(jù)庫(kù)服務(wù)器實(shí)現(xiàn)，行鎖由存儲(chǔ)引擎實(shí)現(xiàn)。

除此之外,我們可以顯示加鎖

加鎖時(shí), 如果沒(méi)有索引，會(huì)鎖表，如果加了索引，就會(huì)鎖行

InnoDB默認(rèn)支持行鎖，獲取鎖是分步的，并不是一次性獲取所有的鎖，因此在鎖競(jìng)爭(zhēng)的時(shí)候就會(huì)出現(xiàn)死鎖的情況

解決方法:

即ACID特性:

由于并發(fā)事務(wù)會(huì)引發(fā)上面這些問(wèn)題, 我們可以設(shè)置事務(wù)的隔離級(jí)別解決上面的問(wèn)題.

MySQL的默認(rèn)隔離級(jí)別（可重復(fù)讀）

查看當(dāng)前會(huì)話隔離級(jí)別

方式1

方式2

設(shè)置隔離級(jí)別

主從集群的示意圖如下:

主要涉及三個(gè)線程： binlog 線程、 I/O 線程和 SQL 線程。

同步流程:

由于MySQL主從集群只會(huì)從主節(jié)點(diǎn)同步到從節(jié)點(diǎn), 不會(huì)反過(guò)來(lái)同步, 所以需要讀寫(xiě)分離

讀寫(xiě)分離需要在業(yè)務(wù)層面實(shí)現(xiàn) , 寫(xiě)數(shù)據(jù)只能在主節(jié)點(diǎn)上完成, 而讀數(shù)據(jù)可以在主節(jié)點(diǎn)或從節(jié)點(diǎn)上完成

索引是幫助MySQL高效獲取數(shù)據(jù)的排好序的數(shù)據(jù)結(jié)構(gòu)

MySQL的索引有

推薦兩個(gè)在線工具:

簡(jiǎn)單來(lái)說(shuō), B樹(shù)是在紅黑樹(shù)(一個(gè)平衡二叉樹(shù))的基礎(chǔ)上將一個(gè)節(jié)點(diǎn)存放多個(gè)值, 實(shí)現(xiàn)的, 降低了樹(shù)的高度, 每個(gè)節(jié)點(diǎn)都存放索引及對(duì)應(yīng)數(shù)據(jù)指針, 同一層的節(jié)點(diǎn)是遞增的

而B(niǎo)+樹(shù)在B樹(shù)的基礎(chǔ)上進(jìn)行優(yōu)化, 非葉子節(jié)點(diǎn)存放 子節(jié)點(diǎn)的開(kāi)始的索引, 葉子節(jié)點(diǎn)存放索引和數(shù)據(jù)的指針, 且葉子節(jié)點(diǎn)之間有雙向的指針

如下示意圖:

不同的引擎, 主鍵索引存放的數(shù)據(jù)也不一樣, 比如常見(jiàn)的 MyISAM 和 InnoDB

MyISAM 的B+樹(shù)葉子節(jié)點(diǎn)存放表數(shù)據(jù)的指針, InnoDB 的B+樹(shù)葉子節(jié)點(diǎn)存放處主鍵外的數(shù)據(jù)

其他的:

即多個(gè)列組成一個(gè)索引, 語(yǔ)法:

由于聯(lián)合索引的B+樹(shù)的結(jié)構(gòu), 根據(jù)列建立, 所以我們的查找條件也要根據(jù)索引列的順序( where column1=x, column2=y,columnN... ), 否則會(huì)全表掃描

如果你對(duì)列進(jìn)行了 （+，-，*，/，!） , 那么都將不會(huì)走索引。

OR 引起的索引失效

OR 導(dǎo)致索引是在特定情況下的，并不是所有的 OR 都是使索引失效，如果OR連接的是 同 一個(gè)字段，那么索引 不會(huì)失效 ， 反之索引失效 。

這個(gè)我相信大家都明白，模糊搜索如果你前綴也進(jìn)行模糊搜索，那么不會(huì)走索引。

這兩種用法，也將使索引失效。另 IN 會(huì)走索引，但是當(dāng)IN的取值范圍較大時(shí)會(huì)導(dǎo)致索引失效，走全表掃描, 見(jiàn): MySQL中使用IN會(huì)不會(huì)走索引

不走索引。

走索引。

所以設(shè)計(jì)表的時(shí)候, 建議不可為空, 而是將默認(rèn)值設(shè)置為 "" ( NOT NULL DEFAULT "" )

MySQL簡(jiǎn)單介紹——換個(gè)角度認(rèn)識(shí)MySQL

1、InnoDB存儲(chǔ)引擎

Mysql版本=5.5 默認(rèn)的存儲(chǔ)引擎，MySQL推薦使用的存儲(chǔ)引擎。支持事務(wù)，行級(jí)鎖定，外鍵約束。事務(wù)安全型存儲(chǔ)引擎。更加注重?cái)?shù)據(jù)的完整性和安全性。

存儲(chǔ)格式 : 數(shù)據(jù)，索引集中存儲(chǔ)，存儲(chǔ)于同一個(gè)表空間文件中。

InnoDB的行鎖模式及其加鎖方法： InnoDB中有以下兩種類型的行鎖：共享鎖（讀鎖： 允許事務(wù)對(duì)一條行數(shù)據(jù)進(jìn)行讀?。┖?互斥鎖（寫(xiě)鎖： 允許事務(wù)對(duì)一條行數(shù)據(jù)進(jìn)行刪除或更新）， 對(duì)于update，insert，delete語(yǔ)句，InnoDB會(huì)自動(dòng)給設(shè)計(jì)的數(shù)據(jù)集加互斥鎖，對(duì)于普通的select語(yǔ)句，InnoDB不會(huì)加任何鎖。

InnoDB行鎖的實(shí)現(xiàn)方式： InnoDB行鎖是通過(guò)給索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的，如果沒(méi)有索引，InnoDB將通過(guò)隱藏的聚簇索引來(lái)對(duì)記錄加鎖。InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：如果不通過(guò)索引條件檢索數(shù)據(jù)，那么InnoDB將對(duì)表中的所有記錄加鎖，實(shí)際效果跟表鎖一樣。

（1）在不通過(guò)索引條件查詢時(shí)，InnoDB會(huì)鎖定表中的所有記錄。

（2）Mysql的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖，所以雖然是訪問(wèn)不同行的記錄，但是如果使用相同的索引鍵，是會(huì)出現(xiàn)沖突的。

（3）當(dāng)表有多個(gè)索引的時(shí)候，不同的事務(wù)可以使用不同的索引鎖定不同的行，但都是通過(guò)行鎖來(lái)對(duì)數(shù)據(jù)加鎖。

優(yōu)點(diǎn)：

1、支持事務(wù)處理、ACID事務(wù)特性；

2、實(shí)現(xiàn)了SQL標(biāo)準(zhǔn)的四種隔離級(jí)別（ 原子性（ Atomicity ）、一致性（ Consistency ）、隔離性（Isolation ）和持續(xù)性（Durability ））；

3、支持行級(jí)鎖和外鍵約束；

4、可以利用事務(wù)日志進(jìn)行數(shù)據(jù)恢復(fù)。

5、鎖級(jí)別為行鎖，行鎖優(yōu)點(diǎn)是適用于高并發(fā)的頻繁表修改，高并發(fā)是性能優(yōu)于 MyISAM。缺點(diǎn)是系統(tǒng)消耗較大。

6、索引不僅緩存自身，也緩存數(shù)據(jù)，相比 MyISAM 需要更大的內(nèi)存。

缺點(diǎn)：

因?yàn)樗鼪](méi)有保存表的行數(shù)，當(dāng)使用COUNT統(tǒng)計(jì)時(shí)會(huì)掃描全表。

使用場(chǎng)景：

(1)可靠性要求比較高，或者要求事務(wù)；(2)表更新和查詢都相當(dāng)?shù)念l繁，并且表鎖定的機(jī)會(huì)比較大的情況。

2、 MyISAM存儲(chǔ)引擎

MySQL= 5.5 MySQL默認(rèn)的存儲(chǔ)引擎。ISAM：Indexed Sequential Access Method(索引順序存取方法)的縮寫(xiě)，是一種文件系統(tǒng)。擅長(zhǎng)與處理，高速讀與寫(xiě)。

功能：

（1）支持?jǐn)?shù)據(jù)壓縮存儲(chǔ),但壓縮后的表變成了只讀表，不可寫(xiě)；如果需要更新數(shù)據(jù)，則需要先解壓后更新。

（2）支持表級(jí)鎖定，不支持高并發(fā)；

（3）支持并發(fā)插入。寫(xiě)操作中的插入操作，不會(huì)阻塞讀操作（其他操作）；

優(yōu)點(diǎn)：

1.高性能讀取；

2.因?yàn)樗４媪吮淼男袛?shù)，當(dāng)使用COUNT統(tǒng)計(jì)時(shí)不會(huì)掃描全表；

缺點(diǎn)：

1、鎖級(jí)別為表鎖，表鎖優(yōu)點(diǎn)是開(kāi)銷小，加鎖快；缺點(diǎn)是鎖粒度大，發(fā)生鎖沖動(dòng)概率較高，容納并發(fā)能力低，這個(gè)引擎適合查詢?yōu)橹鞯臉I(yè)務(wù)。

2、此引擎不支持事務(wù)，也不支持外鍵。

3、INSERT和UPDATE操作需要鎖定整個(gè)表；

使用場(chǎng)景：

(1)做很多count 的計(jì)算；(2)插入不頻繁，查詢非常頻繁；(3)沒(méi)有事務(wù)。

InnoDB和MyISAM一些細(xì)節(jié)上的差別：

1、InnoDB不支持FULLTEXT類型的索引，MySQL5.6之后已經(jīng)支持（實(shí)驗(yàn)性）。

2、InnoDB中不保存表的 具體行數(shù)，也就是說(shuō)，執(zhí)行select count() from table時(shí)，InnoDB要掃描一遍整個(gè)表來(lái)計(jì)算有多少行，但是MyISAM只要簡(jiǎn)單的讀出保存好的行數(shù)即可。注意的是，當(dāng)count()語(yǔ)句包含 where條件時(shí)，兩種表的操作是一樣的。

3、對(duì)于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。

4、DELETE FROM table時(shí)，InnoDB不會(huì)重新建立表，而是一行一行的刪除。

5、LOAD TABLE FROM MASTER操作對(duì)InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導(dǎo)入數(shù)據(jù)后再改成InnoDB表，但是對(duì)于使用的額外的InnoDB特性（例如外鍵）的表不適用。

6、另外，InnoDB表的行鎖也不是絕對(duì)的，如果在執(zhí)行一個(gè)SQL語(yǔ)句時(shí)MySQL不能確定要掃描的范圍，InnoDB表同樣會(huì)鎖全表。

1．索引概述

利用關(guān)鍵字，就是記錄的部分?jǐn)?shù)據(jù)（某個(gè)字段，某些字段，某個(gè)字段的一部分），建立與記錄位置的對(duì)應(yīng)關(guān)系，就是索引。索引的關(guān)鍵字一定是排序的。索引本質(zhì)上是表字段的有序子集，它是提高查詢速度最有效的方法。一個(gè)沒(méi)有建立任何索引的表，就相當(dāng)于一本沒(méi)有目錄的書(shū)，在每次查詢時(shí)就會(huì)進(jìn)行全表掃描，這樣會(huì)導(dǎo)致查詢效率極低、速度也極慢。如果建立索引，那么就好比一本添加的目錄，通過(guò)目錄的指引，迅速翻閱到指定的章節(jié)，提升的查詢性能，節(jié)約了查詢資源。

2．索引種類

從索引的定義方式和用途中來(lái)看：主鍵索引，唯一索引，普通索引，全文索引。

無(wú)論任何類型，都是通過(guò)建立關(guān)鍵字與位置的對(duì)應(yīng)關(guān)系來(lái)實(shí)現(xiàn)的。索引是通過(guò)關(guān)鍵字找對(duì)應(yīng)的記錄的地址。

以上類型的差異：對(duì)索引關(guān)鍵字的要求不同。

關(guān)鍵字：記錄的部分?jǐn)?shù)據(jù)（某個(gè)字段，某些字段，某個(gè)字段的一部分）。

普通索引,index：對(duì)關(guān)鍵字沒(méi)有要求。

唯一索引,unique index：要求關(guān)鍵字不能重復(fù)。同時(shí)增加唯一約束。

主鍵索引,primary key：要求關(guān)鍵字不能重復(fù)，也不能為NULL。同時(shí)增加主鍵約束。

全文索引,fulltext key：關(guān)鍵字的來(lái)源不是所有字段的數(shù)據(jù)，而是從字段中提取的特別關(guān)鍵詞。

PS：這里主鍵索引和唯一索引的區(qū)別在于：主鍵索引不能為空值，唯一索引允許空值；主鍵索引在一張表內(nèi)只能創(chuàng)建一個(gè)，唯一索引可以創(chuàng)建多個(gè)。主鍵索引肯定是唯一索引，但唯一索引不一定是主鍵索引。

3.索引原則

如果索引不遵循使用原則，則可能導(dǎo)致索引無(wú)效。

（1）列獨(dú)立

如果需要某個(gè)字段上使用索引，則需要在字段參與的表達(dá)中，保證字段獨(dú)立在一側(cè)。否則索引不會(huì)用到索引, 例如這條sql就不會(huì)用到索引:select * from A where id+1=10;

（2）左原則

Like：匹配模式必須要左邊確定不能以通配符開(kāi)頭。例如:select * from A where name like '%小明%' ，不會(huì)用到索引，而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 '%小明%' 這三種情況都會(huì)用到name的索引;

復(fù)合索引：一個(gè)索引關(guān)聯(lián)多個(gè)字段，僅僅針對(duì)左邊字段有效果，添加復(fù)合索引時(shí)，第一個(gè)字段很重要，只有包含第一個(gè)字段作為查詢條件的情況才會(huì)使用復(fù)合索引(必須用到建索引時(shí)選擇的第一個(gè)字段作為查詢條件,其他字段的順序無(wú)關(guān)),而且查詢條件只能出現(xiàn)and拼接,不能用or,否則則無(wú)法使用索引.

（3）OR的使用

必須要保證 OR 兩端的條件都存在可以用的索引，該查詢才可以使用索引。

（4）MySQL智能選擇

即使?jié)M足了上面說(shuō)原則，MySQL也能棄用索引，例如：select * from A where id 1;這里棄用索引的主要原因：查詢即使使用索引，會(huì)導(dǎo)致出現(xiàn)大量的隨機(jī)IO，相對(duì)于從數(shù)據(jù)記錄的第一條遍歷到最后一條的順序IO開(kāi)銷，還要大。

4.索引的使用場(chǎng)景

（1）索引檢索：檢索數(shù)據(jù)時(shí)使用索引。

（2）索引排序: 如果order by 排序需要的字段上存在索引，則可能使用到索引。

（3）索引覆蓋: 索引擁有的關(guān)鍵字內(nèi)容，覆蓋了查詢所需要的全部數(shù)據(jù)，此時(shí)，就不需要在數(shù)據(jù)區(qū)獲取數(shù)據(jù)，僅僅在索引區(qū)即可。覆蓋就是直接在索引區(qū)獲取內(nèi)容，而不需要在數(shù)據(jù)區(qū)獲取。例如: select name from A where name like '小明%';

建立索引索引時(shí)，不能僅僅考慮where檢索，同時(shí)考慮其他的使用場(chǎng)景。（在所有的where字段上增加索引，就是不合理的）

5.前綴索引

前綴索引是建立索引關(guān)鍵字一種方案。通常會(huì)使用字段的整體作為索引關(guān)鍵字。有時(shí)，即使使用字段前部分?jǐn)?shù)據(jù)，也可以去識(shí)別某些記錄。就比如一個(gè)班級(jí)里，我要找王xx，假如姓王的只有1個(gè)人，那么就可以建一個(gè)關(guān)鍵字為'王'的前綴索引。語(yǔ)法：Index `index_name` (`index_field`(N))使用index_name前N個(gè)字符建立的索引。

6.索引失效

（1） 應(yīng)盡量避免在 where 子句中使用 != 或 操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描；

（2） 應(yīng)盡量避免在 where 子句中使用 or 來(lái)連接條件，如果一個(gè)字段有索引，一個(gè)字段沒(méi)有索引，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；

（3） 應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；

（4）應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；如select id from t where num/2 = 100；

（5） 應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；如：select id from t where substring(name,1,3) = ’abc’ ；

（6）應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行類型轉(zhuǎn)換，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描； 如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號(hào)引用起來(lái),如select id from t where id = 1；如果id字段在表設(shè)計(jì)中是varchar類型，那么即使id列上存的是數(shù)字，在查詢時(shí)也一定要用varchar去匹配，sql應(yīng)改為select id from t where id = '1'；

（7）應(yīng)盡量避免在where子句中單獨(dú)引用復(fù)合索引里非第一位置的索引；

join 的兩種算法：BNL 和 NLJ

NLJ（Nested Loop Join）嵌套循環(huán)算法；以如下 SQL 為例：

select * from t1 join t2 on t1.a=t2.a

SQL 執(zhí)行時(shí)內(nèi)部流程是這樣的：

1. 先從 t1（假設(shè)這里 t1 被選為驅(qū)動(dòng)表）中取出一行數(shù)據(jù) X；

2. 從 X 中取出關(guān)聯(lián)字段 a 值，去 t2 中進(jìn)行查找，滿足條件的行取出；

3. 重復(fù)1、2步驟，直到表 t1 最后一行循環(huán)結(jié)束。

這就是一個(gè)嵌套循環(huán)的過(guò)程，如果在被驅(qū)動(dòng)表上查找數(shù)據(jù)時(shí)可以使用索引，總的對(duì)比計(jì)算次數(shù)等于驅(qū)動(dòng)表滿足 where 條件的行數(shù)。假設(shè)這里 t1、t2都是1萬(wàn)行，則只需要 1萬(wàn)次計(jì)算，這里用到的是Index Nested-Loops Join（INLJ，基于索引的嵌套循環(huán)聯(lián)接）。

如果 t1、t2 的 a 字段都沒(méi)有索引，還按照上述的嵌套循環(huán)流程查找數(shù)據(jù)呢？每次在被驅(qū)動(dòng)表上查找數(shù)據(jù)時(shí)都是一次全表掃描，要做1萬(wàn)次全表掃描，掃描行數(shù)等于 1萬(wàn)+1萬(wàn)*1萬(wàn)，這個(gè)效率很低，如果表行數(shù)更多，掃描行數(shù)動(dòng)輒幾百億，所以優(yōu)化器肯定不會(huì)使用這樣的算法，而是選擇 BNL 算法；

BNLJ（Block Nested Loop Join）塊嵌套循環(huán)算法；

1. 把 t1 表（假設(shè)這里 t1 被選為驅(qū)動(dòng)表）滿足條件的數(shù)據(jù)全部取出放到線程的 join buffer 中；

2. 每次取 t2 表一行數(shù)據(jù)，去 joinbuffer 中進(jìn)行查找，滿足條件的行取出，直到表 t2 最后一行循環(huán)結(jié)束。

這個(gè)算法下，執(zhí)行計(jì)劃的 Extra 中會(huì)出現(xiàn) Using join buffer(Block Nested Loop)，t1、t2 都做了一次全表掃描，總的掃描行數(shù)等于 1萬(wàn)+1萬(wàn)。但是由于 joinbuffer 維護(hù)的是一個(gè)無(wú)序數(shù)組，每次在 joinbuffer 中查找都要遍歷所有行，總的內(nèi)存計(jì)算次數(shù)等于1萬(wàn)*1萬(wàn)。另外如果 joinbuffer 不夠大放不下驅(qū)動(dòng)表的數(shù)據(jù)，則要分多次執(zhí)行上面的流程，會(huì)導(dǎo)致被驅(qū)動(dòng)表也做多次全表掃描。

BNLJ相對(duì)于NLJ的優(yōu)點(diǎn)在于，驅(qū)動(dòng)層可以先將部分?jǐn)?shù)據(jù)加載進(jìn)buffer，這種方法的直接影響就是將大大減少內(nèi)層循環(huán)的次數(shù)，提高join的效率。

例如：

如果內(nèi)層循環(huán)有100條記錄，外層循環(huán)也有100條記錄，這樣的話，每次外層循環(huán)先將10條記錄放到buffer中，內(nèi)層循環(huán)的100條記錄每條與這個(gè)buffer中的10條記錄進(jìn)行匹配，只需要匹配內(nèi)層循環(huán)總記錄數(shù)次即可結(jié)束一次循環(huán)（在這里，即只需要匹配100次即可結(jié)束），然后將匹配成功的記錄連接后放入結(jié)果集中，接著，外層循環(huán)繼續(xù)向buffer中放入10條記錄，同理進(jìn)行匹配，并將成功的記錄連接后放入結(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)連接,或等值連接）：取得兩個(gè)表中存在連接匹配關(guān)系的記錄。

LEFT JOIN（左連接）：取得左表（table1）完全記錄，即是右表（table2）并無(wú)對(duì)應(yīng)匹配記錄。

RIGHT JOIN（右連接）：與 LEFT JOIN 相反，取得右表（table2）完全記錄，即是左表（table1）并無(wú)匹配對(duì)應(yīng)記錄。

注意：mysql不支持Full join,不過(guò)可以通過(guò)UNION 關(guān)鍵字來(lái)合并 LEFT JOIN 與 RIGHT JOIN來(lái)模擬FULL join。

mysql 多表連接查詢方式，因?yàn)閙ysql只支持NLJ算法,所以如果是小表驅(qū)動(dòng)大表則效率更高；反之則效率下降；因此mysql對(duì)內(nèi)連接或等值連接的方式做了一個(gè)優(yōu)化,會(huì)去判斷join表的數(shù)據(jù)行大小,然后取數(shù)據(jù)行小的表為驅(qū)動(dòng)表。

INNER JOIN、JOIN、WHERE等值連接和STRAIGHT_JOIN都能表示內(nèi)連接，那平時(shí)如何選擇呢？一般情況下用INNER JOIN、JOIN或者WHERE等值連接，因?yàn)镸ySQL 會(huì)按照"小表驅(qū)動(dòng)大表的策略"進(jìn)行優(yōu)化。當(dāng)出現(xiàn)需要排序時(shí)，才考慮用STRAIGHT_JOIN指定某張表為驅(qū)動(dòng)表。

兩表JOIN優(yōu)化

a.當(dāng)無(wú)order by條件時(shí)，根據(jù)實(shí)際情況，使用left/right/inner join即可，根據(jù)explain優(yōu)化 ；

b.當(dāng)有order by條件時(shí)，如select * from a inner join b where 1=1 and other condition order by a.col；使用explain解釋語(yǔ)句；

1）如果第一行的驅(qū)動(dòng)表為a，則效率會(huì)非常高，無(wú)需優(yōu)化；

2）否則，因?yàn)橹荒軐?duì)驅(qū)動(dòng)表字段直接排序的緣故，會(huì)出現(xiàn)using temporary，所以此時(shí)需要使用STRAIGHT_JOIN明確a為驅(qū)動(dòng)表，來(lái)達(dá)到使用a.col上index的優(yōu)化目的；或者使用left join且Where條件中不含b的過(guò)濾條件，此時(shí)的結(jié)果集為a的全集，而STRAIGHT_JOIN為inner join且使用a作為驅(qū)動(dòng)表。注：使用STRAIGHT_JOIN雖然不會(huì)using temporary，但也不是一定就能提高效率，如果a表數(shù)據(jù)遠(yuǎn)遠(yuǎn)超過(guò)b表，那么有可能使用STRAIGHT_JOIN時(shí)比原來(lái)的sql效率更低，所以怎么使用STRAIGHT_JOIN，還是要視情況而定。

在使用left join（或right join）時(shí)，應(yīng)該清楚的知道以下幾點(diǎn)：

(1). on與 where的執(zhí)行順序

ON 條件（“A LEFT JOIN B ON 條件表達(dá)式”中的ON）用來(lái)決定如何從 B 表中檢索數(shù)據(jù)行。如果 B 表中沒(méi)有任何一行數(shù)據(jù)匹配 ON 的條件,將會(huì)額外生成一行所有列為 NULL 的數(shù)據(jù),在匹配階段 WHERE 子句的條件都不會(huì)被使用。僅在匹配階段完成以后，WHERE 子句條件才會(huì)被使用。它將從匹配階段產(chǎn)生的數(shù)據(jù)中檢索過(guò)濾。

所以我們要注意：在使用Left (right) join的時(shí)候，一定要在先給出盡可能多的匹配滿足條件，減少Where的執(zhí)行。

(2).注意ON 子句和 WHERE 子句的不同

即使右表的數(shù)據(jù)不滿足ON后面的條件,也會(huì)在結(jié)果集拼接一條為NULL的數(shù)據(jù)行,但WHERE后面的條件不一樣,右表不滿足WHERE的條件,左表關(guān)聯(lián)的數(shù)據(jù)也會(huì)被過(guò)濾掉。

(3).盡量避免子查詢，而用join

往往性能這玩意兒，更多時(shí)候體現(xiàn)在數(shù)據(jù)量比較大的時(shí)候，此時(shí)，我們應(yīng)該避免復(fù)雜的子查詢。

（1）in 和 not in 要慎用，如：select id from t where num in(1,2,3)對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in：select id from t where num between 1 and 3很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇：select num from a where num in(select num from b)用下面的語(yǔ)句替換：select num from a where exists(select 1 from b where num=a.num)

（2）Update 語(yǔ)句，如果只更改1、2個(gè)字段，不要Update全部字段，否則頻繁調(diào)用會(huì)引起明顯的性能消耗，同時(shí)帶來(lái)大量日志。

（3）join語(yǔ)句,MySQL里面的join是用小表去驅(qū)動(dòng)大表,而由于MySQL join實(shí)現(xiàn)的原理就是做循環(huán)，比如left join就是對(duì)左邊的數(shù)據(jù)進(jìn)行循環(huán)去驅(qū)動(dòng)右邊的表,左邊有m條記錄匹配,右邊有n條記錄那么就是做m次循環(huán),每次掃描n行數(shù)據(jù),總掃面行數(shù)是m*n行數(shù)據(jù)。左邊返回的結(jié)果集的大小就決定了循環(huán)的次數(shù),故單純的用小表去驅(qū)動(dòng)大表不一定的正確的,小表的結(jié)果集可能也大于大表的結(jié)果集,所以寫(xiě)join的時(shí)候盡可能的先估計(jì)兩張表的可能結(jié)果集,用小結(jié)果集去驅(qū)動(dòng)大結(jié)果集.值得注意的是在使用left/right join的時(shí)候,從表的條件應(yīng)寫(xiě)在on之后,主表應(yīng)寫(xiě)在where之后.否則MySQL會(huì)當(dāng)作普通的連表查詢；

（4）select count(*) from table；這樣不帶任何條件的count會(huì)引起全表掃描，并且沒(méi)有任何業(yè)務(wù)意義，是一定要杜絕的；

（5）select * from t 這種語(yǔ)句要盡量避免，使用具體的字段代替*，更有實(shí)際意義，需要什么字段就返回什么字段；

（6）數(shù)據(jù)量大的情況下，limit要慎用，因?yàn)槭褂胠imit m，n方式分頁(yè)時(shí)，mysql每次都是查詢前m+n條，然后舍棄前m條，所以m越大，偏移量越大，性能就越差。比如：select * from A limit 1000000,20這鐘，查詢效率就會(huì)非常低，當(dāng)分頁(yè)的頁(yè)數(shù)大于一定的數(shù)量之后，就可以換種方式來(lái)分頁(yè)：select * from A a join （select id from A limit 1000000，20） b on a.id=b.id;

MySQL - for update 行鎖 表鎖

for update 的作用是在查詢的時(shí)候?yàn)樾屑由吓潘i，當(dāng)一個(gè)事務(wù)的操作未完成時(shí)候，其他事務(wù)可以讀取但是不能寫(xiě)入或更新。

它的典型使用場(chǎng)景是 高并發(fā)并且對(duì)于數(shù)據(jù)的準(zhǔn)確性有很高要求 ，比如金錢、庫(kù)存等，一般這種操作都是很長(zhǎng)一串并且開(kāi)啟事務(wù)的，假如現(xiàn)在要對(duì)庫(kù)存進(jìn)行操作，在剛開(kāi)始讀的時(shí)候是1，然后馬上另外一個(gè)進(jìn)程將庫(kù)存更新為0了，但事務(wù)還沒(méi)結(jié)束，會(huì)一直用1進(jìn)行后續(xù)的邏輯，就會(huì)有問(wèn)題，所以需要用for upate 加鎖防止出錯(cuò)。

行鎖的具體實(shí)現(xiàn)算法有三種：record lock、gap lock以及next-key lock。

只在可重復(fù)讀或以上隔離級(jí)別下的特定操作才會(huì)取得 gap lock 或 next-key lock，在 Select、Update 和 Delete 時(shí)，除了基于唯一索引的查詢之外，其它索引查詢時(shí)都會(huì)獲取 gap lock 或 next-key lock，即鎖住其掃描的范圍。主鍵索引也屬于唯一索引，所以主鍵索引是不會(huì)使用 gap lock 或 next-key lock

for update 僅適用于InnoDB，并且必須開(kāi)啟事務(wù)，在begin與commit之間才生效。

select 語(yǔ)句默認(rèn)不獲取任何鎖，所以是可以讀被其它事務(wù)持有排它鎖的數(shù)據(jù)的！

InnoDB 既實(shí)現(xiàn)了行鎖，也實(shí)現(xiàn)了表鎖。

當(dāng)有明確指定的主鍵/索引時(shí)候，是行級(jí)鎖，否則是表級(jí)鎖

假設(shè)表 user，存在有id跟name字段，id是主鍵，有5條數(shù)據(jù)。

明確指定主鍵，并且有此記錄，行級(jí)鎖

無(wú)主鍵/索引，表級(jí)鎖

主鍵/索引不明確，表級(jí)鎖

明確指定主鍵/索引，若查無(wú)此記錄，無(wú)鎖

參考博文：

mysql 核心內(nèi)容-上

1、SQL語(yǔ)句執(zhí)行流程

MySQL大體上可分為Server層和存儲(chǔ)引擎層兩部分。

Server層：

連接器：TCP握手后服務(wù)器來(lái)驗(yàn)證登陸用戶身份，A用戶創(chuàng)建連接后，管理員對(duì)A用戶權(quán)限修改了也不會(huì)影響到已經(jīng)創(chuàng)建的鏈接權(quán)限，必須重新登陸。

查詢緩存：查詢后的結(jié)果存儲(chǔ)位置，MySQL8.0版本以后已經(jīng)取消，因?yàn)椴樵兙彺媸l繁，得不償失。

分析器：根據(jù)語(yǔ)法規(guī)則，判斷你輸入的這個(gè)SQL語(yǔ)句是否滿足MySQL語(yǔ)法。

優(yōu)化器：多種執(zhí)行策略可實(shí)現(xiàn)目標(biāo)，系統(tǒng)自動(dòng)選擇最優(yōu)進(jìn)行執(zhí)行。

執(zhí)行器：判斷是否有權(quán)限，將最終任務(wù)提交到存儲(chǔ)引擎。

存儲(chǔ)引擎層

負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。其架構(gòu)模式是插件式的，支持InnoDB、MyISAM、Memory等多個(gè)存儲(chǔ)引擎?，F(xiàn)在最常用的存儲(chǔ)引擎是InnoDB，它從MySQL 5.5.5版本開(kāi)始成為了默認(rèn)存儲(chǔ)引擎(經(jīng)常用的也是這個(gè))。

SQL執(zhí)行順序

2、BinLog、RedoLog、UndoLog

BinLog

BinLog是記錄所有數(shù)據(jù)庫(kù)表結(jié)構(gòu)變更（例如create、alter table）以及表數(shù)據(jù)修改(insert、update、delete)的二進(jìn)制日志，主從數(shù)據(jù)庫(kù)同步用到的都是BinLog文件。BinLog日志文件有三種模式。

STATEMENT 模式

內(nèi)容：binlog 記錄可能引起數(shù)據(jù)變更的 sql 語(yǔ)句

優(yōu)勢(shì)：該模式下，因?yàn)闆](méi)有記錄實(shí)際的數(shù)據(jù)，所以日志量很少 IO 都消耗很低，性能是最優(yōu)的

劣勢(shì)：但有些操作并不是確定的，比如 uuid() 函數(shù)會(huì)隨機(jī)產(chǎn)生唯一標(biāo)識(shí)，當(dāng)依賴 binlog 回放時(shí)，該操作生成的數(shù)據(jù)與原數(shù)據(jù)必然是不同的，此時(shí)可能造成無(wú)法預(yù)料的后果。

ROW 模式

內(nèi)容：在該模式下，binlog 會(huì)記錄每次操作的源數(shù)據(jù)與修改后的目標(biāo)數(shù)據(jù)，StreamSets就要求該模式。

優(yōu)勢(shì)：可以絕對(duì)精準(zhǔn)的還原，從而保證了數(shù)據(jù)的安全與可靠，并且復(fù)制和數(shù)據(jù)恢復(fù)過(guò)程可以是并發(fā)進(jìn)行的

劣勢(shì)：缺點(diǎn)在于 binlog 體積會(huì)非常大，同時(shí)，對(duì)于修改記錄多、字段長(zhǎng)度大的操作來(lái)說(shuō)，記錄時(shí)性能消耗會(huì)很嚴(yán)重。閱讀的時(shí)候也需要特殊指令來(lái)進(jìn)行讀取數(shù)據(jù)。

MIXED 模式

內(nèi)容：是對(duì)上述STATEMENT 跟 ROW 兩種模式的混合使用。

細(xì)節(jié)：對(duì)于絕大部分操作，都是使用 STATEMENT 來(lái)進(jìn)行 binlog 沒(méi)有記錄，只有以下操作使用 ROW 來(lái)實(shí)現(xiàn)：表的存儲(chǔ)引擎為 NDB，使用了uuid() 等不確定函數(shù)，使用了 insert delay 語(yǔ)句，使用了臨時(shí)表

主從同步流程：

1、主節(jié)點(diǎn)必須啟用二進(jìn)制日志，記錄任何修改了數(shù)據(jù)庫(kù)數(shù)據(jù)的事件。

2、從節(jié)點(diǎn)開(kāi)啟一個(gè)線程（I/O Thread)把自己扮演成 mysql 的客戶端，通過(guò) mysql 協(xié)議，請(qǐng)求主節(jié)點(diǎn)的二進(jìn)制日志文件中的事件 。

3、主節(jié)點(diǎn)啟動(dòng)一個(gè)線程（dump Thread），檢查自己二進(jìn)制日志中的事件，跟對(duì)方請(qǐng)求的位置對(duì)比，如果不帶請(qǐng)求位置參數(shù)，則主節(jié)點(diǎn)就會(huì)從第一個(gè)日志文件中的第一個(gè)事件一個(gè)一個(gè)發(fā)送給從節(jié)點(diǎn)。

4、從節(jié)點(diǎn)接收到主節(jié)點(diǎn)發(fā)送過(guò)來(lái)的數(shù)據(jù)把它放置到中繼日志（Relay log）文件中。并記錄該次請(qǐng)求到主節(jié)點(diǎn)的具體哪一個(gè)二進(jìn)制日志文件內(nèi)部的哪一個(gè)位置（主節(jié)點(diǎn)中的二進(jìn)制文件會(huì)有多個(gè)）。

5、從節(jié)點(diǎn)啟動(dòng)另外一個(gè)線程（sql Thread ），把 Relay log 中的事件讀取出來(lái)，并在本地再執(zhí)行一次。

mysql默認(rèn)的復(fù)制方式是異步的，并且復(fù)制的時(shí)候是有并行復(fù)制能力的。主庫(kù)把日志發(fā)送給從庫(kù)后不管了，這樣會(huì)產(chǎn)生一個(gè)問(wèn)題就是假設(shè)主庫(kù)掛了，從庫(kù)處理失敗了，這時(shí)候從庫(kù)升為主庫(kù)后，日志就丟失了。由此產(chǎn)生兩個(gè)概念。

全同步復(fù)制

主庫(kù)寫(xiě)入binlog后強(qiáng)制同步日志到從庫(kù)，所有的從庫(kù)都執(zhí)行完成后才返回給客戶端，但是很顯然這個(gè)方式的話性能會(huì)受到嚴(yán)重影響。

半同步復(fù)制

半同步復(fù)制的邏輯是這樣，從庫(kù)寫(xiě)入日志成功后返回ACK確認(rèn)給主庫(kù)，主庫(kù)收到至少一個(gè)從庫(kù)的確認(rèn)就認(rèn)為寫(xiě)操作完成。

還可以延伸到由于主從配置不一樣、主庫(kù)大事務(wù)、從庫(kù)壓力過(guò)大、網(wǎng)絡(luò)震蕩等造成主備延遲，如何避免這個(gè)問(wèn)題？主備切換的時(shí)候用可靠性優(yōu)先原則還是可用性優(yōu)先原則？如何判斷主庫(kù)Crash了？互為主備的情況下如何避免主備循環(huán)復(fù)制？被刪庫(kù)跑路了如何正確恢復(fù)？( o )… 感覺(jué)越來(lái)越扯到DBA的活兒上去了。

RedoLog

可以先通過(guò)下面demo理解：

飯點(diǎn)記賬可以把賬單寫(xiě)在賬本上也可以寫(xiě)在粉板上。有人賒賬或者還賬的話，一般有兩種做法：

1、直接把賬本翻出來(lái)，把這次賒的賬加上去或者扣除掉。

2、先在粉板上記下這次的賬，等打烊以后再把賬本翻出來(lái)核算。

生意忙時(shí)選后者，因?yàn)榍罢咛闊┝?。得在密密麻麻的記錄中找到這個(gè)人的賒賬總額信息，找到之后再拿出算盤計(jì)算，最后再將結(jié)果寫(xiě)回到賬本上。

同樣在MySQL中如果每一次的更新操作都需要寫(xiě)進(jìn)磁盤，然后磁盤也要找到對(duì)應(yīng)的那條記錄，然后再更新，整個(gè)過(guò)程IO成本、查找成本都很高。而粉板和賬本配合的整個(gè)過(guò)程就是MySQL用到的是Write-Ahead Logging 技術(shù)，它的關(guān)鍵點(diǎn)就是先寫(xiě)日志，再寫(xiě)磁盤。此時(shí)賬本 = BinLog，粉板 = RedoLog。

1、 記錄更新時(shí)，InnoDB引擎就會(huì)先把記錄寫(xiě)到RedoLog（粉板）里面，并更新內(nèi)存。同時(shí)，InnoDB引擎會(huì)在空閑時(shí)將這個(gè)操作記錄更新到磁盤里面。

2、 如果更新太多RedoLog處理不了的時(shí)候，需先將RedoLog部分?jǐn)?shù)據(jù)寫(xiě)到磁盤，然后擦除RedoLog部分?jǐn)?shù)據(jù)。RedoLog類似轉(zhuǎn)盤。

RedoLog有write pos 跟checkpoint

write pos ：是當(dāng)前記錄的位置，一邊寫(xiě)一邊后移，寫(xiě)到第3號(hào)文件末尾后就回到0號(hào)文件開(kāi)頭。

check point：是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件。

write pos和check point之間的是粉板上還空著的部分，可以用來(lái)記錄新的操作。如果write pos追上checkpoint，表示粉板滿了，這時(shí)候不能再執(zhí)行新的更新，得停下來(lái)先擦掉一些記錄，把checkpoint推進(jìn)一下。

有了redo log，InnoDB就可以保證即使數(shù)據(jù)庫(kù)發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失，這個(gè)能力稱為crash-safe。 redolog兩階段提交：為了讓binlog跟redolog兩份日志之間的邏輯一致。提交流程大致如下：

1 prepare階段 -- 2 寫(xiě)binlog -- 3 commit

當(dāng)在2之前崩潰時(shí)，重啟恢復(fù)后發(fā)現(xiàn)沒(méi)有commit，回滾。備份恢復(fù)：沒(méi)有binlog 。一致

當(dāng)在3之前崩潰時(shí)，重啟恢復(fù)發(fā)現(xiàn)雖沒(méi)有commit，但滿足prepare和binlog完整，所以重啟后會(huì)自動(dòng)commit。備份：有binlog. 一致

binlog跟redolog區(qū)別：

redo log是InnoDB引擎特有的；binlog是MySQL的Server層實(shí)現(xiàn)的，所有引擎都可以使用。

redo log是物理日志，記錄的是在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改；binlog是邏輯日志，記錄的是這個(gè)語(yǔ)句的原始邏輯，比如給ID=2這一行的c字段加1。

redo log是循環(huán)寫(xiě)的，空間固定會(huì)用完；binlog是可以追加寫(xiě)入的。追加寫(xiě)是指binlog文件寫(xiě)到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志。

UndoLog

UndoLog 一般是邏輯日志，主要分為兩種：

insert undo log

代表事務(wù)在insert新記錄時(shí)產(chǎn)生的undo log, 只在事務(wù)回滾時(shí)需要，并且在事務(wù)提交后可以被立即丟棄

update undo log

事務(wù)在進(jìn)行update或delete時(shí)產(chǎn)生的undo log; 不僅在事務(wù)回滾時(shí)需要，在快照讀時(shí)也需要；所以不能隨便刪除，只有在快速讀或事務(wù)回滾不涉及該日志時(shí)，對(duì)應(yīng)的日志才會(huì)被purge線程統(tǒng)一清除

3、MySQL中的索引

索引的常見(jiàn)模型有哈希表、有序數(shù)組和搜索樹(shù)。

哈希表：一種以KV存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)，只適合等值查詢，不適合范圍查詢。

有序數(shù)組：只適用于靜態(tài)存儲(chǔ)引擎，涉及到插入的時(shí)候比較麻煩。可以參考Java中的ArrayList。

搜索樹(shù)：按照數(shù)據(jù)結(jié)構(gòu)中的二叉樹(shù)來(lái)存儲(chǔ)數(shù)據(jù)，不過(guò)此時(shí)是N叉樹(shù)(B+樹(shù))。廣泛應(yīng)用在存儲(chǔ)引擎層中。

B+樹(shù)比B樹(shù)優(yōu)勢(shì)在于：

B+ 樹(shù)非葉子節(jié)點(diǎn)存儲(chǔ)的只是索引，可以存儲(chǔ)的更多。B+樹(shù)比B樹(shù)更加矮胖，IO次數(shù)更少。

B+ 樹(shù)葉子節(jié)點(diǎn)前后管理，更加方便范圍查詢。同時(shí)結(jié)果都在葉子節(jié)點(diǎn)，查詢效率穩(wěn)定。

B+樹(shù)中更有利于對(duì)數(shù)據(jù)掃描，可以避免B樹(shù)的回溯掃描。

索引的優(yōu)點(diǎn)：

1、唯一索引可以保證每一行數(shù)據(jù)的唯一性

2、提高查詢速度

3、加速表與表的連接

4、顯著的減少查詢中分組和排序的時(shí)間

5、通過(guò)使用索引，可以在查詢的過(guò)程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。

索引的缺點(diǎn)：

1、創(chuàng)建跟維護(hù)都需要耗時(shí)

2、創(chuàng)建索引時(shí)，需要對(duì)表加鎖，在鎖表的同時(shí)，可能會(huì)影響到其他的數(shù)據(jù)操作

3、 索引需要磁盤的空間進(jìn)行存儲(chǔ)，磁盤占用也很快。

4、當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行CRUD的時(shí)，也會(huì)觸發(fā)索引的維護(hù)，而維護(hù)索引需要時(shí)間，可能會(huì)降低數(shù)據(jù)操作性能

索引設(shè)計(jì)的原則不應(yīng)該：

1、索引不是越多越好。索引太多，維護(hù)索引需要時(shí)間跟空間。

2、 頻繁更新的數(shù)據(jù)，不宜建索引。

3、數(shù)據(jù)量小的表沒(méi)必要建立索引。

應(yīng)該：

1、重復(fù)率小的列建議生成索引。因?yàn)橹貜?fù)數(shù)據(jù)少，索引樹(shù)查詢更有效率，等價(jià)基數(shù)越大越好。

2、數(shù)據(jù)具有唯一性，建議生成唯一性索引。在數(shù)據(jù)庫(kù)的層面，保證數(shù)據(jù)正確性

3、頻繁group by、order by的列建議生成索引。可以大幅提高分組和排序效率

4、經(jīng)常用于查詢條件的字段建議生成索引。通過(guò)索引查詢，速度更快

索引失效的場(chǎng)景

1、模糊搜索：左模糊或全模糊都會(huì)導(dǎo)致索引失效，比如'%a'和'%a%'。但是右模糊是可以利用索引的，比如'a%' 。

2、隱式類型轉(zhuǎn)換：比如select * from t where name = xxx , name是字符串類型，但是沒(méi)有加引號(hào)，所以是由MySQL隱式轉(zhuǎn)換的，所以會(huì)讓索引失效 3、當(dāng)語(yǔ)句中帶有or的時(shí)候：比如select * from t where name=‘sw’ or age=14

4、不符合聯(lián)合索引的最左前綴匹配：(A,B,C)的聯(lián)合索引，你只where了C或B或只有B,C

關(guān)于索引的知識(shí)點(diǎn)：

主鍵索引：主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)信息。在InnoDB里，主鍵索引也被稱為聚簇索引（clustered index）。主鍵自增是無(wú)法保證完全自增的哦，遇到唯一鍵沖突、事務(wù)回滾等都可能導(dǎo)致不連續(xù)。

唯一索引：以唯一列生成的索引，該列不允許有重復(fù)值，但允許有空值(NULL)

普通索引跟唯一索引查詢性能：InnoDB的數(shù)據(jù)是按數(shù)據(jù)頁(yè)為單位來(lái)讀寫(xiě)的，默認(rèn)每頁(yè)16KB，因此這兩種索引查詢數(shù)據(jù)性能差別微乎其微。

change buffer：普通索引用在更新過(guò)程的加速，更新的字段如果在緩存中，如果是普通索引則直接更新即可。如果是唯一索引需要將所有數(shù)據(jù)讀入內(nèi)存來(lái)確保不違背唯一性，所以盡量用普通索引。

非主鍵索引：非主鍵索引的葉子節(jié)點(diǎn)內(nèi)容是主鍵的值。在InnoDB里，非主鍵索引也被稱為二級(jí)索引（secondary index）

回表：先通過(guò)數(shù)據(jù)庫(kù)索引掃描出數(shù)據(jù)所在的行，再通過(guò)行主鍵id取出索引中未提供的數(shù)據(jù)，即基于非主鍵索引的查詢需要多掃描一棵索引樹(shù)。

覆蓋索引：如果一個(gè)索引包含（或者說(shuō)覆蓋）所有需要查詢的字段的值，我們就稱之為覆蓋索引。

聯(lián)合索引：相對(duì)單列索引，組合索引是用多個(gè)列組合構(gòu)建的索引，一次性最多聯(lián)合16個(gè)。

最左前綴原則：對(duì)多個(gè)字段同時(shí)建立的組合索引(有順序，ABC，ACB是完全不同的兩種聯(lián)合索引) 以聯(lián)合索引(a,b,c)為例，建立這樣的索引相當(dāng)于建立了索引a、ab、abc三個(gè)索引。另外組合索引實(shí)際還是一個(gè)索引，并非真的創(chuàng)建了多個(gè)索引，只是產(chǎn)生的效果等價(jià)于產(chǎn)生多個(gè)索引。

索引下推：MySQL 5.6引入了索引下推優(yōu)化，可以在索引遍歷過(guò)程中，對(duì)索引中包含的字段先做判斷，過(guò)濾掉不符合條件的記錄，減少回表字?jǐn)?shù)。

索引維護(hù)：B+樹(shù)為了維護(hù)索引有序性涉及到頁(yè)分裂跟頁(yè)合并。增刪數(shù)據(jù)時(shí)需考慮頁(yè)空間利用率。

自增主鍵：一般會(huì)建立與業(yè)務(wù)無(wú)關(guān)的自增主鍵，不會(huì)觸發(fā)葉子節(jié)點(diǎn)分裂。

延遲關(guān)聯(lián)：通過(guò)使用覆蓋索引查詢返回需要的主鍵，再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù)。

InnoDB存儲(chǔ): * .frm文件是一份定義文件，也就是定義數(shù)據(jù)庫(kù)表是一張?jiān)趺礃拥谋怼?.ibd文件則是該表的索引，數(shù)據(jù)存儲(chǔ)文件，既該表的所有索引樹(shù)，所有行記錄數(shù)據(jù)都存儲(chǔ)在該文件中。

MyISAM存儲(chǔ)：* .frm文件是一份定義文件，也就是定義數(shù)據(jù)庫(kù)表是一張?jiān)趺礃拥谋怼? .MYD文件是MyISAM存儲(chǔ)引擎表的所有行數(shù)據(jù)的文件。* .MYI文件存放的是MyISAM存儲(chǔ)引擎表的索引相關(guān)數(shù)據(jù)的文件。MyISAM引擎下，表數(shù)據(jù)和表索引數(shù)據(jù)是分開(kāi)存儲(chǔ)的。

MyISAM查詢：在MyISAM下，主鍵索引和輔助鍵索引都屬于非聚簇索引。查詢不管是走主鍵索引，還是非主鍵索引，在葉子結(jié)點(diǎn)得到的都是目的數(shù)據(jù)的地址，還需要通過(guò)該地址，才能在數(shù)據(jù)文件中找到目的數(shù)據(jù)。

PS：InnoDB支持聚簇索引，MyISAM不支持聚簇索引

4、SQL事務(wù)隔離級(jí)別

ACID的四個(gè)特性

原子性（Atomicity）：把多個(gè)操作放到一個(gè)事務(wù)中，保證這些操作要么都成功，要么都不成功

一致性（Consistency）：理解成一串對(duì)數(shù)據(jù)進(jìn)行操作的程序執(zhí)行下來(lái)，不會(huì)對(duì)數(shù)據(jù)產(chǎn)生不好的影響，比如憑空產(chǎn)生，或消失

隔離性（Isolation，又稱獨(dú)立性）：隔離性的意思就是多個(gè)事務(wù)之間互相不干擾，即使是并發(fā)事務(wù)的情況下，他們只是兩個(gè)并發(fā)執(zhí)行沒(méi)有交集，互不影響的東西；當(dāng)然實(shí)現(xiàn)中，也不一定需要這么完整隔離性，即不一定需要這么的互不干擾，有時(shí)候還是允許有部分干擾的。所以MySQL可以支持4種事務(wù)隔離性

持久性（Durability）：當(dāng)某個(gè)操作操作完畢了，那么結(jié)果就是這樣了，并且這個(gè)操作會(huì)持久化到日志記錄中

PS：ACID中C與CAP定理中C的區(qū)別

ACID的C著重強(qiáng)調(diào)單數(shù)據(jù)庫(kù)事務(wù)操作時(shí)，要保證數(shù)據(jù)的完整和正確性，數(shù)據(jù)不會(huì)憑空消失跟增加。CAP 理論中的C指的是對(duì)一個(gè)數(shù)據(jù)多個(gè)備份的讀寫(xiě)一致性

事務(wù)操作可能會(huì)出現(xiàn)的數(shù)據(jù)問(wèn)題

1、臟讀(dirty read)：B事務(wù)更改數(shù)據(jù)還未提交，A事務(wù)已經(jīng)看到并且用了。B事務(wù)如果回滾，則A事務(wù)做錯(cuò)了

2、 不可重復(fù)讀(non-repeatable read)：不可重復(fù)讀的重點(diǎn)是修改: 同樣的條件, 你讀取過(guò)的數(shù)據(jù), 再次讀取出來(lái)發(fā)現(xiàn)值不一樣了，只需要鎖住滿足條件的記錄

3、 幻讀(phantom read)：事務(wù)A先修改了某個(gè)表的所有紀(jì)錄的狀態(tài)字段為已處理，未提交；事務(wù)B也在此時(shí)新增了一條未處理的記錄，并提交了；事務(wù)A隨后查詢記錄，卻發(fā)現(xiàn)有一條記錄是未處理的造成幻讀現(xiàn)象，幻讀僅專指新插入的行?；米x會(huì)造成語(yǔ)義上的問(wèn)題跟數(shù)據(jù)一致性問(wèn)題。

4、 在可重復(fù)讀RR隔離級(jí)別下，普通查詢是快照讀，是不會(huì)看到別的事務(wù)插入的數(shù)據(jù)的。因此，幻讀在當(dāng)前讀下才會(huì)出現(xiàn)。要用間隙鎖解決此問(wèn)題。

在說(shuō)隔離級(jí)別之前，你首先要知道，你隔離得越嚴(yán)實(shí)，效率就會(huì)越低。因此很多時(shí)候，我們都要在二者之間尋找一個(gè)平衡點(diǎn)。SQL標(biāo)準(zhǔn)的事務(wù)隔離級(jí)別由低到高如下： 上圖從上到下的模式會(huì)導(dǎo)致系統(tǒng)的并行性能依次降低，安全性依次提高。

讀未提交：別人改數(shù)據(jù)的事務(wù)尚未提交，我在我的事務(wù)中也能讀到。

讀已提交(Oracle默認(rèn))：別人改數(shù)據(jù)的事務(wù)已經(jīng)提交，我在我的事務(wù)中才能讀到。

可重復(fù)讀(MySQL默認(rèn))：別人改數(shù)據(jù)的事務(wù)已經(jīng)提交，我在我的事務(wù)中也不去讀，以此保證重復(fù)讀一致性。

串行：我的事務(wù)尚未提交，別人就別想改數(shù)據(jù)。

標(biāo)準(zhǔn)跟實(shí)現(xiàn)：上面都是關(guān)于事務(wù)的標(biāo)準(zhǔn)，但是每一種數(shù)據(jù)庫(kù)都有不同的實(shí)現(xiàn)，比如MySQL InnDB 默認(rèn)為RR級(jí)別，但是不會(huì)出現(xiàn)幻讀。因?yàn)楫?dāng)事務(wù)A更新了所有記錄的某個(gè)字段，此時(shí)事務(wù)A會(huì)獲得對(duì)這個(gè)表的表鎖，因?yàn)槭聞?wù)A還沒(méi)有提交，所以事務(wù)A獲得的鎖沒(méi)有釋放，此時(shí)事務(wù)B在該表插入新記錄，會(huì)因?yàn)闊o(wú)法獲得該表的鎖，則導(dǎo)致插入操作被阻塞。只有事務(wù)A提交了事務(wù)后，釋放了鎖，事務(wù)B才能進(jìn)行接下去的操作。所以可以說(shuō) MySQL的RR級(jí)別的隔離是已經(jīng)實(shí)現(xiàn)解決了臟讀，不可重復(fù)讀和幻讀的。

5、MySQL中的鎖

無(wú)論是Java的并發(fā)編程還是數(shù)據(jù)庫(kù)的并發(fā)操作都會(huì)涉及到鎖，研發(fā)人員引入了悲觀鎖跟樂(lè)觀鎖這樣一種鎖的設(shè)計(jì)思想。

悲觀鎖：

優(yōu)點(diǎn)：適合在寫(xiě)多讀少的并發(fā)環(huán)境中使用，雖然無(wú)法維持非常高的性能，但是在樂(lè)觀鎖無(wú)法提更好的性能前提下，可以做到數(shù)據(jù)的安全性

缺點(diǎn)：加鎖會(huì)增加系統(tǒng)開(kāi)銷，雖然能保證數(shù)據(jù)的安全，但數(shù)據(jù)處理吞吐量低，不適合在讀書(shū)寫(xiě)少的場(chǎng)合下使用

樂(lè)觀鎖：

優(yōu)點(diǎn)：在讀多寫(xiě)少的并發(fā)場(chǎng)景下，可以避免數(shù)據(jù)庫(kù)加鎖的開(kāi)銷，提高DAO層的響應(yīng)性能，很多情況下ORM工具都有帶有樂(lè)觀鎖的實(shí)現(xiàn)，所以這些方法不一定需要我們?nèi)藶榈娜?shí)現(xiàn)。

缺點(diǎn)：在寫(xiě)多讀少的并發(fā)場(chǎng)景下，即在寫(xiě)操作競(jìng)爭(zhēng)激烈的情況下，會(huì)導(dǎo)致CAS多次重試，沖突頻率過(guò)高，導(dǎo)致開(kāi)銷比悲觀鎖更高。

實(shí)現(xiàn)：數(shù)據(jù)庫(kù)層面的樂(lè)觀鎖其實(shí)跟CAS思想類似， 通數(shù)據(jù)版本號(hào)或者時(shí)間戳也可以實(shí)現(xiàn)。

數(shù)據(jù)庫(kù)并發(fā)場(chǎng)景主要有三種：

讀-讀：不存在任何問(wèn)題，也不需要并發(fā)控制

讀-寫(xiě)：有隔離性問(wèn)題，可能遇到臟讀，幻讀，不可重復(fù)讀

寫(xiě)-寫(xiě)：可能存更新丟失問(wèn)題，比如第一類更新丟失，第二類更新丟失

兩類更新丟失問(wèn)題：

第一類更新丟失：事務(wù)A的事務(wù)回滾覆蓋了事務(wù)B已提交的結(jié)果 第二類更新丟失：事務(wù)A的提交覆蓋了事務(wù)B已提交的結(jié)果

為了合理貫徹落實(shí)鎖的思想，MySQL中引入了雜七雜八的各種鎖：

鎖分類

MySQL支持三種層級(jí)的鎖定，分別為

表級(jí)鎖定

MySQL中鎖定粒度最大的一種鎖，最常使用的MYISAM與INNODB都支持表級(jí)鎖定。

頁(yè)級(jí)鎖定

是MySQL中鎖定粒度介于行級(jí)鎖和表級(jí)鎖中間的一種鎖，表級(jí)鎖速度快，但沖突多，行級(jí)沖突少，但速度慢。所以取了折衷的頁(yè)級(jí)，一次鎖定相鄰的一組記錄。

行級(jí)鎖定

Mysql中鎖定粒度最細(xì)的一種鎖，表示只針對(duì)當(dāng)前操作的行進(jìn)行加鎖。行級(jí)鎖能大大減少數(shù)據(jù)庫(kù)操作的沖突。其加鎖粒度最小，但加鎖的開(kāi)銷也最大行級(jí)鎖不一定比表級(jí)鎖要好：鎖的粒度越細(xì)，代價(jià)越高，相比表級(jí)鎖在表的頭部直接加鎖，行級(jí)鎖還要掃描找到對(duì)應(yīng)的行對(duì)其上鎖，這樣的代價(jià)其實(shí)是比較高的，所以表鎖和行鎖各有所長(zhǎng)。

MyISAM中的鎖

雖然MySQL支持表，頁(yè)，行三級(jí)鎖定，但MyISAM存儲(chǔ)引擎只支持表鎖。所以MyISAM的加鎖相對(duì)比較開(kāi)銷低，但數(shù)據(jù)操作的并發(fā)性能相對(duì)就不高。但如果寫(xiě)操作都是尾插入，那還是可以支持一定程度的讀寫(xiě)并發(fā)

從MyISAM所支持的鎖中也可以看出，MyISAM是一個(gè)支持讀讀并發(fā)，但不支持通用讀寫(xiě)并發(fā)，寫(xiě)寫(xiě)并發(fā)的數(shù)據(jù)庫(kù)引擎，所以它更適合用于讀多寫(xiě)少的應(yīng)用場(chǎng)合，一般工程中也用的較少。

InnoDB中的鎖

該模式下支持的鎖實(shí)在是太多了，具體如下：

共享鎖和排他鎖 (Shared and Exclusive Locks)

意向鎖（Intention Locks）

記錄鎖（Record Locks）

間隙鎖（Gap Locks）

臨鍵鎖 （Next-Key Locks）

插入意向鎖（Insert Intention Locks）

主鍵自增鎖 (AUTO-INC Locks)

空間索引斷言鎖（Predicate Locks for Spatial Indexes）

舉個(gè)栗子，比如行鎖里的共享鎖跟排它鎖：lock in share modle 共享讀鎖：

為了確保自己查到的數(shù)據(jù)沒(méi)有被其他的事務(wù)正在修改，也就是說(shuō)確保查到的數(shù)據(jù)是最新的數(shù)據(jù)，并且不允許其他人來(lái)修改數(shù)據(jù)。但是自己不一定能夠修改數(shù)據(jù)，因?yàn)橛锌赡芷渌氖聞?wù)也對(duì)這些數(shù)據(jù)使用了 in share mode 的方式上了S 鎖。如果不及時(shí)的commit 或者rollback 也可能會(huì)造成大量的事務(wù)等待。

for update排它寫(xiě)鎖:

為了讓自己查到的數(shù)據(jù)確保是最新數(shù)據(jù)，并且查到后的數(shù)據(jù)只允許自己來(lái)修改的時(shí)候，需要用到for update。相當(dāng)于一個(gè) update 語(yǔ)句。在業(yè)務(wù)繁忙的情況下，如果事務(wù)沒(méi)有及時(shí)的commit或者rollback 可能會(huì)造成其他事務(wù)長(zhǎng)時(shí)間的等待，從而影響數(shù)據(jù)庫(kù)的并發(fā)使用效率。

Gap Lock間隙鎖：

1、行鎖只能鎖住行，如果在記錄之間的間隙插入數(shù)據(jù)就無(wú)法解決了，因此MySQL引入了間隙鎖(Gap Lock)。間隙鎖是左右開(kāi)區(qū)間。間隙鎖之間不會(huì)沖突。

2、間隙鎖和行鎖合稱NextKeyLock，每個(gè)NextKeyLock是前開(kāi)后閉區(qū)間。

間隙鎖加鎖原則(學(xué)完忘那種)：

1、加鎖的基本單位是 NextKeyLock，是前開(kāi)后閉區(qū)間。

2、查找過(guò)程中訪問(wèn)到的對(duì)象才會(huì)加鎖。

3、索引上的等值查詢，給唯一索引加鎖的時(shí)候，NextKeyLock退化為行鎖。

4、索引上的等值查詢，向右遍歷時(shí)且最后一個(gè)值不滿足等值條件的時(shí)候，NextKeyLock退化為間隙鎖。

5、唯一索引上的范圍查詢會(huì)訪問(wèn)到不滿足條件的第一個(gè)值為止。

文章名稱：mysql索引怎么鎖 mysql有哪些索引類型有哪些鎖
當(dāng)前網(wǎng)址：http://jinyejixie.com/article48/hejjhp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供、微信小程序、微信公眾號(hào)、品牌網(wǎng)站制作、網(wǎng)站導(dǎo)航、品牌網(wǎng)站設(shè)計(jì)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)