澳门新萄京记叁次表总括音讯未马上更新引致查
分类:数据库

             某日同事丢给我一个看上去复杂的查询(实际就涉及两张表,套来套去)说只是换了日期条件,但一个查询5秒出数据,一个根本查不出来。现在整理下解决过程,及涉及的知识点。  

ANALYZE TABLE SeikyuTbl COMPUTE Statistics FOR TABLE FOR ALL COLUMNS FOR ALL INDEXES ; 

MySQL执行SQL会经过SQL解析和查询优化的过程,解析器将SQL分解成数据结构并传递到后续步骤,查询优化器发现执行SQL查询的最佳方案、生成执行计划。查询优化器决定SQL如何执行,依赖于数据库的统计信息,下面我们介绍MySQL 5.7中innodb统计信息的相关内容。

  若有不正之处,请多多谅解并欢迎批评指正,不甚感激。

一、优化器的优化方式 
Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。 
1、CBO方式:依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。 
2、RBO方式:优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。 
我们要明了,不一定走索引就是优的 ,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时对这个表做全表扫描(full table scan)是最好的。 
二、优化器的优化模式(Optermizer Mode) 
优化模式包括Rule,Choose,First rows,All rows这四种方式: 
Rule:不用多说,即走基于规则的方式。 
Choolse:默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。 
First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。 
All Rows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。 
三、如何设定选用哪种优化模式 
1、Sessions级别 
通过SQL> ALTER SESSION SET OPTIMIZER_MODE=?;来设定。 
2、Instance级别 
我们可以通过在init.ora文件中设定OPTIMIZER_MODE=RULE、OPTIMIZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS去选用以上所提的四种方式,如果你没设定OPTIMIZER_MODE参数则默认用的是Choose这种方式。 
3、语句级别 
这些需要用到Hint,比如: 
SELECT /* RULE */ a.userid,b.name,b.depart_name FROM tf_f_yhda a,tf_f_depart b WHERE a.userid=b.userid; 
四、与CBO相关统计信息的获取(analyze 与 dbms_stats 使用) 
壹、analyze 使用 
1、功能 
a)搜集和删除索引、表和簇的统计信息 
b)验证表、索引和簇的结构 
c)鉴定表和簇和行迁移和行联接 
d)针对analyze的搜集和删除统计信息功能而言,oracle推荐使用DBMS_STATS包来搜集优化信息,DBMS_STATS可以并行的搜集信息,可以搜集分区表的全局信息,进一步来说,按成本的优化器只会使用DBMS_STATS包所统计出来的信息。 
2、可供分析的对象 
INDEX:对索引进行分析,分析的结果会放在USER_INDEXES, ALL_INDEXES,或 DBA_INDEXES中 
分析的内容: 
Depth of the index from its root block to its leaf blocks (BLEVEL) 
Number of leaf blocks (LEAF_BLOCKS) 
Number of distinct index values (DISTINCT_KEYS) 
澳门新萄京,Average number of leaf blocks for each index value (AVG_LEAF_BLOCKS_PER_KEY) 
Average number of data blocks for each index value (for an index on a table) (AVG_DATA_BLOCKS_PER_KEY) 
Clustering factor (how well ordered the rows are about the indexed values) (CLUSTERING_FACTOR) 
TABLE:对表进行分析,分析的结果会放在USER_TABLES, ALL_TABLES, and DBA_TABLES表中,在分析表的时候,oracle也会分析基于函数的index所引用的表达式 
分析的内容: 
Number of rows (NUM_ROWS) 
Number of data blocks below the high water mark (that is, the number of data blocks that have been formatted to receive data, regardless whether they currently contain data or are empty) (BLOCKS) 
Number of data blocks allocated to the table that have never been used (EMPTY_BLOCKS) Average available free space in each data block in bytes (AVG_SPACE) 
Number of chained rows (CHAIN_COUNT) Average row length, including the row's overhead, in bytes (AVG_ROW_LEN) 
PARTITION | SUBPARTITION:对分区表或索引进行分析 
CLUSTER:对簇进行分析,分析的结果会放在ALL_CLUSTERS, USER_CLUSTERS and DBA_CLUSTERS. 
compute_statistics_clause 
语法:COMPUTE [ SYSTEM ] STATISTICS [for_clause] 
对分析对像进行精确的统计,然后把信息存储的数据字典中。可以选择对表或对字段进行分析。 
computed和estimated这两种方式的统计数据都被优化器用来影响sql的执行计划 
如果指定system选项就只统计系统产生的信息 
for_clause FOR TABLE:只统计表 FOR COLUMNS:只统计某个字段 FOR ALL COLUMNS:统计所有字段 FOR ALL INDEXED COLUMNS:统计索引的所有字段 
estimate_statistics_clause 
ESTIMATE [ SYSTEM ] STATISTICS [for_clause][SAMPLE integer { ROWS | PERCENT }] 
只是对部分行做一个大概的统计。适用于大表 
SAMPLE:指定具体统计多少行,如果忽略这个参数的话,oracle会默认为1064行 
ROWS causes:行数 Oracle to sample integer rows of the table or cluster or integer entries from the index. The integer must be at least 1. 
PERCENT causes:百分数 
validation_clauses 
分析REF或是对像的结构 
EG:ANALYZE TABLE employees VALIDATE STRUCTURE CASCADE; 
ANALYZE TABLE customers VALIDATE REF UPDATE; 
3、分析表的限制 
a)不可以分析数据字典表 
b)不可以分析扩展表,但可以用DBMS_STATS来实现这个目的 
c)不可以分析临时表 
d)不可以计算或估计下列字段类型REFs, varrays, nested tables, LOBs (LOBs are not analyzed, they are skipped), LONGs, or object types. 
贰、dbms_stats 使用 
Dbms_stats是oracle8i新增的程序包,它使统计数据的生成和处理更加方便。 
--参数 
estimate_percent        --估算抽样百分比 
method_opt for table    --只统计表  
for all indexed columns --只统计有索引的表列 
for all indexes         --只分析统计相关索引 
--创建统计信息历史保留表 
sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ; 
pl/sql procedure successfully completed 
--导出整个scheme的统计信息 
sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ; 
pl/sql procedure successfully completed 
--分析scheme 
Exec dbms_stats.gather_schema_stats( 
ownname => 'scott', 
options => 'GATHER AUTO', 
estimate_percent => dbms_stats.auto_sample_size, 
method_opt => 'for all indexed columns ', 
degree => 6 ) 
--分析表 
sql> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'work_list',estimate_percent => 10,method_opt=> 'for all indexed columns') ; 
pl/sql procedure successfully completed 
--分析索引 
SQL> exec dbms_stats.gather_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ; 
pl/sql procedure successfully completed 
--如果发现执行计划走错,删除表的统计信息 
SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_list') ; 
pl/sql procedure successfully completed 
--导入表的历史统计信息 
sql> exec dbms_stats.import_table_stats(ownname => 'scott',tabname => 'work_list',stattab => 'stat_table') ; 
pl/sql procedure successfully completed 
--如果进行分析后,大部分表的执行计划都走错,需要导回整个scheme的统计信息 
sql> exec dbms_stats.import_schema_stats(ownname => 'scott',stattab => 'stat_table'); 
pl/sql procedure successfully completed 
--导入索引的统计信息 
SQL> exec dbms_stats.import_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',stattab => 'stat_table') 
--检查是否导入成功 
SQL> select table_name,num_rows,a.blocks,a.last_analyzed from all_tables a where a.table_name='WORK_LIST'; 
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED 
------------------------------ ---------- ---------- ------------- 
WORK_LIST 4005 186 2007-10-12 15 
叁、analyze dbms_stats 区别 
自从Oracle8.1.5引入dbms_stats包,Experts们便推荐使用dbms_stats取代analyze。理由如下 
1,dbms_stats可以并行分析 
2,dbms_stats有自动分析的功能(alter table monitor ) 
3,analyze 分析统计信息的不准确some times 
如果想分析整个用户或数据库,还可以采用工具包,可以并行分析:Dbms_utility(8i以前的工具包) Dbms_stats(8i以后提供的工具包),如:(以下两个dbms_stats最常用) 
dbms_stats.gather_schema_stats(User,estimate_percent=>100,cascade=> TRUE); 
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true); 
总结: 
1、DBMS_STATS的优点 
a) 可以并行进行,对多个用户,多个Table 
b) 可以得到整个分区表的数据和单个分区的数据。 
c) 可以在不同级别上Compute Statistics:单个分区,子分区,全表,所有分区 
d) 可以倒出统计信息 
e) 可以用户自动收集统计信息 
f) 对于分区表,建议使用DBMS_STATS,而不是使用Analyze语句 
g) 对于oracle 9里面的External Table,Analyze不能使用,只能使用DBMS_STATS来收集信息 
2、DBMS_STATS的缺点 
a) 不能Validate Structure 
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息,这两个仍旧需要使用Analyze语句 
c) DBMS_STATS 默认不对索引进行Analyze,因为默认Cascade是False,需要手工指定为True 
五、应用一例: 
1、按用户分析 
BEGIN                                                                         
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>   'SCOTT'   ,   CASCADE=>   TRUE);  
END ;                  
2、单表分析 
ANALYZE   TABLE   MYTABLE   COMPUTE   STATISTICS;         
获取分析语句: 
SELECT   'ANALYZE   TABLE   '||TABLE_NAME||'   COMPUTE   STATISTICS;' FROM   USER_TABLES;         
3、用途举例: 
select a.table_name, a.num_rows from user_tables a where a.num_rows = 0; 
--统计记录数据为空的表,如果事先未进行数据分析,则统计结果可能会不正确 

