|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/hands-on-high-performance-sql/
课程评论：没有评论
课程名称：SQL性能调优入门 课程概述：您是否发现SQL查询执行时间过长？是否想了解数据库如何决定数据的检索和过滤？如果您已经熟悉SQL编写，但想学习如何提高查询效率，那么本课程将非常适合您。课程将解释SQL性能原则和优化技术的基础知识。基于您现有的SQL知识，我们将深入研究SQL的查询规划器，探索如何评估查询性能并选择不同的查询编写方式。 课程内容包括： 1. **查询分析与执行深度解析**：学习数据库查询规划器的工作原理，包括不同类型的查询计划节点如何实现核心操作，比如数据检索、表连接和结果过滤。通过阅读执行计划、比较成本计算和评估查询的不同实现，您将深入理解查询的优化。 2. **高级性能优化技术**：探讨多种优化策略，包括： - 索引技术（如覆盖索引、全文索引和表达式索引） - 连接算法的审查，结合实际场景展示每种算法的最佳应用 - 优化相关子查询和复杂窗口函数的方法 - 使用物化视图和公共表表达式（CTE）提升查询性能的策略 - 各种模式匹配技术（包括正则表达式和全文搜索策略）的性能影响 3. **实施生产级SQL解决方案**：处理企业级场景，如： - 大表的高效分区策略 - IoT数据的时间序列优化 - 在大规模应用中实施全文搜索 4. **性能监控与调优**：学习用于持续性能优化的工具和技术，包括： - pg_stat视图的高级使用方法 - autovacuum的调优以实现最佳性能 - 表中统计信息的维护 课程结束时，您将能够分析复杂的SQL查询，掌握数据库性能权衡的知识并能在生产系统中做出明智的决策。整个课程通过包含基础销售数据库和生成每日数百万次读数的时间序列IoT车辆传感器系统的数据集进行实践，帮助您将理论知识应用于实际场景。 注：该课程曾正式被称为“实践SQL性能调优”。
Are your SQL queries taking too long to execute? Do you find yourself wondering how databases decide how to retrieve and filter data? If you're comfortable writing SQL but want to learn more about how to make your queries more efficient, then this course is for you. This course explains the basics of SQL performance principles and optimization techniques. Building on your existing SQL knowledge, we'll look into SQL's query planner and exploring how to evaluate query performance and choose among different approaches to writing queries.Query Analysis and Execution Deep DiveTo tune queries, it helps to understand a databases query planner. In this course you will learn how query plan builders work, including how different types of query plan nodes implement core operations, such as retrieving data, joining tables, and filtering results. Learn to read execution plans, compare cost calculations, and evaluate alternative implementations of your queries. Through hands-on exercises, you'll analyze various query patterns and see how rewriting queries can impact their performance.Advanced Performance Optimization TechniquesThe course explores several optimization strategies:Indexing techniques including covering, full-text, and expression indexesReviewing join algorithms with real-world scenarios demonstrating when each type is optimalSeeing options for optimizing correlated subqueries and complex window functionsWeighing when to use materialized views and common table expressions (CTEs) for query performanceLearning about different pattern matching techniques including regular expressions and full-text search strategiesBecoming familiar with the performance implications of different GiST, GIN, and SP-GiST index typesImplementing Production-Grade SQL SolutionsTackle enterprise-level scenarios including:Implementing efficient table partitioning strategies for large tablesUnderstanding time-series optimization techniques for IoT dataImplementing full-text search in large-scale applicationsPerformance Monitoring and TuningLearn how to use tools and techniques for ongoing performance optimization:Advanced usage of pg_stat views for performance monitoringUnderstanding and tuning autovacuum for optimal performanceMaintaining statistics in tablesThroughout the course, you'll work with datasets that include a basic a sales database as well as a time-series IoT vehicle sensor system generating millions of readings per day. The hands-on exercises give you an opportunity to apply theoretical knowledge to practical application scenarios.By the end of this course, you'll have practiced the skills you need to analyze complex SQL queries and the knowledge to make informed decisions about database performance trade-offs in production systems.Note: This course was formally listed as Hands-On SQL for Performance Tuning
