Prestasi PostgreSQL & Pengoptimuman Pertanyaan · Pelajaran

Bila dan Cara Mengindeks

Pelajari amalan terbaik untuk menentukan lajur yang perlu diindeks dan cara mengelakkan pengindeksan berlebihan.

Pelajaran 3 daripada 411 langkah

Bila dan Cara Mengindeks ialah pelajaran Prestasi PostgreSQL & Pengoptimuman Pertanyaan percuma di CoddyKit. Ini ialah pelajaran 3 daripada 4. Sebanyak 3 pelajaran dalam laluan pembelajaran ini boleh dibaca sepenuhnya secara percuma — selepas itu, CoddyKit PRO membuka akses kepada semua pelajaran, serta latihan praktikal dengan penyunting kod terbina dalam dan tutor kecerdasan buatan yang tersedia 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran Prestasi PostgreSQL & Pengoptimuman Pertanyaan, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus Prestasi PostgreSQL & Pengoptimuman Pertanyaan merangkumi sejumlah 4 pelajaran.

Pengindeksan Bijak Bermula di Sini

Indeks ialah alat yang berkuasa untuk mempercepatkan pertanyaan PostgreSQL. Namun, indeks bukanlah sesuatu yang ajaib dan menambahnya secara membuta tuli sebenarnya boleh menjejaskan prestasi!

Dalam pelajaran ini, kita akan mempelajari seni pengindeksan bijak: bila hendak mencipta indeks, jenis yang hendak digunakan dan cara mengelakkan perangkap lazim seperti pengindeksan berlebihan.

Mengindeks Klausa WHERE Anda

Sebab paling lazim untuk mencipta indeks adalah untuk mempercepatkan carian dalam klausa WHERE anda. Jika anda kerap menapis data berdasarkan lajur tertentu, indeks pada lajur tersebut boleh mengurangkan masa pertanyaan dengan ketara.

Anggaplah ia seperti indeks mengikut abjad dalam buku. Daripada mengimbas setiap halaman, anda terus pergi ke bahagian yang berkaitan.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  name VARCHAR(255)
);
INSERT INTO users (email, name) VALUES
('alice@example.com', 'Alice'),
('bob@example.com', 'Bob'),
('charlie@example.com', 'Charlie');

-- To make this query fast, index 'email'
-- CREATE INDEX idx_users_email ON users (email);
SELECT * FROM users WHERE email = 'alice@example.com';

Indeks untuk JOIN dan ORDER BY

Indeks bukan sahaja membantu penapisan; indeks juga penting untuk operasi JOIN yang cekap dan penyusunan hasil dengan ORDER BY. Apabila menggabungkan dua jadual, indeks pada lajur cantuman membantu PostgreSQL memadankan baris dengan cepat.

Begitu juga, indeks pada lajur yang digunakan dalam ORDER BY membolehkan PostgreSQL mendapatkan data yang telah disusun secara terus, sekali gus mengelakkan operasi penyusunan yang mahal.

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255)
);
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT,
  order_date DATE
);
INSERT INTO customers (name) VALUES ('Alice'), ('Bob');
INSERT INTO orders (customer_id, order_date) VALUES
(1, '2023-01-01'), (2, '2023-01-02'), (1, '2023-01-05');

-- To speed up this query, index customer_id and order_date
-- CREATE INDEX idx_orders_customer_id ON orders (customer_id);
-- CREATE INDEX idx_orders_order_date ON orders (order_date);
SELECT c.name, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.id
ORDER BY o.order_date DESC;

Kardinaliti: Lebih Banyak Nilai Unik, Lebih Baik

Kardinaliti merujuk kepada bilangan nilai unik dalam sesuatu lajur. Lajur dengan kardinaliti tinggi (banyak nilai unik, seperti user_id atau email) biasanya merupakan calon yang sangat baik untuk diindeks.

