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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 数据库系列课程(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索引 :只能支持等于查询,无法支持范围查询

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
239
分享
相关文章
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了:Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
MySQL底层概述—7.优化原则及慢查询
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
98 42
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
54 25
从MySQL优化到脑力健康:技术人与效率的双重提升
聊到效率这个事,大家应该都挺有感触的吧。 不管是技术优化还是个人状态调整,怎么能更快、更省力地完成事情,都是我们每天要琢磨的事。
58 23
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
Aurora MySQL负载突增应对策略与优化方案
通过以上策略,企业可以有效应对 Aurora MySQL 的负载突增,确保数据库在高负载情况下依然保持高性能和稳定性。这些优化方案涵盖了从架构设计到具体配置和监控的各个方面,能够全面提升数据库的响应速度和处理能力。在实际应用中,应根据具体的业务需求和负载特征,灵活调整和应用这些优化策略。
53 22

热门文章

最新文章

推荐镜像

更多
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等