MySQL性能调优【一】

前言

sql语句性能达不到你的要求，执行效率让你忍无可忍，一般会时下面几种情况。

• 网速不给力，不稳定。
• 服务器内存不够，或者SQL 被分配的内存不够。
• sql语句设计不合理
• 没有相应的索引，索引不合理
• 没有有效的索引视图
• 表数据过大没有有效的分区设计
• 数据库设计太差，存在大量的数据冗余
• 索引列上缺少相应的统计信息，或者统计信息过期
• …

本片文章主要介绍的是如何sql优化方法跟技巧。

使用explain 分析你SQL的计划

日常开发写SQL的时候建议用explain分析一下自己书写的SQL语句，尤其是走不走索引这一块。使用 Explain 关键字可以模拟优化器执行SQL查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。

（1）语法：Explain + SQL语句
（2）执行计划包含的信息（如果有分区表的话还会有partitions）

• id（select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序）。

  • id相同，执行顺序从上往下
  • id全不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  • id部分相同，执行顺序是先按照数字大的先执行，然后数字相同的按照从上往下的顺序执行

• select_type（查询类型，用于区别普通查询、联合查询、子查询等复杂查询）

  • SIMPLE ：简单的select查询，查询中不包含子查询或UNION
  • PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY
  • SUBQUERY：在select或where列表中包含了子查询
  • DERIVED：在from列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里
  • UNION：若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在from子句的子查询中，外层 select将被标记为DERIVED
  • UNION RESULT：从UNION表获取结果的select

• table（显示这一行的数据是关于哪张表的）

• type（显示查询使用了那种类型，从最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）

  • system：表只有一行记录（等于系统表），是 const 类型的特例，平时不会出现
  • const：表示通过索引一次就找到了，const 用于比较 primary key 或 unique 索引，因为只要匹配一行数据，所以很快，如将主键置于 where 列表中，mysql 就能将该查询转换为一个常量
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
  • ref：非唯一性索引扫描，范围匹配某个单独值得所有行。本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，它可能也会找到多个符合条件的行，多以他应该属于查找和扫描的混合体
  • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需开始于索引的某一点，而结束于另一点，不用扫描全部索引
  • index：Full Index Scan，index于ALL区别为index类型只遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
  • ALL：Full Table Scan，将遍历全表找到匹配的行

一般来说，得保证查询至少达到range级别，最好到达ref

• possible_keys（显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用）

• key

  • 实际使用的索引，如果为NULL，则没有使用索引

  • 查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠，仅出现在key列表中

• key_len

  • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
  • key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

• ref（显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）

• rows（根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数）

• Extra（包含不适合在其他列中显示但十分重要的额外信息）

  • using filesort: 说明mysql会对数据使用一个外部的索引排序，不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中

  • Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

  • using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作

  • using where：使用了where过滤

  • using join buffer：使用了连接缓存

  • impossible where：where子句的值总是false，不能用来获取任何元祖

  • select tables optimized away：在没有group by子句的情况下，基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

  • distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作

举例子：

• 第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name…】

• 第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,name from t1 where other_column=’’】

• 第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】

• 第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】

• 第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】

关键字的慎用

避免判断 null 值

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引从而进行全表扫描，如：select id from t where num is null
可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：
select id from t where num=0

避免使用 or 逻辑

应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10
union all
select id from t where num=20

mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效，看起来也合情合理。

慎用 in 和 not in 逻辑

in和 not in也要慎用，否则会导致全表扫描，如：

select id from t1 where num in(select id from t2 where id > 10)

此时外层查询会全表扫描，不使用索引。可以修改为：

select id from t1,(select id from t1 where id > 10)t2 where t1.id = t2.id

此时索引被使用，可以明显提升查询效率。

Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小

• Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集
• left join在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。
• right join在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

都满足SQL需求的前提下，推荐优先使用Inner join（内连接），如果要使用left join，左边表数据结果尽量小，如果有条件的尽量放到左边处理。

原因：

• 如果inner join是等值连接，或许返回的行数比较少，所以性能相对会好一点。
• 同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。

exists的合理使用

很多时候用exists代替in是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

慎用distinct关键字

distinct关键字一般用来过滤重复记录，以返回不重复的记录。在查询一个字段或者很少字段的情况下使用时，给查询带来优化效果。但是在字段很多的时候使用，却会大大降低查询效率。

• 反例：
  SELECT DISTINCT * from user;

• 正例：
  select DISTINCT name from user;

• 理由：
  带distinct的语句cpu时间和占用时间都高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较，过滤的过程会占用系统资源，cpu时间。

尽量用 union all 替换 union

如果检索结果中不会有重复的记录，推荐union all 替换 union。

理由：如果使用union，不管检索结果有没有重复，都会尝试进行合并，然后在输出最终结果前进行排序。如果已知检索结果没有重复记录，使用union all 代替union，这样会提高效率。

查询优化

GROUP BY关键字优化

• group by实质是先排序后进行分组，遵照索引建的最佳左前缀
• 当无法使用索引列，增大max_length_for_sort_data 参数的设置，增大sort_buffer_size参数的设置
• where高于having，能写在where限定的条件就不要去having限定了

查询SQL尽量不要使用select *，而是select具体字段

