PHP での MySQL データベース最適化戦略の詳細な説明

この記事では、PHP データベース プログラミングのための MySQL 最適化戦略について簡単に説明します。参考のために皆さんと共有してください。詳細は次のとおりです:

数日前、PHP のボトルネックは多くの場合 PHP 自体ではなく、データベースにあるという記事を目にしました。 PHP 開発では、データの追加、削除、変更、チェックが中心であることは誰もが知っています。 PHP の動作効率を向上させるには、プログラマは明確なロジックで効率の高いコードを作成するだけでなく、クエリ ステートメントを最適化できる必要があります。データベースの読み取りおよび書き込み速度についてはどうすることもできませんが、いくつかのデータベース クラス拡張機能や、memcache、mongodb、redis などのデータ ストレージ サーバーの助けを借りて、PHP はより高速なアクセス速度も実現できるため、理解して学習してください。これらの拡張機能も非常に必要です。この記事ではまず、MySQL の一般的な最適化戦略について説明します。

MySQL のいくつかのヒント

1. SQL ステートメント内のキーワードは大文字で記述するのが最適です。第 2 に、SQL ステートメントが実行されるときに、キーワードと操作オブジェクトを区別するのが簡単です。大文字を手動で記述すると、クエリの効率が (わずかではありますが) 向上します。

2. データベース内のデータ行を追加または削除すると、データ ID が大きすぎます。
ALTER TABLE tablename AUTO_INCREMENT=N を使用して、N から始まる自動インクリメント ID を作成します。 3. int 型に
ZEROFILL 属性を追加して、データに 0 を自動的に追加します。 4. 大量のデータをインポートする場合は、最初にインデックスを削除し、データを挿入してからインデックスを追加することをお勧めします。そうしないと、MySQL はインデックスの更新に多くの時間を費やします。
5. データベースを作成して SQL ステートメントを記述する場合、IDE で .sql という接尾辞が付いたファイルを作成できるため、IDE は SQL 構文を認識し、記述が容易になります。さらに重要なのは、データベースが失われた場合でも、このファイルを見つけて、現在のディレクトリで
/path/mysql -uusername -ppassword データベース名 < filename.sql を使用して、ファイル全体の SQL ステートメントを実行できることです (注 - uおよび -p の直後にユーザー名とパスワードが続きます (スペースは含まれません)。

データベース設計の最適化

1. データベース設計は 3 番目のパラダイムに準拠しており、クエリの便宜のために特定のデータの冗長性を持たせることができます。

2. データ型の優先順位を int > date, time > char > varchar > blob に置き換えることを検討できます。 int 型で保存するには ip2long() 関数を使用します。

3. char(n) 型の場合、データが完成したときに n の値をできるだけ小さく保つようにします。

4. テーブルを作成するときに、partition コマンドを使用すると、クエリ効率が大幅に向上します。その中で、RANGE は最も一般的に使用されるパーティション タイプです。 :

CREATE TABLE tablename{
}ENGINE innodb/myisam CHARSET utf8 //选择数据库引擎和编码
PARTITION BY RANGE/LIST(column),//按范围和预定义列表进行分区
PARTITION partname VALUES LESS THAN /IN(n),//命名分区并详细限定分区的范围

5 . データベースエンジンを選択するときは、innodb と myisam の違いに注意してください。

ストレージ構造: MyISAM はディスク上の 3 つのファイルに保存されます。 InnoDB のすべてのテーブルは同じデータ ファイル (通常は 2 GB) に保存されます。 トランザクション サポート: MyISAM はトランザクション サポートを提供しません。 InnoDB はトランザクション サポートを提供します。 テーブル ロックの違い: MyISAM はテーブル レベルのロックのみをサポートします。 InnoDB はトランザクションと行レベルのロックをサポートします。

全文インデックス: MyISAM は FULLTEXT タイプの全文インデックスをサポートしています (中国語には適用されないため、Sphinx 全文インデックス エンジンを使用する必要があります)。 InnoDB はそれをサポートしていません。

テーブル内の特定の行数: MyISAM はテーブル内の合計行数を保存し、count(*) のクエリは非常に高速です。 InnoDB はテーブル内の行の合計数を保存しないため、再計算する必要があります。
外部キー: MyISAM ではサポートされていません。 InnoDB は



インデックスの最適化

1 をサポートしています。Innodb はクラスター化インデックスである必要があります。指定されていない場合、エンジンは自動的に非表示の主キーを生成し、主インデックスを生成します。インデックスは主キーの物理アドレスに格納されます。データは主キーによって格納されます。インデックスを使用するたびに、まず主インデックスを検索し、次に主キーの下のデータを検索する必要があります。 利点は、主キーによる検索が非常に高速であることです。欠点は、最初にセカンダリ インデックスを通じてプライマリ インデックスを見つける必要があるため、セカンダリ インデックスが遅くなるということです (セカンダリ インデックスはプライマリ インデックスの場所です)。 .)、プライマリ インデックスを通じてデータを検索します。また、主キーが不規則だと、新しい値を挿入する際に多くのデータブロックを移動する必要があり、効率に影響するため、主キーには定期的に増加するint型を使用するようにしてください。また、データは主キーの直後に配置されるため、データ内に特に大量のデータを含むカラム (テキスト/ブロブ) がある場合、InnoDB はクエリ中に多くのデータ ブロックをスキップし、これも速度低下の原因となります。

