##################################
高性能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
目标达成
网友评论