|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/mysql-high-performance-tuning-guide/
课程评论：没有评论
课程名称：MySQL 高性能调优指南 课程概述：该在线课程专注于MySQL的性能调优，提供了深入的知识和实用的技巧，旨在帮助学员优化MySQL数据库的性能。 课程内容： - **MySQL架构**：了解查询执行过程中的步骤，以及客户端/服务器协议如何影响MySQL，深入分析查询优化器的工作原理。 - **存储引擎层的独特性**：探索如何发现查询优化的候选项，利用性能模式（Performance Schema）来识别查询瓶颈，通过测量时间资源的消耗来进行优化。 - **关键查询分析**：学习如何识别造成系统负荷的关键查询，认识到更新和删除语句也会引发读操作的影响。 - **查询分析**：了解MySQL在处理每个查询请求之前如何创建查询计划，学习如何阅读查询计划并分析性能瓶颈。 - **集群索引与主键选择**：探讨主键对随机和顺序I/O的影响，学习如何选择最佳的主键以优化查询性能。 - **索引性能优化**：了解索引的主要功能，识别需要索引的表，以及如何利用统计信息帮助MySQL做出更好的索引选择。 - **复合索引的使用**：学习复合索引的常见误区，掌握多列索引的列顺序选择。 - **MySQL服务器配置**：了解如何根据一些最佳实践进行MySQL配置调整，调整重要参数以适应特定用例。 - **事务和锁的管理**：认识事务在数据完整性中的重要性，掌握如何减少锁的影响，包括索引、交易分割及隔离级别的使用。 - **MySQL扩展入门**：定义MySQL扩展的含义，讨论不同负载的扩展需求，学习复制与数据库分片的概念及其使用场景。 通过此课程，学员将能够深入理解MySQL的性能调优，并掌握优化数据库的技术和方法，以提升应用程序的响应速度和整体性能。
MySQL Performance Tuning Online Course Curriculum:MySQL ArchitectureView the steps involved in Query Execution How the Cient/Server Protocol affects MySQLUnderstand how the Query Optimizer does its jobHow the Storage Engine Layer makes MySQL uniqueFinding Candidates for Query OptimizationsMaking use of The Performance Schema, which is a gold mine for spotting querying for optimization because it allows us to measure where the time goesFinding the few queries that are responsible for most of the load on the system. Checking to the top 1000 queries it's usually not worth your time to optimize.How to find the relevant information out of the plethora of performance views and metrics available in MySQLUnderstand why UPDATE and DELETE statements are also reads even though they are primarily write statements. This will have an impact on I/O, and therefore to the response time.Analyzing the QueriesUnderstand why MySQL creates a query plan, before each query requestHow to read query plans, and understand how each of the underlying node types works on MySQLA series of examples, to show how the EXPLAIN output can be used, to identify where MySQL spends time on your query, and whyWhich are the important pieces of information, to look for in order to spot performance issuesThe Clustered Index and Choosing the Primary KeyWhen we work with index-organized tables, as the ones in MySQL, the choice of the primary index, is very important.We'll see how the primary key, can impact the amount of random, or sequential I/O, the size of secondary indexes, and how many pages need to be read into the buffer pool.We're going to understand, why an optimal primary key, with respect to the clustered index, is as small (in bytes) as possible, keeps increasing monotonically, and groups the rows we query frequently, and within a short distance of each other.Indexing for performanceWhich are the main function of an index?We'll see exactly which tables are in need for an index, and which tables are better without one.We'll check how MySQL relies on statistics to choose the best index, and how we can help it to make better decisions.Composite IndexesCommon mistakes of composite indexesHow to decide the order of columns for multicolumn indexesBe able to reason through how indexes work, and to choose them based on that understanding, not on rules of thumbWhich are the use-cases, that make Redundant Indexes useful, and when to avoid them.Work on some benchmarks, to check the actual performance in numbers.MySQL Server ConfigurationsCheck how we should approach MySQL configuration changes by considering some best practices, so that we can successfully tune MySQL.Which are the three options that MySQL is most likely to benefit, from non-default values, and also how to set them.Go through an overview of the data lifecycle in InnoDB, so that we have background knowledge when making configuration changes.Adjust parameters that are important for certain use-cases, for example, buffer pool instances for high concurrent workloads, and how to make use of parallel execution for certain queries.MySQL Transactions and LocksTransactions are very important to ensure data integrity but when used wrong they can cause severe performance issues.Get an overview of why locks are needed and some ways to reduce their impact.How indexes, splitting the transaction and the isolation levels can help to reduce the number of locksMonitoring options and what reporting tables to check to spot lock problems.Introduction to Scaling MySQLWhat it means to scale MySQL and, we'll walk through the different axes where we may need to scale.Understand how to scale different kinds of loadsReplication and why we need to make use of Read-Pools, Health Checks, and Service Discovery Which are the main concerns and pitfalls when deciding to Shard the databaseHow to partition data in MySQLWhich are the use-cases and advantages for using a Queue Fundamental Concepts for Scaling Writes with Sharding
