mysql - 索引我的表 mysql

Question

我有一个巨大的表，其中包含超过 1 亿条记录。该表的格式如下:

id (AUTO_INCREMENT) - main key | username VARCHAR(100) | email VARCHAR(100) | ip VARCHAR(30) | hash VARCHAR(150) | salt VARCHAR(100)

我对索引很陌生，当我运行此查询时我意识到了潜力:

"SELECT * FROM users WHERE id = 1000000;"

查询以闪电般的速度提供数据，而如果我运行不同的查询，例如:

"SELECT * FROM users WHERE email = 'test@test.com';"

获取结果需要一分多钟的时间，我知道这是因为该表未建立索引。

我想要针对表运行的查询如下:

"SELECT * FROM users WHERE INSTR('email', 'test@');"

和

"SELECT * FROM users WHERE INSTR('username', 'testuser');"

我可以提供的一些其他信息是用户名和电子邮件列不是唯一的。当我创建列时，我没有指定它们是否可以是“DEFAULT NULL”或“NOT NULL”。

我想知道索引该表的最佳方法是什么，以便我可以运行上面的查询？我尝试过研究，但我有点困惑。

哦，还有一点要注意，我使用的数据库是mariadb，后端是innodb。

Answer 1

在我们开始建立索引之前，您可以改进一些事情;):

• 列定义:
  • ID、用户名和电子邮件对我来说似乎不错
  • ip:我会将其存储为 int unsigned然后使用MySQL INET_ATON和INET_NTOA存储/检索值的函数。如果您想存储 IPv6 地址或混合 IPv4 和 IPv6，事情会变得稍微棘手，但总体来说它的工作原理是相同的(Google 是您的 friend )
  • 哈希:我从未听说过长度可变的哈希，最多 150 个字符；)找出哈希的长度，然后使用 BINARY具有此长度的字段(如果您想以十六进制而不是二进制存储它，则使用 CHAR 和双倍长度)，例如BINARY(20)或CHAR(40)对于 SHA-1
  • 盐:最大长度 100 对我来说似乎有点超出了盐的上限，但无论如何都能让你的船漂浮。
• 查询:您确定要使用 INSTR ？我这样问是因为这样您可以返回搜索词位于文本中任何位置的所有行，例如ThisIsMySuperFancytestuser!!! 。特别是对于用户来说，这可能不是您想要的；)此外，这使得无法使用任何索引。就做一个简单的SELECT * FROM users WHERE username = 'testuser';对于用户和 SELECT * FROM users WHERE email LIKE 'test@%';如果您想让域名保持开放状态，请获取电子邮件。 (请注意，B 树索引 [您将要使用] 与 LIKE 的通配符一起使用，只要它们不在开头，因此 SELECT * FROM users WHERE email LIKE '%test@%'; 将无法使用索引 [此查询是与您的 INSTR 示例基本相同]，请参阅 https://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html 。

现在，对于索引。您可以简单地添加这样的索引 CREATE INDEX inx_username ON table (username); 。使用索引时有大量的微妙之处和陷阱，所以我建议你沿着书本走很长一段路(或谷歌;)至少看看MySQL引用:https://dev.mysql.com/doc/refman/5.7/en/create-index.html - 在那里您将学到一些知识，例如仅对列的前 n 个字符建立索引以节省一些内存;)

祝你好运