mysql的SQL基础规范与权限管理

SQL基础、规范、用户权限管理

雷彻

Outline

ASQL基础

/什么是SQL?

/DDL/DML

/DataType

Asql规范

/数据库设计

/数据库操作

»用户权限管理

,用户授权与用户信息表

/安全管理用户

注意：本ppt涉及内容仅适合mysqL默认Innodb引擎

本ppt所述仅代表个人观点

WhatisSQL?

SQL,StructureQueryLanguage,结构化查询语言

T-SQL,Transact-SQL,MSSql对sql标准的增强

PL/SQL,ProceduralLanguageSQL,Oracle对SQL的扩展

DDL

•DDL,DataDefinitionLanguage,数据库结构定义语言

描述数据库中要存储的现实实体的语言

•常见的DDL

create/alter/dropdatabase...

create/alter/drop/truncate/renametable...

create/alter/dropview...

create/dropindex...

3SWtrigger,event,procedure,logfile,tablespace,function

•DDL需要的权限

create,drop,alter,event,trigger...

DML

•DML,DataManipulationLanguage,数据操纵语言

•常见的DML

select/insert/delete/update/replace

call/do/load/subquery...

•DML的权限

select/insert/delete/update

OtherStatement

•transaction&&lockstatement

-常见SQL：

begin/start[readonly]transaction,commit,rollback,XAtransaction...

lock/unlock

-权限：lock

•replicationstatement

-常见SQL：

changemasterto...,start/stopslave,start/stop[I/O|SQL]thread

setsql_log_bin...,resetmaster,purgebinarylogs...

一权限：repelication

•administationstatement

-常见SQL：

grant/revoke/drop/alteruser,set,checksum/anlyse/repairtable...

Outline

ASQL基砒1

/什么是SQL?

♦DDL/DML

/DataType

Asql规范

/数据库设计

/数据库操作

A用户权限管理

/用户授权与用户信息表

/安全管理用户

DataType

Mysql的DataType

year

timestamp

tinyintH期时间型time

smallintdate

mediumint整数型datetime

-------------------

int-

bigint数值型丁字符小型

浮点

Float

Double小数型

decimal定点

Numericjnteger

•类型

TypeStorageMinimumValueMaximumValue

(Bytes)(Signed/Unsigned)(Signed/Unsigned)

TINYINT-272A7

02A8

SMALLINT2-2752A15

02A16

MEDIUMINT3-2*232A23

02A24

INT4-2*312A31

02A32

BIGINT8-2A632A63

02A64

■哪些字段可用？

IP,0^4244897025,unsignedini而不用chd”5),函数inel_alon(),inel_nloa()

手机号，unsignedbigint而不用char(ll)

Numericdecimal

•fixed-point

-decimal(M,D)

-整数最大位数(M)为65,小数位数最大(D)为30

•floating-point

-float(p)p<=24,4B；25<=p<=53,8B;

-double8B

建议使用decimal,避免float和double

Date&&Time

•date&&time

-datetime格式，取值范围1000-01-0100:00:00-9999-12-3123:59:59,

8B（5.6.4以前）

-date格式，YYYY-MM-DD,3B

-time格式，hh:mm:ss,3B

-timestamp格式，函数转换，范围1970〜2037年，4B

-year格式，YYYY,1B

•变化

5.6.4以后，不再对timestamp的cunrent_timestamp类型进行限制，默认为null

so,选用timestamp,避免datetime；其余按需求;

string

varchar&&char

varchar(n)变长，表中n取值l<nv(65535-[1,2])/字符集需要1〜2个节来存储长度

char(n)定长

存储长度相差不大，频繁变化的的类型用char

varchar(BN)&&blob&&text

-varchar(BN)

—blob二进制，不可排序

-text文本类型，可排序，选择前缀排序

Outline

ASQL基础

/什么是SQL?

/DDL/DML

/DataType

Asql规范

/数据库设计

/数据库操作

»用户权限管理

/用户授权与用户信息表

/安全管理用户

SQLStantard

•数据库设计

-预估

-范式与反范式

-字段规范

-索引规范

-其他注意点

•数据库操作

关绝3B

一分页limitM,N

-subquery

-join

一避免count(*)

-其他

DB_Designjestimate

•目的

-预估数据量，增长速度，热数据，访问量，库表大小，数量，热库

热表，读写比例等(两年内)

-确定存储(type,size),内存，架构，带宽，备份，数据物理分布等等

-减少后期维护成本，避免资源浪费

•如何确定

-根据数据量，增长量来确定磁盘大小

-根据增长速度，访问量，读写比例来确定磁盘类型，raid,文件系统，架构等

-根据热数据热库热表业务来确定内存大小，配置参数，数据分布

-根据数据重要性，量，来确定容灾及备份策略

本ppt暂不讨论硬件和配置问题。

DB_Designjestimate_ldtc

Instance

单实例建议不要超过100G。

内存制约。200G热数据一般在15~20G,内存需求18~40G

备份恢复。

具体根据业务来确定

Database

单库不超过300-400个表。

单表字段20~50。

DB_Design_form&anti-form

•范式

-范式定义，举例

1NF：字段属性单一，不可再分

2NF：实体的属性完全依赖于主关键字

3NF：不存在非关键字段对任一候选关键字段存在传递函数依赖

•反范式

-违反范式设计表

-通过适当增加冗余来减少多表join,降低io消耗，内存消耗等。

-适用场景

•涉及多表查询时，为精简程序，可以适度冗余

