mysqldumpslow 分析慢查詢工具

由于開啟慢查詢?nèi)罩荆€上的日志可能會特別大枣申，直接看文本是不切實(shí)際的售葡，使用mysqldumpslow 工具可以分析統(tǒng)計(jì)日志文件形成報(bào)表，方便我們查看

//查看工具的命令
mysqldumpslow --help

下面列出比較常用的忠藤，也是最實(shí)用的

-s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time（按平均鎖定時(shí)間排序）
                ar: average rows sent（按平均行數(shù)排序）
                at: average query time（按平均查詢時(shí)間排序）
                 c: count（sql次數(shù)）
                 l: lock time（鎖定時(shí)間）
                 r: rows sent（行數(shù)）
                 t: query time （查詢時(shí)間） 
  -r           reverse the sort order (largest last instead of first)（倒序）

例如

//按照時(shí)間進(jìn)行排序挟伙，排序是從大到小排序
mysqldumpslow -s t /xxxx/xxx.log
//按照次數(shù)進(jìn)行排序
mysqldumpslow -s c /xxxx/xxx.log

SQL語句的優(yōu)化方案

join分析

一、小表驅(qū)動大表優(yōu)于大表驅(qū)動小表

先了解在join連接時(shí)哪個(gè)表是驅(qū)動表熄驼，哪個(gè)表是被驅(qū)動表：

• 1.當(dāng)使用left join時(shí)像寒，左表是驅(qū)動表烘豹，右表是被驅(qū)動表
• 2.當(dāng)使用right join時(shí)瓜贾，右表是驅(qū)動表，左表是被驅(qū)動表
• 3.當(dāng)使用join時(shí)携悯，mysql會選擇數(shù)據(jù)量比較小的表作為驅(qū)動表祭芦，大表作為被驅(qū)動表

例如: A是小表，B是大表
　　使用left join 時(shí)憔鬼，則應(yīng)該這樣寫select * from A a left join B b on a.code=b.code
　　A表時(shí)驅(qū)動表龟劲，B表是被驅(qū)動表

測試：A表140多條數(shù)據(jù)胃夏，B表20萬左右的數(shù)據(jù)量
　　select * from A a left join B b on a.code=b.code
　　執(zhí)行時(shí)間：7.5s

　　select * from B b left join A a on a.code=b.code
　　執(zhí)行時(shí)間：19s

二、在join中驅(qū)動表的索引會失效昌跌，只有被驅(qū)動表的索引起效果仰禀，所以給被驅(qū)動表建立索引

在以小表驅(qū)動大表的情況下，再給大表建立索引會大大提高執(zhí)行速度蚕愤，驅(qū)動表的索引會失效答恶。
假如驅(qū)動表A有100條數(shù)據(jù)，驅(qū)動表B有1000條數(shù)據(jù) 并且B表的字段s有索引萍诱，現(xiàn)在執(zhí)行這樣的語句：

select * from A a left join B b on (a.s = b.s)悬嗓；

這條語句會執(zhí)行多少次查詢呢？答案是：200次裕坊。
首先遍歷A表包竹，把A表的100條數(shù)據(jù)拿到B表一一查詢，由于B表的s字段是有索引的籍凝，所以每一次都能查到一一對應(yīng)的數(shù)據(jù)周瞎，所以需要100次查詢。也就是100+100=200静浴。
但是如果被驅(qū)動表B沒有的s字段沒有索引堰氓，那么就變成了全表掃描：100*1000=100000。性能就會變得及其低下苹享。所以使用join查詢時(shí),被驅(qū)動表務(wù)必加上索引双絮，否則性能會急劇下降。

三得问、在排序時(shí)使用索引字段效率會大幅度提升

• 1囤攀、在進(jìn)行排序時(shí)如果排序字段是索引字段，那么排序?qū)呙杷饕瓿膳判蚬常饕緛砭褪怯行虻姆倌樱恍枰獟呙璞恚孕蕦浅漓骚？?/li>

• 2蝌衔、而不使用索引字段排序的情況，需要回表掃描然后開辟額外的內(nèi)存空間進(jìn)行排序蝌蹂，如果內(nèi)存空間不足還會生成臨時(shí)文件進(jìn)行落盤增加物理磁盤IO,極大的影響了SQL效率

using filesort

using filesort有兩種算法噩斟，一個(gè)是常規(guī)排序法，一個(gè)是優(yōu)化排序法

using filesort的常規(guī)排序法執(zhí)行步驟：

實(shí)例：

排序緩沖區(qū)

using filesort的優(yōu)化排序法執(zhí)行步驟：

using filesort的優(yōu)化排序法執(zhí)行步驟

using filesort什么時(shí)候執(zhí)行常規(guī)排序和優(yōu)化排序：

當(dāng)排序數(shù)量小4096B時(shí)使用的優(yōu)化排序法拧抖，否則使用常規(guī)排序

以下是聯(lián)合索引時(shí)排序是否走索引的情況

先建立一個(gè)聯(lián)合索引
索引 key a_b_c(a,b,c)

• 使用索引的左端字段排序時(shí)能走索引，并且排序的升降序要一致

ORDER BY a
ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC,b DESC,c DESC

• 如果WHERE使用了索引的最左端,則ORDER BY 也能使用索引

WHERE a = const  ORDER BY b,c
WHERE a = const AND b = const ORDER BY c
WHERE a = const  ORDER BY b,c
WHERE a = const AND b > const ORDER BY b,c

• 以下情況不能使用索引排序

