概述MySQL统计信息
MySQL执行SQL会经过SQL解析和查询优化的过程，解析器将SQL分解成数据结构并传递到后续步骤，查询优化器发现执行SQL查询的最佳方案、生成执行计划。查询优化器决定SQL如何执行，依赖于数据库的统计信息，下面我们介绍MySQL 5.7中innodb统计信息的相关内容。
MySQL统计信息的存储分为两种，非持久化和持久化统计信息。
一、非持久化统计信息
非持久化统计信息存储在内存里，如果数据库重启，统计信息将丢失。有两种方式可以设置为非持久化统计信息：
|
1 全局变量， INNODB_STATS_PERSISTENT=OFF |
|
2 CREATE/ALTER表的参数， STATS_PERSISTENT=0 |
非持久化统计信息在以下情况会被自动更新：
|
1 执行ANALYZE TABLE |
|
2 innodb_stats_on_metadata=ON情况下，执SHOW TABLE STATUS, SHOW INDEX, 查询 INFORMATION_SCHEMA下的TABLES, STATISTICS |
|
3 启用--auto-rehash功能情况下，使用mysql client登录 |
|
4 表第一次被打开 |
|
5 距上一次更新统计信息，表1/16的数据被修改 |
非持久化统计信息的缺点显而易见，数据库重启后如果大量表开始更新统计信息，会对实例造成很大影响，所以目前都会使用持久化统计信息。
二、持久化统计信息
5.6.6开始，MySQL默认使用了持久化统计信息，即INNODB_STATS_PERSISTENT=ON，持久化统计信息保存在表mysql.innodb_table_stats和mysql.innodb_index_stats。
持久化统计信息在以下情况会被自动更新：
|
1 INNODB_STATS_AUTO_RECALC=ON 情况下，表中10%的数据被修改 |
| 2 增加新的索引 |
innodb_table_stats是表的统计信息，innodb_index_stats是索引的统计信息，各字段含义如下：
|
innodb_table_stats |
|
|
database_name |
数据库名 |
|
table_name |
表名 |
|
last_update |
统计信息最后一次更新时间 |
|
n_rows |
表的行数 |
|
clustered_index_size |
聚集索引的页的数量 |
|
sum_of_other_index_sizes |
其他索引的页的数量 |
|
innodb_index_stats |
|
|
database_name |
数据库名 |
|
table_name |
表名 |
|
index_name |
索引名 |
|
last_update |
统计信息最后一次更新时间 |
|
stat_name |
统计信息名 |
|
stat_value |
统计信息的值 |
|
sample_size |
采样大小 |
|
stat_description |
类型说明 |
为更好的理解innodb_index_stats，建一张测试表做说明：
CREATE TABLE t1 ( a INT, b INT, c INT, d INT, e INT, f INT, PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f) ) ENGINE=INNODB;
写入数据如下：
查看t1表的统计信息，需主要关注stat_name和stat_value字段
tat_name=size时：stat_value表示索引的页的数量
stat_name=n_leaf_pages时：stat_value表示叶子节点的数量
stat_name=n_diff_pfxNN时：stat_value表示索引字段上唯一值的数量，此处做一下具体说明：
1、n_diff_pfx01表示索引第一列distinct之后的数量，如PRIMARY的a列，只有一个值1，所以index_name='PRIMARY' and stat_name='n_diff_pfx01'时，stat_value=1。
2、n_diff_pfx02表示索引前两列distinct之后的数量，如i2uniq的e,f列，有4个值，所以index_name='i2uniq' and stat_name='n_diff_pfx02'时，stat_value=4。
3、对于非唯一索引，会在原有列之后加上主键索引，如index_name='i1' and stat_name='n_diff_pfx03'，在原索引列c,d后加了主键列a，(c,d,a)的distinct结果为2。
了解了stat_name和stat_value的具体含义，就可以协助我们排查SQL执行时为什么没有使用合适的索引，例如某个索引n_diff_pfxNN的stat_value远小于实际值，查询优化器认为该索引选择度较差，就有可能导致使用错误的索引。
三、统计信息不准确的处理
我们查看执行计划，发现未使用正确的索引，如果是innodb_index_stats中统计信息差别较大引起，可通过以下方式处理：
1、手动更新统计信息，注意执行过程中会加读锁：
ANALYZETABLE TABLE_NAME;
2、如果更新后统计信息仍不准确，可考虑增加表采样的数据页，两种方式可以修改：
a) 全局变量INNODB_STATS_PERSISTENT_SAMPLE_PAGES，默认为20；
b) 单个表可以指定该表的采样：
ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=40;
经测试，此处STATS_SAMPLE_PAGES的最大值是65535，超出会报错。
目前MySQL并没有提供直方图的功能，某些情况下（如数据分布不均）仅仅更新统计信息不一定能得到准确的执行计划，只能通过index hint的方式指定索引。新版本8.0会增加直方图功能，让我们期待MySQL越来越强大的功能吧！
相关推荐
-
mysql中数据统计的技巧备忘录
mysql 作为常用数据库,操作贼六是必须的,对于数字操作相关的东西,那是相当方便,本节就来拎几个统计案例出来供参考! order订单表,样例如下: CREATE TABLE `yyd_order` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `order_nid` varchar(50) NOT NULL, `status` varchar(50) NOT NULL DEFAUL
-
php 广告点击统计代码(php+mysql)
php 广告点击统计代码,昨天晚上有几个IDC网想与本站合作放些广告,但是我想看看广告效果后想了就写了一个简单的广告统计代码了,这里只是等的统计不能IP限制或是恶心点击等等了. 先来创建数据库. CREATE TABLE IF NOT EXISTS `ad_count` ( `ad_id` int(8) NOT NULL auto_increment, `ad_hit` int(8) NOT NULL default '0', `ad_name` varchar(200) character s
-
mysql按照天统计报表当天没有数据填0的实现代码
1.问题复现: 按照天数统计每天的总数,如果其中有几天没有数据,那么group by 返回会忽略那几天,如何填充0?如下图,统计的10-3~10-10 7天的数据,其中只有8号和10号有数据,这样返回,数据只有2个,不符合报表统计的需求.期望没有值填0 2.换个思维: 我们用一组连续的天数作为左表然后left join 要查询的数据 最后group by.:连续天数表 t1 left join 业务数据 t2 group by t1.day ,如下图: SELECT t1.`day`, COU
-
MySQL按时间统计数据的方法总结
在做数据库的统计时,经常会需要根据年.月.日来统计数据,然后配合echarts来制作可视化效果. 数据库:MySQL 思路 按照时间维度进行统计的前提是需要数据库中有保留时间信息,建议是使用MySQL自带的datetime类型来记录时间. `timestamp` datetime DEFAULT NULL, 在MySQL中对于时间日期的处理的函数主要是DATE_FORMAT(date,format).可用的参数如下 格式 描述 %a 缩写星期名 %b 缩写月名 %c 月,数值 %D 带有英文前缀
-
PHP+MySQL实现对一段时间内每天数据统计优化操作实例
本文实例讲述了PHP+MySQL实现对一段时间内每天数据统计优化操作.分享给大家供大家参考,具体如下: 在互联网项目中,对项目的数据分析必不可少.通常会统计某一段时间内每天数据总计变化趋势调整营销策略.下面来看以下案例. 案例 在电商平台中通常会有订单表,记录所有订单信息.现在我们需要统计某个月份每天订单数及销售金额数据从而绘制出如下统计图,进行数据分析. 订单表数据结构如下: order_id order_sn total_price enterdate 25396 A4E610E250C2D
-
概述MySQL统计信息
MySQL执行SQL会经过SQL解析和查询优化的过程,解析器将SQL分解成数据结构并传递到后续步骤,查询优化器发现执行SQL查询的最佳方案.生成执行计划.查询优化器决定SQL如何执行,依赖于数据库的统计信息,下面我们介绍MySQL 5.7中innodb统计信息的相关内容. MySQL统计信息的存储分为两种,非持久化和持久化统计信息. 一.非持久化统计信息 非持久化统计信息存储在内存里,如果数据库重启,统计信息将丢失.有两种方式可以设置为非持久化统计信息: 1 全局变量, INNODB_STATS
-
MySQL 8.0统计信息不准确的原因
前言 不管是Oracle还是MySQL,新版本推出的新特性,一方面给产品带来功能.性能.用户体验等方面的提升,另一方面也可能会带来一些问题,如代码bug.客户使用方法不正确引发问题等等. 案例分享 MySQL 5.7下的场景 (1)首先,创建两张表,并插入数据 mysql> select version(); +------------+ | version() | +------------+ | 5.7.30-log | +------------+ 1 row in set (0.00 s
-
详解mysql持久化统计信息
一.持久化统计信息的意义: 统计信息用于指导mysql生成执行计划,执行计划的准确与否直接影响到SQL的执行效率:如果mysql一重启 之前的统计信息就没有了,那么当SQL语句来临时,那么mysql就要收集统计信息然后再生成SQL语句的执行 计划.如果能在关闭mysql的时候就把统计信息保存起来,那么在启动时就不要再收集一次了,这种处理方式 有助于效率的提升. 二.统计信息准确与否也同样重要: 第一目中我们说明了"持久化统计信息的意义",我们的假设统计信息是有用的,是准确的:如果统计信
-
实例讲解MySQL统计库表大小
统计每个库每个表的大小是数据治理的其中最简单的一个要求,本文将从抽样统计结果及精确统计结果两方面来统计MySQL的每个库每个表的数据量情况. 1.统计预估数据量 mysql数据字典库information_schema里记录了统计的预估数据量(innodb引擎表不准确,MyISAM引擎表准确)及数据大小.索引大小及表碎片的大小等信息. 如果想了解每个库及表的大概数据量级,可以直接查information_schema.tables进行统计即可.例如: SELECT table_schema,ta
-
通过Python收集汇聚MySQL 表信息的实例详解
目录 一.需求 二.公共基础文件说明 1.配置文件 2.定义声明db连接 3.定义声明访问db的操作 三.主要代码 3.1 创建保存数据的脚本 3.2 收集的功能脚本 一.需求 统计收集各个实例上table的信息,主要是表的记录数及大小. 收集的范围是cmdb中所有的数据库实例. 二.公共基础文件说明 1.配置文件 配置文为db_servers_conf.ini,假设cmdb的DBServer为119.119.119.119,单独存放收集监控数据的DBserver为110.110.110.110
-
SQL Server自动更新统计信息的基本算法
自动更新统计信息的基本算法是: · 如果表格是在 tempdb 数据库表的基数是小于 6,自动更新到表的每个六个修改. · 如果表的基数是大于 6,但小于或等于 500,更新状态每 500 的修改. · 如果基数大于 500,表为更新统计信息时(500 + 20%的表)发生了更改. · 表变量为基数的更改不会触发自动更新统计信息. 注意:此严格意义上讲,SQL Server 计算基数为表中的行数. 注意:除了基数,该谓语的选择性也会影响 AutoStats 生成.这意味着该统计信息可能无法更新的
-
Oracle 12c新特性之如何检测有用的多列统计信息详解
前言 之前和大家分享过Oracle 11g下的一个新特性--收集多列统计信息(http://www.jb51.net/article/109514.htm),今天和大家分享Oracle 12c的一个新特性--自动检测有用列组信息.二者相得益彰,大家可以具体情况酌情使用. 言归正传,我们可以针对一个表,基于特定的工作负荷,通过使用DBMS_STATS.SEED_COL_USAGE和REPORT_COL_USAGE来确定我们需要哪些列组.当你不清除需要创建哪个扩展统计信息时,这个技术是非常有用的.需
-
Oracle 11g收集多列统计信息详解
前言 通常,当我们将SQL语句提交给Oracle数据库时,Oracle会选择一种最优方式来执行,这是通过查询优化器Query Optimizer来实现的.CBO(Cost-Based Optimizer)是Oracle默认使用的查询优化器模式.在CBO中,SQL执行计划的生成,是以一种寻找成本(Cost)最优为目标导向的执行计划探索过程.所谓成本(Cost)就是将CPU和IO消耗整合起来的量化指标,每一个执行计划的成本就是经过优化器内部公式估算出的数字值. 我们在写SQL语句的时候,经常会碰到w
-
在ASP.NET 2.0中操作数据之十五：在GridView的页脚中显示统计信息
导言 除了需要了解产品的单价.库存量和订货量,并按等级排序之外,用户可能还对统计信息感兴趣,比如说平均价格.库存总量等等.这些统计信息常常显示在报表最下面的一个统计行中.GridView控件可以含有一个页脚行,我们可以通过编程将统计数据插入到它的单元格里面去.这个任务给了我们以下3个挑战: 1.配置GridView以显示它的页脚行 2.确定统计数据.即我们应该如何计算平均价格还有库存总量? 3.将统计信息插入到页脚行的相应的单元格中 在本节教程中,我们将会看到如何去征服这些挑战.另外呢,我们将创
-
PHP+MySQL统计该库中每个表的记录数并按递减顺序排列的方法
本文实例讲述了PHP+MySQL统计该库中每个表的记录数并按递减顺序排列的方法.分享给大家供大家参考,具体如下: 这是一段简单的代码,可实现统计该数据库中每个表的记录数,并按递减顺序排列的功能 $host = '127.0.0.1'; $port = 3306; $dbname = 'test'; $username = 'root'; $password = ''; function ee($p) { if(PHP_SAPI == 'cli') { echo "\n"; }else{