在介绍具体的 SQL 调优的方法前,我们先来简单了解下 MySQL 调优金字塔理论。
如上图所示,数据库优化维度有四个:硬件、系统配置、数据库表结构、SQL 及索引。
优化成本:
硬件>系统配置>数据库表结构>SQL及索引
优化效果:
硬件
我们可以看出数据库 SQL 语句效率调优是最省成本效果最好的办法,也就是结构设计上的优化。本文我们就来谈谈 MySQL 中常用的 SQL 优化方法,利用好这些方法会让你的 MySQL 效率提高提升至少 3 倍。
1、EXPLAIN
做 MySQL 优化,我们要善用 EXPLAIN 查看 SQL 执行计划。
下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据:
•Type 列,连接类型。一个好的 SQL 语句至少要达到 Range 级别。杜绝出现 All 级别。•Key 列,使用到的索引名。如果没有选择索引,值是 NULL。可以采取强制索引方式。•Key_Len 列,索引长度。•Rows 列,扫描行数。该值是个预估值。•Extra 列,详细说明。注意,常见的不太友好的值,如下:Using filesort,Using 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 列达到 Cons