MySQL数据库设计、优化

《MySQL数据库设计、优化》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、MySQL数据库设计、优化叶金荣微博：@yejinrong微信公众号：MySQL中文网QQ群:125572178、2726754722013.08.20提纲规范基础规范命名规范库表规范字段规范索引规范开发环境优化规范基础规范全部使用InnoDB引擎，MyISAM适用场景非常少字符集：latin1=>utf8=>gbk用数据库来持久化存储以及保证事务一致性，不是运算器读写分离，主库只写和少量实时读取请求采用队列方式合并多次写请求，持续写入，避免瞬间压力超长text/blob进行垂直拆分，并先行压缩冷热数据进行水平拆分，

2、LRU原则快速更新大数据表禁止直接运行count(*)统计规范基础规范单表行记录数控制在1000万以内，行平均长度控制在16KB以内，单表20GB以内单实例下数据表数量不超过2000个，单库下数据表数量不超过500个禁止开发环境直连线上生产环境最少授权，只授予最基础权限需求压力分散，在线表和归档表（日志表）分开存储线上数据库和测试数据库尽可能保持一致禁止明文存储机密数据，需至少两次加密（部分数据可逆运算）规范命名规范涉及系统目录、文件、数据库、表、字段名强烈建议只用小写字符、数字、下划线组合命名长度不超过32个字符不

3、使用select、show、update等保留字全英文或全中文，言之有意，不要半洋半中临时用加上tmp/temp前缀/后缀统计表加上stat/statistic前缀/后缀历史归档加上完整日期，例如：20130802mkdir-p/backup/user_log/2013/08createtableuser_detailcreatetablexxx_1234createtableaccess_log_20130820规范库表规范少用分区表等未完善的新特性不对InnoDB引擎表做在线实时count(*)统计分库、分表策略

4、以用户ID=123456为例，取N/100%10=4，取N%10=6最大10个分库，10个分表，共100个分表则分配到DB_04库下，分表TABLE_06中采用预存映射关系动态分配更灵活，不受分表算法变化而影响，但数据库开销大规范字段规范用timestamp（4字节intunsigned，且效率非常高）记录时间，而非使用date/datetime/char/varcharIPV4地址采用4字节intunsigned，内置INET_ATON/INET_NTOA快速转换，采用char至少15字节性别、状态、是否、小范围枚

5、举使用tinyint（0~255，或-128~127）SignedUnsignedTinyint-128~1270~255Samllint-32768~327670~65535Mediumint-8388608~83886070~16777215Int-2147483648~21474836470~4294967295Bigint-9223372036854775808~92233720368547758070~18446744073709551615规范字段规范char(10)VSvarchar(10)尽可能不使用

6、text/blob类型存储字符型数据时，尽可能先压缩或者序列化注意字符集问题，server=>database(trigger、storedprocedure、eventscheduler)=>table=>column不要同时指定字符集（characterset）和校验集（collectset），避免出现和默认对应关系不一致ValueCHAR(4)StorageRequiredVARCHAR(4)StorageRequired'''    '4bytes''1byte'ab''ab  '4bytes'ab'3byt

7、es'abcd''abcd'4bytes'abcd'5bytes'abcdefgh''abcd'4bytes'abcd'5bytes规范字段规范显式指定自增int/bigintunsignednotnull作为主键杜绝使用UUID/HASH/MD5类型作为主键无须预留，越短越好，此处无须18cmO(∩_∩)O哈哈~MySQL5.5以上，OnlineDDL越来越方便显式约束：NOTNULL规范SQL规范简化每一条SQL，短事务、快速执行、无阻塞固定模式业务逻辑封装成存储过程用括号显式确定AND、OR的先后顺序，避免混淆

8、注意引号问题会导致类型转换（whereid=‘1234’）所有查询想尽一切办法使用索引：主键=>唯一索引=>索引有些查询只需要扫描索引，无需扫描数据（SELECTid,userFROMtableWHEREid=1234）规范SQL规范过滤用户提交SQL，防止注入杜绝like‘%xxx%’，不用/少用like‘xxx%’不用/少用子查询，改造成连