还剩45页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
SQL基础、规范、用户权限管理雷彻Numericjnteger•类型•哪些字段可用?IP0〜4244897025unsignedint而不用char15函数inet_atoninet_ntoa手机号,unsignedbigint而不用charllNumericdecimal•fixed-point-decimalMD-整数最大位数M为65小数位数最大D为30•floating-point-floatpp=244B;25=p=538B;-double8B建议使用decimal避免float和doubleDateTime格式,取值范围1000-01-0100:00:00〜9999-12-3123:59:598B(
5.
6.4以前)格式,YYYY-MM-DD3B格式,hh:mm:ss3B格式,函数转换,范围1970〜2037年,4B格式,YYYY1B•变化
5.
6.4以后,不再对timestamp的cunrent_timestamp类型进行限制,默认为nullso选用timestamp避免datetime;其余按需求;stringvarcharcharvarcharn变长,表中n取值ln65535-[l2]/字符集需要工〜2个节来存储长度charn定长存储长度相差不大,频繁变化的的类型用charvarcharBNblobtextvarcharBNblob二进制,不可排序text文本类型,可排序,选择前缀排序OutlineASQL基础/什么是SQL♦DDL/DML/DataTypeAsql规范/数据库设计/数据库操作»用户权限管理/用户授权与用户信息表/安全管理用户SQLStantard•数据库设计-预估-范式与反范式-字段规范-索引规范-其他注意点•数据库操作-拒绝3B一分页limitMN-subquery-join-避免count*-其他DB_Design_estimate•目的-预估数据量,增长速度,热数据,访问量,库表大小,数量,热库热表,读写比例等两年内-确定存储typesize内存,架构,带宽,备份,数据物理分布等等-减少后期维护成本,避免资源浪费•如何确定-根据数据量,增长量来确定磁盘大小-根据增长速度,访问量,读写比例来确定磁盘类型,raid文件系统,架构等-根据热数据热库热表业务来确定内存大小,配置参数,数据分布-根据数据重要性,量,来确定容灾及备份策略本ppt暂不讨论硬件和配置问题DB_Designjestimate_ldtcInstance单实例建议不要超过TOOG内存制约200G热数据一般在15〜20G备份恢复具体根据业务来确定Database单库不超过300-400个表单表字段20〜50内存需求18〜40GDB_DesignJ:ormanti-form・范式-范式定义,举例1NF字段属性单一,不可再分2NF实体的属性完全依赖于主关键字3NF不存在非关键字段对任一候选关键字段存在传递函数依赖•反范式-违反范式设计表-通过适当增加冗余来减少多表join降低i消耗,内存消耗等-适用场景•涉及多表查询时,为精简程序,可以适度冗余•存在较多数据统计需求(countsum等),效率低下DB_Designjcolumn•规则-越小越优,定长较优,满足应用即可•举例-越小越好范围合适的般形;适当转换字符型为整形;emun或set较小时,使用tinyint替换;避免大字段bIobvarchar2000■■■-精确时间类型采用精确格式,避免浪费存储年用year;存储日期用date;存储时间精确到s使用timestamp或者int需要转换一定长较优char与varcharvarcharnn255时不妨用char代替快;长度一致时,流水号采用char;OutlineASQL基础/什么是SQL♦DDL/DML/DataTypeAsql规范/数据库设计数据库操作»用户权限管理,用户授权与用户信息表/安全管理用户注意本ppt涉及内容仅适合mysql默认Innodb引擎本ppt所述仅代表个人观点DB_Designjcolumn•使用decimal避免使用double和float•varchar255与varchar256的不同;•大字段varchar5000与blob放在单表中;•自增列使用int或者bigint标明unsigned;•避免null字段都必须为notnulldefaultxxx•避免uuid;•int和int8intll有区别吗•设置comment;DB_Design_lndex嗦引idx_columnl_column2_column3o普通索引,小写,可以适当缩写udx_columnl_column2_column3o唯一索引,小写,可以适当缩写•规则最左前缀组合索引上限5column必须明确指定pk长于50的varchar字段使用合适的前缀索引选哪些字段?顺序如何?根据业务sql来定OutlineASQL基础/什么是SQL♦DDL/DML/DataTypeAsql规范/数据库设计/数据库操作»用户权限管理/用户授权与用户信息表/安全管理用户DB_Oper_refuse_3Bwhatis3B一BigTrasaction•资源占用时间长,锁多,日志量大,影响并发,影响数据同步例如:updatetable_Asetcol_a=wherecol_b、b-BigSQL•资源占用filesortgroupjoinsubquery大耗时长例如:selectcol_afromtable_Awhereexistsselectidfromtable_Bwhereidxxxorderbycol_blimit10;-BigBatch•并发高,资源紧张例如:公司批量I/D/U一些数据DB_Oper_refuse_3BHowtodeal一BigTrasaction-拆小事务拆分标准pk最佳,单行数据事务,多行数据事务均可-BigSQL-sql优化,拆分,表拆分,加冗余,程序修改等一BigBatch-拆成小批量,加间隔等,建议分1W条一次,具体和sql效率有关DBOperlimitMN•limitmn如何运行-先遍历前m+n行数据,对结果进行排序,再读取m+lm+n区间的数据-M值越大,查询越慢Select*fromtableAlimitmn;•limitmn如何优化-拿到第m行的id按排序顺序取后n行即可连续id Select*fromtable_Awhereid=mlimitn;非连续id select*fromtable_Awhereid=selectidfromtable_Alimitmllimitn;select*fromtable_Ainnerjoinselectidfromtable_Alimitmnusingid;DB_Oper_subquery•subquery-独立子查询Selectabcfromtable_Awheredinselectefromtable_B;-相关子查询Selectabcfromtable_Awhereexistsselect1fromtable_Bwheretable_B.e=table_A.d•subquery的优化-避免子查询,必要时候在程序中拆分成单句执行-独立子查询令相关子查询||joinDB_Oper_join~~Leftjoi-FM4gh-tjoiR•Innerjoin(内联或等值链接)Selecta.collb.col2fromtable_AaJointable_Bbona.col3=b.col4Selecta.collb.col2fromtable_Aatable_Bbwherea.col3=b.col4•执行顺序ForeachrinRstoreusedcolumnaspintojoinbufferforeachsinSifs=routputpsDB_Oper_join•如何优化-调整关联顺序,小表在前,大表在后-适当添加索引,内表SDB_Oper_count^•count*如何进行-将数据从外存读入内存,计数-避免或在无访问的从库进行•其他count类型的运行一count*countpkcountuniquecountl一速度count*〜countlcountunique〜countpkWhatisSQLSQLStructureQueryLanguage结构化查询语言T-SQLTransact-SQLMSSql对sql标准的增强PL/SQLProceduralLanguageSQLOracle对SQL的扩展DB_Oper_other尽量使用pk或者uniquekey进行updatedelete操作避免isnull或isnotnull减少不等值查询避免使用*,选择需要字段进行查询Where子句中,阈值较多的col放在前面如非去重,使用unionall代替union避免使用in合理利用覆盖索引扫描避免完全模糊匹配OutlineASQL基础/什么是SQL♦DDL/DML/DataTypeAsql规范/数据库设计/数据库操作»用户权限管理/用户授权与用户信息表/安全管理用户DB_Oper_privileges•线上库对程序员开放的访问权限一只读Sdbname_r读写S/I/D/U・测试站点-根据需求开启ddl权限createalter•其他用户一复制replicationslave-root权限用户dbname_wDB_Oper_privileges•相关表mysqLuser对实例的权限,包含passwordmysql.db对库的操作权限,包含db•授权回收权限回收用户grantselectondbname.[tablename|view]touser@hostidentifiedbypassword;flushprivileges;revokeprivilegesondbname.[tablename]fromuser@host;dropuseruser@host;OutlineASQL基础/什么是SQL♦DDL/DML/DataTypeAsql规范/数据库设计/数据库操作»用户权限管理/用户授权与用户信息表/安全管理用户DB_Oper_security•linux的iptables限制网段•用户允许访问的ip段限制•程序用户权限限制・密码复杂度大小写字母、数字、特殊字符,8位以上DB_Oper_security•root用户的初始化mysql〉selectuserhostpasswordfrominysql.user;SETPASSWORDFORYoot^localhost=PASSWORDC123456;SETPASSWORDFORYoot@
12700.li=PASSW0RD123456;SETPASSWORDFORroot@::l=PASSW0RD123456;SETPASSWORDFORYoot^^ostname1=PASSW0RD123456;dropuser©localhost;dropuser”@mysql-l-l;dropuserYoot@mysq1-1-11dropdatabasetest;•丢弃root用户OutlineASQL基础/什么是SQL♦DDL/DML/DataTypeAsql规范/数据库设计/数据库操作A用户权限管理,用户授权与用户信息表,安全管理用户DDLDDLDataDefinitionLanguage数据库结构定义语言描述数据库中要存储的现实实体的语言常见的DDLcreate/alter/dropdatabase...create/alter/drop/truncate/renametable...create/alter/dropview...create/dropindex...j2SWtriggereventprocedurelogfiletablespacefunctionDDL需要的权限createdropaltereventtrigger...DML•DMLDataManipulationLanguage数据操纵语言•常见的DMLselect/insert/delete/update/replacecall/do/load/subquery...•DML的权限select/insert/delete/updateOtherStatement•transactionlockstatement-常见SQL begin/start[readonly]transactioncommitrollbackXAtransaction...lock/unlock-权限lock•replicationstatement-常见SQL changemasterto...start/stopslavestart/stop[I/O|SQL]threadsetsqljog_bin...resetmasterpurgebinarylogs...一权限repelication•administationstatement-常见SQL grant/revoke/drop/aIterusersetchecksum/anlyse/repairtable...OutlineASQL基础/什么是SQL♦DDL/DML/DataTypeAsql规范/数据库设计/数据库操作»用户权限管理/用户授权与用户信息表/安全管理用户DataTypeMysql的DataTypeyeartimestamptinyintsmallintmediumintintbigint•ft5上浮点floatDouble/11期时间型time整数型datetime数值型J字符小型~~setienum小数型blobtextdecimal定点varcharcharTypeStorageMinimumValueMaximumValueBytesSigned/UnsignedSigned/UnsignedTINYINT1-2A72A702A8SMALLINT2-2A152A1502A16MEDIUMINT3-2A232A2302A24INT4■2A312A3102A32BIGINT8■2A632人6302A64—user1—hostFpassword1———1root1localhost1rootayl40711224741674952zroot
127.
0.
0.1root1localhost—ayl407U224741674952z—L。