万字长文带你走进MySql优化(系统层面优化、软件层面优化、SQL层面优化)

发布时间：2023-03-25 文章分类：电脑基础 投稿人：樱花 字号： 默认 | 大 | 超大 打印

文章目录

    • 系统层面优化
      • 采用分布式架构
      • 使用缓存
      • 使用搜索引擎
    • 软件层面优化
      • 调整 MySQL 参数配置
      • 定期清理无用数据
      • 创建索引
        • 创建索引
          • 普通索引
          • 唯一索引
          • 全文索引
          • 组合索引
          • 空间索引
          • 主键索引
          • 外键索引
          • 索引前缀
        • 适合创建索引的场景
        • 不适合创建索引的场景
      • 优化表结构
      • 分库分表
    • SQL优化
      • explain执行计划
        • **样本sql**
        • **执行结果**
        • 字段含义
      • Select 优化
        • 避免 SELECT * 查询，只查需要的字段
        • 尽量避免or查询
        • 使用 UNION ALL 替代 UNION
        • 避免使用子查询，可以改成 JOIN
        • 有必要时在应用层 ORDER BY 和 GROUP BY
        • 避免使用 LIKE '%value%' 或 LIKE '%value'
        • 避免在索引字段上使用函数
        • 避免隐式类型转换
        • 表连接时候用小数据集驱动大数据集
        • 避免在索引字段使用不等值操作(!=、<>)
        • inner join 、left join、right join选择
        • 先过滤，再GROUP BY、ORDER BY
        • 查询时对null数据处理
        • 限制返回条数
        • 明知结果返回一条记录，可以加limit1
        • 分页查询优化
          • 使用书签
          • 延迟关联
          • 倒序查询
      • Update优化
        • 使用或者涉及索引字段进行update
        • 使用批量更新
        • 限制更新
        • 避免使用子查询
        • 减少触发器和外键
      • Insert 优化
        • 批量插入数据
        • 使用 LOAD DATA INFILE
        • 大量更新数据时禁用索引
        • 使用 REPLACE INTO
        • 大数据量减少自增主键的使用
      • Delete 优化
        • 推荐使用 TRUNCATE TABLE
        • 推荐使用删除标记

       MySQL 是一个关系型数据库管理系统，可以从不同的层面进行优化以提高系统的性能和效率。下面就是从系统设计层面、软件层面、SQL层面的一些优化建议。优化MySql可以从减轻数据库压力、提高配置、提高查询效率等方面入手。

系统层面优化

采用分布式架构

       如果单台服务器无法满足需求，可以采用分布式架构来提高 MySQL 的性能。常用的分布式架构包括主从复制、读写分离等。

扩展

  1. 主从复制:主从复制是指将一个 MySQL 实例（主库）中的数据复制到其他 MySQL 实例（从库）中，从库中的数据与主库中的数据保持一致。主从复制的主要优点是：
  1. 读写分离:读写分离是指将读请求和写请求分别分发到不同的 MySQL 实例中处理。写请求只发送到主库中，而读请求则发送到从库中。读写分离的主要优点是：

使用缓存

       使用缓存优化 MySQL 可以大幅提升系统性能，减少数据库的压力。

常用的缓存方案有以下几种：

       前端缓存是指将数据缓存在客户端（如浏览器）中，减少服务器端的请求。前端缓存可以通过 HTTP 缓存头来实现，例如，可以设置 Cache-Control、Expires、Last-Modified 等缓存头。

       应用程序缓存是指将数据缓存在应用程序的内存中，减少对数据库的访问。应用程序缓存可以使用一些缓存框架来实现，例如，Redis、Memcached 等。在使用应用程序缓存时需要注意缓存数据的有效期，避免缓存数据过期或失效。

       数据库缓存是指将数据缓存在数据库的内存中，减少磁盘 I/O 的访问。数据库缓存可以使用 MySQL 内置的缓存机制来实现，例如，使用 Query Cache、InnoDB Buffer Pool 等。

扩展:使用缓存优化 MySQL 需要注意以下几点：

  1. 缓存数据的有效期，避免缓存数据过期或失效。
  2. 缓存数据的一致性，需要确保缓存数据与数据库中的数据保持一致。
  3. 缓存数据的大小，需要根据系统的需求和硬件资源来确定缓存的大小。
  4. 缓存数据的并发访问，需要考虑多线程并发访问时的锁竞争问题。
  5. 缓存的选择，需要根据系统的需求和硬件资源来选择合适的缓存方案，例如，前端缓存、应用程序缓存、数据库缓存等。

