千万条数据统计分析所有年龄的具体人数

突然在群里看到有人在群里发了一个mysql的题目，来了兴致，研究一下

转自 Not Talk 中科软面试题-千万级数据测试

数据什么的可以去上面链接里去拿。
ps: 用navicat导入的话用了10小时，最好搞个脚本，多线程，批量提交啥的，1kw数据半个小时搞定

如题：

数据大致这个样子，1千1百万条，然后有索引，没有分区（mysql myisam引擎好像不让创建分区了。。。）




使用的mysql8.0.26

使用比较容易想到的方法（转载博主的方式）

查出来是7s多，可能我这个硬盘不好吧，离博主的4.9s还差一点。。。

然后我又试了：

1. 分页：分页速度是上面sql的两倍

2. 转换sql：->使用临时表。速度大约慢3-5s

3. 然后搞了个大工程，分了10个表，一个表分了100w数据，速度仍然达不到5s内
   

4. 然后使用innodb引擎，速度直接就是myisam的两倍了。。。。

5. 最后使用脚本生成了一个基础的sql，使用 > <判断的。

select count(*) 人数, 101 as 年龄 from employee where birth <= '1920-09-02'

union all

select count(*) c, 100 y from employee where birth > '1920-09-02' and birth <= '1921-09-02'

union all

select count(*) c, 99 y from employee where birth > '1921-09-02' and birth <= '1922-09-02'

union all

select count(*) c, 98 y from employee where birth > '1922-09-02' and birth <= '1923-09-02'

union all

select count(*) c, 97 y from employee where birth > '1923-09-02' and birth <= '1924-09-02'

union all

select count(*) c, 96 y from employee where birth > '1924-09-02' and birth <= '1925-09-02'

union all

select count(*) c, 95 y from employee where birth > '1925-09-02' and birth <= '1926-09-02'

union all

select count(*) c, 94 y from employee where birth > '1926-09-02' and birth <= '1927-09-02'

union all

select count(*) c, 93 y from employee where birth > '1927-09-02' and birth <= '1928-09-02'

union all

select count(*) c, 92 y from employee where birth > '1928-09-02' and birth <= '1929-09-02'

union all

select count(*) c, 91 y from employee where birth > '1929-09-02' and birth <= '1930-09-02'

union all

select count(*) c, 90 y from employee where birth > '1930-09-02' and birth <= '1931-09-02'

union all

select count(*) c, 89 y from employee where birth > '1931-09-02' and birth <= '1932-09-02'

union all

select count(*) c, 88 y from employee where birth > '1932-09-02' and birth <= '1933-09-02'

union all

select count(*) c, 87 y from employee where birth > '1933-09-02' and birth <= '1934-09-02'

union all

select count(*) c, 86 y from employee where birth > '1934-09-02' and birth <= '1935-09-02'

union all

select count(*) c, 85 y from employee where birth > '1935-09-02' and birth <= '1936-09-02'

union all

select count(*) c, 84 y from employee where birth > '1936-09-02' and birth <= '1937-09-02'

union all

select count(*) c, 83 y from employee where birth > '1937-09-02' and birth <= '1938-09-02'

union all

select count(*) c, 82 y from employee where birth > '1938-09-02' and birth <= '1939-09-02'

union all

select count(*) c, 81 y from employee where birth > '1939-09-02' and birth <= '1940-09-02'

union all

select count(*) c, 80 y from employee where birth > '1940-09-02' and birth <= '1941-09-02'

union all

select count(*) c, 79 y from employee where birth > '1941-09-02' and birth <= '1942-09-02'

union all

select count(*) c, 78 y from employee where birth > '1942-09-02' and birth <= '1943-09-02'

union all

select count(*) c, 77 y from employee where birth > '1943-09-02' and birth <= '1944-09-02'

union all

select count(*) c, 76 y from employee where birth > '1944-09-02' and birth <= '1945-09-02'

union all

select count(*) c, 75 y from employee where birth > '1945-09-02' and birth <= '1946-09-02'

union all

select count(*) c, 74 y from employee where birth > '1946-09-02' and birth <= '1947-09-02'

