MySQL仅有一个简单的统计信息却没有直方图,My

2019-08-09 作者:数据库   |   浏览(149)

 

 

Code:

 

 

原文出处:

 

直方图是表上某个字段在按照一定百分比和规律采样后的数据分布的一种描述,最重要的作用之一就是根据查询条件,预估符合条件的数据量,为sql执行计划的生成提供重要的依据
在MySQL 8.0之前的版本中,MySQL仅有一个简单的统计信息却没有直方图,没有直方图的统计信息可以说是没有任何意义的。
MySQL 8.0新特性之一就是开始支持统计信息的直方图,这个概念很早就提出来了,抽空具体尝试了一下使用方法。

--  统计

select count(*) as '当天记录数' from web_product where date(p_createtime) = curdate(); 
select count(*) as '当天记录数' from web_product where to_days(p_createtime) = to_days(now());

SELECT count(*) as '昨天记录数'  FROM web_product WHERE TO_DAYS( NOW( ) ) - TO_DAYS( p_createtime) <= 1;


-- 前一天
select count(*) as '前一天记录数' from web_product where date(p_createtime) = date_sub(curdate(),interval 1 day);

select count(*) as '本周记录数' from web_product where date(p_createtime) >= date_sub(curdate(),interval 7 day)    
and date(p_createtime) <=  date_sub(curdate(),interval 1 day);

SELECT count(*) as '7天的记录数' FROM web_product where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(p_createtime);

-- 查询近30天的记录
SELECT * FROM web_product where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(p_createtime);