WHERE a > const  ORDER BY b,c/*范圍排序不能使用索引*/
ORDER BY a ASC,b DESC,c DESC /*排序不一致*/
WHERE g = const ORDER BY b,c    /*丟失a索引*/
WHERE a = const ORDER BY c     /*丟失b索引*/
WHERE a = const ORDER BY a,d /*d不是索引的一部分*/

四免绿、禁止使用Select *唧席，只查自己需要的字段

1、在數(shù)據(jù)庫查詢出來的數(shù)據(jù)是需要網(wǎng)絡(luò)傳輸?shù)娇蛻舳说某凹荩樵兂鰜淼臄?shù)據(jù)字段越多數(shù)據(jù)量就越多淌哟，占用網(wǎng)絡(luò)帶寬
2、在排序時(shí)如果沒有走索引的情況下辽故，數(shù)據(jù)庫是需要另外開辟空間進(jìn)行排序的徒仓，字段越多數(shù)據(jù)量大導(dǎo)致內(nèi)存不足就需要進(jìn)行創(chuàng)建臨時(shí)文件進(jìn)行落盤增加物理磁盤IO
3、很有可能會使覆蓋索引失效

五誊垢、order by,group by,distinct盡量使用索引

除了order by會排序外,group by（group by在Mysql8的中進(jìn)行了優(yōu)化掉弛，內(nèi)部已經(jīng)不排序了）和distinct是內(nèi)部先進(jìn)行排序的，所以最好的優(yōu)化方案就是盡量的使它們走索引

group by:

索引：

mysql5.6 group by會默認(rèn)排序

mysql8group by分組時(shí)默認(rèn)不排序

出現(xiàn)了using temporary和using filesort的分組都需要進(jìn)行優(yōu)化

group by的三種類型

group by的三種類型

六、盡量使用子關(guān)聯(lián)查詢代替使用子查詢芋肠。

盡量將子查詢轉(zhuǎn)化為join,子查詢產(chǎn)生的結(jié)果集無法使用索引乎芳，并且子查詢的結(jié)果集會產(chǎn)生臨時(shí)表，如果子查詢數(shù)量過多生成大量的臨時(shí)表帖池，嚴(yán)重消耗cup io資源奈惑。

但是現(xiàn)實(shí)中子查詢是很多業(yè)務(wù)必不可少的，現(xiàn)在mysql的優(yōu)化器對子查詢的優(yōu)化也有相應(yīng)的支撐睡汹，有些子查詢效果不一定比關(guān)聯(lián)查詢差肴甸，所以還以實(shí)際的執(zhí)行計(jì)劃去定

七、分頁優(yōu)化

八帮孔、union優(yōu)化

九雷滋、復(fù)合索引的優(yōu)化

在通常我們查詢時(shí)，sex(男文兢，女)晤斩，flag(0，1)之類的是不適合建立索引的姆坚，這類的散列性太差澳泵，但是很多查詢又用到它們，我們可以給它們建立一個(gè)復(fù)合索引(sex,flag),每一次使用時(shí)必須遵守最左前綴原則兼呵，這里通惩酶ǎ可以這么用：例如我們要查一個(gè)帶上flag又不帶sex字段的查詢，我們可以寫成這樣繞過sex字段击喂，又符合了最左前綴

select xxx from xxx where sex in ('男','女') and flag=xxx

十维苔、范圍查詢優(yōu)化

范圍查詢(between,<,>)會使右邊的索引條件失效，所以在sql中盡可能的把范圍查詢放在最后面

十一懂昂、其他方案

• 1介时、不要在數(shù)據(jù)庫進(jìn)行運(yùn)算，比如：觸發(fā)器凌彬，存儲過程沸柔、函數(shù)。應(yīng)當(dāng)用后端代碼實(shí)現(xiàn)
• 2铲敛、主鍵推薦使用自增的褐澎，不推薦使用字符串作為索引，主鍵不應(yīng)該被修改伐蒋。選擇自增主鍵作為索引時(shí)插入數(shù)據(jù)時(shí)都是按照索引的順序插入的工三，索引不需要移動數(shù)據(jù)和銷毀額外的性能維護(hù)索引數(shù)據(jù)，而頻繁修改索引會造索引數(shù)據(jù)頻繁移動產(chǎn)生索引碎片得不到一個(gè)結(jié)構(gòu)順序緊湊的索引結(jié)構(gòu)先鱼。如果我們沒有選擇自增主鍵也沒有指定其他字段當(dāng)主鍵徒蟆，innodb會選擇一個(gè)rowid作為內(nèi)置主鍵，往后我們插入的數(shù)據(jù)的主鍵都是按照這個(gè)rowid自增生成
• 3型型、事務(wù)簡單段审，甚至不要事務(wù)
• 4、使用or代替in,用union代替or,使用union all代替union(union有去重的開銷)
• 5闹蒜、limit優(yōu)化：偏移量越大寺枉，執(zhí)行越慢

select * from a where id in (select id from a where id>10000) limit0,10
select * from a limit10000,10
第一個(gè)sql優(yōu)于第二個(gè)sql

分析sql的執(zhí)行計(jì)劃

id表示查詢的順序：

• 當(dāng)id相同時(shí)，sql是由上至下執(zhí)行绷落，應(yīng)該由上至下分析
• 當(dāng)id不同時(shí)姥闪，其id越大則表示執(zhí)行的優(yōu)先級越高，應(yīng)該從id高的到id低的分析
• 當(dāng)id為null時(shí)砌烁，其代表了是union查詢的結(jié)果
• 出現(xiàn)兩組id,應(yīng)該id大的一組優(yōu)先權(quán)高

