技术的世界只说技术就好了..

分类目录

近期文章

标签

近期评论

功能

2018年十二月
« 11月    
 12
3456789
10111213141516
17181920212223
24252627282930
31  

MySQL update 语句的正确用法

以下的文章主要介绍的是MySQL update 语句的实际用法,我们首先是以单表的UPDATE语句来引出实现MySQL update 语句的实际方案,以下就是文章的详细内容描述,望你看完之后会有收获。

单表的MySQL UPDATE语句:

	
  • UPDATE [LOW_PRIORITY] [IGNORE] tbl_name  
  • SET col_name1=expr1 [, col_name2=expr2 ...]  
  • [WHERE where_definition]  
  • [ORDER BY ...]  
  • [LIMIT row_count] 
  • 多表的UPDATE语句:

    	
  • UPDATE [LOW_PRIORITY] [IGNORE] table_references  
  • SET col_name1=expr1 [, col_name2=expr2 ...]  
  • [WHERE where_definition] 
  • UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句,则更新所有的行。如果指定了ORDER BY子句,则按照被指定的顺序对行进行更新。LIMIT子句用于给定一个限值,限制可以被更新的行的数目。

    MySQL UPDATE语句支持以下修饰符:

    如果您使用LOW_PRIORITY关键词,则UPDATE的执行被延迟了,直到没有其它的客户端从表中读取为止。

    如果您使用IGNORE关键词,则即使在更新过程中出现错误,更新语句也不会中断。如果出现了重复关键字冲突,则这些行不会被更新。如果列被更新后,新值会导致数据转化错误,则这些行被更新为最接近的合法的值。

    如果您在一个表达式中通过tbl_name访问一列,则UPDATE使用列中的当前值。例如,以下语句把年龄列设置为比当前值多一:

    	
  • MySQL> UPDATE persondata SET ageage=age+1; 
  • MySQL UPDATE赋值被从左到右评估。例如,以下语句对年龄列加倍,然后再进行增加:

    	
  • MySQL> UPDATE persondata SET ageage=age*2, ageage=age+1; 
  • 如果您把一列设置为其当前含有的值,则MySQL会注意到这一点,但不会更新。

    如果您把被已定义为NOT NULL的列更新为NULL,则该列被设置到与列类型对应的默认值,并且累加警告数。对于数字类型,默认值为0;对于字符串类型,默认值为空字符串('');对于日期和时间类型,默认值为“zero”值。

    UPDATE会返回实际被改变的行的数目。MySQL_info() C API函数可以返回被匹配和被更新的行的数目,以及在UPDATE过程中产生的警告的数量。

    您可以使用LIMIT row_count来限定UPDATE的范围。LIMIT子句是一个与行匹配的限定。只要发现可以满足WHERE子句的row_count行,则该语句中止,不论这些行是否被改变。

    如果一个UPDATE语句包括一个ORDER BY子句,则按照由子句指定的顺序更新行。

    您也可以执行包括多个表的UPDATE操作。table_references子句列出了在联合中包含的表。以下是一个例子:

    	
  • SQL>UPDATE items,month SET items.price=month.price  
  • WHERE items.id=month.id; 
  • 以上的例子显示出了使用逗号操作符的内部联合,但是multiple-table UPDATE语句可以使用在SELECT语句中允许的任何类型的联合,比如LEFT JOIN。

    注释:您不能把ORDER BY或LIMIT与multiple-table UPDATE同时使用。

    在一个被更改的multiple-table UPDATE中,有些列被引用。您只需要这些列的MySQL UPDATE权限。有些列被读取了,但是没被修改。您只需要这些列的SELECT权限。

    如果您使用的multiple-table UPDATE语句中包含带有外键限制的InnoDB表,则MySQL优化符处理表的顺序可能与上下层级关系的顺序不同。在此情况下,语句无效并被 回滚。同时,更新一个单一表,并且依靠ON UPDATE功能。该功能由InnoDB提供,用于对其它表进行相应的修改。

    目前,您不能在一个子查询中更新一个表,同时从同一个表中选择。

    mysql数据库索引查询优化的分享

    问题描述:

    我们要访问的表是一个非常大的表,四千万条记录,id是主键,program_id上建了索引。
    执行一条SQL:
    select * from program_access_log where program_id between 1 and 4000

    这条SQL非常慢。
    我们原以为处理记录太多的原因,所以加了id限制,一次只读五十万条记录

    select * from program_access_log where id between 1 and 500000 and program_id between 1 and 4000

    但是这条SQL仍然很慢,速度比上面一条几乎没有提升。
    Mysql处理50万条记录的表,条件字段还建了索引,这条语句应该是瞬间完成的。


    [separator]
    问题分析:

    这张表大约容量30G,数据库服务器内存16G,无法一次载入。就是这个造成了问题。
    这条SQL有两个条件,ID一到五十万和Program_id一到四千,因为program_id范围小得多,mysql选择它做为主要索引。
    先通过索引文件找出了所有program_id在1到4000范围里所有的id,这个过程非常快。

    接下来要通过这些id找出表里的记录,由于这些id是离散的,所以mysql对这个表的访问不是顺序读取。
    而这个表又非常大,无法一次装入内存,所以每访问一条记录mysql都要重新在磁盘上定位并把附近的记录都载入内存,大量的IO操作导致了速度的下降。

    问题解决方案:
    1. 以program_id为条件对表进行分区
    2. 分表处理,每张表的大小不超过内存的大小
    然而,服务器用的是mysql5.0,不支持分区,而且这个表是公共表,无法在不影响其它项目的条件下修改表的结构。

    所以我们采取了第三种办法:
    select * from program_access_log where id between 1 and 500000 and program_id between 1 and 15000000

    现在program_id的范围远大于id的范围,id被当做主要索引进行查找,由于id是主键,所以查找的是连续50万条记录,速度和访问一个50万条记录的表基本一样

    总结:
    这是一个在千万笔记录表中由于使用了索引导致了数据查找变慢的问题,有一定的典型性和大家交流下!


    来源:http://www.phpv.net/html/1624.html

    © 2018 技术控 All Rights Reserved