DataGrip Cheatsheet¶

DataGrip - JetBrains Database IDE

DataGrip ist eine leistungsstarke Datenbank-IDE von JetBrains, die eine intelligente Abfragekonsole, effiziente Schemanavigation und umfassende Datenbankentwicklungstools bietet. Sie unterstützt mehrere Datenbanksysteme und bietet fortschrittliche Funktionen für Datenbankprofis.

Installation und Einrichtung¶

Systemanforderungen¶

# Minimum system requirements
# RAM: 2 GB (8 GB recommended)
# CPU: Any modern CPU
# Disk: 3.5 GB
# OS: Windows 10+, macOS 10.14+, Linux with glibc 2.17+

# Check Java version (required for DataGrip)
java -version

# DataGrip requires Java 11 or higher
# If not installed, download from:
# https://adoptium.net/

Installationsmethoden¶

# Windows - Download from JetBrains website
# https://www.jetbrains.com/datagrip/download/

# macOS - Using Homebrew
brew install --cask datagrip

# Linux - Using Snap
sudo snap install datagrip --classic

# Linux - Using Flatpak
flatpak install flathub com.jetbrains.DataGrip

# Linux - Manual installation
wget https://download.jetbrains.com/datagrip/datagrip-2023.3.tar.gz
tar -xzf datagrip-2023.3.tar.gz
cd DataGrip-2023.3/bin
./datagrip.sh

# Toolbox App (recommended for managing JetBrains products)
# Download JetBrains Toolbox App
# Install DataGrip through Toolbox for easy updates

Erste Konfiguration¶

# Configuration directories
# Windows: %APPDATA%\JetBrains\DataGrip2023.3
# macOS: ~/Library/Application Support/JetBrains/DataGrip2023.3
# Linux: ~/.config/JetBrains/DataGrip2023.3

# Import settings from previous version
# File → Import Settings → Select settings.zip

# Configure IDE settings
# File → Settings (Ctrl+Alt+S)

Lizenzierung¶

# License options:
# 1. 30-day free trial
# 2. Individual license ($199/year)
# 3. Commercial license ($199/year per user)
# 4. Educational license (free for students/teachers)
# 5. Open Source license (free for qualifying projects)

# Activate license
# Help → Register → Enter license key or login to JetBrains account

# Offline activation
# Help → Register → Activation code

Datenbankverbindungen¶

Verbindungskonfiguration¶

-- Supported database systems:
-- MySQL, PostgreSQL, SQLite, Oracle, SQL Server, 
-- DB2, Sybase, H2, HSQLDB, Derby, Exasol, 
-- ClickHouse, MongoDB, Cassandra, Redis, and more

-- MySQL connection example
Host: localhost
Port: 3306
Database: myapp
User: dbuser
Password: password
URL: jdbc:mysql://localhost:3306/myapp

-- PostgreSQL connection
Host: localhost
Port: 5432
Database: myapp
User: postgres
Password: password
URL: jdbc:postgresql://localhost:5432/myapp

-- SQL Server connection
Host: localhost
Port: 1433
Database: myapp
User: sa
Password: password
URL: jdbc:sqlserver://localhost:1433;databaseName=myapp

-- Oracle connection
Host: localhost
Port: 1521
SID: XE
User: hr
Password: password
URL: jdbc:oracle:thin:@localhost:1521:XE

-- SQLite connection
File: /path/to/database.sqlite
URL: jdbc:sqlite:/path/to/database.sqlite

SSL/TLS-Konfiguration¶

# MySQL SSL configuration
useSSL=true
requireSSL=true
verifyServerCertificate=true
trustCertificateKeyStoreUrl=file:///path/to/truststore.jks
trustCertificateKeyStorePassword=password
clientCertificateKeyStoreUrl=file:///path/to/keystore.jks
clientCertificateKeyStorePassword=password

# PostgreSQL SSL configuration
ssl=true
sslmode=require
sslcert=/path/to/client.crt
sslkey=/path/to/client.key
sslrootcert=/path/to/ca.crt

# SQL Server SSL configuration
encrypt=true
trustServerCertificate=false
trustStore=/path/to/truststore.jks
trustStorePassword=password

Verbindungspooling¶

# HikariCP connection pool settings (default in DataGrip)
maximumPoolSize=10
minimumIdle=5
connectionTimeout=30000
idleTimeout=600000
maxLifetime=1800000
leakDetectionThreshold=60000

