MySQL開発者向けのヒントとコツ。 SQLを使用する

私の考えでは、この記事は、MySQLデータベースでのSQLクエリの使用と最適化に関するいくつかの興味深いポイントを集めたものであり、インターネットではあまり取り上げられていません。 したがって、この記事から、ロールアップを使用した構築、結合するサブクエリではなくリライトする方法、および複数のテーブルのデータを更新および削除する方法（1回のリクエストなど）について学習します。 順番に始めましょう。

結合するために、inからではなくサブクエリを書き換える

最も一般的なサブクエリの1つは、inとnotのクエリです。 さらに、MySQLでは、オプティマイザーによるクエリ実行プランの効率的な構築により、クエリの結合が最も頻繁に行われることがわかっています（特定の条件下、および他のデータベースでは、このステートメントは正反対になります）。 これをどのように行いますか？ まず、クエリで両方のテーブルに存在するすべての値が出力されることを理解しましょう。つまり、内部内部結合がこのクエリと一意に一致することを意味します。 反対に、in inを持たないクエリは、サブクエリに存在しないすべての値を表示します。つまり、外部外部結合は既にそれらに対応しています。 それで、始められました、それについて何かをしようとしましょう。



たとえば、テストデータベースの世界を使用します。これは、公式のmysql Webサイトから入手できます。



worldデータベースには、Country（国）とCountryLanguage（公用語）のテーブルがあります。 私たちは、少なくとも1つの言語を話すすべての国を見つけるタスクを設定しました。 言い換えると、公用語を持っている人口のいる地域を探しています。 おなじみのサブクエリを書きましょう。

SELECT * FROM Country WHERE Code IN (SELECT CountryCode FROM CountryLanguage)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このクエリは、次のように書き換えることもできます。

 SELECT * FROM Country c WHERE EXISTS ( SELECT 1 FROM CountryLanguage cl WHERE c.Code = cl.CountryCode )
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

さて、上記の仮定に基づいて、サブクエリを内部結合に書き換えます。

 SELECT c.Name FROM Country c INNER JOIN CountryLanguage cl ON c.Code = cl.CountryCode +-------------+ | Name | +-------------+ | Aruba | | Aruba | | Aruba | | Aruba | | Afghanistan | +-------------+ 5 rows in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

それはほとんど起こりましたが、データの重複があったため、別のオプションを使用して削除しました。 テーブルのすべてのフィールドの最終バージョンは次のとおりです。

 SELECT DISTINCT c.* FROM Country c INNER JOIN CountryLanguage cl ON c.Code = cl.CountryCode
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

いいね！ のサブクエリは、正常に書き直されて結合されました。



少し複雑になりました-外部結合ではなく書き換えます。 リクエストの目的は、人々が住んでおらず、公用語がないすべての地域です。 繰り返しますが、最初に標準をサブクエリに含めません。

 SELECT Name FROM Country WHERE Code NOT IN (SELECT CountryCode FROM CountryLanguage)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、存在しないことを示す：

 SELECT Name FROM Country c WHERE NOT EXISTS ( SELECT 1 FROM CountryLanguage cl WHERE c.Code = cl.CountryCode )
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

最初の場合と同様に、左結合に書き換えます：

 SELECT c.* FROM Country c LEFT JOIN CountryLanguage cl ON c.Code = cl.CountryCode
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

その結果、最初の場合と同様に、データの複製、そしてもちろん、2番目のテーブルでペア値が見つからなかった行を取得します。 問題の解決策を提供するのはこれらの行なので、すべてのペアの行を削除します。

 SELECT c.* FROM Country c LEFT JOIN CountryLanguage cl ON c.Code = cl.CountryCode WHERE cl.CountryCode IS NULL
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これらの単純な変換により、クエリオプティマイザーを少し支援することができました。

サブクエリの行の比較

1つではなく複数の列で比較を実行するサブクエリを記述する必要がある場合はまれですが、これを記述するのは明らかに間違っています。

 SELECT c.Name FROM City c WHERE c.ID = (SELECT Capital FROM Country WHERE Name='Finland') AND c.CountryCode = (SELECT Code FROM Country WHERE Name='Finland')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これらの目的のために、SQLクエリがあります。

 SELECT c.Name FROM City c WHERE (c.ID, c.CountryCode) = (SELECT Capital, Code FROM Country WHERE Name='Finland')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このようなクエリは「文字列コンストラクター」と呼ばれ、ROW（）関数で強調できます。 この場合、次のように記述します。

 SELECT c.Name FROM City c WHERE ROW(c.ID, c.CountryCode) = (SELECT Capital, Code FROM Country WHERE Name='Finland')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

