データ量が昔と比較して増えてきたからか、最近はパーティションの話を良く聞くので、
簡易的に再確認してみました。いざ確認してみると、色々と忘れている事もあり頭がリフレッシュ出来ました。
パーティショニン種類に関しては、以下のサイトに詳しく出ているので参照してみて下さい。
また、MySQL5.6のマニュアルも日本語化されたので参考にしてみて下さい。

MySQL5.6日本語マニュアル
https://dev.mysql.com/doc/refman/5.6/ja/

19.2. パーティショニングタイプ
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-types.html

——————————————————————————————–
※奥野さんのサイトにも書かれていますが、必ずしもパーティションを利用して、
 パフォーマンスが良くなる事ばかりでは無いです。効率良くIndexが使えていたりデータによっては、
 付けない方が良い事もあるので、先ずは検証環境で確認してみて下さい。

 パーティショニングの使用例 – カーディナリティが低いカラムを使って検索する場合
 http://nippondanji.blogspot.jp/2009/04/1.html
——————————————————————————————–

第19章 パーティション化
https://dev.mysql.com/doc/refman/5.6/ja/partitioning.html

19.6. パーティショニングの制約と制限
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations.html#partitioning-limitations-subpartitions

確認:バージョン

root@localhost [test]> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.6.24-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [test]> 

プラグインで有効になっているか確認

root@localhost [test]> SELECT 
    -> PLUGIN_NAME as Name, 
    -> PLUGIN_VERSION as Version, 
    -> PLUGIN_STATUS as Status 
    -> FROM INFORMATION_SCHEMA.PLUGINS 
    -> WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+----------+
| Name               | Version | Status   |
+--------------------+---------+----------+
| binlog             | 1.0     | ACTIVE   |
| MEMORY             | 1.0     | ACTIVE   |
| MRG_MYISAM         | 1.0     | ACTIVE   |
| CSV                | 1.0     | ACTIVE   |
| MyISAM             | 1.0     | ACTIVE   |
| ARCHIVE            | 3.0     | ACTIVE   |
| FEDERATED          | 1.0     | DISABLED |
| InnoDB             | 5.6     | ACTIVE   |
| BLACKHOLE          | 1.0     | ACTIVE   |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE   |
| partition          | 1.0     | ACTIVE   | < --- こちらがACTIVEであれば利用可能
+--------------------+---------+----------+
11 rows in set (0.01 sec)

root@localhost [test]> 

■ 標準的なRANGEパーティショニング

root@localhost [test]> show create table tbl_partition\G
*************************** 1. row ***************************
       Table: tbl_partition
