日付時刻関数の動作確認

mysql> select SYSDATE(),NOW(),CURRENT_DATE,CURRENT_TIME,CURDATE(),CURTIME(),UTC_DATE(),
-> UTC_TIME(),UTC_TIMESTAMP(),CURRENT_TIMESTAMP(),CURRENT_TIME(),CURRENT_DATE()\G
*************************** 1. row ***************************
SYSDATE(): 2009-04-28 12:43:27
NOW(): 2009-04-28 12:43:27
CURRENT_DATE: 2009-04-28
CURRENT_TIME: 12:43:27
CURDATE(): 2009-04-28
CURTIME(): 12:43:27
UTC_DATE(): 2009-04-28
UTC_TIME(): 03:43:27
UTC_TIMESTAMP(): 2009-04-28 03:43:27
CURRENT_TIMESTAMP(): 2009-04-28 12:43:27
CURRENT_TIME(): 12:43:27
CURRENT_DATE(): 2009-04-28
1 row in set (0.00 sec)

mysql> select DAYOFMONTH(CURRENT_DATE);
+--------------------------+
| DAYOFMONTH(CURRENT_DATE) |
+--------------------------+
| 28 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(CURRENT_DATE,INTERVAL 10 DAY);
+----------------------------------------+
| DATE_ADD(CURRENT_DATE,INTERVAL 10 DAY) |
+----------------------------------------+
| 2009-05-08 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ADDDATE(CURRENT_DATE,INTERVAL 10 DAY);
+---------------------------------------+
| ADDDATE(CURRENT_DATE,INTERVAL 10 DAY) |
+---------------------------------------+
| 2009-05-08 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2009-04-28 |
+------------+
1 row in set (0.00 sec)

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

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 12:51:30 |
+-----------+
1 row in set (0.00 sec)

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

mysql>

mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2009-04-28 12:53:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE(now());
+-------------+
| DATE(now()) |
+-------------+
| 2009-04-28 |
+-------------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT DATEDIFF(CURDATE(),'2009-06-10');
+----------------------------------+
| DATEDIFF(CURDATE(),'2009-06-10') |
+----------------------------------+
| -43 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF(CURDATE(),'2008-04-28');
+----------------------------------+
| DATEDIFF(CURDATE(),'2008-04-28') |
+----------------------------------+
| 365 |
+----------------------------------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT DATEDIFF(CURDATE(),'2009-06-10');
+----------------------------------+
| DATEDIFF(CURDATE(),'2009-06-10') |
+----------------------------------+
| -43 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF(CURDATE(),'2008-04-28');
+----------------------------------+
| DATEDIFF(CURDATE(),'2008-04-28') |
+----------------------------------+
| 365 |
+----------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT DAYNAME(CURDATE());
+--------------------+
| DAYNAME(CURDATE()) |
+--------------------+
| Tuesday |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFMONTH(CURDATE());
+-----------------------+
| DAYOFMONTH(CURDATE()) |
+-----------------------+
| 28 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFWEEK(CURDATE());
+----------------------+
| DAYOFWEEK(CURDATE()) |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT SECOND(CURTIME());
+-------------------+
| SECOND(CURTIME()) |
+-------------------+
| 57 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFYEAR(CURDATE());
+----------------------+
| DAYOFYEAR(CURDATE()) |
+----------------------+
| 118 |
+----------------------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT LAST_DAY(CURDATE());
+---------------------+
| LAST_DAY(CURDATE()) |
+---------------------+
| 2009-04-30 |
+---------------------+
1 row in set (0.00 sec)

=====================================================================================
SYSDATE() は、それが実行された時間を戻します。これは NOW() の動作によって異なり、
ステートメントが実行を開始する時間を示す定数時間を戻します。
( ストアド ルーチンまたはトリガ内で、NOW() はルーチンまたは
トリガ文が実行を開始する時間を戻します。)
=====================================================================================

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW() | SLEEP(2) | NOW() |
+---------------------+----------+---------------------+
| 2009-04-28 13:06:17 | 0 | 2009-04-28 13:06:17 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE() | SLEEP(2) | SYSDATE() |
+---------------------+----------+---------------------+
| 2009-04-28 13:06:23 | 0 | 2009-04-28 13:06:25 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

mysql>

11.5. 日付時刻関数


