我在mysql5.6中有一个表cdc\u bond\u valuation：
+--------------+--------------------+--------------+---------------------------------+--------------+--------------+-------------+
| table_schema | table_name | index_schema | index_name | seq_in_index | column_name | cardinality |
+--------------+--------------------+--------------+---------------------------------+--------------+--------------+-------------+
| ss_product | cdc_bond_valuation | ss_product | IDX_cdc_bond_valuation_Bond_Key | 1 | Bond_Key | 377844 |
| ss_product | cdc_bond_valuation | ss_product | IndexValuateDate | 1 | Valuate_Date | 143025 |
| ss_product | cdc_bond_valuation | ss_product | PRIMARY | 1 | ID | 25315548 |
+--------------+--------------------+--------------+---------------------------------+--------------+--------------+-------------+
query 1:
SELECT Valuate_Date FROM cdc_bond_valuation WHERE Valuate_Date = 20181203;
query 2:
SELECT ID, Valuate_Date FROM cdc_bond_valuation WHERE Valuate_Date = 20181203;
query 3:
SELECT Bond_Key FROM cdc_bond_valuation WHERE Valuate_Date = 20181203;
query 4:
SELECT Bond_Key,Valuate_Date FROM cdc_bond_valuation WHERE Valuate_Date = 20181203;
explain 1:
mysql> explain SELECT Valuate_Date FROM cdc_bond_valuation WHERE Valuate_Date = 20181203;
+----+-------------+--------------------+------+------------------+------------------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+------------------+------------------+---------+-------+-------+-------------+
| 1 | SIMPLE | cdc_bond_valuation | ref | IndexValuateDate | IndexValuateDate | 5 | const | 98156 | Using index |
+----+-------------+--------------------+------+------------------+------------------+---------+-------+-------+-------------+
1 row in set
explain 2:
mysql> explain SELECT ID,Valuate_Date FROM cdc_bond_valuation WHERE Valuate_Date = 20181203;
+----+-------------+--------------------+------+------------------+------------------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+------------------+------------------+---------+-------+-------+-------------+
| 1 | SIMPLE | cdc_bond_valuation | ref | IndexValuateDate | IndexValuateDate | 5 | const | 98156 | Using index |
+----+-------------+--------------------+------+------------------+------------------+---------+-------+-------+-------------+
1 row in set
explain 3:
mysql> explain SELECT Bond_Key FROM cdc_bond_valuation WHERE Valuate_Date = 20181203;
+----+-------------+--------------------+------+------------------+------------------+---------+-------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+------------------+------------------+---------+-------+-------+-------+
| 1 | SIMPLE | cdc_bond_valuation | ref | IndexValuateDate | IndexValuateDate | 5 | const | 98156 | NULL |
+----+-------------+--------------------+------+------------------+------------------+---------+-------+-------+-------+
1 row in set
explain 4:
mysql> explain SELECT Bond_Key,Valuate_Date FROM cdc_bond_valuation WHERE Valuate_Date = 20181203;
+----+-------------+--------------------+------+------------------+------------------+---------+-------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+------------------+------------------+---------+-------+-------+-------+
| 1 | SIMPLE | cdc_bond_valuation | ref | IndexValuateDate | IndexValuateDate | 5 | const | 98156 | NULL |
+----+-------------+--------------------+------+------------------+------------------+---------+-------+-------+-------+
1 row in set
mysql> select table_schema,
table_name,
index_schema,
index_name,
seq_in_index,
column_name,
cardinality
from information_schema.statistics
where table_name = 'cdc_bond_valuation'
order by table_schema, table_name, index_name, seq_in_index;
+--------------+--------------------+--------------+---------------------------------+--------------+--------------+-------------+
| table_schema | table_name | index_schema | index_name | seq_in_index | column_name | cardinality |
+--------------+--------------------+--------------+---------------------------------+--------------+--------------+-------------+
| ss_product | cdc_bond_valuation | ss_product | IDX_cdc_bond_valuation_Bond_Key | 1 | Bond_Key | 377844 |
| ss_product | cdc_bond_valuation | ss_product | IndexValuateDate | 1 | Valuate_Date | 143025 |
| ss_product | cdc_bond_valuation | ss_product | PRIMARY | 1 | ID | 25315548 |
+--------------+--------------------+--------------+---------------------------------+--------------+--------------+-------------+
3 rows in set
mysql>表上有4个查询，它们都使用索引indexevaluedate，但是查询1和查询2非常快（不到1秒），但是查询3和查询4非常慢（超过1000秒）。
我注意到1和2只是使用索引来反馈查询（id是主键，evalue\u date是索引）。3和4首先使用evalue\ u date上的索引来筛选表，然后返回到表get column with rowid？为什么不直接使用像1和2这样的索引，因为键也被索引了？
4条答案
按热度按时间ujv3wf0j1#
vbkedwbf2#
bq3bfh9z3#
问题5：
查询5使用索引（bond\u key）进行过滤，然后像对bond\u key的full table scann一样扫描结果（787行）。查询5根本不使用索引（valuedate）。是这样吗？
omvjsjqw4#
请提供
SHOW CREATE TABLE.innodb默默地添加
PRIMARY KEY到每个辅助键。因此，查询1和查询2的执行方式相同。他们只使用索引。如中所示EXPLAIN由Using index. 也就是说，INDEX(Valuate_Date)包含所需的列，其他列不需要。这个
EXPLAINs表明使用了相同的索引，但不是“覆盖”（未提及Using index). 所以索引是线性扫描的，但是对于那个日期估计的98156个条目中的每一个，它必须（在数据的btree中）查找Bond_Key. 这个额外的查找导致了严重的减速(1000秒与硬盘上的98156次磁盘点击非常匹配。）要快速完成所有4个查询，请替换
IndexValuateDate使用此复合索引，并按给定顺序放置列：我可以建议你通过
DATE数据类型，不是DECIMAL(8,0).与其他数据库不同，mysql没有“rowid”。取而代之的是
PRIMARY KEY在btree中用于对数据进行排序。