关于数据库优化，网上有很多资料和方法，但是很多质量参差不齐，有些总结的不够，内容比较冗杂。下面这篇文章主要介绍19种常用于MySQL优化的有效方法，有需要的朋友可以参考一下。

:

目录

1.解释2。SQL语句中IN的值不能太多。3.SELECT语句必须指示字段名称。4.当只需要一条数据时，使用limit 15。如果排序字段没有索引，尽量少排序。6.如果限制条件中的其他字段没有索引，尽量少用or7，用union all代替union8，不要用ORDER BY RAND()9，区分in和exists，not in和not exists10，使用合理的分页方法提高分页效率。11.分段查询。12.避免判断where子句中字段的空值。13.不建议使用%前缀进行模糊查询。14.避免在where子句中对字段进行表达式操作。15.避免隐式类型转换。16.对于联合索引，请遵守最左边的前缀规则。17.如有必要，可以使用force index强制查询转到某个索引。18.注意范围查询语句。19.连接优化综述。在本文中，我们将谈谈项目中常用的19种MySQL优化方法，具体如下：

1、EXPLAIN

优化MySQL，要利用好EXPLAIN来检查SQL执行计划。

这里有一个简单的例子，标记(1，2，3，4，5)我们想要关注的数据：

type列，连接类型。一个好的SQL语句至少要达到范围级别。消除所有级别。使用了key列，索引名称。如果没有选择索引，则该值为空。可以采用强制索引。key_len列，索引长度。rows列，条扫描线。这个值是估计值。extra列，详细描述。注意常见的不友好值如下：使用filesort，使用temporary。

2、SQL语句中IN包含的值不应过多

MySQL IN做了相应的优化，即IN中的所有常量都存储在一个数组中，这个数组的顺序很好。但是值多了，消耗就比较大。另一个例子：select id from t where num in(1，2，3)。对于连续值，使用between而不是in；或者用连接代替。

3、SELECT语句务必指明字段名称

SELECT*增加了很多不必要的消耗(CPU、IO、内存、网络带宽)；增加了使用覆盖索引的可能性；当表结构改变时，前分隔符也需要更新。因此，需要在select之后直接连接字段名。

4、当只需要一条数据的时候，使用limit 1

这是为了使EXPLAIN中的type列达到const type。

5、如果排序字段没有用到索引，就尽量少排序

6、如果限制条件中其他字段没有索引，尽量少用or

如果or两边的字段中有一个不是索引字段，其他条件也不是索引字段，会导致查询不索引。很多时候，用union all或union(必要时)代替“or”会得到更好的结果。

7、尽量用union all代替union

union all和union All的区别在于前者需要将结果合并后再进行唯一性过滤操作，这会涉及到排序，增加大量的CPU操作，增加资源消耗和延迟。当然，union all的前提是两个结果集中没有重复数据。

8、不使用ORDER BY RAND()

从“动态”订单中选择id，rand()限制为1000；

上述SQL语句可以优化如下：

select id from ` dynamic ` t1 join(select rand()*(select max(id)from ` dynamic `) as NID)T2 on t1 . id T2 . NID limit 1000；

9、区分in和exists、not in和not exists

select * from table A where id in(select id from table b)

上面的SQL语句相当于

select * from table A where exists(select * from table B，其中table B.id=table A.id)

IN和exists的区别主要是驱动顺序的变化(这是性能变化的关键)。如果存在，则将外层表作为驱动表，首先访问驱动表。如果在中，则首先执行子查询。所以IN适用于外观大，内在小的情况；EXISTS适用于外观很小但内部很大的情况。

关于not in和not exists，建议用not exists，这不仅是效率问题，也是逻辑问题。如何高效的写一条SQL语句而不是not exists？

原始SQL语句：

从a.id不在的表中选择colname …(从B表中选择b.id)

高效的SQL语句：

select colname…from A table Left join B table on where A . id=B . id where B . id为null

提取的结果集如下图所示。表A中的数据不在表B中：

10、使用合理的分页方式以提高分页的效率

从产品限制866613，20中选择id，名称

在使用上述SQL语句进行分页时，可能有人会发现，随着表数据的增加，直接使用limit分页查询会越来越慢。

优化方法如下：可以取上一页最大行数的id，然后根据这个最大id限定下一页的起点。例如，在该列中，上一页的最大id是86612。SQL可以写成如下形式：

