超值干货：结合执行计划MySQL语句的11大优化策略

背景

全球访问量最大的 20 家网站，绝大多数使用 MySQL,有两个特例的 live.com 和 bing 是微软旗下的网站。它使用的是 MSSQL，并不是他们使用不了 MySQL，而是他要支持自己的数据库。毫无疑问MySQL是当今非常流行的关系数据库之一，不仅因为其绝大多数版本的开源，而且支持多存储引擎、快速、稳定地运行于各种服务器环境。MySQL查询分析优化引擎作为其中核心模块的一部分，占有举足轻重的地位，因此我们今天了解和研究它就非常重要了。

MySQL服务器操作层架构

MySQL语句的优化就发生MySQL Server服务器架构的操作层，这层具体的执行流程是：

这层主要的功能是： SQL 语句的解析、优化，缓存的查询，MySQL 内置函数的实现，跨存储引擎功能（所谓跨存储引擎就是说每个引擎都需提供的功能（引擎需对外提供接口）），例如：存储过程、触发器、视图等。

1. 如果是查询语句（select 语句），首先会查询缓存是否已有相应结果，有则返回结果，无则进行下一步（如果不是查询语句，同样调到下一步）；

2. 解析查询，创建一个内部数据结构（解析树），这个解析树主要用来 SQL 语句的语义与语法解析；

3. 优化：优化 SQL 语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎，优化器会询问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化等。

十一大优化策略

演示准备

用以上演示道具来说明十一经典优化策略如下：

• 策略一、索引字段尽量全值匹配

– 查询1

EXPLAIN SELECT * FROM employee WHERE name=‘joye’; – 走索引

– 查询2

EXPLAIN SELECT * FROM employee WHERE name=‘joye’ AND age=25; – 走索引

– 查询3

EXPLAIN SELECT * FROM employee WHERE NAME=‘joye’ AND age=25 AND pos=‘dev’; – 走索引

以上三种查询方式，查询3的效率最高，索引的使用程度也最高。在表中建立索引后，能用索引的要尽量都要用上。

• 策略二、最佳左前缀法则

如果创建的索引为复合索引，要遵守最左前缀法则。查询从索引的最左前列开始并且不要跳过索引中的列。

– 查询1

EXPLAIN SELECT * FROM employee WHERE age=20 AND pos=‘dev’; – 不走索引

– 查询2

EXPLAIN SELECT * FROM employee WHERE pos=‘dev’; – 不走索引

– 查询3

EXPLAIN SELECT * FROM employee WHERE name=‘Joye’; – 走索引

以上查询只有查询3才走索引。最佳左前缀法则可以理解成火车的车头、中间车厢、车尾的关系。

• 策略三、不在索引列上做任何操作

在索引列上计算、函数、类型转换等会导致索引失效，转向全表扫描。

– 查询1

EXPLAIN SELECT * FROM employee WHERE name=‘Joye’; – 走索引

– 查询2

EXPLAIN SELECT * FROM employee WHERE LEFT(name,4)=‘Joye’; – 不走索引

– 查询3

EXPLAIN SELECT * FROM employee WHERE age*2 = 13; – 不走索引

在索引字段上使用函数、任何计算表达式均会导致索引失效。

• 策略四、尽量多用覆盖索引

尽量使用覆盖索引(只访问索引列的查询，查询列和索引列一致)，减少select开销。全部数据直接通过索引就能获取到，大大提高查询效率。

– 查询1

EXPLAIN SELECT age,pos FROM employee WHERE name = ‘joye’; – 走索引

– 查询2

EXPLAIN SELECT pos FROM employee WHERE name = ‘joye’; – 走索引

– 查询3

EXPLAIN SELECT age,add_time FROM employee WHERE name = ‘joye’; – 不走索引

• 策略五、范围条件放最后

查询优化器不会使用索引中范围条件右边的列，所以范围条件放最后能被主动采用。

– 查询1

EXPLAIN SELECT * FROM employee WHERE NAME=‘joye’ AND age=22 AND pos=‘manager’; – 走索引

– 查询2

