Senibina Pelayan SQL Dijelaskan: Paip Dinamakan, Pengoptimum, Pengurus Penyangga

Isi kandungan:

Anonim

MS SQL Server adalah seni bina pelayan pelanggan. Proses MS SQL Server dimulakan dengan aplikasi klien mengirim permintaan. Pelayan SQL menerima, memproses dan membalas permintaan dengan data yang diproses. Mari kita bincangkan secara terperinci keseluruhan seni bina yang ditunjukkan di bawah:

Seperti gambar rajah di bawah, terdapat tiga komponen utama dalam SQL Server Architecture:

  1. Lapisan Protokol
  2. Enjin Perhubungan
  3. Enjin Storan
Diagram Senibina Pelayan SQL

Mari kita bincangkan secara terperinci mengenai ketiga-tiga modul utama di atas. Dalam tutorial ini, anda akan belajar.

  • Lapisan Protokol - SNI
    • Ingatan Berkongsi
    • TCP / IP
    • Paip Dinamakan
    • Apa itu TDS?
  • Enjin Perhubungan
    • Penyusun CMD
    • Pengoptimum
    • Pelaksana Pertanyaan
  • Enjin Storan
    • Jenis fail
    • Kaedah Akses
    • Pengurus Penyangga
    • Rancang Cache
    • Penguraian Data: Penyangga cache & Penyimpanan Data
    • Pengurus Transaksi

Lapisan Protokol - SNI

MS SQL SERVER PROTOCOL LAYER menyokong 3 Jenis Senibina Pelayan Pelanggan. Kami akan memulakan dengan " Tiga Jenis Senibina Pelayan Pelanggan" yang disokong oleh MS SQL Server.

Ingatan Berkongsi

Mari kita pertimbangkan semula senario Perbualan awal pagi.

MOM dan TOM - Di sini Tom dan Ibunya, berada di tempat yang sama logiknya, iaitu di rumah mereka. Tom dapat meminta Kopi dan Ibu dapat memberikannya panas.

MS SQL SERVER - Di sini pelayan MS SQL menyediakan PROTOKOL MEMORI BERKONGSI . Di sini pelayan CLIENT dan MS SQL dijalankan pada mesin yang sama. Kedua-duanya dapat berkomunikasi melalui protokol Memori Bersama.

Analogi: Mari memetakan entiti dalam dua senario di atas. Kami dapat memetakan Tom ke Klien, pelayan Mom ke SQL, Home to Machine, dan Komunikasi Verbal ke Protokol Memori Bersama dengan mudah.

Dari meja konfigurasi dan pemasangan:

Untuk Sambungan ke DB Tempatan - Di SQL Management Studio, Pilihan "Nama Server" mungkin

"."

"localhost"

"127.0.0.1"

"Mesin \ Instance"

TCP / IP

Sekarang pertimbangkan pada waktu petang, Tom berada dalam suasana pesta. Dia mahukan Kopi yang dipesan dari Kedai Kopi yang terkenal. Kedai Kopi terletak 10 km dari rumahnya.

Di sini Tom dan Starbuck berada di lokasi fizikal yang berbeza. Tom di rumah dan Starbucks di pasar yang sibuk. Mereka berkomunikasi melalui rangkaian Selular. Begitu juga, MS SQL SERVER memberikan kemampuan untuk berinteraksi melalui protokol TCP / IP, di mana CLIENT dan MS SQL Server berada di antara satu sama lain dan dipasang pada mesin yang berasingan.

Analogi: Mari memetakan entiti dalam dua senario di atas. Kita dapat memetakan Tom to Client, Starbuck ke SQL server, tempat Home / Market ke lokasi Remote dan akhirnya rangkaian Cellular ke protokol TCP / IP.

Catatan dari meja Konfigurasi / pemasangan:

  • Di SQL Management Studio - Untuk Sambungan melalui TCP \ IP, "Nama Server" Pilihan harus "Mesin \ Instance pelayan."
  • Pelayan SQL menggunakan port 1433 dalam TCP / IP.

Paip Dinamakan

Sekarang akhirnya pada waktu malam, Tom ingin minum teh hijau muda yang disiapkan oleh jirannya, Sierra.

