首页新闻找找看学习计划

MySQL性能优化问题

0
[已解决问题] 解决于 2019-06-25 14:54

我做了一个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的查询性能会随着数据量的增加而急剧下降,但两者并没有太大的不同。因此,我无法优化我的查询。我不知道何时实现表分区计划或子数据库子表计划。

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

Haceral的主页 Haceral | 初学一级 | 园豆:5
提问于:2019-06-25 11:46
< >
分享
最佳答案
3

你的查询因为索引覆盖了,索引其实就是查询索引的时间,mysql的索引用的是B+树结构,在相同树高度下查询时间基本的一样的,你可以去计算一下你这几张表索引的树的高度是否一致。

奖励园豆:5
让我发会呆 | 老鸟四级 |园豆:2624 | 2019-06-25 13:44

非常感谢您提供的帮助,我试用了您的方案,得到的两个相同的值,但我还想请您帮我确认一下

Haceral | 园豆:5 (初学一级) | 2019-06-25 14:04

@Haceral: 我看网上的公式,大致算了一下,1亿的3.34 4亿是3.589,基本上差不多。

让我发会呆 | 园豆:2624 (老鸟四级) | 2019-06-25 14:43

@让我发会呆: 原来如此,原来时间一致是因为按照我这样的表数据,无论数据量多少它永远都是从相同的高度获取的数据,所以结果一致。非常感谢您,这个困扰我一个星期的问题终于得到了答案,同时我对索引有了进一步的了解,非常感谢!

Haceral | 园豆:5 (初学一级) | 2019-06-25 14:54

@Haceral: 为什么高度是相同的?

会长 | 园豆:5046 (大侠五级) | 2019-06-25 14:58

@会长: 我认为高度相同是因为我的数据全部来源与t_worker_attendance_300w 这张表,后面的1亿和4亿全部是基于300w重复制造出来的,因为它们的索引是重复的

Haceral | 园豆:5 (初学一级) | 2019-06-25 15:17

@会长: 纠正一下,是因此它们的索引是重复的,您觉得对呢?

Haceral | 园豆:5 (初学一级) | 2019-06-25 15:19

@Haceral: 我没有仔细看过B树,应该节点多了高度也变大吧,还需研究

会长 | 园豆:5046 (大侠五级) | 2019-06-25 15:31

@会长: 不管如何还是要谢谢二位,至于为什么高度相同这需要深入到底层才能解决这个问题了吧。

Haceral | 园豆:5 (初学一级) | 2019-06-25 15:57

@Haceral: 不用深入,就研究树结构就好了,如果你有精力,估计一两天能把常见的树结构看完

会长 | 园豆:5046 (大侠五级) | 2019-06-25 16:28
其他回答(2)
0

肯定不会相同的,只是可能差别不大。加了索引查询速度的减慢肯定不是按照记录数增加的幅度变化的,因为索引是基于平衡树的,二叉树的搜索时间复杂度是$log_2^n$吧,Mysql好像是基于B树,复杂度应该和二叉树在一个级别上。另外,你查的时候有没有考虑mysql缓存,当你查询同一个语句第二遍的是时候速度会比第一遍快很多,mysql自己会维护一个缓存的,执行语句前最好清一下缓存。

会长 | 园豆:5046 (大侠五级) | 2019-06-25 11:58

非常感谢您的帮助,但我试过了清理缓存、 SELECT SQL_NO_CACHE 得到的结果仍然是一致的,为此我感到很疑惑。对了,我的数据是重复复制的,难道是这个的原因吗?

支持(0) 反对(0) Haceral | 园豆:5 (初学一级) | 2019-06-25 13:30

@Haceral: 对对对,楼下说的索引覆盖也有关系,因为你查询的是创建索引的字段,索引里本身就存了这个字段的值,不在需要根据索引里记录的地址去找其他字段。

支持(0) 反对(0) 会长 | 园豆:5046 (大侠五级) | 2019-06-25 14:55

你说的高度一样可能是对的,我刚才查了下B树结构,说所有的叶子节点在同一层。楼主有空研究B树吧,我也不太懂

支持(0) 反对(0) 会长 | 园豆:5046 (大侠五级) | 2019-06-25 15:30
0

我觉得limit 0,10000; 这样查询应该没什么意义吧。多厚的书,都是前几页的话,翻的速度应该都是一样的吧。

hfl~ | 园豆:223 (菜鸟二级) | 2019-06-25 16:20

如果我不加的limit的话那返回的结果集就是不一样的了,结果集不一样的话时间一定是不会一样的,我数据是重复制造的,所以如果不加limit 查1000w 返回1000条数据,查2000w返回的就是2000条数据了

支持(0) 反对(0) Haceral | 园豆:5 (初学一级) | 2019-06-25 17:30

非常感谢您查阅我的问题并认真思考

支持(0) 反对(0) Haceral | 园豆:5 (初学一级) | 2019-06-25 17:35
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册