MySQL慢查询优化、索引优化、以及表等优化详解

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。

关注△mikechen的互联网架构△,10年+BAT架构经验倾囊相授


image.png

大家好,我是 mikechen | 陈睿

上篇写了最全性能优化详解(9大必备大厂优化方案),把整个性能优化的框架建立起来了。

本篇,我主要讲解重要的MySQL优化方案,这里会涉及到慢查询、索引、表设计等的优化方式,下面详解这些关键点@mikechen

01 MySQL优化概述

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

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

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

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

02 MySQL优化方案

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

image.png

索引优化

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.索引优化

  • 只要列中含有NULL值,就最好不要在此例设置索引,复合索引如果有NULL值,此列在使用时也不会使用索引
  • 尽量使用短索引,如果可以,应该制定一个前缀长度
  • 对于经常在where子句使用的列,最好设置索引,这样会加快查找速度
  • 对于有多个列where或者order by子句的,应该建立复合索引
  • 对于like语句,以%或者‘-’开头的不会使用索引,以%结尾会使用索引
  • 尽量不要在列上进行运算(函数操作和表达式操作)
  • 尽量不要使用not in和<>操作

03 SQL慢查询优化

image.png

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.优化原则

  • 查询时,能不要就不用,尽量写全字段名
  • 大部分情况连接效率远大于子查询
  • 多使用explain和profile分析查询语句
  • 查看慢查询日志,找出执行时间长的sql语句优化
  • 多表连接时,尽量小表驱动大表,即小表 join 大表
  • 在千万级分页时使用limit
  • 对于经常使用的查询,可以开启缓存

04 数据库表优化

数据库表优化

  • 表的字段尽可能用NOT NULL
  • 字段长度固定的表查询会更快
  • 把数据库的大表按时间或一些标志分成小表
  • 将表拆分

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

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

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

总之,Mysql的优化主要就在于:

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

以上,是MySQL慢查询优化、索引优化、以及表等的优化详解,欢迎评论区留言交流或拓展。

我是 mikechen | 陈睿 ,关注【mikechen的互联网架构】,10年+BAT架构技术倾囊相授。

本文已同步我的技术博客 www.mikechen.cc,更新至我原创的《30W+字大厂架构技术合集》中。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
16天前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
1月前
|
缓存 关系型数据库 MySQL
MySQL执行计划选择策略:揭秘查询优化的艺术
【10月更文挑战第15天】 在数据库性能优化中,选择最优的执行计划是提升查询效率的关键。MySQL作为一个强大的关系型数据库管理系统,提供了复杂的查询优化器来生成执行计划。本文将深入探讨如何选择合适的执行计划,以及为什么某些计划更优。
68 2
|
25天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
56 3
|
26天前
|
搜索推荐 关系型数据库 MySQL
mysql like查询优化
通过合理的索引设计、使用全文索引、优化查询结构以及考虑分片和分区表,可以显著提高MySQL中 `LIKE`查询的性能。针对不同的应用场景选择合适的优化策略,能够有效地提升数据库查询效率,减少查询时间。希望这些方法和技巧能帮助您优化MySQL数据库中的模糊查询。
104 4
|
27天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
52 1
|
28天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
160 1
|
29天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
68 0
|
19天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
34 1
|
22天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
36 4
|
2月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
66 3
Mysql(4)—数据库索引