Di sini Tom dan Jirannya , Sierra, berada di lokasi fizikal yang sama , menjadi jiran satu sama lain. Mereka berkomunikasi melalui rangkaian Intra. Begitu juga, MS SQL SERVER memberikan keupayaan untuk berinteraksi melalui protokol Named Pipe . Di sini CLIENT dan MS SQL SERVER berhubung melalui LAN .

Analogi: Mari memetakan entiti dalam dua senario di atas. Kami dapat memetakan Tom ke Client, pelayan Sierra ke SQL, Jiran ke LAN dan akhirnya rangkaian Intra ke Protokol Pipa Dinamakan dengan mudah.

Catatan dari meja Konfigurasi / pemasangan:

  • Untuk Sambungan melalui Paip Dinamakan. Pilihan ini dilumpuhkan secara lalai dan perlu diaktifkan oleh Pengurus Konfigurasi SQL.

Apa itu TDS?

Setelah mengetahui bahawa terdapat tiga jenis Client-Server Architecture, mari kita melihat TDS:

  • TDS bermaksud Tabular Data Stream.
  • Semua 3 protokol menggunakan paket TDS. TDS dikemas dalam paket Rangkaian. Ini membolehkan pemindahan data dari mesin pelanggan ke mesin pelayan.
  • TDS pertama kali dikembangkan oleh Sybase dan kini Dimiliki oleh Microsoft

Enjin Perhubungan

Relational Engine juga dikenali sebagai Query Processor. Ia mempunyai komponen SQL Server yang menentukan apa sebenarnya pertanyaan yang perlu dilakukan dan bagaimana ia dapat dilakukan dengan sebaik-baiknya. Ini bertanggung jawab untuk pelaksanaan pertanyaan pengguna dengan meminta data dari mesin penyimpanan dan memproses hasil yang dikembalikan.

Seperti yang digambarkan dalam Rajah Senibina terdapat 3 komponen utama Relational Engine. Mari kaji komponennya secara terperinci:

Penyusun CMD

Data setelah diterima dari Protocol Layer kemudian dihantar ke Relational Engine. "CMD Parser" adalah komponen pertama Relational Engine yang menerima data Pertanyaan. Tugas utama CMD Parser adalah memeriksa pertanyaan untuk kesalahan Syntactic and Semantic. Akhirnya, ia menghasilkan Query Tree . Mari kita bincangkan secara terperinci.

Pemeriksaan sintaksis:

  • Seperti bahasa Pengaturcaraan lain, MS SQL juga mempunyai set Kata Kunci yang telah ditetapkan. Juga, SQL Server mempunyai tatabahasa tersendiri yang difahami oleh pelayan SQL.
  • SELECT, INSERT, UPDATE, dan banyak yang lain termasuk dalam senarai Kata Kunci MS SQL yang telah ditetapkan.
  • CMD Parser melakukan pemeriksaan sintaksis. Sekiranya input pengguna tidak mengikuti peraturan sintaks atau tatabahasa bahasa ini, ia akan mengembalikan kesalahan.

Contoh: Katakan seorang Rusia pergi ke restoran Jepun. Dia memesan makanan segera dalam bahasa Rusia. Malangnya, pelayan hanya memahami bahasa Jepun. Apakah hasil yang paling jelas?

Jawapannya - pelayan tidak dapat memproses pesanan lebih jauh.

Tidak boleh ada penyimpangan dalam Tatabahasa atau bahasa yang diterima oleh pelayan SQL. Sekiranya ada, pelayan SQL tidak dapat memprosesnya dan oleh itu akan mengembalikan mesej ralat.

Kami akan mengetahui lebih lanjut mengenai pertanyaan MS SQL dalam tutorial yang akan datang. Namun, pertimbangkan di bawah kebanyakan Sintaks Kueri asas sebagai 

SELECT * from ;

Sekarang, untuk mendapatkan persepsi tentang apa yang dilakukan oleh sintaksis, katakan jika pengguna menjalankan pertanyaan asas seperti di bawah:

SELECR * from

Perhatikan bahawa bukannya 'SELECT' pengguna menaip "SELECR."

