sql优化

慢 SQL 产生的原因

1. 缺少合适的索引和索引失效： 如果查询中涉及的列没有相应的索引，数据库可能需要进行全表扫描来查找符合条件的记录，从而导致查询变慢。 没有索引或者导致索引失效，没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)
2. 查询条件不优化： 查询条件的编写不合理，导致数据库无法有效利用索引进行优化。比如在索引列上使用函数操作、使用LIKE '%xxx%'这样的模糊查询、使用不必要的OR条件等。
3. 数据量过大： 当数据库中的数据量过大时，即使有合适的索引，查询也可能变得缓慢。因为索引需要遍历大量的数据才能找到符合条件的记录。 单表数据量数据过多，导致查询瓶颈
4. 锁竞争： 如果查询涉及到了锁竞争，例如多个会话同时对同一数据进行读写操作，可能会导致查询阻塞，从而影响查询性能。 锁或者死锁。
5. 服务器资源不足： 如果数据库服务器的资源（如CPU、内存、磁盘IO）不足，可能会导致查询性能下降。例如，内存不足可能导致数据库使用了磁盘交换，而磁盘交换的速度远远低于内存访问速度，会导致查询变慢。
6. 不合适的执行计划： 数据库查询优化器选择了不合适的执行计划，导致查询性能下降。这可能是由于数据库统计信息不准确或者过时，或者是由于查询条件的选择不合理导致的。
7. 网络延迟： 如果数据库服务器和应用服务器之间的网络延迟较大，可能会导致查询响应时间变慢。
8. 并发连接过多： 如果数据库服务器同时连接的客户端连接数过多，可能会导致数据库服务器资源耗尽，从而影响查询性能。
9. 热点数据导致单点负载不均衡
10. 网络原因或者机器负载过高，I/O 吞吐量小，形成了瓶颈效应，内存不足，网络速度慢。
11. 读写未分离
12. 字段过多是否返回了不必要的行和列。
    综上所述，慢 SQL 查询的原因可能是多方面的，需要综合考虑数据库结构、查询条件、服务器资源、数据库配置等多个方面来进行诊断和优化。

优化SQL方法优化规则

SQL优化是提高数据库性能和效率的重要手段之一
SQL优化是一个综合考虑索引、查询、表结构、数据类型等多个方面的工作，需要根据具体的业务场景和数据库实现进行调整和优化。

1正确使用索引：确保表中的字段被适当地索引，以便加速检索操作。避免在大表上进行全表扫描。
2**避免使用SELECT 语句：只选择需要的字段，而不是选择整个表。这样可以减少数据传输和处理的成本。
3 合理设计数据库结构：良好的数据库设计可以减少查询时的复杂性，提高性能。包括避免过度规范化和冗余数据，以及正确使用关系和索引。
4使用INNER JOIN替代子查询：子查询通常比JOIN语句效率低。如果可能的话，尽量使用JOIN语句。
5避免在WHERE子句中使用函数：在WHERE子句中使用函数会导致索引失效，影响查询性能。尽量避免在WHERE中使用函数。
6分页查询优化：使用LIMIT和OFFSET分页查询时，确保查询是基于索引的。避免OFFSET过大导致性能下降。
7定期优化数据库：定期分析查询执行计划，识别潜在的性能瓶颈，并进行相应的优化。
8适当使用缓存：使用缓存可以减少数据库负载，提高性能。但是要注意缓存的更新策略，以避免数据不一致性。
9避免频繁的数据库连接和断开：数据库连接的开销比较大，尽量减少连接和断开的次数，可以使用连接池来管理数据库连接。
10注意SQL语句的执行顺序：理解SQL语句的执行顺序可以帮助优化查询。通常情况下，FROM子句先执行，然后是WHERE、GROUP BY、HAVING、SELECT和ORDER BY。
11使用EXPLAIN分析查询计划：使用数据库提供的工具（如MySQL的EXPLAIN）来分析查询计划，找出可能的性能瓶颈，并进行相应的优化。
12避免使用动态SQL：动态SQL可能存在SQL注入的安全风险，同时也会降低数据库的性能。尽量使用参数化查询来避免这些问题。
13分页优化： 对于分页查询，使用LIMIT和OFFSET来限制返回的数据量，避免一次性返回大量数据
14定期更新统计信息： 定期更新数据库的统计信息，以保证数据库查询优化器能够做出正确的执行计划选择
15 优化表结构： 合理设计数据库表的结构，避免过度范式化和反范式化，确保表的结构符合业务需求
16 使用适当的数据类型： 使用合适的数据类型来存储数据，避免使用过大或者过小的数据类型，以节省存储空间和提高查询性能
17 缓存重复查询结果： 对于频繁查询的结果，可以考虑使用缓存来存储结果，减少数据库的查询压力
18 定期维护数据库： 定期进行数据库的维护工作，包括索引重建、表压缩、数据库统计信息更新等，以保证数据库的性能和稳定性
19 优化WHERE子句： 在WHERE子句中使用索引列，并尽量避免在索引列上进行函数操作或者表达式计算，因为这会导致索引失效
20 优化存储引擎和配置：
21 根据数据库类型（如MySQL的InnoDB或MyISAM），选择适合的存储引擎。
22 调整数据库的配置参数，如内存分配、线程数等，以适应工作负载
通过综合运用以上的方式和原则，可以有效地优化SQL查询，提高数据库性能和效率。

