MySQL数据库设计内容与查询.pdf

《MySQL数据库设计内容与查询.pdf》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、互联网软件架构漫游指南!高性能的MySql数据库设计与查询Leap@BestApp核心思想•让MySql做他最擅长的事情：•数据库是用来存储和查询数据的！•尽量不要在数据库做复杂的统计分析运算，复杂运算放在后台程序里进行！（count、sum、min、max、avg、groupby）•尽可能的减少数据库查询次数：•不要在循环中查询数据库，特别是循环次数不确定的时候。表设计•数据规模：•单库数据表的数量控制在300张以内。•单表记录数超过100W就要开始考虑分表，超过500w就必须进行分表。•单表字段数根据字段类型，控制在20～40个。表设计•主

2、键：•每张表都要有唯一的主键•主键由数据库或者主键生成系统统一维护•主键一旦写入，禁止修改•禁止使用数据库自带的外键约束：•不在数据库内创建外键约束，外键约束由程序控制•禁止使用存储过程、触发器、视图、计划任务表设计•字段类型：•可变长字段使用varchar类型，字段长度超过4000时，必须和原表进行分拆•仅当需存储的字节数可能超过20000时，使用TEXT系列类型，必须和原表进行分拆•精确到天的时间字段使用DATE类型•精确到秒的时间使用DATETIME类型•尽量不要使用null字段，为可能为空字段设置默认值。•取值有限的字段（性别、民族等）

3、，使用ENUM代替VARCHAR表设计•索引：•大幅提高查询操作速度，但是会降级变更操作的速度，需要消耗额外的存储空间。所以索引不是越多越好。•每个查询只能使用一个索引，在where子中使用了，就无法在orderby子句中使用，除非两者能使用同一个索引。•索引不会保存索引列全为NULL的记录•字符字段必须且只需要建前缀索引•根据SQL语句的执行频率来决定索引的建立•数据重复度非常高的字段不需要建索引表设计•单列索引•组合索引•最左前缀原则•合理安排where子句中各个条件的顺序，充分利用组合索引，减少单列索引的数量。表设计•最左前缀原则•在or

4、ders表上按照user_id、product_id和depot_id的顺序，建立了复合索引。•那么当查询的where条件以user_id为第一个查询条件时，都可以使用到索引。SQL查询优化•禁止使用：select*from…•浪费内网带宽，增加CPU和内存的消耗。•指定需要的字段：selecta,b,cfrom…•降低表字段变更对程序的影响。•不要使用不走索引的查询语句。•如果一定要使用like进行模糊查询，禁止使用左模糊(%xxx)，可以使用右模糊(xxx%)。SQL查询优化!•尽量减少在查询中使用count关键字，资源消耗很大。•除非是统

5、计特定字段的非空记录数，不要使用count(列名)，使用count(*)。•实时统计可以考虑在外部缓存系统中进行（redis），然后在低负载时段定期同步基准数据。•非实时统计同可以在低负载的凌晨进行运算，将运算的结果单独保存在统计表中。SQL查询优化SQL查询优化•尽量不要写大SQL语句（连接多个表，复杂的查询条件，大量删除）•一条SQL语句只能在一个CPU内核上执行•大SQL可能会卡住整个数据库•难以利用现有缓存•将大SQL拆分成多个简单的小SQL•发挥多核服务器性能•减少锁表时间•提高缓存利用率SQL查询优化•selectusers.id,

6、users.name,users.avatar,comments.contentfromcommentsinnerjoinusersoncomments.user_id=users.idwherecomments.id=2!•selectuser_id,contentfromcommentswhereid=2•selectid,name,avatarfromuserwhereid=user_idSQL查询优化•Where子句中：•同一字段不同值的查询，使用in，不要使用or。•in的时间复杂度：logn•or的时间复杂度：n•不同字段不同值的查

7、询，使用union，不要使用or。•比较条件两侧的字段的类型要保持一致•避免使用否定条件查询SQL查询优化•高效的分页的实现：•普通实现：•select*fromtable_namelimit1000,10•偏移量越大，速度就越慢•优化实现：•selectidfromtablelimit1000,1•select*fromtablewhereid>=idlimit10•有20～50倍的性能提升。其他注意事项•测试期间，定期从PROCEDUREANALYSE()取得建议，根据这些建议，结合实际情况，改进表结构。•打开慢查询日志，定期捕获慢查询语句

8、进行优化•系统正式上线前，Explain所有频繁执行的SQL查询语句，根据结果进行优化。分布式数据库的特殊要求•一条SQL语句只查询一张数据表。•不要