[解決済み] MySQLの隠れた機能

2023-01-24 19:06:35

質問

これまでマイクロソフト SQL サーバーを何年も使ってきましたが、つい最近になって MySQL を使い始めたばかりで、知識を得ることに飢えています。

の長い行程を続けるために隠し機能に関する質問このオープンソースデータベースの知識を向上させるために、MySQL の隠された機能や便利な機能を教えてください。

どのように解決するのですか？

懸賞金を出してくれたので、私の秘密を教えましょう...。

一般的に、今日私がチューニングしたすべての SQL は、サブクエリを使用する必要がありました。Oracle データベースの世界から来た私は、当たり前だと思っていたことが MySQL では同じように動作していませんでした。そして、MySQLのチューニングについて読んだ結果、MySQLはクエリの最適化という点ではOracleに遅れをとっているという結論に達しました。

ほとんどの B2C アプリケーションに必要な単純なクエリは MySQL でうまく動作するかもしれませんが、インテリジェンスレポーティングに必要な集計レポートタイプのクエリのほとんどは、かなりの量の計画と MySQL がより速く実行できるように SQL クエリを再組成する必要があるようです。

管理

max_connections は同時接続数です。デフォルト値は 100 接続 (5.0 以降は 151) - 非常に小さい値です。

注意してください。

接続はメモリを消費するため、OS が多くの接続を処理できない場合があります。

Linux/x86 用の MySQL バイナリは最大 4096 の同時接続を許可していますが、セルフコンパイルされたバイナリではこれより少ない制限しかないことがよくあります。

開いているテーブルと同時接続の数に一致するように table_cache を設定します。open_tables の値を監視し、それが急速に増加している場合、そのサイズを増やす必要があります。

注意してください。

前の 2 つのパラメータは、多くのオープンファイルを必要とする場合があります。20+max_connections+table_cache*2 は、必要なものの良い見積もりです。LinuxのMySQLにはopen_file_limitオプションがあり、この制限を設定します。

複雑なクエリを使用する場合、sort_buffer_size と tmp_table_size は非常に重要なものになると思われます。値はクエリの複雑さと利用可能なリソースに依存しますが、それぞれ4MBと32MBが推奨される開始点です。

注：これらはread_buffer_size、read_rnd_buffer_sizeや他のいくつかの値のうち、quot;接続ごとの値です。つまり、この値は接続ごとに必要かもしれないということです。このため、これらのパラメータを設定する際には、負荷や利用可能なリソースを考慮する必要があります。例えば sort_buffer_size は、MySQL がソートを行う必要がある場合にのみ割り振られます。注：メモリ不足にならないように注意してください。

多くの接続を確立している場合 (すなわち、持続的な接続を持たない Web サイト)、thread_cache_size をゼロ以外の値に設定することでパフォーマンスを向上させることができます。16 は、最初に設定するのに適した値です。threads_createdがあまり速く成長しないようになるまで値を増やします。

PRIMARY KEY。

AUTO_INCREMENT カラムは 1 つのテーブルに 1 つだけ存在でき、インデックスを付けなければならず、DEFAULT 値を持つことはできません。

KEYは通常INDEXと同義語です。キー属性のPRIMARY KEYは、列の定義で指定された場合、単にKEYとして指定することもできます。これは他のデータベースシステムとの互換性のために実装されました。

PRIMARY KEYは一意なインデックスで、全てのキーカラムはNOT NULLと定義されなければなりません。

PRIMARY KEYまたはUNIQUEインデックスが、整数型を持つ1つの列のみで構成されている場合。 SELECT ステートメントでそのカラムを "_rowid" として参照することもできます。

MySQLでは、PRIMARY KEYの名称はPRIMARY

現在、InnoDB (v5.1?) テーブルのみが外部キーをサポートしています。

通常、テーブルを作成する際に、必要なインデックスを全て作成します。 PRIMARY KEY、KEY、UNIQUE、INDEXとして宣言された列はすべてインデックスが作成されます。

NULLとは、値を持たないという意味です。NULLをテストするには、次のようにします。 はできません。 のような算術比較演算子を使用することはできません。代わりに、IS NULL および IS NOT NULL 演算子を使用します。

