ipからint型アクセス効率分析へ移行(2014-10-20)

背景の説明
mysqlはipをvarcharまたはcharからint型に変換し、「高性能mysql」ではintでできるだけint型を使用できることを紹介しています.一部のタイプのデータは、組み込みタイプと直接一致しません.ipv 4アドレスは、varchar(15)列を使用してIPアドレスを格納することが多い.しかし、これらは実際には32ビットの符号なし整数である.文字列ではありません.小数点で住所を4段に分ける表示方法は、読みやすいようにするためだけです.したがって、IPアドレスは符号なし番号で格納されるべきである.mysql提供inet_aton()とinet_ntoa()関数は,この2つの表現方法間で変換される.
mysqlではint型でできるだけint型を使うことができ、varcharは使いません.次に、ipがvarchar(15)であること、およびintに移行したことを記憶空間、アクセス効率からテストする.
その他の説明
独立した表領域は、図1のようにデフォルトで開きます.
図1
関連コード
テーブル文
説明:ip_strテーブルは主に文字列IP,ip_を格納するnumは、IPをデジタルに変換したIPを格納する.

/*    */
DROP TABLE IF EXISTS ip_str;
CREATE TABLE ip_str
(
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   ip_addr VARCHAR(15) NOT NULL ,
   PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS ip_num;
CREATE TABLE ip_num
(
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   ip_addr INT UNSIGNED NOT NULL,
   PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

ストアドプロシージャコード

  ip_num         ：
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `insert_ip_num`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_ip_num`(num INT)
BEGIN 
 DECLARE i INT;
 SET i=0;
 SELECT NOW();
 WHILE i

テストの比較
Insertコントラスト
それぞれ300万データを書き込み、書き込みには図1、図2のような時間がかかる.
ipは文字型に設定され、300万書き込みにかかる時間は53分34.92秒で、図2のように:
図2
ipは文字型に設定され、300万書き込みにかかる時間は48分29.76秒で、図3のように:
図3
selectコントラスト
2つのテーブルのデータはそれぞれ3千万に追加され、それぞれ
テーブル内のIPをクエリーします.
対ip_strテーブルのip_addrは「47.175.221.69’のレコードをクエリーし、図4のように11.91秒かかります.
図4
対ip_strテーブルのip_addrは「47.175.221.69’のレコードをクエリーし、図5のように7.54秒かかります.
図5
きおくくうかんひかく
ip_strテーブルとip_numテーブルが同じ3千万データ量の場合、ip_str 1.2 Gのディスク容量、ip_numは、図6のような840 Mのディスク領域を占有する
図6
結論
IPをint型ストレージに変換すると、varchar(15)に比べてアクセス効率が高くなり、本テストではデータベースインスタンスが純粋なテスト環境となります.本番環境で複雑さが増すと、アクセス効率の差は、この簡単なテスト結果よりも大きくなります.
注意:
テスト時にmysqlでset globalを実行する
innodb_flush_log_at_trx_commit =2 
」と書くスピードを上げることができます.