mysql的SQL基礎(chǔ)規(guī)范與權(quán)限管理
《mysql的SQL基礎(chǔ)規(guī)范與權(quán)限管理》由會(huì)員分享,可在線(xiàn)閱讀,更多相關(guān)《mysql的SQL基礎(chǔ)規(guī)范與權(quán)限管理(37頁(yè)珍藏版)》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。
1、 SQL基礎(chǔ)、規(guī)范、用戶(hù)權(quán)限管理 ——雷徹 Outline ?SQL基礎(chǔ) ü什么是SQL? üDDL/DML üData Type ?sql規(guī)范 ü數(shù)據(jù)庫(kù)設(shè)計(jì) ü數(shù)據(jù)庫(kù)操作 ?用戶(hù)權(quán)限管理 ü用戶(hù)授權(quán)與用戶(hù)信息表 ü安全管理用戶(hù) 注意:本ppt涉及內(nèi)容僅適合mysql,默認(rèn)Innodb引擎 本ppt所述僅代表個(gè)人觀(guān)點(diǎn) What is SQL? SQL,Structure Query Language,結(jié)構(gòu)化查詢(xún)語(yǔ)言 T-SQL,Transact – SQL,MSSql對(duì)sql標(biāo)準(zhǔn)的增強(qiáng) PL/SQL,Proced
2、ural Language SQL, Oracle對(duì)SQL的擴(kuò)展 Outline ?SQL基礎(chǔ) ü什么是SQL? üDDL/DML üData Type ?sql規(guī)范 ü數(shù)據(jù)庫(kù)設(shè)計(jì) ü數(shù)據(jù)庫(kù)操作 ?用戶(hù)權(quán)限管理 ü用戶(hù)授權(quán)與用戶(hù)信息表 ü安全管理用戶(hù) DDL ?DDL,Data Definition Language,數(shù)據(jù)庫(kù)結(jié)構(gòu)定義語(yǔ)言 描述數(shù)據(jù)庫(kù)中要存儲(chǔ)的現(xiàn)實(shí)實(shí)體的語(yǔ)言 ?常見(jiàn)的DDL create/alter/drop database … create/alter/drop/truncate/rename table … create/
3、alter/drop view … create/drop index … 還有trigger,event,procedure,logfile,tablespace,function ?DDL需要的權(quán)限 create,drop,alter,event,trigger… DML ?DML,Data Manipulation Language,數(shù)據(jù)操縱語(yǔ)言 ?常見(jiàn)的DML select/insert/delete/update/replace call/do/load/subquery… ?DML的權(quán)限 select/insert/delete/update
4、Other Statement ?transaction&&lock statement ─常見(jiàn)SQL: begin/start [read only] transaction,commit,rollback,XA transaction… lock/unlock ─權(quán)限:lock ?replication statement ─常見(jiàn)SQL: change master to…,start/stop slave,start/stop [I/O|SQL] thread set sql_log_bin…,reset master,purge bina
5、ry logs… ─權(quán)限:repelication ?administation statement ─常見(jiàn)SQL: grant/revoke/drop/alter user,set,checksum/anlyse/repair table… … Outline ?SQL基礎(chǔ) ü什么是SQL? üDDL/DML üData Type ?sql規(guī)范 ü數(shù)據(jù)庫(kù)設(shè)計(jì) ü數(shù)據(jù)庫(kù)操作 ?用戶(hù)權(quán)限管理 ü用戶(hù)授權(quán)與用戶(hù)信息表 ü安全管理用戶(hù) Data Type Mysql的 Data Type Numeric_integer ?類(lèi)型 Typ
6、e Storage (Bytes) Minimum Value Maximum Value (Signed/Unsigned) (Signed/Unsigned) TINYINT 1-2^7 2^7 0 2^8 SMALLINT 2-2^15 0 2^15 2^16 2^23 MEDIUMINT 3-2^23 0 2^24 2^31 2^32 2^63 2^64 INT 4-2^31 0 BIGINT 8-2^63 0 ?哪些字段可用? IP,0~4244897025,unsigned int而不用char(15),函數(shù)inet
7、_aton(),inet_ntoa() 手機(jī)號(hào),unsigned bigint而不用char(11) Numeric_decimal ?fixed-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&&Time ?date&&time ─ datetime 格式,取值范圍1000-01-0
8、1 00:00:00~9999-12-31 23:59:59, 8B (5.6.4以前) ─ date 格式,YYYY-MM-DD,3B ─ time 格式,hh:mm:ss,3B ─ timestamp 格式,函數(shù)轉(zhuǎn)換,范圍1970~2037年,4B ─ year 格式,YYYY,1B ?變化 5.6.4以后,不再對(duì)timestamp的cunrent_timestamp類(lèi)型進(jìn)行限制,默認(rèn)為null so,選用timestamp,避免datetime;其余按需求;
9、
string
varchar&&char
varchar(n) 變長(zhǎng),表中n取值1 10、ü用戶(hù)授權(quán)與用戶(hù)信息表
ü安全管理用戶(hù)
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ù),訪(fǎng)問(wèn)量,庫(kù)表大小,數(shù)量,熱 庫(kù)
熱表,讀寫(xiě)比例等(兩年內(nèi))
─確定存儲(chǔ)(type,size),內(nèi)存,架構(gòu),帶寬,備份,數(shù)據(jù)物理分布等等
─減少后期維護(hù)成本,避免資源浪費(fèi)
?如何確定
─根 11、據(jù)數(shù)據(jù)量,增長(zhǎng)量來(lái)確定磁盤(pán)大小
─根據(jù)增長(zhǎng)速度,訪(fǎng)問(wèn)量,讀寫(xiě)比例來(lái)確定磁盤(pán)類(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_Idtc
Instance
單實(shí)例建議不要超過(guò)100G。
內(nèi)存制約。200G熱數(shù)據(jù)一般在15~20G,內(nèi)存需求18~40G
備份恢復(fù)。
具體根據(jù)業(yè)務(wù)來(lái)確定
Database
單庫(kù)不超過(guò)300-400個(gè)表。
單表字段20~50。
DB_Design_form&anti-for 12、m
?范式
─范式定義,舉例
1NF:字段屬性單一,不可再分
2NF:實(shí)體的屬性完全依賴(lài)于主關(guān)鍵字
3NF:不存在非關(guān)鍵字段對(duì)任一候選關(guān)鍵字段存在傳遞函數(shù)依賴(lài)
?反范式
─違反范式設(shè)計(jì)表
─通過(guò)適當(dāng)增加冗余來(lái)減少多表join,降低io消耗,內(nèi)存消耗等。
─適用場(chǎng)景
?涉及多表查詢(xún)時(shí),為精簡(jiǎn)程序,可以適度冗余
?存在較多數(shù)據(jù)統(tǒng)計(jì)需求(count, sum等),效率低下
DB_Design_column
?規(guī)則
─越小越優(yōu),定長(zhǎng)較優(yōu),滿(mǎn)足應(yīng)用即可
?舉例
─越小越好
范圍合適的整形;適當(dāng)轉(zhuǎn)換字符型為整形;
emun或set較小時(shí),使 13、用tinyint替換;
避免大字段blob,varchar(2000)…
─精確
時(shí)間類(lèi)型采用精確格式,避免浪費(fèi)。存儲(chǔ)年用year;存儲(chǔ)日期用date;
存儲(chǔ)時(shí)間精確到s,使用timestamp或者int(需要轉(zhuǎn)換)。
─定長(zhǎng)較優(yōu)(char與varchar)
varchar(n),n<255時(shí),不妨用char代替(快);
長(zhǎng)度一致時(shí),流水號(hào)采用char;
DB_Design_column
?使用decimal,避免使用double和float
?varchar(255)與varchar(256)的不同 14、;
?大字段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。普通索引,小寫(xiě),可以適當(dāng)縮寫(xiě)
─ udx_column1_column2_column3。唯一索引,小寫(xiě),可以適當(dāng)縮寫(xiě)
?規(guī)則
─最左前綴
─組合索引上限5 column
─必 15、須明確指定pk
─長(zhǎng)于50的varchar字段使用合適的前綴索引
─選哪些字段?順序如何?
根據(jù)業(yè)務(wù),sql來(lái)定
Outline
?SQL基礎(chǔ)
ü什么是SQL?
üDDL/DML
üData Type
?sql規(guī)范
ü數(shù)據(jù)庫(kù)設(shè)計(jì)
ü數(shù)據(jù)庫(kù)操作
?用戶(hù)權(quán)限管理
ü用戶(hù)授權(quán)與用戶(hù)信息表
ü安全管理用戶(hù)
DB_Oper_ refuse_3B
what is 3B?
─ Big Trasaction
?資源占用時(shí)間長(zhǎng),鎖多,日志量大,影響并發(fā),影響數(shù)據(jù)同步
例如:update table_A set col_a=‘’ where col_b>‘b 16、’
─ Big SQL
?資源占用(filesort,group,join,subquery)大,耗時(shí)長(zhǎng)
例如: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_3B
How to deal?
─ Big Trasaction
─拆小事務(wù)。拆分標(biāo)準(zhǔn):pk最佳 17、,單行數(shù)據(jù)事務(wù),多行數(shù)據(jù)事務(wù)均可
─ Big SQL
─ sql優(yōu)化,拆分,表拆分,加冗余,程序修改等
─ Big Batch
─拆成小批量,加間隔等,建議分1W條一次,具體和sql效率有關(guān)
DB_Oper_ limit_M_N
?limit m,n如何運(yùn)行?
?先遍歷前m+n行數(shù)據(jù),對(duì)結(jié)果進(jìn)行排序,再讀取(m+1,m+n)區(qū)間的數(shù)據(jù)
? M值越大,查詢(xún)?cè)铰?
Select * from table_A limit m,n;
?limit m,n如何優(yōu)化?
?拿到第m行的id,按排序順序取后n行即可
連續(xù)id:Select * from table_A w 18、here 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_ subquery
?subquery
?獨(dú)立子查詢(xún)
Select a,b,c from table_A where d in (select e from table_B) 19、;
?相關(guān)子查詢(xú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)化
?避免子查詢(xún),必要時(shí)候在程序中拆分成單句執(zhí)行
?獨(dú)立子查詢(xún)à相關(guān)子查詢(xú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 o 20、n 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 R
store used column as p into join buffer
for each s in S
if s=r
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ù)從外存 21、讀入內(nèi)存,計(jì)數(shù)
?避免或在無(wú)訪(fǎng)問(wèn)的從庫(kù)進(jìn)行
?其他count類(lèi)型的運(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
減少不等值查詢(xún)
避免使用*,選擇需要字段進(jìn)行查詢(xún)
Where子句中,閾值較多的col放在前面
如非去重,使用union all代替union
避免使用in
合 22、理利用覆蓋索引掃描
避免完全模糊匹配
…
Outline
?SQL基礎(chǔ)
ü什么是SQL?
üDDL/DML
üData Type
?sql規(guī)范
ü數(shù)據(jù)庫(kù)設(shè)計(jì)
ü數(shù)據(jù)庫(kù)操作
?用戶(hù)權(quán)限管理
ü用戶(hù)授權(quán)與用戶(hù)信息表
ü安全管理用戶(hù)
DB_Oper_ privileges
?線(xiàn)上庫(kù)對(duì)程序員開(kāi)放的訪(fǎng)問(wèn)權(quán)限
─只讀(S,dbname_r)、讀寫(xiě)(S/I/D/U,dbname_w)
?測(cè)試站點(diǎn)
─根據(jù)需求開(kāi)啟ddl權(quán)限(create,alter)
?其他用戶(hù)
─復(fù)制 replication slave
─ root權(quán)限用戶(hù)
DB 23、_Oper_ privileges
?相關(guān)表
mysql.user 對(duì)實(shí)例的權(quán)限,包含password
mysql.db 對(duì)庫(kù)的操作權(quán)限,包含db
?授權(quán)&&回收權(quán)限&&回收用戶(hù)
grant select on dbname.[tablename|view] to user@’host’ identified by ‘password’;
flush privileges;
revoke privileges on dbname.[tablename] from user@’host’;
drop user user@’host’;
Outline
?SQL基 24、礎(chǔ)
ü什么是SQL?
üDDL/DML
üData Type
?sql規(guī)范
ü數(shù)據(jù)庫(kù)設(shè)計(jì)
ü數(shù)據(jù)庫(kù)操作
?用戶(hù)權(quán)限管理
ü用戶(hù)授權(quán)與用戶(hù)信息表
ü安全管理用戶(hù)
DB_Oper_ security
?linux的iptables限制網(wǎng)段
?用戶(hù)允許訪(fǎng)問(wèn)的ip段限制
?程序用戶(hù)權(quán)限限制
?密碼復(fù)雜度 大小寫(xiě)字母、數(shù)字、特殊字符,8位以上
DB_Oper_ security
?root用戶(hù)的初始化
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用戶(hù)
- 溫馨提示:
1: 本站所有資源如無(wú)特殊說(shuō)明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
2: 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶(hù)所有。
3.本站RAR壓縮包中若帶圖紙,網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒(méi)有圖紙預(yù)覽就沒(méi)有圖紙。
4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
5. 裝配圖網(wǎng)僅提供信息存儲(chǔ)空間,僅對(duì)用戶(hù)上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對(duì)用戶(hù)上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請(qǐng)與我們聯(lián)系,我們立即糾正。
7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶(hù)因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。
最新文檔
- 2023年六年級(jí)數(shù)學(xué)下冊(cè)6整理和復(fù)習(xí)2圖形與幾何第7課時(shí)圖形的位置練習(xí)課件新人教版
- 2023年六年級(jí)數(shù)學(xué)下冊(cè)6整理和復(fù)習(xí)2圖形與幾何第1課時(shí)圖形的認(rèn)識(shí)與測(cè)量1平面圖形的認(rèn)識(shí)練習(xí)課件新人教版
- 2023年六年級(jí)數(shù)學(xué)下冊(cè)6整理和復(fù)習(xí)1數(shù)與代數(shù)第10課時(shí)比和比例2作業(yè)課件新人教版
- 2023年六年級(jí)數(shù)學(xué)下冊(cè)4比例1比例的意義和基本性質(zhì)第3課時(shí)解比例練習(xí)課件新人教版
- 2023年六年級(jí)數(shù)學(xué)下冊(cè)3圓柱與圓錐1圓柱第7課時(shí)圓柱的體積3作業(yè)課件新人教版
- 2023年六年級(jí)數(shù)學(xué)下冊(cè)3圓柱與圓錐1圓柱第1節(jié)圓柱的認(rèn)識(shí)作業(yè)課件新人教版
- 2023年六年級(jí)數(shù)學(xué)下冊(cè)2百分?jǐn)?shù)(二)第1節(jié)折扣和成數(shù)作業(yè)課件新人教版
- 2023年六年級(jí)數(shù)學(xué)下冊(cè)1負(fù)數(shù)第1課時(shí)負(fù)數(shù)的初步認(rèn)識(shí)作業(yè)課件新人教版
- 2023年六年級(jí)數(shù)學(xué)上冊(cè)期末復(fù)習(xí)考前模擬期末模擬訓(xùn)練二作業(yè)課件蘇教版
- 2023年六年級(jí)數(shù)學(xué)上冊(cè)期末豐收?qǐng)@作業(yè)課件蘇教版
- 2023年六年級(jí)數(shù)學(xué)上冊(cè)易錯(cuò)清單十二課件新人教版
- 標(biāo)準(zhǔn)工時(shí)講義
- 2021年一年級(jí)語(yǔ)文上冊(cè)第六單元知識(shí)要點(diǎn)習(xí)題課件新人教版
- 2022春一年級(jí)語(yǔ)文下冊(cè)課文5識(shí)字測(cè)評(píng)習(xí)題課件新人教版
- 2023年六年級(jí)數(shù)學(xué)下冊(cè)6整理和復(fù)習(xí)4數(shù)學(xué)思考第1課時(shí)數(shù)學(xué)思考1練習(xí)課件新人教版