InnoDB_Pluginについて

13.6. The InnoDB Storage Engineからの抜粋
At the 2008 MySQL User Conference, Innobase announced availability of an InnoDB Plugin for MySQL.
This plugin for MySQL exploits the “pluggable storage engine” architecture of MySQL.
The InnoDB Plugin is included in MySQL 5.5 releases as the built-in version of InnoDB.
The version of the InnoDB Plugin is 1.0.6 as of MySQL 5.5.1 and is considered of Release Candidate (RC) quality.

The InnoDB Plugin offers new features, improved performance and scalability, enhanced reliability
and new capabilities for flexibility and ease of use.
Among the features of the InnoDB Plugin are ….. 以下プラグイン概要….

————————————————————————-
① Fast index creation
② Table and index compression
③ File format management
④ New INFORMATION_SCHEMA tables
⑤ Capacity tuning
⑥ Multiple background I/O threads
⑦ Group commit.
————————————————————————-

詳細は以下のinnodb.comを参照
InnoDB Plugin 1.0 for MySQL 5.1 (Early Adopter Release) User’s Guide

先日、MYSQL5.5をインストールしたので、Innodb_pluginはディフォルトで有効になっている。
以下のコマンドでバージョンを確認する事が出来ます。


select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS
from information_schema.plugins;

SELECT @@innodb_version;

innodb_plugin_version

メモ: INFORMATION_SCHEMA.PLUGINSテーブルにはマイナーバージョン番号は表示されないようです。
Note that the PLUGIN_VERSION column in the table INFORMATION_SCHEMA.PLUGINS does not
display the third component of the version number, only the first and second components, as in 1.0.

その他留意事項
———————————————————————————————————-
1.5. Operational Restrictions
———————————————————————————————————-
Because the InnoDB Plugin introduces a new file format, with new on-disk data structures within
both the database and log files, there are important restrictions on the use of the plugin in typical user
environments. Specifically, you should pay special attention to the information presented here about
file format compatibility with respect to the following scenarios:

1) Downgrading from the InnoDB Plugin to the built-in InnoDB, or otherwise using different
versions of InnoDB with database files created by the InnoDB Plugin
2) Using mysqldump
3) Using MySQL replication
4) Using InnoDB Hot Backup

WARNING: Once you use the InnoDB Plugin on a set of database files, care must be taken to avoid
crashes and corruptions when using those files with an earlier version of InnoDB, as might happen by
opening the database with MySQL when the plugin is not installed. It is strongly recommended that
you use a “slow shutdown” (SET GLOBAL innodb_fast_shutdown=0) when stopping the MySQL server
when the InnoDB Plugin is enabled. This will ensure log files and other system information written by the
plugin will not cause problems when using a prior version of InnoDB.
11.3, “How to Downgrade”.

WARNING:If you dump a database containing compressed tables with mysqldump,
the dump file may contain CREATE TABLE commands that attempt to create compressed tables,
or those using ROW_FORMAT=DYNAMIC in the new database. Therefore, you should be sure
the new database is running the InnoDB Plugin, with the proper settings for innodb_file_format and innodb_file_per_table, if you want to have the tables re-created as they exist in the original database.
Typically, however, when the mysqldump file is loaded, MySQL and InnoDB will ignore
CREATE TABLE options they do not recognize, and the table(s) will be created in a format
used by the running server.

WARNING: If you use MySQL replication, you should be careful to ensure all slaves are configured
with the InnoDB Plugin, with the same settings for innodb_file_format and innodb_file_per_table.
If you do not do so, and you create tables that require the new “Barracuda” file format, replication
errors may occur. If a slave MySQL server is running the built-in InnoDB, it will ignore the
CREATE TABLE options to create a compressed table or one with ROW_FORMAT=DYNAMIC,
and create the table uncompressed, with ROW_FORMAT=COMPACT.

WARNING: The current version of InnoDB Hot Backup does not support the new “Barracuda” file
format. Using InnoDB Hot Backup Version 3 to backup databases in this format will cause
unpredictable behavior. A future version of InnoDB Hot Backup will support databases used
with the InnoDB Plugin. As an alternative, you may back up such databases with mysqldump.

———————————————————————————————————-

Innovative Technologiesfor Performance andData Protection

