序章


MySQLとMariaDBは、データベース管理システムの一般的な選択肢です。 どちらもSQLクエリ言語を使用してデータを入力およびクエリします。

SQLクエリは習得が容易な単純なコマンドですが、すべてのクエリとデータベース関数が同じ効率で動作するわけではありません。 これは、保存する情報の量が増えるにつれて、またデータベースがWebサイトをサポートしている場合は、サイトの人気が高まるにつれて、ますます重要になります。

このガイドでは、MySQLおよびMariaDBクエリを高速化するために実行できるいくつかの簡単な方法について説明します。 オペレーティングシステムに適したガイドのいずれかを使用して、MySQLまたはMariaDBがすでにインストールされていることを前提としています。

テーブルデザインの一般性


クエリ速度を向上させる最も基本的な方法の1つは、テーブル構造の設計自体から始まります。 これは、ソフトウェアの使用を開始する前にデータを整理するための最良の方法を検討し始める必要があることを意味します

これらはあなたがあなた自身に尋ねるべきであるいくつかの質問です:

あなたのテーブルは主にどのように使用されますか?


テーブルのデータをどのように使用するかを予測することで、データ構造を設計するための最良のアプローチが決まることがよくあります。

特定のデータを頻繁に更新する場合は、それらを独自のテーブルに配置するのが最適な場合がよくあります。 これを行わないと、ソフトウェア内に保持されている内部キャッシュであるクエリキャッシュがダンプされ、新しい情報があることを認識するため、何度も再構築される可能性があります。 これが別のテーブルで発生した場合、他の列は引き続きキャッシュを利用できます。

一般に、更新操作は小さいテーブルでは高速ですが、結合はコストのかかる操作になる可能性があるため、複雑なデータの詳細な分析は通常、大きいテーブルに任せるのが最適なタスクです。

どのような種類のデータタイプが必要ですか?


データサイズに事前に制限を設けることができれば、長期的には大幅な時間を節約できる場合があります。

たとえば、文字列値を受け取る特定のフィールドの有効なエントリの数が限られている場合は、「varchar」の代わりに「enum」タイプを使用できます。 このデータ型はコンパクトであるため、クエリをすばやく実行できます。

たとえば、ユーザーの種類が数種類しかない場合は、その「列挙型」を処理する列を、可能な値(admin、moderator、poweruser、user)で作成できます。

どの列をクエリしますか?


繰り返しクエリを実行するフィールドを事前に知っておくと、速度が大幅に向上します。

検索に使用する予定の列にインデックスを付けると、非常に役立ちます。 次の構文を使用して、テーブルを作成するときにインデックスを追加できます。

CREATE TABLE example_table(id INTEGER NOT NULL AUTO_INCREMENT、name VARCHAR(50)、address VARCHAR(150)、username VARCHAR(16)、PRIMARY KEY(id)、 INDEX(username) );

これは、ユーザーがユーザー名で情報を検索することがわかっている場合に役立ちます。 これにより、次のプロパティを持つテーブルが作成されます。

 example_tableを説明します。
  ±---------±-------------±-----±----±--------±----- ---------- + | フィールド| タイプ| ヌル| キー| デフォルト| エクストラ|  ±---------±-------------±-----±----±--------±----- ---------- + |  id |  int(11)| いいえ|  PRI |  NULL |  auto_increment |  | 名前|  varchar(50)| はい|  |  NULL |  |  | 住所|  varchar(150)| はい|  |  NULL |  |  | ユーザー名|  varchar(16)| はい|  MUL |  NULL |  |  ±---------±-------------±-----±----±--------±----- ---------- + 4行セット(0.00秒)

ご覧のとおり、テーブルには2つのインデックスがあります。 1つ目は主キーで、この場合はidフィールドです。 2つ目は、usernameフィールドに追加したインデックスです。 これにより、このフィールドを利用するクエリが改善されます。

概念的な観点からは、作成時にどのフィールドにインデックスを付けるかを考えると便利ですが、既存のテーブルにもインデックスを追加するのは簡単です。 次のように追加できます。

CREATE INDEX index_name ON table_namecolumn_name );

同じことを達成する別の方法はこれです:

ALTER TABLE table_name ADD INDEX( column_name );

Explainを使用して、クエリでインデックスを作成するポイントを検索します

