MySQL 优化必知必会（面试必备）

今天介绍一下 MySQL 数据库优化，从网上收集了 30 个我觉得以我的水平可以用得上（看得懂）的，主打一个接地气能落地，吃好就好（咱也不是 DBA 应付一下面试好了），优化方式实在是太多了，不用给自己增加负担，不信你可以问问 Kimi, 分分钟给你列上百个.

MySQL 优化可以从多个方面进行优化，当然对于研发来说，只需要关注表结构和 SQL 及索引即可，况且性价比最高。

优化成本：硬件 > 系统配置 > 数据库表结构 > SQL及索引
优化效果：硬件 < 系统配置 < 数据库表结构 < SQL及索引

对于MySQL优化一般遵从五个原则：

减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO
减少数据返回：只返回需要的字段和数据分页处理，减少磁盘IO及网络IO
减少交互次数：批量操作，函数存储等减少数据库连接次数
减少服务器开销：尽量减少数据库排序操作以及全表查询
利用更多资源：使用表分区，可以增加并行操作，更大限度利用资源

总结到SQL优化中，就三点:

最大化利用索引；
尽可能避免全表扫描；
减少无效数据的查询；

SQL优化策略适用于数据量较大的场景下，如果数据量较小（10万内），没必要，哈哈。

SELECT 语法顺序

回顾一下查询关键词顺序。

SELECT 
DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

1. 尽量避免在字段开头模糊查询

字段开头模糊查询会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM t WHERE username LIKE '%陈%'
-- 尽量在字段后面使用模糊查询
SELECT * FROM t WHERE username LIKE '陈%'

如果需求是要在前面使用模糊查询

使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()
数据量较大的情况，建议引用ElasticSearch、Solr等，亿级数据量检索速度秒级
退而求其次可以建立全文索引（FULLTEXT），配合全文模糊搜索(MATCH AGAINST)操作，而不是普通的 where 条件操作

看一个 match against 示例

-- MATCH (column1, column2, ...) AGAINST (search_keyword [search_modifier])
SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL');

注意：MySQL的MATCH AGAINST语句仅对MyISAM表类型支持，‌并且对大多数多字节字符集适用，‌但表意性语言如汉语、‌日语等由于没有词分界符，‌全文检索不支持（放弃吧，少年）。‌

2. 尽量避免使用 in

in 和 not in 语句会导致放弃索引走全表扫描

SELECT * FROM t WHERE id IN (2,3)
-- 可以使用 BETWEEN
SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，可以用exists代替

-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);

3. 尽量避免使用 or

会导致放弃索引进行全表扫描

SELECT * FROM t WHERE id = 1 OR id = 3
-- 优化方式，可以用 union 代替 or
SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

4. 避免进行null值判断

会导致放弃索引进行全表扫描

SELECT * FROM t WHERE score IS NULL
-- 可以给字段添加默认值 0，对0值进行判断
SELECT * FROM t WHERE score = 0

5. 避免在条件等号的左侧进行表达式、函数操作

会导致放弃索引进行全表扫描

-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引 可以将表达式、函数操作移动到等号右侧
SELECT * FROM T WHERE score = 10*9

6. 避免使用 where 1=1

通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。

SELECT username, age, sex FROM T WHERE 1=1
-- 优化，不需要条件，就去掉
SELECT username, age, sex FROM T

7. where 条件应包含复合索引前置列

如下：复合（联合）索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列”key_part1”，按照MySQL联合索引的最左匹配原则，不会走联合索引。

优化建议：将复合索引的字段都出现在查询条件中，或者优化复合索引

-- 不会走联合索引
select col1 from table where key_part2=1 and key_part3=2
-- 会走索引
select col1 from table where key_part1=1 and key_part2=1 and key_part3=2

8. order by 条件要与 where 条件一致

否则order by不会利用索引进行排序

-- 不走age索引
SELECT * FROM t order by age;
-- 走age索引
SELECT * FROM t where age > 0 order by age;

对于上面的语句，数据库的处理顺序是：

第一步：根据where条件和统计信息生成执行计划，得到数据。
第二步：将得到的数据排序，当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引，如果是，则可以利用索引顺序而直接取得已经排好序的数据，如果不是，则重新进行排序操作。
第三步：返回排序后的数据。

当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序二次排序。

这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct 等。