# Custom connection pool configuration
# File → Settings → Database → Advanced
# Connection pool settings:
# - Initial connections: 1
# - Maximum connections: 10
# - Connection timeout: 30s
# - Idle timeout: 10m

Datenquellen-Vorlagen¶

<!-- Custom data source template -->
<!-- File → Settings → Database → Data Sources → Templates -->
<dataSource>
    <name>MySQL Production Template</name>
    <driver>mysql</driver>
    <url>jdbc:mysql://{host}:{port}/{database}</url>
    <properties>
        <property name="useSSL" value="true"/>
        <property name="requireSSL" value="true"/>
        <property name="useUnicode" value="true"/>
        <property name="characterEncoding" value="UTF-8"/>
        <property name="zeroDateTimeBehavior" value="convertToNull"/>
        <property name="autoReconnect" value="true"/>
        <property name="failOverReadOnly" value="false"/>
        <property name="maxReconnects" value="3"/>
    </properties>
</dataSource>

Abfragekonsole¶

Grundlegende Abfrageoperationen¶

-- Execute query: Ctrl+Enter (current statement) or Ctrl+Shift+Enter (all)
SELECT * FROM users WHERE created_at >= '2023-01-01';

-- Execute selection: Select text and press Ctrl+Enter
SELECT id, username, email FROM users;

-- Multiple queries (separate with semicolons)
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM products;

-- Query with parameters
SELECT * FROM users WHERE id = ?;
-- DataGrip will prompt for parameter values

-- Named parameters
SELECT * FROM users WHERE status = :status AND created_at >= :start_date;

Abfrageverlauf und Favoriten¶

-- Access query history: Ctrl+Shift+H
-- Recent queries are automatically saved

-- Save query as favorite
-- Right-click in console → Add to Favorites
-- Or use Ctrl+Shift+F

-- Organize favorites in folders
-- Database tool window → Favorites → Create folder

-- Search in query history
-- Ctrl+Shift+H → Type to search

-- Export query history
-- Database tool window → Query History → Export

Abfrageausführungspläne¶

-- View execution plan: Ctrl+Shift+E
EXPLAIN SELECT 
    u.username,
    COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- PostgreSQL execution plan with analysis
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM large_table WHERE indexed_column = 'value';

-- SQL Server execution plan
SET SHOWPLAN_ALL ON;
SELECT * FROM users WHERE email = 'user@example.com';

-- Oracle execution plan
EXPLAIN PLAN FOR
SELECT * FROM users WHERE email = 'user@example.com';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- MySQL execution plan
EXPLAIN FORMAT=JSON
SELECT * FROM users WHERE email = 'user@example.com';

Abfrageprofiling¶

-- Enable query profiling in MySQL
SET profiling = 1;

-- Execute query
SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.created_at >= '2023-01-01';

-- Show profiles
SHOW PROFILES;

-- Show detailed profile for specific query
SHOW PROFILE FOR QUERY 1;

-- Show CPU usage
SHOW PROFILE CPU FOR QUERY 1;

-- Show memory usage
SHOW PROFILE MEMORY FOR QUERY 1;

-- PostgreSQL query profiling with pg_stat_statements
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Erweiterte Abfragefunktionen¶

-- Live templates (code snippets)
-- Type abbreviation and press Tab
-- sel → SELECT * FROM table_name;
-- ins → INSERT INTO table_name (columns) VALUES (values);
-- upd → UPDATE table_name SET column = value WHERE condition;

-- Custom live templates
-- File → Settings → Editor → Live Templates → SQL

-- Query parameters with default values
SELECT * FROM users 
WHERE created_at >= COALESCE(:start_date, '2023-01-01')
  AND status = COALESCE(:status, 'active');

-- Conditional queries using DataGrip variables
-- #if ($condition)
SELECT * FROM users WHERE active = true;
-- #else
SELECT * FROM users;
-- #end

-- Query with file input
-- Use $FILE$ placeholder to read from file
SELECT * FROM users WHERE id IN ($FILE$/path/to/ids.txt$);

-- Batch operations
-- Execute query for each line in a file
-- Tools → Database → Run SQL Script

Schemanavigation¶

Datenbankexplorer¶

-- Navigate database structure
-- Database tool window (Alt+1)
-- Expand data sources to see:
-- - Schemas/Databases
-- - Tables
-- - Views
-- - Stored Procedures
-- - Functions
-- - Triggers
-- - Indexes

-- Quick navigation shortcuts:
-- Ctrl+N: Navigate to table/view
-- Ctrl+Shift+N: Navigate to any database object
-- Ctrl+Alt+Shift+N: Navigate to symbol

