七，SQL优化

normaling
97 阅读7分钟

一，Insert优化

1.1 SQL语句优化

  1. 批量插入（Bulk Insert）

    INSERT INTO table (col1, col2) VALUES (v1, v2), (v3, v4), ...;
    • 单次插入多条数据（建议500~2000条/批）
    • 减少网络传输和SQL解析开销

  2. LOAD DATA INFILE

    LOAD DATA INFILE '/path/data.csv' INTO TABLE table;
    • 比普通INSERT快20倍以上
    • 绕过SQL解析层直接导入

  3. 禁用自动提交

    START TRANSACTION;
INSERT...;
INSERT...;
COMMIT;
    • 将多个INSERT包裹在事务中
    • 减少磁盘刷写次数

1.2 存储引擎调优

InnoDB优化：

  1. 配置参数调整：

    innodb_buffer_pool_size = 系统内存的70-80%
innodb_flush_log_at_trx_commit = 2  # 批量刷盘
innodb_log_file_size = 4G          # 更大的redo日志
innodb_autoinc_lock_mode = 2       # 交错自增锁模式

  2. 页压缩：

    CREATE TABLE ... ROW_FORMAT=COMPRESSED;

MyISAM适用场景：

  • 只用于插入密集型、非事务需求场景

  • 配置调整：

    concurrent_insert = 2      # 允许并发插入
bulk_insert_buffer_size = 256M

二，主键优化

2.1 索引组织表(IOT)

在innodb引擎中表数据是根据主键顺序存放的。这种存储关系叫索引组织表(IOT) image 如上图，就是以主键顺序存放的索引组织表。

  • 叶子节点存放数据，非叶子节点起到索引的作用。
  • 这些黄色的块全部是由逻辑层次结构页（page）组成的。
  • 页是innodb磁盘管理的最小单元，最大16k
  • 页内数据根据主键顺序插入

2.2 页分裂

页可以为空，可以存一半，可以充满，每一个页知识包含2行数据，如果某一行数据过大会产生行溢出现象。

2.2.1 主键顺序插入的流程图

  1. 插入id=1的数据 image

  2. 插入id=2的数据image

  3. 插入id=3的数据image

  4. 插入id=4的数据image

  5. 插入id=5的数据image

  6. 插入id=6的数据image

  7. 插入id=7的数据image

  8. 插入id=8的数据image

  9. 当第一个数据页写满后，想插入9就先申请第二个页并插入9,且将他们之间维护一个双向指针image

  10. 将第二页写满 image

2.2.2 主键乱序插入的流程图

  1. 假定已经存放如下数据image

  2. 这时我们想插入一条id=50的数据,本应该插入到47和55之间，但是他们已经满了。

    故先开辟新的数据页3，因为在第一页中23和47占了页的一半，故移动23和47到新的页3去，然后将50插入到47后image

  3. 最后重构链表指针image

2.3 页合并

假定当前叶子节点的情况如图image

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

当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%)，InnoDB会开始寻找最靠近的页(前或后)看看是否可以将俩个页合并从而优化空间使用。

最终会变成如图

image

MERGE_THRESHOLD页合并参数的阈值，可以自己设置，在创建表或者索引的时候指定即可。

MERGE_THRESHOLD参数设置过程

  • 创建表的时候设置

    create table 表名(
字段1 类型,
...
字段n 类型
)comment='MERGE_THRESHOLD=45';

  • 修改

    ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=40';

2.4 主键设计原则

  1. 尽量降低主键的长度

    理由：在聚集索引和二级索引中，如果二级索引很多并且，主键索引很长那么会占用很大的空间，搜索的时候会降低大量的磁盘io。

  2. 插入数据的时候尽量使用顺序插入，auto_increment约束

    理由： 如果乱序插入会可能出现页分裂现象

  3. 业务操作的时候避免对主键的修改

三，Order by优化

explain中的extra会出现2种情况在order by中

  1. using filesort

    通过表的扫描或者索引，读取满足条件的数据行，然后在排序缓冲区(sort buffer) 中完成排序操作，所有不是通过索引直接返回排序结构的叫using filesort，需要查出来后额外排序，效率低。

  2. using index

    通过有序索引顺序扫描直接返回有序结构，不需要额外排序操作，效率高。

