工作中mysql相关问题罗列（一）

之前工作和mysql强相关，在进一步了解mysql以后，觉得数据库很牛，水很深。不只是因为数据库对我来说是黑盒，更因为数据库对我来说很难成为白盒。一直一来有稍微不爽就看源码的习惯，但mysql源码，呵呵，算法、结构设计甚至代码量和其他很多经典源码都不是一个量级的。以我现在的能力，写关于mysql的博客不过就是堆砌事实，提出疑问，逐步找答案罢了。
千里之行始于足下，就从本文开始，先列举工作中的问题。
当然，所列举的潜在的正反面因素还和mysql版本有关。后面对此不再声明。


1.用存储过程防注入
背景：为了防注入公司决定所有mysql交互都采用框架自带的存储过程。
正思：很多语言框架都支持存储过程，使得项目编码风格统一不易出错；存储过程解析一次反复使用，降低代价；每次调用相同的过程传送的数据较少；安全，很多情况下安全第一；
反思：防注入也有其他手段，比如对每个用户输入参数校验并转换；存储过程的query不能用查询缓存（见百度）；mysql是按链接维护存储过程（http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/），如果不是用连接池且每一次连接都没有重复使用那会承担额外的维护开销；解析结果常驻内存，减少了mysql可用内存；


2.外键的使用
背景：互联网行业流传甚广的说法“千万别用外键”。
正思：只有外键才能真正保证数据的一致性；外键mysql自动建立索引，小索引可以常驻内存，对应的b+树树高增加得慢，代价相对较小；
反思：可以从业务端去保证某种数据一致性，看业务复杂度和对不一致的容忍程度；外键有额外的合法性校验代价；


3.主键用自增主键？
背景：主键是否应该带应用信息？比如对于一般web应用记录用户的登录情况，每个用户登录一次建立一条记录；业务场景是导出一个id的用户最近一百次上线时间。
正思：自增主键表小紧凑；没有中间插入，写入区域集中，写硬盘有利；
反思：以用户id+登录时间戳作为主键，插入常发生在表内部，存储也不紧凑；但是同一用户的信息都放到了一起，局部性好，如果用自增主键做id对一个上线时间间隔很大的用户一百次上线时间可能导致查询一百个不同的mysql页；


4.查询缓存开不开，开多少
背景：对于某一类具体的业务，是否从mysql层次开查询缓存？缓存开多大？其实就是参数调优。
正思：对于一个知识展示性质的问答平台，比如网上的医疗网站，新增内容不多但是读操作很多。觉得该大缓存；
反思：对于论坛性质的，看两贴就回一次那种觉得缓存可能可以关了（这种说法好无力感），不过真得看业务和测试。


5.连表查询还是业务方取数据来自己join
背景：互联网行业流传甚广的说法“千万别连表查询”。比如a表是作者表，b是书表，c表是作者id和书名id表，已知几个作者，要找出这些作者的书名。
正思：连表查编码简单；连表查网络流量小因为只返回有用的结果；
反思：读a表查id，读c表查书名id，读b表查书名，这样mysql只用简单的取和返回，把可能比较耗时的join实际操作给省掉了；实际业务操作机器可以无限扩充；
备注：这个我最是无语，当然也听到得最多，如果join都没优化好mysql也该禁止这种语法了。


6.“点赞”表设计：联合主键和redis
背景：一个用户id只能对一篇文章id点赞一次。点赞次数较多，设计方案和表。读方案都是从mysql读，至于用不用redis缓存或本地缓存另算；写方案一mysql表文章id+用户id为联合主键，innodb_flush_log_at_trx_commit设为0，定期刷回硬盘；方案二把点赞事件写入redis，点赞显示前端渲染，之后定期（或者由下一次点赞激发）把这些事件持久化到mysql。这是实际工作遇到的问题，找了下有点类似于http://www.cnblogs.com/chunguang/p/5682411.html的讨论。
问题一：是否维护一个点赞总数表，还是按照联合主键第一个元素文章id实时统计，前者读取快是肯定的，但是因为业务层有缓存压力也不太大；后者可以缩短流程，免用事务，关键是慢多少；
问题二：redis方案已经有了丢失点赞的风险，那么把这种风险转给mysql，让mysql自己不是条条写硬盘。redis方案最终还是要持久化到mysql的，只是“刷”的频率和前台写的可以不一致。问题最终大约等于，纯内存写的mysql能抗住多少压力？


还有问题下一篇待续，然而上面说的这些问题，在工作中我思考过，但是没有完备地测试过，很多时候是和别人讨论出的方案，额，还是实在没法说服自己，希望接下来能有长进。