关于数据库优化你知道多少?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
日志服务 SLS,月写入数据量 50GB 1个月
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 关于数据库优化你知道多少?

关于数据库优化你知道多少?


文章目录

什么是数据库优化

MySQL数据库优化是多方面的,原则是减少系统的瓶颈,减少资源的占用,增加系统的反应速度。

针对于数据库,我们可以通过使用下面方法进行优化:

  • 通过优化文件系统,提高磁盘I\O的读写速度;
  • 通过优化操作系统调度策略,提高MySQL在高负荷情况下的负载能力;
  • 优化表结构、索引、查询语句等使查询响应更快;

针对于查询语句,我们可以通过优化查询操作:

  • 针对查询,我们可以通过使用索引、使用连接代替子查询的方式来提高查询速度。
  • 针对慢查询,我们可以通过分析慢查询日志,来发现引起慢查询的原因,从而有针对性的进行优化。
  • 针对插入,我们可以通过禁用索引、禁用检查等方式来提高插入速度,在插入之后再启用索引和检查。
  • 针对数据库结构,我们可以通过将字段很多的表拆分成多张表、增加中间表、增加冗余字段等方式进行优化。

好多啊,下面我们来看看里面的细节:

image.png

优化MySQL的查询

使用索引:

如果查询时没有使用索引,查询语句将扫描表中的所有记录。在数据量大的情况下,这样查询的速度会很慢。如果使用索引进行查询,查询语句可以根据索引快速定位到待查询记录,从而减少查询的记录数,达到提高查询速度的目的。

索引可以提高查询的速度,但并不是使用带有索引的字段查询时索引都会起作用。有几种特殊情况,在这些情况下有可能使用带有索引的字段查询时索引并没有起作用。

  1. 使用LIKE关键字的查询语句
    在使用LIKE关键字进行查询的查询语句中,如果匹配字符串的第一个字符为“%”,索引不会起作用。只有“%”不在第一个位置,索引才会起作用。(最左前缀原则)
  2. 使用多列索引的查询语句
    MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引,只有查询条件中使用了这些字段中的第1个字段时索引才会被使用。(最左前缀原则)
  3. 使用OR关键字的查询语句
    查询语句的查询条件中只有OR关键字,且OR前后的两个条件中的列都是索引时,查询中才使用索引。否则,查询将不使用索引。

注意:并不是有OR不能使用索引。

优化子查询

使用子查询可以进行SELECT语句的嵌套查询,即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询虽然可以使查询语句很灵活,但执行效率不高。执行子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表。因此,子查询的速度会受到一定的影响。如果查询的数据量比较大,这种影响就会随之增大。

在MySQL中,可以使用连接(JOIN)查询来替代子查询。连接查询不需要建立临时表,其速度比子查询要快,如果查询中使用索引,性能会更好

优化MySQL的插入

影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。针对这些情况,可以分别进行优化。

MyISAM引擎

对于MyISAM引擎的表,常见的优化方法如下:

  1. 禁用索引
    对于非空表,插入记录时,MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据,建立索引会降低插入记录的速度。为了解决这种情况,可以在插入记录之前禁用索引,数据插入完毕后再开启索引。对于空表批量导入数据,则不需要进行此操作,因为MyISAM引擎的表是在导入数据之后才建立索引的。
  2. 禁用唯一性检查
    插入数据时,MySQL会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响,可以在插入记录之前禁用唯一性检查,等到记录插入完毕后再开启。
  3. 使用批量插入
    插入多条记录时,可以使用一条INSERT语句插入一条记录,也可以使用一条INSERT语句插入多条记录。使用一条INSERT语句插入多条记录的情形如下,而这种方式的插入速度更快。
INSERT INTO fruits VALUES 
('x1', '101', '啦啦啦', '5.7'), 
('x2', '101', '咕咕咕', '5.7'), 
('x3', '101', '呼呼呼', '5.7');
  1. 使用LOAD DATA INFILE批量导入
    当需要批量导入数据时,如果能用LOAD DATA INFILE语句,就尽量使用。因为LOAD DATA INFILE语句导入数据的速度比INSERT语句快。

InnoDB引擎

对于InnoDB引擎的表,常见的优化方法如下:

  1. 禁用唯一性检查
    插入数据之前执行 set unique_checks=0 来禁止对唯一索引的检查,数据导入完成之后再运行 set unique_checks=1 。这个和MyISAM引擎的使用方法一样。
  2. 禁用外键检查
    插入数据之前执行禁止对外键的检查,数据插入完成之后再恢复对外键的检查。
  3. 禁用事务自动提交
    插入数据之前禁止事务的自动提交,数据导入完成之后,执行恢复自动提交操作。

海量数据处理优化

表中包含几千万条数据该怎么办?

