mysql没有索引的更新_mysql update 有无索引对比

mysql> desc ProductInfo;

+----------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------------+--------------+------+-----+---------+-------+

| sn | int(11) | NO | PRI | NULL | |

| productIntro | text | NO | | NULL | |

| borrowerInfo | text | NO | | NULL | |

| realBorrower | varchar(128) | YES | | NULL | |

| capitalPurpose | text | NO | | NULL | |

| repaySource | text | NO | | NULL | |

| riskInfo | text | NO | | NULL | |

| safeguard | varchar(50) | NO | | | |

| contractSn | int(11) | YES | | NULL | |

| delegator | int(11) | NO | | NULL | |

| custody | varchar(125) | NO | | | |

| riskLevel | char(1) | NO | | NULL | |

+----------------+--------------+------+-----+---------+-------+

12 rows in set (0.00 sec)

mysql> show index from ProductIno;

ERROR 1146 (42S02): Table 'zjzc.ProductIno' doesn't exist

mysql> show index from ProductInfo;

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| ProductInfo | 0 | PRIMARY | 1 | sn | A | 283 | NULL | NULL | | BTREE | | |

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.01 sec)

mysql> select sn,contractSn from ProductInfo where contractSn=45;

+-----+------------+

| sn | contractSn |

+-----+------------+

| 58 | 45 |

| 301 | 45 |

+-----+------------+

2 rows in set (0.00 sec)

mysql> explain update ProductInfo set contractSn=99 where contractSn=45;

+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

| 1 | SIMPLE | ProductInfo | index | NULL | PRIMARY | 4 | NULL | 283 | Using where |

+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

1 row in set (0.07 sec)

mysql> create index ProductInfo_idx1 on ProductInfo(contractSn);

Query OK, 0 rows affected (0.23 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain update ProductInfo set contractSn=99 where contractSn=45;

+----+-------------+-------------+-------+------------------+------------------+---------+-------+------+------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------------+-------+------------------+------------------+---------+-------+------+------------------------------+

| 1 | SIMPLE | ProductInfo | range | ProductInfo_idx1 | ProductInfo_idx1 | 5 | const | 2 | Using where; Using temporary |

+----+-------------+-------------+-------+------------------+------------------+---------+-------+------+------------------------------+

1 row in set (0.00 sec)

/***************************************************************************************************8

mysql> show create table test100\G;

*************************** 1. row ***************************

Table: test100

Create Table: CREATE TABLE `test100` (

`sn` int(11) NOT NULL COMMENT 'product sn',

`productIntro` text NOT NULL COMMENT '产品介绍',

`borrowerInfo` text NOT NULL COMMENT '借款方信息',

`realBorrower` varchar(128) DEFAULT NULL COMMENT '实际借款人',

`capitalPurpose` text NOT NULL COMMENT '资金用途',

`repaySource` text NOT NULL COMMENT '还款来源',

`riskInfo` text NOT NULL COMMENT '风控信息',

`safeguard` varchar(50) NOT NULL DEFAULT '' COMMENT '保障信息代码列表，格式1,2,3,4',

`contractSn` int(11) DEFAULT NULL COMMENT '合同模版',

`delegator` int(11) NOT NULL COMMENT '产品委托人',

`custody` varchar(125) NOT NULL DEFAULT '' COMMENT '产品管理人',

`riskLevel` char(1) NOT NULL COMMENT '风险级别'

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> show index from test100;

Empty set (0.00 sec)

mysql> explain update test100 set contractSn=99 where contractSn=45;

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

| 1 | SIMPLE | test100 | ALL | NULL | NULL | NULL | NULL | 283 | Using where |

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

mysql> create index test100_idx1 on test100(contractSn);

Query OK, 0 rows affected (0.36 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain update test100 set contractSn=99 where contractSn=45;

+----+-------------+---------+-------+---------------+--------------+---------+-------+------+------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------+-------+---------------+--------------+---------+-------+------+------------------------------+

| 1 | SIMPLE | test100 | range | test100_idx1 | test100_idx1 | 5 | const | 2 | Using where; Using temporary |

+----+-------------+---------+-------+---------------+--------------+---------+-------+------+------------------------------+

1 row in set (0.03 sec)