《mysql的SQL基礎(chǔ)規(guī)范與權(quán)限管理》由會員分享，可在線閱讀，更多相關(guān)《mysql的SQL基礎(chǔ)規(guī)范與權(quán)限管理（37頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。
1、
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，Proced
2、ural 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)與用戶信息表
ü安全管理用戶
DDL
?DDL，Data Definition Language，數(shù)據(jù)庫結(jié)構(gòu)定義語言
描述數(shù)據(jù)庫中要存儲的現(xiàn)實(shí)實(shí)體的語言
?常見的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ù)操縱語言
?常見的DML
select/insert/delete/update/replace
call/do/load/subquery…
?DML的權(quán)限
select/insert/delete/update
4、Other Statement
?transaction&&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 bina
5、ry 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 Type
Mysql的 Data Type
Numeric_integer
?類型
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ī)號，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以后，不再對timestamp的cunrent_timestamp類型進(jìn)行限制，默認(rèn)為null
so，選用timestamp，避免datetime；其余按需求；
9、
string
varchar&&char
varchar(n) 變長，表中n取值1
10、ü用戶授權(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)
?如何確定
─根
11、據(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_Idtc
Instance
單實(shí)例建議不要超過100G。
內(nèi)存制約。200G熱數(shù)據(jù)一般在15~20G，內(nèi)存需求18~40G
備份恢復(fù)。
具體根據(jù)業(yè)務(wù)來確定
Database
單庫不超過300-400個(gè)表。
單表字段20~50。
DB_Design_form&anti-for
12、m
?范式
─范式定義，舉例
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í)，使
13、用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)的不同
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。普通索引，小寫，可以適當(dāng)縮寫
─ udx_column1_column2_column3。唯一索引，小寫，可以適當(dāng)縮寫
?規(guī)則
─最左前綴
─組合索引上限5 column
─必
15、須明確指定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_3B
what is 3B?
─ Big Trasaction
?資源占用時(shí)間長，鎖多，日志量大，影響并發(fā)，影響數(shù)據(jù)同步
例如:update table_A set col_a=‘’ where col_b>‘b
16、’
─ 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_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ù)，對結(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 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ú)立子查詢
Select a,b,c from table_A where d in (select e from table_B)
19、;
?相關(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 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ù)
?避免或在無訪問的從庫進(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
合
22、理利用覆蓋索引掃描
避免完全模糊匹配
…
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
23、_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 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ù)庫設(shè)計(jì)
ü數(shù)據(jù)庫操作
?用戶權(quán)限管理
ü用戶授權(quán)與用戶信息表
ü安全管理用戶
DB_Oper_ security
?linux的iptables限制網(wǎng)段
?用戶允許訪問的ip段限制
?程序用戶權(quán)限限制
?密碼復(fù)雜度 大小寫字母、數(shù)字、特殊字符，8位以上
DB_Oper_ security
?root用戶的初始化
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用戶
mysql的SQL基礎(chǔ)規(guī)范與權(quán)限管理
