MySQL执行语句性能优化

目录

1. like 前导符优化

2. in 和 exist

3. not in 和 not exist

4. 子查询优化

5. straight_join

6. 高效分页

7. 复杂关联SQL的优化

8. force index

9. count的优化

10. 总结

---

 

MySql学习专栏

1. MySQL基础架构详解

2. MySQL索引底层数据结构与算法

3. MySQL5.7开启binlog日志，及数据恢复简单示例

4. MySQL日志模块

5. MySQL的MVCC及实现原理

6. MySQL索引优化

7. MySQL——通过EXPLAIN分析SQL的执行计划

8. MySQL执行语句性能优化

9. MySQL中的关联查询(内连接，外连接，自连接)

10. MySQL中复杂的增删改语句

11. 面试必问的 MySQL，你懂了吗？

 

---

1. like 前导符优化

like模糊查询形如'%AAA%'和'%AAA'将不会使用索引，但是业务上不可避免可能又需要使用到这种形式。

explain select * from user where user_name like '%change%';

通常的方法有两种：

方案一：使用组合索引，即查询出的列只是用索引就可以获取，而无须查询表记录，这样也走了索引；

explain select user_name from user where user_name like '%change%';

方案二：使用locate函数或者position函数代替like查询，如table.field like '%AAA%'可以改为locate('AAA', table.field) > 0

explain select * from user where locate('change', user_name) > 0 ;

2. in 和 exist

如果查询的两个表大小相当，那么用in和exists差别不大

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in： 例如：表A（小表），表B（大表）

示例一：

// 坏
select * from A where cc in (select cc from B ) 效率低，A表中cc字段索引列;
// 好
select * from A where EXISTS (select cc from B where A.cc = B.cc ) 效率高用到B中cc索引列

示例二：

// 好
select * from B where cc in (select cc from A ) 效率高，使用到B表中cc索引列;
// 坏
select * from B where EXISTS (select cc from A where A.cc = B.cc ) 效率低，用到A中cc中索引列

 

3. not in 和 not exist

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not exist 的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in要快！

// 好
select * from B where not EXISTS (select * from A WHERE B.cc = A.cc )
// 坏
select * from B where cc not in (select cc from A)

 

4. 子查询优化

 

1. MySQL 5.6 之前的版本对子查询处理：不会将查询的结果集计算出来用作与其他表做join,outer表每扫描一条数据，子查询都会被重新执行一遍。
2. MySQL 5.6 对子查询的处理 ：将子查询的结果集 cache 到临时表里，临时表索引主要用来移除重复记录，并且随后也可能用于做join查询，这种技术在 5.6 中叫做物化的子查询，物化子查询可以看到select_type字段为subquery，而在 5.5 里为DEPENDENT SUBQUERY。
3. 子查询一般都可以改成表的关联查询，子查询会有临时表的创建、销毁，效率低下。

// 坏
select * from B where cc in (select cc from A ) 
// 好
select * from B,A where A.cc = B.cc

 

5. straight_join

mysql hint：mysql 优化器在处理多表的关联的时候，很有可能会选择错误的驱动表进行关联，导致了关联次数的增加，从而使得sql语句执行变得非常的缓慢。

这个时候需要有经验的DBA进行判断，选择正确的驱动表，这个时候 straightjoin 就起了作用了，下面我们来看一看使用straight_join进行优化的案例。

尝试采用user表做驱动表，使用straight_join强制连接顺序：

// 坏
select a.id,a.username,a.icon,a.note,a.create_time,a.login_time,a.`status` from ums_admin a, ums_admin_login_log b
where a.id = b.admin_id and a.`status` = 1

// 坏
select a.id,a.username,a.icon,a.note,a.create_time,a.login_time,a.`status` from ums_admin_login_log b, ums_admin a
where a.id = b.admin_id and a.`status` = 1

 

6. 高效分页

 

1. 传统分页

select * from table limit 10000,10

2. limit原理

• Limit 10000,10
• 偏移量越大则越慢

3. 推荐分页

分页方式一
select * from table where id >= xxx limit 10;

分页方式二
select * from table where id >= (select id from table limit 10000,1) limit 10;

分页方式三
select * from table inner join (select id from table limit 10000,10) using (id);

分页方式四
select t1.* from table t1,(select id from table limit 100000,10) t2 where t1.id = t2.id;

 

7. 复杂关联SQL的优化

1、首先查询返回的结果集，通常查询返回的结果集很少，是有优化的空间的。

2、通过查看执行计划，查看优化器选择的驱动表，从执行计划的rows可以大致反应出问题的所在。

3、搞清各表的关联关系，查看关联字段是否有合适的索引。

4、使用straight_join关键词来强制调整驱动表的选择，对优化的想法进行验证。

5、如果条件允许，对复杂的SQL进行拆分。尽可能越简单越好。

8. force index

有时优化器可能由于统计信息不准确等原因，没有选择最优的执行计划，可以人为改变mysql的执行计划，例如：

 

9. count的优化

按照效率排序的话，count(字段) < count(主键id) < count(1) ≈ count(*)，所以我建议你，尽量使用count(*)。

现在的优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。

这是什么意思呢？接下来，我们就一个个地来看看。

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

对于 count(字段) 来说：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；

如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。

但是 count() 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。

count() 肯定不是 null，按行累加。

看到这里，你一定会说，优化器就不能自己判断一下吗，主键 id 肯定非空啊，为什么不能按照 count() 来处理，多么简单的优化啊。

当然，MySQL 专门针对这个语句进行优化，也不是不可以。但是这种需要专门优化的情况太多了，而且 MySQL 已经优化过 count() 了，你直接使用这种用法就可以了。

所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count()*，所

以我建议你，尽量使用 count(*)

 

10. 总结

MySQL 性能优化 最主要是理解 innodb 的索引原理及结构及 SQL 的执行计划，在不断累积经验的基础上熟能生巧。

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。

1. InnoDB不支持FULLTEXT类型的索引。
2. InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
3. 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
4. DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
5. MyISAM在insert、update时为表锁，而InnoDB提供行锁。