パフォーマンスを向上させるために収集する価値のある 52 の SQL 最適化戦略-見出し-php.cn

SQL ステートメントはデータ操作とデータ定義を実行できるため、ユーザーに大きな利便性をもたらします。この記事では、52 の SQL ステートメントのパフォーマンス最適化戦略について説明します。困っている友達は集めることをお勧めします。 <img src="https://img.php.cn/upload/article/202103/24/2021032416152970237.jpg" style="max-width:90%" style="max-width:90%" border="0" vspace="0" title="" alt="" style="width: 800px; height: 320px;"> #SQL ステートメントのパフォーマンス最適化戦略 1. クエリを最適化するときは、クエリ全体を回避するようにしてください。 table スキャンするときは、<code>WHERE</code> と <code> ORDER BY</code> に関係する列にインデックスを作成することを最初に検討する必要があります。 2. WHERE 句のフィールドで NULL 値の判断を行わないようにしてください。テーブル作成時のデフォルト値は NULL ですが、ほとんどの場合、<code> NOT NULL</code> を使用する必要があります。 <code>0 </code>、<code>-1</code> などの特別な値がデフォルト値として使用されます。 3. WHERE 句では != または <> 演算子を使用しないようにしてください。 MySQL は次の演算子にのみインデックスを使用します: <code><</code>, <code><=</code>, <code>=</code>, <code>></code>, <code>&gt ; =</code>、<code>BETWEEN</code>、<code>IN</code>、および場合によっては <code> LIKE</code>。 4. 条件を接続するために WHERE 句で OR を使用することは避けてください。そうしないと、エンジンはインデックスの使用を放棄し、テーブル全体のスキャンを実行します。UNION を使用してクエリをマージできます。 5. IN と NOT IN も注意して使用する必要があり、使用しないとテーブル全体のスキャンが発生します。連続値の場合、BETWEEN を使用できる場合は IN を使用しないでください。 6. 次のクエリでもテーブル全体のスキャンが発生します: <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false">select id from t where name like‘%abc%’//用到索引</pre><div class="contentsignin">ログイン後にコピー</div></div> または <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false">select id from t where name like‘%abc’//若要提高效率，可以考虑全文检索</pre><div class="contentsignin">ログイン後にコピー</div></div>7. WHERE 句でパラメータが使用されている場合、フルテーブルスキャンも発生します。 8. WHERE 句内のフィールドに対する式操作や関数操作は避けてください。 9. 多くの場合、IN の代わりに <code>EXISTS </code> を使用するのが良い選択です。 10. インデックスにより、対応する SELECT の効率が向上しますが、<code>INSERT </code> と <code>UPDATE </code> も削減されます。インデックスは INSERT または UPDATE 中に再構築される可能性があるため、テーブルに 6 つを超えるインデックスを持たないことをお勧めします。 11. クラスター化インデックスデータ列の順序はテーブルレコードの物理的な格納順序であるため、<code>クラスター化 </code>インデックスデータ列の更新はできる限り避けてください。テーブル全体のレコードの順序を調整すると、かなりのリソースが消費されます。 12. 数値型を使用するようにしてください。数値情報のみを含むフィールドを文字型として設計しないようにすると、クエリと接続のパフォーマンスが低下し、ストレージが増加します。 13. できるだけ <code> char</code>、<code>nchar</code> の代わりに <code> varchar</code>、<code>nvarchar</code> を使用してください。まず第一に、長いフィールドは記憶域が小さく、記憶域を節約できるため、クエリの場合は、比較的小さなフィールドで検索する方が明らかに効率的です。 14. return all: <code>select from t</code> を使用しないことをお勧めします。「*」を特定のフィールドリストに置き換え、未使用のフィールドを返さないようにします。 15. クライアントに大量のデータを返さないようにしてください。データの量が大きすぎる場合は、対応する要件が妥当であるかどうかを検討する必要があります。 16. テーブルエイリアス (Alias) を使用する: SQL ステートメントで複数のテーブルを接続する場合は、テーブルエイリアスを使用し、各 <code> Column</code> にそのエイリアスをプレフィックスとして付けてください。これにより、解析時間が短縮され、列の曖昧さによって引き起こされる構文エラーが減少します。 17. 中間結果を一時的に保存するには「一時テーブル」を使用します。 SQL ステートメントを簡素化する重要な方法は、一時テーブルを使用して中間結果を一時的に保存することです。一時的な結果を一時テーブルに一時的に保存すると、後続のクエリは <code>tempdb </code> に保存されます。これにより、プログラム内でのメインテーブルの複数回のスキャンが回避され、「更新ロック」をブロックする「共有ロック」も大幅に軽減されます。 " プログラム実行中。これにより、ブロッキングが軽減され、同時実行パフォーマンスが向上します。 18. 一部の SQL クエリステートメントは <code>nolock</code> を使用して追加する必要があります。同時実行パフォーマンスを向上させるために、読み取りと書き込みが相互にブロックされます。一部のクエリでは、読み取り時に書き込みを許可する nolock を追加できますが、欠点は、コミットされていないダーティデータが読み取られる可能性があることです。 nolock の使用には 3 つの原則があります。 <ul style="list-style-type: disc;"><li> クエリ結果が「挿入、削除、変更」に使用される場合、nolock は追加できません。 </li><li>クエリ対象のテーブルはページ分割が頻繁に発生するテーブルであるため、nolock の使用には注意してください。</li><li>一時テーブルを使用すると、"データフォアシャドウ"。Oracle の UNDO テーブルスペースのように機能し、一時テーブルを使用して同時実行パフォーマンスを向上させることができます。nolock は使用しないでください。 </li></ul>19. 一般的な簡略化ルールは次のとおりです: 5 つを超えるテーブル接続 (<code>JOIN</code>) を持たないでください。中間結果を保存するために一時テーブルまたはテーブル変数の使用を検討してください。。使用するサブクエリを減らし、ビューをあまり深くネストしないでください。一般に、ネストするビューは 2 つまでにするのが適切です。 20. クエリする結果を事前に計算してテーブルに入力し、クエリ時に <code>Select</code> します。 21、用 OR 字句可以分解成多个查询，并且通过 <code>UNION </code>连接多个查询。他们的速度与是否使用索引有关，如果查询需要用到联合索引，用 <code>UNION all </code>执行的效率更高。多个 OR 的字句没有用到索引，改写成 <code>UNION</code> 的形式再试图与索引匹配。 22、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断次数。 23、尽量将数据的处理工作放在服务器上，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划、且存储在数据库中的 SQL 语句，是控制流语言的集合，速度当然快。反复执行的动态 SQL，可以使用临时存储过程，该过程（临时表）被放在 <code>Tempdb </code>中。24、当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用配制线程数量< 最大连接数，启用 SQL SERVER 的线程池来解决，如果还是数量 = 最大连接数+5，严重的损害服务器的性能。 25、查询的关联同写的顺序： <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false">select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' （A = B, B = '号码'） select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' （A = B, B = '号码', A = '号码'） select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' （B = '号码', A = '号码'）</pre><div class="contentsignin">ログイン後にコピー</div></div>26、尽量使用 EXISTS 代替 <code>select count(1)</code> 来判断是否存在记录。count 函数只有在统计表中所有行数时使用，而且 <code>count(1)</code> 比 <code>count(*) </code>更有效率。27、尽量使用 “>=”，不要使用 “>”。 28、索引的使用规范： <ul style="list-style-type: disc;"><li>索引的创建要与应用结合考虑，建议大的<code> OLTP 表</code>不要超过 6 个索引； </li><li>尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过 <code>index index_name </code>来强制指定索引；</li><li>避免对大表查询时进行 <code>table scan</code>，必要时考虑新建索引；</li><li>在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用；</li><li>要注意索引的维护，周期性重建索引，重新编译存储过程。　　</li></ul>29、下列 SQL 条件语句中的列都建有恰当的索引，但执行速度却非常慢： <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false">SELECT * FROM record WHERE substrINg(card_no, 1, 4) = '5378' --13秒 SELECT * FROM record WHERE amount/30 < 1000 --11秒 SELECT * FROM record WHERE convert(char(10), date, 112) = '19991201' --10秒</pre><div class="contentsignin">ログイン後にコピー</div></div>分析： WHERE 子句中对列的任何操作结果都是在 SQL 运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引。如果这些结果在查询编译时就能得到，那么就可以被 SQL 优化器优化，使用索引，避免表搜索，因此将 SQL 重写成下面这样：<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false">SELECT * FROM record WHERE card_no like '5378%' -- < 1秒 SELECT * FROM record WHERE amount < 1000*30 -- < 1秒 SELECT * FROM record WHERE date = '1999/12/01' -- < 1秒</pre><div class="contentsignin">ログイン後にコピー</div></div>30、当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新。 31、在所有的存储过程中，能够用 SQL 语句的，绝不用循环去实现。 32、选择最有效率的表名顺序（只在基于规则的优化器中有效）： Oracle 的解析器按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表（基础表 driving table）将被最先处理，在 FROM 子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表。如果有 3 个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表。 33、提高<code> GROUP BY</code> 语句的效率，可以通过将不需要的记录在 GROUP BY 之前过滤掉。 34、SQL 语句用大写，因为 Oracle 总是先解析 SQL 语句，把小写的字母转换成大写的再执行。 35、别名的使用，别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快 1.5 倍。 36、避免死锁，在你的存储过程和触发器中访问同一个表时总以相同的顺序；事务应尽可能地缩短，减少数据量的涉及；永远不要在事务中等待用户输入。 37、避免使用临时表，除非有需要，可以使用表变量代替。大多数时候（99%），表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在 TempDb 数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。 38、最好不要使用触发器： <ul style="list-style-type: disc;"> <li>触发，执行一个触发器事件本身就是一个耗费资源的过程； </li> <li>如果能够使用约束实现的，尽量不要使用触发器；</li> <li>不要为不同的触发事件（<code>Insert、Update 和 Delete</code>）使用相同的触发器；</li> <li>トリガーではトランザクションコードを使用しないでください。 </li> </ul> 39. インデックス作成ルール: <ul style="list-style-type: disc;"> <li>テーブルの主キーと外部キーにはインデックスが必要です。 </li> <li>データボリュームが 300 を超えるテーブルにはインデックスが必要です。</li> <li>他のテーブルに頻繁に接続されるテーブルには、接続フィールドにインデックスが必要です。 . ; </li> <li>WHERE 句に頻繁に現れるフィールド、特に大きなテーブルのフィールドにはインデックスを付ける必要があります; </li> <li>インデックスを構築する必要があります選択性の高いフィールドについて; </li> <li>インデックスは小さなフィールドに構築する必要があります。大きなテキストフィールドや長いフィールドの場合でも、インデックスを構築しないでください。</li> <li> 複合インデックスの確立には慎重な分析が必要なので、代わりに単一フィールドインデックスの使用を検討してください。 </li> <li> 複合インデックスのメイン列フィールド (通常は選択性の高いフィールド ; </li> <li> 複合インデックスの複数のフィールドが AND モードの WHERE 句に同時に出現することがよくありますか?単一フィールドのクエリはほとんどないか、まったくありませんか?その場合は、複合インデックスを作成できます。それ以外の場合は、単一のフィールドインデックスを検討してください。</li> <li>複合インデックスに含まれるフィールドが WHERE 句内で単独で出現することが多い場合は、フィールドを複数に分割します。単一フィールドのインデックス; </li> <li>複合インデックスに 3 つ以上のフィールドが含まれている場合は、必要性を慎重に検討し、複合フィールドの数を減らします。</li> <li>これらのフィールドに単一フィールドインデックスと複合インデックスの両方がある場合、通常、複合インデックスは削除できます。</li> <li>データ操作を頻繁に実行するテーブルの場合は、インデックスを作成しすぎないでください。 ; </li> <li>実行計画への悪影響を避けるために、不要なインデックスを削除します; </li> <li>#テーブルに作成される各インデックスにより、ストレージのオーバーヘッドが増加し、挿入、削除、更新の操作でも処理のオーバーヘッドが増加します。さらに、単一フィールドインデックスがある場合、複合インデックスが多すぎると通常は役に立ちません。それどころか、特に頻繁に更新されるテーブルの場合、データの追加や削除時のパフォーマンスも低下し、悪影響はさらに大きくなります。。 </li> <li>重複する値が多数含まれるデータベース内のフィールドにはインデックスを作成しないようにしてください。 </li> </ul>40. MySQL クエリ最適化の概要: <ul style="list-style-type: disc;"> <li>遅いクエリログを使用して遅いクエリを発見し、実行プランを使用して判断するクエリが適切に実行されるかどうかクエリが最適に実行されているかどうかを確認するために、クエリを常にテストしてください。 </li> <li>パフォーマンスは時間の経過とともに変化します。テーブル全体で count(*)<code> を使用することは避けてください。テーブル全体がロックされる可能性があるため、後続の同様のクエリでクエリの一貫性が保たれます。クエリではクエリキャッシュを使用でき、必要に応じて </code>DISTINCT<code> の代わりに </code>GROUP BY<code> を使用し、WHERE、GROUP BY、ORDER BY 句でインデックス付き列を使用し、インデックスを単純に保つことができます。複数のインデックスに同じ列を含めます。 </code> </li> <li>MySQL は間違ったインデックスを使用する場合があります。この場合は USE INDEX<code> を使用し、For インデックスに </code>SQL_MODE=STRICT<code> を使用して問題を確認してください。レコード数が 5 未満のフィールドでは、UNION 中に </code>LIMIT<code> を使用しても、OR を使用することにはなりません。 </code> </li> <li>更新前の SELECT を回避するには、INSERT ON DUPLICATE KEY<code> または </code>INSERT IGNORE<code> を使用します。実装には UPDATE を使用しないでください。 MAX を使用する; インデックス付きフィールドと </code>ORDER BY<code> 句を使用する </code>LIMIT M<code>, N は場合によっては実際にクエリの速度を低下させる可能性があるため、使用は控えめにし、サブクエリの代わりに WHERE 句で UNION を使用してください。 MySQL を再起動する場合は、必ずデータベースをウォームアップしてデータがメモリ内にあり、クエリが高速であることを確認し、オーバーヘッドを削減するために複数の接続ではなく永続的な接続を検討してください。 </code> </li> <li> ベンチマーククエリには、サーバー上の負荷の使用が含まれます。場合によっては、単純なクエリが他のクエリに影響を与える可能性があります。サーバー上の負荷が増加した場合は、SHOW PROCESSLIST<code> を使用して表示します。クエリと問題のあるクエリ、すべての疑わしいクエリは、開発環境で生成されたミラーリングされたデータでテストされます。 </code> </li> </ul>41. MySQL バックアッププロセス: <ul style="list-style-type: disc;"> <li>セカンダリレプリケーションサーバーからのバックアップ; </li> <li> データの依存関係や外部キー制約の不一致を避けるために、バックアップ中にレプリケーションを停止します。 </li> <li> MySQL を完全に停止し、データベースからデータベースを削除します。ファイルのバックアップ; </li> <li>バックアップに MySQL ダンプを使用する場合は、レプリケーションが中断されないように、バイナリログファイルも同時にバックアップしてください。 </li> <li>LVM スナップショットは信頼しないでください。LVM スナップショットはデータの不整合を引き起こす可能性があり、将来問題が発生する可能性があります。 </li> <li>単一テーブルのリカバリを容易にするために、次のような場合があります。データは他のテーブルから分離されているため、そのテーブルをユニットエクスポートデータとして使用します。 </li> <li> mysqldump<code>;</code><code></code>バックアップする前に合計を確認してください。テーブルを最適化します;</li> <li>インポートを高速化するために、外部キー制約と一意性検出はインポート中に一時的に無効になります。</li> <li>各バックアップ後にデータベースとテーブルのサイズを計算します。データサイズの増加を監視できるようにインデックスを変更します。</li> <li>定期的にバックアップを実行します。 </li> </ul> 42. クエリバッファはスペースを自動的に処理しません。そのため、SQL ステートメントを作成するときは、スペース、特に SQL の先頭と末尾のスペースの使用を減らすように努めてください。 (クエリバッファの先頭と末尾のスペースが自動的にインターセプトされないため)。 43. メンバーは、クエリを容易にするためにテーブルを複数のテーブルに分割するために標準として Mid を使用できますか?一般的なビジネス要件では、クエリのベースとしてユーザー名が基本的に使用されますが、通常はテーブルを分割するためのハッシュ係数としてユーザー名を使用する必要があります。テーブルのパーティショニングに関しては、MySQL の <code>partition</code> 関数がこれを実行し、コードに対して透過的であるため、コードレベルで実装するのは不合理と思われます。 44. データベース内の各テーブルの主キーとして ID を設定する必要があり、最適なものは INT 型 (<code>UNSIGNED</code> を推奨) であり、自動的にインクリメントされるように設定します <code>AUTO_INCREMENT</code> フラグ。 45. すべてのストアドプロシージャとトリガーの先頭で <code>SET NOCOUNT ON</code> を設定し、最後に <code>SET NOCOUNT OFF</code> を設定します。ストアドプロシージャとトリガーの各ステートメントの後に、クライアントに <code>DONE_IN_PROC</code> メッセージを送信する必要はありません。 46. MySQL クエリは高速クエリキャッシュを有効にできます。これは、データベースのパフォーマンスを向上させる効果的な MySQL 最適化方法の 1 つです。同じクエリが複数回実行される場合は、キャッシュからデータを取得し、データベースから直接返す方がはるかに高速です。 47、<code>EXPLAIN SELECT</code> クエリは表示効果を追跡するために使用されます: EXPLAIN キーワードを使用すると、MySQL が SQL を処理する方法を知ることができます。声明。これは、クエリステートメントまたはテーブル構造のパフォーマンスのボトルネックを分析するのに役立ちます。 EXPLAIN クエリの結果には、インデックスの主キーがどのように使用されているか、データテーブルがどのように検索および並べ替えられているかもわかります。 48. データが 1 行しかない場合は LIMIT 1 を使用します: テーブルにクエリを実行すると、結果が 1 つしかないことがすでにわかっていますが、カーソルをフェッチする必要がある場合や、返されたレコードの数を確認する必要がある場合があるためです。この場合、<code>LIMIT 1 </code> を追加するとパフォーマンスが向上する可能性があります。このようにして、MySQL データベースエンジンは、レコードに一致する次のデータの検索を続けるのではなく、データを見つけた後に検索を停止します。 49. テーブルに適切なストレージエンジンを選択します: myisam: アプリケーションは主に読み取り操作と挿入操作に基づいており、少量のみです。更新と削除の頻度が高く、トランザクションの整合性と同時実行性の要件はそれほど高くありません。 InnoDB: トランザクション処理、および同時条件下で必要なデータの一貫性。挿入とクエリに加えて、多くの更新と削除も含まれます。 (InnoDB は、削除と更新によって発生するロックを効果的に削減します)。 トランザクションをサポートする InnoDB タイプのテーブルの場合、速度に影響する主な理由は、<code>AUTOCOMMIT</code> デフォルト設定がオンであり、プログラムが明示的に BEGIN を呼び出してトランザクションを開始しないため、すべてが自動的に送信されるため、速度に重大な影響を与えます。 SQL を実行する前に begin を呼び出すことができます。複数の SQL が 1 つのものを形成し (自動コミットがオンになっている場合でも)、パフォーマンスが大幅に向上します。 50. テーブルのデータ型を最適化し、適切なデータ型を選択します: 原則: 通常は小さい方が良い、シンプルであるほど良い、すべてのフィールドデフォルト値が存在する必要があり、NULL を避けるようにしてください。 MySQL は大量のデータへのアクセスを非常に適切にサポートできますが、一般的に言えば、データベース内のテーブルが小さいほど、テーブル上で実行されるクエリは高速になります。したがって、テーブルを作成するときに、パフォーマンスを向上させるために、テーブル内のフィールドの幅をできるだけ小さく設定できます。 同様に、可能であれば、整数フィールドを定義するには <code>BIGIN </code> の代わりに <code>MEDIUMINT </code> を使用し、そのフィールドを <code>NOT NULL# に設定するようにしてください。 ## により、今後クエリを実行するときにデータベースが NULL 値を比較する必要がなくなります。 </code>「都道府県」や「性別」などの一部のテキストフィールドについては、ENUM <code> タイプとして定義できます。 MySQL では ENUM 型は数値データとして扱われ、数値データはテキスト型よりもはるかに高速に処理されるためです。このようにして、データベースのパフォーマンスを向上させることができます。 </code> 51. 文字列データ型: char、varchar、text。 52. 列に対する操作はすべて、データベース関数、計算式などを含むテーブルスキャンになります。クエリを実行するときは、操作を等号の右側に移動する必要があります。できるだけ。 推奨チュートリアル: 「MySQL チュートリアル <a href="https://www.php.cn/mysql-tutorials.html" target="_self">」</a>