Gunakan contoh untuk memberitahu anda cara mengoptimumkan SQL

Walaupun kos perkakasan telah menurun pada masa kini, meningkatkan prestasi sistem dengan menaik taraf perkakasan juga merupakan kaedah pengoptimuman yang biasa. Sistem dengan keperluan masa nyata yang tinggi masih perlu dioptimumkan dari aspek SQL Hari ini kami akan memperkenalkan cara mengoptimumkan SQL berdasarkan contoh.

Hakim masalah SQL

Apabila menilai sama ada terdapat masalah dengan SQL, anda boleh menilainya melalui dua penampilan:

• Penampilan tahap sistem

  • Penggunaan CPU adalah serius

  • IO menunggu adalah serius

  • Masa respons halaman terlalu lama

  • Tamat masa dan ralat lain muncul dalam log aplikasi

Anda boleh menggunakan perintah sar dan arahan atas untuk melihat status sistem semasa. Anda juga boleh melihat status sistem melalui alat pemantauan seperti Prometheus dan Grafana.

• Perwakilan pernyataan SQL

  • Panjang

  • Pelaksanaan Ia mengambil masa terlalu lama

  • Dapatkan data daripada imbasan jadual penuh

  • Baris dan kos dalam pelan pelaksanaan adalah sangat besar

SQL yang panjang mudah difahami Jika SQL terlalu panjang, kebolehbacaan akan menjadi lemah, dan kekerapan masalah pasti akan lebih tinggi. Untuk menilai lebih lanjut masalah SQL, kita perlu bermula dengan pelan pelaksanaan, seperti yang ditunjukkan di bawah:

Pelan pelaksanaan memberitahu kami bahawa pertanyaan ini menggunakan imbasan jadual penuh Type= SEMUA, dan barisnya sangat besar (9950400) Pada asasnya boleh dinilai bahawa ini adalah SQL "berperisa".

SQL masalah pemerolehan

Pangkalan data yang berbeza mempunyai kaedah pemerolehan yang berbeza Berikut ialah alat pemerolehan SQL pertanyaan lambat untuk pangkalan data arus perdana

• MySQL

  • Log pertanyaan perlahan

  • Pemuat alat ujian

  • ptquery Percona dan alatan lain

• Oracle
  • Laporan AWR

  • Uji pemuat alat dll.

  • Pandangan dalaman yang berkaitan seperti v$, $session_wait, dsb.

  • Alat pemantauan KAWALAN GRID

• Pangkalan data Dameng
  • Laporan AWR

  • Pemuat alat ujian dll.

  • Dameng alat pemantauan prestasi (dem)

  • Pandangan dalaman yang berkaitan seperti v$, $session_wait, dsb.

SQL kemahiran menulis

Penulisan SQL mempunyai kemahiran umum berikut:

• Gunakan indeks secara rasional

Dengan lebih sedikit indeks, pertanyaan akan menjadi lebih perlahan dengan lebih banyak indeks; mengambil lebih banyak ruang apabila melaksanakan tambah, padam dan ubah suai penyataan Indeks perlu dikekalkan secara dinamik, yang menjejaskan prestasi Jika kadar pemilihan adalah tinggi (nilai pendua yang lebih sedikit) dan sering dirujuk oleh tempat, indeks B-tree. untuk diwujudkan; perhatian harus diberikan kepada pertanyaan berdasarkan lajur bukan peneraju

• Gunakan UNION ALL dan bukannya UNION

UNION ALL mempunyai kecekapan pelaksanaan yang lebih tinggi daripada UNION dinyahgandakan apabila dilaksanakan; UNION perlu mengisih data

• Elakkan memilih * menulis

Optimumkan apabila melaksanakan SQL Pemproses perlu menukar * ke dalam lajur tertentu mesti dikembalikan jadual, dan indeks penutup tidak boleh digunakan.

• Adalah disyorkan untuk mencipta indeks untuk medan JOIN

Secara amnya, medan JOIN diindeks terlebih dahulu

• Elakkan SQL kompleks penyata

Tingkatkan kebolehbacaan; elakkan kebarangkalian pertanyaan yang perlahan boleh ditukar kepada berbilang pertanyaan pendek dan diproses oleh penghujung perniagaan

• Elakkan di mana 1=1 menulis

• Elakkan tertib mengikut rand() tulisan serupa

RAND() menyebabkan lajur data diimbas beberapa kali

Pengoptimuman SQL

Pelan pelaksanaan

Untuk melengkapkan pengoptimuman SQL, anda mesti membaca pelan pelaksanaan terlebih dahulu Pelan pelaksanaan akan memberitahu anda di mana kecekapan adalah rendah dan di mana pengoptimuman diperlukan. Mari kita ambil MYSQL sebagai contoh untuk melihat apakah rancangan pelaksanaan itu. (Pelan pelaksanaan setiap pangkalan data adalah berbeza, anda perlu memahaminya sendiri)

