《mysql的SQL基礎(chǔ)規(guī)范與權(quán)限管理》由會(huì)員分享,可在線閱讀,更多相關(guān)《mysql的SQL基礎(chǔ)規(guī)范與權(quán)限管理(37頁(yè)珍藏版)》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。
1、 SQL基礎(chǔ)、規(guī)范、用戶權(quán)限管理雷徹 OutlineSQL基礎(chǔ)什么是SQL?DDL/DML Data Type sql規(guī)范數(shù)據(jù)庫(kù)設(shè)計(jì)數(shù)據(jù)庫(kù)操作用戶權(quán)限管理用戶授權(quán)與用戶信息表安全管理用戶注意:本ppt涉及內(nèi)容僅適合mysql,默認(rèn)Innodb引擎 本ppt所述僅代表個(gè)人觀點(diǎn) What is SQL?SQL,Structure Query Language,結(jié)構(gòu)化查詢語(yǔ)言T-SQL,Transact SQL,MSSql對(duì)sql標(biāo)準(zhǔn)的增強(qiáng)PL/SQL,Procedural Language SQL, Oracle對(duì)SQL的擴(kuò)展 OutlineSQL基礎(chǔ)什么是SQL?DDL/DML Data Typ
2、e sql規(guī)范數(shù)據(jù)庫(kù)設(shè)計(jì)數(shù)據(jù)庫(kù)操作用戶權(quán)限管理用戶授權(quán)與用戶信息表安全管理用戶 DDLDDL,Data Definition Language,數(shù)據(jù)庫(kù)結(jié)構(gòu)定義語(yǔ)言描述數(shù)據(jù)庫(kù)中要存儲(chǔ)的現(xiàn)實(shí)實(shí)體的語(yǔ)言常見(jiàn)的DDLcreate/alter/drop database create/alter/drop/truncate/rename table create/alter/drop view create/drop index 還有trigger,event,procedure,logfile,tablespace,function DDL需要的權(quán)限create,drop,alter,event,tr
3、igger DMLDML,Data Manipulation Language,數(shù)據(jù)操縱語(yǔ)言常見(jiàn)的DMLselect/insert/delete/update/replacecall/do/load/subqueryDML的權(quán)限select/insert/delete/update Other Statementtransaction&lock statement常見(jiàn)SQL: begin/start read only transaction,commit,rollback,XA transaction lock/unlock 權(quán)限:lock replication statement常見(jiàn)SQ
4、L: change master to,start/stop slave,start/stop I/O|SQL thread set sql_log_bin,reset master,purge binary logs權(quán)限:repelication administation statement常見(jiàn)SQL:grant/revoke/drop/alter user,set,checksum/anlyse/repair table OutlineSQL基礎(chǔ)什么是SQL?DDL/DML Data Type sql規(guī)范數(shù)據(jù)庫(kù)設(shè)計(jì)數(shù)據(jù)庫(kù)操作用戶權(quán)限管理用戶授權(quán)與用戶信息表安全管理用戶 Data Type
5、Mysql的 Data Type Numeric_integer類型TypeStorage(Bytes)Minimum ValueMaximum Value(Signed/Unsigned)(Signed/Unsigned)TINYINT1-2727028SMALLINT2-2150215216223MEDIUMINT3-2230224231232263264INT4-2310BIGINT8-2630哪些字段可用?IP,04244897025,unsigned int而不用char(15),函數(shù)inet_aton(),inet_ntoa() 手機(jī)號(hào),unsigned bigint而不用char
6、(11) Numeric_decimalfixed-point decimal(M,D) 整數(shù)最大位數(shù)(M)為65,小數(shù)位數(shù)最大(D)為30 floating-point float(p) p=24,4B;25=p=53,8B; double 8B 建議使用decimal,避免float和double Date&Timedate&time datetime 格式,取值范圍1000-01-01 00:00:009999-12-31 23:59:59, 8B (5.6.4以前) date 格式,YYYY-MM-DD,3B time 格式,hh:mm:ss,3B timestamp 格式,函數(shù)轉(zhuǎn)換,
7、范圍19702037年,4B year 格式,YYYY,1B 變化5.6.4以后,不再對(duì)timestamp的cunrent_timestamp類型進(jìn)行限制,默認(rèn)為null so,選用timestamp,避免datetime;其余按需求; stringvarchar&charvarchar(n) 變長(zhǎng),表中n取值1n(65535-1,2)/字符集需要12個(gè)節(jié)來(lái)存儲(chǔ)長(zhǎng)度char(n) 定長(zhǎng)存儲(chǔ)長(zhǎng)度相差不大,頻繁變化的的類型用char varchar(BN) & blob & text varchar(BN) blob二進(jìn)制,不可排序 text文本類型,可排序,選擇前綴排序 OutlineSQL基礎(chǔ)
8、什么是SQL?DDL/DML Data Type sql規(guī)范數(shù)據(jù)庫(kù)設(shè)計(jì)數(shù)據(jù)庫(kù)操作用戶權(quán)限管理用戶授權(quán)與用戶信息表安全管理用戶 SQL Stantard數(shù)據(jù)庫(kù)設(shè)計(jì)預(yù)估范式與反范式字段規(guī)范索引規(guī)范其他注意點(diǎn)數(shù)據(jù)庫(kù)操作拒絕3B 分頁(yè)limit M,N subquery join 避免count(*) 其他 DB_Design_estimate目的預(yù)估數(shù)據(jù)量,增長(zhǎng)速度,熱數(shù)據(jù),訪問(wèn)量,庫(kù)表大小,數(shù)量,熱 庫(kù)熱表,讀寫比例等(兩年內(nèi))確定存儲(chǔ)(type,size),內(nèi)存,架構(gòu),帶寬,備份,數(shù)據(jù)物理分布等等減少后期維護(hù)成本,避免資源浪費(fèi)如何確定根據(jù)數(shù)據(jù)量,增長(zhǎng)量來(lái)確定磁盤大小根據(jù)增長(zhǎng)速度,訪問(wèn)量,讀寫比例
9、來(lái)確定磁盤類型,raid,文件系統(tǒng),架構(gòu)等根據(jù)熱數(shù)據(jù)熱庫(kù)熱表業(yè)務(wù)來(lái)確定內(nèi)存大小,配置參數(shù),數(shù)據(jù)分布根據(jù)數(shù)據(jù)重要性,量,來(lái)確定容災(zāi)及備份策略本ppt暫不討論硬件和配置問(wèn)題。 DB_Design_estimate_IdtcInstance單實(shí)例建議不要超過(guò)100G。內(nèi)存制約。200G熱數(shù)據(jù)一般在1520G,內(nèi)存需求1840G 備份恢復(fù)。具體根據(jù)業(yè)務(wù)來(lái)確定Database單庫(kù)不超過(guò)300-400個(gè)表。單表字段2050。 DB_Design_form&anti-form范式范式定義,舉例1NF:字段屬性單一,不可再分2NF:實(shí)體的屬性完全依賴于主關(guān)鍵字3NF:不存在非關(guān)鍵字段對(duì)任一候選關(guān)鍵字段存在傳遞
10、函數(shù)依賴反范式違反范式設(shè)計(jì)表通過(guò)適當(dāng)增加冗余來(lái)減少多表join,降低io消耗,內(nèi)存消耗等。適用場(chǎng)景涉及多表查詢時(shí),為精簡(jiǎn)程序,可以適度冗余存在較多數(shù)據(jù)統(tǒng)計(jì)需求(count, sum等),效率低下 DB_Design_column規(guī)則越小越優(yōu),定長(zhǎng)較優(yōu),滿足應(yīng)用即可舉例越小越好 范圍合適的整形;適當(dāng)轉(zhuǎn)換字符型為整形; emun或set較小時(shí),使用tinyint替換; 避免大字段blob,varchar(2000)精確 時(shí)間類型采用精確格式,避免浪費(fèi)。存儲(chǔ)年用year;存儲(chǔ)日期用date; 存儲(chǔ)時(shí)間精確到s,使用timestamp或者int(需要轉(zhuǎn)換)。定長(zhǎng)較優(yōu)(char與varchar) var
11、char(n),nb Big SQL資源占用(filesort,group,join,subquery)大,耗時(shí)長(zhǎng)例如:select col_a from table_A where exists (select id from table_B where idxxx) order by col_b limit 10; Big Batch并發(fā)高,資源緊張例如:公司批量I/D/U一些數(shù)據(jù) DB_Oper_ refuse_3BHow to deal? Big Trasaction 拆小事務(wù)。拆分標(biāo)準(zhǔn):pk最佳,單行數(shù)據(jù)事務(wù),多行數(shù)據(jù)事務(wù)均可 Big SQL sql優(yōu)化,拆分,表拆分,加冗余,程序修
12、改等 Big Batch 拆成小批量,加間隔等,建議分1W條一次,具體和sql效率有關(guān) DB_Oper_ limit_M_Nlimit m,n如何運(yùn)行?先遍歷前m+n行數(shù)據(jù),對(duì)結(jié)果進(jìn)行排序,再讀取(m+1,m+n)區(qū)間的數(shù)據(jù) M值越大,查詢?cè)铰齋elect * from table_A limit m,n; limit m,n如何優(yōu)化?拿到第m行的id,按排序順序取后n行即可連續(xù)id:Select * from table_A where id=m limit n; 非連續(xù)id:select * from table_A where id=(select id from table_A lim
13、it m,1) limit n; select * from table_A inner join (select id from table_A limit m,n) using (id); DB_Oper_ subquerysubquery獨(dú)立子查詢Select a,b,c from table_A where d in (select e from table_B); 相關(guān)子查詢Select a,b,c from table_A where exists (select 1 from table_B where table_B.e=table_A.d) subquery的優(yōu)化避免子查詢,
14、必要時(shí)候在程序中拆分成單句執(zhí)行獨(dú)立子查詢相關(guān)子查詢 | join DB_Oper_ join Left join,right join Inner join(內(nèi)聯(lián)或等值鏈接)Select a.col1,b.col2 from table_A a Join table_B b on a.col3=b.col4 Select a.col1,b.col2 from table_A a,table_B b where a.col3=b.col4 執(zhí)行順序For each r in Rstore used column as p into join bufferfor each s in Sif s=r
15、output DB_Oper_ join如何優(yōu)化調(diào)整關(guān)聯(lián)順序,小表在前,大表在后適當(dāng)添加索引,內(nèi)表S DB_Oper_ count(*)count(*)如何進(jìn)行?將數(shù)據(jù)從外存讀入內(nèi)存,計(jì)數(shù)避免或在無(wú)訪問(wèn)的從庫(kù)進(jìn)行其他count類型的運(yùn)行 count(*),count(pk),count(unique),count(1)速度:count(*)count(1)count(unique)count(pk) DB_Oper_ other盡量使用pk或者unique key進(jìn)行update,delete操作避免is null或is not null 減少不等值查詢避免使用*,選擇需要字段進(jìn)行查詢Wher
16、e子句中,閾值較多的col放在前面如非去重,使用union all代替union 避免使用in 合理利用覆蓋索引掃描避免完全模糊匹配 OutlineSQL基礎(chǔ)什么是SQL?DDL/DML Data Type sql規(guī)范數(shù)據(jù)庫(kù)設(shè)計(jì)數(shù)據(jù)庫(kù)操作用戶權(quán)限管理用戶授權(quán)與用戶信息表安全管理用戶 DB_Oper_ privileges線上庫(kù)對(duì)程序員開放的訪問(wèn)權(quán)限只讀(S,dbname_r)、讀寫(S/I/D/U,dbname_w) 測(cè)試站點(diǎn)根據(jù)需求開啟ddl權(quán)限(create,alter) 其他用戶復(fù)制 replication slave root權(quán)限用戶 DB_Oper_ privileges相關(guān)表mys
17、ql.user 對(duì)實(shí)例的權(quán)限,包含password mysql.db 對(duì)庫(kù)的操作權(quán)限,包含db 授權(quán)&回收權(quán)限&回收用戶grant select on dbname.tablename|view to userhost identified by password;flush privileges; revoke privileges on dbname.tablename from userhost; drop user userhost; OutlineSQL基礎(chǔ)什么是SQL?DDL/DML Data Type sql規(guī)范數(shù)據(jù)庫(kù)設(shè)計(jì)數(shù)據(jù)庫(kù)操作用戶權(quán)限管理用戶授權(quán)與用戶信息表安全管理用戶 D
18、B_Oper_ securitylinux的iptables限制網(wǎng)段用戶允許訪問(wèn)的ip段限制程序用戶權(quán)限限制密碼復(fù)雜度 大小寫字母、數(shù)字、特殊字符,8位以上 DB_Oper_ securityroot用戶的初始化SET PASSWORD FOR rootlocalhost = PASSWORD(123456); SET PASSWORD FOR root127.0.0.1 = PASSWORD(123456); SET PASSWORD FOR root:1 = PASSWORD(123456); SET PASSWORD FOR roothostname = PASSWORD(123456);drop user localhost; drop user mysql-1-1; drop user rootmysql-1-1; drop database test; 丟棄root用戶