«

MYSQL优化实战:15招让你的数据库性能飞升

时间:2024-2-21 13:45     作者:Anglei     分类: 数据库


前言

解决MySQL性能瓶颈非常重要。通过优化MySQL性能,可以保证系统稳定运行、提供良好的用户体验、确保业务正常运营,并为未来的扩展和增长奠定基础。文末有彩蛋!!

优化

1.不要select *

通常在程序里面,是不建议使用select *的,具体使用哪些字段,查哪些字段即可,主要有以下几方面原因:

性能问题:当查询大量数据时,使用 SELECT * 会返回表中的所有列,这可能导致大量数据的传输,并增加处理时间。如果只需要表中的特定列,明确指定这些列名可以显著提高查询性能。

数据泄露风险:如果查询结果意外地暴露了敏感数据(例如,通过日志、错误消息或调试工具),使用 SELECT * 可能会暴露表中的所有列,而不是只暴露需要的列。这增加了数据泄露的风险。

数据库大小:随着时间的推移,表中的列可能会增加或减少。使用 SELECT * 可能会导致查询返回不必要的数据,从而增加数据库的大小。

维护问题:如果表的结构发生变化(例如,添加或删除列),使用 SELECT * 的查询将不会自动更新。这可能会导致查询返回错误的结果或导致应用程序错误。

2.where中避免使用or条件

在语句中使用 OR条件可能会导致性能问题,尤其是在数据库大表中,当使用OR连接多个查询条件时,查询优化解释器就可能无法有效地使用索引,导致全表扫描,从而降低查询性能,总体而言,使用OR可能会导致以下几方面的问题:

索引失效:使用 OR 条件连接列时,如果列上有索引,查询优化器可能会决定不使用这些索引,因为优化器认为全表扫描更快,这会导致查询性能下降。

性能下降:在数据库大表中,使用OR条件可能显著的导致查询性能下降,因为数据库需要扫描更多的数据来查找满足条件的行。

无法利用索引优势:即使某些列上有索引,查询优化器也无法有效地使用这些索引来加速查询,因为OR条件导致无法利用索引的优势。

所以SQL语句中应该尽量避免使用OR,可改为以下方式使用:

使用 UNION 替代 OR:将OR 条件拆分为多个查询,并使用 UNION 将结果合并。每个查询可以单独优化并使用索引,从而提高性能。

创建复合索引:如果经常在多个列上使用 OR 条件进行查询,可以考虑创建一个复合索引来加速查询。复合索引包含所有涉及的列,可以使查询优化器更有效地使用索引。

重构查询:尝试重新编写查询,避免使用 OR 条件连接列。可以使用其他逻辑或联接操作来达到相同的效果。

分析查询执行计划:使用数据库提供的工具分析查询的执行计划,查看是否可以优化索引或查询结构来提高性能。

3.尽量使用数字类型替代字符串类型

ID主键使用int或者bigint类型,性别,状态类等字段通常可以使用tinyint类型。使用数字类型,具体有以下方面的优点:

节省存储空间(通常比字符串类型更紧凑)。

计算速度快(数字类型的操作通常比字符串操作更快)。

容易进行数值比较和排序。

4.使用varchar替代char

char:定长。不论实际存储的字符串长度如何,都会占用固定长度的空间。例如,CHAR(10) 会始终占用 10 个字符的空间,即使实际存储的数据只有 5 个字符。由于是定长的,所以在某些情况下可能比 VARCHAR 更快,尤其是当数据长度变化不大时。适用于那些长度几乎不变或长度变化不大的字段,例如星期('星期一' 到 '星期日')。CHAR 类型的字段默认值只接受字符串('')。如果插入的数据长度小于定义的长度,MySQL 会自动在其右侧添加空格以达到定义的长度。

varchar:变长。根据实际存储的字符串长度来分配空间。例如,VARCHAR(10) 可以存储最多 10 个字符的字符串,但实际使用的空间会根据实际数据长度而变化。对于经常变长的数据,VARCHAR 可能更有优势,因为它不会浪费空间来存储不需要的数据。适用于长度经常变化或长度不固定的字段,例如用户名、地址或描述字段。VARCHAR 类型的字段默认值只接受字符串('')或 NULL。如果插入的数据长度小于定义的长度,MySQL 会截断超出的部分。

5.!=跟<>尽量避免使用