-- Search in database objects
-- Ctrl+Shift+F: Find in database
-- Search by name, content, or SQL

-- Filter objects
-- Use filter in Database tool window
-- Filter by name pattern or object type

Tabellen- und Schemainformationen¶

-- View table structure
-- Double-click table → DDL tab
-- Or right-click → Show DDL

-- Table information query
SELECT 
    column_name,
    data_type,
    is_nullable,
    column_default,
    character_maximum_length
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;

-- Index information
SELECT 
    index_name,
    column_name,
    non_unique,
    seq_in_index
FROM information_schema.statistics
WHERE table_name = 'users'
ORDER BY index_name, seq_in_index;

-- Foreign key relationships
SELECT 
    constraint_name,
    column_name,
    referenced_table_name,
    referenced_column_name
FROM information_schema.key_column_usage
WHERE table_name = 'orders'
  AND referenced_table_name IS NOT NULL;

-- Table size and row count
SELECT 
    table_name,
    table_rows,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY size_mb DESC;

Diagramme und Beziehungen¶

-- Generate ER diagram
-- Right-click database → Diagrams → Show Visualization
-- Or Tools → Database → ER Diagram

-- Customize diagram
-- Show/hide columns, indexes, foreign keys
-- Arrange tables manually or auto-layout
-- Export diagram as image

-- Dependency analysis
-- Right-click object → Analyze → Dependencies
-- Shows what depends on the object and what it depends on

-- Find usages
-- Right-click object → Find Usages (Alt+F7)
-- Shows where the object is referenced

Schemavergleich¶

-- Compare schemas
-- Tools → Database → Compare Schemas
-- Select source and target schemas
-- View differences in structure and data

-- Generate migration script
-- After comparison, click "Generate Script"
-- Review and execute migration

-- Synchronize schemas
-- Tools → Database → Synchronize Schemas
-- Apply changes from one schema to another

-- Export schema comparison
-- Save comparison results as HTML or text

Dateneditor¶

Datenansicht und -bearbeitung```sql¶

-- Open table data -- Double-click table → Data tab -- Or right-click table → Edit Data

-- Navigation shortcuts: -- Ctrl+Page Up/Down: Navigate between tabs -- F4: Edit cell -- Ctrl+Enter: Commit changes -- Escape: Cancel edit

-- Filtering data -- Click filter icon in column header -- Use quick filters or custom conditions -- Combine multiple filters

-- Sorting data -- Click column header to sort -- Ctrl+click for multi-column sort -- Right-click for sort options

-- Search in data -- Ctrl+F: Find in current view -- Ctrl+R: Find and replace -- Use regular expressions for advanced search

### Data Modification
```sql
-- Insert new row
-- Click "+" button or press Alt+Insert
-- Fill in values and press Ctrl+Enter to commit

-- Update existing data
-- Double-click cell or press F4
-- Edit value and press Enter
-- Commit with Ctrl+Enter

-- Delete rows
-- Select rows and press Delete
-- Confirm deletion

-- Bulk operations
-- Select multiple rows for bulk delete
-- Use clipboard for bulk insert/update
-- Import data from CSV/Excel

-- Undo/Redo changes
-- Ctrl+Z: Undo
-- Ctrl+Shift+Z: Redo
-- View pending changes before commit

Data Import/Export¶

-- Import data
-- Right-click table → Import Data from File
-- Supported formats: CSV, TSV, Excel, JSON, XML
-- Configure import mapping and options

-- Export data
-- Right-click table → Export Data to File
-- Choose format and destination
-- Configure export options

-- Copy data
-- Select rows → Ctrl+C
-- Paste in external applications
-- Various copy formats available

-- Generate INSERT statements
-- Select rows → Copy as INSERT statements
-- Useful for data migration scripts

Advanced Data Features¶

-- Aggregation view
-- Right-click column → Show Aggregation
-- View COUNT, SUM, AVG, MIN, MAX

-- Transpose view
-- Tools → Database → Transpose
-- Switch rows and columns view

-- Data comparison
-- Select two rows → Compare
-- Highlight differences between records

-- Value editor
-- F4 on large text/binary fields
-- Specialized editors for JSON, XML, images

-- Calculated columns
-- Add virtual columns with expressions
-- Useful for data analysis without modifying schema

Code Generation¶

SQL Generation¶

-- Generate SELECT statement
-- Right-click table → SQL Generator → SELECT
-- Customize columns, conditions, joins

