MySQL を簡単に学ぶ (コレクション)

まえがき

著者はデータベースが常に苦手であったこと(python+sqlalchemy)などを踏まえ、ORMを好んで使用していることを記録します。 SQL をスペルアウトするのはもっと面倒なことだと常々感じていました (主に SQL が苦手なためです)。また、以前のメンテナンス プロジェクトで、エンコーディングの問題や浮動小数点数の精度の低下など、いくつかの落とし穴にも遭遇しました。 、将来繰り返される落とし穴を防ぐため。

第1章: ヘルプの使用

mysql組み込みヘルプコマンドを使用する

1. msyql> ? data types : 查看数据类型
   mysql> ? int
   mysql> ? create table

第2章: テーブルタイプ(ストレージエンジン)の選択

最も一般的なのは2 つのエンジンが使用されます:

1. Myisam は Mysql のデフォルトのストレージ エンジンです。create が新しいテーブルを作成し、新しいテーブルのストレージ エンジンが指定されていない場合、Myisam がデフォルトで使用されます。 各 MyISAM はディスク上に 3 つのファイルとして保存されます。ファイル名はテーブル名と同じで、拡張子は .frm (ストレージテーブル定義)、.MYD (MYData、ストレージデータ)、.MYI (MYIndex、ストレージインデックス) です。データ ファイルとインデックス ファイルを異なるディレクトリに配置すると、IO が均等に分散され、速度が向上します。

2. InnoDB ストレージ エンジンは、コミット、ロールバック、クラッシュ回復機能を備えたトランザクションを安全にします。ただし、Myisam のストレージ エンジンと比較すると、InnoDB の書き込み処理効率は低く、データとインデックスを保持するためにより多くのディスク領域を消費します。

一般的な環境:

1. MyISAM: Web、データ ウェアハウス、その他のアプリケーション環境で最も一般的に使用されるストレージ エンジンの 1 つです

2. InnoDB: に使用されます。トランザクション処理アプリケーションには、ACID トランザクションのサポートを含む多くの機能があります。

第 3 章: 適切なデータ型の選択

まず、適切なストレージ エンジンを選択し、指定されたストレージ エンジンに基づいて適切なデータ型を決定します。

• MyISAM: 可変長データ列の代わりに固定長データ列を使用する方が良いでしょう。

• InnoDB: varchar の使用をお勧めします

  
  

注意すべきいくつかのデータ型:

1. Char と varchar: 異なる保管方法と取得方法、最大長、および末尾のスペースが保持されるかどうか。も違います。 char は固定長です。長さが足りない場合は、取得時に PAD_CHAR_TO_FULL_LENGTH が設定されていない場合、末尾のスペースはデフォルトで削除されます。

varchar は可変長の
string であり、末尾のスペースは取得中に保持されます。クエリでは大文字と小文字が区別されないことに注意してください。sqlalchemy を使用して大文字と小文字を区別する場合は、func.binary 関数を使用しないでください。 func.binary函数。

2、text和blob: text和blob执行大量的更新或者删除的时候会留下很大『空洞』，建议定期用OPTIMIZE TABLE功能对这类表碎片整理。避免检索大型的blob或text值 。把text和blob列分离到单独的表中。

3、浮点数float与定点数decimal:

注意几个点：

      1.浮点数虽然能表示更大的数据范围，但是有误差问题。

      2.对货币等精度敏感的问题，应使用定点数存储。之前项目踩过坑，结果不得不用放大和缩小倍数的方法解决，比较ugly。

      3.编程如果遇到浮点数，注意误差问题，尽量避免浮点数比较（比较浮点数需要作差小于一个特定精度），python3.5中可以这么比较：float_eq = partial(math.isclose, rel_tol=1e-09, abs_tol=0.0)2. テキストと blob: テキスト と blob は、大量の更新や削除が実行されると大きな「穴」を残します。このようなテーブルを最適化するには、定期的に OPTIMIZE TABLE 関数を使用することをお勧めします。 。大きな blob またはテキスト値を取得しないでください。テキスト列と BLOB 列を別々のテーブルに分離します。

3. 浮動小数点および固定小数点 10 進数:

いくつかの点に注意してください:

1. 浮動小数点数はより大きなデータ範囲を表すことができますが、エラーの問題があります。
2. 通貨などの精度が重要な問題については、固定小数点ストレージを使用する必要があります。以前のプロジェクトには落とし穴があり、その結果、問題を解決するには拡大と縮小の方法を使用する必要があり、非常に見苦しいものでした。

