<legend id='eiS3Q'><style id='eiS3Q'><dir id='eiS3Q'><q id='eiS3Q'></q></dir></style></legend>
      <bdo id='eiS3Q'></bdo><ul id='eiS3Q'></ul>

      <small id='eiS3Q'></small><noframes id='eiS3Q'>

      <tfoot id='eiS3Q'></tfoot>
      1. <i id='eiS3Q'><tr id='eiS3Q'><dt id='eiS3Q'><q id='eiS3Q'><span id='eiS3Q'><b id='eiS3Q'><form id='eiS3Q'><ins id='eiS3Q'></ins><ul id='eiS3Q'></ul><sub id='eiS3Q'></sub></form><legend id='eiS3Q'></legend><bdo id='eiS3Q'><pre id='eiS3Q'><center id='eiS3Q'></center></pre></bdo></b><th id='eiS3Q'></th></span></q></dt></tr></i><div id='eiS3Q'><tfoot id='eiS3Q'></tfoot><dl id='eiS3Q'><fieldset id='eiS3Q'></fieldset></dl></div>

        如何在 MySQL 中为多对多连接正确索引链接表?

        How to properly index a linking table for many-to-many connection in MySQL?(如何在 MySQL 中为多对多连接正确索引链接表?)

          <i id='ggjC0'><tr id='ggjC0'><dt id='ggjC0'><q id='ggjC0'><span id='ggjC0'><b id='ggjC0'><form id='ggjC0'><ins id='ggjC0'></ins><ul id='ggjC0'></ul><sub id='ggjC0'></sub></form><legend id='ggjC0'></legend><bdo id='ggjC0'><pre id='ggjC0'><center id='ggjC0'></center></pre></bdo></b><th id='ggjC0'></th></span></q></dt></tr></i><div id='ggjC0'><tfoot id='ggjC0'></tfoot><dl id='ggjC0'><fieldset id='ggjC0'></fieldset></dl></div>

                <tbody id='ggjC0'></tbody>

              <tfoot id='ggjC0'></tfoot>

              <small id='ggjC0'></small><noframes id='ggjC0'>

            • <legend id='ggjC0'><style id='ggjC0'><dir id='ggjC0'><q id='ggjC0'></q></dir></style></legend>
                • <bdo id='ggjC0'></bdo><ul id='ggjC0'></ul>
                • 本文介绍了如何在 MySQL 中为多对多连接正确索引链接表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  假设我在表table1"和table2"之间有一个简单的多对多表,它由两个 int 字段组成:table1-id"和table2-id".我应该如何索引这个链接表?

                  我曾经只创建一个复合主索引 (table1-id,table2-id),但我读到如果您更改查询中字段的顺序,该索引可能不起作用.那么最佳的解决方案是什么——在没有主索引的情况下为每个字段创建独立的索引?

                  谢谢.

                  解决方案

                  这取决于你的搜索方式.

                  如果你这样搜索:

                  /* 给定 table1 中的值,从 table2 中查找所有相关值 */选择 *从表 1 t1JOIN table_table tt ON (tt.table_1 = t1.id)JOIN table2 t2 ON (t2.id = tt.table_2)哪里 t1.id = @id

                  那么你需要:

                  ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_1, table_2)

                  在这种情况下,table1 将在 NESTED LOOPS 中领先,并且您的索引仅在 table1 首先被编入索引时才可用.>

                  如果你这样搜索:

                  /* 给定 table2 中的一个值,从 table1 中查找所有相关值 */选择 *从表 2 t2JOIN table_table tt ON (tt.table_2 = t2.id)JOIN table1 t1 ON (t1.id = tt.table_1)哪里 t2.id = @id

                  那么你需要:

                  ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_2, table_1)

                  出于上述原因.

                  这里不需要独立索引.可以在第一列上使用普通索引的任何地方都可以使用复合索引.如果您使用独立索引,您将无法有效地搜索这两个值:

                  /* 检查两个给定值之间是否存在关系 */选择 1FROM table_tableWHERE table_1 = @id1AND table_2 = @id2

                  对于这样的查询,两列至少需要一个索引.

                  为第二个字段添加一个额外的索引从来都不是坏事:

                  ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 PRIMARY KEY (table_1, table_2)在 table_table (table_2) 上创建索引 ix_table2

                  主键将用于两个值的搜索和基于table_1值的搜索,附加索引将用于基于值的搜索table_2.

                  Lets say I have a simple many-to-many table between tables "table1" and "table2" that consists from two int fields: "table1-id" and "table2-id". How should I index this linking table?

                  I used to just make a composite primary index (table1-id,table2-id), but I read that this index might not work if you change order of the fields in the query. So what's the optimal solution then - make independent indexes for each field without a primary index?

                  Thanks.

                  解决方案

                  It depends on how you search.

                  If you search like this:

                  /* Given a value from table1, find all related values from table2 */
                  SELECT *
                  FROM table1 t1
                  JOIN table_table tt ON (tt.table_1 = t1.id)
                  JOIN table2 t2 ON (t2.id = tt.table_2)
                  WHERE t1.id = @id
                  

                  then you need:

                  ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_1, table_2)
                  

                  In this case, table1 will be leading in NESTED LOOPS and your index will be usable only when table1 is indexed first.

                  If you search like this:

                  /* Given a value from table2, find all related values from table1 */
                  SELECT *
                  FROM table2 t2
                  JOIN table_table tt ON (tt.table_2 = t2.id)
                  JOIN table1 t1 ON (t1.id = tt.table_1)
                  WHERE t2.id = @id
                  

                  then you need:

                  ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_2, table_1)
                  

                  for the reasons above.

                  You don't need independent indices here. A composite index can be used everywhere where a plain index on the first column can be used. If you use independent indices, you won't be able to search efficiently for both values:

                  /* Check if relationship exists between two given values */
                  SELECT 1
                  FROM table_table
                  WHERE table_1 = @id1
                    AND table_2 = @id2
                  

                  For a query like this, you'll need at least one index on both columns.

                  It's never bad to have an additional index for the second field:

                  ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 PRIMARY KEY (table_1, table_2)
                  CREATE INDEX ix_table2 ON table_table (table_2)
                  

                  Primary key will be used for searches on both values and for searches based on value of table_1, additional index will be used for searches based on value of table_2.

                  这篇关于如何在 MySQL 中为多对多连接正确索引链接表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  Set the variable result, from query(设置变量结果,来自查询)
                  What is dynamic SQL?(什么是动态 SQL?)
                  Mysql - How to quit/exit from stored procedure(Mysql - 如何退出/退出存储过程)
                  Does MySQL have time-based triggers?(MySQL 有基于时间的触发器吗?)
                  is it possible to call a sql script from a stored procedure in another sql script?(是否可以从另一个 sql 脚本中的存储过程调用 sql 脚本?)
                  Procedure to loop through comma separated string is not working(遍历逗号分隔字符串的过程不起作用)

                    <tfoot id='tnOOG'></tfoot>
                    <i id='tnOOG'><tr id='tnOOG'><dt id='tnOOG'><q id='tnOOG'><span id='tnOOG'><b id='tnOOG'><form id='tnOOG'><ins id='tnOOG'></ins><ul id='tnOOG'></ul><sub id='tnOOG'></sub></form><legend id='tnOOG'></legend><bdo id='tnOOG'><pre id='tnOOG'><center id='tnOOG'></center></pre></bdo></b><th id='tnOOG'></th></span></q></dt></tr></i><div id='tnOOG'><tfoot id='tnOOG'></tfoot><dl id='tnOOG'><fieldset id='tnOOG'></fieldset></dl></div>
                    <legend id='tnOOG'><style id='tnOOG'><dir id='tnOOG'><q id='tnOOG'></q></dir></style></legend>

                        <tbody id='tnOOG'></tbody>

                      <small id='tnOOG'></small><noframes id='tnOOG'>

                      • <bdo id='tnOOG'></bdo><ul id='tnOOG'></ul>