MySQL のトップ 10 の最適化方法

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

テーブル内のフィールドの幅を最小値に設定します。可能: char varchar の上限は 255 バイト (固定占有領域)、varchar の上限は 65535 バイト (実際の占有領域)、text の上限は 65535 です。 char は varchar より効率的です。

フィールドを NOT NULL に設定してみてください。クエリの実行時に、データベースは NULL 値を比較する必要はありません。

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

結合 (JOIN) の方が効率的である理由は、MySQL ではこの論理クエリを完了するには、メモリ内に一時テーブルを作成します。この論理クエリには 2 つの手順が必要です (結合クエリ条件とインデックスの方が高速です)。

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

一時テーブルの使用を必要とする 2 つ以上の SELECT クエリを結合するクエリ。

クライアントから名前、電話番号を選択 UNION 作成者から名前、生年月日を選択 UNION 製品から名前、サプライヤーを選択;

4. トランザクション

当社はサブクエリ (Sub-Queries)、接続 (JOIN)、およびユニオン (UNION) を使用してさまざまなクエリを作成できますが、すべてのデータベース操作が 1 つまたはいくつかの SQL ステートメントだけで完了できるわけではありません。特定の種類の作業を完了するには、一連のステートメントが必要になることがよくあります。

結果は、ステートメント ブロック内のすべてのステートメントが正常に動作するか、すべてが失敗するかのどちらかです。つまり、データベース内のデータの一貫性と完全性を維持できます。物事は BEGIN キーワードで始まり COMMIT キーワードで終わります。この期間中に SQL 操作が失敗した場合、ROLLBACK コマンドによってデータベースを BEGIN が開始される前の状態に復元できます。

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 ステートメントは、UNLO​​CK TABLES コマンドが実行される前に、挿入、更新、または削除のためのインベントリへの他のアクセスがないことを保証します。

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".. フィールドなど、重複する値が多数存在する可能性があります。そのようなフィールドにインデックスを作成しても役に立ちません。逆に、データベースのパフォーマンスを低下させます。

通常のインデックス (キーワード KEY または INDEX で定義されたインデックス) の唯一のタスクは、データへのアクセスを高速化することです。したがって、インデックスは、クエリ条件 (WHEREcolumn=) または並べ替え条件 (ORDERBYcolumn) で最も頻繁に出現するデータ列に対してのみ作成する必要があります。

一意のインデックスの利点: 第一に、MySQL はこのインデックスの管理を簡素化し、インデックスの効率が向上します。第二に、新しいレコードがデータ テーブルに挿入されると、MySQL は自動的に新しいレコードをチェックします。レコードのこのフィールドの値がレコードのこのフィールドにすでに出現している場合、MySQL は新しいレコードの挿入を拒否します。言い換えれば、一意のインデックスによりデータ レコードの一意性が保証されます。多くの場合、一意のインデックスを作成する目的は、アクセス速度を向上させることではなく、単にデータの重複を避けることです。

8. 最適化されたクエリ ステートメント

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;

変換プロセスによりインデックスが無効になるため、クエリ内で MySQL に自動型変換を実行させないでください。

9. インデックスの失敗

like 以 % 开头，索引无效；当 like 前缀没有 %，后缀有 % 时，索引有效。

or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效，只有当 or 左右查询字段均为索引时，才会生效。

组合索引，不是使用第一列索引，索引失效。

数据类型出现隐式转化。如 varchar 不加单引号的话可能会自动转换为 int 型，使索引无效，产生全表扫描。

在索引字段上使用 not，a8093152e673feb7aba1828c43532094，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： keya8093152e673feb7aba1828c435320940 改为 key>0 or key562387249b991e085ea202fe92f7872ehttps://blog.csdn.net/yhl_jxy/article/details/88570154

一、分表的分类（单表记录条数达到百万到千万级别时就要使用分表）

1. 纵向分表

文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。

浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。

首先存储引擎的使用不同，冷数据使用 MyIsam 可以有更好的查询数据。活跃数据，可以使用 Innodb , 可以有更好的更新速度。

就是把原来一张表里的字段，冷数据的字段和活跃数据的字段分别建立 2 张表来管理。

2. 横向分表

把大的表结构，横向切割为同样结构的不同表，如，用户信息表，user_1,user_2 等，表结构是完全一样。

二、慢查询

show variables like 'slow%';
show global status like 'slow%';

使用 mysqlreport；

正确使用索引：explain 分析查询语句，组合索引，索引副作用（占空间、update）

开启慢查询日志、使用慢查询分析工具 mysqlsla；

索引缓存、索引代价（插入更新索引）；

表锁，行锁，行锁副作用（update 多时候变慢），在 select 和 update 混合的情况下，行锁巧妙解决了读写互斥的问题；

开启使用查询缓存；

修改临时表内存空间；

开启线程池；

MySQL Query 语句优化的基本思路和原则

1、优化需要优化的 Query；

2、定位优化对象的性能瓶颈；

3、明确优化目标；

4、从 Explaing 入手；

5、多使用 Profile；

6、永远用小结果集推动大的结果集；

7、尽可能在索引中完成排序；

8、只取自己需要的 Columns；

9、仅仅使用最有效的过滤条件；

10、尽可能避免复杂的 Join 和子查询。

推荐教程：《MySQL教程》

以上がMySQL のトップ 10 の最適化方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。