當(dāng)id相同時(shí)催式，sql是由上至下執(zhí)行，應(yīng)該由上至下分析

當(dāng)id不同時(shí)荣月，其id越大則表示執(zhí)行的優(yōu)先級越高

出現(xiàn)兩組id,應(yīng)該id大的一組優(yōu)先權(quán)高

各個(gè)select_type的含義

table

查詢的表名梳毙，并不一定是真實(shí)存在的表哺窄，有別名顯示別名，也可能為臨時(shí)表

type

mysql查詢的方式账锹，這是Mysql查詢性能好壞最重要的指標(biāo)

keys

• possible_index:查詢出可能會用到的索引
• key:查詢時(shí)實(shí)際用到的索引
• key_len:查詢實(shí)際使用索引的最大長度煤辨，與字段的數(shù)據(jù)類型有關(guān)

ref

常見的有：const，func木张，null众辨，字段名。
4舷礼、當(dāng)使用常量等值查詢鹃彻，顯示const，
1妻献、當(dāng)關(guān)聯(lián)查詢時(shí)蛛株，會顯示相應(yīng)關(guān)聯(lián)表的關(guān)聯(lián)字段
2、如果查詢條件使用了表達(dá)式育拨、函數(shù)谨履，或者條件列發(fā)生內(nèi)部隱式轉(zhuǎn)換，可能顯示為func
3熬丧、其他情況null

extra

這一列包含的是不在其他列顯示的額外信息笋粟。

• using index:這個(gè)說明MySQL使用覆蓋索引，避免了回表操作析蝴，效率不錯(cuò)矗钟。
• using where:查詢時(shí)未找到可用的索引，進(jìn)而通過where條件過濾獲取所需數(shù)據(jù)嫌变，但要注意的是并不是所有帶where語句的查詢都會顯示Using where吨艇。
• using filesort:表示無法利用索引完成的排序操作，也就是ORDER BY的字段沒有索引腾啥，通常這樣的SQL都是需要優(yōu)化的东涡。
• using temporay:表示查詢后結(jié)果需要使用臨時(shí)表來存儲冯吓，一般在排序或者分組查詢時(shí)用到。
• Using join buffer：在我們聯(lián)表查詢的時(shí)候疮跑，如果表的連接條件沒有用到索引组贺，需要有一個(gè)連接緩沖區(qū)來存儲中間結(jié)果。
• Impossible where：表示在我們用不太正確的where語句祖娘，導(dǎo)致沒有符合條件的行失尖。

索引的優(yōu)化

哪些字段適合創(chuàng)建索引、哪些不適合的：

• 1渐苏、字段值散列度大掀潮，唯一性高的字段。例如：狀態(tài)字段可能只是兩個(gè)值這種唯一性太差的值不適合當(dāng)索引琼富。散列性越差仪吧，說明重復(fù)數(shù)據(jù)越多，如果作為索引鞠眉，占據(jù)的葉子節(jié)點(diǎn)量龐大薯鼠，此時(shí)索引掃描跟全表掃描區(qū)別不大
• 2、如果非要為這些唯一性性差的字段創(chuàng)建索引械蹋，可以嘗試使用復(fù)合索引
• 3出皇、更新太頻繁的字段不適合創(chuàng)建索引，因?yàn)槊看胃伦侄位└辏饕紩匦抡{(diào)整排序io消耗巨大
• 4恶迈、頻繁使用where、order by谱醇、group by暇仲、distinct、join 建立索引或者聯(lián)合索引
• 5副渴、建立聯(lián)合索引奈附，應(yīng)當(dāng)優(yōu)先左側(cè)優(yōu)化，如：散列性最好煮剧、使用最頻繁斥滤、字段長度最小符合這些條件的優(yōu)先放在聯(lián)合索引的左側(cè)

在 MySQL 中，下列幾種情況下有可能使用到索引勉盅。

• 索引一是否被用到舉例說明如下佑颇。
  有幾個(gè)原則：

1、 復(fù)合索引左前綴和中間不斷原則草娜，復(fù)合索引的前綴不能丟失挑胸，中間索引不能斷，
例如：key(a,b,c),where a b c宰闰、where a b茬贵、where a簿透、where a f b 都能用到索引，
where a c,where b c解藻、where c老充、where b都用不到索引

2、不能在索引上進(jìn)行特殊操作螟左。例如：函數(shù)啡浊、計(jì)算、類型轉(zhuǎn)換等

3胶背、在聯(lián)合索引中使用范圍（between,<,>,in等）會導(dǎo)致右邊的索引失效巷嚣，
例如：where a=1 and b>1 order by c desc b這個(gè)字段的條件是一個(gè)范圍，
導(dǎo)致右邊c字段索引失效奄妨。解決辦法：把b從聯(lián)合索引移除涂籽，只留下a c

4苹祟、在索引中使用(!=,<>,is null,%xx,%xx%)會導(dǎo)致索引失效,
注意(is not null)在mysql8以下的版本會使索引失效

5砸抛、索引是字符串，查詢時(shí)卻不用單引號括起來會導(dǎo)致索引失效

6树枫、使用or時(shí)直焙，查詢的字段不是索引字段就會失效，or中的條件不是索引字段也會失效砂轻，
例如奔誓，key g,key(a,b,c),   select a b where a or b、select a g where a or g   都走索引搔涝，
select f a g where a or g厨喂、select a g where a or g or f就不走索引了

7、禁止使用子查詢庄呈，將子查詢轉(zhuǎn)化為join,子查詢產(chǎn)生的結(jié)果集無法使用索引蜕煌，
并且子查詢的結(jié)果集會產(chǎn)生臨時(shí)表，如果子查詢數(shù)量過多生成大量的臨時(shí)表诬留，嚴(yán)重消耗cup io資源

首先按 company_id斜纪，moneys 的順序創(chuàng)建一個(gè)復(fù)合索引，具體如下:

create index ind_sales2_companyid_moneys on sales2(company_id,moneys);
create index ind_company2_name on company2(name);
create index ind_sales_year on sales(year);

• 然后按 company_id 進(jìn)行表查詢文兑，具體如下:

可見雖然在 moneys 上面建有復(fù)合索引籍铁，但是由于 moneys不是聯(lián)合索引的第一列贮聂，那么在查詢中 這個(gè)索引也不會被 MySQL 采用靠柑。

• 對于使用 like 的查詢，后面如果是常量并且只有%號不在第一個(gè)字符吓懈，索引才可能會 被使用歼冰，來看下面這個(gè)個(gè)執(zhí)行計(jì)劃

可以發(fā)現(xiàn)第一個(gè)例子沒有使用索引，而第二例子就能夠使用索引耻警，區(qū)別就在于“%”的位置 不同隔嫡，前者把“%”放到第一位就不能用到索引，而后者沒有放到第一位就使用了索引甘穿。 另外腮恩，如果如果 like 后面跟的是一個(gè)列的名字，那么索引也不會被使用温兼。
如果對大的文本進(jìn)行搜索秸滴，使用全文索引而不用使用 like ‘%…%’。

• 如果列名是索引募判，使用column_name is null可以走索引荡含。如下例中查詢name為null 的記錄就用到了索引，但是如果是name is not null届垫，not in,<>都是使用不到索引

• 用 or 分割開的條件释液，如果 or 前的條件中的列有索引，而后面的列中沒有索引装处， 那么涉及到的索引都不會被用到误债，例如:

可見雖然在 year 這個(gè)列上存在索引 ind_sales_year，但是這個(gè) SQL 語句并沒有用到這個(gè)索引妄迁， 原因就是 or 中有一個(gè)條件中的列沒有索引寝蹈。

• 如果列類型是字符串，那么一定記得在 where 條件中把字符常量值用引號引 起來登淘，否則的話即便這個(gè)列上有索引箫老，MySQL 也不會用到的，因?yàn)樾伟铮琈ySQL 默認(rèn)把輸入的 常量值進(jìn)行轉(zhuǎn)換以后才進(jìn)行檢索槽惫。如下面的例子中 company2 表中的 name 字段是字符型的， 但是 SQL 語句中的條件值 294 是一個(gè)數(shù)值型值辩撑，因此即便在 name 上有索引界斜，MySQL 也不能 正確地用上索引，而是繼續(xù)進(jìn)行全表掃描合冀。

