MySQL存储引擎

1.MySQL提供的存储引擎

1）MySQL 提供以下存储引擎:

1.InnoDB
    数据经常添加、删除、修改、查询，使用该引擎
2.MyISAM
    只对数据进行查询和添加
3.MEMORY
    支持hash索引
4.ARCHIVE
5.FEDERATED
6.EXAMPLE
7.BLACKHOLE
8.MERGE
9.NDBCLUSTER
10.CSV

2）第三方存储引擎:

1.MySQL当中插件式的存储引擎类型
2.MySQL的两个分支
    1）perconaDB
    2）mariaDB

3）查看存储引擎

#查看当前MySQL支持的存储引擎类型
mysql> show engines

#查看innodb的表有哪些
mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb';

#查看myisam的表有哪些
mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam';

#查看某个表的信息
mysql> select * from tables where TABLE_NAME='city'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def                    #表的注册信息
   TABLE_SCHEMA: world                    #表所在的库
     TABLE_NAME: city                    #表名字
     TABLE_TYPE: BASE TABLE                #表的类型
         ENGINE: InnoDB                    #存储引擎
        VERSION: 10                        #默认版本
     ROW_FORMAT: Compact                #行模式
     TABLE_ROWS: 4188                    #查询数据的量
 AVG_ROW_LENGTH: 97                        #行数据的平均长度
    DATA_LENGTH: 409600                    #数据长度
MAX_DATA_LENGTH: 0                        #最大数据长度
   INDEX_LENGTH: 131072                    #索引的数据长度
      DATA_FREE: 0                        #空间碎片
 AUTO_INCREMENT: 4080                    #自增属性的值到了多少
    CREATE_TIME: 2020-10-28 18:27:42     #创建时间
    UPDATE_TIME: NULL                    #修改时间
     CHECK_TIME: NULL                    #检查时间
TABLE_COLLATION: latin1_swedish_ci         #表的字符集
       CHECKSUM: NULL                    #检查次数
 CREATE_OPTIONS:                         #建表的参数
  TABLE_COMMENT:                         #表的注释
1 row in set (0.00 sec)

2.innodb和myisam的物理区别

#myisam存储引擎的文件
-rw-rw---- 1 mysql mysql  10684 10月 19 17:09 user.frm            #表结构    
-rw-rw---- 1 mysql mysql    728 10月 23 20:02 user.MYD            #数据库的用户密码
-rw-rw---- 1 mysql mysql   2048 10月 27 08:51 user.MYI            #数据库的用户

#innodb存储引擎的文件
-rw-rw---- 1 mysql mysql    8710 10月 28 19:53 city.frm            #表结构
-rw-rw---- 1 mysql mysql 2097152 10月 28 19:54 city.ibd            #表数据

#查看文件
strings user.MYI

3.innodb的核心特性

MVCC            #多版本并发控制
事务               #事务的特性
备份               #mysqldump   xtrabackup
故障自动恢复        #CSR

4.存储引擎相关命令

1）查看当前存储引擎

#查看当前所在库的存储引擎
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.01 sec)

#查看指定表的存储引擎
mysql> select table_schema,table_name,engine from information_schema.tables where table_name='city';
+--------------+------------+--------+
| table_schema | table_name | engine |
+--------------+------------+--------+
| world        | city       | InnoDB |
+--------------+------------+--------+
1 row in set (0.00 sec)

2）修改默认的存储引擎

#在配置文件的[mysqld]标签下添加，永久设置
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
default-storage-engine=InnoDB

#在MySQL命令行中临时设置
SET @@storage_engine=MyISAM

3）建表时指定存储引擎

mysql> create table test4(id int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)