mysql存储引擎

mysql的存储引擎 = 车的发动机

作为可插拔式的组件提供
MySQL服务软件自带的功能程序，处理表的处理器
不同的存储引擎，有不同的功能和数据存储方式

默认的存储引擎
MySQL5.0/5.1 ---> MyISAM
MySQL5.5/5.6 ---> InnoDB
#####################################################################################
# mysql 有关mysql的命令，摁2次tab键可以看到
mysql mysqld mysql_secure_installation
mysqladmin mysqld-debug mysqlshow
mysqlbinlog mysqld_pre_systemd mysqlslap
mysqlcheck mysqldump mysql_ssl_rsa_setup
mysql_client_test mysqldumpslow mysqltest
mysql_client_test_embedded mysqlimport mysqltest_embedded
mysql_config mysql_install_db mysql_tzinfo_to_sql
mysql_config-64 mysql_plugin mysql_upgrade
mysql_config_editor mysqlpump mysqlxtest

# which mysql 执行mysql这个命令的程序在哪里
/usr/bin/mysql

# rpm -qf /usr/bin/mysql 查看一个文件是由哪个包安装产生的
mysql-community-client-5.7.17-1.el7.x86_64 社区(community)
###########################################################################################
mysql> show engines; 查看可用的存储引擎,engine(发动机)
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

Transactions事务
foreign keys外键
row-level locking行级锁

最常用的存储引擎：InnoDB和MyISAM

修改表的存储引擎
建表时手动指定
未指定时，使用默认存储引擎
show create table 表名\G； (可查看某个表的默认存储引擎是什么)

mysql> show engines; 查看默认的存储引擎(Support这一列中是DEFAULT的)，发现是InnoDB
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

# systemctl stop mysqld
# vim /etc/my.cnf

[mysqld]
default-storage-engine=myisam 添加这一行，修改默认的存储引擎
validate_password_policy=0
validate_password_length=6

# systemctl start mysqld

mysql> show engines; 查看默认的存储引擎(Support这一列中是DEFAULT的)，发现已经更改成功，变成了MyISAM
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

mysql> use db3;
mysql> create table t1(name char(3)); 创建表t1，没有指定存储引擎
mysql> show create table t1; 看表t1的存储引擎，可以发现是ENGINE=MyISAM
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`name` char(3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+

mysql> create table t2(age tinyint)engine=innodb; 创建表t2，指定存储引擎为innodb
mysql> show create table t2; 看表t2的存储引擎，可以发现是ENGINE=InnoDB
+-------+--------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------+

mysql> create table t3(mail char(30))engine=memory; 创建表t3，指定存储引擎为memory
mysql> show create table t3; 看表t3的存储引擎，可以发现是ENGINE=MEMORY
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE `t3` (
`mail` char(30) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------+
#################################################################################
# cd /var/lib/mysql/db3
# ls
db.opt t1.frm t1.MYD t1.MYI t2.frm t2.ibd t3.frm
#################################################################################
myisam存储引擎
特点：
支持表级锁
不支持事务、事务回滚、外键

相关的3个表文件 (t1.frm t1.MYD t1.MYI)
表名.frm .frm结尾的文件存表结构。也就是执行desc t1这个命令所看到的结果。
表名.MYI .MYI存的是索引(index)
表名.MYD .MYD存的是数据(data)

innodb存储引擎
特点：
支持行级锁
支持事务、事务回滚、外键

相关的5个表文件 (t2.frm t2.ibd 和3个事务日志文件)
表名.frm .frm结尾的文件存表结构。
表名.ibd .ibd存数据和索引
ibdata1 innodb会自动创建3个事务日志文件，存放位置/var/lib/mysql
ib_logfile0 innodb的事务日志文件
ib_logfile1 innodb的事务日志文件

menmory存储引擎
特点：数据的其他所有信息都存储在内存里，是临时的。重启就没有了。
相关的1个文件。 (t3.frm)
表名.frm .frm结尾的文件存表结构。

工作中，如果表里面已经有数据了，就不要再改存储引擎了。

InnoDB  主要特点：支持行级锁定，支持事务，事务回滚，外键。
相关的表文件：表名.frm(存放表结构）  表名.idb（存放数据和索引信息）
三个事务日志文件： ibdata1  ib_logfile0  ib_logfile1（启动Mysqld服务时自动创建记录所有innodb表中执行过的操作）
MyISAM  主要特点： 支持表级锁，不支持事务以及事务回滚和外键。        
                   相关表文件：表名.frm(存放表结构）  表名.MYI（存放索引信息）  表名.MYD（存放数据）
MEMORY 一个文件 表名.frm(存放表结构) 数据存放在内存中，使用于临时表

查询访问多的表，适合使用myisam存储引擎，节省系统资料
写访问多的表，适合使用innodb存储引擎，并发访问量大

转载于:https://www.cnblogs.com/summer2/p/10788096.html