MySQL-存储引擎

1、储存引擎

查看MySQL支持的引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | 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         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

查看默认存储引擎

mysql> select @@default_storage_engine;

查看除系统库之外的所有库的表存储引擎

mysql> select table_schema,table_name, engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');

2、碎片

查看某个数据库中表的碎片情况

mysql> select table_name,data_free,engine from information_schema.tables where table_schema='test';

对表优化

optimize table `table_name`;

3、数据库线程

查看连接会话

mysql> select * from information_schema.processlist;

查看performance_schema是否打开，默认是打开的

mysql> show variables like 'performance_schema';

查看会话所对应的线程信息

mysql> select * from performance_schema.threads where processlist_id=8;

查看历史语句

mysql> select * from performance_schema.events_statements_history where thread_id=?

4、buffer pool

用来缓冲、缓存，MySQL的数据页（data page ）和索引页、UNDO。MySQL中最大的、最重要的内存区域。

查看buffer pool大小

mysql> select @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              1 |
+--------------------------------+

# 单位是字节，默认大小是128M，建议设置物理内存的50-75%。
mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+

5、log buffer pool

用来缓冲 redo log日志信息。

查看log buffer pool大小

# 单位字节，默认16M，建议设置innodb_log_file_size的1到2倍。
mysql> select @@innodb_log_buffer_size;
+--------------------------+
| @@innodb_log_buffer_size |
+--------------------------+
|                 16777216 |
+--------------------------+

# redo log日志组单个文件大小，单位字节，默认48M
mysql> select @@innodb_log_file_size;
+------------------------+
| @@innodb_log_file_size |
+------------------------+
|               50331648 |
+------------------------+