MySQL Advanced Features: Views, Transactions, Triggers, and Performance Optimization

Views in MySQL

Views are virtual tables that don't占用 any physical storage. When querying a view, MySQL retrieves data dynamically from the underlying base tables.

Key Characteristics

Modifications to a view propagate to the underlying base tables
Changes to base tables are visible through the view when querying
Views typically shouldn't be modified directly due to potential field mapping issues
They simplify complex SQL queries and provide data security by restricting access to specific columns

View Operations

CREATE OR REPLACE VIEW user_summary AS 
SELECT user_id, username, email FROM users WHERE active = 1;

DESC user_summary;
SHOW CREATE VIEW user_summary;

ALTER VIEW user_summary AS 
SELECT user_id, username, email, created_at FROM users;

DROP VIEW user_summary;

Triggers

Triggers execute predefined code automatically when specific database events occur.

Syntax Structure

CREATE TRIGGER trigger_name trigger_time trigger_event 
ON table_name FOR EACH ROW
BEGIN
    -- trigger logic here
END;

Parameters:

trigger_time: BEFORE or AFTER
trigger_event: INSERT, UPDATE, or DELETE
OLD and NEW keywords access row data: OLD for previous values, NEW for incoming values

Practical Example

CREATE TABLE command_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    operator VARCHAR(50),
    command VARCHAR(255),
    executed_at DATETIME,
    status ENUM('success', 'failure')
);

CREATE TABLE failed_commands (
    id INT PRIMARY KEY AUTO_INCREMENT,
    failed_command VARCHAR(255),
    failure_time DATETIME
);

DELIMITER //

CREATE TRIGGER log_command_failure 
AFTER INSERT ON command_log
FOR EACH ROW
BEGIN
    IF NEW.status = 'failure' THEN
        INSERT INTO failed_commands VALUES 
            (NULL, NEW.command, NEW.executed_at);
    END IF;
END//

DELIMITER ;

INSERT INTO command_log VALUES 
(NULL, 'admin', 'DROP TABLE users', NOW(), 'failure'),
(NULL, 'admin', 'SELECT * FROM logs', NOW(), 'success');

SELECT * FROM failed_commands;

Important Notes

Use DELIMITER to change the statement terminator
Foriegn key cascades don't activate triggers
Transactions and SELECT statements aren't allowed within triggers
Only one trigger per table for the same timing and event combination

Transactions

Transactions group multiple operations into a single logical unit that either completely succeeds or fails.

Transaction Control Commands

START TRANSACTION;

SAVEPOINT checkpoint1;

COMMIT;

ROLLBACK TO checkpoint1;

ROLLBACK;

Transaction Implementation with Python

import pymysql

try:
    connection = pymysql.connect(
        host='localhost',
        user='app_user',
        password='secure_pass',
        database='finance_db'
    )
    
    cursor = connection.cursor(pymysql.cursors.DictCursor)
    
    debit_sql = 'UPDATE accounts SET balance = balance - 500 WHERE account_id = 1'
    credit_sql = 'UPDATE accounts SET balance = balance + 500 WHERE account_id = 2'
    
    try:
        cursor.execute(debit_sql)
        cursor.execute(credit_sql)
        connection.commit()
        print("Transaction completed successfully")
    except Exception:
        connection.rollback()
        print("Transaction failed, rolled back")
        
except Exception as error:
    print(f"Connection error: {error}")
finally:
    if cursor:
        cursor.close()
    if connection:
        connection.close()

Transaction Properties (ACID)

Atomicity: Transactions execute as a single indivisible unit—either all operations succeed or none do.

Consistency: The database maintains integrity constraints before and after transaction execution.

Isolation: Concurrent transactions don't interfere with each other's data modifications.

Durability: Once committed, transaction changes persist permanently.

Isolation Levels

Concurrency Problems

Problem	Description
Dirty Read	Reading uncommitted changes from other transactions
Non-repeatable Read	Different results when re-reading due to concurrent modifications
Phantom Read	Additional rows appear when re-executing a query after inserts

Isolation Level Configuration