-- Generate INSERT statement
-- Right-click table → SQL Generator → INSERT
-- Include all columns or select specific ones

-- Generate UPDATE statement
-- Right-click table → SQL Generator → UPDATE
-- Specify SET clauses and WHERE conditions

-- Generate DELETE statement
-- Right-click table → SQL Generator → DELETE
-- Add WHERE conditions for safe deletion

-- Generate DDL
-- Right-click object → SQL Scripts → DDL
-- Create table, index, or constraint definitions

Code Templates¶

-- Create custom templates
-- File → Settings → Editor → Live Templates → SQL

-- Common templates:
-- Table creation template
CREATE TABLE ${TABLE_NAME} (
    id ${ID_TYPE} PRIMARY KEY,
    ${COLUMN_NAME} ${COLUMN_TYPE},
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Stored procedure template
DELIMITER //
CREATE PROCEDURE ${PROCEDURE_NAME}(${PARAMETERS})
BEGIN
    ${BODY}
END //
DELIMITER ;

-- Function template
CREATE FUNCTION ${FUNCTION_NAME}(${PARAMETERS}) 
RETURNS ${RETURN_TYPE}
READS SQL DATA
DETERMINISTIC
BEGIN
    ${BODY}
    RETURN ${RETURN_VALUE};
END;

-- Trigger template
CREATE TRIGGER ${TRIGGER_NAME}
    ${TIMING} ${EVENT} ON ${TABLE_NAME}
    FOR EACH ROW
BEGIN
    ${BODY}
END;

Documentation Generation¶

-- Generate database documentation
-- Tools → Database → Generate Documentation
-- Include tables, columns, relationships, indexes

-- Custom documentation templates
-- Modify templates in DataGrip settings
-- Add custom sections and formatting

-- Export documentation
-- HTML format with navigation
-- Include diagrams and statistics
-- Customizable styling and layout

Migration Scripts¶

-- Generate migration scripts
-- Compare schemas and generate differences
-- Version control integration for migrations

-- Migration script template
-- Migration: Add user preferences table
-- Date: 2023-12-01
-- Author: Developer Name

-- Up migration
CREATE TABLE user_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    preference_key VARCHAR(100) NOT NULL,
    preference_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uk_user_preference (user_id, preference_key)
);

-- Down migration
DROP TABLE IF EXISTS user_preferences;

-- Data migration example
INSERT INTO user_preferences (user_id, preference_key, preference_value)
SELECT 
    id,
    'theme',
    CASE 
        WHEN profile_data->>'$.theme' IS NOT NULL 
        THEN profile_data->>'$.theme'
        ELSE 'default'
    END
FROM users
WHERE profile_data IS NOT NULL;

Version Control¶

Git Integration¶

# Initialize Git repository
git init
git add .
git commit -m "Initial database schema"

# DataGrip Git integration
# VCS → Enable Version Control Integration → Git
# VCS → Git → Add to VCS

# Commit changes
# Ctrl+K: Commit changes
# Review changes before commit
# Add commit message and commit

# View history
# VCS → Git → Show History
# See file changes over time
# Compare versions

# Branching
# VCS → Git → Branches
# Create, switch, merge branches
# Useful for feature development

Database Versioning¶

-- Schema versioning table
CREATE TABLE schema_versions (
    version VARCHAR(50) PRIMARY KEY,
    description TEXT,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    applied_by VARCHAR(100),
    checksum VARCHAR(64)
);

-- Track schema changes
INSERT INTO schema_versions (version, description, applied_by, checksum)
VALUES ('1.0.0', 'Initial schema', USER(), SHA2('schema_content', 256));

-- Migration tracking
CREATE TABLE migrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    migration_name VARCHAR(255) NOT NULL,
    batch INT NOT NULL,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Version control best practices
-- 1. Always version control schema changes
-- 2. Use descriptive commit messages
-- 3. Include rollback scripts
-- 4. Test migrations on staging first
-- 5. Keep migrations atomic and reversible

Change Tracking¶

-- Enable change tracking in DataGrip
-- File → Settings → Database → General → Track changes

-- View local changes
-- Database tool window → Local Changes
-- See modified objects and SQL

-- Revert changes
-- Right-click object → Local History → Revert
-- Or use VCS → Revert

-- Compare with repository
-- Right-click object → Compare with Repository
-- See differences between local and committed versions

-- Shelve changes
-- VCS → Shelve Changes
-- Temporarily save changes without committing

Debugging¶

SQL Debugging¶

