SQL 性能优化梳理

本文主要针对MySQL关系型数据库，从创建时和查询时两个阶段进行性能优化梳理，涵盖基本概念、Schema优化、索引优化、查询优化等内容。

一、基本概念简述

MySQL逻辑架构分为三层：

• 第一层：客户端通过连接服务，将要执行的SQL指令传输过来。
• 第二层：服务器解析并优化SQL，生成最终的执行计划并执行。
• 第三层：存储引擎，负责数据的储存和提取。

数据库通过锁机制解决并发场景，主要锁类型包括：

• 共享锁（读锁）：不阻塞，多个客户端可同时读取同一资源。
• 排他锁（写锁）：排他，阻塞其他读锁和写锁。
• 乐观锁：适用于数据竞争不激烈场景，通过版本号和时间戳实现。
• 悲观锁：适用于数据竞争激烈场景，每次操作锁定数据。
• 表锁：锁定整张表，开销小但加剧锁竞争。
• 行锁：锁定行级别，开销大但支持高并发。
• MVCC（多版本并发控制）：行级锁变种，通过保存数据快照避免加锁，降低开销。

事务保证一组原子性操作，要么全部成功，要么全部失败。MySQL采用自动提交模式，四种常见隔离级别：

• 未提交读（Read UnCommitted）：事务未提交修改对其他事务可见，可能脏读。
• 提交读（Read Committed）：事务只能看见已提交修改，可能不可重复读。
• 可重复读（Repeatable Read）：同一事务多次读取结果相同。
• 可串行化（Serializable）：最高隔离级别，强制事务串行执行。

• InnoDB：最常用，支持事务和行级锁，具有高性能和自动崩溃恢复特性。
• MyISAM：不支持事务和行级锁，崩溃后无法安全恢复。

二、创建时优化

• 整数类型：TinyInt（8位）、SmallInt（16位）、MediumInt（24位）、Int（32位）、BigInt（64位），使用Unsigned提高正数上限。
• 实数类型：

  Float、Double：支持近似浮点运算。

  Decimal：存储精确小数。

• 字符串类型：

  VarChar：变长字符串，需额外字节记录长度。

  Char：定长字符串，适合固定长度数据（如MD5值）。

  Blob、Text：存储大数据，分别采用二进制和字符方式。

• 时间类型：

  DateTime：保存大范围值，占8字节。

  TimeStamp：推荐使用，与UNIX时间戳相同，占4字节。

优化建议：

• 使用对应数据类型，避免用字符串存储时间，用整型存储IP。
• 选择更小数据类型，能用TinyInt不用Int。
• 标识列建议使用整型，不推荐字符串类型。
• 避免ORM系统自动生成的Schema，通常存在数据类型不合理、索引利用不当等问题。
• 真实场景中混用范式和反范式，平衡查询效率与插入更新效率。
• 创建独立汇总表/缓存表，定时生成数据，用于耗时操作。
• 数据迁移时使用影子表方式，修改原表名保存历史数据。

• B-Tree索引：

  优势：减少查询扫描数据量，避免排序和临时表，将随机IO变为顺序Io。

  限制：非最左列查询无法使用索引，不能跳过索引列，范围查询右边列无法使用索引。

• 哈希索引：

  优势：精确匹配所有列时查询有效。

  限制：无法排序，不支持部分匹配，仅支持等值查询。

优化建议：

• 注意索引适用范围和限制。
• 避免索引列作为表达式或函数参数。
• 针对长字符串使用前缀索引，选择合适前缀长度。
• 多列索引通过AND和OR语法连接。
• 避免重复索引，如（A，B）和（A）重复。
• 索引在where条件和group by查询中特别有效。
• 将范围查询放在条件查询最后。
• 避免索引列过长或为null。

三、查询时优化

• 响应时间：包括服务时间和排队时间。
• 扫描的行：查询过程中扫描的行数。
• 返回的行：查询结果返回的行数。

• 避免查询无关列：如避免使用Select *返回所有列。
• 避免查询无关行：精确指定查询条件，减少扫描行数。
• 切分查询：将大任务分解为多次小查询，分时执行，释放服务器资源。
• 分解关联查询：将多表关联查询分解为单表多次查询，减少锁竞争，提高查询效率。
• 注意count操作：统计总行数使用count(*)，避免count(列名)因列值为null导致统计不准确。
• group by优化：按标识列分组效率高，分组结果避免出现分组列之外的列。
• 关联查询延迟关联：根据查询条件缩小范围后再关联。
• Limit分页优化：通过索引覆盖扫描再关联查询其他列，示例如下：

• Union查询优化：默认去重，非业务必须时使用Union All提高效率。

四、补充内容

条件中的字段类型和表结构类型不一致时，MySQL会自动加转换函数，导致索引作为函数参数失效。

like查询以%开头无法命中索引。

• generated column：列由其他列计算而得，示例如下：

结果：

• JSON格式数据支持：提供相关内置函数，示例如下：

通过explain分析查询执行计划，关键字段说明：

• select_type：查询类型，包括simple、primary、union、dependent union。
• type：访问类型，包括system、const、ref、eq_ref、all、index、range。
• possible_keys：可能帮助查询的索引。
• key：选择使用的索引。
• key_len：使用的索引长度。
• rows：扫描的行数，值越大性能越差。
• extra：额外信息，包括Only index、where used、Using filesort、Using temporary。

示例：

结果分析：

通过以上优化策略，可有效提升MySQL数据库的性能和查询效率。