Dengan menggunakan kursor, Anda dapat menerima dari batch database dan memproses sejumlah besar data tanpa membuang-buang memori aplikasi. Saya yakin bahwa setiap pengembang web menghadapi tugas serupa setidaknya sekali, dan tidak hanya sekali sebelum saya. Dalam artikel ini saya akan memberi tahu Anda apa tugas kursor dapat berguna, dan saya akan memberikan kode yang sudah jadi untuk bekerja dengannya dari PHP + Doktrin menggunakan contoh PostrgeSQL.
Masalah
Bayangkan saja kita punya proyek PHP, besar dan berat. Tentunya, itu ditulis dengan bantuan beberapa kerangka kerja. Misalnya, Symfony. Ini juga menggunakan database, misalnya, PostgreSQL, dan database memiliki plat untuk 2.000.000 catatan dengan informasi tentang pesanan. Dan proyek itu sendiri adalah antarmuka untuk pesanan ini, yang dapat menampilkan dan memfilternya. Dan, izinkan saya mengatakan, ini cukup baik.
Sekarang kami ditanya (apakah Anda belum ditanyai? Mereka pasti akan diminta) untuk mengunggah hasil pemfilteran pesanan ke file Excel. Mari kita menyiapkan tombol dengan ikon tabel, yang akan memuntahkan file dengan perintah kepada pengguna.
Bagaimana biasanya diputuskan, dan mengapa itu buruk?
Bagaimana seorang programmer yang belum mengalami tugas seperti itu? Dia membuat SELECT dalam database, membaca hasil kueri, mengubah respons menjadi file Excel dan memberikannya ke browser pengguna. Tugas berhasil, pengujian selesai, tetapi masalah mulai dalam produksi.
Tentunya, kami telah menetapkan batas memori untuk PHP di beberapa yang wajar (bisa dibilang) 1 GB per proses, dan begitu 2 juta baris ini tidak lagi sesuai dengan gigabyte ini, semuanya rusak. PHP macet dengan kesalahan "kehabisan memori", dan pengguna mengeluh bahwa file tersebut tidak diunggah. Ini terjadi karena kami memilih cara yang agak naif untuk membongkar data dari database - semuanya pertama-tama ditransfer dari memori basis data (dan disk di bawahnya) ke RAM proses PHP, kemudian mereka diproses dan diunggah ke browser.
Agar data selalu ditempatkan dalam memori, Anda perlu mengambilnya dari basis data menjadi beberapa bagian. Misalnya, 10.000 catatan dibaca, diproses, ditulis ke file, dan berkali-kali.
Nah, pikir programmer yang memenuhi tugas kita untuk pertama kalinya. Kemudian saya akan melakukan loop dan mengempiskan hasil query dalam potongan-potongan, yang menunjukkan LIMIT dan OFFSET. Ini berfungsi, tetapi ini adalah operasi yang sangat mahal untuk database, dan karenanya mengunggah laporan tidak mulai memakan waktu 30 detik, tetapi 30 menit (tidak terlalu buruk!). By the way, jika terlepas dari OFFSET pada saat ini tidak ada lagi yang terlintas dalam pikiran programmer, maka masih ada banyak cara untuk mencapai hal yang sama tanpa memperkosa basis data.
Pada saat yang sama, database itu sendiri memiliki kemampuan bawaan untuk membaca data darinya - kursor.
Kursor
Kursor adalah penunjuk ke baris dalam hasil kueri yang hidup di database. Saat menggunakannya, kita dapat melakukan SELECT tidak dalam mode segera mengunduh data, tetapi buka kursor dengan pilihan ini. Selanjutnya, kita mulai menerima aliran data dari database ketika kursor bergerak maju. Ini memberi kita hasil yang sama: kita membaca data secara bagian, tetapi database tidak melakukan pekerjaan yang sama untuk menemukan baris yang harus dimulai, seperti halnya dengan OFFSET.
Kursor dibuka hanya di dalam transaksi dan hidup sampai transaksi itu hidup (ada pengecualian, lihat DENGAN TAHAN ). Ini berarti bahwa jika kita perlahan membaca banyak data dari basis data, maka kita akan memiliki transaksi yang panjang. Ini terkadang buruk , Anda perlu memahami dan menerima risiko ini.
Kursor dalam Ajaran
Mari kita coba terapkan pekerjaan dengan kursor dalam Ajaran. Pertama, seperti apa permintaan untuk membuka kursor?
BEGIN; DECLARE mycursor1 CURSOR FOR ( SELECT * FROM huge_table );
DECLARE membuat dan membuka kursor untuk kueri SELECT yang ditentukan. Setelah membuat kursor, Anda dapat mulai membaca data darinya:
FETCH FORWARD 10000 FROM mycursor1; < 10 000 > FETCH FORWARD 10000 FROM mycursor1; < 10 000 > ...
Dan seterusnya, hingga FETCH mengembalikan daftar kosong. Ini berarti bahwa mereka menggulir hingga akhir.
COMMIT;
Kami menguraikan kelas yang kompatibel dengan Doctrine yang akan merangkum pekerjaan dengan kursor. Dan untuk memecahkan 80% masalah dalam 20% dari waktu , itu hanya akan berfungsi dengan Native Queries. Jadi mari kita sebut saja, PgSqlNativeQueryCursor.
Konstruktor:
public function __construct(NativeQuery $query) { $this->query = $query; $this->connection = $query->getEntityManager()->getConnection(); $this->cursorName = uniqid('cursor_'); assert($this->connection->getDriver() instanceof PDOPgSqlDriver); }
Di sini saya menghasilkan nama untuk kursor masa depan.
Karena kelas memiliki kode SQL khusus PostgreSQL di kelas, lebih baik untuk memeriksa bahwa driver kami adalah PG.
Dari kelas kita membutuhkan tiga hal:
- Dapat membuka kursor.
- Mampu mengembalikan data kepada kami.
- Mampu menutup kursor.
Buka kursor:
public function openCursor() { if ($this->connection->getTransactionNestingLevel() === 0) { throw new \BadMethodCallException('Cursor must be used inside a transaction'); } $query = clone $this->query; $query->setSQL(sprintf( 'DECLARE %s CURSOR FOR (%s)', $this->connection->quoteIdentifier($this->cursorName), $this->query->getSQL() )); $query->execute($this->query->getParameters()); $this->isOpen = true; }
Seperti yang saya katakan, kursor terbuka dalam transaksi. Oleh karena itu, di sini saya memeriksa bahwa kami tidak lupa menelepon ke metode ini di dalam transaksi yang sudah terbuka. (Alhamdulillah, waktu telah berlalu ketika saya akan ditarik untuk membuka transaksi di sini!)
Untuk menyederhanakan tugas membuat dan menginisialisasi NativeQuery baru, saya hanya mengkloning yang dimasukkan ke dalam konstruktor dan membungkusnya dalam DECLARE ... CURSOR FOR (here_original_query). Saya melakukannya.
Mari kita membuat metode getFetchQuery. Ini tidak akan mengembalikan data, tetapi permintaan lain yang dapat digunakan sesuka Anda untuk mendapatkan data yang diinginkan dalam kumpulan yang diberikan. Ini memberi kode panggilan lebih banyak kebebasan.
public function getFetchQuery(int $count = 1): NativeQuery { $query = clone $this->query; $query->setParameters([]); $query->setSQL(sprintf( 'FETCH FORWARD %d FROM %s', $count, $this->connection->quoteIdentifier($this->cursorName) )); return $query; }
Metode ini memiliki satu parameter - ini adalah ukuran bundel, yang akan menjadi bagian dari permintaan yang dikembalikan oleh metode ini. Saya menerapkan trik yang sama dengan kloning permintaan, menimpa parameter di dalamnya dan mengganti SQL dengan FETCH ... FROM ...;
Agar tidak lupa untuk membuka kursor sebelum panggilan pertama ke getFetchQuery () (tiba-tiba saya tidak akan cukup tidur), saya akan membuat pembukaan implisit langsung di metode getFetchQuery ():
public function getFetchQuery(int $count = 1): NativeQuery { if (!$this->isOpen) { $this->openCursor(); } …
Dan metode openCursor () itu sendiri akan dibuat pribadi. Saya tidak melihat kasus sama sekali ketika saya perlu menyebutnya secara eksplisit.
Di dalam getFetchQuery (), saya hard-coded FORWARD untuk menggerakkan kursor ke depan sejumlah baris. Tetapi mode panggilan FETCH banyak berbeda. Mari kita tambahkan juga?
const DIRECTION_NEXT = 'NEXT'; const DIRECTION_PRIOR = 'PRIOR'; const DIRECTION_FIRST = 'FIRST'; const DIRECTION_LAST = 'LAST'; const DIRECTION_ABSOLUTE = 'ABSOLUTE';
Setengah dari mereka menerima jumlah baris dalam parameter, dan setengahnya lagi tidak. Inilah yang saya dapat:
public function getFetchQuery(int $count = 1, string $direction = self::DIRECTION_FORWARD): NativeQuery { if (!$this->isOpen) { $this->openCursor(); } $query = clone $this->query; $query->setParameters([]); if ( $direction == self::DIRECTION_ABSOLUTE || $direction == self::DIRECTION_RELATIVE || $direction == self::DIRECTION_FORWARD || $direction == self::DIRECTION_BACKWARD ) { $query->setSQL(sprintf( 'FETCH %s %d FROM %s', $direction, $count, $this->connection->quoteIdentifier($this->cursorName) )); } else { $query->setSQL(sprintf( 'FETCH %s FROM %s', $direction, $this->connection->quoteIdentifier($this->cursorName) )); } return $query; }
Tutup kursor dengan TUTUP, tidak perlu menunggu transaksi selesai:
public function close() { if (!$this->isOpen) { return; } $this->connection->exec('CLOSE ' . $this->connection->quoteIdentifier($this->cursorName)); $this->isOpen = false; }
Destructor:
public function __destruct() { if ($this->isOpen) { $this->close(); } }
Inilah keseluruhan kelas secara keseluruhan . Mari kita coba beraksi?
Saya membuka beberapa Penulis bersyarat di beberapa XLSX bersyarat.
$writer->openToFile($targetFile);
Di sini saya mendapatkan NativeQuery untuk menarik daftar pesanan dari database.
$query = $this->getOrdersRepository($em) ->getOrdersFiltered($dateFrom, $dateTo, $filters);
Berdasarkan permintaan ini, saya menyatakan kursor.
$cursor = new PgSqlNativeQueryCursor($query);
Dan untuknya saya mendapatkan permintaan untuk menerima data dalam batch 10.000 baris.
$fetchQuery = $cursor->getFetchQuery(10000);
Saya beralih sampai saya mendapatkan hasil kosong. Di setiap iterasi, saya melakukan FETCH, memproses hasilnya, dan menulis ke file.
do { $result = $fetchQuery->getArrayResult(); foreach ($result as $row) { $writer->addRow($this->toXlsxRow($row)); } } while ($result);
Saya menutup kursor dan Penulis.
$cursor->close(); $writer->close();
Saya menulis file ke disk di direktori untuk file sementara, dan setelah rekaman selesai saya kirim ke browser untuk menghindari, lagi, buffering di memori.
LAPORAN SIAP! Kami menggunakan jumlah memori yang konstan dari PHP untuk memproses semua data dan tidak menyiksa database dengan serangkaian pertanyaan berat. Dan pembongkaran itu sendiri memakan waktu sedikit lebih banyak daripada yang dibutuhkan pangkalan untuk memenuhi permintaan.
Lihat apakah ada tempat di proyek Anda yang dapat mempercepat / menghemat memori dengan kursor?