高性能Mysql(第3版)

文書ディレクトリ

1. Mysqlアーキテクチャと履歴

2. サーバパフォーマンスプロファイル

3. Schemaとデータ型の最適化

1. 整数は文字列タイプよりもコストが小さい.文字列には文字セットと校正規則が必要です.

2. 時間タイプ:

3. 整数タイプ:

4. インデックス

2. 最左接頭辞:

3. ≪索引の上書き｜Override Index｜oem_src≫:Selectの列.索引でカスタマイズされた列です.

4. インデックスの役割

5. プレフィックスインデックス

6. 連結インデックス

7. クラスタインデックス

8. インデックスの上書き

9. インデックススキャンを使用してソート

10. 圧縮インデックス

11. 冗長インデックス

12. インデックスとロック

13. 最適化ソート

5. クエリ最適化

1.Mysqlアーキテクチャと履歴

MyISAMはメモリにデータを書き込むだけで、オペレーティングシステムが定期的にディスクにデータをブラシするのを待っています.そのため、停電が保証されず、データが失われない.

infobrightは、Mysqlのデータ量が大きすぎる場合に、データウェアハウスとして使用されます.

表のエンジンを変更すると、表の元のエンジンのすべての特性が失われます.

Mysqldumpでデータインポートを行う場合、mysqlDumpのデフォルトではcreate table文の間にdrop tableがロードされていることに注意してください.この点に注意しないと、データベースの元のテーブルのデータが失われます.

Mysql5.5からInnoDBがMysqlのデフォルトエンジンになった.

2.サーバのパフォーマンスプロファイル

パフォーマンス分析ツール:

NewRelic.

xhprof.

Mysql 5.0以降、遅いクエリー・ログにはミリ秒レベルが表示されます.

3.Schemaとデータ型の最適化
1.整数は文字列タイプよりもコストが小さい.文字列には文字セットと校正規則が必要です.
2.時間タイプ:

DateTime:時間と日付を秒まで正確に保存します.<1001年-9999年>

TimeSamp:時間と日付を秒まで正確に保存します.DateTimeの半分のストレージスペースであり、タイムゾーンが変化するにつれて、特別な自動更新能力があります.さらに、許容時間の範囲は小さくなります.<1970年1月1日-2038年>

3.整形タイプ:

データ範囲:-2^(n-1)~2^(n-1)-1,Nは空間ビット数である.またunsignedプロパティを選択して、負数にできないことを示し、正数の上限を2倍にすることができます.指定した整数長は不要であり、格納された数値範囲を制限することはできません.一部のデータベースのインタラクティブツールで表示されるデータビット数を制御します.記憶と計算において,Int(1)とInt(20)は一致している.

tinyInt:8ビット空間.

smallInt:16ビット.

MediumInt:24ビット.

Int:32ビットです.

BigInt:64ビット.

実数タイプ:小さいデータに対してdecimalを使用して格納し、データ量が大きい場合はDecimalの代わりにbigIntを使用することを考慮し、後の小数に相応の倍数を乗せればよい.

文字列タイプ:varcharが長くなる記憶データに対して、データの末尾の空の文字列は消去されません.charは固定長で、データの末尾の空の文字列が自動的に消去されます.MYISAMは文字列の圧縮インデックスをデフォルトで設定しており、クエリが大幅に遅くなり、パフォーマンスが大幅に低下します.

4.索引
良いインデックスは文章を使う

Where条件列でインデックスが使用されている場合、インデックスで値を検索してから、その値を含む行を返します.

プライマリ・キー・インデックスと非プライマリ・キー・インデックスは異なり、プライマリ・キー・インデックスに格納されている値は、プライマリ・キー・インデックスに格納されている値ではなく、プライマリ・キー・インデックスに格納されている値であり、プライマリ・キー・フィールドの値ではありません.

2.一番左の接頭辞:
複合インデックスを使用する場合は、インデックスキーの順序でのみ使用できます.一番左のキーをスキップしてもインデックスは使用できません.
3.索引の上書き:Selectの列で、索引でカスタマイズされた列です.
4.インデックスの役割

は、サーバをテーブルに指定された場所に迅速に配置します.

は、サーバがスキャンする必要があるデータ量を削減します.

ソートとテンポラリ・テーブルの生成を回避します.

ランダムI/Oを順次I/Oに変換します.

5.接頭辞索引

定義:BlobとText、または長いvarcharタイプのカラムに対して、通常のインデックスを使用すると、データ量が多く、インデックス空間が急激に増加するため、インデックス空間を小さくするため、インデックス接頭辞がよい.またmysqlの場合、これらの3つのタイプについては、これらのカラムのすべての長さをインデックスすることはできません.

