mysql innodb 表分区_Mysql学习第二篇-认识innodb中的表、约束、分区

********************

全局分区索引和局部分区索引

********************

全局分区索引：在oracle中，全局分区索引为一个独立的segment。各分区为独立的segment。

在mysql中不支持此种类型的索引。

局部分区索引：在oracle中，各分区为独立的segment，分区索引也为独立的segment。

在mysql中分区和索引放于一个segment中。

***********************

查看是否支持分区

***********************

mysql>show plugins;+----------------------------+----------+--------------------+---------+---------+

| Name | Status | Type | Library | License |

+----------------------------+----------+--------------------+---------+---------+

| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |

| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |

| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |

| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |

| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |

| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |

| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |

| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |

| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |

| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |

| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |

| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |

| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |

| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |

| ngram | ACTIVE | FTPARSER | NULL | GPL |

+----------------------------+----------+--------------------+---------+---------+

44 rows in set (0.02sec)

****************

当前mysql支持的分区方式

****************

range、

list、

hash还有linear hashkeycolumns

****************

mysql也支持子分区技术

****************

子分区技术：在range、list的基础上再进行hash、或者key的分区。在分区中对于null值，range分区会将其置于最小的分区中。在list分区中必须显示的说明null存放的分区，否则会报错。

******************

如何查看有关子分区的相关信息

******************

mysql> select * frominformation_schema.partitions;+---------------+--------------------+------------------------------------------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+--------------------+--------------+-----------+---------------------+---------------------+------------+----------+-------------------+-----------+-----------------+

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME |

+---------------+--------------------+------------------------------------------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+--------------------+--------------+-----------+---------------------+---------------------+------------+----------+-------------------+-----------+-----------------+

| def | information_schema | CHARACTER_SETS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 384 | 0 | 16434816 | 0 | 0 | 2019-05-28 16:27:45 | NULL | NULL | NULL | | | NULL |