Level	Dirty Read	Non-repeatable	Phantom
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

SELECT @@transaction_isolation;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Configuration file setting:

transaction_isolation=REPEATABLE-READ

Stored Procedures

Stored procedures contain precompiled SQL statements with optional flow control and transaction logic.

Creating and Using Procedures

DELIMITER //

CREATE PROCEDURE get_accounts_in_range(
    IN min_balance DECIMAL(10,2),
    IN max_balance DECIMAL(10,2),
    OUT result_status VARCHAR(50)
)
BEGIN
    SELECT * FROM accounts 
    WHERE balance BETWEEN min_balance AND max_balance;
    
    SET result_status = 'completed';
END//

DELIMITER ;

SET @status = '';
CALL get_accounts_in_range(1000, 5000, @status);
SELECT @status;

Error Handling in Procedures

DELIMITER //

CREATE PROCEDURE transfer_funds(OUT return_code VARCHAR(50))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET return_code = 'Error occurred, rolling back';
        ROLLBACK;
    END;
    
    DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
        SET return_code = 'Warning occurred, rolling back';
        ROLLBACK;
    END;
    
    START TRANSACTION;
    UPDATE accounts SET balance = balance - 200 WHERE id = 1;
    UPDATE accounts SET balance = balance + 200 WHERE id = 2;
    COMMIT;
    
    SET return_code = 'Success';
END//

DELIMITER ;

CALL transfer_funds(@result);
SELECT @result;

Calling from Python

import pymysql

conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='banking'
)
cursor = conn.cursor()

cursor.callproc('get_accounts_in_range', (500, 3000, 0))
results = cursor.fetchall()

cursor.execute('SELECT @_get_accounts_in_range_2')
output_params = cursor.fetchall()

cursor.close()
conn.close()

Procedures vs Functions

Functions: Pure computation tools, return single values, cannot contain SQL that modifies data
Procedures: Contain both SQL and logic, support input/output parameters, can handle transactions

User-Defined Functions

CREATE FUNCTION calculate_tax(subtotal DECIMAL(10,2))
RETURNS DECIMAL(10,2)
RETURN subtotal * 0.08;

SELECT product_name, price, calculate_tax(price) AS tax FROM products;

Database Backup and Restore

Backup Commands

mysqldump -uroot -p dbname > backup.sql
mysqldump -uroot -p dbname table1 table2 > partial_backup.sql
mysqldump -uroot -p --databases db1 db2 db3 > multi_db_backup.sql
mysqldump -uroot -p --all-databases > full_backup.sql

Restore Commands

mysql -uroot -p dbname < backup.sql

Or within MySQL:

USE dbname;
SOURCE /path/to/backup.sql;

Indexes and Query Optimization

Indexes are specialized data structures that accelerate data retrieval by reducing disk I/O operations.

Index Types

Clustered Index (Primary Key):

Stores actual row data at the leaf node
Each InnoDB table requires a primary key
Fastest access method

Secondary Index:

Contains index key values with corresponding primary keys
May require additional lookups (covering index optimization)

Creating and Managing Indexes

CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_name_status ON users(full_name, account_status);
DROP INDEX idx_email ON users;

Optimization Strategies

Index columns with low cardinality (distinct values)
Use indexed columns in WHERE clauses
Avoid leading wildcards in LIKE patterns
Don't perform calculations on indexed columns
Consider composite indexes with low-cardinality columns on the left
Use UNION instead of OR for better index utilization

-- Prefer this:
SELECT * FROM users WHERE email = 'user@example.com';

-- Avoid this:
SELECT * FROM users WHERE id * 10 = 100;

-- Use UNION for OR conditions:
SELECT * FROM users WHERE status = 'active'
UNION
SELECT * FROM users WHERE role = 'admin';

Index Structure

MySQL uses B+ tree structure for indexing, which minimizes disk I/O by maintaining balanced tree depth. The leftmost column principle governs composite index usage.

Tags: MySQL database views transactions triggers

Posted on Thu, 07 May 2026 02:04:26 +0000 by recklessgeneral

Freaks City