MySQL统计信息的存储分为两种,非持久化和持久化统计信息。

  请尊重作者劳动成果,转载请标明原文链接:

一、非持久化统计信息

  

非持久化统计信息存储在内存里,如果数据库重启,统计信息将丢失。有两种方式可以设置为非持久化统计信息:

 

INNODB_STATS_PERSISTENT=OFF

一.问题描述


环境:sqlserver 2008r2 

现象:

查询涉及到两张表

ODS_TABLE_A     每日数据700万现在总计60多亿。   已建立索引 分区

MID_TABLE_B      每日数据20万 总计3000万。         已建立索引未分区

当etldate为 '2016-08-12' 及以前的时间时,本查询5秒出数据,

当etldate为 '2016-08-16' 及以后的时间时,本查询出不来数据。

贴上问题sql:做过数据字段处理,针对本篇主题注意点放在查询因为日期的选择不同导致查询时间变的超级慢,而不是改变sql写法比如用临时表,强制索引上。

 

----------《代码开始》

select 

COUNT(distinct(case when COL_USERID3 is null then COL_USERID6 end)) as 'aa',

COUNT(distinct(case when COL_USERID3 is null and COL_USERID7 is not null then COL_USERID6 end)) as 'bb',

COUNT(distinct(case when COL_USERID3 is not null then COL_USERID6 end)) as 'cc',

