ABP(ASP.NET Boilerplate Project)快速入門

前言

這两天看了一下ABP,做個簡單的學習記錄。記錄主要有以下內容:

  1. 從官網創建並下載項目(.net core 3.x + vue)
  2. 項目在本地成功運行
  3. 新增實體並映射到數據庫
  4. 完成對新增實體的基本增刪改查

ABP官網:https://aspnetboilerplate.com/
Github:https://github.com/aspnetboilerplate

創建項目

進入官網

Get started,選擇前後端技術棧,我這裏就選.net core 3.x和vue。

填寫自己的項目名稱,郵箱,然後點create my project就可以下載項目了。

解壓文件

運行項目

後端項目

首先運行後端項目,打開/aspnet-core/MyProject.sln

改一下MyProject.Web.Host項目下appsettings.json的數據庫連接字符串,如果本地安裝了mssql,用windows身份認證,不改也行

數據庫默認是使用mssql的,當然也可以改其他數據庫。

將MyProject.Web.Host項目設置為啟動項,打開程序包管理器控制台,默認項目選擇DbContext所在的項目,也就是MyProject.EntityFrameworkCore。執行update-database

數據庫已成功創建:

Ctrl+F5,不出意外,瀏覽器就會看到這個界面:

前端項目

後端項目成功運行了,下面運行一下前端項目,先要確保本機有nodejs環境並安裝了vue cli,這個就不介紹了。

/vue目錄下打開cmd執行:npm install

install完成后執行:npm run serve

打開瀏覽器訪問http://localhost:8080/,不出意外的話,會看到這個界面:

使用默認用戶 admin/123qwe 登錄系統:

至此,前後端項目都已成功運行。
那麼基於abp的二次開發該從何下手呢,最簡單的,比如要增加一個數據表,並且完成最基本CRUD該怎麼做?

新增實體

實體類需要放在MyProject.Core項目下,我新建一個MyTest文件夾,並新增一個Simple類,隨意給2個屬性。

我這裏繼承了abp的Entity 類,Entity類有主鍵ID屬性,這個泛型int是指主鍵的類型,不寫默認就是int。abp還有一個比較複雜的FullAuditedEntity類型,繼承FullAuditedEntity的話就有創建時間,修改時間,創建人,修改人,軟刪除等字段。這個看實際情況。

public class Simple : Entity<int>
{
    public string Name { get; set; }

    public string Details { get; set; }
}

修改MyProject.EntityFrameworkCore項目的/EntityFrameworkCore/MyProjectDbContext:

public class MyProjectDbContext : AbpZeroDbContext<Tenant, Role, User, MyProjectDbContext>
{
    /* Define a DbSet for each entity of the application */

    public DbSet<Simple> Simples { get; set; }

    public MyProjectDbContext(DbContextOptions<MyProjectDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Simple>(p =>
        {
            p.ToTable("Simples", "test");
            p.Property(x => x.Name).IsRequired(true).HasMaxLength(20);
            p.Property(x => x.Details).HasMaxLength(100);
        });
    }
}

然後就可以遷移數據庫了,程序包管理器控制台執行:add-migration mytest1update-database

刷新數據庫,Simples表已生成:

實體的增刪改查

進入MyProject.Application項目,新建一個MyTest文件夾

Dto

CreateSimpleDto,新增Simple數據的傳輸對象,比如ID,創建時間,創建人等字段,就可以省略

public class CreateSimpleDto
{
    public string Name { get; set; }

    public string Details { get; set; }
}

PagedSimpleResultRequestDto,分頁查詢對象

public class PagedSimpleResultRequestDto : PagedResultRequestDto
{
    /// <summary>
    /// 查詢關鍵字
    /// </summary>
    public string Keyword { get; set; }
}

SimpleDto,這裏跟CreateSimpleDto的區別就是繼承了EntityDto,多了個ID屬性

public class SimpleDto : EntityDto<int>
{
    public string Name { get; set; }

    public string Details { get; set; }
}

SimpleProfile,用來定義AutoMapper的映射關係清單

public class SimpleProfile : Profile
{
    public SimpleProfile()
    {
        CreateMap<Simple, SimpleDto>();
        CreateMap<SimpleDto, Simple>();
        CreateMap<CreateSimpleDto, Simple>();
    }
}

Service

注意,類名參考abp的規範去命名。

ISimpleAppService,Simple服務接口。我這裏繼承IAsyncCrudAppService,這個接口中包含了增刪改查的基本定義,非常方便。如果不需要的話,也可以繼承IApplicationService自己定義

public interface ISimpleAppService : IAsyncCrudAppService<SimpleDto, int, PagedSimpleResultRequestDto, CreateSimpleDto, SimpleDto>
{

}

SimpleAppService,Simple服務,繼承包含了增刪改查的AsyncCrudAppService類,如果有需要的話可以override這些增刪改查方法。也可以繼承MyProjectAppServiceBase,自己定義。

public class SimpleAppService : AsyncCrudAppService<Simple, SimpleDto, int, PagedSimpleResultRequestDto, CreateSimpleDto, SimpleDto>, ISimpleAppService
{
    public SimpleAppService(IRepository<Simple, int> repository) : base(repository)
    {

    }

    /// <summary>
    /// 條件過濾
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    protected override IQueryable<Simple> CreateFilteredQuery(PagedSimpleResultRequestDto input)
    {
        return Repository.GetAll()
            .WhereIf(!input.Keyword.IsNullOrWhiteSpace(), a => a.Name.Contains(input.Keyword));
    }
}

接口測試

重新運行項目,不出意外的話,Swagger中就會多出Simple相關的接口。

  • Create

  • Get

  • GetAll

  • Update

  • Delete

總結

ABP是一個優秀的框架,基於ABP的二次開發肯定會非常高效,但前提是需要熟練掌握ABP,弄清楚他的設計理念以及他的一些實現原理。

以後有時間的話再深入學習一下。文中如果有不妥之處歡迎指正。

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

網頁設計公司推薦不同的風格,搶佔消費者視覺第一線

※廣告預算用在刀口上,台北網頁設計公司幫您達到更多曝光效益

※自行創業缺乏曝光? 網頁設計幫您第一時間規劃公司的形象門面

南投搬家公司費用需注意的眉眉角角,別等搬了再說!

新北清潔公司,居家、辦公、裝潢細清專業服務

世界最髒河水可飲用 印尼拚2025乾淨家園

摘錄自2019年11月18日中央通訊社報導

西爪哇的西大魯河曾被稱為世界上最髒的河流,印尼從2年前整治至今,部分上游河水已可供居民飲用。印尼官員說,乾淨印尼是全國性運動,將持續動員全民達到2025年的目標。

印尼總統佐科威(Joko Widodo)在2017年宣示,2025年將達成讓印尼乾淨的目標(Gerakan Indonesia Bersih),包括垃圾從源頭減量3成、並處理7成的垃圾,以避免垃圾直接放置於堆積場或流入海洋。

這是印尼首度、也是唯一動員軍隊清理的河川。負責清理的少將蘇山托(Susanto)之前表示,上游整治後,經養魚測試水質6個月前設置第一台淨水機器,已開始供居民使用。

印尼人口2億6700萬,因垃圾清運不普及、缺乏處理機制,也沒有回收概念,大量垃圾不是丟河裡,就是掩埋或燒掉。許多都市貧民社區及鄉下民眾取用浸泡垃圾的河水作日常使用,暴露在遭垃圾污染的環境及空氣中,衛生條件極差。

輿論普遍分析,印尼乾淨運動是遠大、有難度的目標。印尼海洋事務統籌部海洋科學與科技主任納尼說,政府已列出67條優先治理的河川,以減少海洋垃圾;密集與地方政府合作推動減量,目前已有峇里島等12個地方政府制定限制使用一次性塑膠產品;也研擬嚴懲與觀光有關的行為造成的垃圾污染;並尋求新科技的運用。

本站聲明:網站內容來源環境資訊中心https://e-info.org.tw/,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

網頁設計公司推薦不同的風格,搶佔消費者視覺第一線

※廣告預算用在刀口上,台北網頁設計公司幫您達到更多曝光效益

※自行創業缺乏曝光? 網頁設計幫您第一時間規劃公司的形象門面

南投搬家公司費用需注意的眉眉角角,別等搬了再說!

新北清潔公司,居家、辦公、裝潢細清專業服務