union all

select count(*) c, 73 y from employee where birth > '1947-09-02' and birth <= '1948-09-02'

union all

select count(*) c, 72 y from employee where birth > '1948-09-02' and birth <= '1949-09-02'

union all

select count(*) c, 71 y from employee where birth > '1949-09-02' and birth <= '1950-09-02'

union all

select count(*) c, 70 y from employee where birth > '1950-09-02' and birth <= '1951-09-02'

union all

select count(*) c, 69 y from employee where birth > '1951-09-02' and birth <= '1952-09-02'

union all

select count(*) c, 68 y from employee where birth > '1952-09-02' and birth <= '1953-09-02'

union all

select count(*) c, 67 y from employee where birth > '1953-09-02' and birth <= '1954-09-02'

union all

select count(*) c, 66 y from employee where birth > '1954-09-02' and birth <= '1955-09-02'

union all

select count(*) c, 65 y from employee where birth > '1955-09-02' and birth <= '1956-09-02'

union all

select count(*) c, 64 y from employee where birth > '1956-09-02' and birth <= '1957-09-02'

union all

select count(*) c, 63 y from employee where birth > '1957-09-02' and birth <= '1958-09-02'

union all

select count(*) c, 62 y from employee where birth > '1958-09-02' and birth <= '1959-09-02'

union all

select count(*) c, 61 y from employee where birth > '1959-09-02' and birth <= '1960-09-02'

union all

select count(*) c, 60 y from employee where birth > '1960-09-02' and birth <= '1961-09-02'

union all

select count(*) c, 59 y from employee where birth > '1961-09-02' and birth <= '1962-09-02'

union all

select count(*) c, 58 y from employee where birth > '1962-09-02' and birth <= '1963-09-02'

union all

select count(*) c, 57 y from employee where birth > '1963-09-02' and birth <= '1964-09-02'

union all

select count(*) c, 56 y from employee where birth > '1964-09-02' and birth <= '1965-09-02'

union all

select count(*) c, 55 y from employee where birth > '1965-09-02' and birth <= '1966-09-02'

union all

select count(*) c, 54 y from employee where birth > '1966-09-02' and birth <= '1967-09-02'

union all

select count(*) c, 53 y from employee where birth > '1967-09-02' and birth <= '1968-09-02'

union all

select count(*) c, 52 y from employee where birth > '1968-09-02' and birth <= '1969-09-02'

union all

select count(*) c, 51 y from employee where birth > '1969-09-02' and birth <= '1970-09-02'

union all

select count(*) c, 50 y from employee where birth > '1970-09-02' and birth <= '1971-09-02'

union all

select count(*) c, 49 y from employee where birth > '1971-09-02' and birth <= '1972-09-02'

union all

select count(*) c, 48 y from employee where birth > '1972-09-02' and birth <= '1973-09-02'

union all

select count(*) c, 47 y from employee where birth > '1973-09-02' and birth <= '1974-09-02'

union all

select count(*) c, 46 y from employee where birth > '1974-09-02' and birth <= '1975-09-02'

union all

select count(*) c, 45 y from employee where birth > '1975-09-02' and birth <= '1976-09-02'

union all

select count(*) c, 44 y from employee where birth > '1976-09-02' and birth <= '1977-09-02'

union all

select count(*) c, 43 y from employee where birth > '1977-09-02' and birth <= '1978-09-02'

union all

select count(*) c, 42 y from employee where birth > '1978-09-02' and birth <= '1979-09-02'

union all

select count(*) c, 41 y from employee where birth > '1979-09-02' and birth <= '1980-09-02'

union all

select count(*) c, 40 y from employee where birth > '1980-09-02' and birth <= '1981-09-02'

union all

select count(*) c, 39 y from employee where birth > '1981-09-02' and birth <= '1982-09-02'

union all

select count(*) c, 38 y from employee where birth > '1982-09-02' and birth <= '1983-09-02'

union all

select count(*) c, 37 y from employee where birth > '1983-09-02' and birth <= '1984-09-02'

union all

