Đã đăng vào khoảng 21 giờ trước 6 phút đọc

Query Processing và SQL_ID trong MySQL Database

📋 Mục Lục

Tổng Quan về Query Processing
6 Bước Xử Lý Query
Shared Pool và Cơ Chế So Sánh
SQL_ID (DIGEST) - Khái Niệm Quan Trọng
Prepared Statements và Bind Variables
Vấn Đề Thực Tế với TypeORM + NestJS
Monitoring và Optimization
Best Practices và Khuyến Nghị

1. Tổng Quan về Query Processing

Khi một câu SQL query được gửi đến MySQL database, hệ thống sẽ thực hiện một quy trình xử lý phức tạp để đảm bảo câu lệnh được thực thi đúng và hiệu quả.

Luồng Xử Lý Cơ Bản:

Client Request → Parse → Validate → Shared Pool Check → Execute → Return Result

2. 6 Bước Xử Lý Query

Bước 1: Parse (Phân Tích Cú Pháp)

Mục đích: Kiểm tra tính đúng đắn về mặt cú pháp SQL

Ví dụ:

-- ✅ ĐÚNG
SELECT * FROM employee WHERE emp_id = 100;

-- ❌ SAI
SELCT * FROM employee WHERE emp_id = 100;  -- Lỗi: ORA-00923

Bước 2: Validate (Kiểm Tra Ngữ Nghĩa)

Mục đích: Xác minh sự tồn tại của table, column, permissions
Kiểm tra:
- Bảng employee có tồn tại không?
- Cột emp_id có trong bảng không?
- User có quyền truy cập không?
Lỗi phổ biến: ORA-00942: table or view does not exist

Bước 3: Shared Pool Check

Mục đích: Tìm kiếm execution plan đã có sẵn
Kết quả:
- FOUND → Soft Parse (nhanh)
- NOT FOUND → Hard Parse (chậm)

Bước 4: Hard Parse (Nếu Cần)

Mục đích: Tạo execution plan tối ưu
Quy trình:
- Phân tích các chiến lược thực thi
- Chọn plan tối ưu nhất
- Tính toán cost và statistics

Bước 5: Store Plan

Mục đích: Lưu execution plan vào Shared Pool
Lợi ích: Tái sử dụng cho các lần thực thi sau

Bước 6: Execute

Mục đích: Thực thi câu lệnh và trả về kết quả
Hoạt động: Truy xuất dữ liệu từ storage theo plan

3. Shared Pool và Cơ Chế So Sánh

Cách Thức So Sánh Trong Shared Pool:

A) Text Comparison (Nghiêm Ngặt 100%)

-- ❌ Những câu này được coi là KHÁC NHAU:
SELECT * FROM employee WHERE emp_id = 100
Select * from employee where emp_id = 100    -- Khác case
SELECT * FROM EMPLOYEE WHERE EMP_ID = 100    -- Khác case
select * from employee where emp_id=100      -- Thiếu space

B) Hash Value Generation

Mỗi câu SQL tạo ra một hash value duy nhất
Hash được tính dựa trên:
- Từng ký tự trong câu SQL
- Vị trí của ký tự
- Case sensitive
- Khoảng trắng (spaces, tabs)

C) Các Yếu Tố Khác

Parsing User ID: Cùng user thực thi
Optimizer Environment: Cùng settings
Database Schema: Cùng schema context

4. SQL_ID (DIGEST) - Khái Niệm Quan Trọng

Định Nghĩa:

SQL_ID (trong MySQL gọi là DIGEST) là một hash identifier duy nhất cho mỗi câu SQL.

Ví Dụ Thực Tế:

-- Ba câu lệnh này có 3 SQL_ID KHÁC NHAU:
SELECT * FROM employee WHERE emp_id = 100  -- SQL_ID: ABC123...
SELECT * FROM employee WHERE emp_id = 200  -- SQL_ID: DEF456...  
SELECT * FROM employee WHERE emp_id = 300  -- SQL_ID: GHI789...

-- Nhưng có cùng DIGEST_TEXT (normalized):
-- DIGEST_TEXT: "SELECT * FROM employee WHERE emp_id = ?"

Kiểm Tra SQL_ID trong MySQL:

-- Xem SQL_ID của các statements
SELECT 
    LEFT(DIGEST, 16) as SQL_ID_Short,
    DIGEST_TEXT as Normalized_SQL,
    COUNT_STAR as Execution_Count,
    SUM_TIMER_WAIT/1000000000 as Total_Time_Sec
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%employee%'
ORDER BY COUNT_STAR DESC;

5. Prepared Statements và Bind Variables

Vấn Đề Cơ Bản:

-- ❌ Cách Cũ (Inefficient): Mỗi câu = 1 Hard Parse
SELECT * FROM employee WHERE emp_id = 100;  -- Hard Parse #1
SELECT * FROM employee WHERE emp_id = 200;  -- Hard Parse #2
SELECT * FROM employee WHERE emp_id = 300;  -- Hard Parse #3

Giải Pháp Prepared Statements:

-- ✅ Cách Mới (Efficient): 1 Hard Parse + nhiều Soft Parse
PREPARE stmt FROM 'SELECT * FROM employee WHERE emp_id = ?';

-- Lần đầu: Hard Parse
EXECUTE stmt USING 100;  -- Hard Parse
-- Các lần sau: Soft Parse  
EXECUTE stmt USING 200;  -- Soft Parse
EXECUTE stmt USING 300;  -- Soft Parse

Lifecycle của Prepared Statement:

Phase 1: PREPARE (Chỉ 1 lần)

Parse → Kiểm tra cú pháp
Validate → Kiểm tra table/column
Optimize → Tạo execution plan
Store → Lưu plan vào memory

Phase 2: EXECUTE (Nhiều lần)

Bind values → Gán giá trị cho placeholder
Execute → Chạy với plan có sẵn

Implementation Trong Các Ngôn Ngữ:

Java/JDBC:

// ❌ BAD - String Concatenation
String sql = "SELECT * FROM users WHERE id = " + userId;
Statement stmt = conn.createStatement();

// ✅ GOOD - Prepared Statement  
String sql = "SELECT * FROM users WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, userId);

Python:

# ❌ BAD
cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")

# ✅ GOOD
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

Node.js:

// ❌ BAD
const sql = `SELECT * FROM users WHERE id = ${userId}`;

// ✅ GOOD
const sql = 'SELECT * FROM users WHERE id = ?';
db.query(sql, [userId], callback);

6. Vấn Đề Thực Tế với TypeORM + NestJS

Tại Sao TypeORM Ra Đời?

TypeORM được thiết kế với mục tiêu đơn giản hóa việc làm việc với database, giải quyết nhiều vấn đề phổ biến trong development:

A) Abstraction Layer - Tầng Trừu Tượng

// ❌ Raw SQL - Phức tạp, dễ lỗi
const getUserWithPosts = async (userId: number) => {
  const userQuery = `
    SELECT u.id, u.name, u.email, u.created_at,
           p.id as post_id, p.title, p.content, p.published_at
    FROM users u 
    LEFT JOIN posts p ON u.id = p.user_id 
    WHERE u.id = ? AND u.active = 1
    ORDER BY p.published_at DESC
  `;
  return db.query(userQuery, [userId]);
}

// ✅ TypeORM - Đơn giản, trực quan
const getUserWithPosts = async (userId: number) => {
  return this.userRepository.findOne({
    where: { id: userId, active: true },
    relations: ['posts'],
    order: { posts: { publishedAt: 'DESC' } }
  });
}

B) Database Agnostic - Độc Lập Database

// Cùng 1 code TypeORM chạy được trên:
// - MySQL: SELECT * FROM users WHERE id = ?
// - PostgreSQL: SELECT * FROM users WHERE id = $1  
// - SQLite: SELECT * FROM users WHERE id = ?
// - MongoDB: db.users.findOne({ _id: ObjectId(...) })

const user = await this.userRepository.findOne({
  where: { id: userId }
});

C) Type Safety - An Toàn Kiểu Dữ Liệu

// ❌ Raw SQL - Không có type checking
const result = await db.query('SELECT * FROM users WHERE id = ?', [userId]);
// result: any[] - Không biết structure

// ✅ TypeORM - Full TypeScript support
const user: User = await this.userRepository.findOne({
  where: { id: userId }
});
// user.name ✓ - IntelliSense support
// user.invalidField ❌ - Compile error

D) Reduced Learning Curve - Giảm Đường Cong Học Tập

// Thay vì học SQL syntax cho từng database:
// MySQL: LIMIT 10
// PostgreSQL: LIMIT 10  
// SQL Server: TOP 10
// Oracle: ROWNUM <= 10

// Chỉ cần học TypeORM syntax:
const users = await this.userRepository.find({
  take: 10  // Works everywhere
});

E) Migration & Schema Management

// Tự động generate migrations
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;
  
  @Column({ length: 100 })
  name: string;
  
  @CreateDateColumn()
  createdAt: Date;
}

// TypeORM tự động tạo:
// CREATE TABLE users (
//   id INT PRIMARY KEY AUTO_INCREMENT,
//   name VARCHAR(100),
//   createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
// );

Trade-offs của TypeORM:

✅ Advantages:

Developer Productivity - Code nhanh hơn 3-5x
Cross-platform - Một codebase, nhiều database
Type Safety - Catch errors at compile time
Easy Maintenance - Ít boilerplate code
Team Scalability - Junior devs có thể contribute ngay

⚠️ Disadvantages:

Performance Overhead - Abstraction layer có cost
Complex Query Limitations - Khó optimize advanced queries
Generated SQL - Không control được 100%
Learning Curve - Phải học ORM patterns
Debug Complexity - Khó debug khi có performance issues

Scenario Điển Hình:

// NestJS Service
@Injectable()
export class UserService {
  async getUserById(userId: number) {
    return this.userRepository.findOne({
      where: { id: userId }
    });
  }
}

// Controller  
@Get('user/:id')
async getUser(@Param('id') id: number) {
  return this.userService.getUserById(id);
}

SQL Generated:

-- Request 1: GET /user/100
SELECT * FROM users WHERE id = 100;  -- SQL_ID: ABC123...

-- Request 2: GET /user/200
SELECT * FROM users WHERE id = 200;  -- SQL_ID: DEF456... (KHÁC!)

-- Request 3: GET /user/300  
SELECT * FROM users WHERE id = 300;  -- SQL_ID: GHI789... (KHÁC!)

Kết Quả: Mỗi Request = 6 Bước Đầy Đủ!

Request /user/100:
1. Parse syntax ✓
2. Validate semantic ✓  
3. Check Shared Pool → NOT FOUND
4. HARD PARSE → Create execution plan
5. Execute plan
6. Return result

Request /user/200:  
1. Parse syntax ✓
2. Validate semantic ✓
3. Check Shared Pool → NOT FOUND (SQL_ID khác)
4. HARD PARSE → Create execution plan MỚI
5. Execute plan
6. Return result

Giải Pháp trong TypeORM:

1. Enable Prepared Statements:

// app.module.ts
TypeOrmModule.forRoot({
  type: 'mysql',
  // ...other config
  extra: {
    prepareStatement: true,
    useServerPrepStmts: true
  }
})

2. Sử dụng QueryBuilder:

async getUserById(userId: number) {
  return this.userRepository
    .createQueryBuilder('user')
    .where('user.id = :id', { id: userId })  // Tự động dùng parameters
    .getOne();
}

3. Raw Query với Parameters:

async getUserById(userId: number) {
  return this.userRepository.query(
    'SELECT * FROM users WHERE id = ?', 
    [userId]
  );
}

Khi Nào Nên Dùng TypeORM vs Raw SQL?

✅ Dùng TypeORM Khi:

Rapid Development: Cần ship feature nhanh
Team có nhiều Junior: Không cần deep SQL knowledge
Multi-database Support: App cần support nhiều DB
CRUD Operations: Các operations đơn giản
Maintainability: Code dễ đọc, dễ maintain

// Perfect for TypeORM
const createUser = async (userData: CreateUserDto) => {
  const user = this.userRepository.create(userData);
  return this.userRepository.save(user);
}

const getUserPosts = async (userId: number) => {
  return this.userRepository.findOne({
    where: { id: userId },
    relations: ['posts', 'profile']
  });
}

⚠️ Dùng Raw SQL Khi:

High Performance: Cần optimize tối đa
Complex Analytics: Reporting, aggregations phức tạp
Database-specific Features: Stored procedures, functions
Full Control: Cần control 100% generated SQL

// Better with Raw SQL
const getMonthlyReport = async (year: number, month: number) => {
  return this.userRepository.query(`
    SELECT 
      DATE(created_at) as date,
      COUNT(*) as user_count,
      COUNT(CASE WHEN premium = 1 THEN 1 END) as premium_count,
      AVG(DATEDIFF(NOW(), created_at)) as avg_age_days,
      PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY login_count) as median_logins
    FROM users 
    WHERE YEAR(created_at) = ? AND MONTH(created_at) = ?
    GROUP BY DATE(created_at)
    ORDER BY date
  `, [year, month]);
}

Hybrid Approach - Cách Tiếp Cận Kết Hợp:

@Injectable()
export class UserService {
  
  // Simple operations: TypeORM
  async createUser(data: CreateUserDto): Promise<User> {
    return this.userRepository.save(data);
  }
  
  async getUserById(id: number): Promise<User> {
    return this.userRepository.findOne({
      where: { id },
      relations: ['profile']
    });
  }
  