從上面的例子中可以看到吁伺，第一個(gè) SQL 語句中把一個(gè)數(shù)值型常量賦值給了一個(gè)字符型的列 name陪捷，那么雖然在 name 列上有索引九杂，但是也沒有用到;而第二個(gè) SQL 語句就可以正確使 用索引教硫。

其他SQL優(yōu)化

INSERT

如果同時(shí)從同一客戶插入很多行族吻，盡量使用多個(gè)值表的 INSERT 語句，這種方式將大大
縮減客戶端與數(shù)據(jù)庫之間的連接、關(guān)閉等消耗，使得效率比分開執(zhí)行的單個(gè) INSERT 語 句快(在一些情況中幾倍)奕删。下面是一次插入多值的一個(gè)例子:

insert into test values(1,2),(1,3),(1,4)...

GROUP BY

提高GROUP BY語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉.下面兩個(gè)查詢返回相同結(jié)果，但第二個(gè)明顯就快了許多.

低效:
SELECT JOB , AVG(SAL) FROM EMP GROUP BY JOB
HAVING JOB ='PRESIDENT' OR JOB ='MANAGER'
高效:
SELECT JOB , AVG(SAL) FROM EMP WHERE JOB ='PRESIDENT'
OR JOB ='MANAGER' GROUP BY JOB

嵌套查詢

盡量使用JOIN代替子查詢疗认，使用JOIN時(shí)
子查詢產(chǎn)生的結(jié)果集無法使用索引完残，并且子查詢的結(jié)果集會產(chǎn)生臨時(shí)表，如果子查詢數(shù)量過多生成大量的臨時(shí)表横漏，嚴(yán)重消耗cup io資源
如果一定要使用子查詢進(jìn)行復(fù)制的sql嵌套谨设，應(yīng)當(dāng)盡量縮小內(nèi)表，也就是先把子查詢的表按條先進(jìn)行篩選然后在進(jìn)行查詢關(guān)聯(lián)這樣關(guān)聯(lián)的范圍就會變小

MySql表字段優(yōu)化

char和varchar

• char：定長缎浇，一般用于固定長度的數(shù)據(jù)存儲 扎拣，例如：身份證號，手機(jī)號素跺，電話二蓝，密碼等
  CHAR(M)定義的列的長度為固定的，M取值可以為0～255之間亡笑，M代表了可以存儲的字符的個(gè)數(shù)而不是字節(jié)數(shù)侣夷，當(dāng)存儲的字符不足指定的M值時(shí)會充空格以達(dá)到指定的長度横朋，比如定義 char(10)仑乌，那么不論你存儲的數(shù)據(jù)是否達(dá)到了10個(gè)字節(jié)，都要占去10個(gè)字節(jié)的空間,不足的自動用空格填充琴锭。晰甚。當(dāng)檢索時(shí)，尾部的空格被刪除掉决帖。在存儲或檢索過程中不進(jìn)行大小寫轉(zhuǎn)換厕九。由于是固定長度，更新時(shí)不需要調(diào)整存儲空間地回，所以在CHAR字段上的索引效率級高