COUNT(distinct(case when COL_USERID3 is not null and COL_USERID7 is not null then COL_USERID6 end)) as 'dd',

SUM(case when COL_USERID3 IS not null then ee end) as 'ee'

from

(

    select c.COL_USERID3,c.ee,g.COL_USERID6

    from

    (

        select  b.COL_USERID2 as COL_USERID3,COUNT(b.COL_USERID2) as ee

        from

        (

            select COL_USERID as COL_USERID1,min(EventTime) as time1

                from ODS_TABLE_A    

                where  EtlDate = '2016-08-12'

                    and colid LIKE 'heihei%'

                    group by COL_USERID

        )as a
         join
        (
            select COL_USERID as COL_USERID2,eventtime as time2

                from ODS_TABLE_A  

                where EtlDate = '2016-08-12'

                    and ItemId = '1111111111101'

                    and colid like 'haha-%'

                    and colid not like 'haha-skill%'

                    and colid not like 'haha-fine%'

        )as b 

        on a.COL_USERID1 = b.COL_USERID2 and  a.time1 > b.time2

        group by b.COL_USERID2

    )as c
    right join
    (

        select  DISTINCT d.COL_USERID4 as COL_USERID6

        from

        (        
            select distinct COL_USERID as COL_USERID4

            from MID_TABLE_B     

            where etldate = '2016-08-12' 

        )as d

        join

        (
            select COL_USERID AS COL_USERID5

            from ODS_TABLE_A  

            where  EtlDate = '2016-08-12'

                and colid LIKE 'heihei%'

        )as f 

        on d.COL_USERID4 = f.COL_USERID5

    )as g

    on c.COL_USERID3 = g.COL_USERID6

)as i

left join
(
    select COL_USERID as COL_USERID7

    from MID_TABLE_B

    where EtlDate = '2016-08-12' 

        and IsTodayPay = '1'

)as h

on i.COL_USERID6 = h.COL_USERID7

 ----------《代码结束》

 

2 CREATE/ALTER表的参数,

二。解决过程


 1.先看了下上述代码的执行计划如下图初看上去需要用索引的地方都用到了。应该没啥大问题。

