Rumah > Artikel > pangkalan data > 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.
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".
Pangkalan data yang berbeza mempunyai kaedah pemerolehan yang berbeza Berikut ialah alat pemerolehan SQL pertanyaan lambat untuk pangkalan data arus perdana
MySQL
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 tertib mengikut rand() tulisan serupa• Elakkan di mana 1=1 menulis
RAND() menyebabkan lajur data diimbas beberapa kali
Pelan pelaksanaanPengoptimuman SQL
字段 | 解释 |
---|---|
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.
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
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.
Oleh kerana terdapat perkaitan antara jadual b dan jadual c, buat indeks pada user_id jadual b dan c
Kerana di sana ialah perkaitan antara jadual a dan jadual b, Cipta indeks pada medan nama_penjual bagi jadual a dan b
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
Lihat pelan pelaksanaan terangkan
Jika terdapat maklumat amaran, semak maklumat penggera tunjukkan amaran;
Lihat struktur jadual dan maklumat indeks yang terlibat dalam SQL
Fikirkan tentang kemungkinan titik pengoptimuman mengikut pelan pelaksanaan
Lakukan struktur jadual perubahan, tambah indeks, penulisan semula SQL dan operasi lain mengikut titik pengoptimuman yang mungkin
Lihat masa pelaksanaan dan pelan pelaksanaan yang dioptimumkan
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!