Nasıl bu MySQL sorgusu optimize edebilirsiniz?

6 Cevap php

Ben 300.000.000 üzerinde (evet, üç yüz milyon) satırları içeren bir veritabanı üzerinde bir PHP komut dosyası aşağıdaki MySQL sorgusu kullanıyorum. Ben son derece yoğun bir kaynak olduğunu biliyoruz ve bu bir sorgu çalıştırmak için yaş alır. Herkes Ben de daha hızlı olan başka bir şekilde sorgu optimize veya bilgi alabilirsiniz biliyor mu?

I () MID 14 yerine 1 ile 15 arasında herhangi bir tamsayı kullanmak gerekiyor. Ben de LIKE fıkrasında aynı aralığında uzunluklarda dizelerini maç gerekiyor.

Tablo Bilgi:

games | longint, unsigned, Primary Key
win   | bit(1)
loss  | bit(1)

Örnek Sorgu:

SELECT MID(`game`,14,1) AS `move`,
       COUNT(*) AS `games`,
       SUM(`win`) AS `wins`,
       SUM(`loss`) AS `losses`
FROM `games`
WHERE `game` LIKE '1112223334%'
GROUP BY MID(`game`,1,14)

Yardımlarınız için şimdiden teşekkürler!

6 Cevap

Birincisi, ... oyun alanında bir dizin var :)

Sorgu basit ve anlaşılır görünüyor, ama bir datasbase tasarım değişikliği muhtemelen gerekli olduğu gerçeği gizler.

Bu gibi durumlarda hep kullanıcı başına, ya da diğer herhangi bir eksen başına, ya günde, toplanan verileri tutan bir alanı korumak için tercih. Bu şekilde ilgili verileri toplayan ve veritabanına kaydeder bir günlük bir görev olabilir.

Eğer sık ​​sık bu sorgu çağırmak gerçekten varsa, geri alma verimliliğini artırmak için ekleme verimliliği azalan prensibini kullanmalısınız.

game sütun bu sorgu kullanan iki (ya da belki daha fazla) farklı şeyler depolamak gibi görünüyor:

  1. game (ilk 10 karakter) başlamasıyla Filtreleme
  2. Gruplandırarak ve MID( oyun ,1,14) (I MID ifadelerden birini varsayarak yaşıyorum dönen bir yazım hatası.

Sana game sütun üzerinde string işlemleri kullanmak zorunda değilsiniz böylece bölmek ve düzgün filtre ve grup onları böylece de yeni sütunlar üzerinde dizinler koymak istiyorum.

Bu sorgu tablo (şimdi olduğu gibi yerine çoklu kolon başına bir bilgi parçası gibi) normalize olsaydı gerekli olmaz dönüşüm (uzun dize) ve dize manipülasyonlar bir sürü yapıyor.

game sütuna o şekilde bırakmak, ve WHERE yan kullanmak için buna dayalı bir game_filter dize sütun oluşturun. Sonra game_group sütun kurmak ve insert üzerinde MID ifadesi ile doldurmak. Sonra kümelenmiş dizin, ilk game_filter, olarak bu iki sütunu kurmak game_group.

The query is simple and, aside from making sure there are all the necessary indexes ("game" field obviously), there may be no obvious way to make it faster by rewriting the query only. Some modification of data structures will probably be necessary.

Tek yol: meblağlar ön hesaplama. Bu kayıtların her biri büyük olasılıkla bir create_date veya autoincremented anahtar alanı olacak. Bu alan, bazı X ≤ tüm kayıtları için toplamları ön hesaplama, yan masada sonuçları koymak, ve sonra sadece o zaman önceden hesaplanmış olanlarla bu kısmi sonuçları özetlemek,> X tüm kayıtlar için hesaplamak gerekir.

Sen MID (game, 14,1) ve MID (game, 1,14) precompute ve bir de game ilk on basamağını saklamak olabilir endeksli ayrı gameid sütun.

Ayrıca yerine insert üzerinde sayım ve kazanç ya da kayıplar sütun artırmak böylece sadece precomputed değerlerin toplam tablo depolamak eğer araştırmak için bir fikir olabilir.

SELECT  MID(`game`,14,1) AS `move`,
        COUNT(*) AS `games`,
        SUM(`win`) AS `wins`,
        SUM(`loss`) AS `losses`
FROM    `games`
WHERE   `game` LIKE '1112223334%'

Bir indeks oluştur game:

CREATE INDEX ix_games_game ON games (game)

ve bu gibi sorguyu yeniden yazın:

SELECT  move,
        (
        SELECT  COUNT(*)
        FROM    games
        WHERE   game >= move
                AND game < CONCAT(SUBSTRING(move, 1, 13), CHR(ASCII(SUBSTRING(move, 14, 1)) + 1))
        ),
        (
        SELECT  SUM(win)
        FROM    games
        WHERE   game >= move
                AND game < CONCAT(SUBSTRING(move, 1, 13), CHR(ASCII(SUBSTRING(move, 14, 1)) + 1))
        ),
        (
        SELECT  SUM(lose)
        FROM    games
        WHERE   game >= move
                AND game < CONCAT(SUBSTRING(move, 1, 13), CHR(ASCII(SUBSTRING(move, 14, 1)) + 1))
        )
FROM    (
        SELECT  DISTINCT SUBSTRING(q.game, 1, 14) AS move
        FROM    games
        WHERE   game LIKE '1112223334%'
        ) q

Bu daha verimli game üzerine dizin kullanmak için yardımcı olacaktır.

Eğer Memcache ya da benzer bir set sonucu önbelleğe miyim? Bu tekrarlanan isabet ile yardımcı olacaktır. Eğer sadece bir kaç saniye için sonuç kümesini önbelleğe bile, DB çok okur önlemek mümkün olabilir.

etiketler