【悄咪咪学MySql】11. SQL优化

别再写JS了吧
215 阅读5分钟

一. 插入数据 INSERT

1. 批量插入

INSERT INTO 表名 VALUES(值1, 值2, ...),(值3, 值4, ...),(值n, 值n+1, ...);

不建议批量插入超过1000条，建议500~1000比较合适

2. 手动事务提交

默认为自动提交，建议手动提交，以减少不必要的事务的申请和提交造成浪费

3. 主键顺序插入

建议按主键升序排序后插入，AUTO_INCREMENT也行

4. 大批量插入数据

如果一次性需要插入大批量数据，使用 INSERT 语句插入性能较低，此时可以使用 MySQL 数据库提供的 load 指令进行插入。

操作如下：

# 客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u 用户名 -p

# 设置全局参数 local_infile为 1，开启从本地加载文件导入数据的开关
SET GLOBAL LOCAL_INFILE = 1;

# 执行 load 指令将准备好的数据，加载到表结构中
LOAD DATA LOCAL INFILE '/本地文件地址' INTO TABLE `表名` FIELDS TERMINATED BY '分隔符' LINES TERMINATED BY '\n';

二. 主键优化 PRIMARY

1. 数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）

1.1 页分裂

页可以为空，也可以填充一半，也可以填充满。每个页包含2~N行数据，根据主键排列。

主键乱序插入时，很容易导致页分裂

1.2 页合并

当删除一行记录时，实际上记录并没有被物理删除，只是被标记为删除状态，并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到 MERGE_THRESHOLD（合并阈值：默认为页的50%）时，InnoDB会开始寻找相邻页，看看是否可以将两个页合并，以优化空间使用。

MERGE_THRESHOLD：合并阈值，可以自己设置，在创建表或创建索引时指定

2. 主键设计原则

  • 在满足业务需求的情况下，尽量降低主键长度（所有二级索引都储存主键，如果主键过长，会导致浪费）
  • 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键
  • 尽量不要使用UUID做主键或者其他自然主键，如身份证号（太长且无顺序）
  • 业务操作时，避免对主键修改（避免改变InnoDB物理存储顺序）

三. 排序优化 ORDER BY

1. 排序方式

  1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
  2. Using index：通过有序索引，顺序扫描，直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

2. 优化策略

为优化排序建立索引时：

  • 需要根据排序顺序建立索引顺序，否则会由于先后顺序不同，违反最左前缀法则，导致出现 using filesort 排序方式。
  • 出现多个排序字段时，需根据排序方向（升降序）建立联合索引的排序方向（Collation，默认ASC），否则会由于排序方向不同，不能使用 Using index 排序方式。
    • 默认排序方向（如AA）也可用于 DD 的排序优化
  • 必须覆盖索引，一旦出现回表，必定是 using filesort 方式
  • 如果不可避免地出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size （默认256k）
    • 否则会使用磁盘空间进行排序，这时候效率最低

四. 分组优化 GROUP BY

1. 分组方式

  1. Using temporary：通过临时表分组，需要进行IO，效率较低
  2. Using index：通过索引分组，效率较高

2. 优化策略

  • 也需要遵守最左前缀法则
    • 最左前缀如果在 WHERE 条件中，GROUP BY为其下一个索引字段，也能符合最左前缀法则

五. 分页优化 Limit

一个常见地问题是 limit 2000000,10时，需要MySQL排序前 2000010条记录，仅仅返回2000001~2000010的记录，其他记录没有作用，造成浪费，导致排序代价非常大

1. 优化策略

  • 使用覆盖索引 + 子查询
SELECT S.*
FROM
    表A A,
    (
    SELECT ID 
    FROM
        表A
    ORDER BY 
        ID
    LIMIT 9000000,10
    ) B 
WHERE
    A.ID = B.ID;
  • 添加过滤标识 降低逻辑分页数（大厂在用）
    • 第一页翻页到第二页时，传递第一页最后一条数据的排序参数，在上面的例子中为 ID
    • 根据前端传递的这个ID，添加条件，以减少分页数
SELECT 
    字段列表
FROM
    表A
WHERE
    ID [>|<] 前端返回的ID # 选择大于还是小于根据排序方向决定
ORDER BY
    ID
LIMIT 0,10

六. 计数优化 COUNT

  • MyISAM引擎把一个表的总行数卸载了磁盘上，因此执行 COUNT(*) 时会直接返回这个数，效率很高
  • InnoDB引擎需要把数据一行一行从引擎里读出来，然后累积计数

1. COUNT的几种用法

在InnoDB中：

  • COUNT(*)
    • 不会把全部字段取出来，做了优化，不取值，服务层直接累加
  • COUNT(主键)
    • 遍历整张表，把每一行的主键取出来返回服务层，服务层哪到主键后直接累加(主键不可能为NULL)
  • COUNT(字段)
    • 没有NOT NULL约束，遍历取出每一行的字段值，返回服务层，服务层判断 IS NULL，不为NULL时计数累加
    • 有NOT NULL约束：同 COUNT(*)
  • COUNT(1)
    • 遍历整表，不取值，返回1给服务层，服务层判断 1 IS NULL，然后计算累加

2. 优化策略

  • COUNT 在 InnoDB 中，不同的用法效率为 COUNT(字段) < COUNT(主键) < COUNT(1) ≈ COUNT(*)
    • 尽量用 COUNT(*)
  • 自己维护业务，自己计数，存在 Memory 或 redis 中

七. 更新优化 UPDATE

InnoDB 中支持行锁，但使用行锁需要在该操作下有索引（即 WHERE 条件时全使用索引），否则使用表锁。

1. 优化策略

  • 避免行锁升级成表锁
    • 根据索引修改，而且该索引不能失效
avatar
文章
阅读
粉丝