ReaderMX.
37DATA

SQL优化小技巧

3殷丽文约 8 分钟

1. 查询SQL尽量不要使用SELECT *，而是SELECT具体字段

反例：SELECT * FROM table_name;

正例：SELECT column_name1，column_name2  FROM table_name;

理由：

  • 只取需要的字段，节省资源、减少网络开销。

  • select * 进行查询时，很可能就不会使用到覆盖索引了，就会造成回表查询。

2. 如果知道查询结果只有一条或者只要最大/最小一条记录，建议用LIMIT 1

假设现在有employee员工表，要找出一个名字叫‘张三’的人.

反例：select id，name from employee where name='张三'

正例：select id，name from employee where name='张三' limit 1;

理由：

  • 加上limit 1后，只要找到了对应的一条记录，就不会继续向下扫描了，效率将会大大提高。

  • 当然，如果name是唯一索引（Primary Key / Unique）的话，就没必要加上limit 1了，因为limit的存在主要就是为了防止全表扫描，从而提高性能，如果一个语句本身可以预知不用全表扫描，有没有limit ，性能的差别并不大。

3. 应尽量避免在where子句中使用or来连接条件

假设现在需要查询id为1或者年龄为28岁的用户，很容易有以下sql

反例：select * fromuserwhere id=1 or age =28

正例：//使用unionall

select * fromuserwhere id=1 

unionall

select * fromuserwhere age = 18

或者分开两条sql 

理由：

  • 使用or可能会使索引失效，从而全表扫描。

4、优化limit分页

日常做分页需求时，一般会用 limit 实现，但是当偏移量特别大的时候，查询效率就变得低下。

反例：select id，name，age from employee limit 10000，10

正例：

//方案一 ：返回上次查询的最大记录(偏移量)

select id，name from employee where id>10000 limit 10.

//方案二：order by + 索引

select id，name from employee order by id  limit 10000，10

//方案三：在业务允许的情况下限制页数

理由：

  • 当偏移量最大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。

  • 如果使用优化方案一，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。

  • 方案二使用order by+索引，也是可以提高查询效率的。

5、优化like语句

日常开发中，如果用到模糊关键字查询，很容易想到like，但是like很可能让你的索引失效。

反例：select userId，name fromuserwhere userId like'%123';

正例：select userId，name fromuserwhere userId like'123%';

理由：

  • like ’%字符串‘ 、like ’%字符串‘%    %开头无法使用范围查询，会引起全表扫描导致索引失效；

  • like ’%字符串%‘ %放结尾 会优先使用范围查询

6、尽量避免在索引列上做任何操作(计算、函数(自动or手动)类型转换等mysql内置函数)，会导致索引失效而转向全表扫描

要查询9月份的数据(DATE字段加了索引)

反例：explain   selectCAMPAIGN_IDfromgoogle_campaign_metrics where DATE_FORMAT(DATE, '%Y-%m') = '2022-09';

正例：explain   selectCAMPAIGN_IDfromgoogle_campaign_metrics where DATE >= '2022-09-01' and DATE <= '2022-09-30';

理由：索引列上使用mysql的内置函数，索引失效

7、连表时优先使用inner join，如果是left Join ,左边表结果尽量选小的（小表驱动大表）

  • Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集

  • left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

  • right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

8、mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描，应尽量减少使用

9、最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左列开始并且不跳过索引中的列（最佳左前缀法则：带头大哥不能死、中间兄弟不能断）

示例：

Image

理由：

  • 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

  • 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的

10、对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引，尽量避免全表扫描

反例：

