redcloud @ ウィキ
redcloud @ ウィキ

MySQL

目次

前提条件

CentOS5.4上に構築する

MySQL5.0.67-tritonn1.0.12

インストール

mysqlユーザの作成 

# groupadd mysql
# useradd -g mysql -s /sbin/nologin mysql

ncurses-develパッケージのインストール 

# yum -y install ncurses-devel

mecab本体＋mecab辞書インストール

  • mecab本体 
    $ wget http://downloads.sourceforge.net/project/mecab/mecab/0.98/mecab-0.98.tar.gz?use_mirror=jaist
$ tar zxvf mecab-0.98.tar.gz
$ cd mecab-0.98
$ ./configure --prefix=/usr/local --with-charset=utf8
$ make
$ sudo make install
  • mecab辞書 
    $ wget http://downloads.sourceforge.net/project/mecab/mecab-ipadic/2.7.0-20070801/mecab-ipadic-2.7.0-20070801.tar.gz?use_mirror=jaist
$ tar zxvf mecab-ipadic-2.7.0-20070801.tar.gz
$ cd mecab-ipadic-2.7.0-20070801
$ ./configure --prefix=/usr/local --with-mecab-config=/usr/local/bin/mecab-config --with-charset=utf8
$ make
$ sudo make install

sennaインストール 

$ wget http://osdn.dl.sourceforge.jp/senna/33763/senna-1.1.4.tar.gz
$ tar zxvf senna-1.1.4.tar.gz
$ cd senna-1.1.4
$ ./configure --prefix=/usr/local --with-mecab-config=/usr/local/bin/mecab-config
  • nkfc.cのmakeにかなり時間が掛かる（場合によってはメモリallocateでエラーとなってしまう事もある）ので回避策 
    $ cd lib
$ ../libtool --tag=CC --mode=compile gcc -I. -I.. -Wall -O0 -fno-strict-aliasing -g -MT nfkc.lo -MD -MP -MF .deps/nfkc.Plo -c -o nfkc.lo nfkc.c

$ make
$ sudo make install

mysql+tritonn本体インストール 

$ wget http://osdn.dl.sourceforge.jp/tritonn/36449/tritonn-1.0.12-mysql-5.0.67.tar.gz
$ tar zxvf tritonn-1.0.12-mysql-5.0.67.tar.gz
$ cd tritonn-1.0.12-mysql-5.0.67
$ ./configure --prefix=/usr/local --with-charset=utf8 --with-extra-charsets=all --with-mysqld-user=mysql --with-readline --with-innodb --enable-local-infile --with-senna=/usr/local --with-mecab=/usr/local
$ make
$ sudo make install

設定

初期設定 

$ cd /usr/local/bin/
$ sudo ./mysql_install_db --user=mysql
$ sudo cp -p /usr/local/share/mysql/my-small.cnf /usr/local/etc/my.cnf
$ cd /usr/local
$ sudo chown -R mysql.mysql var

起動・停止確認 

$ cd /usr/local/bin/
$ sudo ./mysqld_safe --user=mysql &
$ sudo ./mysqladmin -u root shutdown

my.cnf修正 

$ cd /usr/local/etc/
$ sudo vi my.cnf
[mysqld]
skip-character-set-client-handshake
log
log-warnings
log-slow-queries=mysql-slow-queries
long-query-time=2
key_buffer = 64M←DB専用サーバなら全メモリの30%～40%が目安、とりあえず10%弱程度
table_cache = 128←DB専用サーバなら全メモリの256が目安
max_connections = 100←運用サイトによりけりだが、300ぐらいが目安？
thread_cache = 100←max_connectionsと同値が目安
max_allowed_packet  =  1M←レコードサイズの大きいテーブルを使う場合などは8Mぐらいまで大きくしてもいいかも
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/var/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/var/
innodb_log_arch_dir = /usr/local/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 64M←DB専用サーバなら全メモリの70%が目安、とりあえず20%弱程度
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 16M←innodb_buffer_pool_sizeの25%以上が目安
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1←よほどの事がない限り1で(特にミッションクリティカルなシステムは)
innodb_lock_wait_timeout = 50

