#################################

root@10.10.10.10 (mac) > show create table apple\G
*************************** 1. row ***************************
       Table: apple
Create Table: CREATE TABLE `apple` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cluster_name` varchar(64) NOT NULL,
  `queue_name` varchar(128) NOT NULL,
  `time` datetime NOT NULL,
  `apps` int(11) NOT NULL,
  `numapps` int(11) NOT NULL,
  `maxApps` int(11) NOT NULL,
  `used_resource` int(11) NOT NULL,
  `expected_share` int(11) NOT NULL,
  `max_resource` int(11) NOT NULL,
  `used_resource_memory` int(11) NOT NULL,
  `expected_memory` int(11) NOT NULL,
  `max_memory` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `time` (`time`)
) ENGINE=InnoDB AUTO_INCREMENT=218408440 DEFAULT CHARSET=utf8
1 row in set (0.14 sec)

Wed Jun 23 14:30:00 2021
root@10.10.10.10 (mac) > select count(*) from apple;
+-----------+
| count(*)  |
+-----------+
| 157898648 |
+-----------+
1 row in set (41.96 sec)

Wed Jun 23 14:30:53 2021
root@10.10.10.10 (mac) > desc SELECT queue_name, AVG(used_resource) 

FROM apple

WHERE cluster_name='azmbcommonprc-hadoop'

AND DATE(time) >='2021-06-22 00:00:00'

AND DATE(time) <= '2021-06-22 23:59:59'

GROUP BY queue_name; +----+-------------+---------+------------+------+---------------+------+---------+------+-----------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+-----------+----------+----------------------------------------------+ | 1 | SIMPLE | apple | NULL | ALL | NULL | NULL | NULL | NULL | 156586044 | 10.00 | Using where; Using temporary; Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+-----------+----------+----------------------------------------------+ 1 row in set, 1 warning (0.14 sec) Wed Jun 23 14:31:58 2021


###################################################################

#####################:下面将time字段上的date函数去掉,则sql优化效果明显:

root@10.10.10.10 (mac) > desc SELECT queue_name, AVG(used_resource)

FROM apple

WHERE cluster_name='azmbcommonprc-hadoop'

AND time >= '2021-06-22 00:00:00'

AND time <= '2021-06-22 23:59:59'

GROUP BY queue_name; +----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+---------------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+---------------------------------------------------------------------+ | 1 | SIMPLE | apple | NULL | range | time | time | 5 | NULL | 231088 | 10.00 | Using index condition; Using where; Using temporary; Using filesort | +----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+---------------------------------------------------------------------+ 1 row in set, 1 warning (0.14 sec) Wed Jun 23 14:32:05 2021 root@10.10.10.10 (mac) > select * from apple limit 1\G; *************************** 1. row *************************** id: 1 cluster_name: azmbcommonprc-hadoop queue_name: root time: 2018-03-15 19:39:57 apps: 0 numapps: 0 maxApps: 700 used_resource: 0 expected_share: 15 max_resource: 324 used_resource_memory: 0 expected_memory: 30720 max_memory: 552960 1 row in set (0.14 sec) ERROR: No query specified Wed Jun 23 14:32:25 2021 root@10.10.10.10 (mac) >

 

 

 

 

 

 

 

 

 

 

 ##########################

 

############################