引言

的统计消息中包括多个统计项，由于基于采样计算，因此存在误差，最经常出现的是统计项【表的行数】不准确，或许造成口头方案决定失误。

本文经过剖析系统表中表大小与物理文件大小差异较大的现象，定位到要素是统计项【索引数据页的数量】不准确造成，文中对统计消息相关的常识点启动了引见。

现象

期间：20231026

现象：监控显示数据2T，系统表显示不到1T，有分区表，并且有删除操作

剖析

系统表

口头以下SQL失掉实例上每个库的大小。

selecttable_schemaas'数据库',sum(table_rows)as'行数',sum(truncate(data_length/1024/1024/1024,2))as'数据容量(GB)',sum(truncate(index_length/1024/1024/1024,2))as'索引容量(GB)',sum(truncate(data_free/1024/1024/1024,2))as'碎片空间(GB)',sum(truncate((>+--------------------+------------+------------------+------------------+------------------+---------------+|数据库|行数|数据容量(GB)|索引容量(GB)|碎片空间(GB)|总容量(GB)|+--------------------+------------+------------------+------------------+------------------+---------------+|information_schema|NULL|0.00|0.00|98.60|98.60||mysql|254074|0.03|0.00|0.00|0.04||performance_schema|13328253|0.00|0.00|0.00|0.00||sys|6|0.00|0.00|0.00|0.00||tracking_46|1903822252|185.59|88.38|4.47|280.90||tracking_47|1958199360|190.14|90.70|4.56|287.67||tracking_detl_46|677699672|58.23|25.56|0.59|84.68||tracking_detail_47|667959727|57.23|24.87|0.60|83.00|+--------------------+------------+------------------+------------------+------------------+---------------+8rowsinset(0.26sec)

从库，口头结果如下所示。

+--------------------+------------+------------------+------------------+------------------+---------------+|数据库|行数|数据容量(GB)|索引容量(GB)|碎片空间(GB)|总容量(GB)|+--------------------+------------+------------------+------------------+------------------+---------------+|information_schema|NULL|0.00|0.00|0.00|0.00||mysql|146406|0.06|0.00|0.00|0.07||performance_schema|1519229|0.00|0.00|0.00|0.00||sys|6|0.00|0.00|0.00|0.00||tracking_46|3006864458|309.54|144.75|3.46|460.18||tracking_47|3006350150|310.27|144.79|3.53|460.63||tracking_detail_46|1511754256|112.39|56.80|0.62|170.12||tracking_detail_47|1515881664|112.67|56.89|0.57|170.43|+--------------------+------------+------------------+------------------+------------------+---------------+8rowsinset(0.31sec)

其中：

主库，检查详细表的大小，显示单表3G左右，碎片20M左右。

监控显示用户数据2T。

由于监控数据采集自物理文件的大小，因此体现为物理文件大小和系统表大小不分歧。

物理文件

库级别

#du--max-depth=1-h.259G./tracking_detail_46100M./mysql680K./sys16K./tracking_details_92259G./tracking_detail_4777./tracking_46774G./tracking_4716K./tracking_details_9416K./tracking_details_9316K./tracking_details_954.0K./tracking1.1M./performance_schema2.2T.

表级别对比结果，输入结果依照差异倒排，其中单位是byte。

{"tracking_detail_46.tracking_info_1497":{"table_size_file":9127723008,"table_size_sys":2268192768,"table_size_gap":6859530240},"tracking_detail_47.tracking_info_1519":{"table_size_file":9127723008,"table_size_sys":2286411776,"table_size_gap":6841311232},"tracking_detail_46.tracking_info_1490":{"table_size_file":9127723008,"table_size_sys":2368405504,"table_size_gap":6759317504},...}

差异最大的表tracking_detail_46.tracking_info_1497。

检查表结构

mysql>showcreatetabletracking_detail_46.tracking_info_1497G***************************1.row***************************Table:tracking_info_1497CreateTable:CREATETABLE`tracking_info_1497`(`id`bigint(20)NOTNULLCOMMENT'主键',`tenant_id`varchar(32)DEFAULTNULLCOMMENT'租户',`source_ele_id`bigint(20)NOTNULLCOMMENT'原始要素表主键',`template_id`int(6)NOTNULLCOMMENT'模板编号',`business_id`varchar(100)NOTNULLCOMMENT'业务操作单号',`related_id`varchar(100)DEFAULTNULLCOMMENT'关联业务单号，例如：包裹号',`remark`varchar(1024)DEFAULTNULLCOMMENT'物流跟踪话术',`is_delete`tinyint(2)NOTNULLDEFAULT'0'COMMENT'删除标识，0有效，1删除',`create_time`datetimeDEFAULTNULLCOMMENT'创立期间',`update_time`datetimeDEFAULTNULLCOMMENT'降级期间',`partition_time`datetimeNOTNULLCOMMENT'分区期间',`ts`timestamp(3)NOTNULLDEFAULTCURRENT_TIMESTAMP(3)ONUPDATECURRENT_TIMESTAMP(3)COMMENT'期间戳',PRIMARYKEY(`id`,`partition_time`),KEY`idx_business_id`(`business_id`,`template_id`,`source_ele_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4ROW_FORMAT=COMPRESSEDKEY_BLOCK_SIZE=8/*!50500PARTITIONBYRANGECOLUMNS(partition_time)(PARTITIONp202305VALUESLESSTHAN('2023-06-0100:00:00')ENGINE=InnoDB,PARTITIONp202306VALUESLESSTHAN('2023-07-0100:00:00')ENGINE=InnoDB,PARTITIONp202307VALUESLESSTHAN('2023-08-0100:00:00')ENGINE=InnoDB,PARTITIONp202308VALUESLESSTHAN('2023-09-0100:00:00')ENGINE=InnoDB,PARTITIONp202309VALUESLESSTHAN('2023-10-0100:00:00')ENGINE=InnoDB,PARTITIONp202310VALUESLESSTHAN('2023-11-0100:00:00')ENGINE=InnoDB,PARTITIONp202311VALUESLESSTHAN('2023-12-0100:00:00')ENGINE=InnoDB,PARTITIONp202312VALUESLESSTHAN('2024-01-0100:00:00')ENGINE=InnoDB,PARTITIONp202401VALUESLESSTHAN('2024-02-0100:00:00')ENGINE=InnoDB,PARTITIONp202402VALUESLESSTHAN('2024-03-0100:00:00')ENGINE=InnoDB,PARTITIONp202403VALUESLESSTHAN('2024-04-0100:00:00')ENGINE=InnoDB,PARTITIONp202404VALUESLESSTHAN('2024-05-0100:00:00')ENGINE=InnoDB,PARTITIONp202405VALUESLESSTHAN('2024-06-0100:00:00')ENGINE=InnoDB,PARTITIONp202406VALUESLESSTHAN('2024-07-0100:00:00')ENGINE=InnoDB,PARTITIONp202407VALUESLESSTHAN('2024-08-0100:00:00')ENGINE=InnoDB,PARTITIONp202408VALUESLESSTHAN('2024-09-0100:00:00')ENGINE=InnoDB,PARTITIONp202409VALUESLESSTHAN('2024-10-0100:00:00')ENGINE=InnoDB,PARTITIONp202410VALUESLESSTHAN('2024-11-0100:00:00')ENGINE=InnoDB,PARTITIONp202411VALUESLESSTHAN('2024-12-0100:00:00')ENGINE=InnoDB,PARTITIONp202412VALUESLESSTHAN('2025-01-0100:00:00')ENGINE=InnoDB)*/1rowinset(0.00sec)

其中：

经确认，该实例上的表都是分区表，相当于分表➕分区。

检查该表对应的文件大小，由于是分区表，因此对应多个文件。

-rw-r-----1mysqlmysql1.6GOct2517:45./tracking_detail_46/tracking_info_1497#P#p202305.ibd-rw-r-----1mysqlmysql1.7GOct2615:55./tracking_detail_46/tracking_info_1497#P#p202306.ibd-rw-r-----1mysqlmysql1.2GOct2611:37./tracking_detail_46/tracking_info_1497#P#p202307.ibd-rw-r-----1mysqlmysql1.3GOct2610:48./tracking_detail_46/tracking_info_1497#P#p202308.ibd-rw-r-----1mysqlmysql1.5GOct2617:25./tracking_detail_46/tracking_info_1497#P#p202309.ibd-rw-r-----1mysqlmysql1.2GOct2618:03./tracking_detail_46/tracking_info_1497#P#p202310.ibd-rw-r-----1mysqlmysql64KJun2017:18./tracking_detail_46/tracking_info_1497#P#p202311.ibd-rw-r-----1mysqlmysql64KJun2017:18./tracking_detail_46/tracking_info_1497#P#p202312.ibd-rw-r-----1mysqlmysql64KJun2017:18./tracking_detail_46/tracking_info_1497#P#p202401.ibd-rw-r-----1mysqlmysql64KJun2017:18./tracking_detail_46/tracking_info_1497#P#p202402.ibd-rw-r-----1mysqlmysql64KJun2017:18./tracking_detail_46/tracking_info_1497#P#p202403.ibd-rw-r-----1mysqlmysql64KJun2017:18./tracking_detail_46/tracking_info_1497#P#p202404.ibd-rw-r-----1mysqlmysql64KJun2017:18./tracking_detail_46/tracking_info_1497#P#p202405.ibd-rw-r-----1mysqlmysql64KJun2017:18./tracking_detail_46/tracking_info_1497#P#p202406.ibd-rw-r-----1mysqlmysql64KJun2017:18./tracking_detail_46/tracking_info_1497#P#p202407.ibd-rw-r-----1mysqlmysql64KJun2017:18./tracking_detail_46/tracking_info_1497#P#p202408.ibd-rw-r-----1mysqlmysql64KJun2017:18./tracking_detail_46/tracking_info_1497#P#p202409.ibd-rw-r-----1mysqlmysql64KJun2017:18./tracking_detail_46/tracking_info_1497#P#p202410.ibd-rw-r-----1mysqlmysql64KJun2017:19./tracking_detail_46/tracking_info_1497#P#p202411.ibd-rw-r-----1mysqlmysql64KJun2017:21./tracking_detail_46/tracking_info_1497#P#p202412.ibd

差异要素剖析

上方是回答的差异要素剖析。

由于之前有删除操作，因此疑心与数据删除有相关，区分剖析删除行与删除表能否会造成该现象。

删除行有两种场景：

删除表有一种场景：

因此检查mysqld进程关上的deleted文件。

[root@MSS-pz564g9cew~]#ps-efUIDPIDPPIDCSTIMETTYTIMECMDroot100Jun20?00:00:03/usr/sbin/initroot70510Jun20?00:00:13/usr/sbin/sshd-Droot73610Jun20?00:00:00/usr/sbin/rsyslogd-nroot73910Jun20?00:00:12/usr/sbin/crond-nroot74310Jun20?00:39:41/usr/bin/-api--config-file=/etc/docker-api.tomlroot79710Jun20?00:00:00/export/data/zabbix/sbin/zabbix_agentdroot7997970Jun20?01:32:42/export/data/zabbix/sbin/zabbix_agentd:collector[idle1sec]root8007970Jun20?00:00:21/export/data/zabbix/sbin/zabbix_agentd:listener#1[waitingforconnection]root8017970Jun20?00:00:21/export/data/zabbix/sbin/zabbix_agentd:listener#2[waitingforconnection]root8027970Jun20?00:00:21/export/data/zabbix/sbin/zabbix_agentd:listener#3[waitingforconnection]root8037970Jun20?00:00:21/export/data/zabbix/sbin/zabbix_agentd:listener#4[waitingforconnection]root8047970Jun20?00:00:21/export/data/zabbix/sbin/zabbix_agentd:listener#5[waitingforconnection]root8057970Jun20?00:10:06/export/data/zabbix/sbin/zabbix_agentd:activechecks#1[idle1sec]mysql337110Jun20?00:00:00/bin/sh/export/servers/mysql/bin/mysqld_safe--defaults-file=/export/servers/mysql/etc/my.cnfmysql4628337199Jun20?165-22:28:24/export/servers/mysql/bin/mysqld--defaults-file=/export/servers/mysql/etc/my.cnf--basedir=/export/servers/mysql--datadir=/export/data/mysql/data--plugin-dir=/export/servers/mysql/lroot45907705116:04?00:00:00sshd:root@pts/0root4590945907016:04pts/000:00:00-bashroot4592445909016:04pts/000:00:00ps-efroot129505111Oct20?2-20:31:08/usr/bin//target=_blankclass=infotextkey>Python/usr/lib/python2.7/site-packages/trove/cmd/trove-guestagent--config-dir=/export/etc/trove/conf.d/root12956712Oct20?16:15:01/usr/bin/python/usr/lib/python2.7/site-packages/trove/cmd/opentsdb-agent--config-file=/etc/opentsdb_agent/opentsdb_agent.conftd-agent12969710Oct20?00:05:07/opt/td-agent/embedded/bin/ruby/opt/td-agent/embedded/bin/fluentd--log/export/data/td-agent/td-agent.log--daemon/var/run/td-agent/td-agent.pid--log-rotate-age2--log-rotate-size104td-agent1297021296970Oct20?02:28:18/opt/td-agent/embedded/bin/ruby-Eascii-8bit:ascii-8bit/opt/td-agent/embedded/bin/fluentd--log/export/data/td-agent/td-agent.log--daemon/var/run/td-agent/td-agent.pid--log-rotate-age[root@MSS-pz564g9cew~]#[root@MSS-pz564g9cew~]#[root@MSS-pz564g9cew~]#lsof-p4628|grepdeletedmysqld4628mysql5uREG253,32252692147483908/export/data/mysql/tmp/ibKCmSCB(deleted)mysqld4628mysql6uREG253,302147483909/export/data/mysql/tmp/ibb414ul(deleted)mysqld4628mysql7uREG253,302147483910/export/data/mysql/tmp/ibS2Rhn5(deleted)mysqld4628mysql8uREG253,342072147483911/export/data/mysql/tmp/ibSHfCOD(deleted)mysqld4628mysql13uREG253,302147483912/export/data/mysql/tmp/ibN8igSs(deleted)

其中：

统计消息

由于该表是分区表，因此进一步检查系统表判别详细哪些分区的差异大。

mysql>SELECTPARTITION_NAME,DATA_LENGTH,INDEX_LENGTH,DATA_FREE,TABLE_ROWSFROMINFORMATION_SCHEMA.PARTITIONSWHERETABLE_NAME='tracking_info_1497';+----------------+-------------+--------------+-----------+------------+|PARTITION_NAME|>mysql>selectdate_format(partition_time,'%Y-%m')mont,count(*)fromtracking_detail_46.tracking_info_1497groupbydate_format(partition_time,'%Y-%m');+---------+----------+|mont|count(*)|+---------+----------+|2023-05|10571445||2023-06|13659671||2023-07|10874195||2023-08|12275399||2023-09|13722214||2023-10|13669851||2023-11|10710033|+---------+----------+7rowsinset(2min2.82sec)

其中：

标明行数的统计消息误差较大，因此疑心表大小与行数相似，也是统计消息不准确造成差异大。

首先须要确认> mysql>select*frommysql.innodb_index_statswhere>

stat_value

mysql>SELECTSUM(stat_value)ASpages,index_name,SUM(stat_value)*8*1024ASsizeFROMmysql.innodb_index_statsWHEREtable_nameLIKE'tracking_info_1497%'AND>mysql>select*frominformation_schema.tableswheretable_name='tracking_info_1497'ANDtable_schema='tracking_detail_46'G***************************1.row***************************TABLE_CATALOG:defTABLE_SCHEMA:tracking_detail_46TABLE_NAME:tracking_info_1497TABLE_TYPE:BASETABLEENGINE:InnoDBVERSION:10ROW_FORMAT:CompressedTABLE_ROWS:15785619AVG_ROW_LENGTH:100>mysql>analyzetabletracking_detail_46.tracking_info_1497;+---------------------------------------+---------+----------+----------+|Table|Op|Msg_type|Msg_text|+---------------------------------------+---------+----------+----------+|tracking_detail_46.tracking_info_1497|analyze|status|OK|+---------------------------------------+---------+----------+----------+1rowinset(0.31sec)

mysql>SELECTPARTITION_NAME,DATA_LENGTH,INDEX_LENGTH,DATA_FREE,TABLE_ROWSFROMINFORMATION_SCHEMA.PARTITIONSWHERETABLE_NAME='tracking_info_1497';+----------------+-------------+--------------+-----------+------------+|PARTITION_NAME|>
stat_value
从新检查索引大小
mysql>SELECTSUM(stat_value)ASpages,index_name,SUM(stat_value)*8*1024ASsizeFROMmysql.innodb_index_statsWHEREtable_nameLIKE'tracking_info_1497%'AND>mysql>select(6553649152+3398983680)/9127723008;+------------------------------------+|(6553649152+3398983680)/9127723008|+------------------------------------+|1.0904|+------------------------------------+1rowinset(0.00sec)mysql>select9127723008/2268192768;+-----------------------+|9127723008/2268192768|+-----------------------+|4.0242|+-----------------------+1rowinset(0.00sec)
标明物理文件的大小准确，统计消息不准确。
常识点
统计消息
关于统计消息，关键存在以下三个疑问：
上方区分启动便捷引见。
存储模式
InnoDB提供了两种存储统计数据的模式：
系统参数用于控制能否终身性存储统计数据，MySQL5.6版本之前自动OFF，示意保留在内存中，自MySQL5.6版本起自动ON，示意保留在磁盘中。
mysql>select@@innodb_stats_persistent;+---------------------------+|@@innodb_stats_persistent|+---------------------------+|1|+---------------------------+1rowinset(0.00sec)
其中，终身性存储详细是保留在以下两张系统表中：
两张表每个列的用途见下表。
innodb_table_stats表
字段名
形容
database_name
数据库名
table_name
表名
last_update
本条记载最后降级的期间
表中记载的条数
clustered_index_size
表的聚簇索引占用的页面数量
sum_of_other_index_sizes
表的其余索引占用的页面数量
其中有两个统计项：
innodb_index_stats表
字段名
形容
database_name
数据库名
table_name
表名
index_name
索引名
last_update
本条记载最后降级的期间
统计项的称号
stat_value
对应的统计项的值
sample_size
为生成统计数据而采样的页面数量
stat_description
对应的统计项的形容
其中有三个统计项：
查问一张表的统计数据启动举例说明。
mysql>select*frommysql.innodb_table_statswhere>mysql>select@@innodb_stats_persistent_sample_pages;+----------------------------------------+|@@innodb_stats_persistent_sample_pages|+----------------------------------------+|20|+----------------------------------------+1rowinset(0.00sec)
2）索引的页面数量
每个索引占用两个段（segment），一个叶子节点段，一个非叶子节点段。
因此索引的页面数量等于对应的叶子节点段与非叶子节点段区分占用的页面数量之和。
数据字典中存在每个表中各个索引对应的根页面位置，而根页面的PageHeader中保留叶子节点段与非叶子节点段对应的Segmentheader。
其中：
段是以区（extent）为单位放开存储空间的，关于16KB的页来说，延续的64个页就是一个区，也就是说一个区自动占用1MB空间大小。
区经过链表启动治理，链表基节点ListBaseNode中保留链表的头节点和尾节点的指针以及这个链表中蕴含了多少个节点即ListLength。
其中：
经过链表的ListLength字段读出该段占用的数量，每个区占用64个页，就可以统计出整个段占用的空间，进而失掉索引的页面数量。
3）索引列不重复的值有多少
计算环节可以简化为：
其中关于有多个列的联结索引将从返回后依次统计列的组合的不重复的值有多少。
mysql>selectstat_name,stat_value,sample_size,stat_descriptionfrommysql.innodb_index_statswhere>innodb_stats_persistent_sample_pages*表中索引列的数量*分区数
ANALYZETABLE复杂度可以形容为：
O(n_sample*(n_cols_in_uniq_i+n_cols_in_non_uniq_i+n_cols_in_pk*(1+n_non_uniq_i))*n_part)
其中：
依据复杂度公式可以预估ANALYZETABLE命令的口头用时。
比如前文中降级统计消息的表结构。
PRIMARYKEY(`id`,`partition_time`),KEY`idx_business_id`(`business_id`,`template_id`,`source_ele_id`)
可以确定以下值：
计算扫描的页数等于2800
mysql>select20*(0+3+2*(1+1))*20;+---------------------+|20*(0+3+2*(1+1))*20|+---------------------+|2800|+---------------------+1rowinset(0.00sec)
口头用时0.31s
mysql>analyzetabletracking_detail_46.tracking_info_1497;+---------------------------------------+---------+----------+----------+|Table|Op|Msg_type|Msg_text|+---------------------------------------+---------+----------+----------+|tracking_detail_46.tracking_info_1497|analyze|status|OK|+---------------------------------------+---------+----------+----------+1rowinset(0.31sec)
论断
数据库实例的物理文件的大小是依据系统表计算的表大小的两倍以上，要素是统计数据不准确，详细是索引数据页的数量不准确。
在定位到差异最大的表以后，经过降级统计消息，将差异从4倍增加为1.09倍。
索引的大小等于数据页的数量✖️页大小，其中数据页的数量依赖统计数据，显然在除了数据行以外，数据页的数量也是一个关键的统计项。
统计数据的终身性存储详细保留在innodb_table_stats与innodb_index_stats数据表中。
其中关键有以下三个统计项：
参考教程

mysql的特点是什么?

1) 功能强大MySQL 中提供了多种数据库存储引擎，各引擎各有所长，适用于不同的应用场合，用户可以选择最合适的引擎以得到最高性能，可以处理每天访问量超过数亿的高强度的搜索 Web 站点。 MySQL5 支持事务、视图、存储过程、触发器等。 2) 支持跨平台MySQL 支持至少 20 种以上的开发平台，包括 Linux、Windows、FreeBSD 、IBMAIX、AIX、FreeBSD 等。 这使得在任何平台下编写的程序都可以进行移植，而不需要对程序做任何的修改。 3) 运行速度快高速是 MySQL 的显著特性。 在 MySQL 中，使用了极快的 B 树磁盘表（MyISAM）和索引压缩；通过使用优化的单扫描多连接，能够极快地实现连接；SQL 函数使用高度优化的类库实现，运行速度极快。 4) 支持面向对象PHP 支持混合编程方式。 编程方式可分为纯粹面向对象、纯粹面向过程、面句对象与面向过程混合 3 种方式。 5) 安全性高灵活和安全的权限与密码系统，允许基本主机的验证。 连接到服务器时，所有的密码传输均采用加密形式，从而保证了密码的安全。 6) 成本低MySQL 数据库是一种完全免费的产品，用户可以直接通过网络下载。 7) 支持各种开发语言MySQL 为各种流行的程序设计语言提供支持，为它们提供了很多的 API 函数，包括 PHP、、Java、Eiffel、Python、Ruby、Tcl、C、C++、Perl 语言等。 8) 数据库存储容量大MySQL 数据库的最大有效表尺寸通常是由操作系统对文件大小的限制决定的，而不是由 MySQL 内部限制决定的。 InnoDB 存储引擎将 InnoDB 表保存在一个表空间内，该表空间可由数个文件创建，表空间的最大容量为 64TB，可以轻松处理拥有上千万条记录的大型数据库。 9) 支持强大的内置函数PHP 中提供了大量内置函数，几乎涵盖了 Web 应用开发中的所有功能。 它内置了数据库连接、文件上传等功能，MySQL 支持大量的扩展库，如 MySQLi 等，可以为快速开发 Web 应用提供便利。

MySQL数据库优化（七）

为了能最小化磁盘I/O MyISAM 存储引擎采用了很多数据库系统使用的一种策略 它采用一种机制将最经常访问的表保存在内存区块中

本章首先描述了 MyISAM 索引缓存的基本操作 然后讨论在MySQL 中所做的改进 它提高了索引缓存性能 同时能更好地控制缓存操作

索引缓存机制对 ISAM 表同样适用 不过 这种有效性正在减弱 自从MySQL 开始 MyISAM 表类型引进之后 ISAM 就不再建议使用了 MySQL 更是延续了这个趋势 ISAM 类型默认被禁用了

可以通过系统变量 key_buffer_size 来控制索引缓存区块的大小 如果这个值大小为 那么就不使用缓存 当这个值小得于不足以分配区块缓冲的最小数量( )时 也不会使用缓存

当索引缓存无法操作时 索引文件就只通过操作系统提供的本地文件系统缓冲来访问(换言之 表索引区块采用的访问策略和数据区块的一致)

一个索引区块在 MyISAM 索引文件中是一个连续访问的单元 通常这个索引区块的大小和B树索引节点大小一样(索引在磁盘中是以B树结构来表示的 这个树的底部时叶子节点 叶子节点之上则是非叶子节点)

在索引缓存结构中所有的区块大小都是一样的 这个值可能等于 大于 或小于表的索引区块大小 通常这两个值是不一样的

当必须访问来自任何表的索引区块时 服务器首先检查在索引缓存中是否有可用的缓冲区块 如果有 服务器就访问缓存中的数据 而非磁盘 就是说 它直接存取缓存 而不是存取磁盘 否则 服务器选择一个(多个)包含其它不同表索引区块的缓存缓冲区块 将它的内容替换成请求表的索引区块的拷贝 一旦新的索引区块在缓存中了 索引数据就可以存取了

当发生被选中要替换的区块内容修改了的情况时 这个区块就被认为 脏 了 那么 在替换之前 它的内容就必须先刷新到它指向的标索引

通常服务器遵循LRU(最近最少使用)策略 当要选择替换的区块时 它选择最近最少使用的索引区块 为了想要让选择变得更容易 索引缓存模块会维护一个包含所有使用区块特别的队列(LRU链) 当一个区块被访问了 就把它放到队列的最后位置 当区块要被替换时 在队列开始位置的区块就是最近最少使用的 它就是第一候选删除对象

共享访问索引缓存

在MySQL 以前 访问索引缓存是串行的 两个线程不能并行地访问索引缓存缓冲 服务器处理一个访问索引区块的请求只能等它之前的请求处理完 结果 新的请求所需的索引区块就不在任何索引缓存环冲区块中 因为其他线程把包含这个索引区块的缓冲给更新了

从MySQL 开始 服务器支持共享方式访问索引缓存

没有正在被更新的缓冲可以被多个线程访问

缓冲正被更新时 需要使用这个缓冲的线程只能等到更新完成之后

多个线程可以初始化需要替换缓存区块的请求 只要它们不干扰别的线程(也就是 它们请求不同的索引区块 因此不同的缓存区块被替换)

共享方式访问索引缓存令服务器明显改善了吞吐量

多重索引缓存

共享访问索引缓存改善了性能 却不能完全消除线程间的冲突 它们仍然争抢控制管理存取索引缓存缓冲的结构 为了更进一步减少索引缓存存取冲突 MySQL 提供了多重索引缓存特性 这能将不同的表索引指定到不同的索引缓存

当有多个索引缓存 服务器在处理指定的 MyISAM 表查询时必须知道该使用哪个 默认地 所有的 MyISAM 表索引都缓存在默认的索引缓存中 想要指定到特定的缓存中 可以使用 CACHE INDEX 语句

如下语句所示 指定表的索 t t 和 t 引缓存到名为 hot_cache 的缓存中

mysql>CACHEINDEXt t t INhot_cache;　+ + + + +　|Table|Op|Msg_type|Msg_text|　+ + + + +　|test t |assign_to_keycache|status|OK|　|test t |assign_to_keycache|status|OK|　|test t |assign_to_keycache|status|OK|　+ + + + +

注意 如果服务器编译支持存 ISAM 储引擎了 那么 ISAM 表也使用索引缓存机制 不过 ISAM 表索引只能使用默认的索引缓存而不能自定义

CACHE INDEX 语句中用到的索引缓存是根据用 SET GLOBAL 语句的参数设定的值或者服务器启动参数指定的值创建的 如下　mysql> SET GLOBAL keycache key_buffer_size= * ;想要删除索引缓存 只需设置它的大小为　mysql> SET GLOBAL keycache key_buffer_size= ;索引缓存变量是一个结构体变量 由名字和组件构成 例如 keycache key_buffer_size keycache 就是缓存名 key_buffer_size 是缓存组件　默认地 表索引在服务器启动时指定到主(默认的)索引缓存中 当一个索引缓存被删掉后 指定到这个缓存的所有索引都被重新指向到了默认索引缓存中去　对一个繁忙的系统来说 我们建议以下三条策略来使用索引缓存　热缓存占用 %的总缓存空间 用于繁重搜索但很少更新的表　冷缓存占用 %的总缓存空间 用于中等强度更新的表 如临时表　冷缓存占用 %的总缓存空间 作为默认的缓存 用于所有其他表　使用三个缓存的一个原因是好处在于 存取一个缓存结构时不会阻止对其他缓存的访问 访问一个表索引的查询不会跟指定到其他缓存的查询竞争 性能提高还表现在以下几点原因　热缓存只用于检索记录 因此它的内容总是不需要变化 所以 无论什么时候一个索引区块需要从磁盘中引入 被选中要替换的缓存区块的内容总是要先被刷新　索引被指向热缓存中后 如果没有需要扫描全部索引的查询 那么对应到B树中非叶子节点的索引区块极可能还保留在缓存中　在临时表里必须频繁执行一个更新操作是相当快的 如果要被更新的节点已经在缓存中了 它无需先从磁盘中读取出来 当临时表的索引大小和冷缓存大小一样时 那么在需要更新一个节点时它已经在缓存中存在的几率是相当高的

中点插入策略

默认地 MySQL 的索引缓存管理系统采用LRU策略来选择要被清除的缓存区块 不过它也支持更完善的方法 叫做 中点插入策略

使用中点插入策略时 LRU链就被分割成两半 一个热子链 一个温子链 两半分割的点不是固定的 不过缓存管理系统会注意不让温子链部分 太短 总是至少包括全部缓存区块的 key_cache_division_limit 比率 key_cache_division_limit 是缓存结构体变量的组件部分 因此它是每个缓存都可以设置这个参数值

当一个索引区块从表中读入缓存时 它首先放在温子链的末尾 当达到一定的点击率(访问这个区块)后 它就提升到热子链中去 目前 要提升一个区块的点击率( )对每个区块来说都是一样的 将来 我们会让点击率依靠B树中对应的索引区块节点的级别 包含非叶子节点的索引区块所要求的提升点击率就低一点 包含叶子节点的B索引树的区块的值就高点

提升起来的区块首先放在热子链的末尾 这个区块在热子链内一直循环 如果这个区块在该子链开头位置停留时间足够长了 它就会被降级回温子链 这个时间是由索引缓存结构体变量的组件 key_cache_age_threshold 值来决定的

这个阀值是这么描述的 一个索引缓存包含了 N 个区块 热子链开头的区块在低于 N*key_cache_age_threshold/ 次访问后就被移动到温子链的开头位置 它又首先成为被删除的候选对象 因为要被替换的区块还是从温子链的开头位置开始的

中点插入策略就能在缓存中总能保持更有价值的区块 如果更喜欢采用LRU策略 只需让 key_cache_division_limit 的值低于默认值

中点插入策略能帮助改善在执行需要有效扫描索引 它会将所有对应到B树中高级别的有价值的节点推出的查询时的性能 为了避免这样 就必须设定 key_cache_division_limit 远远低于 以采用中点插入策略 则在扫描索引操作时那些有价值的频繁点击的节点就会保留在热子链中了

索引预载入

如果索引缓存中有足够的区块用来保存全部索引 或者至少足够保存全部非叶子节点 那么在使用前就载入索引缓存就很有意义了 将索引区块以十分有效的方法预载入索引缓存缓冲 从磁盘中顺序地读取索引区块

没有预载入 查询所需的索引区块仍然需要被放到缓存中去 虽然索引区块要保留在缓存中 因为有足够的缓冲 它们可以从磁盘中随机读取到 而非顺序地

想要预载入缓存 可以使用 LOAD INDEX INTO CACHE 语句 如下语句预载入了表 t 和 t 的索引节点(区块)

mysql>LOADINDEXINTOCACHEt t IGNORELEAVES;　+ + + + +　|Table|Op|Msg_type|Msg_text|　+ + + + +　|test t |preload_keys|status|OK|　|test t |preload_keys|status|OK|　+ + + + +

增加修饰语 IGNORE LEAVES 就只预载入非叶子节点的索引区块 因此 上述语句加载了 t 的全部索引区块 但是只加载 t 的非叶子节点区块

如果使用 CACHE INDEX 语句将索引指向一个索引缓存 将索引区块预先放到那个缓存中去 否则 索引区块只会加载到默认的缓存中去

索引缓存大小

MySQL 引进了对每个索引缓存的新变量 key_cache_block_size 这个变量可以指定每个索引缓存的区块大小 用它就可以来调整索引文件I/O操作的性能

当读缓冲的大小和本地操作系统的I/O缓冲大小一样时 就达到了I/O操作的最高性能了 但是设置索引节点的大小和I/O缓冲大小一样未必能达到最好的总体性能 读比较大的叶子节点时 服务器会读进来很多不必要的数据 这大大阻碍了读其他叶子节点

目前 还不能控制数据表的索引区块大小 这个大小在服务器创建索引文件 ` MYI 时已经设定好了 它根据数据表的索引大小的定义而定 在很多时候 它设置成和I/O缓冲大小一样 在将来 可以改变它的值 并且会全面采用变量 key_cache_block_size

重建索引缓存

索引缓存可以通过修改其参数值在任何时候重建它 例如

mysql>SETGLOBALcold_cache key_buffer_size= * * ;

如果设定索引缓存的结构体变量组件变量 key_buffer_size 或 key_cache_block_size 任何一个的值和它当前的值不一样 服务器就会清空原来的缓存 在新的变量值基础上重建缓存 如果缓存中有任何的 脏 索引块 服务器会先把它们保存起来然后才重建缓存 重新设定其他的索引缓存变量并不会重建缓存