Rumah pangkalan data tutorial mysql 巧用临时表将大结果集转换为小结果集驱动查询

巧用临时表将大结果集转换为小结果集驱动查询

Jun 07, 2016 pm 05:36 PM
mysql meja sementara

sql如下SELECTDISTINCTo.orders_id,o.oa_order_id,os.orders_status_name,o.order_type,o.date_purchasedASadd_date,dop.resource,dop.country_codeFROMdm_order

sql如下

SELECT DISTINCT o.orders_id, o.oa_order_id,os.orders_status_name, o.order_type, o.date_purchased AS add_date,dop.resource, dop.country_code FROM dm_order_products AS dop LEFT JOIN orders AS o ON o.orders_id=dop.orders_id LEFT JOIN orders_total AS ot ON ot.orders_id=o.orders_id AND ot.class='ot_total' LEFT JOIN orders_status AS os ON os.orders_status_id=o.orders_status WHERE o.date_purchased >= '2014-01-31 10:00:00' AND o.date_purchased 因为需要在大结果集中order by 去重,再显示20条.

表特性是orders(o)表对dm_order_products(dop)表为一对多关系,而取出来的dop.country_code为一个订单号对应唯一值,由于表结构设计问题,每次查询该country_code都需要去dop查询。所以,每次查询都放大结果集,,然后再去重,得到所要的结果集合。

explain

+----+-------------+-------+-------+----------------------------------+----------------------------+---------+-------------------------------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------------------------+----------------------------+---------+-------------------------------+-------+----------------------------------------------+ | 1 | SIMPLE | o | range | PRIMARY,date_purchased | date_purchased | 9 | NULL | 952922 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | ot | ref | idx_orders_total_orders_id,class | idx_orders_total_orders_id | 4 | banggood_work.o.orders_id | 3 | | | 1 | SIMPLE | os | ref | PRIMARY | PRIMARY | 4 | banggood_work.o.orders_status | 1 | | | 1 | SIMPLE | dop | ref | orders_id | orders_id | 4 | banggood_work.o.orders_id | 2 | | +----+-------------+-------+-------+----------------------------------+----------------------------+---------+-------------------------------+-------+----------------------------------------------+

索引情况使用正常,但是发现需要扫描一个大结果集.

profiling,执行时间为将近20s

mysql> show profile cpu,block io for query 1; +--------------------------------+-----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+-----------+----------+------------+--------------+---------------+ | starting | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | | Waiting for query cache lock | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | checking query cache for query | 0.000080 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000034 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | | Waiting for query cache lock | 0.000024 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000046 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000018 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000193 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000054 | 0.000000 | 0.000000 | 0 | 0 | | Creating tmp table | 0.000031 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | Copying to tmp table | 12.491533 | 3.039538 | 3.107527 | 11896 | 824 | | Sorting result | 0.030709 | 0.034995 | 0.004000 | 16 | 496 | | Sending data | 0.000048 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | removing tmp table | 0.010108 | 0.000000 | 0.010998 | 8 | 32 | | end | 0.000013 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000338 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000033 | 0.000000 | 0.000000 | 0 | 8 | | cleaning up | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |

可以看到Copying to tmp table 占了大部分的cpu时间和io,最后sorting result占比重不大。

我们可以上面描述的结合特性,是否能够去掉Copying to tmp table 选项!因为是根据orders_id排序,取出最新的20条数据,如果我们在orders表中先把20条数据取出来,再和对应的表连接,这样一来,就将整个大结果Copying to tmp table 再排序这一步去掉!

看sql语句如下