Indeks pada lajur berkardinaliti rendah (sedikit nilai unik, seperti bendera boolean atau gender) selalunya kurang berkesan kerana pangkalan data mungkin masih perlu mengimbas sebahagian besar jadual.

Indeks Berbilang Lajur: Susunan Itu Penting

Adakalanya, pertanyaan anda menapis atau menyusun berdasarkan beberapa lajur. Indeks berbilang lajur (atau komposit) boleh menangani keadaan ini. Susunan lajur dalam indeks komposit amat penting disebabkan peraturan "awalan paling kiri".

Indeks pada (A, B, C) boleh membantu pertanyaan pada A, (A, B) atau (A, B, C).
Indeks ini biasanya tidak membantu pertanyaan yang hanya menggunakan B, C atau (B, C).

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  category VARCHAR(50),
  price DECIMAL(10, 2),
  color VARCHAR(20)
);
INSERT INTO products (category, price, color) VALUES
('Electronics', 599.99, 'Black'),
('Books', 25.00, 'Red'),
('Electronics', 120.00, 'Silver');

-- Create a multi-column index
CREATE INDEX idx_prod_cat_price ON products (category, price);

-- This query uses the index efficiently
SELECT * FROM products
WHERE category = 'Electronics' AND price > 100;

Indeks Separa: Menyasarkan Subset

Indeks separa ialah indeks yang dicipta pada subset baris dalam jadual dan ditakrifkan oleh klausa WHERE. Indeks ini boleh menjadi lebih kecil, lebih pantas untuk diselenggarakan dan lebih cekap bagi pertanyaan yang hanya menyasarkan subset data tertentu itu.

Indeks ini sangat sesuai untuk jadual yang hanya mempunyai peratusan kecil baris yang kerap dipertanyaan dengan cara tertentu (contohnya, pengguna "aktif" atau tugasan "tertunda").

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  status VARCHAR(20),
  due_date DATE
);
INSERT INTO tasks (status, due_date) VALUES
('pending', '2023-12-31'),
('completed', '2023-11-15'),
('pending', '2024-01-31'),
('archived', '2023-10-01');

-- Index only pending tasks, smaller and faster
CREATE INDEX idx_pending_tasks ON tasks (due_date) WHERE status = 'pending';

-- This query uses the partial index
SELECT * FROM tasks WHERE status = 'pending' AND due_date < '2024-01-01';

Indeks Ungkapan: Nilai Terhitung

Indeks ungkapan membolehkan anda mencipta indeks pada hasil fungsi atau ungkapan, bukan sekadar pada nilai mentah sesuatu lajur. Indeks ini amat berguna untuk pertanyaan yang mengubah data sebelum perbandingan dibuat.

Penggunaan lazim termasuk carian tanpa mengira huruf besar kecil (menggunakan LOWER() atau UPPER()) atau mengindeks sebahagian rentetan atau tarikh.

CREATE TABLE contacts (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255)
);
INSERT INTO contacts (email) VALUES
('JOHN.DOE@example.com'),
('jane.doe@example.com'),
('peter.smith@example.com');

-- Index for case-insensitive email searches
CREATE INDEX idx_email_lower ON contacts (LOWER(email));

-- This query uses the expression index
SELECT * FROM contacts WHERE LOWER(email) = 'john.doe@example.com';

Bila TIDAK Perlu Mengindeks: Perangkapnya

Tidak setiap lajur memerlukan indeks. Berikut ialah beberapa keadaan apabila indeks mungkin tidak membantu atau malah menjejaskan prestasi:

Kardinaliti Rendah: Lajur dengan sangat sedikit nilai unik (contohnya, bendera boolean is_active) selalunya tidak banyak mendapat manfaat.
Jadual Kecil: Bagi jadual yang hanya mempunyai beberapa ratus baris, imbasan seluruh jadual selalunya lebih pantas daripada carian melalui indeks.
Lajur yang Jarang Dipertanyaan: Jika lajur jarang digunakan dalam klausa WHERE, JOIN atau ORDER BY, indeks mungkin tidak diperlukan.

