文章详情

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

阿里P8架构师谈:MySQL慢查询优化、索引优化、以及表等优化总结

2017-07-17 19:29

关注

阿里P8架构师谈:MySQL慢查询优化、索引优化、以及表等优化总结[数据库教程]

MySQL优化概述

MySQL数据库常见的两个瓶颈是:CPU和I/O的瓶颈。

CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候

磁盘I/O瓶颈发生在装入数据远大于内存容量的时候,如果应用分布在网络上,那么查询量相当大的时候那么平瓶颈就会出现在网络上。

我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。除了服务器硬件的性能瓶颈,对于MySQL系统本身,我们可以使用工具来优化数据库的性能。

MySQL优化方案

Mysql的优化,大体可以分为三部分:索引的优化,sql语句的优化,表的优化

技术分享图片

1.索引

一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的也是最容易出现问题的,还是一些复杂的查询操作,因此对查询语句的优化是重中之重,加速查询最好的方法就是索引。

索引:简单的说,相当于图书的目录,可以帮助用户快速的找到需要的内容。

在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。

总结:索引的目的在于提高查询效率,与我们查询图书所用的目录是一个道理:先定位到章,然后定位到该章下的一个小结,然后找到页数。相似的例子还有:查字典,查地图等。

2.索引类型

是最基本的索引,它没有任何限制。

与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。

是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引

主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。

3.索引优化

SQL慢查询的优化

技术分享图片

1.如何捕获低效sql

1)slow_query_log

这个参数设置为ON,可以捕获执行时间超过一定数值的SQL语句。

2)ong_query_time

当SQL语句执行时间超过此数值时,就会被记录到日志中,建议设置为1或者更短。

3)slow_query_log_file

记录日志的文件名。

4)log_queries_not_using_indexes

这个参数设置为ON,可以捕获到所有未使用索引的SQL语句,尽管这个SQL语句有可能执行得挺快。

2.慢查询优化的基本步骤

1)先运行看看是否真的很慢,注意设置SQL_NO_CACHE

2)where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高

3)explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)

4)order by limit 形式的sql语句让排序的表优先查

5)了解业务方使用场景

6)加索引时参照建索引的几大原则

7)观察结果,不符合预期继续从1开始分析

2.优化原则

数据库表优化

数据表拆分:主要就是垂直拆分和水平拆分。

水平切分:将记录散列到不同的表中,各表的结构完全相同,每次从分表中查询, 提高效率。

垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。

总之:

Mysql的优化主要就在于:索引的优化,sql语句的优化,表的优化,在高并发网络环境下,除了优化数据库外,还会涉及到分布式缓存,CDN,数据库读写分离等高并发优化技术。

你可能也喜欢:

  1. 阿里P8架构师谈:MySQL有哪些存储引擎,各自的优缺点,应用场景
  2. 阿里P8架构师谈:MySQL行锁、表锁、悲观锁、乐观锁的特点与应用
  3. 阿里P8架构师谈:MySQL数据库的索引原理、与慢SQL优化的5大原则
  4. 阿里P8架构师谈:数据库分库分表、读写分离的原理实现,使用场景
  5. 去腾讯等BAT面试完的Mysql面试55题总结,含答案大赠送!
  6. 最全MySQL面试60题和答案

没钱没人脉也能轻松入门,让你每年多赚10万!

技术分享图片

阿里P8架构师谈:MySQL慢查询优化、索引优化、以及表等优化总结

原文:https://www.cnblogs.com/liuyongzhi/p/12835505.html

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