mysql性能优化之default_storage_engine

yorknong

　　1：查看MySQL的存储引擎信息
　　1.1 使用show engines命令。
　　mysql> show engines;
　　+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
　　| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
　　+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
　　| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |

　　| MRG_MYISAM         | YES     | Collection of>　　| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
　　| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
　　| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
　　| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
　　| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
　　| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
　　| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
　　+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
　　9 rows in set (0.01 sec)
　　Support列， YES表示当前版本支持这个存储引擎， DEFAULT表示该引擎是默认的引擎。NO表示不支持该存储引擎。如下所示，InnoDB是默认的存储引擎。
　　1.2 可以查看系统变量default_storage_engine或storage_engine
　　1：default_storage_engine 表示永久表（permanent tables）的默认存储引擎。
　　2：default_tmp_storage_engine 表示临时表的默认存储引擎。
　　storage_engine这个系统变量不推荐使用，它已经被系统变量default_storage_engine替代了
　　mysql> show variables like '%storage_engine%';
　　+----------------------------+--------+
　　| Variable_name              | Value  |
　　+----------------------------+--------+
　　| default_storage_engine     | InnoDB |
　　| default_tmp_storage_engine | InnoDB |
　　| storage_engine             | InnoDB |
　　+----------------------------+--------+
　　3 rows in set (0.00 sec)
　　2：如何修改MySQL的默认存储引擎？
　　2.1 修改my.cnf，在配置文件里面增加参数default-storage-engine，然后重启数据库服务。
　　[mysqld]
　　default-storage-engine=MyISAM
　　然后检查默认存储引擎，就会看到MyISAM为默认存储引擎了。
　　2.2 使用命令修改系统变量(system variables)
　　mysql> set default_storage_engine=InnoDB;
　　Query OK, 0 rows affected (0.09 sec)
　　mysql> show engines;
　　+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
　　| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
　　+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
　　| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |

　　| MRG_MYISAM         | YES     | Collection of>　　| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
　　| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
　　| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
　　| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
　　| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
　　| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
　　| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
　　+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
　　9 rows in set (0.00 sec)
　　mysql>
　　3：如何查看表使用的存储引擎？
　　查看表所用的存储引擎，有下面几种方法：
　　3.1 查询information_schema.TABLES
　　mysql> SELECT TABLE_SCHEMA,
　　->        TABLE_NAME,
　　->        TABLE_TYPE,
　　->        ENGINE
　　-> FROM   information_schema.TABLES
　　-> WHERE  TABLE_NAME = 'TEST';
　　+--------------+------------+------------+--------+
　　| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE |
　　+--------------+------------+------------+--------+
　　| gsp          | TEST       | BASE TABLE | InnoDB |
　　+--------------+------------+------------+--------+
　　1 row in set (0.00 sec)
　　mysql>
　　3.2 使用SHOW CREATE TABLE TEST \G命令。
　　mysql> SHOW CREATE TABLE TEST \G;
　　*************************** 1. row ***************************
　　Table: TEST
　　Create Table: CREATE TABLE `TEST` (
　　`ID` int(11) DEFAULT NULL,
　　`COL1` varchar(6) DEFAULT NULL,
　　`COL2` varchar(6) DEFAULT NULL,
　　`COL3` varchar(6) DEFAULT NULL,
　　`COL4` varchar(6) DEFAULT NULL
　　) ENGINE=InnoDB DEFAULT CHARSET=latin1
　　1 row in set (0.00 sec)
　　ERROR:
　　No query specified
　　mysql>
　　3.3 SHOW TABLE STATUS
　　mysql> SHOW TABLE STATUS WHERE Name='TEST';
　　ERROR 1046 (3D000): No database selected
　　mysql>
　　mysql>
　　mysql> use gsp;
　　Reading table information for completion of table and column names
　　You can turn off this feature to get a quicker startup with -A
　　Database changed
　　mysql> SHOW TABLE STATUS WHERE Name='TEST';
　　如上所示，这种写法必须制定数据库，否则，你只能使用下面语法
　　SHOW TABLE STATUS [{FROM | IN} db_name]
　　[LIKE 'pattern' | WHERE expr]
　　mysql> SHOW TABLE STATUS FROM gsp WHERE Name='TEST';
　　4：如何修改相关表的存储引擎？
　　修改表的存储引擎非常简单，语法如下所示
　　ALTER TABLE my_table ENGINE = InnoDB;

　　mysql>>　　Query OK, 0 rows affected (0.02 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　mysql> SHOW CREATE TABLE TEST \G;
　　*************************** 1. row ***************************
　　Table: TEST
　　Create Table: CREATE TABLE `TEST` (
　　`ID` int(11) DEFAULT NULL,
　　`COL1` varchar(6) DEFAULT NULL,
　　`COL2` varchar(6) DEFAULT NULL,
　　`COL3` varchar(6) DEFAULT NULL,
　　`COL4` varchar(6) DEFAULT NULL
　　) ENGINE=MyISAM DEFAULT CHARSET=latin1
　　1 row in set (0.00 sec)
　　ERROR:
　　No query specified
　　mysql>
　　5：创建表的时候如何指定存储引擎。
　　创建表的时候，如果要指定存储引擎，只需要设置参数ENGINE即可。非常简单。
　　mysql> CREATE TABLE TEST1 (ID INT) ENGINE=InnoDB;
　　Query OK, 0 rows affected (0.02 sec)
　　mysql>  CREATE TABLE TEST2 (ID INT) ENGINE=MyISAM;
　　Query OK, 0 rows affected (0.00 sec)