Hasil: Parser CMD akan menghuraikan pernyataan ini dan akan membuang mesej ralat. Sebagai "SELECR" tidak mengikut nama dan tatabahasa kata kunci yang telah ditetapkan. Di sini CMD Parser mengharapkan "PILIH."

Pemeriksaan semantik:

  • Ini dilakukan oleh Normalizer .
  • Dalam bentuk termudah, ia memeriksa sama ada nama Kolum, Nama jadual yang ditanyakan ada di Skema. Dan jika ada, ikat pada Pertanyaan. Ini juga dikenali sebagai Binding .
  • Kerumitan meningkat apabila pertanyaan pengguna mengandungi LIHAT. Normalizer melakukan penggantian dengan definisi pandangan yang disimpan secara dalaman dan banyak lagi.

Mari fahami ini dengan bantuan contoh di bawah -

SELECT * from USER_ID

Hasilnya: CMD Parser akan menghuraikan pernyataan ini untuk semantik semakan. Penghurai akan membuang mesej ralat kerana Normalizer tidak akan menemui jadual yang diminta (USER_ID) kerana tidak ada.

Buat Pohon Pertanyaan:

  • Langkah ini menghasilkan pokok pelaksanaan yang berbeza di mana pertanyaan dapat dijalankan.
  • Perhatikan bahawa, semua pokok yang berbeza mempunyai hasil yang diinginkan yang sama.

Pengoptimum

Kerja pengoptimum adalah membuat rancangan pelaksanaan untuk pertanyaan pengguna. Ini adalah rancangan yang akan menentukan bagaimana pertanyaan pengguna akan dijalankan.

Perhatikan bahawa tidak semua pertanyaan dioptimumkan. Pengoptimuman dilakukan untuk perintah DML (Data Modification Language) seperti SELECT, INSERT, DELETE, dan UPDATE. Pertanyaan sedemikian ditandai terlebih dahulu kemudian hantar ke pengoptimum. Perintah DDL seperti CREATE dan ALTER tidak dioptimumkan, tetapi sebaliknya disusun ke dalam bentuk dalaman. Kos pertanyaan dikira berdasarkan faktor seperti penggunaan CPU, penggunaan Memori, dan keperluan Input / Output.

Peranan pengoptimum adalah mencari rancangan pelaksanaan yang paling murah, bukan yang terbaik dan menjimatkan.

Sebelum kita melihat lebih terperinci teknikal Optimizer, pertimbangkan di bawah contoh kehidupan sebenar:

Contoh:

Katakan, anda mahu membuka akaun Bank dalam talian. Anda sudah mengetahui tentang satu Bank yang memerlukan maksimum 2 Hari untuk membuka akaun. Tetapi, anda juga mempunyai senarai 20 bank lain, yang mungkin memakan masa kurang dari 2 hari atau mungkin. Anda boleh mula terlibat dengan bank-bank ini untuk menentukan bank mana yang mengambil masa kurang dari 2 hari. Sekarang, anda mungkin tidak menemui bank yang mengambil masa kurang dari 2 Hari, dan ada masa tambahan yang hilang kerana aktiviti carian itu sendiri. Lebih baik membuka akaun dengan bank pertama itu sendiri.

Kesimpulan: Lebih penting untuk memilih dengan bijak. Untuk tepat, pilih pilihan mana yang terbaik, bukan yang termurah.

Begitu juga, MS SQL Optimizer berfungsi pada algoritma lengkap / heuristik inbuilt. Tujuannya adalah untuk meminimumkan masa menjalankan pertanyaan. Semua algoritma Optimizer adalah kepunyaan Microsoft dan rahsia. Walaupun , di bawah ini adalah langkah-langkah tahap tinggi yang dilakukan oleh MS SQL Optimizer. Pencarian Pengoptimuman mengikuti tiga fasa seperti yang ditunjukkan dalam rajah di bawah:

Fasa 0: Cari Pelan Trivial:

  • Ini juga dikenali sebagai tahap Pengoptimuman Pra .
  • Untuk beberapa kes, mungkin hanya ada satu rancangan praktikal dan dapat dilaksanakan, yang dikenali sebagai rancangan remeh. Tidak perlu membuat rancangan yang dioptimumkan. Sebabnya, mencari lebih banyak akan menghasilkan rancangan pelaksanaan jangka masa yang sama. Itu juga dengan kos tambahan untuk Mencari Pelan yang dioptimumkan yang tidak diperlukan sama sekali.
  • Jika tiada pelan Trivial ditemui, maka 1 st Fasa bermula.

Fasa 1: Mencari rancangan pemprosesan Transaksi

  • Ini merangkumi pencarian Pelan Ringkas dan Kompleks .
  • Pencarian Pelan Ringkas: Data kolom dan Indeks yang lalu yang terlibat dalam Pertanyaan, akan digunakan untuk Analisis Statistik. Ini biasanya terdiri tetapi tidak terhad kepada satu Indeks Per jadual.
  • Namun, jika rancangan mudah tidak dijumpai, maka Pelan yang lebih kompleks dicari. Ia melibatkan Indeks Pelbagai setiap jadual.

Fasa 2: Pemprosesan dan Pengoptimuman Selari.

  • Sekiranya tiada strategi di atas berfungsi, Pengoptimum mencari kemungkinan Pemprosesan Selari. Ini bergantung pada kemampuan dan konfigurasi pemprosesan Mesin.
  • Sekiranya masih belum dapat dilakukan, maka fasa pengoptimuman terakhir akan dimulakan. Sekarang, tujuan pengoptimuman terakhir adalah mencari semua kemungkinan pilihan lain untuk melaksanakan pertanyaan dengan cara terbaik. Algoritma fasa pengoptimuman terakhir ialah Microsoft Propriety.

Pelaksana Pertanyaan

Kaedah Executer pertanyaan Kaedah Akses. Ini menyediakan rencana pelaksanaan untuk logik pengambilan data yang diperlukan untuk pelaksanaan. Setelah data diterima dari Storage Engine, hasilnya akan diterbitkan ke lapisan Protokol. Akhirnya, data dihantar kepada pengguna akhir.

Enjin Storan

Kerja Mesin Penyimpanan adalah menyimpan data dalam sistem penyimpanan seperti Disk atau SAN dan mengambil data apabila diperlukan. Sebelum kita menyelami mesin Penyimpanan, mari kita lihat bagaimana data disimpan dalam Pangkalan Data dan jenis fail yang ada.

Fail Data dan Luas:

File Data, secara fizikal menyimpan data dalam bentuk halaman data, dengan setiap halaman data memiliki ukuran 8KB, membentuk unit penyimpanan terkecil di SQL Server. Halaman data ini dikumpulkan secara logik untuk membentuk lanjutan. Tidak ada objek yang diberikan halaman di SQL Server.

Pemeliharaan objek dilakukan melalui luaran. Halaman ini memiliki bagian yang disebut Header Halaman dengan ukuran 96 byte, membawa informasi metadata tentang halaman seperti Jenis Halaman, Nomor Halaman, Ukuran Ruang Terpakai, Ukuran Ruang Bebas, dan Penunjuk ke halaman berikutnya dan halaman sebelumnya , dan lain-lain.

Jenis fail

  1. Fail utama
  • Setiap pangkalan data mengandungi satu fail Utama.
  • Ini menyimpan semua data penting yang berkaitan dengan jadual, paparan, Pencetus, dll.
  • Sambungan adalah. mdf biasanya tetapi boleh memberi lanjutan.
  1. Fail sekunder
  • Database may or may not contains multiple Secondary files.
  • This is optional and contain user-specific data.
  • Extension is .ndf usually but can be of any extension.
  1. Log file
  • Also known as Write ahead logs.
  • Extension is .ldf
  • Used for Transaction Management.
  • This is used to recover from any unwanted instances. Perform important task of Rollback to uncommitted transactions.

Storage Engine has 3 components; let's look into them in detail.

Access Method

It acts as an interface between query executor and Buffer Manager/Transaction Logs.

Access Method itself does not do any execution.

The first action is to determine whether the query is:

  1. Select Statement (DDL)
  2. Non- Select Statement (DDL & DML)

