Skip to main content
AIDevOps
  • Learn
  • Learning Paths
  • Practice
  • Open Source
  • Books
  • Engineering

    AI DevOpsThe full map of building and operating AI servicesLLMOpsLLM deployment Β· evaluation Β· observabilityHands-on ProjectsBuild AI Agent projects

    Knowledge

    DocsTechnical documentationBlogEngineering articlesPloggerDevelopment log feed

    Validate

    Certification3-level skills certification Β· coming soon
AI Models
LlamaTutorial|MistralTutorial|GemmaTutorial|DeepSeekTutorial|QwenTutorial
πŸ’Ύ Database
DB Intro & RoadmapCore SQLOracleMySQLPostgreSQL|MongoDBVector DB
πŸ€– AI Applied Development
Applied AI Intro & RoadmapHugging FaceLangChainLlamaIndexLLMOps|LangGraphMCPMulti-AgentAgent Evaluation
🧠 AI Core
AI Intro & RoadmapML FundamentalsLLM Fundamentals|Python AIC++|PyTorchTensorFlowJAX
🧠 AI Agent Development
Finance AI AgentLLM API ServerStock Investing AgentAIOps AI AgentEducation AI AgentCoding AI Agent
🌱 Spring Cloud
Spring Intro & RoadmapSpring Cloud GatewaySpring BootJava|Spring AISpring SecuritySpring BatchSpring JPA
🐳 DevOps
DevOps Intro & RoadmapLinuxDockerCI/CD|Kubernetes BasicsK8s AdvancedPrometheusGrafana
🧱 Infrastructure
Infrastructure Intro & RoadmapNginxRedis
☁️ Cloud
Cloud Intro & RoadmapAWSGCPAzureNCPCloudflare
🎨 Frontend
Frontend Intro & RoadmapJavaScriptTypeScript|ReactNext.js|VueNuxt|Electron
πŸ“± Mobile
Mobile Intro & RoadmapKotlinAndroidFlutter
βš™οΈ Backend
Backend Intro & RoadmapPython BasicsFastAPIDjangoFlask|CGoGinNode.js
πŸ§ͺ Testing
k6JMeternGrinder
AIDevOps

Engineering AI. From Code to Production.
An engineering learning platform for building and operating AI and AI Agents

Learn

  • All Guides
  • Learning Paths
  • AI Tutorial
  • Practice
  • Books

Resources

  • AI DevOps
  • LLMOps
  • Hands-on Projects
  • Docs
  • Blog
  • Plogger
  • Open Source
  • Certification (coming soon)

Start Here

  • AI Core Roadmap
  • AI Applied Development Roadmap
  • Spring Cloud Roadmap
  • DevOps Roadmap
  • Infrastructure Roadmap

Β 

  • Cloud Roadmap
  • Frontend Roadmap
  • Mobile Roadmap
  • Backend Roadmap
  • Database Roadmap
Β© 2026 AIDevOps. All rights reserved.
Terms of ServicePrivacy PolicySitemaptestforge.kr
  1. Home
  2. Learn
  3. Database
  4. MySQL
Guide to MySQL, the most widely used open source RDBMS

🐬 MySQL Complete Guide

Visitors

Learn MySQL, the open source relational database accepted as the standard in web service and microservice environments. Covers what matters most in real service development and operations: the traits of the InnoDB storage engine, index tuning (EXPLAIN), setting up replication, and backups.

  • Intermediate
  • Updated Oct 10, 2026
  • ~9 min read
  • 12 sections
  • 11 code examples
🐬
Web service developmentMicroservice databasesRead-scaling architectureOpen source backends

Related frameworks & environments

πŸ’ΎCore SQLβ†’πŸPython Basicsβ†’βš‘FastAPIβ†’πŸ—„οΈSpring JPAβ†’

Contents

0 / 15
  1. How to use this guide
  2. Architecture diagrams
  3. InnoDB vs MyISAM storage engines
  4. B+Tree indexes and EXPLAIN analysis
  5. Query tuning β€” EXPLAIN and hints
  6. MySQL tuning strategy
  7. Connection pool optimization
  8. Source-replica replication
  9. MySQL privilege system & security
  10. MySQL character set and collation settings
  11. Data backup & restore (mysqldump)
  12. MySQL design
  13. Operating standards
  14. Verification strategy
  15. Frequently asked questions
Contents 15 sections
  1. How to use this guide
  2. Architecture diagrams
  3. InnoDB vs MyISAM storage engines
  4. B+Tree indexes and EXPLAIN analysis
  5. Query tuning β€” EXPLAIN and hints
  6. MySQL tuning strategy
  7. Connection pool optimization
  8. Source-replica replication
  9. MySQL privilege system & security
  10. MySQL character set and collation settings
  11. Data backup & restore (mysqldump)
  12. MySQL design
  13. Operating standards
  14. Verification strategy
  15. Frequently asked questions

How to use this guide

How to read it

Understanding MySQL as a real-world workflow

Learn MySQL, the open source relational database accepted as the standard in web service and microservice environments. Covers what matters most in real service development and operations: the traits of the InnoDB storage engine, index tuning (EXPLAIN), setting up replication, and backups. Rather than listing concepts, this guide is organized so you can follow the content in the order you have to make decisions on a real project.

Key perspective

Databases

Alongside query syntax, look at schema design, indexes, transactions, permissions, and backups from an operations perspective.

Web service developmentMicroservice databasesRead-scaling architectureOpen source backends

Architecture diagrams

To make what you read stick, it helps to capture the flow as a picture first. The two diagrams below are reference maps you can keep coming back to while learning MySQL.

Learning flow

Rendering diagram…

Architecture view

Rendering diagram…

InnoDB vs MyISAM storage engines

When you first open MySQL, the big picture matters more than individual commands. This section starts with the problems the concepts ahead were created to solve.

MySQL supports a variety of storage engines as plugins. InnoDB, established as the default since version 5.5, excels at concurrency thanks to transaction support and row-level locking.
Feature / traitInnoDB (recommended)MyISAM
Transactions (ACID)SupportedNot supported (atomicity not guaranteed)
Locking granularityRow-level lock (excellent concurrency)Table-level lock (whole table locked on writes)
Foreign keys (FK)SupportedNot supported
Crash recoveryAutomatic recovery (based on the Redo Log)Manual table check and repair needed

B+Tree indexes and EXPLAIN analysis

Here you look at B+Tree indexes and EXPLAIN analysis alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

InnoDB in MySQL uses the B+Tree index structure by default. To avoid full table scans when querying, set suitable indexes on the WHERE, JOIN, and ORDER BY columns and use EXPLAIN to find out whether the query really uses an index.
mysql_explain.sqlSQL
-- Check the query execution plan
EXPLAIN SELECT emp_name, salary FROM employees WHERE dept_id = 10 AND salary > 50000;

-- What to focus on in the output
-- table: the table being queried
-- type: ref, range, index, ALL (ALL is a full scan, so a tuning target)
-- possible_keys: list of candidate indexes
-- key: the index actually chosen and used
-- rows: the number of rows the optimizer estimates it must examine
-- Extra: Using index (covering index), Using filesort (sort overhead)

Tip

  • The primary key (PK) is created as a clustered index, so it is the basis for the physical ordering of the table rows. Using a sequentially increasing key as the PK wherever possible therefore avoids page split overhead on inserts.
  • Wrapping an indexed column in a function (WHERE YEAR(hire_date) = 2026 and the like) prevents the index from being used, so keep the column itself intact wherever possible (WHERE hire_date >= "2026-01-01") to steer toward the index.

Query tuning β€” EXPLAIN and hints

Here you look at Query tuning β€” EXPLAIN and hints alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

In MySQL the execution plan is read with EXPLAIN. There is EXPLAIN, which shows only the estimated plan, and EXPLAIN ANALYZE, which actually runs the query and shows actual row counts and times per step. Output comes in a table format (TRADITIONAL) and a tree format (TREE), and the default depends on the version. The results below were run on MySQL 9.7 with a table of 300,000 orders.

Actual execution statistics (EXPLAIN ANALYZE)SQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- Without an index
-- -> Filter: (orders.customer_id = 42)  (cost=30188 rows=29956) (actual time=1.01..49.9 rows=30 loops=1)
--     -> Table scan on orders  (cost=30188 rows=299555) (actual time=0.268..39.7 rows=300000 loops=1)
--                                                                              ↑ read all 300,000 rows

CREATE INDEX ix_orders_customer ON orders (customer_id);

-- After creating the index
-- -> Index lookup on orders using ix_orders_customer (customer_id = 42)
--      (cost=10.5 rows=30) (actual time=0.0598..0.062 rows=30 loops=1)
Table formatSQL
EXPLAIN FORMAT=TRADITIONAL SELECT * FROM orders WHERE customer_id = 42 ORDER BY amount;

-- type  possible_keys        key                 key_len  ref    rows  filtered  Extra
-- ref   ix_orders_customer   ix_orders_customer  4        const  30    100.00    Using filesort

-- Without an index it looks like this
-- type  key   rows    filtered  Extra
-- ALL   NULL  299555  10.00     Using where
Optimizer hints and index hintsSQL
-- Index hint (after the table in the FROM clause)
SELECT * FROM orders FORCE INDEX (ix_orders_created)
WHERE  customer_id = 42 AND created_at >= '2026-07-01';
-- -> Filter: (orders.customer_id = 42)
--     -> Index range scan on orders using ix_orders_created over ('2026-07-01 00:00:00' <= created_at) ...

-- Optimizer hint (a comment right after SELECT)
SELECT /*+ NO_INDEX(orders ix_orders_cust_created) */ *
FROM   orders
WHERE  customer_id = 42 AND created_at >= '2026-07-01';
-- -> Filter: (orders.created_at >= TIMESTAMP'2026-07-01 00:00:00')
--     -> Index lookup on orders using ix_orders_customer (customer_id = 42)

-- Specify the join order
SELECT /*+ JOIN_ORDER(c, o) */ c.grade, SUM(o.amount)
FROM   orders o JOIN customers c ON c.id = o.customer_id
WHERE  o.created_at >= '2026-07-25'
GROUP  BY c.grade;
-- Without the hint: a plan that reads orders first (cost=3841)
-- With the hint:    a plan that reads customers first (cost=103744)  ← a hint can also force a worse plan

-- Execution time limit (milliseconds). The query is stopped when it is exceeded
SELECT /*+ MAX_EXECUTION_TIME(1000) */ COUNT(*) FROM orders;
Column in the table formatWhat to look at
typeThe access method. From best to worst: const, eq_ref, ref, range, index, ALL. ALL is a full table read.
possible_keys / keyThe indexes that could be used and the one actually chosen. A NULL key means no index was used.
rowsThe number of rows expected to be read. Far more than the result count means the index did not narrow the condition.
filteredThe estimated percentage of rows read that remain after the condition.
Extra: Using indexAnswered from the index alone (covering index).
Extra: Using whereRows are filtered by the condition after being read.
Extra: Using filesortIndex order could not be used, so a separate sort runs.
Extra: Using temporaryA temporary table is created (GROUP BY, DISTINCT, and so on).

Tip

  • EXPLAIN ANALYZE actually runs the query. A long query makes you wait that long, and a modifying query changes data.
  • An index forced with FORCE INDEX that does not fit the condition makes things slower. In the example above too, the composite index chosen without a hint is the better choice. Use hints only after confirming the optimizer is wrong.
  • A typo in an optimizer hint produces a warning, not an error, and the hint is ignored. Check with SHOW WARNINGS.
  • The default output format is set by the explain_format variable. On 9.7, where this guide was run, TREE was the default. Add FORMAT=TRADITIONAL when you need the table format.

MySQL tuning strategy

Here you look at MySQL tuning strategy alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

MySQL tuning starts by turning on the tools that find slow queries. The slow query log is off by default and its threshold is 10 seconds, so left alone it records almost no problem queries. Then fix the queries that use the most total time, taking execution counts into account.

1. Collect slow queriesSQL
-- Current settings (defaults right after installation)
SHOW VARIABLES WHERE Variable_name IN ('slow_query_log', 'long_query_time');
-- long_query_time   10.000000
-- slow_query_log    OFF

-- Log queries that take more than 1 second (put it in the config file to keep it across restarts)
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
2. Find the queries that use the most total timeSQL
-- Groups queries of the same shape and shows execution counts and total time
SELECT LEFT(digest_text, 80)            AS query,
       count_star                       AS calls,
       ROUND(sum_timer_wait / 1e9)      AS total_ms,
       ROUND(avg_timer_wait / 1e9, 1)   AS avg_ms,
       sum_rows_examined                AS rows_examined,
       sum_rows_sent                    AS rows_sent
FROM   performance_schema.events_statements_summary_by_digest
ORDER  BY sum_timer_wait DESC
LIMIT  10;

-- When rows_examined is far larger than rows_sent, the query reads more than it needs
3. Unused indexes and statisticsSQL
-- Indexes never used since the server started
SELECT object_schema, object_name, index_name FROM sys.schema_unused_indexes;

-- Before dropping an index, make it "invisible" and check the impact
ALTER TABLE orders ALTER INDEX ix_orders_customer INVISIBLE;
-- (the optimizer stops using this index. Drop it if nothing breaks, or make it VISIBLE again)
ALTER TABLE orders ALTER INDEX ix_orders_customer VISIBLE;

-- Refresh statistics
ANALYZE TABLE orders;

-- Describe the value distribution of a column without an index (histogram)
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;
-- Histogram statistics created for column 'status'.
ItemCheck and adjust
innodb_buffer_pool_sizeThe memory that caches data and indexes. The default of 128MB is far too small for production. On a dedicated database server, give it half the memory or more.
Slow query logTurn it on in production and lower the threshold to around 1 second.
ConnectionsSize the application connection pool rather than raising max_connections.
Composite indexesCheck that the order matches the conditions and sort, and that no indexes overlap.
Primary keyInnoDB stores data in primary key order. A random value (UUID v4) as the primary key slows inserts and uses more space.
Long transactionsA transaction left open piles up locks and undo. Check with information_schema.innodb_trx.

Tip

  • sys.schema_unused_indexes sees usage only since the server started. An index may serve a query that runs occasionally, such as a month-end batch, so decide after running long enough and check with INVISIBLE first.
  • InnoDB secondary indexes contain the primary key value. A long primary key makes every index larger.
  • MySQL usually picks one index per table. To narrow several conditions together, one composite index beats an index per column.

Connection pool optimization

Here you look at Connection pool optimization alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

Opening and closing DB connections is very expensive. Backend applications (Spring, Node.js, FastAPI, and so on) should configure a connection pool such as HikariCP, which creates connections ahead of time and reuses them, appropriately to keep the database from being overloaded.
application.ymlYAML
# Example Spring Boot HikariCP settings
spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 10
      idle-timeout: 30000
      connection-timeout: 10000
      max-lifetime: 1800000

Source-replica replication

Here you look at Source-replica replication alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

To spread load and secure availability, MySQL commonly uses the source-replica replication architecture. Writes run on the source node, and reads are distributed across read-only replica nodes fed by the replicated data.
Rendering diagram…

MySQL privilege system & security

Here you look at MySQL privilege system & security alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

MySQL grants privileges per user account ('user'@'host'), and control can be subdivided into 5 levels according to the scope of the privilege: Global, Database, Table, Column, and Routine.
mysql_grants.sqlSQL
-- 1. Grant global privileges (every DB/table on the whole server)
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'%' WITH GRANT OPTION;

-- 2. Grant database-level privileges (every object in a specific schema)
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app_user'@'localhost';

-- 3. Grant table- and column-level privileges (maximum security: block/allow only specific columns, such as salary data)
GRANT SELECT (emp_id, emp_name), UPDATE (emp_name) ON mydb.employees TO 'helper_user'@'%';

-- 4. Check the granted privileges (prints the GRANT statements of a specific user)
SHOW GRANTS FOR 'app_user'@'localhost';

-- 5. Propagation through WITH GRANT OPTION
-- A user granted WITH GRANT OPTION can grant the privileges they hold to others, so use it with great care.

Tip

  • When matching 'user'@'host', a wildcard ('%') as the host value allows access from any IP, so on development/production servers restrict it to specific IP ranges.
  • It is sometimes said that FLUSH PRIVILEGES; must be run for a grant to take effect immediately, but when GRANT/REVOKE statements are used directly the change is applied in memory at once, so it can be omitted.

MySQL character set and collation settings

Here you look at MySQL character set and collation settings alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

MySQL and MariaDB have a flexible hierarchy in which the character set and collation can be defined at the server, database, table, and column level and inherited step by step. In particular, utf8mb4 has become the standard for multilingual Unicode handling that includes emoji.
mysql_charset.sqlSQL
-- 1. Check the character set settings through the system variables
SHOW VARIABLES LIKE 'char%';
SHOW VARIABLES LIKE 'colla%';

-- 2. Specify the character set when creating schemas and tables
-- utf8mb4: the standard Unicode character set, with full support for emoji (4 bytes)
-- utf8mb4_general_ci: fast sorting and comparison (case-insensitive)
CREATE DATABASE mydb_global
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_general_ci;

USE mydb_global;

-- Specify the character set per table (the database default can be inherited)
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    emoji_status VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin -- can be set precisely per column (utf8mb4_bin: binary comparison, exact match on case/emoji)
);

-- 3. Change the character set of an existing table and database
ALTER DATABASE mydb_global CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Tip

  • The old utf8 (utf8mb3) of MySQL supports at most 3 bytes per character, so storing 4-byte mobile emoji (🌟 and the like) raises an error. When building a new service, always use the utf8mb4 character set for every table and schema.
  • The collation suffix ci (case-insensitive) means searches ignore case, while cs (case-sensitive) or bin (binary) compares with exact case distinction, so for tasks such as verifying member IDs and passwords, utf8mb4_bin or utf8mb4_unicode_cs is appropriate.

Data backup & restore (mysqldump)

Here you look at Data backup & restore (mysqldump) alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

Regular backups are the last line of defense against data loss. With mysqldump, a logical backup tool that produces SQL scripts, you can easily back up the database structure and data.
BASH
# Back up a specific database
mysqldump -u root -p my_database > my_database_backup.sql

# Restore the database
mysql -u root -p my_database < my_database_backup.sql

MySQL practical design

MySQL practical design is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

The core of MySQL design is InnoDB index optimization together with precise mapping of privilege levels down through Global, DB, Table, and Column. In particular, for connections from external hosts, restrict the host part of the account to an IP range instead of the wildcard ('%').
Decision pointQuestion to askPractical guideline
BoundariesWhich parts of the MySQL code are most likely to change?Separate input/output, configuration, external integrations, and the core rules.
StateWhere is state created and where does it go away?Make the owner and lifecycle of state visible in the code.
FailureWhat does the caller receive on failure?Decide the timeout, fallback, and error contract first.

MySQL operating standards

This section covers MySQL operating standards from a practical perspective. Instead of memorizing the concept, focus on the situations in which you would reach for it.

Collecting the Slow Query Log, tuning the InnoDB Buffer Pool, and managing Source-Replica replication lag are essential, and you should monitor whether the WITH GRANT OPTION privilege is being abused to propagate privileges arbitrarily.

Tip

  • Monitor the InnoDB Buffer Pool hit rate
  • Scan account privileges with SHOW GRANTS
  • Check the replication lag
  • Check that accounts using the host wildcard ('%') are blocked

MySQL verification strategy

MySQL verification strategy is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

Periodically run query execution analysis with EXPLAIN ANALYZE and an automated script that verifies the privilege set of each user with SHOW GRANTS queries.
Quality axisHow to verifyDefinition of done
CorrectnessAutomate the normal/failure cases.The core scenarios pass reproducibly.
Regression preventionWhen fixing a bug, leave the same case behind as a test.The same failure is not deployed again.
OperabilityCheck the logs, metrics, and alerts.There is a path for tracing the cause when a problem occurs.

MySQL: frequently asked questions

What is the difference between InnoDB and MyISAM?

InnoDB is the default storage engine and supports transactions, row-level locking, foreign keys, and crash recovery. MyISAM has no transactions and locks whole tables. Use InnoDB unless you have a specific reason not to.

What should I check when non-Latin text or emoji is garbled?

Check that the character set is utf8mb4. MySQL's utf8 stores at most 3 bytes per character, so it cannot store 4-byte characters such as emoji. Set utf8mb4 everywhere: the database, tables, and columns, and the application's connection settings.

How do I find and improve slow queries?

Turn on the slow query log to collect queries that exceed a threshold, and read their execution plans with EXPLAIN. If a query reads the whole table, add an index that matches its conditions and sort order; if an index exists but is not used, check whether a function is applied to the column or the types do not match.

← Previous guideOracleNext guide β†’PostgreSQL