关于mysql查询优化的建议和方法

一 数据库表设计

1.表设计时遵循三大范式

（1）数据表的每一列都要保持它的原子特性，也就是列不能再被分割。

（2）数据表的每一列都和主键相关。

（3）数据表的每一列都和主键直接相关，而不是间接相关。

2.使用索引

（1）为经常查询的列或排序的列和WHERE子句中的条件列建立索引。（索引会自动排序）

（2）考虑使用复合索引来满足多个列的查询需求。

（3）索引的开销很大，维护麻烦，因此需要避免过度索引。

3.避免索引失效

（1）确保WHERE子句中的条件能够利用索引。尽量避免在索引列上使用函数或表达式，这会导致索引失效。例如，如果有一个索引在列A上，那么查询条件应该直接引用列A，而不是列A上的函数或表达式。

（2）尽量避免使用OR运算符。在某些情况下，使用OR会使索引失效。可以考虑将查询拆分成多个查询，然后在应用程序中进行结果合并。

（3）确保索引列的数据类型与查询条件中的数据类型匹配。如果数据类型不匹配，MySQL可能会无法使用索引。同时，避免在索引列上进行隐式类型转换也是为了使索引列数据类型和查询条件中的数据类型相匹配。

（4）定期检查并优化索引。随着数据的变化和查询的增加，索引可能会变得不再高效。可以使用工具如EXPLAIN来检查查询的执行计划。

（5）复合索引在使用时遵循最左列原则。例如索引A，B。在使用时，查询语句where中 A在前面，B在后面。

（6）数据量不大的表，可以考虑不使用索引。因为mysql可能会认为全文检索更快。

（7）模糊查询时避免使用%开头，这种查询无法有效的使用索引，可能会导致全表扫描。

4.查询语句的优化

（1）在查询时，只返回需要的字段，避免返回多余的数据。

（2）表关联查询时，小表驱动大表（简单的说就是，左关联时数据量少的表放在前面，也就是左边，这样可以减少扫描的数据量，提高查询效率）。遵循阿里巴巴规范，关联表不能超过三个。尽量避免使用子查询。

（3）如果不需要返回全部数据，使用limit分页。返回的数据越少越好。（业务上分页的时候，避免全部查出来再分页。最好是一次翻页查询一次，每次只返回十条数据。）

（4）表关联查询时，尽量使用INNER JOIN 代替 LEFT JOIN，如果业务允许的话。

（5）多条数据插入或更新的时候，在mybatis中使用批量新增或批量修改。也可以使用replace into或insert into on duplicate key update（都需要配合主键或唯一索引使用）。这里推荐使用insert into on duplicate key update。因为replace into在走编辑逻辑时是先删除再新增，可能会导致某些字段变成缺省值，从而导致出现问题。

5.业务代码优化

（1）避免在循环语句中的访问数据库操作。（多次io会降低效率）

（2）在业务允许的范围内尽可能的减少数据库的访问，查询数据库次数越少越好。