方法一：用date_format 函数
select count(*) from test where date_format(create_time1,'%Y%m%d')='20220101';
+----------+
| count(*) |
+----------+
| 98256 |
+----------+
1 row in set (17.969 sec)
加索引后
select count(*) from test where date_format(create_time1,'%Y%m%d')='20220101';
+----------+
| count(*) |
+----------+
| 98256 |
+----------+
1 row in set (5.718 sec)
方法二：用 > < 查询
select count(*) from test where create_time1 >= '20220101' and create_time1 < '20220102';
+----------+
| count(*) |
+----------+
| 98256 |
+----------+
1 row in set (16.445 sec)
加索引后
select count(*) from test where create_time1 >= '20220101' and create_time1 < '20220102';
+----------+
| count(*) |
+----------+
| 98256 |
+----------+
1 row in set (0.049 sec)
方法三：用 SUBSTING函数
select count(*) from test where SUBSTRING(create_time1,1,10)='2022-01-01';
+----------+
| count(*) |
+----------+
| 98256 |
+----------+
1 row in set (16.880 sec)
加索引后
select count(*) from test where SUBSTRING(create_time1,1,10)='2022-01-01';
+----------+
| count(*) |
+----------+
| 98256 |
+----------+
1 row in set (4.234 sec)
方法四： between and
select count(*) from test where create_time1 between '2022-01-01' and '2022-01-02';
+----------+
| count(*) |
+----------+
| 100282 |
+----------+
1 row in set (15.842 sec)
加索引后
select count(*) from test where create_time1 between '2022-01-01' and '2022-01-02';
+----------+
| count(*) |
+----------+
| 100282 |
+----------+
1 row in set (0.045 sec)
方法五：时间用int保存形式
select count(*) from test where create_time between 1640966400 and 1641052800;
+----------+
| count(*) |
+----------+
| 100282 |
+----------+
1 row in set (15.755 sec)
加索引后
select count(*) from test where create_time between 1640966400 and 1641052800;
+----------+
| count(*) |
+----------+
| 100282 |
+----------+
1 row in set (0.037 sec)
PS.
1、使用between 条数多的原因是，1.2日00:00:00 数据也包含在内
2、create_time 为int类型 时间戳，create_time1 为 datetime类型
3、测试数据总条数为1300w