•存在较多数据统计需求（count,sum等），效率低下

DB_Designjcolumn

•规则

-越小越优，定长较优，满足应用即可

•举例

-越小越好

范围合适的整形；适当转换字符型为整形；

emun或set较小时，使用tinyint替换；

避免大字段blob,varchar(2000)

-精确

时间类型采用精确格式，避免浪费。存储年用year；存储日期用date；

存储时间精确到s,使用timestamp或者int(需要转换)。

一定长较优(char与varchar)

varchar(n),n<255时,不妨用char代替(快)；

长度一致时，流水号采用char；

DB_Designjcolumn

•使用decimal,避免使用double和float

•varchar(255)与varchar(256)的不同；

•大字段varchar(5000)与blob放在单表中；

•自增列使用汨七或者匕也冷七标明unsigned：

•避免null,字段都必须为notnulldefault'xxx'

•避免uuid；

•int和int(8),int(ll)有区另ij吗？

•设置comment；

DB_Design_lndex

嗦引

-idx_columnl_column2_column3o普通索引，小写，可以适当缩写

-udx_columnl_column2_column3o唯一索引,小写,可以适当缩写

•规则

-最左前缀

-组合索引上限5column

-必须明确指定pk

-长于50的varchar字段使用合适的前缀索引

-选哪些字段？顺序如何？

根据业务,sql来定

Outline

ASQL基础

/什么是SQL?

/DDL/DML

/DataType

Asql规范

/数据库设计

/数据库操作

»用户权限管理

/用户授权与用户信息表

/安全管理用户

DB_Oper_refuse_3B

whatis3B?

-BigTrasaction

•资源占用时间长，锁多，日志量大，影响并发，影响数据同步

例如:updatetable_Asetcol_a=°wherecol_b><b>

-BigSQL

•资源占用(filesort,group,join,subquery)大，耗时长

例如:selectcol_afromtable_A

whereexists(selectidfromtable_Bwhereid>,xxx,)

orderbycol_blimit10;

-BigBatch

•并发高，资源紧张

例如:公司批量I/D/U一些数据

DB_Oper_refuse_3B

Howtodeal?

-BigTrasaction

-拆小事务。拆分标准：pk最佳，单行数据事务，多行数据事务均可

-BigSQL

-sql优化，拆分，表拆分，加冗余，程序修改等

-BigBatch

-拆成小批量，加间隔等，建议分1W条一次，具体和sql效率有关

DB_Oper_

•limitm,n如何运行？

-先遍历前m+n行数据，对结果进行排序，再读取(m+l,m+n)区间的数据

-M值越大，查询越慢

Select*fromtable_Alimitm,n;

•limitm,n如何优化？

-拿到第m行的id,按排序顺序取后n行即可

连续id：Select*fromtable_Awhereid>=mlimitn;

非连续id：select*fromtablc_A

whereid>=(selectidfromtable_Alimitm,l)limitn;

select*fromtable_Ainnerjoin(selectidfromtable_Alimitm,n)using(id);

DB_Oper_subquery

•subquery

-独立子查询

Selecta,b,cfromtable_Awheredin(selectefromtable_B);

-相关子查询

Selecta,b,cfromtable_A

whereexists(select1fromtable_Bwheretable_B.e=table_A.d)

•subquery的优化

-避免子查询，必要时候在程序中拆分成单句执行

-独立子查询少相关子查询11join

DB_Oper_join

•Innerjoin（内联或等值链接）

Selecta.coll,b.col2

fromtable_Aa

Jointable_Bbona.col3=b.col4

Selecta.coll,b.col2

fromtable_Aa,table_Bb

wherea.col3=b.col4

•执行顺序

ForeachrinR

storeusedcolumnaspintojoinbuffer

foreachsinS

ifs=r

output<p,s>

DB_Oper_join

•如何优化

-调整关联顺序，小表在前，大表在后

-适当添加索引，内表s

DB_Oper_count(^)

•count(*)如何进行?

-将数据从外存读入内存，计数

-避免或在无访问的从库进行

•其他count类型的运行

-count(*),count(pk),count(unique),count(l)

一速度：count(*)~count(l)>count(unique)-count(pk)

DB_Oper_other

尽量使用pk或者uniquekey进行update,delete操作

避免isnull或isnotnull

减少不等值查询

避免使用*，选择需要字段进行查询

Wh6r6子句中，阈值较多的col放在前面

如非去重，使用unionall代替union

避免使用in

合理利用覆盖索引扫描

避免完全模糊匹配

Outline

ASQL基础

/什么是SQL?

/DDL/DML

/DataType

Asql规范

/数据库设计

/数据库操作

A用户权限管理

/用户授权与用户信息表

/安全管理用户

DB_Oper_privileges

•线上库对程序员开放的访问权限

一只读(S,dbname_r),读写(S/I/D/U,dbname_w)

・测试站点

-根据需求开启ddl权限(create,alter)

•其他用户

一复制replicationslave

-root权限用户

DB_Oper_privileges

•相关表

mysql.user对实例的权限，包含password

mysql.db对库的操作权限，包含db

•授权&&回收权限&&回收用户

grantselectondbname.[tablename|view]touser@'host'identifiedby'password*;

flushprivileges;

revokeprivilegesondbname.[tablename]fromuser@'host';

dropuseruser@'host';

Outline

ASQL基础

/什么是SQL?

/DDL/DML

/DataType

Asql规范

/数据库设计

/数据库操作

A用户权限管理