使用搜索引擎

       使用 Elasticsearch 进行查询可以加速查询操作，并且可以提供强大的搜索和分析功能。

注意⚠️

软件层面优化

调整 MySQL 参数配置

       通过修改 MySQL 的配置参数来优化 MySQL 的性能，例如，修改缓冲池的大小、修改连接数的数量、缓存池大小等等。

扩展

定期清理无用数据

       定期清理无用数据可以帮助我们减少数据存储空间，提高数据库的性能，以及减少备份和恢复数据的时间和成本

扩展

创建索引

索引类似于字典的目录，可以提高查询的效率。

索引从物理上可以分为：聚集索引，非聚集索引

从逻辑上可以分为：普通索引，唯一索引，主键索引，联合索引，全文索引

创建索引可以提高数据库查询性能，但是不是所有场景都适合创建索引。

创建索引

普通索引

适用场景：对于一些较小的表或者经常需要进行查询的表，可以使用普通索引来提高查询效率。

CREATE INDEX idx_name ON table_name(column_name);
唯一索引

适用场景：当需要保证某个字段的唯一性时，可以使用唯一索引。

CREATE UNIQUE INDEX idx_name ON table_name(column_name);
全文索引

适用场景：当需要进行全文搜索时，可以使用全文索引。

CREATE FULLTEXT INDEX idx_name ON table_name(column_name);
组合索引

适用场景：当查询条件中涉及多个字段时，可以使用组合索引来提高查询效率。

CREATE INDEX idx_name ON table_name(column1, column2, ...);
空间索引

适用场景：当需要对地理位置进行查询时，可以使用空间索引。

CREATE SPATIAL INDEX idx_name ON table_name(column_name);
主键索引

适用场景：当需要对某个字段进行唯一标识时，可以使用主键索引。

ALTER TABLE table_name ADD PRIMARY KEY(column_name);
外键索引

适用场景：当需要对表之间的关联进行查询时，可以使用外键索引。

ALTER TABLE table_name ADD FOREIGN KEY(column_name) REFERENCES ref_table(ref_column);
索引前缀

适用场景：当某个字段值的长度较长，可以使用索引前缀来提高查询效率。

注意⚠️：使用索引前缀时，可能会导致索引失效或者查询结果不准确，需要根据实际情况进行选择。

CREATE INDEX idx_name ON table_name(column_name(prefix_length));

适合创建索引的场景

不适合创建索引的场景

优化表结构

分库分表

       MySQL 分库分表是一种常用的数据库水平扩展方式，可以有效地解决单个 MySQL 实例无法满足高并发、海量数据存储等需求的问题。可以将一个大型的数据库拆分成多个小型的数据库，每个小型数据库中包含一部分数据。同时，可以将一个大型的表拆分成多个小型的表，每个小型表中包含一部分数据。这种方式可以解决单一数据库或表过大导致的性能瓶颈问题，提高系统的可扩展性和可用性。

扩展

分库分表的具体实现方法有以下几种：

       垂直分库是将一个大型的数据库拆分成多个小型的数据库，每个小型数据库中包含一部分相关的表。例如，可以将一个电商系统中的用户表、订单表、商品表等拆分成多个小型数据库，每个小型数据库中只包含一部分相关的表。垂直分库的优点是易于管理，每个小型数据库中包含的表都具有相似的特点。缺点是可能会导致数据不一致，例如，当一个表的数据需要更新时，可能需要在多个小型数据库中进行更新操作。

       水平分库是将一个大型的数据库中的表按照某种规则分散到多个小型数据库中，每个小型数据库中包含一部分表。例如，可以将一个电商系统中的订单表按照订单号的范围分散到多个小型数据库中，每个小型数据库中包含一部分订单数据。水平分库的优点是易于扩展，可以将新的小型数据库添加到系统中。缺点是可能会导致数据不一致，例如，当一个表的数据需要更新时，可能需要在多个小型数据库中进行更新操作。

       水平分表是将一个大型的表按照某种规则分散到多个小型表中，每个小型表中包含一部分数据。例如，可以将一个电商系统中的订单表按照订单号的范围分散到多个小型表中，每个小型表中包含一部分订单数据。水平分表的优点是易于扩展，可以将新的小型表添加到系统中。缺点是可能会导致查询性能下降，因为查询可能需要在多个小型表中进行。

