MySQL数据库优化个人总结

小镇cxy
211 阅读8分钟

数据库优化

数据库优化，为了提高SQL的执行效率，更加快速的完成SQL运行，可以数据库层面和硬件层面两个方面进行优化。 硬件层面造成的瓶颈通常有：磁盘寻道、磁盘读写、CPU周期、内存带宽等等。这里主要是从数据库层进行优化，详情参看官方文档

SQL执行过程

在开始数据库优化之前，先了解SQL查询语句如何执行的，执行SELECT语句时，执行的先后顺序.

  1. SQL 查询语句执行过程

    • 连接器：建立连接，管理连接、校验用户身份

    • 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0已删除

    • 解析SQL：通过解析器对SQL查询语句进行词法分析、语法分析，构建语法树，便于后续模板读取表明、字段、语句类型

    • 执行SQL

      • 预处理阶段：检查表或字段是否存在
      • 优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划
      • 执行阶段：根据执行计划执行SQL查询语句，从存储引擎读取记录

  2. SELECT执行顺序 关键字顺序：

     SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

    SELECT语句的执行顺序

     FROM = JOIN > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

    比如一个SQL语句，它的关键字顺序和执行顺序是下面这样的：

     SELECT DISTINCT player_id, player_name, count(*) as num #顺序5
 FROM player JOIN team ON player.team_id = team.team_id #顺序1
 WHERE height > 1.80 #顺序2
 GROUP BY player.team_id #顺序3
 HAVING num > 2 #顺序4
 ORDER BY num DESC #顺序6
 LIMIT 2 #顺序7

    在SELECT语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入

优化数据库结构

  1. 在建数据表时，先预估数据表的容量，数据表存放的数据量较大，要对数据表进行分区

  2. 优化数据类型

    • 优先使用数字类型的做唯一ID
    • 对于大小小于8KB的列值，使用二进制VARCHAR而不是BLOB

  3. 数据库和表的数量限制

    • MySQL 对数据库的数量没有限制。底层文件系统可能对目录的数量有限制
    • InnoDB最多允许 40 亿个表

  4. 数据表大小限制，InnoDB表，对于大于 1TB 的表，建议将表分区为多个表空间文件

  5. 数据表行数和列数限，InnoDB表

    • 一个表最多可以包含 1017 个列。虚拟生成的列也包含在该限制内
    • 一个表最多可以包含64个 二级索引
    • 行数没有明确要求，但是和数据表“页”相关

  6. 配置缓冲池

优化SQL

优化SQL需要一个过程的优化和判断才能达到预期

  1. 优化SQL语句(// 感觉现在整理的不够 todo)

    • 避免使用select *进行查询
    • 索引优化，建立合适索引，在查询时保证索引生效
    • 连接优化，小表连接大表，连接条件尽可能的缩小表的大小

  2. 建立合适索引，甄别正确的列做二级索引和二级联合索引，合理使用覆盖索引，避免索引失效，MySQL创建索引

     CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...
 ​
 key_part: {col_name [(length)] | (expr)} [ASC | DESC]
 ​
 index_option: {
    KEY_BLOCK_SIZE [=] value
 | index_type
 | WITH PARSER parser_name
 | COMMENT 'string'
 | {VISIBLE | INVISIBLE}
 | ENGINE_ATTRIBUTE [=] 'string'
 | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
 }
 ​
 index_type:
    USING {BTREE | HASH}
 ​
 algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}
 ​
 lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

  3. SQL分析，使用EXPLAIN优化查询，去分析SQL执行日志，日志字段含义解释如下

    • id: 查询的唯一标识符

      • 在单个简单查询中，通常这个值为1。在复杂查询中，如包含子查询、联合查询（UNION）或是多表连接的查询，id的值可以帮助你理解查询的执行顺序和结构
      • 相同的id值表示这些操作是在同一个级别执行的。例如，在JOIN操作中，参与相同JOIN的表会有相同的id值。

    • select_type: 查询的类型（如 SIMPLE, PRIMARY, SUBQUERY 等）

      • SIMPLE 表示一个简单的SELECT（不使用UNION或子查询的情况）
      • PRIMARY 一个复杂的查询中（比如涉及到 UNION 或子查询的情况），最外层的查询被标记为 PRIMARY
      • SUBQUERY 查询中包含子查询时，子查询的 select_type 是 SUBQUERY

    • table: 正在访问的表。

    • type: 表示连接类型（如ALL、index、range等）。

      • system 表只有一行（等同于系统表）。这是可能的最好的 type 值，查询速度非常快
      • const 表示通过索引一次就能找到一行数据，通常用于比较主键或唯一索引的等值查询
      • eq_ref 在使用主键或唯一索引作为连接条件时
      • ref 访问类型只返回匹配某个单个值的行，它使用非唯一或非主键索引
      • fulltext 全文索引
      • unique_subquery 在 IN 子句中使用的子查询将被优化为一个唯一查询
      • index_subquery 类似于 unique_subquery，但用于非唯一索引
      • range 只检索给定范围内的行，使用索引来选择行
      • index 表示全索引扫描，比全表扫描快，但不如 range 类型
      • all 表示全表扫描，这通常是性能最差的情况，应尽可能避免

    • possible_keys: 可能用于查询的索引。

    • key: 实际使用的索引。

    • key_len: 索引使用的字节数。

    • ref: 显示索引如何被使用，如列名或常量。

    • rows: 估计查询需要检查的行数。

    • Extra: 额外的信息（如Using where、Using index等）

      • Using filesort 表明 MySQL 将使用一个外部索引排序，而不是按索引顺序进行读取。这通常发生在 ORDER BY 查询中，指定的排序无法通过索引直接完成
      • Using temporary 表示 MySQL 需要使用临时表来存储结果，这通常发生在排序和分组查询中（例如，含有 GROUP BY、DISTINCT、ORDER BY 或多表 JOIN）
      • Using index 查询能够通过只访问索引来获取所需的数据，无需读取实际的表行。这通常是性能较好的情况
      • Using where 指 MySQL 在存储引擎层面之外进行了额外的过滤

    根据分析报告，主要查看SQL是否按预期走索引获取结果，如果出现嵌套循环等，应该尝试打破嵌套循环。更多查询执行计划报告见MySQL原文

