Ben bu MySQL sorgusu optimize etmek için yapabileceğiniz başka bir şey var mı?

3 Cevap php

İki tablo, 600,000 girişleri ile 700.000 girişleri ve Tablo B Tablo A var. Aşağıdaki gibi yapısı şu şekildedir:

Tablo A:

+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment | 
| number    | bigint(20) unsigned | YES  |     | NULL    |                | 
+-----------+---------------------+------+-----+---------+----------------+

Tablo B:

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment | 
| number_s    | bigint(20) unsigned | YES  | MUL | NULL    |                | 
| number_e    | bigint(20) unsigned | YES  | MUL | NULL    |                | 
| source      | varchar(50)         | YES  |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

Ben Tablo A'da değerlerden herhangi birini aşağıdaki kodu kullanarak Tablo B mevcut olup olmadığını bulmak için çalışıyorum:

$sql = "SELECT number from TableA";
$result = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_assoc($result)) {
        $number = $row['number'];
        $sql = "SELECT source, count(source) FROM TableB WHERE number_s < $number AND number_e > $number GROUP BY source";
        $re = mysql_query($sql) or die(mysql_error);
        while($ro = mysql_fetch_array($re)) {
                echo $number."\t".$ro[0]."\t".$ro[1]."\n";
        }
}

Ben sorgu nedense hızlı ama sonra gitmek umuyordum, hızlı korkunç değil. ("Numara" belirli bir değere sahip) seçin açıklamak benim bana aşağıdaki verir:

mysql> explain SELECT source, count(source) FROM TableB WHERE number_s < 1812194440 AND number_e > 1812194440 GROUP BY source;
+----+-------------+------------+------+-------------------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table      | type | possible_keys           | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+------------+------+-------------------------+------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | TableB     | ALL  | number_s,number_e       | NULL | NULL    | NULL | 696325 | Using where; Using temporary; Using filesort | 
+----+-------------+------------+------+-------------------------+------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)

Ben bu dışarı sıkmak olabilir herhangi bir optimizasyon var mı?

Ben aynı görev için bir saklı yordam yazmaya çalıştım ama o bile ilk etapta çalışmak için görünmüyor ... Ben bir gün için çalışan denedim ve hala koşuyordu ... yazım hataları vermez ki garip hissettim.

CREATE PROCEDURE Filter() 
Begin 
  DECLARE number BIGINT UNSIGNED; 
  DECLARE x INT; 
  DECLARE done INT DEFAULT 0; 
  DECLARE cur1 CURSOR FOR SELECT number FROM TableA; 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
  CREATE TEMPORARY TABLE IF NOT EXISTS Flags(number bigint unsigned, count int(11)); 
  OPEN cur1; 
  hist_loop: LOOP 
    FETCH cur1 INTO number; 
    SELECT count(*) from TableB WHERE number_s < number AND number_e > number INTO x; 
    IF done = 1 THEN 
      LEAVE hist_loop; 
    END IF; 
    IF x IS NOT NULL AND x>0 THEN 
      INSERT INTO Flags(number, count) VALUES(number, x); 
    END IF; 
  END LOOP hist_loop; 
  CLOSE cur1;
END

3 Cevap

Bir noktayı içeren aralıklarını bulmak için çalışıyoruz. Bu B-tree indeksi (çoğu veritabanlarında varsayılan indeks türü) ile çok hızlı değildir, ancak bir R-tree endeksi sorgu bu tür için iyi çalışacaktır. MySQL doğrudan bir dizin türünü değiştirmek için izin vermez, ancak GEOMETRİ sütun türünü kullanarak bir R-Ağacı kullanmak için MySQL zorlayabilir.

Quassnoi his article on nested sets in MySQL, bu kapsar. Oldukça aynı olmasa da, çok benzer. Makalesinden bir alıntı:

There is also a certain class of tasks that require searching for all ranges containing a known value:

* Searching for an IP address in the IP range ban list
* Searching for a given date within a date range

and several others. These tasks can be improved by using R-Tree capabilities of MySQL

Muhtemelen ayrı ADD INDEX(number_e) ve ADD INDEX(number_s) sütunlar ile oluşturulan ayrı number_e dizinleri ve number_s sütun, var gibi görünüyor bana.

Eğer onlar sorguda kullanılan her ikisi gibi, her ikisi de bu sütunları kapsar ve MySQL açık yargılama, tek sütun indeksleri birini kullanmayı tercih değil, bir dizin eklemek olsaydı muhtemelen çok daha iyi bir performans elde edeceği bir bütün tablo taraması (sorgu değerleri geniş bir ürün yelpazesi yayılıyorsa nadir değildir) daha hızlı olacaktır.

ALTER TABLE tblB ADD INDEX(number_s,number_e);

Siz de o bir damla olabilir number_s sadece, bu yüzden MySQL sorguları için sadece oluşturduğunuz birini kullanmak gibi, bundan sonra bireysel number_s endeksi gerekmez.

First, I assume the desired output is to group all 'source' where the input lies between number_e and number_s, and the count thereof.

Ben sözdizimi cızırtılı değilim, ama less-than/greater-than işletmecileri kullanarak yerine açık bir karşılaştırma orada maddesi 'ARASINDA' kullanmayı düşünebilirsiniz

Edit: Zombat de geçerlidir ne diyor; indeksler de yardımcı olacaktır.

etiketler