建议按照如下顺序进行优化:

  1. 优化SQL和索引;
  2. 增加缓存,如memcached、redis;
  3. 读写分离,可以采用主从复制,也可以采用主主复制;
  4. 使用MySQL自带的分区表,这对应用是透明的,无需改代码,但SQL语句是要针对分区表做优化的;
  5. 做垂直拆分,即根据模块的耦合度,将一个大的系统分为多个小的系统;
  6. 做水平拆分,要选择一个合理的sharding key,为了有好的查询效率,表结构也要改动,做一定的冗余,应用也要改,sql中尽量带sharding key,将数据定位到限定的表上去查,而不是扫描全部的表。

MySQL的慢查询优化

慢查询概念

慢查询,顾名思义,执行很慢的查询。有多慢?超过 long_query_time 参数设定的时间阈值(默认10s),就被认为是慢的,是需要优化的。慢查询被记录在慢查询日志里。

然而,慢查询日志默认是不开启的,也就是说一般人没玩过这功能。如果你需要优化SQL语句,就可以开启这个功能,它可以让你很容易地知道哪些语句是需要优化的(想想一个SQL要10s就可怕)。

优化MySQL的慢查询,可以按照如下步骤进行:

开启慢查询日志:

MySQL中慢查询日志默认是关闭的,可以通过配置文件my.ini或者my.cnf中的log-slow-queries选项打开,也可以在MySQL服务启动的时候使用 --log-slow-queries[=file_name] 启动慢查询日志。

启动慢查询日志时,需要在my.ini或者my.cnf文件中配置long_query_time选项指定记录阈值,如果某条查询语句的查询时间超过了这个值,这个查询过程将被记录到慢查询日志文件中。

分析慢查询日志:

直接分析mysql慢查询日志,利用explain关键字可以模拟优化器执行SQL查询语句,来分析sql慢查询语句。

1. 索引没起作用的情况

在使用LIKE关键字进行查询的查询语句中,如果匹配字符串的第一个字符为“%”,索引不会起作用。只有“%”不在第一个位置,索引才会起作用(最左前缀原则)。

MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引,只有查询条件中使用了这些字段中的第1个字段时索引才会被使用(最左前缀原则)。

查询语句的查询条件中只有OR关键字,且OR前后的两个条件中的列都是索引时,查询中才使用索引。否则,查询将不使用索引。

2. 优化数据库结构

对于字段比较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时,会由于使用频率低的字段的存在而变慢。

对于需要经常联合查询的表,可以建立中间表以提高查询效率。通过建立中间表,把需要经常联合查询的数据插入到中间表中,然后将原来的联合查询改为对中间表的查询,以此来提高查询效率。

3. 分解关联查询

很多高性能的应用都会对关联查询进行分解,就是可以对每一个表进行一次单表查询,然后将查询结果在应用程序中进行关联,很多场景下这样会更高效。

4. 优化LIMIT分页

当偏移量非常大的时候,例如可能是limit 10000,20这样的查询,这是mysql需要查询10020条然后只返回最后20条,前面的10000条记录都将被舍弃,这样的代价很高。

优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描,而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。


相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
4月前
|
关系型数据库 MySQL 数据库连接
Django数据库配置避坑指南:从初始化到生产环境的实战优化
本文介绍了Django数据库配置与初始化实战,涵盖MySQL等主流数据库的配置方法及常见问题处理。内容包括数据库连接设置、驱动安装、配置检查、数据表生成、初始数据导入导出,并提供真实项目部署场景的操作步骤与示例代码,适用于开发、测试及生产环境搭建。
142 1
|
16天前
|
缓存 Java 应用服务中间件
Spring Boot配置优化:Tomcat+数据库+缓存+日志,全场景教程
本文详解Spring Boot十大核心配置优化技巧,涵盖Tomcat连接池、数据库连接池、Jackson时区、日志管理、缓存策略、异步线程池等关键配置,结合代码示例与通俗解释,助你轻松掌握高并发场景下的性能调优方法,适用于实际项目落地。
208 4
|
3月前
|
机器学习/深度学习 SQL 运维
数据库出问题还靠猜?教你一招用机器学习优化运维,稳得一批!
数据库出问题还靠猜?教你一招用机器学习优化运维,稳得一批!
104 4
|
7月前
|
SQL 关系型数据库 MySQL
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
|
8月前
|
关系型数据库 数据库 数据安全/隐私保护
云数据库实战:基于阿里云RDS的Python应用开发与优化
在互联网时代,数据驱动的应用已成为企业竞争力的核心。阿里云RDS为开发者提供稳定高效的数据库托管服务,支持多种数据库引擎,具备自动化管理、高可用性和弹性扩展等优势。本文通过Python应用案例,从零开始搭建基于阿里云RDS的数据库应用,详细演示连接、CRUD操作及性能优化与安全管理实践,帮助读者快速上手并提升应用性能。
|
9月前
|
缓存 NoSQL JavaScript
Vue.js应用结合Redis数据库:实践与优化
将Vue.js应用与Redis结合,可以实现高效的数据管理和快速响应的用户体验。通过合理的实践步骤和优化策略,可以充分发挥两者的优势,提高应用的性能和可靠性。希望本文能为您在实际开发中提供有价值的参考。
190 11
|
9月前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
1487 1
|
10月前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
231 11
|
11月前
|
SQL 存储 BI
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句