将using filesort优化成using index的办法：在创建索引的时候规定排序方式

create index 索引名 on 表名(字段1 排序方式,字段2 排序方式...);

注意要点： 记得遵循索引规则

  • 如果无法避免必须使用using filesort，可以增加sort_buffer_size区的大小(默认256kb)

查询sort_buffer_size的大小

show variables like 'sort_buffer_size';

设置sort_buffer_size的大小

set global sort_buffer_size=大小;

四，Group by优化

explain中的extra会出现一种情况说明group by的效率较低

  • using tempory：代表MYSQL使用了临时表

  • 解决办法： 给分组的字段加上索引优化。

五，Limit优化

5.1 基础优化原则

  1. 避免全表扫描

    -- 低效写法（扫描前100010行）
SELECT * FROM orders 
ORDER BY id 
LIMIT 100000, 10;

-- 优化方案：使用覆盖索引
SELECT * FROM orders 
WHERE id > 100000 
ORDER BY id 
LIMIT 10;

  2. 游标分页（Cursor-based Pagination）

    -- 传统分页（性能差）
SELECT * FROM products 
ORDER BY created_at DESC 
LIMIT 10000, 20;

-- 游标分页（记住最后一条的created_at和id）
SELECT * FROM products 
WHERE created_at < '2023-08-01 12:00:00' 
  AND id < 5000 
ORDER BY created_at DESC, id DESC 
LIMIT 20;

5.2 高级优化技巧

  1. 延迟关联（Deferred Join）

    -- 原始查询（全表扫描）
SELECT * FROM users 
WHERE country='CN' 
ORDER BY score DESC 
LIMIT 100000, 10;

-- 优化版本（先查主键）
SELECT users.* 
FROM users 
  JOIN (
    SELECT id 
    FROM users 
    WHERE country='CN' 
    ORDER BY score DESC 
    LIMIT 100000, 10
  ) AS tmp USING(id);

  2. 预计算分页数据

    -- 创建分页元数据表
CREATE TABLE page_metadata (
  page_num INT,
  start_id INT,
  end_id INT,
  PRIMARY KEY (page_num)
);

-- 定期预计算（每页100条）
REPLACE INTO page_metadata
SELECT 
  (ROW_NUMBER() OVER (ORDER BY id)-1)/100 +1 AS page_num,
  MIN(id) AS start_id, 
  MAX(id) AS end_id
FROM orders
GROUP BY (ROW_NUMBER() OVER (ORDER BY id)-1)/100;

-- 使用元数据快速分页
SELECT * FROM orders 
WHERE id BETWEEN 
  (SELECT start_id FROM page_metadata WHERE page_num=1000)
  AND 
  (SELECT end_id FROM page_metadata WHERE page_num=1000);

六，Count优化

在InnoDB存储引擎中他会将表一行行读取并累加，类似与循环++

优化思路： 使用触发器在insert或者delete的时候+1或-1并存储在表中某一字段内即可。

count的几种情况

  • count(*)

    InnoDB引擎不会把字段全部取出而是专门做了优化，不取值直接在服务层按行进行类型。

  • count(1)

    我们所查询的每一条记录都会放一个1进去，然后在服务器层对数据进行累加(如果是1就+1)

  • count(主键)

    InnoDB引擎会遍历整张表，然后把每一行的主键id值取出，返回给服务器层，服务器层拿到后直接开始累加(因为主键不可能为null)

  • count(字段)

    没有not null约束：innoDB引擎会遍历整张表，然后把每一行的字段值取出返回给服务器层判断是否为null

    有not null的约束：InnoDB引擎会遍历整张表，然后把每一行的字段值取出返回给服务器层直接开始累加

从上到下效率依次降低

七，Update优化(避免行锁升级为表锁)

在InnoDB引擎中开启事务后执行update语句他会将数据行锁住即行锁。

事务没提交前行锁不会释放，注意一定要对索引数据进行更新才能避免从行锁升级为表锁。

原因： 在InnoDB引擎中他不是针对记录加的行锁而是根据索引加的行锁，在更新数据的时候如果索引失效那么就会从行锁升级为表锁。

avatar
文章
阅读
粉丝