Mysql buat tampilan dengan index

MySQL adalah aplikasi basis data yang mengatur data dalam tabel, yang pada gilirannya memiliki nilai yang diatur dalam baris dan kolom. Di MySQL, indeks adalah struktur data yang membuat informasi lebih mudah dicari

Tanpa indeks, MySQL membaca seluruh tabel untuk menemukan baris yang diperlukan dan sangat tidak efisien

Panduan ini akan menunjukkan cara membuat indeks di MySQL menggunakan pernyataan

mysql -u username -p

Prasyarat

• Instalasi MySQL yang sudah ada
• Akun pengguna MySQL dengan hak istimewa root atau admin 
• Akses ke baris perintah / jendela terminal
  • Di Linux, klik Aktivitas > Penelusuran > Terminal
  • Di Windows, klik kanan Mulai > Baris Perintah (atau Windows PowerShell)

Apa itu Indeks MySQL? . Ini mempercepat kueri data, tetapi mengorbankan ruang disk dan membaca/menulis

MySQL Membuat Sintaks Indeks

Pernyataan

mysql -u username -p

mysql> CREATE INDEX [some_index] ON [some_table] ([some_column],[other_column]);

• mysql -u username -p

  _2 – Nama indeks

• mysql -u username -p

  3 – Nama tabel dengan kolom yang diindeks

• mysql -u username -p

  4 – Nama kolom yang akan diindeks

Catatan. Berikut ini adalah contoh pernyataan

mysql -u username -p

mysql -u username -p

MySQL Buat Indeks di Tabel Baru

Untuk membuat indeks sekaligus tabel dibuat di MySQL

1. Buka jendela terminal dan masuk ke shell MySQL

mysql -u username -p

2. Buat dan alihkan ke database baru dengan memasukkan perintah berikut

mysql> CREATE DATABASE mytest;
Query OK, 1 row affected (0.01 sec)

mysql; USE mytest;
Database changed

3. Selanjutnya, buat kolom di tabel, dan indeks

mysql> CREATE TABLE example (
col1 INT PRIMARY KEY,
col2 INT NOT NULL,
col3 INT NOT NULL,
col4 VARCHAR(20),
INDEX (col2, col3)
);

Perintah membuat tabel bernama contoh, mengisinya dengan 4 kolom, dan menambahkan indeks untuk kolom 2 dan kolom 3

4. Selanjutnya, tampilkan daftar indeks dari tabel

mysql; SHOW INDEXES FROM example;

Anda akan melihat indeks untuk PRIMARY, COL2, dan COL3. Meskipun PRIMARY tidak ditentukan, MySQL secara otomatis membuat indeks untuk kunci utama

Catatan. Indeks tidak dapat digunakan untuk membuat kunci utama. Namun, ketika tabel dibuat dengan kunci utama, MySQL membuat indeks yang disebut

mysql -u username -p

Tambahkan Indeks ke Tabel MySQL yang Ada

Praktik terbaik MySQL merekomendasikan pembuatan indeks bersamaan dengan pembuatan tabel. Namun, indeks juga dapat ditambahkan ke tabel yang sudah ada. Petunjuk di bawah ini menjelaskan cara menambahkan indeks ke tabel MySQL yang sudah ada

Buat tampilan untuk indeks

    
mysql>
mysql> CREATE   OR REPLACE VIEW INDEXES
    ->         (INDEX_CREATOR, INDEX_NAME, CREATE_TIMESTAMP,
    ->          TABLE_CREATOR, TABLE_NAME, UNIQUE_ID, INDEX_TYPE) AS
    -> SELECT   DISTINCT UPPER(I.INDEX_SCHEMA), UPPER(I.INDEX_NAME),
    ->          T.CREATE_TIME, UPPER(I.TABLE_SCHEMA),
    ->          UPPER(I.TABLE_NAME),
    ->          CASE
    ->             WHEN I.NON_UNIQUE = 0 THEN 'YES'
    ->             ELSE 'NO'
    ->          END,
    ->          I.INDEX_TYPE
    -> FROM     INFORMATION_SCHEMA.STATISTICS AS I,
    ->          INFORMATION_SCHEMA.TABLES AS T
    -> WHERE    I.TABLE_NAME = T.TABLE_NAME
    -> AND      I.TABLE_SCHEMA = T.TABLE_SCHEMA;
Query OK, 0 rows affected (0.00 sec)

mysql>

   
    
    
    
  

Contoh terkait dalam kategori yang sama

