インタビュアー: SQL の最適化についてご存知ですか?私は20種類しか知りませんが、もっとたくさんあります...

「最適化戦略」

select id from t where num=10 union all select id from t where num=20。

select id from t where num between 1 and 3。

select id from t where name like‘%abc%’

select id from t where name like‘%abc’

select id from t where name like‘abc%’

才用到索引。

7、如果在 WHERE 子句中使用参数，也会导致全表扫描。

8、应尽量避免在 WHERE 子句中对字段进行表达式操作，应尽量避免在 WHERE 子句中对字段进行函数操作。

9、很多时候用 EXISTS 代替 IN 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

10、索引固然可以提高相应的 SELECT 的效率，但同时也降低了 INSERT 及 UPDATE 的效。因为 INSERT 或 UPDATE 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

11、应尽可能的避免更新 clustered 索引数据列， 因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

12、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

13、尽可能的使用 varchar, nvarchar 代替 char, nchar。因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

14、最好不要使用返回所有：select from t ，用具体的字段列表代替 “*”，不要返回用不到的任何字段。

15、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

16、使用表的别名（Alias）：当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个 Column 上。这样一来，就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。

17、使用“临时表”暂存中间结果 ：

简化 SQL 语句的重要方法就是采用临时表暂存中间结果。但是临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在 tempdb 中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。

18、一些 SQL 查询语句应加上 nolock，读、写是会相互阻塞的，为了提高并发性能。对于一些查询，可以加上 nolock，这样读的时候可以允许写，但缺点是可能读到未提交的脏数据。

使用 nolock 有3条原则：

• 查询的结果用于“插、删、改”的不能加 nolock；
• 查询的表属于频繁发生页分裂的，慎用 nolock ；
• 使用临时表一样可以保存“数据前影”，起到类似 Oracle 的 undo 表空间的功能，能采用临时表提高并发性能的，不要用 nolock。

19、常见的简化规则如下：

不要有超过 5 个以上的表连接（JOIN），考虑使用临时表或表变量存放中间结果。少用子查询，视图嵌套不要过深，一般视图嵌套不要超过 2 个为宜。

20、将需要查询的结果预先计算好放在表中，查询的时候再Select。这在SQL7.0以前是最重要的手段，例如医院的住院费计算。

21、用 OR 的字句可以分解成多个查询，并且通过 UNION 连接多个查询。他们的速度只同是否使用索引有关，如果查询需要用到联合索引，用 UNION all 执行的效率更高。多个 OR 的字句没有用到索引，改写成 UNION 的形式再试图与索引匹配。一个关键的问题是否用到索引。

22、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。

23、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。

存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的 SQL 语句，是控制流语言的集合，速度当然快。反复执行的动态 SQL，可以使用临时存储过程，该过程（临时表）被放在 Tempdb 中。

24、当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用配制线程数量< 最大连接数，启用 SQL SERVER 的线程池来解决，如果还是数量 = 最大连接数+5，严重的损害服务器的性能。

25、查询的关联同写的顺序 ：

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 = '号码'）

26、尽量使用 EXISTS 代替 select count(1) 来判断是否存在记录。count 函数只有在统计表中所有行数时使用，而且 count(1) 比 count(*) 更有效率。

27、尽量使用 “>=”，不要使用 “>”。

28、索引的使用规范：

索引的创建要与应用结合考虑，建议大的 OLTP 表不要超过 6 个索引；尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过 index index_name 来强制指定索引；避免对大表查询时进行 table scan，必要时考虑新建索引；在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用；要注意索引的维护，周期性重建索引，重新编译存储过程。

29、下列 SQL 条件语句中的列都建有恰当的索引，但执行速度却非常慢：

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) = &#39;19991201&#39; --10秒

分析：

WHERE 子句中对列的任何操作结果都是在 SQL 运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引。

如果这些结果在查询编译时就能得到，那么就可以被 SQL 优化器优化，使用索引，避免表搜索，因此将 SQL 重写成下面这样：

SELECT * FROM record WHERE card_no like &#39;5378%&#39; -- < 1秒 
SELECT * FROM record WHERE amount < 1000*30 -- < 1秒 
SELECT * FROM record WHERE date = &#39;1999/12/01&#39; -- < 1秒

30、当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新。

