mysqlのパフォーマンス最適化の問題を整理する
- 王林オリジナル
- 2019-08-19 15:46:022223ブラウズ
Mysql 最適化の総合的な問題:
A. テーブル設計の合理化 (3 つのパラダイムに沿った)
B. 適切なインデックス (インデックス) の追加 [4 種類: 通常インデックス、主キー インデックス、一意のインデックス、一意のフルテキスト インデックス]
C. テーブル分割テクノロジ (水平分割、垂直分割)
D. 読み取りおよび書き込み [書き込み: update/ delete/add ] の分離
E. ストアド プロシージャ [モジュラー プログラミング、速度を向上させることができます]
F. mysql 構成の最適化 [同時実行の最大数を構成し、my.data のキャッシュ サイズを調整します。 ini]
G. Mysql サーバーの推奨アップグレード
H. 不要なデータを定期的に削除し、定期的にデフラグする
推奨される Mysql 関連のビデオ チュートリアル:https://www. php.cn/course/list/51/type/2.html
1. データベース テーブルの設計
第一正規形: 1NF は制約が必要とする属性の原子性です属性 (列) はアトミックであり、分解できません; (リレーショナル データベースが 1NF を満たす限り)
第 2 正規形: 2NF はレコードに対する一意の制約であり、レコードには一意の識別子が必要です。エンティティの一意性;
第 3 正規形: 3NF はフィールドの冗長性に関する制約であり、フィールドが冗長でないことを要求します。冗長データベース設計ではそれができません。
2. SQL 最適化の一般的な手順
操作手順:
1. show status コマンドを使用して、さまざまな SQL の実行頻度を把握します。
2. 実行効率の低い SQL ステートメントを特定します - (キー選択)
3. Explain を通じて効率の低い SQL ステートメントの実行を分析します
4. 問題を特定しますそして、対応する最適化措置を講じてください。
MySQL は、show [session|global] status コマンドを使用してサーバーのステータス情報を提供できます。
session は現在の接続の統計結果を表し、global はデータベースが最後に起動されてからの統計結果を表します。デフォルトはセッションレベルです。
「Com_%」のようなステータスを表示;
Com_XXX は、XXX ステートメントが実行された回数を表します。例:Com_insert,Com_Select...
重要な注意点: Com_select,Com_insert,Com_update,Com_delete これらのパラメータを通じて、現在のデータベース アプリケーションが主に挿入および更新操作に基づいているか、クエリ操作に基づいているか、また、クエリ操作に基づいているかを簡単に理解できます。各種SQLの実行率の目安はどれくらいですか。
Connections: MySQL サーバーへの接続試行回数
Uptime: サーバーの動作時間 (秒単位)
Slow_queries: 低速クエリの数 (デフォルトは低速クエリ時間 10 秒)
「Handler_read%」のようなステータスを表示 使用されたクエリの数
##＃1。
# 1.1の追加プライマリキーインデックス
の追加
Createtable aaa(id int unsigned Primary key auto_increment,
一般に、通常のインデックスは最初にテーブルを作成し、次に通常のインデックスを作成します。
例:
CreateINDEX インデックス FROM テーブル名
1.3 フルテキスト インデックスの作成
フルテキスト インデックス (主にドキュメント用) MyISAM では便利ですが、タイトル varchar(20),
本文,
本文、#本文、 本文が「%mysql%」[全文インデックス] のような記事から * を選択してください使用されません]
%mysql%'
## SELECT * FROM Article WHEREMATCH (Title, Body) Against ('database'); [はい]## ::
## 1. mysql では、Myisam のインデックス値が有効になります 2、英語で有効、àsphinx (Coreseek) 技術処理中国語 3、使用されるメソッド、match (フィールド名、 ...）（ 'キーワード'）##＃テキストでは、インデックスとして無限のブックが作成されるため、一部の一般的な単語や文字は作成されません。これらの単語はストップワードと呼ばれます。 1.4 一意のインデックスを作成しますテーブルの列が UNIQUE 制約として指定されており、この列が唯一のインデックスです 最初のテーブル作成 DDD (ID Int Primary Keyauto_increment, name varchar (32); At今回は、名前がデフォルトの唯一のインデックスですテーブル eee(id int main keyauto_increment, name varchar(32));
テーブル eee(id int Primary keyauto_increment, name varchar(32)) を作成します;
簡単に言うと、プライマリです。 Key = Unique Not NULL
unique フィールドは null にすることも、複数の NULL を指定することもできますが、特定のコンテンツの場合は繰り返すことはできません。
2. クエリ
1 。 Desc テーブル名 [この方法の欠点、インデックス名が実現できない]
2.テーブル名からインデックスを表示;
テーブル名からインデックスを表示します。テーブル名からのキーを表示
3、削除
変更可能なテーブル名ドロップインデックスインデックス名,
変更可能なテーブル名ドロップ主キー。 (メインの barbenic 名を削除)
4.
を修正してから最初に削除します
2 では、SQL を書くことによって SQL が遅くなるため、最適化するのが比較的便利です。前のセクションで説明したように、インデックスを正しく使用するとクエリを高速化できるため、SQL を作成するときはインデックスに関連するルールに注意する必要があります:
1. フィールド型の変換によりインデックスは不要になります。文字列型など。数値型などには引用符を使用しないでください。これにより、インデックスが使用されず、テーブル全体のスキャンが発生する可能性があります。
2.mysql は関数変換をサポートしていないため、関数を追加できませんフィールドの前に追加しないとインデックスに使用されません;
3. フィールドの前に加算または減算しないでください;
4. 文字列が比較的長い場合は、インデックス ファイルのサイズを削減し、書き込み効率を向上させるために、その一部にインデックスを作成することを検討できます。
5.like % インデックスは先頭では使用されません。
6. インデックスは次のとおりです。ジョイント インデックスの 2 番目以降のフィールドに基づく個別のクエリでは使用されません;
7. select * は使用しないでください;
8. 並べ替えには昇順を使用するようにしてください;
9. or クエリには代わりに Union (Innodb) を使用してみてください;
10. 複合インデックスの高い選択性 フィールドが最初にランク付けされます;
11. / による順序groupby フィールドはインデックスに含まれるため、並べ替えが軽減され、効率が高くなります。
SQL を作成するときは、上記のインデックス使用規則に加えて、次の点に特に注意する必要があります:
1. 同時実行性に影響を与える大規模なトランザクション SQL は避けるようにしてください。データベースのパフォーマンスとパフォーマンス マスターとスレーブの同期;
2. ページング ステートメントの制限に関する問題;
3. テーブル内のすべてのレコードを削除するには、truncate を使用してください。delete は使用しないでください。
4. mysql にはやらせないでください 計算などの冗長なこと;
5. その後のテーブル変更による問題を防ぐため、フィールドを含む SQL を入力して記述します パフォーマンスも比較的良好です良い (データ ディクショナリ分析が含まれるため、自分で情報をクエリしてください);
6. Innodb は統計情報を保存するため、Innodb で select count(*) を使用します;
7. オーダーを使用します。 rand() を使用する場合は注意が必要です。
3. 遅いクエリの数を表示します: 'slow_queries' のようなステータスを表示します。
4. 非効率な SQL ステートメントの分析の説明: 
は次の情報を生成します:
## クエリのタイプ。
table: 結果セットを出力するテーブル
type: テーブルの接続タイプを示します
possible_keys: クエリを実行するときに使用される可能性のあるインデックスを示します
キー: 実際に使用されるインデックスを示します
＃＃：
：
Use 's' s 's' s 'sを使用して「s」を使用して使用する使用を使用して使用します。 to ‐‐ ‐‐‐‐ lead to 内層の最初の選択は外部クエリに依存します
Union: Union ステートメントの 2 番目の select は後続のすべての select で始まります
Simple: シンプルモード
ユニオンの結果: ユニオンのマージ結果
タイプ タイプ:
すべて: 完全なテーブル スキャンは通常は適切ではありません
システム: テーブルには1 行 (= システム テーブル) これは const 結合タイプです。特殊なケースです。
const: テーブルには一致する行が 1 つまであります。
extra type:
no table: from Dual がクエリ ステートメントで使用されているか、from 句が含まれていません
filesort の使用: クエリに order by 操作が含まれており、並べ替えを完了するためにインデックスを使用できない場合
USING TEMPORARY: 一部の操作では一時テーブルを使用する必要があります。共通の group by、order by
使用場所: テーブルを読み取らなくても、テーブル内のすべての情報から必要なデータを取得できます。
##4. インデックスを使用するとクエリの速度が速くなるのはなぜですか?通常のクエリにインデックスがない場合、クエリは実行され続けます。の場合、クエリは続行されます。クエリが後で実行されるという保証はありません。何かお問い合わせはありますか?全文インデックス作成が必要です。
#■インデックス使用上の注意事項
インデックス作成のコスト:
1. ディスクを占有します。スペース
2。DML (挿入、更新、作成) 操作に影響し、速度が低下します。
#■概要: インデックスは、次の条件が満たされる場合にのみ作成する必要があります
A. 間違いなく 
#■ インデックス使用上の注意点 ：
alter table dept addindex myind (dname,loc); // dname は左側のカラム、loc は右側のカラム
次の状況でインデックスを使用することができます
a. 作成された複数列インデックスの場合、クエリ条件が左端の列を使用している限り、通常、インデックスは Explain Select を使用します。 * from dept where dname='aaa';
b. Query のようなものを使用する場合、クエリ条件が '�a' の場合、インデックスは使用されず、'aaa%' の場合はインデックスが使用されます。
次の状況ではインデックスは使用されません:
a. or がある場合、条件付きインデックスがあっても使用されません。つまり、すべてのフィールドused はインデックスを作成するために必要です。提案: または キーワード
b の使用は避けてください。複数列インデックスの場合、これは最初に使用される部分ではないため、インデックスは使用されません
Explain select * from dept where loc='aaa';//複数列インデックスを使用する場合、loc は正しい列であり、インデックスは使用されません
c.like クエリは % で始まります。使用する必要があります。
d をクエリするにはフルテキスト インデックスを使用してください。列の型が文字列の場合、条件内でデータを引用符で囲む必要があります。そうしないと、インデックスは使用されません
e. MySQL がインデックス ブロックを使用するよりもテーブル全体のスキャンを使用する方が優れていると判断した場合は、インデックスを使用しないでください。
mysql のストレージ エンジンの選択方法
1: myISAM# ＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃BBSへの投稿と返信など、同僚が主に質問したり追加したりする事務です。 2:InnoDBトランザクションの要件が高く、保存されるデータは重要なデータであるため、データベースにアクセスすると同時に、クエリや変更のためにサイトにアクセスすることもできます。
myISAM と InnoDB の違い:
1. MyISAM のバッチ挿入は高速ですが、InnoDB の挿入は遅く、myISAM は挿入時にソートされません。
2. InnoDB はトランザクションをサポートしますが、myISAM はトランザクションをサポートしません。
3. MyISAM はフルテキスト インデックスをサポートします、
4. ロック メカニズム、myISAM はテーブル ロック、InnoDB は行ロックです
5. MyISAM は外部インデックスをサポートしませんJian
① 進行要件が高いアプリケーションでは、固定小数点データを使用して値を保存し、データの精度を確保するためにグループ U を使用することをお勧めします。 float よりも、
② を使用してみてください。 ストレージ用 エンジンの myISAM データベースを削除して変更する必要がある場合は、optimize_table_name 関数を定期的に実行してテーブルをデフラグする必要があります。
③ 日付タイプは、実際のニーズに基づいてストレージ参照が最小の初期タイプを選択する必要があります。
データベースを手動でバックアップします:
1. cmd
と入力します。2. Mysqldump –uroot –proot データベース [テーブル名 1, テーブル名 2…] > ファイルパス
例: mysqldump -uroot -proot temp > d:/temp.bak
バックアップ ファイル データの復元:
ソース d:/temp.bak (mysql コンソール内)
合理的なハードウェア リソースとオペレーティング システム
マスター
Slave1
Slave2
Slave3
メイン データベース マスターは書き込みに使用され、スレーブ 1 ～ スレーブ 3 は選択に使用されます。各データベース
共有プレッシャーが少なくて済みます。
この方法を実現するには、書き込み用のマスター、読み出し用の
スレーブを使用するため、プログラムを特別に設計する必要があり、プログラム開発に負担がかかります。もちろん、この
プロキシを実装するミドルウェアはすでに存在しており、プログラムがどのデータベースを読み書きするかを透過的に認識します。公式の mysql-proxy はありますが、
はまだアルファ版です。 Sina には、この目的を達成できる mysql 用の amobe があり、構造
は次のとおりです:
5. テーブルのパーティショニング 水平分割 :
データ量の多いテーブルの検索を提供する場合、ビジネスのニーズに応じてテーブルの標準を見つけ、検索ページでユーザーの検索方法を制限し、検索ページと連携する必要があります。ページ、
# ケース: 大量のデータを含むユーザー テーブル
3 つのテーブル: qqlogin0、qqlogin1、qqlogin2
ユーザー ID%3 を結果に応じて異なるテーブルに配置します
create tableqqlogin0(
id int unsigned not null 主キー,/* この ID は自動インクリメントに設定できません*/
name varchar (32) not null デフォルト'',
pwd varchar(32)not null default''
)engine = myisam default charset = utf8;
Create table qqlogin1(
id int unsigned not null 主キー, / *この ID は自動インクリメントに設定できません* /
name varchar (32) not null default'',
pwd varchar (32) not null デフォルト ''
) エンジン = myisam デフォルト charset = utf8;
テーブル qqlogin2 を作成 (
id int unsigned) null ではない主キー、/*この ID は自動インクリメントに設定できません * /
name varchar(32)not null default''，
pwd varchar(32)not null default' '
)engine = myisam default charset = utf8;
垂直セグメンテーション:
テーブルのいくつかのフィールドを 1 つのテーブルに配置します。これらのフィールドはクエリ時には関係ありませんが、データ量が多いため、これらのフィールドをテーブルに配置することをお勧めします。これにより、効率が向上します。
6. 最適化された mysql 構成
MY.INI
port = 3306デフォルトポートは 3306、
ポート port = 3309 を変更したい場合は、mysql_connect ('localhost: 3309', 'root', 'root') で、
query_cache_size = 15M であることに注意してください。これはクエリ キャッシュのサイズです。
InnoDB パラメータも使用できます。次の 2 つのパラメータを増やします。
innodb_Additional_mem_pool_size = 64M
innodb_buffer_pool_size = 1G
myisam key_buffer_size を調整する必要があります。
パラメータの調整はステータスにも依存します。show status を使用します。現在のステータスを確認して、どのパラメータを調整する必要があるかを決定できます。
7. 増分バックアップ
実際のケース: 増分バックアップとリカバリを実行する方法
手順:
図 1 に示すように、my.ini ファイルまたは my.cof を次のように構成します。バイナリ バックアップを有効にします
2 、MySQLを再起動します
起動後、mylog ディレクトリにいくつかのファイルが生成されることがわかります。
その中には、E:\binary log\mylog.index インデックス ファイルがあります。バックアップ ファイルはそこにあります。
E:\Binary Log\mylog.000001 ユーザー オブジェクト データベースの操作を保存するファイル
3 を操作 (選択) するとき、
インストールを表示します。ディレクトリに MySQL bin を入力し、mysqlbinlog を実行してファイルを見つけ、次に示すようにファイル パス
を追加する必要があります。図 4 を特定の時点に復元するステートメントの時点
4,1時点に応じて返信
Mysqlbinlog -stop-datetime="2013-01-17 12:00:23 "d:/binlog/mylog.000001 | mysq -uroot -p
(停止時間前にすべてのデータを復元)
Mysqlbinlog-start-datetime="2013-01-17 12:00 :23" d:/binlog/ mylog.000001 | mysq -uroot -p
(開始時刻以降のすべてのデータを復元)
4,2位置に従って復元
Mysqlbinlog-stop-position=" 234"d:/binlog/mylog.000001 | mysq -uroot -p
(停止時間前のすべてのデータを復元)
Mysqlbinlog-start-position= "234"d:/ binlog/mylog.000001 | mysq -uroot -p
(開始時間後にすべてのデータを復元)
その他の関連する質問については、PHP 中国語 Web サイトを参照してください。 https://www.php.cn/
以上がmysqlのパフォーマンス最適化の問題を整理するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。