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
推荐阅读:
![]() 路过(0) |
![]() 雷人(0) |
![]() 握手(0) |
![]() 鲜花(0) |
![]() 鸡蛋(0) |