Apakah kaedah pengendalian yang berkaitan dengan pengoptimuman prestasi MySQL pangkalan data dan pertanyaan kompleks?

Pengoptimuman indeks

Indeks ialah kunci untuk mempercepatkan pertanyaan dalam MySQL. Jika indeks direka bentuk dengan betul, ia boleh meningkatkan kecekapan pertanyaan dengan berkesan, sebaliknya, jika ia direka bentuk secara tidak betul, kecekapan pertanyaan mungkin terjejas.

Berikut ialah beberapa petua pengoptimuman indeks biasa:

• Gunakan lebih sedikit indeks dan elakkan membuat terlalu banyak indeks, kerana penciptaan indeks mengurangkan prestasi tulis.

• Pilih jenis data yang sesuai, seperti menggunakan kekunci utama jenis integer dan kekunci asing, yang lebih cekap daripada menggunakan kekunci utama jenis UUID dan kekunci asing.

• Secara amnya, adalah perlu untuk memastikan selektiviti indeks, iaitu nisbah bilangan nilai berbeza dalam indeks kepada bilangan nilai berbeza dalam jadual adalah lebih tinggi daripada ambang, yang biasanya kira-kira 10%. Jika selektiviti indeks terlalu rendah, kesan pengoptimuman indeks pada pertanyaan akan menjadi sangat terhad.

• Elakkan menggunakan fungsi yang MySQL tidak menyokong dalam pertanyaan, kerana ini akan menghalang penggunaan indeks.

• Gunakan indeks penutup, iaitu, anda hanya perlu mengembalikan hasil pertanyaan melalui indeks tanpa perlu mengakses lajur lain jadual. Ini boleh meningkatkan prestasi pertanyaan dengan sangat baik.

• Dengan membahagikan jadual besar, membahagikan jadual kepada berbilang sub-jadual dan menyimpan data dalam sub-jadual yang berbeza mengikut kunci partition, operasi pertanyaan dan padam boleh dibuat dengan lebih cekap .

Buat indeks:

CREATE INDEX idx_user_email ON user (email);

Gunakan indeks:

SELECT name FROM user WHERE email = 'example@example.com';

Pengoptimuman pertanyaan

Pertanyaan ialah salah satu operasi yang paling biasa dalam MySQL satu. Untuk meningkatkan kecekapan pertanyaan, beberapa teknik pengoptimuman pertanyaan mesti diikuti.

Berikut ialah beberapa petua pengoptimuman pertanyaan biasa:

• Gunakan LIMIT untuk mengehadkan hasil pertanyaan untuk mengelak daripada mengembalikan terlalu banyak baris.

• Gunakan subquery EXISTS atau NOT EXISTS dalam pertanyaan bukannya IN atau NOT IN subquery.

• Elakkan menggunakan klausa LIKE dalam pertanyaan, terutamanya apabila aksara kad bebas muncul pada permulaan klausa LIKE.

• Gunakan UNION atau UNION ALL untuk menggabungkan berbilang hasil pertanyaan dan elakkan menggunakan subkueri.

• Gunakan GROUP BY dan fungsi agregat untuk mengagregat data dan bukannya menggunakan kata kunci DISTINCT.

• Elakkan menggunakan klausa ORDER BY dalam pertanyaan, terutamanya apabila berurusan dengan jumlah data yang besar.

• Apabila menggunakan operasi JOIN, gunakan operasi INNER JOIN dan bukannya operasi LEFT JOIN atau RIGHT JOIN untuk meningkatkan prestasi pertanyaan.

• Elakkan menggunakan operator OR dalam pertanyaan, terutamanya apabila terdapat banyak syarat pertanyaan.

Gunakan HAD:

SELECT name FROM user LIMIT 10;

Guna WUJUD:

SELECT name FROM user WHERE EXISTS (SELECT * FROM order WHERE user.id = order.user_id);

Gunakan KUMPULAN OLEH:

SELECT name, SUM(amount) FROM order GROUP BY name;

Gunakan INNER JOIN: <🎜

SELECT user.name, order.amount FROM user INNER JOIN order ON user.id = order.user_id;

Pengoptimuman Pangkalan Data

Selain pengoptimuman indeks dan pertanyaan, prestasi dan kebolehpercayaan MySQL juga boleh dipertingkatkan dengan mengoptimumkan reka bentuk pangkalan data.

Berikut ialah beberapa petua pengoptimuman pangkalan data biasa:

• Gunakan enjin InnoDB dan bukannya enjin MyISAM, kerana InnoDB menyokong transaksi dan kunci peringkat baris, yang boleh menambah baik kebolehpercayaan dan integriti data.

• Elakkan menggunakan lajur BLOB atau TEKS dalam jadual kerana lajur ini boleh menyebabkan banyak operasi IO.

• Apabila mereka bentuk jadual, elakkan menggunakan terlalu banyak nilai NULL, kerana ini akan membazirkan banyak ruang storan.

• Untuk mengelak daripada menyimpan terlalu banyak data dalam satu jadual, anda boleh membahagikan jadual kepada berbilang sub-jadual untuk meningkatkan prestasi pertanyaan.

• Kerap bersihkan data tidak berguna dalam pangkalan data untuk mengelakkan kemerosotan prestasi yang disebabkan oleh volum data yang berlebihan.

• Konfigurasikan tetapan cache yang betul, termasuk cache pertanyaan, cache InnoDB, dsb.

Gunakan enjin InnoDB:

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50)
) ENGINE=InnoDB;

Elakkan menggunakan lajur BLOB atau TEKS:

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  content TEXT
);

Elakkan menggunakan terlalu banyak nilai NULL:

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT NOT NULL
);

Jadual pisah:

CREATE TABLE user_1 (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50)
);

CREATE TABLE user_2 (
  id INT PRIMARY KEY,
  address VARCHAR(100),
  phone VARCHAR(20)
);

Bersihkan data secara kerap:

DELETE FROM user WHERE created_at < &#39;2022-01-01&#39;;

Konfigurasikan cache:

SET GLOBAL query_cache_size = 1073741824;

Atas ialah kandungan terperinci Apakah kaedah pengendalian yang berkaitan dengan pengoptimuman prestasi MySQL pangkalan data dan pertanyaan kompleks?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!