php - 求这个sql怎么优化下

Question

sql语句是：
explain SELECT bp.amount / (select count(*) from yd_order_product where order = bp.order and product = bp.product) as amount,s.costprice as price,s.store,b.type from yd_batch_product as bp left join yd_order_product as op on op.order = bp.order and op.product = bp.product left join yd_batch as b on bp.batch = b.id left join yd_order as o on bp.order = o .id left join yd_stock as s on bp.product = s.id where o.deleted = '0' and s.forbidden = '0' and b.createdDate between '2015-10-13 00:00:00' and '2017-04-30 23:59:59' and s.store in (1,2,3,4,5,6) and s.chainID = 1

请问下这个第一行的type值为什么会是ALL

Answer 1

@TroyLiu, cadangan anda ialah terlalu banyak indeks dicipta dan idea untuk mewujudkan indeks pesanan/produk/kelompok/jumlah adalah salah.

Idea teras pengoptimuman SQL ialah jadual data besar boleh menapis data mengikut keadaan dalam pernyataan where yang sedia ada Dalam contoh ini:
yd_batch_product table hendaklah yang terbesar, tetapi dalam pernyataan where Terdapat. tiada syarat penapisan, mengakibatkan kecekapan pelaksanaan yang lemah.

Sebab mengapa "Menggunakan sementara; Menggunakan filesort" muncul ialah medan dalam kumpulan oleh tidak sepenuhnya dalam julat medan jadual pertama yd_batch_product table.

Cadangan:
1. yd_batch/yd_order/yd_stock menggunakan LEFT JOIN, tetapi terdapat syarat penapis untuk medan jadual ini dalam pernyataan where, jadi ia bersamaan dengan INNER JOIN, jadi kaedah sambungan boleh ditukar kepada INNER JOIN, jadi mysql Apabila menilai pelan pelaksanaan, anda bukan sahaja boleh menanyakan jadual yd_batch_product terlebih dahulu.
2. Cipta indeks pada medan CreatedDate pada jadual yd_batch untuk melihat sama ada terdapat sebarang perubahan dalam jadual pemacu pertama dalam pelan pelaksanaan Jika keadaan createdDate sangat menapis, jadual pertama dalam pelan pelaksanaan hendaklah yd_batch. .
3. Jadual yd_stock, cara menggabungkan kedai dan chainID untuk mencapai penapisan yang kuat, boleh mencipta indeks gabungan dan menentukan sama ada pelan pelaksanaan telah berubah dengan cara yang sama seperti langkah sebelumnya.

Sudah tentu, situasi yang paling ideal ialah bermula dari perniagaan dan data, ketahui syarat pada jadual yd_batch_product yang boleh menapis sejumlah besar data, dan kemudian buat indeks berdasarkan syarat ini. Sebagai contoh, medan CreatedDate bagi jadual yd_batch juga mempunyai medan lewah yang serupa pada jadual yd_batch_product dan medan ini digunakan untuk mencipta indeks.

---

Cadangan selepas mengemas kini soalan:
1 TYPE=ALL menunjukkan bahawa imbasan jadual penuh digunakan Walaupun CreateDate telah mencipta indeks, penilaian pangkalan data percaya bahawa menggunakan imbasan jadual penuh adalah lebih murah dan biasanya dianggap biasa (julat. daripada CreatedDate Lebih Besar, termasuk lebih daripada satu tahun data, lebih daripada 10,000 item keseluruhannya). Jika anda ingin menggunakan indeks, anda boleh mengecilkan julat dalam masa beberapa hari dan melihat jika terdapat sebarang perubahan dalam pelan pelaksanaan. Sudah tentu, anda juga boleh menggunakan gesaan untuk memaksa penggunaan indeks, tetapi kecekapannya tidak semestinya tinggi.
2. Jadual yd_order_product disoal dua kali, terutamanya subquery terpilih dalam pelan pelaksanaan, yang harus dielakkan sebaik mungkin.

Answer 2

Pertama sekali, jumlah data dalam jadual yd_batch_product adalah sangat besar, dan indeks tidak dibuat dengan betul, menyebabkan keseluruhan pertanyaan tidak menggunakan indeks.
Ia boleh dilihat dari lajur kunci yang sepadan bagi jadual bp ialah NULL.

Rujukan pengoptimuman:

1. Buat indeks bersama pada lajur pesanan/produk/kelompok/jumlah untuk jadual yd_batch_product Perhatikan urutan indeks bersama (jumlah juga ditambah pada indeks untuk menggunakan indeks penutup)

2. Jika tiada pertanyaan lain untuk yd_batch_product, pertimbangkan untuk memadamkan semua indeks lajur tunggal pada jadual.

Jadual

3. yd_stock diindeks bersama pada lajur dilarang/kedai/chainID (perhatikan pesanan indeks).

Jadual

4. yd_order mencipta indeks lajur tunggal pada lajur yang dipadamkan.

Jadual

5. yd_batch mencipta indeks lajur tunggal pada lajur CreatedDate.

Berdasarkan analisis semasa, pengoptimuman di atas boleh dilakukan.
Memandangkan tiada helaian data untuk ujian, sila betulkan saya jika terdapat sebarang kesilapan.