-- Debug stored procedures (MySQL, PostgreSQL, Oracle)
-- Set breakpoints in procedure code
-- Step through execution line by line
-- Inspect variable values

-- MySQL debugging example
DELIMITER //
CREATE PROCEDURE debug_example(IN user_id INT)
BEGIN
    DECLARE user_count INT DEFAULT 0;
    DECLARE order_total DECIMAL(10,2) DEFAULT 0;

    -- Breakpoint here
    SELECT COUNT(*) INTO user_count FROM users WHERE id = user_id;

    IF user_count > 0 THEN
        -- Breakpoint here
        SELECT SUM(total_amount) INTO order_total 
        FROM orders WHERE user_id = user_id;

        SELECT user_count, order_total;
    ELSE
        SELECT 'User not found' as message;
    END IF;
END //
DELIMITER ;

-- Debug execution
-- Right-click procedure → Debug
-- Set breakpoints and start debugging

Query Performance Debugging¶

-- Analyze slow queries
-- Tools → Database → Performance Monitor
-- View real-time query performance

-- Query execution statistics
SELECT 
    sql_text,
    executions,
    avg_timer_wait/1000000000 as avg_time_seconds,
    sum_timer_wait/1000000000 as total_time_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- Index usage analysis
SELECT 
    object_schema,
    object_name,
    index_name,
    count_read,
    count_write,
    count_fetch,
    count_insert,
    count_update,
    count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'myapp'
ORDER BY count_read DESC;

-- Lock analysis
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Error Diagnosis¶

-- Common error patterns and solutions

-- 1. Deadlock detection
-- Error: Deadlock found when trying to get lock
-- Solution: Retry transaction, optimize query order

-- 2. Lock timeout
-- Error: Lock wait timeout exceeded
-- Solution: Optimize queries, reduce transaction time

-- 3. Connection issues
-- Error: Too many connections
-- Solution: Optimize connection pooling, increase max_connections

-- 4. Memory issues
-- Error: Out of memory
-- Solution: Optimize queries, increase memory limits

-- 5. Syntax errors
-- Use DataGrip's syntax highlighting and error detection
-- Real-time error highlighting in query console

-- Error log analysis
SELECT 
    logged,
    thread_id,
    prio,
    error_code,
    subsystem,
    data
FROM performance_schema.error_log
WHERE logged >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY logged DESC;

Performance Analysis¶

Query Performance Monitoring¶

-- Enable performance monitoring
-- Tools → Database → Performance Monitor
-- Real-time monitoring of database performance

-- Query execution time analysis
SELECT 
    digest_text,
    count_star as executions,
    avg_timer_wait/1000000000 as avg_seconds,
    max_timer_wait/1000000000 as max_seconds,
    sum_timer_wait/1000000000 as total_seconds,
    sum_rows_examined/count_star as avg_rows_examined,
    sum_rows_sent/count_star as avg_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text IS NOT NULL
ORDER BY sum_timer_wait DESC
LIMIT 20;

-- Index efficiency analysis
SELECT 
    table_schema,
    table_name,
    index_name,
    cardinality,
    ROUND(cardinality/table_rows*100, 2) as selectivity_percent
FROM information_schema.statistics s
JOIN information_schema.tables t ON s.table_schema = t.table_schema 
    AND s.table_name = t.table_name
WHERE s.table_schema = 'myapp'
    AND t.table_rows > 0
ORDER BY selectivity_percent DESC;

-- Buffer pool analysis (MySQL)
SELECT 
    pool_id,
    pool_size,
    free_buffers,
    database_pages,
    old_database_pages,
    modified_database_pages,
    pending_decompress,
    pending_reads,
    pending_flush_lru,
    pending_flush_list
FROM information_schema.innodb_buffer_pool_stats;

Resource Usage Analysis¶

-- Connection analysis
SELECT 
    user,
    host,
    db,
    command,
    time,
    state,
    info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

-- Table space usage
SELECT 
    table_schema,
    table_name,
    table_rows,
    data_length,
    index_length,
    data_length + index_length as total_size,
    ROUND((data_length + index_length)/1024/1024, 2) as size_mb
FROM information_schema.tables
WHERE table_schema = 'myapp'
ORDER BY total_size DESC;

-- Temporary table usage
SELECT 
    digest_text,
    count_star,
    sum_created_tmp_tables,
    sum_created_tmp_disk_tables,
    ROUND(sum_created_tmp_disk_tables/sum_created_tmp_tables*100, 2) as disk_tmp_percent
