フィールドを巧みに変換してインデックスを使用
3803 ワード
あるバックグラウンドユーザ管理sql(バックグラウンドユーザはユーザ操作インタフェースがますます遅くなっていることを反映し、そのsqlを捕まえる)
explain後に発見
Customersテーブル、すなわちcテーブルは全テーブルスキャンです!そしてcustomers_info(ci)テーブルのcustomers_info_date_account_createdフィールドにはインデックスが存在するが、このsqlはleft joinであり、cテーブルでciを駆動するため、ciのcustomers_info_date_account_createdフィールドのインデックスは使用できません!
しかし、私たちは少し考えを変えることができます.customers_info_date_account_createdフィールドはアカウント作成時間を意味し、sql全体が実現する機能はアカウント作成時間に従って最新の20人のユーザーの情報を出すことである.ほとんどのシステムのアカウントの顧客id(customers_id)は自己増加して得られることを知っています.これは関係があります.アカウントの作成時間の前後順は、基本的にcustomers_に対応しています.idの大きさ関係!だから、後ろのci.customers_info_date_account_created DESC LIMIT 0,20 ORDER BY c.customers_id DESC LIMIT 0,20を置換
置換後のsqlは以下の通りです.
Customersテーブルはインデックスを使用しています.
2回のprofilingの比較から、前者は実行中にCopying to tmp table on disk状態が存在し、実行時間は約20 sであることが分かった.
後者のCopying to tmp table on disk状態は存在せず,実行時間はほぼ0.01 sであった.
注意:
order byタイプのsqlに対して、直接order byこのフィールドでインデックスが使えない場合は、そのフィールドの対応関係を探してみて、互いに変換した後、クラス比の実現機能!
SELECT c.dropshipper_level,c.customers_id,c.customers_firstname,c.is_wholesale, c.customers_email_address,
a.entry_country_id, ci.customers_info_date_account_created, ci.customers_info_number_of_logons ,admin_id
FROM customers c LEFT JOIN address_book a ON c.customers_id = a.customers_id
LEFT JOIN customers_info AS ci ON ci.customers_info_id=c.customers_id
WHERE 1 ORDER BY ci.customers_info_date_account_created DESC LIMIT 0, 20
explain後に発見
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------+-------------------------------+---------+------------------------------+--------+---------------------------------+
| 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 4064464 | Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | idx_address_book_customers_id | idx_address_book_customers_id | 4 | banggood_work.c.customers_id | 2 | |
| 1 | SIMPLE | ci | eq_ref | PRIMARY | PRIMARY | 4 | banggood_work.c.customers_id | 1 | |
Customersテーブル、すなわちcテーブルは全テーブルスキャンです!そしてcustomers_info(ci)テーブルのcustomers_info_date_account_createdフィールドにはインデックスが存在するが、このsqlはleft joinであり、cテーブルでciを駆動するため、ciのcustomers_info_date_account_createdフィールドのインデックスは使用できません!
しかし、私たちは少し考えを変えることができます.customers_info_date_account_createdフィールドはアカウント作成時間を意味し、sql全体が実現する機能はアカウント作成時間に従って最新の20人のユーザーの情報を出すことである.ほとんどのシステムのアカウントの顧客id(customers_id)は自己増加して得られることを知っています.これは関係があります.アカウントの作成時間の前後順は、基本的にcustomers_に対応しています.idの大きさ関係!だから、後ろのci.customers_info_date_account_created DESC LIMIT 0,20 ORDER BY c.customers_id DESC LIMIT 0,20を置換
置換後のsqlは以下の通りです.
mysql> EXPLAIN
SELECT c.dropshipper_level,c.customers_id,c.customers_firstname,c.is_wholesale, c.customers_email_address,
a.entry_country_id, ci.customers_info_date_account_created, ci.customers_info_number_of_logons ,admin_id
FROM customers c LEFT JOIN address_book a ON c.customers_id = a.customers_id
LEFT JOIN customers_info AS ci ON ci.customers_info_id=c.customers_id
WHERE 1 ORDER BY c.customers_id DESC LIMIT 0, 20;
+----+-------------+-------+--------+-------------------------------+-------------------------------+---------+------------------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------+-------------------------------+---------+------------------------------+------+-------+
| 1 | SIMPLE | c | index | NULL | PRIMARY | 4 | NULL | 10 | |
| 1 | SIMPLE | a | ref | idx_address_book_customers_id | idx_address_book_customers_id | 4 | banggood_work.c.customers_id | 2 | |
| 1 | SIMPLE | ci | eq_ref | PRIMARY | PRIMARY | 4 | banggood_work.c.customers_id | 1 | |
Customersテーブルはインデックスを使用しています.
2回のprofilingの比較から、前者は実行中にCopying to tmp table on disk状態が存在し、実行時間は約20 sであることが分かった.
後者のCopying to tmp table on disk状態は存在せず,実行時間はほぼ0.01 sであった.
注意:
order byタイプのsqlに対して、直接order byこのフィールドでインデックスが使えない場合は、そのフィールドの対応関係を探してみて、互いに変換した後、クラス比の実現機能!