Untuk memahami bagaimana MySQL menggunakan indeks, sebaiknya pahami dulu cara kerja dasar dan fitur indeks. Setelah Anda memiliki pemahaman dasar tentang karakteristiknya, Anda dapat mulai membuat pilihan yang lebih cerdas tentang cara yang tepat untuk menggunakannya

Untuk memahami indeks apa yang memungkinkan MySQL untuk melakukannya, sebaiknya pikirkan tentang cara kerja MySQL untuk menjawab kueri. Bayangkan

ALTER TABLE phone_book ADD INDEX (last_name)

SELECT * FROM phone_book WHERE last_name = 'Zawodny'

Tanpa indeks apa pun untuk berkonsultasi, MySQL harus membaca semua catatan di tabel

ALTER TABLE phone_book ADD INDEX (last_name)

ALTER TABLE phone_book ADD INDEX (last_name)

Tetapi dengan buku telepon asli, kita semua tahu cara cepat menemukan seseorang bernama Zawodny. buka Zs di bagian belakang buku dan mulai dari sana. Karena huruf kedua adalah "a", kita tahu bahwa kecocokan apa pun akan berada di atau dekat bagian depan daftar semua nama yang dimulai dengan Z. Metode yang digunakan didasarkan pada pengetahuan tentang data dan cara pengurutannya

Itu curang, bukan? . Alasan Anda dapat menemukan Zawodny begitu cepat adalah karena mereka diurutkan menurut abjad berdasarkan nama belakang. Jadi mudah menemukannya, asalkan Anda tahu ABC Anda, tentu saja

Sebagian besar buku teknis (seperti ini) menyediakan indeks di bagian belakang. Ini memungkinkan Anda untuk menemukan lokasi istilah dan konsep penting dengan cepat karena terdaftar dalam urutan terurut bersama dengan nomor halaman yang sesuai. Perlu tahu di mana mysqlhotcopy dibahas?

Indeks basis data serupa. Sama seperti penulis atau penerbit buku dapat memilih untuk membuat indeks dari konsep dan istilah penting dalam buku, Anda dapat memilih untuk membuat indeks pada kolom tertentu dari tabel database. Dengan menggunakan contoh sebelumnya, Anda dapat membuat indeks pada nama belakang untuk mempercepat pencarian nomor telepon

ALTER TABLE phone_book ADD INDEX (last_name)

Dengan demikian, Anda meminta MySQL untuk membuat daftar terurut dari semua nama belakang di tabel

ALTER TABLE phone_book ADD INDEX (last_name)

Dari sudut pandang server basis data, ada indeks sehingga basis data dapat dengan cepat menghilangkan kemungkinan baris dari kumpulan hasil saat menjalankan kueri. Tanpa indeks apa pun, MySQL (seperti server basis data lainnya) harus memeriksa setiap baris dalam tabel. Tidak hanya memakan waktu, ini menggunakan banyak I/O disk dan dapat mencemari cache disk secara efektif

Di dunia nyata, sangat jarang menemukan data dinamis yang kebetulan diurutkan (dan tetap diurutkan). Buku adalah kasus khusus;

Karena MySQL perlu mempertahankan daftar nilai indeks yang terpisah dan memperbaruinya saat data Anda berubah, Anda benar-benar tidak ingin mengindeks setiap kolom dalam tabel. Indeks adalah trade-off antara ruang dan waktu. Anda mengorbankan beberapa ruang disk ekstra dan sedikit overhead CPU pada setiap kueri

ALTER TABLE phone_book ADD INDEX (last_name)

ALTER TABLE phone_book ADD INDEX (last_name)

ALTER TABLE phone_book ADD INDEX (last_name)

Sebagian besar dokumentasi MySQL menggunakan istilah indeks dan kunci secara bergantian. Mengatakan bahwa

ALTER TABLE phone_book ADD INDEX (last_name)

ALTER TABLE phone_book ADD INDEX (last_name)

ALTER TABLE phone_book ADD INDEX (last_name)

ALTER TABLE phone_book ADD INDEX (last_name)

Indeks memperdagangkan ruang untuk kinerja. Namun terkadang Anda lebih suka tidak memperdagangkan terlalu banyak ruang untuk performa yang Anda kejar. Untungnya, MySQL memberi Anda banyak kendali atas berapa banyak ruang yang digunakan oleh indeks. Mungkin Anda memiliki tabel

ALTER TABLE phone_book ADD INDEX (last_name)

