整合營(yíng)銷服務(wù)商
電腦端+手機(jī)端+微信端=數(shù)據(jù)同步管理
免費(fèi)咨詢熱線：
電腦端+手機(jī)端+微信端=數(shù)據(jù)同步管理
免費(fèi)咨詢熱線：
事務(wù)特性ACID：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）。
第一范式1NF
確保數(shù)據(jù)庫(kù)表字段的原子性。
比如字段 userInfo: 廣東省 10086' ，依照第一范式必須拆分成 userInfo: 廣東省 userTel:10086兩個(gè)字段。
第二范式2NF
首先要滿足第一范式，另外包含兩部分內(nèi)容，一是表必須有一個(gè)主鍵；二是非主鍵列必須完全依賴于主鍵，而不能只依賴于主鍵的一部分。
舉個(gè)例子。假定選課關(guān)系表為student_course(student_no, student_name, age, course_name, grade, credit)，主鍵為(student_no, course_name)。其中學(xué)分完全依賴于課程名稱，姓名年齡完全依賴學(xué)號(hào)，不符合第二范式，會(huì)導(dǎo)致數(shù)據(jù)冗余（學(xué)生選n門課，姓名年齡有n條記錄）、插入異常（插入一門新課，因?yàn)闆](méi)有學(xué)號(hào)，無(wú)法保存新課記錄）等問(wèn)題。
應(yīng)該拆分成三個(gè)表：學(xué)生：student(stuent_no, student_name, 年齡)；課程：course(course_name, credit)；選課關(guān)系：student_course_relation(student_no, course_name, grade)。
第三范式3NF
首先要滿足第二范式，另外非主鍵列必須直接依賴于主鍵，不能存在傳遞依賴。即不能存在：非主鍵列 A 依賴于非主鍵列 B，非主鍵列 B 依賴于主鍵的情況。
假定學(xué)生關(guān)系表為Student(student_no, student_name, age, academy_id, academy_telephone)，主鍵為"學(xué)號(hào)"，其中學(xué)院id依賴于學(xué)號(hào)，而學(xué)院地點(diǎn)和學(xué)院電話依賴于學(xué)院id，存在傳遞依賴，不符合第三范式。
可以把學(xué)生關(guān)系表分為如下兩個(gè)表：學(xué)生：(student_no, student_name, age, academy_id)；學(xué)院：(academy_id, academy_telephone)。
2NF和3NF的區(qū)別？
先了解下幾個(gè)概念：臟讀、不可重復(fù)讀、幻讀。
不可重復(fù)讀和臟讀的區(qū)別是，臟讀是某一事務(wù)讀取了另一個(gè)事務(wù)未提交的臟數(shù)據(jù)，而不可重復(fù)讀則是讀取了前一事務(wù)提交的數(shù)據(jù)。
事務(wù)隔離就是為了解決上面提到的臟讀、不可重復(fù)讀、幻讀這幾個(gè)問(wèn)題。
MySQL數(shù)據(jù)庫(kù)為我們提供的四種隔離級(jí)別：
查看隔離級(jí)別：
select @@transaction_isolation;
設(shè)置隔離級(jí)別：
set session transaction isolation level read uncommitted;
生產(chǎn)環(huán)境大多使用RC。為什么不是RR呢？
可重復(fù)讀(Repeatable Read)，簡(jiǎn)稱為RR
讀已提交(Read Commited)，簡(jiǎn)稱為RC
緣由一：在RR隔離級(jí)別下，存在間隙鎖，導(dǎo)致出現(xiàn)死鎖的幾率比RC大的多！
緣由二：在RR隔離級(jí)別下，條件列未命中索引會(huì)鎖表！而在RC隔離級(jí)別下，只鎖行!
也就是說(shuō)，RC的并發(fā)性高于RR。
并且大部分場(chǎng)景下，不可重復(fù)讀問(wèn)題是可以接受的。畢竟數(shù)據(jù)都已經(jīng)提交了，讀出來(lái)本身就沒(méi)有太大問(wèn)題！
互聯(lián)網(wǎng)項(xiàng)目中mysql應(yīng)該選什么事務(wù)隔離級(jí)別
我們平時(shí)可以在編輯器上輸入各種中文英文字母，但這些都是給人讀的，不是給計(jì)算機(jī)讀的，其實(shí)計(jì)算機(jī)真正保存和傳輸數(shù)據(jù)都是以二進(jìn)制0101的格式進(jìn)行的。
那么就需要有一個(gè)規(guī)則，把中文和英文字母轉(zhuǎn)化為二進(jìn)制。其中d對(duì)應(yīng)十六進(jìn)制下的64，它可以轉(zhuǎn)換為01二進(jìn)制的格式。于是字母和數(shù)字就這樣一一對(duì)應(yīng)起來(lái)了，這就是ASCII編碼格式。
它用一個(gè)字節(jié)，也就是8位來(lái)標(biāo)識(shí)字符，基礎(chǔ)符號(hào)有128個(gè)，擴(kuò)展符號(hào)也是128個(gè)。也就只能表示下英文字母和數(shù)字。
這明顯不夠用。于是，為了標(biāo)識(shí)中文，出現(xiàn)了GB2312的編碼格式。為了標(biāo)識(shí)希臘語(yǔ)，出現(xiàn)了greek編碼格式，為了標(biāo)識(shí)俄語(yǔ)，整了cp866編碼格式。
為了統(tǒng)一它們，于是出現(xiàn)了Unicode編碼格式，它用了2~4個(gè)字節(jié)來(lái)表示字符，這樣理論上所有符號(hào)都能被收錄進(jìn)去，并且它還完全兼容ASCII的編碼，也就是說(shuō)，同樣是字母d，在ASCII用64表示，在Unicode里還是用64來(lái)表示。
但不同的地方是ASCII編碼用1個(gè)字節(jié)來(lái)表示，而Unicode用則兩個(gè)字節(jié)來(lái)表示。
同樣都是字母d，unicode比ascii多使用了一個(gè)字節(jié)，如下：
D ASCII: 01100100
D Unicode: 00000000 01100100
可以看到，上面的unicode編碼，前面的都是0，其實(shí)用不上，但還占了個(gè)字節(jié)，有點(diǎn)浪費(fèi)。如果我們能做到該隱藏時(shí)隱藏，這樣就能省下不少空間，按這個(gè)思路，就是就有了UTF-8編碼。
總結(jié)一下，按照一定規(guī)則把符號(hào)和二進(jìn)制碼對(duì)應(yīng)起來(lái)，這就是編碼。而把n多這種已經(jīng)編碼的字符聚在一起，就是我們常說(shuō)的字符集。
比如utf-8字符集就是所有utf-8編碼格式的字符的合集。
想看下mysql支持哪些字符集。可以執(zhí)行 show charset;
上面提到utf-8是在unicode的基礎(chǔ)上做的優(yōu)化，既然unicode有辦法表示所有字符，那utf-8也一樣可以表示所有字符，為了避免混淆，我在后面叫它大utf8。
mysql支持的字符集中有utf8和utf8mb4。
先說(shuō)utf8mb4編碼，mb4就是most bytes 4的意思，從上圖最右邊的Maxlen可以看到，它最大支持用4個(gè)字節(jié)來(lái)表示字符，它幾乎可以用來(lái)表示目前已知的所有的字符。
再說(shuō)mysql字符集里的utf8，它是數(shù)據(jù)庫(kù)的默認(rèn)字符集。但注意，此utf8非彼utf8，我們叫它小utf8字符集。為什么這么說(shuō)，因?yàn)閺腗axlen可以看出，它最多支持用3個(gè)字節(jié)去表示字符，按utf8mb4的命名方式，準(zhǔn)確點(diǎn)應(yīng)該叫它utf8mb3。
utf8 就像是閹割版的utf8mb4，只支持部分字符。比如emoji表情，它就不支持。
而mysql支持的字符集里，第三列，collation，它是指字符集的比較規(guī)則。
比如，"debug"和"Debug"是同一個(gè)單詞，但它們大小寫不同，該不該判為同一個(gè)單詞呢。
這時(shí)候就需要用到collation了。
通過(guò)SHOW COLLATION WHERE Charset='utf8mb4';可以查看到utf8mb4下支持什么比較規(guī)則。
如果collation=utf8mb4_general_ci，是指使用utf8mb4字符集的前提下，挨個(gè)字符進(jìn)行比較（general），并且不區(qū)分大小寫（_ci，case insensitice）。
這種情況下，"debug"和"Debug"是同一個(gè)單詞。
如果改成collation=utf8mb4_bin，就是指挨個(gè)比較二進(jìn)制位大小。
于是"debug"和"Debug"就不是同一個(gè)單詞。
那utf8mb4對(duì)比utf8有什么劣勢(shì)嗎？
我們知道數(shù)據(jù)庫(kù)表里，字段類型如果是char(2)的話，里面的2是指字符個(gè)數(shù)，也就是說(shuō)不管這張表用的是什么編碼的字符集，都能放上2個(gè)字符。
而char又是固定長(zhǎng)度，為了能放下2個(gè)utf8mb4的字符，char會(huì)默認(rèn)保留2*4（maxlen=4）=8個(gè)字節(jié)的空間。
如果是utf8mb3，則會(huì)默認(rèn)保留 2 * 3 (maxlen=3)=6個(gè)字節(jié)的空間。也就是說(shuō)，在這種情況下，utf8mb4會(huì)比utf8mb3多使用一些空間。
索引是存儲(chǔ)引擎用于提高數(shù)據(jù)庫(kù)表的訪問(wèn)速度的一種數(shù)據(jù)結(jié)構(gòu)。它可以比作一本字典的目錄，可以幫你快速找到對(duì)應(yīng)的記錄。
索引一般存儲(chǔ)在磁盤的文件中，它是占用物理空間的。
優(yōu)點(diǎn)：
缺點(diǎn)：
數(shù)據(jù)是存儲(chǔ)在磁盤上的，查詢數(shù)據(jù)時(shí)，如果沒(méi)有索引，會(huì)加載所有的數(shù)據(jù)到內(nèi)存，依次進(jìn)行檢索，讀取磁盤次數(shù)較多。有了索引，就不需要加載所有數(shù)據(jù)，因?yàn)锽+樹(shù)的高度一般在2-4層，最多只需要讀取2-4次磁盤，查詢速度大大提升。
索引的數(shù)據(jù)結(jié)構(gòu)主要有B+樹(shù)和哈希表，對(duì)應(yīng)的索引分別為B+樹(shù)索引和哈希索引。InnoDB引擎的索引類型有B+樹(shù)索引和哈希索引，默認(rèn)的索引類型為B+樹(shù)索引。
B+樹(shù)索引
B+ 樹(shù)是基于B 樹(shù)和葉子節(jié)點(diǎn)順序訪問(wèn)指針進(jìn)行實(shí)現(xiàn)，它具有B樹(shù)的平衡性，并且通過(guò)順序訪問(wèn)指針來(lái)提高區(qū)間查詢的性能。
在 B+ 樹(shù)中，節(jié)點(diǎn)中的 key 從左到右遞增排列，如果某個(gè)指針的左右相鄰 key 分別是 keyi 和 keyi+1，則該指針指向節(jié)點(diǎn)的所有 key 大于等于 keyi 且小于等于 keyi+1。
進(jìn)行查找操作時(shí)，首先在根節(jié)點(diǎn)進(jìn)行二分查找，找到key所在的指針，然后遞歸地在指針?biāo)赶虻墓?jié)點(diǎn)進(jìn)行查找。直到查找到葉子節(jié)點(diǎn)，然后在葉子節(jié)點(diǎn)上進(jìn)行二分查找，找出key所對(duì)應(yīng)的數(shù)據(jù)項(xiàng)。
MySQL 數(shù)據(jù)庫(kù)使用最多的索引類型是BTREE索引，底層基于B+樹(shù)數(shù)據(jù)結(jié)構(gòu)來(lái)實(shí)現(xiàn)。
mysql> show index from blog\G;
*************************** 1. row ***************************
Table: blog
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: blog_id
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
哈希索引
哈希索引是基于哈希表實(shí)現(xiàn)的，對(duì)于每一行數(shù)據(jù)，存儲(chǔ)引擎會(huì)對(duì)索引列進(jìn)行哈希計(jì)算得到哈希碼，并且哈希算法要盡量保證不同的列值計(jì)算出的哈希碼值是不同的，將哈希碼的值作為哈希表的key值，將指向數(shù)據(jù)行的指針作為哈希表的value值。這樣查找一個(gè)數(shù)據(jù)的時(shí)間復(fù)雜度就是O(1)，一般多用于精確查找。
1、主鍵索引：名為primary的唯一非空索引，不允許有空值。
2、唯一索引：索引列中的值必須是唯一的，但是允許為空值。唯一索引和主鍵索引的區(qū)別是：唯一索引字段可以為null且可以存在多個(gè)null值，而主鍵索引字段不可以為null。唯一索引的用途：唯一標(biāo)識(shí)數(shù)據(jù)庫(kù)表中的每條記錄，主要是用來(lái)防止數(shù)據(jù)重復(fù)插入。創(chuàng)建唯一索引的SQL語(yǔ)句如下：
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);
3、組合索引：在表中的多個(gè)字段組合上創(chuàng)建的索引，只有在查詢條件中使用了這些字段的左邊字段時(shí)，索引才會(huì)被使用，使用組合索引時(shí)需遵循最左前綴原則。
4、全文索引：只能在CHAR、VARCHAR和TEXT類型字段上使用全文索引。
5、普通索引：普通索引是最基本的索引，它沒(méi)有任何限制，值可以為空。
如果 SQL 語(yǔ)句中用到了組合索引中的最左邊的索引，那么這條 SQL 語(yǔ)句就可以利用這個(gè)組合索引去進(jìn)行匹配。當(dāng)遇到范圍查詢(>、<、between、like)就會(huì)停止匹配，后面的字段不會(huì)用到索引。
對(duì)(a,b,c)建立索引，查詢條件使用 a/ab/abc 會(huì)走索引，使用 bc 不會(huì)走索引。
對(duì)(a,b,c,d)建立索引，查詢條件為a=1 and b=2 and c > 3 and d=4，那么a、b和c三個(gè)字段能用到索引，而d無(wú)法使用索引。因?yàn)橛龅搅朔秶樵儭?/p>
如下圖，對(duì)(a, b) 建立索引，a 在索引樹(shù)中是全局有序的，而 b 是全局無(wú)序，局部有序（當(dāng)a相等時(shí)，會(huì)根據(jù)b進(jìn)行排序）。直接執(zhí)行b=2這種查詢條件無(wú)法使用索引。
當(dāng)a的值確定的時(shí)候，b是有序的。例如a=1時(shí)，b值為1，2是有序的狀態(tài)。當(dāng)a=2時(shí)候，b的值為1，4也是有序狀態(tài)。 當(dāng)執(zhí)行a=1 and b=2時(shí)a和b字段能用到索引。而執(zhí)行a > 1 and b=2時(shí)，a字段能用到索引，b字段用不到索引。因?yàn)閍的值此時(shí)是一個(gè)范圍，不是固定的，在這個(gè)范圍內(nèi)b值不是有序的，因此b字段無(wú)法使用索引。
InnoDB使用表的主鍵構(gòu)造主鍵索引樹(shù)，同時(shí)葉子節(jié)點(diǎn)中存放的即為整張表的記錄數(shù)據(jù)。聚集索引葉子節(jié)點(diǎn)的存儲(chǔ)是邏輯上連續(xù)的，使用雙向鏈表連接，葉子節(jié)點(diǎn)按照主鍵的順序排序，因此對(duì)于主鍵的排序查找和范圍查找速度比較快。
聚集索引的葉子節(jié)點(diǎn)就是整張表的行記錄。InnoDB 主鍵使用的是聚簇索引。聚集索引要比非聚集索引查詢效率高很多。
對(duì)于InnoDB來(lái)說(shuō)，聚集索引一般是表中的主鍵索引，如果表中沒(méi)有顯示指定主鍵，則會(huì)選擇表中的第一個(gè)不允許為NULL的唯一索引。如果沒(méi)有主鍵也沒(méi)有合適的唯一索引，那么InnoDB內(nèi)部會(huì)生成一個(gè)隱藏的主鍵作為聚集索引，這個(gè)隱藏的主鍵長(zhǎng)度為6個(gè)字節(jié)，它的值會(huì)隨著數(shù)據(jù)的插入自增。
select的數(shù)據(jù)列只用從索引中就能夠取得，不需要回表進(jìn)行二次查詢，也就是說(shuō)查詢列要被所使用的索引覆蓋。對(duì)于innodb表的二級(jí)索引，如果索引能覆蓋到查詢的列，那么就可以避免對(duì)主鍵索引的二次查詢。
不是所有類型的索引都可以成為覆蓋索引。覆蓋索引要存儲(chǔ)索引列的值，而哈希索引、全文索引不存儲(chǔ)索引列的值，所以MySQL使用b+樹(shù)索引做覆蓋索引。
對(duì)于使用了覆蓋索引的查詢，在查詢前面使用explain，輸出的extra列會(huì)顯示為using index。
比如user_like 用戶點(diǎn)贊表，組合索引為(user_id, blog_id)，user_id和blog_id都不為null。
explain select blog_id from user_like where user_id=13;
explain結(jié)果的Extra列為Using index，查詢的列被索引覆蓋，并且where篩選條件符合最左前綴原則，通過(guò)索引查找就能直接找到符合條件的數(shù)據(jù)，不需要回表查詢數(shù)據(jù)。
explain select user_id from user_like where blog_id=1;
explain結(jié)果的Extra列為Using where; Using index， 查詢的列被索引覆蓋，where篩選條件不符合最左前綴原則，無(wú)法通過(guò)索引查找找到符合條件的數(shù)據(jù)，但可以通過(guò)索引掃描找到符合條件的數(shù)據(jù)，也不需要回表查詢數(shù)據(jù)。
導(dǎo)致索引失效的情況：
有時(shí)需要在很長(zhǎng)的字符列上創(chuàng)建索引，這會(huì)造成索引特別大且慢。使用前綴索引可以避免這個(gè)問(wèn)題。
前綴索引是指對(duì)文本或者字符串的前幾個(gè)字符建立索引，這樣索引的長(zhǎng)度更短，查詢速度更快。
創(chuàng)建前綴索引的關(guān)鍵在于選擇足夠長(zhǎng)的前綴以保證較高的索引選擇性。索引選擇性越高查詢效率就越高，因?yàn)檫x擇性高的索引可以讓MySQL在查找時(shí)過(guò)濾掉更多的數(shù)據(jù)行。
建立前綴索引的方式：
// email列創(chuàng)建前綴索引
ALTER TABLE table_name ADD KEY(column_name(prefix_length));
參考我的另一篇文章：圖解索引下推！
MySQL中常用的四種存儲(chǔ)引擎分別是： MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL 5.5版本后默認(rèn)的存儲(chǔ)引擎為InnoDB。
InnoDB存儲(chǔ)引擎
InnoDB是MySQL默認(rèn)的事務(wù)型存儲(chǔ)引擎，使用最廣泛，基于聚簇索引建立的。InnoDB內(nèi)部做了很多優(yōu)化，如能夠自動(dòng)在內(nèi)存中創(chuàng)建自適應(yīng)hash索引，以加速讀操作。
優(yōu)點(diǎn)：支持事務(wù)和崩潰修復(fù)能力；引入了行級(jí)鎖和外鍵約束。
缺點(diǎn)：占用的數(shù)據(jù)空間相對(duì)較大。
適用場(chǎng)景：需要事務(wù)支持，并且有較高的并發(fā)讀寫頻率。
MyISAM存儲(chǔ)引擎
數(shù)據(jù)以緊密格式存儲(chǔ)。對(duì)于只讀數(shù)據(jù)，或者表比較小、可以容忍修復(fù)操作，可以使用MyISAM引擎。MyISAM會(huì)將表存儲(chǔ)在兩個(gè)文件中，數(shù)據(jù)文件.MYD和索引文件.MYI。
優(yōu)點(diǎn)：訪問(wèn)速度快。
缺點(diǎn)：MyISAM不支持事務(wù)和行級(jí)鎖，不支持崩潰后的安全恢復(fù)，也不支持外鍵。
適用場(chǎng)景：對(duì)事務(wù)完整性沒(méi)有要求；表的數(shù)據(jù)都會(huì)只讀的。
MEMORY存儲(chǔ)引擎
MEMORY引擎將數(shù)據(jù)全部放在內(nèi)存中，訪問(wèn)速度較快，但是一旦系統(tǒng)奔潰的話，數(shù)據(jù)都會(huì)丟失。
MEMORY引擎默認(rèn)使用哈希索引，將鍵的哈希值和指向數(shù)據(jù)行的指針保存在哈希索引中。
優(yōu)點(diǎn)：訪問(wèn)速度較快。
缺點(diǎn)：
ARCHIVE存儲(chǔ)引擎
ARCHIVE存儲(chǔ)引擎非常適合存儲(chǔ)大量獨(dú)立的、作為歷史記錄的數(shù)據(jù)。ARCHIVE提供了壓縮功能，擁有高效的插入速度，但是這種引擎不支持索引，所以查詢性能較差。
按鎖粒度分類，有行級(jí)鎖、表級(jí)鎖和頁(yè)級(jí)鎖。
按鎖級(jí)別分類，有共享鎖、排他鎖和意向鎖。
意向共享鎖（IS）：表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入共享鎖，也就是說(shuō)一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖；
意向排他鎖（IX）：類似上面，表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入排他鎖，說(shuō)明事務(wù)在一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。
意向鎖是 InnoDB 自動(dòng)加的，不需要用戶干預(yù)。
對(duì)于INSERT、UPDATE和DELETE，InnoDB 會(huì)自動(dòng)給涉及的數(shù)據(jù)加排他鎖；對(duì)于一般的SELECT語(yǔ)句，InnoDB 不會(huì)加任何鎖，事務(wù)可以通過(guò)以下語(yǔ)句顯式加共享鎖或排他鎖。
共享鎖：SELECT … LOCK IN SHARE MODE;
排他鎖：SELECT … FOR UPDATE;
MVCC(Multiversion concurrency control) 就是同一份數(shù)據(jù)保留多版本的一種方式，進(jìn)而實(shí)現(xiàn)并發(fā)控制。在查詢的時(shí)候，通過(guò)read view和版本鏈找到對(duì)應(yīng)版本的數(shù)據(jù)。
作用：提升并發(fā)性能。對(duì)于高并發(fā)場(chǎng)景，MVCC比行級(jí)鎖開(kāi)銷更小。
MVCC 實(shí)現(xiàn)原理如下：
MVCC 的實(shí)現(xiàn)依賴于版本鏈，版本鏈?zhǔn)峭ㄟ^(guò)表的三個(gè)隱藏字段實(shí)現(xiàn)。
每條表記錄大概是這樣的：
使用事務(wù)更新行記錄的時(shí)候，就會(huì)生成版本鏈，執(zhí)行過(guò)程如下：
下面舉個(gè)例子方便大家理解。
1、初始數(shù)據(jù)如下，其中DB_ROW_ID和DB_ROLL_PTR為空。
2、事務(wù)A對(duì)該行數(shù)據(jù)做了修改，將age修改為12，效果如下：
3、之后事務(wù)B也對(duì)該行記錄做了修改，將age修改為8，效果如下：
4、此時(shí)undo log有兩行記錄，并且通過(guò)回滾指針連在一起。
接下來(lái)了解下read view的概念。
read view可以理解成將數(shù)據(jù)在每個(gè)時(shí)刻的狀態(tài)拍成“照片”記錄下來(lái)。在獲取某時(shí)刻t的數(shù)據(jù)時(shí)，到t時(shí)間點(diǎn)拍的“照片”上取數(shù)據(jù)。
在read view內(nèi)部維護(hù)一個(gè)活躍事務(wù)鏈表，表示生成read view的時(shí)候還在活躍的事務(wù)。這個(gè)鏈表包含在創(chuàng)建read view之前還未提交的事務(wù)，不包含創(chuàng)建read view之后提交的事務(wù)。
不同隔離級(jí)別創(chuàng)建read view的時(shí)機(jī)不同。
read view的記錄篩選方式
前提：DATA_TRX_ID 表示每個(gè)數(shù)據(jù)行的最新的事務(wù)ID；up_limit_id表示當(dāng)前快照中的最先開(kāi)始的事務(wù)；low_limit_id表示當(dāng)前快照中的最慢開(kāi)始的事務(wù)，即最后一個(gè)事務(wù)。
總結(jié)：InnoDB 的MVCC是通過(guò) read view 和版本鏈實(shí)現(xiàn)的，版本鏈保存有歷史版本記錄，通過(guò)read view 判斷當(dāng)前版本的數(shù)據(jù)是否可見(jiàn)，如果不可見(jiàn)，再?gòu)陌姹炬溨姓业缴弦粋€(gè)版本，繼續(xù)進(jìn)行判斷，直到找到一個(gè)可見(jiàn)的版本。
表記錄有兩種讀取方式。
快照讀情況下，InnoDB通過(guò)mvcc機(jī)制避免了幻讀現(xiàn)象。而mvcc機(jī)制無(wú)法避免當(dāng)前讀情況下出現(xiàn)的幻讀現(xiàn)象。因?yàn)楫?dāng)前讀每次讀取的都是最新數(shù)據(jù)，這時(shí)如果兩次查詢中間有其它事務(wù)插入數(shù)據(jù)，就會(huì)產(chǎn)生幻讀。
下面舉個(gè)例子說(shuō)明下：
1、首先，user表只有兩條記錄，具體如下：
2、事務(wù)a和事務(wù)b同時(shí)開(kāi)啟事務(wù)start transaction；
3、事務(wù)a插入數(shù)據(jù)然后提交；
insert into user(user_name, user_password, user_mail, user_state) values('tyson', 'a', 'a', 0);
4、事務(wù)b執(zhí)行全表的update；
update user set user_name='a';
5、事務(wù)b然后執(zhí)行查詢，查到了事務(wù)a中插入的數(shù)據(jù)。（下圖左邊是事務(wù)b，右邊是事務(wù)a。事務(wù)開(kāi)始之前只有兩條記錄，事務(wù)a插入一條數(shù)據(jù)之后，事務(wù)b查詢出來(lái)是三條數(shù)據(jù)）
以上就是當(dāng)前讀出現(xiàn)的幻讀現(xiàn)象。
那么MySQL是如何避免幻讀？
next-key包括兩部分：行鎖和間隙鎖。行鎖是加在索引上的鎖，間隙鎖是加在索引之間的。
Serializable隔離級(jí)別也可以避免幻讀，會(huì)鎖住整張表，并發(fā)性極低，一般不會(huì)使用。
SELECT 的讀取鎖定主要分為兩種方式：共享鎖和排他鎖。
select * from table where id<6 lock in share mode;--共享鎖
select * from table where id<6 for update;--排他鎖
這兩種方式主要的不同在于LOCK IN SHARE MODE多個(gè)事務(wù)同時(shí)更新同一個(gè)表單時(shí)很容易造成死鎖。
申請(qǐng)排他鎖的前提是，沒(méi)有線程對(duì)該結(jié)果集的任何行數(shù)據(jù)使用排它鎖或者共享鎖，否則申請(qǐng)會(huì)受到阻塞。在進(jìn)行事務(wù)操作時(shí)，MySQL會(huì)對(duì)查詢結(jié)果集的每行數(shù)據(jù)添加排它鎖，其他線程對(duì)這些數(shù)據(jù)的更改或刪除操作會(huì)被阻塞（只能讀操作），直到該語(yǔ)句的事務(wù)被commit語(yǔ)句或rollback語(yǔ)句結(jié)束為止。
SELECT... FOR UPDATE 使用注意事項(xiàng)：
MySQL日志主要包括查詢?nèi)罩尽⒙樵內(nèi)罩尽⑹聞?wù)日志、錯(cuò)誤日志、二進(jìn)制日志等。其中比較重要的是 bin log（二進(jìn)制日志）和 redo log（重做日志）和 undo log（回滾日志）。
bin log
bin log是MySQL數(shù)據(jù)庫(kù)級(jí)別的文件，記錄對(duì)MySQL數(shù)據(jù)庫(kù)執(zhí)行修改的所有操作，不會(huì)記錄select和show語(yǔ)句，主要用于恢復(fù)數(shù)據(jù)庫(kù)和同步數(shù)據(jù)庫(kù)。
redo log
redo log是innodb引擎級(jí)別，用來(lái)記錄innodb存儲(chǔ)引擎的事務(wù)日志，不管事務(wù)是否提交都會(huì)記錄下來(lái)，用于數(shù)據(jù)恢復(fù)。當(dāng)數(shù)據(jù)庫(kù)發(fā)生故障，innoDB存儲(chǔ)引擎會(huì)使用redo log恢復(fù)到發(fā)生故障前的時(shí)刻，以此來(lái)保證數(shù)據(jù)的完整性。將參數(shù)innodb_flush_log_at_tx_commit設(shè)置為1，那么在執(zhí)行commit時(shí)會(huì)將redo log同步寫到磁盤。
undo log
除了記錄redo log外，當(dāng)進(jìn)行數(shù)據(jù)修改時(shí)還會(huì)記錄undo log，undo log用于數(shù)據(jù)的撤回操作，它保留了記錄修改前的內(nèi)容。通過(guò)undo log可以實(shí)現(xiàn)事務(wù)回滾，并且可以根據(jù)undo log回溯到某個(gè)特定的版本的數(shù)據(jù)，實(shí)現(xiàn)MVCC。
MySQL主要分為 Server 層和存儲(chǔ)引擎層：
Server 層基本組件
當(dāng)單表的數(shù)據(jù)量達(dá)到1000W或100G以后，優(yōu)化索引、添加從庫(kù)等可能對(duì)數(shù)據(jù)庫(kù)性能提升效果不明顯，此時(shí)就要考慮對(duì)其進(jìn)行切分了。切分的目的就在于減少數(shù)據(jù)庫(kù)的負(fù)擔(dān)，縮短查詢的時(shí)間。
數(shù)據(jù)切分可以分為兩種方式：垂直劃分和水平劃分。
垂直劃分
垂直劃分?jǐn)?shù)據(jù)庫(kù)是根據(jù)業(yè)務(wù)進(jìn)行劃分，例如購(gòu)物場(chǎng)景，可以將庫(kù)中涉及商品、訂單、用戶的表分別劃分出成一個(gè)庫(kù)，通過(guò)降低單庫(kù)的大小來(lái)提高性能。同樣的，分表的情況就是將一個(gè)大表根據(jù)業(yè)務(wù)功能拆分成一個(gè)個(gè)子表，例如商品基本信息和商品描述，商品基本信息一般會(huì)展示在商品列表，商品描述在商品詳情頁(yè)，可以將商品基本信息和商品描述拆分成兩張表。
優(yōu)點(diǎn)：行記錄變小，數(shù)據(jù)頁(yè)可以存放更多記錄，在查詢時(shí)減少I/O次數(shù)。
缺點(diǎn)：
水平劃分
水平劃分是根據(jù)一定規(guī)則，例如時(shí)間或id序列值等進(jìn)行數(shù)據(jù)的拆分。比如根據(jù)年份來(lái)拆分不同的數(shù)據(jù)庫(kù)。每個(gè)數(shù)據(jù)庫(kù)結(jié)構(gòu)一致，但是數(shù)據(jù)得以拆分，從而提升性能。
優(yōu)點(diǎn)：?jiǎn)螏?kù)（表）的數(shù)據(jù)量得以減少，提高性能；切分出的表結(jié)構(gòu)相同，程序改動(dòng)較少。
缺點(diǎn)：
分區(qū)是把一張表的數(shù)據(jù)分成N多個(gè)區(qū)塊。分區(qū)表是一個(gè)獨(dú)立的邏輯表，但是底層由多個(gè)物理子表組成。
當(dāng)查詢條件的數(shù)據(jù)分布在某一個(gè)分區(qū)的時(shí)候，查詢引擎只會(huì)去某一個(gè)分區(qū)查詢，而不是遍歷整個(gè)表。在管理層面，如果需要?jiǎng)h除某一個(gè)分區(qū)的數(shù)據(jù)，只需要?jiǎng)h除對(duì)應(yīng)的分區(qū)即可。
分區(qū)一般都是放在單機(jī)里的，用的比較多的是時(shí)間范圍分區(qū)，方便歸檔。只不過(guò)分庫(kù)分表需要代碼實(shí)現(xiàn)，分區(qū)則是mysql內(nèi)部實(shí)現(xiàn)。分庫(kù)分表和分區(qū)并不沖突，可以結(jié)合使用。
range分區(qū)，按照范圍分區(qū)。比如按照時(shí)間范圍分區(qū)
CREATE TABLE test_range_partition(
id INT auto_increment,
createdate DATETIME,
primary key (id,createdate)
)
PARTITION BY RANGE (TO_DAYS(createdate) ) (
PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
);
在/var/lib/mysql/data/可以找到對(duì)應(yīng)的數(shù)據(jù)文件，每個(gè)分區(qū)表都有一個(gè)使用#分隔命名的表文件：
-rw-r----- 1 MySQL MySQL 65 Mar 14 21:47 db.opt
-rw-r----- 1 MySQL MySQL 8598 Mar 14 21:50 test_range_partition.frm
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201801.ibd
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201802.ibd
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201803.ibd
...
list分區(qū)
list分區(qū)和range分區(qū)相似，主要區(qū)別在于list是枚舉值列表的集合，range是連續(xù)的區(qū)間值的集合。對(duì)于list分區(qū)，分區(qū)字段必須是已知的，如果插入的字段不在分區(qū)時(shí)的枚舉值中，將無(wú)法插入。
create table test_list_partiotion
(
id int auto_increment,
data_type tinyint,
primary key(id,data_type)
)partition by list(data_type)
(
partition p0 values in (0,1,2,3,4,5,6),
partition p1 values in (7,8,9,10,11,12),
partition p2 values in (13,14,15,16,17)
);
hash分區(qū)
可以將數(shù)據(jù)均勻地分布到預(yù)先定義的分區(qū)中。
create table test_hash_partiotion
(
id int auto_increment,
create_date datetime,
primary key(id,create_date)
)partition by hash(year(create_date)) partitions 10;
查詢語(yǔ)句的執(zhí)行流程如下：權(quán)限校驗(yàn)、查詢緩存、分析器、優(yōu)化器、權(quán)限校驗(yàn)、執(zhí)行器、引擎。
舉個(gè)例子，查詢語(yǔ)句如下：
select * from user where id > 1 and name='大彬';
更新語(yǔ)句執(zhí)行流程如下：分析器、權(quán)限校驗(yàn)、執(zhí)行器、引擎、redo log（prepare狀態(tài)）、binlog、redo log（commit狀態(tài)）
舉個(gè)例子，更新語(yǔ)句如下：
update user set name='大彬' where id=1;
為什么記錄完redo log，不直接提交，而是先進(jìn)入prepare狀態(tài)？
假設(shè)先寫redo log直接提交，然后寫binlog，寫完redo log后，機(jī)器掛了，binlog日志沒(méi)有被寫入，那么機(jī)器重啟后，這臺(tái)機(jī)器會(huì)通過(guò)redo log恢復(fù)數(shù)據(jù)，但是這個(gè)時(shí)候binlog并沒(méi)有記錄該數(shù)據(jù)，后續(xù)進(jìn)行機(jī)器備份的時(shí)候，就會(huì)丟失這一條數(shù)據(jù)，同時(shí)主從同步也會(huì)丟失這一條數(shù)據(jù)。
exists用于對(duì)外表記錄做篩選。exists會(huì)遍歷外表，將外查詢表的每一行，代入內(nèi)查詢進(jìn)行判斷。當(dāng)exists里的條件語(yǔ)句能夠返回記錄行時(shí)，條件就為真，返回外表當(dāng)前記錄。反之如果exists里的條件語(yǔ)句不能返回記錄行，條件為假，則外表當(dāng)前記錄被丟棄。
select a.* from A awhere exists(select 1 from B b where a.id=b.id)
in是先把后邊的語(yǔ)句查出來(lái)放到臨時(shí)表中，然后遍歷臨時(shí)表，將臨時(shí)表的每一行，代入外查詢?nèi)ゲ檎摇?/p>
select * from Awhere id in(select id from B)
子查詢的表比較大的時(shí)候，使用exists可以有效減少總的循環(huán)次數(shù)來(lái)提升速度；當(dāng)外查詢的表比較大的時(shí)候，使用in可以有效減少對(duì)外查詢表循環(huán)遍歷來(lái)提升速度。
相同點(diǎn)：
不同點(diǎn)：
int(10)中的10表示的是顯示數(shù)據(jù)的長(zhǎng)度，而char(10)表示的是存儲(chǔ)數(shù)據(jù)的長(zhǎng)度。
主從同步使得數(shù)據(jù)可以從一個(gè)數(shù)據(jù)庫(kù)服務(wù)器復(fù)制到其他服務(wù)器上，在復(fù)制數(shù)據(jù)時(shí)，一個(gè)服務(wù)器充當(dāng)主服務(wù)器（master），其余的服務(wù)器充當(dāng)從服務(wù)器（slave）。
因?yàn)閺?fù)制是異步進(jìn)行的，所以從服務(wù)器不需要一直連接著主服務(wù)器，從服務(wù)器甚至可以通過(guò)撥號(hào)斷斷續(xù)續(xù)地連接主服務(wù)器。通過(guò)配置文件，可以指定復(fù)制所有的數(shù)據(jù)庫(kù)，某個(gè)數(shù)據(jù)庫(kù)，甚至是某個(gè)數(shù)據(jù)庫(kù)上的某個(gè)表。
數(shù)據(jù)庫(kù)中的并發(fā)控制是確保在多個(gè)事務(wù)同時(shí)存取數(shù)據(jù)庫(kù)中同一數(shù)據(jù)時(shí)不破壞事務(wù)的隔離性和統(tǒng)一性以及數(shù)據(jù)庫(kù)的統(tǒng)一性。樂(lè)觀鎖和悲觀鎖是并發(fā)控制主要采用的技術(shù)手段。
show processlist 或 show full processlist 可以查看當(dāng)前 MySQL 是否有壓力，正在運(yùn)行的SQL，有沒(méi)有慢SQL正在執(zhí)行。返回參數(shù)如下：
兩種查詢方式。對(duì)應(yīng) limit offset, size 和 limit size 兩種方式。
而其實(shí) limit size ，相當(dāng)于 limit 0, size。也就是從0開(kāi)始取size條數(shù)據(jù)。
也就是說(shuō)，兩種方式的區(qū)別在于offset是否為0。
先來(lái)看下limit sql的內(nèi)部執(zhí)行邏輯。
MySQL內(nèi)部分為server層和存儲(chǔ)引擎層。一般情況下存儲(chǔ)引擎都用innodb。
server層有很多模塊，其中需要關(guān)注的是執(zhí)行器是用于跟存儲(chǔ)引擎打交道的組件。
執(zhí)行器可以通過(guò)調(diào)用存儲(chǔ)引擎提供的接口，將一行行數(shù)據(jù)取出，當(dāng)這些數(shù)據(jù)完全符合要求（比如滿足其他where條件），則會(huì)放到結(jié)果集中，最后返回給調(diào)用mysql的客戶端。
以主鍵索引的limit執(zhí)行過(guò)程為例：
執(zhí)行select * from xxx order by id limit 0, 10;，select后面帶的是星號(hào)，也就是要求獲得行數(shù)據(jù)的所有字段信息。
server層會(huì)調(diào)用innodb的接口，在innodb里的主鍵索引中獲取到第0到10條完整行數(shù)據(jù)，依次返回給server層，并放到server層的結(jié)果集中，返回給客戶端。
把offset搞大點(diǎn)，比如執(zhí)行的是：select * from xxx order by id limit 500000, 10;
server層會(huì)調(diào)用innodb的接口，由于這次的offset=500000，會(huì)在innodb里的主鍵索引中獲取到第0到（500000 + 10）條完整行數(shù)據(jù)，返回給server層之后根據(jù)offset的值挨個(gè)拋棄，最后只留下最后面的size條，也就是10條數(shù)據(jù)，放到server層的結(jié)果集中，返回給客戶端。
可以看出，當(dāng)offset非0時(shí)，server層會(huì)從引擎層獲取到很多無(wú)用的數(shù)據(jù)，而獲取的這些無(wú)用數(shù)據(jù)都是要耗時(shí)的。
因此，mysql查詢中 limit 1000,10 會(huì)比 limit 10 更慢。原因是 limit 1000,10 會(huì)取出1000+10條數(shù)據(jù)，并拋棄前1000條，這部分耗時(shí)更大。
還是以上面的SQL為空：select * from xxx order by id limit 500000, 10;
方法一：
從上面的分析可以看出，當(dāng)offset非常大時(shí)，server層會(huì)從引擎層獲取到很多無(wú)用的數(shù)據(jù)，而當(dāng)select后面是*號(hào)時(shí)，就需要拷貝完整的行信息，拷貝完整數(shù)據(jù)相比只拷貝行數(shù)據(jù)里的其中一兩個(gè)列字段更耗費(fèi)時(shí)間。
因?yàn)榍懊娴膐ffset條數(shù)據(jù)最后都是不要的，沒(méi)有必要拷貝完整字段，所以可以將sql語(yǔ)句修改成：
select * from xxx where id >=(select id from xxx order by id limit 500000, 1) order by id limit 10;
先執(zhí)行子查詢 select id from xxx by id limit 500000, 1, 這個(gè)操作，其實(shí)也是將在innodb中的主鍵索引中獲取到500000+1條數(shù)據(jù)，然后server層會(huì)拋棄前500000條，只保留最后一條數(shù)據(jù)的id。
但不同的地方在于，在返回server層的過(guò)程中，只會(huì)拷貝數(shù)據(jù)行內(nèi)的id這一列，而不會(huì)拷貝數(shù)據(jù)行的所有列，當(dāng)數(shù)據(jù)量較大時(shí)，這部分的耗時(shí)還是比較明顯的。
在拿到了上面的id之后，假設(shè)這個(gè)id正好等于500000，那sql就變成了
select * from xxx where id >=500000 order by id limit 10;
這樣innodb再走一次主鍵索引，通過(guò)B+樹(shù)快速定位到id=500000的行數(shù)據(jù)，時(shí)間復(fù)雜度是lg(n)，然后向后取10條數(shù)據(jù)。
方法二：
將所有的數(shù)據(jù)根據(jù)id主鍵進(jìn)行排序，然后分批次取，將當(dāng)前批次的最大id作為下次篩選的條件進(jìn)行查詢。
select * from xxx where id > start_id order by id limit 10;
mysql
通過(guò)主鍵索引，每次定位到start_id的位置，然后往后遍歷10個(gè)數(shù)據(jù)，這樣不管數(shù)據(jù)多大，查詢性能都較為穩(wěn)定。
InnoDB存儲(chǔ)引擎有自己的最小儲(chǔ)存單元——頁(yè)（Page）。
查詢InnoDB頁(yè)大小的命令如下：
mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
可以看出 innodb 默認(rèn)的一頁(yè)大小為 16384B=16384/1024=16kb。
在MySQL中，B+樹(shù)一個(gè)節(jié)點(diǎn)的大小設(shè)為一頁(yè)或頁(yè)的倍數(shù)最為合適。因?yàn)槿绻粋€(gè)節(jié)點(diǎn)的大小 < 1頁(yè)，那么讀取這個(gè)節(jié)點(diǎn)的時(shí)候其實(shí)讀取的還是一頁(yè)，這樣就造成了資源的浪費(fèi)。
B+樹(shù)中非葉子節(jié)點(diǎn)存的是key + 指針；葉子節(jié)點(diǎn)存的是數(shù)據(jù)行。
對(duì)于葉子節(jié)點(diǎn)，如果一行數(shù)據(jù)大小為1k，那么一頁(yè)就能存16條數(shù)據(jù)。
對(duì)于非葉子節(jié)點(diǎn)，如果key使用的是bigint，則為8字節(jié)，指針在MySQL中為6字節(jié)，一共是14字節(jié)，則16k能存放 16 * 1024 / 14=1170 個(gè)索引指針。
于是可以算出，對(duì)于一顆高度為2的B+樹(shù)，根節(jié)點(diǎn)存儲(chǔ)索引指針節(jié)點(diǎn)，那么它有1170個(gè)葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)，每個(gè)葉子節(jié)點(diǎn)可以存儲(chǔ)16條數(shù)據(jù)，一共 1170 x 16=18720 條數(shù)據(jù)。而對(duì)于高度為3的B+樹(shù)，就可以存放 1170 x 1170 x 16=21902400 條數(shù)據(jù)（兩千多萬(wàn)條數(shù)據(jù)），也就是對(duì)于兩千多萬(wàn)條的數(shù)據(jù)，我們只需要高度為3的B+樹(shù)就可以完成，通過(guò)主鍵查詢只需要3次IO操作就能查到對(duì)應(yīng)數(shù)據(jù)。
所以在 InnoDB 中B+樹(shù)高度一般為3層時(shí)，就能滿足千萬(wàn)級(jí)的數(shù)據(jù)存儲(chǔ)。
參考：https://www.cnblogs.com/leefreeman/p/8315844.html
目前主流的有兩種說(shuō)法：
事實(shí)上，這個(gè)數(shù)值和實(shí)際記錄的條數(shù)無(wú)關(guān)，而與 MySQL 的配置以及機(jī)器的硬件有關(guān)。因?yàn)镸ySQL為了提高性能，會(huì)將表的索引裝載到內(nèi)存中。在InnoDB buffer size 足夠的情況下，其能完成全加載進(jìn)內(nèi)存，查詢不會(huì)有問(wèn)題。但是，當(dāng)單表數(shù)據(jù)庫(kù)到達(dá)某個(gè)量級(jí)的上限時(shí)，導(dǎo)致內(nèi)存無(wú)法存儲(chǔ)其索引，使得之后的 SQL 查詢會(huì)產(chǎn)生磁盤 IO，從而導(dǎo)致性能下降。當(dāng)然，這個(gè)還有具體的表結(jié)構(gòu)的設(shè)計(jì)有關(guān)，最終導(dǎo)致的問(wèn)題都是內(nèi)存限制。
因此，對(duì)于分庫(kù)分表，需要結(jié)合實(shí)際需求，不宜過(guò)度設(shè)計(jì)，在項(xiàng)目一開(kāi)始不采用分庫(kù)與分表設(shè)計(jì)，而是隨著業(yè)務(wù)的增長(zhǎng)，在無(wú)法繼續(xù)優(yōu)化的情況下，再考慮分庫(kù)與分表提高系統(tǒng)的性能。對(duì)此，阿里巴巴《Java 開(kāi)發(fā)手冊(cè)》補(bǔ)充到：如果預(yù)計(jì)三年后的數(shù)據(jù)量根本達(dá)不到這個(gè)級(jí)別，請(qǐng)不要在創(chuàng)建表時(shí)就分庫(kù)分表。
至于MySQL單表多大進(jìn)行分庫(kù)分表，應(yīng)當(dāng)根據(jù)機(jī)器資源進(jìn)行評(píng)估。
某個(gè)表有近千萬(wàn)數(shù)據(jù)，查詢比較慢，如何優(yōu)化？
當(dāng)MySQL單表記錄數(shù)過(guò)大時(shí)，數(shù)據(jù)庫(kù)的性能會(huì)明顯下降，一些常見(jiàn)的優(yōu)化措施如下：
嗯，先說(shuō)說(shuō)count(1) and count(字段名)的區(qū)別。
兩者的主要區(qū)別是
接下來(lái)看看三者之間的區(qū)別。
執(zhí)行效果上：
執(zhí)行效率上：
嗯，TIMESTAMP和DATETIME都可以用來(lái)存儲(chǔ)時(shí)間，它們主要有以下區(qū)別：
1.表示范圍
TIMESTAMP支持的時(shí)間范圍比DATATIME要小，容易出現(xiàn)超出的情況。
2.空間占用
3.存入時(shí)間是否會(huì)自動(dòng)轉(zhuǎn)換
TIMESTAMP類型在默認(rèn)情況下，insert、update 數(shù)據(jù)時(shí)，TIMESTAMP列會(huì)自動(dòng)以當(dāng)前時(shí)間（CURRENT_TIMESTAMP）填充/更新。DATETIME則不會(huì)做任何轉(zhuǎn)換，也不會(huì)檢測(cè)時(shí)區(qū)，你給什么數(shù)據(jù)，它存什么數(shù)據(jù)。
4.TIMESTAMP比較受時(shí)區(qū)timezone的影響以及MYSQL版本和服務(wù)器的SQL MODE的影響。因?yàn)?span style="color: #E83E8C; --tt-darkmode-color: #E83E8C;">TIMESTAMP存的是時(shí)間戳，在不同的時(shí)區(qū)得出的時(shí)間不一致。
5.如果存進(jìn)NULL，兩者實(shí)際存儲(chǔ)的值不同。
外鍵是一種約束，這個(gè)約束的存在，會(huì)保證表間數(shù)據(jù)的關(guān)系始終完整。外鍵的存在，并非全然沒(méi)有優(yōu)點(diǎn)。
外鍵可以保證數(shù)據(jù)的完整性和一致性，級(jí)聯(lián)操作方便。而且使用外鍵可以將數(shù)據(jù)完整性判斷托付給了數(shù)據(jù)庫(kù)完成，減少了程序的代碼量。
雖然外鍵能夠保證數(shù)據(jù)的完整性，但是會(huì)給系統(tǒng)帶來(lái)很多缺陷。
1、并發(fā)問(wèn)題。在使用外鍵的情況下，每次修改數(shù)據(jù)都需要去另外一個(gè)表檢查數(shù)據(jù)，需要獲取額外的鎖。若是在高并發(fā)大流量事務(wù)場(chǎng)景，使用外鍵更容易造成死鎖。
2、擴(kuò)展性問(wèn)題。比如從MySQL遷移到Oracle，外鍵依賴于數(shù)據(jù)庫(kù)本身的特性，做遷移可能不方便。
3、不利于分庫(kù)分表。在水平拆分和分庫(kù)的情況下，外鍵是無(wú)法生效的。將數(shù)據(jù)間關(guān)系的維護(hù)，放入應(yīng)用程序中，為將來(lái)的分庫(kù)分表省去很多的麻煩。
自增主鍵可以讓主鍵索引盡量地保持遞增順序插入，避免了頁(yè)分裂，因此索引更緊湊，在查詢的時(shí)候，效率也就更高。
主要為了提升插入數(shù)據(jù)的效率和并行度。
假設(shè)有兩個(gè)并行執(zhí)行的事務(wù)，在申請(qǐng)自增值的時(shí)候，為了避免兩個(gè)事務(wù)申請(qǐng)到相同的自增 id，肯定要加鎖，然后順序申請(qǐng)。
假設(shè)事務(wù) A 申請(qǐng)到了 id=2， 事務(wù) B 申請(qǐng)到 id=3，那么這時(shí)候表 t 的自增值是 4，之后繼續(xù)執(zhí)行。
事務(wù) B 正確提交了，但事務(wù) A 出現(xiàn)了唯一鍵沖突。
如果允許事務(wù) A 把自增 id 回退，也就是把表 t 的當(dāng)前自增值改回 2，那么就會(huì)出現(xiàn)這樣的情況：表里面已經(jīng)有 id=3 的行，而當(dāng)前的自增 id 值是 2。
接下來(lái)，繼續(xù)執(zhí)行的其他事務(wù)就會(huì)申請(qǐng)到 id=2，然后再申請(qǐng)到 id=3。這時(shí)，就會(huì)出現(xiàn)插入語(yǔ)句報(bào)錯(cuò)“主鍵沖突”。
而為了解決這個(gè)主鍵沖突，有兩種方法：
可見(jiàn)，這兩個(gè)方法都會(huì)導(dǎo)致性能問(wèn)題。
因此，InnoDB 放棄了“允許自增 id 回退”這個(gè)設(shè)計(jì)，語(yǔ)句執(zhí)行失敗也不回退自增 id。
不同的引擎對(duì)于自增值的保存策略不同：
不一定，有幾種情況會(huì)導(dǎo)致自增主鍵不連續(xù)。
1、唯一鍵沖突導(dǎo)致自增主鍵不連續(xù)。當(dāng)我們向一個(gè)自增主鍵的InnoDB表中插入數(shù)據(jù)的時(shí)候，如果違反表中定義的唯一索引的唯一約束，會(huì)導(dǎo)致插入數(shù)據(jù)失敗。此時(shí)表的自增主鍵的鍵值是會(huì)向后加1滾動(dòng)的。下次再次插入數(shù)據(jù)的時(shí)候，就不能再使用上次因插入數(shù)據(jù)失敗而滾動(dòng)生成的鍵值了，必須使用新滾動(dòng)生成的鍵值。
2、事務(wù)回滾導(dǎo)致自增主鍵不連續(xù)。當(dāng)我們向一個(gè)自增主鍵的InnoDB表中插入數(shù)據(jù)的時(shí)候，如果顯式開(kāi)啟了事務(wù)，然后因?yàn)槟撤N原因最后回滾了事務(wù)，此時(shí)表的自增值也會(huì)發(fā)生滾動(dòng)，而接下里新插入的數(shù)據(jù)，也將不能使用滾動(dòng)過(guò)的自增值，而是需要重新申請(qǐng)一個(gè)新的自增值。
3、批量插入導(dǎo)致自增值不連續(xù)。MySQL有一個(gè)批量申請(qǐng)自增id的策略：
如果下一個(gè)事務(wù)再次插入數(shù)據(jù)的時(shí)候，則會(huì)基于上一個(gè)事務(wù)申請(qǐng)后的自增值基礎(chǔ)上再申請(qǐng)。此時(shí)就出現(xiàn)自增值不連續(xù)的情況出現(xiàn)。
4、自增步長(zhǎng)不是1，也會(huì)導(dǎo)致自增主鍵不連續(xù)。
參考：https://cloud.tencent.com/developer/article/1805755
有兩種方案：
1、通過(guò)MySQL自動(dòng)同步刷新Redis，MySQL觸發(fā)器+UDF函數(shù)實(shí)現(xiàn)。
過(guò)程大致如下：
2、解析MySQL的binlog，實(shí)現(xiàn)將數(shù)據(jù)庫(kù)中的數(shù)據(jù)同步到Redis。可以通過(guò)canal實(shí)現(xiàn)。canal是阿里巴巴旗下的一款開(kāi)源項(xiàng)目，基于數(shù)據(jù)庫(kù)增量日志解析，提供增量數(shù)據(jù)訂閱&消費(fèi)。
canal的原理如下：
先看看什么是存儲(chǔ)過(guò)程。
存儲(chǔ)過(guò)程是在大型數(shù)據(jù)庫(kù)系統(tǒng)中，一組為了完成特定功能的SQL 語(yǔ)句集，它存儲(chǔ)在數(shù)據(jù)庫(kù)中，一次編譯后永久有效，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它。
存儲(chǔ)過(guò)程主要有以下幾個(gè)缺點(diǎn)。
事務(wù)特性ACID：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）。
第一范式1NF
確保數(shù)據(jù)庫(kù)表字段的原子性。
比如字段 userInfo: 廣東省 10086' ，依照第一范式必須拆分成 userInfo: 廣東省 userTel:10086兩個(gè)字段。
第二范式2NF
首先要滿足第一范式，另外包含兩部分內(nèi)容，一是表必須有一個(gè)主鍵；二是非主鍵列必須完全依賴于主鍵，而不能只依賴于主鍵的一部分。
舉個(gè)例子。假定選課關(guān)系表為student_course(student_no, student_name, age, course_name, grade, credit)，主鍵為(student_no, course_name)。其中學(xué)分完全依賴于課程名稱，姓名年齡完全依賴學(xué)號(hào)，不符合第二范式，會(huì)導(dǎo)致數(shù)據(jù)冗余（學(xué)生選n門課，姓名年齡有n條記錄）、插入異常（插入一門新課，因?yàn)闆](méi)有學(xué)號(hào)，無(wú)法保存新課記錄）等問(wèn)題。
應(yīng)該拆分成三個(gè)表：學(xué)生：student(stuent_no, student_name, 年齡)；課程：course(course_name, credit)；選課關(guān)系：student_course_relation(student_no, course_name, grade)。
第三范式3NF
首先要滿足第二范式，另外非主鍵列必須直接依賴于主鍵，不能存在傳遞依賴。即不能存在：非主鍵列 A 依賴于非主鍵列 B，非主鍵列 B 依賴于主鍵的情況。
假定學(xué)生關(guān)系表為Student(student_no, student_name, age, academy_id, academy_telephone)，主鍵為"學(xué)號(hào)"，其中學(xué)院id依賴于學(xué)號(hào)，而學(xué)院地點(diǎn)和學(xué)院電話依賴于學(xué)院id，存在傳遞依賴，不符合第三范式。
可以把學(xué)生關(guān)系表分為如下兩個(gè)表：學(xué)生：(student_no, student_name, age, academy_id)；學(xué)院：(academy_id, academy_telephone)。
2NF和3NF的區(qū)別？
先了解下幾個(gè)概念：臟讀、不可重復(fù)讀、幻讀。
不可重復(fù)讀和臟讀的區(qū)別是，臟讀是某一事務(wù)讀取了另一個(gè)事務(wù)未提交的臟數(shù)據(jù)，而不可重復(fù)讀則是讀取了前一事務(wù)提交的數(shù)據(jù)。
事務(wù)隔離就是為了解決上面提到的臟讀、不可重復(fù)讀、幻讀這幾個(gè)問(wèn)題。
MySQL數(shù)據(jù)庫(kù)為我們提供的四種隔離級(jí)別：
查看隔離級(jí)別：
select @@transaction_isolation;
設(shè)置隔離級(jí)別：
set session transaction isolation level read uncommitted;
生產(chǎn)環(huán)境大多使用RC。為什么不是RR呢？
可重復(fù)讀(Repeatable Read)，簡(jiǎn)稱為RR
讀已提交(Read Commited)，簡(jiǎn)稱為RC
緣由一：在RR隔離級(jí)別下，存在間隙鎖，導(dǎo)致出現(xiàn)死鎖的幾率比RC大的多！
緣由二：在RR隔離級(jí)別下，條件列未命中索引會(huì)鎖表！而在RC隔離級(jí)別下，只鎖行!
也就是說(shuō)，RC的并發(fā)性高于RR。
并且大部分場(chǎng)景下，不可重復(fù)讀問(wèn)題是可以接受的。畢竟數(shù)據(jù)都已經(jīng)提交了，讀出來(lái)本身就沒(méi)有太大問(wèn)題！
互聯(lián)網(wǎng)項(xiàng)目中mysql應(yīng)該選什么事務(wù)隔離級(jí)別
我們平時(shí)可以在編輯器上輸入各種中文英文字母，但這些都是給人讀的，不是給計(jì)算機(jī)讀的，其實(shí)計(jì)算機(jī)真正保存和傳輸數(shù)據(jù)都是以二進(jìn)制0101的格式進(jìn)行的。
那么就需要有一個(gè)規(guī)則，把中文和英文字母轉(zhuǎn)化為二進(jìn)制。其中d對(duì)應(yīng)十六進(jìn)制下的64，它可以轉(zhuǎn)換為01二進(jìn)制的格式。于是字母和數(shù)字就這樣一一對(duì)應(yīng)起來(lái)了，這就是ASCII編碼格式。
它用一個(gè)字節(jié)，也就是8位來(lái)標(biāo)識(shí)字符，基礎(chǔ)符號(hào)有128個(gè)，擴(kuò)展符號(hào)也是128個(gè)。也就只能表示下英文字母和數(shù)字。
這明顯不夠用。于是，為了標(biāo)識(shí)中文，出現(xiàn)了GB2312的編碼格式。為了標(biāo)識(shí)希臘語(yǔ)，出現(xiàn)了greek編碼格式，為了標(biāo)識(shí)俄語(yǔ)，整了cp866編碼格式。
為了統(tǒng)一它們，于是出現(xiàn)了Unicode編碼格式，它用了2~4個(gè)字節(jié)來(lái)表示字符，這樣理論上所有符號(hào)都能被收錄進(jìn)去，并且它還完全兼容ASCII的編碼，也就是說(shuō)，同樣是字母d，在ASCII用64表示，在Unicode里還是用64來(lái)表示。
但不同的地方是ASCII編碼用1個(gè)字節(jié)來(lái)表示，而Unicode用則兩個(gè)字節(jié)來(lái)表示。
同樣都是字母d，unicode比ascii多使用了一個(gè)字節(jié)，如下：
D ASCII: 01100100
D Unicode: 00000000 01100100
可以看到，上面的unicode編碼，前面的都是0，其實(shí)用不上，但還占了個(gè)字節(jié)，有點(diǎn)浪費(fèi)。如果我們能做到該隱藏時(shí)隱藏，這樣就能省下不少空間，按這個(gè)思路，就是就有了UTF-8編碼。
總結(jié)一下，按照一定規(guī)則把符號(hào)和二進(jìn)制碼對(duì)應(yīng)起來(lái)，這就是編碼。而把n多這種已經(jīng)編碼的字符聚在一起，就是我們常說(shuō)的字符集。
比如utf-8字符集就是所有utf-8編碼格式的字符的合集。
想看下mysql支持哪些字符集。可以執(zhí)行 show charset;
上面提到utf-8是在unicode的基礎(chǔ)上做的優(yōu)化，既然unicode有辦法表示所有字符，那utf-8也一樣可以表示所有字符，為了避免混淆，我在后面叫它大utf8。
mysql支持的字符集中有utf8和utf8mb4。
先說(shuō)utf8mb4編碼，mb4就是most bytes 4的意思，從上圖最右邊的Maxlen可以看到，它最大支持用4個(gè)字節(jié)來(lái)表示字符，它幾乎可以用來(lái)表示目前已知的所有的字符。
再說(shuō)mysql字符集里的utf8，它是數(shù)據(jù)庫(kù)的默認(rèn)字符集。但注意，此utf8非彼utf8，我們叫它小utf8字符集。為什么這么說(shuō)，因?yàn)閺腗axlen可以看出，它最多支持用3個(gè)字節(jié)去表示字符，按utf8mb4的命名方式，準(zhǔn)確點(diǎn)應(yīng)該叫它utf8mb3。
utf8 就像是閹割版的utf8mb4，只支持部分字符。比如emoji表情，它就不支持。
而mysql支持的字符集里，第三列，collation，它是指字符集的比較規(guī)則。
比如，"debug"和"Debug"是同一個(gè)單詞，但它們大小寫不同，該不該判為同一個(gè)單詞呢。
這時(shí)候就需要用到collation了。
通過(guò)SHOW COLLATION WHERE Charset='utf8mb4';可以查看到utf8mb4下支持什么比較規(guī)則。
如果collation=utf8mb4_general_ci，是指使用utf8mb4字符集的前提下，挨個(gè)字符進(jìn)行比較（general），并且不區(qū)分大小寫（_ci，case insensitice）。
這種情況下，"debug"和"Debug"是同一個(gè)單詞。
如果改成collation=utf8mb4_bin，就是指挨個(gè)比較二進(jìn)制位大小。
于是"debug"和"Debug"就不是同一個(gè)單詞。
那utf8mb4對(duì)比utf8有什么劣勢(shì)嗎？
我們知道數(shù)據(jù)庫(kù)表里，字段類型如果是char(2)的話，里面的2是指字符個(gè)數(shù)，也就是說(shuō)不管這張表用的是什么編碼的字符集，都能放上2個(gè)字符。
而char又是固定長(zhǎng)度，為了能放下2個(gè)utf8mb4的字符，char會(huì)默認(rèn)保留2*4（maxlen=4）=8個(gè)字節(jié)的空間。
如果是utf8mb3，則會(huì)默認(rèn)保留 2 * 3 (maxlen=3)=6個(gè)字節(jié)的空間。也就是說(shuō)，在這種情況下，utf8mb4會(huì)比utf8mb3多使用一些空間。
索引是存儲(chǔ)引擎用于提高數(shù)據(jù)庫(kù)表的訪問(wèn)速度的一種數(shù)據(jù)結(jié)構(gòu)。它可以比作一本字典的目錄，可以幫你快速找到對(duì)應(yīng)的記錄。
索引一般存儲(chǔ)在磁盤的文件中，它是占用物理空間的。
優(yōu)點(diǎn)：
缺點(diǎn)：
數(shù)據(jù)是存儲(chǔ)在磁盤上的，查詢數(shù)據(jù)時(shí)，如果沒(méi)有索引，會(huì)加載所有的數(shù)據(jù)到內(nèi)存，依次進(jìn)行檢索，讀取磁盤次數(shù)較多。有了索引，就不需要加載所有數(shù)據(jù)，因?yàn)锽+樹(shù)的高度一般在2-4層，最多只需要讀取2-4次磁盤，查詢速度大大提升。
索引的數(shù)據(jù)結(jié)構(gòu)主要有B+樹(shù)和哈希表，對(duì)應(yīng)的索引分別為B+樹(shù)索引和哈希索引。InnoDB引擎的索引類型有B+樹(shù)索引和哈希索引，默認(rèn)的索引類型為B+樹(shù)索引。
B+樹(shù)索引
B+ 樹(shù)是基于B 樹(shù)和葉子節(jié)點(diǎn)順序訪問(wèn)指針進(jìn)行實(shí)現(xiàn)，它具有B樹(shù)的平衡性，并且通過(guò)順序訪問(wèn)指針來(lái)提高區(qū)間查詢的性能。
在 B+ 樹(shù)中，節(jié)點(diǎn)中的 key 從左到右遞增排列，如果某個(gè)指針的左右相鄰 key 分別是 keyi 和 keyi+1，則該指針指向節(jié)點(diǎn)的所有 key 大于等于 keyi 且小于等于 keyi+1。
進(jìn)行查找操作時(shí)，首先在根節(jié)點(diǎn)進(jìn)行二分查找，找到key所在的指針，然后遞歸地在指針?biāo)赶虻墓?jié)點(diǎn)進(jìn)行查找。直到查找到葉子節(jié)點(diǎn)，然后在葉子節(jié)點(diǎn)上進(jìn)行二分查找，找出key所對(duì)應(yīng)的數(shù)據(jù)項(xiàng)。
MySQL 數(shù)據(jù)庫(kù)使用最多的索引類型是BTREE索引，底層基于B+樹(shù)數(shù)據(jù)結(jié)構(gòu)來(lái)實(shí)現(xiàn)。
mysql> show index from blog\G;
*************************** 1. row ***************************
Table: blog
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: blog_id
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
哈希索引
哈希索引是基于哈希表實(shí)現(xiàn)的，對(duì)于每一行數(shù)據(jù)，存儲(chǔ)引擎會(huì)對(duì)索引列進(jìn)行哈希計(jì)算得到哈希碼，并且哈希算法要盡量保證不同的列值計(jì)算出的哈希碼值是不同的，將哈希碼的值作為哈希表的key值，將指向數(shù)據(jù)行的指針作為哈希表的value值。這樣查找一個(gè)數(shù)據(jù)的時(shí)間復(fù)雜度就是O(1)，一般多用于精確查找。
1、主鍵索引：名為primary的唯一非空索引，不允許有空值。
2、唯一索引：索引列中的值必須是唯一的，但是允許為空值。唯一索引和主鍵索引的區(qū)別是：唯一索引字段可以為null且可以存在多個(gè)null值，而主鍵索引字段不可以為null。唯一索引的用途：唯一標(biāo)識(shí)數(shù)據(jù)庫(kù)表中的每條記錄，主要是用來(lái)防止數(shù)據(jù)重復(fù)插入。創(chuàng)建唯一索引的SQL語(yǔ)句如下：
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);
3、組合索引：在表中的多個(gè)字段組合上創(chuàng)建的索引，只有在查詢條件中使用了這些字段的左邊字段時(shí)，索引才會(huì)被使用，使用組合索引時(shí)需遵循最左前綴原則。
4、全文索引：只能在CHAR、VARCHAR和TEXT類型字段上使用全文索引。
5、普通索引：普通索引是最基本的索引，它沒(méi)有任何限制，值可以為空。
如果 SQL 語(yǔ)句中用到了組合索引中的最左邊的索引，那么這條 SQL 語(yǔ)句就可以利用這個(gè)組合索引去進(jìn)行匹配。當(dāng)遇到范圍查詢(>、<、between、like)就會(huì)停止匹配，后面的字段不會(huì)用到索引。
對(duì)(a,b,c)建立索引，查詢條件使用 a/ab/abc 會(huì)走索引，使用 bc 不會(huì)走索引。
對(duì)(a,b,c,d)建立索引，查詢條件為a=1 and b=2 and c > 3 and d=4，那么a、b和c三個(gè)字段能用到索引，而d無(wú)法使用索引。因?yàn)橛龅搅朔秶樵儭?/p>
如下圖，對(duì)(a, b) 建立索引，a 在索引樹(shù)中是全局有序的，而 b 是全局無(wú)序，局部有序（當(dāng)a相等時(shí)，會(huì)根據(jù)b進(jìn)行排序）。直接執(zhí)行b=2這種查詢條件無(wú)法使用索引。
當(dāng)a的值確定的時(shí)候，b是有序的。例如a=1時(shí)，b值為1，2是有序的狀態(tài)。當(dāng)a=2時(shí)候，b的值為1，4也是有序狀態(tài)。 當(dāng)執(zhí)行a=1 and b=2時(shí)a和b字段能用到索引。而執(zhí)行a > 1 and b=2時(shí)，a字段能用到索引，b字段用不到索引。因?yàn)閍的值此時(shí)是一個(gè)范圍，不是固定的，在這個(gè)范圍內(nèi)b值不是有序的，因此b字段無(wú)法使用索引。
InnoDB使用表的主鍵構(gòu)造主鍵索引樹(shù)，同時(shí)葉子節(jié)點(diǎn)中存放的即為整張表的記錄數(shù)據(jù)。聚集索引葉子節(jié)點(diǎn)的存儲(chǔ)是邏輯上連續(xù)的，使用雙向鏈表連接，葉子節(jié)點(diǎn)按照主鍵的順序排序，因此對(duì)于主鍵的排序查找和范圍查找速度比較快。
聚集索引的葉子節(jié)點(diǎn)就是整張表的行記錄。InnoDB 主鍵使用的是聚簇索引。聚集索引要比非聚集索引查詢效率高很多。
對(duì)于InnoDB來(lái)說(shuō)，聚集索引一般是表中的主鍵索引，如果表中沒(méi)有顯示指定主鍵，則會(huì)選擇表中的第一個(gè)不允許為NULL的唯一索引。如果沒(méi)有主鍵也沒(méi)有合適的唯一索引，那么InnoDB內(nèi)部會(huì)生成一個(gè)隱藏的主鍵作為聚集索引，這個(gè)隱藏的主鍵長(zhǎng)度為6個(gè)字節(jié)，它的值會(huì)隨著數(shù)據(jù)的插入自增。
select的數(shù)據(jù)列只用從索引中就能夠取得，不需要回表進(jìn)行二次查詢，也就是說(shuō)查詢列要被所使用的索引覆蓋。對(duì)于innodb表的二級(jí)索引，如果索引能覆蓋到查詢的列，那么就可以避免對(duì)主鍵索引的二次查詢。
不是所有類型的索引都可以成為覆蓋索引。覆蓋索引要存儲(chǔ)索引列的值，而哈希索引、全文索引不存儲(chǔ)索引列的值，所以MySQL使用b+樹(shù)索引做覆蓋索引。
對(duì)于使用了覆蓋索引的查詢，在查詢前面使用explain，輸出的extra列會(huì)顯示為using index。
比如user_like 用戶點(diǎn)贊表，組合索引為(user_id, blog_id)，user_id和blog_id都不為null。
explain select blog_id from user_like where user_id=13;
explain結(jié)果的Extra列為Using index，查詢的列被索引覆蓋，并且where篩選條件符合最左前綴原則，通過(guò)索引查找就能直接找到符合條件的數(shù)據(jù)，不需要回表查詢數(shù)據(jù)。
explain select user_id from user_like where blog_id=1;
explain結(jié)果的Extra列為Using where; Using index， 查詢的列被索引覆蓋，where篩選條件不符合最左前綴原則，無(wú)法通過(guò)索引查找找到符合條件的數(shù)據(jù)，但可以通過(guò)索引掃描找到符合條件的數(shù)據(jù)，也不需要回表查詢數(shù)據(jù)。
導(dǎo)致索引失效的情況：
有時(shí)需要在很長(zhǎng)的字符列上創(chuàng)建索引，這會(huì)造成索引特別大且慢。使用前綴索引可以避免這個(gè)問(wèn)題。
前綴索引是指對(duì)文本或者字符串的前幾個(gè)字符建立索引，這樣索引的長(zhǎng)度更短，查詢速度更快。
創(chuàng)建前綴索引的關(guān)鍵在于選擇足夠長(zhǎng)的前綴以保證較高的索引選擇性。索引選擇性越高查詢效率就越高，因?yàn)檫x擇性高的索引可以讓MySQL在查找時(shí)過(guò)濾掉更多的數(shù)據(jù)行。
建立前綴索引的方式：
// email列創(chuàng)建前綴索引
ALTER TABLE table_name ADD KEY(column_name(prefix_length));
參考我的另一篇文章：圖解索引下推！
MySQL中常用的四種存儲(chǔ)引擎分別是： MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL 5.5版本后默認(rèn)的存儲(chǔ)引擎為InnoDB。
InnoDB存儲(chǔ)引擎
InnoDB是MySQL默認(rèn)的事務(wù)型存儲(chǔ)引擎，使用最廣泛，基于聚簇索引建立的。InnoDB內(nèi)部做了很多優(yōu)化，如能夠自動(dòng)在內(nèi)存中創(chuàng)建自適應(yīng)hash索引，以加速讀操作。
優(yōu)點(diǎn)：支持事務(wù)和崩潰修復(fù)能力；引入了行級(jí)鎖和外鍵約束。
缺點(diǎn)：占用的數(shù)據(jù)空間相對(duì)較大。
適用場(chǎng)景：需要事務(wù)支持，并且有較高的并發(fā)讀寫頻率。
MyISAM存儲(chǔ)引擎
數(shù)據(jù)以緊密格式存儲(chǔ)。對(duì)于只讀數(shù)據(jù)，或者表比較小、可以容忍修復(fù)操作，可以使用MyISAM引擎。MyISAM會(huì)將表存儲(chǔ)在兩個(gè)文件中，數(shù)據(jù)文件.MYD和索引文件.MYI。
優(yōu)點(diǎn)：訪問(wèn)速度快。
缺點(diǎn)：MyISAM不支持事務(wù)和行級(jí)鎖，不支持崩潰后的安全恢復(fù)，也不支持外鍵。
適用場(chǎng)景：對(duì)事務(wù)完整性沒(méi)有要求；表的數(shù)據(jù)都會(huì)只讀的。
MEMORY存儲(chǔ)引擎
MEMORY引擎將數(shù)據(jù)全部放在內(nèi)存中，訪問(wèn)速度較快，但是一旦系統(tǒng)奔潰的話，數(shù)據(jù)都會(huì)丟失。
MEMORY引擎默認(rèn)使用哈希索引，將鍵的哈希值和指向數(shù)據(jù)行的指針保存在哈希索引中。
優(yōu)點(diǎn)：訪問(wèn)速度較快。
缺點(diǎn)：
ARCHIVE存儲(chǔ)引擎
ARCHIVE存儲(chǔ)引擎非常適合存儲(chǔ)大量獨(dú)立的、作為歷史記錄的數(shù)據(jù)。ARCHIVE提供了壓縮功能，擁有高效的插入速度，但是這種引擎不支持索引，所以查詢性能較差。
按鎖粒度分類，有行級(jí)鎖、表級(jí)鎖和頁(yè)級(jí)鎖。
按鎖級(jí)別分類，有共享鎖、排他鎖和意向鎖。
意向共享鎖（IS）：表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入共享鎖，也就是說(shuō)一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖；
意向排他鎖（IX）：類似上面，表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入排他鎖，說(shuō)明事務(wù)在一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。
意向鎖是 InnoDB 自動(dòng)加的，不需要用戶干預(yù)。
對(duì)于INSERT、UPDATE和DELETE，InnoDB 會(huì)自動(dòng)給涉及的數(shù)據(jù)加排他鎖；對(duì)于一般的SELECT語(yǔ)句，InnoDB 不會(huì)加任何鎖，事務(wù)可以通過(guò)以下語(yǔ)句顯式加共享鎖或排他鎖。
共享鎖：SELECT … LOCK IN SHARE MODE;
排他鎖：SELECT … FOR UPDATE;
MVCC(Multiversion concurrency control) 就是同一份數(shù)據(jù)保留多版本的一種方式，進(jìn)而實(shí)現(xiàn)并發(fā)控制。在查詢的時(shí)候，通過(guò)read view和版本鏈找到對(duì)應(yīng)版本的數(shù)據(jù)。
作用：提升并發(fā)性能。對(duì)于高并發(fā)場(chǎng)景，MVCC比行級(jí)鎖開(kāi)銷更小。
MVCC 實(shí)現(xiàn)原理如下：
MVCC 的實(shí)現(xiàn)依賴于版本鏈，版本鏈?zhǔn)峭ㄟ^(guò)表的三個(gè)隱藏字段實(shí)現(xiàn)。
每條表記錄大概是這樣的：
使用事務(wù)更新行記錄的時(shí)候，就會(huì)生成版本鏈，執(zhí)行過(guò)程如下：
下面舉個(gè)例子方便大家理解。
1、初始數(shù)據(jù)如下，其中DB_ROW_ID和DB_ROLL_PTR為空。
2、事務(wù)A對(duì)該行數(shù)據(jù)做了修改，將age修改為12，效果如下：
3、之后事務(wù)B也對(duì)該行記錄做了修改，將age修改為8，效果如下：
4、此時(shí)undo log有兩行記錄，并且通過(guò)回滾指針連在一起。
接下來(lái)了解下read view的概念。
read view可以理解成將數(shù)據(jù)在每個(gè)時(shí)刻的狀態(tài)拍成“照片”記錄下來(lái)。在獲取某時(shí)刻t的數(shù)據(jù)時(shí)，到t時(shí)間點(diǎn)拍的“照片”上取數(shù)據(jù)。
在read view內(nèi)部維護(hù)一個(gè)活躍事務(wù)鏈表，表示生成read view的時(shí)候還在活躍的事務(wù)。這個(gè)鏈表包含在創(chuàng)建read view之前還未提交的事務(wù)，不包含創(chuàng)建read view之后提交的事務(wù)。
不同隔離級(jí)別創(chuàng)建read view的時(shí)機(jī)不同。
read view的記錄篩選方式
前提：DATA_TRX_ID 表示每個(gè)數(shù)據(jù)行的最新的事務(wù)ID；up_limit_id表示當(dāng)前快照中的最先開(kāi)始的事務(wù)；low_limit_id表示當(dāng)前快照中的最慢開(kāi)始的事務(wù)，即最后一個(gè)事務(wù)。
總結(jié)：InnoDB 的MVCC是通過(guò) read view 和版本鏈實(shí)現(xiàn)的，版本鏈保存有歷史版本記錄，通過(guò)read view 判斷當(dāng)前版本的數(shù)據(jù)是否可見(jiàn)，如果不可見(jiàn)，再?gòu)陌姹炬溨姓业缴弦粋€(gè)版本，繼續(xù)進(jìn)行判斷，直到找到一個(gè)可見(jiàn)的版本。
表記錄有兩種讀取方式。
快照讀情況下，InnoDB通過(guò)mvcc機(jī)制避免了幻讀現(xiàn)象。而mvcc機(jī)制無(wú)法避免當(dāng)前讀情況下出現(xiàn)的幻讀現(xiàn)象。因?yàn)楫?dāng)前讀每次讀取的都是最新數(shù)據(jù)，這時(shí)如果兩次查詢中間有其它事務(wù)插入數(shù)據(jù)，就會(huì)產(chǎn)生幻讀。
下面舉個(gè)例子說(shuō)明下：
1、首先，user表只有兩條記錄，具體如下：
2、事務(wù)a和事務(wù)b同時(shí)開(kāi)啟事務(wù)start transaction；
3、事務(wù)a插入數(shù)據(jù)然后提交；
insert into user(user_name, user_password, user_mail, user_state) values('tyson', 'a', 'a', 0);
4、事務(wù)b執(zhí)行全表的update；
update user set user_name='a';
5、事務(wù)b然后執(zhí)行查詢，查到了事務(wù)a中插入的數(shù)據(jù)。（下圖左邊是事務(wù)b，右邊是事務(wù)a。事務(wù)開(kāi)始之前只有兩條記錄，事務(wù)a插入一條數(shù)據(jù)之后，事務(wù)b查詢出來(lái)是三條數(shù)據(jù)）
以上就是當(dāng)前讀出現(xiàn)的幻讀現(xiàn)象。
那么MySQL是如何避免幻讀？
next-key包括兩部分：行鎖和間隙鎖。行鎖是加在索引上的鎖，間隙鎖是加在索引之間的。
Serializable隔離級(jí)別也可以避免幻讀，會(huì)鎖住整張表，并發(fā)性極低，一般不會(huì)使用。
SELECT 的讀取鎖定主要分為兩種方式：共享鎖和排他鎖。
select * from table where id<6 lock in share mode;--共享鎖
select * from table where id<6 for update;--排他鎖
這兩種方式主要的不同在于LOCK IN SHARE MODE多個(gè)事務(wù)同時(shí)更新同一個(gè)表單時(shí)很容易造成死鎖。
申請(qǐng)排他鎖的前提是，沒(méi)有線程對(duì)該結(jié)果集的任何行數(shù)據(jù)使用排它鎖或者共享鎖，否則申請(qǐng)會(huì)受到阻塞。在進(jìn)行事務(wù)操作時(shí)，MySQL會(huì)對(duì)查詢結(jié)果集的每行數(shù)據(jù)添加排它鎖，其他線程對(duì)這些數(shù)據(jù)的更改或刪除操作會(huì)被阻塞（只能讀操作），直到該語(yǔ)句的事務(wù)被commit語(yǔ)句或rollback語(yǔ)句結(jié)束為止。
SELECT... FOR UPDATE 使用注意事項(xiàng)：
MySQL日志主要包括查詢?nèi)罩尽⒙樵內(nèi)罩尽⑹聞?wù)日志、錯(cuò)誤日志、二進(jìn)制日志等。其中比較重要的是 bin log（二進(jìn)制日志）和 redo log（重做日志）和 undo log（回滾日志）。
bin log
bin log是MySQL數(shù)據(jù)庫(kù)級(jí)別的文件，記錄對(duì)MySQL數(shù)據(jù)庫(kù)執(zhí)行修改的所有操作，不會(huì)記錄select和show語(yǔ)句，主要用于恢復(fù)數(shù)據(jù)庫(kù)和同步數(shù)據(jù)庫(kù)。
redo log
redo log是innodb引擎級(jí)別，用來(lái)記錄innodb存儲(chǔ)引擎的事務(wù)日志，不管事務(wù)是否提交都會(huì)記錄下來(lái)，用于數(shù)據(jù)恢復(fù)。當(dāng)數(shù)據(jù)庫(kù)發(fā)生故障，innoDB存儲(chǔ)引擎會(huì)使用redo log恢復(fù)到發(fā)生故障前的時(shí)刻，以此來(lái)保證數(shù)據(jù)的完整性。將參數(shù)innodb_flush_log_at_tx_commit設(shè)置為1，那么在執(zhí)行commit時(shí)會(huì)將redo log同步寫到磁盤。
undo log
除了記錄redo log外，當(dāng)進(jìn)行數(shù)據(jù)修改時(shí)還會(huì)記錄undo log，undo log用于數(shù)據(jù)的撤回操作，它保留了記錄修改前的內(nèi)容。通過(guò)undo log可以實(shí)現(xiàn)事務(wù)回滾，并且可以根據(jù)undo log回溯到某個(gè)特定的版本的數(shù)據(jù)，實(shí)現(xiàn)MVCC。
MySQL主要分為 Server 層和存儲(chǔ)引擎層：
Server 層基本組件
當(dāng)單表的數(shù)據(jù)量達(dá)到1000W或100G以后，優(yōu)化索引、添加從庫(kù)等可能對(duì)數(shù)據(jù)庫(kù)性能提升效果不明顯，此時(shí)就要考慮對(duì)其進(jìn)行切分了。切分的目的就在于減少數(shù)據(jù)庫(kù)的負(fù)擔(dān)，縮短查詢的時(shí)間。
數(shù)據(jù)切分可以分為兩種方式：垂直劃分和水平劃分。
垂直劃分
垂直劃分?jǐn)?shù)據(jù)庫(kù)是根據(jù)業(yè)務(wù)進(jìn)行劃分，例如購(gòu)物場(chǎng)景，可以將庫(kù)中涉及商品、訂單、用戶的表分別劃分出成一個(gè)庫(kù)，通過(guò)降低單庫(kù)的大小來(lái)提高性能。同樣的，分表的情況就是將一個(gè)大表根據(jù)業(yè)務(wù)功能拆分成一個(gè)個(gè)子表，例如商品基本信息和商品描述，商品基本信息一般會(huì)展示在商品列表，商品描述在商品詳情頁(yè)，可以將商品基本信息和商品描述拆分成兩張表。
優(yōu)點(diǎn)：行記錄變小，數(shù)據(jù)頁(yè)可以存放更多記錄，在查詢時(shí)減少I/O次數(shù)。
缺點(diǎn)：
水平劃分
水平劃分是根據(jù)一定規(guī)則，例如時(shí)間或id序列值等進(jìn)行數(shù)據(jù)的拆分。比如根據(jù)年份來(lái)拆分不同的數(shù)據(jù)庫(kù)。每個(gè)數(shù)據(jù)庫(kù)結(jié)構(gòu)一致，但是數(shù)據(jù)得以拆分，從而提升性能。
優(yōu)點(diǎn)：?jiǎn)螏?kù)（表）的數(shù)據(jù)量得以減少，提高性能；切分出的表結(jié)構(gòu)相同，程序改動(dòng)較少。
缺點(diǎn)：
分區(qū)是把一張表的數(shù)據(jù)分成N多個(gè)區(qū)塊。分區(qū)表是一個(gè)獨(dú)立的邏輯表，但是底層由多個(gè)物理子表組成。
當(dāng)查詢條件的數(shù)據(jù)分布在某一個(gè)分區(qū)的時(shí)候，查詢引擎只會(huì)去某一個(gè)分區(qū)查詢，而不是遍歷整個(gè)表。在管理層面，如果需要?jiǎng)h除某一個(gè)分區(qū)的數(shù)據(jù)，只需要?jiǎng)h除對(duì)應(yīng)的分區(qū)即可。
分區(qū)一般都是放在單機(jī)里的，用的比較多的是時(shí)間范圍分區(qū)，方便歸檔。只不過(guò)分庫(kù)分表需要代碼實(shí)現(xiàn)，分區(qū)則是mysql內(nèi)部實(shí)現(xiàn)。分庫(kù)分表和分區(qū)并不沖突，可以結(jié)合使用。
range分區(qū)，按照范圍分區(qū)。比如按照時(shí)間范圍分區(qū)
CREATE TABLE test_range_partition(
id INT auto_increment,
createdate DATETIME,
primary key (id,createdate)
)
PARTITION BY RANGE (TO_DAYS(createdate) ) (
PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
);
在/var/lib/mysql/data/可以找到對(duì)應(yīng)的數(shù)據(jù)文件，每個(gè)分區(qū)表都有一個(gè)使用#分隔命名的表文件：
-rw-r----- 1 MySQL MySQL 65 Mar 14 21:47 db.opt
-rw-r----- 1 MySQL MySQL 8598 Mar 14 21:50 test_range_partition.frm
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201801.ibd
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201802.ibd
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201803.ibd
...
list分區(qū)
list分區(qū)和range分區(qū)相似，主要區(qū)別在于list是枚舉值列表的集合，range是連續(xù)的區(qū)間值的集合。對(duì)于list分區(qū)，分區(qū)字段必須是已知的，如果插入的字段不在分區(qū)時(shí)的枚舉值中，將無(wú)法插入。
create table test_list_partiotion
(
id int auto_increment,
data_type tinyint,
primary key(id,data_type)
)partition by list(data_type)
(
partition p0 values in (0,1,2,3,4,5,6),
partition p1 values in (7,8,9,10,11,12),
partition p2 values in (13,14,15,16,17)
);
hash分區(qū)
可以將數(shù)據(jù)均勻地分布到預(yù)先定義的分區(qū)中。
create table test_hash_partiotion
(
id int auto_increment,
create_date datetime,
primary key(id,create_date)
)partition by hash(year(create_date)) partitions 10;
查詢語(yǔ)句的執(zhí)行流程如下：權(quán)限校驗(yàn)、查詢緩存、分析器、優(yōu)化器、權(quán)限校驗(yàn)、執(zhí)行器、引擎。
舉個(gè)例子，查詢語(yǔ)句如下：
select * from user where id > 1 and name='大彬';
更新語(yǔ)句執(zhí)行流程如下：分析器、權(quán)限校驗(yàn)、執(zhí)行器、引擎、redo log（prepare狀態(tài)）、binlog、redo log（commit狀態(tài)）
舉個(gè)例子，更新語(yǔ)句如下：
update user set name='大彬' where id=1;
為什么記錄完redo log，不直接提交，而是先進(jìn)入prepare狀態(tài)？
假設(shè)先寫redo log直接提交，然后寫binlog，寫完redo log后，機(jī)器掛了，binlog日志沒(méi)有被寫入，那么機(jī)器重啟后，這臺(tái)機(jī)器會(huì)通過(guò)redo log恢復(fù)數(shù)據(jù)，但是這個(gè)時(shí)候binlog并沒(méi)有記錄該數(shù)據(jù)，后續(xù)進(jìn)行機(jī)器備份的時(shí)候，就會(huì)丟失這一條數(shù)據(jù)，同時(shí)主從同步也會(huì)丟失這一條數(shù)據(jù)。
exists用于對(duì)外表記錄做篩選。exists會(huì)遍歷外表，將外查詢表的每一行，代入內(nèi)查詢進(jìn)行判斷。當(dāng)exists里的條件語(yǔ)句能夠返回記錄行時(shí)，條件就為真，返回外表當(dāng)前記錄。反之如果exists里的條件語(yǔ)句不能返回記錄行，條件為假，則外表當(dāng)前記錄被丟棄。
select a.* from A awhere exists(select 1 from B b where a.id=b.id)
in是先把后邊的語(yǔ)句查出來(lái)放到臨時(shí)表中，然后遍歷臨時(shí)表，將臨時(shí)表的每一行，代入外查詢?nèi)ゲ檎摇?/p>
select * from Awhere id in(select id from B)
子查詢的表比較大的時(shí)候，使用exists可以有效減少總的循環(huán)次數(shù)來(lái)提升速度；當(dāng)外查詢的表比較大的時(shí)候，使用in可以有效減少對(duì)外查詢表循環(huán)遍歷來(lái)提升速度。
相同點(diǎn)：
不同點(diǎn)：
int(10)中的10表示的是顯示數(shù)據(jù)的長(zhǎng)度，而char(10)表示的是存儲(chǔ)數(shù)據(jù)的長(zhǎng)度。
主從同步使得數(shù)據(jù)可以從一個(gè)數(shù)據(jù)庫(kù)服務(wù)器復(fù)制到其他服務(wù)器上，在復(fù)制數(shù)據(jù)時(shí)，一個(gè)服務(wù)器充當(dāng)主服務(wù)器（master），其余的服務(wù)器充當(dāng)從服務(wù)器（slave）。
因?yàn)閺?fù)制是異步進(jìn)行的，所以從服務(wù)器不需要一直連接著主服務(wù)器，從服務(wù)器甚至可以通過(guò)撥號(hào)斷斷續(xù)續(xù)地連接主服務(wù)器。通過(guò)配置文件，可以指定復(fù)制所有的數(shù)據(jù)庫(kù)，某個(gè)數(shù)據(jù)庫(kù)，甚至是某個(gè)數(shù)據(jù)庫(kù)上的某個(gè)表。
數(shù)據(jù)庫(kù)中的并發(fā)控制是確保在多個(gè)事務(wù)同時(shí)存取數(shù)據(jù)庫(kù)中同一數(shù)據(jù)時(shí)不破壞事務(wù)的隔離性和統(tǒng)一性以及數(shù)據(jù)庫(kù)的統(tǒng)一性。樂(lè)觀鎖和悲觀鎖是并發(fā)控制主要采用的技術(shù)手段。
show processlist 或 show full processlist 可以查看當(dāng)前 MySQL 是否有壓力，正在運(yùn)行的SQL，有沒(méi)有慢SQL正在執(zhí)行。返回參數(shù)如下：
兩種查詢方式。對(duì)應(yīng) limit offset, size 和 limit size 兩種方式。
而其實(shí) limit size ，相當(dāng)于 limit 0, size。也就是從0開(kāi)始取size條數(shù)據(jù)。
也就是說(shuō)，兩種方式的區(qū)別在于offset是否為0。
先來(lái)看下limit sql的內(nèi)部執(zhí)行邏輯。
MySQL內(nèi)部分為server層和存儲(chǔ)引擎層。一般情況下存儲(chǔ)引擎都用innodb。
server層有很多模塊，其中需要關(guān)注的是執(zhí)行器是用于跟存儲(chǔ)引擎打交道的組件。
執(zhí)行器可以通過(guò)調(diào)用存儲(chǔ)引擎提供的接口，將一行行數(shù)據(jù)取出，當(dāng)這些數(shù)據(jù)完全符合要求（比如滿足其他where條件），則會(huì)放到結(jié)果集中，最后返回給調(diào)用mysql的客戶端。
以主鍵索引的limit執(zhí)行過(guò)程為例：
執(zhí)行select * from xxx order by id limit 0, 10;，select后面帶的是星號(hào)，也就是要求獲得行數(shù)據(jù)的所有字段信息。
server層會(huì)調(diào)用innodb的接口，在innodb里的主鍵索引中獲取到第0到10條完整行數(shù)據(jù)，依次返回給server層，并放到server層的結(jié)果集中，返回給客戶端。
把offset搞大點(diǎn)，比如執(zhí)行的是：select * from xxx order by id limit 500000, 10;
server層會(huì)調(diào)用innodb的接口，由于這次的offset=500000，會(huì)在innodb里的主鍵索引中獲取到第0到（500000 + 10）條完整行數(shù)據(jù)，返回給server層之后根據(jù)offset的值挨個(gè)拋棄，最后只留下最后面的size條，也就是10條數(shù)據(jù)，放到server層的結(jié)果集中，返回給客戶端。
可以看出，當(dāng)offset非0時(shí)，server層會(huì)從引擎層獲取到很多無(wú)用的數(shù)據(jù)，而獲取的這些無(wú)用數(shù)據(jù)都是要耗時(shí)的。
因此，mysql查詢中 limit 1000,10 會(huì)比 limit 10 更慢。原因是 limit 1000,10 會(huì)取出1000+10條數(shù)據(jù)，并拋棄前1000條，這部分耗時(shí)更大。
還是以上面的SQL為空：select * from xxx order by id limit 500000, 10;
方法一：
從上面的分析可以看出，當(dāng)offset非常大時(shí)，server層會(huì)從引擎層獲取到很多無(wú)用的數(shù)據(jù)，而當(dāng)select后面是*號(hào)時(shí)，就需要拷貝完整的行信息，拷貝完整數(shù)據(jù)相比只拷貝行數(shù)據(jù)里的其中一兩個(gè)列字段更耗費(fèi)時(shí)間。
因?yàn)榍懊娴膐ffset條數(shù)據(jù)最后都是不要的，沒(méi)有必要拷貝完整字段，所以可以將sql語(yǔ)句修改成：
select * from xxx where id >=(select id from xxx order by id limit 500000, 1) order by id limit 10;
先執(zhí)行子查詢 select id from xxx by id limit 500000, 1, 這個(gè)操作，其實(shí)也是將在innodb中的主鍵索引中獲取到500000+1條數(shù)據(jù)，然后server層會(huì)拋棄前500000條，只保留最后一條數(shù)據(jù)的id。
但不同的地方在于，在返回server層的過(guò)程中，只會(huì)拷貝數(shù)據(jù)行內(nèi)的id這一列，而不會(huì)拷貝數(shù)據(jù)行的所有列，當(dāng)數(shù)據(jù)量較大時(shí)，這部分的耗時(shí)還是比較明顯的。
在拿到了上面的id之后，假設(shè)這個(gè)id正好等于500000，那sql就變成了
select * from xxx where id >=500000 order by id limit 10;
這樣innodb再走一次主鍵索引，通過(guò)B+樹(shù)快速定位到id=500000的行數(shù)據(jù)，時(shí)間復(fù)雜度是lg(n)，然后向后取10條數(shù)據(jù)。
方法二：
將所有的數(shù)據(jù)根據(jù)id主鍵進(jìn)行排序，然后分批次取，將當(dāng)前批次的最大id作為下次篩選的條件進(jìn)行查詢。
select * from xxx where id > start_id order by id limit 10;
mysql
通過(guò)主鍵索引，每次定位到start_id的位置，然后往后遍歷10個(gè)數(shù)據(jù)，這樣不管數(shù)據(jù)多大，查詢性能都較為穩(wěn)定。
InnoDB存儲(chǔ)引擎有自己的最小儲(chǔ)存單元——頁(yè)（Page）。
查詢InnoDB頁(yè)大小的命令如下：
mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
可以看出 innodb 默認(rèn)的一頁(yè)大小為 16384B=16384/1024=16kb。
在MySQL中，B+樹(shù)一個(gè)節(jié)點(diǎn)的大小設(shè)為一頁(yè)或頁(yè)的倍數(shù)最為合適。因?yàn)槿绻粋€(gè)節(jié)點(diǎn)的大小 < 1頁(yè)，那么讀取這個(gè)節(jié)點(diǎn)的時(shí)候其實(shí)讀取的還是一頁(yè)，這樣就造成了資源的浪費(fèi)。
B+樹(shù)中非葉子節(jié)點(diǎn)存的是key + 指針；葉子節(jié)點(diǎn)存的是數(shù)據(jù)行。
對(duì)于葉子節(jié)點(diǎn)，如果一行數(shù)據(jù)大小為1k，那么一頁(yè)就能存16條數(shù)據(jù)。
對(duì)于非葉子節(jié)點(diǎn)，如果key使用的是bigint，則為8字節(jié)，指針在MySQL中為6字節(jié)，一共是14字節(jié)，則16k能存放 16 * 1024 / 14=1170 個(gè)索引指針。
于是可以算出，對(duì)于一顆高度為2的B+樹(shù)，根節(jié)點(diǎn)存儲(chǔ)索引指針節(jié)點(diǎn)，那么它有1170個(gè)葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)，每個(gè)葉子節(jié)點(diǎn)可以存儲(chǔ)16條數(shù)據(jù)，一共 1170 x 16=18720 條數(shù)據(jù)。而對(duì)于高度為3的B+樹(shù)，就可以存放 1170 x 1170 x 16=21902400 條數(shù)據(jù)（兩千多萬(wàn)條數(shù)據(jù)），也就是對(duì)于兩千多萬(wàn)條的數(shù)據(jù)，我們只需要高度為3的B+樹(shù)就可以完成，通過(guò)主鍵查詢只需要3次IO操作就能查到對(duì)應(yīng)數(shù)據(jù)。
所以在 InnoDB 中B+樹(shù)高度一般為3層時(shí)，就能滿足千萬(wàn)級(jí)的數(shù)據(jù)存儲(chǔ)。
參考：https://www.cnblogs.com/leefreeman/p/8315844.html
目前主流的有兩種說(shuō)法：
事實(shí)上，這個(gè)數(shù)值和實(shí)際記錄的條數(shù)無(wú)關(guān)，而與 MySQL 的配置以及機(jī)器的硬件有關(guān)。因?yàn)镸ySQL為了提高性能，會(huì)將表的索引裝載到內(nèi)存中。在InnoDB buffer size 足夠的情況下，其能完成全加載進(jìn)內(nèi)存，查詢不會(huì)有問(wèn)題。但是，當(dāng)單表數(shù)據(jù)庫(kù)到達(dá)某個(gè)量級(jí)的上限時(shí)，導(dǎo)致內(nèi)存無(wú)法存儲(chǔ)其索引，使得之后的 SQL 查詢會(huì)產(chǎn)生磁盤 IO，從而導(dǎo)致性能下降。當(dāng)然，這個(gè)還有具體的表結(jié)構(gòu)的設(shè)計(jì)有關(guān)，最終導(dǎo)致的問(wèn)題都是內(nèi)存限制。
因此，對(duì)于分庫(kù)分表，需要結(jié)合實(shí)際需求，不宜過(guò)度設(shè)計(jì)，在項(xiàng)目一開(kāi)始不采用分庫(kù)與分表設(shè)計(jì)，而是隨著業(yè)務(wù)的增長(zhǎng)，在無(wú)法繼續(xù)優(yōu)化的情況下，再考慮分庫(kù)與分表提高系統(tǒng)的性能。對(duì)此，阿里巴巴《Java 開(kāi)發(fā)手冊(cè)》補(bǔ)充到：如果預(yù)計(jì)三年后的數(shù)據(jù)量根本達(dá)不到這個(gè)級(jí)別，請(qǐng)不要在創(chuàng)建表時(shí)就分庫(kù)分表。
至于MySQL單表多大進(jìn)行分庫(kù)分表，應(yīng)當(dāng)根據(jù)機(jī)器資源進(jìn)行評(píng)估。
某個(gè)表有近千萬(wàn)數(shù)據(jù)，查詢比較慢，如何優(yōu)化？
當(dāng)MySQL單表記錄數(shù)過(guò)大時(shí)，數(shù)據(jù)庫(kù)的性能會(huì)明顯下降，一些常見(jiàn)的優(yōu)化措施如下：
嗯，先說(shuō)說(shuō)count(1) and count(字段名)的區(qū)別。
兩者的主要區(qū)別是
接下來(lái)看看三者之間的區(qū)別。
執(zhí)行效果上：
執(zhí)行效率上：
嗯，TIMESTAMP和DATETIME都可以用來(lái)存儲(chǔ)時(shí)間，它們主要有以下區(qū)別：
1.表示范圍
TIMESTAMP支持的時(shí)間范圍比DATATIME要小，容易出現(xiàn)超出的情況。
2.空間占用
3.存入時(shí)間是否會(huì)自動(dòng)轉(zhuǎn)換
TIMESTAMP類型在默認(rèn)情況下，insert、update 數(shù)據(jù)時(shí)，TIMESTAMP列會(huì)自動(dòng)以當(dāng)前時(shí)間（CURRENT_TIMESTAMP）填充/更新。DATETIME則不會(huì)做任何轉(zhuǎn)換，也不會(huì)檢測(cè)時(shí)區(qū)，你給什么數(shù)據(jù)，它存什么數(shù)據(jù)。
4.TIMESTAMP比較受時(shí)區(qū)timezone的影響以及MYSQL版本和服務(wù)器的SQL MODE的影響。因?yàn)?span style="color: #E83E8C; --tt-darkmode-color: #E83E8C;">TIMESTAMP存的是時(shí)間戳，在不同的時(shí)區(qū)得出的時(shí)間不一致。
5.如果存進(jìn)NULL，兩者實(shí)際存儲(chǔ)的值不同。
外鍵是一種約束，這個(gè)約束的存在，會(huì)保證表間數(shù)據(jù)的關(guān)系始終完整。外鍵的存在，并非全然沒(méi)有優(yōu)點(diǎn)。
外鍵可以保證數(shù)據(jù)的完整性和一致性，級(jí)聯(lián)操作方便。而且使用外鍵可以將數(shù)據(jù)完整性判斷托付給了數(shù)據(jù)庫(kù)完成，減少了程序的代碼量。
雖然外鍵能夠保證數(shù)據(jù)的完整性，但是會(huì)給系統(tǒng)帶來(lái)很多缺陷。
1、并發(fā)問(wèn)題。在使用外鍵的情況下，每次修改數(shù)據(jù)都需要去另外一個(gè)表檢查數(shù)據(jù)，需要獲取額外的鎖。若是在高并發(fā)大流量事務(wù)場(chǎng)景，使用外鍵更容易造成死鎖。
2、擴(kuò)展性問(wèn)題。比如從MySQL遷移到Oracle，外鍵依賴于數(shù)據(jù)庫(kù)本身的特性，做遷移可能不方便。
3、不利于分庫(kù)分表。在水平拆分和分庫(kù)的情況下，外鍵是無(wú)法生效的。將數(shù)據(jù)間關(guān)系的維護(hù)，放入應(yīng)用程序中，為將來(lái)的分庫(kù)分表省去很多的麻煩。
自增主鍵可以讓主鍵索引盡量地保持遞增順序插入，避免了頁(yè)分裂，因此索引更緊湊，在查詢的時(shí)候，效率也就更高。
主要為了提升插入數(shù)據(jù)的效率和并行度。
假設(shè)有兩個(gè)并行執(zhí)行的事務(wù)，在申請(qǐng)自增值的時(shí)候，為了避免兩個(gè)事務(wù)申請(qǐng)到相同的自增 id，肯定要加鎖，然后順序申請(qǐng)。
假設(shè)事務(wù) A 申請(qǐng)到了 id=2， 事務(wù) B 申請(qǐng)到 id=3，那么這時(shí)候表 t 的自增值是 4，之后繼續(xù)執(zhí)行。
事務(wù) B 正確提交了，但事務(wù) A 出現(xiàn)了唯一鍵沖突。
如果允許事務(wù) A 把自增 id 回退，也就是把表 t 的當(dāng)前自增值改回 2，那么就會(huì)出現(xiàn)這樣的情況：表里面已經(jīng)有 id=3 的行，而當(dāng)前的自增 id 值是 2。
接下來(lái)，繼續(xù)執(zhí)行的其他事務(wù)就會(huì)申請(qǐng)到 id=2，然后再申請(qǐng)到 id=3。這時(shí)，就會(huì)出現(xiàn)插入語(yǔ)句報(bào)錯(cuò)“主鍵沖突”。
而為了解決這個(gè)主鍵沖突，有兩種方法：
可見(jiàn)，這兩個(gè)方法都會(huì)導(dǎo)致性能問(wèn)題。
因此，InnoDB 放棄了“允許自增 id 回退”這個(gè)設(shè)計(jì)，語(yǔ)句執(zhí)行失敗也不回退自增 id。
不同的引擎對(duì)于自增值的保存策略不同：
不一定，有幾種情況會(huì)導(dǎo)致自增主鍵不連續(xù)。
1、唯一鍵沖突導(dǎo)致自增主鍵不連續(xù)。當(dāng)我們向一個(gè)自增主鍵的InnoDB表中插入數(shù)據(jù)的時(shí)候，如果違反表中定義的唯一索引的唯一約束，會(huì)導(dǎo)致插入數(shù)據(jù)失敗。此時(shí)表的自增主鍵的鍵值是會(huì)向后加1滾動(dòng)的。下次再次插入數(shù)據(jù)的時(shí)候，就不能再使用上次因插入數(shù)據(jù)失敗而滾動(dòng)生成的鍵值了，必須使用新滾動(dòng)生成的鍵值。
2、事務(wù)回滾導(dǎo)致自增主鍵不連續(xù)。當(dāng)我們向一個(gè)自增主鍵的InnoDB表中插入數(shù)據(jù)的時(shí)候，如果顯式開(kāi)啟了事務(wù)，然后因?yàn)槟撤N原因最后回滾了事務(wù)，此時(shí)表的自增值也會(huì)發(fā)生滾動(dòng)，而接下里新插入的數(shù)據(jù)，也將不能使用滾動(dòng)過(guò)的自增值，而是需要重新申請(qǐng)一個(gè)新的自增值。
3、批量插入導(dǎo)致自增值不連續(xù)。MySQL有一個(gè)批量申請(qǐng)自增id的策略：
如果下一個(gè)事務(wù)再次插入數(shù)據(jù)的時(shí)候，則會(huì)基于上一個(gè)事務(wù)申請(qǐng)后的自增值基礎(chǔ)上再申請(qǐng)。此時(shí)就出現(xiàn)自增值不連續(xù)的情況出現(xiàn)。
4、自增步長(zhǎng)不是1，也會(huì)導(dǎo)致自增主鍵不連續(xù)。
參考：https://cloud.tencent.com/developer/article/1805755
有兩種方案：
1、通過(guò)MySQL自動(dòng)同步刷新Redis，MySQL觸發(fā)器+UDF函數(shù)實(shí)現(xiàn)。
過(guò)程大致如下：
2、解析MySQL的binlog，實(shí)現(xiàn)將數(shù)據(jù)庫(kù)中的數(shù)據(jù)同步到Redis。可以通過(guò)canal實(shí)現(xiàn)。canal是阿里巴巴旗下的一款開(kāi)源項(xiàng)目，基于數(shù)據(jù)庫(kù)增量日志解析，提供增量數(shù)據(jù)訂閱&消費(fèi)。
canal的原理如下：
先看看什么是存儲(chǔ)過(guò)程。
存儲(chǔ)過(guò)程是在大型數(shù)據(jù)庫(kù)系統(tǒng)中，一組為了完成特定功能的SQL 語(yǔ)句集，它存儲(chǔ)在數(shù)據(jù)庫(kù)中，一次編譯后永久有效，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它。
存儲(chǔ)過(guò)程主要有以下幾個(gè)缺點(diǎn)。
月8日，“哈妹”薛佳凝在微博上曬出修行照，自曝已帶發(fā)修行11個(gè)月零17天。一時(shí)間驚呆了所有人，難道薛佳凝已看破紅塵？胡歌真的沒(méi)戲了？
薛佳凝：“11個(gè)月零17天…圓滿了五加行…這樣的一段時(shí)間和經(jīng)歷…對(duì)我來(lái)說(shuō)…很特殊…上師…一切都是您的指引…心不迷于紛擾…能于靜處自由…感念著您的慈悲…慢慢的…慈悲就會(huì)滲透心流…心里不再有那么多自我…開(kāi)始裝得下更多人的幸福…今天…是一個(gè)圓滿…愿…回向給無(wú)盡眾生”。
薛佳凝提到的五加行是佛教術(shù)語(yǔ)，一般來(lái)說(shuō)常見(jiàn)于藏傳佛教，五加行既是五種加行，分別是皈依、發(fā)心、念修金剛薩埵懺悔、供曼茶羅和上師瑜伽。具體內(nèi)容小編這種俗人也感受不了，但可以明確的是五加行是正式修行的基礎(chǔ)，想要五加行修得圓滿每個(gè)加行數(shù)量至少要達(dá)十萬(wàn)之?dāng)?shù)，總計(jì)在五十五萬(wàn)左右，過(guò)程十分艱苦。而五加行圓滿就可以開(kāi)始正式的修行了。
每每提到薛佳凝就一定會(huì)提到她之前與胡歌那段轟轟烈烈的愛(ài)情。
薛佳凝是78年生人，今年39歲，比胡歌大近四歲。薛佳凝以出演《粉紅女郎》中的哈妹走紅，而后在2004年在拍攝《天下無(wú)雙》時(shí)與當(dāng)年常來(lái)串門（也不知道是不是故意套近乎）的胡歌相識(shí)，2006年兩人相戀。而后2008年胡歌在拍攝《射雕英雄傳》時(shí)發(fā)生車禍，女助手當(dāng)場(chǎng)死亡，而胡歌也幾近毀容。薛佳凝在這段姐弟戀中十分癡情胡歌，將姐姐得角色做得很好，處處遷就胡歌。據(jù)悉，在這次車禍發(fā)生以后，薛佳凝更是第一時(shí)間趕到了病房，照顧胡歌。為了胡歌，正值事業(yè)上升期的薛佳凝甚至停掉了一年的工作，專心照顧胡歌這個(gè)男友。這種患難見(jiàn)真情，最后卻因?yàn)橐恍┢渌颍?tīng)說(shuō)是胡歌媽媽不同意，而胡歌是個(gè)出了名孝順的人），兩人最終還是以分手告終，令粉絲十分惋惜。
胡歌曾經(jīng)公開(kāi)表示過(guò)，他最大的一個(gè)遺憾，就是自己從未在公眾面前感謝過(guò)薛佳凝。
而在2015年胡歌憑借《瑯玡榜》和《偽裝者》再次大火時(shí)，這段戀情被再度提起時(shí)，薛佳凝只是表示了他現(xiàn)在很紅，而她不想回答這個(gè)話題（這種不蹭熱點(diǎn)不舊事重提的人品，給100分都不嫌多！！！）。當(dāng)年胡歌在接受《魯豫有約》采訪時(shí)也是眼眶微紅地說(shuō)：“她是真的很好。”
在胡歌今年新劇《獵場(chǎng)》熱播之時(shí)，薛佳凝卻在微博公開(kāi)了自己已帶發(fā)修行近一年地消息，引來(lái)了大家熱烈討論。胡歌作為娛樂(lè)圈為數(shù)不多地清流，又是知名地黃金單身漢（送走了無(wú)數(shù)好友，胡歌啊，你不要連吳磊都送走了啊！！），每每有人結(jié)婚時(shí)，胡歌總會(huì)被大家逼婚。胡歌的人格魅力和現(xiàn)在粉絲的寬容度是一方面，而另一方面胡歌與薛佳凝這段遺憾的戀情也為大家所惋惜。畢竟大家也是想胡歌有一個(gè)好歸宿，早點(diǎn)結(jié)婚生子（胡歌做伴郎超帥的好嗎，每次微博淪陷都是他莫名其妙就搶了新郎新娘的戲有沒(méi)有！！！）。
算起來(lái)，雖然胡歌前前后后又分別交過(guò)幾任女朋友，但距離兩人分手也實(shí)實(shí)在在小十年了。其實(shí)讓他倆復(fù)合的呼聲那么高，也是網(wǎng)友粉絲們不想看到兩個(gè)人格魅力如此之好的人還單著。天后王菲和謝霆鋒闊別十一年之后又在一起之時(shí)，舒淇馮德倫結(jié)婚的時(shí)候，林心如霍建華等等多年之后終于在一起的大團(tuán)圓結(jié)局是我們所喜聞樂(lè)見(jiàn)的。
其實(shí)兩個(gè)人既然愛(ài)過(guò)，無(wú)論是現(xiàn)在大火特火還是帶發(fā)修行，最后是不是在一起其實(shí)就是兩個(gè)人自己的緣分，沒(méi)有什么好強(qiáng)求的，畢竟兩個(gè)都是如此出色的人。特別是胡歌，在生死之間走了一圈，看著現(xiàn)在的明星都是化妝到“無(wú)毛孔”時(shí)代，而胡歌卻總是選擇不將自己車禍損傷的那半邊臉完全遮瑕，靜下心來(lái)去演話劇，去讀書進(jìn)修。曾經(jīng)對(duì)胡歌有一個(gè)說(shuō)法，一邊是天賦，一邊是人生。在人生都快走到不惑之年的日子，相信他倆最后能有自己的善果。
薛佳凝帶發(fā)修行近一年！哈妹別走，胡歌怎么辦？？？
http://www.333fun.com/thread-9244-1-1.html
(出處: 嚇歪寶寶了_小說(shuō)迷_原創(chuàng)文章論壇)
*請(qǐng)認(rèn)真填寫需求信息，我們會(huì)在24小時(shí)內(nèi)與您取得聯(lián)系。