最后引用美团技术团队的一段话，任何数据库层面的优化都抵不上应用系统的优化，同样是MySQL，可以用来支撑Google/FaceBook/Taobao应用，但可能个人网站都撑不住。套用最近比较流行的话：“查询容易，优化不易，且写且珍惜！”

锁

InnoDB Locking

  1. 共享锁与排他锁 (Shared and Exclusive Locks)

    • 共享锁(S锁)：允许其他事务读取数据，但不允许其他事务修改数据
     SELECT * FROM table_name WHERE id = 1 LOCK IN SHARE MODE;
 -- 或
 SELECT * FROM users WHERE id = 1 FOR SHARE;
    • 排他锁(X锁)：允许其他事务读取和修改数据
     SELECT * FROM table_name WHERE id = 1 FOR UPDATE;

  2. 意向锁 (Intention Locks)

    • 意向锁是一种表级锁，用于表示事务将要对表中的某些行进行加锁
     -- 意向锁是表级锁，在获取行锁之前自动设置
 -- 意向共享锁（IS）：表示事务打算给表中的某些行加共享锁
 SELECT * FROM table_name WHERE id = 1 LOCK IN SHARE MODE;
 -- 意向排他锁（IX）：表示事务打算给表中的某些行加排他锁
 SELECT * FROM table_name WHERE id = 1 FOR UPDATE;

  3. 记录锁 (Record Locks)

    • 记录锁是一种行级锁，用于锁定单个行
     SELECT * FROM table_name WHERE id = 1 FOR UPDATE;
 -- 特点：
 -- 锁定单个索引记录
 -- 防止其他事务修改或删除该记录
 -- 必须是精确匹配的查询条件

  4. 间隙锁 (Gap Locks)

    • 间隙锁是一种间隙级锁，用于锁定索引记录之间的间隙
     SELECT * FROM table_name WHERE id BETWEEN 1 AND 10 FOR UPDATE;

    特点：

    • 锁定范围，防止其他事务在范围内插入数据
    • 防止幻读
    • 只在REPEATABLE READ隔离级别下生效

  5. 临键锁 (Next-Key Locks)

    • 临键锁是一种行级锁，用于锁定索引记录及其之间的间隙
     -- Record Lock + Gap Lock
 SELECT * FROM users WHERE age > 20 FOR UPDATE;

    • 特点：

      • 锁定索引记录及其之间的间隙
      • 防止幻读
      • 在REPEATABLE READ隔离级别下生效

  6. 插入意向锁 (Insert Intention Locks)

    • 插入新记录时自动获取

  7. 自增锁 (Auto Increment Locks)

    • 自增锁是一种表级锁，用于确保多个事务对自增列的并发插入操作的正确性
     CREATE TABLE t1 (
   c1 INT(11) NOT NULL AUTO_INCREMENT,
   c2 VARCHAR(10) DEFAULT NULL,
   PRIMARY KEY (c1)
 ) ENGINE=InnoDB;

上述的数据库优化，是结合个人工作经历以及官方文档进行了简单总结，其实数据库的优化是多变，具体问题具体分析，“正确的”使用数据库，去提升查询效率。数据库的锁，主要是为了解决并发操作数据库的问题，这里主要介绍了MySQL提供的锁和简单的使用....

avatar
文章
阅读
粉丝