mysql> select Variable_name from
-> information_schema.GLOBAL_VARIABLES
-> where Variable_name like ‘innodb_%’;
+———————————+
| Variable_name |
+———————————+
| INNODB_VERSION |
| INNODB_LOCKS_UNSAFE_FOR_BINLOG |
| INNODB_BUFFER_POOL_SIZE |
| INNODB_CONCURRENCY_TICKETS |
| INNODB_OLD_BLOCKS_PCT |
| INNODB_LOG_BUFFER_SIZE |
| INNODB_MAX_PURGE_LAG |
| INNODB_DOUBLEWRITE |
| INNODB_IO_CAPACITY |
| INNODB_TABLE_LOCKS |
| INNODB_AUTOEXTEND_INCREMENT |
| INNODB_THREAD_SLEEP_DELAY |
| INNODB_REPLICATION_DELAY |
| INNODB_STATS_ON_METADATA |
| INNODB_ROLLBACK_ON_TIMEOUT |
| INNODB_CHANGE_BUFFERING |
| INNODB_FILE_FORMAT |
| INNODB_DATA_FILE_PATH |
| INNODB_STRICT_MODE |
| INNODB_MAX_DIRTY_PAGES_PCT |
| INNODB_AUTOINC_LOCK_MODE |
| INNODB_COMMIT_CONCURRENCY |
| INNODB_MIRRORED_LOG_GROUPS |
| INNODB_SUPPORT_XA |
| INNODB_SYNC_SPIN_LOOPS |
| INNODB_ADAPTIVE_FLUSHING |
| INNODB_ADAPTIVE_HASH_INDEX |
| INNODB_DATA_HOME_DIR |
| INNODB_READ_IO_THREADS |
| INNODB_WRITE_IO_THREADS |
| INNODB_FORCE_RECOVERY |
| INNODB_LOG_FILES_IN_GROUP |
| INNODB_OPEN_FILES |
| INNODB_FILE_FORMAT_CHECK |
| INNODB_READ_AHEAD_THRESHOLD |
| INNODB_LOG_GROUP_HOME_DIR |
| INNODB_FAST_SHUTDOWN |
| INNODB_THREAD_CONCURRENCY |
| INNODB_STATS_SAMPLE_PAGES |
| INNODB_FLUSH_LOG_AT_TRX_COMMIT |
| INNODB_FLUSH_METHOD |
| INNODB_CHECKSUMS |
| INNODB_LOG_FILE_SIZE |
| INNODB_SPIN_WAIT_DELAY |
| INNODB_FILE_PER_TABLE |
| INNODB_LOCK_WAIT_TIMEOUT |
| INNODB_OLD_BLOCKS_TIME |
| INNODB_ADDITIONAL_MEM_POOL_SIZE |
| INNODB_USE_SYS_MALLOC |
+———————————+
49 rows in set (0.00 sec)

mysql>

    新機能として