SELECT DISTINCT o.orders_id, o.oa_order_id,os.orders_status_name, o.order_type, o.date_purchased AS add_date,dop.resource, dop.country_code FROM ( SELECT * FROM orders AS o WHERE o.date_purchased >= '2014-01-31 10:00:00' AND o.date_purchased | ALL | NULL | NULL | NULL | NULL | 20 | Using temporary; Using filesort | | 1 | PRIMARY | dop | ref | orders_id | orders_id | 4 | o.orders_id | 2 | | | 1 | PRIMARY | ot | ref | idx_orders_total_orders_id,class | idx_orders_total_orders_id | 4 | o.orders_id | 3 | | | 1 | PRIMARY | os | ref | PRIMARY | PRIMARY | 4 | o.orders_status | 1 | | | 2 | DERIVED | o | index | date_purchased | PRIMARY | 4 | NULL | 330 | Using where | +----+-------------+------------+-------+----------------------------------+----------------------------+---------+-----------------+------+---------------------------------+
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn

Alat AI Hot

Undresser.AI Undress

Undresser.AI Undress

Apl berkuasa AI untuk mencipta foto bogel yang realistik

AI Clothes Remover

AI Clothes Remover

Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.

Undress AI Tool

Undress AI Tool

Gambar buka pakaian secara percuma

Clothoff.io

Clothoff.io

Penyingkiran pakaian AI

AI Hentai Generator

AI Hentai Generator

Menjana ai hentai secara percuma.

Artikel Panas

R.E.P.O. Kristal tenaga dijelaskan dan apa yang mereka lakukan (kristal kuning)
3 minggu yang lalu By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Tetapan grafik terbaik
3 minggu yang lalu By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Cara Memperbaiki Audio Jika anda tidak dapat mendengar sesiapa
3 minggu yang lalu By 尊渡假赌尊渡假赌尊渡假赌
WWE 2K25: Cara Membuka Segala -galanya Di Myrise
4 minggu yang lalu By 尊渡假赌尊渡假赌尊渡假赌

Alat panas

Notepad++7.3.1

Notepad++7.3.1

Editor kod yang mudah digunakan dan percuma

SublimeText3 versi Cina

SublimeText3 versi Cina

Versi Cina, sangat mudah digunakan

Hantar Studio 13.0.1

Hantar Studio 13.0.1

Persekitaran pembangunan bersepadu PHP yang berkuasa

Dreamweaver CS6

Dreamweaver CS6

Alat pembangunan web visual

SublimeText3 versi Mac

SublimeText3 versi Mac

Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

MySQL: Kemudahan Pengurusan Data untuk Pemula MySQL: Kemudahan Pengurusan Data untuk Pemula Apr 09, 2025 am 12:07 AM

MySQL sesuai untuk pemula kerana mudah dipasang, kuat dan mudah untuk menguruskan data. 1. Pemasangan dan konfigurasi mudah, sesuai untuk pelbagai sistem operasi. 2. Menyokong operasi asas seperti membuat pangkalan data dan jadual, memasukkan, menanyakan, mengemas kini dan memadam data. 3. Menyediakan fungsi lanjutan seperti menyertai operasi dan subqueries. 4. Prestasi boleh ditingkatkan melalui pengindeksan, pengoptimuman pertanyaan dan pembahagian jadual. 5. Sokongan sokongan, pemulihan dan langkah keselamatan untuk memastikan keselamatan data dan konsistensi.

Bolehkah saya mengambil kata laluan pangkalan data di Navicat? Bolehkah saya mengambil kata laluan pangkalan data di Navicat? Apr 08, 2025 pm 09:51 PM

Navicat sendiri tidak menyimpan kata laluan pangkalan data, dan hanya boleh mengambil kata laluan yang disulitkan. Penyelesaian: 1. Periksa Pengurus Kata Laluan; 2. Semak fungsi "Ingat Kata Laluan" Navicat; 3. Tetapkan semula kata laluan pangkalan data; 4. Hubungi pentadbir pangkalan data.

Cara Membuat Premium Navicat Cara Membuat Premium Navicat Apr 09, 2025 am 07:09 AM

Buat pangkalan data menggunakan Navicat Premium: Sambungkan ke pelayan pangkalan data dan masukkan parameter sambungan. Klik kanan pada pelayan dan pilih Buat Pangkalan Data. Masukkan nama pangkalan data baru dan set aksara yang ditentukan dan pengumpulan. Sambung ke pangkalan data baru dan buat jadual dalam penyemak imbas objek. Klik kanan di atas meja dan pilih masukkan data untuk memasukkan data.

Cara menyalin jadual di mysql Cara menyalin jadual di mysql Apr 08, 2025 pm 07:24 PM

Menyalin jadual di MySQL memerlukan membuat jadual baru, memasukkan data, menetapkan kunci asing, menyalin indeks, pencetus, prosedur tersimpan, dan fungsi. Langkah -langkah khusus termasuk: mewujudkan jadual baru dengan struktur yang sama. Masukkan data dari jadual asal ke dalam jadual baru. Tetapkan kekangan utama asing yang sama (jika jadual asal mempunyai satu). Buat indeks yang sama. Buat pencetus yang sama (jika jadual asal mempunyai satu). Buat prosedur atau fungsi yang disimpan yang sama (jika jadual asal digunakan).

Bagaimana untuk melihat kata laluan pangkalan data di Navicat untuk MariaDB? Bagaimana untuk melihat kata laluan pangkalan data di Navicat untuk MariaDB? Apr 08, 2025 pm 09:18 PM

Navicat untuk MariaDB tidak dapat melihat kata laluan pangkalan data secara langsung kerana kata laluan disimpan dalam bentuk yang disulitkan. Untuk memastikan keselamatan pangkalan data, terdapat tiga cara untuk menetapkan semula kata laluan anda: Tetapkan semula kata laluan anda melalui Navicat dan tetapkan kata laluan yang kompleks. Lihat fail konfigurasi (tidak disyorkan, risiko tinggi). Gunakan alat baris perintah sistem (tidak disyorkan, anda perlu mahir dalam alat baris arahan).

Mysql: Konsep mudah untuk pembelajaran mudah Mysql: Konsep mudah untuk pembelajaran mudah Apr 10, 2025 am 09:29 AM

MySQL adalah sistem pengurusan pangkalan data sumber terbuka. 1) Buat Pangkalan Data dan Jadual: Gunakan perintah Createdatabase dan Createtable. 2) Operasi Asas: Masukkan, Kemas kini, Padam dan Pilih. 3) Operasi lanjutan: Sertai, subquery dan pemprosesan transaksi. 4) Kemahiran Debugging: Semak sintaks, jenis data dan keizinan. 5) Cadangan Pengoptimuman: Gunakan indeks, elakkan pilih* dan gunakan transaksi.

Navicat tidak dapat menyambung ke MySQL/MariaDB/PostgreSQL dan pangkalan data lain Navicat tidak dapat menyambung ke MySQL/MariaDB/PostgreSQL dan pangkalan data lain Apr 08, 2025 pm 11:00 PM

Alasan Biasa Mengapa Navicat tidak dapat menyambung ke pangkalan data dan penyelesaiannya: 1. Periksa status berjalan pelayan; 2. Periksa maklumat sambungan; 3. Laraskan tetapan firewall; 4. Konfigurasi akses jauh; 5. menyelesaikan masalah rangkaian; 6. Periksa keizinan; 7. Memastikan keserasian versi; 8. menyelesaikan masalah lain.

Cara Melaksanakan SQL di Navicat Cara Melaksanakan SQL di Navicat Apr 08, 2025 pm 11:42 PM

Langkah -langkah untuk melaksanakan SQL di Navicat: Sambungkan ke pangkalan data. Buat tetingkap editor SQL. Tulis pertanyaan SQL atau skrip. Klik butang Run untuk melaksanakan pertanyaan atau skrip. Lihat hasilnya (jika pertanyaan dilaksanakan).

See all articles