3.プログラミング

浮動小数点数が発生した場合は、エラーの問題に注意し、回避するようにしてください。浮動小数点数の比較 (浮動小数点数の比較には、特定の精度未満の差が必要です)。 Python3.5 では、次のように比較できます: float_eq = Partial(math.isclose, rel_tol=1e-09, abs_tol=0.0) )

• 4. 浮動小数点数におけるいくつかの特殊な値の処理に注意してください。

第 4 章:
• 文字セット

  
  

最初に適切な文字セットを選択する必要があります。そうしないと、後で置き換えるコストが非常に高くなります。 Python2 の文字セットは長年の問題であり、多くの初心者を混乱させています。以前に保守されていたプロジェクトでは msyql のデフォルトの latin1 文字セットが使用されていたため、文字列を書き込むたびに手動で utf8 にエンコードする必要がありました。最近、python3.5+flask を使用するプロジェクトに utf8 を直接使用しましたが、エンコードの問題に再び遭遇することはありませんでした。

sqlalchemy 接続 URL は mysql://root:root@127.0.0.1:3306/my_db?charset=utf8 を使用します。もう

コード文字化けの問題を心配する必要はありません

🎜🎜第5章: インデックスの設計と使用🎜🎜🎜🎜

すべての mysql 列タイプにインデックスを付けることができます。関連する列にインデックスを使用することが、選択操作のパフォーマンスを向上させる最良の方法です。インデックス設計の原則:

1. 検索されるインデックス列は、必ずしも選択される列であるとは限りません。インデックスに最も適した列は、select キーワードの後の選択リストに表示される列ではなく、where 句 に表示される列、または join 句で指定された列です。

2. 一意のインデックスを使用します。一意の値を持つ列の場合、インデックス作成の効果は高くなりますが、重複する値が複数ある列の場合、インデックス作成の効果は低くなります。

3. 短いインデックスを使用します。文字列列にインデックスを付ける場合は、接頭辞の長さを指定する必要があり、可能な限りこれを行う必要があります。

4. 一番左のプレフィックスを使用します。 n 列インデックスを作成すると、MySQL で使用できる n 個のインデックスが実際に作成されます。複数列インデックスは、インデックスの左端の列セット (左端のプレフィックス) を使用して行を照合できるため、複数のインデックスとして機能できます。

5. インデックスを過剰に作成しないでください。インデックスはディスク領域を無駄にし、書き込みパフォーマンスを低下させます。

6. 列に対して行う比較の種類を検討します。

第 6 章: ロック メカニズムとトランザクション制御

InnoDB エンジンは行レベルのロックを提供し、共有ロックと排他ロック、および 4 つの異なる分離レベルをサポートします。 mysql は、AUTOCOMIT、START TRANSACTIONS、COMMIT、ROLLBACK などのステートメントを通じてローカル トランザクションをサポートします。

第7章: SQLのセキュリティ問題

SQLインジェクション: 一部のデータベースの外部インターフェースを使用して、実際のデータベース操作音声(SQL)にユーザーデータを挿入してデータベースに侵入する目的もオペレーティングシステムの。主な理由は、プログラム ヒープ ユーザーによって入力されたデータが厳密にフィルタリングされていないため、不正なデータベース クエリ ステートメントが実行されることです:

1. prepareStatement = Bind-variable, doスプライシングSQLを使用しないprepareStatement = Bind-variable，不要使用拼接的sql

2. 使用应用程序提供的转换函数

3. 自定义函数校验（表单校验等）
   

8章：SQL Mode及相关问题

更改默认的mysql执行模式，比如严格模式下列的插入或者更新不正确时mysql会给出错误，并放弃操作。set session sql_mode='STRICT_TRANS_TABLES'。设置sql_mode需要应用人员权衡各种得失，做一个合适的选择。

9章：常用SQL技巧

1. 检索包含最大/最小值的行：MAX([DISTINCE] expr), MIN([DISTINCE] expr)

2. 巧用rand()/rand(n)提取随机行

3. 利用group by和with rollup子句做统计

4. 用bit group functions
   

アプリケーションが提供する変換関数を使用する

カスタム関数の検証(フォーム検証など)

第8章: SQLモードと関連する問題