[innodb_file_format」
InnoDBのファイルフォーマットを指定可能
Antelope  従来のファイルフォーマット
Barracuda 圧縮機能サポートファイルフォーマット

innodb_file_format

検証
CREATE DATABASE `T_INNODB` /*!40100 DEFAULT CHARACTER SET utf8 */


CREATE TABLE `T_Antelope` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `T_Barracuda` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 DEFAULT CHARSET=utf8;

ROW_FORMAT

* REDUNDANT
* COMPACT
* DYNAMIC (Barracuda format & No Compress)
* COMPRESSED: (Barracuda format & Compress)

innodb_plugin_test

よく見てみると、上記QueryはWarrningが出ていてテーブルが圧縮されていない事が分かった。

オプションファイルに以下の設定を入れて再度、MYSQLを再起動して設定を反映させた。
innodb_file_format= Barracuda

innodb_file_format_bara

mysql> show variables like 'innodb_file%';

barracuda

ファイルフォーマットがBarracudaに変わったので再度テーブルを作成してみる。


CREATE TABLE `T_Antelope` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `T_Barracuda` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 DEFAULT CHARSET=utf8
/* BLOCKサイズを4に変更した */;


select TABLE_SCHEMA,TABLE_NAME,ENGINE,ROW_FORMAT
from information_schema.tables
where TABLE_SCHEMA = 'T_INNODB';

innodb_file_formatを変更した事でwarrningが消えた事を確認
barracuda_confirm

実際のファイルを確認してみるとファイルサイズが違う。
※ ブロックサイズを8で作成してもファイルサイズはBarracudaの方が小さい。

compare

information_schema.INNODB_CMPの変化の確認。
圧縮を行った回数(compress_ops), 圧縮が成功した回数(compress_ops_ok)
などを見て圧縮への対応を検討していく事が可能です。


CREATE TABLE `T_Antelope_Key` (
`number` int(11) unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(100) DEFAULT NULL,
PRIMARY KEY (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `T_Barracuda_Key` (
`number` int(11) unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(100) DEFAULT NULL,
PRIMARY KEY (`number`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 DEFAULT CHARSET=utf8;

select * from information_schema.INNODB_CMP;

innodb_cmp

【メモ:パフォーマンス検証の際は、以下の値を確認】
innodb_buffer_pool_size
innodb_flush_methodをO_DIRECT

参考サイト
——————————————
INNOBASE
InnoDB Plugin Change History
13.6. The InnoDB Storage Engine
InnoDB Plugin 1.0.4 – InnoDB史上極めて重要なリリース



SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE
FROM information_schema.tables
where TABLE_SCHEMA = 'information_schema';

mysql55_information_schema

■CHARACTER_SETS | MEMORY | 利用できる文字セットに関する情報
■COLLATIONS | MEMORY | 各文字セットの照合に関する情報
■COLLATION_CHARACTER_SET_APPLICABILITY | MEMORY | どの文字セットがどの照合に適用できるかを示します
■COLUMNS | MyISAM | テーブルのカラムに関する情報
■COLUMN_PRIVILEGES | MEMORY | カラムの権限に関する情報
■ENGINES | MEMORY | ストレージ エンジンに関する情報
■EVENTS | MyISAM | 計画したイベントに関する情報
■FILES | MEMORY | NDBディスクデータテーブルが保存されるファイルに関する情報
■GLOBAL_STATUS | MEMORY | サーバーのステータス変数に関する情報(GLOBAL)
■GLOBAL_VARIABLES | MEMORY | サーバーのステータス変数に関する情報(GLOBAL)
■KEY_COLUMN_USAGE | MEMORY | どのキーカラムに制約があるかを説明
■PARTITIONS | MyISAM | テーブルの分割に関する情報 (パーティショニング)
■PLUGINS | MyISAM | サーバーのプラグインに関する情報
■PROCESSLIST | MyISAM | 動作しているスレッドに関する情報
■PROFILING | MEMORY | プロファイリング情報(SHOW PROFILES/SHOW PROFILE)
■REFERENTIAL_CONSTRAINTS | MEMORY | 外部キーに関する情報
■ROUTINES | MyISAM | 保存されたルーチンに関する情報
■SCHEMATA | MEMORY | データベースに関する情報
■SCHEMA_PRIVILEGES | MEMORY | スキーマ(データベース)権限に関する情報
■SESSION_STATUS | MEMORY | サーバーのステータス変数に関する情報(SESSION)
■SESSION_VARIABLES | MEMORY | サーバーのステータス変数に関する情報(SESSION)
■STATISTICS | MEMORY | テーブル インデックスの情報
■TABLES | MEMORY | データベースのテーブルに関する情報
■TABLE_CONSTRAINTS | MEMORY | テーブルに制約があるか情報
■TABLE_PRIVILEGES | MEMORY | テーブル権限に関する情報
■TRIGGERS | MyISAM | トリガに関する情報
■USER_PRIVILEGES | MEMORY | グルーバル権限に関する情報
■VIEWS | MyISAM | データベースの表示に関する情報
■INNODB_CMP_RESET | MEMORY | InnoDB Plugin関連(圧縮テーブルに関する情報/統計)
■INNODB_TRX | MEMORY | InnoDB Plugin関連(実行中のトランザクション)
■INNODB_CMPMEM_RESET | MEMORY | InnoDB Plugin関連(Buffer pool内の圧縮されたPageに関する情報)
■INNODB_LOCK_WAITS | MEMORY | InnoDB Plugin関連(Blocking/Requestingトランザクション情報)
■INNODB_CMPMEM | MEMORY | InnoDB Plugin関連(Buffer pool内の圧縮されたPageに関する情報)
■INNODB_CMP | MEMORY | InnoDB Plugin関連(圧縮テーブルに関する情報/統計)
■INNODB_LOCKS | MEMORY | InnoDB Plugin関連(ロック競合が発生しているトランザクション)

INFORMATION_SCHEMA ON MYSQL5.5 (基本)
information_schema_55_basic

INFORMATION_SCHEMA ON MYSQL5.5 (INNODB_PLUGIN関連)
information_schema_55_innodb_p

参考サイト
————————————————————————
Chapter 6. InnoDB INFORMATION_SCHEMA tables


最近のMYSQLのリリースの方法が変わったのか。。。
5.4になるとJOINの処理が早くなると言われていたのでGAリリースを待ち望んでいたが、
MySQL 5.4のサイトへ行くと
5.4.3-betaで止まっている。にもかかわらず、MySQL 5.5がリリースされている。
しかもマイルストーンリリース。

【以下MYSQL5.5のサイトから抜粋】

■This brings me back to where I started the blog entry: the SELECT VERSION() statement,
which doesn’t say “-alpha” nor “-beta” but “-m2″ (where “m2″ stands for “second milestone”).

Getting you faster access to new features is a key idea behind the new milestones.
Milestones don’t map one-to-one on version numbers, so hence the first 5.5 release can be
the second milestone (with the first milestone being in the already community tested
5.4 tree,which won’t get any further updates
).

■Is the MySQL 5.5 quality good enough to merit your testing?
Yes, we think it is. Although MySQL Server 5.5-M2 isn’t production ready, we think it’s
suitable for testing and feedback by you, because we have significantly increased our
internal testing in order to avoid accepting unstable code into the milestone.
A sign of this is that we do have other new code written, i.e. functionality brewing but not
accepted into the milestone because it doesn’t fit our quality requirements at this point
in time.

STEP1) まずは何はともあれダウンロードしてみる

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.0-m2-linux-i686-icc-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/

STEP2) 展開してMYSQLを自分の運用パスに移動
①展開
[root@colinux src]# tar zxvf mysql-5.5.0-m2-linux-i686-icc-glibc23.tar.gz
②移動
[root@colinux src]# mv mysql-5.5.0-m2-linux-i686-icc-glibc23 /usr/local/

STEP3) MYSQLフォルダーのパーミッション設定

[root@colinux local]# chown -R mysql:mysql mysql-5.5.0-m2-linux-i686-icc-glibc23/
[root@colinux local]# chmod -R 755 mysql-5.5.0-m2-linux-i686-icc-glibc23/

mysql_55m

STEP4) 既存のMYSQLのアップグレードを開始(5.1.41-log —> 5.5.0-m2)

①既存のバージョン確認
5141-log

②データコピー前に既存のMYSQLサービスを停止
[root@colinux local]# /etc/init.d/mysql.server stop
Shutting down MySQL. SUCCESS!
[root@colinux local]#

③ データフォルダーをMYSQL5.5用にコピー
[root@colinux local]# cp -rp mysql/data mysql-5.5.0-m2-linux-i686-icc-glibc23/
[root@colinux local]#

④ シンボリックリンクを古いバージョンから新しいバージョンへ切り替え
[root@colinux local]#rm mysql
[root@colinux local]# ln -s mysql-5.5.0-m2-linux-i686-icc-glibc23/ mysql

symbol

※ バージョンに応じてオプションファイル(my.cnf)の変更もすべきだと思いますが、後で調整するのでここでは割愛してます。

⑤ MYSQLのサービスを再開
[root@colinux local]# /etc/init.d/mysql.server start
Starting MySQL…. SUCCESS!
[root@colinux local]#

⑥ MYSQLのシステムテーブルをアップグレード
[root@colinux bin]# pwd
/usr/local/mysql/bin
[root@colinux bin]#./mysql_upgrade -u root -p

mysql55_system

Running ‘mysql_fix_privilege_tables’…OK

STEP5) MYSQLのアップグレード状態を確認

