临沧永德网站建设电子商务公司,杭州外贸网站建设,做网站常用的小语种有哪些,网站的欢迎页怎么做#x1f61c;作 者#xff1a;是江迪呀✒️本文关键词#xff1a;MySQL、SQL优化、阶段性优化☀️每日 一言#xff1a;我们要把懦弱扼杀在摇篮中。 一、前言
我们在做系统的过程中#xff0c;难免会遇到页面查询速度慢#xff0c;性能差的问题#xff0c;… 作 者是江迪呀✒️本文关键词MySQL、SQL优化、阶段性优化☀️每日 一言我们要把懦弱扼杀在摇篮中。 一、前言
我们在做系统的过程中难免会遇到页面查询速度慢性能差的问题当遇到这些问题时我们就不得不想到SQL优化。SQL优化可以有效的提升系统的性能提高用户的体验降低资源消耗比如CPU的使用率、内存消耗的情况从而能够支持更大规模的数据。但是SQL优化是要分阶段的只有在某个阶段中优化遇到了瓶颈才会进入下个阶段寻找优化的点。
二、数据准备
如果你没有大数据量进行测试可以通过下面的存储过程生成白玩甚至是千万级别的数据量。代码如下
-- 创建班级表
CREATE TABLE class (class_id INT PRIMARY KEY,class_name VARCHAR(50) NOT NULL
);-- 创建课程表
CREATE TABLE course (course_id INT PRIMARY KEY,course_name VARCHAR(50) NOT NULL
);-- 创建学生表并将班级表和课程表作为外键关联
CREATE TABLE student (student_id INT PRIMARY KEY,student_name VARCHAR(50) NOT NULL,age INT,gender VARCHAR(10),class_id INT,course_id INT,create)time DATETIME,FOREIGN KEY (class_id) REFERENCES class(class_id),FOREIGN KEY (course_id) REFERENCES course(course_id)
);
-- 生成100w数据的存储过程
CREATE PROCEDURE InsertTestData2()
BEGINDECLARE i INT DEFAULT 1;WHILE i 1000000 DOINSERT INTOclass (class_id, class_name)VALUES(i, CONCAT(班级,i));INSERT INTOcourse (course_id, class_name)VALUES(i,CONCAT(班级,i));INSERT INTOstudent (student_id, student_name, age, gender,class_id,course_id)VALUES(i, CONCAT(姓名,i), FLOOR(RAND() * 4) 15, RAND(), i, i);SET i i 1;END WHILE;
END三、阶段一从代码和业务场景方面优化
这个阶段最为重要也是可以优化的地方最多的。
1不要写过于复杂的SQL
示例
当产品要求在一个学生信息页面中需要展示以下列学生姓名、性别、班级、课程那你是不是要这样写SQL
select s.student_name,s.gender ,c.class_name ,c2.course_name
from student s
left join class c on s.class_id c.class_id
left join course c2 on c2.course_id s.course_id 分析原因
上面的SQL没有一点问题但是随着student表中的数据日益增加SQL查询必定变慢更何况这只是关联三张表遇到更加复杂的业务场景甚至要关联10几张表这样查询效率是很慢的。
优化建议
将关联的表拆分开使用代码处理关联关系。比如你先查询出student表中的数据拿着class_id去查询class表再拿着course_id去查询course表中的数据最后将数据组装起来返回。
这样做的好处如下
一是可以减少关联的表提高SQL查询的性能。二是可以提高SQL的可读性如果写个十几二十行的SQL再加上子查询啥的我感觉维护的人一定背地里骂娘。
2分页查询优化
示例 如果产品要求页面都是需要分页的并且或根据某个字段进行排序SQL可能是这样的
select s.student_name,s.gender ,c.class_name ,c2.course_name
from student s
left join class c on s.class_id c.class_id
left join course c2 on c2.course_id s.course_id
order by s.create_time DESC
limit 10我就要开始优化啦。什么的代码依然是没什么问题但是如果三张表中的数据都是百万级别的那就说不好了查询会很慢。
分析原因
上面查询之所以会慢主要有一下几点
关联的表太多。数据量太大每个表都是全表扫描。排序order by的执行顺序实在limit之前的故而是全表数据进行排序后再进行截取分页。
优化建议
将关联的表拆分开来先查询出student表中的数据拿着class_id去查询class表再拿着course_id去查询course表中的数据最后将数据组装起来返回。这样做在查询student表时字段可以减少很多。拿着course_id后续查询的表只会查询10条查询压力会小很多。切记不要在student表中进行order by排序使用代码进行排序代码排序只会处理limit后的10条数据性能会提高很多。
select s.student_id,s.student_name,s.class_id,s.gender
from student s
limit 10代码进行排序select c.class_name
from class
where class_id ${class_id} select c.course_name
from course
where course_id ${course_id} 3避免大事务
在某些业务场景中我们需要保证业务的原子性比如一个场景 用户下单商品并使用优惠券增加积分假设此业务需要操作四张表订单、库存、优惠券、积分。在处理业务逻辑时不应当将四个表的操作都揉成一个原子性操作而应该是订单和减库存保持一个原子性操作减去优惠券和增加积分是一个原子性操作保证事务的最小粒度避免锁定过多的资源。这里场景只是举个例子实际上订单、库存、优惠券、积分应该在不同的系统
四、阶段二从SQL方面优化
1避免使用子查询
子查询弊端
多次执行子查询通常会在每次主查询的行被处理时执行一次这可能导致多次查询操作增加数据库的负担。循环执行子查询会导致主表扫描多次。
优化建议
使用连接查询使用join进行查询。在一些情况下可以使用EXISTS或NOT EXISTS子查询来替代IN或NOT IN子查询因为它们更有效。
为什么使用EXISTS 或 NOT EXISTS 子查询来替代 IN 或 NOT IN 子查询可以提高性能
处理 NULL 值 NOT IN 子查询中如果存在 NULL 值会导致整个 NOT IN 条件无法判断。而 NOT EXISTS 并不会受到 NULL 值的影响因为它只关心子查询是否返回结果。性能优化 NOT IN 子查询可能需要对外部查询中的每个值与子查询中的每个值进行比较这可能导致大量的比较操作尤其是在子查询的结果集较大时。而 NOT EXISTS 子查询在找到第一个匹配项后就会停止比较这可以提高效率。查询优化器的作用 数据库查询优化器在处理 NOT EXISTS 时通常会使用半连接(semi-join)或反连接(anti-join)的方式而在处理 NOT IN 时可能会选择更简单的方式这会导致性能下降。
2避免使用SELECT *
select * 弊端
降低索引利用率 会导致数据库无法充分利用索引。增加磁盘IO 查询的数据量越大需要从磁盘读取的数据也越多。如果只查询需要的列可以减少磁盘IO提高查询速度。降低缓存效率 数据库系统在执行查询时会使用缓存来存储查询结果如果查询的数据量过大会降低缓存的效率影响其他查询的性能。减低数据库安全性 查询数据库的所有字段并返回到客户端很容易被看储底层数据库结构设计。
优化建议
业务场景中按需返回数据库字段。
3避免使用函数
SQL中使用函数的弊端
函数计算的开销 使用函数会导致数据库对每一行数据进行计算这会增加查询的计算开销。特别是在大数据表中频繁使用函数可能会导致性能下降。索引无法使用 在查询条件中使用函数会导致数据库无法充分利用索引。通常情况下索引是在列上建立的而不是在函数的结果上。如果在查询条件中使用函数数据库可能无法使用索引从而影响查询性能。查询执行计划变得复杂 使用函数可能会导致查询的执行计划变得更加复杂从而增加了数据库优化器的工作量降低了查询性能。难以阅读和维护 降低SQL的可读性有些时候系统维护人员并不知道该函数是用来干什么的。
优化建议
可以使用代码来替代并实现SQL函数的功能。
五、阶段三从数据库索引方面优化
1使用索引
数据库索引是一种数据结构用于加快数据库表中数据的检索速度。索引可以类比于书的目录它提供了一种快速访问表中特定数据行的方法从而避免全表扫描提高查询效率。
2 索引类型以及使用场景
B树索引B-Tree Index最常用作用用于加速等值查询、范围查询和排序操作。使用场景适合大多数查询特别是在查询中涉及到等值匹配、范围查询、排序和分组。唯一索引Unique Index作用保证索引列的唯一性避免重复数据。使用场景适合需要确保某一列的值是唯一的情况如主键、唯一约束等。主键索引Primary Key Index作用是一种唯一索引同时标识了表的主键。使用场景每个表应该有一个主键索引用于唯一标识表中的每一行数据。全文索引Full-Text Index作用用于全文搜索查找文本中的关键字。使用场景适合文本字段的模糊搜索如文章内容、评论等。** 哈希索引Hash Index**作用用于等值查询通过哈希函数映射到索引值。使用场景适合精确匹配的查询不适用于范围查询。空间索引Spatial Index作用用于地理信息和几何数据的查询。使用场景适合存储包含空间坐标信息的数据如地图数据。前缀索引Prefix Index作用用于索引列的前缀节省存储空间。使用场景适合较长的字符串列但可能影响查询性能。联合索引Composite Index作用多个列上的组合索引加速组合查询。使用场景适合多个列的联合查询但顺序很重要查询只能使用索引的最左前缀。
3如何SQL查询是否走索引
使用 EXPLAIN 命令可以分析查询语句的执行计划以便了解查询是如何被优化和执行的。EXPLAIN查询会返回一张表其中包含了查询优化器的执行计划以及每个步骤的详细信息。
id 表示查询中每个操作步骤的标识主要用于标识操作的执行顺序。select_type 表示操作的类型如 SIMPLE简单查询、PRIMARY主查询、SUBQUERY子查询、DERIVED派生表的子查询等。table 表示涉及的表名如果涉及多个表则会依次显示。partitions 表示查询使用的分区。type 表示表访问的类型常见的类型有 ALL 全表扫描效率最低。index 通过索引进行扫描。range 使用索引范围进行扫描。ref 使用非唯一索引来查找匹配行。eq_ref 类似于ref但是使用的是唯一索引。const 使用常量条件进行查询。system 查询表只有一行快速访问。NULL 没有表结果为空。possible_keys 显示可能应用在这张表中的索引。 key 显示实际使用的索引。如果为 NULL则没有使用索引。key_len 表示索引字段的最大长度。ref 显示连接匹配条件的列如果没有则为 NULL。rows 表示预计需要扫描的行数越小越好。filtered 表示根据表连接条件和 where 子句中的条件过滤的行的百分比。Extra 额外的信息可能包括 “Using filesort”使用文件排序、“Using temporary”使用临时表等。
六、阶段四从缓存方面优化
1使用数据库自带的缓存机制
查询缓存 MySQL 查询缓存是一种基于 SQL 查询的结果的缓存机制它可以缓存完全相同的查询语句的结果集。
query_cache_type 1 # 开启查询缓存
query_cache_type 0 # 关闭查询缓存键缓存 MySQL 使用键缓存来存储数据字典中的表、索引和列名等信息这样可以在查询时更快地查找元数据。键缓存是自动开启的不需要手动配置。缓存索引 MySQL 在 InnoDB 存储引擎中提供了缓存索引的功能它可以将索引数据存储在内存中从而加速索引查找。你可以通过配置参数进行设置
innodb_buffer_pool_size 128M # 设置 InnoDB 缓存池大小数据库自带缓存弊端
查询缓存查询缓存的使用在实际情况中往往不太推荐因为它在高并发环境下可能会带来锁竞争同时对于稍微复杂的查询和更新操作可能会导致整个查询缓存被清空。缓存索引增加磁盘压力。
2使用Redis或者Memcached
使用中间件将热点数据存放在缓存中当查询来临时将缓存数据返回从而避免直接打到数据库减轻数据库压力。这种方式是我们在实际开发中最长用的方式在此不过多赘述。
中间件缓存弊端
数据一致性保证。缓存穿透。缓存失效时间合理设置。
七、 阶段五从数据库设计方面优化
1合理设计数据库
如何合理的设计数据库是个非常有技术含量的工作我从一个简单的场景入手说下假如你需要开发一个博客网站那么你如何设计数据库用于博客内容的存储呢拿一些博客网站来说页面先展示文章标题、浏览量、点赞量点击文章后进入文章详情展示具体的内容。 那么我们在设计数据库时就应该想到将标题、浏览量、点赞量放到一张表中去文章内容单独一张表文章内容由于很多查询消耗的性能比较大所以我们应该避免。 通过一个简单的例子说明下数据库设计对数据库优化起着至关重要的作用设计的拉胯再高超的优化手段也无济于事。
2使用合适的数据库类型
使用合适的数据库类型是优化 MySQL 数据库性能的一个重要方面不同的数据类型在存储、查询和计算方面会有不同的影响。 选择合适的整数类型 当存储整数值时应该根据数值的范围来选择合适的整数类型以节省存储空间和提高查询性能。例如如果某个字段的取值范围在0到 255 之间可以选择使用TINYINT UNSIGNED类型来存储而不是使用INT 类型。 选择合适的浮点数类型 如果需要存储小数可以根据精度的要求选择合适的浮点数类型。FLOAT 和DOUBLE 类型可以存储更大范围的数值但是会占用更多的存储空间。应根据业务需要权衡存储空间和精度。 避免使用不必要的文本类型 对于存储长度固定的文本数据可以使用CHAR类型而对于长度可变的文本数据可以使用 VARCHAR 类型。避免使用过大的文本类型因为它们可能导致额外的存储开销和查询性能下降。 使用适当的日期和时间类型 选择合适的日期和时间类型来存储日期和时间信息例如 DATE、TIME、DATETIME 和 TIMESTAMP。使用TIMESTAMP类型可以充分利用 MySQL 的自动更新特性但要注意其范围。 选择合适的枚举类型 当某个字段的取值在一个固定的集合内时可以使用枚举类型。这不仅节省存储空间还可以提高查询性能因为查询和索引枚举类型字段通常更高效。 使用合适的字符集和排序规则 对于需要支持多语言的应用应选择合适的字符集和排序规则。例如utf8mb4 字符集支持更广泛的字符范围但也会占用更多的存储空间。
3分区分表
分区表是一种将数据库表按照某个规则划分为多个子表的技术可以提高查询性能、管理效率以及数据维护。实现步骤如下
选择分区键 分区键是用来划分表的依据通常选择一个与业务相关的字段作为分区键例如日期、地区、用户等。选择合适的分区键可以使查询在特定范围内更加高效。分区类型 MySQL 提供了多种分区类型包括范围分区、列表分区、哈希分区和键值分区。选择分区类型要根据业务需求和查询模式来确定。创建分区表 在创建表的时候使用 PARTITION BY 子句来指定分区方式和分区键。例如以下是一个按照日期范围分区的示例管理分区表 分区表需要特殊的管理和维护。例如当数据增长时可能需要添加新的分区。可以使用 ALTER TABLE 命令来添加分区。查询分区表 查询分区表时数据库会根据查询条件自动选择需要扫描的分区从而提高查询性能。
下面就是一个根据日期进行分区的表切记分区表是一张表分为多个不同的区域可不是多张表
CREATE TABLE sales (sale_id INT AUTO_INCREMENT,sale_date DATE,sale_amount DECIMAL(10, 2),PRIMARY KEY (sale_id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (PARTITION p0 VALUES LESS THAN (2020),PARTITION p1 VALUES LESS THAN (2021),PARTITION p2 VALUES LESS THAN (2022)
);
分区表注意事项
分区表的索引也需要按照分区键来创建以保证查询性能。需要根据业务需求来选择合适的分区策略和分区类型。分区表的设计需要考虑数据均衡避免某个分区数据过多而导致性能问题。
4分库分表
分库分表是处理大数据量的常用手段可以提高数据库的扩展性和性能。分库分表的基本思想是将数据分散存储到多个数据库实例或多张数据表中从而减少单个数据库或表的负载。我们可以使用MyCat工具来实现分库分表如何使用mycat分库分表可以自行百度。
数据库水平分库 将数据按照一定的规则分散存储到不同的数据库中每个数据库独立管理。可以使用分片键如用户ID、时间等来决定数据存放在哪个数据库中。数据库垂直分表 将同一个表中的不同列分散到不同的数据表中从而减小单张表的数据量。例如将一个订单表分成订单基本信息表和订单明细表类似于分区只不过分区是一张表这是多张。
八、阶段六从硬件方面优化
1升级硬件
增加内存RAM 内存是数据库性能的重要因素之一足够的内存可以缓存更多的数据和索引减少磁盘IO从而提高查询速度。适用于需要频繁进行数据读取的场景。使用快速存储设备 使用SSD固态硬盘代替传统的机械硬盘可以显著提高数据的读写速度从而减少IO等待时间。提升CPU性能 数据库查询过程中会进行大量的计算操作提升CPU性能可以加快计算速度从而提高查询效率。、优化网络带宽 如果数据库涉及跨网络的查询优化网络带宽和延迟可以减少数据传输时间。使用多核处理器 如果数据库支持并行查询使用多核处理器可以同时处理多个查询提高处理能力。分布式架构 将数据库分布到多个服务器上采用分布式架构可以减轻单一服务器的负担提高整体查询吞吐量。使用专用硬件 针对特定的任务如数据分析、数据仓库等可以选择使用专用的硬件加速器例如GPU图形处理器等。
想加机器?这考验的就不是你的技术能力了而是说话沟通能力如何说服老板领导同意你升级硬件的方案。
