我在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 setexplain 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 setexplain 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 setexplain 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 setmysql> select table_schema,table_name,index_schema,index_name,seq_in_index,column_name,cardinalityfrom information_schema.statisticswhere 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 setmysql>
表上有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中用于对数据进行排序。