FROM performance_schema.events_statements_summary_by_digest
WHERE sum_created_tmp_tables > 0
ORDER BY sum_created_tmp_disk_tables DESC;

-- File I/O analysis
SELECT 
    file_name,
    event_name,
    count_read,
    count_write,
    sum_number_of_bytes_read,
    sum_number_of_bytes_write,
    ROUND(sum_number_of_bytes_read/1024/1024, 2) as read_mb,
    ROUND(sum_number_of_bytes_write/1024/1024, 2) as write_mb
FROM performance_schema.file_summary_by_instance
WHERE event_name LIKE 'wait/io/file/%'
ORDER BY sum_number_of_bytes_read + sum_number_of_bytes_write DESC;

Performance Optimization¶

-- Query optimization recommendations
-- 1. Use EXPLAIN to analyze query execution plans
-- 2. Add appropriate indexes for WHERE, JOIN, ORDER BY clauses
-- 3. Avoid SELECT * in production queries
-- 4. Use LIMIT for large result sets
-- 5. Optimize JOIN order and conditions

-- Index optimization
-- Create covering indexes for frequently used queries
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);

-- Partial indexes for filtered queries
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- Composite indexes for multi-column searches
CREATE INDEX idx_products_category_price ON products(category_id, price);

-- Query rewriting for better performance
-- Original slow query
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2023-01-01'
  AND u.status = 'active';

-- Optimized version
SELECT o.id, o.order_number, o.total_amount, u.username
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2023-01-01'
  AND u.status = 'active'
  AND o.status != 'cancelled'
ORDER BY o.created_at DESC
LIMIT 1000;

-- Batch processing for large operations
-- Instead of single large transaction
UPDATE large_table SET status = 'processed' WHERE status = 'pending';

-- Use batched approach
UPDATE large_table 
SET status = 'processed' 
WHERE status = 'pending' 
  AND id BETWEEN 1 AND 10000;
-- Repeat for next batch

Plugins and Extensions¶

Essential Plugins¶

# Install plugins
# File → Settings → Plugins → Marketplace

# Recommended plugins:
# 1. Database Navigator - Enhanced database browsing
# 2. SQL Query Plugin - Advanced SQL features
# 3. Rainbow Brackets - Better code readability
# 4. String Manipulation - Text processing tools
# 5. CSV Plugin - CSV file editing
# 6. JSON Parser - JSON formatting and validation
# 7. Grep Console - Enhanced console output
# 8. GitToolBox - Advanced Git integration
# 9. Key Promoter X - Learn keyboard shortcuts
# 10. Presentation Assistant - Show shortcuts during demos

Custom Plugin Development¶

// Basic plugin structure for DataGrip
// plugin.xml
<idea-plugin>
    <id>com.example.datagrip.plugin</id>
    <name>Custom DataGrip Plugin</name>
    <version>1.0</version>
    <vendor>Your Company</vendor>

    <description>Custom functionality for DataGrip</description>

    <depends>com.intellij.modules.platform</depends>
    <depends>com.intellij.database</depends>

    <extensions defaultExtensionNs="com.intellij">
        <toolWindow id="CustomTool" 
                   secondary="true" 
                   anchor="right" 
                   factoryClass="com.example.CustomToolWindowFactory"/>
    </extensions>

    <actions>
        <action id="CustomAction" 
               class="com.example.CustomAction" 
               text="Custom Action" 
               description="Performs custom database operation">
            <add-to-group group-id="DatabaseViewPopupMenu" anchor="last"/>
        </action>
    </actions>
</idea-plugin>

// Custom action implementation
public class CustomAction extends AnAction {
    @Override
    public void actionPerformed(AnActionEvent e) {
        // Get database context
        DatabaseView databaseView = DatabaseView.DATABASE_VIEW_KEY.getData(e.getDataContext());
        if (databaseView != null) {
            // Perform custom database operation
            performCustomOperation(databaseView);
        }
    }

    private void performCustomOperation(DatabaseView view) {
        // Custom logic here
        // Access database connections, execute queries, etc.
    }
}

Script Integration¶

# External tool integration
# File → Settings → Tools → External Tools

# Add custom database scripts
# Name: Backup Database
# Program: mysqldump
# Arguments: -u $USER$ -p$PASSWORD$ $DATABASE$ > backup_$DATE$.sql
# Working directory: $ProjectFileDir$

# Python script integration
# Name: Data Analysis
# Program: python
# Arguments: scripts/analyze_data.py $SelectedText$
# Working directory: $ProjectFileDir$

