Semakan Skema ySQL Teratas untuk Meningkatkan Prestasi Pangkalan Data

Skema pangkalan data mentakrifkan struktur logik pangkalan data anda, termasuk jadual, lajur, perhubungan, indeks dan kekangan yang membentuk cara data disusun dan diakses. Ia bukan sahaja tentang cara data disimpan tetapi juga cara ia berinteraksi dengan pertanyaan, urus niaga dan operasi lain.

Semakan ini boleh membantu anda mengetahui sebarang masalah baharu atau masalah yang berlarutan sebelum ia menjadi isu yang lebih besar. Anda boleh menyelam lebih dalam ke dalam semakan skema ini di bawah dan mengetahui dengan tepat cara untuk menyelesaikan sebarang isu jika pangkalan data anda tidak lulus. Cuma ingat, sebelum anda membuat sebarang perubahan skema, sentiasa sandarkan data anda untuk melindungi daripada potensi risiko yang mungkin berlaku semasa pengubahsuaian.

1. Semakan Kunci Utama (Kunci Utama Tiada)

Kunci utama ialah bahagian kritikal mana-mana jadual, mengenal pasti setiap baris secara unik dan membolehkan pertanyaan yang cekap. Tanpa kunci utama, jadual mungkin mengalami masalah prestasi dan alatan tertentu seperti utiliti replikasi dan perubahan skema mungkin tidak berfungsi dengan betul.

Terdapat beberapa isu yang boleh anda elakkan dengan menentukan kunci utama semasa mereka bentuk skema:

1. Jika tiada kunci utama atau unik ditentukan, MySQL mencipta kunci dalaman, yang tidak boleh diakses untuk digunakan.
2. Ketiadaan kunci utama boleh melambatkan prestasi replikasi, terutamanya dengan replikasi berasaskan baris atau bercampur.
3. Kunci utama membenarkan pengarkiban dan pembersihan data boleh skala. Alat seperti pt-online-schema-change memerlukan kunci utama atau unik.
4. Kunci utama mengenal pasti baris secara unik, yang penting dari perspektif aplikasi.

Contoh

Untuk mencipta kekangan KUNCI UTAMA pada lajur "ID" apabila jadual sudah dibuat, gunakan SQL berikut:

ALTER TABLE Persons ADD PRIMARY KEY (ID);

Untuk menentukan kunci utama pada berbilang lajur:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

Nota: Jika anda menggunakan perintah ALTER TABLE, maka lajur kunci utama mestilah telah diisytiharkan tidak mengandungi nilai NULL apabila jadual mula-mula dibuat.

2. Pemeriksaan Enjin Meja (Enjin Meja Dihentikan)

Enjin storan MyISAM telah ditamatkan, dan jadual yang masih menggunakannya harus dipindahkan ke InnoDB. InnoDB ialah enjin lalai dan disyorkan untuk kebanyakan kes penggunaan kerana prestasi unggulnya, keupayaan pemulihan data dan sokongan transaksi. Berhijrah daripada MyISAM ke InnoDB boleh meningkatkan prestasi secara mendadak dalam aplikasi berat tulis, memberikan toleransi kesalahan yang lebih baik dan membolehkan ciri MySQL yang lebih maju seperti carian teks penuh dan kunci asing.

Mengapa InnoDB diutamakan:

• Keupayaan pemulihan ranap membolehkan ia pulih secara automatik daripada pelayan pangkalan data atau ranap hos tanpa kerosakan data.
• Hanya mengunci baris yang dipengaruhi oleh pertanyaan, membolehkan prestasi yang lebih baik dalam persekitaran konkurensi tinggi.
• Menyimpan data dan mengindeks dalam memori, yang lebih disukai untuk beban kerja yang berat dibaca.
• Mematuhi ACID sepenuhnya, memastikan integriti data dan menyokong transaksi.
• Enjin InnoDB menerima sebahagian besar tumpuan daripada komuniti pembangunan MySQL, menjadikannya enjin yang paling terkini dan disokong dengan baik.

Bagaimana untuk Berhijrah ke InnoDB

ALTER TABLE Persons ADD PRIMARY KEY (ID);

3. Semakan Pengumpulan Jadual (Pengumpulan Campuran)

Menggunakan himpunan berbeza merentas jadual atau malah dalam jadual boleh membawa kepada masalah prestasi, terutamanya semasa perbandingan dan cantuman rentetan. Jika himpunan dua lajur rentetan berbeza, MySQL mungkin perlu menukar rentetan pada masa jalan, yang boleh menghalang indeks daripada digunakan dan memperlahankan pertanyaan anda.

