MySQL中的常见SQL语句优化策略（超全超详细！！！）

mysql作为日常企业开发中最常用的数据库，如何更高效率地对其进行查询是企业中非常关心的一个问题，本篇文章结合作者学习以及日常应用的体验，列举一些常见的mysql查询优化策略：

思维导图：

一、不要使用select *

不要使用select *，而是明确列出要查询的具体字段，减少数据传输量

SELECT name,age FROM users;

二、合理创建并使用索引

索引能够提高我们的查询效率，原因也很简单，由原来的顺序查找变味了现在的B+树查找，具体创建索引原则主要有以下几点

1、根据查询条件创建索引

2、根据where子句中频繁使用的列创建索引

3、根据join连接的列条件创建索引

4、对于order by和group by操作的列考虑建立索引，同时当排序或分组涉及大量数据时，考虑复合索引

CREATE INDEX idx_order_date ON vouchers (order_date);
SELECT * FROM vouchers ORDER BY order_date;

5、对于要查询的特定几个列数据，建立复合索引，尽量避免回表查询

CREATE INDEX idx_price_date ON orders (price, date)
SELECT price, date FROM orders

但是在使用复合索引时，要注意复合索引生效以及失效的场景：

复合索引生效场景：

最左前缀法则：只有查询条件使用了最左边的几个条件，索引才会生效，比如说你对orders表中的price, average, date（按照顺序从左到右）建立了索引，查询条件包括price, average或者price, average, date时会生效

索引覆盖查询：即包括要查询的列数据，避免二次回表

排序和分组查询：若order by或过group by字句中的列与索引匹配，并且按照顺序，那么会使用到索引进行查询

复合索引失效的场景：

跳过了中间列：你对orders表中的price, average, date（按照顺序从左到右）建立了索引，但是查询条件只有price和date，那么索引不会生效

不遵循索引顺序：使用where、order by以及group by查询时，如果条件顺序并没有按照创建索引时候的顺序执行，那么索引可能不会生效

对创建索引的列使用了like %的模糊查询：复合索引遵循最左前缀法则，如果使用了 ’ like %word% ’ 进行查询索引不会生效

使用了范围索引：对于复合索引中的第列如果使用了范围查询（>, <, between, like %prefix）等，那么该列之后的索引将不会被优化

索引列用于计算或者函数：如果索引列被用于了计算或者函数中，那么索引可能不会被使用

由此可见，索引在mysql的查询优化中有着举足轻重的地位，但是并不代表我们可以一味地去创建索引，对于频繁更新的数据，创建索引则会增加写操作的开销，同时也要避免在过多的列上面创建索引，这样会增大索引表的内存开销

三、合理使用JOIN操作

只做必要的JOIN操作，减少JOIN的数量和复杂度，同时最好优化连接条件，最好确保连接列上面都创建了索引

四、使用UNION代替OR操作，UNION ALL代替UNION

假设你有这样一条sql语句要执行：

SELECT name, age FROM users WHERE name = 'zhangsan'  OR name = 'lisi' ;

那么即使你给name字段创建了索引，由于OR条件会导致索引的多个部分被扫描，这可能会导致查询成本

但是如果你使用UNION进行查询

SELECT name, age FROM users WHERE name = 'zhangsan'
UNION
SELECT name, age FROM users WHERE name = 'lisi'; 

那么MySQL会对两条语句各自使用索引进行优化查询，提高查询效率

而对于UNION ALL与UNION的区别，UNION会对查询的数据去重，但是UNION ALL会全部返回，如果查询的数据没有重复数据使用UNION ALL会更加合理

五、LIMIT优化

当只需要查找少量数据时，没有必要将所有数据都查找出来进行返回，可以使用LIMIT进行优化，直接取出前面一定量的数据

同时对于一些特殊唯一列的数据表，也可以直接使用LIMIT 1来作为后缀减少查到数据之后的后续查询，提高查询效率

SELECT name, age FROM users WHERE name = 'zhangsan' LIMIT 1;

六、避免使用负条件以及is NULL、distinct

有些存储引擎对于is NULL判断并不支持

尽量避免负条件（!=，NOT IN）查询，负条件查询并不会有效使用索引

例如可以对下面的语句这样优化：

原语句：SELECT name,age FROM users WHERE name != 'zhangsan';

优化后：SELECT name,age FROM users WHERE name in ('lisi','wangwu');

使用distinct（清除多余重复数据）时，可能会导致索引失效、全表扫描、额外的排序操作，降低性能

七、其他方法

还有很多优化查询的方法，比如避免使用HAVING代替WHERE，使用合适的参数类型（使用text而不是varchar），以及提高硬盘内存，使用搭建MySQL主存分布等，也可以更改innodb_buffer_pool_size（缓冲池大小）、thread_cache_size（线程缓存大小）等来进行优化。

更改缓冲池大小：
SET GLOBAL innodb_buffer_pool_size = 4G; 

增大线程缓存大小：
SET GLOBAL thread_cache_size = 100;

本文介绍的都是一些SQL语句执行的优化策略，其实从硬件、操作系统、配置等方面也可以进行优化，这里就不做过多介绍了。

好啦，如果看到这里对你有收获的话，不烦点个赞收藏一下，以后开发的时候实际运用一下会更加得心应手噢，祝好！！！