プログラムが非常に予測可能な方法でクエリを実行している場合は、クエリを分析して、可能な限りインデックスを使用していることを確認する必要があります。 これはexplain機能で簡単です。

MySQLサンプルデータベースをインポートして、これがどのように機能するかを確認します。

wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
tar xjvf employees_db-full-1.0.6.tar.bz2
cd employees_db
mysql -u root -p -t < employees.sql

これで、MySQLに再度ログインして、いくつかのクエリを実行できます。

mysql -u root -p
use employees;

まず、MySQLがキャッシュを使用しないように指定する必要があります。これにより、これらのタスクの完了にかかる時間を正確に判断できます。

SET GLOBAL query_cache_size = 0;
SHOW VARIABLES LIKE "query_cache_size";

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)

これで、大規模なデータセットに対して簡単なクエリを実行できます。

SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.60 sec)

MySQLがクエリを実行する方法を確認するには、クエリの直前にexplainキーワードを追加します。

EXPLAIN SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | salaries | ALL  | NULL          | NULL | NULL    | NULL | 2844738 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

keyフィールドを見ると、その値がNULLであることがわかります。 これは、このクエリにインデックスが使用されていないことを意味します。

1つ追加し、クエリを再度実行して、速度が上がるかどうかを確認しましょう。

ALTER TABLE salaries ADD INDEX ( salary );
SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.14 sec)

ご覧のとおり、これによりクエリのパフォーマンスが大幅に向上します。

インデックスで使用するもう1つの一般的なルールは、テーブルの結合に注意を払うことです。 インデックスを作成し、テーブルの結合に使用されるすべての列に同じデータ型を指定する必要があります。

たとえば、「cheeses」というテーブルと「ingredients」というテーブルがある場合、各テーブルの同様のcomponent_idフィールド(INTの場合もあります)に結合することができます。

次に、これらのフィールドの両方にインデックスを作成すると、結合が高速化されます。

速度のためのクエリの最適化


クエリを高速化しようとするときの方程式の残りの半分は、クエリ自体を最適化することです。 特定の操作は、他の操作よりも計算量が多くなります。 多くの場合、同じ結果を得るには複数の方法があり、そのうちのいくつかはコストのかかる操作を回避します。

クエリ結果の使用目的によっては、限られた数の結果のみが必要になる場合があります。 たとえば、会社に40,000ドル未満の収益を上げている人がいるかどうかを確認するだけでよい場合は、次を使用できます。

SELECT * FROM SALARIES WHERE salary < 40000 LIMIT 1;

+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10022 |  39935 | 2000-09-02 | 2001-09-02 |
+--------+--------+------------+------------+
1 row in set (0.00 sec)

このクエリは、基本的に最初の肯定的な結果で短絡するため、非常に高速に実行されます。

クエリで「または」比較が使用され、2つのコンポーネント部分が異なるフィールドをテストしている場合、クエリは必要以上に長くなる可能性があります。

たとえば、名前が「Bre」で始まる従業員を検索する場合は、2つの別々の列を検索する必要があります。

SELECT * FROM employees WHERE last_name like 'Bre%' OR first_name like 'Bre%';

この操作は、あるクエリで名の検索を実行し、別のクエリで一致する最後の名前の検索を実行してから、出力を組み合わせると、より高速になる可能性があります。 これは、ユニオン演算子を使用して実行できます。

SELECT * FROM employees WHERE last_name like 'Bre%' UNION SELECT * FROM employees WHERE first_name like 'Bre%';

場合によっては、MySQLは自動的に和集合演算を使用します。 上記の例は、実際にはMySQLがこれを自動的に行う場合です。 explainを再度使用して、ソートの種類を確認することで、これが当てはまるかどうかを確認できます。

結論


ユースケースに応じて、MySQLおよびMariaDBのテーブルとデータベースを微調整する方法は非常にたくさんあります。 この記事には、始めるのに役立つかもしれないいくつかのヒントが含まれています。

これらのデータベース管理システムには、さまざまなシナリオを最適化および微調整する方法に関する優れたドキュメントがあります。 詳細は、最適化する機能の種類に大きく依存します。そうでない場合は、そのままで完全に最適化されます。 要件を固め、繰り返し実行される操作を把握したら、それらのクエリの設定を微調整する方法を学ぶことができます。

ジャスティン・エリングウッド