2. myisam のインデックスの各インデックスは同じで、ディスク上の各行のアドレスを指します。これらはすべて軽量のポインター データです。欠点は、各インデックスが主キーを介して確立されないことと、クエリがクラスター化インデックスで主キーを検索するほど高速ではないことです。ただし、アドレスが保存されているため、新しい値を挿入すると比較の側面が移動して変更されます。

3. 複数条件のクエリを実行するとき、複数の条件のインデックスを個別に作成するとき、SQL クエリを実行するとき、MySQL は使用するインデックスのみを選択します。そのため、複数条件のクエリが必要な場合は、結合インデックスが使用されます。データの冗長性が生じる場合でも、確立する必要があります。

联合索引的BTREE建立方法：对第一个条件建立索引，在第一个索引的BTREE区域对第二个条件建立索引，以此类推，所以，在使用索引时，不用第一个条件用第二个条件也不会用到联合索引。使用索引时要条件要有顺序，有序列的使用。

4、索引长度对查询也有很大影响，我们应该尽量建立短的索引长度，我们可以使用查询列

SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename

来测试对column列建立索引时选取不同的长度，索引的覆盖率有多大，我们选择一下接近饱和的n个长度来建立索引
ALTER TABLE tablename ADD INDEX (column(n)); 来对某一列的前n个字符建立索引。若前n个字符相同，我们甚至可以对字符串进行反转存储，然后建立索引。

5、对于经常修改导致的索引碎片的维护方式：ALTER TABLE tablename ENGINE oldengine;即再次应用一下表存储引擎，使其自动维护；也可以用 OPTIMIZE tablename 命令来进行维护。

数据查询方面优化

数据库操作尽量少查询，有查询时尽量不在数据库层面上进行数据操作，而是返回到PHP脚本中操作数据，减轻数据库压力。

一旦发现有数据库性能问题，要及时解决，一般用慢查询日志记录查询很"慢"的语句，用EXPLAIN分析查询和索引使用情况，用PROFILE分析语句执行时的具体资源消耗。

慢查询日志：

1、在my.ini或my.cnf的[mysqld]下添加

slow_query_log_file=/path //设置日志存储路径
long_query_time=n //设置如果语句执行时间达到n秒，就会被记录下来

2、然后在MySQL里设置SET slow_query_log='ON'来开启慢查询。

3、记录下日志后，我们用/bin/目录下的mysqldumpslow filename来查看日志，其常用参数如下:

-g pattern 使用正则表达式
-t n返回前n条数据
-s c/t/l/r 以记录次数/时间/查询时间/返回记录数来排序

EXPLAIN语句

使用方法，在要执行的查询语句前面加EXPLAIN

EXPLAIN SELECT * FROM user;

得到形如下图的结果：

下面是对每一项的解释：

