单键索引是否会一个sql走多个？

最新推荐文章于 2023-06-27 09:43:12 发布

partner4java

最新推荐文章于 2023-06-27 09:43:12 发布

阅读量1.4k

点赞数

分类专栏： sql 文章标签： sql null mysql character table server

本文链接：https://blog.csdn.net/partner4java/article/details/7595587

版权

sql 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

当你的sql可以匹配到多个单间索引的时候，大家一般都认为只走一个索引。
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.1.48, for Win32 (ia32)

Connection id:          13
Current database:       pk_test
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.1.48-community MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 43 min 42 sec

Threads: 2  Questions: 2604  Slow queries: 0  Opens: 23  Flush tables: 1  Open tables: 2  Queries per second avg: 0.993
--------------

========================================
首先下面两个表是在同一个库里。


CREATE TABLE `t_carmodelparamvalue2` (
  `ModelParamValueID` int(11) NOT NULL AUTO_INCREMENT,
  `ParameterID` int(11) NOT NULL,
  `ParamValueID` int(11) DEFAULT NULL,
  `ModelID` int(11) NOT NULL,
  `ValueText` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `SeriesID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ModelParamValueID`),
  KEY `ModelID` (`ModelID`),
  KEY `SeriesID` (`SeriesID`),
  KEY `ak_paramvalue_parameterid` (`ParameterID`)
) ENGINE=InnoDB AUTO_INCREMENT=1254098 DEFAULT CHARSET=gb2312;

mysql> select count(*) from `t_carmodelparamvalue2`;
+----------+
| count(*) |
+----------+
|      500 |
+----------+
1 row in set (0.00 sec)
mysql> explain select * from T_CarModelParamValue2 where modelid = 276666 and ParameterID = 180;
+----+-------------+-----------------------+------+-----------------------------------+---------+---------+-------+------+-------------+
| id | select_type | table                 | type | possible_keys                     | key     | key_len | ref   | rows | Extra       |
+----+-------------+-----------------------+------+-----------------------------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | T_CarModelParamValue2 | ref  | ModelID,ak_paramvalue_parameterid | ModelID | 4       | const |    1 | Using where |
+----+-------------+-----------------------+------+-----------------------------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

===================================================

但是，当数据超过百万级的时候呢？
CREATE TABLE `t_carmodelparamvalue` (
  `ModelParamValueID` int(11) NOT NULL AUTO_INCREMENT,
  `ParameterID` int(11) NOT NULL,
  `ParamValueID` int(11) DEFAULT NULL,
  `ModelID` int(11) NOT NULL,
  `ValueText` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `SeriesID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ModelParamValueID`),
  KEY `ModelID` (`ModelID`),
  KEY `SeriesID` (`SeriesID`),
  KEY `ak_paramvalue_parameterid` (`ParameterID`)
) ENGINE=InnoDB AUTO_INCREMENT=1253591 DEFAULT CHARSET=gb2312;

mysql> select count(*) from `t_carmodelparamvalue`;
+----------+
| count(*) |
+----------+
|  1189500 |
+----------+
1 row in set (2.73 sec)
mysql> explain select * from T_CarModelParamValue where modelid = 276666 and ParameterID = 180;
+----+-------------+----------------------+-------------+-----------------------------------+-----------------------------------+---------+------+----
--+-----------------------------------------------------------------+
| id | select_type | table                | type        | possible_keys                     | key                               | key_len | ref  | rows | Extra                                                           |
+----+-------------+----------------------+-------------+-----------------------------------+-----------------------------------+---------+------+------+-----------------------------------------------------------------+
|  1 | SIMPLE      | T_CarModelParamValue | index_merge | ModelID,ak_paramvalue_parameterid | ModelID,ak_paramvalue_parameterid | 4,4     | NULL |1 | Using intersect(ModelID,ak_paramvalue_parameterid); Using where |
+----+-------------+----------------------+-------------+-----------------------------------+-----------------------------------+---------+------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
走的却是两个。

看表现的结论是，当数据量达到百万的时候就会走多个。

其实，是，当一个表做过大量的insert、select或者转换引擎等操作时，就会出现这个问题。

清空那个百万级的表之后还是会先走多个引擎。