[mysqld]
default_time_zone = +11:00

@Resource
    private JdbcTemplate jdbcTemplate;
    @Test
    /**
     * 10個線程每次查詢10次  一次查500條
     * timestamp:11,978ms
     * datetime:9,057ms
     */
    void getTimestamp() throws BrokenBarrierException, InterruptedException {
        String timestampSql = "select SQL_NO_CACHE test_timestamp from datetime_test  where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00' order by test_timestamp  limit 500;";
        String dateTimeSql = "select SQL_NO_CACHE test_datetime from datetime_test  where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00' order by test_datetime  limit 500;";
        CountDownLatch countDownLatch = new CountDownLatch(10);
        long start = System.currentTimeMillis();
        forQuery(timestampSql, countDownLatch);
        countDownLatch.await();
        //timestamp:11,978ms
        System.out.println(MessageFormat.format("timestamp:{0}ms", System.currentTimeMillis() - start));
        CountDownLatch countDownLatch2 = new CountDownLatch(10);
        start = System.currentTimeMillis();
        forQuery(dateTimeSql, countDownLatch2);
        countDownLatch2.await();
        //datetime:9,057ms
        System.out.println(MessageFormat.format("datetime:{0}ms", System.currentTimeMillis() - start));
    }
    private void forQuery(String timestampSql, CountDownLatch countDownLatch) {
        for (int j = 1; j <= 10; j++) {
            new Thread(() -> {
                for (int i = 0; i < 10; i++) {
                    jdbcTemplate.queryForList(timestampSql);
                }
                countDownLatch.countDown();
            }).start();
        }
    }

#開啟函數(shù)創(chuàng)建
set global log_bin_trust_function_creators=1;
#ON表示已開啟
show variables like 'log_bin_trust%';

CREATE TABLE `datetime_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `money` int(11) DEFAULT NULL,
  `test_datetime` datetime DEFAULT NULL,
  `test_timestamp` timestamp NULL DEFAULT NULL,
  `test_bigint` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

#分割符從;改為$$
delimiter $$
#函數(shù)名ran_string 需要一個參數(shù)int類型 返回類型varchar(255)
create function ran_string(n int) returns varchar(255)
begin
#聲明變量chars_str默認(rèn)'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
#聲明變量return_str默認(rèn)''
declare return_str varchar(255) default '';
#聲明變量i默認(rèn)0
declare i int default 0;
#循環(huán)條件 i<n
while i < n do
set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i=i+1;
end while;
return return_str;
end $$

#生成隨機(jī)num的函數(shù)
delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i=floor(100+rand()*10);
return i;
end $$

#插入 從參數(shù)start開始 插入max_num條數(shù)據(jù)（未使用startc）
delimiter $$ 
create procedure insert_datetime_test(in start int(10),in max_num int(10))
begin
declare i int default 0;
declare random datetime default '2022-01-01 00:00:00';
set autocommit = 0;
repeat
set i = i+1;
set random = DATE_ADD('2022-01-01 00:00:00', INTERVAL FLOOR(RAND() * 31536000) SECOND);
#SQL 語句
insert into datetime_test(username,money,test_bigint,test_datetime,test_timestamp) 
values (ran_string(8),rand_num(),UNIX_TIMESTAMP(random),random,random);
until i=max_num
end repeat;
commit;
end $$

#執(zhí)行插入函數(shù)
delimiter ;
call insert_datetime_test(1,10000000);

alter table datetime_test add index idx_datetime(test_datetime);
alter table datetime_test add index idx_timestamp(test_timestamp);
alter table datetime_test add index idx_bigint(test_bigint);

select SQL_NO_CACHE * from datetime_test 
where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00'
order by test_datetime 
limit 20
> OK
> 時間: 0.038s
select SQL_NO_CACHE * from datetime_test 
where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00'
order by test_timestamp 
limit 20
> OK
> 時間: 0.034s
select SQL_NO_CACHE * from datetime_test 
where test_bigint >= 1665331200 and test_bigint <= 1665417600
order by test_bigint 
limit 20
> OK
> 時間: 0.036s

select SQL_NO_CACHE * from datetime_test 
where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00'
> OK
> 時間: 37.084s
select SQL_NO_CACHE * from datetime_test 
where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00'
> OK
> 時間: 39.558s
select SQL_NO_CACHE * from datetime_test 
where test_bigint >= 1665331200 and test_bigint <= 1665417600
> OK
> 時間: 38.966s

select SQL_NO_CACHE test_datetime from datetime_test 
where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00'
> OK
> 時間: 8.478s
select SQL_NO_CACHE test_timestamp from datetime_test 
where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00'
> OK
> 時間: 9.063s
select SQL_NO_CACHE test_bigint from datetime_test 
where test_bigint >= 1665331200 and test_bigint <= 1665417600
> OK
> 時間: 5.773s

select SQL_NO_CACHE count(*) from datetime_test 
where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00'
> OK
> 時間: 0.053s
select SQL_NO_CACHE count(*) from datetime_test 
where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00'
> OK
> 時間: 0.078s
select SQL_NO_CACHE count(*) from datetime_test 
where test_bigint >= 1665331200 and test_bigint <= 1665417600
> OK
> 時間: 0.049s

select SQL_NO_CACHE count(*) from datetime_test
> OK
> 時間: 3.898s
select SQL_NO_CACHE count(*) from datetime_test
> OK
> 時間: 4.152s
select SQL_NO_CACHE count(*) from datetime_test
> OK
> 時間: 3.17s