MySQL 存储引擎 (一)

PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB
其他的存储引擎支持:
TokuDB (MariaDB原生态支持)
RocksDB
MyRocks
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高
现在很多的NewSQL,使用比较多的功能特性.

查看数据库所支持的存储引擎

show engines;

查看存储引擎为 CSV 的表有哪些

select TABLE_NAME,ENGINE from information_schema.tables where ENGINE = 'CSV';
+-------------+--------+
| TABLE_NAME  | ENGINE |
+-------------+--------+
| general_log | CSV    |
| slow_log    | CSV    |
+-------------+--------+

查看默认的存储引擎

select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

确认每个表的存储引擎

use klvchen;
show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

# 查看全部
show table status\G;
# 或单个表
show table status like 't1'\G;
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-01-12 23:06:05
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

查看单个数据库所有表的存储引擎

show table status from klvchen;

查看所有数据库表的存储引擎

select TABLE_SCHEMA,TABLE_NAME,ENGINE from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA not in ('sys','information_schema','performance_schema');

修改一个表的存储引擎

alter table klvchen.t1 engine innodb;
注意：此命令我们经常使用他，进行innodb表的碎片整理

修改klvchen库下所有的表存储引擎为 innodb

select concat("alter table ",table_schema,".",table_name," engine innodb;") from information_schema.tables where table_schema='klvchen';
+----------------------------------------------------------------------+
| concat("alter table ",table_schema,".",table_name," engine innodb;") |
+----------------------------------------------------------------------+
| alter table klvchen.t1 engine innodb;                                |
| alter table klvchen.t2 engine innodb;                                |
| alter table klvchen.t3 engine innodb;                                |
| alter table klvchen.t4 engine innodb;                                |
+----------------------------------------------------------------------+