mysql 优化引擎_MySQL存储引擎优化

如何在两种存储引擎中进行选择？

① 是否有事务操作？有，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)