Depending upon the result, the Access Method takes the following steps:

  1. If the query is DDL, SELECT statement, the query is pass to the Buffer Manager for further processing.
  2. And if query if DDL, NON-SELECT statement, the query is pass to Transaction Manager. This mostly includes the UPDATE statement.

Buffer Manager

Buffer manager manages core functions for modules below:

  • Plan Cache
  • Data Parsing: Buffer cache & Data storage
  • Dirty Page

We will learn Plan, Buffer and Data cache in this section. We will cover Dirty pages in the Transaction section.

Plan Cache

  • Existing Query plan: The buffer manager checks if the execution plan is there in the stored Plan Cache. If Yes, then query plan cache and its associated data cache is used.
  • First time Cache plan: Where does existing Plan cache come from?

    If the first-time query execution plan is being run and is complex, it makes sense to store it in in the Plane cache. This will ensure faster availability when the next time SQL server gets the same query. So, it's nothing else but the query itself which Plan execution is being stored if it is being run for the first time.

Data Parsing: Buffer cache & Data Storage

Buffer manager provides access to the data required. Below two approaches are possible depending upon whether data exist in the data cache or not:

Buffer Cache - Soft Parsing:

Buffer Manager looks for Data in Buffer in Data cache. If present, then this Data is used by Query Executor. This improves the performance as the number of I/O operation is reduced when fetching data from the cache as compared to fetching data from Data storage.

Data Storage - Hard Parsing:

If data is not present in Buffer Manager than required Data is searched in Data Storage. If also stores data in the data cache for future use.

Dirty Page

It is stored as a processing logic of Transaction Manager. We will learn in detail in Transaction Manager section.

Transaction Manager

Transaction Manager is invoked when access method determines that Query is a Non-Select statement.

Log Manager

  • Log Manager keeps a track of all updates done in the system via logs in Transaction Logs.
  • Logs have Logs Sequence Number with the Transaction ID and Data Modification Record.
  • This is used for keeping track of Transaction Committed and Transaction Rollback.

Lock Manager

  • During Transaction, the associated data in Data Storage is in the Lock state. This process is handled by Lock Manager.
  • This process ensures data consistency and isolation. Also known as ACID properties.

Execution Process

  • Log Manager start logging and Lock Manager locks the associated data.
  • Data's copy is maintained in the Buffer cache.
  • Copy of data supposed to be updated is maintained in Log buffer and all the events updates data in Data buffer.
  • Pages which store the data is also known as Dirty Pages.
  • Checkpoint and Write-Ahead Logging: This process run and mark all the page from Dirty Pages to Disk, but the page remains in the cache. Frequency is approximately 1 run per minute.But the page is first pushed to Data page of the log file from Buffer log. This is known as Write Ahead Logging.
  • Lazy Writer: The Dirty page can remain in memory. When SQL server observes a huge load and Buffer memory is needed for a new transaction, it frees up Dirty Pages from the cache. It operates on LRU - Least recently used Algorithm for cleaning page from buffer pool to disk.

Summary:

  • Three Type of Client Server Architecture exist: 1) Shared Memory 2) TCP/IP 3)Named Pipes
  • TDS, developed by Sybase and now owned by Microsoft, is a packet which is encapsulated in Network packets for data transfer from the client machine to the server machine.
  • Relational Engine contains three major components:

    CMD Parser: This is responsible for Syntactic and Semantic error & finally generate a Query Tree.

    Optimizer: Optimizer role is to find the cheapest, not the best, cost-effective execution plan.

    Query Executor: Query executer calls Access Method and provides execution plan for data fetching logic required for execution.

  • Three type of files exists Primary file, Secondary file, and Log files.
  • Storage Engine: Has following important components

    Access Method: This Component Determine whether the query is Select or Non-Select Statement. Invokes Buffer and Transfer Manager accordingly.

    Buffer Manager: Buffer manager manages core functions for Plan Cache, Data Parsing & Dirty Page.

    Transaction Manager: It manager Non-Select Transaction dengan bantuan Log dan Lock Manager. Juga, memudahkan pelaksanaan penulisan penulisan Log Ahead dan Lazy.