SQL优化

explain执行计划

       在看具体SQL优化之前，可以先了解一下explain执行计划，使用 EXPLAIN 命令来获取一个 SQL 查询语句的执行计划。执行计划描述了 MySQL 数据库系统如何执行查询，并且可以用来分析和优化查询语句。

样本sql

CREATE TABLE students (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);
ALTER TABLE students ADD UNIQUE INDEX email_UNIQUE (email);
#explain执行计划
EXPLAIN select * from students where email ! "123";

执行结果

万字长文带你走进MySql优化(系统层面优化、软件层面优化、SQL层面优化)

字段含义

  1. id：查询的唯一标识符。如果查询包含子查询，则每个子查询都有一个唯一标识符。

  2. select_type：查询的类型，例如 SIMPLE（简单查询）、PRIMARY（主查询）或 UNION（联合查询）等。

  3. table：查询的表名。

  4. partitions：查询的分区。

  5. type:连接类型【SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是

    consts 最好。】(执行性能排名:system > const > eq_ref > ref > range > index > all。)

    • system：表仅有一行，基本用不到；

    • const：表最多一行数据配合，主键查询时触发较多；

    • eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型；

    • ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；

    • range：只检索给定范围的行，使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range；

    • index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小；

    • all：全表扫描；

  6. possible_keys：查询可能使用的索引列表。

  7. key：查询实际使用的索引。

  8. key_len：索引的长度。

  9. ref：使用索引的列或常数。

  10. rows：MySQL 估计将会扫描的行数。

  11. filtered：使用条件过滤后，剩余的行数百分比。

  12. Extra：额外的信息，例如使用了哪些索引或排序方式等。

    • Using index：只从索引树中获取信息，而不需要回表查询；

    • Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。需要回表查询。

    • Using temporary：mysql常建一个临时表来容纳结果，典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时；

Select 优化

避免 SELECT * 查询，只查需要的字段

  1. 性能问题：使用"SELECT字段"可以提高查询性能。当使用"SELECT *"时，MySQL需要检索表中的所有字段，包括不需要的字段，这会占用更多的系统资源和时间。而使用"SELECT字段"可以减少检索的数据量，从而提高查询性能。
  2. 可读性和维护性问题：使用"SELECT字段"可以提高查询的可读性和维护性。当使用"SELECT *"时，查询结果中的字段顺序可能会发生变化，这会给程序员带来一定的困扰。而使用"SELECT字段"可以明确指定查询的字段，使得查询结果的字段顺序和查询语句中的字段顺序一致，更易于程序员理解和维护。
  3. 安全问题：使用"SELECT字段"可以提高查询的安全性。当使用"SELECT *"时，如果表结构发生变化，可能会将不需要的字段暴露给外部，这会给系统带来潜在的安全风险。而使用"SELECT字段"可以避免暴露不需要的字段，从而提高查询的安全性。

尽量避免or查询

or没有索引的字段会走全表查询，有必要时可以让拆成多条sql，让没有索引的字段and有索引的字段

如a，c字段有单值索引

#这条语句走全表扫描
select * from test where a = "xxx" or b = "xxx";

可以优化成下面语句

#分开查询后续合并结果
select * from test where a = "xxx";
select * from test where c = "xxx" and b = "xxx";
#或者单次查询
select * from test where a = "xxx" or a in (select a from test where c = "xxx" and b = "xxx";);

使用 UNION ALL 替代 UNION

       因为 UNION ALL 不会去重，速度更快，UNION 会去重，即将两个结果集中相同的记录合并成一条记录。如果我们确定合并的结果集中不会出现重复记录，那么我们可以使用 UNION ALL 来代替 UNION 操作。

注意⚠️:使用 UNION ALL 可能会增加网络传输的数据量，因为结果集中可能会有重复的记录。因此，我们需要在实际应用中根据具体情况来选择使用 UNION 还是 UNION ALL。

避免使用子查询，可以改成 JOIN

有必要时在应用层 ORDER BY 和 GROUP BY

       避免使用不必要的 ORDER BY 和 GROUP BY 操作，可以在应用层进行排序和分组。

  1. 减少数据库负担：ORDER BY 和 GROUP BY 操作需要对结果集进行排序或分组，会增加数据库的负担，特别是当结果集非常大时，效率会更低。而在应用层进行排序或分组可以减轻数据库的压力。
  2. 灵活性更高：在应用层进行排序或分组可以更加灵活地控制结果集的处理方式，可以根据具体需求进行自定义排序或分组操作。

