Polimi CS - Notes

4.5 - The SELECT Statement in MySQL

5 min read

  • SELECT statement

In MySQL, the SELECT statement is a powerful and flexible command used to retrieve data from one or more tables. The syntax for the SELECT statement encompasses a variety of options and clauses that allow for precise control over the data returned. Below is the general syntax:

SELECT
  [ALL | DISTINCT | DISTINCTROW]
  [HIGH_PRIORITY]
  [STRAIGHT_JOIN]
  [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
  [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
  select_expr [, select_expr]
  [INTO out_variable]
  [FROM table_references
    [PARTITION partition_list]]
  [WHERE where_condition]
  [GROUP BY {col_name | expr | position}
  [HAVING where_condition]
  [WINDOW window_name AS (window_spec)
    [, window_name AS (window_spec)] ... ]
  [ORDER BY {col_name | expr | position}
    [ASC | DESC], ... [WITH ROLLUP]]
  [LIMIT {[offset,] row_count | row_count OFFSET offset}]
  [FOR {UPDATE | SHARE}
    [OF tbl_name [, tbl_name] ... ]
    [NOWAIT | SKIP LOCKED] | LOCK IN SHARE MODE];

Key Components of the SELECT Statement

  1. Selection Modifiers:

    • ALL, DISTINCT, or DISTINCTROW: These keywords specify whether to return all rows or only unique rows from the query result.
    • HIGH_PRIORITY: This modifier can be used to execute the query with higher priority than other concurrent operations.

  2. Join Types: STRAIGHT_JOIN: This forces the join order as specified in the query, which can be useful for performance tuning.

  3. Result Options:

    • SQL_SMALL_RESULT and SQL_BIG_RESULT: These indicate the expected size of the result set, optimizing memory usage.
    • SQL_BUFFER_RESULT: This option allows results to be buffered, useful for complex queries.
    • SQL_NO_CACHE: This prevents caching of the query results, ensuring fresh data.
    • SQL_CALC_FOUND_ROWS: This option allows for the retrieval of the total number of rows that match the query without the LIMIT clause, which can be useful for pagination.

  4. Data Source: The FROM clause specifies the tables from which to retrieve data, and may include partitioning options with PARTITION.

  5. Filtering and Aggregation:

    • The WHERE clause allows for filtering results based on specified conditions.
    • The GROUP BY clause is used for aggregating data based on one or more columns.
    • The HAVING clause filters aggregated results.

  6. Windowing: The WINDOW clause defines named windows for use in window functions, providing advanced analytic capabilities.

  7. Sorting and Limiting Results:

    • The ORDER BY clause sorts the result set based on specified columns, allowing for ascending or descending order. The WITH ROLLUP option can be used for summary rows.
    • The LIMIT clause restricts the number of rows returned, with options for specifying an offset.

  8. Locking Mechanisms:

    • The FOR UPDATE or FOR SHARE clauses place locks on the rows returned by the query. Rows examined by the query are write-locked until the current transaction concludes.
    • Using FOR UPDATE allows only one transaction to update the locked rows, while other transactions can read them but not modify or delete them.
    • NOWAIT causes the query to return an error immediately if the row lock cannot be obtained due to locks held by other transactions. This is useful for avoiding long waits for locks that may never be released.
    • SKIP LOCKED allows the query to execute immediately while excluding rows that are already locked by another transaction, thus enabling other transactions to proceed without waiting for locks to be released.

When using locking mechanisms such as FOR UPDATE or FOR SHARE, it is essential to understand the implications for concurrent transactions. Shared locks permit other transactions to read the locked rows, but prevent any modifications or deletions. This ensures data consistency during transactions. However, the choice of locking strategy can significantly impact performance and concurrency.

In scenarios where immediate access is critical, NOWAIT may be preferred to avoid delays, whereas SKIP LOCKED can be beneficial in high-concurrency environments, allowing transactions to bypass locked rows altogether.

Overall, the SELECT statement in MySQL is not only a tool for retrieving data but also plays a crucial role in managing data integrity and concurrency in database systems. Understanding its components and options can greatly enhance the effectiveness of database interactions and transaction management.

MySQL Transactions and Locking Mechanisms

Basic Transaction with Locking
mysql> CREATE TABLE t (i INT, PRIMARY KEY(i)) ENGINE=InnoDB;
 
mysql> INSERT INTO t (i) VALUES (1), (2), (3);
mysql> START TRANSACTION;
 
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

A new table t is created with a primary key on column i, and three records are inserted. A transaction is initiated with START TRANSACTION. The SELECT statement retrieves the row with i = 2 while applying a FOR UPDATE lock. This lock prevents other transactions from modifying or deleting the row until the transaction is committed or rolled back. As a result, row 2 is successfully locked for update.

Locking with NOWAIT
mysql> START TRANSACTION;
 
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.

Another transaction begins. The SELECT statement attempts to lock the same row (where i = 2) for update, but it includes the NOWAIT option. If the row is already locked by another transaction, the query will return an error immediately instead of waiting for the lock to be released. This strategy is useful when immediate access is required, and waiting for a lock would lead to performance degradation.

Locking with SKIP LOCKED
mysql> START TRANSACTION;
 
mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+ 
| i |
+---+ 
| 1 |
| 3 |
+---+

In this session, a transaction is initiated again, but this time, the SELECT statement employs the SKIP LOCKED option. This means the query will skip over any rows that are currently locked by other transactions. As a result, the query retrieves the unclaimed records (i.e., the rows that are not locked), which in this case are rows 1 and 3. This approach is particularly beneficial in high-concurrency environments where avoiding conflicts is more critical than retrieving every possible row. It allows for efficient queue management, such as processing tasks that can be consumed immediately without waiting for locks to be released.

For further details, you can refer to the MySQL documentation on InnoDB locking and read behavior: MySQL InnoDB Locking Reads.

Graph View