Apabila anda membuat perubahan pada jadual pengumpulan bercampur, beberapa masalah boleh timbul:

• Pengumpulan boleh berbeza pada peringkat lajur, jadi ketidakpadanan pada peringkat jadual tidak akan menyebabkan isu jika lajur yang berkaitan dalam gabungan mempunyai koleksi yang sepadan.
• Menukar susunan jadual, terutamanya dengan suis set charset, bukan selalu mudah. Penukaran data mungkin diperlukan dan aksara yang tidak disokong boleh bertukar menjadi data yang rosak.
• Jika anda tidak menentukan pengumpulan atau set aksara semasa membuat jadual, ia mewarisi lalai pangkalan data. Jika tiada yang ditetapkan pada peringkat pangkalan data, lalai pelayan akan digunakan. Untuk mengelakkan isu ini, adalah penting untuk menyeragamkan pengumpulan merentas keseluruhan set data anda, terutamanya untuk lajur yang kerap digunakan dalam operasi gabungan.

Cara Mengubah Tetapan Pengumpulan

Sebelum membuat sebarang perubahan pada tetapan pengumpulan pangkalan data anda, uji pendekatan anda dalam persekitaran bukan pengeluaran untuk mengelakkan akibat yang tidak diingini. Jika anda tidak pasti tentang apa-apa, sebaiknya berunding dengan DBA.

Dapatkan semula set aksara lalai dan pengumpulan untuk semua pangkalan data:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

Semak pengumpulan jadual tertentu:

ALTER TABLE <table_name> ENGINE=InnoDB;

Cari set aksara lalai pelayan:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

Cari pengumpulan lalai pelayan:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

Kemas kini pengumpulan untuk pangkalan data tertentu:

SELECT @@GLOBAL.character_set_server;

Kemas kini pengumpulan untuk jadual tertentu:

SELECT @@GLOBAL.collation_server;

4. Semak Set Aksara Meja (Set Aksara Campuran)

Set aksara campuran adalah serupa dengan himpunan campuran kerana ia boleh membawa kepada isu prestasi dan keserasian. Set aksara bercampur berlaku apabila lajur atau jadual yang berbeza menggunakan format pengekodan yang berbeza untuk menyimpan data.

• Set aksara bercampur boleh menjejaskan prestasi penyertaan pada lajur rentetan dengan menghalang penggunaan indeks atau memerlukan penukaran nilai.
• Set aksara boleh ditakrifkan pada peringkat lajur dan selagi lajur yang terlibat dalam gabungan mempunyai set aksara yang sepadan, prestasi tidak akan terjejas oleh ketidakpadanan pada peringkat jadual.
• Menukar set aksara jadual mungkin melibatkan penukaran data, yang boleh membawa kepada data rosak jika aksara yang tidak disokong ditemui.
• Jika tiada set aksara atau penyusunan dinyatakan, jadual mewarisi lalai pangkalan data dan pangkalan data mewarisi set aksara dan penyusunan lalai pelayan.

Cara Menukar Tetapan Aksara

Sebelum melaraskan tetapan aksara pangkalan data anda, pastikan anda menguji perubahan dalam persekitaran pementasan untuk mengelakkan sebarang isu yang tidak dijangka. Jika anda tidak pasti tentang sebarang langkah, rujuk DBA untuk mendapatkan panduan.

Dapatkan semula set aksara lalai dan pengumpulan untuk semua pangkalan data:

ALTER TABLE Persons ADD PRIMARY KEY (ID);

Dapatkan set aksara lajur:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

Cari set aksara lalai pelayan:

ALTER TABLE <table_name> ENGINE=InnoDB;

Cari pengumpulan lalai pelayan:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

Untuk melihat struktur jadual:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

Contoh output:

SELECT @@GLOBAL.character_set_server;

Untuk menukar set aksara lajur:

SELECT @@GLOBAL.collation_server;

5. Semakan Penambahan Auto Lajur(Jenis Lajur Penambahan Auto)

Untuk jadual yang dijangka berkembang selama-lamanya dan menggunakan kenaikan automatik untuk kunci utama, disyorkan untuk beralih kepada jenis data UNSIGNED BIGINT. Ini membolehkan lajur mengendalikan julat nilai yang lebih besar, menghalang keperluan untuk perubahan jadual yang mahal pada masa hadapan sebaik sahaja nilai maksimum dicapai. Dengan menyatakan UNSIGNED, hanya nilai positif disimpan, dengan berkesan menggandakan julat jenis data.

Cara Menukar Tetapan Aksara

