MySql Delete 使用及优化

这篇具有很好参考价值的文章主要介绍了MySql Delete 使用及优化。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、概述

DELETE是一个DML语句,用于从表中删除行。
DELETE语句可以从with子句开始,以定义DELETE中可访问的公用表表达式。

单表语法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

DELETE语句从tbl_name中删除行,并返回已删除的行数。要检查已删除的行数,请调用ROW_COUNT()函数。

二、主要条款

可选WHERE子句中的条件标识要删除的行。如果没有WHERE子句,则会删除所有行。
其中_condition是一个表达式,对于要删除的每一行,该表达式的计算结果都为true。
如果指定了ORDER BY子句,则按指定的顺序删除行。LIMIT子句限制了可以删除的行数。这些子句适用于单表删除,但不适用于多表删除。

多表语法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

特权
您需要对表具有DELETE权限才能从表中删除行。您只需要对任何只读取的列(如WHERE子句中命名的列)具有SELECT权限。

性能
当您不需要知道删除的行数时,TRUNCATE TABLE语句是比不带WHERE子句的DELETE语句更快清空表的方法。与DELETE不同,TRUNCATE TABLE不能在事务中使用,也不能在表上有锁的情况下使用。

为了确保给定的DELETE语句不会花费太多时间,DELETE的MySQL特定的LIMIT row_count子句指定了要删除的最大行数。如果要删除的行数大于限制,请重复delete语句,直到受影响的行数小于limit值。

子查询
不能从表中删除,也不能在子查询中从同一表中选择。

分区表支持
      DELETE支持使用partition子句进行显式分区选择,该子句采用一个或多个分区或子分区(或两者)的逗号分隔名称列表,从中选择要删除的行。未包含在列表中的分区将被忽略。给定分区为p0的分区表t,执行语句DELETE FROM t partition(p0)对该表的影响与执行ALTER table t TRUNCATE partition (p0);在这两种情况下,分区p0中的所有行都被删除。
       PARTITION可以与WHERE条件一起使用,在这种情况下,只在列出的分区中的行上测试条件。例如,DELETE FROM t PARTITION(p0)WHERE c<5仅从条件c<5为true的分区p0中删除行;不检查任何其他分区中的行,因此不受DELETE的影响。
       PARTITION子句也可以用于多个表DELETE语句中。每个在FROM选项中命名的表最多可以使用一个这样的选项。

自动递增列
如果删除包含AUTO_INCREMENT列最大值的行,则MyISAM或InnoDB表不会重用该值。如果在自动提交模式下使用delete FROM tbl_name(不带WHERE子句)删除表中的所有行,则除InnoDB和MyISAM外,所有存储引擎的顺序都将重新开始。InnoDB表的这种行为有一些例外。

对于MyISAM表,可以在多列键中指定AUTO_INCREMENT辅助列。在这种情况下,即使对于MyISAM表,也会重复使用从序列顶部删除的值。

修饰符
DELETE语句支持以下修饰符:

  • 如果指定LOW_PRIORITY修饰符,则服务器会延迟DELETE的执行,直到没有其他客户端从表中读取为止。这只会影响仅使用表级锁定的存储引擎(如MyISAM、MEMORY和MERGE)。
  • 对于MyISAM表,如果使用QUICK修饰符,存储引擎在删除期间不会合并索引叶,这可能会加快某些类型的删除操作。
  • IGNORE修饰符导致MySQL在删除行的过程中忽略可忽略的错误。(解析阶段遇到的错误以通常的方式处理。)由于使用IGNORE而被忽略的错误将作为警告返回。有关详细信息

删除顺序
如果DELETE语句包含ORDER BY子句,则按该子句指定的顺序删除行。这主要与LIMIT一起使用。例如,以下语句查找与WHERE子句匹配的行,按时间戳_列对它们进行排序,并删除第一个(最旧的)行: 

DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;

ORDERBY还有助于按照避免引用完整性冲突所需的顺序删除行。

三、InnoDB 表

如果要从一个大表中删除许多行,则可能会超过InnoDB表的锁表大小。为了避免这个问题,或者只是为了尽量减少表保持锁定的时间,以下策略(根本不使用DELETE)可能会有所帮助:

  • 在与原始表具有相同结构的空表中选择不删除的行:
INSERT INTO t_copy SELECT * FROM t WHERE ... ;
  • 使用RENAME TABLE自动移动原始表,并将副本重命名为原始名称:
RENAME TABLE t TO t_old, t_copy TO t;
  • 删除原始表格:
    DROP TABLE t_old;
    

    当RENAME TABLE执行时,没有其他会话可以访问所涉及的表,因此重命名操作不会出现并发问题。

四、多表删除

根据WHERE子句中的条件,可以在DELETE语句中指定多个表来删除一个或多个表中的行。不能在多表DELETE中使用ORDER BY或LIMIT。
对于第一个多表语法,只删除from子句之前列出的表中的匹配行。对于第二个多表语法,只删除from子句(在USING子句之前)中列出的表中的匹配行。其效果是,您可以同时从多个表中删除行,并具有仅用于搜索的其他表:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

