精通数据库系列之入门-技巧篇5_MySQL
作者:SHARK | 来源:互联网 | 2018-04-20 06:22
大型数据库设计原则作者:思索整理:南海一个好的数据库产品不等于就有一个好的应用系统,如果不能设计一个合理的数据库模型,不仅会增加客户端和服务器段程序的编程和维护的难度,而且将会影响系统实际运行的性能。一般来讲,在一个MIS系统分析、设计、测试和试运行
大型数据库设计原则
作者:思索 整理:南海
一个好的数据库产品不等于就有一个好的应用系统,如果不能设计一个合理的数据库模型,不仅会增加客户端和服务器段程序的编程和维护的难度,而且将会影响系统实际运行的性能。一般来讲,在一个MIS系统分析、设计、测试和试运行阶段,因为数据量较小,设计人员和测试人员往往只注意到功能的实现,而很难注意到性能的薄弱之处,等到系统投入实际运行一段时间后,才发现系统的性能在降低,这时再来考虑提高系统性能则要花费更多的人力物力,而整个系统也不可避免的形成了一个打补丁工程。笔者依据多年来设计和使用数据库的经验,提出以下一些设计准则,供同仁们参考。
命名的规范
不同的数据库产品对对象的命名有不同的要求,因此,数据库中的各种对象的命名、后台程序的代码编写应采用大小写敏感的形式,各种对象命名长度不要超过30个字符,这样便于应用系统适应不同的数据库。
游标(Cursor)的慎用
游标提供了对特定集合中逐行扫描的手段,一般使用游标逐行遍历数据,根据取出的数据不同条件进行不同的操作。尤其对多表和大表定义的游标(大的数据集合)循环很容易使程序进入一个漫长的等特甚至死机,笔者在某市《住房公积金管理系统》进行日终帐户滚积数计息处理时,对一个10万个帐户的游标处理导致程序进入了一个无限期的等特(后经测算需48个小时才能完成)(硬件环境:Alpha/4000 128Mram ,Sco Unix,Sybase 11.0),后根据不同的条件改成用不同的UPDATE语句得以在二十分钟之内完成。
示例如下:
Declare Mycursor cursor for select count_no from COUNT
Open Mycursor
Fetch Mycursor into @vcount_no
While (@@sqlstatus=0)
Begin
If @vcount_no=’’ 条件1
操作1
If @vcount_no=’’ 条件2
操作2
。。。
Fetch Mycursor into @vcount_no
End
。。。
。。。
改为
Update COUNT set 操作1 for 条件1
Update COUNT set 操作2 for 条件2
。。。
。。。
在有些场合,有时也非得使用游标,此时也可考虑将符合条件的数据行转入临时表中,再对临时表定义游标进行操作,可时性能得到明显提高。笔者在某地市〈电信收费系统〉数据库后台程序设计中,对一个表(3万行中符合条件的30多行数据)进行游标操作(硬件环境:PC服务器,PII266 64Mram ,NT4.0 Ms Sqlserver 6.5)。 示例如下:
Create #tmp /* 定义临时表 */
( 字段1
字段2
。。。
)
Insert into #tmp select * from TOTAL where 条件 /* TOTAL中3万行 符合条件只有几十行 */
Declare Mycursor cursor for select * from #tmp
/*对临时表定义游标*/
。。。
索引(Index)的使用原则
创建索引一般有以下两个目的:维护被索引列的唯一性和提供快速访问表中数据的策略。大型数据库有两种索引即簇索引和非簇索引,一个没有簇索引的表是按堆结构存储数据,所有的数据均添加在表的尾部,而建立了簇索引的表,其数据在物理上会按照簇索引键的顺序存储,一个表只允许有一个簇索引,因此,根据B树结构,可以理解添加任何一种索引均能提高按索引列查询的速度,但会降低插入、更新、删除操作的性能,尤其是当填充因子(Fill Factor)较大时。所以对索引较多的表进行频繁的插入、更新、删除操作,建表和索引时因设置较小的填充因子,以便在各数据页中留下较多的自由空间,减少页分割及重新组织的工作。
数据的一致性和完整性
为了保证数据库的一致性和完整性,设计人员往往会设计过多的表间关联(Relation),尽可能的降低数据的冗余。表间关联是一种强制性措施,建立后,对父表(Parent Table)和子表(Child Table)的插入、更新、删除操作均要占用系统的开销,另外,最好不要用Identify 属性字段作为主键与子表关联。如果数据冗余低,数据的完整性容易得到保证,但增加了表间连接查询的操作,为了提高系统的响应时间,合理的数据冗余也是必要的。使用规则(Rule)和约束(Check)来防止系统操作人员误输入造成数据的错误是设计人员的另一种常用手段,但是,不必要的规则和约束也会占用系统的不必要开销,需要注意的是,约束对数据的有效性验证要比规则快。所有这些,设计人员在设计阶段应根据系统操作的类型、频度加以均衡考虑。
事务的陷阱
事务是在一次性完成的一组操作。虽然这些操作是单个的操作,SQL Server能够保证这组操作要么全部都完成,要么一点都不做。正是大型数据库的这一特性,使得数据的完整性得到了极大的保证。
众所周知,SQL Server为每个独立的SQL语句都提供了隐含的事务控制,使得每个DML的数据操作得以完整提交或回滚,但是SQL Server还提供了显式事务控制语句
---- BEGIN TRANSACTION 开始一个事务
---- COMMIT TRANSACTION 提交一个事务
---- ROLLBACK TRANSACTION 回滚一个事务
---- 事务可以嵌套,可以通过全局变量@@trancount检索到连接的事务处理嵌套层次。
需要加以特别注意并且极容易使编程人员犯错误的是,每个显示或隐含的事物开始都使得该变量加1,每个事务的提交使该变量减1,每个事务的回滚都会使得该变量置0,而只有当该变量为0时的事务提交(最后一个提交语句时),这时才把物理数据写入磁盘。
数据库性能调整
在计算机硬件配置和网络设计确定的情况下,影响到应用系统性能的因素不外乎为数据库性能和客户端程序设计。而大多数数据库设计员采用两步法进行数据库设计:首先进行逻辑设计,而后进行物理设计。数据库逻辑设计去除了所有冗余数据,提高了数据吞吐速度,保证了数据的完整性,清楚地表达数据元素之间的关系。而对于多表之间的关联查询(尤其是大数据表)时,其性能将会降低,同时也提高了客 户端程序的编程难度,因此,物理设计需折衷考虑,根据业务规则,确定对关联表的数据量大小、数据项的访问频度,对此类数据表频繁的关联查询应适当提高数据冗余设计。
数据类型的选择
数据类型的合理选择对于数据库的性能和操作具有很大的影响,有关这方面的书籍也有不少的阐述,这里主要介绍几点经验。
Identify字段不要作为表的主键与其它表关联,这将会影响到该表的数据迁移。
Text 和Image字段属指针型数据,主要用来存放二进制大型对象(BLOB)。这类数据的操作相比其它数据类型较慢,因此要避开使用。
日期型字段的优点是有众多的日期函数支持,因此,在日期的大小比较、加减操作上非常简单。但是,在按照日期作为条件的查询操作也要用函数,相比其它数据类型速度上就慢许多,因为用函数作为查询的条件时,服务器无法用先进的性能策略来优化查询而只能进行表扫描遍历每行。
例如:要从DATA_TAB1中(其中有一个名为DATE的日期字段)查询1998年的所有记录。
推荐阅读
本文介绍了如何通过修改织梦DedeCms源代码来实现全站伪静态,以提高管理和SEO效果。全站伪静态可以避免重复URL的问题,同时通过使用mod_rewrite伪静态模块和.htaccess正则表达式,可以更好地适应搜索引擎的需求。文章还提到了一些相关的技术和工具,如Ubuntu、qt编程、tomcat端口、爬虫、php request根目录等。 ...
[详细]
蜡笔小新 2023-12-14 19:45:47
本文介绍了基于PgpoolII的PostgreSQL集群的安装与配置教程。Pgpool-II是一个位于PostgreSQL服务器和PostgreSQL数据库客户端之间的中间件,提供了连接池、复制、负载均衡、缓存、看门狗、限制链接等功能,可以用于搭建高可用的PostgreSQL集群。文章详细介绍了通过yum安装Pgpool-II的步骤,并提供了相关的官方参考地址。 ...
[详细]
蜡笔小新 2023-12-14 19:10:25
本文详细介绍了SQL日志收缩的方法,包括截断日志和删除不需要的旧日志记录。通过备份日志和使用DBCC SHRINKFILE命令可以实现日志的收缩。同时,还介绍了截断日志的原理和注意事项,包括不能截断事务日志的活动部分和MinLSN的确定方法。通过本文的方法,可以有效减小逻辑日志的大小,提高数据库的性能。 ...
[详细]
蜡笔小新 2023-12-14 18:23:25
本文介绍了在开发Android新闻App时,搭建本地服务器的步骤。通过使用XAMPP软件,可以一键式搭建起开发环境,包括Apache、MySQL、PHP、PERL。在本地服务器上新建数据库和表,并设置相应的属性。最后,给出了创建new表的SQL语句。这个教程适合初学者参考。 ...
[详细]
蜡笔小新 2023-12-14 17:15:19
本文介绍了如何使用php限制数据库插入的条数并显示每次插入数据库之间的数据数目,以及避免重复提交的方法。同时还介绍了如何限制某一个数据库用户的并发连接数,以及设置数据库的连接数和连接超时时间的方法。最后提供了一些关于浏览器在线用户数和数据库连接数量比例的参考值。 ...
[详细]
蜡笔小新 2023-12-14 14:06:10
本文介绍了如何使用Power Design(PD)和SQL Server进行数据库反向工程的方法。通过创建数据源、选择要反向工程的数据表,PD可以生成物理模型,进而生成所需的概念模型。该方法适用于SQL Server数据库,对于其他数据库是否适用尚不确定。详细步骤和操作说明可参考本文内容。 ...
[详细]
蜡笔小新 2023-12-14 16:18:06
本文介绍了Oracle Database 10g许可授予信息及其中的高级功能,包括数据库优化数据包、SQL访问指导、SQL优化指导、SQL优化集和重组对象。同时提供了详细说明,指导用户在Oracle Database 10g中如何使用这些功能。 ...
[详细]
蜡笔小新 2023-12-14 13:12:10
本文介绍了adg架构设置在企业数据治理中的应用。随着信息技术的发展,企业IT系统的快速发展使得数据成为企业业务增长的新动力,但同时也带来了数据冗余、数据难发现、效率低下、资源消耗等问题。本文讨论了企业面临的几类尖锐问题,并提出了解决方案,包括确保库表结构与系统测试版本一致、避免数据冗余、快速定位问题等。此外,本文还探讨了adg架构在大版本升级、上云服务和微服务治理方面的应用。通过本文的介绍,读者可以了解到adg架构设置的重要性及其在企业数据治理中的应用。 ...
[详细]
蜡笔小新 2023-12-14 13:05:22
在说Hibernate映射前,我们先来了解下对象关系映射ORM。ORM的实现思想就是将关系数据库中表的数据映射成对象,以对象的形式展现。这样开发人员就可以把对数据库的操作转化为对 ...
[详细]
蜡笔小新 2023-12-14 10:57:47
本文介绍了使用postman进行接口测试的方法,以测试用户管理模块为例。首先需要下载并安装postman,然后创建基本的请求并填写用户名密码进行登录测试。接下来可以进行用户查询和新增的测试。在新增时,可以进行异常测试,包括用户名超长和输入特殊字符的情况。通过测试发现后台没有对参数长度和特殊字符进行检查和过滤。 ...
[详细]
蜡笔小新 2023-12-14 10:29:45
本文详细介绍了MysqlDump和mysqldump进行全库备份的相关知识,包括备份命令的使用方法、my.cnf配置文件的设置、binlog日志的位置指定、增量恢复的方式以及适用于innodb引擎和myisam引擎的备份方法。对于需要进行数据库备份的用户来说,本文提供了一些有价值的参考内容。 ...
[详细]
蜡笔小新 2023-12-14 10:03:27
使用Ubuntu中的Python获取浏览器历史记录原文: ...
[详细]
蜡笔小新 2023-12-14 08:57:59
本文由编程笔记小编整理,介绍了PHP中的MySQL函数库及其常用函数,包括mysql_connect、mysql_error、mysql_select_db、mysql_query、mysql_affected_row、mysql_close等。希望对读者有一定的参考价值。 ...
[详细]
蜡笔小新 2023-12-14 08:19:53
本文介绍了Oracle数据库中tnsnames.ora文件的作用和配置方法。tnsnames.ora文件在数据库启动过程中会被读取,用于解析LOCAL_LISTENER,并且与侦听无关。文章还提供了配置LOCAL_LISTENER和1522端口的示例,并展示了listener.ora文件的内容。 ...
[详细]
蜡笔小新 2023-12-14 07:44:06
本文详细介绍了如何使用Spring特性实现接口多类的动态调用。通过对Spring IoC容器的基础类BeanFactory和ApplicationContext的介绍,以及getBeansOfType方法的应用,解决了在实际工作中遇到的接口及多个实现类的问题。同时,文章还提到了SPI使用的不便之处,并介绍了借助ApplicationContext实现需求的方法。阅读本文,你将了解到Spring特性的实现原理和实际应用方式。 ...
[详细]
蜡笔小新 2023-12-14 03:24:19