その魅力にもかかわらず、文字列コンストラクタにはいくつかの制限があります。

1.サブクエリは、複数ではなく1つの行を返す必要があります

2.比較演算子<、>または<>は使用できませんが、この制限は特殊な単語all、any、inまたはexistsで回避できます



このような構造は、サブクエリだけでなく、スカラー式でも使用できることに注意してください。

 SELECT Name, Population FROM Country WHERE (Continent, Region) = ('Europe', 'Western Europe')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ただし、実際には、クエリデザイナはスカラー式に対してあまり効率的ではないため、クエリを通常の形式に書き換えます。

 SELECT Name, Population FROM Country WHERE Continent = 'Europe' AND Region = 'Western Europe'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

複数のテーブルのデータを同時に更新および削除します。

おそらくそのような見出しは誰かを驚かせるでしょうが、なぜですか？ データを更新することから始めましょう。 公式ドキュメントには、次の構文が記載されています。

 UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition]
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ほとんどの場合、次の形式の要求を行います。

 UPDATE t1, t2 SET t1.name1 = t2.name2 WHERE t1.id = t2.id;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

一方、2つ、3つ、またはそれ以上のテーブルのデータを一度に更新する要求を行うことを誰も気にしません。

 UPDATE t1, t2 SET t1.name1 = 'name1', t2.name2 = 'name2' WHERE t1.id = t2.id;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

確かに、それは意味をなさない可能性が高いですが、それにもかかわらず、これは可能です。



削除操作では、さらに興味深いことがあります。 公式ドキュメントでは、次の構文を宣言しています：

 DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition]
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

どちらか

 DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references [WHERE where_condition]
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ビュー要求に一致するもの：

 DELETE t1 FROM t1, t2 WHERE t1.id = t2.id; DELETE FROM t1 USING t1, t2 WHERE t1.id = t2.id;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これら2つのクエリでは、テーブルt1から削除が行われ、t2を使用してデータ選択条件が作成されます。



ご想像のとおり、2つのテーブルから同時にデータを削除するには、次のようにします。

 DELETE t1, t2 FROM t1, t2 WHERE t1.id = t2.id; DELETE FROM t1, t2 USING t1, t2 WHERE t1.id = t2.id;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

OLAPについて少し説明します。 WITH ROLLUP修飾子

タイトルを読んでいる間にOracleやSQL Serverのような産業用データベースに出くわした人たちは、「まあ、それは必要です！」と叫びました。 MySQL 4.1.1以降、with rollup修飾子が登場したとき、このトピックは1ミリも進んでいなかったため、このデータベースの組み込みツールを使用してデータキューブを構築することはできません。



with rollup modifierの意味がわからない人のために、小計と最終合計値を含むレポートの作成に使用されることを簡単に説明します。 例では、再びワールドベースを使用します。



すべての地理的領域（地域）だけでなく、大陸および世界中に住む人々の総数と平均数を取得する必要があるとします。 額を決めると、次のクエリが表示されます。



すべての地理的領域（地域）に住む人々の総数と平均数：

 SELECT Continent, Region, SUM(Population) AS pop, AVG(Population) AS avg_pop FROM Country GROUP BY Continent, Region
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

すべての大陸に住んでいる人の総数と平均数：

 SELECT Continent, SUM(Population) AS pop, AVG(Population) AS avg_pop FROM Country GROUP BY Continent
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

