Mysql 建库建表技巧分享

数据库  /  管理员 发布于 6年前   187

一、两表之间若有关联，你是否还在用主键进行关联？
　　比如现在有2张表，一张新闻栏目表，一张新闻表，现在两张表需要进行关联，我想大多数人的做法肯定是在新闻表里建一个新闻栏目id，然后把新闻栏目表里的主键ID（自增）写到这个字段里，通过这样进行两表关联。
　　如果你是这样做的，赶紧改掉这个习惯吧。也许你会问为什么，栏目id是主键啊，又是自增的，为什么这样操作不行？原因其实很简单，栏目我们会增加，也会删除，删除就会造成主键id之间会有断号的情况，由于主键设置为自增，也就是说你之前删掉的栏目，再进行添加，id是不会去补上哪个空缺的，而是一直递增。这样就会造成一种情况，如果那天对数据库进行优化，把主键进行了重新排序（暂时没有找到mysql优化软件会优化主键，但是可以通过代码删除主键，然后从新建立自增主键来实现主键重新排序），那就彻底杯具了，栏目和文章完全对不上号了。所以我建议两表之间关联不用主键，而是单独建一个编号的字段，我们这里可以用mysql的uuid()函数做为编号，相关文献可以参考《UUID做主键好还是不好》，只所以一张表要2个主键，一个物理主键（自增id），一个逻辑主键（UUID），原因是：对于InnoDB这种聚集主键类型的引擎来说，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力，此时不适合使用UUID做物理主键，可以把它作为逻辑主键，物理主键依然使用自增ID。至于性能，我本地测了下基本上没差异，网上也有人做了10W条数据的测试――《实测MYSQL UUID性能》。
二、统一把主键类型设为bigint吧
　　bigint是从-2^63 (-9223372036854775808)到2^63-1 (9223372036854775807)的所有整型数据，存储大小为8个字节。而int是从-2^31 (-2,147,483,648)到2^31-1 (2,147,483,647)的整型数据，存储大小为4个字节。存储空间扩大一倍，而存储数据却扩大N倍，再加上主键是一个自增的字段，我们根本无法控制它会自增到多少数值，所以我通常在建表的时候，主键类型都是设为bigint的，同样，上面提到的编号字段类型也是bigint。
三、不要把varchar长度设太“死”
　　这也是我之前经常犯得一个毛病，比如手机，我就设置为varchar(11)，邮编设置成varchar(6)，姓名设置成varchar(10)等等等等，看似每个字段都设置得很严谨，但是在项目实际进行中，这完全就是自找苦吃，比如手机，用户偏偏就要在手机号前输个0，又比如邮编，如果用户输入的是全角的数字呢？姓名就更不用说了，万一是个少数民族的人，名字七八个字。所以我建议，既然定义为varchar，就代表不会涉及到计算，何不干脆定义一个通用的长度，比如varchar(50)，如果真要限制长度，用程序去判断，不要让数据库来限制，不然用户输了一长串，结果mysql就存了前几个字符，让人觉得这程序有问题。
　　还有就是，如果你是做cms这种通用后台，更别把字段限制得太“死”，因为你无法预料之后的每个项目的需求，所以还是把varchar设大一点，我现在是统一都设为255，如果很有可能会超过255的字段，比如URL，我就干脆设置成text，一劳永逸。
四、为常用的搜索字段建立索引吧
　　不解释，但不要盲目建立索引。
五、欢迎您的回复补充