Back-End Development Database Design and Optimization 2 — Questions and Answers
Question 1: What is the difference between a clustered and a non-clustered index?
- A clustered index is stored in memory; a non-clustered index is stored on disk
- A clustered index determines the physical order of rows; a non-clustered index is a separate structure with pointers (Correct answer)
- Clustered indexes are for text columns; non-clustered are for numeric columns
- There is no functional difference
Correct answer: A clustered index determines the physical order of rows; a non-clustered index is a separate structure with pointers
A clustered index sorts and stores the actual data rows in index order (one per table), while a non-clustered index is a separate structure that points to the actual data rows.
Question 2: What is database sharding?
- Splitting a database table into multiple columns for performance
- Horizontally partitioning data across multiple database instances to distribute load (Correct answer)
- Creating a read-only replica of the database
- Compressing old database records into archive tables
Correct answer: Horizontally partitioning data across multiple database instances to distribute load
Sharding distributes rows of a database table across multiple database servers (shards) based on a shard key, allowing horizontal scaling beyond a single server's capacity.
Question 3: What is a database connection pool and why is it important?
- A backup group of database servers for failover
- A cache of reusable database connections to avoid the overhead of establishing new connections for each request (Correct answer)
- A group of database queries batched together for efficiency
- A network load balancer for database traffic
Correct answer: A cache of reusable database connections to avoid the overhead of establishing new connections for each request
Connection pooling maintains a set of established database connections that can be reused by incoming requests, significantly reducing the latency and resource cost of creating connections.
Question 4: What does the EXPLAIN statement do in SQL?
- Adds comments to a SQL query
- Shows the query execution plan the database engine will use, helping identify performance bottlenecks (Correct answer)
- Lists all available SQL functions and their usage
- Describes the schema of a table
Correct answer: Shows the query execution plan the database engine will use, helping identify performance bottlenecks
EXPLAIN (or EXPLAIN ANALYZE) reveals how the database engine plans to execute a query, including whether it will use indexes or perform full table scans.
Question 5: What is optimistic locking in database concurrency?
- Locking all rows in a table before any writes
- Assuming conflicts are rare and checking for changes at commit time rather than locking rows upfront (Correct answer)
- Allowing multiple transactions to write simultaneously without any checks
- Locking only the most frequently accessed rows
Correct answer: Assuming conflicts are rare and checking for changes at commit time rather than locking rows upfront
Optimistic locking lets transactions proceed without row-level locks, using a version number or timestamp to detect conflicts at commit time and reject stale updates.
Question 6: What is a database view and what is its primary use case?
- A cached copy of query results stored on disk permanently
- A virtual table defined by a stored query, used to simplify complex queries and enforce access control (Correct answer)
- A graphical tool for viewing database schemas
- A read-only snapshot of the database at a point in time
Correct answer: A virtual table defined by a stored query, used to simplify complex queries and enforce access control
A view is a stored query that acts like a virtual table, allowing developers to simplify complex joins, encapsulate business logic, and restrict data access without duplicating data.
Back-End Development Database Design and Optimization 2
What is the difference between a clustered and a non-clustered index?