Databases

- mysql: - Stores MySQL user accounts and privilege information, definitions of some stored procedures and events, some log information generated during operation, help information, time zone information, etc. - information_schema: - Stores information about all other databases maintained by the MySQL server, such as tables, views, triggers, columns, and indexes

1. What Is the Parser - Parse SQL statements 2. Parser Functions 2.1. Lexical Analysis - Statement -> String Tokens 2.2. Syntax Analysis - String Tokens -> Syntax Tree

1. What Is the Executor - Execute SQL 2. Executor Functions 2.1. Permission Check - For example, whether there is Select permission 2.2. Fetch Data - Call the storage engine interface to obtain data; if the conditions are met, put it into the result set

CPU 100% 1. Use show processlist to list all processes and inspect the sessions that are running to see whether resource-consuming SQL is executing 2. Find the high-consumption SQL, kill those threads, and use explain to analyze the SQL

Performance Evaluation (1): MySQL Cloud Database vs Self-Hosted Database - Tencent Cloud MySQL Column - Online Tuning and Load Testing - SegmentFault

1. Tencent Cloud MySQL - The example environment uses MySQL on Tencent Cloud 1.1. Deployment - Node topology: <redacted> - Node configuration: <redacted> - Version 5.7 1.2. Availability - Service Level Agreement 1.3. Parameters - Cloud Database MySQL Instance Parameter Settings - Operation Guide

1. What Is the Buffer Pool - A continuous memory space requested from the operating system when MySQL starts 2. Why the Buffer Pool Is Needed - The speed difference between disk and CPU is too large, so memory is needed as a cache 3. Buffer Pool Workflow - When reading data, read from the Buffer Pool first; if it is present, return it directly, otherwise read it from disk and put it into the Buffer Pool

1. Overall Approach 1. Record slow SQL through slow query logs / monitoring / Druid - MySQL Tuning.md 2. Analyze with explain - Index invalidation - Too many tables in join queries - Low server parameter configuration 3. Add indexes - MySQL Indexes.md - Pay attention to scenarios where indexes cannot be used 4. Modify SQL statements - Use join or exists depending on the situation - Do not use select *

1. What Is canal - A component that parses MySQL bin-log 2. Why canal Is Needed - Obtain incremental changes to MySQL data and synchronize them to components such as Elasticsearch and Redis 3. canal Principle - Essentially simulates a replica in primary-replica replication, so it is the same as MySQL Primary-Replica Replication.md 4. How to Use canal

1. What Is the Buffer Pool - A contiguous memory space requested from the operating system when MySQL starts 2. Why the Buffer Pool Is Needed - The speed gap between disk and CPU is too large, so memory is needed as a cache - When InnoDB accesses table and index data, it caches them here, greatly reducing disk I/O and improving efficiency 3. Buffer Pool Workflow