for(User u :list){

INSERT into user(name,age) values(#name#,#age#)

}

正例：

//一次500批量插入，分批进行insert into user(name,age) values<foreach collection="list" item="item" index="index" separator=",">(#{item.name},#{item.age})</foreach>

INSERT INTO user (userId, name, age) VALUES (1, 'Lisa', 12), (2, 'Alice', 18), (3, 'Angela', 18)

理由：

批量插入性能好，更加省时间。

（假如你需要搬100个箱子到楼顶,你有一个电梯,电梯一次可以放适量的箱子（最多放20）,你可以选择一次运送一箱,也可以一次运送20箱,你觉得哪个时间消耗大?）

11、如果数据量较大，优化你的修改/删除语句。

理由：

    • 避免同时修改或删除过多数据，因为会造成cpu利用率过高，从而影响别人对数据库的访问。

    • 一次性删除太多数据，可能会有lock wait timeout exceed的错误，所以建议分批操作。

12、不要有超过5个以上的表连接

  • 连表越多，编译的时间和开销也就越大。

  • 把连接表拆开成较小的几个执行，可读性更高。

  • 如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了

13、慎用DISTINCT关键字

  • distinct 关键字一般用来过滤重复记录，以返回不重复的记录。在查询一个字段或者很少字段的情况下使用时，给查询带来优化效果。但是在字段很多的时候使用，却会大大降低查询效率。

  • 带distinct的语句cpu时间和占用时间都高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较，过滤的过程会占用系统资源，cpu时间。

14、尽可能使用varchar/nvarchar 代替 char/nchar

  • 因为首先变长字段存储空间小，可以节省存储空间。

  • 其次对于查询来说，在一个相对较小的字段内搜索，效率更高（此时就涉及到varchar和char的区别了）

15、如果字段类型是字符串，where时一定用引号括起来，否则索引失效

不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。

16、合理使用索引，索引不宜太多，一般5个以内

  • 索引并不是越多越好，索引虽然提高了查询的效率，但是也降低了插入和更新的效率

  • insert或update时有可能会重建索引，所以建索引需要慎重考虑，视具体情况来定

  • 一个表的索引数最好不要超过5个，若太多需要考虑一些索引的合理性

那些情况需要建索引？

  • 主键自动建立唯一索引

  • 频繁作为查询条件的字段应该创建索引；

  • 查询中与其他表关联的字段，外键关系建立索引；

  • 频繁更新的字段不适合创建索引(因为每次更新不单单是更新了记录还会更新索引)；

  • where条件里用不到的字段不创建索引；

  • 单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)；

  • 查询中排序的字段，排序字段如通过索引去访问将大大提高排序速度；

  • 查询中统计或者分组字段

哪些情况不需要建索引？

  • 表记录太少；

  • 经常增删改的表（why:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，    因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件）；

  • 数据重复且分部平均的表字段，因此应该只为经常查询的和最经常排序的数据列建立索引，注意，如果某个数据列包含许多重复的内容，为他建立索引就没有太大的实际效果。

17、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少适用select *

覆盖索引能够使得你的SQL语句不需要回表，仅仅访问索引就能够得到所有需要的数据，大大提高了查询效率。

18、使用explain 分析SQL

在日常工作中，我们有时会遇到查询特别慢，SQL执行比较久的情况，此时我们可以使用 explain 这个命令来分析查看 SQL 语句的执行，查看该 SQL 是否使用上了索引，尽可能通过分析统计信息和调整query的写法来达到选择合适索引目的。

type访问类型排序（从最好到最差依次是）

  • system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL，

  • 常见类型排序：system>const>eq_ref>reg>range>index>ALL;

一般来说，得保证查询至少达到range级别，最好能达到ref;

常见类型解释

  • system：表只有一行记录（等于系统表），是const类型的特例，平时不会出现，可以忽略不计

  • const：表示通过索引一次就找到了。用于比较主键或唯一索引。如将主键置于where中，mysql就能将查询转换为一个常量

  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

  • ref：非唯一性索引扫描，返回匹配某个值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能找到多个符合条件的行，所以它应该属于查找和扫描的混合体

  • range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引比权标扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

  • index：全索引扫描，index与All区别为index类型只遍历索引树。这通常比All快，因为索引文件通常比数据文件小。

  • All：全表扫描

KEEP THE THREAD GOING

更多来源文章