mysql> select @@version;

version55

mysql> show tables from information_schema;
information_schema_55m2

————————————————————————————————————————-


select TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_TIME
from information_schema.tables
where TABLE_SCHEMA = 'information_schema'
Order by CREATE_TIME desc;

information_schema_55_created


MYSQL5.1.41がリリースされていたので、アップグレード
C.1.2. Changes in MySQL 5.1.41 (05 November 2009)

■InnoDB Plugin has been upgraded to version 1.0.5. This version is considered of
 Release Candidate (RC) quality.
■The InnoDB buffer pool is divided into two sublists: A new sublist containing blocks
 that are heavily used by queries, and an old sublist containing less-used blocks and from
 which candidates for eviction are taken.
 innodb_old_blocks_pct / innodb_old_blocks_time
■Important Change: Security Fix
Bug#32167, CVE-2008-2079 / Bug#47320, CVE-2009-4028 / Bug#46922
Partitioning: Bug#44059
Replication: Bug#48297 / Bug#48216 / Bug#47678 / Bug#47323 / Bug#47287/ Bug#46640
Bug#42829 / Bug#34582 / Bug#48370 / Bug#48295 / Bug#48293
その他多数……………………….

1) 現在のバージョン確認
original_20091221

http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-i686-icc-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/

2) 最新版のダウンロード
[root@colinux src]# wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-i686-icc-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/
–00:06:43– http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-i686-icc-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/

wget_5141

3) ダウンロードしたtarを解凍
[root@colinux src]# tar zxvf mysql-5.1.41-linux-i686-icc-glibc23.tar.gz
tar_zxvf

4) 展開したtarを自分が希望するパスへ移動 (これはオプション)
mv_mysql5141

5) 新しく展開したフォルダーのパーミッションの変更
[root@colinux local]# chown -R mysql:mysql mysql-5.1.41-linux-i686-icc-glibc23/
[root@colinux local]# chmod -R 755 mysql-5.1.41-linux-i686-icc-glibc23/

6) 現在稼動している、MYSQLサービスを停止します。
mysql_5141_migration

7) シンボリックリンクの削除
[root@colinux local]# pwd
[root@colinux local]# rm mysql

8) アップグレード前のまで利用していた、MYSQLのdataフォルダーを新しいバージョンのMYSQLにコピー

[root@colinux local]# cp -rp mysql-5.1.40-linux-i686-icc-glibc23/data mysql-5.1.41-linux-i686-icc-glibc23/

9) シンボリックリンクの作成とサービスの再開
[root@colinux local]# ln -s mysql-5.1.41-linux-i686-icc-glibc23/ mysql

mysql5141_symbolic

[root@colinux local]# /etc/init.d/mysql.server start
Starting MySQL…. SUCCESS!
[root@colinux local]#

10) システムデータベースのアップグレード(information_schema / mysql )

[root@colinux local]# /usr/local/mysql/bin/mysql_upgrade -u root -p

mysql_5141_sqlupgrade

11) バージョンアップの確認

※ mysql_upgrade_infoファイルの確認
[root@colinux data]# cat /usr/local/mysql/data/mysql_upgrade_info
upgrade_info

※ エラーログの確認
mysql_5141_log_confirmation

※ information_schemaとmysqlスキーマの確認
select TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_TIME,UPDATE_TIME
from information_schema.tables where
TABLE_SCHEMA = 'information_schema' or TABLE_SCHEMA = 'mysql'
and CREATE_TIME is not NULL and UPDATE_TIME is not null
order by CREATE_TIME desc;

system_tables

※ バージョン確認 (mysql> select @@version;)
version_5141


1つのテーブルには最大6つのトリガーを作成する事が出来ます。
内訳は、INSERT,UPDATE,DELETEにBEFORE,AFTERの2つずつ作成する事が出来ます。

BEFORE INSERT ON table_name
AFTER INSERT ON table_name
BEFORE UPDATE ON table_name
AFTER UPDATE ON table_name
BEFORE DELETE ON table_name
AFTER DELETE ON table_name

BEFORE UPDATEトリガー


DELIMITER //
CREATE TRIGGER dbcomp_before_update_log
BEFORE UPDATE ON dbcomp FOR EACH ROW
BEGIN
INSERT INTO dbcomp_update_log( new_name, old_name )
VALUES ( NEW.name, OLD.name );
-- NEW.nameは更新後、OLD.nameは更新前の値を表す
END//
DELIMITER ;

trigger_review

mysql> update dbcomp set name = '5.1.40-log';
を実行すると以下のトリガーが実行されて値が自動更新される。

INSERT INTO dbcomp_update_log( new_name, old_name )
VALUES ( NEW.name, OLD.name );
-- NEW.nameは更新後、OLD.nameは更新前の値を表す

trigger_review_action

mysql> update dbcomp set name = '5.4.0';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from dbcomp;
+——-+
| name |
+——-+
| 5.4.0 |
+——-+
1 row in set (0.00 sec)

mysql> select * from dbcomp_update_log;
+————+————+
| old_name | new_name |
+————+————+
| MySQL | 5.1.40-log |
| 5.1.40-log | 5.4.0 |
+————+————+
2 rows in set (0.00 sec)

mysql>

INSERTによって起動されたトリガではNEWのみが有効で、それがDELETEであればOLDのみが有効になる。
たとえば前の例でデータ追加・削除時の状況も記録できるようにするには、以下のトリガを追加する。


