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

前言

解决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的性能，满足业务的需求。然而，值得注意的是，每个数据库和应用都有其特殊性，我们在进行性能优化时，需要根据实际情况进行调整和优化