从产品中选择id、名称，其中id 866612限制为20

11、分段查询

在用户选择的一些页面中，部分用户选择的时间范围可能过大，导致查询缓慢。主要原因是扫描线太多。此时，您可以按程序、逐段、循环查询，并组合显示结果。

如以下SQL语句所示，当扫描的行数超过数百万时，可以使用分段查询：

12、避免在where子句中对字段进行null值判断

null的判断会导致引擎放弃索引，扫描整个表。

13、不建议使用%前缀模糊查询

比如像“%name”或者像“%name%”，这种查询会导致索引失效和全表扫描。但是你可以用“姓名%”。

那如何查询%name%？

如下图所示，虽然在secret字段中添加了一个索引，但结果并不用于explain:

那么如何解决这个问题呢？回答：使用全文索引。

Select id，fnum，FDST from dynamic _ 201606其中用户名类似“%张三%”经常用在我们的查询中；有了这样的语句，普通的索引就无法满足查询需求。幸运的是，在MySQL中，有一个全文索引可以帮助我们。

创建全文索引的SQL语法是：

ALTER TABLE ` dynamic _ 201606 ` ADD full text INDEX ` idx _ user _ name `( ` user _ name `)；

使用全文索引的SQL语句有：

select id，fnum，fdst from dynamic _ 201606 where match(user _ name)against(布尔模式下为'张三')；

注意：在需要创建全文索引之前，请联系DBA以确定是否可以创建全文索引。同时要注意查询语句的编写与常用索引的区别。

14、避免在where子句中对字段进行表达式操作

例如：

select user_id，user_project from user_base其中年龄* 2=36；

对中的字段执行算术运算，这将导致引擎放弃使用索引。建议改为：

select user_id，user_project from user_base其中年龄=36/2；

15、避免隐式类型转换

当where子句中的列字段类型与传入参数的类型不一致时，会发生类型转换。建议首先确定where中的参数类型。

16、对于联合索引来说，要遵守最左前缀法则

例如，索引包含字段id、名称和学校，可以直接使用，也可以按照id和名称的顺序使用，但是名称；学校不能用这个指标。所以在创建联合索引时一定要注意索引字段的顺序，把常用的查询字段放在最前面。

17、必要时可以使用force index来强制查询走某个索引

有时候MySQL优化器会使用它认为合适的索引来检索SQL语句，但可能它使用的索引并不是我们想要的。此时，可以使用forceindex来强制优化器使用我们的索引。

18、注意范围查询语句

对于联合索引，如果存在范围查询，如、等之间的查询，则以下索引字段将变得无效。

19、关于JOIN优化

左JOIN A表是驱动表，内JOIN MySQL会自动找出哪个数据少的表是驱动表，右JOIN B表是驱动表。

注意：

1）MySQL中没有full join，可以用以下方式来解决：

select * from A left join B on B.name=A . name其中B . name为null union all select * from B；

2）尽量使用inner join，避免left join：

参与联合查询的至少有两个表，一般大小有差异。如果连接方式是inner join，MySQL会自动选择一个小表作为驱动表，不需要其他过滤条件，但是left join遵循左侧驱动右侧的原则，即left join左侧的表名就是驱动表。

3）合理利用索引：

从动表的索引字段用作on的限制字段。

4）利用小表去驱动大表：

从原理图中可以直观的看出，如果能减少驱动表，就可以减少嵌套循环中的循环次数，从而减少总的IO量和CPU操作次数。

5）巧用STRAIGHT_JOIN：

Inner join是MySQL选择的驱动表，但是有一些特殊情况需要选择另一个表作为驱动表，比如group by、order by等“使用filesort”和“使用temporary”。STRAIGHT_JOIN强制连接顺序。STRAIGHT_JOIN左边的表名是驱动表，右边是从动表。使用STRAIGHT_JOIN的前提是查询是内连接，也就是内连接。其他链接不建议使用STRAIGHT_JOIN，否则可能导致查询结果不准确。

这种方法有时可以将时间缩短三倍。

总结

以上就是本文关于MySQL优化常用的19种有效方法。有关MySQL优化常用方法的更多信息，请搜索我们以前的文章或继续浏览下面的相关文章。希望大家以后能多多支持我们！

郑重声明：本文由网友发布，不代表盛行IT的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。