デフォルトの MySQL 実行モード (厳密モードなど) を変更します。挿入または更新が正しくない場合、MySQL はエラーを返し、操作を中止します。 セッション sql_mode='STRICT_TRANS_TABLES' を設定します。 sql_mode を設定するには、アプリケーション担当者がさまざまな長所と短所を比較検討し、適切な選択を行う必要があります。

第 9 章: 一般的な SQL テクニック

最大値/最小値を含む行を取得する: MAX([DISTINCE] expr), MIN([DISTINCE] expr)

rand()/rand(n) を賢く使用して、ランダムな行を抽出します🎜🎜🎜🎜 group by 句と with rollup 句を使用して統計を実行します🎜🎜🎜🎜統計を行うには ビット グループ関数 を使用します🎜🎜🎜🎜🎜🎜🎜第 10 章: 注意が必要なその他の問題🎜🎜🎜🎜🎜データベース名とテーブル名の大文字と小文字の問題: さまざまなプラットフォームとシステム、大文字と小文字を区別するかどうかが異なります。常に小文字の名前を使用することをお勧めします。 🎜外部キーを使用する際の注意事項: mysql の InnoDB は、外部キーワード制約のチェックをサポートしています。 🎜🎜🎜🎜🎜 第 11 章: SQL の最適化 🎜🎜🎜🎜🎜 SQL を最適化するための一般的な手順: 🎜🎜 1. ステータス表示機能とアプリケーション機能を使用して、さまざまな SQL の実行頻度とさまざまな SQL のおおよその実行率を理解します。たとえば、InnoDB のパラメータ Innode_rows_read クエリによって返される行の数、挿入の実行時に Innodb_rows_inserted によって挿入される行の数、Innodb_rows_updated によって更新される行の数などです。また、いくつかのパラメーターもあります。mysql サーバーへの接続試行、Uptime サーバーの稼働時間、および Slow_queries 低速クエリの数です。 🎜🎜🎜 2. 実行効率の低い SQL ステートメントを見つけます。 2 つの方法があります。1 つは、低速クエリ ログを通じて実行効率の低いステートメントを見つけることです。 --log-slow-queries[=file_name] オプションを使用して開始すると、mysqld は、実行時間が long_query_time を超えるすべての SQL ステートメントを含むログ ファイルを書き込みます。秒。もう 1 つは、スレッドの 🎜 ステータス、テーブルがロックされているかどうかなどを含む、MySQL で実行中の現在のスレッドを表示するための show processlist です。SQL の実行ステータスをリアルタイムで確認し、一部のロック テーブル操作を最適化できます。 🎜🎜🎜 3. EXPLAIN を通じて非効率な SQL の実行計画を分析します。Explain は、インデックスを使用してレコードを検索する高速な SELECT を取得するためにテーブルのインデックスを作成する必要がある時期を知ることができます。 以下は EXPLAIN 実行後の結果です。

• select_type: タイプの選択

• table: 結果セットを出力するテーブル

• type: テーブルの接続タイプを表します。 type の値が system である行がテーブルに 1 つだけある場合、それが最適な接続タイプです。選択操作でテーブル接続にインデックスが使用される場合、type 値は ref になります。インデックスを使用する場合、type の値が ALL である場合、この時点でテーブルの接続効率を向上させるためにインデックスの作成を検討する必要があります。

• possible_keys: クエリ時に使用できるインデックス列を示します。

• key: 使用されるインデックスを示します

• key_len: インデックスの長さ

• rows: スキャン範囲

• Extra: 実行の説明と説明

• 
  

4. 確認問題を解決し、対応する最適化措置を講じます。

インデックスの問題

1. インデックスの格納分類: myisam テーブルのデータファイルとインデックスファイルは自動的に分離され、innodb のデータとインデックスは同じテーブルスペースに配置されます。 myisam と innodb のインデックス ストレージ タイプは両方とも btree です

2. Mysql のインデックスの使用方法: インデックスは、列内の特定の値を持つ行をすばやく検索するために使用されます。クエリでインデックスを使用するための最も重要な条件は、クエリ条件でインデックス キーを使用することです。複数列インデックスの場合、インデックスは複数列キーの左端のプレフィックスが使用されている場合にのみ使用できます。クエリ条件を指定しないと、インデックスを使用できません。

3. インデックスの使用状況を確認します。 Handler_read_key の値は、行のインデックスが作成された回数を表します。値が低いということは、インデックスが頻繁に使用されていないことを意味します。 Handler_read_rnd_next の値が高い場合は、クエリが非効率的に実行されており、それを修正するためにインデックスを作成する必要があることを意味します。 show status like 'Handler_read%';show status like 'Handler_read%';

