mysql fix 使用 where;

2021-12-21 00:00:00 indexing sql where mysql query-optimization

我的 SQL 查询:

SELECT *
FROM updates_cats
WHERE uid =118697835834
ORDER BY created_date ASC

当前索引:

index1(uid, created_date)

解释扩展结果:

1 SIMPLE updates_cats ref index1 index1 8 const 2 100.00 Using where

如何修复 Extra 字段使用的地方，以便它可以使用索引?

How can i fix the Extra field where it has Using where so it can use the indexes instead?

显示创建表:

CREATE TABLE `updates_cats` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `u_cat_id` bigint(20) NOT NULL DEFAULT '0',
 `uid` bigint(20) NOT NULL,
 `u_cat_name` text COLLATE utf8_unicode_ci NOT NULL,
 `total_updates` int(11) unsigned NOT NULL DEFAULT '0',
 `created_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`),
 KEY `index1` (`uid`,`created_date`)
) ENGINE=MyISAM AUTO_INCREMENT=23522 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

推荐答案

唯一比 Using where 更好的是 Using where;将 index 与覆盖索引"一起使用.尝试只选择 uid 和 created_date.

The only thing that would be better than Using where is Using where; Using index with a "covering index". Try selecting just uid and created_date.

Using where 很好.这意味着它将指定的索引应用于 WHERE 子句并减少返回的行.要摆脱它，您必须摆脱 WHERE 子句.

Using where is fine. It means it's applying the indicated index to the WHERE clause and reducing the rows returned. To get rid of it, you'd have to get rid of the WHERE clause.

以下是您应该关注的事项:

Here are things that you should be concerned about:

  • 使用文件排序
  • 使用临时
  • 未使用索引:EXPLAIN 的 'key' 列中的 NULL 和 'rows' 列中的大量行.
  • Using filesort
  • Using temporary
  • Not using an index: NULL in the 'key' column of the EXPLAIN and a large number of rows in the 'rows' column.

您的 EXPLAIN 结果表明 MySQL 正在将 index1 应用于 WHERE 子句并返回 2 行:

Your EXPLAIN result shows that MySQL is applying index1 to the WHERE clause and returning 2 rows:

1 SIMPLE updates_cats ref index1 index1 8 const 2 100.00 Using where

相关文章