mysql数据库教程外联_活字格外联数据库SQLServer和Mysql
的经验(⼤多数经验。。。
来⾃学习和实操后的总结,有说得不对的,或者遗漏的,⼤家留⾔补充。希望这个贴⼦,能成为活字格⽼铁们使⽤外联库的⼀个指南。PS 即使你不打算使⽤外联库,⾥⾯的⼀些⽅法,也值得看⼀看!
⼀、库表规划
1、系统表:如组织架构、⽤户、⾓⾊、权限等。活字格内置在sqlite中,⽆法直连,可通过视图⽅式读取出来
2、词典表dictionary:辅助填报的列表信息,如⾏政区域、男⼥、学历、岗位、合同类型、商品类别等。其中数据量多、结构⽐较独特或需要中台设定的,单独做表,如⾏政区域表(记录多)、商品类别(中台设定)等,其它放在通⽤词典表,做⼀些表设计,我⼀般是设置类别字段、属性字段(固定参/可变参)、排序字段、图⽚字段、三个字符字段,三个⼩数字段。
3、业务表business:记录业务的发⽣,这是使⽤最多的表
4、当前表current:存放当前值/累计值或档案类,如当前库存、累计销售、商品档案等
5、期末表end:存放某个时期的期末结存数,如⽉末库存
6、期间表period:存放某个时期内的发⽣值,如⽉销售统计
⼆、建表
1、表命名:⽬前我使⽤的表命令⽅法:模块英⽂标识_表主名使⽤驼峰法,如hr_employee,hr_employeeFamily
2、视图命名:模块英⽂标识v_视图的作⽤
3、函数命名:模块英⽂标识p_函数的作⽤
4、表名/字段长度:虽然mssql可以128个字符,但还是建议在64个以内
5、字段名全部⽤英⽂,字段全部⽤⼩写英⽂字母,字母开头,不同单词⽤_连接,不⽤复词,即使是内置的sqlite,也建议⽤英⽂名
6、表/字段设计时,尽可能都加注释,我会把字段注释全部⼊到表注释那⾥,省时间。如果字段有固定参的,最好也把固定参定上(2020-9-5改)
7、临时表,使⽤内置的sqlite表,可以提升效率,特别是进⾏中间计算或笛卡尔积时,能极⼤的提升效率,注意,是极⼤!
8、在活字格内先建表,然后在数据库IDE中设计表。这样会在前⾯⼏个字段⾃动建⽴ID(默认主键)、FGC_CreateDate、
FGC_LastModifier、FGC_LastModifyDate、FGC_Creator、FGC_Rowversion、FGC_UpdateHelp字段。主键、并发、权限问题⼀次性解决。PS.⾃建字段千万不要去动,另外,外联表的字段设计不要在活字格⾥弄,因为数据类型有限。
三、字段
1、mysql⾥能⽤char,就不⽤vchar,mssql也⼀样。另外,mssql⾥,如果有中⽂就⽤nchar和nvarchar,纯英⽂和数字,那就⽤char 和varchar
2、vchar不超过5000,如果超过,⽤text,并独⽴出⼀张表,⽤主键对应
3、tinyint-255,smallint-65535,int-42.9亿,bigint-最⼤,实际使⽤时再放⼤⼏个零
4、⼩数类型,如果是⾦融类的⼀定要⽤decimal(18,2),float(7位)和double(15位)⾃⼰选择。(2020-9-5改)
5、是否字段⽤is_,如果是表现已经状态,如is_deleted,1表⽰已删除
6、数据表虽然有⼆进制字段,但活字格的图⽚和附件都是以字符串形式记录地址,以防万⼀,在mssql⾥我都直接⽤nvarchar(max)来
7、表名、字段名、字段类型真得⾮常重要,在⽣产环境中,基本上没有机会可以更改,对有长度的字段类型,还是要留有余量
8、⼀张表只能有⼀个timestamp时间戳类型,如果你给活字格修改数据表的权限,活字格会⾃动帮你建⼀个,那是⽤来防⽌并发脏读的。重复⼀次,活字格⾃动建的⼏个字段,不要去改动,包括字段类型也不要去动!
9、SQL数据库的⽇期,⼀般有date、datetime、datetime2三种,活字格的⽇期对应的是datetime。之前,为了省资源,将⽇期设为了date,但期间出现的⼏次时间字段的数据库报错。出现⼏次后,发现了规律,就是数据库连接断开,重新连的时候,活字格会把date字段识别为⽂本,这样就会出现报错。所以,建议外联数据库的⽇期还是设置为datetime,时间则设置为time(7,0),和活字格的机制保持⼀致!
四、主键/外键
1、主键最好只⽤⾃然主键,不要和业务关联,⽐如活字格⾃动新建的ID字段,同时尽量避免⽤联合主键
2、外键使⽤⽐较讲究,先说逻辑外键,就是活字格中设置关联字段,但不设置联级约束,就是逻辑外键(在活字格⾥不能设置外联表的联级约束,需要在数据库IDE中设置),逻辑外键可以随意⽤。但联级约束外键要慎⽤,因为每次增改删都会触发联级查询,会影响效率。阿⾥的开发⼿册⼲脆就直接⼲掉联级外键了。
3、如果⼀定要⽤联级外键,注意以下⼏点:(1)引⽤列并不⼀定⾮要主键,但必须是唯⼀列;(2)外键列与引⽤列的数据类型/字符集/校对规则要⼀致;(3)外键列和引⽤列都必须建索引;(4)外键列引⽤多个列的,列顺序要⼀致;(5)⼤对象字段不要⽤做引⽤列;(6)外键命名在库⾥要唯⼀,命名规则fk_本表名_引⽤表名_on_引⽤字段,表名的模块缩写和结尾标识去掉;(6)如果⼦表弄了触发器,外键联级更新是不会触发的,所以这种情况不要⽤;(7)不再⽀持分区表了,所以有计划⽤分区表的,不要⽤外键;(8)⾼并发更新表情况下,⼀定⼀定要避免使⽤联级外键
4、虽然阿⾥不建议⽤联级外键,但在⾮常严格要求主⼦表⼀致性、表结构简单、并发不⾼、查询数据量不⾼时,可以⽤⼀下。其它情况,需要联级约束的,最好在前端通过设计来实现
5、⾝份证号、银⾏账号、社保账号之类敏感的字段,不要做主键或外键
五、索引
1、索引命名:主键索引pk_表名_字段名,表名的模块缩写和结尾标识去掉,唯⼀索引uk_,普通索引idx_。如果你选择在活字格中建外联表,会⾃动建⽴⼀个ID的索引(主键⾃动建索引),最好按规则改⼀下名。注:索引需要在数据表IDE中建,活字格中建不了
2、业务上具有唯⼀特性的字段,即使是多个字段的组合,如果要建⽴索引,则必须建成唯⼀索引
3、索引如果不确定要⽤,可以先不设置,后期进⾏sql优化时再使⽤。⼀般建在多表联接⽤的关联字段以及查询频率较⾼的条件字段上。
4、vchar索引,如果⽐较长,需要注意。⼀般20长度已经能覆盖90%的查询判断, 可以⽤count(distinct left(列名, 索引长度))和
count(*)的来测试决定,设置⽅式column_name(length)
5、where和orderby有索引字段,且该字段在复合索引中时,将该字段放在复合索引最后
6、建组合索引的时候,区分度最⾼的在最左边。如where a=? and b=? ,如果 a 列⼏乎接近于唯⼀值,那么只需要单建a列 索引即可
7、存在⾮等号和等号混合时,在建索引时,请把等号条件的列前置。如:where c>? and d=? 那么即使 c 的区分度更⾼,也必须把 d 放在索引的最前列,即索引 idx_d_c
8、不要索引常⽤的⼩型表
9、查看select是否使⽤了索引、使⽤了哪个索引,可以使⽤⽤执⾏计划(EXPLAIN,在IDE中使⽤)查询结果,看key和key_len两项
10、mssql设置为主键和唯⼀键的,会⾃动建索引,不需要另外建;mysql的唯⼀键,是通过建⽴唯⼀性的索引来设置,主键也会⾃动建索引;
六、其它
1、join不要超过3个表,join字段数据类型必须⼀致。多表关联查询时,保证被关联的字段需要有索引。
2、查询先快速定位需要获取的 id 段,然后再关联,如:SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT
100000,20 ) b where a.id=b.id
3、如果对字符编码和引擎不熟悉,反正看到UTF8、innodb的字眼,选它就对
4、反范式:字段允许适当冗余,以提⾼查询性能,但必须考虑数据⼀致。冗余字段应遵循:(1)不是频繁修改的字段;(2)不是 varchar 超长字段,更不能是 text 字段;(3)不是唯⼀索引的字段。举个例⼦就明⽩:商品类⽬名称使⽤频率⾼,字段长度短,名称基本⼀不变,可在相关联的表中冗余存储类⽬名称,避免关联查询。看了⼀个测试,150万的数据,⼀个35秒,⼀个⼏秒,惊呆了
5、通常数据库优化的⽅向:(1)服务器硬件(三⼤件);(2)服务器布置环境(⽐如你⽤mysql,就⼀定不能在windows环境⾥);(3)SQL本⾝优化;(4)反范式;(5)索引优化。查⼀下关键词“慢查询”、“慢sql”、“perl”。
6、阿⾥开发⼿册是禁⽌使⽤存储过程的,认为存储过程难以调试和扩展,更没有移植性。但也有⼈认为可以使⽤,⽐如⼀些常⽤的关联表功能组,还是可以封装。但原则是前后端设计能解决的,就不要⽤存储过程,特别是现在有了服务端命令,应该可以尽量避免使⽤
7、设计视图要从业务出发,视图和业务建⽴⼀对⼀的关系,不要因为想让⼀个视图为多个业务服务,⽽放进不需要的语句、表和字段。视图不要嵌套视图,效率会被⼤⼤影响,和join⼀样,也不要超过3个表。太复杂的,可以考虑⾛存储过程,效率会更⾼。活字格中不能直接建⽴外联表的视图,需要在数据库IDE中建⽴,导⼊时需要选择主键,SQL⾥视图是没有主键概念的,但既然它让你设,你就设⼀
个吧,据说能提⾼性能。⽽且,如果你不设,表名旁边的图标会显⽰为和普通表⼀样,⽽且不是视图特有的图标,完美主义者的我,是不能容忍这种事情的
8、关于外联表选mssql还是mysql,如果活字格服务端和数据库在同⼀个服务器,还是⾃⼰安装数据库,就⽤mssql,⼤多数⼈推荐
2008R2和2012,我已经升级到2012。这种情况选mssql的原因,⼀是阿⾥云和腾讯云亲测都可以安装使⽤,盗不盗版,如果不是做公有云布置,估计也没啥问题,⼆是windows环境还是⽤mssql,⽽且活字格是的,属于⼀家⼈;三是你⾃⼰下载安装的mysql和腾讯云提供给你的mysql是不⼀样的,⾃⼰安装的话,最好不选mysql。如果是使⽤阿⾥云或腾讯云的云数据库,那就选 mysql,⼀是更便宜,⼆是他们都做了优化,也有很多优化⼯具可以使⽤,⽐如前⾯的慢sql,不⽐mssql差。
之前下过mysql现在重新下载mysql9、数据库IDE,因为计划只⽤sqlserver,所以改⽤原⽣的IDE,SQLserver同⼀个安装包,安装时只选IDE就可以,可⽹上搜教程(2020-9-5改)
10、最后说说,我对选内置数据库还是外联数据库的看法。既然写了这么多外联数据库的东西,⾃然我的选择是外联数据库了。选什么不重要,重要的是为什么选?所以简单说⼀下原因:(1)百度sqlite,出现最多的关键词,是轻型、嵌⼊、桌⾯、不需要配置安装,然后sqlite ⽀持⾼并发的读,但不⽀持⾼并发的写,等等,我们是要⽤活字格来做企业业务应⽤的,⽽且是web应⽤,你会觉得sqlite的特点和
需求吻合吗?;(2)上⾯说了很多数据库优化的东西,⽆论是mssql还是mysql,都有⾮常多的优化教程、⽅法和⼯具,阿⾥云和腾讯云也都有提供专门的数据库优化⼯作和⽅案,我认为在数据量到百万甚⾄是⼏⼗万级的时候,性能上的表现⼀定会突出出来,⽽sqlite的优化空间太⼩;(3)接触到外联数据库,我才打开了⼀个新的窗⼝,才能写以上的东西。我相信很多⽤内置库的⽼铁们,表名字段名还是⽤中⽂吧,你不⽤外联库,你就根本不会关注到这些东西;(4)外联mysql和mssql都可以直联,这样可以⽅便的相互窜门,⽽sqlite虽然是活字格内置的,但却不能直连。
PS:虽然我选择了外联数据库,但我仍然会经常⽤到内置库,⽐如前⾯说的,在⼀些临时表和中间表的使⽤上,我基本上都是⽤sqlite,很多情况下,执⾏效率⽐外联表⾼很多,这样你的外联库看上去也清爽些。所以,活字格的内置库⽤sqlite我觉得挺好的,内置和外联,相互补充。另外,新接触低代码系统的⽼铁们,进⼊门槛也⼤⼤降低了~~~
11、看了⼏篇关于mysql和mssql安全性能的⽐较,以及云开发中云函数可以外接mysql,再加上两者云数据库相关两三倍的价格,⼼⾎来潮,花了⼀天时间捣⿎mysql,结果踩了坑。测试的环境,包括(1)使⽤腾讯云数据库mysql,与安装活字格的腾讯云服务器在⼀个私有⽹络内连结;(2)直接在安装活字格安装腾讯云服务器上⾃建mysql。原来环境是在腾讯云服务器中⾃建mssql。结果两个mysql环境效率,都⽐mssql环境,低了2倍左右。这个结果,不能说mysql的执⾏效率⽐mssql低,但⾄少可以下结论:活字格中,使⽤mssql⽐使⽤mysql 的效率更⾼。所以使⽤外联数据库,还是好好把mssql⽤好。
附两个测试点:(1)页⾯表格500⾏数据,使⽤提交表格命令,mssql>2-3分钟左右,mysql>6-8分钟
(2)页⾯两个表格,根据单元格值变化执⾏查询命令,mssql>1秒左右,mysql>3-5秒
12、另外,也总结⼀下使⽤mysql的注意事项:
(1)字符集选择utf8或utf8mb4,腾讯云官⽅是建议直接⽤uft8mb4,能⽀持表情字符,因为很多⼈的名会⽤表情字符。这两种字符集,排序规则都⽤general_ci。这个和mssql不⼀样,在mssql⾥,没有字符集的选择,是通过选择排序规则来选择的,选
Chinese_PRC_CI_AS
(2)是否,⽤tinyint(1)
(3)补充⼀下⼏个⼩数float、double和decimal的应⽤,三个精度依次提升,但效率也依次降低,和前⽂说的有不⼀样的地⽅。A、⾦融相关的,⼀定⽤decimal,⼤⼩⼀般设置为(18,2),不⽤decimal,会遇到浮点不等情况;B、float效率最快,但只有7个有效位,⽐如你设置为(7,4)的话,最多只能表⽰***.****, ⼀些参数型、百分率的字段我会⽤到;C、double翻⼀倍,15个有效位
(4)mysql的数据类型更少,⽐如没有nvarchar(max)、varchar(max)这样的,所以在mysql⾥,长⽂本要⽤text
(5)活字格的图⽚和附件转到mysql,是⽤varchar(500),多附件这种,可能会遇到字符长度不够的情况,如果预计不够,会要改⼀下。不过⾄今我还是没搞清楚,究竟varchar最长可以放多少个字符,资料,有说255的
(5)mysql⾥没有设置唯⼀键的地⽅,是通过建唯⼀索引来设置唯⼀键,索引类型⼀般选unique或normal(index),索引⽅法⼀般BTREE。主键会⾃动建⼀个索引

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。