最適なプレフィックス長:COUNT(DISTINCT LEFT(name,1)/count(*)AS pre 1を計算します.長さを変更します.

実装:Alter TABLE user ADD KEY(name(8);8:name列に設定されたインデックス接頭辞の長さです.

6.連結索引

mysql 5.0までは、複数のインデックスを同時に使用することはできませんが、単一のインデックスを有効にするには使用できません.mysql 5.0以降、作成したインデックスに問題がある場合、mysqlはインデックスの最適化を行い、使用可能なインデックスを1つのインデックスに結合して使用します.

7.クラスタインデックス

定義:インデックスタイプではなく、データストレージ方式です.InnoDB内のクラスタインデックスは、実際には同じ構造で1つのB−Treeインデックスとデータ行を保存している.インデックスノードに対応するデータは、インデックスのリーフページに実際に格納されます.2つの異なる場所にデータ行を同時に格納できないため、1つのテーブルに1つのクラスタインデックスしか存在しません.

InnoDBは、プライマリ・キーを介してデータを集約する.

optimize table:データを削除するとmysqlは回収されず、削除されたデータの占有されたストレージスペース、インデックスビットが表示されます.そこに空いているのではなく、新しいデータを待ってこの空きを補うので、不足があります.もししばらくして、この空きを埋めるデータがなければ、資源を浪費しすぎます.だから、比較的煩わしい表を書くには、定期的にoptimizeを行い、1ヶ月に1回、実際の状況を見て決めます.

8.索引の上書き

定義:selectのフィールドは、インデックスに存在し、テーブルに戻ってデータをクエリーする必要はありません.パフォーマンスが大幅に向上します.

9.インデックススキャンでソート

mysqlは、ソート操作とインデックス順のスキャンのみで秩序化された結果を生成します.

explain以降のtypeがindexであれば、インデックスシーケンススキャンを用いた方法でソートすることを説明する.

mysqlは、同じインデックスを使用してソートを満たし、検索行データを満たすことができます.

実装:

インデックスの列順序とORDER BY句の順序が完全に一致し、すべてのソート方向(逆または正の順序)が同じである場合にのみmysqlはインデックスを使用して結果をソートします.

クエリーで複数のテーブルを関連付ける必要がある場合は、ORDER BY句が参照するフィールドがすべて最初のテーブルである場合にのみ、インデックスを使用してソートできます.ORDER BYもインデックスの左端の接頭辞を満たさなければなりません.

インデックスの最初のカラムが定数である場合、ORDER BYは、最も左のプレフィックスを満たす必要はありません.eg: where name = ‘Alex’ order by age. index(name, age). ただし,拍順の順序方向とインデックス順が一致しなければインデックスには使用できない.

10.圧縮(プレフィックス圧縮)インデックス

定義:接頭辞を圧縮することで、インデックスの記憶領域を小さくし、より多くのインデックスをメモリに入れることができます.デフォルトでは文字列のみが圧縮され、整数はパラメータで設定する必要があります.

計算方法:インデックスブロックの最初の値を完全に保存し、他の値と最初の値を比較して同じ接頭辞のバイト数と残りの異なる接尾辞部分を得て、この部分を記憶すればよい.eg: 1. perform 2. performance,格納時,格納データ:7,ance.

11.冗長性と重複インデックス

定義:重複インデックスとは、同じカラムに同じ順序で同じタイプのインデックスを作成することを意味します.

12.インデックスとロック

mysql 5.1以降、InnoDBは、サービス側がローをフィルタリングした後にロックを解除できますが、mysqlの初期バージョンでは、トランザクションがコミットされた後にのみロックが解除されます.

13.ソートの最適化

Distinct:実行すると中間テーブルが生成され、パフォーマンスが低下し、Using temporaryが表示されます.

Using file Sort:ソートされたデータが大きすぎるため、ファイル内でソートされ、インデックスが追加され、ソートフィールドがインデックス内にあります.

5.クエリーの最適化

using(Field):fieldは2つのテーブルの同じ名前でなければ使用できません.ONに相当する機能です.

select t1.id, t1.name
from test t1
inner join test2 using (id)
   
inner join test2 t2 ON t2.id = t1.id

連表クエリーの場合、関連表から出されたフィールドはインデックスに入れるのが望ましいが、インデックスデータを使用し、データ行に戻ってデータを取得する必要はない.

遅延関連付け:アクセスが必要なレコードを取得した後、関連付けられたカラムに基づいて元のテーブルクエリに必要なすべてのカラムを返します.このテクノロジーは、関連クエリのlimit句を最適化するために使用できます.

  ：           
1.    
SELECT id,name,age
FROM `user`
LIMIT 50, 5

2.    
SELECT  id, name, age
FRO `user`
INNER JOIN (   //        id，  id       ，        ，      id。
	SELECT id	//    inner join，  id，        。
	FROM `user`
	LIMIT 50, 5
) as user1 USING(id)