任何地方都不要使用 select * from t，用具体的字段列表代替“*”，不要返回用不到的任何字段。
优点：

• 只取需要的字段，节省资源、减少网络开销。
• select *进行查询时，很可能就不会使用到覆盖索引了，就会造成回表查询。

优化limit分页

我们日常做分页需求时，一般会用 limit 实现，但是当偏移量特别大的时候，查询效率就变得低下。
因此我们有以下优化方案：

• 返回上次查询的最大记录(偏移量)
  当偏移量最大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。

• 使用order by + 索引
  使用order by+索引，也是可以提高查询效率的。

• 在业务允许的情况下限制页数
  建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。

知道查询结果为一条记录，建议使用limit 1

• 如果知道查询结果只有一条或者只要最大/最小一条记录，建议用limit 1，当加上limit 1后，只要找到了对应的一条记录,就不会继续向下扫描了,效率将会大大提高。
• 当然，如果name是唯一索引的话，是不必要加上limit 1了，因为limit的存在主要就是为了防止全表扫描，从而提高性能,如果一个语句本身可以预知不用全表扫描，有没有limit ，性能的差别并不大。

注意模糊查询

如果用到模糊关键字查询，很容易想到like，但是like很可能让你的索引失效从而导致全表扫描，如下所示：

select id from t where name like '%abc%'

模糊查询如果是必要条件时，可以使用 select id from t where name like 'abc%'来实现模糊查询，此时索引将被使用。如果头匹配是必要逻辑，建议使用全文搜索引擎（Elasticsearch、Lucene、Solr 等）。

• 把%放前面，并不走索引，把% 放关键字后面，还是会走索引的。

避免查询条件中字段计算

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

避免查询条件中对字段进行函数操作

应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
如：

select id from t where substring(name,1,3)='abc'  --name 以 abc 开头的 id

应改为:

select id from t where name like 'abc%'

原因：需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销

避免不等值判断

应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
例如：

select age,name  from user where age !=18;

以上使用!=或者<>很可能会导致索引失效从而进行全表扫描，所以我们应该使用下面的方式：

//可以考虑分开两条sql写
select age,name  from user where age <18;
select age,name  from user where age >18;

对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引，尽量避免全表扫描。

where子句中考虑使用默认值代替null。

• 并不是说使用了is null或者is not null就会不走索引了，这个跟mysql版本以及查询成本都有关。
• 如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思会相对清晰一点。

where子句 “= ” 左边注意点

不要在where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

不要定义无意义的查询

不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(...)

索引优化

在适当的时候，使用覆盖索引。

覆盖索引能够使得你的SQL语句不需要回表，仅仅访问索引就能够得到所有需要的数据，大大提高了查询效率。

索引无关优化

• 不使用*、尽量不使用 union，union all等关键字、尽量不使用 or关键字、尽量使用等值判断。

• 表连接建议不超过 5 个。如果超过 5 个，则考虑表格的设计。（互联网应用中）

• 表连接方式使用外联优于内联。

• 外连接有基础数据存在。如：A left join B,基础数据是 A。

• A inner join B，没有基础数据的，先使用笛卡尔积完成全连接，在根据连接条件得到内连接结果集。

• 大数据量级的表格做分页查询时，如果页码数量过大，则使用子查询配合完成分页逻辑。

Select * from table limit 1000000, 10
Select * from table where id in (select pk from table limit 100000, 10)

索引也可能失效

并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL 查询可能不会去利用索引，如一表中有字段 sex，male、female 几乎各一半，那么即使在 sex 上建了索引也对查询效率起不了作用。

组合索引使用

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

索引优化总结

• 全值匹配牛逼
• 最佳左前缀法则，比如建立了一个联合索引(a,b,c)，那么其实我们可利用的索引就有(a), (a,b), (a,b,c)
• 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select
• is null ,is not null 也无法使用索引
• like “xxxx%” 是可以用到索引的，like “%xxxx” 则不行(like “%xxx%” 同理)。like以通配符开头(’%abc…’)索引失效会变成全表扫描的操作，
• 字符串不加单引号索引失效
• 少用or，用它来连接时会索引失效
• <，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行，会导致全表扫描

其它注意事项

表格字段类型选择

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

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

如果插入数据过多，考虑批量插入

当我们插入数据过多时，一次性插入会严重影响性能，同时会造成卡顿，浪费时间，因此建议分批次插入数据。
反例：

for(User u :list){
 INSERT into user(name,age) values(#name#,#age#)   
}

正例：

//一次500批量插入，分批进行
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
    (#{item.name},#{item.age})
</foreach>

count(*) 和 count(1)和count(列名)区别

从执行效果上：

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
• count(1)包括了所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：

• 列名为主键，count(列名)会比count(1)快
• 列名不为主键，count(1)会比count(列名)快
• 如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)
• 如果有主键，则select count（主键）的执行效率是最优的
• 如果表只有一个字段，则select count(*)最优。

有一个问题经常问：count(*)会造成全表扫描吗？

大家可以看以下这篇文章：【https://zhuanlan.zhihu.com/p/149874583?from_voters_page=true】
说明得很细。

参考：
https://juejin.im/post/6850037271233331208#heading-65
https://www.jianshu.com/p/074f3eafcadf