某后台用户管理sql(后台人员反映用户操作界面越来越慢,抓到该sql)
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表已经使用索引。
且两次profiling对比发现,前者在执行过程中存在Copying to tmp table on disk状态,执行时间大约为20s;
而后者的Copying to tmp table on disk状态不复存在,执行时间大致为0.01s。
注意:
对于order by类型的sql,如果直接order by该字段,无法使用索引,可以试着寻找该字段的对应关系,相互转换后,类比的实现功能!