my.cnfが適用できているか確認 

$ cd /usr/local/bin/
$ ./my_print_defaults mysqld

起動スクリプト作成、自動起動設定（サービス化） 

$ cd <tar展開先>
$ sudo cp -p support-files/mysql.server /etc/init.d/mysqld
# cd /etc/init.d
# chown root.root mysqld
# chmod 755 mysqld
# chkconfig --add mysqld
# service mysqld start
# service mysqld stop

セキュリティ強化 

# service mysqld start
$ cd /usr/local/bin/
$ sudo ./mysqladmin -u root password proot
$ ./mysql -u root -p

mysql>
  • 匿名ユーザ削除 
    mysql> delete from mysql.user where user = '';
  • testデータベース削除 
    mysql> drop database test;

DB動作確認

DB作成 

mysql> create database apldb;

ユーザ作成＆権限付与 

mysql> grant all on apldb.* to apldbmaster@localhost identified by 'papldbmaster' with grant option;
mysql> grant select,update,insert,delete on apldb.* to apldbuser@localhost identified by 'papldbuser';
mysql> grant select,update,insert,delete on apldb.* to apldbuser@'192.168.1.%' identified by 'papldbuser';

作成したユーザでのログイン確認 

$ mysql -u apldbmaster -p apldb
mysql>

テーブル作成 

mysql> CREATE TABLE t_sample (
   ->     userid                   CHAR(8)                       NOT NULL
   -> ,   username                 VARCHAR(20)                   NOT NULL
   -> ,   telno                    CHAR(13)
   -> ,   postno                   CHAR(8)
   -> ,   mailaddress              VARCHAR(100)
   -> ,   pref                     CHAR(2)
   -> ,   sex                      CHAR(2)
   -> ,   passwd                   CHAR(15)                      NOT NULL
   -> ,   entry_date               DATETIME
   -> ,   update_date              DATETIME
   -> ,   PRIMARY KEY (userid)
   ->  ) ENGINE=InnoDB DEFAULT CHARSET utf8
   -> ;

日本語を含むデータをコマンドラインから投入、閲覧 

mysql> INSERT INTO t_sample (userid,username,telno,postno,mailaddress,pref,sex,passwd,entry_date,update_date)
   -> VALUES ('00000001','あいう　太郎','03-0000-0000','000-1111','hoge@hoge.com','01','01','11111111',NOW(),NOW());
mysql> SELECT * FROM t_sample;
+----------+--------------------+--------------+----------+---------------+------+------+----------+---------------------+---------------------+
| userid   | username           | telno        | postno   | mailaddress   | pref | sex  | passwd   | entry_date          | update_date         |
+----------+--------------------+--------------+----------+---------------+------+------+----------+---------------------+---------------------+
| 00000001 | あいう　太郎       | 03-0000-0000 | 000-1111 | hoge@hoge.com | 01   | 01   | 11111111 | 2008-05-13 23:59:33 | 2008-05-13 23:59:33 |
+----------+--------------------+--------------+----------+---------------+------+------+----------+---------------------+---------------------+
1 row in set (0.00 sec)

全文検索テスト 

mysql> CREATE TABLE t_sample2 (
   ->     userid                   CHAR(8)                       NOT NULL
   -> ,   attr                     VARCHAR(20)                   NOT NULL
   -> ,   etc                      TEXT
   -> ,   PRIMARY KEY (userid)
   ->  ) ENGINE=MyISAM DEFAULT CHARSET utf8
   -> ;
mysql> CREATE FULLTEXT INDEX t_sample2_ftidx1 ON t_sample2 (attr,etc);
mysql> INSERT INTO t_sample2 (userid,attr,etc) VALUES ('00000001','ほげほげですが何か？','ほげほげと呼ばれてますが？');
mysql> SELECT * FROM t_sample2 WHERE MATCH(attr,etc) AGAINST('何か');
+----------+-----------------------------+-------------------------------------------------------------------------------+
| userid   | attr                        | etc                                                                           |
+----------+-----------------------------+-------------------------------------------------------------------------------+
| 00000001 | ほげほげですが何か？        | ほげほげと呼ばれてますが？                                                    |
+----------+-----------------------------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Tips

