数据库系列课程(12)-MySQL数据库优化及注意事项

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 数据库系列课程(12)-MySQL数据库优化及注意事项

1.MySQL数据库优化方案

Mysql的优化,大体可以分为三部分:

  • 索引的优化
  • 慢查询优化
  • 表优化

开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。

还可以使用腾讯云MySQL数据库监控:

2.MySQL数据库配置慢查询

参数说明:

  • 「slow_query_log」 慢查询开启状态
  • 「slow_query_log_file」 慢查询日志存放的位置(这个目录需要MySQL的运行帐号的可写权限,一般设置为MySQL的数据存放目录)
  • 「long_query_time」 查询超过多少秒才记录

例子:

  1. 查询慢查询配置
show variables like 'slow_query%';
  1. 查询慢查询限制时间
show variables like 'long_query_time';
  1. 将 slow_query_log 全局变量设置为“ON”状态
set global slow_query_log='ON';
  1. 查询超过1秒就记录
set global long_query_time=1;

查询日志:cat /var/lib/mysql/localhost-slow.log

重启MySQL服务:service mysqld restart

3.索引注意事项

Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看SQL语句的执行效果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句。

CREATE TABLE `user_details` (
  `id` int(11) DEFAULT NULL,
  `user_name` varchar(50) DEFAULT NULL,
  `user_phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `user_details` ADD INDEX user_name_index ( `user_name` )
insert into user_details values(1,'ylw1','15921009245');
insert into user_details values(2,'ylw2','15921009245');
insert into user_details values(3,'ylw3','15921009245');
insert into user_details values(4,'ylw4','15921009245');
insert into user_details values(5,'ylw5','15921009245');
insert into user_details values(6,'ylw6','15921009245');
EXPLAIN select * from user_details WHERE id=1
EXPLAIN select * from user_details WHERE id=1 and  user_name='ylw1';
EXPLAIN select * from user_details WHERE id like '%sss' 
EXPLAIN select * from user_details WHERE id like '%1' 
EXPLAIN select * from user_details WHERE user_name like '1%' 
EXPLAIN select * from user_details WHERE user_name =1; 
EXPLAIN select * from user_details WHERE user_name ='1';

  1. 索引无法存储null值。
  2. 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引。
  3. 对于多列索引,不是使用的第一部分,则不会使用索引。
  4. like查询以%开头。
  5. 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引。
  6. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引。

4. 联合索引为什么需要遵循左前缀原则?

对于多列索引,不是使用的第一部分,则不会使用索引:

CREATE TABLE `user_details` (
  `id` int(11) DEFAULT NULL,
  `user_name` varchar(50) DEFAULT NULL,
  `user_phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (id,user_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into user_details values(1,'ylw1','15921009245');
insert into user_details values(1,'ylw2','15921009245');
insert into user_details values(2,'ylw1','15921009245');
insert into user_details values(2,'ylw2','15921009245');
insert into user_details values(3,'ylw1','15921009245');
insert into user_details values(3,'ylw2','15921009245');
EXPLAIN select * from user_details WHERE id=1
EXPLAIN select * from user_details WHERE id=1 and  user_name='ylw1';
EXPLAIN select * from user_details WHERE  user_name='ylw1';
EXPLAIN select * from user_details WHERE  user_name='ylw1' and  id=1
(1,ylw1 1,ylw2),(2,ylw1 2,ylw2),(3,ylw1 3,ylw2)

如果在一张表中,存在联合索引的话,在根据条件查询的时候必须要加上第一个索引条件。

EXPLAIN select * from user_details WHERE id=1 and  user_name=‘ylw1’;---索引生效
EXPLAIN select * from user_details WHERE  user_name=‘ylw1’; 索引是不生效的

因为索引底层采用B+树叶子节点顺序排列,必须通过左前缀索引才能定位到具体的节点范围。

(1,ylw1 1,ylw2),(2,ylw1 2,ylw2),(3,ylw1 3,ylw2)

5. 表分库为什么能够提高数据库查询效率?

分表分库为什么提高查询的效率?因为会将一张表的数据拆分成多个n张表进行存放,然后再使用第三方中间件(MyCat或者Sharding-JDBC)并行同时查询,然后在交给第三方中间进行组合返回给客户端。

6. MySQLb+树能够存放多少字节数据

由于存储介质的特性,磁盘本身存取就比主存慢很多,再加上机械运动耗费,磁盘的存取速度往往是主存的几百分之一,因此为了提高效率,要尽量减少磁盘I/O。为了达到这个目的,磁盘往往不是严格按需读取,而是每次都会预读,即使只需要一个字节,磁盘也会从这个位置开始,顺序向后读取一定长度的数据放入内存。

这样做的理论依据是计算机科学中著名的局部性原理

  • 当一个数据被用到时,其附近的数据也通常会马上被使用。
  • 程序运行期间所需要的数据通常比较集中。
  • 由于磁盘顺序读取的效率很高(不需要寻道时间,只需很少的旋转时间),因此对于具有局部性的程序来说,预读可以提高I/O效率。
  • 预读的长度一般为页(page)的整倍数。页是计算机管理存储器的逻辑块,硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块,每个存 储块称为一页(在许多操作系统中,页得大小通常为4k),主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时,会触发一个缺页异常,此时系统 会向磁盘发出读盘信号,磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中,然后异常返回,程序继续运行(一般操作系统一页大小为4kb;getconf PAGE_SIZE)。

假设一个节点为1页:

  • 如果从磁盘读取超过1页大小,根据局部性原理与磁盘预读会读出2页大小
  • 如果从磁盘读取小于1页大小,根据局部性原理与磁盘预读会读出1页大小

根据以上规则,如果读取整哈是页的倍数,这样就可以不用浪费,所以B+树的每一个节点是页的倍数是最佳的。

在MySQL中我们的InnoDB页的大小默认是16k,当然也可以通过参数设置:

show variables like 'innodb_page_size';//16384/1024=16kb;

MySQL b+树能够存放多少字节数据:

假设一行为1kb,那么一页可以读取16行数据,一个叶子节点可以存放16条数据

那么非叶子节点存放多少条数据?非叶子节点存放索引值(bigint 8b)和指针(6b)

那么一页 : 16*1024/(8+6)=1170指针

B+树高度为2: 1170*16=18720条数据

B+树高度为3: 1170*1170*16=21902400 条数据

所以在InnoDB中B+树高度一般为1-3层,它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO,所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

7. 为什么MySQL底层使用b+树?

B+树索引 :具有范围查找和前缀查找的能力,相当于二分查找。

Hash索引 :只能支持等于查询,无法支持范围查询

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
13 3
|
5天前
|
SQL 缓存 监控
数据库优化
【10月更文挑战第29天】数据库优化
13 1
|
5天前
|
存储 缓存 关系型数据库
数据库查询优化的注意事项
【10月更文挑战第28天】
13 2
|
5天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
22 1
|
8天前
|
XML Java 数据库连接
如何使用HikariCP连接池来优化数据库连接管理
在Java应用中,高效管理数据库连接是提升性能的关键。本文介绍了如何使用HikariCP连接池来优化数据库连接管理。通过引入依赖、配置参数和获取连接,你可以显著提高系统的响应速度和吞吐量。 示例代码展示了从配置到使用的完整流程,帮助你轻松上手。
31 3
|
7天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
38 0
|
8天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
34 0
|
26天前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
103 6
|
23天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
56 3
Mysql(4)—数据库索引
|
9天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
45 2