select count(*) c, 36 y from employee where birth > '1984-09-02' and birth <= '1985-09-02'

union all

select count(*) c, 35 y from employee where birth > '1985-09-02' and birth <= '1986-09-02'

union all

select count(*) c, 34 y from employee where birth > '1986-09-02' and birth <= '1987-09-02'

union all

select count(*) c, 33 y from employee where birth > '1987-09-02' and birth <= '1988-09-02'

union all

select count(*) c, 32 y from employee where birth > '1988-09-02' and birth <= '1989-09-02'

union all

select count(*) c, 31 y from employee where birth > '1989-09-02' and birth <= '1990-09-02'

union all

select count(*) c, 30 y from employee where birth > '1990-09-02' and birth <= '1991-09-02'

union all

select count(*) c, 29 y from employee where birth > '1991-09-02' and birth <= '1992-09-02'

union all

select count(*) c, 28 y from employee where birth > '1992-09-02' and birth <= '1993-09-02'

union all

select count(*) c, 27 y from employee where birth > '1993-09-02' and birth <= '1994-09-02'

union all

select count(*) c, 26 y from employee where birth > '1994-09-02' and birth <= '1995-09-02'

union all

select count(*) c, 25 y from employee where birth > '1995-09-02' and birth <= '1996-09-02'

union all

select count(*) c, 24 y from employee where birth > '1996-09-02' and birth <= '1997-09-02'

union all

select count(*) c, 23 y from employee where birth > '1997-09-02' and birth <= '1998-09-02'

union all

select count(*) c, 22 y from employee where birth > '1998-09-02' and birth <= '1999-09-02'

union all

select count(*) c, 21 y from employee where birth > '1999-09-02' and birth <= '2000-09-02'

union all

select count(*) c, 20 y from employee where birth > '2000-09-02' and birth <= '2001-09-02'

union all

select count(*) c, 19 y from employee where birth > '2001-09-02' and birth <= '2002-09-02'

union all

select count(*) c, 18 y from employee where birth > '2002-09-02' and birth <= '2003-09-02'

union all

select count(*) c, 17 y from employee where birth > '2003-09-02' and birth <= '2004-09-02'

union all

select count(*) c, 16 y from employee where birth > '2004-09-02' and birth <= '2005-09-02'

union all

select count(*) c, 15 y from employee where birth > '2005-09-02' and birth <= '2006-09-02'

union all

select count(*) c, 14 y from employee where birth > '2006-09-02' and birth <= '2007-09-02'

union all

select count(*) c, 13 y from employee where birth > '2007-09-02' and birth <= '2008-09-02'

union all

select count(*) c, 12 y from employee where birth > '2008-09-02' and birth <= '2009-09-02'

union all

select count(*) c, 11 y from employee where birth > '2009-09-02' and birth <= '2010-09-02'

union all

select count(*) c, 10 y from employee where birth > '2010-09-02' and birth <= '2011-09-02'

union all

select count(*) c, 9 y from employee where birth > '2011-09-02' and birth <= '2012-09-02'

union all

select count(*) c, 8 y from employee where birth > '2012-09-02' and birth <= '2013-09-02'

union all

select count(*) c, 7 y from employee where birth > '2013-09-02' and birth <= '2014-09-02'

union all

select count(*) c, 6 y from employee where birth > '2014-09-02' and birth <= '2015-09-02'

union all

select count(*) c, 5 y from employee where birth > '2015-09-02' and birth <= '2016-09-02'

union all

select count(*) c, 4 y from employee where birth > '2016-09-02' and birth <= '2017-09-02'

union all

select count(*) c, 3 y from employee where birth > '2017-09-02' and birth <= '2018-09-02'

union all

select count(*) c, 2 y from employee where birth > '2018-09-02' and birth <= '2019-09-02'

union all

select count(*) c, 1 y from employee where birth > '2019-09-02' and birth <= '2020-09-02'

union all

select count(*) c, 0 y from employee where birth > '2020-09-02' and birth <= '2021-09-02'

最终的速度在3.9s,4s多。。

最后，最后有哪位老哥想出啥好方法来了，欢迎留言，告知。。