世界中に住んでいる人の総数と平均数：

 SELECT SUM(Population) AS pop, AVG(Population) AS avg_pop FROM Country
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これらのクエリを実行してから結果を複雑に結合する代わりに、1つだけ実行できます。

 SELECT Continent, Region, SUM(Population) AS pop, AVG(Population) AS avg_pop FROM Country GROUP BY Continent, Region WITH ROLLUP; +---------------+---------------------------+------------+----------------+ | Continent | Region | pop | avg_pop | +---------------+---------------------------+------------+----------------+ | Asia | Eastern Asia | 1507328000 | 188416000.0000 | | Asia | Middle East | 188380700 | 10465594.4444 | | Asia | Southeast Asia | 518541000 | 47140090.9091 | | Asia | Southern and Central Asia | 1490776000 | 106484000.0000 | | Asia | NULL | 3705025700 | 72647562.7451 | | Europe | Baltic Countries | 7561900 | 2520633.3333 | | Europe | British Islands | 63398500 | 31699250.0000 | ... | Europe | Eastern Europe | 307026000 | 30702600.0000 | | Europe | Nordic Countries | 24166400 | 3452342.8571 | | Europe | Southern Europe | 144674200 | 9644946.6667 | ... | Europe | Western Europe | 183247600 | 20360844.4444 | | Europe | NULL | 730074600 | 15871186.9565 | | North America | Caribbean | 38140000 | 1589166.6667 | | North America | Central America | 135221000 | 16902625.0000 | | North America | North America | 309632000 | 61926400.0000 | | North America | NULL | 482993000 | 13053864.8649 | | Africa | Central Africa | 95652000 | 10628000.0000 | ... | Africa | Eastern Africa | 246999000 | 12349950.0000 | | Africa | Northern Africa | 173266000 | 24752285.7143 | | Africa | Southern Africa | 46886000 | 9377200.0000 | ... | Africa | Western Africa | 221672000 | 13039529.4118 | | Africa | NULL | 784475000 | 13525431.0345 | | Oceania | Australia and New Zealand | 22753100 | 4550620.0000 | ... | Oceania | Melanesia | 6472000 | 1294400.0000 | | Oceania | Micronesia | 543000 | 77571.4286 | | Oceania | Micronesia/Caribbean | 0 | 0.0000 | ... | Oceania | Polynesia | 633050 | 63305.0000 | | Oceania | NULL | 30401150 | 1085755.3571 | | Antarctica | Antarctica | 0 | 0.0000 | | Antarctica | NULL | 0 | 0.0000 | | South America | South America | 345780000 | 24698571.4286 | | South America | NULL | 345780000 | 24698571.4286 | | NULL | NULL | 6078749450 | 25434098.1172 | +---------------+---------------------------+------------+----------------+
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

非集計列の一部の行はNULLであることに注意してください。これは、この行が小計であることを示しています。 たとえば、次の行

 | South America | NULL | 345780000 | 24698571.4286 |
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

南アメリカの総人口は3億4,785万人で、平均値は24698571.4286であることがわかります。



そして、これが行です

 | NULL | NULL | 6078749450 | 25434098.1172 |
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これは、地球全体の人口に関する最終結果です。



with rollup修飾子のプラスの効果は、レコードを1回繰り返すことです！ ところで、この機能は、サイト（プログラム）の統計を表示するときに非常に便利です。 この機能に興味がある場合、または質問がある場合は、詳細について公式文書を要求します

起動オプション--i-am-a-dummy

口語の「--I-AM-A-DUMMY」は、「私はバカです」と翻訳されます。 このオプションは、初心者向けに作成された--safe-updatesオプションと同義であり、お気に入りのデータベースがアクションによって悪くならないように、いくつかの制限を課しています。

1.更新および削除リクエストは、条件がwhereまたはlimitである場合にのみ実行されます

2.制限が明示的に指定されていない限り、1つのテーブルを選択（join'ovおよびサブクエリなし）は最初の1000行のみを返します

3.結合またはサブクエリでクエリを選択し、最初の1,000,000行のみを処理します



これらの制限を無効にするには、パラメーターを使用して次のクエリを実行します。

 SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

現在の設定を表示するには：

 SELECT @@sql_safe_updates, @@sql_select_limit, @@sql_max_join_size
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

シェルでクライアントを起動するときに設定をパラメーター化することもできます

 mysql -u user -p -h host --i-am-a-dummy --select_limit=500 --max_join_size=10000
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

美的コメント

おやつに。 MySQL以外のデータベースの後、標準の必須の制御文字はないはずなのに、二重ダッシュのように見える単一行コメントのMySQLには必ずスペース、タブ、または他の制御文字が必要になる理由にいつも驚きました。 同意して、何らかのリクエストを作成し、コードの一部をすばやくコメントアウトする必要がある場合、非常に多くの文字を入力するのに非常に長い時間がかかります。



どういう意味ですか？ MySQLでは、次のように記述します。

 -- SELECT 1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

（SELECTの前にスペースを使用）、および他のデータベースでは：

 --SELECT 1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

（制御文字なし）。



答えは非常にシンプルでした。 事実、このような不注意なリクエストを書くと

 UPDATE account SET credit=credit--1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

