DataGrip Cheatsheet

1 contactoDataGrip - JetBrains Base de datos IDE seleccionada/h1 "Clase de inscripción" DataGrip es una poderosa base de datos IDE de JetBrains que proporciona consola de consulta inteligente, navegación eficiente del esquema y herramientas integrales de desarrollo de bases de datos. Soporta múltiples sistemas de bases de datos y ofrece funciones avanzadas para profesionales de bases de datos. ▪/p] ■/div titulada

########################################################################################################################################################################################################################################################## Copiar todos los comandos

########################################################################################################################################################################################################################################################## Generar PDF seleccionado/button

■/div titulada ■/div titulada

Cuadro de contenidos

• Instalación y configuración
• Conexiones de base de datos
• Consola de preguntas
• Schema Navigation
• Data Editor
• Code Generation
• Version Control
• Debugging
• Análisis de la ejecución
• Plugins and Extensions
• Keyboard Shortcuts
• Las mejores prácticas

Instalación y configuración

Requisitos del sistema

# 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/

Métodos de instalación

# 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

Configuración inicial

# 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)

Configuración de licencia

# 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

Conexiones de bases de datos

Configuración de conexión

-- 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 Configuración

# 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

Conexión Piscina

# 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

Plantillas de fuentes de datos

<!-- 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>

Query Console

Operaciones básicas de consulta

-- 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;

Historia de la consulta y favoritos

-- 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

Planes de ejecución de consultas

-- 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';

Query Profiling

-- 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;

Características de la consulta avanzada

-- 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

Navegación por esquemas

Database Explorer

-- 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

Table and Schema Information

-- 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;

Diagramas y relaciones

-- 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

Comparación de esquemas

-- 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

Data Editor

Visualización y edición de datos

-- 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

Modificación de datos

-- 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

Importación de datos/Exportación

-- 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

Características avanzadas de datos

-- 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

Plantillas de código

-- 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;

Documentación Generación

-- 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

Scripts de migración

-- 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;

Control de versiones

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

Versión de la base de datos

-- 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

Seguimiento de cambios

-- 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;

Diagnóstico de error

-- 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;

Análisis de la ejecución

Supervisión del desempeño de las consultas

-- 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;

Análisis del uso de recursos

-- 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;

Optimización del rendimiento

-- 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 y Extensiones

Plugins esenciales

# 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

Desarrollo de Plugin personalizado

// 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$

Atajos de teclado

Atajos esenciales

# Navigation
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

Atajos personalizados

# 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

Buenas prácticas

Base de datos

-- 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

Prácticas óptimas de seguridad

-- 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_PORT}
DB_NAME=${DB_NAME}
DB_USER=${DB_USER}
DB_PASSWORD=${DB_PASSWORD}
DB_SSL_MODE=require

Prácticas óptimas de rendimiento

-- 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;

Mejores prácticas de colaboración

```sql -- 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; ```_

-...

Resumen

DataGrip es un IDE de base de datos completo que proporciona herramientas poderosas para el desarrollo, administración y análisis de bases de datos. Esta hoja de cálculo cubre las características esenciales y las mejores prácticas para un trabajo eficaz de bases de datos.

Key Strengths - Multi-Database Support: Funciona con todos los principales sistemas de bases de datos - ** Asistencia al código inteligente: terminación y análisis avanzados de SQL - #Powerful Consola de consultas: Ejecutar, analizar y optimizar las consultas - Schema Navigation: Herramientas completas de exploración de bases de datos - ** Integración de Control de Versión: Seguimiento y gestión de los cambios de bases de datos - ** Análisis de la actuación: Herramientas de monitoreo y optimización incorporadas

Mejores casos de uso: - Elaboración y administración de bases de datos - Desarrollo de consultas y optimización - Diseño y migración de esquemas - Análisis de rendimiento y ajuste - Colaboración en equipo en proyectos de base de datos - Análisis y presentación de datos

** Consideraciones importantes:** - Requiere licencia JetBrains para uso comercial - Aplicación intensiva de los recursos - Curva de aprendizaje para características avanzadas - Actualizaciones periódicas recomendadas - Configuración adecuada necesaria para un rendimiento óptimo

Al seguir las prácticas y técnicas descritas en esta hoja de trampa, puede utilizar DataGrip para gestionar bases de datos, desarrollar consultas eficientes y mantener aplicaciones de base de datos de alta calidad mientras colabora eficazmente con su equipo.

" copia de la funciónToClipboard() {} comandos const = document.querySelectorAll('code'); que todos losCommands = '; comandos. paraCada(cmd = confianza allCommands += cmd.textContent + '\n'); navigator.clipboard.writeText(allCommands); alerta ('Todos los comandos copiados a portapapeles!'); }

función generaPDF() { ventana.print(); } ■/script título