Identifying Redundant Records

To locate duplicate rows based on a specific column, use the following approach:

SELECT * FROM target_table 
WHERE target_id IN (
   SELECT target_id FROM target_table 
   GROUP BY target_id HAVING COUNT(target_id) > 1
);

Database and Table Capacity Analysis

To identify the largest tables by storage size (excluding system schemas), run the following query:

SELECT TABLE_SCHEMA, TABLE_NAME, 
      ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS total_gb,
      TABLE_ROWS
FROM information_schema.tables
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY total_gb DESC LIMIT 30;

Index Management

To find tables lacking a Primary Key:

SELECT t.TABLE_SCHEMA, t.TABLE_NAME 
FROM information_schema.tables t
LEFT JOIN information_schema.columns c 
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME AND c.COLUMN_KEY = 'PRI'
WHERE t.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')
AND c.TABLE_NAME IS NULL AND t.TABLE_TYPE = 'BASE TABLE';

To identify potential redundant indexes by matching columns:

SELECT s1.TABLE_SCHEMA, s1.TABLE_NAME, s1.INDEX_NAME AS index_a, s2.INDEX_NAME AS index_b
FROM information_schema.statistics s1
JOIN information_schema.statistics s2 ON s1.TABLE_SCHEMA = s2.TABLE_SCHEMA 
   AND s1.TABLE_NAME = s2.TABLE_NAME 
   AND s1.SEQ_IN_INDEX = s2.SEQ_IN_INDEX 
   AND s1.COLUMN_NAME = s2.COLUMN_NAME
WHERE s1.SEQ_IN_INDEX = 1 AND s1.INDEX_NAME <> s2.INDEX_NAME;

System Performacne and Session Monitoring

To view active (non-sleeping) connections:

SELECT * FROM information_schema.processlist WHERE COMMAND <> 'Sleep';

To identify high-impact queries using the Performance Schema:

SELECT query, avg_latency, exec_count 
FROM sys.statement_analysis 
ORDER BY avg_latency DESC LIMIT 10;

Transactional Isolation Levels

To check and modify current transaction isolation:

-- Check session isolation
SELECT @@transaction_isolation;

-- Set global isolation
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

In-Place Online DDL

Perform schema changes without locking the table by specifying the algorithm and lock policy:

ALTER TABLE my_table ADD INDEX idx_col(col_name), ALGORITHM=INPLACE, LOCK=NONE;

Monitoring Auto-Increment Usage

To prevent integer overflow, monitor the usage percentage of auto-increment columns:

SELECT TABLE_NAME, COLUMN_NAME, AUTO_INCREMENT,
      ROUND((AUTO_INCREMENT / POWER(2, 31) * 100), 2) AS usage_percent
FROM information_schema.tables t
JOIN information_schema.columns c USING (table_schema, table_name)
WHERE c.EXTRA = 'auto_increment';