MySQLオプティマイザーの問題

MySQLの開発の数年にわたって理解してきたこと：

• 下位互換性を損なうことなく開発することは不可能です
• 後方互換性が壊れているMySQLは誰も必要としません。

MySQL 5.1および5.5ユーザーが間接的に遭遇する2つの深刻なMySQL DNA問題について説明します（5.6についてはテストしていませんが、この瞬間は変わっていないと思います）。



MySQLはどのように機能しますか？



MySQL自体があります-これ

• パーサー
• クエリオプティマイザー
• 複製
• システムカタログ

他にもありますが、これらは主なものです。



ストレージエンジンがあります：次の機能を実装するプラグイン：

• 取引
• テーブルをディスクに保存する
• インデックス
• 統計

このリストは完全なものではなく、責任にあまり正確に分割されていませんが、私たちの検討では満足のいくものです。



Storage Engine APIは、触ることができないほど聖なる牛です。

このStorage Engine APIがどのように「完全に」作成され、どのような問題が発生するかについて、すばらしい素晴らしい例を挙げたいと思います。



このバグに苦労しながらこれらの問題を具体的に研究しました： bugs.mysql.com/bug.php?id=63320

誰かがバグとソースコードの両方を理解したいという強さと欲求を感じたら、幸運を祈ります。

怠け者で私の話を信頼している人は、そのまま読み進めてください。



それでは、ストレージエンジンはオプティマイザーにどのような情報を報告/提供できますか？

• テーブル内のレコード数（row_countとする）
• 整数値rows_per_key（number_of_records_ofのnumber_of unique_recordsに対する比率）。

注意-一意のエントリの数は直接認識できません。

MySQLは、テーブル内のレコード数/ rows_per_keyと見なします



これをコードの形式で記述します（結果としてどのように計算されるか）：

int unique_row_count_mysql = row_count / rows_per_key; int rows_per_key = row_count / unique_row_count_engine; int unique_row_count_mysql = row_count / ((int) (row_count / unique_row_count_engine));
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

私が説明する：

 a2 = (int)b / ((int) (b / a));
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この機能がないため、数を2回連続で正確に丸めることができません。

例について説明します。 100万レコードのテーブルがあるとします。

異なる数の一意のキーを検討してください。

総記録	一意のエントリ	rows_per_key	一意のエントリ（オプティマイザーによる）
1,000,000	100,000	10	100,000
1,000,000	200,000	5	200,000
1,000,000	300,000	3	333333
1,000,000	400,000	2	500,000
1,000,000	500,000	2	500,000
1,000,000	600,000	1	1,000,000
1,000,000	700,000	1	1,000,000
1,000,000	800,000	1	1,000,000
1,000,000	900,000	1	1,000,000
1,000,000	1,000,000	1	1,000,000

好きですか？

Storage Engine APIを除き、精度を失う理由はありません。

APIに触れることはできません。

この問題は、すべてのストレージエンジンに適用されます。



これは戦いの半分です。 さらに進んでいます。

効果的なプランを選択するには、オプティマイザーが一意のレコードの数と合計のレコード数を知るだけでは不十分です。

多くの場合、彼はNULLの数、非NULLの数、複合キーの場合-さまざまな組み合わせを知る必要があります。

多くの場合、これは次の場合です。

• 内部結合（「単純」結合）
• 外部結合（左/右/完全）
• サブクエリ（/にない/すべて/すべて/ある/存在する/存在しない）

NULLの統計を使用すると、さまざまな効果的な最適化を構築できます。

これについては少し後で、最初に問題を説明します。



Storage Engine APIには、nullを他のエントリと区別する_機能がありません。

ほとんどありません。

InnoDBとMyISAMは、innodb_stats_methodとmyisam_stats_methodの2つのグローバル変数を実装しています。

これらのグローバル変数の意味は次のとおりです。

• ヌルは無視する
• ヌルは等しい
• 等しくないヌル。

これをソースコードで初めて見たとき、自分の目を信じることができませんでした。

これらの変数は、結合キーとサブクエリにヌルがあるすべてのテーブルとクエリの統計情報の収集に影響します。



なぜこれが悪いのですか？

簡単なリクエストを次に示します。

  a.id = b.idの結合bから*を選択します 

nullの場合は無視-すべてが正常です

ヌルが等しい場合-すべてが正常です

NULLが等しくない場合-すべてが非常に悪い場合、オプティマイザーは悲観的な計画を選択します-結合の出力に多くのレコードがあると仮定します。



別のリクエスト

  a.id = b.idの左結合bから*を選択します 

等しくないnullの場合-すべてが正常です

nulls equalsまたはnulls ignore-すべてが非常に悪い場合、オプティマイザーは楽観的な計画を選択します-結合の出力にレコードがほとんどないと仮定します。



サブクエリの場合、状況はこれとそれの両方です。

実際、私が言及したバグは、innodb_stats_method設定に基づいてレコード数を計算する機能に関するものです。

このアーキテクチャは当初、nullを他のすべてのものから分離することを提供していませんでしたが、現在では開発者はよりunningな松葉杖とヒューリスティックを考え出すことで苦労しています。



私の観点から-これは死んだ湿布です。

AST（抽象構文ツリー）の上に構築されたMySQLオプティマイザーは死んでおり、開発も改善もできません。 これは多くの時間を費やす大きな穴であり、クエリのパフォーマンスを改善する機会を提供しません。



Storage Engine自体は高速ですが、オプティマイザーはそれらを使用できません。 API、機能、通常の統計はありません。



この状況が根本的に変わるとは思わない。 基本的に、MySQLのかなりの部分を捨てて、ゼロから書き直す必要があります。

しかし、それは完全に異なる製品になります。