MySQL优化建议

• MySQL优化建议

MySQL优化建议

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

理由：

• 只取需要的字段，节省资源、减少网络开销。
  
• select * 进行查询时，很可能就不会使用到覆盖索引了，就会造成回表查询。
  

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

理由：

• 加上limit 1后,只要找到了对应的一条记录,就不会继续向下扫描了,效率将会大大提高。
• 当然，如果name是唯一索引的话，是不必要加上limit 1了，因为limit的存在主要就是为了防止全表扫描，从而提高性能,如果一个语句本身可以预知不用全表扫描，有没有limit ，性能的差别并不大。

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

理由：

• 使用or可能会使索引失效，从而全表扫描。 对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并 如果它一开始就走全表扫描，直接一遍扫描就完事。mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效，看起来也合情合理。

4.优化limit分页

理由：

• 当偏移量最大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。
• 如果使用优化方案一，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。
• 方案二使用order by+索引，也是可以提高查询效率的。
• 方案三的话，建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。

5.优化你的like语句

6.使用where条件限定要查询的数据，避免返回多余的行

7.尽量避免在索引列上使用mysql的内置函数

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫

9.Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小

• Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集
• left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。
• right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

都满足SQL需求的前提下，推荐优先使用Inner join（内连接），如果要使用left join，左边表数据结果尽量小，如果有条件的尽量放到左边处理。

10.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

11.使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。

理由：

• 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。
• 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的。

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

13.如果插入数据过多，考虑批量插入。

14.在适当的时候，使用覆盖索引。

15.慎用distinct关键字

16.删除冗余和重复索引

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

18.where子句中考虑使用默认值代替null。

• 如果mysql优化器发现，走索引比不走索引成本还要高，肯定会放弃索引，这些条件!=，>is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃的。
• 如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思会相对清晰一点。

19.不要有超过5个以上的表连接

• 连表越多，编译的时间和开销也就越大。
• 把连接表拆开成较小的几个执行，可读性更高。
• 如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了。

20.exist & in的合理利用

21.尽量用 union all 替换 union

22.索引不宜太多，一般5个以内。

• 索引并不是越多越好，索引虽然提高了查询的效率，但是也降低了插入和更新的效率。
• insert或update时有可能会重建索引，所以建索引需要慎重考虑，视具体情况来定。
• 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否没有存在的必要。

23.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型

24.索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段。

• 因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

25.尽量避免向客户端返回过多数据量。

26.当在SQL语句中连接多个表时,请使用表的别名，并把别名前缀于每一列上，这样语义更加清晰。

27.尽可能使用varchar/nvarchar 代替 char/nchar。

28.为了提高group by 语句的效率，可以在执行到该语句前，把不需要的记录过滤掉。

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

30.使用explain 分析你SQL的计划