11 advanced MySQL questions

 

1. Explain MySQL architecture
   . - The front layer
   takes care of network connections and security authentications, the
   middle layer does the SQL query parsing, and then the query is handled
   off to the storage engine. A storage engine could be either a default
   one supplied with MySQL (MyISAM) or a commercial one supplied by a
   third-party vendor (ScaleDB, InnoDB, etc.)
2. Explain MySQL locks
   . - Table-level locks allow the
   user to lock the entire table, page-level locks allow locking of
   certain portions of the tables (those portions are referred to as
   tables), row-level locks are the most granular and allow locking of
   specific rows.
3. Explain multi-version concurrency control in MySQL
   .
   - Each row has two additional columns associated with it - creation
   time and deletion time, but instead of storing timestamps, MySQL stores
   version numbers.

 

4. What are MySQL transactions?
   - A set of instructions/queries that should be executed or rolled back as a single atomic unit.
5. What’s ACID?
   - Automicity - transactions are
   atomic and should be treated as one in case of rollback. Consistency -
   the database should be in consistent state between multiple states in
   transaction. Isolation - no other queries can access the data modified
   by a running transaction. Durability - system crashes should not lose
   the data.
6. Which storage engines support transactions in MySQL?
   - Berkeley DB and InnoDB.
7. How do you convert to a different table type?
   - ALTER TABLE customers TYPE = InnoDB
8. How do you index just the first four bytes of the column?
   - ALTER TABLE customers ADD INDEX (business_name(4))
9. What’s the difference between PRIMARY KEY and UNIQUE in MyISAM?
   - PRIMARY KEY cannot be null, so essentially PRIMARY KEY is equivalent to UNIQUE NOT NULL.
10. How do you prevent MySQL from caching a query?
    - SELECT SQL_NO_CACHE …
11. What’s the difference between query_cache_type 1 and 2?
    - The second one is on-demand and can be retrieved via SELECT SQL_CACHE
    … If you’re worried about the SQL portability to other servers, you can
    use SELECT /* SQL_CACHE */ id FROM … - MySQL will interpret the code
    inside comments, while other servers will ignore it.