mysql サーバは、様々なシステム変数を保有し、その変数をどのように設定したかを示します 。
それぞれのシステム変数にはデフォルト値があります。システム変数はサーバ起動時に、
コマンドラインまたはオプション ファイルなどを使用してセットできます。大抵の場合、
SETコマンドを使用して実行中のサーバで動的に変更できます。

サーバには 2 種類のシステム変数があります。グローバル変数はサーバの
全体的なオペレーションに影響し、セッション変数はそれぞれのクライアント接続
でのオペレーションに影響します。与えられた変数はグローバルとセッション、
両方の値を持つこともあります。グローバルおよびセッション変数は次のように関係しています。

サーバが起動するとき、すべてのグローバル変数をデフォルト値に初期化する。
このデフォルト値はコマンド ラインまたはオプション ファイルなどで指定できる。

サーバにはクライアントが接続するセッション変数の組み合わせがある。
クライアントのセッション変数は、グローバル変数に呼応するカレント値を使用して
接続タイムで初期化する。たとえば、クライアントの SQL モードは、セッション
sql_mode 値で制御し、クライアントが sql_mode のグローバル値に接続するときに初期化する。

システム変数はサーバ起動時にコマンドラインまたはオプションファイルを使用して
グローバル設定できます。起動オプションを使用して値を設定するときは、数値を使用し、
値には K (キロバイト)、M (メガバイト)、G (ギガバイト) などのサフィックスで与えます
(大文字あるは小文字)。これらは、1024、10242 または 10243 の倍数を示します。
これにより、次のコマンドは、クエリ キャッシュ サイズが 16 メガバイト、最大パケット
サイズが 1 ギガバイトでサーバが起動することを示します。

===================================
起動時のオプション
===================================

コマンドラインまたはオプション

mysqld --query_cache_size=16M --max_allowed_packet=1G

オプション ファイル

[mysqld]
query_cache_size=16M
max_allowed_packet=1G

===================================
稼動時
===================================

変数がグローバルであることを明示するには、GLOBAL または @@global. で名前を先行する。
グローバル変数を設定するには SUPER 権限が必要。

変数がセッションであることを明示するには、SESSION、@@session.、@@ などで名前を先行する。
セッション値の設定には特別の権限は不要であるが、クライアントだけがそのセッション変数を変更
できる。別のクライアントからはできない。

LOCAL および @@local. は SESSION と @@session. のシノニム。.

修飾子がない場合は、SET でセッション変数を変更する。

システム変数に値を SET で指定するときには、変数にスフィックス文字は使用しません。
(起動オプションのときは使用する。) ただし、この値は例示のようにプログラミング形式にできます。

SET sort_buffer_size = 10 * 1024 * 1024;


mysql> SHOW VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

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

mysql> SHOW VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

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

mysql> SHOW VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.01 sec)

===================================
稼動時のみ
===================================
time_zone

現在のタイムゾーン。この変数はクライアント接続毎にタイム ゾーンを初期化する。
デオフォルトは ‘SYSTEM’ 、つまり system_time_zone「の値を使う」 ということ。
サーバ起動時に –default-time-zone オプションで明示的に指定できる。

system_time_zone

サーバ システムのタイム ゾーン。サーバが起動するときは、マシンのデフォルトタイムゾーン
を継承する。これは サーバを起動したユーザアカウントの環境やスタートアップ スクリプトの
オプションなどで変更可能。値は system_time_zone を設定する。通常、タイム ゾーンは
TZ 環境変数で指定する。または mysqld_safe スクリプトでの –timezone オプションでも指定可能。

open_files_limit

mysqld が開けるオペレーティング システムのファイル数。これはシステムで指定されている
実際の値であり、起動時のパラメータとして –open-files-limit オプションで mysqld または
mysqld_safe に指定したものとは異なる場合がある。MySQL がオープンファイル数を
変更できないシステムでは 0 になる。

などがあります

open
4.2.4. システム変数の使用

4.2.4.2. 動的システム変数


MySQL サーバは様々な MySQL モードで動作し、モードをクライアント毎に別々に
設定できます。この機能により、各アプリケーションが それぞれの要件に応じて
サーバのオペレーティングモードを指定することができるようになります。