• varchar：不定長扁远，VARCHAR(M)，M取值可以為0~65535之間刻像，M代表了可以存儲的字符的個(gè)數(shù)而不是字節(jié)數(shù)畅买，整體最大長度是65,532字節(jié)，在utf-8中一個(gè)字符占用磁盤空間的3個(gè)字節(jié)细睡，另加一個(gè)字節(jié)或兩個(gè)字節(jié)來記錄長度(如果列聲明的長度超過255谷羞，否則使用兩個(gè)字節(jié))。VARCHAR值保存時(shí)不進(jìn)行填充溜徙。當(dāng)值保存和檢索時(shí)尾部的空格仍保留湃缎。varchar存儲變長數(shù)據(jù)犀填，但是VARCHAR可以節(jié)約內(nèi)存塊的長度和磁盤空間的大小，檢索上可以提升性能嗓违，但存儲效率沒有CHAR高九巡。

char盡量存儲一些定長的數(shù)據(jù)，比如身份證蹂季、電話號碼比庄、姓名等，char因?yàn)槭枪潭碎L度所以可以避免不定長度字符串的空間調(diào)節(jié)乏盐。

varchar根據(jù)實(shí)際存儲的數(shù)據(jù)分配最終的存儲空間佳窑，由于可以自動調(diào)節(jié)長度，所以可以節(jié)省磁盤空間父能，但是并不能調(diào)節(jié)內(nèi)存空間神凑，內(nèi)存空間是定長的時(shí)候固定的，但是如果進(jìn)行字段更新時(shí)會做空間調(diào)節(jié)操作何吝，額外消耗性能溉委，如果擴(kuò)展長度時(shí)頁空間不足還會通過頁的分裂來達(dá)到存儲的目的，進(jìn)一步降低性能爱榕，所以在創(chuàng)建varchar不要使用默認(rèn)的255瓣喊，而根據(jù)需要的中文個(gè)數(shù)創(chuàng)建，過大的字符長度會消耗過多的緩沖池內(nèi)存和B+樹結(jié)構(gòu)葉子節(jié)點(diǎn)的增多

char和varchar相同點(diǎn)：
char(n)黔酥，varchar(n)中的n都代表字符的個(gè)數(shù)
超過char藻三，varchar最大長度n的限制后，字符串會被截?cái)唷?/p>

varchar定長的意義：
使用VARCHAR(100)與VARCHAR(200)存儲的90字符的效果是的相同嗎?結(jié)果是否定的跪者。雖然他們用來存儲90個(gè)字符的數(shù)據(jù)棵帽，其存儲空間相同。但是對于內(nèi)存的消耗是不同的渣玲。對于VARCHAR數(shù)據(jù)類型來說逗概，硬盤上的存儲空間雖然都是根據(jù)實(shí)際字符長度來分配存儲空間的，但是對于內(nèi)存來說忘衍，則不是逾苫。其時(shí)使用固定大小的內(nèi)存塊來保存值。簡單的說枚钓，就是使用字符類型中定義的長度铅搓，即200個(gè)字符空間。顯然秘噪，這對于排序或者臨時(shí)表(這些內(nèi)容都需要通過內(nèi)存來實(shí)現(xiàn))作業(yè)會產(chǎn)生比較大的不利影響狸吞。所以如果某些字段會涉及到文件排序或者基于磁盤的臨時(shí)表時(shí)，分配VARCHAR數(shù)據(jù)類型時(shí)仍然不能夠太過于慷慨。還是要評估實(shí)際需要的長度蹋偏，然后選擇一個(gè)最長的字段來設(shè)置字符長度便斥。如果為了考慮冗余，可以留10%左右的字符長度威始。千萬不能認(rèn)為其為根據(jù)實(shí)際長度來分配存儲空間枢纠，而隨意的分配長度，或者說干脆使用最大的字符長度黎棠。

由于B+樹的特點(diǎn)所以設(shè)計(jì)字段時(shí)盡量設(shè)計(jì)的小晋渺，夠用就好了，這樣可以減少磁盤的io次數(shù)脓斩，有以下方案：

• 1.表字段避免null值出現(xiàn)木西，null值很難查詢優(yōu)化且占用額外的索引空間，推薦默認(rèn)數(shù)字0代替null随静。
• 2.盡量使用INT而非BIGINT八千，如果非負(fù)則加上UNSIGNED（這樣數(shù)值容量會擴(kuò)大一倍），當(dāng)然能使用TINYINT燎猛、SMALLINT恋捆、MEDIUM_INT更好。
• 3.少用text類型重绷，盡量用varchar代替text沸停，char雖然比varchar快，但是char是定長昭卓，varchar是可擴(kuò)展長度的愤钾，更適合代替text類型的場景
• 4.使用枚舉或整數(shù)代替字符串類型
• 5.日期盡量使用TIMESTAMP代替DATETIME
• 6.單表不要有太多字段，建議在20以內(nèi)
• 7.用整型來存IP葬凳，如：使用函數(shù)相互轉(zhuǎn)化inet_aton('255.255.255.255')=4294967295,inet_ntoa(4294967295)='255.255.255.255'