避免使用 LIKE ‘%value%’ 或 LIKE ‘%value’

       索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。以通配符（%）开头的搜索字符串会强制数据库系统进行全表扫描。

       尽可能使用前缀搜索：如果可以使用前缀搜索而不是在字符串的任意位置进行匹配，那么查询将更加高效。例如，LIKE ‘value%’ 比 LIKE ‘%value%’ 更加高效

如果非要使用 LIKE ‘%value%’ 或 LIKE ‘%value’，可以采取放在语句末、搜索引擎、离线数据仓库

-- 原查询：
SELECT * FROM my_table WHERE my_column LIKE '%abc';
-- 优化后的查询：
SELECT * FROM my_table WHERE REVERSE(my_column) LIKE REVERSE('cba')%;

避免在索引字段上使用函数

       在索引字段上运用了函数，导致索引失效。B+ 树提供索引的快速定位能力，来源于同一层兄弟节点的有序性。也就是说，对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。可以在查询后对结果数据字段进行函数操作。

避免隐式类型转换

隐式类型转换会导致索引失效

#如order_id是varchar类型索引字段，查询时用的整数，底层查询时会做类型转换
select * from test where order_id = 110717;

表连接时候用小数据集驱动大数据集

       小表驱动大表可以减少不必要的表连接，从而达到优化效果

扩展:
       嵌套循环联接（NLJ）算法：循环从第一个表中依次读取行，取到每行再到联接的下一个表中循环匹配。这个过程会重复多次直到剩余的表都被联接了。通过外循环的行去匹配内循环的行，所以内循环的表会被扫描多次。(可以理解为外层循环一次就是一次表连接，以小表1万条，大表1000万条来对比，小表驱动大表来算只需要1万次表连接)

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions,
          send to client
    }
  }
}

避免在索引字段使用不等值操作(!=、<>)

       当不等值操作在主键索引上时，索引生效(range查询)

万字长文带你走进MySql优化(系统层面优化、软件层面优化、SQL层面优化)

       当不等值操作在唯一索引上时，索引生效(唯一索引)

万字长文带你走进MySql优化(系统层面优化、软件层面优化、SQL层面优化)

       注意⚠️:当不等值操作作用在普通索引上时，索引失效

万字长文带你走进MySql优化(系统层面优化、软件层面优化、SQL层面优化)

inner join 、left join、right join选择

先过滤，再GROUP BY、ORDER BY

       在SQL查询语句中，过滤（Filtering）、分组（Grouping）和排序（Sorting）是常见的操作。正确的顺序应该是先过滤、再分组、最后排序。

  1. 减少操作的数据量,如果不先过滤数据，查询引擎可能需要对整个数据集进行聚合操作，这会消耗大量的时间和计算资源。
  2. 提高查询效率,如果不先过滤数据，查询引擎需要对大量无用的数据进行排序或分组操作，这会降低查询效率。
  3. 优化查询计划，查询引擎可以通过优化查询计划，选择更高效的执行计划来执行查询。如果不先过滤数据，查询引擎可能无法有效地优化查询计划，导致查询效率低下。

查询时对null数据处理

       使用 IS NULL 或 IS NOT NULL 进行判断：在 WHERE 子句中使用 IS NULL 或 IS NOT NULL 来判断某个列是否为 NULL 值，可以避免使用等于号（=）进行判断时可能出现的索引失效情况。

       使用 COALESCE 函数替换 NULL 值：在 SELECT 子句中使用 COALESCE 函数可以将 NULL 值替换为其他指定的值，例如：

#如果 column_name 列的值为 NULL，则查询结果中将显示 'default_value'。
SELECT COALESCE(column_name, 'default_value') AS column_alias FROM table_name;

       使用 IFNULL 或 NVL 函数替换 NULL 值：在一些数据库中，也可以使用 IFNULL 或 NVL 函数来替换 NULL 值，例如：

#如果 column_name 列的值为 NULL，则查询结果中将显示 'default_value'。
SELECT IFNULL(column_name, 'default_value') AS column_alias FROM table_name;
  1. 在索引中使用 IS NULL 进行查询：对于包含大量 NULL 值的列，可以使用包含 IS NULL 的查询条件来进行索引查询。这样可以避免对 NULL 值进行排序或聚合操作，提高查询效率。

