使用tenant_id进行Mysql复合索引 [英] Mysql composite indexing with tenant_id

问题描述

我们有一个多租户应用程序，它有一个包含129个字段的表，这些字段都可以在WHERE和ORDER BY子句中使用。我花了5天时间试图为我们找到最好的索引策略，我获得了很多知识，但我仍然有一些问题。

1）创建索引时应该我总是首先使用tenant_id作为复合索引？（所有查询在WHERE子句中都有tenant_id =？）

2）因为所有列都可以使用在WHERE子句和order by子句中，我应该为它们创建索引吗？ （正确的是当我通过一个没有索引的列进行订购时，需要6s来执行一个拥有大约1,500,000行的租户）

3）制作PK（tenant_id， ID），但是这不会影响到该表的连接吗？

任何关于如何处理这个问题的建议都会非常感激。

======
数据库引擎是InnoDB

=======

结构：

  ID bigint（20）auto_increment primary 
 tenant_id int（11 ）
 created_by int（11）
 created_on时间戳
 updated_by int（11）
 updated_on时间戳
 owner_id int（11）
 first_name VARCHAR（60）
 last_name VARCHAR（60）
。 
。 
。 
（大约120个其他列都是可搜索的）
  

解决方案

对这些问题进行简要回答。据我所知，你对使用索引感到困惑

如果比率为< - >考虑在列上创建索引/ p>

代价1 -

（列的唯一条目数）/（列中的总条目数）〜= 1

这是计数特定列中的DISTINCT行数很高。

创建额外 索引将总是为MySQL服务器创造开销，所以绝不能创建索引的每一列。 单个表可以拥有的索引数量限制=每个表64个

现在，如果你的所有搜索查询中都存在 tenant_id ，您应该将其视为索引或复合键，

提供 -

考虑2 - UPDATEs 的数量小于<$ c的 SELECTs 的数量$ c> tenant_id

代价3 - indexe s在数据类型方面应该尽可能小。您绝不能创建 varchar 64 索引

http://www.mysqlperformanceblog.com/2012/08/16/mysql-indexing-best- practice-webinar-questions-followup /

指向注1 - 即使您确实将任何列声明为索引，MySQL优化器仍可能不会将其视为查询执行的最佳计划。所以总是使用 EXPLAIN 来了解最新情况。 http://www.mysqlperformanceblog.com/2009/ 09/12 / 3-ways-mysql-uses-indexes /

指向注2 -
您可能希望缓存您的搜索查询，因此请记住不要在 SELECT中使用不可预测的语句查询，例如 NOW（）

最后 - 制作PK（tenant_id， ID）不应该影响表的连接。

一个很棒的链接，可以回答你所有的问题 - http://www.percona.com/files/presentations/WEBINAR-MySQL-Indexing-Best-Practices.pdf

We have a multitenant application that has a table with 129 fields that can all be used in WHERE and ORDER BY clauses. I spent 5 days now trying to find out the best indexing strategy for us, I gained lot of knowledge but I still have some questions.

1) When creating an index should I always make it a composite index with tenant_id in the first place ?(all queries have tenant_id = ? in there WHERE clause)

2) Since all the columns can be used in both the WHERE clause and the order by clause, should I create an index on them all ? (right know when I order by a column that has no index it takes 6s to execute with a tenant that has about 1,500,000 rows )

3) make the PK (tenant_id, ID), but wouldn't this affect the joins to that table ?

Any advice on how to handle this would be much appreciated.

====== The database engine is InnoDB

=======

structure :

ID bigint(20) auto_increment primary
tenant_id int(11)
created_by int(11)
created_on Timestamp
updated_by int(11)
updated_on Timestamp
owner_id int(11)
first_name VARCHAR(60)
last_name VARCHAR(60)
.
.
.
(some 120 other columns that are all searchable)

解决方案

A few brief answers to the questions. As far as I can see you are confused with using indexes

Consider creating Indexes on columns if the Ratio -

Consideration 1 -

(Number of UNIQUE Entries of the Columns)/(Number of Total Entries in the Column) ~= 1

That is Count of DISTINCT rows in a particular column is high.

Creating an extra index will always create overhead for the MySQL server, so you MUST NOT create every column an index. There is also a limit on number of indexes your single table can have = 64 per table

Now if your tenant_id is present in all the search queries, you should consider it as an index or in a composite key,

provided that -

Consideration 2 - number of UPDATEs are less that number of SELECTs on the tenant_id

Consideration 3 - The indexes should be as small as possible in terms of data types. You MUST NOT create a varchar 64 an index
http://www.mysqlperformanceblog.com/2012/08/16/mysql-indexing-best-practices-webinar-questions-followup/

Point to Note 1 - Even if you do declare any column an index, MySQL optimizer may still not consider it as best plan of query execution. So always use EXPLAIN to know whats going on. http://www.mysqlperformanceblog.com/2009/09/12/3-ways-mysql-uses-indexes/

Point to Note 2 - You may want to cache your search queries, so remember not to use unpredicted statements in your SELECT queries, such as NOW()

Lastly - making the PK (tenant_id, ID) should not affect the joins on your table.
And an awesome link to answer all your questions in general - http://www.percona.com/files/presentations/WEBINAR-MySQL-Indexing-Best-Practices.pdf

这篇关于使用tenant_id进行Mysql复合索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！