  // Complex operations: Raw SQL với Prepared Statements
  async getUserAnalytics(userId: number): Promise<UserAnalytics> {
    return this.userRepository.query(`
      SELECT 
        u.id,
        u.name,
        COUNT(p.id) as post_count,
        AVG(p.views) as avg_views,
        MAX(p.created_at) as last_post_date,
        (SELECT COUNT(*) FROM user_activities ua WHERE ua.user_id = u.id) as activity_count
      FROM users u
      LEFT JOIN posts p ON u.id = p.user_id  
      WHERE u.id = ?
      GROUP BY u.id, u.name
    `, [userId]);
  }
  
  // Batch operations: Raw SQL for performance
  async bulkUpdateUserStatus(userIds: number[], status: string): Promise<void> {
    await this.userRepository.query(
      `UPDATE users SET status = ? WHERE id IN (${userIds.map(() => '?').join(',')})`,
      [status, ...userIds]
    );
  }
}

---

## 7. Monitoring và Optimization

### Kiểm Tra Prepared Statements Status:
```sql
-- Xem tổng quan
SHOW STATUS LIKE 'Com_stmt%';

-- Kết quả mong đợi:
-- Com_stmt_execute / Com_stmt_prepare > 5 (tỷ lệ tái sử dụng tốt)

Detailed Monitoring Query:

SELECT 
    LEFT(DIGEST, 16) as SQL_ID,
    DIGEST_TEXT as Query_Pattern,
    COUNT_STAR as Executions,
    SUM_TIMER_WAIT/1000000000 as Total_Time_Sec,
    AVG_TIMER_WAIT/1000000000 as Avg_Time_Sec
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT NOT LIKE 'SELECT%performance_schema%'
ORDER BY COUNT_STAR DESC
LIMIT 20;

8. Best Practices và Khuyến Nghị

✅ DO's:

Luôn sử dụng Prepared Statements cho dynamic queries
Reuse Prepared Statements khi có thể
Enable connection pooling để tối ưu performance
Monitor SQL_ID patterns để identify optimization opportunities
Use QueryBuilder trong ORM thay vì string concatenation

❌ DON'Ts:

Không concatenate user input vào SQL string
Không tạo mới PreparedStatement cho mỗi execution
Không ignore Performance Schema monitoring
Không bind quá nhiều parameters (>1000)

Security Benefits:

-- ❌ VULNERABLE to SQL Injection
SELECT * FROM users WHERE username = '" + userInput + "'
-- userInput = "admin'; DROP TABLE users; --"

-- ✅ SAFE with Prepared Statements
SELECT * FROM users WHERE username = ?
-- userInput được escape tự động

TypeORM/NestJS Specific Recommendations:

1. Configuration:

{
  type: 'mysql',
  extra: {
    prepareStatement: true,
    connectionLimit: 10,
    acquireTimeout: 60000,
    timeout: 60000
  },
  logging: ['query', 'error'], // For monitoring
}

2. Query Patterns:

// ✅ GOOD - Automatic parameterization
findOne({ where: { id: userId } })

// ✅ GOOD - Manual parameterization  
createQueryBuilder().where('id = :id', { id: userId })

// ❌ BAD - String concatenation
query(`SELECT * FROM users WHERE id = ${userId}`)

🎯 Tổng Kết

Những Điểm Quan Trọng:

Mỗi câu SQL với literal values khác nhau → SQL_ID khác nhau → 6 bước xử lý đầy đủ
Shared Pool so sánh nghiêm ngặt 100% → Một ký tự khác biệt = Hard Parse mới
Prepared Statements giải quyết vấn đề → 1 Hard Parse + nhiều Soft Parse
TypeORM/NestJS có thể tối ưu → Enable prepared statements + đúng query patterns
Performance impact rất lớn → 5-10x faster với prepared statements
Security bonus → Tự động prevent SQL injection

Key Takeaway:

"Trong production environment với high traffic, việc không sử dụng Prepared Statements có thể dẫn đến performance bottleneck nghiêm trọng. Mỗi user ID khác nhau = một Hard Parse mới = tài nguyên server bị lãng phí không cần thiết."

TypeORM Philosophy vs Performance Reality:

🎯 TypeORM's Mission:

"Write once, run anywhere" - Database abstraction
"Developer happiness" - Reduced complexity
"Type safety" - Catch errors early
"Rapid development" - Focus on business logic

⚖️ Performance Trade-off:

Convenience ↔️ Performance
Abstraction ↔️ Control
Productivity ↔️ Optimization
Learning curve ↔️ Deep knowledge

💡 Optimal Strategy:

// 80% of cases: TypeORM for productivity
const simpleOperations = () => {
  return this.repository.find({ where: { active: true } });
}

// 20% of cases: Raw SQL for performance  
const complexOperations = () => {
  return this.repository.query(
    'SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ...',
    [params]
  );
}

📚 Tài Liệu Tham Khảo

nestjs backend