mysql效能最佳化之default_storage_engine

標籤：dup   storage   get   arch   duplicate   port   命令   spec   替代   

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 identical MyISAM tables                          | NO           | NO   | NO         |

| 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 identical MyISAM tables                          | NO           | NO   | NO         |

| 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> ALTER TABLE TEST ENGINE=MyISAM;

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)

 

mysql效能最佳化之default_storage_engine