Ver 5系

有名なお言葉 

MySQLでは、１つのクエリを実行するとき、１つのテーブルにつき１つのインデックスしか使用できない

最適化TIPS

  • インデックス使わないパターン
    • LIKEがワイルドカードで始まる時
    • WHEREとORDER BYのフィールドが違う時にはどちらかしか使われない
    • カラムとカラムをORで検索する場合は複合インデックスが無効になる
      • 複合インデックスが有効 
        WHERE col1=1;
WHERE col1 AND col2=1;
WHERE col1 AND (col2=1 OR col2=2);
      • 複合インデックスが無効 
        WHERE col1=1 OR col2=1;   //OR条件
WHERE col2=1 AND col1=1;  //複合インデックスのカラム順定義と異なる
WHERE col2=1;             //複合インデックスの一部
WHERE col2=1 AND col3=1;  //複合インデックス外のカラムを含む
  • ソート(ORDER BY)関連
    • ソートする場合はインデックスがあるフィールドを「ORDER BY」において指定
    • グループ化（「GROUP BY」）する際にソートする必要がない場合でも「ORDER BY NULL」と指定。
    • 「ORDER BY」がある場合、「DISTINCT」を使うと遅くなる
  • ORDER BYでインデックスを使用できないパターン
    • 複数のキーに対してORDER BYを実行する場合 
      SELECT * FROM t1 ORDER BY key1, key2;
    • 連続しないキー部分に対してORDER BYを実行する場合 
      SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
    • ASCとDESCが混在している場合 
      SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
    • 行の取り出しに使用されるキーが ORDER BYの実行に使用されるキーと異なる場合 
      SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
    • ORDER BYで多くのテーブルとカラムを結合していて、それら全てがレコードの取り出しに使用される最初の非 const テーブルではない場合（これは EXPLAINで出力される最初のテーブルで、かつ、constメソッドを使用していないテーブル）
  • SELECT文の最適化
    • 可能な限りSQL文は後から一括して実行
    • LEFT JOIN, STRAIGHT JOINとUSINGの組み合わせはWHEREより早い
    • WHERE field INはかなり早い
    • WHEREが使えるならHAVINGは使うな(HAVINGはindex使わず)
    • SELECT等においてフィールドを呼び出す際、インデックスがあるものを優先して指定
    • WHERE節で不必要な()を使わない
    • WHERE節で「LIKE」の代わりに「REGEXP」や「in」が使えないか検討
    • SELECT等においてフィールドを呼び出す際、不要なフィールドは呼び出さない
    • LIMITを使い、呼び出すレコード数を制限
    • テーブルの結合はWHEREではなく「LEFT JOIN」を使用する
  • 他
    • マルチプル「INSERT INTO」を利用。（※後から一括してSQL文を実行）
    • UPDATEは遅い。一度「TRUNCATE TABLE」でテーブルを空にした後に更新されたデータを一気にマルチプル「INSERT INTO」することを検討
    • DELETEは遅い。「TRUNCATE TABLE」を検討
    • 大量のレコードをDELETEした後は「OPTIMIZE TABLE」でテーブルを最適化
    • 定期的に「ANALYZE TABLE」を実行
  • ハマりどころ
    • UNION結合時、UNION内部クエリでORDER BYしても結果には反映されない。UNION外部でORDER BYする（＝filesort発生）か、内部クエリそれぞれにLIMIT句を書いて件数を絞ると回避できる
  • JOINは極力避ける
    • JOINすると内部で複数テーブルの積に相当する一時テーブルを生成し、メモリを圧迫する
  • インデックスが効かないORDER BY
    • filesort発生（ディスクアクセス）、重い処理となる
「MySQL」をウィキ内検索
最終更新：2010年01月16日 15:26