DataGrip 치트시트

DataGrip - JetBrains Database IDE

DataGrip은 JetBrains에서 제공하는 강력한 데이터베이스 IDE로, 지능형 쿼리 콘솔, 효율적인 스키마 탐색, 포괄적인 데이터베이스 개발 도구를 제공합니다. 여러 데이터베이스 시스템을 지원하며 데이터베이스 전문가를 위한 고급 기능을 제공합니다.

(No text to translate)

설치 및 설정

시스템 요구 사항

# 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/
```(No text provided)

### 설치 방법
```bash
# 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
```(No text provided)

### 초기 구성
```bash
# 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)
```(No text provided)

### 라이선스 구성
```bash
# 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
```(No text provided)

## 데이터베이스 연결

### 연결 구성
```sql
-- 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
```(No text provided)

### SSL/TLS 구성
```properties
# 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
```(No text provided)

### 연결 풀링
```properties
# 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
```(No text provided)

### 데이터 소스 템플릿
```xml
<!-- 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>
```(No text provided)

## 쿼리 콘솔

### 기본 쿼리 작업
```sql
-- 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;
```(No text provided)

### 쿼리 히스토리 및 즐겨찾기
```sql
-- 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
```(No text provided)

### 쿼리 실행 계획
```sql
-- 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';
```(No text provided)

### 쿼리 프로파일링
```sql
-- 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;
```(No text provided)

### 고급 쿼리 기능
```sql
-- 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
```(No text provided)

## 스키마 탐색

### 데이터베이스 탐색기
```sql
-- 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
```(No text provided)

### 테이블 및 스키마 정보
```sql
-- 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;
```(No text provided)

### 다이어그램 및 관계
```sql
-- 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
```(No text provided)

### 스키마 비교
```sql
-- 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
```(No text provided)

## 데이터 편집기

### 데이터 보기 및 편집

Note: For sections 4-20, no text was provided in the original input, so I've left them as is with a note.```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

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

# 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
```### 사용자 정의 단축키
```bash
# 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
```## 모범 사례

### 데이터베이스 개발 워크플로우
```sql
-- 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
```### 보안 모범 사례
```sql
-- 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
```### 성능 모범 사례
```sql
-- 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;
```### 협업 모범 사례
```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;
```---

## 요약

DataGrip은 데이터베이스 개발, 관리 및 분석을 위한 포괄적인 데이터베이스 IDE입니다. 이 치트시트는 효과적인 데이터베이스 작업을 위한 필수 기능과 모범 사례를 다룹니다.

**주요 강점:**
- **다중 데이터베이스 지원**: 모든 주요 데이터베이스 시스템과 호환
- **지능형 코드 지원**: 고급 SQL 자동완성 및 분석
- **강력한 쿼리 콘솔**: 쿼리 실행, 분석 및 최적화
- **스키마 탐색**: 포괄적인 데이터베이스 탐색 도구
- **버전 관리 통합**: 데이터베이스 변경 추적 및 관리
- **성능 분석**: 내장된 모니터링 및 최적화 도구

**최적의 사용 사례:**
- 데이터베이스 개발 및 관리
- 쿼리 개발 및 최적화
- 스키마 설계 및 마이그레이션
- 성능 분석 및 튜닝
- 데이터베이스 프로젝트 팀 협업
- 데이터 분석 및 보고

**중요 고려사항:**
- 상업적 사용을 위해 JetBrains 라이선스 필요
- 리소스 집약적 애플리케이션
- 고급 기능에 대한 학습 곡선
- 정기적인 업데이트 권장
- 최적의 성능을 위한 적절한 구성 필요

이 치트시트에 설명된 사례와 기술을 따르면 DataGrip을 효과적으로 사용하여 데이터베이스를 관리하고, 효율적인 쿼리를 개발하며, 팀과 효과적으로 협업하면서 고품질 데이터베이스 애플리케이션을 유지할 수 있습니다.

<script>
function copyToClipboard() {
    const commands = document.querySelectorAll('code');
    let allCommands = '';
    commands.forEach(cmd => allCommands += cmd.textContent + '\n');
    navigator.clipboard.writeText(allCommands);
    alert('모든 명령어가 클립보드에 복사되었습니다!');
}

function generatePDF() {
    window.print();
}
</script>

DataGrip 치트시트

DataGrip - JetBrains Database IDE

목차

설치 및 설정

시스템 요구 사항

Data Modification

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