id 查询语句的id，简单查询无意义，多重查询时可以看出执行查询的顺序
select-type 执行的查询语句的类型，对应多重查询，有simple/primary/union等。
tabel 查询语句查询的数据表
type 获得数据的类型 常见的类型效率从高到低为 null>const>eq_ref>ref>range>index>all
possible-keys:可能使用到的索引
key 使用到的索引
key_len索引长度
ref 使用哪个列与索引一起从表中选择。
rows 查找到数据要扫描的大概行数，可看出索引的优劣
extra 常见的有
using filesort 查询到数据后进行文件排序，较慢，需要优化索引
using where 读取整行数据后进行判断过滤，是否符合where条件
using index 索引覆盖，即在牵引中已经有这存储了目标数据，直接读取索引，很快。

PROFILE

用SELECT @@frofiling来查看PROFILE的开启状态。
如果未开启，用SET profiling=1来开启。
开启之后，再执行查询语句，MySQL会自动记录profile信息。
应用show profiles查看所有的sql信息，结果为 Query_ID Duration Query三列结果，分别是查询ID，用时和所用的sql语句。
我们可以使用

SHOW PFROFILE [type[,type]][FOR QUREY Query_ID][Limit rwo_count [OFFSET offset]]

type常见有ALL(全部) BLOCK IO(显示IO相关开销) CPU(CPU开销) MEMORY(内存开销)等

大型存储方面优化

数据库主从复制和读写分离

1、master将改变记录到二进制日志中，slave将master的二进制拷贝到它的中继日志中，重新将数据返回到它自己的数据中，达到复制主服务器数据的目的。

主从复制可以用作：数据库负载均衡、数据库备份、读写分离等功能。

2、配置主服务器master

修改my.ini/my.conf

[mysqld]
log-bin=mysql-bin //启用二进制日志
server-id=102 //服务器唯一ID

3、配置从服务器slave

log-bin=mysql-bin //启用二进制日志
server-id=226 //服务器唯一ID

4、在主服务器上授权从服务器

GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identified by 'root'

5、在从服务器上使用

マスターを
master_host="masterip",
master_user="masteruser",
master_password="masterpasswd";

に変更します。 次に、start smile コマンドを使用してマスター/スレーブ レプリケーションを開始します。

設定を変更するたびにサーバーを再起動することを忘れないでください。その後、マスターサーバーとスレーブサーバーで show master/slave status を使用してマスター/スレーブのステータスを表示できます。

データベースでの読み取りと書き込みの分離を実現するには、mysql_proxy、atlas などの MySQL ミドルウェアに依存します。これらのミドルウェアをマスターサーバーとスレーブサーバーで読み取りと書き込みを分離するように構成することで、スレーブサーバーが読み取りを担当するようになり、マスターサーバーの負担が軽減されます。

データベースの共有

データベース内のデータテーブルのデータ量が非常に大きい場合、インデックス作成であってもキャッシュであっても、データベースをシャーディングして使用できるようにすることは大きな負担となります。複数のデータベース サーバーまたは複数のテーブル ストレージを使用して、クエリの負荷を軽減します。

この方法には、垂直セグメンテーション、水平セグメンテーション、および複合セグメンテーションが含まれます。

垂直セグメンテーション: 多数のデータテーブルがある場合、データベース内で密接に関連するテーブル (同じモジュール、頻繁に接続されクエリが実行されるなど) を分割し、異なるマスター/スレーブサーバーに配置します。

水平シャーディング: テーブルの数はそれほど多くないが、テーブル内のデータの量が非常に多い場合、クエリを高速化するために、ハッシュやその他のアルゴリズムを使用してデータテーブルをいくつかに分割して配置できます。クエリを高速化するために、別のサーバー上で実行します。水平シャーディングとデータテーブルパーティショニングの違いは、ストレージメディアの違いにあります。

結合分割: 多くの場合、データテーブルとテーブル内のデータ量が非常に大きいため、結合分割が必要になります。つまり、垂直方向と水平方向のテーブル分割が同時に実行され、データベースが分散行列に分割して格納されます。

これらのデータベース最適化手法はそれぞれ、記事を書くために使用できます。これらの手法を理解して覚えておくと、必要に応じて目的を持った選択と最適化を実行して、データベースの効率を高めることができます。

次に、一般的に使用される PHP データベース クラス拡張機能である memcache、redis、mongodb の基本的な使用シナリオと使用方法をさらにまとめます。

以上がPHP での MySQL データベース最適化戦略の詳細な説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。