モードとは、どの SQL シンタックスを MySQL がサポートし、どのようなデータ バリデーション
チェックを実行するべきかを定義するものです。これにいより、異なる環境で MySQL を使用
したり、MySQL を他のデータベース サーバと併用したりするのが容易になります。

デフォルトの SQL モードを指定するには、–sql-mode=”modes” オプションで mysqld を立ち上げます。
または、Unix では my.cnf に、Window では my.iniに、sql-mode=”modes” を記述します。
modes とは、カンマ (「,」) で区切られた各モードのリストです。デフォルトは空の状態で、
モード設定がないことを意味します。

SET [GLOBAL|SESSION] sql_mode=’modes’

GLOBAL 値を指定するには、SUPER 権限が必要になり、また、それ以降に接続する
すべてのクライアント操作に影響します。

(例)
NO_AUTO_CREATE_USER

パスワードを指定しないとGRANT 文で新規ユーザを作成出来ないようにする。

no_au

4.2.6. SQL モード


MYSQLで他のシステムからアクセスできないように起動するには、
–skip-networkを指定して起動すればTCP/IPが無効になりアクセス
出来なくなる。TCP/IIPが無効な場合でもUnix Domain Socket(Unix-like System)
かnamed pipe、shareped memory(Windows System)を利用して接続は可能。
しかしそれらも無効になっている場合は、MYSQLへの接続は出来ない。
もしTCP/IPしか無くてローカルからのみ接続させたい場合は、オプションファイル
(my.cnf)に以下の設定を入れる。NICが複数ある場合も、こちらの設定で
MYSQLがListenするインターフェースを設定可能。(LAN側NICのIPなど)

[mysqld]
bind-address=127.0.0.1


SPは直接テーブルに権限を付与したくない場合やその他制限をかける場合など
の他パフォーマンスを考慮したりする場合に利用される。直接テーブルにSELECT権限を
付与せずともSPに対するEXEC権限のみで必要なデータを参照出来る。


mysql> delimiter //
mysql> CREATE PROCEDURE SP_TABLE101()
-> BEGIN
-> SELECT title,body FROM TABLE101;
-> END
-> //
Query OK, 0 rows affected (0.14 sec)

SQLクエリーの最後は「;」を付けます、ストアドプロシージャを記述する場合、
スクリプト中のセンテンスの終了にも「;」を使用するため、区別のために
デリミタ(SQLクエリーの区切り文字)を「//」のようにして変更します。
デリミタは、「|」、「/」などで問題ありません。

sp