标准性:尽管 != 和 <> 在许多数据库系统中都有效,但并不是所有的数据库系统都支持这两个操作符。为了确保最大的兼容性和可移植性,最好使用 ANSI SQL 标准中定义的操作符。

可读性:对于不熟悉 SQL 的开发者或数据库管理员来说,!= 和 <> 可能会造成混淆。使用 ANSI SQL 标准中的操作符(如 <>)可以增加代码的可读性。

性能:在某些数据库系统中,使用非标准的操作符可能会影响查询的性能。尽管大多数现代数据库系统会优化查询,但使用标准操作符可以减少潜在的性能问题。

维护:如果代码需要在不同的数据库系统之间迁移,使用标准操作符可以简化迁移过程,因为它们在不同的系统中具有一致的行为。

6.尽量避免使用左连及右连,而使用内连

inner join :内连接,只保留两张表中完全匹配的结果集;
left join: 会返回左表所有的行,即使在右表中没有匹配的记录;
right join:会返回右表所有的行,即使在左表中没有匹配的记录;
如果inner join是等值连接,返回的行数比较少,所以性能相对会好一点;
使用了左连接,左边表数据结果尽量小,条件尽量放到左边处理,意味着返回的行数可能比较少;
这是mysql优化原则,就是小表驱动大表,小的数据集驱动大的数据集,从而让性能更优;

7.合理使用group by

选择适当的列进行分组:

确定哪些列最适合进行分组。通常,分组列应该是那些在WHERE子句中用于过滤数据的列,或者在聚合函数中使用的列。

避免在GROUP BY子句中使用不必要的列,这可能导致性能下降。

使用聚合函数:

GROUP BY通常与聚合函数(如COUNT(), SUM(), AVG()等)一起使用,以对每个分组执行计算。

确保在使用聚合函数时选择正确的列。例如,如果你想计算每个部门的员工数量,你应该在GROUP BY子句中使用部门列,并在聚合函数中使用员工ID列。

排序结果:

使用ORDER BY子句对分组后的结果进行排序。这有助于按照特定的顺序显示结果,使数据更易于理解。

避免使用隐式类型转换:

确保在比较和连接不同数据类型的列时使用适当的类型转换。避免隐式类型转换可能导致意外的结果或性能问题。

优化查询性能:

考虑查询的执行计划和索引使用情况。使用EXPLAIN关键字可以帮助分析查询的执行计划,并确定是否需要优化索引或查询结构。

尽量减少在GROUP BY子句中使用非索引列,这有助于提高查询性能。

注意NULL值处理:

在处理包含NULL值的列时,要特别小心。GROUP BY和聚合函数通常对NULL值有特定的处理方式。了解并正确处理NULL值对于确保查询结果的准确性至关重要。

合理使用子查询和临时表:

在某些情况下,将复杂的GROUP BY查询分解为多个步骤,使用子查询或临时表,可以提高查询性能和可读性。

8.多条插入使用批量提升性能

如:insert into 表(字段1)values('值1'),('值2'),('值3');

9.表连接及索引

表连接不宜太多,一般5个以内

关联的表个数越多,编译的时间和开销也就越大
每次关联内存中都生成一个临时表
应该把连接表拆开成较小的几个执行,可读性更高
如果一定需要连接很多表才能得到数据,那么意味着这是个糟糕的设计了
阿里规范中,建议多表联查三张表以下
索引不宜太多,一般5个以内

索引并不是越多越好,虽其提高了查询的效率,但却会降低插入和更新的效率;
索引可以理解为一个就是一张表,其可以存储数据,其数据就要占空间;
索引表的数据是排序的,排序也是要花时间的;
insert或update时有可能会重建索引,如果数据量巨大,重建将进行记录的重新排序,所以建索引需要慎重考虑,视具体情况来定;
一个表的索引数最好不要超过5个,若太多需要考虑一些索引是否有存在的必要;

10.索引最左原则

如果使用了复合索引(即多个列的索引),则最左边的列在索引中最有用。也就是说,只有当查询条件中使用了复合索引的最左边的列时,索引才会被有效地利用。

例如,假设有一个复合索引(A, B, C),如果查询条件中只涉及列A,那么索引会被完全利用;如果查询条件中同时涉及列A和列B,那么索引仍然会被利用;但如果查询条件中只涉及列B或列C,那么索引不会被利用。

因此,在设计复合索引时,应该将查询中最常用于条件判断的列放在最左边,以提高索引的利用率。同时,如果查询中涉及到的列不在复合索引的最左边,可以考虑创建单列索引或重新设计复合索引。

