MySQL 调优

存储引擎的种类

MySQL 中存在多种存储引擎，比如：

  • InnoDB
    • 支持事务；
    • 支持外键；
    • 同时支持行锁和表锁。
    • 适用场景：经常更新的表，存在并发读写或者有事务处理的业务场景。
  • MyISAM
    • 支持表锁（插入更新操作会锁表）；
    • 不支持事务；
    • 拥有较高的插入和查询速度。
    • 适用场景：只读类的数据分析的业务场景。
  • MEMORY
    • 所有数据存储在内存中，读写速度快；因此数据库重启，数据会消失。
    • 适用场景：临时表。

如何选择合适的存储引擎？

  1. 数据一致性要求较高，需要事务，选择 InnoDB
  2. 数据查询多，更新少，对查询性能要求较高，选择 MyISAM
  3. 用于查询的临时表，选择 MEMORY

索引种类

InnoDB 中有 3 中索引类型：

  • 普通索引，没有任何限制
  • 唯一索引，要求键值不能重复，主键索引是特殊的唯一索引，主键索引还要求键值不能为空
  • 全文索引，大文本的场景下，可以用全文索引解决 like 查询效率低的问题，全文索引只有 char、varchar、text 类型的字段才能创建
create table t_user(
 id bigint(13) not null,
 name varchar(20),
 remark varchar(200),
 primary key(id),
 fulltext index(remark) -- 创建全文索引
);

-- 使用全文索引
select * from t_user where match(remark) against('xkcoding' IN NATURAL LANGUAGE MODE);

InnoDB 锁

InnoDB 支持表级别的锁，也支持行级别的锁。锁的粒度，表锁大于行锁；加锁的效率，表锁高于行锁；锁的并发性能，行锁优于表锁。

共享锁

共享锁是个行级别的锁，主要用于读取数据的场景，也叫读锁，不要在获取了读锁之后去写数据，可能会出现死锁的情况，多个事务可以共享一把读锁。

添加读锁：

select * from t_user lock in share mode;

排他锁

排他锁是个行级别的锁，主要用于操作数据的场景，也叫写锁。只要一个事务获取了一行数据的写锁，那么其他事务就不能再获取这一行数据的读锁和写锁了。

添加排他锁：

① 数据库会默认为增删改操作加上排他锁

② 使用 FOR UPDATE 语法

select * from t_user for update;

行锁的原理

InnoDB 的行锁，是通过锁住索引来实现的。如果一个表没有创建索引怎么办？如果定义了主键，InnoDB 会选择主键作为聚集索引。

SQL 优化

sql 优化要从几个方面考虑：sql 语句优化、表结构优化、架构优化。

SQL 语句优化

看执行计划

定位到慢 sql 后，通过 explain 分析 sql 执行计划。

explain 得到的结果，如果存在多条记录，执行顺序会根据 id 从大到小排序，排序靠前的先执行。

执行计划的结果要重点关注的字段有：type、rows、filtered、extra。

type 字段表示连接类型，包括：system > const > eq_ref > ref > range > index > all，一般来说，至少需要达到 range 级别，尽量保证 ref 级别，all 和 index 都是需要进行优化的。

  • all 全表扫描，Full Table Scan。
  • index 索引全扫描，Full Index Scan，结合回表去理解。
  • range 索引范围扫描，常用于 <，<=，>=，between，in 等操作。
  • ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中。
  • eq_ref 类似 ref，区别在于使用的是唯一索引，使用主键的关联查询。
  • const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询。

Extra：

  • Using filesort：MySQL 需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
  • Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化。比如使用了 union。
  • Using index：表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据。
  • Using index condition：MySQL5.6 之后新增的 ICP，using index condtion 就是使用了ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。

SQL 优化场景

1、不要使用select *

首先，数据库解析 * 有时间消耗；其次，使用 * 可能导致索引失效，比如 where 条件是大于，或者 in 等范围条件的时候，如果查询的列只有带索引的列时，执行计划会走索引，否则全表扫描。

2、建索引

索引创建原则：

  • 在用于 where 条件过滤、order by 排序的字段创建索引
  • 在 join 的 on 字段创建索引
  • 索引的个数不要过多，浪费空间，过多的索引还会导致更新变慢
  • 在离散度高的字段创建索引
  • 频繁更新的值，不要作为主键或者索引
  • 联合索引最好将离散度高的字段放在前面，联合索引最左匹配原则
  • 创建联合索引，而不是去修改单列索引
  • 不建议使用无序的值作为索引
  • 字段较长时，可以通过截取字段的前一部分来创建索引，具体截取多少，可以通过计算离散度，找到最合适的长度

