mysql二亿大表_面对有2亿条数据的mysql表

看到这个2亿5千条数据的表，我的内心是拒绝的，各种条件筛选要取出相应的数据，被折磨了两天，现在记录下心路历程

先分享下mysql相关的知识点1 名词解释

主键(PRIMARY KEY): 唯一索引，不能重复

组合主键(UNIQUE): 组合索引，若干个字段组成一个主键2 SELECT必备 – EXPLAIN使用EXPLAIN来查看sql语句是否能命中索引，量大的表必须要命中索引才能执行，也还得看以下条件

(1) type

ALL， index， range， ref， eq_ref， const， system， NULL(从左到右，性能从差到好)(具体就不在这解释了)

(2) possible_keys

可能用到的索引，但不一定命中

(3) key

实际使用的key。想强制使用某个索引，可用FORCE INDEX。

(4) key_len

根据表定义计算而得的索引中使用的字节数，越短越好。

(5) rows

可能要读取的行数

(6) Extra (划重点)

Using Index: 表示使用索引，叫做覆盖索引，没有查询数据表，只查询了索引。如果同时出现Using Where代表可以用到索引，但是需要查询数据表。

Using where: 表示条件查询，不读取所有数据或者通过索引获取所需的数据。

Using filesort: 文件排序，无法利用索引的完成的排序操作

Using temporary: 需要使用临时表来存储结果集，常见于多表联合查询和排序如果出现Using filesort和Using temporary，尽量优化sql语句

开始操作这个2亿条数据的表1 命中联合主键

表中有若干个联合主键，就尽量往这些联合主键上靠了。EXPLAIN

SELECT *

FROM student

WHERE

class IN (1, 2, 3) AND

score > 90 AND

sex = 1

ORDER BY id DESC

LIMIT 0, 60idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEstudentrangeindex2， index3， index4， index5index244NULL642323Using index condition; Using filesort

可以命中联合主键索引，但rows特别多，执行非常慢，这个方案就pass了。2 命中联合主键进阶版

方案一中有些条件筛选命中联合主键速度还可以，但是加上ORDER BY time(time也是个索引)后就不行了。

主键id是时间加随机数，大体能满足排序，就改成了ORDER BY id，发现命中的索引还是联合主键。

对sql语句进行了如下修改:EXPLAIN

SELECT *

FROM student AS st1

LEFT JOIN student AS st2

ON st1.id = st2.id

WHERE

st1.class IN (1, 2, 3) AND

st1.score > 90 AND

st1.sex = 1

ORDER BY st1.id DESC

LIMIT 0, 60idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEst1rangeindex2， index3， index4， index5index244NULL642323Using where; Using index; Using filesort

1SIMPLEst2eq_refPRIMARYPRIMARY413800st.id1NULL

这样命中了联合主键和唯一主键，执行速度也很快，但是如果量比较大，改为st1.score > 60，就又不行了，第二个方案也pass。3 命中主键

方法总比困难多，最后只能往主键索引上靠了。先限定时间区间(可命中主键索引)，能有效控制要筛选的数据量，再对时间区间内的数据筛选。EXPLAIN

SELECT *

FROM student

WHERE

(id LIKE '20180501%' OR id LIKE '20180502%' OR id LIKE '20180503%') AND

class IN (1, 2, 3) AND

score > 90 AND

sex = 1

ORDER BY id DESC

LIMIT 0, 60idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEstudentrangePRIMARY， index2， index3， index4， index5PRIMARY98NULL413800Using where

这样命中主键索引，Extra是Using where，执行速度也是相关快。某个条件筛选时Extra会有Using filesort，但执行速度挺快，就没管了。

总结

面对这样的mysql表只有命中主键索引啦。