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

《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)限管理 ——雷徹 Outline ?SQL基礎(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，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ù)操作 ?用戶權(quán)限管理 ü用戶授權(quán)與用戶信息表 ü安全管理用戶 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ù)操作 ?用戶權(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ī)號(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類型進(jìn)行限制，默認(rèn)為null so，選用timestamp，避免datetime；其余按需求；

9、 string varchar&&char varchar(n) 變長(zhǎng)，表中n取值1

10、ü用戶授權(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) ?如何確定 ─根

11、據(jù)數(shù)據(jù)量，增長(zhǎng)量來(lái)確定磁盤大小 ─根據(jù)增長(zhǎng)速度，訪問(wè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í)體的屬性完全依賴于主關(guān)鍵字 3NF：不存在非關(guān)鍵字段對(duì)任一候選關(guān)鍵字段存在傳遞函數(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í)，使

13、用tinyint替換； 避免大字段blob,varchar(2000)… ─精確 時(shí)間類型采用精確格式，避免浪費(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。普通索引，小寫，可以適當(dāng)縮寫 ─ udx_column1_column2_column3。唯一索引，小寫，可以適當(dāng)縮寫 ?規(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ù)操作 ?用戶權(quán)限管理 ü用戶授權(quán)與用戶信息表 ü安全管理用戶 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值越大，查詢?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ú)立子查詢 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ù) ?避免或在無(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)行查詢 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ù)操作 ?用戶權(quán)限管理 ü用戶授權(quán)與用戶信息表 ü安全管理用戶 DB_Oper_ privileges ?線上庫(kù)對(duì)程序員開(kāi)放的訪問(wèn)權(quán)限 ─只讀(S，dbname_r)、讀寫(S/I/D/U，dbname_w) ?測(cè)試站點(diǎn) ─根據(jù)需求開(kāi)啟ddl權(quán)限(create,alter) ?其他用戶 ─復(fù)制 replication slave ─ root權(quán)限用戶 DB

23、_Oper_ privileges ?相關(guān)表 mysql.user 對(duì)實(shí)例的權(quán)限，包含password mysql.db 對(duì)庫(kù)的操作權(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ù)庫(kù)設(shè)計(jì) ü數(shù)據(jù)庫(kù)操作 ?用戶權(quán)限管理 ü用戶授權(quán)與用戶信息表 ü安全管理用戶 DB_Oper_ security ?linux的iptables限制網(wǎng)段 ?用戶允許訪問(wèn)的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用戶