grafana基于mysql数据源的数据可视化展示

行云流水
2022-03-15 / 10 评论 / 1,273 阅读 / 正在检测是否收录...

前言

Grafana是一个开源的,拥有丰富dashboard和图表编辑的指标分析平台,支持多种数据源。下面介绍一些基于mysql数据源的面板配置方法。

端口流量图

数据来源(zabbix监控采集)

  • 表结构

    MariaDB [zreport]> desc history_uint;
    +--------+---------------------+------+-----+---------+-------+
    | Field  | Type                | Null | Key | Default | Extra |
    +--------+---------------------+------+-----+---------+-------+
    | itemid | bigint(20) unsigned | NO   | MUL | NULL    |       |
    | clock  | int(11)             | NO   |     | 0       |       |
    | value  | bigint(20) unsigned | NO   |     | 0       |       |
    | ns     | int(11)             | NO   |     | 0       |       |
  • rows in set (0.001 sec)

  • 数据样本

    MariaDB [zreport]> select * from history_uint order by clock desc limit 10;
    +--------+------------+-----------+-----------+
    | itemid | clock      | value     | ns        |
    +--------+------------+-----------+-----------+
    |  36336 | 1639155600 | 122559104 | 354559595 |
    |  36505 | 1639155600 |  18404320 | 462215215 |
    |  36506 | 1639155600 |  20577192 | 932727023 |
    |  36513 | 1639155600 |  15658232 | 932727023 |
    |  36337 | 1639155600 | 116371000 | 778553999 |
    |  36553 | 1639155600 | 597137480 | 932727023 |
    |  36385 | 1639155600 | 174508568 | 827251666 |
    |  36552 | 1639155600 | 628670928 | 462215215 |
    |  36504 | 1639155600 |  15002368 | 462215215 |
    |  36384 | 1639155600 |        88 | 223615600 |
  • rows in set (1.025 sec)

  • 95计费

    #in端口ID元组   IN_ITEMS
    SELECT 'IN_ITEMS' AS __text,  '(36384, 36385)' as __value;
    
    #95计费去掉的点数
    SELECT 'DROPCOUNTS' AS __text, ROUND((${__to}+1000-${__from})/6000000) AS __value;
    
    #原始数据表名TABLE_R
    SELECT 'TABLE_R' AS __text, 'history_uint' AS __value;
    
    #95计费In端口速率(原始数据) INRATE
    SELECT 'INRATE' AS __text, (SUM(value)/1000/1000/1000) as __value
    FROM 
    (SELECT  
    date_format(from_unixtime(clock), '%Y-%m-%d %H:%i') as time, 
    value  
    FROM  ${TABLE_R}  
    WHERE itemid IN  ${IN_ITEMS}
    AND $__unixEpochFilter(clock)
    ORDER BY time) 
    AS temp 
    GROUP BY time
    ORDER by __value 
    DESC LIMIT ${DROPCOUNTS},1
    
    #流量表
    SELECT 
      unix_timestamp(time) as time_sec, 
      (SUM(value)/1000/1000/1000) as value,
      'In端口流量汇总(优化数据)' as metric  
    FROM 
    (SELECT  date_format(from_unixtime(clock), '%Y-%m-%d %H:%i') as time , value 
    FROM   ${TABLE_O}
    WHERE itemid IN ${IN_ITEMS}
    AND   $__unixEpochFilter(clock)
    ORDER BY  time) 
    AS temp 
    GROUP BY time;

订单统计分析面板

数据源(mysql数据库)

  • order_record表

    MySQL [yaoguai]> select order_status,price,create_time,format_id,order_type,user_id  from order_record order by id desc limit 10;
    +--------------+-------+---------------------+-----------+------------+---------+
    | order_status | price | create_time         | format_id | order_type | user_id |
    +--------------+-------+---------------------+-----------+------------+---------+
    |            5 |   290 | 2022-03-15 19:21:44 |        42 |          0 | 1087835 |
    |           10 |   290 | 2022-03-15 19:21:39 |        42 |          0 | 1087836 |
    |            5 |   290 | 2022-03-15 19:21:03 |        42 |          0 | 1087833 |
    |            5 |   290 | 2022-03-15 19:20:54 |         4 |          0 | 1087834 |
    |            5 |   290 | 2022-03-15 19:19:57 |        42 |          0 | 1087824 |
    |            5 |   290 | 2022-03-15 19:19:14 |        42 |          0 | 1087830 |
    |            5 |   290 | 2022-03-15 19:18:52 |        42 |          0 | 1087831 |
    |           10 |   290 | 2022-03-15 19:16:11 |        42 |          0 | 1087810 |
    |            5 |   290 | 2022-03-15 19:15:50 |        42 |          0 | 1087829 |
    |            5 |   290 | 2022-03-15 19:14:55 |        42 |          0 | 1087828 |
  • rows in set (0.00 sec)

  • photo_format

    MySQL [yaoguai]> select format_id,format_name from photo_format limit 10;
    +-----------+----------------------+
    | format_id | format_name          |
    +-----------+----------------------+
    |         1 | 一寸                 |
    |         2 | 小一寸               |
    |         3 | 大一寸               |
    |         4 | 二寸                 |
    |         5 | 小二寸               |
    |         6 | 大二寸               |
    |         7 | 美国签证             |
    |         8 | 日本签证             |
    |         9 | 越南签证             |
    |        10 | 身份证(无回执)       |
  • rows in set (0.00 sec)

  • user表

    MySQL [yaoguai]> select id,mini_kind from user order by id desc limit 10;
    +---------+-----------+
    | id      | mini_kind |
    +---------+-----------+
    | 1087840 |         1 |
    | 1087839 |         1 |
    | 1087838 |         1 |
    | 1087837 |         1 |
    | 1087836 |         1 |
    | 1087835 |         1 |
    | 1087834 |         1 |
    | 1087833 |        10 |
    | 1087832 |         1 |
    | 1087831 |         1 |
  • rows in set (0.00 sec)