# Shell script for maintenance
# Name: Database Maintenance
# Program: bash
# Arguments: scripts/db_maintenance.sh $DATABASE$
# Working directory: $ProjectFileDir$

Keyboard Shortcuts¶

Essential Shortcuts¶

```bash

Ctrl+N # Navigate to table/view Ctrl+Shift+N # Navigate to any database object Ctrl+Alt+Shift+N # Navigate to symbol Alt+F1 # Select in Database view Ctrl+E # Recent files Ctrl+Shift+E # Recent locations

Query Console¶

Ctrl+Enter # Execute current statement Ctrl+Shift+Enter # Execute all statements Ctrl+Shift+F10 # Execute selection Ctrl+/ # Comment/uncomment line Ctrl+Shift+/ # Block comment Ctrl+D # Duplicate line Ctrl+Y # Delete line

Data Editor¶

F4 # Edit cell Ctrl+Enter # Commit changes Escape # Cancel edit Alt+Insert # Insert new row Delete # Delete selected rows Ctrl+F # Find in data Ctrl+R # Find and replace

Code Editing¶

Ctrl+Space # Code completion Ctrl+Shift+Space # Smart code completion Ctrl+P # Parameter info Ctrl+Q # Quick documentation Alt+Enter # Show intention actions Ctrl+Alt+L # Reformat code Ctrl+Alt+O # Optimize imports

Debugging¶

F8 # Step over F7 # Step into Shift+F8 # Step out F9 # Resume program Ctrl+F8 # Toggle breakpoint Ctrl+Shift+F8 # View breakpoints

Version Control¶

Ctrl+K # Commit changes Ctrl+T # Update project Alt+` # VCS operations popup Ctrl+Alt+Z # Rollback changes Ctrl+Shift+K # Push changes

General¶

Ctrl+Alt+S # Settings Ctrl+Shift+A # Find action Ctrl+Shift+F12 # Hide all tool windows Alt+1 # Database tool window Alt+4 # Run tool window Alt+9 # Version control tool window ### Benutzerdefinierte Tastenkombinationenbash

Create custom shortcuts¶

File → Settings → Keymap¶

Useful custom shortcuts to add:¶

Execute EXPLAIN: Ctrl+Shift+E¶

Show table DDL: Ctrl+Shift+D¶

Export data: Ctrl+Shift+X¶

Import data: Ctrl+Shift+I¶

Generate SQL: Ctrl+Shift+G¶

Compare schemas: Ctrl+Shift+C¶

Database console: Ctrl+Shift+Q¶

Refresh database: F5¶

New query console: Ctrl+Shift+N¶

Close query console: Ctrl+W¶

Macro recording¶

Edit → Macros → Start Macro Recording¶

Perform actions¶

Edit → Macros → Stop Macro Recording¶

Assign shortcut to macro¶

## Bewährte Praktikensql -- 1. Environment Setup -- Use separate databases for development, staging, production -- Configure connection profiles for each environment -- Use version control for all database changes

-- 2. Schema Design -- Follow naming conventions consistently -- Use appropriate data types and constraints -- Document tables and columns -- Plan for scalability and performance

-- 3. Query Development -- Start with simple queries and build complexity gradually -- Use EXPLAIN to analyze query performance -- Test queries with realistic data volumes -- Optimize before deploying to production

-- 4. Code Organization -- Use folders to organize queries by feature/module -- Create reusable query templates -- Document complex queries with comments -- Version control query files

-- 5. Testing Strategy -- Test schema changes on sample data -- Validate data integrity after migrations -- Performance test with production-like data -- Have rollback plans for all changes ### Workflow für Datenbankentwicklungsql -- 1. Connection Security -- Use SSL/TLS for database connections -- Implement proper authentication -- Use connection pooling appropriately -- Limit connection privileges

-- 2. Access Control -- Follow principle of least privilege -- Use role-based access control -- Regularly audit user permissions -- Remove unused accounts

-- 3. Data Protection -- Encrypt sensitive data at rest -- Use parameterized queries to prevent SQL injection -- Implement audit logging -- Regular security assessments

-- 4. Development Security -- Don't store credentials in code -- Use environment variables for configuration -- Implement proper error handling -- Regular security updates

-- Example secure connection configuration -- Use environment variables for credentials DB_HOST=${DB_HOST} DB_PORT=$ DB_NAME=${DB_NAME} DB_USER=$ DB_PASSWORD=${DB_PASSWORD} DB_SSL_MODE=require ### Sicherheits-Best-Practicessql -- 1. Query Optimization -- Use appropriate indexes -- Avoid SELECT * in production -- Use LIMIT for large result sets -- Optimize JOIN operations