Mengelakkan Pengindeksan Berlebihan

Anda mungkin tergoda untuk mengindeks segala-galanya, tetapi pengindeksan berlebihan ialah masalah sebenar. Setiap indeks mempunyai overhed:

Prestasi Penulisan: Setiap operasi INSERT, UPDATE atau DELETE juga mesti mengemas kini semua indeks yang berkaitan, lalu memperlahankan penulisan.
Ruang Cakera: Indeks menggunakan ruang cakera, kadangkala dalam jumlah yang besar.
Overhed Perancang Pertanyaan: Terlalu banyak indeks boleh mengelirukan perancang pertanyaan, lalu menyukarkan PostgreSQL memilih pelan yang optimum.

Usahakan pendekatan yang seimbang: indekskan perkara yang benar-benar diperlukan.

Amalan Terbaik Pengindeksan

Antara senario berikut, yang manakah biasanya merupakan calon yang baik untuk penciptaan indeks dalam PostgreSQL?

Jawab dalam pelajaran interaktif

Imbas Kembali: Mengindeks dengan Bijak

Anda telah mempelajari bahawa pengindeksan bukanlah tentang mengindeks segala-galanya, tetapi tentang membuat pilihan yang strategik. Indeks penting untuk mempercepatkan klausa WHERE, JOIN dan ORDER BY, khususnya pada lajur dengan kardinaliti tinggi.

Ingatlah untuk mempertimbangkan indeks separa dan indeks ungkapan bagi keperluan tertentu, serta sentiasa mengambil kira kos pengindeksan berlebihan. Matlamatnya adalah untuk mengoptimumkan bacaan tanpa mengorbankan prestasi penulisan secara berlebihan atau menggunakan sumber secara berlebihan.

Percuma untuk bermula

Pelajari SQL dengan tutor kecerdasan buatan — percuma

Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.

Kursus: 22
Pelajaran: 88

Mula belajar secara percuma Lihat kursus penuh

Soalan Lazim

Adakah pelajaran “Bila dan Cara Mengindeks” percuma?

Ya — sebanyak 3 pelajaran dalam laluan pembelajaran Prestasi PostgreSQL & Pengoptimuman Pertanyaan, termasuk “Bila dan Cara Mengindeks”, boleh dibaca sepenuhnya secara percuma di web ini. Selepas itu, CoddyKit PRO membuka akses kepada semua pelajaran, serta latihan interaktif dengan penyunting kod terbina dalam dan tutor kecerdasan buatan yang tersedia 24/7. Kursus Prestasi PostgreSQL & Pengoptimuman Pertanyaan merangkumi sejumlah 4 pelajaran.

Apakah yang akan saya pelajari dalam “Bila dan Cara Mengindeks”?

Pelajari amalan terbaik untuk menentukan lajur yang perlu diindeks dan cara mengelakkan pengindeksan berlebihan. Anda berlatih Prestasi PostgreSQL & Pengoptimuman Pertanyaan menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.

Adakah saya memerlukan pengalaman untuk memulakan Prestasi PostgreSQL & Pengoptimuman Pertanyaan?

Tiada pengalaman terdahulu diperlukan. Pembelajaran Prestasi PostgreSQL & Pengoptimuman Pertanyaan di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 3 daripada 4.

Berapa lamakah pelajaran “Bila dan Cara Mengindeks” diambil?

Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.

Bolehkah saya menulis dan menjalankan kod dalam pelajaran Prestasi PostgreSQL & Pengoptimuman Pertanyaan ini?

Ya. Setiap pelajaran Prestasi PostgreSQL & Pengoptimuman Pertanyaan menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.

Semua pelajaran dalam kursus ini

← Kembali ke Prestasi PostgreSQL & Pengoptimuman Pertanyaan