ALTER TABLE phone_book ADD INDEX (last_name)

ALTER TABLE phone_book ADD INDEX (last_name)

Alih-alih mengindeks seluruh nama belakang, Anda mungkin hanya mengindeks 4 byte pertama

ALTER TABLE phone_book ADD INDEX (last_name(4))

Dengan demikian, Anda telah mengurangi kebutuhan ruang untuk porsi data indeks sekitar setengahnya. Imbalannya adalah MySQL tidak dapat menghilangkan banyak baris menggunakan indeks ini. Permintaan seperti

SELECT * FROM phone_book WHERE last_name = 'Smith'

mengambil semua bidang yang dimulai dengan

ALTER TABLE phone_book ADD INDEX (last_name(4))

ALTER TABLE phone_book ADD INDEX (last_name(4))

ALTER TABLE phone_book ADD INDEX (last_name(4))

ALTER TABLE phone_book ADD INDEX (last_name(4))

Seperti banyak mesin basis data relasional, MySQL memungkinkan Anda membuat indeks yang terdiri dari banyak kolom

ALTER TABLE phone_book ADD INDEX (last_name, first_name)

Indeks tersebut dapat meningkatkan kecepatan kueri jika Anda sering membuat kueri semua kolom secara bersamaan dalam klausa

SELECT * FROM phone_book WHERE last_name = 'Smith'

ALTER TABLE phone_book ADD INDEX (last_name(4), first_name(4))

Dalam kedua kasus tersebut, kueri untuk menemukan Josh Woodward dijalankan dengan cepat

SELECT * FROM phone_book
 WHERE last_name = 'Woodward'
   AND first_name = 'Josh'

Memiliki nama belakang dan nama depan yang diindeks bersama berarti MySQL dapat menghilangkan baris berdasarkan kedua bidang, sehingga sangat mengurangi jumlah baris yang harus dipertimbangkan. Lagi pula, ada lebih banyak orang di buku telepon yang nama belakangnya dimulai dengan "Kayu" daripada orang yang nama belakangnya dimulai dengan "Kayu" dan yang nama depannya juga dimulai dengan "Josh. ”

Saat membahas indeks multikolom, Anda mungkin melihat kolom terindeks individual yang disebut sebagai bagian kunci atau “bagian dari kunci. ” Indeks multikolom juga disebut sebagai indeks komposit atau indeks majemuk

Jadi mengapa tidak membuat dua indeks saja, satu di

ALTER TABLE phone_book ADD INDEX (last_name)

SELECT * FROM phone_book WHERE last_name = 'Smith'

SELECT * FROM phone_book WHERE last_name = 'Smith'

Dengan indeks terpisah pada

SELECT * FROM phone_book WHERE last_name = 'Smith'

ALTER TABLE phone_book ADD INDEX (last_name)

ALTER TABLE phone_book ADD INDEX (last_name(4))

Bagaimana nilai urutan MySQL dalam indeks? . MySQL tidak memberi Anda kendali atas penyortiran nilai indeks internalnya. Ini memiliki sedikit alasan untuk. Pada Versi 4. 0, itu melakukan pekerjaan yang baik untuk mengoptimalkan kasus yang menyebabkan kinerja lebih lambat untuk sistem basis data lainnya

Misalnya, beberapa produk basis data dapat menjalankan kueri ini dengan cepat

SELECT * FROM phone_book WHERE last_name = 'Zawodny'
ORDER BY first_name DESC

Dan permintaan ini perlahan

SELECT * FROM phone_book WHERE last_name = 'Zawodny'
ORDER BY first_name ASC

Mengapa? . Dalam kasus pertama, database menggunakan indeks multikolom untuk menemukan semua rekaman yang cocok. Karena catatan sudah disimpan dalam urutan menurun, tidak perlu mengurutkannya. Namun dalam kasus kedua, server menemukan semua catatan yang cocok dan kemudian melakukan penerusan kedua pada baris tersebut untuk mengurutkannya

MySQL cukup pintar untuk "melintasi indeks mundur" bila diperlukan. Ini akan mengeksekusi kedua kueri dengan sangat cepat. Dalam kedua kasus itu tidak perlu mengurutkan catatan

Indeks tidak selalu digunakan untuk menemukan baris yang cocok untuk kueri. Indeks unik menentukan bahwa nilai tertentu hanya dapat muncul sekali dalam kolom tertentu. [] Dalam contoh buku telepon, Anda dapat membuat indeks unik pada

SELECT * FROM phone_book WHERE last_name = 'Smith'

ALTER TABLE phone_book ADD UNIQUE (phone_number)

Indeks unik melayani tujuan ganda. Ini berfungsi seperti indeks lainnya saat Anda melakukan kueri berdasarkan nomor telepon

ALTER TABLE phone_book ADD INDEX (last_name)

Namun, ini juga memeriksa setiap nilai saat mencoba menyisipkan atau memperbarui catatan untuk memastikan bahwa nilai tersebut belum ada. Dengan cara ini, indeks unik bertindak sebagai kendala

Indeks unik menggunakan ruang sebanyak indeks tidak unik. Nilai setiap kolom serta lokasi catatan disimpan. Ini bisa sia-sia jika Anda menggunakan indeks unik sebagai batasan dan tidak pernah sebagai indeks. Dengan kata lain, Anda dapat mengandalkan indeks unik untuk menerapkan keunikan tetapi tidak pernah menulis kueri yang menggunakan nilai unik. Dalam hal ini, MySQL tidak perlu menyimpan lokasi setiap record dalam indeks. Anda tidak akan pernah menggunakannya

Sayangnya, tidak ada cara untuk menandakan niat Anda ke MySQL. Di masa mendatang, kami kemungkinan akan menemukan fitur yang diperkenalkan untuk kasus khusus ini. Mesin penyimpanan MyISAM sudah memiliki dukungan untuk kolom unik tanpa indeks (menggunakan sistem berbasis hash), tetapi mekanismenya belum diekspos pada level SQL

Clustered dan indeks sekunder

Dengan tabel MyISAM, indeks disimpan dalam file yang sepenuhnya terpisah yang berisi daftar kunci primer (dan mungkin sekunder) dan nilai yang mewakili offset byte untuk catatan. Ini memastikan MySQL dapat menemukan dan kemudian dengan cepat melompat ke titik itu di dalam database untuk menemukan catatan. MySQL harus menyimpan indeks dengan cara ini karena catatan disimpan dalam urutan yang pada dasarnya acak

Dengan indeks berkerumun, kunci utama dan catatan itu sendiri "berkumpul" bersama, dan semua catatan disimpan dalam urutan kunci utama. InnoDB menggunakan indeks berkerumun. Dalam dunia Oracle, clustered indexes dikenal sebagai "index-organized tables", yang dapat membantu Anda mengingat hubungan antara primary key dan pengurutan baris.

Saat data Anda hampir selalu dicari melalui kunci utamanya, indeks berkerumun dapat membuat pencarian menjadi sangat cepat. Dengan indeks MyISAM standar, ada dua pencarian, satu ke indeks, dan yang kedua ke tabel itu sendiri melalui lokasi yang ditentukan dalam indeks. Dengan indeks berkerumun, ada satu pencarian yang mengarah langsung ke rekaman yang dimaksud

Beberapa operasi membuat indeks berkerumun menjadi kurang efektif. Misalnya, pertimbangkan ketika indeks sekunder sedang digunakan. Kembali ke contoh buku telepon kita, misalkan Anda memiliki

ALTER TABLE phone_book ADD INDEX (last_name)

SELECT * FROM phone_book WHERE last_name = 'Smith'

ALTER TABLE phone_book ADD INDEX (last_name)

MySQL memindai indeks

SELECT * FROM phone_book WHERE last_name = 'Smith'

ALTER TABLE phone_book ADD INDEX (last_name, first_name)

ALTER TABLE phone_book ADD INDEX (last_name, first_name)

ALTER TABLE phone_book ADD INDEX (last_name)

Dengan kata lain, pencarian berdasarkan kunci utama Anda terjadi sangat cepat, dan pencarian berdasarkan indeks sekunder pada dasarnya terjadi pada kecepatan yang sama seperti pencarian indeks MyISAM.

Tetapi dalam keadaan yang benar (atau lebih tepatnya, salah), indeks berkerumun sebenarnya dapat menurunkan kinerja. Saat Anda menggunakannya bersama dengan indeks sekunder, Anda harus mempertimbangkan dampak gabungan pada penyimpanan. Indeks sekunder menunjuk ke kunci utama daripada baris. Oleh karena itu, jika Anda mengindeks pada nilai yang sangat besar dan memiliki beberapa indeks sekunder, Anda akan mendapatkan banyak salinan duplikat dari indeks primer tersebut, pertama sebagai indeks berkerumun yang disimpan di samping catatan itu sendiri, tetapi sekali lagi sebanyak yang Anda miliki . Dengan nilai kecil sebagai kunci utama, ini mungkin tidak terlalu buruk, tetapi jika Anda menggunakan sesuatu yang berpotensi panjang, seperti URL, penyimpanan berulang kunci utama pada disk ini dapat menyebabkan masalah penyimpanan.

