MySQL Storage Engine MyISAM and InnoDB configuration

MySQL Storage Engine MyISAM and InnoDB configuration MyISAM and InnoDB Maximum features: MyISAM: ① does not support transactions. ② table-level locking, where data locks the entire table when it is updated. ③ does not support foreign key constraints, only full-text indexing is supported. The ④ database blocks each other during the read process. ⑤ data is written or read separately, faster and consumes less resources. Innodb:① support transactions. ② row-level locking, but a full table scan will still be a table-level lock. ③ supports partitions, table spaces, and similar Oracle databases. ④ read-write blocking is related to the transaction isolation level. The ⑤ table and the primary key are stored in a clustered manner. The ⑥ has a very efficient caching feature that caches the lasso and caches the data. How to view the storage engine: View the MySQL default storage engine:

mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES      

View MySQL-supported engines and the default storage engine.

To view the storage engine that the table is using: Method one:

show table status from 库名 where name=‘表名‘;

mysql> show table status from jdy where name=‘test‘;+------+--------+---------+------------+------+| Name | Engine | Version | Row_format | Rows |+------+-------------+-----------------+--------------+| test | InnoDB |      10 | Dynamic    |    0 |                 # 表的存储引擎  InnoDB

Method Two:

show create table 表名；

mysql> use jdy;          # 进入查看表的数据库Database changedmysql> show create table test;+-------+---------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                              |+-------+---------------------------------------------------------------------------------------------------------------------------+| test  | CREATE TABLE "test" ( "name" varchar(10) DEFAULT NULL,  "id" int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |          # 表的存储引擎  InnoDB

How to modify the storage engine:

• Method One:

mysql> use Jdy; # Enter the database of the view Table changedmysql> Show CREATE table test;+-------+----------------------------------------------- ----------------------------------------------------------------------------+| Table |                                                                                                              Create Table |+-------+----------------------------------------------------------------------------------------------------- ----------------------+| Test |          CREATE TABLE "Test" ("name" varchar (TEN) default NULL, "id" int (one) default null) Engine=innodb default Charset=utf8 | # Table Storage Engine innodb# command: ALTER TABLE name engine= engine;       mysql> ALTER TABLE Test Engine=myisam; #修改表的存储引擎为 myisamquery OK, 0 rows affected (0.39 sec) records:0 duplicates:0 warnings:0mysql> Show CREATE TABLE tes t;+-------+---------------------------------------------------------------------------------------------------- -----------------------+| Table | Create Table                                                                                                              |+-------+-- --------------------------------------------------------------------------------------------------------------- ----------+| Test |            CREATE TABLE "Test" ("name" varchar (TEN) default NULL, "id" int (one) default null) Engine=myisam default Charset=utf8 | #修改成功

Method Two:

• Modify the MySQL configuration file my.cnf, specify the default storage engine

vim /etc/my.cnf[mysqld]default-storage-engine=MyISAM   #添加指定默认存储引擎

mysql> create table test01 (id int );     #创建表Query OK, 0 rows affected (0.00 sec)mysql> show create table test01;+--------+------------------------------------------------------------------------------------------+| Table  | Create Table                                                                             |+--------+------------------------------------------------------------------------------------------+| test01 | CREATE TABLE "test01" (  "id" int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 |       #创建的新表默认 MyISAM

Set the default storage engine, and the new table created will use the MyISAM storage engine.

Method Three:

• Specifying the storage engine when creating a table

mysql> create table test0２ (id int ) engine=InnoDB;Query OK, 0 rows affected (0.35 sec)mysql> show create table test0２;+--------+------------------------------------------------------------------------------------------+| Table  | Create Table                                                                             |+--------+------------------------------------------------------------------------------------------+| test0２ | CREATE TABLE "test０２" (  "id" int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |　　　　　　　　　＃新表存储引擎是　InnoDB

Method Four:

• Batch conversion using the Mysql_convert_table_format command

  [[email protected] ~]#mysql_convert_table_format --host=localhost --user=root --password=abc123 --socket=/tmp/mysql.scok --engine=InnoDB jdy test01 test02

  Execution will be error, need to install Operation module

  yum install perl perl-DBD-MySQL -y

  Unable to convert InnoDB engine, Mysql_convert_table_forma command bug, need to modify command script.

  vim /usr/local/mysql/bin/mysql_convert_table_format 　　　＃修改命令脚本$opt_help=$opt_version=$opt_verbose=$opt_force=0;$opt_user=$opt_database=$opt_password=undef;$opt_host="localhost";$opt_socket="";$opt_engine="MYISAM";$opt_port=0;$exit_status=0;GetOptions("e|engine|type=s"       => \$opt_type,　　＃把type修改成engine"f|force"               => \$opt_force,"help|?"               => \$opt_help,"h|host=s"              => \$opt_host,"p|password=s"          => \$opt_password,"u|user=s"              => \$opt_user,"v|verbose"             => \$opt_verbose,"V|version"             => \$opt_version,"S|socket=s"            => \$opt_socket, "P|port=i"              => \$opt_port) || usage(0);

  PS: The fourth method is only suitable for MySQL version 5.5. MySQL 5.5 default storage engine MyISAM 5.7 default storage engine InnoDB.

MySQL Storage Engine MyISAM and InnoDB configuration