今天开发提出需求,让统计数据,一询问才得知表中的数据量已达亿级以上。具体的sql如下:
SELECT id_province_code,gender,age,COUNT(1),SUM(zy_days),SUM(zf),SUM(ybnje)FROM medicare2017 WHERE zy_enter_date BETWEEN '2017-01-01 00:00:00' AND '2017-12-31 12:59:59' GROUP BY id_province_code,age,gender;
然后查看该sql的执行计划
mysql> explain SELECT id_province_code,gender,age,COUNT(1),SUM(zy_days),SUM(zf),SUM(ybnje) FROM medicare2017 WHERE zy_enter_date BETWEEN '2017-01-01 00:00:00' AND '2017-12-31 12:59:59' GROUP BYid_province_code,age,gender;+----+-------------+--------------+------------+-------+-------------------------------------------+-------------------+---------+------+---------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |