SQL优化

 

SQL执行过程：

　　连接建立：

  1. 应用程序连接数据库： 应用程序通过数据库客户端与数据库服务器建立连接。
  2. 认证和授权： 数据库服务器验证应用程序的身份，并根据其权限确定其是否可以执行特定的SQL操作。

　　SQL解析：

  1. SQL解析： 数据库服务器接收到应用程序发送的SQL语句，对其进行解析，检查语法和语义的正确性。
  2. 语法检查： 确保SQL语句的语法是正确的，并且符合数据库系统的规范。
  3. 语义分析： 确保SQL语句的语义是正确的，包括表名、列名的存在性和合法性等。

　　 执行计划生成：

  1. 查询优化器： 数据库系统使用查询优化器根据查询的逻辑和条件生成一个执行计划，确定如何访问数据以满足查询需求。
  2. 执行计划生成： 数据库生成执行计划，确定了查询的执行路径、访问方法和操作顺序。

　　数据访问与处理：

  1. 数据访问： 数据库根据执行计划访问存储中的数据，执行查询操作。
  2. 数据处理： 数据库对查询结果进行处理和计算，可能包括排序、聚合、连接等操作，以生成最终的结果集。

　　结果返回与连接关闭：

  1. 结果返回： 查询结果被返回给应用程序，可以是单行、多行或空结果集。
  2. 连接关闭： 查询执行完成后，数据库服务器关闭与应用程序的连接，释放资源。

 

　　1) 客户端：如Navicat，idea内mapper/dao层调用的SQL语句；

　　2) 服务器：也就是远程服务器；

　　3) 连接器：如Navicat需要与mysql数据库连接。用户名权限等都需要连接器。

　　4) 分析器：进行语法解析。
　　　　词法分析器：把完整的SQL语句打碎成一个个单词；
　　　　语法分析器：将词法分析的结果根据一定的规则生成一个数据结构，语法树。语法解析也是会解析格式的。
　　　　预处理器：语法正确后，会对表名、字段等是否存在进行检测；

　　5) 优化器：MySql自带优化器。优化器按照自己特定的规则进行优化；
　　　　一条SQL语句执行的方式很多，最终返回的结果是相同的；优化器的目的是根据(语法解析的)解析树生成不同的执行计划，择优执行；

　　简单的执行过程：SQL语句在客户端内写好，在服务器内来执行，执行SQL语句需连接器，再将SQL语句给到分析器，继而到优化器，优化完成后开始执行sql语句；执行SQL语句需要存储引擎。

 

　　连接MySQL需要通信协议；
　　6) 通信协议：（数据库）本地连接--socket；远程连接--TCP协议；

　　7) 长连接与短连接：
　　    简单来说就是与服务器连接的长短；
　　    连接好后，执行SQL语句，关闭；
　　    短链接：连接-->数据传输-->关闭；查询：show global variables like 'wait_timeout'; -- 非交互式超时时间，如 JDBC 程序；
　　    长链接：连接-->数据传输-->保持连接-->数据传输-->保持连接-->..-->关闭连接；查询：show global variables like 'interactive_timeout'; -- 交互式超时时间，如数据库工具
　　    长连接与短连接都会有一定的时限自动关闭，默认8h；
　　　

 

　　 查看当前服务器的连接状态：show global status like 'Thread%';
　　    Thread_cached：线程缓存中的线程数;
　　    Thread_connected：当前打开的连接数;
　　    Thread_created：为处理连接而创建的线程数;
　　    Thread_running：未休眠的线程数;
　　

 　　MySQL最大连接数：show variables like 'max_connections';
　　
　　默认151，最大为2的14次方16384；
　　临时修改：set global max_connections = 1000;
　　永久生效：修改/etc/my.cnf 文件(是MySQL配置文件)

　　缓存

　　再次执行同一条SQL语句，就回去缓存查找；注意：语句必须一模一样，甚至字母大小写；表内任何一条数据有变化，缓存都会失效；
　　版本8以后取消了此功能。
　　show variables like 'query_cache%'; 
　　

　　8) 存储引擎（索引结构）
　　　　索引：帮助mysql高效获取数据的排好序的数据结构。

　　　　关系型数据的类型是存在表里的，表在存储数据的同时会组织数据的存储结构；此结构是存储引擎决定的，而存储引擎也叫表类型；
　　　　在MySQL内支持多种存储引擎，是可替换的，所以也叫插件式存储引擎。
　　　　查看支持的存储引擎：show engines;

　　　　
　　　　MylSAM与InnoDB是最常见的；
　　

　　　　不同的存储引擎决定一张表在磁盘的类型，每个存储引擎都有一个frm文件，是表结构定义文件；
　　　　查看文件存储地址：show engines;

　　9) MylSAM 引擎
　　在MySQL版本5.5前，默认存储引擎是MylSAM，是MySQL自带的。建表时，不指定存储引擎，则会默认MylSAM为存储引擎。

　　特点：
　　1) 应用范围小。表级锁限制了读/写的性能。
　　2) 通常用于只读或读为主的工作。
　　3) 支持表级锁(插入或更新时会锁表)。不支持事务。
　　4) 拥有较高的增(insert)与查(select)的速度。
　　5）存储了表的行数（count速度更快）。
　　6) 当指定主键时，插入顺序与查询顺序是一致的。

　　表级锁：操作数据库时，将整个表都锁柱，不能操作表内任何数据；
　　行级锁：操作数据库时，将整行数据锁住，不能错做被锁住的行内数据；

　　10) InnoDB 引擎
　　MySQL5.7的默认引擎。InnoDB是一个事务安全的(与ACID兼容)，使得MySQL默认引擎具有提交、回滚和崩溃恢复功能保护用户数据。
　　InnoDB行级锁和Oracle的风格一致。

　　特点：
　　1) 支持事务、支持外键，故数据的完整性、一致性更高；
　　2) 支持行级锁和表级锁；
　　3) 支持读写并发，写不阻塞读(MVCC)；
　　4) 特殊的索引存放方式，可减少io，提升查询效率；
　　5) 当指定主键时，插入顺序与查询顺序不一致；插入数据时，InnoDB会根据主键进行排序；

　　用于：经常跟新的表，存在并发读写或有事务处理的业务系统。

 

 

1. 查询语句优化：

  • 选择合适的列： 在查询中只选择需要的列，避免使用 SELECT *；查询时请书写需要的字段，这样可以减少数据传输和查询的时间。查询包含的列越少，io开销越小。

  • 避免使用子查询： 尽量避免在查询中使用子查询，尤其是在 WHERE 子句中，可以考虑使用连接（JOIN）来替代子查询，子查询结果会形临时表，增加内存资源。

  • 合理使用条件： 确保查询条件能够充分利用索引，在SQL语句的WHERE与JOIN部分中用得到的字段，都应该用到索引；避免在列上进行函数操作，这样可以使索引失效。表达式运算时，尽量将造作移至等号右侧。尽量使用LIMIT分页，每页数量不宜过大。

  • 查询条件的顺序： 将最限制性的查询条件放在 WHERE 子句的前面，这样可以尽早过滤掉不符合条件的数据，提高查询效率。此外，SQL Server的查询处理器（Parser）会按照 FROM 子句中的表名从右到左的顺序进行处理。这意味着在 FROM 子句中写在最后(右侧)的表（也就是基础表，通常是包含最少记录的表）将首先被处理。在处理多个表的情况下，SQL Server会选择记录条数最少的表作为基础表，以提高查询的效率。故：尽量将数据量少的表放于FROM最右侧。

  • 当 SQL Server 处理多个表时，通常会运用排序（Sort）和合并（Merge）的方式来连接它们，以执行查询操作。

  • 避免使用%通配符： 在查询中使用%通配符可能会导致全表扫描，影响性能，尽量避免在查询中使用LIKE '%value%'这样的模糊查询，尽量使用右模糊查询。

  • 读写分离： 从库读主库写，将读操作路由到专门负责处理读请求的从数据库服务器上，这样可以减轻主数据库服务器的负载，提高系统的负载均衡能力。

2. 索引优化：

  • 创建合适的索引： 根据查询频率和字段选择，针对性的创建；为经常查询的字段创建索引，以加速查询速度。但是不要为所有字段都创建索引，因为索引也会占用额外的存储空间并增加写操作的成本。

  • 选择合适的索引类型： 单列索引、复合索引、唯一索引等不同类型的索引适用于不同的场景，需要根据实际情况进行选择。注意：使用复合(多列)索引时，索引的列顺序需要与查询条件保持一致是很重要的，同时删除不必要的单列索引也是一种优化数据库性能的策略。

  • 避免过多的索引： 过多的索引会增加数据库的维护成本，而且可能导致查询优化器选择错误的索引。　　　

　　　　1) like "%"，避免使用模糊查询；尽量使用右模糊，例：like "张%"；

　　　　2) 避免使用 in，not in ，连续条件可使用between ... and ... ；

　　　　3) 避免使用or，可用 union 代替；

　　　　4) 避免使用 null 判断，可给字段添加默认值 0 ；

　　　　5) 避免 where = 左侧进行表达式、函数操作；可更改为 = 右侧；

　　　　6) 数据量大时，避免使用 where 1=1；

　　　　7) 不可用 != 或 <>；

　　　　8) 隐式类型转换造成不使用索引；例：varchar可以向下转为int，而int不可向上转；

　　　　9) 联合索引最左匹配原则；where条件包含复合索引非前之列；

3. 数据库表结构优化：

  • 合理设计表结构： 选择合适的数据类型、字段长度和表关系，避免不必要的冗余数据，确保表的正规化和一致性。如：数据范围较小，可选择 TINYINT 或 SMALLINT 来节省存储空间。如果需要存储更大范围的整数，可以选择 MEDIUMINT 或者 INT。

  • 分解大表： 当表过大时，可以考虑对其进行分解，将其拆分成多个表，以减少数据的存储和查询成本。

  • 移除不必要的表：减少数据库执行的流程和资源消耗，减少数据传输和网络带宽消耗。

4. 缓存和预编译语句：

  • 使用缓存： 尽量减少数据库查询的次数，可以使用缓存技术将查询结果缓存起来，减少数据库的压力。

　　　　　　两种常见实现方式，直写式（Write Through）和回写式（Write Back）

  直写式 回写式

工作方式

 在数据写入数据库后，立即更新缓存，确保数据库与缓存的一致性。 当有数据要写入数据库时，只更新缓存，然后通过异步批量的方式将缓存数据同步到数据库上。
特点
  • 实现简单，同步性好，易于理解和维护。
  • 保证了数据库与缓存的一致性，避免了数据不一致的问题。
  • 效率较高，因为写操作不需要等待数据库的确认，直接写入缓存，可以减少写操作的延迟。
  • 异步批量同步数据库，减少了数据库访问的次数，提高了性能。
缺点
  • 效率一般，因每次写入数据库都需同步更新缓存，可能会增加写操作的延迟。
  • 可能会降低写操作的吞吐量，特别是在高并发的情况下。
  • 实现较为复杂，需要考虑缓存与数据库之间的一致性问题，可能会导致数据不一致的风险。
  • 可能会出现数据丢失的情况，如果在数据写入缓存后发生系统崩溃或者网络故障，尚未同步到数据库时。

 

  • 使用预编译语句： 使用预编译语句（Prepared Statement）可以减少SQL语句的解析时间，提高查询的效率。

5. 监控和调优：

  • 监控数据库性能： 定期监控数据库的性能指标，如查询响应时间、查询执行计划等，及时发现并解决性能瓶颈。

  • 分析查询执行计划： 使用数据库提供的工具分析查询执行计划，优化查询语句，改进索引设计。

6. 数据库统计信息：

  • 收集统计信息： 定期收集数据库的统计信息，包括表的大小、索引的使用情况、查询的执行计划等，以便更好地了解数据库的性能状况，发现潜在的性能问题。

  • 分析数据库统计信息： 分析数据库的统计信息，了解数据库中数据的分布情况和查询的访问模式，有助于优化索引设计和查询语句。

 

 

　　使用Explain命令：用于解释（或说明）数据库系统执行查询的方式和计划。它通常返回一个执行计划，显示了数据库系统将如何执行查询以及执行查询时所使用的索引、连接方式等关键信息。

　　对于优化查询性能非常有用。

　　　例-语法：EXPLAIN SELECT username FROM table_a ta INNER JOIN table_b tb ON ta.id = tb.id;

　　运行带有 EXPLAIN 命令的查询时，数据库系统不会执行查询，而是返回一个关于查询执行计划的结果集。

　　查询执行计划的结果通常包括以下重要信息：　

    • id： 每个查询操作的唯一标识符，通常按照执行顺序递增。
    • select_type： 查询的类型，例如简单查询、联合查询、子查询等。
    • table： 查询涉及的表名。
    • type： 访问表的方式，包括全表扫描、索引扫描、范围扫描等。
    • possible_keys： 可能用于查询的索引列表。
    • key： 实际用于查询的索引。
    • key_len： 索引字段的长度。
    • ref： 显示索引是如何与表中的行匹配的。
    • rows： 估计的结果集行数。
    • Extra： 额外的信息，例如是否使用了临时表、文件排序等。

　　通过分析 EXPLAIN 命令的结果，可以更好地理解查询的执行方式，发现潜在的性能瓶颈，并对查询语句进行优化。

 

 

