Guide to MySQL, the most widely used open source RDBMS
π¬ MySQL Complete Guide
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
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 / trait
InnoDB (recommended)
MyISAM
Transactions (ACID)
Supported
Not supported (atomicity not guaranteed)
Locking granularity
Row-level lock (excellent concurrency)
Table-level lock (whole table locked on writes)
Foreign keys (FK)
Supported
Not supported
Crash recovery
Automatic 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 planEXPLAIN 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)
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 rowsCREATE 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 ordersWHERE 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 orderSELECT /*+ JOIN_ORDER(c, o) */ c.grade, SUM(o.amount)FROM orders o JOIN customers c ON c.id = o.customer_idWHERE 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 exceededSELECT /*+ MAX_EXECUTION_TIME(1000) */ COUNT(*) FROM orders;
Column in the table format
What to look at
type
The access method. From best to worst: const, eq_ref, ref, range, index, ALL. ALL is a full table read.
possible_keys / key
The indexes that could be used and the one actually chosen. A NULL key means no index was used.
rows
The number of rows expected to be read. Far more than the result count means the index did not narrow the condition.
filtered
The estimated percentage of rows read that remain after the condition.
Extra: Using index
Answered from the index alone (covering index).
Extra: Using where
Rows are filtered by the condition after being read.
Extra: Using filesort
Index order could not be used, so a separate sort runs.
Extra: Using temporary
A temporary table is created (GROUP BY, DISTINCT, and so on).
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 timeSELECT 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_sentFROM performance_schema.events_statements_summary_by_digestORDER BY sum_timer_wait DESCLIMIT 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 startedSELECT object_schema, object_name, index_name FROM sys.schema_unused_indexes;-- Before dropping an index, make it "invisible" and check the impactALTER 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 statisticsANALYZE 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'.
Item
Check and adjust
innodb_buffer_pool_size
The 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 log
Turn it on in production and lower the threshold to around 1 second.
Connections
Size the application connection pool rather than raising max_connections.
Composite indexes
Check that the order matches the conditions and sort, and that no indexes overlap.
Primary key
InnoDB stores data in primary key order. A random value (UUID v4) as the primary key slows inserts and uses more space.
Long transactions
A transaction left open piles up locks and undo. Check with information_schema.innodb_trx.
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 settingsspring: 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.
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 variablesSHOW 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 databaseALTER DATABASE mydb_global CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
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 databasemysqldump -u root -p my_database > my_database_backup.sql# Restore the databasemysql -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 point
Question to ask
Practical guideline
Boundaries
Which parts of the MySQL code are most likely to change?
Separate input/output, configuration, external integrations, and the core rules.
State
Where is state created and where does it go away?
Make the owner and lifecycle of state visible in the code.
Failure
What 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.
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 axis
How to verify
Definition of done
Correctness
Automate the normal/failure cases.
The core scenarios pass reproducibly.
Regression prevention
When fixing a bug, leave the same case behind as a test.
The same failure is not deployed again.
Operability
Check 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.