EXPLAIN SELECT * FROM employee WHERE NAME=‘joye’ AND age>22 AND pos=‘manager’;-- 不充分走索引

– 查询3

EXPLAIN SELECT * FROM employee WHERE NAME=‘joye’ AND pos=‘manager’ AND age>22; – 充分走索引

• 策略六、不等于(! <>) 要慎用

mysql在使用不等于(!= 或者 <>)的时候无法使用索引，导致全表扫描

– 查询1

EXPLAIN SELECT * FROM employee WHERE name != ‘joye’; – 不走索引

– 查询2

EXPLAIN SELECT * FROM employee WHERE NAME <>‘joye’; – 不走索引

若要使用不等号，尽量采用覆盖索引；

– 查询3

EXPLAIN SELECT name,age,pos FROM employee WHERE NAME != ‘joye’; – 走索引

– 查询4

EXPLAIN SELECT NAME,age,pos FROM employee WHERE NAME <> ‘joye’; – 走索引

• 策略七、IN/NOT IN要慎用

– 查询1

EXPLAIN SELECT * FROM employee WHERE name IN(‘joye’,‘9000’); – 不走索引

– 查询2

EXPLAIN SELECT * FROM employee WHERE name NOT IN(‘joye’,‘9000’); – 不走索引

查询1和查询2不走索引的原因是IN/NOT IN 匹配让索引失效，转向全表扫描 。若需要使用IN/NOT IN，则同时尽量采用覆盖索引或就使用场景使用JOIN连表方式

– 查询3

EXPLAIN SELECT age,pos FROM employee WHERE name IN(‘joye’,‘9000’); – 走索引

– 查询4

EXPLAIN SELECT age,pos FROM employee WHERE name NOT IN(‘joye’,‘9000’); – 充分使用索引

• 策略八、NULL/NOT NULL有影响

索引字段为null 和 not null 对索引的影响, 可能导致索引失效(分情况)。

– 查询1

EXPLAIN SELECT * FROM employee WHERE name IS NULL; – 不走索引

– 查询2

EXPLAIN SELECT * FROM employee WHERE name IS NOT NULL; – 不走索引

这时设置name字段允许为null

– 查询3

EXPLAIN SELECT * FROM employee2 WHERE NAME IS NULL; – 走索引

• 策略九、LIKE查询要小心

LIKE以通配符开头(‘%abc ’)mysql索引失效会变成全表扫描操作。

– 查询1

EXPLAIN SELECT * FROM employee WHERE name LIKE ‘%july%’ – 不走索引

– 查询2

EXPLAIN SELECT * FROM employee WHERE name LIKE ‘%july’ – 不走索引

– 查询3

EXPLAIN SELECT * FROM employee WHERE name LIKE ‘july%’-- 走索引

• 策略十、字符类型字段加引号

字符窜不加引号会导致索引失效。

– 查询1

EXPLAIN SELECT * FROM employee WHERE name = 9000; – 不走索引

– 查询2

EXPLAIN SELECT * FROM employee WHERE name = ‘9000’ – 走索引

• 策略十一、OR改UNION 效率高

WHERE查询或子查询条件中使用OR,会导致索引失效，转向全表数据扫描。

– 查询1

EXPLAIN SELECT * FROM employee WHERE name=‘joye’ OR name=‘andy3’; – 不走索引

– 查询2

EXPLAIN

SELECT * FROM employee WHERE name=‘joye’

UNION

SELECT * FROM employee WHERE name=‘andy3’; – 走索引

总结

以上仅为SQL语句优化领域的关键优化指标和技巧。在具体项目的优化中，我们可能会综合使用以上多个策略和手段完成一个SQL的优化；如何用好这些策略完全取决于我们在项目实战中循序渐进的优化、尝试、摸索、总结。

来源：https://blog.51cto.com/14815984/2512163
转载自：51CTO

写在最后

欢迎大家关注我的公众号【风平浪静如码】，海量Java相关文章，学习资料都会在里面更新，整理的资料也会放在里面。

觉得写的还不错的就点个赞，加个关注呗！点关注，不迷路，持续更新！！！