Stat 面板

#金额
SELECT 
  now() as time_sec,
  sum(case a.order_status when '10' then a.num else 0 end) as '成功订单金额(单位:元)',
    sum(case a.order_status when '5' then a.num else 0 end) as '失败订单金额(单位:元)'
from (
    select order_status ,sum(price)/100 as num
    from order_record 
    where $__timeFilter(create_time)
    GROUP BY order_status) a

#个数
SELECT 
  now() as time_sec,
  sum(case a.order_status when '10' then a.num else 0 end) as '成功订单数(单位:个)',
    sum(case a.order_status when '5' then a.num else 0 end) as '失败订单数(单位:个)'
from (
    select order_status , count(order_status) as num
    from order_record 
    where $__timeFilter(create_time)
    GROUP BY order_status) a

Bar gauge 面板

select 
    c.format_name as format_name,
    count(c.format_name) as num 
from order_record b 
    join photo_format c 
    on b.format_id = c.format_id 
where 
  $__timeFilter(b.create_time)
and 
  b.order_status = 10  
GROUP BY c.format_name
order by count(c.format_name) desc
;

Pie chart 面板

SELECT 
  now() as time_sec,
    sum(case a.order_type when '0' then a.num else 0 end) as '未换装电子版',
    sum(case a.order_type when '5' then a.num else 0 end) as '未换装打印版',
    sum(case a.order_type when '10' then a.num else 0 end) as '已换装电子版',
    sum(case a.order_type when '15' then a.num else 0 end) as '已换装打印版'
from (
    select order_type ,sum(price)/100 as num
    from order_record 
    where $__timeFilter(create_time)
    and order_status = 10 
    GROUP BY order_type) a

Time series 面板

SELECT 
    unix_timestamp(time) as time_sec, 
    sum(value) as value,
    '所有订单' as metric  
FROM (
    select date_format(b.create_time, '%Y-%m-%d %H') as time , 1 as value
    from order_record b
    where $__timeFilter(b.create_time)
    and b.order_status = 10 
    GROUP BY b.create_time)
    AS temp 
GROUP BY time;

Table 面板

SELECT 
    (
        CASE 
            WHEN `mini_kind`=0 THEN 'web-官网'
            WHEN `mini_kind`=1 THEN '微信-APP'
            WHEN `mini_kind`=10 THEN '支付宝-APP'
            WHEN `mini_kind`=12 THEN '支付宝-APP2'
            ELSE NULL
        END
      ) '用户类型',
    user_id as '用户ID', 
    count(user_id) as '订单数',
    sum(price)/100 as '总金额(单位:元)'
FROM 
(SELECT 
    c.mini_kind as mini_kind, 
    b.user_id as user_id,  
    b.price as price
  FROM  order_record b 
  join user c 
  on b.user_id = c.id
  WHERE 
    $__timeFilter(b.create_time)
  AND
    b.order_status = 10
  ORDER BY  b.user_id) 
AS a 
GROUP BY user_id
order by sum(price) desc 
limit 20 
;

评论 (10)

取消
只有登录/注册用户才可评论
  1. 头像
    geek
    中国江苏省 · iPhone · Safari
    沙发

    请问mysql里面的数据更新后grafana视图会更新吗

    回复
    1. 头像
      行云流水 作者
      中国江苏省 · MacOS · Google Chrome
      @ geek

      会,mysql数据源和其他数据源没什么区别

      回复
      1. 头像
        geek
        中国江苏省 · Windows 10 · Google Chrome
        @ 行云流水

        我测试了一下,好像最快刷新频率是5s一次,输入更低的刷新时间系统默认不能使用,是这样吗

        回复
        1. 头像
          行云流水 作者
          中国江苏省 · MacOS · Google Chrome
          @ geek

          5s还不够快吗,你要干啥

          回复
          1. 头像
            geek
            中国江苏省 · Windows 10 · Google Chrome
            @ 行云流水

            想拿他监控股市变化表情

            回复
            1. 头像
              行云流水 作者
              · MacOS · Google Chrome
              @ geek

              在Dashboard settings 里面可以自定义 刷新频率

              回复
  2. 头像
    · MacOS · Google Chrome
    板凳

    添加邮件通知了

    回复
  3. 头像
    卡卡
    · MacOS · Google Chrome
    地毯

    继续测试

    回复
    1. 头像
      行云流水 作者
      · MacOS · Google Chrome
      @ 卡卡

      谢谢你的评论

      回复
  4. 头像
    卡卡
    · MacOS · Google Chrome
    第4楼

    我就是测试一下

    回复