MySQLストレージエンジン(二)
41471 ワード
文書ディレクトリ
一.MyISAMストレージエンジン(下)
1.MyISAMがまだ使用されている理由
MyISAMの利点も、存在する理由ではない2.MyISAMファイル構成
frm
表構造ファイルMYI
インデックスファイルMYD
データファイルMYI
のリーフノードは、MYD
のデータページ3. myisamchk
myisamchk
MYDファイルをスキャンすることによってMYIファイルを再構築する.MYDファイルのレコードに問題がある場合は、そのレコードはスキップされます.二.Memoryストレージエンジン
1.Memoryの紹介
Memory (Cache)
、性能上RedisとMemcahced
は、内部ソート動作に関するテンポラリ・テーブルの場合に使用されるmax_heap_table_size
使用メモリのサイズを決定し、デフォルトでは16M
tmpdir
)mysql> show global status like "%tmp%tables";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 | -- , , ,
| Created_tmp_tables | 4 | --
+-------------------------+-------+
2 rows in set (0.00 sec)
mysql> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir | /tmp | -- memory
+---------------+-------+
1 row in set (0.00 sec)
mysql> show create table User\G
*************************** 1. row ***************************
Table: User
Create Table: CREATE TABLE `User` (
`id` int(11) NOT NULL,
`name` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`) USING HASH -- USING HASH, hash
) ENGINE=MEMORY DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
3.Memoryの物理的特性
三.CSVストレージエンジン
1.CSV紹介
2.CSVファイル構成
frm
表構造CSV
データファイルCSM
メタデータ情報2.CSV特性
NOT NULL
属性四.Federatedストレージエンジン
1.Federatedの紹介
my.cnf
の[mysqld]
ラベルの下にfederated
のFederatedX
は2.Federated構文
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
CONNECTION='mysql://username:password@hostname:port/database/tablename'
--
--
--
CREATE TABLE `T1` (
`A` VARCHAR(100),
UNIQUE KEY (`A` (30))
) ENGINE=FEDERATED
CONNECTION='MYSQL://david:[email protected]:3306/TEST/T1';
3.Federatedテスト
--
-- mysql1
--
mysql> show variables like "port";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 | -- mysql1
+---------------+-------+
1 row in set (0.00 sec)
mysql> create database burn;
Query OK, 1 row affected (0.00 sec)
mysql> use burn;
Database changed
mysql> create table book (
-> id int not null auto_increment,
-> name varchar(128) not null,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.20 sec)
mysql> insert into book values(1, "book1");
Query OK, 1 row affected (0.02 sec)
mysql> select * from book;
+----+-------+
| id | name |
+----+-------+
| 1 | book1 |
+----+-------+
1 row in set (0.00 sec)
mysql> create user 'burn'@'127.0.0.1' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant select on burn.* to 'burn'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'burn'@'127.0.0.1';
+------------------------------------------------+
| Grants for [email protected] |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'burn'@'127.0.0.1' |
| GRANT SELECT ON `burn`.* TO 'burn'@'127.0.0.1' |
+------------------------------------------------+
2 rows in set (0.00 sec)
--
-- mysql2 Federated
--
mysql> show variables like "port";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3307 | -- msyql2
+---------------+-------+
1 row in set (0.01 sec)
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
--
-- federated
--
9 rows in set (0.00 sec)
#
# [mysqld2] federated
#
[root@MyServer ~]> cat /etc/my.cnf
# ... ...
[mysqld2]
federated # , federated
# ... ...
[root@MyServer ~]> mysqld_multi stop 2
[root@MyServer ~]> mysqld_multi start 2 #
mysql> show variables like "port";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3307 | -- msyql2
+---------------+-------+
1 row in set (0.01 sec)
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
--
-- federated
--
mysql> create database federated_test;
Query OK, 1 row affected (0.00 sec)
mysql> use federated_test;
Database changed
mysql> create table federated_table_1 (
-> id int not null auto_increment,
-> name varchar(128) not null,
-> primary key(id)
-> ) engine=federated
-> connection='mysql://burn:[email protected]:3306/burn/book';
Query OK, 0 rows affected (0.04 sec)
mysql> select * from federated_table_1;
+----+-------+
| id | name |
+----+-------+
| 1 | book1 | -- mysqld1 。
+----+-------+
1 row in set (0.00 sec)
--
-- select , insert
--
mysql> insert into federated_table_1 values(2, "book2");
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1142: INSERT command denied to user 'burn'@'127.0.0.1' for table 'book'' from FEDERATED