SQL调优思路

前言

SQL调优是数据库管理和开发中的关键环节，它涉及到对数据库查询语句的精细调整，以及整个数据库结构的优化。这个过程并不仅仅局限于编写高效的查询语句，而是涉及到数据库的整个生命周期，包括表的设计、索引的创建、以及更高级的架构设计，如主从复制和读写分离策略。在处理大量数据时，还可能涉及到分库分表等技术来提升性能。

SQL调优的目的是多方面的，不仅包括提升查询的响应速度，还包括减少服务器的资源消耗，提高系统的稳定性和可靠性。这通常涉及到对查询计划的分析，以找出可能导致性能瓶颈的环节，并进行相应的优化措施。

表设计优化

在数据库设计中，遵循合理的范式原则是至关重要的。这意味着我们需要根据业务规则和数据特性来设计表结构，以消除数据冗余和维护数据一致性，从而提升数据存储的效率和质量。然而，在某些情况下，为了提高查询效率，我们可能会采取适当的反范式策略，比如将某些经常一起查询的字段冗余存储在同一个表中，以减少表之间的连接操作。

选择正确的数据类型对于优化数据库性能同样重要。对于数值类型的字段，我们应该根据数值的范围来选择最合适的整数类型，例如使用TINYINT来存储小范围的整数，如布尔值或状态码；使用INT来存储常规大小的整数，如用户ID或计数器；使用BIGINT来存储大范围的整数，如大型计数器或ID。对于字符串类型的字段，我们应该根据字符串的特性和长度来选择数据类型，例如使用CHAR来存储固定长度的字符串，如国家代码或性别标识；使用VARCHAR来存储可变长度的字符串，如个人姓名或地址；使用TEXT来存储较长的文本内容，如文章或评论。

索引优化

索引是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗。

通俗来说, 索引就相当于一本书的目录, 可以根据页码快速查找到指定的内容, 目的就是加快数据库的查询速度，但这也就意味着书中如果要增加一个章节，修改目录是比较麻烦的，使用索引适用于经常查询很少修改的业务

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1) 顺序访问

• 顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。
• 顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

2) 索引访问

• 索引访问是通过遍历索引来直接访问表中记录行的方式。
• 使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。
• 注意: 建立索引后, 查询速度不一定会变快，例如, 你在teacher表中建立了关于id的索引, 如果你按照name查询, 那么查询速度也不会变快，查询得用到你建立的索引

sql语句优化

1. 避免开头模糊查询：如LIKE ‘%value’，这种查询会导致数据库无法使用索引，因为它必须检查每个字符。
2. 减少IN和NOT IN的使用：虽然IN运算符在某些情况下可以使用索引，但如果列表过长或与NULL值一起使用，它可能会导致性能下降。
3. 谨慎使用OR：当使用OR连接多个条件时，如果每个条件都不能有效利用索引，那么可能会导致全表扫描。
4. 避免NULL值判断：查询中的NULL值判断（如IS NULL或IS NOT NULL）通常会导致索引失效，因为索引不存储NULL值。
5. 避免在WHERE子句中使用函数和表达式：对索引列使用函数或表达式（如WHERE UPPER(column) = ‘VALUE’）会阻止索引的使用。
6. 避免使用不等于运算符：如<>或!=，这些运算符通常会导致索引失效。
7. 避免隐式类型转换：在比较不同类型的数据时，数据库可能会进行隐式类型转换，这会导致索引不被使用。
8. **避免使用SELECT ***：只选择需要的列，可以减少不必要的数据传输和处理。
9. 避免使用通配符选择所有列：在SELECT语句中使用通配符可能会检索不需要的数据，增加I/O消耗。
10. 优化多表关联查询：在JOIN操作中，将小表放在前面，大表放在后面，可以减少总的扫描行数。
11. 使用UNION ALL代替UNION：UNION ALL不会去重，但它比UNION更快，因为UNION需要额外的一步来去除重复行。

MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。