內(nèi)存優(yōu)化

https://www.cnblogs.com/ilifeilong/p/7224678.html

緩沖池：

innodb是基于磁盤存儲的绰垂，為提升性能室奏，innodb會在內(nèi)存中有一個(gè)緩沖池火焰，每次讀書數(shù)據(jù)會將數(shù)據(jù)放到緩沖池中緩存，修改數(shù)據(jù)也是先對緩沖池進(jìn)行修改胧沫，然后按照一定的頻率刷回硬盤昌简，當(dāng)查詢命中內(nèi)存中的緩沖池時(shí)就不用查詢磁盤。

在專用數(shù)據(jù)庫服務(wù)器上绒怨，通常將多達(dá)80％的物理內(nèi)存分配給InnoDB緩沖池纯赎。因?yàn)镮nnoDB的存儲引擎的工作方式總是將數(shù)據(jù)庫文件按頁讀取到緩沖池，每個(gè)頁16k默認(rèn)（innodb_page_size=16k）南蹂，在MySQL 5.7中增加了32KB和64KB頁面大小的支持犬金，通過innodb_buffer_pool_size設(shè)置緩沖池大小，通過innodb_page_size設(shè)置頁的大小，設(shè)置innodb_buffer_pool_size時(shí)晚顷，操作以塊（chunk）形式執(zhí)行峰伙。塊大小由innodb_buffer_pool_chunk_size配置選項(xiàng)定義，默認(rèn)值128M该默，也就是說每個(gè)緩沖池實(shí)例大小默認(rèn)是128M瞳氓，innodb_buffer_pool_size=innodb_buffer_pool_chunk_sizeinnodb_buffer_pool_instances的倍數(shù)，如果配置innodb_buffer_pool_size為不等于innodb_buffer_pool_chunk_sizeinnodb_buffer_pool_instances的倍數(shù)栓袖，則緩沖池大小將自動調(diào)整為等于或不小于指定緩沖池大小的innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍數(shù)匣摘。
例如：

show variables like 'innodb_buffer_pool_size'   //查詢innodb_buffer_pool_size的大小
SET GLOBAL innodb_buffer_pool_size=16106127360 //設(shè)置innodb_buffer_pool_size的大小

LRU

InnoDB將buffer pool作為一個(gè)list管理，基于LRU算法裹刮。當(dāng)有新的頁要讀入到buffer pool的時(shí)候音榜，buffer pool就將最近最少使用的頁從buffer pool中驅(qū)逐出去（類似redis的內(nèi)存淘汰）并且將新頁加入到list的中間位置，這就是所謂的“中點(diǎn)插入策略”捧弃。一般情況下list頭部存放的是熱數(shù)據(jù)就是所謂的young pages（最近經(jīng)常訪問的數(shù)據(jù)）囊咏，list尾部存放的就是old pages（最近不被訪問的數(shù)據(jù)），默認(rèn)3/8的list信息是作為old list塔橡，這些信息是被驅(qū)逐的對象梅割，通過innodb_old_block_pct設(shè)置比例，默認(rèn)是37也就是3/8葛家，每次插入新頁都會從倒數(shù)的3/8的位置插入户辞，倒數(shù)的3/8的頁是要被刷出緩沖池的

• 1、3/8的list信息是作為old list癞谒，這些信息是被驅(qū)逐的對象底燎。
• 2、list的中點(diǎn)就是我們所謂的old list頭部和new list尾部的連接點(diǎn)弹砚，相當(dāng)于一個(gè)界限双仍。
• 3、新數(shù)據(jù)的讀入首先會插入到old list的頭部桌吃。
• 4朱沃、如果是old list的數(shù)據(jù)被訪問到了，這個(gè)頁信息就會變成new list茅诱，變成young page逗物，就會將數(shù)據(jù)頁信息移動到new sublist的頭部。

innodb_old_blocks_time瑟俭，單位是毫秒翎卓，默認(rèn)是1000，可以設(shè)置當(dāng)頁在磁盤中被讀出后經(jīng)過多長時(shí)間才能被加入的緩沖池摆寄。如果增大這個(gè)值的話失暴，就會讓buffer pool里面很多頁信息變老的速度變快坯门，因?yàn)檫@些數(shù)據(jù)不會很快被內(nèi)存中擦除的話，就會變成熱數(shù)據(jù)而擠掉原有緩存的數(shù)據(jù)逗扒。

緩沖池實(shí)例

并且緩沖池是支持多實(shí)例的田盈，也就是一個(gè)MySql實(shí)例支持多個(gè)緩沖池以減少并發(fā)操作中內(nèi)存結(jié)構(gòu)的爭用，對應(yīng)內(nèi)存比較大的64位服務(wù)器缴阎，可以設(shè)置為8個(gè)允瞧，通過show engine innodb status查詢，通過innodb_buffer_pool_instances設(shè)置緩沖池的實(shí)例數(shù)

show engine innodb status //查詢緩沖池實(shí)例
SET GLOBAL innodb_buffer_pool_instances=8 // 設(shè)置緩沖池的實(shí)例數(shù)

MySql的優(yōu)化極限

當(dāng)面對百萬級別的數(shù)據(jù)量級時(shí)蛮拔，就算是Mysql調(diào)優(yōu)大濕都不可能調(diào)得動了述暂，這時(shí)就到了MySql的性能極限了，請放棄在MySql的關(guān)聯(lián)查詢吧建炫，盡量利用緩存和隊(duì)列

