MySQL优化技巧

方博学

2023-12-01

大家好，我是八哥，一个三年开发经验的小厂程序员，不管是面试中还是我们的日常开发中，都会遇到的一个问题就是MySQL的优化问题。下面我就以我的认知来谈谈我对MySQL优化的理解。

前言

一般来讲，我优化SQL一般从四个方面开始入手。
1.业务优化
2.SQL语句优化
3.索引优化
当我们在遇到慢SQL时，当然这个慢SQL可能是自己开发的也可能不是自己开发的。不管怎么样，我们首先需要弄清楚的就是当前这个SQL的作用及业务用途。因为这是优化SQL语句的大前提。下面我就从实际业务的角度分别去解释一下该如何进行优化。

1.表结构优化（设计优化）

首先我们设计的时候尽可能的遵从三范式的设计，尽可能的保证数据库的具有唯一的主键、具有明确的一对一、一对多的关系、字段尽量保证不要重复，但是为了方便逻辑查询，我们经常会对一些关联表的主键或者记录的状态进行冗余设计。冗余外键的设计在关键时刻可以大大减少关联表的数量。
设计字段的时候保证选择合适的数据类型，比如说金融的关于钱的数字选择decimal类型

2.业务优化

业务方面通常会有批量插入，批量更新，数据查询，批量删除等业务。
当我们进行批量写操作的时候，由于MySQL进行写入操作的时候不是直接插入数据库中，而是要先写undolog日志，然后修改buffer pool中的page记录，最后通过特定的刷写策略录入到磁盘中的，所以说我们一次性写入太多数据时，会占用大量的Mysql内存。我们知道通常情况Mysql的内存比服务内存更加珍贵，因为服务的扩容相对于数据库来说更加简单一点。而且数据库崩溃对服务影响的范围也会更大。影响到Mysql服务的吞吐量。所以通常我们的做法就是分批操作。

1.批量插入数据

批量插入数据，通常我们会在表的数据初始化，或者修复历史的丢失数据的时候采用。

