mysqlテーブル(クエリーの最適化)
一、まずなぜ表を分けたのか
1枚のデータが数百万に達すると、1回のクエリーにかかる時間が多くなり、連合クエリーがあれば、そこで死ぬ可能性があると思います.表分けの目的は、データベースの負担を減らし、クエリー時間を短縮することです.
個人的な経験に基づいて、mysqlがsqlを実行するプロセスは以下の通りです.
1,sqlを受信する;2,sqlをキューに入れる;3、sqlを実行する.4、実行結果を返します.この実行過程で最も時間がかかる場所はどこですか?第一に、キュー待ち時間、第二に、sqlの実行時間である.実はこの2つは同じことで、待つと同時にsqlが実行されているに違いない.だから私たちはsqlの実行時間を短縮します.
mysqlにはテーブルロックとローロックのメカニズムがありますが、なぜこのメカニズムが現れたのか、データの完全性を保証するためです.例を挙げてみましょう.もし2つのsqlが同じテーブルの同じデータを修正しなければならない場合、このときどうすればいいのでしょうか.2つのsqlが同時にこのデータを修正できるのではないでしょうか.この場合のmysqlの処理は、テーブルロック(myisamストレージエンジン)、ローロック(innodbストレージエンジン)であることが明らかである.テーブルのロックは、このテーブルを操作できないことを示しています.私がテーブルを操作するのを待たなければなりません.行ロックも同様で、他のsqlは私がこのデータを操作し終わってから、このデータを操作する必要があります.データが多すぎると、一度に実行する時間が長すぎると、待つ時間が長くなります.これも私たちが表を分ける理由です.
二、分表
1、mysqlクラスタを作ります.例えば、mysql cluster、mysql proxy、mysql replication、drdbなどを利用します.
mysqlクラスタ、ルートテーブルに何か関係があるのかと聞かれる人がいますか?実際の意味での分表ではありませんが、分表の役割を果たしています.クラスタを作る意味は何ですか.1つのデータベースの負担を軽減するために、はっきり言ってsqlキューの中のsqlの数を減らすことです.例えば、10個のsqlリクエストがあり、1つのデータベースサーバのキューに置くと、彼は長い間待たなければなりません.この10個のsqlリクエストを5個のデータベースサーバのキューに割り当てると、1つのデータベースサーバのキューの中に2個しかありません.これで待ち時間が大幅に短縮されたのではないでしょうか.これはもう明らかだ.だから私はそれを分表の範囲内に列挙して、私はいくつかmysqlのクラスタをしたことがあります:
linux mysql proxyのインストール、構成、および読み書き分離
mysql replication相互主従のインストールと構成、およびデータ同期
利点:拡張性が良く、複数の表分割後の複雑な操作(phpコード)がない.
欠点:単一テーブルのデータ量は変わらず、1回の操作にかかる時間はそれほど多く、ハードウェアのオーバーヘッドが大きい.
2,大きなデータ量が予想されて頻繁にアクセスするテーブルをいくつかのテーブルに分ける
このような見積りは大きくて悪くなくて、フォーラムの中で招待状を発表する表、時間が長くなってこの表はきっと大きくて、数十万、数百万はすべて可能です.チャットルームの中の情報表、数十人が一緒に一晩中話して、時間が長くなって、この表のデータはきっと大きいに違いありません.このような場合が多い.だから、このような予想できるビッグデータ量表は、事前にN個の表を分けて、このNがいくらなのか、実際の状況によって決まります.チャット情報テーブルの例:
私は事前にこのような時計を100個建てました.message_00,message_01,message_02……….message_98,message_99.それからユーザーIDによってこのユーザーのチャット情報をどの表に入れるかを判断して、あなたはhashの方式で得ることができて、余计な方式で得ることができて、方法は多くて、みんなは各人のを思っています.次にhashの方法でテーブル名を取得します.
説明すると、上記の方法は、user 18991というユーザーのメッセージがmessage_に記録されていることを示しています.10この表には、user 34523というユーザーのメッセージがmessage_に記録されています.13この表は、読み取るときは、それぞれの表から読み取ればよい.
利点:1枚のテーブルに数百万個のデータが表示されないようにし、1つのsqlの実行時間を短縮します.
欠点:1つのルールが確定すると、このルールを破るのは面倒になります.上の例で私が使っているhashアルゴリズムはcrc 32です.もし私が今このアルゴリズムを使いたくないなら、md 5を変更すると、同じユーザーのメッセージが異なるテーブルに格納され、データがめちゃくちゃになります.拡張性が悪い.
3,mergeストレージエンジンによるサブテーブル化
この方法は適切だと思います.事前に考えずに、データのクエリーが遅い場合があります.この时、既存のビッグデータ量表を分けるのが苦痛で、最も苦痛なことはコードを変更することです.プログラムの中のsql文はすでに书いてあるので、今1枚の表は数十枚の表に分けて、百枚以上の表に分けて、このようにsql文は书き直すのではないでしょうか.例を挙げると、私は挙子が大好きです.
mysql>show engines; mrg_を見つけますmyisamは実はmergeです.
上の操作から、何か見つけましたか?もし私が1枚のユーザーテーブルuserを持っていて、50 Wのデータがあるならば、今2枚のテーブルuser 1とuser 2に分解して、1枚のテーブル25 Wのデータ、INSERT INTO user 1(user 1.id、user 1.name、user 1.sex)SELECT(user.id、user.name、user.sex)FROM user where user.id <= 250000
INSERT INTO user2(user2.id,user2.name,user2.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id > 250000
このように私は成功して1枚のuser表を、2つの表に分けて、この时1つの问题があって、コードの中のsql文はどのようにして、以前は1枚の表で、今2枚の表になって、コードの変更はとても大きくて、このようにプログラマーにとても大きい仕事量を持ってきて、この点を解决する良い方法がありますか?方法は、以前のuserテーブルをバックアップして削除することです.上の操作でalluserテーブルを作成し、このalluserテーブルのテーブル名だけをuserに変更すればいいです.ただし、すべてのmysql操作が使えるわけではありません
a,alter tableを使用してmergeテーブルを他のテーブルタイプにすると,レイヤテーブルのマッピングが失われる.代わりに、最下位のmyisamテーブルからのローが交換済みテーブルにコピーされ、その後、新しいタイプが指定されます.
b、ネット上でreplaceが役に立たないと言っているのを見て、私は役に立つことを試してみました.めまいがする
c,mergeテーブルはテーブル全体でunique制約を維持できない.insertを実行すると、insert_methodオプションの値に応じて、最初のmyisamテーブルまたは最後のmyisamテーブルにデータが入ります.mysqlは、そのmyisamテーブルで一意のキー値が保持されていることを確認しますが、集合内のすべてのテーブルにまたがっているわけではありません.d,mergeテーブルを作成するとき,下位テーブルの存在と同じ機構があることを確認しなかった.mergeテーブルが使用される場合、mysqlは、マッピングされたテーブルごとに記録長が等しいかどうかをチェックするが、これは信頼性が低い.似たようなmyisamテーブルからmergeテーブルを作成しないと、奇妙な問題にぶつかる可能性があります.
cとdはネットで見ましたが、テストはありません.みんなやってみましょう.
利点:拡張性がよく、プログラムコードの変更が大きくありません.
欠点:この方法の効果は2つ目より少し悪い.
三、まとめて
上記の3つの方法は、実際に2つ、1つ目と2つ目を作ったことがあります.3つ目はやったことがないので、細かく話します.ははは.何をするにも度があり、度を超えると度が悪くなりすぎて、データベース・サーバ・クラスタを作ることができません.ハードウェアはお金を払って買う必要があります.また、ひたすら表を分けてはいけません.1000表を分けて、mysqlの記憶は結局ファイルの情勢でハードディスクに存在します.1枚の表は3つのファイルに対応しています.1000表は3000ファイルに対応しています.これで検索も遅くなります.私の提案は
方法1と方法2を組み合わせて表分けを行う
方法1と方法3を組み合わせて表分けを行う
私の2つの提案は状況によって異なるので、個人の状況によっては、方法1と方法3を組み合わせた方法を選ぶ人が多いと思います.
1枚のデータが数百万に達すると、1回のクエリーにかかる時間が多くなり、連合クエリーがあれば、そこで死ぬ可能性があると思います.表分けの目的は、データベースの負担を減らし、クエリー時間を短縮することです.
個人的な経験に基づいて、mysqlがsqlを実行するプロセスは以下の通りです.
1,sqlを受信する;2,sqlをキューに入れる;3、sqlを実行する.4、実行結果を返します.この実行過程で最も時間がかかる場所はどこですか?第一に、キュー待ち時間、第二に、sqlの実行時間である.実はこの2つは同じことで、待つと同時にsqlが実行されているに違いない.だから私たちはsqlの実行時間を短縮します.
mysqlにはテーブルロックとローロックのメカニズムがありますが、なぜこのメカニズムが現れたのか、データの完全性を保証するためです.例を挙げてみましょう.もし2つのsqlが同じテーブルの同じデータを修正しなければならない場合、このときどうすればいいのでしょうか.2つのsqlが同時にこのデータを修正できるのではないでしょうか.この場合のmysqlの処理は、テーブルロック(myisamストレージエンジン)、ローロック(innodbストレージエンジン)であることが明らかである.テーブルのロックは、このテーブルを操作できないことを示しています.私がテーブルを操作するのを待たなければなりません.行ロックも同様で、他のsqlは私がこのデータを操作し終わってから、このデータを操作する必要があります.データが多すぎると、一度に実行する時間が長すぎると、待つ時間が長くなります.これも私たちが表を分ける理由です.
二、分表
1、mysqlクラスタを作ります.例えば、mysql cluster、mysql proxy、mysql replication、drdbなどを利用します.
mysqlクラスタ、ルートテーブルに何か関係があるのかと聞かれる人がいますか?実際の意味での分表ではありませんが、分表の役割を果たしています.クラスタを作る意味は何ですか.1つのデータベースの負担を軽減するために、はっきり言ってsqlキューの中のsqlの数を減らすことです.例えば、10個のsqlリクエストがあり、1つのデータベースサーバのキューに置くと、彼は長い間待たなければなりません.この10個のsqlリクエストを5個のデータベースサーバのキューに割り当てると、1つのデータベースサーバのキューの中に2個しかありません.これで待ち時間が大幅に短縮されたのではないでしょうか.これはもう明らかだ.だから私はそれを分表の範囲内に列挙して、私はいくつかmysqlのクラスタをしたことがあります:
linux mysql proxyのインストール、構成、および読み書き分離
mysql replication相互主従のインストールと構成、およびデータ同期
利点:拡張性が良く、複数の表分割後の複雑な操作(phpコード)がない.
欠点:単一テーブルのデータ量は変わらず、1回の操作にかかる時間はそれほど多く、ハードウェアのオーバーヘッドが大きい.
2,大きなデータ量が予想されて頻繁にアクセスするテーブルをいくつかのテーブルに分ける
このような見積りは大きくて悪くなくて、フォーラムの中で招待状を発表する表、時間が長くなってこの表はきっと大きくて、数十万、数百万はすべて可能です.チャットルームの中の情報表、数十人が一緒に一晩中話して、時間が長くなって、この表のデータはきっと大きいに違いありません.このような場合が多い.だから、このような予想できるビッグデータ量表は、事前にN個の表を分けて、このNがいくらなのか、実際の状況によって決まります.チャット情報テーブルの例:
私は事前にこのような時計を100個建てました.message_00,message_01,message_02……….message_98,message_99.それからユーザーIDによってこのユーザーのチャット情報をどの表に入れるかを判断して、あなたはhashの方式で得ることができて、余计な方式で得ることができて、方法は多くて、みんなは各人のを思っています.次にhashの方法でテーブル名を取得します.
<?php
function get_hash_table($table,$userid) {
$str = crc32($userid);
if($str<0){
$hash = "0".substr(abs($str), 0, 1);
}else{
$hash = substr($str, 0, 2);
}
return $table."_".$hash;
}
echo get_hash_table('message','user18991'); // message_10
echo get_hash_table('message','user34523'); // message_13
?> 説明すると、上記の方法は、user 18991というユーザーのメッセージがmessage_に記録されていることを示しています.10この表には、user 34523というユーザーのメッセージがmessage_に記録されています.13この表は、読み取るときは、それぞれの表から読み取ればよい.
利点:1枚のテーブルに数百万個のデータが表示されないようにし、1つのsqlの実行時間を短縮します.
欠点:1つのルールが確定すると、このルールを破るのは面倒になります.上の例で私が使っているhashアルゴリズムはcrc 32です.もし私が今このアルゴリズムを使いたくないなら、md 5を変更すると、同じユーザーのメッセージが異なるテーブルに格納され、データがめちゃくちゃになります.拡張性が悪い.
3,mergeストレージエンジンによるサブテーブル化
この方法は適切だと思います.事前に考えずに、データのクエリーが遅い場合があります.この时、既存のビッグデータ量表を分けるのが苦痛で、最も苦痛なことはコードを変更することです.プログラムの中のsql文はすでに书いてあるので、今1枚の表は数十枚の表に分けて、百枚以上の表に分けて、このようにsql文は书き直すのではないでしょうか.例を挙げると、私は挙子が大好きです.
mysql>show engines; mrg_を見つけますmyisamは実はmergeです.
mysql> CREATE TABLE IF NOT EXISTS `user1` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `sex` int(1) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE IF NOT EXISTS `user2` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `sex` int(1) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO `user1` (`name`, `sex`) VALUES(' ', 0);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1);
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS `alluser` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `sex` int(1) NOT NULL DEFAULT '0',
-> INDEX(id)
-> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select id,name,sex from alluser;
+----+--------+-----+
| id | name | sex |
+----+--------+-----+
| 1 | | 0 |
| 1 | tank | 1 |
+----+--------+-----+
2 rows in set (0.00 sec)
mysql> INSERT INTO `alluser` (`name`, `sex`) VALUES('tank2', 0);
Query OK, 1 row affected (0.00 sec)
mysql> select id,name,sex from user2
-> ;
+----+-------+-----+
| id | name | sex |
+----+-------+-----+
| 1 | tank | 1 |
| 2 | tank2 | 0 |
+----+-------+-----+
2 rows in set (0.00 sec) 上の操作から、何か見つけましたか?もし私が1枚のユーザーテーブルuserを持っていて、50 Wのデータがあるならば、今2枚のテーブルuser 1とuser 2に分解して、1枚のテーブル25 Wのデータ、INSERT INTO user 1(user 1.id、user 1.name、user 1.sex)SELECT(user.id、user.name、user.sex)FROM user where user.id <= 250000
INSERT INTO user2(user2.id,user2.name,user2.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id > 250000
このように私は成功して1枚のuser表を、2つの表に分けて、この时1つの问题があって、コードの中のsql文はどのようにして、以前は1枚の表で、今2枚の表になって、コードの変更はとても大きくて、このようにプログラマーにとても大きい仕事量を持ってきて、この点を解决する良い方法がありますか?方法は、以前のuserテーブルをバックアップして削除することです.上の操作でalluserテーブルを作成し、このalluserテーブルのテーブル名だけをuserに変更すればいいです.ただし、すべてのmysql操作が使えるわけではありません
a,alter tableを使用してmergeテーブルを他のテーブルタイプにすると,レイヤテーブルのマッピングが失われる.代わりに、最下位のmyisamテーブルからのローが交換済みテーブルにコピーされ、その後、新しいタイプが指定されます.
b、ネット上でreplaceが役に立たないと言っているのを見て、私は役に立つことを試してみました.めまいがする
mysql> UPDATE alluser SET sex=REPLACE(sex, 0, 1) where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from alluser;
+----+--------+-----+
| id | name | sex |
+----+--------+-----+
| 1 | | 0 |
| 1 | tank | 1 |
| 2 | tank2 | 1 |
+----+--------+-----+
3 rows in set (0.00 sec) c,mergeテーブルはテーブル全体でunique制約を維持できない.insertを実行すると、insert_methodオプションの値に応じて、最初のmyisamテーブルまたは最後のmyisamテーブルにデータが入ります.mysqlは、そのmyisamテーブルで一意のキー値が保持されていることを確認しますが、集合内のすべてのテーブルにまたがっているわけではありません.d,mergeテーブルを作成するとき,下位テーブルの存在と同じ機構があることを確認しなかった.mergeテーブルが使用される場合、mysqlは、マッピングされたテーブルごとに記録長が等しいかどうかをチェックするが、これは信頼性が低い.似たようなmyisamテーブルからmergeテーブルを作成しないと、奇妙な問題にぶつかる可能性があります.
cとdはネットで見ましたが、テストはありません.みんなやってみましょう.
利点:拡張性がよく、プログラムコードの変更が大きくありません.
欠点:この方法の効果は2つ目より少し悪い.
三、まとめて
上記の3つの方法は、実際に2つ、1つ目と2つ目を作ったことがあります.3つ目はやったことがないので、細かく話します.ははは.何をするにも度があり、度を超えると度が悪くなりすぎて、データベース・サーバ・クラスタを作ることができません.ハードウェアはお金を払って買う必要があります.また、ひたすら表を分けてはいけません.1000表を分けて、mysqlの記憶は結局ファイルの情勢でハードディスクに存在します.1枚の表は3つのファイルに対応しています.1000表は3000ファイルに対応しています.これで検索も遅くなります.私の提案は
方法1と方法2を組み合わせて表分けを行う
方法1と方法3を組み合わせて表分けを行う
私の2つの提案は状況によって異なるので、個人の状況によっては、方法1と方法3を組み合わせた方法を選ぶ人が多いと思います.