下面是未使用最近創建的複合索引的更新查詢/查詢計劃。解釋顯示它不使用名爲radacctupdate的複合索引,我認爲它會使更新查詢更快。還有其他索引也被其他查詢使用。如何強制mysql更新查詢使用索引?如何啓用mysql引擎自動使用索引而不是強制它?
EXPLAIN UPDATE radacct SET acctstoptime = '2017-01-08 11:52:24',
acctsessiontime = unix_timestamp('2017-01-08 11:52:24') - unix_timestamp(acctstarttime),
acctterminatecause = '', acctstopdelay = 14855646
WHERE acctstoptime IS NULL AND
nasipaddress = '102.34.56.234' AND acctstarttime <= '2017-01-08 11:52:24';
************* 1. row ***********
id: 1
select_type: SIMPLE
table: radacct
type: range
possible_keys: acctstarttime,acctstoptime,nasipaddress,radacctupdate
key: nasipaddress
key_len: 17
ref: const
rows: 94
Extra: Using where; Using MRR
下面是show index from
+---------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| radacct | 0 | PRIMARY | 1 | radacctid | A | 29299212 | NULL | NULL | | BTREE | | |
| radacct | 1 | username | 1 | username | A | NULL | NULL | NULL | | BTREE | | |
| radacct | 1 | framedipaddress | 1 | framedipaddress | A | NULL | NULL | NULL | | BTREE | | |
| radacct | 1 | acctsessionid | 1 | acctsessionid | A | NULL | NULL | NULL | | BTREE | | |
| radacct | 1 | acctsessiontime | 1 | acctsessiontime | A | NULL | NULL | NULL | YES | BTREE | | |
| radacct | 1 | acctstarttime | 1 | acctstarttime | A | NULL | NULL | NULL | YES | BTREE | | |
| radacct | 1 | acctstoptime | 1 | acctstoptime | A | NULL | NULL | NULL | YES | BTREE | | |
| radacct | 1 | nasipaddress | 1 | nasipaddress | A | NULL | NULL | NULL | | BTREE | | |
| radacct | 1 | acctuniqueid | 1 | acctuniqueid | A | NULL | NULL | NULL | | BTREE | | |
| radacct | 1 | radacctupdate | 1 | acctstoptime | A | NULL | NULL | NULL | YES | BTREE | | |
| radacct | 1 | radacctupdate | 2 | nasipaddress | A | NULL | NULL | NULL | | BTREE | | |
| radacct | 1 | radacctupdate | 3 | acctstarttime | A | NULL | NULL | NULL | YES | BTREE | | |
+---------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+