数据库结构和表设计

单表数量大的表，可以考虑进行分离/分表（如交易流水表），进行分表分库
垂直分库：“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以
降低表的复杂度和字段的数目，从而达到优化的目的。
水平分库：“水平分割”是一种把数据库中的表按行变成几张表的方法，这样可以
降低表的复杂度和字段的数目，从而达到优化的目的。
避免使用 null，建表时，尽量设置 not nul，提高查询性能
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
若只含数值信息的字段尽量不要设计为字符型,尽量使用数字型字段,这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
增加中间表进行优化（这个主要是在统计报表的场景，后台开定时任务将数据先统计好，尽量不要在查询的时候去统计）；
数据库主从分离，读写分离，比如MySQL支持一主多从的分布式部署，我们可以将主库只用来处理写数据的操作，而多个从库只用来处理读操作。在流量比较大的场景中，可以增加从库来提高数据库的负载能力，从而提升数据库的总体性能。
查看mysql 执行日志，分析是否有其他方面的问题

索引

合理运用索引，对字段建立索引， 为搜索字段添加索引，增加查询效率，优化索引结构，看是否可以适当添加索引
可以对主键和外键建立索引
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低insert 及 update的效率,因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要
一个字段索引 字段值重复过高，可以将这个字段和时间字段组成联合字段，原因：索引字段区分度要高

查询条件

使用 explain 查看 sql 性能 ，分析 sql 语句，查看执行计划，优化 sqlexplain 的查询结果会告诉你索引主键是如何被利用的
查询有效的列信息即可.少用 * 代替列信息，尽量用类名替代*
使用Not Existsts 替代Not In
SQL 的查询一定要基于索引来进行数据扫描，避免索引列上使用函数或者运算,这样会导致索引失效
永远用小结果集驱动大结果集，使用 join 时应该小结果集驱动大结果集，同时把复杂的 join 查询拆分成多个 query，不然 join 越多表，会导致越多的锁定和堵塞。
使用多表查询替代子查询，查看是否涉及多表和子查询，优化 Sql 结构，如去除冗余字段，是否可拆表等
注意 like 模糊查询的使用，避免使用%% ，where 字句中 like %号,尽量放置在右边，使用索引扫描,联合索引中的列从左往右,命中越多越好
避免使用in , not in , is null , is not null , < >等，防止索引失效
应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描
尽量减少子查询，使用关联查询（left join, right join, inner join）代替；
只需要一行数据时使用 limit1
字段尽可能的使用 NOT NULL
对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
避免在列上做运算，可能会导致索引失败
不要使用 rand 函数随机获取记录
不要使用 count（id），应该使用 count（*）
不要做无谓的排序，尽可能在索引中完成排序
From 语句中一定不要使用子查询使用更多的 where 加以限制，缩小查找范围
尽量使用in运行符来替代or运算
in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)
用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)
那些可以过滤掉最大数量记录的条件必须写在where字句的最末尾；
新增修改删除
拆分大的delete和insert语句：delete和insert会锁表；
Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志
使用批量插入语句，节省交互

其他

如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table,然后 drop table ，这样可以避免系统表的较长时间锁定。
尽量避免大事务操作，提高系统并发能力

在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息
尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

选取最适用的字段属性

1).数据库中的表越小，在它上面执行的查询也就会越快,如char(6)和char(255),char(6)合适选他,他可以节省空间和性能
2).另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL
3).在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多

如果是长度固定的字段，比如用户手机号，一般都是11位的，可以定义成char类型，长度是11字节。
但如果是企业名称字段，假如定义成char类型，就有问题了。
如果长度定义得太长，比如定义成了200字节，而实际企业长度只有50字节，则会浪费150字节的存储空间。
如果长度定义得太短，比如定义成了50字节，但实际企业名称有100字节，就会存储不下，而抛出异常。
所以建议将企业名称改成varchar类型，变长字段存储空间小，可以节省存储空间，而且对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
我们在选择字段类型时，应该遵循这样的原则：
undefined 能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
undefined 尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等。
undefined 长度固定的字符串字段，用char类型。
undefined 长度可变的字符串字段，用varchar类型。
undefined 金额字段用decimal，避免精度丢失问题。
Char是一种固定长度的类型，varchar是一种可变长度的类型
如果存进去的是‘csdn’,那么char所占的长度依然为10，除了字符‘csdn’外，后面跟六个
空格，varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余
的空格，而varchar是不需要的。
char的存取数度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找。