限制返回条数

       有必要时限制返回结果的数量：在查询中使用 LIMIT 语句可以限制返回结果的数量，只拿取所需要的数据，减少数据传输和处理的时间。

好处

明知结果返回一条记录，可以加limit1

       当我们明确知道某个查询的结果只会返回一条记录时，可以添加 LIMIT 1 语句，让MySQL停止游标移动，来提高查询效率和减少资源消耗。这是因为 LIMIT 1 会让 MySQL 在找到第一条匹配记录之后就停止搜索，而不会继续扫描整个表，从而可以更快地返回结果。

分页查询优化

       当进行分页时，MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后放弃前 offset 行，返回 N 行。例如 limit 10000, 20。mysql排序取出10020条数据后，仅返回20条数据，查询和排序的代价都很高。那当 offset 特别大的时候，效率就非常的低下，所以我们要对sql进行改写

使用书签

       用书签记录上次取数据的位置，过滤掉部分数据.

如下面语句

#改进前
SELECT id, name, description FROM film ORDER BY name LIMIT 1000, 10;
#改进后
#name为上次查询后的最大值，注意这种场景只适用于不存在重复值的场景。
SELECT id, name, description FROM film WHERE name > 'begin' ORDER BY name LIMIT 10;
延迟关联

       延迟关联：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据.MySQL 延迟关联（Delayed Join）是一种优化技术，可以提高查询性能。当查询需要关联多个表时，延迟关联可以将一些关联操作推迟到后面的查询中，从而减少关联操作的数量，提高查询性能。

#id是主键值，name上面有索引。这样每次查询的时候，会先从name索引列上找到id值，然后回表，查询到所有的数据。可以看到有很多回表其实是没有必要的。完全可以先从name索引上找到id（注意只查询id是不会回表的，因为非聚集索引上包含的值为索引列值和主键值，相当于从索引上能拿到所有的列值，就没必要再回表了），然后再关联一次表，获取所有的数据
#改进前
SELECT id, name, description FROM film ORDER BY name LIMIT 100,5;
#改进后
SELECT film.id, name, description FROM film 
JOIN (SELECT id from film ORDER BY name LIMIT 100,5) temp
ON film.id = temp.id
倒序查询

       假如查询倒数最后一页，offset可能会非常大

#改进前
SELECT id, name, description FROM film ORDER BY name LIMIT 100000, 10;
#改进后
SELECT id, name, description FROM film ORDER BY name DESC LIMIT 10;

Update优化

使用或者涉及索引字段进行update

       确保涉及到UPDATE语句的列上有索引，这样可以大大减少查询时间。特别是对于更新大表的操作。

使用批量更新

       批量更新可以将多个单独的UPDATE操作合并为一个，从而减少服务器和客户端之间的通信次数。可以使用INSERT … ON DUPLICATE KEY UPDATE语句或使用UPDATE … WHERE语句，同时更新多行数据。

限制更新

避免使用子查询

使用子查询更新数据可能会导致以下问题：

       可以使用 JOIN 或 EXISTS 进行相关的过滤和更新操作，这通常比子查询更高效和可读。

减少触发器和外键

在 MySQL 中进行 UPDATE 操作时，可能会触发与表相关的触发器和外键，这可能会导致以下问题：

  1. 性能问题：触发器和外键需要额外的时间和资源来处理，这可能会导致 UPDATE 操作变得缓慢，并增加数据库的负载。
    • 扩展1:当进行 UPDATE 操作时，MySQL 会检查更新的数据是否涉及到任何触发器或外键。如果存在相关的触发器或外键，MySQL 需要执行额外的逻辑来确保数据的完整性和一致性。这可能会导致额外的时间和资源开销，从而导致 UPDATE 操作变得缓慢。
    • 扩展2:如果表中存在多个触发器和外键，那么在进行 UPDATE 操作时，需要处理多个复杂的操作，从而增加代码的复杂度和难度。这可能会导致数据库负载的增加，影响数据库性能。
  2. 可维护性问题：如果表中存在多个触发器和外键，那么在进行 UPDATE 操作时，可能需要处理多个复杂的操作，从而增加代码的复杂度和难度。
  3. 数据完整性问题：如果 UPDATE 操作中涉及到多个表或外键关系，那么触发器和外键可能会对数据完整性产生影响。如果不正确地处理这些关系，可能会导致数据不一致或损坏。