DELIMITER //
-- データ追加時に起動されるトリガ
CREATE TRIGGER dbcomp_before_insert_log BEFORE INSERT ON dbcomp FOR EACH ROW
BEGIN
INSERT INTO dbcomp_update_log( new_name )
VALUES ( NEW.name );
END//

BEFORE INSERT
before_insert


-- データ削除時に起動されるトリガ
CREATE TRIGGER dbcomp_before_delete_log BEFORE DELETE ON dbcomp FOR EACH ROW
BEGIN
INSERT INTO dbcomp_update_log( old_name )
VALUES ( OLD.name );
END//

BEFORE DELETE
trigger_before_delete

————————————————————————————————–
トリガの処理においては、その起動の対象となったテーブルに対して
INSERT / UPDATE / DELETEを実行することはできないのだが、唯一の例外がある。
それは、BEFORE INSERTもしくはBEFORE UPDATEのトリガにおいて、
NEW.nameのような、これから追加・更新しようとする値だけはトリガ内で変更できる
————————————————————————————————–

トリガを削除するには、以下のようなDROP TRIGGER構文を実行する。
スキーマ名称およびトリガ名称はCREATE TRIGGERで設定済みのものでなければならない。
ただしIF EXISTSを付けた場合は、存在しないトリガを指定してもエラーではなくwarningとなる。

DROP TRIGGER [IF EXISTS] [スキーマ名称.]トリガ名称;

トリガーの確認
show triggers from データベース\G

show-_triggers_from_db

show create trigger dbcomp_before_delete_log
show_create_trigger


SELECT * FROM information_schema.triggers
where action_timing = 'BEFORE' AND event_manipulation = 'DELETE' \G

information_schema_triggers

MYSQL Triggers
[MySQL] MySQLのトリガとイベントスケジューラ


MYSQLでUPDATE文を利用する場合の構文

単一テーブル構文:


UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

単一テーブル構文には、UPDATE ステートメントは新しい値を利用して tbl_name 内に既存行のカラムを
更新します。SET 条項は、どのカラムを変更し、それらにはどの値が与えられるべきかという事を指示します。
もし WHERE 条項が与えられたら、それはどの行を更新するべきかを決定します。WHERE 条項が無ければ、
全ての行が更新されます。もし ORDER BY 条項が指定されると、指定された順に行が更新されます。
LIMIT 条項は、更新できる行数に制限を設定します。

update_single_table

複合テーブル構文:


UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_condition]

複合テーブル構文には、UPDATE が、条件を満たす table_references
で名づけられたそれぞれのテーブルの行を更新します。この場合、ORDER BY と LIMIT を利用する事はできません
※  MySQL バージョン 4.0.4 以降では、複数のテーブルに対する UPDATE 操作も実行可能。


UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

上の例では、カンマ演算子を使用した内部結合を示していますが、複数テーブルの
UPDATE ステートメントでは、LEFT JOIN など、SELECT ステートメントで使用可能な任意
の結合型を使用することができます。


mysql> update MYSQLIMP,MYSQLIMP2 set MYSQLIMP.n = MYSQLIMP2.n
-> where MYSQLIMP.id = MYSQLIMP2.id
-> and MYSQLIMP.id = 100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

update_multi_tables

UPDATE ステートメントは次の修飾因子をサポートします。

もし LOW_PRIORITY キーワードを利用すると、別のクライアントがテーブルからの
読み込みをしなくなるまで、UPDATE の実行が遅れます。

もし IGNORE キーワードを利用すると、更新中にエラーが発生しても更新ステートメントは異常終了しません。
複製キーの矛盾が起きた行は更新されません。データ変換エラーを起こす値にカラムが更新された行は、
代わりに一番近い有効値に更新されます。

単一テーブル UPDATE 割り当ては通常左から右に評価されます。複合テーブルの更新に関しては、
割り当てが特定の順番で行われるという保証はありません。

NULL に設定する事で NOT NULL を宣言されたカラムを更新すると、カラムはそのデータ タイプに
適切なデフォルト値に設定され、警告カウントはインクリメントされます。
数値タイプ、文字列タイプの空の文字列(”)、そして日付と時刻タイプの「ゼロ」 値のデフォルト値は 0 です。

UPDATE の領域を制限する為に LIMIT row_count を利用する事ができます。
LIMIT 条項は行に一致した制限です。ステートメントは、実際に変更されたかどうかに関わらず、
WHERE 条項の条件を満たす row_count 行を見付けるとすぐに止まります。

もし UPDATE ステートメントが ORDER BY 条項を含むなら、行は条項に指示された順番で更新されます。
これは、エラーが起こるかもしれない特定の場合に有効です。
複合テーブルをカバーする UPDATE 演算を行う事もできます。
.しかし、複合テーブル UPDATE と共に ORDER BY や LIMIT を利用する事はできません。


テーブルからファイルにデータを書き込むには、SELECT … INTO OUTFILE を利用してください。
テーブルにファイルをリード バックするには、LOAD DATA INFILE を利用してください。
両方のステートメントに対して FIELDS と LINES 条項の構文は同じです。
条項は両方とも任意ですが、もし両方が指定された場合 FIELDS は LINES に先行しなければいけません。

SELECT … INTO OUTFILE

SELECT の SELECT … INTO OUTFILE ‘file_name’ 型は選択された行をファイルに書き込みます。
ファイルはサーバ ソフト上に作成されるので、この構文を利用するには FILE 権限を持たなければいけません。
file_name は、/etc/passwd のようなファイルやデータベース テーブルが、その他の物の間で破壊されるのを防ぐ
既存ファイルにはなり得ません。MySQL 5.1.6 以降のバージョンでは、character_set_filesystem システム変数は、
ファイル名の解明をコントロールします。