邏輯式編程語言極簡實現(使用C#) – 1. 邏輯式編程語言介紹

相信很多朋友對於邏輯式編程語言,都有一種最熟悉的陌生人的感覺。一方面,平時在書籍、在資訊網站,偶爾能看到一些吹噓邏輯式編程的話語。但另一方面,也沒見過周圍有人真正用到它(除了SQL)。

遙記當時看《The Reasoned Schemer》(一本講邏輯式編程語言的小人書),被最後兩頁的解釋器實現驚艷到了。看似如此複雜的計算邏輯,其實現竟然這麼簡潔。不過礙於當時水平有限,也就囫圇吞棗般看了過去。後來有一天,不知何故腦子靈光一閃,把圖遍歷和流計算模式聯繫在一起,瞬間明白了《The Reasoned Schemer》中的做法。動手寫了寫代碼,果然如此,短短兩百來行代碼,就完成了解釋器的實現,才發現原來如此簡單。很多時候,並非問題本身有多難,只是沒有想到正確的方法。

本系列將盡可能簡潔地說明邏輯式編程語音的原理,並實現一門簡單的邏輯式編程語言。考慮到C#的用戶較多,因此選擇用C#來實現。實現的這門語言就叫NMiniKanren。文章總體內容如下:

  • NMiniKanren語言介紹
    • 語言基礎
    • 一道有趣的邏輯題:誰是兇手
  • NMiniKanren運行原理
    • 構造條件關係圖,遍歷分支
    • 代入消元法解未知量
  • 實現NMiniKanren
    • 流計算模式簡介
    • 代入消元法的實現
    • 遍歷分支的實現

故事從兩個正在吃午餐的程序員說起。

老明和小皮是就職於同一家傳統企業的程序員。這天,兩人吃着午餐。老明邊吃邊刷着抖音,鼻孔時不時噴出幾條米粉。

小皮是一臉麻木地刷着求職網和資訊網,忽然幾個大字映入眼底:《新型邏輯式編程語言重磅出世,即將顛覆IT界!》小皮一陣好奇,往下一翻,結果接着的是一些難懂的話,什麼“一階邏輯”,什麼“合一算法”,以及鬼畫符似的公式之類。

小皮看得索然無味,但被勾引起來的對邏輯式編程的興趣彷彿澳洲森林大火一樣難以平息。於是伸手拍下老明高举手機的左手,問道:“嘿!邏輯式編程有了解過么?是個啥玩意兒?”

“邏輯式編程啊……嘿嘿,前段時間剛好稍微了解了一下。”老明鼻孔朝天吸了兩口氣,“我說的稍微了解,是指實現了一門邏輯式編程語言。”

“不愧是資深老IT,了解也比別人深入一坨坨……”

“也就比你早來一年好不好……我是一邊看一本奇書一邊做的。Dan老師(Dan Friedman)寫的《The Reasoned Schemer》。這本書挺值得一看的,書中使用一門教學用的邏輯式編程語言,講解這門語言的特性、用法、以及原理。最後還給出了這門語言的實現。核心代碼只用了兩頁紙。

“所謂邏輯式編程,從使用上看是把聲明式編程發揮到極致的一種編程範式。普通的編程語言,大部分還是基於命令式編程,需要你告訴機器每一步執行什麼指令。而邏輯式編程的理念是,我們只需要告訴機器我們需要的目標,機器會根據這個目標自動探索執行過程。

邏輯式編程的特點是可以反向運行。你可以像做數學題一樣,聲明未知量,列出方程,然後程序會為你求解未知量。

“挺神奇的。聽起來有點像AI編程。不過這麼高級的東西怎麼沒有流行起來?感覺可以節省不少人力。”小皮忽然有種飯碗即將不保的感覺。

“嘿嘿……想得美。其實邏輯式編程,既不智能,也不好用。你回憶一下你中學的時候是怎麼解方程組的?”

“嗯……先盯一會方程組,看看它長得像不像有快捷解法的樣子。看不出來的話就用代入法慢慢算。這和邏輯式編程有什麼關係?”

邏輯式編程並不智能,它只是把某種類似代入法的通用算法內置到解釋器里。邏輯式編程語言寫的程序運行時,不過是根據通用算法進行求解而已。它不像人一樣會去尋找更快捷的方法,同時也不能解決超綱問題。

而且邏輯式編程語言的學習成本也不低。如果你要用好這門語言,你得把它使用的通用算法搞清楚。雖然你寫的聲明式的代碼,但內心要時刻清楚程序的執行過程。如果你拿它當個黑盒來用,那很可能你寫出來的程序的執行效率會非常低,甚至跑出一些莫名其妙的結果。”

“哦哦,要學會用它,還得先懂得怎麼實現它。這學習成本還挺高的。”小皮跟着吐槽,不過他知道老明表明上看似嫌棄邏輯式編程的實用性,私底下肯定玩得不亦樂乎,並且也喜歡跟別人分享。於是小皮接着道:“雖然應該是用不着,但感覺挺有意思的,再仔細講講唄。天天寫CRUD,腦子都淡出個鳥了。”

果然老明坐直起來:“《The Reasoned Schemer》用的這門邏輯式編程語言叫miniKanren,用Scheme/Lisp實現的。去年給你安利過Scheme了,現在掌握得怎麼樣?”

“一竅不通……”小皮大窘。去年到現在,小皮一直很忙,並沒有自學什麼東西。如果沒有外力驅動的話,他還將一直忙下去。

“果然如此。所以我順手也實現了個C#魔改版本的miniKanren。就叫NMiniKanren。我把NMiniKanren實現為C#的一個DSL。這樣的好處是方便熟悉C#或者Java的人快速上手;壞處是DSL會受限於C#語言的能力,代碼看起來沒有Scheme版那麼優雅。”老明用左手做了個打引號的動作,“先從簡單的例子開始吧。比如說,有個未知量q,我們的目標是讓q等於5或者等於6。那麼滿足條件的q值有哪些?”

“不就是5和6么……這也太簡單了吧。”

“Bingo!”老明打了個響指,“我們先用簡單的例子看看代碼結構。”只見老明兩指輕輕夾住一隻筷子,勾出幾條米粉,快速在桌上擺出如下代碼:

// k提供NMiniKanren的方法,q是待求解的未知變量。
var res = KRunner.Run(null /* null表示輸出所有可能的結果 */, (k, q) =>
{
    // q == 5 或者 q == 6
    return k.Any(
        k.Eq(q, 5),
        k.Eq(q, 6));
});
KRunner.PrintResult(res);  // 輸出結果:[5, 6]

“代碼中,KRunner.Run用於運行一段NMiniKanren代碼,它的聲明如下。”老明繼續撥動米粉:

public class KRunner
{
    public static IList<object> Run(int? n, Func<KRunner, FreshVariable, Goal> body)
    {
        ...
    }
}

“其中,參數n是返回結果的數量限制,n = null表示無限制;參數body是一個函數:

  • 函數的第一個參數是一個KRunner實例,用於引用NMiniKanren方法;
  • 函數的第二個參數是我們將要求解的未知量;
  • 函數的函數體是我們編寫的NMiniKanren代碼;
  • 函數的返回值為需要滿足的約束條件。

“接着我們看函數體的代碼。k.Eq(q, 5)表示q需要等於5k.Eq(q, 6)表示q需要等於6k.Any表示滿足至少一個條件。整段代碼的意思為:求所有滿足q等於5或者q等於6q值。顯然答案為56,程序的運行結果也是如此。很神奇吧?”

“你這米粉打碼的功夫更讓我驚奇……”小皮仔細看了一會,“原來如此。不過這DSL的語法確實看着比較累。”

“主要是我想做得簡單一些。其實使用C#的Lambda表達式也可以實現像……”老明勾出幾條米粉擺出q == 5 || q == 6表達式,“……這樣的語法,不過這樣會增加NMiniKanren實現的複雜度。況且這無非是前綴表達式或中綴表達式這種語法層面的差別而已,語義上並沒有變化。學習應先抓住重點,花里胡哨的東西可以放到最後再來琢磨。

“嗯嗯。KRunner.Run里這個null的參數是做什麼用的呢?”

KRunner.Run的第一個參數用來限制輸出結果的數量。null表示輸出所有可能的結果。還是上面例子的條件,我們改成限制只輸出1個結果。”小皮用筷子改了下代碼:

// k提供NMiniKanren的方法,q是待求解的未知變量。
var res = KRunner.Run(1 /* 輸出1個結果 */, (k, q) =>
{
    // q == 5 或者 q == 6
    return k.Any(
        k.Eq(q, 5),
        k.Eq(q, 6));
});
KRunner.PrintResult(res);  // 輸出結果:[5]

“這樣程序只會輸出5一個結果。在一些包含遞歸的代碼中,可能會有無窮多個結果,這種情況下需要限制輸出結果的數量來避免程序不會終止。”

“原來如此。不過這個例子太簡單了,有沒有其他更好玩的例子。”

老明喝下一口湯,說:“好。時間不早了,我們回公司找個會議室慢慢說。”

NMiniKanren支持的數據類型

到公司后,老明的講課開始了……

首先,要先明確NMiniKanren支持的數據類型。後續代碼都要基於數據類型來編寫,所以規定好數據類型是基礎中的基礎。

簡單起見,NMiniKanren只支持四種數據類型:

  • string:就是一個普普通通的值類型,僅有值相等判斷。
  • int:同string。使用int是因為有時候想少寫兩個雙引號……
  • KPair:二元組。可用來構造鏈表及其他複雜的數據結構。如果你學過Lisp會對這個數據結構很熟悉。下面詳細說明。
  • null:這個類型只有null一個值。表示空引用或者空數組。

KPair類型

KPair的定義為:

public class KPair
{
    public object Lhs { get; set; }
    public object Rhs { get; set; }
    
    // methods
    ...
}

KPair除了用作二元組(其實是最少用的)外,更多的是用來構造鏈表。構造鏈表時,約定一個KPair作為一個鏈表的節點,Lhs為元素值,Rhs為一下個節點。當Rhsnull時鏈表結束。空鏈表用null表示。

public static KPair List(IEnumerable<object> lst)
{
    var fst = lst.FirstOrDefault();
    if (fst == null)
    {
        return null;
    }
    return new KPair(fst, List(lst.Skip(1)));
}

使用null表示空鏈表其實並不合適,這裏純粹是為了簡單而偷了個懶。

我們知道,很多複雜的數據結構都是可以通過鏈表來構造的。所以雖然NMiniKanren只有三種數據類型,但可以表達很多數據結構了。

這時候小皮有疑問了:“C#本身已經自帶了List等容器了,為什麼還要用KPair來構造鏈表?”

“為了讓底層盡可能簡潔。”老明說道,“我們都知道,程序本質上分為數據結構和算法。算法是順着數據結構來實現的。簡潔的數據結構會讓算法的實現顯得更清晰。相比C#自帶的List,使用KPair構造的鏈表更加清晰簡潔。按照構造的方式,我們的鏈表定義為:

  1. 空鏈表null
  2. 或者是非空鏈表。它的第一個元素為Lhs,並且Rhs是後續的鏈表。

“鏈表相關的算法都會順着定義的這兩個分支實現:一個處理空鏈表的分支,一個處理非空鏈表的遞歸代碼。比如說判斷一個變量是不是鏈表的方法:

public static bool IsList(object o)
{
    // 空鏈表
    if (o == null)
    {
        return true;
    }
    // 非空鏈表
    if (o is KPair p)
    {
        // 遞歸
        return IsList(p.Rhs);
    }
    // 非鏈表
    return false;
}

“以及判斷一個元素是不是在鏈表中的方法:

public static bool Memeber(object lst, object e)
{
    // 空鏈表
    if (lst == null)
    {
        return false;
    }
    // 非空鏈表
    if (lst is KPair p)
    {
        if (p.Lhs == null && e == null || p.Lhs.Equals(e))
        {
            return true;
        }
        else
        {
            // 遞歸
            return Memeber(p.Rhs, e);
        }
    }
    // 非鏈表
    return false;
}

“數據類型明確后,接下來我們來看看NMiniKanren能做什麼。”

目標(Goal)

編寫NMiniKanren代碼是一個構造目標(Goal類型)的過程。NMiniKanren解釋器運行時將求解使得目標成立的所有未知量的值

顯然,有兩個平凡的目標:

  • k.Succeed:永遠成立,未知量可取任意值。
  • k.Fail:永遠不成立,無論未知量為何值都不成立。

其中kKRunner的一個實例。C#跟Java一樣不能定義獨立的函數和常量,所以我們DSL需要的函數和常量就都定義為KRunner的方法或屬性。後面不再對k進行複述。

一個基本的目標是k.Eq(v1, v2)。這也是NMiniKanren唯一一個使用值來構造的目標,它表示值v1v2應該相等。也就是說,當v1v2相等時,目標k.Eq(v1, v2)成立;否則不成立。

這裏的相等,指的是值相等:

  • 不同類型不相等。
  • string類型相等當且僅當值相等。
  • KPair類型相等當且僅當它們的Lhs相等且Rhs相等。

KPair相等的定義,可以推出由KPair構造的數據結構(比如鏈表),相等條件為當且僅當它們結構一樣且對應的值相等。

接下來我們看幾個例子。

等於一個值

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    return k.Eq(q, 5);
}));  // 輸出[5]

直接q等於5

等於一個鏈表

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    return k.Eq(q, k.List(1, 2));
}));  // 輸出[(1 2)]

k.List(1, 2)相當於new KPair(1, new KPair(2, null)),用來快速構造鏈表。

鏈表間的相等

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    return k.Eq(k.List(1, q), k.List(1, 2));
}));  // 輸出[2]

這個例子比較像一個方程了。q匹配k.List(1, 2)的第二項,也就是2

無法相等的例子

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    return k.Eq(k.List(2, q), k.List(1, 2));
}));  // 輸出[]

由於k.List(2, q)的第一項和k.List(1, 2)的第一項不相等,所以這個目標無法成立,q沒有值。

不成立的例子

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    return k.Fail;
}));  // 輸出[]

目標無法成立,q沒有值。

永遠成立的例子

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    return k.Succeed;
}));  // 輸出[_0]

目標恆成立,q可取任意值。輸出_0表示一個可取任意值的自由變量。

更多構造目標的方式

目標可以看作布爾表達式,因此可以通過“與或非”運算,用簡單的目標構造成複雜的“組合”目標。我們把被用來構造“組合”目標的目標叫做該“組合”目標的子目標。

定義未知量

在前面的例子中,我們只有一個未知量qq既是未知量,也是程序輸出。

在處理更複雜的問題時,通常需要定義更多的未知量。定義未知量的方法是k.Fresh

// 定義x, y兩個未知量
var x = k.Fresh()
var y = k.Fresh()

新定義的未知量和q一樣,可以用來構造目標:

// x == 2
k.Eq(x, 2)
// x == y
k.Eq(x, y)

使用“與”運算組合的目標,僅當所有子目標成立時,目標才成立。

使用方法k.All來構造“與”運算組合的目標。

var g = k.All(g1, g2, g3, ...)

當且僅當g1, g2, g3, ……,都成立時,g才成立。

特別的,空子目標的情況,即k.All(),恆成立。

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    return k.All(
        k.Eq(q, 1),
        k.Eq(q, 2));
}));  // 輸出[]

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    var x = k.Fresh();
    var y = k.Fresh();
    return k.All(
        k.Eq(x, 1),
        k.Eq(y, x),
        k.Eq(q, k.List(x, y)));
}));  // 輸出[(1 1)]

使用“或”運算組合的目標,只要一個子目標成立時,目標就成立。

使用方法k.Any來構造“或”運算組合的目標。

var g = k.Any(g1, g2, g3, ...)

g1, g2, g3, ……中至少一個成立,g成立。

特別的,空子目標的情況,即k.Any(),恆不成立。

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    return k.Any(
        k.Eq(q, 5),
        k.Eq(q, 6));
}));  // 輸出[5, 6]

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    var x = k.Fresh();
    var y = k.Fresh();
    return k.All(
        k.Any(k.Eq(x, 5), k.Eq(y, 6)),
        k.Eq(q, k.List(x, y)));
}));  // 輸出[(5 _0), (_0 6)]

非?

MiniKanren(以及NMiniKanren)不支持“非”運算。支持“非”會讓miniKanren的實現複雜很多。

這或許令人驚訝。“與或非”在邏輯代數中一直像是連體嬰兒似的扎堆出現。並且“非”運算是單目運算符,看起來應該更簡單。

然而,“與”和“或”運算是在已知的兩(多)個集合中取交集或者並集,結果也是已知的。而“非”運算則是把一個已知的集合映射到可能未知的集合,遍歷“非”運算的結果可能會很久或者就是不可能的。

對於基於圖搜索和代入法求解的miniKanren來說,支持“非”運算需要對核心的數據結構和算法做較大改變。因此以教學為目的的miniKanren沒有支持“非”運算。

不過,在一定程度上,也是有不完整替代方法的。

If(這個比較奇葩,可以先跳過)

If是一個特殊的構造目標的方式。對應《The Reasoned Schemer》中的conda

var g = k.If(g1, g2, g3)

如果g1g2成立,那麼g成立;否則當且僅當g3成立時,g成立。

這個和k.Any(k.All(g1, g2), g3)很像,但他們是有區別的:

  • k.Any(k.All(g1, g2), g3)會解出所有讓k.All(g1, g2)或者g3成立的解
  • k.If(g1, g2, g3)如果k.All(g1, g2)有解,那麼只給出使k.All(g1, g2)成立的解;否則再求使得g3成立的解。

也可以說,If是短路的。

這麼詭異的特性有什麼用呢?

它可以部分地實現“非”運算的功能:

k.If(g, k.Fail, k.Succeed)

這個這裏先不詳細展開了,後面用到再說。

控制輸出順序

這是一個容易被忽略的問題。如果程序需要求出所有的解,那麼輸出順序影響不大。但是一些情況下,求解速度很慢,或者解的數量太多甚至無窮,這時只求前幾個解,那麼輸出的內容就和輸出順序有關了。

因為miniKanren以圖遍歷的方式來查找問題的解,所以解的順序其實也是解釋器運行時遍歷的順序。先看如下例子:

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    var x = k.Fresh();
    var y = k.Fresh();
    return k.All(
        k.Any(k.Eq(x, 1), k.Eq(x, 2)),
        k.Any(k.Eq(y, "a"), k.Eq(y, "b")),
        k.Eq(q, k.List(x, y)));
}));  // 輸出[(1 a), (1 b), (2 a), (2 b)]

有兩個未知變量xyx可能的取值為1或2,y可能的取值為a或b。可以看到,程序查找解的順序為:

  • x值為1
    • y值為a,q=(1 a)
    • y值為b,q=(1 b)
  • x值為2
    • y值為a,q=(2 a)
    • y值為b,q=(2 b)

如果要改變這個順序,我們有一個交替版的“與”運算k.Alli

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    var x = k.Fresh();
    var y = k.Fresh();
    return k.Alli(
        k.Any(k.Eq(x, 1), k.Eq(x, 2)),
        k.Any(k.Eq(y, "a"), k.Eq(y, "b")),
        k.Eq(q, k.List(x, y)));
}));  // 輸出[(1 a), (2 a), (1 b), (2 b)]