这些语句在搜索要删除的行时使用所有三个表,但仅从表t1和t2中删除匹配的行。
前面的示例使用INNER JOIN,但是多个表DELETE语句可以使用SELECT语句中允许的其他类型的联接,例如LEFT JOIN。例如,要删除t1中存在但t2中没有匹配项的行,请使用LEFT JOIN:

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

语法允许在每个tbl_name后面加.*,以与Access兼容。
如果使用涉及存在外键约束的InnoDB表的多表DELETE语句,MySQL优化器可能会以不同于其父/子关系的顺序处理表。在这种情况下,语句将失败并回滚。相反,您应该从单个表中删除,并依靠InnoDB提供的on delete功能来相应地修改其他表。

多表DELETE中的表别名只能在语句的Table_references部分声明。在其他地方,允许使用别名引用,但不允许使用别名声明。
正确:

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

不正确的

DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;

DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2
WHERE a1.id=a2.id;

从MySQL 8.0.16开始的单表DELETE语句也支持表别名。文章来源地址https://www.toymoban.com/news/detail-678939.html

到了这里,关于MySql Delete 使用及优化的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【MySQL 数据库】7、SQL 优化

    ① 批量插入数据 ② 手动控制事务 ③ 主键顺序插入,性能要高于乱序插入 主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3 主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89 【☆】 】 ① 如果需要一次性插入大批量数据(百万级别),使用 insert 语句插入性能 很低 ② 可使用 MySQL 数据库提供的 loa

    2024年02月08日
    浏览(59)
  • MySQL数据库性能优化技巧介绍

    MySQL是目前最流行和广泛使用的开源关系型数据库之一,随着数据量的增长和访问负载的提高,优化数据库性能变得至关重要,以确保系统能够高效地处理大量的并发请求。本文将记录一些MySQL数据库性能优化的技巧,提高数据库的运行效率,提升系统性能。 对于MySQL,最简单

    2024年02月08日
    浏览(131)
  • MYSQL数据库故障排除与优化

    目录 MySQL 单实例故障排查 MySQL 主从故障排查 MySQL 优化 故障现象 1 ERROR    2002    (HY000):    Can\\\'t    connect    to    local    MySQL    server    through    socket \\\'/data/mysql/mysql.sock\\\' (2) 问题分析:以上这种情况一般都是数据库未启动或者数据库端口被防火墙拦截导致。 解

    2024年04月11日
    浏览(41)
  • MySQL数据库的优化技术二

    纵论 对mysql优化时一个综合性的技术,主要包括  表的设计合理化(符合3NF) 添加适当索引(index)  [ 四种: 普通索引、主键索引、唯一索引unique、全文索引 ] 分表技术( 水平分割、垂直分割 ) 水平分割根据一个标准重复定义几个字段值相同,表名称不同的表,表的结构相同 读(

    2024年02月11日
    浏览(37)
  • MySQL数据库——MySQL优化服务器,提高MySQL的运行速度!

    MySQL是一种广泛使用的关系型数据库管理系统,优化MySQL服务器可以显著提高数据库的性能和运行速度。在下面的回答中,我将介绍一些常见的MySQL优化策略和技术,以帮助提高MySQL服务器的性能。 1、优化数据库设计: 正确设计数据库结构,使用适当的数据类型、索引和约束

    2024年02月07日
    浏览(64)
  • 数据库——MySQL高性能优化规范

    所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用 MySQL 保留(如果表名中包含查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过 32 个字符 临时库表必须以 tmp_为前缀并以日期为后缀,

    2024年02月11日
    浏览(99)
  • MySQL数据库进行性能优化的思路

    对MySQL数据库进行性能优化的思路可以涵盖以下方面: 索引优化: 索引是提高查询性能的关键。确保表中的关键列和经常用于查询条件的列都被适当地创建了索引。可以使用 CREATE INDEX 语句添加索引,或者使用 ALTER TABLE 语句在已有表上添加索引。例如,对于一个用户表中的

    2024年02月06日
    浏览(53)
  • MySQL——性能优化与关系型数据库

    吞吐与延迟:有些结论是反直觉的,指导我们关注什么。 没有量化就没有改进:监控与度量指标,指导我们怎么去入手。 80/20原则:先优化性能瓶颈问题,指导我们如何去优化。 过早的优化是万恶之源:指导我们要选择优化的时机。 脱离场景谈性能都是耍流氓:指导我们对

    2024年02月01日
    浏览(48)
  • Django操作MySQL数据库的优化方法

    Django 是一个很流行的 Web 框架,而 MySQL 是常用的关系型数据库之一。在使用 Django 开发 Web 应用时,我们经常需要使用 MySQL 存储数据,因此如何加速 MySQL 是我们需要关注的问题。本文将介绍一些方法来优化 Django 中 MySQL 的性能。 使用适当的索引 索引是 MySQL 中提高查询性能的

    2024年02月10日
    浏览(68)
  • MySQL数据库内存配置与性能优化:合理分配内存,提升数据库性能

             引言 :MySQL是广泛使用的关系型数据库管理系统,而合理配置数据库的内存是保障其高性能运行的关键之一.本文将介绍如何根据MySQL数据库内存值大小来定义,以及这样配置如何影响数据库的性能   内存配置的基本原则 : innodb_buffer_pool_size :该参数定义了InnoDB存储引擎

    2024年02月22日
    浏览(54)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包