interview questions for mysql

mysql.md

Basic

Storage Engine

https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

• InnoDB (default)
  • transaction-safe (ACID compliant) (commit, rollback, crash recovery)
  • row-level lock and Oracle-style consistent nonlocking reads
• MyISAM
  • small footprint
  • table-level locking
  • used in read-only or read-mostly workloads
• Memory (HEAP)
  • fast access
• CSV
  • import and export
• Archive
  • compact
  • uhindexed
  • for large amounts of information
• Balckhole
  • accept but doesn't store data (like /dev/null)
• NDB (NDBCLUSTER)
  • clustered database
• Merge
  • for VLDB such as data warehousing
• Federated
  • link separate MySQL servers
  • for distrubted or data mart environments

ACID

https://dev.mysql.com/doc/refman/8.0/en/mysql-acid.html

• Atomicity
  • COMMIT statement
  • ROLLBACK statement
  • operational data from the INFORMATION_SCHEMA tables
• Consistency
  • double write buffer
  • crash recovery
• Isolation
  • SET ISOLATION LEVEL statement
  • low-level details of InnoDB locking
• Durability
  • double write buffer
  • binlog
  • file per table
  • write buffer in a storage device
  • battery-backed cache in a storage device
  • fsync() system call
  • uninterruptible power supple (UPS)
  • backup strategy
  • hardware, network

Isolation Level

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_isolation_level

• SERIALIZABLE
• REPEATABLE READ (default)
• READ COMMITTED
• READ UNCOMMITTED

Optimizing with EXPLAIN

https://dba.stackexchange.com/questions/101623/identify-slow-query-without-slow-query-logs-in-mysql-server

InnoDB

Data Structure

B tree v.s. B+ tree

AVL, Red-black tree, Skip List

Indexing

Lock

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

Concurrency

Read

Write

MVCC

Log

Crash-safe

Redo log

Binlog

Undo log

Relay log