Mysql百万数据查询之时间查询

Mysql百万数据查询之时间查询

在校期间，一直没有接触过百万级的数据，一直没有百万数据下查询的感觉。
近期做毕业设计，需要用到较大的数据来进行图表分析和订单推荐。
一直疑惑如何设计表才能让普通的sql更高效，因此便以下尝试并记录于此。
本篇文章着重于表的设计对查询sql的影响
小弟不才，文章仅做记录，有更好的想法或有误请留言指出，必当积极回应。

优化设想

1. 时间以int时间戳的形式保存，并建立相关索引
2. 时间拆分，以year、month、day进行保存，并以year+month和year+month+day做索引(空间换时间)
3. 以时间戳保存，并按月进行分区

测试环境

- 表引擎:innoDB
- 字符集:utf8mb4
- 数据库版本：5.7.18
数据库引擎为默认的innoDB、因为实际业务考虑事务，因此就默认innoDB
为了不受其他环节影响，用的是`腾讯云mysql基础版1核1000MB/50GB`

建表

创建相关Order表

-- 创建order表
CREATE TABLE tb_order (
    `id` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT,
    `item_name` VARCHAR ( 255 ) NOT NULL,
    `item_price` INT ( 11 ) UNSIGNED NOT NULL,
    `item_state` TINYINT ( 1 ) NOT NULL,
    `create_time` INT ( 11 ) UNSIGNED NOT NULL,
    `time_year` CHAR ( 4 ) NOT NULL,
    `time_month` CHAR ( 2 ) NOT NULL,
    `time_day` CHAR ( 2 ) NOT NULL,
    PRIMARY KEY ( id ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
CREATE INDEX idx_order_ctime ON tb_order ( create_time );
CREATE INDEX idx_order_state ON tb_order ( `item_state` );
CREATE INDEX idx_order_day ON tb_order ( `time_year`, `time_month` );
CREATE INDEX idx_order_dmonth ON tb_order ( `time_year`, `time_month`, `time_day`);

创建Mock存储过程

-- 随机字符串函数
CREATE DEFINER=`root`@`%` FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8
BEGIN 
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; 
    DECLARE return_str varchar(255) DEFAULT '' ;
    DECLARE i INT DEFAULT 0; 
WHILE i < n DO 
    SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1)); 
    SET i = i +1; 
END WHILE; 
    RETURN return_str; 
END

-- 随机范围时间函数
CREATE DEFINER=`root`@`%` FUNCTION `rand_date`(`startDate` date,`endDate` date) RETURNS datetime
BEGIN
    #Routine body goes here...
    DECLARE sec INT DEFAULT 0;
    DECLARE ret DATETIME;
    SET sec = ABS(UNIX_TIMESTAMP(endDate) - UNIX_TIMESTAMP(startDate));
    SET ret = DATE_ADD(startDate, INTERVAL FLOOR( 1+RAND ( ) * ( sec-1))SECOND);
    RETURN ret;
END

-- 模拟订单存储过程
CREATE DEFINER=`root`@`%` PROCEDURE `mock_order`(IN `size` int UNSIGNED,IN `sd` date,IN `ed` date)
BEGIN
    #Routine body goes here...
    DECLARE i int UNSIGNED DEFAULT 1;
    DECLARE randOrderName VARCHAR(10);
    DECLARE randOrderTime DATETIME;
    WHILE i<= size DO
        SELECT rand_string(10) INTO randOrderName;
        SELECT rand_date(sd,ed) INTO randOrderTime;
        INSERT INTO tb_order(`item_name`,`item_price`,`item_state`,`create_time`,`time_year`,`time_month`,`time_day`)
        VALUES(randOrderName,RAND()*100,ROUND(RAND()),UNIX_TIMESTAMP(randOrderTime),DATE_FORMAT(randOrderTime,'%Y'