MySQL データベース設計を最適化するための 8 つの方法の紹介

この記事では、主にコンピュータ レベル 2 試験における MySQL の共通テスト ポイントを詳細に紹介し、一定の参考値を持つ 8 つの MySQL データベース設計最適化方法を詳しく紹介します。興味のある方は、

8 MySQL データベース設計最適化を参照してください。メソッドの具体的な内容は次のとおりです

1. 最も適切なフィールド属性を選択します

MySQL は大量のデータへのアクセスを十分にサポートできますが、一般的に言えば、データベース内のテーブルが小さいほど、実行が向上します。クエリが高速になります。したがって、テーブルを作成するときに、パフォーマンスを向上させるために、テーブル内のフィールドの幅をできるだけ小さく設定できます。たとえば、郵便番号フィールドを定義する場合、CHAR(255) に設定すると、CHAR(6) で問題ないため、VARCHAR 型を使用しても明らかに冗長になります。同様に、可能であれば、整数フィールドを定義するには BIGIN の代わりに MEDIUMINT を使用する必要があります。

効率を向上させるもう 1 つの方法は、可能であればフィールドを NOT NULL に設定して、今後クエリを実行するときにデータベースが NULL 値を比較する必要がないようにすることです。

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

2. サブクエリ (Sub-Queries) の代わりに結合 (JOIN) を使用する

MySQL は 4.1 から SQL サブクエリをサポートします。この手法を使用すると、SELECT ステートメントを使用してクエリ結果の単一列を作成し、この結果を別のクエリのフィルター条件として使用できます。例えば、基本顧客情報テーブルにある注文のない顧客を削除したい場合、サブクエリを使用して、まず売上情報テーブルから注文を行ったすべての顧客のIDを取得し、その結果を次のテーブルに渡すことができます。以下に示すメイン クエリ:

DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

サブクエリを使用すると、論理的に複数のステップを一度に完了する必要がある多くの SQL 操作を完了でき、トランザクションやテーブルのロックも回避でき、記述も簡単です。ただし、場合によっては、サブクエリをより効率的な結合 (JOIN) に置き換えることができます。たとえば、注文レコードを持たないすべてのユーザーを取得したいとします。次のクエリを使用してそれを完了できます:

SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

このクエリを完了するために接続 (JOIN)... を使用する場合、速度ははるかに速くなります。特に salesinfo テーブルに CustomerID のインデックスがある場合、パフォーマンスが向上します。 クエリは次のとおりです。

SELECT * FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.
CustomerID
WHERE salesinfo.CustomerID IS NULL

結合 (JOIN) .. より効率的である理由は、MySQL が必要ないためです。一時テーブルを使用して、2 つの手順を必要とするこの論理クエリを完了します。

3. UNION を使用して手動で作成した一時テーブルを置き換えます

MySQL はバージョン 4.0 以降、一時テーブルの使用を必要とする 2 つ以上の SELECT クエリを 1 つの問い合わせにマージできる UNION クエリをサポートしています。クライアントのクエリ セッションが終了すると、データベースが整然と効率的に保たれるように、一時テーブルは自動的に削除されます。 UNION を使用してクエリを作成する場合、複数の SELECT ステートメントを接続するキーワードとして UNION を使用するだけで済みます。すべての SELECT ステートメントのフィールドの数が同じである必要があることに注意してください。次の例は、UNION を使用したクエリを示しています。

SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product

4. トランザクション

サブクエリ (サブクエリ)、接続 (JOIN)、ユニオン (UNION) を使用してさまざまなクエリを作成できますが、すべてのデータベース操作を使用できるわけではありません。 1 つまたはいくつかの SQL ステートメントだけで完了します。特定の種類の作業を完了するには、一連のステートメントが必要になることがよくあります。ただし、この場合、このステートメント ブロック内の特定のステートメントが誤って実行されると、ステートメント ブロック全体の動作が不確実になります。特定のデータを 2 つの関連するテーブルに同時に挿入したいとします。最初のテーブルが正常に更新された後、データベースで予期しない状況が発生し、2 番目のテーブルの操作が完了しない場合があります。このようにして、データは不完全になり、データベース内のデータさえも破壊されます。この状況を回避するには、トランザクションを使用する必要があります。その機能は、ステートメント ブロック内のすべてのステートメントが成功するか失敗するかのいずれかです。つまり、データベース内のデータの一貫性と完全性を維持できます。物事は BEGIN キーワードで始まり COMMIT キーワードで終わります。この期間中に SQL 操作が失敗した場合、ROLLBACK コマンドによってデータベースを BEGIN が開始される前の状態に復元できます。

