mysqlデータベースの最適化まとめ

PHP 開発で mysql データベースを使用することは、すべての PHP プログラマーの習慣になっているようです。PHP で mysql をより高速かつ便利に動作させたい場合は、mysql データベースの最適化も面接でよく質問されます。一緒に見てみましょう！

1. MySQL クエリ キャッシュを最適化する

MySQL サーバーでクエリを実行する場合、高速クエリ キャッシュを有効にすることができます。データベース エンジンにバックグラウンドで静かに処理させることは、パフォーマンスを向上させる最も効果的な方法の 1 つです。同じクエリが複数回実行される場合、結果がキャッシュから取得されると非常に高速になります。
しかし、主な問題は、それがあまりにも簡単に隠蔽されてしまうため、私たちプログラマーのほとんどがそれを無視していることです。一部の処理タスクでは、実際にクエリ キャッシュが機能しなくなる可能性があります。

// query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 // query cache works!
 $today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
 // query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 // query cache works!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

2. EXPLAIN を使用して SELECT クエリを明確にする

EXPLAIN キーワードを使用すると、MySQL が実行しているクエリ操作の種類を理解できるようになります。ボトルネックを見つけて、クエリまたはテーブル構造のどこで問題が発生しているかを示すのに役立ちます。
EXPLAIN クエリの結果から、どのインデックスが参照されているか、テーブルがどのようにスキャンされ、並べ替えられているかなどがわかります。
SELECT クエリ (結合を含むより複雑なクエリが望ましい) を実装し、そこにキーワードの説明を追加します。これにより、テーブル内の結果が表示されます。たとえば、結合を実行するときにインデックスに列を追加するのを忘れた場合、EXPLAIN は問題の発見に役立ちます。

group_id フィールドにインデックスを追加した後

3. LIMIT 1 を使用して一意の行を取得します

場合によっては、テーブルにクエリを実行する必要があることがわかっている場合があります。 1行を見てください。非常にユニークなレコードを探している場合もあれば、WHERE 句を満たす存在するレコードの数を確認しているだけである場合もあります。
この場合、LIMIT 1 を追加するとクエリがより効率的になります。このようにして、データベース エンジンはテーブルまたはインデックス全体をスキャンするのではなく、1 つだけを見つけた後にスキャンを停止します。

 // do I have any users from Alabama?   
 // what NOT to do:   
 $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");   
 if (mysql_num_rows($r) > 0) {   
     // ...   
 }     
 // much better:   
 $r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");   
 if (mysql_num_rows($r) > 0) {   
     // ...   
 }

4. インデックス内の検索フィールド

インデックスは主キーや一意のキーだけではありません。テーブル内の任意の列を検索する場合は、常にインデックスを指定する必要があります。

5. 結合インデックスが同じタイプであることを確認します

