MySQL 两张千万级数据表联查如何优化？

Question

最近去面试，面试官提了一个问题：现在有2张表，一张是user表，一张是order表，数据都在千万级以上的，order表有个user_id字段是user表的主键，现在查出用户订单情况的sql是：

SELECT
    `user`. user_name,
    `order`.order_number
FROM
    `user`
LEFT JOIN `order` ON`user`.id = `order`.user_id

请问如何优化上面代码或其他方法，使得查询效率变高？？？？

Answer 1

除了楼上的建索引外，建一张中间表，用来存储已经付费的玩家的id和username，这张表可以提前刷好，当有用户付费的时候就往里面插入。这样可以减小user 表的规模。

Answer 2

1.id 主键 2.user_id加索引
2.在数据表结构优化，增加临时表，专门存储两个表的id，并将user. user_name,order.order_number存储在临时表内

Answer 3

这是一个典型的大表左连接问题。
分析：order表中的数据一旦创建修改就很少了，所以可以采取冗余的方式，亦即直接把，order的多个order_number字段放在user表里面（适当的违反范式也是可以的，毕竟mysql都可以存json了）。
如果跳出mysql，这本书https://segmentfault.com/a/11...
里面的OceanBase有专门针对这个问题做优化，亦即采用冗余+基准数据和新改数据合并+定期更新基准数据的方式。

Answer 4

这个sql就很可疑，最简单的把order作主表join关联user应该比现在的sql效率好

SELECT
    `user`. user_name,
    `order`.order_number
FROM
    `order`
JOIN `order` ON`user`.id = `order`.user_id

当然了，单表数据上升到1千万之前，就该采取适当的分表策略，比如楼上gary345的回答

Answer 5

将这两个表做读写分离，专门查的地方启用数据库缓存，对于订单表可以做拆分，比如将历史记录（范围可以视业务情况而定，一般可以将超过1年的记录放到历史记录表里面），因为数据一般都是分页显示的，历史记录大部分情况用户不会去关心，所以跟新纪录放到一起完全没有必要，当然这么做需要开发成本较高，对于用户表可以根据userid（假设userid是自增的）做分区，按照一定的数据量级别（10W）作为一个分区单元，这样查询的时候就把1000W级缩成了10W级