31、在所有的存储过程中，能够用 SQL 语句的，我绝不会用循环去实现。

例如：列出上个月的每一天，我会用 connect by 去递归查询一下，绝不会去用循环从上个月第一天到最后一天。

32、选择最有效率的表名顺序（只在基于规则的优化器中有效）：

Oracle 的解析器按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表（基础表 driving table）将被最先处理，在 FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。

如果有 3 个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表。

33、提高 GROUP BY 语句的效率，可以通过将不需要的记录在 GROUP BY 之前过滤掉。下面两个查询返回相同结果，但第二个明显就快了许多。

低效：

SELECT JOB, AVG(SAL) 
FROM EMP 
GROUP BY JOB 
HAVING JOB = 'PRESIDENT' 
OR JOB = 'MANAGER'

高效：

SELECT JOB, AVG(SAL) 
FROM EMP
WHERE JOB = 'PRESIDENT' 
OR JOB = 'MANAGER' 
GROUP BY JOB

34、SQL 语句用大写，因为 Oracle 总是先解析 SQL 语句，把小写的字母转换成大写的再执行。

35、别名的使用，别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快 1.5 倍。

36、避免死锁，在你的存储过程和触发器中访问同一个表时总是以相同的顺序；事务应经可能地缩短，在一个事务中应尽可能减少涉及到的数据量；永远不要在事务中等待用户输入。

37、避免使用临时表，除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替。大多数时候（99%），表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在 TempDb 数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。

38、最好不要使用触发器：

触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程；如果能够使用约束实现的，尽量不要使用触发器；不要为不同的触发事件（Insert、Update 和 Delete）使用相同的触发器；不要在触发器中使用事务型代码。

39、索引创建规则：

テーブルの主キーと外部キーにはインデックスが必要です。データ ボリュームが 300 を超えるテーブルにはインデックスが必要です。他のテーブルに頻繁に接続されるテーブルには、接続フィールドにインデックスが必要です。WHERE に頻繁に表示されるフィールド条項、特に大きなテーブルのフィールドにはインデックスを作成する必要があります。インデックスは選択性の高いフィールドに基づいて構築する必要があります。インデックスは小さなフィールドに構築する必要があります。大きなテキスト フィールドや非常に長いフィールドの場合でも、インデックスを構築しないでください。複合インデックスの確立インデックスを作成する必要があります。慎重に分析し、単一フィールド インデックスに置き換えることを検討してください。複合インデックスのメイン列フィールド (通常、より選択性の高いフィールドです) を正しく選択します。複合インデックスの複数のフィールドが頻繁に使用されます。同時に WHERE 句を AND モードで実行しますか?単一フィールドのクエリはほとんどないか、まったくありませんか?その場合は、複合インデックスを構築できます。それ以外の場合は、単一フィールド インデックスを検討してください。複合インデックスに含まれるフィールドが WHERE 句に単独で出現することが多い場合は、複数の単一フィールド インデックスに分割します。複合インデックスに複数のフィールドが含まれる場合は、単一フィールド インデックスを作成します。フィールドが 3 つある場合は、必要性を慎重に検討し、複合フィールドの数を減らすことを検討してください。これらのフィールドに単一フィールド インデックスと複合インデックスの両方がある場合は、通常、複合インデックスを削除できます。頻繁にデータを実行するテーブルを作成しすぎないでください。インデックス。実行計画への悪影響を避けるために不要なインデックスを削除します。テーブル上に作成されるインデックスごとにストレージ オーバーヘッドが増加し、インデックスにより挿入、削除、更新操作の処理オーバーヘッドも増加します。さらに、単一フィールド インデックスがある場合、複合インデックスが多すぎると通常は役に立ちません。それどころか、特に頻繁に更新されるテーブルの場合、データの追加や削除時のパフォーマンスも低下し、悪影響はさらに大きくなります。 。多数の重複値を含むデータベース内のフィールドにインデックスを作成しないようにしてください。

40. MySQL クエリ最適化の概要:

遅いクエリ ログを使用して遅いクエリを見つけ、実行プランを使用してクエリが正常に実行されているかどうかを判断し、常にクエリをテストしてクエリが実行されるかどうかを確認します。最高です。