Insert 优化

批量插入数据

       使用 INSERT INTO … VALUES (…) 语句单条插入数据的效率较低，可以使用 INSERT INTO … VALUES (…), (…), (…) 的语法批量插入多条数据，可以显著提高插入数据的速度。

使用 LOAD DATA INFILE

       如果需要插入大量数据，可以使用 LOAD DATA INFILE 语句从本地文件中快速地加载数据到 MySQL 数据库中。这种方式比使用 INSERT INTO … VALUES (…) 的方式要快得多。

扩展：

以下是使用 LOAD DATA INFILE 的步骤：

  1. 创建表：首先需要创建一个表，用于存储要导入的数据。
  2. 创建文件：创建一个包含要导入数据的文件。文件格式必须与 MySQL 表中列的顺序一致，并使用特定的分隔符将列分开。
  3. 使用 LOAD DATA INFILE：使用 LOAD DATA INFILE 语句将文件中的数据加载到 MySQL 表中。

例如，如果要将数据从名为 data.txt 的文件中加载到名为 mytable 的表中，可以使用以下 SQL 语句：

#指定文件路径
LOAD DATA INFILE 'data.txt'
#指定要插入的表
INTO TABLE mytable
#指定列之间的分隔符
FIELDS TERMINATED BY ',' 
#指定每个列值的引号字符
ENCLOSED BY '"' 
#指定行分隔符
LINES TERMINATED BY '\n'

       使用 LOAD DATA INFILE 时，需要确保 MySQL 用户拥有从指定文件读取数据的权限。如果需要从网络中的文件中读取数据，可以使用 MySQL 客户端的 --local-infile 选项启用从本地加载数据的功能。

大量更新数据时禁用索引

       在进行大量数据插入时，MySQL 可能会花费很多时间来更新索引。如果可以接受数据插入完成后再创建索引，可以使用 ALTER TABLE … DISABLE KEYS 语句禁用索引，等数据插入完成后再重新启用索引。【注意⚠️:当前可以在极少量查询的情况下使用！否则在大数据量下会导致大量慢sql】

使用 REPLACE INTO

        REPLACE INTO 语句与 INSERT INTO 语句的区别在于，如果要插入的数据已经存在，INSERT INTO 语句会产生重复记录错误。而 REPLACE INTO 语句会自动更新原有的数据行，而不是产生错误。

REPLACE INTO 语句在以下情况下非常有用：

  1. 当需要插入新数据时，如果表中已经存在相同的数据，则用新的数据替换原有数据。
  2. 当需要更新一行数据时，如果该行数据不存在，则插入新数据；如果该行数据已经存在，则用新的数据替换原有数据。

大数据量减少自增主键的使用

       如果表中存在自增主键，并且插入的数据量很大，那么每次插入数据时，MySQL 都需要重新计算自增主键的值，这可能会导致插入数据变慢。可以考虑使用其他方式来生成主键，例如使用 UUID 或使用应用程序生成主键。

Delete 优化

推荐使用 TRUNCATE TABLE

       如果您需要删除整个表格中的所有行，则可以使用 TRUNCATE TABLE 命令。这比 DELETE 命令更快，并且可以在不影响系统性能的情况下删除大量数据。

推荐使用 TRUNCATE TABLE 的原因有以下几点：

  1. TRUNCATE TABLE 比 DELETE 语句更快。这是因为 TRUNCATE TABLE 在删除数据时不会记录每一行的删除操作，而是将整个表格直接清空，从而避免了一些额外的开销和日志记录。对于大型表格，TRUNCATE TABLE 可以显著提高清空数据的速度。
  2. TRUNCATE TABLE 比 DELETE 语句更安全。TRUNCATE TABLE 操作是原子性的，也就是说，在 TRUNCATE TABLE 运行过程中，如果出现故障或错误，整个操作会被回滚到原始状态，从而保证了数据的一致性。相比之下，DELETE 语句需要一行一行地删除数据，如果出现故障或错误，数据可能会被删除一部分，但不会被完全清空。

推荐使用删除标记

       有些场景下需要让某些数据不展示出去，可以使用一个标记来表示这个数据被删除。例如可以使用date_delete字段，里面可以存储删除的时间戳或者0、1等，在查询的时候进行过滤。

标签 , ,