我的mySQL数据库应该建立哪些索引？

Question

我的mySQL数据库应该建立哪些索引？

5

我正在尝试为我正在开发的程序设计数据库。在发布我的程序之前，我希望设计是完美无瑕的，因为据说一旦启动就很难更改。总之，我的程序是一个用于购买和销售图书的平台。用户可以在“距离”范围内搜索特定ISBN的书籍。他们可以按价格或日期列出图书。我将在表格后描述程序需要执行的操作。

BookListings (table)...

    userID VARCHAR(50) NOT NULL
    dateListed timestamp Default: CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    isbn13 VARCHAR(20) NOT NULL
    price UNSIGNED TINYINT NOT NULL
    email VARCHAR(30) NOT NULL
    phone VARCHAR(20) NOT NULL
    condition VARCHAR(30) NOT NULL
    latitude FLOAT(9,7) NOT NULL
    longitude FLOAT(9,7) NOT NULL

ContactInfo (table)...

   email VARCHAR(30) NOT NULL
   phone VARCHAR(20) NOT NULL

请记住，ContactInfo表并不是非常重要。我会经常清理它，一旦我将信息从数据库中移出以释放空间。如果这个表导致了严重的效率问题，我完全可以牺牲它，我也不会感到困扰。

BannedUsers（表）...

- userID VARCHAR(50) NOT NULL - banReason VARCHAR(50) NOT NULL

BannedUsers表是我将跟踪禁令的地方。它很少被使用，但如果出于某种原因我想禁用某个人使用我的程序，我只需在其中放入他们的userID。当我的程序启动时，它会检查BannedUsers表，看看用户是否被禁止，如果是，它会给出禁止原因。

我有点困惑该在哪里放置索引（我对它们不太了解）。我只是听说索引可以极大地加快搜索速度。对于我的BannedUsers，我认为在userID列上放置索引是显而易见的？如果是这样，应该使用什么类型的索引？

对于BookListings表，我更加困惑该在哪里放置索引。首先，我将解释我的程序的所有功能（需要执行的操作），以及从我的php脚本中获取的查询：

1. 我使用以下查询将列表输入我的系统。当用户想要出售一本书时，他们“列出”这本书。因此，您可以想象这个操作会经常执行...

INSERT INTO Listings
VALUES ('$userID', (NOW() + INTERVAL 2 HOUR), '$isbn13', $price, '$email', '$phone', 
        '$condition', '$latitude', '$longitude')

我在用户想要购买（搜索）某本书且该书在其附近距离内时，会使用以下查询。就像列出图书的查询一样，这个操作也会被经常使用：

SELECT
    *, (
      6371 * acos (
      cos ( radians($userLatitude) )
      * cos( radians( latitude ) )
      * cos( radians( longitude ) - radians($userLongitude) )
      + sin ( radians($userLatitude) )
      * sin( radians( latitude ) )
    )
) AS distance
FROM Listings
WHERE isbn13='$isbn13'
HAVING distance <= $withinDistance
ORDER BY price, dateListed

上面的查询是按价格排序的。下面的查询则按日期排序：

SELECT
    *, (
      6371 * acos (
      cos ( radians($userLatitude) )
      * cos( radians( latitude ) )
      * cos( radians( longitude ) - radians($userLongitude) )
      + sin ( radians($userLatitude) )
      * sin( radians( latitude ) )
    )
) AS distance
FROM Listings
WHERE isbn13='$isbn13'
HAVING distance <= $withinDistance
ORDER BY dateListed DESC

以下查询是在列出书籍后立即执行的。这是收集用户联系信息的一种方法：

INSERT INTO ContactInfo
VALUES ('$email', '$phone')

以下查询语句用于程序开始时查找系统中列出了多少本书（只是为了保持对系统处理的书籍数量的有趣计数，不是非常重要）：

SELECT COUNT(*) FROM ContactInfo

以下查询用于查找用户在系统中列出的所有书籍。由于用户需要在删除他们创建的列表之前执行此操作，因此此操作将经常进行。它基本上用于显示所有列表，然后用户选择要删除的列表。

SELECT dateListed, isbn13, price 
FROM Listings 
WHERE userID='$userID' 
ORDER BY dateListed DESC

以下是实际删除列表的查询语句：

DELETE FROM Listings WHERE userID='$userID' AND isbn13='$isbn13

请帮助我让我的设计更高效。我不太确定应该在哪里建立索引，因为我知道建立索引会使更新和删除变得更加困难..而我的程序也需要这样做。最初，我考虑对isbn13进行索引（主要搜索对象），但后来意识到我还将搜索纬度和经度，所以我不确定这些是否也需要建立索引...这真的让我很困惑。请告诉我有什么可以改进数据库设计和查询的方法。

- user4233467

有两个索引- Listings(userID)和Listings(isbn13)，但是需要一个地理空间专家来就distance优化给您建议。 - StuartLC

考虑查看以下问题及其答案：使用索引优化MySQL数据库，如何通过索引提高速度，如何加速SQL查询。 - Barranka

如果你住在老挝以东，我不知道会发生什么？ - Strawberry

我不确定？你指的是什么？ - user4233467

如果你的经度是一个3位数，怎么办？ - Strawberry

你能举个例子吗？ 3位数是指X.XX吗？我对坐标不是很熟悉。 - user4233467

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Neil McGuigan · Accepted Answer

没有免费的午餐。索引既有好处也有成本。

好处是某些操作会更快。

成本是某些操作会变慢，并且您将消耗更多的磁盘空间和内存。

查找记录（包括查找更新和删除记录）将更快，但更新、删除和插入记录将更慢，因为需要更新索引。

您的查询目前很慢吗？为什么？您需要查看执行计划以了解它们为什么很慢。如果它们由于顺序扫描而变慢，则尝试添加索引。这如何影响插入、删除和更新操作？这是否值得成本？您是否有足够的磁盘空间和内存来支持这些索引？这些是我们无法为您回答的问题。