表有索引，还是提示创建索引

[wxlun]

版本：./soar --version; Version: 2019-01-21 16:54:09 +0800 0.11.0-16-gc12ae96 Branch: master Compile: 2019-01-21 16:55:46 +0800 by go version go1.10.7 linux/amd64 GitDirty: 0

user_id是主键，输出的信息如下：

echo "select 1 from usertest where user_id=87;" | ./soar -log-output=soar.log Query: EEDFD74439CB0F05 ★ ★ ★ ★ ☆ 90分

SELECT 1 FROM usertest WHERE user_id= 87 Explain信息 id select_type table partitions type possible_keys key key_len ref rows filtered scalability Extra 1 SIMPLE usertest NULL const PRIMARY PRIMARY 8 const 1 ☠️ 100.00% ☠️ O(n) Using index Explain信息解读 SelectType信息解读 SIMPLE: 简单SELECT(不使用UNION或子查询等). Type信息解读 const: const用于使用常数值比较PRIMARY KEY时, 当查询的表仅有一行时, 使用system. 例:SELECT * FROM tbl WHERE col = 1. Extra信息解读 Using index: 只需通过索引就可以从表中获取列的信息, 无需额外去读取真实的行数据. 如果查询使用的列值仅仅是一个简单索引的部分值, 则会使用这种策略来优化查询. 为ysx_dbatest库的usertest表添加索引 Item: IDX.001

Severity: L2

Content: 为列user_id添加索引

Case: ALTER TABLE ysx_dbatest.usertest add index idx_user_id (user_id) ;

[martianzhang]

CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8

Query: B38046026F6A19A8

★ ★ ★ ★ ★ 100分

SELECT
  first_name
FROM
  sakila. actor
WHERE
  actor_id= 1

Explain信息

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	scalability	Extra
1	SIMPLE	actor	NULL	const	PRIMARY	PRIMARY	2	const	1	☠️ 100.00%	O(1)	NULL

Explain信息解读

SelectType信息解读

• SIMPLE: 简单SELECT(不使用UNION或子查询等).

Type信息解读

• const: const用于使用常数值比较PRIMARY KEY时, 当查询的表仅有一行时, 使用system. 例:SELECT * FROM tbl WHERE col = 1.

[martianzhang]

I suggest you update your soar binary by building with the newest version of the source code.