不過這個交替版也不是交替得很漂亮。下面增加x可能的取值到3個:

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    var x = k.Fresh();
    var y = k.Fresh();
    return k.Alli(
        k.Any(k.Eq(x, 1), k.Eq(x, 2), k.Eq(x, 3)),
        k.Any(k.Eq(y, "a"), k.Eq(y, "b")),
        k.Eq(q, k.List(x, y)));
}));  // 輸出[(1 a), (2 a), (1 b), (3 a), (2 b), (3 b)]

同樣,“或”運算也有交替版。

正常版:

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    return k.Any(
        k.Any(k.Eq(q, 1), k.Eq(q, 2)),
        k.Any(k.Eq(q, 3), k.Eq(q, 4)));
}));  // 輸出[1, 2, 3, 4]

交替版:

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    return k.Anyi(
        k.Any(k.Eq(q, 1), k.Eq(q, 2)),
        k.Any(k.Eq(q, 3), k.Eq(q, 4)));
}));  // 輸出[1, 3, 2, 4]

後面講到miniKanren實現原理時會解釋正常版、交替版為什麼會是這種表現。

遞歸

無遞歸,不編程!

遞歸給予了程序語言無限的可能。NMiniKanren也是支持遞歸的。下面我們實現一個方法,這個方法構造的目標要求指定的值或者未知量是一個所有元素都為1的鏈表。

錯誤的示範

一個值或者未知量的元素都為1,用遞歸的方式表達是:

  1. 它是一個空鏈表
  2. 或者它的第一個元素是1,且剩餘部分的元素都為1

直譯為代碼就是:

public static Goal AllOne_Wrong(this KRunner k, object lst)
{
    var d = k.Fresh();
    return k.Any(
        // 空鏈表
        k.Eq(lst, null),
        // 非空
        k.All(
            k.Eq(lst, k.Pair(1, d)),  // 第一個元素是1
            k.AllOne_Wrong(d)));  // 剩餘部分的元素都是1
}

直接運行這段代碼,死循環。

為什麼呢?因為我們直接使用C#的方法來定義函數,C#在構造目標的時候,會運行最後一行的k.AllOne_Wrong(d),於是就陷入死循環了。

正確的做法

為了避免死循環,在遞歸調用的地方,需要用k.Recurse方法特殊處理一下,讓遞歸的部分變為惰性求值,防止直接調用:

public static Goal AllOne(this KRunner k, object lst)
{
    var d = k.Fresh();
    return k.Any(
        k.Eq(lst, null),
        k.All(
            k.Eq(lst, k.Pair(1, d)),
            k.Recurse(() => k.AllOne(d))));
}

隨便構造兩個問題運行一下:

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    var x = k.Fresh();
    var y = k.Fresh();
    return k.All(
        k.AllOne(k.List(1, x, y, 1)),
        k.Eq(q, k.List(x, y)));
}));  // 輸出[(1 1)]

KRunner.PrintResult(KRunner.Run(null, (k, q) =>
{
    var x = k.Fresh();
    var y = k.Fresh();
    return k.All(
        k.AllOne(k.List(1, x, y, 0)),
        k.Eq(q, k.List(x, y)));
}));  // 輸出[]

k.Recurse這種處理方法其實是比較醜陋而且不好用的。特別是多個函數相互調用引起遞歸的情況,很可能會漏寫k.Recurse導致死循環。

聽到這裏,小皮疑惑道:“這個有點丑誒。剛剛網上瞄了下《The Reasoned Schemer》,發現人家的遞歸併不需要這種特殊處理。看起來直接調用就OK了,跟普通程序沒啥兩樣,很美很和諧。”

“因為《The Reasoned Schemer》使用Lisp的宏實現的miniKanren,宏的機制會有類似惰性計算的效果。”老明用擦白板的抹布拍了下小皮的腦袋,“可惜你不會Lisp。如果你不努力提升自己,那丑一點也只能將就着看了。”

關於數值計算

MiniKanren沒有直接支持數值計算。也就是說,miniKanren不能直接幫你解像2 + x = 5的這種方程。如果要直接支持數值計算,需要實現很多數學相關的運算和變換,會讓miniKanren的實現變得非常複雜。MiniKanren是教學性質的語言,只支持了最基本的邏輯判斷功能。

“沒有‘直接’支持。”小皮敏銳地發現了關鍵,“也就是可以間接支持咯?”

“沒錯!你想想,0和1是我們支持的符號,與和或也是我們支持的運算符!”老明興奮起來了。

“二進制?”

“是的!任何一本計算機組成原理教程都會教你怎麼做!這裏就不多說了,你可以自己回去試一下。”

“嗯嗯。我以前這門課學得還不錯,現在還記得大概是先實現半加器和全加器,然後構造加法器和乘法器等。”小皮幹勁十足,從底層開始讓他想起了小時候玩泥巴的樂趣。

“而且用miniKanren實現的不是一般的加法器和乘法器,是可以反向運行的加法器和乘法器。”

“有意思,晚上下班回去就去試試。”小皮真心地說。正如他下班回家躺床上后,就再也不想動彈一樣真心實意。

(注:《The Reasoned Schemer》第7章、第8章會講到相關內容。)

小結

“好了,NMiniKanren語言的介紹就先說到這裏了。”老明拍了拍手,看了看前面的例子,撇了撇嘴,“以C#的DSL方式實現出來果然丑很多,語法上都不一致了。不過核心功能都還在。”

“接下來就是最有意思的部分,NMiniKanren的原理了吧?”

“是的。不過在繼續之前,還有個問題。”

“啥問題?”

“中午米線都用來打碼了。現在肚子餓了,你要請我吃下午茶。”

NMiniKanren的源碼在:https://github.com/sKabYY/NMiniKanren

示例代碼在:https://github.com/sKabYY/NMiniKanren/tree/master/NMiniKaren.Tests

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

網頁設計公司推薦不同的風格,搶佔消費者視覺第一線

※廣告預算用在刀口上,台北網頁設計公司幫您達到更多曝光效益

※自行創業缺乏曝光? 網頁設計幫您第一時間規劃公司的形象門面

南投搬家公司費用需注意的眉眉角角,別等搬了再說!

新北清潔公司,居家、辦公、裝潢細清專業服務

特斯拉Q1財報優 今年或賣出5.5萬輛車

特斯拉6日公佈首季財報,銷售金額比去年同期增加 50%,營收高達 11 億美元,每股淨損 36 美分,優於分析師預期,股價盤後上漲 2.4%,上個月則已累積大漲達 13%。同時,特斯拉預計新的太陽能電池廠將在今年第 3 季啟用,明年第一季前汽車產能估計將快速暴增。   特斯拉首季賣出的車輛達 10045 輛,稍優於原先發佈的 10030 輛,預期 2015 年總銷售車輛將可達 5.5 萬輛。同時,特斯拉的最新車款 Model X 也將於第三季開始販售。特斯拉表示,受到美元強勢走升的影響,今年首季提列的匯損金額高達 2200 萬美元,預計第二季 Model X 的售價仍會受美元影響,因此其將在第三季後調漲部分歐洲市場的電動車售價。   目前特斯拉仍積極在美國擴產,第三季新的太陽能電池廠就即將啟用,汽車產能到了 2016 年首季可望大幅增加。去年馬斯克稱將在內華達州建造一個超級電池工廠,到了 2020 年將生產約 50 萬顆的鋰電池。

本站聲明:網站內容來源於EnergyTrend https://www.energytrend.com.tw/ev/,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

網頁設計公司推薦不同的風格,搶佔消費者視覺第一線

※廣告預算用在刀口上,台北網頁設計公司幫您達到更多曝光效益

※自行創業缺乏曝光? 網頁設計幫您第一時間規劃公司的形象門面

南投搬家公司費用需注意的眉眉角角,別等搬了再說!

新北清潔公司,居家、辦公、裝潢細清專業服務

Jmeter(十三) – 從入門到精通 – JMeter定時器 – 上篇(詳解教程)

1.簡介

  用戶實際操作時,並非是連續點擊,而是存在很多停頓的情況,例如:用戶需要時間閱讀文字內容、填表、或者查找正確的鏈接等。為了模擬用戶實際情況,在性能測試中我們需要考慮思考時間。若不認真考慮思考時間很可能會導致測試結果的失真。例如,估計的可支撐用戶數偏小。在性能測試中,訪問請求之間的停頓時間被稱之為思考時間,那麼如何模擬這種停頓呢?我們可以藉助JMeter的定時器實現。

  JMeter中的定時器一般被我們用來設置延遲與同步。定時器的執行優先級高於Sampler(取樣器),在同一作用域(例如控制器下)下有多個定時器存在時,每一個定時器都會執行,如果想讓某一定時器僅對某一Sampler有效,則可以把定時器加在此Sampler節點下。

2.預覽定時器

首先我們來看一下JMeter的定時器,路徑:線程組(用戶)->添加->定時器(Timer);我們可以清楚地看到JMeter5中共有9個定時器,如下圖所示:

如果上圖您看得不是很清楚的話,宏哥總結了一個思維導圖,關於JMeter5的邏輯控制器類型,如下圖所示: 

 通過以上的了解,我們對定時器有了一個大致的了解和認識。下面宏哥就給小夥伴或則童鞋們分享講解一些通常在工作中會用到的定時器。 

4.常用定時器詳解

這一小節,宏哥就由上而下地詳細地講解一下常用的定時器。

4.1Constant Timer

固定定時器,看名稱大家也知道是一個固定定時器,多用來模擬思考時間,顧名思義是:請求之間的間隔時間為固定值。

作用:通過ThreadDelay設定每個線程請求之前的等待時間(單位為毫秒)。注意:固定定時是有作用域的,放到線程組下其作用域是所有請求都會延遲固定器設置的時間,如果放到請求內,作用域是單個請求延遲時間(常用)。

1、我們先來看看這個Constant Timer長得是啥樣子,路徑:線程組 > 添加 > 定時器 > 固定定時器,如下圖所示: 

2、關鍵參數說明如下:

Name:名稱,可以隨意設置,甚至為空;

Comments:註釋,可隨意設置,可以為空;

Thread Delay(in milliseconds):線程等待時間,單位毫秒。

用法(場景),更真實的模擬用戶場景,需要設置等待時間,或是等待上一個請求的時間,才執行,給sampler之間的思考時間;

4.1.1實例

場景應用:性能測試中,根據用戶操作預估時間,或者需要等待一段時間來加載數據。
PS:在實際模擬用戶請求的過程中,會失去靈活性,不推薦大量使用

1、新建測試計劃,線程組下添加2個取樣器 訪問博客園首頁、訪問度娘,如下圖所示:

2、然後再添加固定定時器,設置延遲時間3000ms,即3s,如下圖所示:

3、配置好以後,點擊“保存”,運行JMeter,查看錶格結果(取樣器訪問博客園首頁和訪問度娘間隔3s),如下圖所示:

4.2Uniform Random Timer

統一(均勻)隨機定時器,也是讓線程暫停一個隨機時間,只不過力求隨機時間能夠更均勻,都會出現。均勻隨機定時器,顧名思義,它產生的延遲時間是個隨機值,而各隨機值出現的概率均等。總的延遲時間等於一個隨機延遲時間加上一個固定延遲時間,用戶可以設置隨機延遲時間和固定延遲時間。

作用:它產生的延遲時間是個隨機值,而各隨機值出現的概率均等。總的延遲時間等於一個隨機延遲時間加上一個固定延遲時間,用戶可以設置隨機延遲時間和固定延遲時間。每個線程的延遲時間是符合標準正態分佈的隨機時間停頓,那麼使用這個定時器,總延遲 = 高斯分佈值(平均0.0和標準偏差1.0)* 指定的偏差值+固定延遲偏移(Math.abs((this.random.nextGaussian() * 偏差值) + 固定延遲偏移))

總延遲時間 = 指定範圍內的隨機時間(在範圍內各隨機值等概率)+ 固定延遲時間

1、我們先來看看這Uniform Random Timer長得是啥樣子,路徑:線程組 > 添加 > 定時器 > 統一隨機定時器,如下圖所示: 

2、關鍵參數說明如下:

Name:名稱,可以隨意設置,甚至為空;

Comments:註釋,可隨意設置,可以為空;

Random Delay Maximum:最大隨機延遲時間;

Constant Delay Offset: 固定延遲時間。

4.2.1實例

1、新建測試計劃,線程組下添加2個取樣器 訪問博客園首頁、訪問度娘,如下圖所示:

2、然後再添加統一隨機定時器,設置延遲時間3s,如下圖所示:

3、配置好以後,點擊“保存”,運行JMeter,查看錶格結果(取樣器訪問博客園首頁和訪問度娘間隔4s = 1000ms + 3000ms),如下圖所示:

4.3Precise Throughput Timer

準確的吞吐量定時器,顧名思義,這個就是控制吞吐量的。和Constant Throughput Timer類似,但是能更精準的控制請求。區別就是Constant Throughput Timer根據時間來做定時器(到了多少秒就發請求);Precise Throughput Timer是根據吞吐量在做計時器(到了多少量就發請求)。也就是能做到控制請求的速度和個數。

1、我們先來看看這個Precise Throughput Timer長得是啥樣子,路徑:線程組 > 添加 > 定時器 > 準確的吞吐量定時器,如下圖所示: 

2、關鍵參數說明如下:

Name:名稱,可以隨意設置,甚至為空;

Comments:註釋,可隨意設置,可以為空;

Thread Delay:忽略子控制器,即子控制器失效,由交替控制器接管。

Target Throught:目標吞吐量

Throught Period:表示在多長時間內發送Target Throught指定的請求數(以秒為單位)

Test Druation:指定測試運行時間(以秒為單位)

Number of threads in the bath:用來設置集合點,等到指定個數的請求后併發執行

4.3.1實例

1、新建測試計劃,線程組(設置線程組,保證有足夠的時間)下添加2個取樣器 訪問博客園首頁(已禁用)、訪問度娘,如下圖所示:

2、然後再添加準確的吞吐量定時器,設置10個吞吐量,設置10s啟動完10個請求,設置運行時間20s,如下圖所示:

3、配置好以後,點擊“保存”,運行JMeter,查看錶格結果(大約用了20秒啟動了21個線程),如下圖所示:

4、設置集合點在Precise Throughput Timer中設置集合點為10,其它參數不變,如下圖所示:

5、在Thread Group中設置線程數為10,如下圖所示: 

6、配置好以後,點擊“保存”,運行JMeter,查看錶格結果(可以看到,每10個線程為1組,同時啟動。),如下圖所示: 

4.4Constant Throughput Timer

固定吞吐量定時器,這個定時器引入了變量暫停,通過計算使總吞吐量(以每分鐘去楊樹計)盡可能接近給定的数字。當然,如果服務器不能處理它,或者如果其他定時器或耗時的測試原件阻止它,那麼吞吐量將更低。
雖然計時器被稱為常數吞吐量定時器,但吞吐量值並不一定是常數。它可以根據變量或函數調用定義,並且可以在測試期間改變該值。通過以下多種方式都可以改變:
使用計數器變量
使用一個 __jexl3, __groovy 函數來提供一個變化的值
使用遠程BeeShell服務器更改Jmeter屬性
請注意,在測試期間,不應該頻繁地更改吞吐量值——新值生效需要一段時間。

常數吞吐量定時器作用:控制吞吐量(線上壓測時候,避免一下就上百上千的吞吐量影響線上性能,加上這個之後較安全,可以一點一點往上加); 按指定的吞吐量執行,以每分鐘為單位。計算吞吐量依據是最後一次線程的執行時延。

作用域:此定時器放在請求的下級,只對它的上級請求起作用

1、我們先來看看這個Constant Throughput Timer長得是啥樣子,路徑:線程組 > 添加 > 定時器 > 常數吞吐量定時器,如下圖所示: 

2、關鍵參數說明如下:

Name:名稱,可以隨意設置,甚至為空;

Comments:註釋,可隨意設置,可以為空;

Target throughput(in samples per minute):目標吞吐量。注意這裡是每分鐘發送的請求數,可以選擇作用的線程:當前線程、當前線程組、所有線程組等,具體含義如下:

this thread only: 設置每個線程的吞吐量。總的吞吐量=線程數*該值。

all active threads in current thread group:吞吐量被分攤到當前線程組所有的活動線程上。每個線程將根據上次運行時間延遲。

all active threads:吞吐量被分配到所有線程組的所有活動線程的總吞吐量。每個線程將根據上次運行時間延遲。在這種情況下,每個線程組需要一個具有相同設置的固定吞吐量定時器。(不常用)

all active threads in current thread group (shared):同上,但是每個線程是根據組中的線程的上一次運行時間來延遲。相當於線程組組內排隊。(不常用)

all active threads (shared):同上,但每個線程是根據線程的上次運行時間來延遲。相當於讓所有線程組整體排隊。(不常用)

 4.4.1實例

1、新建測試計劃,線程組下添加1個取樣器 訪問博客園首頁(已禁用)、訪問度娘,如下圖所示:

2、然後再添加常數吞吐量定時器,設置目標吞吐量為300,如下圖所示:

3、配置好以後,點擊“保存”,運行JMeter,查看jp@gc – Transactions per Second(常數吞吐量定時器設置300/分鐘,也就是5/秒,故tps最大5,這裏的tps大約都是5,說明已經超過5,可以往上增加了),如下圖所示:

5. 定時器的作用域

1. 定時器是在每個sampler(採樣器)之前執行的,而不是之後(無論定時器位置在sampler之前還是下面);
2. 當執行一個sampler之前時,所有當前作用域內的定時器都會被執行;
3. 如果希望定時器僅應用於其中一個sampler,則把定時器作為子節點加入;
4. 如果希望在sampler執行完之後再等待,則可以使用Test Action;

6.小結

6.1安裝插件管理

1、安裝前查看選項,沒有看到插件管理,如下圖所示:

