SQL优化的经验

SQL优化的经验

• 索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引，比如 select id from table where id + 1 = 5 ； mysql 无法解析这个方程式。要将索引列单独的放在比较符号的一侧。

• 将一个大查询分解为多个小查询： 一个大查询一次性完成的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。如果分解成多个小查询，可以将对服务器的压力分散到一个较长的时间段中，从而降低对服务器性能的影响。

• 选择合适的字段类型，选择标准是：尽可能小、尽可能定长、尽可能使用整数。

• 不要直接使用 select *，因为这样会进行全表扫描，应该使用具体的表字段。

• 在查询语句中使用 like 关键字时，如果匹配字符串的第一个字符为 %，比如 like %abc，索引就不会起作用，只有 % 不在第一个位置，索引才会起作用。

• 使用 explain 命令来优化 select 查询。对于效率低的 sql 语句，也可以通过 explain sql 来进行分析和优化。

• 使用多列索引比使用多个单列索引性能更好。

• 当 select 查询语句只需要使用一条记录时，要使用 limit 1。

• 避免在 where 中对字段进行 null 判断，也避免在 where 中使用 >、<、!= 操作符。

• 使用 between and 代替 in。

• 选择合适的搜索引擎（InnoDB、MyISAM）。

• 拆分大的 delete 或 insert 语句。

• 字段设计尽可能使用 not null。

• 进行水平分割和垂直分割。水平分割指将一张表中的数据拆分到多个结构相同的表中，比如将一个用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。垂直分割指将一张表按列切分成多个表，比如将电商数据库垂直切分成商品数据库、用户数据库，这样简化了表的结构，方便维护（但主键会出现冗余）。

---

1. 数据类型优化

• 使用小而且简单的数据类型，因为它们占用较少的空间，操作代价低。
• 尽量避免使用 null 值。
• 尽量使用相同的数据类型存储相似的值。
• 尽量使用整型来定义标识列，因为整型比较快而且可以使用 auto_increment。（整型比较快的原因是一个整型只有 4 个字节，比较小，而且它们之间的比较规则很简单）

2. 索引优化

• 正确的创建和使用索引，而不能随便的滥用索引，比如对于非常小的表，如果采用索引，反而会增大开销，这种情况下采用全表扫描更简单高效。
• 独立的索引列： 索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引，比如 select id from table where id + 1 = 5 ； mysql 无法解析这个方程式。要将索引列单独的放在比较符号的一侧。
• 多列索引： 在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。比如 select film_id, actor_ id from table where actor_id = 1 and film_id = 1;
• 前缀索引： 对于 BLOB、TEXT 或者很长的 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符，这样可以节约索引空间，提高索引效率。前缀长度根据索引的选择性来确定。索引的选择性是指，不重复的索引值和数据表的记录总数的比值，索引的选择性越高，就会过滤掉越多的行，查询效率也就越高。
• 覆盖索引： 尽量使用覆盖索引，因为如果一个索引包含了查询需要的所有列，那么存储引擎只需要访问索引就好了，提高了效率。（mysql 使用 B+ 树索引来实现覆盖索引）

3. 查询性能优化

• 优化数据访问： 方式有两种，第一种是减少请求的数据量，比如只返回必要的列，而不是使用 select * 这种耗时的语句，以及使用 limit 语句来限制返回的数据。另一种是减少服务端扫描的行数，比如使用覆盖索引，服务端只需要访问索引就好了。
• 将一个大查询分解为多个小查询： 一个大查询一次性完成的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。如果分解成多个小查询，可以将对服务器的压力分散到一个较长的时间段中，从而降低对服务器性能的影响。
• 将一个大的关联查询分解成对每个表进行一次单表查询，然后将结果在应用程序中进行关联。这样做的好处是：
  • 让缓存更高效。 对于关联查询，其中一个表发生变化，那么整个查询缓存就无法使用了。而分解后的多个查询，即使其中一个表发生变化，其它表的缓存依然可以使用。
  • 将查询分解后，执行单个查询可以减少锁的竞争。
  • 减少冗余记录的查询。 分解成多个单表查询后，其他查询可以直接使用单表查询的缓存结果，而不用像关联查询那样重复的访问一部分数据。