Mysql 你不知道的 Limit

发表信息: May 12, 2018 by gc

文章分类:
学习 32

表
业务
总结

表

表结构

表名: restaurant
备注: 店铺信息表

字段名	字段类型	是否为null	默认值	是否索引	索引名	备注
id	bigint(20)	no	自增	yes	id_UNIQUE	自增主键
saturn_city_id	int(11)	no	0	yes	ix_saturn_city_id	城市id
is_valid	tinyint(4)	no	0	no		是否有效
created_at	timestamp(3)	no	CURRENT_TIMESTAMP(3)	yes	ix_created_at	创建时间
updated_at	timestamp(3)	no	CURRENT_TIMESTAMP(3)	yes	ix_updated_at	更新时间
deleted_at	timestamp(3)	no	CURRENT_TIMESTAMP(3)	yes	ix_deleted_at	删除时间

为什么 is_valid 没有索引

首先，不单单好似is_valid, 还有些类似 data_status 等表示枚举的字段，都没有必要增加索引，因为这种字段的值往往屈指可数，加上索引之后一个只值仍然会对应很多条记录（这里所说的很多条一般都是10w条以上），所以设置索引的效果并不明显。

还有这种字段特别是表示记录／任务状态的字段，值最好不要以0，1这样数值，特别是0， 0 最好只代表无效／初始数据（因为0一般都是数据库的默认值），如果0这个值表示比较复杂的业务，那么以后就会感觉很乱，更要注意的是，不要用0表示有效营业展示这种带有true的意义。

物理删除／逻辑删除

db的表最好不设置物理删除，只做逻辑删除，这样数据就算业务上没有了价值，也可以分析出别的，具有商业价值的信息出来。而且可以做业务回滚，毕竟物理删除就真的是把数据从这个世界上删除了，而数据对这个世界的重要性，已经不言而喻了。如果数据过多，可以选择归档（归档也只是把数据从线上表中转移到其他表中，放置起来）。

操作记录

如果是业务表，比如店铺基本数据，商品基本数据等，这种对操作及其敏感的数据，一定要做操作记录，对谁(who)什么时候(when) 在哪里(where) 做了什么(what) 都要记录下来，就算记录下来的数据爆炸，也要记录，可以把归档频率调高，比如一周一次归档，三天一次归档等。这种数据是非常非常有价值的（如果有人恶意操作，我们可以在业务上进行限制，比如对商品的操作一天不能超过100次等）

业务

查询所有

1.如果想要查询某个城市下有效的店铺，可以使用 select * from restaurant where saturn_city_id = xxx and is_valid = 1

上面那个SQL由于Where条件里面只有 saturn_city_id 和 is_valid 两个字段，且只有 saturn_city_id 有索引，所以肯定走 satrun_city_id 这个索引

真的没有坑么

我们再看下上面的sql select * from restaurant where saturn_city_id = xxx and is_valid = 1, 难道这样就足够了吗？当然不是，因为查询结构可能有上万甚至上十万条，甚至可能造成OOM（如果数据量足够大的话）。而且当 restaurant 表中一行纪录的数据足够多，就会造成网络流量被打满， DB压力过大的情况，所以我们查询要改一改，当然具体改成什么样子的，就要看真正的用途了，如果只想要所有的店铺id，可以使用 select id from restaurant where saturn_city_id = xxx and is_valid = 1 , 当然，如果数据量过大，仍然会造成问题，所以可能用如下的做法:

last_id = 0
limit = 500
while True:
    shop_ids = select * from restaurant where saturn_city_id = xxx and id > last_id and is_valid = 1 and limit = 500
    if not shop_ids:
        break
    
    for shop_id in shop_ids:
        do_some_things(shop_id)
    
    last_id = shop_ids[len(shop_ids) - 1]

为什么不用 limit offset

有过对大数据量查询经验的朋友肯定知道， sql执行时间会随着 offset 的变大直线增长（之前用offset遍历500w的数据，当offset到100w的时候， sql响应时间就已经4s左右了，可想而知当offset快到500w左右，会多么恐怖

使用 id > last_id 的响应时间是个常量，且不会随着last_id的增长而增长

当Where中有两个索引字段

当你认为上面那个sql已经天衣无缝的时候，你就错了， mysql innodb引擎只会选择一个索引使用，上面那个sql的where条件中有两个索引可以供 mysql 选择 ix_saturn_city_id 和 id_UNIQE ，当 mysql 选择 ix_saturn_city_id 索引走的时候万事大吉，但是当选择 id_UNIQUE 的时候就是悲剧了，因为这时候是 全表扫描

Force Index

如果要强制让SQL走某个索引可以使用 Force Index 关键字，如 :

select * from restaurant force index (ix_saturn_city_id) where saturn_city_id = xxx and id > xxx and is_valid = 1 limit 100

使用 sqlalchemy 的话要这样写:

shop_ids = shop_session.query(Restaurant.id).with_hint(Restaurant, 'FORCE INDEX(ix_saturn_city_id)').filter(Restaurant.saturn_city_id == xxx ).filter(Restaurant.is_valid == xxx).filter(Restaurant.id > last_id).limit(1000)

总结

当sql中查询的Where条件有多个，且有多个索引可以供mysql选择， mysql就可能选错索引，也由此如过想让mysql强制走某个索引，可以使用 force index(xxxx) 关键字。

表

表结构

为什么 is_valid 没有索引

物理删除 ／ 逻辑删除

操作记录

业务