アプリケーションに複数の結合クエリが含まれている場合は、リンクする列が両方のテーブルでインデックス付けされていることを確認する必要があります。これは、MySQL が内部結合操作を最適化する方法に影響します。
さらに、追加された列は同じタイプである必要があります。たとえば、DECIMAL カラムを結合し、同時に別のテーブルの int カラムを結合する場合、MySQL は少なくとも 1 つのインジケータを使用できなくなります。文字エンコーディングも文字列型と同じである必要があります。

 // looking for companies in my state   
 $r = mysql_query("SELECT company_name FROM users  
     LEFT JOIN companies ON (users.state = companies.state)  
     WHERE users.id = $user_id");   

 // both state columns should be indexed   
 // and they both should be the same type and character encoding   
 // or MySQL might do full table scans

6. BY RAND() コマンドを使用しないでください

这是一个令很多新手程序员会掉进去的陷阱。你可能不知不觉中制造了一个可怕的平静。这个陷阱在你是用BY RAND（）命令时就开始创建了。
如果您真的需要随机显示你的结果，有很多更好的途径去实现。诚然这需要写更多的代码，但是能避免性能瓶颈的出现。问题在于，MySQL可能会为表中每一个独立的行执行BY RAND()命令（这会消耗处理器的处理能力），然后给你仅仅返回一行。

 // what NOT to do:   
 $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");   
 // much better:   
 $r = mysql_query("SELECT count(*) FROM user");   
 $d = mysql_fetch_row($r);   
 $rand = mt_rand(0,$d[0] - 1);   
 $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

7. 尽量避免SELECT *命令

从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。
始终指定你需要的列，这是一个非常良好的习惯。

 // not preferred   
 $r = mysql_query("SELECT * FROM user WHERE user_id = 1");   
 $d = mysql_fetch_assoc($r);   
 echo "Welcome {$d['username']}";   
 // better:   
 $r = mysql_query("SELECT username FROM user WHERE user_id = 1");   
 $d = mysql_fetch_assoc($r);   
 echo "Welcome {$d['username']}";    
 // the differences are more significant with bigger result sets

8. 从PROCEDURE ANALYSE()中获得建议

PROCEDURE ANALYSE()可让MySQL的柱结构分析和表中的实际数据来给你一些建议。如果你的表中已经存在实际数据了，能为你的重大决策服务。

9. 准备好的语句

准备好的语句，可以从性能优化和安全两方面对大家有所帮助。
准备好的语句在过滤已经绑定的变量默认情况下，能给应用程序以有效的保护，防止SQL注入攻击。当然你也可以手动过滤，不过由于大多数程序员健忘的性格，很难达到效果。

 // create a prepared statement   
 if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {    
     // bind parameters   
     $stmt->bind_param("s", $state);    
     // execute   
     $stmt->execute();    
     // bind result variables   
     $stmt->bind_result($username);     
     // fetch value   
     $stmt->fetch();    
     printf("%s is from %s\n", $username, $state);     
     $stmt->close();   
 }

10. 将IP地址存储为无符号整型

许多程序员在创建一个VARCHAR（15）时并没有意识到他们可以将IP地址以整数形式来存储。当你有一个INT类型时，你只占用4个字节的空间，这是一个固定大小的领域。
你必须确定你所操作的列是一个UNSIGNED INT类型的,因为IP地址将使用32位unsigned integer。

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

11.永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的(推荐使用UNSIGNED)，并设置上自动增加的AUTO_INCREMENT标志。
就算是你users表有一个主键叫“email”的字段，你也别让它成为主键。使用VARCHAR类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。
而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……
在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫“外键”其共同组成主键。

12. VARCHAR の代わりに ENUM を使用します

ENUM 型は非常に高速でコンパクトです。実際には、TINYINT が保存されますが、文字列として表示されます。このようにして、このフィールドを使用して選択リストを作成するのが非常に完璧になります。
「性別」、「国」、「民族」、「ステータス」、「部門」などのフィールドがあり、これらのフィールドの値が制限され固定されていることがわかっている場合は、ENUM を使用する必要があります。 VARCHAR の代わりに。
MySQL には、テーブル構造を再編成する方法を示す「提案」 (項目 10 を参照) もあります。 VARCHAR フィールドがある場合、この提案はそれを ENUM 型に変更するように指示します。 PROCEDURE ANALYSE() を使用すると、関連する提案を取得できます。

13. PROCEDURE ANALYSE() から提案を取得する p プログラマー ステーション

PROCEDURE ANALYSE() は、MySQL を使用してフィールドとその実際のデータを分析し、いくつかの有用な提案を提供します。いくつかの大きな決定を下すには基礎としてデータが必要であるため、これらの提案はテーブルに実際のデータがある場合にのみ役に立ちます。
たとえば、主キーとして INT フィールドを作成したが、データがあまりない場合、PROCEDURE ANALYSE() はこのフィールドのタイプを MEDIUMINT に変更することを推奨します。または、VARCHAR フィールドを使用している場合は、データが少ないため、ENUM に変更するよう提案される場合があります。これらの提案はすべて、十分なデータがなく、意思決定が十分に正確ではないために可能になります。
phpmyadmin では、テーブルを表示するときに [テーブル構造の提案] をクリックしてこれらの提案を表示できます。これらの提案は、テーブル内のデータが増えた場合にのみ変更されることに注意してください。正確な。最終決定を下すのは自分であることを常に忘れないでください

14. 可能な限り NOT NULL php プログラマ ステーションを使用してください
NULL 値を使用する特別な理由がない限り、常に次のようにする必要があります。フィールドは NULL にしないでください。これは少し物議を醸すかもしれませんが、読み続けてください。

まず、「Empty」と「NULL」(INT の場合、0 と NULL) の違いは何なのかを自問してください。両者に違いがないと思われる場合は、NULL を使用しないでください。 (ご存知ですか? Oracle では、NULL と空の文字列は同じです!)

NULL にはスペースが必要ないと考えないでください。NULL には追加のスペースが必要であり、比較を実行するとプログラムがより複雑になります。もちろん、これは NULL を使用できないという意味ではありません。現実は非常に複雑なので、NULL 値を使用する必要がある状況が依然として存在します。
以下は MySQL 自身のドキュメントからの抜粋です:

15. プリペアド ステートメント

プリペアド ステートメントは、バックグラウンドで実行される SQL ステートメントのコレクションに似ています。パフォーマンスの問題であれ、セキュリティの問題であれ、プリペアドステートメントの使用から解放されます。

プリペアド ステートメントはバインドした一部の変数をチェックできるため、プログラムを「SQL インジェクション」攻撃から保護できます。もちろん、変数を手動でチェックすることもできますが、手動によるチェックは問題が発生しやすく、プログラマによって忘れられることがよくあります。何らかのフレームワークやORMを使用すると、この問題は改善されるでしょう。 パフォーマンスの面では、同じクエリが複数回使用される場合に、パフォーマンスが大幅に向上します。これらのプリペアド ステートメントにはいくつかのパラメータを定義できますが、MySQL はそれらを 1 回だけ解析します。 最新バージョンの MySQL はプリペアド ステートメントの送信時にバイナリ形式を使用しますが、これによりネットワーク送信が非常に効率的になります。
もちろん、プリペアド ステートメントはクエリ キャッシュをサポートしていないため、プリペアド ステートメントの使用を避ける必要がある場合もあります。ただし、バージョン5.1以降でサポートされるとのこと。 PHP でプリペアド ステートメントを使用するには、そのマニュアルを確認するか、mysqli 拡張機能を確認するか、PDO.

16 などのデータベース抽象化レイヤーを使用します。

通常の状況では、スクリプト内で SQL ステートメントを実行すると、SQL ステートメントが返されなくなるまでプログラムはそこで停止し、その後、プログラムは実行を続けます。バッファリングされていないクエリを使用して、この動作を変更できます。
この問題については、PHP ドキュメントに非常に詳しい説明があります: mysql_unbuffered_query() 関数:
上記の文の翻訳は、mysql_unbuffered_query() が SQL ステートメントを MySQL に送信し、mysql_query() とは異なることを意味します。自動フェッチとキャッシュされた結果の場合。これにより、特に多数の結果を生成するクエリの場合に、かなりのメモリが節約され、すべての結果が返されるまで待つ必要がなく、データの最初の行を返すだけで開始できます。クエリ結果はすぐに表示されます。
ただし、これにはいくつかの制限があります。すべての行を読み取るか、次のクエリの前に mysql_free_result() を呼び出して結果をクリアする必要があるためです。また、mysql_num_rows() または mysql_data_seek() は機能しません。したがって、バッファなしクエリを使用するかどうかを慎重に検討する必要があります。

17. IP アドレスを UNSIGNED INT として保存します

多くのプログラマは、IP を整数 IP の代わりに文字列形式で保存するために VARCHAR(15) フィールドを作成します。整数を使用して格納する場合、必要なバイト数は 4 バイトだけであり、固定長のフィールドを持つことができます。さらに、これは特に、ip1 と ip2 の間の IP のような WHERE 条件を使用する必要がある場合に、クエリに利点をもたらします。
IP アドレスは 32 ビットの符号なし整数全体を使用するため、UNSIGNED INT を使用する必要があります。
クエリでは、INET_ATON() を使用して文字列 IP を整数に変換し、INET_NTOA() を使用して整数を文字列 IP に変換できます。 PHP には、ip2long() および long2ip() という関数もあります。

18. 固定長テーブルは高速になります

テーブル内のすべてのフィールドが「固定長」の場合、テーブル全体が「静的」または「固定長」とみなされます。 たとえば、テーブルには VARCHAR、TEXT、BLOB 型のフィールドはありません。これらのフィールドのいずれかを含めている限り、テーブルは「固定長の静的テーブル」ではなくなり、MySQL エンジンは別の方法でテーブルを処理します。
固定長テーブルは MySQL の検索を高速化するため、パフォーマンスが向上します。これらの固定長により次のデータのオフセットの計算が容易になるため、当然読み取りも高速になります。また、フィールドが固定長でない場合、次のフィールドを検索するたびに、プログラムは主キーを検索する必要があります。
また、固定長テーブルはキャッシュと再構築が簡単です。ただし、唯一の副作用は、固定長フィールドは使用するかどうかに関係なく非常に多くのスペースを必要とするため、固定長フィールドによってスペースが浪費されることです。 PHP プログラマー ステーション
「垂直分割」テクノロジ (次の項目を参照) を使用すると、テーブルを 2 つに分割し、1 つは固定長、もう 1 つは可変長にすることができます。

19. 垂直分割

「垂直分割」は、データベース内のテーブルを列ごとに複数のテーブルに変換する方法で、テーブルの複雑さとフィールドの数を削減し、最適化を実現します。の目標。 (私は以前銀行でプロジェクトを行っていたのですが、100 を超えるフィールドがあるテーブルを見ました。これは怖かったです)
例 1: Users テーブルに自宅の住所であるフィールドがあります。このフィールドは、When you と比較するとオプションです。 データベースを操作する場合、個人情報を除き、このフィールドを頻繁に読み取ったり書き換えたりする必要はありません。では、別のテーブルに入れてみてはいかがでしょうか? これにより、テーブルが改善されます

関連する推奨事項:

MySQL 最適化の概要 - クエリの総数

以上がmysqlデータベースの最適化まとめの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。