• 首页
  • css3教程
  • html5教程
  • jQuery手册
  • vue手册
  • php手册
  • MySQL手册
  • apache手册
  • redis手册
  • 配置索引页的合并阈值

    您可以配置MERGE_THRESHOLD索引页的值。如果删除某行或操作缩短某行时索引页的“ page-full ”百分比低于该MERGE_THRESHOLDUPDATE,则InnoDB尝试将索引页与相邻索引页合并。默认MERGE_THRESHOLD值为50,这是以前的硬编码值。最小值MERGE_THRESHOLD为1,最大值为50。

    当索引页面的“页面已满”百分比降至默认MERGE_THRESHOLD设置的50%以下时,InnoDB尝试将索引页面与相邻页面合并。如果两个页面都接近50%充满,则页面合并后可能会很快发生页面拆分。如果此合并拆分行为频繁发生,则可能会对性能产生不利影响。为了避免频繁的合并拆分,您可以降低此MERGE_THRESHOLD值,以使InnoDB尝试页面合并的尝试以较低的“全页”百分比进行。以较低的页面占满百分比合并页面会在索引页面中留出更多空间,并有助于减少合并分割行为。

    MERGE_THRESHOLD索引页面可以为表或个别指标进行定义。阿MERGE_THRESHOLD为单个索引定义的值在一个优先MERGE_THRESHOLD的表中定义的值。如果未定义,则MERGE_THRESHOLD默认值为50。

    为表设置MERGE_THRESHOLD

    您可以MERGE_THRESHOLD使用语句的子句为表设置值。例如:table_optionCOMMENTCREATE TABLE

    CREATE TABLE t1 (
       id INT,
      KEY id_index (id)
    ) COMMENT='MERGE_THRESHOLD=45';
    

    您还可以MERGE_THRESHOLD通过以下子句为现有表设置值:table_optionCOMMENTALTER TABLE

    CREATE TABLE t1 (
       id INT,
      KEY id_index (id)
    );
    
    ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=40';
    

    为单个索引设置MERGE_THRESHOLD

    要设置MERGE_THRESHOLD为单个索引值,则可以使用子句,,或,如图所示在以下实施例:index_optionCOMMENTCREATE TABLEALTER TABLECREATE INDEX

    • MERGE_THRESHOLD使用CREATE TABLE以下命令设置单个索引:

      CREATE TABLE t1 (
         id INT,
        KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40'
      );
      
    • MERGE_THRESHOLD使用ALTER TABLE以下命令设置单个索引:

      CREATE TABLE t1 (
         id INT,
        KEY id_index (id)
      );
      
      ALTER TABLE t1 DROP KEY id_index;
      ALTER TABLE t1 ADD KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40';
      
    • MERGE_THRESHOLD使用CREATE INDEX以下命令设置单个索引:

      CREATE TABLE t1 (id INT);
      CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';
      
    注意

    您不能MERGE_THRESHOLD在的索引级别修改的值GEN_CLUST_INDEX,这是在创建没有主键或唯一键索引InnoDBInnoDB表时通过创建的聚集索引。您只能通过设置表格来修改其MERGE_THRESHOLD值。GEN_CLUST_INDEXMERGE_THRESHOLD

    查询MERGE_THRESHOLD值以获取索引

    MERGE_THRESHOLD索引的当前值可以通过查询INNODB_INDEXES表获得。例如:

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='id_index' \G
    *************************** 1. row 	***************************
           INDEX_ID	: 91
               NAME	: id_index
           TABLE_ID	: 68
               TYPE	: 0
           N_FIELDS	: 1
            PAGE_NO	: 4
              SPACE	: 57
    MERGE_THRESHOLD	: 40
    

    如果使用以下子句明确定义,则可以SHOW CREATE TABLE用来参见MERGE_THRESHOLD表的值:table_optionCOMMENT

    mysql> SHOW CREATE TABLE t2 \G
    *************************** 1. row 	***************************
           Table	: t2
    Create Table	: CREATE TABLE `t2` (
      `id` int(11) DEFAULT NULL,
      KEY `id_index` (`id`) COMMENT 'MERGE_THRESHOLD=40'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    
    注意

    MERGE_THRESHOLD在索引级别定义的值在一个优先MERGE_THRESHOLD的表定义的值。如果未定义,则MERGE_THRESHOLD默认为50%(MERGE_THRESHOLD=50,这是以前的硬编码值。

    同样,如果使用子句明确定义,则可以SHOW INDEX用来参见MERGE_THRESHOLD索引的值:index_optionCOMMENT

    mysql> SHOW INDEX FROM t2 \G
    *************************** 1. row 	***************************
            Table	: t2
       Non_unique	: 1
         Key_name	: id_index
     Seq_in_index	: 1
      Column_name	: id
        Collation	: A
      Cardinality	: 0
         Sub_part	: NULL
           Packed	: NULL
             Null	: YES
       Index_type	: BTREE
          Comment	:
    Index_comment	: MERGE_THRESHOLD=40
    

    测量MERGE_THRESHOLD设置的效果

    INNODB_METRICS表提供了两个计数器,这些计数器可用于衡量MERGE_THRESHOLD设置对索引页合并的影响。

    mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS
           WHERE NAME like '%index_page_merge%';
    +-----------------------------	+----------------------------------------	+
    | NAME                        	| COMMENT                                	|
    +-----------------------------	+----------------------------------------	+
    | index_page_merge_attempts   	| Number of index page merge attempts    	|
    | index_page_merge_successful 	| Number of successful index page merges 	|
    +-----------------------------	+----------------------------------------	+
    

    降低MERGE_THRESHOLD价值时,目标是:

    • 较少的页面合并尝试和成功的页面合并
    • 页面合并尝试和成功的页面合并次数相似

    MERGE_THRESHOLD太小的设置可能会由于过多的空白页空间而导致数据文件很大。

    有关使用INNODB_METRICS计数器的信息,请参见“ InnoDB INFORMATION_SCHEMA指标表”。