15问答网 > mysql多表联合搜索，每个表都有索引，where的条件会不会使用索引

mysql多表联合搜索，每个表都有索引，where的条件会不会使用索引

2025-02-07 19:05:49

推荐回答（1个）

回答1：

二级索引？？
mysql中每个表都有一个聚簇索引（clustered
index
），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary
indexes）。
以innodb来说，每个innodb表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键，该主键索引是聚集索引。如果你不定义为您的表的主键时，mysql取第一个唯一索引（unique）而且只含非空列（not
null）作为主键，innodb使用它作为聚集索引。如果没有这样的列，innodb就自己产生一个这样的id值，它有六个字节，而且是隐藏的，使其作为聚簇索引。
聚簇索引主要是为了方便存储。。所以二级索引应该都是对聚簇索引的索引。
下面是mysql
manual上的原话，也可能我理解有误。
every
innodb
table
has
a
special
index
called
the
clustered
index
where
the
data
for
the
rows
is
stored.
if
you
define
a
primary
key
on
your
table,
the
index
of
the
primary
key
is
the
clustered
index.
if
you
do
not
define
a
primary
key
for
your
table,
mysql
picks
the
first
unique
index
that
has
only
not
null
columns
as
the
primary
key
and
innodb
uses
it
as
the
clustered
index.
if
there
is
no
such
index
in
the
table,
innodb
internally
generates
a
hidden
clustered
index
on
a
synthetic
column
containing
row
id
values.
the
rows
are
ordered
by
the
id
that
innodb
assigns
to
the
rows
in
such
a
table.
the
row
id
is
a
6-byte
field
that
increases
monotonically
as
new
rows
are
inserted.
thus,
the
rows
ordered
by
the
row
id
are
physically
in
insertion
order.
accessing
a
row
through
the
clustered
index
is
fast
because
the
row
data
is
on
the
same
page
where
the
index
search
leads.
if
a
table
is
large,
the
clustered
index
architecture
often
saves
a
disk
i/o
operation
when
compared
to
storage
organizations
that
store
row
data
using
a
different
page
from
the
index
record.
(for
example,
myisam
uses
one
file
for
data
rows
and
another
for
index
records.)
in
innodb,
the
records
in
non-clustered
indexes
(also
called
secondary
indexes)
contain
the
primary
key
value
for
the
row.
innodb
uses
this
primary
key
value
to
search
for
the
row
in
the
clustered
index.
if
the
primary
key
is
long,
the
secondary
indexes
use
more
space,
so
it
is
advantageous
to
have
a
short
primary
key.

最新问答

形容一个人没有做事情的态度的古代诗词或诗句

一部电影两个卧底警察去了高中学校

就是ug10.0里面通过点生成样条命令在哪里，找不到，做逆向需要

家里房子比如100万，首付50万元，现在才交了5年房贷，现在想卖了，卖120万，该怎么处理？