数据库优化常用技巧【面试】

常见需要优化场景

某个查询超时，比如要进行搜索或者是展现某个table显示超时或者很慢

排查定位方法

• 首先要确定开启了慢查询，可自行搜索开启（有永久和临时两个方式）
• 查看日志，例如：

  # Time: 2025-05-27T15:01:34.892085Z
  # User@Host: root[root] @ localhost []  Id:     8
  # Query_time: 2.276437  Lock_time: 0.000138 Rows_sent: 165346  Rows_examined: 9900000 Thread_id: 8 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 4848540 Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 9900000 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2022-12-14T15:01:30.906448Z End: 2025-05-27T15:01:34.892085Z Schema: slow Rows_affected: 0
  # Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
  # InnoDB_trx_id: 0
  # Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
  # Filesort: No  Filesort_on_disk: No  Merge_passes: 0
  #   InnoDB_IO_r_ops: 27606  InnoDB_IO_r_bytes: 452296704  InnoDB_IO_r_wait: 0.220474
  #   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
  #   InnoDB_pages_distinct: 8191
  

  从日志中可以得到如下
  1.这个SQL的耗时2.276437 秒。
  2.返回结果有165346行，总共需要扫描9900000行数据。如果扫描行数很多，但返回行数很少，说明该SQL效率很低，可能索引不当。(常用!这时候要考虑使用索引优化)
  3.Read_* 等几个指标表示这个SQL读记录的方式，是否顺序读、随机读等。
  4.Sort_* 等几个指标表示该SQL是否产生了排序，及其代价。优化思路也是调整sql语句。
  5.tmp 等几个指标表示该SQL是否产生临时表，及其代价。如果有且代价较大，需要想办法优化。
  6.Full_scan/Full_join表示是否产生了全表扫描或全表JOIN，如果有且SQL耗时较大，需要调整sql语句，思路可以为：先筛选再进行表连接。
  7.InnoDB_IO_* 等几个指标表示InnoDB逻辑读相关数据。
  8.InnoDB_rec_lock_wait 表示是否有行锁等待。
  9.InnoDB_queue_wait 表示是否有排队等待。
  10.InnoDB_pages_distinct 表示该SQL总共读取了多少个InnoDB page，是个非常重要的指标。

  索引优化

  通过日志可判断出是否存在索引失效的情况，即：例如在加了索引，但是查询的时候，还是返回全表查询的条数，和没加索引的用时一样，说明索引失效。

  补充索引知识 索引类型

  普通索引：最常见的索引类型，允许重复值和 NULL 值。

  ALTER TABLE table_name ADD INDEX index_name (column);
  唯一索引：索引列的值必须唯一，但允许 NULL 值。

  ALTER TABLE table_name ADD UNIQUE (column);
  主键索引：一种特殊的唯一索引，不允许 NULL 值，一个表只能有一个主键。

  ALTER TABLE table_name ADD PRIMARY KEY (column);
  复合索引：在多个列上创建的索引，查询时必须包含第一个字段。

  ALTER TABLE table_name ADD INDEX index_name (column1, column2);
  全文索引：用于快速检索大文本数据中的关键字，仅适用于 CHAR、VARCHAR 和 TEXT 类型。

  ALTER TABLE table_name ADD FULLTEXT index_name (column);
  聚集索引：主键索引的叶子节点存储整行数据，一个表只能有一个聚集索引。

  非聚集索引：索引的叶子节点存储主键的值，一个表可以有多个非聚集索引。

  可以对表进行如下操作进行排查： 1、查询表上已加的索引：

  SHOW INDEX FROM table_name;
  

  2、看索引是否生效，可通过explain+语句，例如：

  explain select * from actor;
  

  
  相关字段含义可参见：
  explain相关字段含义
  3、结合了explain和日志分析后，如果发现索引未生效，则要添加相关索引，或者调整失效索引（例如该联合索引不符合左前缀原则等）

  表优化

• 小表（行数小的表）作为驱动表，大表作为被驱动表，并在大表的连接字段上创建索引
• 例如：

  select name from user left join order on user.id=order.user_id
  

  在user_id字段加上索引

  子查询优化

• 用join代替in，将复杂的连接拆分成子查询，减少中间结果集
• 例如：

  select name from user where id in (select id from order )
  变成=》
  select name from user join order on user.id=order.id
  

  后续待更新……