mysql - 即使在大表上有索引，查询也很慢

Question

我正在执行一个简单的选择查询以从表日志(包含 54864 行)中提取用户名。检索数据大约需要 7.836s。我怎样才能加快性能？？？

SELECT username FROM `logs`
WHERE 
logs.branch=1 
and
logs.added_on > '2016-11-27 00:00:00'

关于描述表，

+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
| id                | int(11)     | NO   | PRI | NULL    | auto_increment |
| username          | char(255)   | YES  | MUL | NULL    |                |
| fullname          | char(255)   | YES  |     | NULL    |                |
| package           | char(255)   | YES  |     | NULL    |                |
| prev_expiry       | date        | YES  |     | NULL    |                |
| recharged_upto    | date        | YES  |     | NULL    |                |
| payment_option    | int(11)     | YES  | MUL | NULL    |                |
| amount            | float(14,2) | YES  |     | NULL    |                |
| branch            | int(11)     | YES  | MUL | NULL    |                |
| added_by          | int(11)     | YES  |     | NULL    |                |
| added_on          | datetime    | YES  | MUL | NULL    |                |
| remark            | text        | YES  |     | NULL    |                |
| payment_mode      | char(255)   | YES  |     | NULL    |                |
| recharge_duration | char(255)   | YES  |     | NULL    |                |
| invoice_number    | char(255)   | YES  |     | NULL    |                |
| cheque_no         | char(255)   | YES  |     | NULL    |                |
| bank_name         | char(255)   | YES  |     | NULL    |                |
| verify_by_ac      | int(11)     | YES  |     | 0       |                |
| adjusted_days     | int(11)     | YES  |     | NULL    |                |
| adjustment_note   | text        | YES  |     | NULL    |                |
+-------------------+-------------+------+-----+---------+----------------+
20 rows in set

在解释查询时，

+----+-------------+--------------------------+------+-----------------------------+--------------+---------+-------+------+-------------+
| id | select_type | table                    | type | possible_keys               | key          | key_len | ref   | rows | Extra       |
+----+-------------+--------------------------+------+-----------------------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | logs                     | ref  | branch_index,added_on_index | branch_index | 5       | const |   37 | Using where |
+----+-------------+--------------------------+------+-----------------------------+--------------+---------+-------+------+-------------+
1 row in set

更新::添加复合索引(branch_added_index)后的解释查询

+----+-------------+--------------------------+------+------------------------------------------------+--------------+---------+-------+------+-------------+
| id | select_type | table                    | type | possible_keys                                  | key          | key_len | ref   | rows | Extra       |
+----+-------------+--------------------------+------+------------------------------------------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | logs                     | ref  | branch_index,added_on_index,branch_added_index | branch_index | 5       | const |   37 | Using where |
+----+-------------+--------------------------+------+------------------------------------------------+--------------+---------+-------+------+-------------+
1 row in set

Answer 1

在 branch,added_on 上添加复合键，这样您就可以覆盖所有 WHERE 条件，因为您使用了 AND。

ALTER TABLE 记录 ADD KEY(branch,added_on)

这应该快得多，您也可以删除 branch_index 键，因为上面的索引可以替换它。您只返回 54000 中的 37 行，所以基数没问题。

ALTER TABLE logs DROP INDEX `branch_index`;

或者你可以使用索引提示

SELECT username FROM `logs` USE INDEX (branch_added_index)  WHERE 
logs.branch=1 
and
logs.added_on > '2016-11-27 00:00:00'