字段	解释
id	每个被独立执行的操作标识，标识对象被操作的顺序，id值越大，先被执行，如果相同，执行顺序从上到下
select_type	查询中每个select 字句的类型
table	被操作的对象名称，通常是表名，但有其他格式
partitions	匹配的分区信息(对于非分区表值为NULL)
type	连接操作的类型
possible_keys	可能用到的索引
key	优化器实际使用的索引(最重要的列) 从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL。当出现ALL时表示当前SQL出现了“坏味道”
key_len	被优化器选定的索引键长度，单位是字节
ref	表示本行被操作对象的参照对象，无参照对象为NULL
rows	查询执行所扫描的元组个数（对于innodb，此值为估计值）
filtered	条件表上数据被过滤的元组个数百分比
extra	执行计划的重要补充信息，当此列出现Using filesort , Using temporary 字样时就要小心了，很可能SQL语句需要优化

Seterusnya, kami menggunakan kes pengoptimuman praktikal untuk menggambarkan proses pengoptimuman SQL dan teknik pengoptimuman.

Kes Pengoptimuman

Struktur Jadual

CREATE TABLE `a`
(
    `id`          int(11) NOT NULLAUTO_INCREMENT,
    `seller_id`   bigint(20)                                       DEFAULT NULL,
    `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    `gmt_create`  varchar(30)                                      DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `b`
(
    `id`          int(11) NOT NULLAUTO_INCREMENT,
    `seller_name` varchar(100) DEFAULT NULL,
    `user_id`     varchar(50)  DEFAULT NULL,
    `user_name`   varchar(100) DEFAULT NULL,
    `sales`       bigint(20)   DEFAULT NULL,
    `gmt_create`  varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `c`
(
    `id`         int(11) NOT NULLAUTO_INCREMENT,
    `user_id`    varchar(50)  DEFAULT NULL,
    `order_id`   varchar(100) DEFAULT NULL,
    `state`      bigint(20)   DEFAULT NULL,
    `gmt_create` varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);

Tiga jadual berkaitan untuk menanyakan status pesanan pengguna semasa 10 jam sebelum dan selepas masa semasa dan memesannya dalam susunan menaik mengikut susunan masa penciptaan susunan, SQL khusus adalah seperti berikut

select a.seller_id,
       a.seller_name,
       b.user_name,
       c.state
from a,
     b,
     c
where a.seller_name = b.seller_name
  and b.user_id = c.user_id
  and c.user_id = 17
  and a.gmt_create
    BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
    AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create；

Lihat volum data

Masa pelaksanaan asal

Pelan pelaksanaan asal

Idea pengoptimuman awal

1. Di mana jenis medan keadaan dalam SQL mesti konsisten dengan struktur jadual user_id adalah jenis varchar(50) Jenis int sebenar yang digunakan dalam SQL mempunyai penukaran tersirat dan tiada indeks ditambahkan. Tukar medan user_id dalam jadual b dan c kepada jenis int.

2. Oleh kerana terdapat perkaitan antara jadual b dan jadual c, buat indeks pada user_id jadual b dan c

3. Kerana di sana ialah perkaitan antara jadual a dan jadual b, Cipta indeks pada medan nama_penjual bagi jadual a dan b

4. Gunakan indeks komposit untuk menghapuskan jadual sementara dan mengisih

Pengoptimuman awal SQL

alter table b modify `user_id` int(10) DEFAULT NULL;
alter table c modify `user_id` int(10) DEFAULT NULL;
alter table c add index `idx_user_id`(`user_id`);
alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);

Lihat masa pelaksanaan yang dioptimumkan

Lihat pelan pelaksanaan yang dioptimumkan

Lihat maklumat amaran

Teruskan untuk mengoptimumkan ubah jadual dan ubah suai "gmt_create" datetime NULL;

Lihat masa pelaksanaan

Lihat rancangan pelaksanaan

Ringkasan

1. Lihat pelan pelaksanaan terangkan

2. Jika terdapat maklumat amaran, semak maklumat penggera tunjukkan amaran;

3. Lihat struktur jadual dan maklumat indeks yang terlibat dalam SQL

4. Fikirkan tentang kemungkinan titik pengoptimuman mengikut pelan pelaksanaan

5. Lakukan struktur jadual perubahan, tambah indeks, penulisan semula SQL dan operasi lain mengikut titik pengoptimuman yang mungkin

6. Lihat masa pelaksanaan dan pelan pelaksanaan yang dioptimumkan

7. Jika pengoptimuman kesan tidak jelas, ulangi langkah keempat

Cadangan berkaitan: "tutorial mysql"

Atas ialah kandungan terperinci Gunakan contoh untuk memberitahu anda cara mengoptimumkan SQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!