MySQL索引设计与表分区 [英] MySQL index design with table partitioning

问题描述

我有2个MySQL表格，其网站的架构类似于杂志。

I have 2 MySQL tables with the following schemas for a web site that's kinda like a magazine.

Article (articleId int auto increment ,
         title varchar(100),
         titleHash guid -- a hash of the title
         articleText varchar(4000)
         userId int)

User (userId int autoincrement
      userName varchar(30)
      email etc...)

最重要的查询是;

select title,articleText,userName,email 
from Article inner join user
on article.userId = user.UserId
where titleHash = <some hash>

我正在考虑将articleId和titleHash列一起用作Article表的聚簇主y。并且userId和userName作为用户表的主键。
因为搜索将基于titlehash和userName列。

I am thinking of using the articleId and titleHash columns together as a clustered primary y for the Article table. And userId and userName as a primary key for the user table. As the searches will be based on titlehash and userName columns.

此外，titlehash和userName在设计上是unqiue，不会正常更改。

Also titlehash and userName are unqiue by design and will not change normally.

articleId和userid列不是业务键，对应用程序不可见，因此它们只能用于连接。

The articleId and userid columns are not business keys and are not visible to the application, so they'll only be used for joins.

我将在titlehash列上使用mysql表分区，因此选择将更快，因为db将能够使用基于该列的分区消除。

I'm going to use mysql table partitioning on the titlehash column so the selects will be faster as the db will be able to use partition elimination based on that column.

我使用innoDB作为存储引擎;

I'm using innoDB as the storage engine;

所以这是我的问题;

1. 我是否需要在
   的titlehash列上创建另一个索引，因为主
   键（articleId，titlehash）不是
   在
   titlehash列上搜索，因为它是主键上的第二个
   列？

1. Do I need to create another index on the titlehash column as the primary key (articleId,titlehash) is not good for the searches on the titlehash column as it is the second column on the primary key ?

这个$有什么问题b $ b设计？

What are the problems with this design ?

我需要这个提示非常快，并期望表有数百万行，请注意 int Id列对业务层不可见，永远不能用于查找记录

I need the selects to be very fast and expects the tables to have millions of rows and please note that the int Id columns are not visible to the business layer and can never be used to find a record

我来自sql server后台并且使用mysql，因为在sql server上使用分区将花费我一大笔钱，因为它只在企业版中提供。

I'm from a sql server background and going to use mysql as using the partitioning on sql server will cost me a fortune as it is only available in the Enterprise edition.

所以DB大师，请帮助我;非常感谢。

So DB gurus, please help me; Many thanks.

推荐答案

如上所述，您的最重要的查询实际上似乎不涉及用户表。如果没有丢失的东西，加快这一点的最好方法是从图片中获取用户表并在 titleHash 。繁荣，完成。

As written, your "most important query" doesn't actually appear to involve the User table at all. If there isn't just something missing, the best way to speed this up will be to get the User table out of the picture and create an index on titleHash. Boom, done.

如果该查询还有另一个条件，我们需要知道提供更具体的建议是什么。

鉴于您的更改，关键应该是所有必需的：

Given your changes, all that should be necessary as far as keys should be:

• On Article ：
  
  
  
  • PRIMARY KEY（articleId）（没有其他栏目，不要试图看中）
  
  
  • KEY（userId）
  
  
  • UNIQUE KEY（titleHash）
  
  
  • On Article:
    • PRIMARY KEY (articleId) (no additional columns, don't try to be fancy)
    • KEY (userId)
    • UNIQUE KEY (titleHash)
    
    
    • PRIMARY KEY（userId）
    
    
    • PRIMARY KEY (userId)

    不要试图使用复合主键。 InnoDB可以更有效地处理由自动递增整数组成的主键，因为该键可以在内部用作行ID。实际上，您可以免费获得一个整数主键。

    Don't try to get fancy with composite primary keys. Primary keys which just consist of an autoincrementing integer are handled more efficiently by InnoDB, as the key can be used internally as a row ID. In effect, you get one integer primary key "for free".

    最重要的是，使用实际数据进行测试并查看 EXPLAIN的结果查询。

    Above all else, test with real data and look at the results from EXPLAINing your query.

    这篇关于MySQL索引设计与表分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！