這時(shí)一個(gè)優(yōu)秀的架構(gòu)就比極限調(diào)優(yōu)更勝百倍畦韭，MySql在一個(gè)不錯(cuò)的物理機(jī)器上最多能承載3500-4500的QPS

讀多寫少用緩存，寫多讀少用隊(duì)列肛跌，那么高并發(fā)用哪種艺配？當(dāng)然是兩種一起用：

• 1、高并發(fā)讀數(shù)據(jù)：利用redis將熱點(diǎn)數(shù)據(jù)進(jìn)行緩存衍慎，將絕大部分的讀壓力讓redis承擔(dān)转唉，以減輕mysql的讀壓力
• 2、高并發(fā)寫數(shù)據(jù)：當(dāng)大量的寫請求到達(dá)Mysql時(shí)稳捆，將寫流量先進(jìn)隊(duì)列赠法，通過隊(duì)列慢慢的來對MySql進(jìn)行寫操作，緩沖了MySql的寫壓力

MySQL的并發(fā)控制優(yōu)化

LBCC乔夯，基于鎖的并發(fā)控制砖织，Lock Based Concurrency Control。

使用鎖的機(jī)制末荐，在當(dāng)前事務(wù)需要對數(shù)據(jù)修改時(shí)侧纯，將當(dāng)前事務(wù)加上鎖，同一個(gè)時(shí)間只允許一條事務(wù)修改當(dāng)前數(shù)據(jù)甲脏，其他事務(wù)必須等待鎖釋放之后才可以操作眶熬。性能太差

MVCC，多版本的并發(fā)控制剃幌，Multi-Version Concurrency Control聋涨。

MVCC的目的就是多版本并發(fā)控制，在數(shù)據(jù)庫中的實(shí)現(xiàn)负乡，就是為了解決讀寫沖突。使用鎖和鎖協(xié)議來實(shí)現(xiàn)相應(yīng)的隔離級別來進(jìn)行并發(fā)控制會因?yàn)殒i會造成事務(wù)阻塞脊凰。而多版本并發(fā)控制使得對同一行記錄做讀寫的事務(wù)之間不用相互阻塞等待抖棘，提高了事務(wù)的并發(fā)能力茂腥，可以認(rèn)為MVCC是一種解決讀寫阻塞等待的行級鎖。

MVCC的數(shù)據(jù)庫表中每一行數(shù)據(jù)都可能存在多個(gè)版本切省，對數(shù)據(jù)庫的任何修改的提交都不會直接覆蓋之前的數(shù)據(jù)最岗，而是產(chǎn)生一個(gè)新的版本與老版本共存，通過讀寫數(shù)據(jù)時(shí)讀不同的版本來避免加鎖阻塞

• 1朝捆、MVCC只支持(已提交讀)和(可重復(fù)讀)隔離級別般渡。
• 2、MVCC能解決臟讀芙盘、不可重復(fù)讀問題驯用，不能解決幻讀問題。
• 3儒老、MVCC是用來解決讀寫操作之間的阻塞問題蝴乔。

隱式字段

每行記錄除了我們自定義的字段外，還有數(shù)據(jù)庫隱式定義的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段

• DB_TRX_ID：數(shù)據(jù)行版本號：大小為6byte驮樊，記錄最近修改(修改/插入)事務(wù)ID薇正，記錄創(chuàng)建這條記錄/最后一次修改該記錄的事務(wù)ID
• DB_ROLL_PTR：刪除版本號：大小為7byte，記錄回滾指針囚衔，指向當(dāng)前記錄行的undo log信息(指向該數(shù)據(jù)的前一個(gè)版本數(shù)據(jù))
• DB_ROW_ID：行數(shù)據(jù)隱式id：大小為6byte挖腰，隱含的自增ID（隱藏主鍵），如果數(shù)據(jù)表沒有主鍵练湿，InnoDB會自動以DB_ROW_ID
  產(chǎn)生一個(gè)聚簇索引

ReadView

read view是讀視圖曙聂，其實(shí)就相當(dāng)于一種快照，里面記錄了系統(tǒng)中當(dāng)前活躍事務(wù)的ID以及相關(guān)信息鞠鲜，主要用途是用來做可見性判斷宁脊，判斷當(dāng)前事務(wù)是否有資格訪問該行數(shù)據(jù)。read view有多個(gè)變量：

• trx_ids: 它里面的trx_ids變量存儲了活躍事務(wù)列表贤姆，也就是Read View開始創(chuàng)建時(shí)其他未提交的活躍事務(wù)的ID列表榆苞。例如事務(wù)A在創(chuàng)建read view(快照)時(shí)，數(shù)據(jù)庫中事務(wù)B和事務(wù)C還沒提交或者回滾結(jié)束事務(wù)霞捡，此時(shí)trx_ids就會將事務(wù)B和事務(wù)C的事務(wù)ID記錄下來坐漏。

假設(shè)當(dāng)前事務(wù)生成了一個(gè)ReadView，trx_ids列表里的事務(wù)id為[60,100]碧信。

1赊琳、如果你要訪問的記錄版本的事務(wù)id為50，比當(dāng)前列表最小的id 60還小砰碴，那說明這個(gè)事務(wù)在ReadView生成之前就提交了躏筏，所以對當(dāng)前活動的事務(wù)來說是可訪問的。
2呈枉、如果你要訪問的記錄版本的事務(wù)id為70,發(fā)現(xiàn)此事務(wù)在列表id最大值和最小值之間趁尼，那就再判斷一下 70 這個(gè)id是否在列表內(nèi)埃碱，如果在那就說明此事務(wù)還未提交，所以版本不能被訪問酥泞。如果不在那說明事務(wù)已經(jīng)提交砚殿，所以版本可以被訪問。
3芝囤、如果你要訪問的記錄版本的事務(wù)id為110似炎，那比事務(wù)列表最大id100都大，那說明這個(gè)版本是在ReadView生成之后才發(fā)生的悯姊，所以不能被訪問羡藐。