可能你注意到系统提示的缺少索引信息,加上去一样效果,不能解决‘2016-08-16’ 查询慢的问题。

 澳门新萄京 1

 

澳门新萄京 2

 

 2.在修改下日期 ,就是把 【所有】  etldate=‘2016-08-12’  的改成  etldate=‘2016-08-16’

看下执行计划:

对不起跑了半个小时没出来,查看估计的执行执行和上面的图类似。

减少涉及到数据集的量 加top 1 我再看执行计划:

不贴图了 结果就是比上面的图少了个 【并行度】**
**

 

初步以为是优化器因为估计行数等不准的原因没选择并行度,赶紧找代码让它强行这样走。

找到一篇宋大师的:强制SQL Server执行计划使用并行提升在复杂查询语句下的性能

 

 二话不说加关键字

OPTION(querytraceon 8649)

 

可是应用到实际发现查询效率无任何改善,久久不出结果。后来问宋大师(感谢宋大神)。他说有些操作是没法并行的,更新统计信息试试先。

一击命中!一击命中!一击命中!

执行如下代码:

update STATISTICS ODS_TABLE_A  --(把ODS_TABLE_A  这个大表统计信息更新)

 

默认情况下,查询优化器已根据需要更新统计信息以改进查询计划;但在某些情况下,你可以通过使用 UPDATE STATISTICS 或存储过程 sp_updatestats 来比默认更新更频繁地更新统计信息,提高查询性能。针对文中此种情况**澳门新萄京记叁次表总括音讯未马上更新引致查询极快,概述MySQL总结音讯_Mysql_脚本之家。新插入的数据没统计信息,大表自动更新统计信息触发自动更新机制频率不够,最好定期更新。**

关于update STATISTICS 就不累述了 :给出相关技术贴连接

更新统计相关知识点传送门

至此问题解决。

STATS_PERSISTENT=0

 

非持久化统计信息在以下情况会被自动更新:

三。总结


  对于大表新插入的数据没及时更新统计信息,导致出现上面文中的现象,一个日期导致查询效率天壤之别的分水岭(查12号前5秒出数据,查12号后死活不出来。) 

    解决办法是大表自动更新统计信息触发自动更新机制频率不够,定期更新。

  

1执行ANALYZE TABLE

2 innodb_stats_on_metadata=ON情况下,执SHOW TABLE STATUS, SHOW INDEX, 查询 INFORMATION_SCHEMA下的TABLES, STATISTICS

3 启用--auto-rehash功能情况下,使用mysql client登录

5 距上一次更新统计信息,表1/16的数据被修改

非持久化统计信息的缺点显而易见,数据库重启后如果大量表开始更新统计信息,会对实例造成很大影响,所以目前都会使用持久化统计信息。

二、持久化统计信息

5.6.6开始,MySQL默认使用了持久化统计信息,即INNODB_STATS_PERSISTENT=ON,持久化统计信息保存在表mysql.innodb_table_stats和mysql.innodb_index_stats。

持久化统计信息在以下情况会被自动更新:

1INNODB_STATS_AUTO_RECALC=ON

情况下,表中10%的数据被修改

2增加新的索引

innodb_table_stats是表的统计信息,innodb_index_stats是索引的统计信息,各字段含义如下:

innodb_table_stats

database_name

last_update

统计信息最后一次更新时间

clustered_index_size

sum_of_other_index_sizes

innodb_index_stats

database_name

last_update

统计信息最后一次更新时间

sample_size

stat_description

为更好的理解innodb_index_stats,建一张测试表做说明:

CREATE TABLE t1 ( a INT, b INT, c INT, d INT, e INT, f INT, PRIMARY KEY , UNIQUE KEY i2uniq  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,的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) 单个表可以指定该表的采样:

澳门新萄京记叁次表总括音讯未马上更新引致查询极快,概述MySQL总结音讯_Mysql_脚本之家。ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=40;

经测试,此处STATS_SAMPLE_PAGES的最大值是65535,超出会报错。

目前MySQL并没有提供直方图的功能,某些情况下仅仅更新统计信息不一定能得到准确的执行计划,只能通过index hint的方式指定索引。新版本8.0会增加直方图功能,让我们期待MySQL越来越强大的功能吧!

本文由澳门新萄京发布于数据库,转载请注明出处:澳门新萄京记叁次表总括音讯未马上更新引致查

上一篇:minimal图像和文字化教育程,数据库二进制包安装 下一篇:没有了
猜你喜欢
热门排行
精彩图文