-- 2. Index Strategy -- Create indexes for WHERE clauses -- Use composite indexes for multi-column searches -- Monitor index usage and remove unused indexes -- Consider partial indexes for filtered queries

-- 3. Connection Management -- Use connection pooling -- Set appropriate timeout values -- Monitor connection usage -- Close connections properly

-- 4. Monitoring and Maintenance -- Regular performance monitoring -- Analyze slow query logs -- Update table statistics -- Regular maintenance tasks

-- Example performance monitoring query SELECT table_name, table_rows, avg_row_length, data_length, index_length, ROUND((index_length/data_length)*100, 2) as index_ratio FROM information_schema.tables WHERE table_schema = DATABASE() AND table_rows > 0 ORDER BY data_length DESC; ### Performance-Best-Practicessql -- 1. Team Workflow -- Use shared connection configurations -- Standardize naming conventions -- Document database changes -- Code review for schema changes

-- 2. Version Control -- Track all schema changes -- Use meaningful commit messages -- Branch for feature development -- Tag releases appropriately

-- 3. Documentation -- Document database schema -- Maintain query documentation -- Keep migration scripts -- Document deployment procedures

-- 4. Communication -- Share query templates and snippets -- Communicate schema changes early -- Use issue tracking for database tasks -- Regular team sync on database changes

-- Example documentation template /* Query: User Order Summary Purpose: Generate summary of user orders for reporting Author: Developer Name Created: 2023-12-01 Last Modified: 2023-12-01 Dependencies: users, orders tables Performance: ~500ms for 100K users Notes: Requires index on orders(user_id, created_at) */

SELECT u.id, u.username, u.email, COUNT(o.id) as total_orders, SUM(o.total_amount) as total_spent, MAX(o.created_at) as last_order_date FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.is_active = TRUE AND u.created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR) GROUP BY u.id, u.username, u.email HAVING total_orders > 0 ORDER BY total_spent DESC LIMIT 1000; ```### Kollaborations-Best-Practices

DataGrip Cheatsheet¶

DataGrip - JetBrains Database IDE

Inhaltsverzeichnis¶

Installation und Einrichtung¶

Systemanforderungen¶

Installationsmethoden¶

Erste Konfiguration¶

Lizenzierung¶

Datenbankverbindungen¶

Verbindungskonfiguration¶

SSL/TLS-Konfiguration¶

Verbindungspooling¶

Datenquellen-Vorlagen¶

Abfragekonsole¶

Grundlegende Abfrageoperationen¶

Abfrageverlauf und Favoriten¶

Abfrageausführungspläne¶

Abfrageprofiling¶

Erweiterte Abfragefunktionen¶

Schemanavigation¶

Datenbankexplorer¶

Tabellen- und Schemainformationen¶

Diagramme und Beziehungen¶

Schemavergleich¶

Dateneditor¶

Datenansicht und -bearbeitung```sql¶

Data Import/Export¶

Advanced Data Features¶

Code Generation¶

SQL Generation¶

Code Templates¶

Documentation Generation¶

Migration Scripts¶

Version Control¶

Git Integration¶

Database Versioning¶

Change Tracking¶

Debugging¶

SQL Debugging¶

Query Performance Debugging¶

Error Diagnosis¶

Performance Analysis¶

Query Performance Monitoring¶

Resource Usage Analysis¶

Performance Optimization¶

Plugins and Extensions¶

Essential Plugins¶

Custom Plugin Development¶

Script Integration¶

Keyboard Shortcuts¶

Essential Shortcuts¶

Navigation¶

Query Console¶

Data Editor¶

Code Editing¶

Debugging¶

Version Control¶

General¶

Create custom shortcuts¶

File → Settings → Keymap¶

Useful custom shortcuts to add:¶

Execute EXPLAIN: Ctrl+Shift+E¶

Show table DDL: Ctrl+Shift+D¶

Export data: Ctrl+Shift+X¶

Import data: Ctrl+Shift+I¶

Generate SQL: Ctrl+Shift+G¶

Compare schemas: Ctrl+Shift+C¶

Database console: Ctrl+Shift+Q¶

Refresh database: F5¶

New query console: Ctrl+Shift+N¶

Close query console: Ctrl+W¶

Macro recording¶

Edit → Macros → Start Macro Recording¶

Perform actions¶

Edit → Macros → Stop Macro Recording¶

Assign shortcut to macro¶