NO_AUTO_VALUE_ON_ZEROは、NULLのみが次のシーケンス番号を生成するように、0に対する自動インクリメントを抑止します。このモードは、テーブルのAUTO_INCREMENT列に0が格納されている場合に有用です。(ちなみに、0を格納することは推奨されません)。

新しい行に使用されるAUTO_INCREMENTカウンタの値を変更するためです。

ALTER TABLE mytable AUTO_INCREMENT = value;

または SET INSERT_ID = 値です。

特に指定がない限り、値の先頭は 1000000またはこのように指定します。

...) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

タイムスタンプ。

TIMESTAMPカラムの値は、保存時に現在のタイムゾーンからUTCに変換されます。また、検索時にはUTCから現在のタイムゾーンに変換されます。

http://dev.mysql.com/doc/refman/5.1/en/timestamp.html テーブルの1つのTIMESTAMPカラムに対して、現在のタイムスタンプをデフォルト値および自動更新値として割り当てることができます。

WHERE句でこれらの型を使用する際に気をつけるべきことは、以下のようにするのがベストです。 WHERE datecolumn = FROM_UNIXTIME(1057941242) であって WHERE UNIX_TIMESTAMP(datecolumn) = 1057941242)ではなく、WHERE UNIX_TIMESTAMP(datecolumn) = 1057941242 とすることです。後者を実行すると、そのカラムのインデックスを利用することができません。

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

 UNIX_TIMESTAMP() 
 FROM_UNIXTIME() 
 UTC_DATE()
 UTC_TIME()
 UTC_TIMESTAMP()

MySQLでdatetimeをunix timestampに変換した場合。

そして、それに24時間を追加します。

そして、それを日付に戻すと、不思議なことに1時間分減っているのです!

ここで何が起こっているかというと unix タイムスタンプを日付時刻に変換する際にタイムゾーンが考慮され、2006 年 10 月 28 日から 29 日の間にサマータイムが導入され、1 時間が失われたのです。

MySQL 4.1.3 以降、CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), FROM_UNIXTIME() 関数は、接続の 現在のタイムゾーン これは、time_zoneシステム変数の値として利用可能である。さらに、UNIX_TIMESTAMP() は、その引数が現在のタイムゾーンの日付時間値であると仮定します。

現在のタイムゾーンの設定は、UTC_TIMESTAMP()のような関数で表示される値やDATE、TIME、DATETIMEカラムの値には影響しません。

注意: 更新時にのみは、フィールドが変更された場合に DateTime を更新します。 UPDATE の結果、フィールドが変更されなかった場合、 DateTime は更新されません!

さらに、最初のTIMESTAMPは、指定されていない場合でも、デフォルトで常にAUTOUPDATEです。

日付を扱うとき、私はほとんど常にユリウス日付に納得します。なぜなら、データ計算は整数の加算または減算の単純な問題であり、同じ理由で真夜中からの秒数にも納得するからです。秒より細かい粒度の時間情報が必要なことはめったにありません。

これらは両方とも 4 バイトの整数として保存でき、スペースが本当に限られている場合は、符号なし整数として UNIX 時間 (エポック 1970 年 1 月 1 日からの秒) にまとめることができ、2106 年頃まで使用できます。

24 時間における秒数 = 86400

符号付き整数の最大値 = 2,147,483,647 - 68年分の秒を格納可能

符号なし整数の最大値 = 4,294,967,295 - 136 年間の秒数を保持できます。

バイナリプロトコル。

MySQL 4.1 はバイナリプロトコルを導入し、ストリング以外のデータ値をストリングフォーマットから変換することなくを文字列フォーマットに変換することなくネイティブフォーマットで送信し、返すことができます。(非常に便利です)

余談ですが、mysql_real_query() は strlen() を呼び出さないので mysql_query() よりも速いです。を呼び出してステートメント文字列を操作しないからです。

http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html バイナリプロトコルはサーバサイドのプリペアドステートメントをサポートし、ネイティブフォーマットでのデータ値の送信を可能にします。バイナリプロトコルは、MySQL 4.1 の初期リリースでかなりの改訂が行われました。

IS_NUM()マクロを使用して、フィールドが数値型を持つかどうかをテストすることができます。 IS_NUM()に型の値を渡すと、フィールドが数値の場合、TRUEと評価されます。