Undo log

Undo log中存儲的是老版本數(shù)據(jù)，當(dāng)一個(gè)事務(wù)需要讀取記錄行時(shí)挠轴，如果當(dāng)前記錄行不可見传睹，可以通過回滾指針順著undo log鏈找到滿足其可見性條件的記錄行版本。

在InnoDB里岸晦，undo log分為如下兩類：
①insert undo log : 事務(wù)對insert新記錄時(shí)產(chǎn)生的undo log, 只在事務(wù)回滾時(shí)需要, 并且在事務(wù)提交后就可以立即丟棄欧啤。
②update undo log : 事務(wù)對記錄進(jìn)行delete和update操作時(shí)產(chǎn)生的undo log，在事務(wù)回滾時(shí)需要

實(shí)際還有一個(gè)刪除flag隱藏字段, 既記錄被更新或刪除并不代表真的刪除启上，而是刪除flag變了

插入：獲取最新的事務(wù)版本號n,保存n到對應(yīng)行的行版本號

刪除：獲取最新的事務(wù)版本號n邢隧，保存到對應(yīng)行的刪除版本號

修改：變?yōu)閕nsert和delete操作的組合，先獲取最新的事務(wù)版本號n冈在，然后進(jìn)行數(shù)據(jù)行拷貝倒慧，插入拷貝的數(shù)據(jù)，保存n到新插入數(shù)據(jù)行行版本號的字段中包券，然后保存n到舊的數(shù)據(jù)行的刪除版本號字段中

查詢：獲取最新的事務(wù)版本號n,查詢行版本號小于或者等于n的行數(shù)據(jù)纫谅，防止讀到其他事務(wù)提交的數(shù)據(jù)

MVCC實(shí)現(xiàn)過程原理主要的原理：

參考：https://baijiahao.baidu.com/s?id=1629409989970483292&wfr=spider&for=pc

版本記錄都是去版本鏈里面找的，然后根據(jù)不同隔離級別生成的ReadView就會有所不同

• 例如：在一個(gè)讀已提交或者是重復(fù)讀的級別事務(wù)中
  

  有一個(gè)事務(wù)id為100的事務(wù)溅固，修改了name,使得的name等于小明2付秕，但是事務(wù)還沒提交。則此時(shí)的版本鏈如下：
  此時(shí)另一個(gè)事務(wù)發(fā)起了select 語句要查詢id為1的記錄侍郭，那此時(shí)生成的ReadView 列表就是[100]询吴。最新版本100是活躍事務(wù)不能訪問，那就得使用版本鏈去找了亮元，首先找100的下個(gè)版本name為小明1的記錄猛计，發(fā)現(xiàn)trx_id是60，小于列表中的最小id,所以可以訪問爆捞，直接訪問結(jié)果為小明1奉瘤。那這時(shí)候我們把事務(wù)id為100的事務(wù)提交了，并且新建了一個(gè)事務(wù)id為110也修改id為1的記錄嵌削，name修改為小明3毛好，并且不提交事務(wù)

  這時(shí)候之前那個(gè)select事務(wù)又執(zhí)行了一次查詢,要查詢id為1的記錄望艺。這時(shí)候會發(fā)生兩種情況

• 1苛秕、已提交讀隔離級別
  會重新一個(gè)生成一個(gè)ReadView肌访，那你的活動事務(wù)列表中的值就變了，變成了[110]艇劫，通過版本鏈查trx_id對比吼驶，查到的只能是小明2。
• 2店煞、可重復(fù)讀隔離級別
  ReadView還是第一次select時(shí)候生成的ReadView,也就是列表的值還是[100]蟹演。所以select的結(jié)果是小明1。所以第二次select結(jié)果和第一次一樣顷蟀，所以叫可重復(fù)讀酒请！

也就是說已提交讀隔離級別下的事務(wù)在每次查詢的開始都會生成一個(gè)獨(dú)立的ReadView,而可重復(fù)讀隔離級別則在第一次讀的時(shí)候生成一個(gè)ReadView，之后的讀都復(fù)用之前的ReadView鸣个。

當(dāng)前讀
需要特別注意的是在MVCC下的可重復(fù)讀在讀操作是防止了幻讀羞反，讀操作下完全就是按照ReadView進(jìn)行的快照讀。但是對于會對數(shù)據(jù)的操作例如：

select * from .... where ... for update 
select * from .... where ... lock in share mode 
update .... set .. where ... 
delete from. . where ..

都是采用當(dāng)前讀的模式囤萤。在執(zhí)行這幾個(gè)操作時(shí)會讀取最新的記錄昼窗，即使是別的事務(wù)提交的數(shù)據(jù)也可以查詢到。假設(shè)要update一條記錄涛舍，但是在另一個(gè)事務(wù)中已經(jīng)delete掉這條數(shù)據(jù)并且commit了澄惊，如果update就會產(chǎn)生沖突，所以在update的時(shí)候需要知道最新的數(shù)據(jù)富雅。如果事務(wù)中都使用快照讀掸驱，那么就不會產(chǎn)生幻讀現(xiàn)象，但是快照讀和當(dāng)前讀混用就會產(chǎn)生幻讀没佑。