//java代码
List<List<UserInfo>> partitions = Lists.partition(list,100);
partitions.forEach(partition->userInfoMapper.batchInsert(partition));
//SQL语句
 <insert id="batchInsert" keyColumn="seqId" keyProperty="seqid" parameterType="map" useGeneratedKeys="true">
     INSERT INFO user_info(`name`)
     <foreach collection="list" item="item" separator=",">
     (#{name})
     </foreach>
 </insert>

2.批量更新语句

批量更新语句时我们需要注意的是，尽量使用主键作为条件去更新。所以我们通常的用法都是要将需要处理的数据查询出来，然后进行一定的数据加工处理后将数据更新的值更新到数据库。所以这里我就讲一个查询的小技巧。
通常来讲，我们分批更新肯定是采用分页查询的，采用 limit index , pageSize;的语法，我们知道Mysql的分页在进行大批量查询时会产生效率很慢的问题。如果我们的表使用的是递增主键，可以使用以下语法来避免出现这个问题。

SELECT id,a,b FROM user_info WHERE id < #{maxId} ORDER BY id DESC limit 0 , 1000;

3.批量删除语句

批量删除语句通常使用的语法 in 语法。最好将in后边的数据量限制在1000个作用，保证SQL语句的执行效率。

//java代码
List<List<Long>> partitions = Lists.partition(list, 1000);
partitions.forEach(partition->userInfoMapper.batchInsert(partition));
//SQL语句
 <insert id="batchInsert" keyColumn="seqId" keyProperty="seqid" parameterType="map" useGeneratedKeys="true">
     DELETE FROM user_info 
     WHERE id in(
     <foreach collection="list" item="item" separator=",">
        #{item}
     </foreach>
     )
 </insert>

3.语句优化

查询语句尽可能的加上 LIMIT

一个常见的错误是常常会误以为MySQL会只返回需要的数据，实际上MySQL却是先返回全部结果集再进行计算。我们经常会看到一些了解其他数据库系统的人会设计出这类应用程序。这些开发者习惯使用这样的技术，先使用SELECT语句查询大量的结果，然后获取前面的N行后关闭结果集（例如在新闻网站中取出100条记录，但是只是在页面上显示前面10条）。他们认为MySQL会执行查询，并只返回他们需要的10条数据，然后停止查询。实际情况是MySQL会查询出全部的结果集，客户端的应用程序会接收全部的结果集数据，然后抛弃其中大部分数据。最简单有效的解决方法就是在这样的查询后面加上LIMIT。

避免使用 SELECT *

避免使用select _ ， select_* 会导致我们无法使用覆盖索引的优化，并且会查询出多余的数据，占用我们的cpu和内存资源。而且在增加表中的字段时候可能会影响到之前编写的sql语句。导致一些业务不兼容的语句产生SQLException。

尽量避免使用OR查询

select
   distinct cert.emp_id 
from
   cm_log cl 
inner join
   (
      select
         emp.id as emp_id,
         emp_cert.id as cert_id 
      from
         employee emp 
      left join
         emp_certificate emp_cert 
            on emp.id = emp_cert.emp_id 
      where
         emp.is_deleted=0
   ) cert 
      on (
         cl.ref_table='Employee' 
         and cl.ref_oid= cert.emp_id
      ) 
      or (
         cl.ref_table='EmpCertificate' 
         and cl.ref_oid= cert.cert_id
      ) 
where
   cl.last_upd_date >='2013-11-07 15:03:00' 
   and cl.last_upd_date<='2013-11-08 16:00:00';

简述一下执行计划，首先mysql根据idx_last_upd_date索引扫描cm_log表获得379条记录；然后查表扫描了63727条记录，分为两部分，derived表示构造表，也就是不存在的表，可以简单理解成是一个语句形成的结果集，后面的数字表示语句的ID。derived2表示的是ID = 2的查询构造了虚拟表，并且返回了63727条记录。我们再来看看ID = 2的语句究竟做了写什么返回了这么大量的数据，首先全表扫描employee表13317条记录，然后根据索引emp_certificate_empid关联emp_certificate表，rows = 1表示，每个关联都只锁定了一条记录，效率比较高。获得后，再和cm_log的379条记录根据规则关联。从执行过程上可以看出返回了太多的数据，返回的数据绝大部分cm_log都用不到，因为cm_log只锁定了379条记录。
如何优化呢？可以看到我们在运行完后还是要和cm_log做join,那么我们能不能之前和cm_log做join呢？仔细分析语句不难发现，其基本思想是如果cm_log的ref_table是EmpCertificate就关联emp_certificate表，如果ref_table是Employee就关联employee表，我们完全可以拆成两部分，并用union连接起来，注意这里用union，而不用union all是因为原语句有“distinct”来得到唯一的记录，而union恰好具备了这种功能。如果原语句中没有distinct不需要去重，我们就可以直接使用union all了，因为使用union需要去重的动作，会影响SQL性能。
优化过的语句如下：

select
   emp.id 
from
   cm_log cl 
inner join
   employee emp 
      on cl.ref_table = 'Employee' 
      and cl.ref_oid = emp.id  
where
   cl.last_upd_date >='2013-11-07 15:03:00' 
   and cl.last_upd_date<='2013-11-08 16:00:00' 
   and emp.is_deleted = 0  
union
select
   emp.id 
from
   cm_log cl 
inner join
   emp_certificate ec 
      on cl.ref_table = 'EmpCertificate' 
      and cl.ref_oid = ec.id  
inner join
   employee emp 
      on emp.id = ec.emp_id  
where
   cl.last_upd_date >='2013-11-07 15:03:00' 
   and cl.last_upd_date<='2013-11-08 16:00:00' 
   and emp.is_deleted = 0

原来的语句53条记录 1.87秒，又没有用聚合语句，比较慢，不需要了解业务场景，只需要改造的语句和改造之前的语句保持结果一致，现有索引可以满足，不需要建索引，用改造后的语句实验一下，只需要10ms 降低了近200倍。

4.索引优化

索引分类，主键索引，唯一索引，联合索引，索引可以根据叶子节点是否存储数据分为聚簇索引和非聚簇索引。

1.union、in、or 都能够命中索引，建议使用 in

union能够命中索引，并且MySQL 耗费的 CPU 最少。

select * from doc where status=1  
union all  
select * from doc where status=2;

in能够命中索引，查询优化耗费的 CPU 比 union all 多，但可以忽略不计，一般情况下建议使用 in。

select * from doc where status in (1, 2);

or 新版的 MySQL 能够命中索引，查询优化耗费的 CPU 比 in多，不建议频繁用or。

select * from doc where status = 1 or status = 2;

补充：有些地方说在where条件中使用or，索引会失效，造成全表扫描，这是个误区。

2、联合索引最左前缀原则

如果在(a,b,c)三个字段上建立联合索引，那么他会自动建立 a| (a,b) | (a,b,c)组索引。
登录业务需求，SQL语句如下：

select uid, login_time from user where login_name=? and passwd=?;

可以建立(login_name, passwd)的联合索引。因为业务上几乎没有passwd 的单条件查询需求，而有很多login_name 的单条件查询需求，所以可以建立(login_name, passwd)的联合索引，而不是(passwd, login_name)。

建立联合索引的时候，区分度最高的字段在最左边
存在非等号和等号混合判断条件时，在建立索引时，把等号条件的列前置。如 where a>? and b=?，那么即使a 的区分度更高，也必须把 b 放在索引的最前列。
最左前缀查询时，并不是指SQL语句的where顺序要和联合索引一致。

下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引：

select uid, login_time from user where passwd=? and login_name=?;

但还是建议 where 后的顺序和联合索引一致，养成好习惯。

假如index(a,b,c), where a=3 and b like ‘abc%’ and c=4，a能用，b能用，c不能用。

3、如果有order by、group by的场景，请注意利用索引的有序性

order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort 的情况，影响查询性能。

例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。

如果索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b;，索引(a,b)无法排序。

4、使用短索引(前缀索引)

对列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果该列在前10个或20个字符内，可以做到既使得前缀索引的区分度接近全列索引，那么就不要对整个列进行索引。因为短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作，减少索引文件的维护开销。可以使用count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度。
但缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引。
不过很多时候没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

5、利用延迟关联或者子查询优化超多分页场景

MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。
示例如下，先快速定位需要获取的id段，然后再关联:

select a.* 
from 表1 a,(select id from 表1 where 条件 limit 100000,20 ) b 
where a.id=b.id;

6、如果明确知道只有一条结果返回，limit 1 能够提高效率

因为查询时，MySQL会从存储引擎中查询出全部的结果集，然后where条件在 server层进行数据过滤，如果加上limit的话，可以立即停止结果集的返回。

比如如下 SQL 语句：

select * from user where login_name=?;

可以优化为：

select * from user where login_name=? limit 1;

自己明确知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动。

7、超过三个表最好不要 join

需要 join 的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。
例如：left join是由左边决定的，左边的数据一定都有，所以右边是我们的关键点，建立索引要建右边的。当然如果索引在左边，可以用right join。

8、单表索引建议控制在5个以内

9、业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引

不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

10.创建索引时避免以下错误观念

索引越多越好，认为需要一个查询就建一个索引。
宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度。
抵制惟一索引，认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。
过早优化，在不了解系统的情况下就开始优化。

5.Explain解读

SQL 性能优化 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好

explain 列含义解读

![](https://img-blog.csdnimg.cn/img_convert/962f26cb0a6b159e8116e57130f69d55.png#crop=0&crop=0&crop=1&crop=1&from=url&id=lMxQK&margin=[object Object]&originHeight=412&originWidth=631&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)

查询时可能命中的索引解释

consts：单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据。
ref：使用普通的索引(Normal Index)。
range：对索引进行范围检索。
当 type=index 时，索引物理文件全扫，速度非常慢。
![](https://img-blog.csdnimg.cn/img_convert/95fb1ea5960d8755107942c63a59a072.png#crop=0&crop=0&crop=1&crop=1&from=url&id=ta6ik&margin=[object Object]&originHeight=325&originWidth=631&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)

6.索引失效的情况

1、like语句的前导模糊查询不能使用索引

因为页面搜索严禁左模糊或者全模糊，如果需要可以使用搜索引擎来解决。

select * from doc where title like '%XX'；   --不能使用索引 
select * from doc where title like 'XX%'；   --非前导模糊查询，可以使用索引

2、负向条件查询不能使用索引

负向条件有：!=、<>、not in、not exists、not like 等。
例如下面SQL语句：

select * from doc where status != 1 and status != 2;

可以优化为 in 查询：

select * from doc where status in (0,3,4);

3、不能使用索引中范围条件右边的列(范围列可以用到索引)，范围列之后列的索引全失效

范围条件有：<、<=、>、>=、between等。
索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。
假如有联合索引 (empno、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而title 和 from_date 则使用不到索引。

select * from 
employees.titles 
where emp_no < '10010' 
and title='Senior Engineer'
and from_date between '1986-01-01' and '1986-12-31' ;

4、不要在索引列上面做任何操作(计算、函数)，否则会导致索引失效而转向全表扫描

例如下面的 SQL 语句，即使 date 上建立了索引，也会全表扫描：

select * from doc where YEAR(create_time) <= '2016';

可优化为值计算，如下：

select * from doc where create_time <= '2016-01-01';

比如下面的 SQL 语句：

select * from order where date < = CURDATE();

可以优化为：

select * from order where date < = '2018-01-2412:00:00';

5、强制类型转换会全表扫描

字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。
如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引。

select * from user where phone=13800001234;