9. 显示指定索引

在特定场合下，指定索引可以排除其他索引干扰而获得更优的执行计划。

USE INDEX 在你查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，让 MySQL 不再考虑其他可用的索引。例子: SELECT col1 FROM table USE INDEX (mod_time, name)…
IGNORE INDEX 如果只是单纯的想让 MySQL 忽略一个或者多个索引，可以使用 IGNORE INDEX.例子: SELECT col1 FROM table IGNORE INDEX (priority) …
FORCE INDEX 强制 MySQL 使用一个特定的索引，可在查询中使用FORCE INDEX . 例子: SELECT col1 FROM table FORCE INDEX (mod_time) …

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。

但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。

如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。

SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

10. 避免出现 select *

首先，select * 操作在任何类型数据库中都不是一个好的SQL编写习惯。

使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。

建议提出业务实际需要的列数，将指定列名以取代select *。

11. 多表查询时，小表在前，大表在后

在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，再扫描后面的大表。

12. 使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上，这样就可以减少解析的时间并减少哪些有列名歧义引起的语法错误。

13. 用where替换having

避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。

HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。

14. 调整where字句中的顺序

MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

看一个例子：

SELECT id FROM XX WHERE ( STATUS = '1' AND shop_code = 'XXX' )

一般状态值status只有0和1，区分度不大，而shop_code的区分度大，在执行where条件筛选的时候，区分度大的放在前面，第一层过滤的时候就能过滤掉大部分行数，减少扫描的行数，提高效率。

15. 批量插入数据优化

如果同时执行大量的插入，建议使用多个值的INSERT语句。这比使用分开INSERT语句快，一般情况下批量插入效率有几倍的差别。

insert into T values(1,2); 
insert into T values(1,3); 
insert into T values(1,4);
# 优化
Insert into T values(1,2),(1,3),(1,4);

16. 适当使用commit

适当使用commit可以释放事务占用的资源而减少消耗，commit后能释放的资源如下：

事务占用的undo数据块；
事务在redo log中记录的数据块；
释放事务施加的锁，减少锁争用影响性能。

特别是在需要使用delete删除大量数据的时候，必须分解删除量并定期commit。

17. 避免重复查询更新的数据

针对业务中经常出现的更新行同时又希望获得改行信息的需求，MySQL并不支持PostgreSQL那样的UPDATE RETURNING语法，但在MySQL中可以通过变量实现。

例如，更新一行记录的时间戳，同时希望查询当前记录中存放的时间戳是什么，简单方法实现：

Update t1 set time=now() where col1=1; 
Select time from t1 where id =1; 
-- 前后二者都需要两次网络请求，但使用变量避免了再次访问数据表，特别是当t1表数据量较大时，后者比前者快很多。
Update t1 set time=now() where col1=1 and @now: = now(); 
Select @now; 

18. 优化group by语句

默认情况下，MySQL 会对GROUP BY分组的所有值进行排序，如 “GROUP BY col1，col2，….;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，…;” 如果显式指定 ORDER BY子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。

因此，如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL禁止排序。例如：

SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

19. 优化join语句

MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)..替代。

例子：假设要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

如果使用连接(JOIN).. 来完成这个查询工作，速度将会有所提升。尤其是当 salesinfo表中对 CustomerID 建有索引的话，性能将会更好，查询如下：

SELECT col1 FROM customerinfo 
   LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID 
      WHERE salesinfo.CustomerID IS NULL 

连接(JOIN).. 之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

20. 优化union查询

MySQL通过创建并填充临时表的方式来执行union查询。

除非确实要消除重复的行，否则建议使用union all。

原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
UNION ALL 
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST'; 

21. 使用合理的分页方式以提高分页效率

合适的分页方式能够提高分页的效率，看下面的案例对比。

案例1：

select xx from t where thread_id = 10000 and deleted = 0 
   order by gmt_create asc limit 0, 15;

上述例子通过一次性根据过滤条件取出所有字段进行排序返回。

数据访问开销=索引IO+索引全部记录结果对应的表数据IO。

因此，该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候。

案例2：

select t.* from (select id from t where thread_id = 10000 and deleted = 0
   order by gmt_create asc limit 0, 15) a, t 
      where a.id = t.id;

