MySQL索引不适用于特定表

本文介绍了MySQL索引不适用于特定表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在MySQL 5.6版中有大量数据 并假设名称为columnA，columnB和& C列.

I have a large set of data in the MySQL version 5.6 And Suppose name columnA, columnB, & columnC.

columnA(bigint)columnB(bigint)时间(时间戳)

columnA(bigint) columnB(bigint) Time(timestamp)

我在其中应用了BTREE UNIQUE INDEXING.

Where I have applied BTREE UNIQUE INDEXING.

但是当我解释查询时，我遍历了所有行.

But When I explaining my query, I am getting all rows traversed.

我试图分析，修复表，但是没有运气.

I have tried to Analyze, Repair table but no luck.

注意:

在不同的表中，应用到索引后，我得到了预期的结果.

In different tables, I am getting expected result after applying to index.

我遇到问题的columnA(bigint 20)的大小超过50,000.

The size of columnA(bigint 20) where I am having an issue, is more than 50,000.

我要在索引之后遍历一行.

I want one row traversed after indexing.

Mysql数据库存储在索引上显示0B.

Mysql database storage shows 0B on Index.

CREATE TABLE TableA ( ColumnA bigint(20) NOT NULL DEFAULT '0', ColumnB bigint(20) NOT NULL DEFAULT '0', ColumnC bigint(20) NOT NULL DEFAULT '0', ColumnD bigint(20) NOT NULL DEFAULT '0', ColumnE timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `TableA` ADD UNIQUE KEY `idx_npp_pid` (`ColumnA`), ADD KEY `idx_npp_uid` (`ColumnD`); COMMIT; EXPLAIN SELECT `ColumnA` FROM `TableA` WHERE `ColumnA`=4444;

说明结果:

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE TableA ALL NULL NULL NULL NULL 132244 Using where

推荐答案

Mysql数据库存储在索引上显示0B.

Mysql database storage shows 0B on Index.

InnoDB始终需要一个主键，因为它会将主键与数据聚在一起(这就是为什么它也称为聚簇索引)的原因.这就是为什么当InnoDB表上仅定义主键时，索引没有显式磁盘存储的原因.

InnoDB always needs a Primary Key, because it clusters the Primary Key with Data (and that is why it is also called as Clustered Index). That is why, there is no explicit disk storage for Index, when there is only Primary Key defined on an InnoDB table.

现在，如果您没有显式定义主键，则InnoDB会寻找第一个唯一键(该键也不为NULL)隐式定义为主键.如果没有这样的密钥，它将创建一个隐藏的主密钥.因此，定义主键总是更好.在您的情况下，它隐式地将ColumnA视为主键，因为它是UNIQUE且NOT NULL.

Now, if you don't explicitly define a Primary Key, InnoDB looks for the first Unique Key which is NOT NULL also, to be implicitly defined as Primary Key. If no such key is available, it creates a hidden Primary Key. So, it is always better to define a Primary Key. In your case, it has implicitly considered ColumnA as the primary key because it is UNIQUE and NOT NULL.

如果没有自然主键(组合)可用，那么人们通常建议使用auto_increment定义代理主键.

If no Natural Primary Key (combination) is available, then people generally advise to define a Surrogate Primary Key, using auto_increment.

在此处获取更多详细信息: dev.mysql/doc/refman/5.6/en/innodb-index-types.html

Get more details here: dev.mysql/doc/refman/5.6/en/innodb-index-types.html

但是当我解释查询时，我遍历了所有行.

But When I explaining my query, I am getting all rows traversed.

您需要向我们提供您的EXPLAIN..计划结果，以便得出一些结论.

You need to provide us your EXPLAIN.. plan result, to be able to come to some conclusion.