1.工具

1.mysql：8.0.25
2.msyqlworkbench

2.成本定義

執(zhí)行sql查詢所需要花費(fèi)的代價(jià)

3.查看成本的方式

執(zhí)行一條示例語句，如下：

select sql_no_cache suser.id,suser.name ,srole.name from sys_user suser
 inner join sys_user_role surole on suser.id=surole.user_id
 inner join sys_role srole on surole.role_id=srole.id;

sql_no_cache:告訴mysql服務(wù)器不緩存這條語句的執(zhí)行結(jié)果

執(zhí)行完上面的sql語句后，再執(zhí)行以下語句查看查詢成本：

show status like 'last_query_cost';

執(zhí)行結(jié)果截圖如下：

查詢成本.png

不過，workbench可以直接在執(zhí)行計(jì)劃中展示查詢成本，截圖如下：

workbench查看查詢成本和執(zhí)行計(jì)劃.png

從執(zhí)行計(jì)劃中可以看到：
1.執(zhí)行計(jì)劃的第一步是查詢stole表，而且是全表查詢；
2.執(zhí)行計(jì)劃的第二部是查詢surole表，也是全表查詢；
3.執(zhí)行計(jì)劃的第三部是查詢suser表，通過聚集索引查詢，所以精確查找出一條匹配的數(shù)據(jù)；
4.srole表和surole表通過hash join關(guān)聯(lián)查詢數(shù)據(jù)，最終查出12條匹配的數(shù)據(jù),然后和suer表的查詢結(jié)果進(jìn)行嵌套循環(huán)查詢，前臺(tái)循環(huán)查詢的成本計(jì)算公式很簡單，就是將潛逃的字查詢的查詢成本進(jìn)行累加求和；
5.sql語句中，suser表是主表，然后依次關(guān)聯(lián)surole表和srole表。但是，執(zhí)行計(jì)劃是先查詢srole表，再查詢surole表，最后查詢suser表，兩者順序不同；
6.這是mysql優(yōu)化器最終選擇的它認(rèn)為最優(yōu)的執(zhí)行計(jì)劃；

4.sql的第二種寫法

上面的sql可以用另一種寫法，然后我們再看看新寫法的查詢成本
以下是新的寫法：

select straight_join suser.id,suser.name ,srole.name from sys_user suser
 inner join sys_user_role surole on suser.id=surole.user_id
 inner join sys_role srole on surole.role_id=srole.id;

straight_join: 讓mysql優(yōu)化器按照sql的join順序來查詢數(shù)據(jù)
現(xiàn)在我們再看一下查詢成本及執(zhí)行計(jì)劃：

查詢成本二.png

從上圖可知：
1.現(xiàn)在的sql查詢數(shù)據(jù)的順序和執(zhí)行計(jì)劃是一致的；
2.最終查詢成本是42.05，比優(yōu)化器選擇的執(zhí)行計(jì)劃的成本要高很多；

5.總結(jié)

1.從sql語句和執(zhí)行計(jì)劃可以看出，suser表全表只有12數(shù)據(jù)，srole表全表有4條數(shù)據(jù)，surole表全表有30條數(shù)據(jù)，如果suser表和srole表之間有關(guān)聯(lián)字段的話，就能讓這兩張表做hash join關(guān)聯(lián)查詢，最后在與surole表做潛逃循環(huán)查詢，這樣的話，成本能比現(xiàn)在更低，但是，實(shí)際上，suser表和srole表之間并沒有關(guān)聯(lián)字段，所以這種假設(shè)不成立，感覺是在說廢話...;
2.大多數(shù)情況下，優(yōu)化器選擇的執(zhí)行計(jì)劃都是查詢成本最低的；

6.說明：

1.執(zhí)行成本：執(zhí)行成本為42.05的意思是，mysql認(rèn)為大概需要做42個(gè)數(shù)據(jù)頁的隨機(jī)查找才能完成查詢；
2.執(zhí)行成本來源：執(zhí)行成本是根據(jù)一系列的統(tǒng)計(jì)信息得來的，包括：每個(gè)表活著索引的頁面?zhèn)€數(shù)、索引的基數(shù)（索引中不同值的數(shù)量）、索引和數(shù)據(jù)行的長度、索引分布情況；
3.優(yōu)化器在評(píng)估成本的時(shí)候不會(huì)評(píng)估任何層面的緩存，包括mysql服務(wù)器內(nèi)部的緩存，它假設(shè)讀取任何數(shù)據(jù)都需要一次磁盤I/O；

7.mysql優(yōu)化器在哪些情況戲會(huì)選擇錯(cuò)誤的（非最優(yōu)的）執(zhí)行計(jì)劃

• 統(tǒng)計(jì)信息不準(zhǔn)確。mysql服務(wù)器依賴存儲(chǔ)引擎提供的統(tǒng)計(jì)信息來評(píng)估成本，但是有的存儲(chǔ)引擎提供的信息是準(zhǔn)確的，比如myisam,有的則不準(zhǔn)確，比如innodb。
• 執(zhí)行計(jì)劃中的成本估算不等同于實(shí)際執(zhí)行的成本。即使統(tǒng)計(jì)信息準(zhǔn)確，優(yōu)化器給出的執(zhí)行計(jì)劃也可能不是最優(yōu)的。有時(shí)候某個(gè)查詢雖然需要讀取更多的數(shù)據(jù)頁，但是這些數(shù)據(jù)頁都是順序讀活著已經(jīng)在內(nèi)存中，導(dǎo)致它的成本會(huì)更低。mysql并不知道哪些數(shù)據(jù)頁是在內(nèi)存中，哪些數(shù)據(jù)頁是在磁盤上，所以查詢在實(shí)際執(zhí)行過程中的物理I/O次數(shù)是無從得知的。
• mysql的最優(yōu)和我們想要的最優(yōu)可能不同。我們想要的最優(yōu)的執(zhí)行計(jì)劃必然是能讓查詢最快的，但mysql是基于成本模型選擇最優(yōu)的執(zhí)行計(jì)劃。
• mysql并不考慮查詢兵法執(zhí)行的情況。
• mysql并不都是基于成本的優(yōu)化，有時(shí)也會(huì)基于一些固定的規(guī)則。比如，存在全文搜索的match()子句，當(dāng)有全文索引的時(shí)候，優(yōu)化器就會(huì)選擇全文索引來執(zhí)行查詢，即使用別的索引和where條件的查詢會(huì)更快。
• mysql不會(huì)考慮不受其控制的操作的成本。比如我么自定義的函數(shù)及存儲(chǔ)過程。