|
|
51CTO旗下网站
|
|
移动端

DBA大牛MySQL优化心得,语句执行加速就是这么简单!

一打开科技类论坛,最常看到的文章主题就是MySQL性能优化了,为什么要优化呢?今天小编对MySQL优化总结了一些心得,希望在大家之后的工作中能有所有帮助!

作者:老王谈运维来源:今日头条|2019-05-30 05:19

一打开科技类论坛,最常看到的文章主题就是MySQL性能优化了,为什么要优化呢?

因为:

  • 数据库出现瓶颈,系统的吞吐量出现访问速度慢
  • 随着应用程序的运行,数据库的中的数据会越来越多,处理时间变长
  • 数据读写速度缓慢

就是咱们说的“性能问题”,程序员一遇到它总是焦头烂额!

今天小编对MySQL优化总结了一些心得,希望在大家之后的工作中能有所有帮助!

MySQL性能优化

like 前导符优化

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

DBA大牛MySQL优化心得,语句执行加速就是这么简单!

通常的方法有两种:

  • 优化方案一:使用覆盖索引,即查询出的列只是用索引就可以获取,而无须查询表记录,这样也走了索引;
  • 优化方案二:使用locate函数或者position函数代替like查询:如table.field like '%AAA%'可以改为locate('AAA', table.field) > 0或POSITION('AAA' IN table.field)>0

in 和 exist

如果查询的两个表大小相当,那么用in和exists差别不大。 如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in: 例如:表A(小表),表B(大表)

示例一:

DBA大牛MySQL优化心得,语句执行加速就是这么简单!

示例二:

DBA大牛MySQL优化心得,语句执行加速就是这么简单!

not in 和 not exist

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

DBA大牛MySQL优化心得,语句执行加速就是这么简单!

子查询优化

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

DBA大牛MySQL优化心得,语句执行加速就是这么简单!

straight_join

mysql hint:

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

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

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

DBA大牛MySQL优化心得,语句执行加速就是这么简单!

高效分页

传统分页:

  1. select * from table limit 10000,10 

limit原理:

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

推荐分页:

DBA大牛MySQL优化心得,语句执行加速就是这么简单!

复杂关联SQL的优化

  • 首先查询返回的结果集,通常查询返回的结果集很少,是有优化的空间的。
  • 通过查看执行计划,查看优化器选择的驱动表,从执行计划的rows可以大致反应出问题的所在。
  • 搞清各表的关联关系,查看关联字段是否有合适的索引。
  • 使用straight_join关键词来强制调整驱动表的选择,对优化的想法进行验证。
  • 如果条件允许,对复杂的SQL进行拆分。尽可能越简单越好。

force index

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

DBA大牛MySQL优化心得,语句执行加速就是这么简单!

count的优化

按照效率排序的话,count(字段)

总结

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

【编辑推荐】

  1. 系统慢得一批?看数据库运维老司机如何做优化
  2. MySQL底层架构大揭秘,远不止写SQL那么简单!
  3. MySQL到底是怎么解决幻读的?
  4. MySQL删除操作其实是假删除
  5. MySQL数据库字符编码总结--数据传输编码
【责任编辑:赵宁宁 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+澳门新濠天地平台

Spring Boot 爬虫搜索轻松游

Spring Boot 爬虫搜索轻松游

全栈式开发之旅
共4章 | 美码师

63人订阅学习

Linux性能调优攻略

Linux性能调优攻略

性能调优规范
共15章 | 南非蚂蚁

202人订阅学习

VMware vSphere虚拟化常见故障

VMware vSphere虚拟化常见故障

搞定vSphere虚拟化
共18章 | 王春海

78人订阅学习

读 书 +澳门新濠天地平台

点石成金:访客至上的网页设计秘笈(原书第2版)

有些网站看起来很清爽; 有些网站看起来很杂乱; 有些网站能让你轻松地找到资料; 有些网站让你犹如置身迷宫…… ...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO播客

博聚网