SELECT … INTO OUTFILE ステートメントは、サーバ マシン上のテキスト ファイルにテーブルをすばやく
書き出す事ができます。もしサーバ ホストではなく、クライアント ホスト上に結果ファイルを
作成したければ、SELECT … INTO OUTFILE を利用する事はできません。
その場合、クライアント ホスト上にファイルを生成する為には、代わりに mysql -e “SELECT …” > file_name
のようなコマンドを利用しなければいけません。
SELECT … INTO OUTFILE は LOAD DATA INFILE の補数です。


SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;


SELECT ID,Name,Population INTO OUTFILE '/tmp/City20091218.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM Study.City;

select_into_outfile1

LOAD DATA INFILE

LOAD DATA INFILE ステートメントは高スピードでテキスト ファイルからテーブルに行を読み込みます。
ファイル名は直定数文字列として与えられなければいけません。
LOAD DATA INFILE は SELECT … INTO OUTFILE の補数です。

mysqlimport ユーティリティを利用する事でデータ ファイルをロードする事もできます。
これは、サーバに LOAD DATA INFILE ステートメントを送信する事で機能します。–local オプション
は mysqlimport がクライアント ホストからデータファイルを読み込むよう働きかけます。
もしクライアントとサーバが圧縮されたプロトコルをサポートするなら、スピードが遅いネットワークにより
良い性能を得る為に –compress オプションを指定する事ができます。

LOCAL キーワードが指定されたら、それは接続の最後にクライアントに関して解明されます。

* もし LOCAL が指定されると、ファイルはクライアント ホスト上のクライアントプログラムによって読み込まれ、
サーバに送られます。ファイルは、その明確な場所を指定する為の完全なパス名として与えられます。
もしそのパス名が相対的な物として与えられると、その名前はクライアントプログラムが開始されたディレクトリ
と比較して解明されます。

* もし LOCAL が指定されなければ、ファイルはサーバ ホスト上に置かれ、サーバによって直接読み込まれる必要があります。

サーバはファイルを置く為に次のルールを利用します。

* もしファイル名が完全なパス名であれば、サーバはそれをそのまま利用します。
* もしファイル名が1つ、または複数の主要コンポネントを持つ相対的なパス名であれば、
サーバはそのデータ ディレクトリに関連するファイルを検索します。
* もし主要コンポネントを持たないファイル名が与えられると、サーバはデフォルト データベース
のデータベース ディレクトリ内のファイルを探します。

これらのルールは、非 LOCAL の場合、 ./myfile.txt としてのファイル名はサーバーのデータ ディレクトリ
から読まれ、その一方、myfile.txt としてのファイル名はデフォルト データベースのデータベース ディレクトリ
から読み込まれるという事を意味しますので、注意してください。例えば、もし db1 がデフォルト データベースなら、
ステーメントが db2 データベース内のテーブルにファイルを明示的にロードしたとしても、
次の LOAD DATA ステートメントが db1 のデータベース ディレクトリからファイル data.txt を読み込みます。

LOAD DATA INFILE ‘data.txt’ INTO TABLE db2.my_table;

ローカル ファイルをロードするのに FILE 特権は必要ありません。
LOCAL は、サーバとクライアントの両方が、これを許容できる場合のみ機能します。
例えば、もし mysqld が –local-infile=0 と共に開始された場合、LOCAL は機能しません。


SELECT ID,Name,Population INTO OUTFILE '/tmp/City20091218.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM STUDY.City;


