Mysql索引讀書筆記（待續）

##################################

高效能Mysql第5章：索引基礎

##################################

eg.

不加索引，走全表

mysql> explain select ifnull(sum(if(a.trade_type in ('deduct_deposit', 'net_deposit', 'offline_pay', 'deposit_back'), 1, 0)), 0) as fundacount, ifnull(sum(if(a.trade_type in ('deduct_deposit', 'net_deposit', 'offline_pay', 'deposit_back'), a.amount, 0)), 0) as fundamount, 0 as withdrawacount, 0 as withdrawamount, ifnull(sum(if(a.trade_type not in ('deduct_deposit', 'net_deposit', 'offline_pay', 'deposit_back', 'withdraw'), 1, 0)), 0) as tradeacount , ifnull(sum(if(a.trade_type not in ('deduct_deposit', 'net_deposit', 'offline_pay', 'deposit_back', 'withdraw'), a.amount, 0)), 0) as tradeamount from act_account_change a where 1 = 1 and a.user_id = '18020514102900300019' and a.change_direction = 'in' and a.create_time >= '2018-03-01 00:00:00' and a.create_time <= '2018-03-02 00:00:00';

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra      |

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

|  1 | SIMPLE      | a    | ALL  | NULL          | NULL | NULL    | NULL | 753975 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

1 row in set (0.00 sec)

mysql> explain select id, create_time, update_time, user_id, user_name , order_type, trade_type, account_type, account_no, change_type , change_direction, amount, balance, status, ref_user_id , ref_account_no, memo, comments, gid, merchant_no , order_no, partner_id, transfer_type from act_account_change where user_id = '18011009184100300278' and create_time >= '2018-02-27 00:00:00' order by id desc;

+----+-------------+--------------------+-------+---------------+---------+---------+------+--------+-------------+

| id | select_type | table              | type  | possible_keys | key    | key_len | ref  | rows  | Extra      |

+----+-------------+--------------------+-------+---------------+---------+---------+------+--------+-------------+

|  1 | SIMPLE      | act_account_change | index | NULL          | PRIMARY | 8      | NULL | 753975 | Using where |

+----+-------------+--------------------+-------+---------------+---------+---------+------+--------+-------------+

1 row in set (0.01 sec)

對時間欄位做索引

mysql> create index idx_test on act_account_change(create_time);

mysql> explain select id, create_time, update_time, user_id, user_name , order_type, trade_type, account_type, account_no, change_type , change_direction, amount, balance, status, ref_user_id , ref_account_no, memo, comments, gid, merchant_no , order_no, partner_id, transfer_type from act_account_change where user_id = '18011009184100300278' and create_time >= '2018-02-27 00:00:00' order by id desc;

+----+-------------+--------------------+-------+---------------+----------+---------+------+-------+---------------------------------------------------------------+

| id | select_type | table              | type  | possible_keys | key      | key_len | ref  | rows  | Extra                                                        |

+----+-------------+--------------------+-------+---------------+----------+---------+------+-------+---------------------------------------------------------------+

|  1 | SIMPLE      | act_account_change | range | idx_test      | idx_test | 6      | NULL | 93422 | Using index condition; Using where; Using MRR; Using filesort |

+----+-------------+--------------------+-------+---------------+----------+---------+------+-------+---------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> explain select ifnull(sum(if(a.trade_type in ('deduct_deposit', 'net_deposit', 'offline_pay', 'deposit_back'), 1, 0)), 0) as fundacount, ifnull(sum(if(a.trade_type in ('deduct_deposit', 'net_deposit', 'offline_pay', 'deposit_back'), a.amount, 0)), 0) as fundamount, 0 as withdrawacount, 0 as withdrawamount, ifnull(sum(if(a.trade_type not in ('deduct_deposit', 'net_deposit', 'offline_pay', 'deposit_back', 'withdraw'), 1, 0)), 0) as tradeacount , ifnull(sum(if(a.trade_type not in ('deduct_deposit', 'net_deposit', 'offline_pay', 'deposit_back', 'withdraw'), a.amount, 0)), 0) as tradeamount from act_account_change a where 1 = 1 and a.user_id = '18020514102900300019' and a.change_direction = 'in' and a.create_time >= '2018-03-01 00:00:00' and a.create_time <= '2018-03-02 00:00:00';

+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------------------------------+

| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows  | Extra                                        |

+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------------------------------+

|  1 | SIMPLE      | a    | range | idx_test      | idx_test | 6      | NULL | 17412 | Using index condition; Using where; Using MRR |

+----+-------------+-------+-------+---------------+----------+---------+------+-------+-----------------------------------------------+

1 row in set (0.00 sec)

把排序欄位和索引欄位一起寫入聚簇索引

mysql> create index idx_uid_time_id on act_account_change(user_id,create_time,id);

mysql> explain select id, create_time, update_time, user_id, user_name , order_type, trade_type, account_type, account_no, change_type , change_direction, amount, balance, status, ref_user_id , ref_account_no, memo, comments, gid, merchant_no , order_no, partner_id, transfer_type from act_account_change where user_id = '18011009184100300278' and create_time >= '2018-02-27 00:00:00' order by id desc;                          +----+-------------+--------------------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+

| id | select_type | table              | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                |

+----+-------------+--------------------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+

|  1 | SIMPLE      | act_account_change | range | idx_uid_time_id | idx_uid_time_id | 105    | NULL |    8 | Using index condition; Using filesort |

+----+-------------+--------------------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+

1 row in set (0.00 sec)

mysql> explain select ifnull(sum(if(a.trade_type in ('deduct_deposit', 'net_deposit', 'offline_pay', 'deposit_back'), 1, 0)), 0) as fundacount, ifnull(sum(if(a.trade_type in ('deduct_deposit', 'net_deposit', 'offline_pay', 'deposit_back'), a.amount, 0)), 0) as fundamount, 0 as withdrawacount, 0 as withdrawamount, ifnull(sum(if(a.trade_type not in ('deduct_deposit', 'net_deposit', 'offline_pay', 'deposit_back', 'withdraw'), 1, 0)), 0) as tradeacount , ifnull(sum(if(a.trade_type not in ('deduct_deposit', 'net_deposit', 'offline_pay', 'deposit_back', 'withdraw'), a.amount, 0)), 0) as tradeamount from act_account_change a where 1 = 1 and a.user_id = '18020514102900300019' and a.change_direction = 'in' and a.create_time >= '2018-03-01 00:00:00' and a.create_time <= '2018-03-02 00:00:00';

+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+------------------------------------+

| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                              |

+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+------------------------------------+

|  1 | SIMPLE      | a    | range | idx_uid_time_id | idx_uid_time_id | 105    | NULL |    3 | Using index condition; Using where |

+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+------------------------------------+

1 row in set (0.00 sec)

掃描行數從75W銳減到3和8，效果明顯；

查詢變化為：1.29s和0.68s→0.05s和0.12s→0.03s和0.04s

目標達成