7. 优化数据库配置：

  • 调整数据库参数： 根据数据库的实际负载和性能需求，调整数据库的参数设置，包括缓冲池大小、连接数、日志设置等，以优化数据库的性能。

  • 使用分区表： 对于大型数据库表，可以考虑使用分区表来提高查询和维护的效率，可以根据时间、范围或者其它条件进行分区。

8. 考虑并发和事务：

  • 事务优化： 优化事务的范围和持续时间，尽量减少事务的锁定时间和资源占用，以提高并发性和吞吐量。

  • 并发控制： 使用合适的并发控制策略，如乐观并发控制（Optimistic Concurrency Control）或悲观并发控制（Pessimistic Concurrency Control），以避免数据的竞争和冲突。

9. 业务逻辑优化：

  • 优化业务逻辑： 重新评估业务逻辑的设计，考虑是否可以简化复杂的业务流程，减少数据库的负载和开销。

  • 批量操作： 尽量使用批量操作来处理大量数据，减少单次数据库操作的次数，提高数据库的性能和效率。

10. 数据库版本升级：

  • 考虑升级数据库版本： 时刻关注数据库厂商发布的新版本和更新，新版本通常会包含性能优化和改进。升级数据库版本可能会带来性能提升和新特性的支持。

11. 考虑存储引擎：

  • 选择合适的存储引擎： 不同的数据库管理系统可能支持不同的存储引擎，如InnoDB、MyISAM等。了解不同存储引擎的特点和适用场景，选择适合自己业务需求的存储引擎。MyISAM引擎是MySQL 5.1及之前版本的默认引擎；InnoDB在MySQL 5.5后成为默认索引。

12. 使用分析工具：

  • 使用数据库性能分析工具： 借助数据库性能分析工具，可以帮助识别潜在的性能问题和瓶颈，并提供优化建议。常见的数据库性能分析工具包括MySQL的EXPLAIN语句、Percona Toolkit、pt-query-digest等。

13. 定期优化：

  • 定期进行优化： SQL优化不是一次性的工作，需要定期进行审查和优化。随着业务的发展和数据库的变化，不断地进行优化和调整以适应新的需求和挑战。

 

　　另外还有表分区：数据库管理中的一种技术；水平拆分（Horizontal Partitioning）：数据库设计中的一种技术；垂直拆分（Vertical Partitioning）：数据库设计中的一种技术，等等。

 

 

SQL生命周期：

　　SQL（Structured Query Language）的生命周期是SQL语句从设计、开发、执行到优化的整个过程，涵盖了SQL在数据库系统中的各个阶段的生命周期管理。

1. 需求分析阶段：

  1. 需求收集和分析： 确定业务需求，了解需要查询和操作的数据。
  2. 定义数据模型： 设计数据库结构和关系模型，包括表、列、主键、外键等。

2. SQL设计和开发阶段：

  1. SQL查询设计： 根据需求设计SQL查询语句，包括选择需要的列、定义条件、排序规则等。
  2. SQL编码： 将设计好的SQL查询语句转化为具体的SQL代码，并进行编码实现。

3. SQL执行阶段：

  1. SQL解析： 数据库系统接收SQL查询，并对其进行解析，检查语法和语义的正确性。
  2. 执行计划生成： 数据库系统生成SQL查询的执行计划，确定查询的执行路径和访问方式。
  3. 数据访问和处理： 数据库系统根据执行计划访问数据，并进行逻辑处理和计算，生成结果集。

4. SQL优化和调优阶段：

  1. 性能分析： 监控SQL查询的性能和执行情况，收集执行统计信息。
  2. 查询优化： 根据性能分析结果，对SQL查询进行优化，包括重写查询、创建索引、优化执行计划等。
  3. 索引优化： 根据查询的特性和访问模式，设计和创建合适的索引，提高查询性能和效率。

5. 维护和监控阶段：

  1. 数据库维护： 定期维护数据库系统，包括备份、恢复、数据清理、性能调整等。
  2. 性能监控： 持续监控数据库系统的性能和运行状态，发现并解决潜在的性能问题。

 

posted @   学Java的`Bei  阅读(23)  评论(0)  编辑  收藏  举报
相关博文：
·  数据库执行原理
·  SQL索引
·  MySQL数据库优化总结
·  从Mysql架构看一条查询sql的执行过程
·  5大步骤+10个案例，堪称SQL优化万能公式
阅读排行：
· winform 绘制太阳，地球，月球 运作规律
· AI与.NET技术实操系列（五）：向量存储与相似性搜索在 .NET 中的实现
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 上周热点回顾（3.3-3.9）
点击右上角即可分享
微信分享提示
AI IDE Trae