تصحيح قاعدة البيانات

تقنيات تصحيح قاعدة البيانات

الطرق والأدوات لتصحيح استعلامات SQL ومشاكل قاعدة البيانات في تطبيقات XOOPS.

---

مخطط تدفق التشخيص

flowchart TD
    A[Database Problem] --> B{Query Runs?}
    B -->|No| C[Check Syntax]
    B -->|Yes| D{Correct Results?}

    C --> C1[Run in MySQL CLI]
    C1 --> C2[Check EXPLAIN plan]
    C2 --> C3[Fix SQL]

    D -->|No| E[Check Logic]
    D -->|Yes| F{Performance OK?}

    E --> E1[Log result set]
    E1 --> E2[Verify data]
    E2 --> E3[Fix query]

    F -->|Slow| G[Optimize]
    F -->|Fast| H[Debugging Complete]

    G --> G1[Add indexes]
    G1 --> G2[Refactor query]
    G2 --> G3[Retest]

---

تفعيل تسجيل الاستعلام

الطريقة 1: وضع تصحيح XOOPS

<?php
// In mainfile.php
define('XOOPS_DEBUG_LEVEL', 2);

// Now all queries appear in xoops_log table
// Or in files: xoops_data/logs/
?>

عرض النتائج:

# View logs
tail -100 xoops_data/logs/*.log

# Or query database
SELECT * FROM xoops_log ORDER BY created DESC LIMIT 20;

---

الطريقة 2: سجل استعلامات MySQL البطيء

فعّل في /etc/mysql/my.cnf:

[mysqld]
# Enable slow query logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1          # Log queries > 1 second
log_queries_not_using_indexes = 1

أعد تشغيل MySQL:

sudo systemctl restart mysql
# or
sudo systemctl restart mariadb

عرض السجل:

tail -100 /var/log/mysql/slow.log

# Or analyze with mysqldumpslow
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

---

الطريقة 3: سجل الاستعلام العام

فعّل لجميع الاستعلامات (حذر: ملفات السجل كبيرة):

-- Enable
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'FILE';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- Disable
SET GLOBAL general_log = 'OFF';

-- View
SHOW VARIABLES LIKE 'general_log%';

---

تصحيح SQL في الكود

تسجيل تنفيذ الاستعلام

<?php
require_once 'mainfile.php';

$ray = ray();  // If using Ray debugger

// Execute query
$query = "SELECT u.uid, u.uname, COUNT(a.id) as total_articles
          FROM xoops_users u
          LEFT JOIN xoops_articles a ON u.uid = a.author_id
          GROUP BY u.uid
          ORDER BY total_articles DESC";

$ray->label('Query')->info($query);

$result = $GLOBALS['xoopsDB']->query($query);

if (!$result) {
    $ray->error("SQL Error: " . $GLOBALS['xoopsDB']->error);
    exit;
}

// Log results
$data = [];
while ($row = $result->fetch_assoc()) {
    $data[] = $row;
}

$ray->label('Results')->dump($data);
$ray->info("Found " . count($data) . " rows");
?>

---

قياس أداء الاستعلام

<?php
$db = $GLOBALS['xoopsDB'];
$ray = ray();

// Measure execution time
$start = microtime(true);

$query = "SELECT * FROM xoops_articles LIMIT 1000";
$result = $db->query($query);

$exec_time = (microtime(true) - $start) * 1000;  // milliseconds

$ray->info("Query executed in: {$exec_time}ms");

// Log slow queries
if ($exec_time > 100) {  // Alert if > 100ms
    $ray->warning("Slow query detected: {$exec_time}ms");
    $ray->info($query);
}
?>

---

التحقق من نتائج الاستعلام

<?php
$db = $GLOBALS['xoopsDB'];
$ray = ray();

$query = "SELECT * FROM xoops_articles WHERE author_id = 5";
$result = $db->query($query);

// Check if query succeeded
if (!$result) {
    $ray->error("Query failed: " . $db->error);
    exit;
}

// Get row count
$count = $result->num_rows;
$ray->info("Query returned: $count rows");

// Fetch results
$articles = [];
while ($row = $result->fetch_assoc()) {
    $articles[] = $row;
}

// Verify data
if (empty($articles)) {
    $ray->warning("No articles found for author 5");
} else {
    $ray->success("Found " . count($articles) . " articles");
    $ray->dump($articles);
}
?>

---

تحليل أداء الاستعلام

أمر EXPLAIN

استخدم EXPLAIN لتحليل تنفيذ الاستعلام:

-- Analyze a query
EXPLAIN SELECT * FROM xoops_articles WHERE author_id = 5;

-- With extended information
EXPLAIN EXTENDED SELECT * FROM xoops_articles WHERE author_id = 5;

-- JSON format (shows more details)
EXPLAIN FORMAT=JSON SELECT * FROM xoops_articles WHERE author_id = 5\G

الحقول الرئيسية للفحص:

type: ALL           (bad) - Full table scan
      INDEX         (ok) - Index scan
      ref/const     (good) - Direct index lookup
      range         (ok) - Range scan using index

possible_keys:      Indexes available
key:                Index actually used
key_len:            Length of index used
rows:               Estimated rows examined
Extra:              Additional info (Using where, Using index, etc.)

مثال على التحليل

-- Slow query without index
EXPLAIN SELECT * FROM xoops_articles WHERE author_id = 5;

+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | articles | ALL  | NULL          | NULL | NULL    | 1000 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
                                      ↑
                          No index available!

-- After adding index
ALTER TABLE xoops_articles ADD INDEX (author_id);

EXPLAIN SELECT * FROM xoops_articles WHERE author_id = 5;

+----+-------------+----------+------+---------------+-----------+---------+-------+------+
| id | select_type | table    | type | possible_keys | key       | key_len | rows  | Extra|
+----+-------------+----------+------+---------------+-----------+---------+-------+------+
|  1 | SIMPLE      | articles | ref  | author_id     | author_id | 4       | 10    |
+----+-------------+----------+------+---------------+-----------+---------+-------+------+
                                                              ↑
                                      Using index - much faster!

---

مشاكل SQL الشائعة

1. مشكلة N+1 الاستعلام

المشكلة:

<?php
// WRONG: Multiple queries in loop
$authors = $db->query("SELECT uid FROM xoops_users LIMIT 100");
while ($author = $authors->fetch_assoc()) {
    // This executes 100 times!
    $articles = $db->query(
        "SELECT COUNT(*) FROM xoops_articles WHERE author_id = " . $author['uid']
    );
    echo $articles->fetch_row()[0];
}
?>

الحل: استخدم JOIN

<?php
// CORRECT: One query
$result = $db->query("
    SELECT u.uid, u.uname, COUNT(a.id) as total
    FROM xoops_users u
    LEFT JOIN xoops_articles a ON u.uid = a.author_id
    GROUP BY u.uid
    LIMIT 100
");

while ($row = $result->fetch_assoc()) {
    echo $row['total'];
}
?>

---

2. الفهارس المفقودة

التعرف:

-- Find queries that scan all rows
SELECT * FROM xoops_log
WHERE info LIKE '%type: ALL%'
ORDER BY created DESC;

إضافة فهارس:

-- Single column index
ALTER TABLE xoops_articles ADD INDEX (author_id);
ALTER TABLE xoops_articles ADD INDEX (created);

-- Composite index
ALTER TABLE xoops_articles ADD INDEX (author_id, created);

-- Unique index
ALTER TABLE xoops_articles ADD UNIQUE INDEX (slug);

---

3. شروط WHERE غير فعالة

المشكلة:

-- Wrong: Functions prevent index use
SELECT * FROM xoops_articles
WHERE YEAR(created) = 2024;

-- Wrong: OR with different columns
SELECT * FROM xoops_articles
WHERE category = 'tech' OR author_id = 5;

الحل:

-- Correct: Use range
SELECT * FROM xoops_articles
WHERE created >= '2024-01-01' AND created < '2025-01-01';

-- Correct: Use UNION for different columns
SELECT * FROM xoops_articles WHERE category = 'tech'
UNION
SELECT * FROM xoops_articles WHERE author_id = 5;

---

تصحيح مشاكل محددة

المشكلة: الاستعلام يعيد نتائج خاطئة

<?php
$ray = ray();

// Test with sample data
$author_id = 5;
$ray->info("Searching for author_id = $author_id");

$query = "SELECT * FROM xoops_articles WHERE author_id = ?";
$stmt = $db->prepare($query);
$stmt->bind_param("i", $author_id);
$stmt->execute();

$result = $stmt->get_result();
$count = $result->num_rows;

$ray->info("Found: $count articles");

// Check if parameterized query helps
if ($count == 0) {
    // Try without parameter to debug
    $debug_query = "SELECT * FROM xoops_articles WHERE author_id = $author_id";
    $ray->warning("Debug query: $debug_query");
}

// Dump first result
if ($row = $result->fetch_assoc()) {
    $ray->label('First Result')->dump($row);
}
?>

---

المشكلة: استعلام Join بطيء

<?php
$ray = ray();

$query = "
    SELECT a.id, a.title, u.uname, u.email
    FROM xoops_articles a
    LEFT JOIN xoops_users u ON a.author_id = u.uid
    WHERE a.status = 1
    ORDER BY a.created DESC
    LIMIT 50
";

$ray->info("Running join query");
$ray->measure(function() use ($query) {
    $result = $GLOBALS['xoopsDB']->query($query);
    return $result;
});

// Analyze with EXPLAIN
$ray->label('Query Analysis')->info($query);
?>

قم بتشغيل EXPLAIN:

EXPLAIN SELECT a.id, a.title, u.uname, u.email
FROM xoops_articles a
LEFT JOIN xoops_users u ON a.author_id = u.uid
WHERE a.status = 1
ORDER BY a.created DESC
LIMIT 50\G

-- Look for:
-- - type: ALL (need index)
-- - Extra: Using temporary; Using filesort (inefficient)
-- Fix: Add composite index
ALTER TABLE xoops_articles ADD INDEX (status, created);

---

قائمة تحسين قاعدة البيانات

graph TD
    A[Database Optimization] --> B["1. Identify Issues"]
    A --> C["2. Add Indexes"]
    A --> D["3. Refactor Queries"]
    A --> E["4. Monitor"]

    B --> B1["✓ Enable slow log"]
    B --> B2["✓ Check EXPLAIN"]
    B --> B3["✓ Profile code"]

    C --> C1["✓ Single column"]
    C --> C2["✓ Composite"]
    C --> C3["✓ Unique"]

    D --> D1["✓ Remove N+1"]
    D --> D2["✓ Use JOINs"]
    D --> D3["✓ Add WHERE"]

    E --> E1["✓ Watch slow log"]
    E --> E2["✓ Monitor size"]
    E --> E3["✓ Clean old data"]

---

استعلامات MySQL مفيدة

-- Find slow tables
SELECT * FROM xoops_log
WHERE info LIKE '%type: ALL%'
ORDER BY created DESC LIMIT 20;

-- List all indexes
SHOW INDEX FROM xoops_articles;

-- Find duplicate indexes
SELECT a.table_name, a.index_name, a.seq_in_index, a.column_name
FROM information_schema.statistics a
JOIN information_schema.statistics b
  ON a.table_name = b.table_name
  AND a.seq_in_index = b.seq_in_index
  AND a.column_name = b.column_name
  AND a.index_name != b.index_name
WHERE a.table_name LIKE 'xoops_%';

-- Table sizes
SELECT table_name,
       ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'xoops_db'
ORDER BY size_mb DESC;

-- Find unused indexes
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema != 'mysql'
AND count_star = 0
ORDER BY object_name;

---

الوثائق ذات الصلة

• Enable Debug Mode
• Using Ray Debugger
• Performance FAQ
• Database Fundamentals

---

#xoops #database #debugging #sql #optimization #mysql