11.合理使用like查询

前缀查询:当使用LIKE进行查询时,如果通配符%放在前面,数据库将无法使用索引,导致全表扫描。例如,查询LIKE '%xyz'将导致全表扫描,而查询LIKE 'xyz%'可以利用索引。因此,尽量将通配符放在字符串的末尾。

避免使用过多的通配符:如果LIKE查询中通配符过多,可能会导致数据库无法有效地使用索引。例如,查询LIKE '%xyz%'可能导致全表扫描。尽量避免在LIKE查询中使用过多的通配符。

考虑使用全文搜索:对于需要执行复杂的文本搜索的应用程序,可以考虑使用MySQL的全文搜索功能。全文搜索提供了更高效和准确的文本搜索功能,可以处理更复杂的查询模式。

使用适当的字符集和校对规则:在执行LIKE查询时,选择适当的字符集和校对规则可以提高查询的准确性和性能。确保字符集和校对规则与您的数据和查询需求相匹配。

考虑使用覆盖索引:如果您的查询中同时使用了WHERE和ORDER BY子句,可以考虑创建覆盖索引,以减少数据读取的开销并提高查询性能。覆盖索引包含查询中所需的所有列,使得数据库可以直接从索引中获取所需的数据,而不是从数据表中读取。

避免在列上使用函数或表达式:在LIKE查询中,避免在列上使用函数或表达式,这会导致索引失效并可能导致全表扫描。例如,查询LIKE CONCAT(column_name, '%')将无法利用索引。

注意数据类型匹配:确保LIKE查询中的数据类型与列的数据类型匹配。类型不匹配可能导致隐式类型转换,这可能会影响查询性能和结果准确性

12.分析sql的执行过程

type

system:表仅有一行,基本用不到;
const:表最多一行数据配合,主键查询时触发较多;
eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型;
ref:对于每个来自于前面表的行组合,所有匹配索引值的行将从这张表中读取;
range:只检索给定范围的行,使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range;
index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小;
all:全表扫描;
性能排名:system > const > eq_ref > ref > range > index > all。
实际sql优化中,最后达到ref或range级别。
Extra常用关键字

Using index:只从索引树中获取信息,而不需要回表查询;
Using where:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。需要回表查询。
Using temporary:mysql常建一个临时表来容纳结果,典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时;

13.表设计垂直拆分,动静分离

用户表现有表结构如上,由字段可知,ID,USER_NAME,STATUS,PWD为不经常改动字段,EMAIL,NICK_NAME及CONTACT为长改字段,于是可把这几个字段拆分到用户表的附属表中,并且一key/value结构存储。如图:

14.热数据表多表冗余水平拆分

如订单表,可以按周,按月水平拆分。按周按月水平拆分还可以做多表冗余,比如一周一个表的同时,一天再一个表,当天的差当天的表,其它查周表。多表写入,天表隔天删除。

15.delete时不要全量删除,加时间条件

降低写错SQL的代价:

清空表数据可不是小事情,一个手抖全没了,删库跑路?如果加limit,删错也只是丢失部分数据,可以通过binlog日志快速恢复的。

SQL效率很可能更高:

SQL中加了时间,时间又带上索引的话,删除效率也会更高

避免长事务:

delete执行时,如果age加了索引,MySQL会将所有相关的行加写锁和间隙锁,所有执行相关行会被锁住,如果删除数量大,会直接影响相关业务无法使用。

数据量大的话,容易把CPU打满

锁表

一次性删除太多数据,可能造成锁表,会有lock wait timeout exceed的错误,所以建议分批操作。

结尾

MySQL性能优化是一项需要综合考虑多种因素的工作。通过理解并优化查询语句、合理的数据库设计和配置、定期进行数据库维护以及利用性能监控工具,我们可以有效地提高MySQL的性能,满足业务的需求。然而,值得注意的是,每个数据库和应用都有其特殊性,我们在进行性能优化时,需要根据实际情况进行调整和优化

本文完结,相关标签: mysql

 版权所有:Anglei
 文章标题:MYSQL优化实战:15招让你的数据库性能飞升
 除非注明,本站文章如未特殊说明均为 MAXADA社区知识库 原创,且版权所有,请勿用于任何商业用途。

推荐阅读:

看完后感想如何?

路过(0)

雷人(0)

握手(0)

鲜花(0)

鸡蛋(0)
分享到: