Question 1

MySQL vs PostgreSQL, which should I learn?

Accepted Answer

MySQL: read-heavy, LAMP ecosystem, simpler administration, 90% of web servers, better for high-traffic CRUD apps, no complex analytics. PostgreSQL: complex queries (CTEs, recursive), advanced data types (JSON, arrays), ACID without compromise, analytics queries, but requires more tuning and bigger ops team. Learn MySQL first if building web apps, PostgreSQL if building data platforms.

Question 2

InnoDB vs MyISAM, which storage engine?

Accepted Answer

Always use InnoDB (default in MySQL 5.7+). MyISAM is obsolete: no transactions, no crash recovery, full table locks (kills concurrency). InnoDB has: ACID compliance, row-level locking, crash-safe replication, foreign keys, and modern query optimization. MyISAM only survives in legacy systems; never use for new projects.

Question 3

How do I set up replication and handle failover?

Accepted Answer

Master-Slave replication: slave reads binary log from master and replays SQL. Setup: enable binary logging on master, create replication user, run `CHANGE MASTER` on slave. Failover: promote slave to master (manual or via MHA/Orchestrator automation). Best practice: use Group Replication for automatic failover without coordinator, or Percona XtraDB Cluster for synchronous replication. Monitor replication lag (`SHOW SLAVE STATUS`).

Question 4

How do I scale a single MySQL server horizontally?

Accepted Answer

Three approaches: (1) Read replicas: distribute SELECT traffic across slaves (same schema), all writes go to master. Slow queries and analytics on replicas. (2) Sharding (horizontal partitioning): split data by key (user_id % shard_count), each shard is separate DB. Complex but true horizontal scale. Need application logic to route queries. (3) Vitess (proxy layer): MySQL sharding + routing + auto-resharding without app changes. Recommended for 100GB+ datasets.

Question 5

When should I NOT use MySQL?

Accepted Answer

Don't use MySQL for: (1) unstructured/nested data (use MongoDB), (2) complex analytical queries across 100B+ rows (use Redshift/BigQuery), (3) real-time graph queries (use Neo4j), (4) <1MB datasets (use SQLite), (5) strict SERIALIZABLE consistency (use PostgreSQL). MySQL shines at web application data (user accounts, orders, content), not analytics or documents.

Question 6

How do I optimize slow queries?

Accepted Answer

Enable slow query log: set `long_query_time=1` in my.cnf. Use `EXPLAIN` to find missing indexes or bad join orders. Key rules: (1) index WHERE + JOIN columns, (2) index prefix for LIKE queries, (3) avoid OR conditions (use UNION instead), (4) denormalize read-heavy tables, (5) use query caching or Redis for repeated results. Use `pt-query-digest` to analyze slowlog patterns.

Question 7

What's the difference between UTF8 and UTF8MB4?

Accepted Answer

UTF8 in MySQL is broken: only 3 bytes max, can't store emoji or rare unicode. UTF8MB4 is real UTF-8: 4 bytes, stores any character. Always use UTF8MB4 for new databases: `ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;` and for new tables. Collation: use `utf8mb4_unicode_ci` (case-insensitive) for user-facing strings, `utf8mb4_bin` for case-sensitive (passwords, identifiers).

地區	初級	中級	高級
USA	$95,000	$140,000	$190,000
UK	£60,000	£90,000	£130,000
EU	€65,000	€95,000	€140,000
CANADA	CA$100,000	CA$150,000	CA$210,000

MySQL

什麼是 MySQL

📋 開始之前

💰 各地區薪資

🎓 認證

🎯 使用 MySQL 的職業

⚖ 對比

❓ 常見問題

不確定這項技能適不適合你？

找到理想的職業方向