mysql データベース最適化操作の概要

この記事では、主に mysql データベースの一般的な最適化操作について紹介します。この記事では、インデックス インデックス、SELECT* の使用の削減、EXPLAIN SELECT、クエリ キャッシュの有効化など、mysql データベースの日常の開発と使用における個人的な経験をまとめています。この情報は誰にとっても一定の参考価値があると信じていますので、必要な方は以下をご覧ください。

はじめに

データ中心のアプリケーションの場合、データベースの品質はプログラムのパフォーマンスに直接影響するため、データベースのパフォーマンスは非常に重要です。したがって、誰もが mysql データベースの最適化操作を理解する必要があります。この記事では、主に mysql データベースの一般的な最適化操作を要約します。以下では詳細を説明しません。

1. インデックス Index

言うまでもなく、私たちは主キーインデックスであるこの最適化手法をひっそりと使用してきました。場合によっては、適切なインデックスが定義されていれば、データベースのクエリのパフォーマンス (速度) が数倍、場合によっては数十倍も向上することがあります。

通常のインデックス

クエリ速度を向上させる機能です。

テーブルの作成、インデックスの作成

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
index [索引名] (column_name)
);

インデックスの作成

CREATE INDEX index_name ON tab_name (column_name)

インデックスの削除

DROP INDEX index_name FROM tab_name

インデックスの表示

SHOW index FROM tab_name

主キーインデックス

機能はクエリと一意制約を高速化することです

テーブルの構築、インデックスの作成

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
PRIMARY KEY(column_name)
);

インデックスの作成

ALTER TABLE tab_name ADD PRIMARY KEY(column_name)

インデックスの削除

ALTER TABLE tab_name DROP PRIMAY KEY(column_name)

一意のインデックス

その役割はクエリと一意制約を高速化することです

テーブルの作成とインデックスの作成

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
unique [索引名] (column_name)
);

インデックスの作成

CREATE UNIQUE INDEX index_name ON tab_name (column_name)

インデックスの削除

DROP UNIQUE INDEX index_name FROM tab_name

2. SELECT* は慎重に使用してください

おそらく、データベースにクエリを実行するときに、クエリしたいものを選択する人もいるでしょう。これは不適切な動作です。すべてではなく、使用したいデータを取得する必要があります。選択すると、Web サーバーの負荷が増加し、ネットワーク送信の負荷が増加し、クエリ速度が自然に低下するためです。

3. EXPLAIN SELECT

この機能を見たことがない人も多いと思われますが、ここでは強くお勧めします。 Explain では、mysql がインデックスを使用して選択ステートメントを処理し、テーブルを結合する方法を示します。より適切なインデックスを選択し、より最適化されたクエリ ステートメントを作成するのに役立ちます。主な用途は、select の前に Explain を追加することです。

EXPLAIN SELECT [查找字段名] FROM tab_name ...

4. クエリ キャッシュをオンにする

ほとんどの MySQL サーバーではクエリ キャッシュがオンになっています。これはパフォーマンスを向上させる最も効果的な方法の 1 つであり、MySQL データベース エンジンによって処理されます。同じクエリの多くが複数回実行されると、クエリ結果はキャッシュに配置されるため、後続の同一クエリはテーブルを操作せずにキャッシュされた結果に直接アクセスします。

最初のステップは、query_cache_type を ON に設定し、システム変数 have_query_cache が利用可能かどうかをクエリすることです:

show variables like 'have_query_cache'

その後、メモリ サイズをクエリ キャッシュに割り当て、キャッシュされたクエリ結果の最大値を制御します。関連する操作は構成ファイルで変更されます。

5. NOT NULL を使用する

アプリケーションが NULL を保存する必要がない場合でも、多くのテーブルには NULL (NULL 値) になる可能性のある列が含まれています。これは、NULL が列のデフォルト属性であるためです。本当に NULL 値を格納する必要がない限り、通常は列を NOT NULL として指定するのが最善です。

クエリに NULL 許容カラムが含まれている場合、NULL 許容カラムによりインデックス、インデックス統計、値の比較がより複雑になるため、MySQL の最適化がより困難になります。 NULL にできるカラムはより多くのストレージ領域を使用するため、MySQL での特別な処理が必要になります。 NULL 許容カラムにインデックスが付けられると、各インデックス レコードに追加のバイトが必要になります。MyISAM では、これによって固定サイズのインデックス (整数カラムが 1 つだけあるインデックスなど) が可変サイズのインデックスになることもあります。

通常、NULL 列を NOT NULL に変更してもパフォーマンスはほとんど向上しません。そのため、問題が発生すると判断されない限り、(チューニング時に) 既存のスキーマでこの状況を最初に見つけて変更する必要はありません。ただし、列にインデックスを構築する予定がある場合は、列が NULL になるように設計しないようにする必要があります。もちろん例外もあります。たとえば、InnoDB は NULL 値を格納するために別のビットを使用するため、スパース データに対して優れたスペース効率を実現します。ただし、これは MyISAM には当てはまりません。

6. ストレージエンジンの選択

MyISAMとInnoDBの選択方法ですが、トランザクション処理や外部キーが必要な場合はInnoDBの方が良いかもしれません。フルテキスト インデックスが必要な場合は、MyISAM がシステムに組み込まれているため、通常はこれが適切な選択となります。ただし、実際には 200 万行のレコードを頻繁にテストすることはありません。したがって、多少遅くても、Sphinx を使用して InnoDB からフルテキスト インデックスを取得できます。

数据的大小，是一个影响你选择什么样存储引擎的重要因素，大尺寸的数据集趋向于选择InnoDB方式，因为其支持事务处理和故障恢复。数据库的在小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。而MyISAM可能会需要

几个小时甚至几天来干这些事，InnoDB只需要几分钟。

您操作数据库表的习惯可能也会是一个对性能影响很大的因素。比如： COUNT() 在 MyISAM表中会非常快，而在InnoDB表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快，但需要小心的是如果我们的主键太长了也会导致性能问题。大批的inserts语句在MyISAM下会快一些，但是updates在InnoDB 下会更快一些——尤其在并发量大的时候。

所以，到底你检使用哪一个呢？根据经验来看，如果是一些小型的应用或项目，那么MyISAM也许会更适合。当然，在大型的环境下使用MyISAM也会有很大成功的时候，但却不总是这样的。如果你正在计划使用一个超大数据量的项目，而且需要事务处理或外键支持，那么你真的应该直接使用InnoDB方式。但需要记住InnoDB的表需要更多的内存和存储，转换100GB的MyISAM 表到InnoDB 表可能会让你有非常坏的体验。

七、避免在 where 子句中使用 or 来连接

如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10
union all
select id from t where Name = 'admin'

八、多使用varchar/nvarchar

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

九、避免大数据量返回

这里要考虑使用limit，来限制返回的数据量，如果每次返回大量自己不需要的数据，也会降低查询速度。

十、where子句优化

where 子句中使用参数，会导致全表扫描,因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。

应尽量避免在 where 子句中对字段进行表达式操作，避免在where子句中对字段进行函数操作这将导致引擎放弃使用索引而进行全表扫描。不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

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