上述例子必须满足t表主键是id列，且有覆盖索引secondary key:(thread_id, deleted, gmt_create)。通过先根据过滤条件利用覆盖索引取出主键id进行排序，再进行join操作取出其他字段。

数据访问开销=索引IO+索引分页后结果（例子中是15行）对应的表数据IO。

因此，该写法每次翻页消耗的资源和时间都基本相同，就像翻第一页一样。

在表中建立索引，优先考虑where、order by使用到的字段。

22. 尽量使用数字型字段

如性别，男：1 女：2，若只含数值信息的字段尽量不要设计为字符型，这会降低查询的性能，并会增加存储开销。

这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

23. 用varchar/nvarchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

24. 使用 EXPLAIN 分析查询

使用 EXPLAIN 来查看查询的执行计划，找出性能瓶颈。

EXPLAIN 是一个非常有用的命令，它可以显示 MySQL 如何执行一个查询。通过 EXPLAIN SELECT …，你可以得到一个执行计划，其中 key 字段会告诉你查询是否使用了索引以及使用了哪个索引。

推荐一篇不错的 MySQL 执行计划文章，有必要抄下来备用。

MySQL执行计划，真的很重要，来一起学习吧

25. 定期使用 ANALYZE TABLE 更新索引统计信息

在低负载的时候通过执行 ANALYZE TABLE tab_name 方式可以分析表的键值分布情况，帮助数据库优化器更好的理解表的结构和数据分布，能够优化查询性能。

26. 表分区

表分区可以加快查询，其实就类似数据库分表，将一个大表进行拆分。如何实现表分区？

在 MySQL 中，可以通过 CREATE TABLE 语句中的 PARTITION BY 子句来实现表分区。

以下是一些常见的分区类型：

RANGE 分区：基于列值的范围进行分区。
LIST 分区：基于列值的列表进行分区。
HASH 分区：基于列值的哈希值进行分区。
KEY 分区：类似于 HASH 分区，但使用多列的组合键。

更多介绍，不在本文目标范围内，了解即可。

27. 慢查询分析

设置合适的慢查询阈值，定期分期慢查询SQL，不断优化。

28. 定期维护索引

使用 OPTIMIZE TABLE 来整理索引和数据文件，可以进行碎片整理，释放空间。

29. 前缀索引优化

有时候将内容很长的列作为索引列，这将会让索引变得很大而且很慢。怎么理解呢？

比如国民身份证，前缀大部分相同，没必要索引全部字符串。

通常可以索引部分字符，这样可以大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性。

对一个字段可以用下面的方式查看区分度，取前几个字符进行去重计数除以总数，得到区分度。

-- 字符串左侧差异更大，计算左侧的当区分度
select count(distinct left(xx_md5, 5))/count(*) as '区分度' from XXX
-- 右侧差异更大，计算右侧的当区分度
select count(distinct left(REVERSE(id_card), 7))/count(*) as '区分度' from XXX;

其实一般来说达到80-90%的区分度是比较好的，降低索引的长度可能达到很好的优化效果。

30. 删掉未使用的索引

多余的索引完全是累赘，建议直接删除。

可以使用 Performance_schema 中的 table_io_waits_summary_by_index_usage 表进行查找：

SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name, index_name;

31. 使用查询缓存

可以开启MySQL的查询缓存，当有很多相同的查询被执行了多次的时候，这些查询结果会被放入一个缓存中，这样后续的相同查询就不用操作而直接访问缓存结果了。

这个缓存对于用户是透明的，应用程序无需关心 MySQL 是通过查询缓存返回的还是实际执行返回的结果。

关于查询缓存有如下参数可供配置：

query_cache_type 是否打开查询缓存。可以设置 OFF、ON、DEMAND，DEMAND 表示只有在查询语句中明确写入 sql_cache 的语句才放入查询缓存。
query_cache_size 查询缓存使用的总内存空间，单位是字节。这个值必须是 1024 的整倍数，否则实际分配的数据会和指定的大小有区别。
query_cache_min_res_unit 在查询缓存中分配内存块时的最小单位。
query_cache_limit 缓存的最大查询结果。如果查询结果大于这个值，则不会被缓存。因为查询缓存在数据生成的时候就开始尝试缓存数据，所以只有当结果全部返回后，MySQL 才知道查询结果是否超出限制。

好了，今天的小目标实现了，面试官问我SQL优化，心里稍微有谱了。