优化性能

1. 批量插入、更新数据
2. 避免使用 select *语句，第一，实际业务场景中，可能我们真正使用的只有其中一两列；第二，select *语句不会走覆盖索引，会出现大量的回表查询，sql 性能低

小表驱动大表

小表驱动大表，即使用小表的数据集驱动大表的数据集

假如有 order 和 user 两张表，其中 order 表有 10000 条数据，而 user 表有 100 条数据。需求：查所有有效的用户下过的订单列表

# in 关键字：优先执行in里面的子查询语句
select * from order
where user_id in (select id from user where status=1)

# exists 关键字
select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)

• in 适用于左边大表，右边小表
• exists 适用于左边小表，右边大表

连接查询代替子查询

子查询语句的优缺点：

• 优点：简单，结构化，适用表数据不多
• 缺点：需要创建临时表，查询完毕之后，再删除这些临时表

# 子查询
select * from order
where user_id in (select id from user where status=1)

# 连接查询
select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1

优化索引

  使用 explain 语句判断 sql 语句有没有走索引，有 type 、key 、key_len 三个属性需要关注

• id：表示 SQL 的执行顺序，从大到小开始执行

  • id 相同时，执行顺序由上至下；
  • 如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行；
  • id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行；

• type：数据的访问类型，表示 MySQL 在表中找到所需行的方式，从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • system：const 连接类型的一种特例，仅有一行满足条件
  • const：当确定最多只会有一行匹配的时候，MySQL 优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入 where 子句时，mysql 把这个查询转为一个常量（高效）
  • eq_ref：最多只返回一条符合条件的记录，使用唯一性索引和主键查找时发生
  • ref：一种索引访问，返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引、或者唯一性索引才会发生
  • range：范围扫描，一个有限制的索引扫描，key 列显示使用了哪个索引
  • index：和全表扫描一样，只是扫描表的时候按照索引次序进行而不是行，避免排序，但是开销还是大
  • all：最坏的情况，全表扫描

• key：显示 MySQL 实际决定使用的键（索引）。如果没有选择索引，键是 NULL

• key_len：显示 MySQL 决定使用的键长度。如果键是 NULL，则长度为 NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好

• rows：预计要检查的行数

• Extra： 包含 MySQL 解决查询的详细信息，也是关键参考项之一。

版权所有

版权归属：haipeng-lin

许可证：署名 4.0 国际 (CC-BY-4.0)