昆明网站设计,高质量的常州网站建设,寻找长沙网站建设,购物网站界面 管理员需求分析mysql数据库相关1. 数据库事务的四个特性及含义2. 视图的作用#xff0c;视图可以更改么2.1 什么是视图#xff0c;作用3. drop,delete与truncate的区别4. 索引的工作原理及其种类5. 连接查询的种类6. 数据库范式7. 数据库优化的思路7.1 sql语句的优化7.2 数据库结构优化7.3 …
mysql数据库相关1. 数据库事务的四个特性及含义2. 视图的作用视图可以更改么2.1 什么是视图作用3. drop,delete与truncate的区别4. 索引的工作原理及其种类5. 连接查询的种类6. 数据库范式7. 数据库优化的思路7.1 sql语句的优化7.2 数据库结构优化7.3 服务器硬件优化7.4 存储过程与触发器的区别1. 数据库事务的四个特性及含义
数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。
原子性:整个事务中的所有操作要么全部完成要么全部不完成不可能停滞在中间某个环节。事务在执行过程中发生错误会被回滚Rollback到事务开始前的状态就像这个事务从来没有执行过一样一致性:在事务开始之前和事务结束以后数据库的完整性约束没有被破坏。隔离性:隔离状态执行事务使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务运行在相同的时间内执行 相同的功能事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化为了防止事务操作间的混淆必须串行化或序列化请 求使得在同一时间仅有一个请求用于同一数据。持久性:在事务完成以后该事务所对数据库所作的更改便持久的保存在数据库之中并不会被回滚。
2. 视图的作用视图可以更改么
2.1 什么是视图作用
视图是虚拟的表与包含数据的表不一样视图只包含使用时动态检索数据的查询不包含任何列或数据。使用视图可以简化复杂的sql操作隐藏具体的细节保护数据视图创建后可以使用与表相同的方式利用它们。 视图不能被索引也不能有关联的触发器或默认值如果视图本身内有order by 则对视图再次order by将被覆盖。 创建视图create view XXX as XXXXXXXXXXXXXX;
创建视图
-- 创建视图my_view1
create view my_view1 as select users1.userName, users1.birthday from users1查询
select * from my_view1对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等是可以对其更新的对视图的更新将对基表进行更新但是视图主要用于简化检索保护数据并不用于更新而且大部分视图都不可以更新。
3. drop,delete与truncate的区别
drop直接删掉表 truncate删除表中数据再插入时自增长id又从1开始 delete删除表中数据可以加where字句。
DELETE语句执行删除的过程是每次从表中删除一行并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。delete一行一行的删除truncate会一次性删掉所有数据标识列为0。表和索引所占空间。当表被TRUNCATE 后这个表和索引所占用的空间会恢复到初始大小而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。一般而言drop truncate delete应用范围。TRUNCATE 只能对TABLEDELETE可以是table和viewTRUNCATE 和DELETE只删除数据而DROP则删除整个表结构和数据。truncate与不带where的delete 只删除数据而不删除表的结构定义drop语句将删除表的结构被依赖的约束constrain),触发器trigger)索引index);依赖于该表的存储过程/函数将被保留但其状态会变为invalid。delete语句为DMLdata maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。truncate、drop是DLLdata define language),操作立即生效原数据不放到 rollback segment中不能回滚在没有备份情况下谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除如果于事务无关用truncate即可实现。如果和事务有关想触发trigger,还是用delete。Truncate table 表名 速度快,而且效率高,因为: truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快且使用的系统和事务日志资源少。DELETE 语句每次删除一行并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据并且只在事务日志中记录页的释放。TRUNCATE TABLE 删除表中的所有行但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值请改用 DELETE。如果要删除表定义及其数据请使用 DROP TABLE 语句。对于由 FOREIGN KEY 约束引用的表不能使用 TRUNCATE TABLE而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中所以它不能激活触发器。
4. 索引的工作原理及其种类 数据库索引是数据库管理系统中一个排序的数据结构以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B树。 在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用指向数据这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。 在大量数据时加快查询用的以空间换取时间对特定的列进行索引作为查询条件多的列。 为表设置索引要付出代价的一是增加了数据库的存储空间二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动) 创建索引可以大大提高系统的查询性能。 第一通过创建唯一性索引可以保证数据库表中每一行数据的唯一性。 第二可以大大加快数据的检索速度这也是创建索引的最主要的原因。 第三可以加速表和表之间的连接特别是在实现数据的参考完整性方面特别有意义。 第四在使用分组和排序子句进行数据检索时同样可以显著减少查询中分组和排序的时间。 第五通过使用索引可以在查询的过程中使用优化隐藏器提高系统的性能。 索引的缺点 第一创建索引和维护索引要耗费时间这种时间随着数据量的增加而增加。 第二索引需要占物理空间除了数据表占数据空间之外每一个索引还要占一定的物理空间如果要建立聚簇索引那么需要的空间就会更大。 第三当对表中的数据进行增加、删除和修改的时候索引也要动态的维护这样就降低了数据的维护速度。 创建索引的考虑的因素 索引是建立在数据库表中的某些列的上面。在创建索引的时候应该考虑在哪些列上可以创建索引在哪些列上不能创建索引。一般来说应该在这些列上创建索引在经常需要搜索的列上可以加快搜索的速度在作为主键的列上强制该列的唯一性和组织表中数据的排列结构在经常用在连接的列上这些列主要是一些外键可以加快连接的速度在经常需要根据范围进行搜索的列上创建索引因为索引已经排序其指定的范围是连续的在经常需要排序的列上创建索引因为索引已经排序这样查询可以利用索引的排序加快排序查询时间在经常使用在WHERE子句中的列上面创建索引加快条件的判断速度。 创建索引的的这些列具有下列特点 第一对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为既然这些列很少使用到因此有索引或者无索引并不能提高查询速度。相反由于增加了索引反而降低了系统的维护速度和增大了空间需求。 第二对于那些只有很少数据值的列也不应该增加索引。这是因为由于这些列的取值很少例如人事表的性别列在查询的结果中结果集的数据行占了表中数据行的很大比例即需要在表中搜索的数据行的比例很大。增加索引并不能明显加快检索速度。 第三对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为这些列的数据量要么相当大要么取值很少。 第四当修改性能远远大于检索性能时不应该创建索引。这是因为修改性能和检索性能是互相矛盾的。当增加索引时会提高检索性能但是会降低修改性能。当减少索引时会提高修改性能降低检索性能。因此当修改性能远远大于检索性能时不应该创建索引。 根据数据库的功能可以在数据库设计器中创建三种索引唯一索引、主键索引和聚集索引。 唯一索引 唯一索引是不允许其中任何两行具有相同索引值的索引。 当现有数据中存在重复的键值时大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如如果在employee表中职员的姓(lname)上创建了唯一索引则任何两个员工都不能同姓。 主键索引 主键索引 数据库表经常有一列或列组合其值唯一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义主键将自动创建主键索引主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时它还允许对数据的快速访问。 聚集索引 在聚集索引中表中行的物理顺序与键值的逻辑索引顺序相同。一个表只能包含一个聚集索引。
如果某索引不是聚集索引则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比聚集索引通常提供更快的数据访问速度。
5. 连接查询的种类
--建表table1,table2
create table table1(id int,name varchar(10))
create table table2(id int,score int)insert into table1 select 1,lee
insert into table1 select 2,zhang
insert into table1 select 4,wanginsert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,70 一、外连接 概念包括左向外联接、右向外联接或完整外部联接 左连接left join 或 left outer join 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行则在相关联的结果集行中右表的所有选择列表列均为空值(null)。sql 语句select * from table1 left join table2 on table1.idtable2.id注释包含table1的所有子句根据指定条件返回table2相应的字段不符合的以null显示 右连接right join 或 right outer join 右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行则将为左表返回空值。sql 语句select * from table1 right join table2 on table1.idtable2.id注释包含table2的所有子句根据指定条件返回table1相应的字段不符合的以null显示 完整外部联接:full join 或 full outer join 完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时则另一个表的选择列表列包含空值。如果表之间有匹配行则整个结果集行包含基表的数据值。sql 语句select * from table1 full join table2 on table1.idtable2.id注释返回左右连接的和见上左、右连接全集。
二、内连接 1.概念内联接是用比较运算符比较要联接列的值的联接
2.内连接join 或 inner join
3.sql 语句
select * from table1 join table2 on table1.idtable2.id注释只返回符合条件的table1和table2的列
内连接的等价查询
A:select a.*,b.* from table1 a,table2 b where a.idb.id
B:select * from table1 cross join table2 where table1.idtable2.id (注cross join后加条件只能用where,不能用on)
三、交叉连接(完全) 概念没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。table1和table2交叉连接产生3*39条记录 交叉连接cross join (不带条件where…) sql语句
select * from table1 cross join table2注释返回3*39条记录即笛卡尔积
等价与下列执行效果相同 A:select * from table1,table2
6. 数据库范式 第一范式1NF 在任何一个关系数据库中第一范式1NF是对关系模式的基本要求不满足第一范式1NF的数据库就不是关系数据库。 所谓第一范式1NF是指数据库表的每一列都是不可分割的基本数据项同一列中不能有多个值即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性就可能需要定义一个新的实体新的实体由重复的属性构成新实体与原实体之间为一对多关系。在第一范式1NF中表的每一行只包含一个实例的信息。简而言之第一范式就是无重复的列。 第二范式2NF 第二范式2NF是在第一范式1NF的基础上建立起来的即满足第二范式2NF必须先满足第一范式1NF。第二范式2NF要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。 第二范式2NF要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性如果存在那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列以存储各个实例的惟一标识。简而言之第二范式就是非主属性非部分依赖于主关键字。 第三范式3NF 满足第三范式3NF必须先满足第二范式2NF。简而言之第三范式3NF要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如存在一个部门信息表其中每个部门有部门编号dept_id、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表则根据第三范式3NF也应该构建它否则就会有大量的数据冗余。简而言之第三范式就是属性不依赖于其它非主属性。消除冗余
7. 数据库优化的思路
7.1 sql语句的优化
应尽量避免在 where 子句中使用!或操作符否则将引擎放弃使用索引而进行全表扫描。应尽量避免在 where 子句中对字段进行 null 值判断否则将导致引擎放弃使用索引而进行全表扫描如select id from t where num is null 可以在num上设置默认值0确保表中num列没有null值然后这样查询 select id from t where num0很多时候用 exists 代替 in 是一个好的选择, exists 将前一个表的结果集作为查询条件与另一个表进行筛选匹配。而in会查询整个表。用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤 对查询进行优化要尽量避免全表扫描首先应考虑在 where 及 order by 涉及的列上建立索引。 应尽量避免在 where 子句中对字段进行 null 值判断否则将导致引擎放弃使用索引而进行全表扫描如
select id from t where num is null最好不要给数据库留NULL尽可能的使用 NOT NULL填充数据库. 备注、描述、评论之类的可以设置为 NULL其他的最好不要使用NULL。
不要以为 NULL 不需要空间比如char(100) 型在字段建立时空间就固定了 不管是否插入值NULL也包含在内都是占用 100个字符的空间的如果是varchar这样的变长字段 null 不占用空间。
可以在num上设置默认值0确保表中num列没有null值然后这样查询
select id from t where num 0应尽量避免在 where 子句中使用 ! 或 操作符否则将引擎放弃使用索引而进行全表扫描。 应尽量避免在 where 子句中使用 or 来连接条件如果一个字段有索引一个字段没有索引将导致引擎放弃使用索引而进行全表扫描如
select id from t where num10 or Name admin可以这样查询使用union 或者 union all
select id from t where num 10
union
select id from t where Name admin
-- 一条数据union all查询了两个结果的并集
in 和 not in 也要慎用否则会导致全表扫描如
select id from t where num in(1,2,3)对于连续的数值能用 between 就不要用 in 了
select id from t where num between 1 and 3很多时候用 exists 代替 in 是一个好的选择
select num from a where num in(select num from b)用下面的语句替换
select num from a where exists(select 1 from b where numa.num)下面的查询也将导致全表扫描
select id from t where name like ‘%abc%’若要提高效率可以考虑全文检索。
如果在 where 子句中使用参数也会导致全表扫描。因为SQL只有在运行时才会解析局部变量但优化程序不能将访问计划的选择推迟到运行时它必须在编译时进行选择。然而如果在编译时建立访问计划变量的值还是未知的因而无法作为索引选择的输入项。如下面语句将进行全表扫描
select id from t where num num可以改为强制查询使用索引
select id from t with(index(索引名)) where num num应尽量避免在 where 子句中对字段进行表达式操作这将导致引擎放弃使用索引而进行全表扫描。如
select id from t where num/2 100应改为:
select id from t where num 100*2应尽量避免在where子句中对字段进行函数操作这将导致引擎放弃使用索引而进行全表扫描。如
select id from t where substring(name,1,3) abc -- name以abc开头的id
select id from t where datediff(day,createdate,2005-11-30) 0 -- 2005-11-30 --生成的id
应改为:
select id from t where name like abc%
select id from t where createdate 2005-11-30 and createdate 2005-12-1不要在 where 子句中的“”左边进行函数、算术运算或其他表达式运算否则系统将可能无法正确使用索引。 在使用索引字段作为条件时如果该索引是复合索引那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引否则该索引将不会被使用并且应尽可能的让字段顺序与索引顺序相一致。 不要写一些没有意义的查询如需要生成一个空表结构
select col1,col2 into #t from t where 10这类代码不会返回任何结果集但是会消耗系统资源的应改成这样
create table #t(…)Update 语句如果只更改1、2个字段不要Update全部字段否则频繁调用会引起明显的性能消耗同时带来大量日志。 对于多张大数据量这里几百条就算大了的表JOIN要先分页再JOIN否则逻辑读会很高性能很差。 select count(*) from table这样不带任何条件的count会引起全表扫描并且没有任何业务意义是一定要杜绝的。先where筛选再聚合。 索引并不是越多越好索引固然可以提高相应的 select 的效率但同时也降低了 insert 及 update 的效率因为 insert 或 update 时有可能会重建索引所以怎样建索引需要慎重考虑视具体情况而定。一个表的索引数最好不要超过6个若太多则应考虑一些不常使用到的列上建的索引是否有 必要。 应尽可能的避免更新 clustered 索引数据列因为 clustered 索引数据列的顺序就是表记录的物理存储顺序一旦该列值改变将导致整个表记录的顺序的调整会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列那么需要考虑是否应将该索引建为 clustered 索引。 尽量使用数字型字段若只含数值信息的字段尽量不要设计为字符型这会降低查询和连接的性能并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符而对于数字型而言只需要比较一次就够了。 尽可能的使用 varchar/nvarchar 代替 char/nchar 因为首先变长字段存储空间小可以节省存储空间其次对于查询来说在一个相对较小的字段内搜索效率显然要高些。 任何地方都不要使用 select * from t 用具体的字段列表代替“*”不要返回用不到的任何字段。 尽量使用表变量来代替临时表。如果表变量包含大量数据请注意索引非常有限只有主键索引。 避免频繁创建和删除临时表以减少系统表资源的消耗。临时表并不是不可使用适当地使用它们可以使某些例程更有效例如当需要重复引用大型表或常用表中的某个数据集时。但是对于一次性事件 最好使用导出表。 在新建临时表时如果一次性插入数据量很大那么可以使用 select into 代替 create table避免造成大量 log 以提高速度如果数据量不大为了缓和系统表的资源应先create table然后insert。 如果使用到了临时表在存储过程的最后务必将所有的临时表显式删除先 truncate table 然后 drop table 这样可以避免系统表的较长时间锁定。 尽量避免使用游标因为游标的效率较差如果游标操作的数据超过1万行那么就应该考虑改写。 使用基于游标的方法或临时表方法之前应先寻找基于集的解决方案来解决问题基于集的方法通常更有效。 与临时表一样游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON 在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。 尽量避免大事务操作提高系统并发能力。 尽量避免向客户端返回大数据量若数据量过大应该考虑相应需求是否合理。
实际案例分析拆分大的 DELETE 或INSERT 语句批量提交SQL语句 如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询你需要非常小心要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的表一锁住了别的操作都进不来了。
Apache 会有很多的子进程或线程。所以其工作起来相当有效率而我们的服务器也不希望有太多的子进程线程和数据库链接这是极大的占服务器资源的事情尤其是内存。
如果你把你的表锁上一段时间比如30秒钟那么对于一个有很高访问量的站点来说这30秒所积累的访问进程/线程数据库链接打开的文件数可能不仅仅会让你的WEB服务崩溃还可能会让你的整台服务器马上挂了。
7.2 数据库结构优化
范式优化 比如消除冗余节省空间。。反范式优化比如适当加冗余等减少join拆分表 分区将数据在物理上分隔开不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样当对这个表进行查询时只需要在表分区中进行扫描而不必进行全表扫描明显缩短了查询时间另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。拆分其实又分垂直拆分和水平拆分 案例 简单购物系统暂设涉及如下表 1.产品表数据量10w稳定 2.订单表数据量200w且有增长趋势 3.用户表 数据量100w且有增长趋势 以mysql为例讲述下水平拆分和垂直拆分mysql能容忍的数量级在百万静态数据可以到千万 垂直拆分解决问题表与表之间的io竞争 不解决问题单表中数据量增长出现的压力 方案 把产品表和用户表放到一个server上 订单表单独放到一个server上 水平拆分 解决问题单表中数据量增长出现的压力 不解决问题表与表之间的io争夺方案 用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上(女的爱购物)。
7.3 服务器硬件优化
不多bb砸钱。
7.4 存储过程与触发器的区别
触发器与存储过程非常相似触发器也是SQL语句集两者唯一的区别是触发器不能用EXECUTE语句调用而是在用户执行Transact-SQL语句时自动触发激活执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。**由于用户不能绕过触发器所以可以用它来强制实施复杂的业务规则以确保数据的完整性。触发器不同于存储过程触发器主要是通过事件执行触发而被执行的而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时SQLSERVER就会自动执行触发器所定义的SQL语句从而确保对数据的处理必须符合这些SQL语句所定义的规则。