パフォーマンスは時間の経過とともに常に変化します。テーブル全体で count(*) を使用することは避けてください。テーブル全体がロックされる可能性があります。適切な状況下で、後続の同様のクエリでクエリ キャッシュを使用できるようにクエリの一貫性を保ちます。 DISTINCT の代わりに GROUP BY を使用し、WHERE、GROUP BY、および ORDER BY 句でインデックス付き列を使用し、インデックスを単純にし、複数のインデックスに同じ列を含めないでください。

MySQL は間違ったインデックスを使用することがあります。この場合は、USE INDEX を使用し、SQL_MODE=STRICT の使用の問題を確認してください。レコード数が 5 未満のインデックス フィールドの場合、UNION での LIMIT の使用は OR ではありません。

更新前の SELECT を回避するには、INSERT ON DUPLICATE KEY または INSERT IGNORE を使用します。実装には UPDATE を使用せず、MAX も使用しません。インデックス フィールドと ORDER BY 句を使用します。LIMIT M、N は実際に速度が低下する可能性があります。場合によっては、クエリは控えめに使用し、サブクエリの代わりに WHERE 句で UNION を使用します。MySQL を再起動する前に、データがメモリ内にありクエリが高速であることを確認するためにデータベースをウォームアップすることを忘れないでください。代わりに永続的な接続を検討してください。オーバーヘッドを削減するために複数の接続を使用します。

サーバーの負荷の使用を含むクエリのベンチマーク。場合によっては、単純なクエリが他のクエリに影響を与える可能性があります。サーバーの負荷が増加した場合、SHOW PROCESSLIST を使用して、開発環境で生成された遅くて問題のあるクエリを確認します。 . すべての疑わしいクエリはミラー データでテストされました。

41. MySQL バックアップ プロセス:

セカンダリ レプリケーション サーバーからバックアップします。データの依存関係や外部キー制約の不一致を避けるために、バックアップ中にレプリケーションを停止します。MySQL を完全に停止し、データベース ファイルからバックアップします。バックアップに MySQL ダンプを使用する場合は、バイナリもバックアップしてください。ログ ファイル – レプリケーションが中断されていないことを確認します。LVM スナップショットは信頼しないでください。LVM スナップショットは、将来問題を引き起こすデータの不整合を引き起こす可能性があります。単一テーブルのリカバリを容易にするために、データがテーブル単位でエクスポートします – データが分離されている場合は、他のテーブル。 mysqldump を使用する場合は –opt を使用します。バックアップ前にテーブルを確認して最適化します。インポートを高速化するには、インポート中に外部キー制約を一時的に無効にします。 ; インポートを高速化するには、インポート中に一意性検出を一時的に無効にします。各バックアップ後にデータベース、テーブル、インデックスのサイズを計算して、データ サイズの増加をより適切に監視します。レプリケーション インスタンスのエラーと自動スケジュール スクリプト遅延を介したエラーを監視します。バックアップを定期的に実行します。

42. クエリ バッファはスペースを自動的に処理しません。そのため、SQL ステートメントを作成するときは、スペース、特に SQL の先頭と末尾のスペースの使用を減らすように努める必要があります (クエリ キャッシュが処理するため)。 SQL ステートメントの先頭と末尾のスペースは自動的にインターセプトされません))。

43. メンバーは、クエリを容易にするためにテーブルを複数のテーブルに分割するために標準として Mid を使用できますか?一般的なビジネス要件では、クエリのベースとしてユーザー名が基本的に使用されますが、通常はテーブルを分割するためのハッシュ係数としてユーザー名を使用する必要があります。

テーブルの分割に関しては、MySQL のパーティション関数がこれを実行し、コードに対して透過的であるため、コード レベルで実装するのは不合理に思えます。

44. データベース内の各テーブルの主キーとして ID を設定する必要があり、最適なのは INT 型 (UNSIGNED を推奨) であり、自動的に増加する AUTO_INCREMENT フラグを設定します。

45. すべてのストアド プロシージャとトリガーの先頭で SET NOCOUNT ON を設定し、最後に SET NOCOUNT OFF を設定します。ストアド プロシージャとトリガーの各ステートメントの後に DONE_IN_PROC メッセージをクライアントに送信する必要はありません。

46. MySQL クエリは高速クエリ キャッシュを有効にできます。これは、データベースのパフォーマンスを向上させる効果的な MySQL 最適化方法の 1 つです。同じクエリが複数回実行される場合は、キャッシュからデータを取得し、データベースから直接返す方がはるかに高速です。

