我做了一个MySQL性能优化测试，但是测试结果让我感到惊讶。

首先，我为我的测试准备了几个表，它们是“t_Worker_考勤_300 w(300万数据)、t_Worker_考勤_1000 w(1000万数据)、t_Worker_考勤_1Y(1亿数据)、t_Worker_考勤_4y(4亿数据)”。

每个表都有相同的字段，相同的索引，它们是被复制粘贴的，包括4亿的数据量也从300万个数据增加。

据我理解，MySQL的性能肯定会受到数据量大小的严重影响，但结果已经困扰了我整整一周。我几乎已经测试了我能想到的场景，但是它们的执行时间是一样的！

这是一个新的MySQL 5.6.16服务器，我测试了我能想到的任何场景，包括内部连接.

为了节约每个人的时间，这里我将表信息全部举列在这里：

A) SHOW CREATE TABLE t_worker_attendance_4y
 
CREATE TABLE `t_worker_attendance_4y` (
`id` bigint(20) NOT NULL ,
`attendance_id` char(32) NOT NULL,
`worker_id` char(32) NOT NULL,
`subcontractor_id` char(32) NOT NULL ,
`project_id` char(32) NOT NULL ,
`sign_date` date NOT NULL ,
`sign_type` char(2) NOT NULL ,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL ,
`sign_wages` decimal(16,2) DEFAULT NULL ,
`confirm_wages` decimal(16,2) DEFAULT NULL ,
`work_content` varchar(60) DEFAULT NULL ,
`team_leader_id` char(32) DEFAULT NULL,
`sign_state` char(2) NOT NULL ,
`confirm_date` date DEFAULT NULL ,
`sign_mode` char(2) DEFAULT NULL ,
`checkin_time` datetime DEFAULT NULL ,
`checkout_time` datetime DEFAULT NULL , 
`sign_hours` decimal(6,1) DEFAULT NULL ,
`overtime` decimal(6,1) DEFAULT NULL ,
`confirm_hours` decimal(6,1) DEFAULT NULL ,
`signimg` varchar(200) DEFAULT NULL ,
`signoutimg` varchar(200) DEFAULT NULL ,
`photocheck` char(2) DEFAULT NULL ,
`machine_type` varchar(2) DEFAULT '1' ,
`project_coordinate` text ,
`floor_num` varchar(200) DEFAULT NULL ,
`device_serial_no` varchar(32) DEFAULT NULL ,
KEY `checkin_time` (`checkin_time`),
KEY `worker_id` (`worker_id`),
KEY `project_id` (`project_id`),
KEY `subcontractor_id` (`subcontractor_id`),
KEY `sign_date` (`sign_date`),
KEY `project_id_2` (`project_id`,`sign_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

B) SHOW INDEX FROM t_worker_attendance_4y

+------------------------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                  | Non_unique | Key_name         | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_worker_attendance_4y |          1 | checkin_time     |            1 | checkin_time     | A         |     5017494 |     NULL | NULL   | YES  | BTREE      |         |               |
| t_worker_attendance_4y |          1 | worker_id        |            1 | worker_id        | A         |     1686552 |     NULL | NULL   |      | BTREE      |         |               |
| t_worker_attendance_4y |          1 | project_id       |            1 | project_id       | A         |      102450 |     NULL | NULL   |      | BTREE      |         |               |
| t_worker_attendance_4y |          1 | subcontractor_id |            1 | subcontractor_id | A         |      380473 |     NULL | NULL   |      | BTREE      |         |               |
| t_worker_attendance_4y |          1 | sign_date        |            1 | sign_date        | A         |      512643 |     NULL | NULL   |      | BTREE      |         |               |
| t_worker_attendance_4y |          1 | project_id_2     |            1 | project_id       | A         |      102059 |     NULL | NULL   |      | BTREE      |         |               |
| t_worker_attendance_4y |          1 | project_id_2     |            2 | sign_date        | A         |     1776104 |     NULL | NULL   |      | BTREE      |         |               |
+------------------------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

C) EXPLAIN SELECT SQL_NO_CACHE tw.project_id, tw.sign_date FROM t_worker_attendance_4y tw WHERE tw.project_id = '39235664ba734887b298ee568fbb66fb' AND sign_date >= '07/01/2018' AND sign_date < '08/01/2018' ;
+----+-------------+-------+------+-----------------------------------+--------------+---------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys                     | key          | key_len | ref   | rows     | Extra                    |
+----+-------------+-------+------+-----------------------------------+--------------+---------+-------+----------+--------------------------+
|  1 | SIMPLE      | tw    | ref  | project_id,sign_date,project_id_2 | project_id_2 | 96      | const | 54134596 | Using where; Using index |
+----+-------------+-------+------+-----------------------------------+--------------+---------+-------+----------+--------------------------+

它们都通过了相同的联合索引

SELECT tw.project_id, tw.sign_date FROM t_worker_attendance_300w tw 
WHERE tw.project_id = '39235664ba734887b298ee568fbb66fb' 
AND sgin_date >= '07/01/2018' 
AND sgin_date < '08/01/2018' LIMIT 0,10000;

Execution time: 0.02 sec

SELECT tw.project_id, tw.sign_date FROM t_worker_attendance_1000w tw 
WHERE tw.project_id = '39235664ba734887b298ee568fbb66fb' 
AND sgin_date >= '07/01/2018' 
AND sgin_date < '08/01/2018' LIMIT 0,10000;

Execution time: 0.01 sec

SELECT tw.project_id, tw.sign_date FROM t_worker_attendance_1y tw 
WHERE tw.project_id = '39235664ba734887b298ee568fbb66fb' 
AND sgin_date >= '07/01/2018' 
AND sgin_date < '08/01/2018' LIMIT 0,10000;

Execution time: 0.02 sec

SELECT tw.project_id, tw.sign_date FROM t_worker_attendance_4y tw 
WHERE tw.project_id = '39235664ba734887b298ee568fbb66fb' 
AND sgin_date >= '07/01/2018' 
AND sgin_date < '08/01/2018' LIMIT 0,10000;

Execution time: 0.02 sec

......

我猜想MySQL的查询性能会随着数据量的增加而急剧下降，但两者并没有太大的不同。因此，我无法优化我的查询。我不知道何时实现表分区计划或子数据库子表计划。

我想知道的是，为什么小数据量索引的执行速度与大数据量索引的执行速度相同。如果你能帮我，我表示非常感谢。