精华内容
下载资源
问答
  • PHP做数据统计分析

    千次阅读 2019-03-25 14:45:31
    前段时间的主要工作是开发统计系统, 统计公司产品的安装量和回访量,统计数据则由客户端调用C接口写入mysql数据库,即我们只需要分析客户端写入的原始数据即可。下面是对这个项目的一个总结: 系统评估 1、预估...

    前段时间的主要工作是开发统计系统, 统计公司产品的安装量和回访量,统计数据则由客户端调用C接口写入mysql数据库,即我们只需要分析客户端写入的原始数据即可。下面是对这个项目的一个总结:

    系统评估

    • 1、预估当前每天的回访量有大几百万,随着其它产品的不断推广, 要统计的数据可能越来越多。
    • 2、统计的数据有比较强的约束关系。对于一条安装数据,必须判断之前唯一安装表中是否存在该记录, 若存在则再根据版本判断升级或重装,否则为新装数据,回访数据类似逻辑。所以,如果要出按小时统计数据,则必须把前一个小时数据处理完之后才可以处理后面的数据;前一天的数据处理完之后才可以处理后一天的数据。
    • 3、团队中都擅长的是PHP。hadoop或其他大数据处理方式经验薄弱,面临学习成本和一些未知因素。

    所以最终还是选择用PHP+Mysql来统计,前期应该可以撑一撑。

    整体思路

    接下来对每个步骤进行梳理:

    • 1、C接口直接写数据到安装表和回访表,原始数据的表采用按年分表,按天分区。原始数据量比较大,也不适合PHP写入。
    • 2、转移数据。原始表记录数比较多,为了尽可能的减少与原始表的耦合,这里做了一个转移的动作,将原始表的最新安装、回访数据转移到近期安装、回访表中。近期表只保留3天的数据,即近期回访表的数据会维持在2000w左右。这个动作是否可以用触发器来完成?这里没有采用触发器,个人始终认为触发器和业务依赖太紧,当原始数据需要调整时触发器也要相应调整,觉得不是很方便,因为修改触发器势必会影响写入操作。
    • 3、数据更新。因为需要一条一条判断数据为新装、重装或者新用户、老用户,区域等,所以有这个更新的过程,更新每一条记录的状态。这里将可能是系统瓶颈所在。
    • 4、小时报表。 数据更新完之后即可根据该数据出报表,因为统计的字段8个左右,所以累计到一定时间之后,这个表的数据也将会很多,前台不适合直接从这里取报表数据。
    • 5、其他报表。 可根据小时报表出天的报表,或者出特定字段的报表等等,这是很方便的,同时数据量也将成倍的减少。

    系统实现

    项目使用CI开发,实现的步骤就没太多说的,查询的时候注意下索引的先后顺序就行了,系统到目前还没出现因不中索引而引起的问题。不过程序上的一些调整可以记录下:

    • 1、 报表采用highchart实现,但最开始是直接在控制器获取到报表数据后传到视图,当一个页面有多个报表的时候需要把这些数据一次性读取出来之后页面才会显示。运行了一段时间发现打开慢,也不方便扩展,所以把报表统一改成了ajax调用。
    • 2、 菜单的调整。最开始未意识到后面有更多的产品移植过来,所以对菜单也进行过一次调整。
    • 3、 crontab的调整。最开始所有的crontab脚本都放置在一个控制器中,随着不断的增加发现越来越难控了,难以按产品区分,有些也不用了。为了理清这些脚本以及执行频率,对这里- 进行了一次调整。
    • 4、主从调整。运行一段时间后增加了个从库(32G),所有查询的操作从从库拿,调整之后前台报表表现明显。
    • 5、模型的调整。 这个还未深入调整^_^,因为有新老系统的原因, 模型太多,以及很多业务逻辑写在模型中,模型很重。

    上面的每一个调整并不需要多少时间, 但对不段增长的系统是很有好处的,每当它要倾斜时,我们就把它扶正,希望它能坚持更久一点。

    系统新增功能和调整

    调整用户唯一ID。

    IOS产品原先用uuid来判断唯一性,但7.0之后发现uuid不唯一了,所以统计系统部分产品要将唯一值由uuid替换为序列号,但一直以来都是uuid为唯一ID,统计这边也直接以uuid为唯一键了。这意味着唯一键要调整,大部分表结构都需要调整了。

    原始表有的有序列号,有的没有,所以首先是原始表统一增加序列号字段,因为转移的数据只将特定的字段值写进去,所以原始表的调整对统计不会有影响。同时原始表已有2.5亿数据,直接调整表结构基本不可能。所以采取新建一张调整后的表,rename一下即可,rename的过程是很快的,rename之前的几千条未转移的数据再手动转移一下。

    统计这边将在近期表新增一个唯一字段, 唯一字段不依赖固定值。因为即便调整了, 有一些产品还是以uuid为准,唯一值在转移的过程中判断即可。 统计系统调整时先停下所有的脚本,近期表直接删除重建即可,唯一表因为需要处理,边转移边处理一下即可,报表数据保留原有。所以整个过程下来调整并不算大,只是因为数据量比较大,处理觉得麻烦一点而已。

    增加一个产品

    系统中已经增加了好几个产品了, 这里增加产品的接口是用php实现的。即客户端调用php页面,php写数据库,回访数据大概每天100w左右。运行几天后发现php接口机器挂了, nignx进程数太多。原因就是统计系统比较忙时,数据库压力比较大,php一条一条写入很慢, 很多进程都在等待,于是爆了。。。

    针对这个问题的处理方法是,php接口直接写数据到文本,然后脚本定时load数据到数据库。

    历史数据处理

    有个产品需要对历史数据进行重新统计,历史数据有1亿多。因为历史数据和新数据之间的字段、值等需要进行一次处理,所以采用 SELECT INTO OUTFILE的方式导出,1.6亿数据中导出1.2亿大概5分钟左右。导出之后的的文件有9G左右,直接一次LOAD mysql会超出binlog的限制。所以设置了binglog为3G,然后对原数据按每1000w行进行切割,在一个个导入。

    如果导入的表已经建好索引,开始导入1000w要半个多小时,导入了4000w数据后发现奇慢无比。后来重新导,导入的表未建立索引,1000w数据大概需要9分钟左右。不过后来增加索引花了大概2个半小时。

    对原始数据的处理也是一个问题,为了提升效率,比较大的数据采用多进程跑,比如开10多个进程同时跑一个小时的数据,二三十万数据3分钟就搞定。但当系统中的这些进程碰到一起时,系统就开始慢了, 所以只能用程序去控制下。

    系统总结

    1、 到目前位置系统运行还算正常,但随着新功能的不断增加,这也是个挑战。如果只是针对单个产品,一般的业务,用php来处理,日2000w数据问题应该不是很大。

    2、系统监控。到目前位置做个几个统计系统了,前面一个是最完善的,有很多监控,可以很快发现问题。当前这个系统数据量是比较大的,但监控还比较薄弱,或者已经有很多潜在的问题被忽略,所以做好监控是有必要的。

    3、 使用php运行crontab要防止脚本重复执行,限制起来也很简单,可以用php的exec函数去查看一下当前脚本是否正在执行(需要服务器未限制exec函数),如果正在执行就直接退出,给个简单的判断方法:

    function get_process_num($process_name) { return exec('ps -ef | grep "'.$process_name.'" | grep -v "grep" | wc -l'); }

     

    展开全文
  • MySql数据统计

    千次阅读 2019-05-06 12:23:43
    MySql数据统计 根据数据的状态或者查询条件进行数据动态统计 直接使用状态查询数据,然后"count(1)"查询数据,SQL编写相对繁琐; 这里使用“case when”区分条件状态,然后用“sum”函数计算符合条件的数据数量即此...

    MySql数据统计

    性别百分比统计

    select count(1) as allData,
    ifnull(sum(case when u_sex = '1' then 1 else 0 end), 0) as nanData,
    ifnull(sum(case when u_sex = '2' then 1 else 0 end), 0) as nvData,
    ifnull(sum(case when u_sex = '1' || u_sex = '2' then 0 else 1 end), 0) as unknownData
    from t_user
    

    在这里插入图片描述
    在这里插入图片描述

    客户类型数量统计

    select case c_type
    when '1' then '大客户'
    when '2' then '一般客户'
    when '3' then '代理商'
    end  as clientType,
    count(1) as clinetTypeNum
    from t_client
    GROUP BY c_type
    

    在这里插入图片描述
    在这里插入图片描述

    月份订单金额统计

    select 
    concat(year(r_create_time), '-', month(r_create_time)) as lineTime,
    IFNULL(sum(r_price), 0) as totalPrice
    from t_order
    where year (r_create_time)= '2019'
    group by year (r_create_time), month (r_create_time)
    

    在这里插入图片描述
    在这里插入图片描述

    展开全文
  • MySQL中的统计数据

    万次阅读 2021-09-06 21:16:28
    基于索引统计数据的成本计算 有时候使用索引执行查询时会有许多单点区间,例如使用in语句就很容易产生非常多的单点区间,比如下边这个查询(下边查询语句中的…表示还有很多参数): select * from t_emp where ...

    基于索引统计数据的成本计算

    有时候使用索引执行查询时会有许多单点区间,例如使用in语句就很容易产生非常多的单点区间,比如下边这个查询(下边查询语句中的…表示还有很多参数):

     select * from t_emp where hire_date in ('1890-11-10','1990-12-10'...'1991-11-12');
    

    很显然,这个查询可能使用到的索引就是idx_hire_date,由于这个索引并不是唯一的二级索引,所以并不能确定一个单点区间对应的二级索引记录的条数有多少,需要我们去计算。就是先获取索引对应的B+树的区间最左记录和区间最右记录,然后再计算这两条记录之间有多少记录(记录条数少的时候可以做到精确计算,多的时候只能估算)。MySQL把这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive。

    只有少数几个单点区间的话,使用index dive的方式去计算这些单点区间对应的记录数也不是什么问题,如果in语句里20000个参数怎么办?这就意味着MySQL的查询优化器为了计算这些单点区间对应的索引记录条数,要进行20000次index dive操作,这性能损耗就很大,搞不好计算这些单点区间对应的索引记录条数的成本比直接全表扫描的成本都大了。MySQL考虑到了这种情况,所以提供了一个系统变量eq_range_index_dive_limit,我们看一下在MySQL5.7.35中这个系统变量的默认值:

    mysql> show variables like '%dive%';
    +---------------------------+-------+
    | Variable_name             | Value |
    +---------------------------+-------+
    | eq_range_index_dive_limit | 200   |
    +---------------------------+-------+
    1 row in set (0.02 sec)
    

    也就是说如果我们的in语句中的参数个数小于200个的话,将使用index dive的方式计算各个单点区间对应的记录条数,如果大于或等于200个的话,可就不能使用index dive了,要使用所谓的索引统计数据来进行估算,也叫index statistics。

    怎么个估算法?像会为每个表维护一份统计数据一样,MySQL也会为表中的每一个索引维护一份统计数据,比如我们查看一下t_emp的各个索引的统计数据可以这么写:

    mysql> show index from t_emp;
    +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | t_emp |          0 | PRIMARY        |            1 | emp_no      | A         |      299645 |     NULL | NULL   |      | BTREE      |         |               |
    | t_emp |          1 | idx_hire_date  |            1 | hire_date   | A         |        5590 |     NULL | NULL   |      | BTREE      |         |               |
    | t_emp |          1 | idx_birth_date |            1 | birth_date  | A         |        4770 |     NULL | NULL   |      | BTREE      |         |               |
    +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    3 rows in set (0.00 sec)
    
    属性名描述
    Table索引所属表的名称。
    Non_unique索引列的值是否是唯一的,聚簇索引和唯一二级索引的该列值为0,普通二级索引该列值为1。
    Key_name索引的名称。
    Seq_in_index索引列在索引中的位置,从1开始计数。对普通索引来说,永远都是1,对联合索引才会大于1。比如对于联合索引u_idx_day_status(insert_time,order_status,expire_time),来说,insert_time、order_status、expire_time对应的位置分别是1、2、3。
    Column_name索引列的名称。
    Collation索引列中的值是按照何种排序方式存放的,值为A时代表升序存放,为NULL 时代表降序存放。
    Cardinality索引列中不重复值的数量。
    Sub_part的前n个字符或字节建立索引,这个属性表示的就是那个n值。如果对完整的列建立索引的话,该属性的值就是NULL。
    Packed索引列如何被压缩,NULL 值表示未被压缩。
    Null该索引列是否允许存储NULL值。
    Index_type使用索引的类型,我们最常见的就是BTREE,其实也就是B+树索引。
    Comment索引列注释信息。
    Index_comment索引注释信息。

    Cardinality属性,Cardinality直译过来就是基数的意思,表示索引列中不重复值的个数。比如对于一个一万行记录的表来说,某个索引列的Cardinality属性是10000,那意味着该列中没有重复的值,如果Cardinality属性是1的话,就意味着该列的值全部是重复的。不过需要注意的是,对于InnoDB存储引擎来说,使用SHOW INDEX语句展示出来的某个索引列的Cardinality属性是一个估计值,并不是精确的。

    前边说道,当in语句中的参数个数大于或等于系统变量eq_range_index_dive_limit的值的话,就不会使用index dive的方式计算各个单点区间对应的索引记录条数,而是使用索引统计数据,这里所指的索引统计数据指的是这两个值:

    • 使用show table status展示出的Rows值,也就是一个表中有多少条记录。

    • 使用show index语句展示出的Cardinality属性。

    mysql> show table status like 't_emp';
    +-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
    | Name  | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
    +-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
    | t_emp | InnoDB |      10 | Dynamic    | 299645 |             50 |    15220736 |               0 |      9469952 |   2097152 |           NULL | 2021-08-19 07:19:44 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
    +-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
    1 row in set (0.00 sec)
    

    结合Rows和Cardinality,我们可以针对索引列,计算出平均一个值重复多少次。

    一个值的重复次数≈ Rows ÷ Cardinality

    以t_emp表的idx_hire_date索引为例,它的Rows值是299645,它对应的Cardinality 值是5590,所以我们可以计算hire_date列平均单个值的重复次数就是:299645÷5590≈53(条)

    此时再看上边那条查询语句:

     select * from t_emp where hire_date in ('1890-11-10','1990-12-10'...'1991-11-12');
    

    假设in语句中有20000个参数的话,就直接使用统计数据来估算这些参数。每个单点区间大约对应53条记录,所以总共需要回表的记录数就是:20000 x 53= 1060000

    使用统计数据来计算单点区间对应的索引记录条数比index dive的方式简单,但是它的致命弱点就是:不精确!。使用统计数据算出来的查询成本与实际所需的成本可能相差非常大。

    大家需要注意一下,在MySQL 5.7.3以及之前的版本中,eq_range_index_dive_limit 的默认值为10,之后的版本默认值为200。所以如果大家采用的是5.7.3以及之前的版本的话,很容易采用索引统计数据而不是index dive的方式来计算查询成本。当你的查询中使用到了in查询,但是却实际没有用到索引,就应该考虑一下是不是由于eq_range_index_dive_limit值太小导致的。

    InnoDB中的统计数据

    我们前边唠叨查询成本的时候经常用到一些统计数据,比如通过SHOW
    TABLE STATUS 可以看到关于表的统计数据,通过SHOW INDEX可以看到关于索引
    的统计数据,那么这些统计数据是怎么来的呢?它们是以什么方式收集的呢?

    统计数据存储方式

    InnoDB提供了两种存储统计数据的方式:

    • 永久性的统计数据,这种统计数据存储在磁盘上,也就是服务器重启之后这
      些统计数据还在。

    • 非永久性的统计数据,这种统计数据存储在内存中,当服务器关闭时这些这
      些统计数据就都被清除掉了,等到服务器重启之后,在某些适当的场景下才会重
      新收集这些统计数据。

    MySQL给我们提供了系统变量innodb_stats_persistent来控制到底采用哪种
    方式去存储统计数据。在MySQL5.6.6之前,innodb_stats_persistent的值默认是
    OFF,也就是说InnoDB的统计数据默认是存储到内存的,之后的版本中
    innodb_stats_persistent 的值默认是ON,也就是统计数据默认被存储到磁盘中。

    mysql> show variables like 'innodb_stats_persistent';
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | innodb_stats_persistent | ON    |
    +-------------------------+-------+
    1 row in set (0.00 sec)
    

    不过最近的MySQL版本都基本不用基于内存的非永久性统计数据了,所以
    我们也就不深入研究。

    InnoDB默认是以表为单位来收集和存储统计数据的,也就是说我们可
    以把某些表的统计数据(以及该表的索引统计数据)存储在磁盘上,把另一些表
    的统计数据存储在内存中。怎么做到的呢?我们可以在创建和修改表的时候通过
    指定STATS_PERSISTENT属性来指明该表的统计数据存储方式:

    CREATE TABLE 表名(...) Engine=InnoDB, STATS_PERSISTENT = (1|0);
    ALTER TABLE 表名Engine=InnoDB, STATS_PERSISTENT = (1|0);
    
    • 当STATS_PERSISTENT=1时,表明我们想把该表的统计数据永久的存储到磁
      盘上。

    • 当STATS_PERSISTENT=0 时,表明我们想把该表的统计数据临时的存储到
      内存中。

    • 如果我们在创建表时未指定STATS_PERSISTENT属性,那默认采用系统
      变量innodb_stats_persistent的值作为该属性的值。

    基于磁盘的永久性统计数据

    当我们选择把某个表以及该表索引的统计数据存放到磁盘上时,实际上是把
    这些统计数据存储到了两个表里:

    
    mysql> SHOW TABLES FROM mysql LIKE 'innodb%';
    +---------------------------+
    | Tables_in_mysql (innodb%) |
    +---------------------------+
    | innodb_index_stats        |
    | innodb_table_stats        |
    +---------------------------+
    2 rows in set (0.00 sec)
    

    可以看到,这两个表都位于mysql系统数据库下边,其中:

    • innodb_table_stats存储了关于表的统计数据,每一条记录对应着一个表的统
      计数据。

    • innodb_index_stats存储了关于索引的统计数据,每一条记录对应着一个索
      引的一个统计项的统计数据。

    innodb_table_stats

    
    mysql> SELECT * FROM mysql.innodb_table_stats where table_name='t_emp';
    +---------------+------------+---------------------+--------+----------------------+--------------------------+
    | database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
    +---------------+------------+---------------------+--------+----------------------+--------------------------+
    | employees     | t_emp      | 2021-08-19 07:19:44 | 299645 |                  929 |                      578 |
    +---------------+------------+---------------------+--------+----------------------+--------------------------+
    1 row in set (0.00 sec)
    

    直接看一下这个innodb_table_stats表中的各个列都是干嘛的:

    列名说明
    database_name数据库名
    table_name表名
    last_update本条记录最后更新时间
    n_rows表中记录的条数
    clustered_index_size表的聚簇索引占用的页面数量
    sum_of_other_index_sizes表的其他索引占用的页面数量

    n_rows统计项的收集

    InnoDB统计一个表中有多少行记录是这样的:
    按照一定算法(并不是纯粹随机的)选取几个叶子节点页面,计算每个页面
    中主键值记录数量,然后计算平均一个页面中主键值的记录数量乘以全部叶子节
    点的数量就算是该表的n_rows值。

    可以看出来这个n_rows值精确与否取决于统计时采样的页面数量,MySQL
    用名为innodb_stats_persistent_sample_pages的系统变量来控制使用永久性的统
    计数据时,计算统计数据时采样的页面数量。该值设置的越大,统计出的n_rows
    值越精确,但是统计耗时也就最久;该值设置的越小,统计出的n_rows 值越不
    精确,但是统计耗时特别少。所以在实际使用是需要我们去权衡利弊,该系统变
    量的默认值是20。

    
    mysql> show variables like 'innodb_stats_persistent_sample_pages';
    +--------------------------------------+-------+
    | Variable_name                        | Value |
    +--------------------------------------+-------+
    | innodb_stats_persistent_sample_pages | 20    |
    +--------------------------------------+-------+
    1 row in set (0.01 sec)
    

    InnoDB默认是以表为单位来收集和存储统计数据的,我们也可以单独设置
    某个表的采样页面的数量,设置方式就是在创建或修改表的时候通过指定
    STATS_SAMPLE_PAGES属性来指明该表的统计数据存储方式:

    CREATE TABLE 表名(...) Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采
    样页面数量;
    ALTER TABLE 表名Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页
    面数量;
    

    如果我们在创建表的语句中并没有指定STATS_SAMPLE_PAGES属性的话,将
    默认使用系统变量innodb_stats_persistent_sample_pages的值作为该属性的值。

    clustered_index_size和sum_of_other_index_sizes统计项的收集牵涉到很具
    体的InnoDB表空间的知识和存储页面数据的细节,我们就不深入讲解了。

    innodb_index_stats

    
    mysql> select * from mysql.innodb_index_stats where table_name='t_emp';
    +---------------+------------+----------------+---------------------+--------------+------------+-------------+-----------------------------------+
    | database_name | table_name | index_name     | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
    +---------------+------------+----------------+---------------------+--------------+------------+-------------+-----------------------------------+
    | employees     | t_emp      | PRIMARY        | 2021-08-19 07:19:40 | n_diff_pfx01 |     299645 |          20 | emp_no                            |
    | employees     | t_emp      | PRIMARY        | 2021-08-19 07:19:40 | n_leaf_pages |        886 |        NULL | Number of leaf pages in the index |
    | employees     | t_emp      | PRIMARY        | 2021-08-19 07:19:40 | size         |        929 |        NULL | Number of pages in the index      |
    | employees     | t_emp      | idx_birth_date | 2021-08-19 07:19:44 | n_diff_pfx01 |       4770 |          20 | birth_date                        |
    | employees     | t_emp      | idx_birth_date | 2021-08-19 07:19:44 | n_diff_pfx02 |     300069 |          20 | birth_date,emp_no                 |
    | employees     | t_emp      | idx_birth_date | 2021-08-19 07:19:44 | n_leaf_pages |        231 |        NULL | Number of leaf pages in the index |
    | employees     | t_emp      | idx_birth_date | 2021-08-19 07:19:44 | size         |        289 |        NULL | Number of pages in the index      |
    | employees     | t_emp      | idx_hire_date  | 2021-08-19 07:19:40 | n_diff_pfx01 |       5590 |          20 | hire_date                         |
    | employees     | t_emp      | idx_hire_date  | 2021-08-19 07:19:40 | n_diff_pfx02 |     300069 |          20 | hire_date,emp_no                  |
    | employees     | t_emp      | idx_hire_date  | 2021-08-19 07:19:40 | n_leaf_pages |        231 |        NULL | Number of leaf pages in the index |
    | employees     | t_emp      | idx_hire_date  | 2021-08-19 07:19:40 | size         |        289 |        NULL | Number of pages in the index      |
    +---------------+------------+----------------+---------------------+--------------+------------+-------------+-----------------------------------+
    11 rows in set (0.00 sec)
    

    直接看一下这个innodb_index_stats表中的各个列都是干嘛的:

    列名说明
    database_name数据库名
    table_name表名
    index_name索引名
    last_update本条记录最后更新时间
    stat_name统计项的名称
    stat_value对应的统计项的值
    sample_size为生成统计数据而采样的页面数量
    stat_description对应的统计项的描述

    innodb_index_stats表的每条记录代表着一个索引的一个统计项。我们来具体看一下一个索引都有哪些统计项:

    • n_leaf_pages:表示该索引的叶子节点占用多少页面。

    • size:表示该索引共占用多少页面。

    • n_diff_pfxNN:表示对应的索引列不重复的值有多少。

    n_diff_pfxNN其中的NN长得有点
    儿怪呀,啥意思呢?
    其实NN可以被替换为01、02、03… 这样的数字。比如对于联合索引u_idx_day_status(insert_time,order_status,expire_time)来说:

    • n_diff_pfx01 表示的是统计insert_time这一个列不重复的值有多少。

    • n_diff_pfx02 表示的是统计insert_time、order_status这两个列组合起来不重
      复的值有多少。

    • n_diff_pfx03 表示的是统计insert_time、order_status、expire_time这三个列组
      合起来不重复的值有多少。

    • n_diff_pfx04 表示的是统计insert_time、order_status、expire_time、id这四个列
      组合起来不重复的值有多少。

    对于普通的二级索引,并不能保证它的索引列值是唯一的,此时只有在索引列上加
    上主键值才可以区分两条索引列值都一样的二级索引记录。
    对于主键和唯一二级索引则没有这个问题,它们本身就可以保证索引列值的
    不重复,所以也不需要再统计一遍在索引列后加上主键值的不重复值有多少。

    在计算某些索引列中包含多少不重复值时,需要对一些叶子节点页面进行采
    样,sample_size列就表明了采样的页面数量是多少。
    对于有多个列的联合索引来说,采样的页面数量是:
    innodb_stats_persistent_sample_pages × 索引列的个数。

    当需要采样的页面数量大于该索引的叶子节点数量的话,就直接采用全表扫
    描来统计索引列的不重复值数量了。所以大家可以在查询结果中看到不同索引对
    应的size列的值可能是不同的。

    定期更新统计数据

    随着我们不断的对表进行增删改操作,表中的数据也一直在变化,
    innodb_table_stats和innodb_index_stats表里的统计数据也在变化。MySQL提供
    了如下两种更新统计数据的方式:

    开启innodb_stats_auto_recalc

    系统变量innodb_stats_auto_recalc决定着服务器是否自动重新计算统计数
    据,它的默认值是ON,也就是该功能默认是开启的。每个表都维护了一个变量,
    该变量记录着对该表进行增删改的记录条数,如果发生变动的记录数量超过了表
    大小的10%,并且自动重新计算统计数据的功能是打开的,那么服务器会重新进
    行一次统计数据的计算,并且更新innodb_table_stats 和innodb_index_stats 表。
    不过自动重新计算统计数据的过程是异步发生的,也就是即使表中变动的记录数
    超过了10%,自动重新计算统计数据也不会立即发生,可能会延迟几秒才会进行
    计算。

    innodb_stats_auto_recalc默认为on,表示开启。

    
    mysql> show variables like 'innodb_stats_auto_recalc';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | innodb_stats_auto_recalc | ON    |
    +--------------------------+-------+
    1 row in set (0.02 sec)
    

    再一次强调,InnoDB 默认是以表为单位来收集和存储统计数据的,我们也
    可以单独为某个表设置是否自动重新计算统计数的属性,设置方式就是在创建或
    修改表的时候通过指定STATS_AUTO_RECALC 属性来指明该表的统计数据存储方
    式:

    CREATE TABLE 表名(...) Engine=InnoDB, STATS_AUTO_RECALC = (1|0);
    ALTER TABLE 表名Engine=InnoDB, STATS_AUTO_RECALC = (1|0);
    
    • 当STATS_AUTO_RECALC=1时,表明我们想让该表自动重新计算统计数据。

    • 当STATS_AUTO_RECALC=0 时,表明不想让该表自动重新计算统计数据。

    • 如果我
      们在创建表时未指定STATS_AUTO_RECALC 属性,那默认采用系统变量
      innodb_stats_auto_recalc的值作为该属性的值。

    手动调用ANALYZE TABLE语句来更新统计信息

    如果innodb_stats_auto_recalc系统变量的值为OFF的话,我们也可以手动
    调用ANALYZE TABLE语句来重新计算统计数据,比如我们可以这样更新关于
    t_emp表的统计数据:

    
    mysql> select * from mysql.innodb_table_stats where table_name='t_emp';
    +---------------+------------+---------------------+--------+----------------------+--------------------------+
    | database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
    +---------------+------------+---------------------+--------+----------------------+--------------------------+
    | employees     | t_emp      | 2021-08-19 07:19:44 | 299645 |                  929 |                      578 |
    +---------------+------------+---------------------+--------+----------------------+--------------------------+
    1 row in set (0.00 sec)
    
    mysql> analyze table employees.t_emp;
    +-----------------+---------+----------+----------+
    | Table           | Op      | Msg_type | Msg_text |
    +-----------------+---------+----------+----------+
    | employees.t_emp | analyze | status   | OK       |
    +-----------------+---------+----------+----------+
    1 row in set (0.05 sec)
    
    mysql> select * from mysql.innodb_table_stats where table_name='t_emp';
    +---------------+------------+---------------------+--------+----------------------+--------------------------+
    | database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
    +---------------+------------+---------------------+--------+----------------------+--------------------------+
    | employees     | t_emp      | 2021-08-30 08:48:24 | 299468 |                  929 |                      578 |
    +---------------+------------+---------------------+--------+----------------------+--------------------------+
    1 row in set (0.00 sec)
    

    ANALYZE TABLE语句会立即重新计算统计数据,也就是这个过程是同步的,在表
    中索引多或者采样页面特别多时这个过程可能会特别慢最好在业务不是很繁忙
    的时候再运行。

    手动更新innodb_table_stats和innodb_index_stats表

    其实innodb_table_stats 和innodb_index_stats表就相当于一个普通的表一样,
    我们能对它们做增删改查操作。这也就意味着我们可以手动更新某个表或者索引
    的统计数据。比如说我们想把t_emp表关于行数的统计数据更改一下可以这
    么做:

    1. 更新innodb_table_stats 表。

    2. 让MySQL 查询优化器重新加载我们更改过的数据。

    更新完innodb_table_stats只是单纯的修改了一个表的数据,需要让MySQL
    查询优化器重新加载我们更改过的数据,运行下边的命令就可以了:

    FLUSH TABLE t_emp;
    
    展开全文
  • mysql 按周统计数据

    千次阅读 2018-07-11 17:20:24
    做数据统计的时候,经常会有按周统计的要求,刚好mysql 有一个week的函数,可以使用,比原来的 通过 DATE_FORMAT进行转换简单方便多了。 实例:按周统计新增客户数 SELECT week(c.CREATED_DATE),count(0) from...

    mysql 按周统计数据

    在做数据统计的时候,经常会有按周统计的要求,刚好mysql 有一个week的函数,可以使用,比原来的 通过 DATE_FORMAT进行转换简单方便多了。

    实例:按周统计新增客户数

    SELECT week(c.CREATED_DATE),count(0) from customer c GROUP BY WEEK(c.CREATED_DATE)

    周一为一周的第一天

    SELECT week(c.CREATED_DATE,1),count(0) from customer c GROUP BY WEEK(c.CREATED_DATE,1)

    其中会出现周的开始时间不一致,导致数据出错的问题。是因为mysql 默认已周日为一周的第一天,国内喜欢用周一作为一周的第一天。这时候可以通过week方法的model参数来调整

    WEEK(date[,mode])

    Mode First day of week Range Week 1 is the first week …
    0 Sunday 0-53 with a Sunday in this year
    1 Monday 0-53 with 4 or more days this year
    2 Sunday 1-53 with a Sunday in this year
    3 Monday 1-53 with 4 or more days this year
    4 Sunday 0-53 with 4 or more days this year
    5 Monday 0-53 with a Monday in this year
    6 Sunday 1-53 with 4 or more days this year
    7 Monday 1-53 with a Monday in this year

    展开全文
  • python数据统计分析

    万次阅读 多人点赞 2019-02-17 09:08:25
      scipy包中的stats模块和statsmodels包是python常用的数据分析工具,scipy.stats以前有一个models子模块,后来被移除了。这个模块被重写并成为了现在独立的statsmodels包。  scipy的stats包含一些比较基本的工具...
  • 小猫统计——快速数据统计专家介绍
  • 数据统计分析方法

    万次阅读 2018-08-10 20:17:52
    数据统计分析方法: 描述统计、假设检验、相关分析、方差分析、回归分析、聚类分析、主成分与因子分析、时间序列分析、决策树。 回归分析 研究自变量与因变量之间的关系、可以用来预测因变量的值、 线性回归使用...
  • 小米运动数据导出并个性化统计

    千次阅读 2019-09-21 16:41:36
    小米运动数据是没有对外提供接口的,它本身提供的数据统计有不足之处,比如体重数据,在一天中不同时间体重数据时不一样的,把所有数据混合在一起看,是很难得出体重变化趋势的。所以,最好是能够获取到这些数据,...
  • MATLAB数据分析与统计

    万人学习 2017-05-06 13:18:12
    全面学习MATLAB在数据统计分析领域中的知识
  • 数据统计分析常用指标

    万次阅读 2018-03-05 16:26:23
    下面是数据统计分析常用的指标或术语: 1.平均数  一般指算术平均数。算术平均数是指,全部数据累加除以数据个数。它是非常重要的基础性指标。  几何平均数:适用于对比率数据的平均,并主要用于计算数据平均...
  • 数据统计特性

    千次阅读 2017-08-15 17:05:55
    数据分布特征可以从集中趋势、离中趋势及分布形态三个方面进行描述。  1、平均指标是在反映总体的一般水平或分布的集中趋势的指标。测定集中趋势的平均指标有两类:位置平均数和数值平均数。位置平均数是根据变量值...
  • sql数据统计——按时间统计

    千次阅读 2017-07-12 15:24:00
    sql 数据分年、月、日的统计, SELECT  YEAR|MONTH|DAY ( 日期的字段 ),  SUM( 需要统计的字段, 比如销售额什么的 ) FROM  表 WHERE YEAR|MONTH|DAY ( 那个日期的字段 ) = 2010 -- 这里假设你要查 ...
  • 基于云服务创建离线数据统计分析服务 离线数据统计分析 使用数据工厂、数据计算服务产品,可对多源数据(DB,FILE)整合分析,制定周期性的调度任务,提供任务监控及预警、数据统计分析等功能,可对接BI报表产品可视...
  • MongoDB aggregate做统计数据(group进阶)。
  • MATLAB-数据统计分析

    万次阅读 2019-08-17 15:39:12
    一、统计量 表示位置的统计量—平均值和中位数. 平均值(或均值,数学期望): 中位数:将数据由小到大排序后位于中间位置的那个数值. 2. 表示变异程度的统计量—标准差、方差和极差. 标准差: 它是各个...
  • 在概率和统计方法中,经常需要绘制图表从而直观地展现数据。下面简介几种常用统计图表绘图函数.
  • 移动互联网数据分析 移动客户端流量统计 移动客户端数据统计 原创文章,转载请注明原地址 http://blog.csdn.net/stevenprime 在这个公司两年多了,期间了移动客户端数据分析,视频播放器数据分析,短地址数据分析等...
  • 不学统计的Python数据分析课程都不是好课程,不如抽空多看看统计学的内容。 本文介绍 Python数据分析师 统计学中需要掌握数据种类方面的知识。 分类数据和数值数据 俗称离散型数据(分类数据)、连续型数据(数值...
  • Python实现Mysql数据统计

    千次阅读 2018-12-29 17:05:44
    这个python脚本,可以统计mysql中各个数据库各个表的数据条数并且导入到桌面excel文件,可以清楚的知数据库的数据情况,还可以防止在日常操作中误删数据不知情。
  • es聚合查询与多维度数据统计

    万次阅读 多人点赞 2019-07-28 10:16:06
    根据业务需求,对文档中的某个或某几个字段进行数据的分组并一些指标数据统计分析,比如要计算一批文档中某个业务字段的总数,平均数,最大最小值等,都属于聚合的范畴 以上两个概念后是理解下...
  • 篮球赛场数据统计系统数据库设计实例 研究内容(篮球赛场数据统计系统)  1)比赛前对球队、球员各项基本信息的录入功能;  2)比赛时对进球得分、各种进球类型以及裁判评判情况等数据和信息的记录功能;  ...
  • 抓取国家统计数据

    千次阅读 2017-05-16 12:10:39
    爬虫抓取国家统计数据
  • 统计学原理 统计数据的类型

    千次阅读 2020-04-15 18:41:10
    统计数据的分类按计量层次按收集方法按时间状况分类的数据顺序的数据数值型数据观察的数据实验的数据截面的数据时序的数据 统计数据的分类 (按计量尺度分) 1.分类数据(categorical data) 只能归于某一类别的非数字...
  • 网站CNZZ数据统计的实现方式

    千次阅读 2018-10-08 11:17:18
    为了方便网站管理者 [ 以下简称:站长 ] 统计分析网站受访情况,很多站长选择使用了 友盟+ 提供的站长 CNZZ数据统计 接口。 样式截图如下: 这里介绍以下两个业务实现方法: cnzz数据统计申请设置与引入使用...
  • R语言之数据统计

    千次阅读 2020-04-11 20:46:33
    我们常常会遇到一个问题,...首先看一下数据 data<-read.table('1.txt',sep='\t',header=T,row.names=1) head(data) table(data$names1) table(data$names1,data$names2) 二、统计矩阵中每一列中某一个数值的...
  • js实现数据统计分类

    千次阅读 2018-11-23 09:53:10
    let msg = [{ name: "张意",address: "北京"}, { name: "李尔",job: "前端开发",salary: "13k"}, { job: "python开发",salary: "...,job: &
  • 数据的描述性统计

    万次阅读 2020-11-12 21:56:11
    一、集中趋势的度量 集中趋势: 1.一组数据向其中心值靠拢的倾向和程度 2.测量集中趋势就是寻找数据水平的代表值和中心...5.主要用于分类数据,也可以用于数值数据和顺序数据 1.2 顺序数据:中位数(Me)和分位数 1.排
  • 极差又被称为范围差或全距(Range),以R表示,是用来表示统计资料中的变异量数,其最大值与最小值之间的差距,即最大值减最小值后所得之数据。 它是标志值变动的最大范围,它是测定标志变动的最简单的指标。移动极...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,447,575
精华内容 579,030
关键字:

如何做数据统计