MySQL的表的存储引擎
笺注：这是在 [url=http://blog.zhuohua.store/viewthread.php?tid=331&extra=page%3D1]CentOS6.9编译安装Zabbix4.4.5[/url] 的基础上进行的。查看当前MySQL的版本：
mysql> select version();
+------------+
| version() |
+------------+
| [color=Purple]5.5.48[/color]-log |
+------------+
1 row in set (0.00 sec)
mysql>
查看当前MySQL已提供什么存储引擎：
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| [color=DarkRed]MyISAM[/color] |[color=Purple] YES | MyISAM storage engine | NO | NO | NO [/color] |
| [color=Blue]InnoDB [/color] | [color=Purple]DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES[/color] |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)
mysql>
注释：
支持 [color=DarkRed]MyISAM [/color]
默认使用的是 [color=Blue]InnoDB [/color]
InnoDB支持事务，MyISAM不支持；
InnoDB支持行级锁，MyISAM不支持；
InnoDB支持外键，MyISAM不支持；
存储引擎一般选择[color=Blue] InnoDB[/color]
看当前MySQL默认使用的存储引擎：
mysql> show variables like '%default_storage_engine%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | [color=Blue]InnoDB[/color] |
+------------------------+--------+
1 row in set (0.00 sec)
mysql>
############
查看某个表使用了什么存储引擎：
use zabbix;
create table t1(id int);
show create table [color=Blue]t1[/color];
[attach]19422[/attach]
############
创建表的时候可以指定使用什么存储引擎：
create table [color=Blue]zabbix.t2[/color](id int) engine=[color=DarkRed]myisam[/color];
show create table [color=Blue]zabbix.t2[/color];
[attach]19423[/attach]
############
############
MySQL的主配置文件：（初始状态）
[root@Zabbix_server_01 ~]# cat /etc/my.cnf |grep -v "^$"
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
[color=DarkRed][mysqld][/color]
port = 3306
socket = /tmp/mysql.sock
datadir = /usr/local/mysql/var
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
[color=Red]myisam_sort_buffer_size = 8M[/color]
thread_cache_size = 8
query_cache_size = 8M
tmp_table_size = 16M
#skip-networking
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
default_storage_engine = [color=Blue]InnoDB[/color]
[color=Purple]innodb_data_home_dir = /usr/local/mysql/var
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50[/color]
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[color=Red][myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M[/color]
[mysqlhotcopy]
interactive-timeout
[root@Zabbix_server_01 ~]#
关于默认存储引擎、存储引擎 InnoDB的配置：
[root@Zabbix_server_01 ~]# grep -A9 "default_storage_engine" /etc/my.cnf
[color=Purple]default_storage_engine = InnoDB
innodb_data_home_dir = /usr/local/mysql/var
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50[/color]
[root@Zabbix_server_01 ~]#
注释：这些代码是在[color=DarkRed][mysqld][/color]下面。
相关文章：
[url=http://blog.zhuohua.store/viewthread.php?tid=234&page=1&extra=#pid237]MySQL的字符集[/url]
页:
[1]