BEGIN;
INSERT INTO salesinfo SET CustomerID=14;
UPDATE inventory SET Quantity=11
WHERE item='book';
COMMIT;

トランザクションのもう 1 つの重要な役割は、複数のユーザーが同じデータ ソースを同時に使用するときに、データベースをロックする方法を使用してユーザーに安全なアクセス方法を提供し、ユーザーの他のユーザーによって操作が中断されることはありません。

5. テーブルをロックする

　　尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

　　其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

LOCK TABLE inventory WRITE
SELECT Quantity FROM inventory
WHEREItem='book';
　　...
UPDATE inventory SET Quantity=11
WHEREItem='book';
UNLOCK TABLES

　　这里，我们用一个 SELECT 语句取出初始数据，通过一些计算，用 UPDATE 语句将新值更新到表中。包含有 WRITE 关键字的 LOCK TABLE 语句可以保证在 UNLOCK TABLES 命令被执行之前，不会有其它的访问来对 inventory 进行插入、更新或者删除的操作。

6、使用外键

　　锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把 customerinfo 表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到 salesinfo中。

　　CREATE TABLE customerinfo
　　(

　　CustomerID INT NOT NULL ,
　　PRIMARY KEY ( CustomerID )
 　) TYPE = INNODB;

　　CREATE TABLE salesinfo

　　(
　　SalesID INT NOT NULL,
　　CustomerID INT NOT NULL,
　　PRIMARY KEY(CustomerID, SalesID),
　　FOREIGN KEY (CustomerID) REFERENCES customerinfo
　　(CustomerID) ON DELETECASCADE

　　) TYPE = INNODB;

　　注意例子中的参数“ON DELETE CASCADE”。该参数保证当 customerinfo 表中的一条客户记录被删除的时候，salesinfo 表中所有与该客户相关的记录也会被自动删除。如果要在 MySQL 中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表 InnoDB类型。该类型不是 MySQL 表的默认类型。定义的方法是在 CREATE TABLE 语句中加上 TYPE=INNODB。如例中所示。

7、使用索引

　　索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(), MIN()和ORDERBY这些命令的时候，性能提高更为明显。那该对哪些字段建立索引呢？一般说来，索引应建立在那些将用于JOIN, WHERE判断和ORDER BY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况，例如 customerinfo中的“province”.. 字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTER TABLE或CREATE INDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL 中是一个FULLTEXT类型索引，但仅能用于MyISAM 类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTER TABLE或CREATE INDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。

8、优化的查询语句

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。下面是应该注意的几个方面。首先，最好是在相同类型的字段间进行比较的操作。在MySQL 3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和 VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。其次，在建有索引的字段上尽量不要使用函数进行操作。

例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。

SELECT * FROM order WHERE YEAR(OrderDate)＜2001;
SELECT * FROM order WHERE OrderDate＜"2001-01-01";

同样的情形也会发生在对数值型字段进行计算的时候：

SELECT * FROM inventory WHERE Amount/7＜24;
SELECT * FROM inventory WHERE Amount＜24*7;

上面的两个查询也是返回相同的结果，但后面的查询将比前面的一个快很多。第三，在搜索字符型字段时，我们有时会使用 LIKE 关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。例如下面的查询将会比较表中的每一条记录。

SELECT * FROM books
WHERE name like "MySQL%"

但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：

SELECT * FROM books
WHERE name＞="MySQL"and name＜"MySQM"

最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。

以上がMySQL データベース設計を最適化するための 8 つの方法の紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。