その場合、最初のダッシュはマイナスと解釈され、2番目のダッシュはコメントと解釈されません。 2つのダッシュの後に制御文字を入力すると、この場合のみコメントが表示されます。 このコメントの詳細については、 こちらをご覧ください。



その結果、ほとんどの場合、単一行コメントの作業では、制御文字を含む二重ダッシュではなくポンド記号（＃）を使用します:-)



UDP：

参加から参加へのリクエストの書き換えの適切性に関するコメントには疑問があります。 以下、私の小さなベンチマーク。



ソースデータ：

 mysql> SELECT @@version; +-------------------------+ | @@version | +-------------------------+ | 5.5.32-0ubuntu0.12.04.1 | +-------------------------+ 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE t11\G *************************** 1. row *************************** Table: t11 Create Table: CREATE TABLE `t11` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `t11` varchar(45) NOT NULL, PRIMARY KEY (`id`), KEY `t11` (`t11`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> SELECT COUNT(*) FROM t11; +----------+ | COUNT(*) | +----------+ | 977360 | +----------+ 1 row in set (0.03 sec) mysql> SHOW CREATE TABLE t22\G *************************** 1. row *************************** Table: t22 Create Table: CREATE TABLE `t22` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `t22` varchar(45) NOT NULL, PRIMARY KEY (`id`), KEY `t22` (`t22`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> SELECT COUNT(*) FROM t22; +----------+ | COUNT(*) | +----------+ | 962078 | +----------+ 1 row in set (0.04 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

JOIN vs IN vs EXISTS



JOINインデックス（7.84秒）を使用すると、IN（1.74秒）およびEXISTS（2.44秒）と比較して大幅に失われます。

以下は、列t11およびt22にインデックスがない場合の例です。

 mysql> SELECT SQL_NO_CACHE * -> FROM t11 -> WHERE t11 IN (SELECT t22 FROM t22) -> LIMIT 1000; +------+-------+ | id | t11 | +------+-------+ | 2 | 5718 | | ........ | | 1001 | 54192 | +------+-------+ 1000 rows in set (13.59 sec) mysql> SELECT SQL_NO_CACHE * -> FROM t11 c -> WHERE EXISTS ( -> SELECT 1 FROM t22 cl WHERE c.t11 = cl.t22 -> ) -> LIMIT 1000; +------+-------+ | id | t11 | +------+-------+ | 2 | 5718 | | ........ | | 1001 | 54192 | +------+-------+ 1000 rows in set (13.94 sec) mysql> SELECT DISTINCT SQL_NO_CACHE c.* -> FROM t11 c -> INNER JOIN t22 cl ON c.t11 = cl.t22 -> LIMIT 1000; +-------+-------+ | id | t11 | +-------+-------+ | 25 | 74376 | | ......... | | 34359 | 62666 | +-------+-------+ 1000 rows in set (3.36 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

回答を長時間待たないように制限を設定します。 結果には影響しません。



外部結合vs NOT IN vs NOT EXISTS



インデックスがない場合、これらのクエリはMySQL 5.5でほぼ同時に処理されます。

以下は、インデックスを使用した例です。

 mysql> SELECT SQL_NO_CACHE * -> FROM t11 -> WHERE t11 NOT IN (SELECT t22 FROM t22) -> LIMIT 1000; +--------+---------------------+ | id | t11 | +--------+---------------------+ | 1 | 0.23670763544431667 | | ........................ | | 891610 | 97811 | +--------+---------------------+ 91 rows in set (1.36 sec) mysql> SELECT SQL_NO_CACHE * -> FROM t11 c -> WHERE NOT EXISTS ( -> SELECT 1 FROM t22 cl WHERE c.t11 = cl.t22 -> ) -> LIMIT 1000; +--------+---------------------+ | id | t11 | +--------+---------------------+ | 1 | 0.23670763544431667 | | ........................ | | 891610 | 97811 | +--------+---------------------+ 91 rows in set (1.87 sec) mysql> SELECT SQL_NO_CACHE c.* -> FROM t11 c -> LEFT JOIN t22 cl ON c.t11 = cl.t22 -> WHERE cl.t22 IS NULL -> LIMIT 1000; +--------+---------------------+ | id | t11 | +--------+---------------------+ | 1 | 0.23670763544431667 | | ........................ | | 891610 | 97811 | +--------+---------------------+ 91 rows in set (1.27 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果として、結果はデー​​タベースのバージョンとソースデータに依存します！