mysql> delimiter /
mysql> CREATE PROCEDURE SP_TABLE101_2(OUT PARAMETER1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO PARAMETER1 FROM TABLE101;
-> END
-> /
Query OK, 0 rows affected (0.00 sec)

ここでは、SELECT * INTOクエリーを使用しています。
MySQLはSELECT …… INTO TABLEクエリーに対応しておらず、
INSERT INTO …… TABLEクエリーを代わりに使用しますが、
格納先が変数の場合はSELECT …… INTO 変数クエリーが使用可能。

sp2

——————–サーバーに格納されているSP確認——————–
mysql> SHOW PROCEDURE STATUS;
show_procedure


mysql> select SPECIFIC_NAME,ROUTINE_TYPE,ROUTINE_SCHEMA,SECURITY_TYPE from information_schema.ROUTINES;
+---------------+--------------+----------------+---------------+
| SPECIFIC_NAME | ROUTINE_TYPE | ROUTINE_SCHEMA | SECURITY_TYPE |
+---------------+--------------+----------------+---------------+
| SP_TABLE101 | PROCEDURE | DB01 | DEFINER |
| SP_TABLE101_2 | PROCEDURE | DB01 | DEFINER |
+---------------+--------------+----------------+---------------+
2 rows in set (0.00 sec)

sp3

SQL SECURITY特徴はルーチンを生成させるユーザあるいはそれを呼び出すユーザの許可
を使って、ルーチンが実行されるべきか否かを明示するために使うことができます。
そのデフォルトはDEFINERです。この特徴はSQL:2003の新機能です。その生成者や利用者は、
ルーチンが属するデータベースにアクセスできる許可を取得していなければなりません。
ルーチンを実行することができるEXECUTE権限を持つ必要があります。
この権限を持たなければいけないユーザは、SQL SECURITY機能を設定する方法によって、
規定者か利用者のいずれかになります。

オプションのDEFINER節はSQL SECURITY DEFINER特徴を有するルーチンに対して、
実行中にアクセス権限をチェックする時使用すべきMySQLアカウントを特定します。
DEFINER節はMySQL 5.1.8.で追加されました。


17.2.1. CREATE PROCEDUREおよびCREATE FUNCTION 構文


DBの基本として、ORACLE、MS SQL、MYSQLもI/Oは分散させた方が
パフォーマンスが向上する。アプリケーションの改良に比べたらパフォーマンスなどは
それほど変わらないかもしれないが、I/O分散してインデックスをきちんと見直したら
バッチ処理なども1/10の時間で終わることもある。I/O分散はお勧めです。
但しI/O分散は物理的なディスクも分けないとあまり意味は無い。
ディスクを扱うディスクヘッドはディスクに1つなので。(RAIDも同じ)
データはランダム、ログはSequentialなのでI/O自体が違う。


mysql> CREATE TABLE `TABLE_FILEPATH2`
    -> (
    -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> `comment` varchar(45) NOT NULL,
    -> PRIMARY KEY (`id`),
    -> KEY `idx_id` (`id`)
    -> )
    -> ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
    -> DATA DIRECTORY = '/home/mysql/data2/'
    -> INDEX DIRECTORY = '/home/mysql/index2/';
Query OK, 0 rows affected (0.02 sec)

mysql> show create table TABLE_FILEPATH2\G
*************************** 1. row ***************************
       Table: TABLE_FILEPATH2
Create Table: CREATE TABLE `TABLE_FILEPATH2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `comment` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 DATA DIRECTORY='/home/mysql/data2/' INDEX DIRECTORY='/home/mysql/index2/'
1 row in set (0.01 sec)

mysql> insert into TABLE_FILEPATH2(comment) values('Insert Data to I/O separate 0001');

tables_myisam_data_index

EXPLAINでINDEXの利用も問題ないことを確認

index_explain

DATA DIRECTORY = ‘/home/mysql/data2/’ の確認

data_dir

INDEX DIRECTORY = ‘/home/mysql/index2/’ の確認

index_file1

※DATA DIRECTORY と INDEX DIRECTORY
Windows では、CREATE TABLE の DATA DIRECTORY オプションと INDEX DIRECTORY
オプションはシンボリックリンクをサポートしていないため無視される。

※Format file(*.frm)は常にディフォルトのDATAディレクトリーに作成される。
DATAファイルとINDEXファイルはオプションで何処にでも配置することが出来る。

※ファイルのパスは、常にフルパスで指定する必要がある。

※–skip-symbolic-linksを指定してMYSQLを起動している場合は利用出来ません。

実際には、ディフォルトのデータディレクトリー(/usr/local/mysq/data)は変更出来ないようで
DATA DIRECTORY と INDEX DIRECTORYを指定すると自動でシンボリックリンクを作成
してくれるようです。なのでシンボリックリンクに対応していないOSでは無理なようです。

ln


15.2.5. サブ分割


5.4.1.3. レプリケーションと DIRECTORY 構文


12.1.8. CREATE TABLE 構文


MYSQLは他のDBと同じようにI/O分散する事でパフォーマンスを大きく向上させることが出来ます。
基本的には、物理的に別のディスク(ディスクヘッドが別)を準備する事でI/O待ちを減らすことが
可能です。
ファイルをそれぞれの場所に分割する事で、I/O分散だけではなくオリジナルディスク領域の
ディスク容量を空ける事も可能です。

以下のファイルはアクセス方法が違う事が多いので分けましょう
Log File     (Sequentialアクセス)
Database File  (Randumアクセス)
Temporary Files (Randum and Sequentialアクセスなど)

=====================================
Windowsでのデータファイルパスの変更方法
=====================================
1. サーバーをSTOPさせる
2. データフォルダー移動、C:\Program Files\MYSQL\MYSQL Server 5.xx\data\移動用DBフォルダー
をG:\MYSQL\DATA\移動先DBフォルダー (名前は変更しておいた方が分かり易い)などへ移動
3. C:\Program Files\MYSQL\MYSQL Server 5.xx\data\ に 「移動用DBフォルダー.sym」という
   ファイル名でテキストファイルを作成する
4. 「移動用DBフォルダー.sym」を編集し、「G:\MYSQL\DATA\移動先DBフォルダー」を記入
5. サーバーをSTARTさせる。

=====================================
Linuxでのデータファイルパスの変更方法
=====================================
1. サーバーをSTOPさせる
2. データフォルダー移動、/usr/local/mysql/data/移動用DBフォルダー
を/home/mysql/data/移動先DBフォルダー (名前は変更しておいた方が分かり易い)などへ移動
3. /usr/local/mysql/data/ に 「移動用DBフォルダー」用のシンボリックリンク作成
   [root@colinux data]#ln -s /home/mysql/data/移動先DBフォルダー 移動用DBフォルダー
4. サーバーをSTARTさせる。

——————- 【Linuxでの例】——————-

1. STOP MYSQL Server
2. データベースフォルダーの移動
move_1

move_2

3. シンボリックリンクの作成
move_3

move_4

4. START MYSQL Server (起動後はデータアクセス確認)
move_5

参考URL

6.6.1.3. 上のデータベースに対するシンボリックリンクの使用

6.6.1. シンボリックリンクの使用

6.6.1.1. Unix 上のデータベースに対するシンボリックリンクの使用


6.6.1.2. Unix 上のテーブルに対するシンボリックリンクの使用


サーバーとクライアントの間で利用されるバッファー設定あれこれ

mysql> show variables like ‘%buffer%’;
+————————-+———-+
| Variable_name | Value |
+————————-+———-+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 16777216 |
| innodb_log_buffer_size | 33554432 |
| join_buffer_size | 131072 |
| key_buffer_size | 16384 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 2048 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 65536 |
| sql_buffer_result | OFF |
+————————-+———-+
12 rows in set (0.03 sec)

server-client

以下の値は、各クライアントに割り当てられているSORTバッファーサイズです。
sort_buffer_size

net_buffer_lengthとmax_allowed_packetは、サーバーとクライアントでデータをやり取りするために
利用されるバッファーです。最初、net_buffer_lengthのサイズでデータのやりとりが行われ
必要に応じてmax_allowed_packetまで拡張されます。
netbuffer


NUMBER OF CONNECTION ATTENPT – NUMBER OF UNSUCCESSFUL CONNECTION
= 成功した接続

connections

接続ATTEMPT

+—————+—————-+
| Variable_name | Value |
+—————+—————-+
| Connections  | 8 |
+—————+—————-+

接続失敗

+——————+—————–+
| Variable_name | Value |
+——————+—————–+
| Aborted_connects | 2 |
+——————+—————–+

8 - 2 = 6つの接続が成功

あまりにもABORTが多い場合は、ネットワーク、権限まわりを確認したほうが良いかも。

接続が不適切にCLOSEされたりKILLされたCONNECTION数

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

mysql>


Connections – Aborted_connects – Aborted_clients = 接続が適切にCLOSEされた数

※ アプリケーションなどの接続がきちんとCLOSEしているか確認した方が良いかな。


SELECTのパフォーマンスに関係ある3つのシステム変数

sort_buffer_size ——> Order by , Grop byのパフォーマンス
join_buffer_size ——> JOINのパフォーマンス …. MYSQLのパフォーマンス
key_buffer_size ——> index関連するステートメント(MYISAM固有)

buffer_size

———————————————————-
おまけ: テイキメンテナンスを忘れずに
———————————————————-
※ mysqlcheck -u root -p –auto-repair –check –optimize –all-databases

※ Query Cache はSelectのスピードアップに大切です。

mysql> show variables like ‘query_cache%’;
+——————————+———+
| Variable_name | Value |
+——————————+———+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+——————————+———+
5 rows in set (0.01 sec)

mysql>

——————————————————————————————————-
Key Cache Efficiency = 1 – (KEY_READS / KEY_READ_REQUESTS)
——————————————————————————————————-

select 1 – ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = ‘KEY_READS’)
/ (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = ‘KEY_READ_REQUESTS’))
as ‘Key cache efficiency’;

key_cache_efficiency

もしkey_buffer_sizeが小さくて、メモリーがあまっていたら以下のようにメモリーを
key_bufferに与えて下さい。

mysql> set global key_buffer_size = 5 * 1024 * 1024;

key_buffer_size