47. EXPLAIN SELECT クエリは、表示効果を追跡するために使用されます:

EXPLAIN キーワードを使用すると、MySQL が SQL ステートメントをどのように処理するかを知ることができます。これは、クエリ ステートメントまたはテーブル構造のパフォーマンスのボトルネックを分析するのに役立ちます。 EXPLAIN クエリの結果には、インデックスの主キーがどのように使用されているか、データ テーブルがどのように検索および並べ替えられているかもわかります。

48. データが 1 行しかない場合は、LIMIT 1 を使用します:

テーブルにクエリを実行するとき、結果が 1 つだけであることがすでにわかっている場合がありますが、必要な場合があります。カーソルをフェッチするか、返されたレコードの数を確認することもできます。

この場合、LIMIT 1 を追加するとパフォーマンスが向上する可能性があります。このようにして、MySQL データベース エンジンは、レコードに一致する次のデータの検索を続けるのではなく、データを見つけた後に検索を停止します。

49. テーブルに適したストレージ エンジンを選択します:

myisam: アプリケーションは主に読み取りと挿入の操作に基づいており、更新と削除は少量のみです。トランザクションの完全性と同時性 性的要件はそれほど高くありません。 InnoDB: トランザクション処理と、同時実行条件下で必要なデータの一貫性。挿入とクエリに加えて、多くの更新と削除も含まれます。 (InnoDB は、削除と更新によって発生するロックを効果的に削減します)。トランザクションをサポートする InnoDB タイプのテーブルの場合、速度に影響を与える主な理由は、AUTOCOMMIT のデフォルト設定がオンになっており、プログラムが明示的に BEGIN を呼び出してトランザクションを開始しないため、各挿入が自動的に送信され、スピード。 SQL を実行する前に begin を呼び出すことができます。複数の SQL が 1 つのものを形成し (自動コミットがオンになっている場合でも)、パフォーマンスが大幅に向上します。

50. テーブルのデータ型を最適化し、適切なデータ型を選択します:

原則: 通常は小さい方が良い、シンプルであることが良い、すべてのフィールドはデフォルト値なので、NULL を避けるようにしてください。

例: データベース テーブルを設計するときは、占有ディスク領域を少なくするために、より小さい整数型を使用します。 (int よりも mediumint の方が適しています)

たとえば、時間フィールド: datetime と timestamp。 datetime は 8 バイト、timestamp は 4 バイトを占め、半分だけが使用されます。タイムスタンプで表される範囲は 1970 ～ 2037 で、更新時刻に適しています。

MySQL は大量のデータへのアクセスを十分にサポートできますが、一般的に言えば、データベース内のテーブルが小さいほど、テーブル上で実行されるクエリは高速になります。

したがって、テーブルを作成するときに、パフォーマンスを向上させるために、テーブル内のフィールドの幅をできるだけ小さく設定できます。

例: 郵便番号フィールドを定義するときに、それが CHAR(255) に設定されている場合、明らかにデータベースに不必要なスペースが追加されます。 CHAR(6) は問題なく機能するため、VARCHAR 型を使用することも冗長です。

同様に、可能であれば、整数フィールドを定義するには BIGIN の代わりに MEDIUMINT を使用する必要があります。また、将来クエリを実行するときにデータベースがそのフィールドを NOT NULL に設定する必要がないようにする必要があります。 NULL 値を比較します。

「都道府県」や「性別」などの一部のテキストフィールドについては、ENUM タイプとして定義できます。 MySQL では ENUM 型は数値データとして扱われ、数値データはテキスト型よりもはるかに高速に処理されるためです。このようにして、データベースのパフォーマンスを向上させることができます。

51. 文字列データ型: char、varchar、text 違いを選択してください。

52. 列に対する操作はすべて、データベース関数、計算式などを含むテーブル スキャンになります。クエリを実行するときは、操作をできるだけ等号の右側に移動する必要があります。

「概要」

この記事では、合計 52 の SQL 最適化戦略について説明します。10 個以上挙げることができれば、このインタビューが依然として非常に興味深いものであることがわかります。できれば 20 種類挙げると、面接官は基本的にあなたが話し続けるのを待つことはなくなります。