两个简单实用的优化方法

• 定期分析表：ANALYZE TABLE, CHECK TABLE, CHECKSUM TABLE

• 使用OPTIMIZE table;
  

从客户端（代码端）角度优化

1. 使用持久的连接数据库以避免连接开销。代码中我们一般使用连接池

2. 检查所有的插叙确实使用了必要的索引。

3. 避免在频繁更新的表上执行复杂的select查询，以避免与锁表有关的由于读，写冲突发生的问题。

4. 充分利用默认值，只有插入值不同于默认值才明确插入值。减少mysql需要做的语法分析从而提高插入速度。

5. 读写分离提高性能

6. 表字段尽量不用自增长变量，防止高并发情况下该字段自增影响效率，推荐通过应用实现字段的自增。

12章: 优化数据库对象

优化表的数据类型：PROCEDURE ANALYZE()对当前表类型的判断提出优化建议。实际可以通过统计信息结合应用实际优化。

通过拆分，提高表的访问效率：这里拆分主要是针对Myisam类型的表。

• 纵向拆分：按照应用访问的频度，将表中经常访问的字段和不经常访问的字段拆分成两个表，经常访问的字段尽量是定长的。

• 横向拆分：按照应用情况，有目的地将数据横向拆分成几个表或者通过分区分到多个分区中，这样可以有效避免Myisam表的读取和更新导致的锁问题。
  

逆规范化：规范化设计强调独立性，数据尽可能少冗余，更多冗余意味着占用更多物理空间，同事也对数据维护和一致性检查带来问题。适当冗余可以减少多表访问，查询效率明显提高，这种情况可以考虑适当通过冗余提高效率。

使用冗余统计表：使用create temporary table做统计分析

选择更合适的表类型：1.如果应用出现比较严重的锁冲突，请考虑是否刻意更改存储引擎到InnoDB,行锁机制可以有效减少锁冲突出现。2.如果应用查询操作很多，且对事务完整性要求不严格，可以考虑使用Myisam。

13章：锁问题

获取锁的等待情况：table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺。检查Innode_row_lock分析行锁的争夺情况。

14章：优化Mysql Server

查看Mysql Server当前参数

1. 查看服务器参数默认值：mysqld --verbose --help

🎜🎜2つのシンプルで実用的な最適化方法🎜🎜🎜🎜🎜通常の分析テーブル: ANALYZE TABLE、CHECK TABLE、CHECKSUM TABLE🎜🎜🎜 🎜 OPTIMIZE テーブルを使用します🎜🎜🎜🎜🎜🎜クライアント (コード側) の観点から最適化します🎜🎜🎜🎜🎜永続的な 接続のオーバーヘッドを避けるために、データベース 🎜 に接続します。コードでは通常、接続プール 🎜🎜🎜🎜 を使用して、すべての挿入が実際に必要なインデックスを使用しているかどうかを確認します。 🎜🎜🎜🎜読み取りと書き込みの競合によるテーブル ロックに関連する問題を回避するために、頻繁に更新されるテーブルに対して複雑な選択クエリを実行しないでください。 🎜🎜🎜🎜 デフォルト値を最大限に活用し、デフォルト値と異なる場合にのみ値を明示的に挿入します。挿入速度を向上させるために MySQL が行う必要がある構文分析を減らします。 🎜🎜🎜🎜読み取りと書き込みを分離するとパフォーマンスが向上します🎜🎜🎜🎜回避すべき変数🎜 同時実行性が高い状況では、このフィールドの自動インクリメントは効率に影響します。アプリケーションを通じてフィールドの自動インクリメントを実装することをお勧めします。 🎜🎜🎜🎜第 12 章: データベースの最適化Object 🎜🎜🎜🎜🎜テーブルのデータ型を最適化します: PROCEDURE ANALYZE() 現在のテーブル型を判断するための最適化提案を提供します。実際には、統計情報を実際の最適化と組み合わせて使用​​できます。 🎜🎜🎜分割によるテーブルアクセス効率の向上: ここでの分割は主にMyisamタイプのテーブルに対して行われます。 🎜🎜🎜🎜垂直分割: アプリケーションのアクセス頻度に応じて、テーブル内の頻繁にアクセスされるフィールドとあまりアクセスされないフィールドを 2 つのテーブルに分割します。頻繁にアクセスされるフィールドは可能な限り固定長にする必要があります。 🎜🎜🎜🎜水平分割: アプリケーションの状況に応じて、データは意図的に複数のテーブルに水平に分割されるか、パーティションを介して複数のパーティションに分割されます。これにより、Myisam テーブルの読み取りと更新によって引き起こされるロックの問題を効果的に回避できます。 🎜🎜🎜🎜🎜非正規化: 正規化された設計では独立性が重視され、データの冗長性ができるだけ少なくなるようにする必要があり、冗長性が高くなると、より多くの物理スペースが必要になり、データの保守と整合性チェックにも問題が生じます。適切な冗長性により、複数テーブルへのアクセスが削減され、クエリの効率が大幅に向上します。この場合、効率を向上させるために適切な冗長性を検討できます。 🎜🎜冗長な統計テーブルを使用します: 統計分析には 一時テーブルの作成 を使用します🎜🎜🎜より適切なテーブル タイプを選択してください: 1. アプリケーションに深刻なロック競合がある場合は、ストレージを意図的に変更するかどうかを検討してください。 InnoDB では、行ロック メカニズムにより、ロック競合の発生を効果的に減らすことができます。 2. アプリケーションに多くのクエリ操作があり、トランザクションの整合性に関する厳密な要件がない場合は、Myisam の使用を検討できます。 🎜🎜🎜🎜第 13 章: ロックの問題🎜🎜🎜🎜ロック待機ステータスを取得: table_locks_waited および table_locks_immediate ステータス変数を使用して、システム上のテーブル ロック競合を分析します。 Innode_row_lock をチェックして、行ロックの競合を分析します。 🎜🎜🎜🎜第 14 章: Mysql サーバーの最適化🎜🎜🎜🎜Mysql Server の現在のパラメーターを表示します🎜🎜🎜🎜サーバー パラメーターのデフォルト値を表示します。 mysqld -- 詳細 --help🎜