2、想安裝一個jmeter的插件,到官網(http://jmeter-plugins.org)上去下載插件安裝包,但是頁面一直都是搜索狀態,如下圖所示:

3、然後宏哥找了一個下載一個jmeter的插件管理工具 地址: http://jmeter-plugins.org/get/

4、將下載的文件拷貝的你的JMeter根目錄下的 lib/ext 目錄,如下圖所示:

5、 重啟jmeter,在選項中可以看到插件管理工具已經安裝成功,如下圖所示:

 6、勾選要下載的插件,點擊Apply changes and restart JMeter按鈕就完成了

Installed Plugins:用於查看已安裝的插件,並可通過 取消勾選 – 應用操作 來卸載插件

Available Plugins:用於查看和安裝可用的插件,通過 勾選-應用操作(右下側有按鈕Apply changes and restart JMeter) 來安裝插件

Upgrades:用於升級插件

   好了,今天關於定時器的上篇就講解到這裏,這一篇主要介紹了 Constant TimerUniform Random TimerPrecise Throughput Timer Constant Throughput Timer。感謝你耐心的閱讀和學習。

 

您的肯定就是我進步的動力。如果你感覺還不錯,就請鼓勵一下吧!記得隨手點波  推薦  不要忘記哦!!!

別忘了點 推薦 留下您來過的痕迹

 

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

網頁設計公司推薦不同的風格,搶佔消費者視覺第一線

※廣告預算用在刀口上,台北網頁設計公司幫您達到更多曝光效益

※自行創業缺乏曝光? 網頁設計幫您第一時間規劃公司的形象門面

南投搬家公司費用需注意的眉眉角角,別等搬了再說!

新北清潔公司,居家、辦公、裝潢細清專業服務

豐田凱美瑞廢舊車載電池回收妙計:為美國黃石國家公園供電

近日,豐田汽車與美國黃石國家公園合作,推出零排放能源系統,將凱美瑞混合動力車的廢舊電池用於國家公園設施供電,不僅延長了車載電池的使用壽命,同時節約能源,降低污染。  

(圖片來源:cnbeta)   此零排放能源系統主要由凱美瑞廢舊電池與一個太陽能系統構成,以滿足美國黃石國家公園Lamar Buffalo Ranch園區內五棟建築的日常用電需求。其日常電能主要由太陽能供電系統提供。當該系統進行能源補充的時候,凱美瑞的208塊廢舊鎳氫電池組便開始工作,互為補充。

本站聲明:網站內容來源於EnergyTrend https://www.energytrend.com.tw/ev/,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

網頁設計公司推薦不同的風格,搶佔消費者視覺第一線

※廣告預算用在刀口上,台北網頁設計公司幫您達到更多曝光效益

※自行創業缺乏曝光? 網頁設計幫您第一時間規劃公司的形象門面

南投搬家公司費用需注意的眉眉角角,別等搬了再說!

新北清潔公司,居家、辦公、裝潢細清專業服務

對抗暖化鼓舞世人 瑞典環保少女獲兒童和平獎

摘錄自2019年11月21日中央通訊社海牙報導

瑞典環保少女童貝里因對抗氣候變遷付出努力,鼓舞數百萬同儕為氣候問題發聲,今天(21日)獲頒國際兒童和平獎(International Children’s Peace Prize)。

年僅16歲的童貝里無法親自出席位於海牙的頒獎典禮,因為她11月中離開美國轉赴歐洲,正搭著小船穿越大西洋,準備參加將在馬德里舉辦的聯合國氣候高峰會。但童貝里還是發布訊息表示,她「非常感謝,並且很榮幸獲得這個獎」。

童貝里去年在國際暴紅,她發起「為氣候罷課」(School Strike for the Climate)的抗議活動,帶動數以萬計世界各地青年學子響應。

本站聲明:網站內容來源環境資訊中心https://e-info.org.tw/,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

網頁設計公司推薦不同的風格,搶佔消費者視覺第一線

※廣告預算用在刀口上,台北網頁設計公司幫您達到更多曝光效益

※自行創業缺乏曝光? 網頁設計幫您第一時間規劃公司的形象門面

南投搬家前需注意的眉眉角角,別等搬了再說!

新北清潔公司,居家、辦公、裝潢細清專業服務

SQL運行內幕:從執行原理看調優的本質

相信大家看過無數的MySQL調優經驗貼了,會告訴你各種調優手段,如:

  • 避免 select *;
  • join字段走索引;
  • 慎用in和not in,用exists取代in;
  • 避免在where子句中對字段進行函數操作;
  • 盡量避免更新聚集索引;
  • group by如果不需要排序,手動加上 order by null;
  • join選擇小表作為驅動表;
  • order by字段盡量走索引…

其中有些手段也許跟隨者MySQL版本的升級過時了。我們真的需要背這些調優手段嗎?我覺得是沒有必要的,在掌握MySQL存儲架構SQL執行原理的情況下,我們就很自然的明白,為什麼要提議這麼優化了,甚至能夠發現別人提的不太合理的優化手段。

在 洞悉MySQL底層架構:遊走在緩衝與磁盤之間 這篇文章中,我們已經介紹了MySQL的存儲架構,詳細對你在MySQL存儲索引緩衝IO相關的調優經驗中有了一定的其實。

本文,我們重點講解常用的SQL的執行原理,從執行原理,以及MySQL內部對SQL的優化機制,來分析SQL要如何調優,理解為什麼要這樣…那樣…那樣…調優。

如果沒有特別說明,本文以MySQL5.7版本作為講解和演示。

閱讀完本文,您將了解到:

  • COUNT: MyISAM和InnoDB存儲引擎處理count的區別是什麼?
  • COUNT: count為何性能差?
  • COUNT: count有哪些書寫方式,怎麼count統計會快點?
  • ORDER BY: order by語句有哪些排序模式,以及每種排序模式的優缺點?
  • ORDER BY: order by語句會用到哪些排序算法,在什麼場景下會選擇哪種排序算法
  • ORDER BY: 如何查看和分析sql的order by優化手段(執行計劃 + OPTIMIZER_TRACE日誌)
  • ORDER BY: 如何優化order by語句的執行效率?(思想:減小行查詢大小,盡量走索引,能夠走覆蓋索引最佳,可適當增加sort buffer內存大小)
  • JOIN: join走索引的情況下是如何執行的?
  • JOIN: join不走索引的情況下是如何執行的?
  • JOIN: MySQL對Index Nested-Loop Join做了什麼優化?(MMR,BKA)
  • JOIN: BNL算法對緩存會產生什麼影響?有什麼優化策略?
  • JOIN: 有哪些常用的join語句?
  • JOIN: 針對join語句,有哪些優化手段?
  • UNION: union語句執行原理是怎樣的?
  • UNION: union是如何去重的?
  • GROUP BY: group by完全走索引的情況下執行計劃如何?
  • GROUP BY: 什麼情況下group by會用到臨時表?什麼情況下會用到臨時表+排序?
  • GROUP BY: 對group by有什麼優化建議?
  • DISTINCT: distinct關鍵詞執行原理是什麼?
  • 子查詢: 有哪些常見的子查詢使用方式?
  • 子查詢: 常見的子查詢優化有哪些?
  • 子查詢: 真的要盡量使用關聯查詢取代子查詢嗎?
  • 子查詢:in 的效率真的這麼慢嗎?
  • 子查詢: MySQL 5.6之後對子查詢做了哪些優化?(SEMIJOIN,Materializatioin,Exists優化策略)
  • 子查詢: Semijoin有哪些優化策略,其中Materializatioin策略有什麼執行方式,為何要有這兩種執行方式?
  • 子查詢: 除了in轉Exists這種優化優化,MariaDB中的exists轉in優化措施有什麼作用?

1、count

存儲引擎的區別

  • MyISAM引擎每張表中存放了一個meta信息,裡面包含了row_count屬性,內存和文件中各有一份,內存的count變量值通過讀取文件中的count值來進行初始化。[1]但是如果帶有where條件,還是必須得進行表掃描

  • InnoDB引擎執行count()的時候,需要把數據一行行從引擎裏面取出來進行統計。

下面我們介紹InnoDB中的count()。

count中的一致性視圖

InnoDB中為何不像MyISAM那樣維護一個row_count變量呢?

前面 洞悉MySQL底層架構:遊走在緩衝與磁盤之間 一文我們了解到,InnoDB為了實現事務,是需要MVCC支持的。MVCC的關鍵是一致性視圖。一個事務開啟瞬間,所有活躍的事務(未提交)構成了一個視圖數組,InnoDB就是通過這個視圖數組來判斷行數據是否需要undo到指定的版本。

如下圖,假設執行count的時候,一致性視圖得到當前事務能夠取到的最大事務ID DATA_TRX_ID=1002,那麼行記錄中事務ID超過1002都都要通過undo log進行版本回退,最終才能得出最終哪些行記錄是當前事務需要統計的:

row1是其他事務新插入的記錄,當前事務不應該算進去。所以最終得出,當前事務應該統計row2,row3。

執行count會影響其他頁面buffer pool的命中率嗎?

我們知道buffer pool中的LRU算法是是經過改進的,默認情況下,舊子列表(old區)佔3/8,count加載的頁面一直往舊子列表中插入,在舊子列表中淘汰,不會晉陞到新子列表中。所以不會影響其他頁面buffer pool的命中率。

count(主鍵)

count(主鍵)執行流程如下:

  • 執行器請求存儲引擎獲取數據;
  • 為了保證掃描數據量更少,存儲引擎找到最小的那顆索引樹獲取所有記錄,返回記錄的id給到server。返回記錄之前會進行MVCC及其可見性的判斷,只返回當前事務可見的數據;
  • server獲取到記錄之後,判斷id如果不為空,則累加到結果記錄中。

count(1)

count(1)與count(主鍵)執行流程基本一致,區別在於,針對查詢出的每一條記錄,不會取記錄中的值,而是直接返回一個”1″用於統計累加。統計了所有的行。

count(字段)

與count(主鍵)類似,會篩選非空的字段進行統計。如果字段沒有添加索引,那麼會掃描聚集索引樹,導致掃描的數據頁會比較多,效率相對慢點

count(*)

count(*)不會取記錄的值,與count(1)類似。

執行效率對比:count(字段) < count(主鍵) < count(1)

2、order by

以下是我們本節作為演示例子的表,假設我們有如下錶:

索引如下:

對應的idx_d索引結構如下(這裏我們做了一些誇張的手法,讓一個頁數據變小,為了展現在索引樹中的查找流程):

2.1、如何跟蹤執行優化

為了方便分析sql的執行流程,我們可以在當前session中開啟 optimizer_trace:

SET optimizer_trace=’enabled=on’;

然後執行sql,執行完之後,就可以通過以下堆棧信息查看執行詳情了:

SELECT * FROM information_schema.OPTIMIZER_TRACE\G;

以下是

select a, b, c, d from t20 force index(idx_abc)  where a=3 order by d limit 100,2;

的執行結果,其中符合a=3的有8457條記錄,針對order by重點關注以下屬性

"filesort_priority_queue_optimization": {  // 是否啟用優先級隊列
  "limit": 102,           // 排序后需要取的行數,這裏為 limit 100,2,也就是100+2=102
  "rows_estimate": 24576, // 估計參与排序的行數
  "row_size": 123,        // 行大小
  "memory_available": 32768,    // 可用內存大小,即設置的sort buffer大小
  "chosen": true          // 是否啟用優先級隊列
},
...
"filesort_summary": {
  "rows": 103,                // 排序過程中會持有的行數
  "examined_rows": 8457,      // 參与排序的行數,InnoDB層返回的行數
  "number_of_tmp_files": 0,   // 外部排序時,使用的臨時文件數量
  "sort_buffer_size": 13496,  // 內存排序使用的內存大小
  "sort_mode": "sort_key, additional_fields"  // 排序模式
}

2.1.1、排序模式

其中 sort_mode有如下幾種形式:

  • sort_key, rowid:表明排序緩衝區元組包含排序鍵值和原始錶行的行id,排序后需要使用行id進行回表,這種算法也稱為original filesort algorithm(回表排序算法);
  • sort_key, additional_fields:表明排序緩衝區元組包含排序鍵值和查詢所需要的列,排序后直接從緩衝區元組取數據,無需回表,這種算法也稱為modified filesort algorithm(不回表排序);
  • sort_key, packed_additional_fields:類似上一種形式,但是附加的列(如varchar類型)緊密地打包在一起,而不是使用固定長度的編碼。

如何選擇排序模式

選擇哪種排序模式,與max_length_for_sort_data這個屬性有關,這個屬性默認值大小為1024字節:

  • 如果查詢列和排序列佔用的大小超過這個值,那麼會轉而使用sort_key, rowid模式;
  • 如果不超過,那麼所有列都會放入sort buffer中,使用sort_key, additional_fields或者sort_key, packed_additional_fields模式;
  • 如果查詢的記錄太多,那麼會使用sort_key, packed_additional_fields對可變列進行壓縮。

2.1.2、排序算法

基於參与排序的數據量的不同,可以選擇不同的排序算法:

  • 如果排序取的結果很小,小於內存,那麼會使用優先級隊列進行堆排序;

    • 例如,以下只取了前面10條記錄,會通過優先級隊列進行排序:

    • select a, b, c, d from t20 force index(idx_abc)  where a=3 order by d limit 10;
      
  • 如果排序limit n, m,n太大了,也就是說需要取排序很後面的數據,那麼會使用sort buffer進行快速排序

    • 如下,表中a=1的數據又三條,但是由於需要limit到很後面的記錄,MySQL會對比優先級隊列排序和快速排序的開銷,選擇一個比較合適的排序算法,這裏最終放棄了優先級隊列,轉而使用sort buffer進行快速排序:

    • select a, b, c, d from t20 force index(idx_abc)  where a=1 order by d limit 300,2;
      
  • 如果參与排序的數據sort buffer裝不下了,那麼我們會一批一批的給sort buffer進行內存快速排序,結果放入排序臨時文件,最終使對所有排好序的臨時文件進行歸併排序,得到最終的結果;

    • 如下,a=3的記錄超過了sort buffer,我們要查找的數據是排序后1000行起,sort buffer裝不下1000行數據了,最終MySQL選擇使用sort buffer進行分批快排,把最終結果進行歸併排序:

    • select a, b, c, d from t20 force index(idx_abc)  where a=3 order by d limit 1000,10;
      

2.2、order by走索引避免排序

執行如下sql:

select a, b, c, d from t20 force index(idx_d) where d like 't%' order by d limit 2;

我們看一下執行計劃:

發現Extra列為:Using index condition,也就是這裏只走了索引。

執行流程如下圖所示:

通過idx_d索引進行range_scan查找,掃描到4條記錄,然後order by繼續走索引,已經排好序,直接取前面兩條,然後去聚集索引查詢完整記錄,返回最終需要的字段作為查詢結果。這個過程只需要藉助索引。

如何查看和修改sort buffer大小?

我們看一下當前的sort buffer大小:

可以發現,這裏默認配置了sort buffer大小為512k。

我們可以設置這個屬性的大小:

SET GLOBAL sort_buffer_size = 32*1024;

或者

SET sort_buffer_size = 32*1024;

下面我們統一把sort buffer設置為32k

SET sort_buffer_size = 32*1024; 

2.3、排序算法案例

2.3.1、使用優先級隊列進行堆排序

如果排序取的結果很小,並且小於sort buffer,那麼會使用優先級隊列進行堆排序;

例如,以下只取了前面10條記錄:

select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 10;

a=3的總記錄數:8520。查看執行計劃:

發現這裏where條件用到了索引,order by limit用到了排序。我們進一步看看執行的optimizer_trace日誌:

"filesort_priority_queue_optimization": {
  "limit": 10,
  "rows_estimate": 27033,
  "row_size": 123,
  "memory_available": 32768,
  "chosen": true  // 使用優先級隊列進行排序
},
"filesort_execution": [
],
"filesort_summary": {
  "rows": 11,
  "examined_rows": 8520,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 1448,
  "sort_mode": "sort_key, additional_fields"
}

發現這裡是用到了優先級隊列進行排序。排序模式是:sort_key, additional_fields,即先回表查詢完整記錄,把排序需要查找的所有字段都放入sort buffer進行排序。

所以這個執行流程如下圖所示:

  1. 通過where條件a=3掃描到8520條記錄;
  2. 回表查找記錄;
  3. 把8520條記錄中需要的字段放入sort buffer中;
  4. 在sort buffer中進行堆排序;
  5. 在排序好的結果中取limit 10前10條,寫入net buffer,準備發送給客戶端。

2.3.2、內部快速排序

如果排序limit n, m,n太大了,也就是說需要取排序很後面的數據,那麼會使用sort buffer進行快速排序。MySQL會對比優先級隊列排序和歸併排序的開銷,選擇一個比較合適的排序算法。

如何衡量究竟是使用優先級隊列還是內存快速排序?
一般來說,快速排序算法效率高於堆排序,但是堆排序實現的優先級隊列,無需排序完所有的元素,就可以得到order by limit的結果。
MySQL源碼中聲明了快速排序速度是堆排序的3倍,在實際排序的時候,會根據待排序數量大小進行切換算法。如果數據量太大的時候,會轉而使用快速排序。

有如下SQL:

select a, b, c, d from t20 force index(idx_abc)  where a=1 order by d limit 300,2;

我們把sort buffer設置為32k:

SET sort_buffer_size = 32*1024; 

其中a=1的記錄有3條。查看執行計劃:

可以發現,這裏where條件用到了索引,order by limit 用到了排序。我們進一步看看執行的optimizer_trace日誌:

"filesort_priority_queue_optimization": {
  "limit": 302,
  "rows_estimate": 27033,
  "row_size": 123,
  "memory_available": 32768,
  "strip_additional_fields": {
    "row_size": 57,
    "sort_merge_cost": 33783,
    "priority_queue_cost": 61158,
    "chosen": false  // 對比發現快速排序開銷成本比優先級隊列更低,這裏不適用優先級隊列
  }
},
"filesort_execution": [
],
"filesort_summary": {
  "rows": 3,
  "examined_rows": 3,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 32720,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

可以發現這裏最終放棄了優先級隊列,轉而使用sort buffer進行快速排序。

所以這個執行流程如下圖所示:

  1. 通過where條件a=1掃描到3條記錄;
  2. 回表查找記錄;
  3. 把3條記錄中需要的字段放入sort buffer中;
  4. 在sort buffer中進行快速排序
  5. 在排序好的結果中取limit 300, 2第300、301條記錄,寫入net buffer,準備發送給客戶端。

2.3.3、外部歸併排序

當參与排序的數據太多,一次性放不進去sort buffer的時候,那麼我們會一批一批的給sort buffer進行內存排序,結果放入排序臨時文件,最終使對所有排好序的臨時文件進行歸併排序,得到最終的結果。

有如下sql:

select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 1000,10;

其中a=3的記錄有8520條。執行計劃如下:

可以發現,這裏where用到了索引,order by limit用到了排序。進一步查看執行的optimizer_trace日誌:

"filesort_priority_queue_optimization": {
  "limit": 1010,
  "rows_estimate": 27033,
  "row_size": 123,
  "memory_available": 32768,
  "strip_additional_fields": {
    "row_size": 57,
    "chosen": false,
    "cause": "not_enough_space"  // sort buffer空間不夠,無法使用優先級隊列進行排序了
  }
},
"filesort_execution": [
],
"filesort_summary": {
  "rows": 8520,
  "examined_rows": 8520,
  "number_of_tmp_files": 24,  // 用到了24個外部文件進行排序
  "sort_buffer_size": 32720,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

我們可以看到,由於limit 1000,要返回排序后1000行以後的記錄,顯然sort buffer已經不能支撐這麼大的優先級隊列了,所以轉而使用sort buffer內存排序,而這裏需要在sort buffer中分批執行快速排序,得到多個排序好的外部臨時文件,最終執行歸併排序。(外部臨時文件的位置由tmpdir參數指定)

其流程如下圖所示:

2.4、排序模式案例

2.4.1、sort_key, additional_fields模式

sort_key, additional_fields,排序緩衝區元組包含排序鍵值和查詢所需要的列(先回表取需要的數據,存入排序緩衝區中),排序后直接從緩衝區元組取數據,無需再次回表。

上面 2.3.1、2.3.2節的例子都是這種排序模式,就不繼續舉例了。

2.4.2、<sort_key, packed_additional_fields>模式

sort_key, packed_additional_fields:類似上一種形式,但是附加的列(如varchar類型)緊密地打包在一起,而不是使用固定長度的編碼。

上面2.3.3節的例子就是這種排序模式,由於參与排序的總記錄大小太大了,因此需要對附加列進行緊密地打包操作,以節省內存。

2.4.3、<sort_key, rowid>模式

前面我們提到,選擇哪種排序模式,與max_length_for_sort_data[2]這個屬性有關,max_length_for_sort_data規定了排序行的最大大小,這個屬性默認值大小為1024字節:

也就是說如果查詢列和排序列佔用的大小小於這個值,這個時候會走sort_key, additional_fields或者sort_key, packed_additional_fields算法,否則,那麼會轉而使用sort_key, rowid模式。

現在我們特意把這個值設置小一點,模擬sort_key, rowid模式:

SET max_length_for_sort_data = 100;

這個時候執行sql:

select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 10;

這個時候再查看sql執行的optimizer_trace日誌:

"filesort_priority_queue_optimization": {
  "limit": 10,
  "rows_estimate": 27033,
  "row_size": 49,
  "memory_available": 32768,
  "chosen": true
},
"filesort_execution": [
],
"filesort_summary": {
  "rows": 11,
  "examined_rows": 8520,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 632,
  "sort_mode": "<sort_key, rowid>"
}

可以發現這個時候切換到了sort_key, rowid模式,在這個模式下,執行流程如下:

  1. where條件a=3掃描到8520條記錄;
  2. 回表查找記錄;
  3. 找到這8520條記錄的idd字段,放入sort buffer中進行堆排序;
  4. 排序完成后,取前面10條;
  5. 取這10條的id回表查詢需要的a,b,c,d字段值;
  6. 依次返回結果給到客戶端。

可以發現,正因為行記錄太大了,所以sort buffer中只存了需要排序的字段和主鍵id,以時間換取空間,最終排序完成,再次從聚集索引中查找到所有需要的字段返回給客戶端,很明顯,這裏多了一次回表操作的磁盤讀,整體效率上是稍微低一點的。

2.5、order by優化總結

根據以上的介紹,我們可以總結出以下的order by語句的相關優化手段:

  • order by字段盡量使用固定長度的字段類型,因為排序字段不支持壓縮;
  • order by字段如果需要用可變長度,應盡量控制長度,道理同上;
  • 查詢中盡量不用用select *,避免查詢過多,導致order by的時候sort buffer內存不夠導致外部排序,或者行大小超過了max_length_for_sort_data導致走了sort_key, rowid排序模式,使得產生了更多的磁盤讀,影響性能;
  • 嘗試給排序字段和相關條件加上聯合索引,能夠用到覆蓋索引最佳。

3、join

為了演示join,接下來我們需要用到這兩個表:

CREATE TABLE `t30` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY idx_a(a)
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE `t31` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `f` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY idx_a(a)
) ENGINE=InnoDB CHARSET=utf8mb4;

insert into t30(a,b,c) values(1, 1, 1),(12,2,2),(3,3,3),(11, 12, 31),(15,1,32),(33,33,43),(5,13,14),(4,13,14),(16,13,14),(10,13,14);

insert into t31(a,f,c) values(1, 1, 1),(21,2,2),(3,3,3),(12, 1, 1),(31,20,2),(4,10,3),(2,23,24),(22,23,24),(5,23,24),(20,23,24);

在MySQL官方文檔中 8.8.2 EXPLAIN Output Format[3] 提到:MySQL使用Nested-Loop Loin算法處理所有的關聯查詢。使用這種算法,意味着這種執行模式:

  • 從第一個表中讀取一行,然後在第二個表、第三個表…中找到匹配的行,以此類推;
  • 處理完所有關聯的表后,MySQL將輸出選定的列,如果列不在當前關聯的索引樹中,那麼會進行回表查找完整記錄;
  • 繼續遍歷,從表中取出下一行,重複以上步驟。

下面我們所講到的都是Nested-Loop Join算法的不同實現。

多表join:不管多少個表join,都是用的Nested-Loop Join實現的。如果有第三個join的表,那麼會把前兩個表的join結果集作為循環基礎數據,在執行一次Nested-Loop Join,到第三個表中匹配數據,更多多表同理。

3.1、join走索引(Index Nested-Loop Join)

3.1.1、Index Nested-Loop Join

我們執行以下sql:

select * from t30 straight_join t31 on t30.a=t31.a;

查看執行計劃:

可以發現:

  • t30作為驅動表,t31作為被驅動表;
  • 通過a字段關聯,去t31表查找數據的時候用到了索引。

該sql語句的執行流程如下圖:

  1. 首先遍歷t30聚集索引;
  2. 針對每個t30的記錄,找到a的值,去t31的idx_a索引中找是否存在記錄;
  3. 如果存在則拿到t30對應索引記錄的id回表查找完整記錄;
  4. 分別取t30和t31的所有字段作為結果返回。

由於這個過程中用到了idx_a索引,所以這種算法也稱為:Index Nested-Loop (索引嵌套循環join)。其偽代碼結構如下:

// A 為t30聚集索引
// B 為t31聚集索引
// BIndex 為t31 idx_a索引
void indexNestedLoopJoin(){
  List result;
  for(a in A) {
    for(bi in BIndex) {
      if (a satisfy condition bi) {
        output <a, b>;
      }
    }
  }
}

假設t30記錄數為m,t31記錄數為n,每一次查找索引樹的複雜度為log2(n),所以以上場景,總的複雜度為:m + m*2*log2(n)

也就是說驅動表越小,複雜度越低,越能提高搜索效率。

3.1.2、Index nested-Loop Join的優化

我們可以發現,以上流程,每次從驅動表取一條數據,然後去被驅動表關聯取數,表現為磁盤的隨記讀,效率是比較低低,有沒有優化的方法呢?

這個就得從MySQL的MRR(Multi-Range Read)[4]優化機制說起了。

3.1.2.1、Multi-Range Read優化

我們執行以下代碼,強制開啟MMR功能:

set optimizer_switch="mrr_cost_based=off"

然後執行以下SQL,其中a是索引:

select * from t30 force index(idx_a) where a<=12 limit 10;

可以得到如下執行計劃:

可以發現,Extra列提示用到了MRR優化。

這裏為了演示走索引的場景,所以加了force index關鍵詞。

正常不加force index的情況下,MySQL優化器會檢查到這裏即使走了索引還是需要回表查詢,並且表中的數據量不多,那乾脆就直接掃描全表,不走索引,效率更加高了。

如果沒有MRR優化,那麼流程是這樣的:

  1. 在idx_a索引中找到a<10的記錄;
  2. 取前面10條,拿着id去回表查找完整記錄,這裏回表查詢是隨機讀,效率較差
  3. 取到的結果通過net buffer返回給客戶端。

使用了MRR優化之後,這個執行流程是這樣的:

  1. 在idx_abc索引中找到a<10的記錄;
  2. 取10條,把id放入read rnd buffer;
  3. read rnd buffer中的id排序;
  4. 排序之後回表查詢完整記錄,id越多,排好序之後越有可能產生連續的id,去磁盤順序讀;
  5. 查詢結果寫入net buffer返回給客戶端;

3.1.2.2、Batched Key Access

與Multi-Range Read的優化思路類似,MySQL也是通過把隨機讀改為順序讀,讓Index Nested-Loop Join提升查詢效率,這個算法稱為Batched Key Access(BKA)[5]算法。

我們知道,默認情況下,是掃描驅動表,一行一行的去被驅動表匹配記錄。這樣就無法觸發MRR優化了,為了能夠觸發MRR,於是BKA算法登場了。

在BKA算法中,驅動表通過使用join buffer批量在被驅動表輔助索引中關聯匹配數據,得到一批結果,一次性傳遞個數據庫引擎的MRR接口,從而可以利用到MRR對磁盤讀的優化。

為了啟用這個算法,我們執行以下命令(BKA依賴於MRR):

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

我們再次執行以下關聯查詢sql:

select * from t30 straight_join t31 on t30.a=t31.a;

我們可以得到如下的執行計劃:

可以發現,這裏用到了:Using join buffer(Batched Key Access)

執行流程如下:

  1. 把驅動表的數據批量放入join buffer中;
  2. 在join buffer中批與被驅動表的輔助索引匹配結果,得到一個結果集;
  3. 把上一步的結果集批量提交給引擎的MRR接口;
  4. MRR接口處理同上一節,主要進行了磁盤順序讀的優化;
  5. 組合輸出最終結果,可以看到,這裏的結果與沒有開啟BKA優化的順序有所不同,這裏使用了t31被驅動表的id排序作為輸出順序,因為最後一步對被驅動表t31讀取進行MRR優化的時候做了排序。

如果join條件沒走索引,又會是什麼情況呢,接下來我們嘗試執行下對應的sql。

3.2、join不走索引(Block Nested-Loop Join)

3.2.1、Block Nested-Loop Join (BNL)

我們執行以下sql:

select * from t30 straight_join t31 on t30.c=t31.c;

查看執行計劃:

可以發現:

  • t30作為驅動表,t31作為被驅動表;
  • 通過c字段關聯,去t31表查找數據的時候沒有用到索引;
  • join的過程中用到了join buffer,這裏提示用到了Block Nested Loop Join;

該語句的執行流程如下圖:

  1. t30驅動表中的數據分批(分塊)存入join buffer,如果一次可以全部存入,則這裡會一次性存入;
  2. t31被驅動表中掃描記錄,依次取出與join buffer中的記錄對比(內存中對比,快),判斷是否滿足c相等的條件;
  3. 滿足條件的記錄合併結果輸出到net buffer中,最終傳輸給客戶端。

然後清空join buffer,存入下一批t30的數據,重複以上流程。

顯然,每批數據都需要掃描一遍被驅動表,批次越多,掃描越多,但是內存判斷總次數是不變的。所以總批次越小,越高效。所以,跟上一個算法一樣,驅動表越小,複雜度越低,越能提高搜索效率。

3.2.2、BNL問題

在 洞悉MySQL底層架構:遊走在緩衝與磁盤之間 一文中,我們介紹了MySQL Buffer Pool的LRU算法,如下:

默認情況下,同一個數據頁,在一秒鐘之後再次訪問,那麼就會晉陞到新子列表(young區)。

恰巧,如果我們用到了BNL算法,那麼分批執行的話,就會重複掃描被驅動表去匹配每一個批次了。

考慮以下兩種會影響buffer pool的場景:

  • 如果這個時候join掃描了一個很大的冷表,那麼在join這段期間,會持續的往舊子列表(old區)寫數據頁,淘汰隊尾的數據頁,這會影響其他業務數據頁晉陞到新子列表,因為很可能在一秒內,其他業務數據就從舊子列表中被淘汰掉了;
  • 而如果這個時候BNL算法把驅動表分為了多個批次,每個批次掃描匹配被驅動表,都超過1秒鐘,那麼這個時候,被驅動表的數據頁就會被晉陞到新子列表,這個時候也會把其他業務的數據頁提前從新子列表中淘汰掉。

3.2.3、BNL問題解決方案

3.2.3.1、調大 join_buffer_size

針對以上這種場景,為了避免影響buffer pool,最直接的辦法就是增加join_buffer_size的值,以減少對被驅動表的掃描次數。

3.2.3.2、把BNL轉換為BKA

我們可以通過把join的條件加上索引,從而避免了BNL算法,轉而使用BKA算法,這樣也可以加快記錄的匹配速度,以及從磁盤讀取被驅動表記錄的速度。

3.2.3.3、通過添加臨時表

有時候,被驅動表很大,但是關聯查詢又很少使用,直接給關聯字段加索引太浪費空間了,這個時候就可以通過把被驅動表的數據放入臨時表,在零時表中添加索引的方式,以達成3.2.3.2的優化效果。

3.2.3.4、使用hash join

什麼是hash join呢,簡單來說就是這樣的一種模型:

把驅動表滿足條件的數據取出來,放入一個hash結構中,然後把被驅動表滿足條件的數據取出來,一行一行的去hash結構中尋找匹配的數據,依次找到滿足條件的所有記錄。

一般情況下,MySQL的join實現都是以上介紹的各種nested-loop算法的實現,但是從MySQL 8.0.18[6]開始,我們可以使用hash join來實現表連續查詢了。感興趣可以進一步閱讀這篇文章進行了解:[Hash join in MySQL 8 | MySQL Server Blog](https://mysqlserverteam.com/hash-join-in-mysql-8/#:~:text=MySQL only supports inner hash,more often than it does.)

3.3、各種join

我們在平時工作中,會遇到各種各樣的join語句,主要有如下:

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

LEFT JOIN EXCLUDING INNER JOIN

RIGHT JOIN EXCLUDING INNER JOIN

OUTER JOIN EXCLUDING INNER JOIN

更詳細的介紹,可以參考:

  • MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS[7]
  • How the SQL join actually works?[8]

3.3、join使用總結

  • join優化的目標是盡可能減少join中Nested-Loop的循環次數,所以請讓小表做驅動表;
  • 關聯字段盡量走索引,這樣就可以用到Index Nested-Loop Join了;
  • 如果有order by,請使用驅動表的字段作為order by,否則會使用 using temporary;
  • 如果不可避免要用到BNL算法,為了減少被驅動表多次掃描導致的對Buffer Pool利用率的影響,那麼可以嘗試把 join_buffer_size調大;
  • 為了進一步加快BNL算法的執行效率,我們可以給關聯條件加上索引,轉換為BKA算法;如果加索引成本較高,那麼可以通過臨時表添加索引來實現;
  • 如果您使用的是MySQL 8.0.18,可以嘗試使用hash join,如果是較低版本,也可以自己在程序中實現一個hash join。

4、union

通過使用union可以把兩個查詢結果合併起來,注意:

union all不會去除重複的行,union則會去除重複讀的行。

4.1、union all

執行下面sql:

(select id from t30 order by id desc limit 10) union all (select c from t31 order by id desc limit 10)

該sql執行計劃如下圖:

執行流程如下:

  1. 從t30表查詢出結果,直接寫出到net buffer,傳回給客戶端;
  2. 從331表查詢出結果,直接寫出到net buffer,傳回給客戶端。

4.2、union

執行下面sql:

(select id from t30 order by id desc limit 10) union (select c from t31 order by id desc limit 10)

該sql執行計劃如下圖:

執行流程如下:

  1. 從t30查詢出記錄,寫入到臨時表;
  2. 從t30查詢出記錄,寫入臨時表,在臨時表中通過唯一索引去重;
  3. 把臨時表的數據通過net buffer返回給客戶端。

5、group by

5.1、完全走索引

我們給t30加一個索引:

alter table t30 add index idx_c(c);

執行以下group bysql:

select c, count(*) from t30 group by c;

執行計劃如下:

發現這裏只用到了索引,原因是idx_c索引本身就是按照c排序好的,那麼直接順序掃描idx_c索引,可以直接統計到每一個c值有多少條記錄,無需做其他的統計了。

5.2、臨時表

現在我們把剛剛的idx_c索引給刪掉,執行以下sql:

select c, count(*) from t30 group by c order by null;

為了避免排序,所以我們這裏添加了 order by null,表示不排序。

執行計劃如下:

可以發現,這裏用到了內存臨時表。其執行流程如下:

  1. 掃描t30聚集索引;
  2. 建立一個臨時表,以字段c為主鍵,依次把掃描t30的記錄通過臨時表的字段c進行累加;
  3. 把最後累加得到的臨時表返回給客戶端。

5.3、臨時表 + 排序

如果我們把上一步的order by null去掉,默認情況下,group by的結果是會通過c字段排序的。我們看看其執行計劃:

可以發現,這裏除了用到臨時表,還用到了排序。

我們進一步看看其執行的OPTIMIZER_TRACE日誌:

"steps": [
  {
    "creating_tmp_table": {
      "tmp_table_info": {
        "table": "intermediate_tmp_table",  // 創建中間臨時表
        "row_length": 13,
        "key_length": 4,
        "unique_constraint": false,
        "location": "memory (heap)",
        "row_limit_estimate": 1290555
      }
    }
  },
  {
    "filesort_information": [
      {
        "direction": "asc",
        "table": "intermediate_tmp_table",
        "field": "c"
      }
    ],
    "filesort_priority_queue_optimization": {
      "usable": false,
      "cause": "not applicable (no LIMIT)" // 由於沒有 limit,不採用優先級隊列排序
    },
    "filesort_execution": [
    ],
    "filesort_summary": {
      "rows": 7,
      "examined_rows": 7,
      "number_of_tmp_files": 0,
      "sort_buffer_size": 344,
      "sort_mode": "<sort_key, rowid>"  // rowid排序模式
    }
  }
]

通過日誌也可以發現,這裏用到了中間臨時表,由於沒有limit限制條數,這裏沒有用到優先級隊列排序,這裏的排序模式為sort_key, rowid。其執行流程如下:

  1. 掃描t30聚集索引;
  2. 建立一個臨時表,以字段c為主鍵,依次把掃描t30的記錄通過臨時表的字段c進行累加;
  3. 把得到的臨時表放入sort buffer進行排序,這裏通過rowid進行排序;
  4. 通過排序好的rowid回臨時表查找需要的字段,返回給客戶端。

臨時表是存放在磁盤還是內存?

tmp_table_size 參數用於設置內存臨時表的大小,如果臨時表超過這個大小,那麼會轉為磁盤臨時表:

可以通過以下sql設置當前session中的內存臨時表大小:SET tmp_table_size = 102400;

5.5、直接排序

查看官方文檔的 SELECT Statement[9],可以發現SELECT後面可以使用許多修飾符來影響SQL的執行效果:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [into_option]
    [FOR UPDATE | LOCK IN SHARE MODE]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

這裏我們重點關注下這兩個:

  • SQL_BIG_RESULT:可以在包含group by 和distinct的SQL中使用,提醒優化器查詢數據量很大,這個時候MySQL會直接選用磁盤臨時表取代內存臨時表,避免執行過程中發現內存不足才轉為磁盤臨時表。這個時候更傾向於使用排序取代二維臨時表統計結果。後面我們會演示這樣的案例;
  • SQL_SMALL_RESULT:可以在包含group by 和distinct的SQL中使用,提醒優化器數據量很小,提醒優化器直接選用內存臨時表,這樣會通過臨時表統計,而不是排序。

當然,在平時工作中,不是特定的調優場景,以上兩個修飾符還是比較少用到的。

接下來我們就通過例子來說明下使用了SQL_BIG_RESULT修飾符的SQL執行流程。

有如下SQL:

select SQL_BIG_RESULT c, count(*) from t30 group by c;

執行計劃如下:

可以發現,這裏只用到了排序,沒有用到索引或者臨時表。這裏用到了SQL_BIG_RESULT修飾符,告訴優化器group by的數據量很大,直接選用磁盤臨時表,但磁盤臨時表存儲效率不高,最終優化器使用數組排序的方式來完成這個查詢。(當然,這個例子實際的結果集並不大,只是作為演示用)

其執行結果如下:

  1. 掃描t30表,逐行的把c字段放入sort buffer;
  2. 在sort buffer中對c字段進行排序,得到一個排序好的c數組;
  3. 遍歷這個排序好的c數組,統計結果並輸出。

5.4、group by 優化建議

  • 盡量讓group by走索引,能最大程度的提高效率;
  • 如果group by結果不需要排序,那麼可以加上group by null,避免進行排序;
  • 如果group by的數據量很大,可以使用SQL_BIG_RESULT修飾符,提醒優化器應該使用排序算法得到group的結果。

6、distinct[10]

在大多數情況下,DISTINCT可以考慮為GROUP BY的一個特殊案例,如下兩個SQL是等效的:

select distinct a, b, c from t30;

select a, b, c from t30 group by a, b, c order by null;

這兩個SQL的執行計劃如下:

由於這種等效性,適用於Group by的查詢優化也適用於DISTINCT。

區別:distinct是在group by之後的每組中取出一條記錄,distinct分組之後不進行排序。

6.1、Extra中的distinct

在一個關聯查詢中,如果您只是查詢驅動表的列,並且在驅動表的列中聲明了distinct關鍵字,那麼優化器會進行優化,在被驅動表中查找到匹配的第一行時,將停止繼續掃描。如下SQL:

explain select distinct t30.a  from t30, t31 where t30.c=t30.c;

執行計劃如下,可以發現Extra列中有一個distinct,該標識即標識用到了這種優化[10:1]

7、子查詢

首先,我們來明確幾個概念:

子查詢:可以是嵌套在另一個查詢(select insert update delete)內,子查詢也可以是嵌套在另一個子查詢裏面。

MySQL子查詢稱為內部查詢,而包含子查詢的查詢稱為外部查詢。子查詢可以在使用表達式的任何地方使用。

接下來我們使用以下錶格來演示各種子查詢:

create table class (
  id bigint not null auto_increment,
  class_num varchar(10) comment '課程編號',
  class_name varchar(100) comment '課程名稱',
  pass_score integer comment '課程及格分數',
  primary key (id)
) comment '課程';

create table student_class (
  id bigint not null auto_increment,
  student_name varchar(100) comment '學生姓名',
  class_num varchar(10) comment '課程編號',
  score integer comment '課程得分',
  primary key (id)
) comment '學生選修課程信息';

insert into class(class_num, class_name, pass_score) values ('C001','語文', 60),('C002','數學', 70),('C003', '英文', 60),('C004', '體育', 80),('C005', '音樂', 60),('C006', '美術', 70);

insert into student_class(student_name, class_num, score) values('James', 'C001', 80),('Talor', 'C005', 75),('Kate', 'C002', 65),('David', 'C006', 82),('Ann', 'C004', 88),('Jan', 'C003', 70),('James', 'C002', 97), ('Kate', 'C005', 90), ('Jan', 'C005', 86), ('Talor', 'C006', 92);

子查詢的用法比較多,我們先來列舉下有哪些子查詢的使用方法。

7.1、子查詢的使用方法

7.1.1、where中的子查詢

7.1.1.1、比較運算符

可以使用比較運算法,例如=,>,<將子查詢返回的單個值與where子句表達式進行比較,如

查找學生選擇的編號最大的課程信息:

SELECT class.* FROM class WHERE class.class_num = ( SELECT MAX(class_num) FROM student_class );

7.1.1.2、in和not in

如果子查詢返回多個值,則可以在WHERE子句中使用其他運算符,例如IN或NOT IN運算符。如

查找學生都選擇了哪些課程:

SELECT class.* FROM class WHERE class.class_num IN ( SELECT DISTINCT class_num FROM student_class );

7.1.2、from子查詢

在FROM子句中使用子查詢時,從子查詢返回的結果集將用作臨時表。該表稱為派生表或實例化子查詢。如 查找最熱門和最冷門的課程分別有多少人選擇:

SELECT max(count), min(count) FROM (SELECT class_num, count(1) as count FROM student_class group by class_num) as t1;

7.1.3、關聯子查詢

前面的示例中,您注意到子查詢是獨立的。這意味着您可以將子查詢作為獨立查詢執行。

獨立子查詢不同,關聯子查詢是使用外部查詢中的數據的子查詢。換句話說,相關子查詢取決於外部查詢。對於外部查詢中的每一行,對關聯子查詢進行一次評估。

下面是比較運算符中的一個關聯子查詢。

查找每門課程超過平均分的學生課程記錄:

SELECT t1.* FROM student_class t1 WHERE t1.score > ( SELECT AVG(score) FROM student_class t2 WHERE t1.class_num = t2.class_num);

關聯子查詢中,針對每一個外部記錄,都需要執行一次子查詢,因為每一條外部記錄的class_num可能都不一樣。

7.1.3.1、exists和not exists

當子查詢與EXISTS或NOT EXISTS運算符一起使用時,子查詢將返回布爾值TRUE或FALSE。

查找所有學生總分大於100分的課程:

select * from class t1 
where exists(
  select sum(score) as total_score from student_class t2 
  where t2.class_num=t1.class_num group by t2.class_num having total_score > 100
)

7.2、子查詢的優化

上面我們演示了子查詢的各種用法,接下來,我們來講一下子查詢的優化[11]

子查詢主要由以下三種優化手段:

  • Semijoin,半連接轉換,把子查詢sql自動轉換為semijion;
  • Materialization,子查詢物化;
  • EXISTS策略,in轉exists;

其中Semijoin只能用於IN,= ANY,或者EXISTS的子查詢中,不能用於NOT IN,<> ALL,或者NOT EXISTS的子查詢中。

下面我們做一下詳細的介紹。

真的要盡量使用關聯查詢取代子查詢嗎?

在《高性能MySQL》[12]一書中,提到:優化子查詢最重要的建議就是盡可能使用關聯查詢代替,但是,如果使用的是MySQL 5.6或者更新版本或者MariaDB,那麼就可以直接忽略這個建議了。因為這些版本對子查詢做了不少的優化,後面我們會重點介紹這些優化。

in的效率真的這麼慢嗎?

在MySQL5.6之後是做了不少優化的,下面我們就逐個來介紹。

7.2.1、Semijoin

Semijoin[13],半連接,所謂半連接,指的是一張表在另一張表棧道匹配的記錄之後,返回第一張表的記錄。即使右邊找到了幾條匹配的記錄,也最終返回左邊的一條。

所以,半連接非常適用於查找兩個表之間是否存在匹配的記錄,而不關注匹配了多少條記錄這種場景。

半連接通常用於IN或者EXISTS語句的優化。

7.2.1.1、優化場景

上面我們講到:接非常適用於查找兩個表之間是否存在匹配的記錄,而不關注匹配了多少條記錄這種場景。

in關聯子查詢

這種場景,如果使用in來實現,可能會是這樣:

SELECT class_num, class_name
    FROM class
    WHERE class_num IN
        (SELECT class_num FROM student_class where condition);

在這裏,優化器可以識別出IN子句要求子查詢僅從student_class表返回唯一的class_num。在這種情況下,查詢會自動優化為使用半聯接。

如果使用exists來實現,可能會是這樣:

SELECT class_num, class_name
    FROM class
    WHERE EXISTS
        (SELECT * FROM student_class WHERE class.class_num = student_class.class_num);

優化案例

統計有學生分數不及格的課程:

SELECT t1.class_num, t1.class_name
    FROM class t1
    WHERE t1.class_num IN
        (SELECT t2.class_num FROM student_class t2 where t2.score < t1.pass_score);

我們可以通過執行以下腳本,查看sql做了什麼優化:

explain extended SELECT t1.class_num, t1.class_name FROM class t1 WHERE t1.class_num IN         (SELECT t2.class_num FROM student_class t2 where t2.score < t1.pass_score);
show warnings\G;

得到如下執行執行計劃,和SQL重寫結果:

從這個SQL重寫結果中,可以看出,最終子查詢變為了semi join語句:

/* select#1 */ select `test`.`t1`.`class_num` AS `class_num`,`test`.`t1`.`class_name` AS `class_name` 
from `test`.`class` `t1` 
semi join (`test`.`student_class` `t2`) where ((`test`.`t2`.`class_num` = `test`.`t1`.`class_num`) and (`test`.`t2`.`score` < `test`.`t1`.`pass_score`))

而執行計劃中,我們看Extra列:

Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)

Using join buffer這項是在join關聯查詢的時候會用到,前面講join語句的時候已經介紹過了,現在我們重點看一下FirstMatch(t1)這個優化項。

FirstMatch(t1)是Semijoin優化策略中的一種。下面我們詳細介紹下Semijoin有哪些優化策略。

7.2.1.2、Semijoin優化策略

MySQL支持5中Semijoin優化策略,下面逐一介紹。

7.2.1.2.1、FirstMatch

在內部表尋找與外部表匹配的記錄,一旦找到第一條,則停止繼續匹配

案例 – 統計有學生分數不及格的課程:

SELECT t1.class_num, t1.class_name
    FROM class t1
    WHERE t1.class_num IN
        (SELECT t2.class_num FROM student_class t2 where t2.score < t1.pass_score);

執行計劃:

執行流程,圖比較大,請大家放大觀看:

  1. 掃描class表,把class表分批放入join buffer中,分批處理;
  2. 在批次中依次取出每一條記錄,在student_class表中掃描查找符合條件的記錄,如果找到,則立刻返回,並從該條匹配的class記錄取出查詢字段返回;
  3. 依次繼續掃描遍歷。

您也可以去MariaDB官網,查看官方的FirstMatch Strategy[14]解釋。

7.2.1.2.2、Duplicate Weedout

將Semijoin作為一個常規的inner join,然後通過使用一個臨時表去重。

具體演示案例,參考MariaDB官網:DuplicateWeedout Strategy[15],以下是官網例子的圖示:

可以看到,灰色區域為臨時表,通過臨時表唯一索引進行去重。

7.2.1.2.3、LooseScan

把內部表的數據基於索引進行分組,取每組第一條數據進行匹配。

具體演示案例,參考MariaDB官網:LooseScan Strategy[16],以下是官網例子的圖示:

7.2.1.4、Materialization[17]

如果子查詢是獨立的(非關聯子查詢),則優化器可以選擇將獨立子查詢產生的結果存儲到一張物化臨時表中。

為了觸發這個優化,我們需要往表裡面添加多點數據,好讓優化器認為這個優化是有價值的。

我們執行以下SQL:

select * from class t1 where t1.class_num in(select t2.class_num from student_class t2 where t2.score > 80) and t1.class_num like 'C%';

執行流程如下:

  1. 執行子查詢:通過where條件從student_class 表中找出符合條件的記錄,把所有記錄放入物化臨時表;
  2. 通過where條件從class表中找出符合條件的記錄,與物化臨時表進行join操作。

物化表的唯一索引

MySQL會報物化子查詢所有查詢字段組成一個唯一索引,用於去重。如上面圖示,灰色連線的兩條記錄衝突去重了。

join操作可以從兩個方向執行:

  • 從物化表關聯class表,也就是說,掃描物化表,去與class表記錄進行匹配,這種我們稱為Materialize-scan
  • 從class表關聯物化表,也就是,掃描class表,去物化表中查找匹配記錄,這種我們稱為Materialize-lookup,這個時候,我們用到了物化表的唯一索引進行查找,效率會很快。

下面我們介紹下這兩種執行方式。

Materialize-lookup

還是以上面的sql為例:

select * from class t1 where t1.class_num in(select t2.class_num from student_class t2 where t2.score > 80) and t1.class_num like 'C%';

執行計劃如下:

可以發現:

  • t2表的select_type為MATERIALIZED,這意味着id=2這個查詢結果將存儲在物化臨時表中。並把該查詢的所有字段作為臨時表的唯一索引,防止插入重複記錄;
  • id=1的查詢接收一個subquery2的表名,這個表正式我們從id=2的查詢得到的物化表。
  • id=1的查詢首先掃描t1表,依次拿到t1表的每一條記錄,去subquery2執行eq_ref,這裏用到了auto_key,得到匹配的記錄。

也就是說,優化器選擇了對t1(class)表進行全表掃描,然後去物化表進行所以等值查找,最終得到結果。

執行模型如下圖所示:

原則:小表驅動大表,關聯字段被驅動表添加索引

如果子查詢查出來的物化表很小,而外部表很大,並且關聯字段是外部表的索引字段,那麼優化器會選擇掃描物化表去關聯外部表,也就是Materialize-scan,下面演示這個場景。

Materialize-scan

現在我們嘗試給class表添加class_num唯一索引:

alter table class add unique uk_class_num(class_num);

並且在class中插入更多的數據。然後執行同樣的sql,得到以下執行計劃:

可以發現,這個時候id=1的查詢是選擇了subquery2,也就是物化表進行掃描,掃描結果逐行去t1表(class)進行eq_ref匹配,匹配過程中用到了t1表的索引。

這裏的執行流程正好與上面的相反,選擇了從class表關聯物化表。

現在,我問大家:Materialization策略什麼時候會選擇從外部表關聯內部表?相信大家心裏應該有答案了。

執行模型如下:

原則:小表驅動大表,關聯字段被驅動表添加索引

現在留給大家另一個問題:以上例子中,這兩種Materialization的開銷分別是多少(從行讀和行寫的角度統計)

答案:

Materialize-lookup:40次讀student_class表,40次寫物化臨時表,42次讀外部表,40次lookup檢索物化臨時表;

Materialize-scan:15次讀student_class表,15次寫物化臨時表,15次掃描物化臨時表,執行15次class表索引查詢。

7.2.2、Materialization

優化器使用Materialization(物化)來實現更加有效的子查詢處理。物化針對非關聯子查詢進行優化。

物化通過把子查詢結果存儲為臨時表(通常在內存中)來加快查詢的執行速度。MySQL在第一次獲取子查詢結果時,會將結果物化為臨時表。隨後如果再次需要子查詢的結果,則直接從臨時表中讀取。

優化器可以使用哈希索引為臨時表建立索引,以使查找更加高效,並且通過索引來消除重複項,讓表保持更小。

子查詢物化的臨時表在可能的情況下存儲在內存中,如果表太大,則會退回到磁盤上進行存儲。

為何要使用物化優化

如果未開啟物化優化,那麼優化器有時會將非關聯子查詢重寫為關聯子查詢。

可以通過以下命令查詢優化開關(Switchable Optimizations[18])狀態:

SELECT @@optimizer_switch\G;

也就是說,如下的in獨立子查詢語句:

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

會重寫為exists關聯子查詢語句:

SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

開啟了物化開關之後,獨立子查詢避免了這樣的重寫,使得子查詢只會查詢一次,而不是重寫為exists語句導致外部每一行記錄都會執行一次子查詢,嚴重降低了效率。

7.2.3、EXISTS策略

考慮以下的子查詢:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL“從外到內”來評估查詢。也就是說,它首先獲取外部表達式outer_expr的值,然後運行子查詢並獲取其產生的結果集用於比較。

7.2.3.1、condition push down 條件下推

如果我們可以把outer_expr下推到子查詢中進行條件判斷,如下:

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

這樣就能夠減少子查詢的行數了。相比於直接用IN來說,這樣就可以加快SQL的執行效率了。

而涉及到NULL值的處理,相對就比較複雜,由於篇幅所限,這裏作為延伸學習,感興趣的朋友可以進一步閱讀:

8.2.2.3 Optimizing Subqueries with the EXISTS Strategy[19]

延伸:
除了讓關聯的in子查詢轉為exists進行優化之外。在MariaDB 10.0.2版本中,引入了另一種相反的優化措施:可以讓exists子查詢轉換為非關聯in子查詢,這樣就可以用上非關聯資產性的物化優化策略了。

詳細可以閱讀:EXISTS-to-IN Optimization[20]

7.2.4、總結

總結一下子查詢的優化方式:

  • 首先優先使用Semijoin來進行優化,消除子查詢,通常選用FirstMatch策略來做表連接;
  • 如果不可以使用Semijoin進行優化,並且當前子查詢是非關聯子查詢,則會物化子查詢,避免多次查詢,同時這一步的優化會遵循選用小表作為驅動表的原則,盡量走索引字段關聯,分為兩種執行方式:Materialize-lookup,Materialization-scan。通常會選用哈希索引為物化臨時表提高檢索效率;
  • 如果子查詢不能物化,那就只能考慮Exists優化策略了,通過condition push down把條件下推到exists子查詢中,減少子查詢的結果集,從而達到優化的目的。

8、limit offset, rows

limit的用法:

limit [offset], [rows]

其中 offset表示偏移量,rows表示需要返回的行數。

offset  limit  表中的剩餘數據
 _||_   __||__   __||__
|    | |      | |
RRRRRR RRRRRRRR RRR...
       |______|
          ||
         結果集

8.1、執行原理

MySQL進行表掃描,讀取到第 offset + rows條數據之後,丟棄前面offset條記錄,返回剩餘的rows條記錄。

比如以下sql:

select * from t30 order by id limit 10000, 10;

這樣總共會掃描10010條。

8.2、優化手段

如果查詢的offset很大,避免直接使用offset,而是通過id到聚集索引中檢索查找。

  1. 利用自增索引,如:
select * from t30 where id > 10000 limit 10;

當然,這也是會有問題的,如果id中間產生了非連續的記錄,這樣定位就不準確了。寫到這裏,篇幅有點長了,最後這個問題留給大家思考,感興趣的朋友可以進一步思考探討與延伸。

這篇文章的內容就差不多介紹到這裏了,能夠閱讀到這裏的朋友真的是很有耐心,為你點個贊。

本文為arthinking基於相關技術資料和官方文檔撰寫而成,確保內容的準確性,如果你發現了有何錯漏之處,煩請高抬貴手幫忙指正,萬分感激。

大家可以關注我的博客:itzhai.com 獲取更多文章,我將持續更新後端相關技術,涉及JVM、Java基礎、架構設計、網絡編程、數據結構、數據庫、算法、併發編程、分佈式系統等相關內容。

如果您覺得讀完本文有所收穫的話,可以關注我的賬號,或者點贊吧,碼字不易,您的支持就是我寫作的最大動力,再次感謝!

關注我的公眾號,及時獲取最新的文章。

更多文章

  • 關注公眾號進入會話窗口獲取
  • JVM系列專題:公眾號發送 JVM

本文作者: arthinking

博客鏈接: https://www.itzhai.com/database/how-sql-works-understand-the-essence-of-tuning-by-the-execution-principle.html

SQL運行內幕:從執行原理看調優的本質

版權聲明: BY-NC-SA許可協議:創作不易,如需轉載,請聯繫作者,謝謝!

References

  1. https://zhuanlan.zhihu.com/p/54378839. Retrieved from https://zhuanlan.zhihu.com/p/54378839 ↩︎

  2. 8.2.1.14 ORDER BY Optimization. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html ↩︎

  3. 8.8.2 EXPLAIN Output Format. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/explain-output.html ↩︎

  4. Batched Key Access: a Significant Speed-up for Join Queries. Retrieved from https://conferences.oreilly.com/mysql2008/public/schedule/detail/582 ↩︎

  5. Batched Key Access Joins. Retrieved from http://underpop.online.fr/m/mysql/manual/mysql-optimization-bka-optimization.html ↩︎

  6. [Hash join in MySQL 8. MySQL Server Blog. Retrieved from https://mysqlserverteam.com/hash-join-in-mysql-8/#:~:text=MySQL only supports inner hash,more often than it does](https://mysqlserverteam.com/hash-join-in-mysql-8/#:~:text=MySQL only supports inner hash,more often than it does) ↩︎

  7. MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS. Retrieved from https://www.guru99.com/joins.html ↩︎

  8. How the SQL join actually works?. Retrieved from https://stackoverflow.com/questions/34149582/how-the-sql-join-actually-works ↩︎

  9. 13.2.9 SELECT Statement. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/select.html ↩︎

  10. 8.2.1.18 DISTINCT Optimization. Retrieved from https://dev.mysql.com/doc/refman/8.0/en/distinct-optimization.html ↩︎ ↩︎

  11. Subquery Optimizer Hints. Retrieved from https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-subquery ↩︎

  12. 高性能MySQL第3版[M]. 电子工業出版社, 2013-5:239. ↩︎

  13. 8.2.2.1 Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/semijoins.html ↩︎

  14. FirstMatch Strategy. Retrieved from https://mariadb.com/kb/en/firstmatch-strategy/ ↩︎

  15. DuplicateWeedout Strategy. Retrieved from https://mariadb.com/kb/en/duplicateweedout-strategy/ ↩︎

  16. LooseScan Strategy. Retrieved from https://mariadb.com/kb/en/loosescan-strategy/ ↩︎

  17. Semi-join Materialization Strategy. Retrieved from https://mariadb.com/kb/en/semi-join-materialization-strategy/ ↩︎

  18. Switchable Optimizations. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html ↩︎

  19. 8.2.2.3 Optimizing Subqueries with the EXISTS Strategy. Retrieved from https://dev.mysql.com/doc/refman/8.0/en/subquery-optimization-with-exists.html ↩︎

  20. EXISTS-to-IN Optimization. Retrieved from https://mariadb.com/kb/en/exists-to-in-optimization/ ↩︎

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

網頁設計公司推薦更多不同的設計風格,搶佔消費者視覺第一線

※廣告預算用在刀口上,網站設計公司幫您達到更多曝光效益

※自行創業 缺乏曝光? 下一步"網站設計"幫您第一時間規劃公司的門面形象

南投搬家前需注意的眉眉角角,別等搬了再說!

新北清潔公司,居家、辦公、裝潢細清專業服務

非洲南部陷旱災 農民牲畜受害慘

摘錄自2019年11月27日公視報導

非洲南部今年遇到了將近半個世紀以來最嚴重的乾旱,導致農民飼養的牲畜大量死亡,糧食也大量短缺,連人都吃不飽。難以維生的農民,因為債台高築、前途茫茫,已經有人走上絕路。

南非近來遭遇嚴重乾旱,小規模養殖的農民,正面臨牲畜不保、債台高築的困境。64歲的莫威,過去兩年來已經失去400頭綿羊,以及450隻提供打獵用的跳羚,他說:「我失去四分之一的綿羊,跳羚也是收入的一部分,我賣給獵人,他們會來打獵,我本來有450隻、500隻左右,現在找不到半隻,除了死掉的。」

莫威的聲音哽咽起來,他說幸好有教會幫忙,現在只能靠捐助的糧草來保住剩下的牲畜,但是已經有兩位農友因為壓力太大撐不下去,選擇輕生。

教會人員海門斯表示,「很多人現在都在掙扎著想輕生,包括這裡和西部地區,因為乾旱影響了南非廣大地區,也有人因為絕望結束自己的生命。」

乾旱衝擊農民生計,當地孩童也面臨飢餓危機,學校每天供應的玉米配蔬菜,就是窮苦孩子們早餐和午餐的全部。學校老師尼格薩蘇說,「這項供餐計畫對支持學生非常重要,尤其那些家裡沒錢的小孩。」

聯合國估計,乾旱加上連續兩個熱帶氣旋摧毀農田,非洲南部包括辛巴威和莫三比克地區,將有超過1100萬人出現糧荒危機。而氣象預報顯示,未來三個月當地降雨量仍將偏低。

本站聲明:網站內容來源環境資訊中心https://e-info.org.tw/,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

網頁設計公司推薦更多不同的設計風格,搶佔消費者視覺第一線

※廣告預算用在刀口上,網站設計公司幫您達到更多曝光效益

※自行創業 缺乏曝光? 下一步"網站設計"幫您第一時間規劃公司的門面形象

南投搬家前需注意的眉眉角角,別等搬了再說!

新北清潔公司,居家、辦公、裝潢細清專業服務

日本女川核電廠2號反應爐獲准重新啟動 防波堤加高到29公尺

摘錄自2019年11月29日ETtoday報導

日本東北電力公司(Tohoku Electric Power)在27日表示,已經獲得日本核監管局的初步批准,重新啟動女川核電廠(Onagawa nuclear powerplant)2號反應爐。日本2011年大地震時,女川核電廠是最接近震央的一座核電廠,當時受到地震及海嘯的破壞而關閉,目前還需要當地居民的同意才能重新啟動。

女川核電廠在311地震時被海嘯淹沒,但裡面的冷卻系統倖存下來,使反應爐免遭受類似於東京電力福島第一核電廠發生的慘況。

東北電力公司預計為女川核電廠的安全升級投資3,400億日元(約新台幣950億元),將電廠防波堤的海拔高度提高到29公尺,這將會是日本各核電廠中最高的一座防波堤。若重新啟動2號反應爐,每年將為公用事業節省350億日元(約新台幣98億元)的燃料成本。

日本在福島核災之前有54座核反應爐在營運,提供日本三分之一的電力,災難突顯了營運和監管方面的缺陷之後,所有的核反應爐若要重啟,都必須按照新的標準許可。

日本的核能安全問題在最近才又被提起,天主教教宗方濟各在上周末訪問日本時表示,除非可以真正保障人民的安全,否則不該再使用核能。

本站聲明:網站內容來源環境資訊中心https://e-info.org.tw/,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

網頁設計公司推薦更多不同的設計風格,搶佔消費者視覺第一線

※廣告預算用在刀口上,網站設計公司幫您達到更多曝光效益

※自行創業 缺乏曝光? 下一步"網站設計"幫您第一時間規劃公司的門面形象

南投搬家前需注意的眉眉角角,別等搬了再說!

新北清潔公司,居家、辦公、裝潢細清專業服務