如何在两种存储引擎中进行选择？
① 是否有事务操作？有，InnoDB。
②是否存储并发修改？有，InnoDB。
③是否追求快速查询，且数据修改较少？是，MyISAM。
④是否使用全文索引？如果不引用第三方框架，可以选择MyISAM，但是可以选用第三方框架和InnDB效率会更高。
本次实验专为追求查询速率，用于数据量少的情况下
===============================================================================
[root@VM_0_4_centos ~]# mysql -uroot -p123123
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | #innodb默认引擎是default
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
MariaDB [(none)]> create database aaa； #创建一张表格，有就不用创建
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)
MariaDB [(none)]> use test;
Database changed
MariaDB [test]> show table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
MariaDB [test]> show tables;
Empty set (0.00 sec)
MariaDB [test]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
MariaDB [test]> show engines;
+--------------------+---------+--------------------------------------------------------------
| Engine | Support | Comment
+--------------------+---------+--------------------------------------------------------------
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and #innoDB默认是default
| MRG_MYISAM | YES | Collection of identical MyISAM tables
| MyISAM | YES | Non-transactional engine with good performance and small data
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears
| PERFORMANCE_SCHEMA | YES | Performance Schema
| CSV | YES | Stores tables as CSV files
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports tr
| Aria | YES | Crash-safe tables with MyISAM heritage
+--------------------+---------+--------------------------------------------------------------
10 rows in set (0.00 sec)
MariaDB [test]> create table t1(id int(10) not null, name char(20));
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> show table status from test where name='t1'G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB #存储引擎为innoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 10485760
Auto_increment: NULL
Create_time: 2019-11-27 17:25:52
Update_time: NULL
Check_time: NULL
Collation: utf8_unicode_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
方法一在mysql数据库内修改存储引擎
MariaDB [test]> alter table test.t1 engine=myisam;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> show table status from test where name='t1'G
*************************** 1. row ***************************
Name: t1
Engine: MyISAM #此时存储引擎为myisam
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 18295873486192639
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2019-11-27 17:28:48
Update_time: 2019-11-27 17:28:48
Check_time: NULL
Collation: utf8_unicode_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
[root@localhost ~]# vim /etc/my.cnf
在mysqld中添加如下一条命令
default-storage-engine=myisam
[root@localhost ~]# systemctl restart mariadb
进入Mysql数据库再次查看存储引擎：
MariaDB [(none)]> show engine;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | DEFAULT | Non-transactional engine with good performance and small data footprint | NO | NO | NO | #此时myisam默认为default
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)