MySQL

集群

联合索引

分库分表

统计时间

获取最近七天时间

SELECT CURDATE() UNION ALL
SELECT DATE_SUB( CURDATE(), INTERVAL 1 DAY ) UNION ALL
SELECT DATE_SUB( CURDATE(), INTERVAL 2 DAY ) UNION ALL
SELECT DATE_SUB( CURDATE(), INTERVAL 3 DAY ) UNION ALL
SELECT DATE_SUB( CURDATE(), INTERVAL 4 DAY ) UNION ALL
SELECT DATE_SUB( CURDATE(), INTERVAL 5 DAY ) UNION ALL
SELECT DATE_SUB( CURDATE(), INTERVAL 6 DAY )

获取最近七天周

SELECT DATE_FORMAT( CURDATE(), '%Y-%u' ) AS weeks UNION ALL
SELECT DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 1 WEEK ), '%Y-%u' ) AS weeks UNION ALL
SELECT DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 2 WEEK ), '%Y-%u' ) AS weeks UNION ALL
SELECT DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 3 WEEK ), '%Y-%u' ) AS weeks UNION ALL
SELECT DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 4 WEEK ), '%Y-%u' ) AS weeks UNION ALL
SELECT DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 5 WEEK ), '%Y-%u' ) AS weeks UNION ALL
SELECT DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 6 WEEK ), '%Y-%u' ) AS weeks

获取最近七月的时间

SELECT DATE_FORMAT( CURDATE(), '%Y-%m' ) AS `year_month` UNION
SELECT DATE_FORMAT(( CURDATE() - INTERVAL 1 MONTH ), '%Y-%m' ) AS `year_month` UNION
SELECT DATE_FORMAT(( CURDATE() - INTERVAL 2 MONTH ), '%Y-%m' ) AS `year_month` UNION
SELECT DATE_FORMAT(( CURDATE() - INTERVAL 3 MONTH ), '%Y-%m' ) AS `year_month` UNION
SELECT DATE_FORMAT(( CURDATE() - INTERVAL 4 MONTH ), '%Y-%m' ) AS `year_month` UNION
SELECT DATE_FORMAT(( CURDATE() - INTERVAL 5 MONTH ), '%Y-%m' ) AS `year_month` UNION
SELECT DATE_FORMAT(( CURDATE() - INTERVAL 6 MONTH ), '%Y-%m' ) AS `year_month`

获取每天的数据量

获取到需要点时间日期后，可和数据表进行左连接，从而查询每个时间段的数据量
例如：查询new表最近7天，每天的数据总数

    SELECT 
    a.tscreate,
    IFNULL( b.count, 0 ) as count
    FROM
            (
            SELECT CURDATE() AS tscreate UNION ALL
            SELECT DATE_SUB( CURDATE(), INTERVAL 1 DAY ) AS tscreate UNION ALL
            SELECT DATE_SUB( CURDATE(), INTERVAL 2 DAY ) AS tscreate UNION ALL
            SELECT DATE_SUB( CURDATE(), INTERVAL 3 DAY ) AS tscreate UNION ALL
            SELECT DATE_SUB( CURDATE(), INTERVAL 4 DAY ) AS tscreate UNION ALL
            SELECT DATE_SUB( CURDATE(), INTERVAL 5 DAY ) AS tscreate UNION ALL
            SELECT DATE_SUB( CURDATE(), INTERVAL 6 DAY ) AS tscreate 
            ) a
        LEFT JOIN (
            SELECT 
                count(*) AS count,
                DATE( tscreate ) AS tscreate 
            FROM
                new 
            GROUP BY
            DAY ( tscreate )) b ON a.tscreate = b.tscreate 
    ORDER BY a.tscreate ASC