百度 MYSQL 笔试题
收藏一下
题目内容:


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.