1つ注意すべきことは、バイナリデータ CAN をエスケープすれば、通常のクエリの中で送信することができ、また、MySQL ではのみを要求し、バックスラッシュと引用符はエスケープされることを覚えておいてください。これは、例えば暗号化/塩漬けされたパスワードのような短いバイナリ文字列をINSERTする本当に簡単な方法です。

マスターサーバー。

http://www.experts-exchange.com/Database/MySQL/Q_22967482.html

http://www.databasejournal.com/features/mysql/article.php/10897_3355201_2

レプリケーションスレーブを . をslave_user IDENTIFIED BY 'slave_password' に付与します。

#Master Binary Logging Config  STATEMENT causes replication 
              to be statement-based -  default

log-bin=Mike
binlog-format=STATEMENT
server-id=1            
max_binlog_size = 10M
expire_logs_days = 120    


#Slave Config
master-host=master-hostname
master-user=slave-user
master-password=slave-password
server-id=2

バイナリログファイルは必ずお読みください。

http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

http://www.mydigitallife.info/2007/10/06/how-to-read-mysql-binary-log-files-binlog-with-mysqlbinlog/

http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html

http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html

RESET MASTER ステートメントですべてのバイナリログファイルを、または PURGE MASTER でそれらのサブセットを削除することができます。

--result-file=binlog.txt TrustedFriend-bin.000030 とします。

正規化します。

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

UDF関数

http://www.koders.com/cpp/fid10666379322B54AD41AEB0E4100D87C8CDDF1D8C.aspx

http://souptonuts.sourceforge.net/readme_mysql.htm

DataTypesです。

http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html

http://www.informit.com/articles/article.aspx?p=1238838&seqNum=2

http://bitfilm.net/2008/03/24/saving-bytes-efficient-data-storage-mysql-part-1/

注意点としては、CHARとVARCHARの両方が混在するテーブルでは、mySQLはCHARをVARCHARに変更することです。

RecNum integer_type UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (RecNum)

MySQLは、標準SQLとISO 8601の仕様に従って、常に年を先にした日付を表します。

Misc:

いくつかの MySQl 機能をオフにすることで、データファイルが小さくなりが小さくなり、アクセスが速くなります。例えば

--datadir はデータディレクトリを指定し

-skip-innodbはinnoオプションをオフにし、10-20Mを節約します。

詳細はこちら http://dev.mysql.com/tech-resources/articles/mysql-c-api.html

第7章ダウンロード（無料

InnoDBはトランザクションですが、それに伴うパフォーマンスのオーバーヘッドがあります。私のプロジェクトの90%では、MyISAMテーブルで十分であることを発見しました。非トランザクションセーフテーブル(MyISAM)には、それ自身のいくつかの利点がありますが、そのすべてが発生する理由です。

トランザクションのオーバーヘッドがない。

はるかに速い

必要なディスク容量が少ない

更新に必要なメモリが少ない

各 MyISAM テーブルは、3 つのファイルに分かれてディスクに保存されます。ファイル名はテーブル名で始まり、ファイルの種類を示す拡張子がついています。.frm ファイルにはテーブルのフォーマットが格納されます。データファイルは .MYD (MYData) という拡張子を持ちます。インデックスファイルには .MYI (MYIndex) という拡張子があります。

これらのファイルはは、時間のかかる MySQL 管理者バックアップ機能を使用せずに、そのまま保管場所にコピーされます（リストアも同様です）。

トリックは、これらのファイルのコピーを作成し、テーブルをDROPすることです。ファイルを戻したとき MySQl はそれらを認識し、テーブルトラッキングを更新します。

もし、バックアップ/リストアが必要なら。

バックアップのリストア、または既存のダンプファイルからのインポートは、各テーブルにあるインデックスと主キーの数に応じて長い時間がかかることがあります。元のダンプファイルを次のように囲んで変更することで、この処理を劇的に高速化することができます。

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. your dump file ..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

再読み込みの速度を大幅に向上させるには、ダンプファイルの先頭に SET AUTOCOMMIT = 0; という SQL コマンドを追加し、最後に COMMIT; コマンドを追加してください。

