mysql的SQL基礎(chǔ)規(guī)范與權(quán)限管理

SQL基礎(chǔ)、規(guī)范、用戶權(quán)限管理雷徹 OutlineØSQL基礎(chǔ)ü什么是SQL？üDDL/DML üData Type Øsql規(guī)范ü數(shù)據(jù)庫設(shè)計(jì)ü數(shù)據(jù)庫操作Ø用戶權(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)化查詢語言T-SQL，Transact SQL，MSSql對sql標(biāo)準(zhǔn)的增強(qiáng)PL/SQL，Procedural Language SQL， Oracle對SQL的擴(kuò)展 OutlineØSQL基礎(chǔ)ü什么是SQL？üDDL/DML üData Type Øsql規(guī)范ü數(shù)據(jù)庫設(shè)計(jì)ü數(shù)據(jù)庫操作Ø用戶權(quán)限管理ü用戶授權(quán)與用戶信息表ü安全管理用戶 DDLDDL，Data Definition Language，數(shù)據(jù)庫結(jié)構(gòu)定義語言描述數(shù)據(jù)庫中要存儲的現(xiàn)實(shí)實(shí)體的語言常見的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,trigger DMLDML，Data Manipulation Language，數(shù)據(jù)操縱語言常見的DMLselect/insert/delete/update/replacecall/do/load/subqueryDML的權(quán)限select/insert/delete/update Other Statementtransaction&&lock statement常見SQL： begin/start read only transaction,commit,rollback,XA transaction lock/unlock 權(quán)限：lock replication statement常見SQL： 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常見SQL：grant/revoke/drop/alter user，set，checksum/anlyse/repair table OutlineØSQL基礎(chǔ)ü什么是SQL？üDDL/DML üData Type Øsql規(guī)范ü數(shù)據(jù)庫設(shè)計(jì)ü數(shù)據(jù)庫操作Ø用戶權(quán)限管理ü用戶授權(quán)與用戶信息表ü安全管理用戶 Data TypeMysql的 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ī)號，unsigned bigint而不用char(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)換，范圍19702037年，4B year 格式，YYYY，1B 變化5.6.4以后，不再對timestamp的cunrent_timestamp類型進(jìn)行限制，默認(rèn)為null so，選用timestamp，避免datetime；其余按需求； stringvarchar&&charvarchar(n) 變長，表中n取值1<n<(65535-1,2)/字符集需要12個(gè)節(jié)來存儲長度char(n) 定長存儲長度相差不大，頻繁變化的的類型用char varchar(BN) && blob && text varchar(BN) blob二進(jìn)制，不可排序 text文本類型，可排序，選擇前綴排序 OutlineØSQL基礎(chǔ)ü什么是SQL？üDDL/DML üData Type Øsql規(guī)范ü數(shù)據(jù)庫設(shè)計(jì)ü數(shù)據(jù)庫操作Ø用戶權(quán)限管理ü用戶授權(quán)與用戶信息表ü安全管理用戶 SQL Stantard數(shù)據(jù)庫設(shè)計(jì)預(yù)估范式與反范式字段規(guī)范索引規(guī)范其他注意點(diǎn)數(shù)據(jù)庫操作拒絕3B 分頁limit M,N subquery join 避免count(*) 其他 DB_Design_estimate目的預(yù)估數(shù)據(jù)量，增長速度，熱數(shù)據(jù)，訪問量，庫表大小，數(shù)量，熱 庫熱表，讀寫比例等（兩年內(nèi)）確定存儲(type,size)，內(nèi)存，架構(gòu)，帶寬，備份，數(shù)據(jù)物理分布等等減少后期維護(hù)成本，避免資源浪費(fèi)如何確定根據(jù)數(shù)據(jù)量，增長量來確定磁盤大小根據(jù)增長速度，訪問量，讀寫比例來確定磁盤類型，raid，文件系統(tǒng)，架構(gòu)等根據(jù)熱數(shù)據(jù)熱庫熱表業(yè)務(wù)來確定內(nèi)存大小，配置參數(shù)，數(shù)據(jù)分布根據(jù)數(shù)據(jù)重要性，量，來確定容災(zāi)及備份策略本ppt暫不討論硬件和配置問題。 DB_Design_estimate_IdtcInstance單實(shí)例建議不要超過100G。內(nèi)存制約。200G熱數(shù)據(jù)一般在1520G，內(nèi)存需求1840G 備份恢復(fù)。具體根據(jù)業(yè)務(wù)來確定Database單庫不超過300-400個(gè)表。單表字段2050。 DB_Design_form&anti-form范式范式定義，舉例1NF：字段屬性單一，不可再分2NF：實(shí)體的屬性完全依賴于主關(guān)鍵字3NF：不存在非關(guān)鍵字段對任一候選關(guān)鍵字段存在傳遞函數(shù)依賴反范式違反范式設(shè)計(jì)表通過適當(dāng)增加冗余來減少多表join，降低io消耗，內(nèi)存消耗等。適用場景涉及多表查詢時(shí)，為精簡程序，可以適度冗余存在較多數(shù)據(jù)統(tǒng)計(jì)需求（count, sum等），效率低下 DB_Design_column規(guī)則越小越優(yōu)，定長較優(yōu)，滿足應(yīng)用即可舉例越小越好 范圍合適的整形；適當(dāng)轉(zhuǎn)換字符型為整形； emun或set較小時(shí)，使用tinyint替換； 避免大字段blob,varchar(2000)精確 時(shí)間類型采用精確格式，避免浪費(fèi)。存儲年用year；存儲日期用date； 存儲時(shí)間精確到s，使用timestamp或者int(需要轉(zhuǎn)換)。定長較優(yōu)(char與varchar) varchar(n),n<255時(shí)，不妨用char代替(快)； 長度一致時(shí)，流水號采用char； DB_Design_column使用decimal，避免使用double和float varchar(255)與varchar(256)的不同；大字段varchar(5000)與blob放在單表中；自增列使用int或者bigint，標(biāo)明unsigned；避免null，字段都必須為not null default xxx避免uuid；int和int(8),int(11)有區(qū)別嗎？設(shè)置comment； DB_Design_Index索引 idx_column1_column2_column3。普通索引，小寫，可以適當(dāng)縮寫 udx_column1_column2_column3。唯一索引，小寫，可以適當(dāng)縮寫規(guī)則最左前綴組合索引上限5 column 必須明確指定pk 長于50的varchar字段使用合適的前綴索引選哪些字段？順序如何？根據(jù)業(yè)務(wù),sql來定 OutlineØSQL基礎(chǔ)ü什么是SQL？üDDL/DML üData Type Øsql規(guī)范ü數(shù)據(jù)庫設(shè)計(jì)ü數(shù)據(jù)庫操作Ø用戶權(quán)限管理ü用戶授權(quán)與用戶信息表ü安全管理用戶 DB_Oper_ refuse_3Bwhat is 3B? Big Trasaction資源占用時(shí)間長，鎖多，日志量大，影響并發(fā)，影響數(shù)據(jù)同步例如:update table_A set col_a= where col_b>b Big SQL資源占用(filesort，group，join，subquery)大，耗時(shí)長例如:select col_a from table_A where exists (select id from table_B where id>xxx) 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)化，拆分，表拆分，加冗余，程序修改等 Big Batch 拆成小批量，加間隔等，建議分1W條一次，具體和sql效率有關(guān) DB_Oper_ limit_M_Nlimit m,n如何運(yùn)行？先遍歷前m+n行數(shù)據(jù)，對結(jié)果進(jìn)行排序，再讀取(m+1,m+n)區(qū)間的數(shù)據(jù) M值越大，查詢越慢Select * 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 limit 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)化避免子查詢，必要時(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=routput <p,s> 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ù)避免或在無訪問的從庫進(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)行查詢Where子句中,閾值較多的col放在前面如非去重,使用union all代替union 避免使用in 合理利用覆蓋索引掃描避免完全模糊匹配 OutlineØSQL基礎(chǔ)ü什么是SQL？üDDL/DML üData Type Øsql規(guī)范ü數(shù)據(jù)庫設(shè)計(jì)ü數(shù)據(jù)庫操作Ø用戶權(quán)限管理ü用戶授權(quán)與用戶信息表ü安全管理用戶 DB_Oper_ privileges線上庫對程序員開放的訪問權(quán)限只讀(S，dbname_r)、讀寫(S/I/D/U，dbname_w) 測試站點(diǎn)根據(jù)需求開啟ddl權(quán)限(create,alter) 其他用戶復(fù)制 replication slave root權(quán)限用戶 DB_Oper_ privileges相關(guān)表mysql.user 對實(shí)例的權(quán)限，包含password mysql.db 對庫的操作權(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; OutlineØSQL基礎(chǔ)ü什么是SQL？üDDL/DML üData Type Øsql規(guī)范ü數(shù)據(jù)庫設(shè)計(jì)ü數(shù)據(jù)庫操作Ø用戶權(quán)限管理ü用戶授權(quán)與用戶信息表ü安全管理用戶 DB_Oper_ securitylinux的iptables限制網(wǎng)段用戶允許訪問的ip段限制程序用戶權(quán)限限制密碼復(fù)雜度 大小寫字母、數(shù)字、特殊字符，8位以上 DB_Oper_ securityroot用戶的初始化SET PASSWORD FOR 'root''localhost' = PASSWORD('123456'); SET PASSWORD FOR 'root''127.0.0.1' = PASSWORD('123456'); SET PASSWORD FOR 'root'':1' = PASSWORD('123456'); SET PASSWORD FOR 'root'hostname' = PASSWORD('123456');drop user '''localhost' drop user '''mysql-1-1' drop user 'root''mysql-1-1' drop database test; 丟棄root用戶