十一个高级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.
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.
【十一个高级MySql面试题】相关文章
2. mysql_pconnect()和mysql_connect()有什么区别
3. MySQL面试题
4. MySQL面试题目集锦
5. 几个MySql的面试题
6. 遇到的Mysql的面试题
8. MYSQL基础面试题
9. 一个SQL面试题
10. Tomcat Mysql datasource数据源配置
本文来源:https://www.mianshiwenti.com/a13448.html
进入下载页面
上一篇:如何提高MySql的安全性
下一篇:什么是PL/SQL
﹝十一个高级MySql面试题﹞相关内容
- mysql的最长数据库名,表名,字段名可以是多长
- PHP如何调用MYSQL存储过程
- PHP如何与mysql建立链接
- 说一下mysql, oracle等常见数据库的分页实现方案
- 如何写出高质量、高性能的MySQL查询
- 介绍一下如何优化MySql
- 介绍一下Mysql的存储引擎
- 如何提高MySql的安全性
- 简单叙述一下MYSQL的优化
- MYSQL支持事务吗