Untuk mengubah suai jenis lajur kepada UNSIGNED BIGINT:

ALTER DATABASE <db-name> COLLATE=<collation-name>;

6. Semakan Kunci Asing Jadual (Kewujudan kunci asing)

Kunci asing menawarkan ketekalan data dengan mengekalkan hubungan antara jadual ibu bapa dan anak, tetapi ia juga memberi kesan kepada prestasi pangkalan data. Setiap kali operasi tulis berlaku, carian tambahan diperlukan untuk mengesahkan integriti data berkaitan. Ini boleh menyebabkan kelembapan, terutamanya dalam persekitaran trafik tinggi.

Jika prestasi membimbangkan, anda mungkin ingin mempertimbangkan untuk mengalih keluar kunci asing, terutamanya dalam senario di mana ketekalan data boleh dikendalikan pada peringkat aplikasi.

Cara Mengeluarkan Kunci Asing

Untuk melepaskan kekangan kunci asing daripada jadual:

ALTER TABLE Persons ADD PRIMARY KEY (ID);

7. Semakan Indeks Pendua

Indeks pendua dalam MySQL menggunakan ruang cakera yang tidak diperlukan dan mencipta overhed tambahan semasa operasi tulis, kerana setiap indeks mesti dikemas kini. Ini boleh merumitkan pengoptimuman pertanyaan, yang berpotensi membawa kepada rancangan pelaksanaan yang tidak cekap tanpa menawarkan sebarang faedah sebenar.

Kenal pasti dan alih keluar indeks pendua untuk menyelaraskan pengoptimuman pertanyaan dan mengurangkan overhed. Tetapi pastikan bahawa indeks tidak digunakan untuk pertanyaan kritikal sebelum mengalih keluarnya.

8. Semakan Indeks Tidak Digunakan

Indeks yang tidak digunakan dalam MySQL boleh memberi kesan negatif terhadap prestasi pangkalan data dengan menggunakan ruang cakera, meningkatkan overhed pemprosesan semasa memasukkan, mengemas kini dan memadam serta memperlahankan operasi keseluruhan. Walaupun indeks berharga untuk mempercepatkan pertanyaan, indeks yang tidak digunakan boleh menimbulkan tekanan yang tidak perlu pada sistem anda.
Faedah tambahan untuk mengalih keluar indeks yang tidak digunakan atau pendua termasuk:

• Dengan indeks yang lebih sedikit, pengoptimum MySQL mempunyai lebih sedikit pilihan untuk dinilai, memudahkan pelaksanaan pertanyaan dan mengurangkan penggunaan CPU/memori.
• Mengalih keluar indeks yang tidak digunakan membebaskan ruang cakera berharga yang boleh digunakan untuk data yang lebih kritikal, juga meningkatkan kecekapan I/O.
• Tugas penyelenggaraan indeks, seperti membina semula atau menyusun semula, menjadi lebih pantas dan kurang intensif sumber apabila bilangan indeks diminimumkan. Ini membawa kepada operasi yang lebih lancar, terutamanya dalam persekitaran yang memerlukan masa operasi 24/7.

Untuk mengenal pasti diindeks yang tidak digunakan dalam MySQL atau MariabDB sila gunakan untuk mengikuti pernyataan SQL:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

Cara Mengalih Keluar Indeks Tidak Digunakan atau Pendua

Dalam MySQL 8.0 dan lebih baru, anda boleh membuat indeks tidak kelihatan untuk menguji sama ada ia diperlukan tanpa menggugurkannya sepenuhnya:

ALTER TABLE <table_name> ENGINE=InnoDB;

Jika prestasi kekal tidak terjejas, indeks boleh digugurkan dengan selamat:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

Anda boleh mengembalikan semula indeks kepada kelihatan jika perlu:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

Semakan Skema Kini Tersedia dengan Releem

Dengan kemas kini terkini, Releem kini menyertakan pemeriksaan kesihatan skema yang komprehensif. Semakan ini memberikan cerapan masa nyata tentang integriti struktur pangkalan data anda, bersama-sama dengan pengesyoran yang boleh diambil tindakan untuk membetulkan sebarang isu yang dikesan.

Dengan mengautomasikan proses pemantauan skema, Releem mengambil tekaan daripada semakan manual, menjimatkan banyak masa dan usaha jurutera pangkalan data. Daripada menghabiskan berjam-jam mengerjakan butiran skema, anda kini boleh menumpukan pada tugasan yang lebih mendesak.

Atas ialah kandungan terperinci Semakan Skema ySQL Teratas untuk Meningkatkan Prestasi Pangkalan Data. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!