索引使用原则：

  • 联合索引最左匹配原则：建立联合索引时，把最常用的字段放在最左边。假设 index(a,b) 建立了 a 和 b 的联合索引，查询条件 where a = ? and b = ? 和 where a = ? 都可以匹配该索引，而 where b = ? 这样子就无法使用当前索引了。

  • 优先使用覆盖索引：非主键索引会先通过索引查询到主键索引，再通过主键索引找到数据，比单独的主键索引的查询多扫描了一颗索引树，这个过程叫做回表。（MySQL 覆盖索引详解）

    如果需要查询的字段从索引中直接可以获取，不需要回表操作，那么此时就用到了覆盖索引。执行计划的 Extra 如果有 Using Index 就表示使用了覆盖索引。假设创建 index(a,b) 我们查询的时候 select b from t where a = ?，这就用到了覆盖索引。需要注意的是 select * 的操作用不到覆盖索引。

索引失效场景：

  • 索引字段使用函数(replace/substr/concat/sum/count/avg)、表达式、或者计算(+ - * /)
  • 隐式转换，比如字符串类型的字段未加单引号
  • like 条件出现%在左，like '%xxx'
  • 反向查询，比如 NOT LIKE 则使用不到索引，但是 <>、NOT IN 在某些情况下可以使用到索引

3、少用 union，尽量用 union all

union 去重是基于临时表，临时表的特性是，如果在 InnoDB 上设置的缓存够大则使用内存来完成临时表的高效处理；如果union 的结果特别大，超出了内存能够承载的范围，MySQL 会自动创建 MyISAM 引擎表，MyISAM 表是在磁盘上处理的， IO 效率变差，造成 sql 执行效率低。如果一定要用 union，可以加 limit 限制结果集数量。

4、大分页

数据量极大的情况下，分页尽量减少偏移量。比如：

-- 大偏移量
select * from t_user limit 900000,10;

-- 修改为先过滤，再 limit
select * from t_user where id >= 900000 limit 10;

5、选择合适的字符类型

6、关联查询时小表驱动大表，外键要加索引

嵌套循环联接（Nest Loop Join）与编程中的二层嵌套类似。外表（驱动表）中的每一条记录与内表（被驱动表）中的记录进行比较判断。

  1. 小表驱动大表：联接查询的执行计划，按上下顺序，第一个是驱动表。驱动表的大小直接决定关联查询时的效率。MySQL 会优先选择小表作为驱动表优先执行，有子查询时会先执行子查询，再执行外层查询。
  2. 外键要加索引：多表关联查询时，只有在外键上也加索引，关联表的索引才能生效。通过建索引能解决大部分的关联查询的效率问题，只要做好单表的数据查询优化，在主外键关联都建索引的情况下，MySQL 优化器会自动选择最优的驱动表，这样执行效率就不会低。如果关联表太多，驱动表的选择可能会有问题，所以一些大厂会建议关联查询的表最好不要超过 3 个。

表结构优化

  • 为不同的业务选择合适的存储引擎，查询插入操作多的业务选择 MyISAM，临时数据选择 MEMORY，并发大更新操作多的业务选择 InnoDB。
  • 尽量选择合适的数据类型和数据长度。
  • 非空字段定义为 NOT NULL，通过提供默认值、特殊值的方式，代替 NULL，减少 NULL 类型的存储。
  • 不要使用外键。
  • 尽量不使用触发器、视图。视图也是查表来的，不能优化查询速度，而且在sql中会与表名混淆，增加复杂度。
  • 不要使用数据库存储二进制文件或者大文件存储，尽量使用文件服务器解决，数据库只存储文件的相对路径。

架构优化

  • 引入缓存服务，如 Redis，降低查询对数据库带来的压力。
  • 分库分表，垂直分库减少并发压力，水平分表解决存储瓶颈。垂直分库是按照业务拆分成不同的数据库。水平分库分表是按照一定规则分不到不同的数据库中。
  • 使用主从复制，从而实现读写分离，可以一定程度减轻数据库访问压力。但是需要注意主从数据一致性问题。

面试题

如何快速往 MySQL 中插入 100W 行的数据？

点击查看答案

先建立一张存储引擎为 MyISAM 的表，往里插入数据，插入完成之后，修改存储引擎为 InnoDB。

主从复制的原理？如何解决主从复制带来的数据一致性问题？

点击查看答案

主从复制原理：从服务器读取主服务器的 binlog 文件，然后执行一遍。

默认情况下，MySQL 主从复制是异步复制，master 不关注 slave 是否写入成功。
如果要保证主从的一致性，可以采用全同步复制，也就是 master 需要等待 slave 写入成功之后，再返回给客户端。这样会导致 master 性能下降。
介于异步复制和全同步复制之间，有一种叫做半同步复制。master 执行完客户端提交的事务后，不立即返回给客户端，而是等待至少一个 slave 接收到 binlog 同时写入到 relay log 才返回给客户端。

如何避免死锁的情况

点击查看答案
  1. 批量操作单表时，先对数据进行排序（避免出现等待环路）
  2. 申请足够级别的锁，如果操作数据，申请排他锁
  3. 尽量使用索引访问数据，避免锁表
  4. 尽量拆分大事务为小事务
  5. 尽量使用等值查询，减少范围查询

参考资料

xkcodingの笔记

posted @ 2023-06-01 17:29  xfcoding  阅读(24)  评论(0)  编辑  收藏  举报