MySQL数据库设计、优化.pptx

MySQL,数据库,设计,、优化,叶金荣,微博：,yejinrong,微,信公众号：,MySQL,中文,网,QQ,群,:125572178,、,272675472,2013.08.20,提纲,规范,基础规范,命名规范,库,表规范,字段规范,索引规范,开发环境,优化,2,可整理ppt,规范,基础,规范,全部使用,InnoDB,引擎，,MyISAM,适用,场景非常少,字符集：,latin1=utf8=gbk,用数据库来持久化存储以及保证事务一致性，不是运算器,读写分离，主库只写和少量实时读取请求,采用队列方式合并多次写请求，持续写入，避免瞬间压力,超,长,text/blob,进行垂直拆分，并先行压缩,冷热数据进行水平拆分，,LRU,原则,快速更新大数据表禁止直接运行,count(*),统计,3,可整理ppt,规范,基础,规范,单表行记录数控制在,1000,万以内，行平均长度控制在,16KB,以内，单表,20GB,以内,单实例下数据表数量不超过,2000,个，单库下数据表数量不超过,500,个,禁止开发环境直连线上生产环境,最少授权，只授予最基础权限需求,压力分散，在线表和归档表（日志表）分开存储,线上数据库和测试数据库尽可能保持,一致,禁止明文存储机密数据，需至少两次加密（部分数据可逆运算）,4,可整理ppt,规范,命名规范,涉及,系统目录、文件、数据库、表、字段名,强烈建议,只用小写字符、数字、下划线组合,命名长度不超过,32,个字符,不使用,select,、,show,、,update,等保留字,全英文或全中文，言之有意，不要半洋半中,临时用加上,tmp/temp,前缀,/,后缀,统计表加上,stat/statistic,前缀,/,后缀,历史归档加上完整日期，例如：,20130802,mkdir,-,p/backup/user_log/2013/08,create table user_detail,create table,xxx_1234,create table access_log_20130820,5,可整理ppt,规范,库表规范,少用分区表等未完善的新特性不对,InnoDB,引擎表做在线实时,count(*),统计,分库、分表策略,以用户,ID=123456,为例，取,N/100%10=4,，取,N%10=6,最大,10,个分库，,10,个分表，共,100,个分表,则,分配到,DB_04,库,下，分表,TABLE_06,中,采用预存映射关系动态分配更灵活，不受分表算法变化而影响，但数据库开销大,6,可整理ppt,规范,字段规范,用,timestamp,（,4,字节,int unsigned,，且效率非常高）记录时间，而非使用,date/datetime/char/varchar,IPV4,地址采用,4,字节,int unsigned,，内置,INET_ATON/INET_NTOA,快速转换，采用,char,至少,15,字节,性别、状态、是否、小范围枚举使用,tinyint,（,0 255,，或,-128 127,）,Signed,Unsigned,Tinyint,-128127,0255,Samllint,-3276832767,065535,Mediumint,-83886088388607,016777215,Int,-21474836482147483647,04294967295,Bigint,-92233720368547758089223372036854775807,018446744073709551615,7,可整理ppt,规范,字段规范,char(10)VS varchar(10),尽可能不使用,text/blob,类型,存储字符型数据时，尽可能先压缩或者序列化,注意字符集问题，,server=database(trigger,、,stored procedure,、,event scheduler)=table=column,不要同时指定字符集（,character set,）和校验集（,collect set,），避免出现和默认对应关系不一致,Value,CHAR(4),Storage Required,VARCHAR(4),Storage Required,4 bytes,1 byte,ab,ab,4 bytes,ab,3 bytes,abcd,abcd,4 bytes,abcd,5 bytes,abcdefgh,abcd,4 bytes,abcd,5 bytes,8,可整理ppt,规范,字段规范,显式指定自增,int/bigint unsigned not null,作为主键,杜绝使用,UUID/HASH/MD5,类型作为主键,无须,预留，,越短越好，此处无须,18cm,O,(_)O,哈哈,MySQL 5.5,以上，,Online DDL,越来越方便,显式约束：,NOT NULL,9,可整理ppt,规范,SQL,规范,简化每一条,SQL,，短事务、快速执行、无阻塞,固定模式业务逻辑封装成存储过程,用括号显式确定,AND,、,OR,的先后顺序，避免混淆,注意引号问题会导致类型转换（,where id=1234,）,所有查询想尽一切办法使用索引：主键,=,唯一索引,=,索引,有些查询只需要扫描索引，无需扫描数据（,SELECT id,user FROM table WHERE id=1234,）,10,可整理ppt,规范,SQL,规范,过滤,用户提交,SQL,，防止注入,杜绝,like%xxx%,，不用,/,少用,like xxx%,不用,/,少用子查询，改造成连接（,JOIN,）,不用,/,少用,FOR UPDATE,、,LOCK IN SHARE MODE,，防止锁范围扩大化,SQL,中不用,/,少用函数，可能造成额外开销或者导致无法使用,索引,分页,SQL,采用内连接（,INNER JOIN,）实现，更高效,11,可整理ppt,规范,索引用途,快速定位,避免排序,覆盖索引可直接返回结果，无需扫描数据,唯一,索引可实现唯一约束,12,可整理ppt,规范,索引类型,B+Tree,Clustered index,（,InonDB vs MyISAM,）,Hash index,13,可整理ppt,规范,索引类型,B+Tree,14,可整理ppt,规范,索引类型,Clustered index,InnoDB vs MyISAM,15,可整理ppt,规范,索引类型,Hash index,16,可整理ppt,规范,索引规范,显式指定自,增,int/bigint,unsigned not,null,作,为主键,不使用外键,合理利用覆盖索引，但字段尽量不超过,5,个,合理利用最左索引（前缀索引,/,部分索引）,及时删除冗余索引,选择适当的索引顺序，选择性高条件靠前,17,可整理ppt,规范,索引规范,基数（,Cardinality,）很低的字段不创建索引（,MySQL,还不支持,bitmap,索引）,采用第三方系统实现,text/blob,全文检索,常用排序（,ORDER BY,）、分组（,GROUP BY,）、取唯一（,DISTINCT,）字段上创建索引,单表索引数量不超过,5,个,索引字段条件不使用函数,18,可整理ppt,规范,开发环境,启用,log_queries_not_using_indexes,设置,long_query_time,为最小值,定期检查分析,slow log,授权和生产环境一致,关闭,Query Cache,设置较小,InnoDB Buffer Pool,、,key buffer,size,数据,量不能太少，否则有些性能问题无法提前规避,19,可整理ppt,规范,行为规范,批量导入、导出,数据须,提前通知,DBA,，请求协助观察,推广,活动或上线新,功能须,提前通知,DBA,，请求压力评估,不,使用,SUPER,权限,连接数据库,单表多次,ALTER,操作,必须合并为一次操作,数据库,DDL,及重要,SQL,及早提交,DBA,评审,重要业务库须告知,DBA,重要等级、数据备份及时性要求,不在业务高峰期批量更新、查询数据库,提交线,上,DDL,需求，所有,SQL,语句须有备注说明,20,可整理ppt,优化,硬件,NUMA,新架构,，,CPU,直接存取内存，更高效,CPU,一般不是,瓶颈,，但,MySQL,多核,支持仍不佳,设备越来越廉价，大内存解决很多问题,SSD,应用越来越广泛，未来是主力,RAID,卡可有效提升,IOPS,及,数据安全（,RAID 10 vs RAID 5,）,RAID,卡必须配备,BBU,设置,FORCE,WB,FushionIO,很,NB,，但还是,贵族,21,可整理ppt,优化,系统,升级到,64,位,/tmp,使用,/dev/shm,的,tmpfs,内核,IO,调度：,deadline,，,noop,，反正不要,cfq,VM,管理：,vm.swappiness=0,文件系统：,xfs/zfs,全,B+,树，高效,分配组，提高并发度,延迟分配，减少,IO,mount,：,nobarrier,、,data=ordered,writeback,22,可整理ppt,优化,MySQL,配置,memlock,open_files_limit,max_connections,longquery_time,table_open_cache,key_buffer_size,query_cache_size,tmp_table_size/max_heap_table_size,23,可整理ppt,优化,MySQL,配置,innodb,buffer pool,innodb_flush_log_at_trx_commit,interactive_timeout/wait_timeout,transaction_isolation,innodb_log_file_size,innodb_data_file_path,innodb_max_dirty_pages_pct,24,可整理ppt,