These interview questions were collected from every corner of the internet

How do you locate a slow query?

  • Approach 1
    • Open-source tools
      • Arthas
    • Ops tools
      • Prometheus
      • Skywalking
  • Approach 2
    • MySQL’s built-in slow log (has a performance cost)
      • How to enable the slow log
        • /etc/my.conf
          • slow_query_log
          • long_query_time

A SQL statement runs slowly — how do you analyze it?

  • Reasons for slowness
    • Aggregate queries
    • Multi-table queries
    • Queries on tables with too much data
    • Deep pagination queries
  • How to analyze the slowness
    • The explain / desc command
      • How to read the result?
        • The extra optimization hints
          • using where; using index — the lookup used an index, and the needed data is all in the index columns, so no back-to-table lookup is needed
          • using index condition — the lookup used an index
        • type
          • index, all — need optimization

Do you know about indexes? What is an index?

An index is a data structure (ordered) that helps MySQL fetch data efficiently. Beyond the data itself, the database system maintains data structures that satisfy specific search algorithms (such as B+ trees); these structures reference (point to) the data in some way, so advanced search algorithms can be implemented on them. Such a data structure is an index.

  • A data structure that helps MySQL fetch data efficiently

Do you understand the underlying data structure of indexes?

  • Binary search tree
  • Red-black tree
  • B-tree
  • B+ tree
    • Higher order, shorter paths
    • Lower disk read/write cost; only leaf nodes actually store data
    • Convenient for full scans and range queries; leaf nodes form a doubly linked list

What is a clustered index? What is a non-clustered index?

  • Clustered index
  • Secondary index

What is a back-to-table (row lookup) query?

Do you know what a covering index is?

When a query can find all its data through the index in one go, with no back-to-table lookup, that’s a covering index

How do you handle super-large pagination in MySQL?

Use a covering index plus a subquery

select * 
from tb_sku t,
  (select id from tb_sku order by id limit 9000000,10) a 
where t.id = a.id

What are the principles for creating indexes?

  • Create indexes on tables with over 100,000 rows that are queried fairly frequently
  • Fields often used as query conditions should be indexed
  • Use highly selective columns as indexes; create unique indexes where possible
  • For long string-type fields, a prefix index can be created
  • Prefer composite indexes; reduce single-column indexes
  • Use NOT NULL on index columns so the optimizer can more easily decide which index is best for a query

Under what circumstances does an index become ineffective?

  • Violating the leftmost-prefix rule
  • Columns to the right of a range query can’t use the index
  • Performing operations on an index column disables that index
  • Not quoting strings disables the index (failure caused by implicit type conversion)
  • Non-trailing string matching disables the index

Talk about your experience with SQL optimization

  • Table-design optimization
    • We referenced the Alibaba developer handbook (“Songshan Edition”)
    • Design data types according to the actual stored value lengths
  • Index optimization
  • SQL-statement optimization
    • select statements must specify field names
      • For covering indexes
    • Prefer union all over union
    • Avoid expression operations on fields in the where clause
    • Use inner join instead of left/right join where possible; if you must, drive with the smaller table
  • Master-replica replication, read-write separation
  • Sharding databases and tables

Explain the properties of a transaction in detail

What problems do concurrent transactions bring, and how are they solved? What is MySQL’s default isolation level?

  • Problems of concurrent transactions
    • Dirty read
      • Read Committed
    • Non-repeatable read
      • a value problem
      • Repeatable Read
    • Phantom read
      • a count problem
      • Serializable
  • Isolation levels
    • Read Uncommitted
    • Read Committed
    • Repeatable Read*
    • Serializable

The difference between undo log and redo log

  • Buffer pool
  • Data page
  • redo log
    • physical
    • durable
  • undo log
    • logical
    • atomic, consistent

How is transaction isolation guaranteed under the hood?

  • Locks: exclusive lock
  • MVCC
    • Multi-Version Concurrency Control
    • Relies on
      • Hidden fields
        • DB_TRX_ID
        • DB_ROLL_PTR
        • DB_ROW_ID
      • undo log
        • the version chain
          • version-chain data access rules
      • readview
        • snapshot read
          • Read Committed: a snapshot read is generated on every select
          • Repeatable Read: the first select after opening the transaction is the snapshot read point
        • core fields
          • m_ids
          • min_trx_id
          • max_trx_id
          • creator_trx_id
        • Under the RC isolation level, a ReadView is generated on each snapshot read within the transaction

The principle of MySQL master-replica synchronization

  • Core: the binary log
    • BINLOG
      • DDL
      • DML When the master commits a transaction, the change record is written to the Binlog Replica: binlog -> relay log Replica: reads events from the relay log -> replica’s database

Has your project used database/table sharding?

  • When to shard databases and tables
    • A single table reaches 10 million rows or 20GB
    • Optimization can’t solve the performance problem
    • IO / CPU bottlenecks
  • How to split
    • Vertical splitting
      • Vertical database splitting
        • Split different tables into different databases
        • Suited for microservices
      • Vertical table splitting
        • Put infrequently used fields in a separate table
    • Horizontal splitting
      • Horizontal database splitting
        • Split one database’s data across multiple databases
        • Routing rules
          • Modulo by id node
          • Route by id range
      • Horizontal table splitting
        • Split one table’s data across multiple tables
  • New problems
    • Distributed transaction consistency
    • Cross-node join queries
    • Cross-node pagination and sorting functions
    • Primary-key deduplication
  • Solutions
    • Sharding middleware
      • MyCat
      • ShardingSphere

Deeper questions

  1. Why is an auto-increment primary key recommended for InnoDB?
  2. Why does a non-increment primary key cause page splits?
  3. Why do MyISAM and InnoDB have different index structures?
  4. Why is a B+ tree suited for disk while a red-black tree is not?