実際のサーバーパラメータ値を表示します: shell> mysqladmin 変数または mysql> SHOW VARIABLESshell> mysqladmin variables or mysql> SHOW VARIABLES

查看服务器运行状态值：mysqladmin extended-status or mysql>SHOW STATUS

サーバーの実行ステータス値を表示します: mysqladmin extend-status または mysql>SHOW STATUS<p></p> <ol class=" list-paddingleft-2"> <li>Mysql のパフォーマンスに影響する重要なパラメータ<p><a href="http://www.php.cn/php/php-tp-caches.html" target="_blank"></a></p>key_buffer_size: key</li>cache<li><p></p></li> <li>table_cache: データベースで開かれているキャッシュの数<p></p> </li> <li>innode_buffer_pool_size: Inno DB データとインデックスをキャッシュするメモリ バッファー サイズ<p></p> </li> </ol>innodb_flush_log_at_trx_commit: 1 に設定することをお勧めします。各トランザクションがコミットされると、ログ バッファーがログ ファイルに書き込まれ、ログ ファイルはディスク操作によって更新されます。 <p><span style="color: #ff0000"><strong></strong></span><br>第 15 章: I/O の問題</p> <p></p> <ol class=" list-paddingleft-2"> <li>ディスク検索はパフォーマンスの大きなボトルネックです。 <p></p> </li> <li>ディスクアレイまたは仮想ファイルボリュームを使用してI/Oを分散する<p><br></p> </li>シンボリックリンクを使用してI/Oを分散する</ol> <p><span style="color: #ff0000"><strong></strong></span><br>第16章: アプリケーションの最適化</p> <ol class=" list-paddingleft-2"> <li> <p></p> </li> <li>接続プーリングを使用する: 設定するup 接続コストが比較的高いため、接続プールを確立することでアクセスパフォーマンスを向上させることができます。 <p></p> </li> <li>Mysql へのアクセスを減らす: 1. 同意データを繰り返し取得しないようにします。 2 mysql クエリ キャッシュ<p></p> </li> <li>を使用して、キャッシュ層<p><a href="http://www.php.cn/java/java-jiagou2.html" target="_blank"></a></p>の負荷分散を増やします。 1. mysql を使用して、クエリ操作のコピーとオフロードを行います。 2 分散データベース</li>アーキテクチャ</ol> <p><span style="color: #ff0000"><strong></strong></span></p>の概要🎜🎜🎜

以上がMySQL を簡単に学ぶ (コレクション)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。