CREATE TABLE `LOAD_D_CITY` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `idx_City_Name` (`Name`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;


LOAD DATA INFILE '/tmp/City20091218.txt' INTO TABLE LOAD_D_CITY
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

load_data_infile

12.2.7. SELECT 構文
12.2.5. LOAD DATA INFILE 構文


LIMIT 条項は SELECT ステートメントに返された行数を制限するのに利用する事ができます。
LIMIT は、負数以外の整数定数でなければいけない、1つか2つの数値引数を取ります。
(準備されたステートメントを利用している時以外)

その2つの引数のうち、最初の物は返される最初の行のオフセットを指定し、2つめの物は返される行の
最高数を指定します。冒頭の行のオフセットは0です。(1ではありません)

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

1つの引数で、その値は結果セットの最初から返される行数を指定します。

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

言い換えると、LIMIT row_count は LIMIT 0, row_count と同等だという事になります。

1行返します

SET @a=1; /* SELECT @a :=1 */
PREPARE STMT FROM 'SELECT * FROM City LIMIT ?';
EXECUTE STMT USING @a;

prepare_limit

2行目から6行目を返します

SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM City LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

prepare_limit_row

1行目~5行目まで
prepare_limit_row_0


比較の演算の結果は、1 ( TRUE ) 、0 ( FALSE ) 、または NULL の値になります。
これらの演算は、数字とストリングの両方に適応します。必要に応じて、
ストリングは数字に、数字はストリングに自動的に変換されます。

——————————————————————
比較の演算
比較の演算の結果は、1 ( TRUE ) 、0 ( FALSE ) 、または NULL の値になります。
このセクションの関数のうちには、1 ( TRUE ) 、0 ( FALSE ) 、
または NULL 以外の値を戻すものもあります。LEAST() および GREATEST()
などがその例です。
——————————————————————
mysql> select 1;
+—+
| 1 |
+—+
| 1 |
+—+
1 row in set (0.00 sec)

mysql> select 1 = 1;
+——-+
| 1 = 1 |
+——-+
| 1 |
+——-+
1 row in set (0.00 sec)

mysql> select 1 = 2;
+——-+
| 1 = 2 |
+——-+
| 0 |
+——-+
1 row in set (0.00 sec)

mysql>

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
+———+—————+————+
| 1 <=> 1 | NULL <=> NULL | 1 <=> NULL |
+———+—————+————+
| 1 | 1 | 0 |
+———+—————+————+
1 row in set (0.01 sec)

mysql> SELECT 1 BETWEEN 2 AND 3;
+——————-+
| 1 BETWEEN 2 AND 3 |
+——————-+
| 0 |
+——————-+
1 row in set (0.00 sec)

mysql> SELECT 'b' BETWEEN 'a' AND 'c';
+————————-+
| ‘b’ BETWEEN ‘a’ AND ‘c’ |
+————————-+
| 1 |
+————————-+
1 row in set (0.00 sec)

mysql>

——————————————————————
ISNULL(expr)
expr が NULL の場合、ISNULL() は 1 を戻し、それ以外では 0 を戻します。
——————————————————————

mysql> select ISNULL(NULL);
+————–+
| ISNULL(NULL) |
+————–+
| 1 |
+————–+
1 row in set (0.00 sec)

mysql> SELECT ISNULL(1+1);
+————-+
| ISNULL(1+1) |
+————-+
| 0 |
+————-+
1 row in set (0.00 sec)

mysql> SELECT ISNULL(1/0);
+————-+
| ISNULL(1/0) |
+————-+
| 1 |
+————-+
1 row in set (0.00 sec)

mysql>

isnull

——————————————————————
IFNULL(expr1,expr2)
expr1 が NULL でない場合、IFNULL() は expr1 を戻し、それ以外では expr2 を
戻します。IFNULL() は、使用されている文脈によって、数値値もしくはストリング値
を戻します。
——————————————————————
mysql> SELECT IFNULL(1,0);
+————-+
| IFNULL(1,0) |
+————-+
| 1 |
+————-+
1 row in set (0.00 sec)

mysql> SELECT IFNULL(NULL,10);
+—————–+
| IFNULL(NULL,10) |
+—————–+
| 10 |
+—————–+
1 row in set (0.00 sec)

mysql> SELECT IFNULL(1/0,10);
+—————-+
| IFNULL(1/0,10) |
+—————-+
| 10.0000 |
+—————-+
1 row in set (0.00 sec)

mysql> SELECT IFNULL(1/0,'yes');
+——————-+
| IFNULL(1/0,’yes’) |
+——————-+
| yes |
+——————-+
1 row in set (0.00 sec)

mysql> SELECT IFNULL(NULL,'NULLなのでこちらを表示');
+————————————————+
| IFNULL(NULL,’NULLなのでこちらを表示’) |
+————————————————+
| NULLなのでこちらを表示 |
+————————————————+
1 row in set (0.01 sec)

mysql> SELECT IFNULL('データはNULLでは無い','NULLなのでこちらを表示');
+————————————————————————–+
| IFNULL(‘データはNULLでは無い’,’NULLなのでこちらを表示’) |
+————————————————————————–+
| データはNULLでは無い |
+————————————————————————–+
1 row in set (0.01 sec)

mysql>

ifnull

——————————————————————
NULLIF(expr1,expr2)
expr1 = expr2 が true の場合は NULL を返し、それ以外は expr1 を返します。
これは、CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END と同様です。
——————————————————————

mysql> SELECT NULLIF(1,1);
+————-+
| NULLIF(1,1) |
+————-+
| NULL |
+————-+
1 row in set (0.05 sec)

mysql> SELECT NULLIF(1,2);
+————-+
| NULLIF(1,2) |
+————-+
| 1 |
+————-+
1 row in set (0.00 sec)

mysql> SELECT NULLIF('おなじ','おなじ');
+———————————+
| NULLIF(‘おなじ’,’おなじ’) |
+———————————+
| NULL |
+———————————+
1 row in set (0.00 sec)

mysql> SELECT NULLIF('おなじ','ちがう');
+———————————+
| NULLIF(‘おなじ’,’ちがう’) |
+———————————+
| おなじ |
+———————————+
1 row in set (0.00 sec)

mysql>

nullif

——————————————————————
LEAST(value1,value2,…)
引数ふたつ以上では、最小の ( 最小値の ) 引数を戻します。
引数は次のルールを使用して比較されます戻り値が INTEGER 文脈で使用されている場合、
またはすべての引数が整数値である場合、それらは整数として比較されます。
戻り値が REAL 文脈で使用されている場合、またはすべての引数が実数値である場合、
それらは実数として比較されます。
引数のいずれかが大文字小文字の区別のあるストリングである場合、
引数は大文字小文字の区別のあるストリングとして比較されます。
他のすべてのケースでは、引数は大文字小文字の区別のあるストリングとして比較されます。
引数のどれかが NULL である場合、LEAST() は NULL を戻します。
——————————————————————

mysql> SELECT LEAST('B','A','C');
+——————–+
| LEAST(‘B’,’A’,’C’) |
+——————–+
| A |
+——————–+
1 row in set (0.07 sec)

mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
+—————————+
| LEAST(34.0,3.0,5.0,767.0) |
+—————————+
| 3.0 |
+—————————+
1 row in set (0.00 sec)

mysql> SELECT LEAST(2,0);
+————+
| LEAST(2,0) |
+————+
| 0 |
+————+
1 row in set (0.00 sec)

mysql>

——————————————————————
GREATEST(value1,value2,…)
引数ふたつ以上では、最大の ( 最大値の ) 引数を戻します。それらの引数は、
LEAST() に対するものと同じルールで比較されます。
——————————————————————
mysql> SELECT GREATEST(2,0);
+—————+
| GREATEST(2,0) |
+—————+
| 2 |
+—————+
1 row in set (0.01 sec)

mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
+——————————+
| GREATEST(34.0,3.0,5.0,767.0) |
+——————————+
| 767.0 |
+——————————+
1 row in set (0.00 sec)

mysql> SELECT GREATEST('B','A','C');
+———————–+
| GREATEST(‘B’,’A’,’C’) |
+———————–+
| C |
+———————–+
1 row in set (0.00 sec)

mysql>

———————————————————–
IF(expr1,expr2,expr3)
expr1 が TRUE である場合は ( expr1 <> 0 および expr1 <> NULL ) 、
IF() は expr2 を戻します。それ以外では、expr3 を戻します。
IF() は、使用されている文脈によって、数値値もしくはストリング値を戻します。
———————————————————–

mysql> SELECT IF(1>2,2,3);
+————-+
| IF(1>2,2,3) |
+————-+
| 3 |
+————-+
1 row in set (0.00 sec)

mysql> SELECT IF(1=1,2,3);
+————-+
| IF(1=1,2,3) |
+————-+
| 2 |
+————-+
1 row in set (0.00 sec)

mysql> SELECT IF(1=1,'おなじ値','違う値');
+————————————+
| IF(1=1,’おなじ値’,’違う値’) |
+————————————+
| おなじ値 |
+————————————+
1 row in set (0.00 sec)

mysql> SELECT IF(1=2,'おなじ値','違う値');
+————————————+
| IF(1=2,’おなじ値’,’違う値’) |
+————————————+
| 違う値 |
+————————————+
1 row in set (0.00 sec)

mysql> SELECT IF(1>2,'1>2は正しい','1>2は間違い');
+———————————————+
| IF(1>2,’1>2は正しい’,’1>2は間違い’) |
+———————————————+
| 1>2は間違い |
+———————————————+
1 row in set (0.01 sec)

mysql>

if

参考サイト
——————————————
11.1.3. 比較関数と演算子


以下のテーブルはi1 char(10)の列に `i1` (`i1`(3))と文字数を3文字に制限したインデックスを
作成してある。最初の3文字でデータが比較出来るようなSELECT文を実行した場合
参照するIndexのデータ量も少なくパフォーマンスが向上する。
但し、対外は色々なアプリケーションから一つのテーブルを参照する事が殆どだと思いますので
あまり利用する頻度は高くありません。列にNOT NULLを常に設定するなどと心がけて対応
する方が実際の運用では全体的なパフォーマンスを向上してくれるかと思います。


CREATE TABLE `fastindex` (
`i1` char(10) NOT NULL default '',
`i2` char(10) NOT NULL default '',
KEY `i1` (`i1`(3)),
KEY `i2` (`i2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

show_index_char

SELECT i1 FROM fastindex WHERE i1 LIKE 'abc%';
SELECT i2 FROM fastindex WHERE i2 LIKE 'abc%';

以下のEXPLAINではデータが殆ど入っていないので、PLANは変わりませんが
i1のインデックスは最初の3文字のみを利用して作成されているので、上記のSELECT
を実行した場合「WHERE i1 LIKE ‘abc%’;」の方が実行が早い。
index_fast

—————————————————————————————————————-
Indexing cannot only help speed up SELECT queries, but it can also improve
UPDATE and DELETE statements. This is because indexing can help the
server

    find the rows more quickly that should be updated or deleted

.
On the other hand, indexes will slow down UPDATE and DELETE statements because
not only the original data have to be updated but also the indexes.
—————————————————————————————————————-

WHERE, ORDER BY, GROUP BYを利用しない場合やデータの種類が少ない場合は
INDEXを付けてもパフォーマンスが向上する事は殆どありません。INSERT、DELETE、
UPDATEなどの処理でINDEXの更新も入るので遅くなる場合が考えられます。
インデックスが実際に利用されるかどうか常にイメージしながらINDEXを作成するように
心がけると良いかも知れません。

    以下のテーブルはName列に5文字を利用したIndexを付与してあります。


CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `idx_City_Name` (`Name`(5))
) ENGINE=MyISAM AUTO_INCREMENT=4083 DEFAULT CHARSET=utf8;

index_on_city

後からインデックスを追加する場合は、以下のようなコマンドを実行すれば良い。
——————————
mysql> CREATE INDEX Name ON City(Name(5));
mysql> ALTER TABLE City ADD INDEX(Name(5));

データのINSERT処理を早くするには?
————————————————————————————————————————-
You could use multiple-row inserts, rather than single-row inserts.
LOAD DATA INFILE will run even faster than any INSERT statement that inserts the
same amount of rows. For InnoDB tables, you could group inserts within a transaction
so that InnoDB will flush changes only when the transaction ends,
rather than after every single INSERT statement. If you’re planning to replace rows using
DELETE and INSERT, you could as well use the MySQL extension REPLACE that runs faster.

For a multiple-row INSERT statementでエラーが起きた場合………….
■ MyISAM では, エラーが起きるまでデータがINSERTされます。
■ InnoDBでは, Rolls backが起きてデータが一見もINSERTされずテーブルはEmptyのままになります。