Kondisi lain yang kurang umum tetapi sama bermasalahnya terjadi ketika data diubah sedemikian rupa sehingga kunci utama diubah pada catatan. Ini adalah fungsi indeks berkerumun yang paling mahal. Beberapa hal dapat terjadi untuk membuat operasi ini menjadi hit kinerja yang lebih parah

• Ubah catatan yang dimaksud sesuai dengan permintaan yang dikeluarkan

• Tentukan primary key baru untuk record tersebut, berdasarkan record data yang diubah

• Pindahkan catatan yang disimpan sehingga catatan yang dimaksud dipindahkan ke lokasi yang tepat di tablespace

• Perbarui semua indeks sekunder yang mengarah ke kunci primer tersebut

Seperti yang Anda bayangkan, jika Anda mengubah kunci utama untuk sejumlah catatan, perintah

ALTER TABLE phone_book ADD INDEX (last_name)

Indeks unik versus kunci utama

Jika Anda berasal dari database relasional lain, Anda mungkin bertanya-tanya apa perbedaan antara kunci utama dan indeks unik di MySQL. Seperti biasa, itu tergantung. Di tabel MyISAM, hampir tidak ada perbedaan. Satu-satunya hal yang istimewa tentang kunci utama adalah tidak boleh berisi nilai NULL. Kunci utama hanyalah

ALTER TABLE phone_book ADD INDEX (last_name, first_name)

ALTER TABLE phone_book ADD INDEX (last_name, first_name)

ALTER TABLE phone_book ADD INDEX (last_name, first_name)

ALTER TABLE phone_book ADD INDEX (last_name, first_name)

ALTER TABLE phone_book ADD INDEX (last_name, first_name)

Tabel InnoDB dan BDB membutuhkan kunci utama untuk setiap tabel. Namun, tidak ada persyaratan yang Anda tentukan. Jika tidak, mesin penyimpanan secara otomatis menambahkan kunci utama tersembunyi untuk Anda. Dalam kedua kasus, kunci utama hanya menambah nilai numerik, mirip dengan kolom

ALTER TABLE phone_book ADD INDEX (last_name(4), first_name(4))

ALTER TABLE phone_book ADD INDEX (last_name(4), first_name(4))

ALTER TABLE phone_book ADD INDEX (last_name(4), first_name(4))

Seringkali sulit untuk mengingat bahwa SQL menggunakan logika tristate saat melakukan operasi logis. Kecuali kolom dinyatakan

ALTER TABLE phone_book ADD INDEX (last_name, first_name)

ALTER TABLE phone_book ADD INDEX (last_name, first_name)

Pemrogram sering menganggap NULL sebagai tidak terdefinisi atau tidak dikenal. Ini adalah cara memberi tahu server basis data “nilai yang tidak diketahui ada di sini. ” Jadi, bagaimana nilai NULL memengaruhi indeks?

Nilai NULL dapat digunakan dalam indeks normal (tidak unik). Ini berlaku untuk semua server basis data. Namun, tidak seperti banyak server basis data, MySQL memungkinkan Anda menggunakan nilai NULL dalam indeks unik. [] Anda dapat menyimpan nilai NULL sebanyak yang Anda inginkan dalam indeks tersebut. Ini mungkin tampak sedikit berlawanan dengan intuisi, tetapi itulah sifat NULL. Karena NULL mewakili nilai yang tidak terdefinisi, MySQL perlu menegaskan bahwa semua nilai NULL adalah sama jika hanya mengizinkan satu nilai dalam indeks unik

Untuk membuatnya sedikit lebih menarik, nilai NULL mungkin hanya muncul sekali sebagai kunci utama. Mengapa? . Ini adalah salah satu dari sedikit perbedaan kunci utama dari indeks unik di MySQL. Dan, jika Anda bertanya-tanya, mengizinkan nilai NULL dalam indeks benar-benar tidak memengaruhi performa

Bisakah kita membuat tampilan pada indeks?

Bagaimana cara membuat tampilan yang diindeks?

Bisakah tampilan menggunakan indeks?

Bisakah tampilan SQL memiliki indeks?