Create Table: CREATE TABLE `tbl_partition` (
  `member_id` varchar(40) NOT NULL,
  `platform` varchar(10) NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `month` tinyint(2) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`,`year`,`month`),
  KEY `idx_tbl_partition` (`year`),
  KEY `idx_tbl_partition_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (`year`)
(PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

root@localhost [test]> 

root@localhost [test]> explain partitions select * from tbl_partition where year = 2015;
+----+-------------+---------------+------------+------+-------------------+-------------------+---------+-------+------+-------+
| id | select_type | table         | partitions | type | possible_keys     | key               | key_len | ref   | rows | Extra |
+----+-------------+---------------+------------+------+-------------------+-------------------+---------+-------+------+-------+
|  1 | SIMPLE      | tbl_partition | p2016      | ref  | idx_tbl_partition | idx_tbl_partition | 2       | const |    2 | NULL  |
+----+-------------+---------------+------------+------+-------------------+-------------------+---------+-------+------+-------+
1 row in set (0.00 sec)

root@localhost [test]> 

■ サブパーティション
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-subpartitions.html

サブパーティションに関する問題
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations.html#partitioning-limitations-subpartitions

サブパーティションに関する問題 サブパーティションは HASH または KEY パーティショニングを使用する必要があります。サブパーティション化できるのは RANGE および LIST パーティションのみです。HASH および KEY パーティションはサブパーティション化できません。 現在のところ、SUBPARTITION BY KEY にはサブパーティショニングカラムを明示的に指定する必要がありますが、PARTITION BY KEY の場合は省略できます (その場合、テーブルの主キーカラムがデフォルトで使用されます)


root@localhost [test]> show create table tbl_sub_partition01\G
*************************** 1. row ***************************
       Table: tbl_sub_partition01
Create Table: CREATE TABLE `tbl_sub_partition01` (
  `member_id` int(11) DEFAULT NULL,
  `platform` varchar(10) NOT NULL,
  `purchased` date DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
SUBPARTITION BY HASH ( TO_DAYS(purchased))
SUBPARTITIONS 12
(PARTITION sub_y2010 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION sub_y2011 VALUES LESS THAN (2011) ENGINE = InnoDB,
 PARTITION sub_y2012 VALUES LESS THAN (2012) ENGINE = InnoDB,
 PARTITION sub_y2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION sub_y2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION sub_y2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION sub_y2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION sub_y2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION sub_y2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION sub_y2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION sub_y2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION sub_y_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

root@localhost [test]> 

timestamp型などを利用すると、
”ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed”というエラーになります。

root@localhost [test]> select * from tbl_sub_partition01;
+-----------+----------+------------+---------------------+
| member_id | platform | purchased  | updated_at          |
+-----------+----------+------------+---------------------+
|         1 | iphone   | 2015-06-28 | 2015-06-30 11:53:32 |
|         2 | iphone   | 2015-06-29 | 2015-06-30 11:53:39 |
|         3 | iphone   | 2015-06-30 | 2015-06-30 11:53:47 |
|         4 | iphone   | 2015-07-01 | 2015-06-30 12:08:13 |
|         5 | iphone6  | 2015-07-02 | 2015-06-30 12:08:26 |
|         6 | iphone6  | 2015-07-03 | 2015-06-30 12:10:11 |
|         7 | iphone3s | 2015-07-04 | 2015-06-30 12:10:27 |
|         8 | iphone4s | 2015-07-05 | 2015-06-30 12:10:37 |
|         9 | iphone4s | 2015-07-06 | 2015-06-30 12:10:46 |
|        10 | iphone6s | 2015-07-07 | 2015-06-30 12:10:59 |
+-----------+----------+------------+---------------------+
10 rows in set (0.00 sec)

root@localhost [test]> explain partitions select * from tbl_sub_partition01 where purchased = '2015-07-01';
+----+-------------+---------------------+------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table               | partitions             | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+------------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tbl_sub_partition01 | sub_y2016_sub_y2016sp5 | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+---------------------+------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

root@localhost [test]> 

パーティションにDATA DIRECTORYなどを指定する場合。
MyISAMで且つ、テーブル全体では無くSubpartition毎に指定しています。

root@localhost [test]> show create table tbl_sub_partition_dir\G
*************************** 1. row ***************************
       Table: tbl_sub_partition_dir
Create Table: CREATE TABLE `tbl_sub_partition_dir` (
  `id` int(11) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
SUBPARTITION BY HASH ( TO_DAYS(purchased))
(PARTITION p0_dir VALUES LESS THAN (1990)
 (SUBPARTITION s0 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM,
  SUBPARTITION s1 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM),
 PARTITION p1_dir VALUES LESS THAN (2000)
 (SUBPARTITION s2 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM,
  SUBPARTITION s3 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM),
 PARTITION p2_dir VALUES LESS THAN MAXVALUE
 (SUBPARTITION s4 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM,
  SUBPARTITION s5 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM)) */
1 row in set (0.01 sec)

root@localhost [test]> system ls /home/mysql/data/
tbl_sub_partition_dir#P#p0_dir#SP#s0.MYD  tbl_sub_partition_dir#P#p1_dir#SP#s2.MYD  tbl_sub_partition_dir#P#p2_dir#SP#s4.MYD  test
tbl_sub_partition_dir#P#p0_dir#SP#s1.MYD  tbl_sub_partition_dir#P#p1_dir#SP#s3.MYD  tbl_sub_partition_dir#P#p2_dir#SP#s5.MYD
root@localhost [test]> system ls /home/mysql/index/
tbl_sub_partition_dir#P#p0_dir#SP#s0.MYI  tbl_sub_partition_dir#P#p1_dir#SP#s2.MYI  tbl_sub_partition_dir#P#p2_dir#SP#s4.MYI
tbl_sub_partition_dir#P#p0_dir#SP#s1.MYI  tbl_sub_partition_dir#P#p1_dir#SP#s3.MYI  tbl_sub_partition_dir#P#p2_dir#SP#s5.MYI
root@localhost [test]> 

sub_myisam

DATAディレクトリーにシンボリックリンクが作成されています。

[root@GA01 test]# pwd
/usr/local/mysql/data/test
[root@GA01 test]# ls -l tbl_sub_partition_dir*
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p0_dir#SP#s0.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p0_dir#SP#s0.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p0_dir#SP#s0.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p0_dir#SP#s0.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p0_dir#SP#s1.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p0_dir#SP#s1.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p0_dir#SP#s1.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p0_dir#SP#s1.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p1_dir#SP#s2.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p1_dir#SP#s2.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p1_dir#SP#s2.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p1_dir#SP#s2.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p1_dir#SP#s3.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p1_dir#SP#s3.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p1_dir#SP#s3.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p1_dir#SP#s3.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p2_dir#SP#s4.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p2_dir#SP#s4.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p2_dir#SP#s4.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p2_dir#SP#s4.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p2_dir#SP#s5.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p2_dir#SP#s5.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p2_dir#SP#s5.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p2_dir#SP#s5.MYI
-rw-rw----. 1 mysql mysql 8596  6月 30 13:31 tbl_sub_partition_dir.frm
-rw-rw----. 1 mysql mysql  104  6月 30 13:31 tbl_sub_partition_dir.par
[root@GA01 test]# 

data foler

19.6.4. パーティショニングとロック
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations-locking.html

MySQL 5.6.6 はパーティションロックプルーニングを実装し、これによって多くの場合に不必要なロックが排除されます。
MySQL 5.6.6 以降では、パーティション化された MyISAM テーブルに対して読み取りまたは更新を行うほとんどのステートメントで、影響を受けるパーティションのみがロックされます。たとえば、MySQL 5.6.6 より前は、パーティション化 MyISAM テーブルからのSELECT でテーブル全体がロックされました。MySQL 5.6.6 以降は、SELECT ステートメントの WHERE 条件を満たす行を実際に含むパーティションのみがロックされます。これには、パーティション化された MyISAM テーブルに対する同時操作の速度および効率を向上させる効果があります。この改善は、多く (32 以上) のパーティションを持つ MyISAM テーブルを操作するときに特に顕著になります。

DATA DIRECTORY、INDEX DIRECTORY
http://dev.mysql.com/doc/refman/5.6/ja/create-table.html

InnoDB では、DATA DIRECTORY=’directory’ オプションを使用すると、MySQL データディレクトリ以外の場所に新しいInnoDB file-per-table テーブルスペースを作成できます。MySQL は、指定されたディレクトリ内にデータベース名に対応するサブディレクトリを作成し、さらにその中に新しいテーブルの .ibd ファイルを作成します。InnoDB テーブルで DATA DIRECTORY オプションを使用するには、innodb_file_per_table 構成オプションを有効にする必要があります。このディレクトリは、ディレクトリへの (相対パスではなく) フルパス名である必要があります。
詳細は、「テーブルスペースの位置の指定」を参照してください。
MyISAM テーブルを作成する場合は、DATA DIRECTORY=’directory’ 句、INDEX DIRECTORY=’directory’ 句、またはその両方を使用できます。これらは、それぞれ MyISAM テーブルのデータファイルとインデックスファイルを配置する場所を指定します。
InnoDB テーブルとは異なり、DATA DIRECTORY または INDEX DIRECTORY オプションで MyISAM テーブルを作成する場合、MySQL はデータベース名に対応するサブディレクトリを作成しません。各ファイルは、指定されたディレクトリ内に作成されます。

重要
https://bugs.mysql.com/bug.php?id=32091
テーブルレベルの DATA DIRECTORY および INDEX DIRECTORY オプションは、パーティション化されたテーブルでは無視されます。(Bug #32091)

■InnoDBで、SUBPARTITIONを指定せずに実行した場合


root@localhost [test]> CREATE TABLE `tbl_partition_innodb` (
    ->   `member_id` varchar(40) NOT NULL,
    ->   `platform` varchar(10) NOT NULL,
    ->   `year` smallint(5) unsigned NOT NULL,
    ->   `month` tinyint(2) unsigned NOT NULL,
    ->   `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`member_id`,`year`,`month`),
    ->   KEY `idx_tbl_partition` (`year`),
    ->   KEY `idx_tbl_partition_id` (`member_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DATA DIRECTORY = '/home/mysql/data';
Query OK, 0 rows affected (0.02 sec)

root@localhost [test]> show create table tbl_partition_innodb\G
*************************** 1. row ***************************
       Table: tbl_partition_innodb
Create Table: CREATE TABLE `tbl_partition_innodb` (
  `member_id` varchar(40) NOT NULL,
  `platform` varchar(10) NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `month` tinyint(2) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`,`year`,`month`),
  KEY `idx_tbl_partition` (`year`),
  KEY `idx_tbl_partition_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DATA DIRECTORY='/home/mysql/data/'
1 row in set (0.00 sec)

ALTERテーブルでパーティション化すると、DATA DIRECTORYは無効になりファイルは削除されます。

root@localhost [test]> Alter table tbl_partition_innodb PARTITION BY RANGE (`year`)
    -> (PARTITION p2013_innodb VALUES LESS THAN (2013) ENGINE = InnoDB,
    ->  PARTITION p2014_innodb VALUES LESS THAN (2014) ENGINE = InnoDB,
    ->  PARTITION p2015_innodb VALUES LESS THAN (2015) ENGINE = InnoDB,
    ->  PARTITION p2016_innodb VALUES LESS THAN (2016) ENGINE = InnoDB,
    ->  PARTITION p2017_innodb VALUES LESS THAN (2017) ENGINE = InnoDB,
    ->  PARTITION p2018_innodb VALUES LESS THAN (2018) ENGINE = InnoDB,
    ->  PARTITION p2019_innodb VALUES LESS THAN (2019) ENGINE = InnoDB,
    ->  PARTITION p2020_innodb VALUES LESS THAN (2020) ENGINE = InnoDB,
    ->  PARTITION pmax_innodb VALUES LESS THAN MAXVALUE ENGINE = InnoDB) ;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> show create table tbl_partition_innodb\G
*************************** 1. row ***************************
       Table: tbl_partition_innodb
Create Table: CREATE TABLE `tbl_partition_innodb` (
  `member_id` varchar(40) NOT NULL,
  `platform` varchar(10) NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `month` tinyint(2) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`,`year`,`month`),
  KEY `idx_tbl_partition` (`year`),
  KEY `idx_tbl_partition_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (`year`)
(PARTITION p2013_innodb VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p2014_innodb VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p2015_innodb VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2016_innodb VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p2017_innodb VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p2018_innodb VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p2019_innodb VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p2020_innodb VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION pmax_innodb VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

root@localhost [test]> 

※ Alter Tableでパーティショニングすると、
  DATA DIRECTORY=’/home/mysql/data/’に存在していたibdファイルは削除されます。

file_innodb_alter_update

■圧縮テーブルとパーティショニング
パーティション毎にストレージエンジンンが指定出来ますが、圧縮はパーティション毎に指定出来ません。
圧縮する場合はテーブル毎に指定してください。

root@localhost [test]> show create table tbl_partition_sample_zip\G
*************************** 1. row ***************************
       Table: tbl_partition_sample_zip
Create Table: CREATE TABLE `tbl_partition_sample_zip` (
  `member_id` varchar(40) NOT NULL,
  `platform` varchar(10) NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `month` tinyint(2) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`,`year`,`month`),
  KEY `idx_tbl_partition` (`year`),
  KEY `idx_tbl_partition_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
/*!50100 PARTITION BY RANGE (`year`)
(PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

root@localhost [test]> 

補足メモ:

パーティションでは無いですが、パスを変更してデータを配置する方法。
MYSQL DATA DIRECTORY と INDEX DIRECTORYの指定

5.7からは複数テーブルを配置出来る、GENERAL TABLESPACEが利用可能になります。
InnoDB General Tablespace


root@localhost [test]> CREATE TABLE `tbl_DATA_DIRECTORY` (
    ->   `member_id` varchar(40) NOT NULL,
    ->   `platform` varchar(10) NOT NULL,
    ->   `year` smallint(5) unsigned NOT NULL,
    ->   `month` tinyint(2) unsigned NOT NULL,
    ->   `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`member_id`,`year`,`month`),
    ->   KEY `idx_tbl_DATA_DIRECTORY` (`year`),
    ->   KEY `idx_tbl_DATA_DIRECTORY_id` (`member_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DATA DIRECTORY = '/home/mysql/data';
Query OK, 0 rows affected (0.03 sec)

root@localhost [test]> 

■テーブル定義はDefault Dataディレクトリーで,データ用テーブルスペースが指定したディレクトリーに作成されます。


[root@GA01 test]# ls -l /usr/local/mysql/data/test/tbl_DATA*
-rw-rw----. 1 mysql mysql 8712  6月 30 15:05 /usr/local/mysql/data/test/tbl_DATA_DIRECTORY.frm
-rw-rw----. 1 mysql mysql   44  6月 30 15:05 /usr/local/mysql/data/test/tbl_DATA_DIRECTORY.isl
[root@GA01 test]# ls -l /home/mysql/data/test/
合計 192
-rw-rw----. 1 mysql mysql 131072  6月 30 15:05 tbl_DATA_DIRECTORY.ibd
[root@GA01 test]# 

基本ファイル構成


テーブルを作成する時に、明示的にストレージエンジンを毎回指定
して作成しているか、既定のストレージエンジンを意図したものに設定して
いれば問題は無いが、他のDBと違いMYSQLには色々なストレージエンジン
があるのでオブジェクト作成する時には少しだけ考慮する事が必要。

+—————-+——–+
| Variable_name | Value |
+—————-+——–+
| storage_engine | MyISAM |
+—————-+——–+


mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

mysql> CREATE TABLE default_engine (id INT);
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'default_engine'\G
*************************** 1. row ***************************
Name: default_engine
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1970324836974591
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2009-07-22 14:18:46
Update_time: 2009-07-22 14:18:46
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql> SET storage_engine = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE set_default_engine (id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLE STATUS LIKE 'set_default_engine'\G
*************************** 1. row ***************************
Name: set_default_engine
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 11534336
Auto_increment: NULL
Create_time: 2009-07-22 14:19:14
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

mysql>

set_engine


mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

mysql> CREATE TABLE set_default_engine_manu (id INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'set_default_engine_manu'\G
*************************** 1. row ***************************
Name: set_default_engine_manu
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1970324836974591
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2009-07-22 14:24:17
Update_time: 2009-07-22 14:24:17
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)

mysql>

set_engine_man

※ 稼動中に以下のコマンドでディフォルトストレージエンジンを変更可能。


mysql> set global storage_engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

mysql> set @@global.storage_engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2009-07-23 00:14:31 |
+---------------------+
1 row in set (0.00 sec)

設定はDBに接続しなおすか、新規接続から有効になります。
しかしオプションファイルに記入しておかないと、
再起動と同時にディフォルトエンジンは元に戻ります。
コマンド:      mysqld –default-storage-engine=InnoDB
オプションファイル:  default-storage-engine=InnoDB
特定セッション: SET SESSION storage_engine=InnoDB;

※新規セッションから有効
set_global1
※再起動後の確認

[root@colinux ~]# /etc/init.d/mysql.server restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@colinux ~]#
[root@colinux ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.01 sec)

mysql>


複数行をINSERTした時に列の長さを超えたデータと正常なデータをINSERTした
場合の結果は、SQL_MODEによって違う。

    以下の条件によっても結果は違う事に注意

MyISAM
InnoDB
STRICT_ALL_TABLES
STRICT_TRANS_TABLES


mysql> SELECT @@session.sql_mode;
+---------------------+
| @@session.sql_mode |
+---------------------+
| NO_AUTO_CREATE_USER |
+---------------------+
1 row in set (0.01 sec)


mysql> CREATE TABLE string_test (
-> comment varchar(10)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc string_test;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into string_test values('THIS IS LONG VALUE FOR VARCHAR(10)'), ('THIS IS OK');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'comment' at row 1 |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from string_test;
+------------+
| comment |
+------------+
| THIS IS LO |
| THIS IS OK |
+------------+
2 rows in set (0.00 sec)

mysql>

string_test


mysql> SET @@session.sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| STRICT_ALL_TABLES |
+--------------------+
1 row in set (0.00 sec)

mysql>


mysql> insert into string_test values('THIS IS LONG VALUE FOR VARCHAR(10)'), ('THIS IS OK');
ERROR 1406 (22001): Data too long for column 'comment' at row 1
mysql> select * from string_test;
+------------+
| comment |
+------------+
| THIS IS LO |
| THIS IS OK |
+------------+
2 rows in set (0.00 sec)

mysql>

strict


FULLTEXTインデックスをシングルカラムに付けたテーブルを作成して検索してみる。
FULLTEXTは2つ作成し、それぞれ別々の列に作成。

single_column_ft

CREATE TABLE `T_FULLTEXT` (
id int UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title varchar(100) NOT NULL,
body varchar(45) NOT NULL,
FULLTEXT (title),
FULLTEXT (body)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;


INSERT INTO T_FULLTEXT (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. MSSQL','In the following database comparison ...'),
('DBMS and MYSQL','MYSQL is Open Source database ...'),
('MySQL Security','When configured properly, MySQL ...');

single_column_ft_result

————————————————————————-
[制限事項など] ↓
————————————————————————-

50_percent

この例で、「MySQL」という言葉は少なくとも 50 % の行で提示されているため、
検索結果は空になります。このように,この言葉は効果的にストップワードとして扱われます。
大きなデータセットでは、これは最も望ましい動作です : 自然言語のクエリは、1GB テーブルの
毎 2 行目は戻さないようになっています。
小さなデータセットにとっては、これはあまり望ましい動作ではありません。

テーブルの行の半分にマッチする言葉は、関連のある資料を見つけるのに適しません。
事実、関連のないものも大量に検出されるでしょう。これはインターネットのサーチエンジン
での検索と同じ論理です。このため、この言葉を含む行は、この特定のデータセットにおいて
語義の値が低く定められています。あるデータセットでは、提示された単語が 50% の
境界値を越えても、他のデータセットではまた異なります。

50% の境界値は、全文検索を行うとその重要性が明らかになります : テーブルを作成し、
テキストの 1 行または 2 行のみをインサートしてみると、テキストのすべての単語は
少なくとも 50% の行に存在することが分かります。そのため、検出結果は検出されません。
少なくとも 3 行以上をインサートするようにしてください。

確かめる為に”MYSQL”という言葉を含まないデータを全体の50%以上Insertしてみて、
再度”MYSQL”という文言を含むデータを検索してみた。結果は全て表示されるようになった。

50_percent_rule

* 単語のあるものは、全文検索では無視されます :
* 短すぎる単語は無視されます。全文検索で検出される言葉で最も短いものは 4 文字です。
* ストップワード リストにある言葉は無視されます。ストップワードは 「the」 や 「some」 などの常用語で、
語義の値はゼロとされています。すでに組み込まれているストップワードのリストがありますが、
ユーザ定義リストで書き換えることができます。

11.7. 全文検索関数
11.7.3. 全文ストップワード
11.7.5. 微調整 MySQL 全文検索


━ MYISAMのテーブルをコピーしてみる。 ━ 

mysql> use TEST
Database changed
mysql> LOCK TABLES MYSQLIMP READ;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH TABLE MYSQLIMP;
Query OK, 0 rows affected (0.00 sec)
mysql>

——- ここは別コンソール ——–
[root@colinux TEST]# cp MYSQLIMP.* /tmp
——————————————

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

myisam_table_copy

DBを停止しても良い場合は、一度サービスを停止してファイル
コピーすればOK (COLD BACKUP)
STOP —> COPY FILES —–> START


———————-「 特定DB内のテーブルをチェック 」———————–
++++ OK: MYISAM , INNODB
++++ NG: MEMORY

[root@colinux ~]# mysqlcheck --check DB01 -u root -p
Enter password:
DB01.TABLE007 OK
DB01.TABLE008 OK
DB01.TABLE009 OK
[root@colinux ~]#

[root@colinux ~]# mysqlcheck --check DB01 TABLE007 -u root -p
Enter password:
DB01.TABLE007 OK
[root@colinux ~]#

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

———————-「DB内のテーブルをOPTIMIZE 」———————–
++++ OK: MYISAM , INNODB
++++ NG: MEMORY

[root@colinux ~]# mysqlcheck --optimize --all-databases -u root -p
Enter password:
[root@colinux ~]#

DB01.TABLE007
note : Table does not support optimize, doing recreate + analyze instead
status : OK
DB01.TABLE008
note : Table does not support optimize, doing recreate + analyze instead
status : OK
DB01.TABLE009
note : Table does not support optimize, doing recreate + analyze instead
status : OK
DB02.animals Table is already up to date
DB02.animals02 Table is already up to date
TEST.Y2008_Y2009
note : The storage engine for the table doesn’t support optimize
TEST.Y2009 Table is already up to date
client_test_db.bug2247 Table is already up to date
client_test_db.foo_dfr Table is already up to date
client_test_db.my_demo_transaction

■INNODBに関しては、制限がある。
[root@colinux ~]# mysqlcheck --optimize DB01 TABLE007 -u root -p
Enter password:

DB01.TABLE007
note : Table does not support optimize, doing recreate + analyze instead
status : OK
[root@colinux ~]#

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

———————-「DB内のテーブルをREPAIR 」———————–
++++ OK: MYISAM
++++ NG: INNODB,MEMORY

■MYISAMは可能
[root@colinux ~]# mysqlcheck --repair TEST MYSQLIMP -u root -p
Enter password:

TEST.MYSQLIMP OK
[root@colinux ~]#

■INNODBはサポート外
[root@colinux ~]# mysqlcheck --repair DB01 -u root -p
Enter password:

DB01.TABLE007
note : The storage engine for the table doesn’t support repair
DB01.TABLE008
note : The storage engine for the table doesn’t support repair
DB01.TABLE009
note : The storage engine for the table doesn’t support repair
[root@colinux ~]#


MySQL はMyISAM とMEMORY テーブルにはテーブルレベルロックを使用し、
InnoDBテーブルには行レベルロックを使用します。
MYISAMのテーブルロックの状態を調査。

mysql> show status like ‘Table_locks_waited’;
+——————–+——-+
| Variable_name | Value |
+——————–+——-+
| Table_locks_waited | 10161 |
+——————–+——-+
1 row in set (0.00 sec)

mysql> show status like ‘Table_locks_immediate’;
+———————–+———-+
| Variable_name | Value |
+———————–+———-+
| Table_locks_immediate | 57905458 |
+———————–+———-+
1 row in set (0.00 sec)

mysql>

mysql> select 10161/57905458;
+—————-+
| 10161/57905458 |
+—————-+
| 0.0002 |
+—————-+
1 row in set (0.02 sec)

mysql>

特に問題は無さそうです。

MySQL のテーブルロック方法


MRG_MyISAMエンジンとしても知られているMERGE ストレージエンジンは、
一つの物として使用する事ができる同一のMyISAM テーブルの集まりです。
「同一の」というのは、全てのテーブルが同一のカラムとインデックス情報
を持つという意味です。カラムのリストされている順番が違っていたり、
カラムが完全に一致していなかったり、インデックスの順番が違っていたりすると
MyISAM テーブルをマージする事はできません。しかし、全てのMyISAM
テーブルはmyisampackで圧縮する事ができます。
AVG_ROW_LENGTH、 MAX_ROWS、または PACK_KEYS 等のようなテーブル
オプションの違いは問題ではありません。

MERGE テーブルを作成する時、MySQLはディスク上に二つファイルを作成します。
そのファイル名はテーブル名で始まり、ファイルタイプを指示する拡張子が付きます。
.frm ファイルはテーブルフォーマットを格納し、
.MRG ファイルは一つの物として使用されるべきテーブルの名前を含んでいます。

———————————————————————
-rw-rw—- 1 mysql mysql 8592 2009-02-24 10:06 Y2008.frm
-rw-rw—- 1 mysql mysql 60 2009-02-24 10:08 Y2008.MYD
-rw-rw—- 1 mysql mysql 3072 2009-02-24 10:08 Y2008.MYI
-rw-rw—- 1 mysql mysql 8592 2009-02-24 10:11 Y2008_Y2009.frm
-rw-rw—- 1 mysql mysql 32 2009-02-24 10:11 Y2008_Y2009.MRG
-rw-rw—- 1 mysql mysql 8592 2009-02-24 10:09 Y2009.frm
-rw-rw—- 1 mysql mysql 36 2009-02-24 10:22 Y2009.MYD
-rw-rw—- 1 mysql mysql 3072 2009-02-24 10:22 Y2009.MYI
———————————————————————


mysql> CREATE TABLE Y2008 (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `comment` varchar(50) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE UNIQUE INDEX uidx_y2008_comment ON Y2008 (comment);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> INSERT INTO Y2008 (comment) VALUES('comment1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Y2008 (comment) VALUES('comment2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Y2008 (comment) VALUES('comment3');
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE Y2009 (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `comment` varchar(50) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE UNIQUE INDEX uidx_y2009_comment ON Y2009 (comment);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE TABLE Y2008_Y2009 (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `comment` varchar(50) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) TYPE=MERGE UNION=(Y2008,Y2009) INSERT_METHOD=LAST;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE UNIQUE INDEX uidx_Yyyyy_comment ON Y2008_Y2009(comment);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>

create_merge

create_merge_status

mysql> select * from Y2008_Y2009;
+—-+———-+
| id | comment |
+—-+———-+
| 1 | comment1 |
| 2 | comment2 |
| 3 | comment3 |
+—-+———-+
3 rows in set (0.00 sec)

mysql>

create_merge_each_table

mysql> insert into Y2008_Y2009(comment) values(‘INSERT INTO MYISAM MERGE’);

mysql> select * from Y2008_Y2009;
+—-+————————–+
| id | comment |
+—-+————————–+
| 1 | comment1 |
| 2 | comment2 |
| 3 | comment3 |
| 4 | INSERT INTO MYISAM MERGE |
+—-+————————–+
4 rows in set (0.00 sec)

mysql>

create_merge_insert

mysql> select * from Y2008;
+—-+———-+
| id | comment |
+—-+———-+
| 1 | comment1 |
| 2 | comment2 |
| 3 | comment3 |
+—-+———-+
3 rows in set (0.00 sec)

mysql> select * from Y2009;
+—-+————————–+
| id | comment |
+—-+————————–+
| 4 | INSERT INTO MYISAM MERGE |
+—-+————————–+
1 row in set (0.00 sec)

mysql>

mrg


mysql> select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE from information_schema.tables where table_schema = 'TEST';
+--------------+-------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE |
+--------------+-------------+------------+------------+
| TEST | MEM | BASE TABLE | MEMORY |
| TEST | MEM2 | BASE TABLE | MEMORY |
| TEST | MEM3 | BASE TABLE | MEMORY |
| TEST | MYSQLIMP | BASE TABLE | MyISAM |
| TEST | T1 | BASE TABLE | InnoDB |
| TEST | T2 | BASE TABLE | InnoDB |
| TEST | Y2008 | BASE TABLE | MyISAM |
| TEST | Y2008_Y2009 | BASE TABLE | MRG_MYISAM |
| TEST | Y2009 | BASE TABLE | MyISAM |
+--------------+-------------+------------+------------+
9 rows in set (0.00 sec)

mrg_myisam

MERGEテーブル上では、SELECT、 DELETE、 UPDATE、そして INSERTを
利用する事ができます。MERGE テーブルにマップするMyISAM テーブル上に、
SELECT、 UPDATE、そして DELETE 権限を持たなければいけません。

MERGE テーブルをDROPする時、 MERGE 仕様だけが削除されます。
基礎となるテーブルは影響を受けません。

MERGE テーブルを作成するには、どの MyISAM テーブルを一つの物として利用
したいかを示すUNION=(list-of-tables) 条項を指定しなければいけません。
MERGE テーブルに、 UNION リストの最初か最後のテーブルに位置する
為の挿入が必要であれば、自由に INSERT_METHOD オプションを指定する事が
できます。テーブルの最初か最後に挿入されるように、FIRST か LAST値を
それぞれ使用してください。INSERT_METHOD オプションを指定しない場合や、
NOの値で指定した場合は、MERGEテーブルに行を挿入しようとしてもエラーが発生します。

※オプションとして、MERGE テーブルへの挿入が UNION リスト内の最初のテーブルと最後のテーブル
のどちらで行われるかを、INSERT_METHOD で指定できます。INSERT_METHOD を指定しなかった
場合、または NO を指定した場合は、MERGE テーブルに対するすべての INSERT コマンドでエラーが
返されます。

※MERGE テーブルの中で、いくつものMyISAMフィーチャーを利用する事はできません。
例えば、MERGE テーブル上でFULLTEXT インデックスを作成する事はできません。
(もちろん、基礎となるMyISAM テーブル上に FULLTEXT インデックスを作成する事はできますが、
全文検索で MERGE テーブルを検索する事はできません。


MySQL はMyISAM とMEMORY テーブルにはテーブルレベルロック
を使用し、InnoDBテーブルには行レベルロックを使用します。
Table_locks_waitedおよびTable_locks_immediateステータス変数を
チェックすることでシステム上でテーブルロック競合を分析できます。

mysql> SHOW STATUS LIKE ‘Table%’;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| Table_locks_immediate | 19 |
| Table_locks_waited | 0 |
+———————–+——-+
2 rows in set (0.01 sec)

mysql>

InnoDBは行ロックを使用する。InnoDBでは、SQLステートメントの
トランザクションの初めではなく、プロセス中に自動的に入手するため、
デッドロックが可能です。

行レベルロックの利点
* 多数のスレッドで異なる行をアクセスする際に、ロックコンフリクトが少なくてすみます。
* ロールバックの変更がすくなくてすみます。
*1つの行を長時間ロックすることが可能です。

http://dev.mysql.com/doc/refman/5.1/ja/internal-locking.html


■CHECK TABLE
http://dev.mysql.com/doc/refman/5.1/ja/check-table.html
CHECK TABLE はテーブルのエラーを確認します。CHECK TABLE は MyISAM、InnoDB、
そして ARCHIVE テーブルにのみ機能します。MySQL 5.1.9 から、CHECK は CSV
テーブルにも有効になりました。
MyISAM テーブルに対しては、キー統計もまた更新されます。

mysql> CHECK TABLE T01;
+———-+——-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+———-+——-+———-+———-+
| test.T01 | check | status | OK |
+———-+——-+———-+———-+
1 row in set (0.00 sec)

mysql>

■REPAIR TABLES
http://dev.mysql.com/doc/refman/5.1/ja/repair-table.html
REPAIR TABLE は破損された可能性があるテーブルを修復します。これはデフォルトで、
myisamchk –recover tbl_name と同じ効果を持っています。REPAIR TABLE は MyISAM と ARCHIVE
テーブルに対して機能します。MySQL 5.1.9 から、REPAIR は CSV テーブルにも有効になりました。

■ANALYZE TABLES (Update table’s index statistics)
http://dev.mysql.com/doc/refman/5.1/ja/analyze-table.html
ANALYZE TABLE はテーブルのキーの分布を分析、格納します。
分析の最中に、テーブルは MyISAM のリード ロックを利用してロックされます。
InnoDB には、テーブルは書き込みロックでロックされます。
このステートメントは MyISAM と InnoDB テーブルと共に機能します。
MyISAM テーブルにとっては、このステートメントは myisamchk –analyze を利用する事と同じです。

mysql> analyze table T01;
+———-+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+———-+———+———-+———-+
| test.T01 | analyze | status | OK |
+———-+———+———-+———-+
1 row in set (0.00 sec)

■OPTIMAIZE TABLES
http://dev.mysql.com/doc/refman/5.1/ja/optimize-table.html
しテーブルの大部分を削除したり、変数長行で何箇所もテーブルを
変更した場合は(VARCHAR、VARBINARY、BLOB、または TEXT カラムを持つテーブル)、
OPTIMIZE TABLE を利用しなければいけません。
OPTIMIZE TABLE は MyISAM と InnoDB テーブルに対して のみ 機能します。
これは、NDB ディスク データ テーブルを含むその他のストレージ エンジンを
利用して作成されたテーブルには機能 しません。

—– MYISAM —–
mysql> OPTIMIZE TABLE T_TEST;
+—————-+———-+———-+—————————–+
| Table | Op | Msg_type | Msg_text |
+—————-+———-+———-+—————————–+
| WINDOWS.T_TEST | optimize | status | Table is already up to date |
+—————-+———-+———-+—————————–+
1 row in set (0.00 sec)

—– INNODB —–
mysql> OPTIMIZE TABLE T01;
+———-+———-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+———-+———-+———-+———-+
| test.T01 | optimize | status | OK |
+———-+———-+———-+———-+
1 row in set (0.05 sec)

mysql>

■mysqlcheck
http://dev.mysql.com/doc/refman/5.1/ja/mysqlcheck.html
mysqlcheckのファンクションはmyisamchkと似ていますが、
作動方法が異なります。実質的な作動方法の違いは、mysqlcheckはmysqld
サーバが作動中の時に使用されなければいけません。
myisamchkはこのサーバが作動していない時に使用品kればいけません。
mysqlcheckを使用することの利点は、テーブルのチェックや修復時に
サーバを停止させなくてすむことです。
mysqlcheckはSQLステートメントCHECK TABLE、REPAIR TABLE、ANALYZE TABLE、
そしてOPTIMIZE TABLEをユーザにとって便利な方法で使用します。
実行したいオペレーションに対してどのステートメントを使用するか決定し、
実行のためサーバにステートメントを送信します。

[root@localhost ~]$ mysqlcheck –analyze test T01 -u root -ppassword
test.T01 OK
[root@localhost ~]$

—– MYISAM —–
[root@localhost WINDOWS]# mysqlcheck –optimize WINDOWS T_TEST -u root -ppassword
WINDOWS.T_TEST Table is already up to date
[root@localhost WINDOWS]#

[root@localhost WINDOWS]# mysqlcheck –repair WINDOWS T_TEST -u root -ppassword
WINDOWS.T_TEST OK
[root@localhost WINDOWS]#

[root@localhost WINDOWS]# mysqlcheck –repair WINDOWS -u root -ppassword
WINDOWS.Brokerage OK
WINDOWS.Channel OK
WINDOWS.ConfidenceTerm OK
WINDOWS.DetailCompany OK
WINDOWS.DetailForeignExchange OK

—– INNODB —–
[root@localhost WINDOWS]# mysqlcheck –optimize test T01 -u root -ppassword
test.T01 OK
[root@localhost WINDOWS]#

※ optimizeはmemory tableに利用出来ません。

[root@localhost WINDOWS]# mysqlcheck –repair test T01 -u root -ppassword
test.T01
note : The storage engine for the table doesn’t support repair

■myisamchk (MYISAM ONLY) テーブルにアクセスが無い事を確認
http://dev.mysql.com/doc/refman/5.1/ja/myisamchk.html
myisamchk ユーティリティはユーザのデータベース テーブルの情報を収集し、
チェック、修復、もしくは最適化します。myisamchk は MyISAM
テーブルとともに作動します(データやインデックスを記憶するための
.MYD や .MYI テーブル)。

[root@localhost WINDOWS]# myisamchk –analyze T_TEST.MYI
Checking MyISAM file: T_TEST.MYI
Data records: 1 Deleted blocks: 0
– check file-size
– check record delete-chain
– check key delete-chain
– check index reference
– check record links
[root@localhost WINDOWS]#

[root@localhost WINDOWS]# myisamchk –recover T_TEST
– recovering (with keycache) MyISAM-table ‘T_TEST’
Data records: 1
[root@localhost WINDOWS]#

※「誰も利用していない状態にしておく」
※「サービス停止をした方が良い」
※「オンラインの場合は、メンテナンス対象テーブルをロックする」

『起動時にMYISAMのテーブルを自動的にRecoverする為のオプション』
起動方法① mysqld –myisam-recover=QUICK
オプションファイル(my.cnf)に記入② –myisam-recover