デフォルトでは、自動コミットはオンになっており、ダンプファイル内の各挿入コマンドはダンプファイル内の各挿入コマンドは、個別のトランザクションとして扱われ、次のトランザクションが開始される前にディスクに書き込まれます。これらのコマンドを追加しない場合、大規模なデータベースを InnoDB に再ロードするのに何時間もかかることがあります...。

MySQL テーブルの行の最大サイズは 65,535 バイトです。

MySQL 5.0.3 以降での VARCHAR の実効最大長 = 最大行サイズ (65,535 バイト)

VARCHAR 値は、保存時にパディングされません。末尾のスペースは値が格納され、検索されるとき、標準SQLに準拠して、末尾のスペースは保持されます。

MySQL の CHAR と VARCHAR 値は、末尾のスペースを考慮せずに比較されます。

CHAR を使用すると、レコード全体が固定サイズである場合にのみアクセスが高速化されます。つまり可変サイズオブジェクトを使用する場合、それらすべてを可変サイズにしたほうがよいでしょう。 VARCHARも含むテーブルでCHARを使用しても、スピードは上がりません。

VARCHAR の 255 文字という制限は、MySQL 5.0.3 で 65535 文字に引き上げられました。

全文検索は MyISAM テーブルのみサポートされます。

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

BLOBカラムは文字セットを持たず、ソートと比較はカラム値のバイト数に基づいて行われます。列の値のバイトの数値に基づきます。

厳密なSQLモードが有効でなく、BLOBまたはTEXT列に列の最大長を超える値を割り当てた場合、その値は適合するように切り捨てられます。に値を割り当てた場合、その値は適合するように切り捨てられ、警告が生成されます。

便利なコマンドです。

ストリクトモードを確認します。 SELECT @@global.sql_mode;

ストリクトモードをオフにします。

SET @@global.sql_mode= '';

SET @@global.sql_mode='MYSQL40'.

または削除してください。 sql-mode="STRICT_TRANS_TABLES,...

から列を表示します。 mytable

SELECT max(namecount) AS virtualcolumn FROM mytable ORDER BY virtualcolumn

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id last_insert_id()

現在のスレッドに挿入された最後の行のPKを取得します max(pkcolname) は、全体の最後のPKを取得します。

注意: テーブルが空の場合 max(pkcolname) は 1 を返します mysql_insert_id() は、ネイティブ MySQL C API 関数 mysql_insert_id() の戻り値の型を、long 型に変換します。 long (PHP では int という名前) の型に変換します。

AUTO_INCREMENT カラムのカラムタイプが BIGINT の場合、この関数が返す値はによって返される値は不正確になります。代わりに、SQL クエリで MySQL 内部 SQL 関数 LAST_INSERT_ID() を使用します。

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

ただ、テーブルにデータを挿入しようとして、エラーが発生したときの注意点です。

Unknown column ‘the first bit of data what you want to put into the table‘ in ‘field list’

のようなものを使って

INSERT INTO table (this, that) VALUES ($this, $that)

というのは、テーブルに貼り付けようとしている値の周りにアポストロフィがないからです。だから、あなたのコードを変更する必要があります。

INSERT INTO table (this, that) VALUES ('$this', '$that')

は MySQL のフィールド、データベース、またはテーブルを定義するために使用され、値ではないことに注意してください;)

クエリ実行中にサーバへの接続を失いました。

http://dev.mysql.com/doc/refman/5.1/en/gone-away.html

http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html

http://dev.mysql.com/doc/refman/5.1/en/show-variables.html

http://dev.mysql.com/doc/refman/5.1/en/option-files.html

http://dev.mysql.com/doc/refman/5.1/en/error-log.html

クエリのチューニング

http://www.artfulsoftware.com/infotree/queries.php?&bw=1313

これでボーナスを得るには十分だと思うのですが...。多くの時間と多くのプロジェクトの成果であり、素晴らしい フリー データベースを使用しています。私は、Windows プラットフォームで、主に MySQL を使用したアプリケーションデータサーバを開発しています。私が修正しなければならなかった最悪の混乱は

究極の MySQL レガシーデータベースの悪夢

このため、ここで述べた多くのトリックを使用して、テーブルを有用なものに処理するための一連のアプリケーションを必要としました。

もしこれが驚くほど役に立つと感じたなら、投票することで感謝の意を表してください。

また、私の他の記事やホワイトペーパーもご覧ください: www.coastrd.com