-- 查询本月的记录
SELECT * FROM web_product WHERE DATE_FORMAT(p_createtime,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');


-- 查询上一月的记录
SELECT * FROM web_product WHERE PERIOD_DIFF(date_format(now(),'%Y%m'),date_format(p_createtime,'%Y%m'))=1;


-- 查询本季度数据
select * from web_product where QUARTER(p_createtime)=QUARTER(now());

-- 查询上季度数据
select * from web_product where QUARTER(p_createtime)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

-- 查询本年数据
select * from web_product  where YEAR(p_createtime)=YEAR(NOW());

-- 查询上年数据
select * from web_product where year(p_createtime)=year(date_sub(now(),interval 1 year));


-- 查询当前这周的数据 
SELECT * FROM web_product WHERE YEARWEEK(date_format(p_createtime,'%Y-%m-%d')) = YEARWEEK(now());

-- 查询上周的数据
SELECT * FROM web_product WHERE YEARWEEK(date_format(p_createtime,'%Y-%m-%d')) = YEARWEEK(now())-1;

-- 查询当前月份的数据
select * from web_product   where date_format(p_createtime,'%Y-%m')=date_format(now(),'%Y-%m');
-- 查询距离当前现在6个月的数据
select p_name,p_createtime from web_product where p_createtime between date_sub(now(),interval 6 month) and now();

-- 按年汇总,统计:

select sum(mymoney) as totalmoney, count(*) as sheets from web_product group by date_format(p_createtime, '%Y');

select date_format(p_createtime, '%Y') as 'year',count(*) as sheets from web_product group by date_format(p_createtime, '%Y');


select DATE_FORMAT(p_createtime,'%Y') years,sum(duration) dur from web_product tv where 1=1 GROUP BY years ORDER BY years desc;

select DATE_FORMAT(p_createtime,'%Y') years,count(*) as sheets  from web_product  where 1=1 GROUP BY years ORDER BY years desc;

SELECT DATE_FORMAT(p_createtime,'%Y') years,COUNT(*) COUNT FROM web_product GROUP BY years;


SELECT year(p_createtime) as 'yearname',count(*) as'sheet' FROM `web_product` group by  yearname;

SELECT count(*), year(p_createtime) yearname  FROM `web_product`  group by  yearname;

SELECT year(p_createtime) yearname  FROM `web_product`;

SELECT  DISTINCT(year(p_createtime)) yearname  FROM `web_product`;

SELECT  COUNT(DISTINCT(year(p_createtime))) yearname  FROM `web_product`;


SELECT year(Addtime) as 'yearname',count(*) as'sheet' FROM `duwebstat` group by  yearname;

SELECT  COUNT(DISTINCT(year(Addtime))) yearname  FROM `duwebstat`;

-- 按月汇总,统计:

select sum(mymoney) as totalmoney, count(*) as sheets from web_product group by date_format(p_createtime, '%Y-%m');

select date_format(p_createtime, '%Y-%m') as 'month',count(*) as sheets from web_product group by date_format(p_createtime, '%Y-%m');

select DATE_FORMAT(p_createtime,'%Y%m') months,count(*) as sheets from web_product  where 1=1 GROUP BY months ORDER BY months desc;

SELECT DATE_FORMAT(p_createtime,'%Y%m') months,COUNT(*) COUNT FROM web_product GROUP BY months;

SELECT year(p_createtime) as 'yearname',month(`p_createtime`) as 'monthname',count(*) as'sheet' FROM `web_product` group by  yearname,monthname;

SELECT year(Addtime) as 'yearname',month(`Addtime`) as 'monthname',count(*) as'sheet' FROM `duwebstat` group by  yearname,monthname;

SELECT  count(DISTINCT(concat(cast(year(Addtime) as char(50)),cast(month(Addtime) as char(50)))))  FROM duwebstat;

select DATE_FORMAT(Addtime,'%Y-%m') months,count(*) as sheets from duwebstat  where 1=1 GROUP BY months ORDER BY months desc;


-- 按季度汇总,统计:

select sum(mymoney) as totalmoney,count(*) as sheets from web_product group by concat(date_format(p_createtime, '%Y'),FLOOR((date_format(p_createtime, '%m') 2)/3));

select count(*) as sheets from web_product group by concat(date_format(p_createtime, '%Y'),FLOOR((date_format(p_createtime, '%m') 2)/3));

select concat(date_format(p_createtime,'%Y'),FLOOR((date_format(p_createtime, '%m') 2)/3)) quarters,sum(duration) dur from web_product  where 1=1  GROUP BY quarters ORDER BY quarters desc;

select concat(date_format(p_createtime,'%Y'),FLOOR((date_format(p_createtime, '%m') 2)/3)) quarters,count(*) as sheets  from web_product  where 1=1  GROUP BY quarters ORDER BY quarters desc;


SELECT id, year(p_createtime),quarter(`p_createtime`) FROM `web_product`;

SELECT year(p_createtime) as 'yearname',quarter(`p_createtime`) as 'quartername',count(*) as'sheet' FROM `web_product` group by  yearname,quartername;

SELECT  DISTINCT(concat(cast(year(p_createtime) as char(50)),cast(quarter(p_createtime) as char(50))))  FROM web_product;

SELECT  count(DISTINCT(concat(cast(year(p_createtime) as char(50)),cast(quarter(p_createtime) as char(50)))))  FROM web_product;


select CAST(122 as CHAR);

select now();

select quarter(now());

SELECT CAST(123 AS CHAR); 

select concat(DATE_FORMAT(now(),'%Y'),cast(quarter(now()) as char(20)));



SELECT year(Addtime) as 'yearname',quarter(`Addtime`) as 'quartername',count(*) as'sheet' FROM `duwebstat` group by  yearname,quartername;


SELECT  COUNT(DISTINCT(year(Addtime))) yearname  FROM `duwebstat`;


SELECT  count(DISTINCT(concat(cast(year(Addtime) as char(50)),cast(quarter(Addtime) as char(50)))))  FROM duwebstat;

--  按周统计
select DATE_FORMAT(p_createtime,'%Y%u') weeks,count(*) as sheets from web_product where 1=1 GROUP BY weeks ORDER BY weeks desc;

select DATE_FORMAT(p_createtime,'%Y-%u') weeks,count(*) as sheets from web_product where 1=1 GROUP BY weeks ORDER BY weeks desc;

SELECT DATE_FORMAT(p_createtime,'%Y%u') weeks,COUNT(*) COUNT FROM web_product GROUP BY weeks;

SELECT DATE_FORMAT(Addtime,'%Y-%u') weeks,COUNT(*) COUNT FROM duwebstat GROUP BY weeks;


select DISTINCT(DATE_FORMAT(p_createtime,'%Y-%u')) from web_product;

SELECT year(p_createtime) yearname,week(p_createtime) weeks,COUNT(*) COUNT FROM web_product GROUP BY weeks,yearname;

SELECT year(Addtime) yearname,week(Addtime) weeks,COUNT(*) COUNT FROM duwebstat GROUP BY weeks,yearname;

select DATE_FORMAT(Addtime,'%Y%u') weeks,count(*) as sheets from duwebstat where 1=1 GROUP BY weeks ORDER BY weeks desc;

select count(DISTINCT(DATE_FORMAT(Addtime,'%Y-%u'))) from duwebstat;

-- 按日统计
-- https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

SELECT DATE_FORMAT(p_createtime,'%Y%m%d') days,COUNT(*) COUNT FROM web_product GROUP BY days; 

SELECT DATE_FORMAT(p_createtime,'%Y-%m-%d') days,COUNT(*) COUNT FROM web_product GROUP BY days; 

SELECT DATE_FORMAT(Addtime,'%Y-%m-%d') days,COUNT(*) as sheet FROM duwebstat GROUP BY days;

select count(DISTINCT(DATE_FORMAT(Addtime,'%Y-%m-%d'))) from duwebstat;


--
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');


-- 
-- 按小时:Hour

select date_format(p_createtime, '%Y-%m-%d %H'),count(*) as sheets from web_product group by date_format(p_createtime, '%Y-%m-%d %H');

select date_format(p_createtime, '%Y-%m-%d %H'),count(*) as sheets from web_product group by date_format(p_createtime, '%Y-%m-%d %H') limit 0,30;


select date_format(Addtime, '%Y-%m-%d %H') as hours,count(*) as sheet from duwebstat group by date_format(Addtime, '%Y-%m-%d %H');


select sum(mymoney) as totalmoney,count(*) as sheets from web_product group by date_format(p_createtime, '%Y-%m-%d %H ');

-- 查询 本年度的数据:

SELECT * FROM web_product WHERE year(FROM_UNIXTIME(p_createtime)) = year(curdate());

-- 查询数据附带季度数:

SELECT id, quarter(FROM_UNIXTIME(p_createtime)) FROM web_product;

-- 查询 本季度的数据:

SELECT * FROM web_product WHERE quarter(FROM_UNIXTIME(p_createtime)) = quarter(curdate());

-- 本月统计:

select * from web_product where month(p_createtime) = month(curdate()) and year(p_createtime) = year(curdate());

-- 本周统计:

select * from web_product where month(p_createtime) = month(curdate()) and week(p_createtime) = week(curdate());

 

/**
 * 生成mysql数据字典
 */
//配置数据库
$dbserver   = "127.0.0.1";
$dbusername = "root";
$dbpassword = "xxxxxx";
$database      = "table_name";
//其他配置
$title = '数据字典';
$pdo=new PDO("mysql:host=".$dbserver.";dbname=".$database,$dbusername,$dbpassword);
$pdo->query('SET NAMES utf8');
$table_result=$pdo->query('show tables');
$arr=$table_result->fetchAll(PDO::FETCH_ASSOC);

//取得所有的表名
foreach ($arr as $val){
    $tables[]['TABLE_NAME'] =$val['Tables_in_table_name'];
}

//循环取得所有表的备注及表中列消息
foreach ($tables AS $k=>$v) {
    $sql  = 'SELECT * FROM ';
    $sql .= 'INFORMATION_SCHEMA.TABLES ';
    $sql .= 'WHERE ';
    $sql .= "table_name = '{$v['TABLE_NAME']}'  AND table_schema = '{$database}'";
    $table_result = $pdo->query($sql);
    $t=$table_result->fetchAll(PDO::FETCH_ASSOC);



    foreach($t as $v) {
        $tables[$k]['TABLE_COMMENT'] = $v['TABLE_COMMENT'];
    }
    $sql  = 'SELECT * FROM ';
    $sql .= 'INFORMATION_SCHEMA.COLUMNS ';
    $sql .= 'WHERE ';
    $sql .= "table_name = '{$v['TABLE_NAME']}' AND table_schema = '{$database}'";
    $fields = array();
    $field_result = $pdo->query($sql);
    $t=$field_result->fetchAll(PDO::FETCH_ASSOC);
    foreach ($t as $v) {
        $fields[] = $v;
    }
    $tables[$k]['COLUMN'] = $fields;
}

$html = '';
//循环所有表
foreach ($tables AS $k=>$v) {
    //$html .= '<p><h2>'. $v['TABLE_COMMENT'] . ' </h2>';
    $html .= '<table  border="1" cellspacing="0" cellpadding="0" align="center">';
    $html .= '<caption>' . $v['TABLE_NAME'] .'  '. $v['TABLE_COMMENT']. '</caption>';
    $html .= '<tbody><tr><th>字段名</th><th>数据类型</th><th>默认值</th>  
     <th>允许非空</th>  
     <th>自动递增</th><th>备注</th></tr>';
    $html .= '';
    foreach ($v['COLUMN'] AS $f) {
        $html .= '<tr><td class="c1">' . $f['COLUMN_NAME'] . '</td>';
        $html .= '<td class="c2">' . $f['COLUMN_TYPE'] . '</td>';
        $html .= '<td class="c3"> ' . $f['COLUMN_DEFAULT'] . '</td>';
        $html .= '<td class="c4"> ' . $f['IS_NULLABLE'] . '</td>';
        $html .= '<td class="c5">' . ($f['EXTRA']=='auto_increment'?'是':' ') . '</td>';
        $html .= '<td class="c6"> ' . $f['COLUMN_COMMENT'] . '</td>';
        $html .= '</tr>';
    }
    $html .= '</tbody></table></p>';
}
//输出
echo '<html>  
 <head>  
 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />  
 <title>'.$title.'</title>  
 <style>  
 body,td,th {font-family:"宋体"; font-size:12px;}  
 table{border-collapse:collapse;border:1px solid #CCC;background:#efefef;}  
 table caption{text-align:left; background-color:#fff; line-height:2em; font-size:14px; font-weight:bold; }  
 table th{text-align:left; font-weight:bold;height:26px; line-height:26px; font-size:12px; border:1px solid #CCC;}  
 table td{height:20px; font-size:12px; border:1px solid #CCC;background-color:#fff;}  
 .c1{ width: 120px;}  
 .c2{ width: 120px;}  
 .c3{ width: 70px;}  
 .c4{ width: 80px;}  
 .c5{ width: 80px;}  
 .c6{ width: 270px;}  
 </style>  
 </head>  
 <body>';
echo '<h1 style="text-align:center;">'.$title.'</h1>';
echo $html;
echo '</body></html>';

之前写过MSSQL相关统计信息的一点东西,在原理上都是一致的,

  

MySQL的10个基本性能技巧

  

 

 

 

照旧,直接上例子,造数据,创建一个测试环境

与所有的关系数据库一样,MySQL正如一头怪兽一般,
它可能会在接到通知的一瞬间陷入停顿,让你的应用程序陷入困境,让你的业务处于危险之中。真是的情况是,常见的错误是导致MySQL性能问题的根源。
工作负载或配置陷阱中的一些微妙之处常常会掩盖这些信息,为了确保MySQL服务器以最快的速度运行,提供稳定一致的性能,消除这些错误是很重要的。
幸运的是,很多MySQL的性能问题都有相似的解决方案,使的故障排除和调优MySQL成为一项易于管理的任务。

参考(转载): 

create table test
(
    id int auto_increment primary key,
    name varchar(100),
    create_date datetime ,
    index (create_date desc)
);


USE `db01`$$

DROP PROCEDURE IF EXISTS `insert_test_data`$$

CREATE DEFINER=`root`@`%` PROCEDURE `insert_test_data`()
BEGIN
    DECLARE v_loop INT;
    SET v_loop = 100000;
    WHILE v_loop>0 DO
        INSERT INTO test(NAME,create_date)VALUES (UUID(),DATE_ADD(NOW(),INTERVAL -RAND()*100000 MINUTE) );
        SET v_loop = v_loop - 1;
    END WHILE;
END$$

DELIMITER ;

 

MySQL中统计信息的创建,不同于MSSQL,MySQL统计信息不依赖于索引,需要单独创建,语法如下

MySQL性能提示1:配置您的工作负载

--创建字段上的统计直方图信息
ANALYZE TABLE test UPDATE HISTOGRAM ON create_date,name WITH 16 BUCKETS;
--删除字段上的统计直方图信息
ANALYZE TABLE test DROP HISTOGRAM ON create_date

了解服务器究竟把时间花在哪些地方的最佳方法是分析服务器的工作负载,
通过分析工作负载,您可以导出最大代价的查询以进行进一步调优,当向服务器发出请求的时候,时间就是最重要的指标,
你几乎不关心任何事情,只关心它完成得有多快。配置工作负载的最佳方法是使用MySQL Enterprise Monitor的查询分析器或Percona工具包中的pt-query-digest之类的工具。

1,可以一次性创建多个字段的统计信息,系统会逐个创建列出的字段上的统计信息,统计信息不依赖于索引,这一点与MSSQL不同(当然MSSQL也可以抛开索引独立创建统计信息)
2,BUCKETS值是一个必须提供的参数,默认值为1000,范围是1-1024,这一点也不同与MSSQL也不一样,MSSQL是有一个类似的最大值为200的步长(step)字段
3,一般来说,数据量较大的情况下,对于不重复或者重复性不高的数据,BUCKETS值越大,描述出来的统计信息越详细
4,统计信息的具体内容在 information_schema.column_statistics中,但是可读性并不好,可以根据需求自行解析(出来一种自己喜欢的格式)

这些工具捕获服务器执行的查询,并返回一个任务表,按照响应时间的顺序进行排序,立即将代价最大和最耗时的任务排在最前面,这样您就可以看到您的工作重点在哪里。
工作负载分析工具将类似的查询组合在一起,允许您查看缓慢的查询,以及快速但多次执行的查询。

与sqlserver中的统计信息一样,理论上,在准确性与取样百分比(BUCKETS)是成正比的,当然生成统计信息的代价也就越大,
至于BUCKETS与统计信息的取样百分比,以及综合代价,笔者暂时没有找到相关的资料。

译者注:找到一些top的sql或者说是执行频率高的sql,这部分是关注的重点

如下是通过ANALYZE TABLE test UPDATE HISTOGRAM ON create_date WITH 4 BUCKETS;创建的统计信息直方图
可以发现直方图的HISTOGRAM字段是一个JSON格式的字符串,可读性并不好。

 

图片 1

MySQL性能提示2:了解四种基本资源

想到了sqlserver中DBCC SHOW_STATISTICS的直方图信息,如下的格式,直方图中的数据分布情况看起来非常清晰直观

本文由小鱼儿玄机30码发布于数据库,转载请注明出处:MySQL仅有一个简单的统计信息却没有直方图,My

关键词: 小鱼儿玄机30码