需要有关表创建和使用文本主键的最佳实践建议
我正在创建一个 MySQL MyISAM （需要全文搜索）表。
- column_1 - 包含一个 TEXT 主键（数据将是一个 64 位编码字符串）
- column_2 - 引用另一个表并用于连接
- column_3 - 另一个 TEXT 列，使用 MATCH 为搜索建立索引
- ...
该表可能保存数十亿条记录时间。
column_1 主搜索将在主键列上执行，如下所示。例如，
SELECT * FROM table WHERE column_1 = 123;
column_2 主搜索将按如下方式执行：
SELECT * FROM table_1
JOIN table_2 ON ( table_1.column_2 == table_2.id );
column_3 主搜索将按如下方式执行：
SELECT column_3, MATCH ( column_3 )
AGAINST ( 'TOKEN' ) AS score
FROM table_1;
我想就需要设置的索引类型以及任何其他听起来相关的建议寻求建议。
提前致谢。
PS
我是否正确地认为，如果您进行搜索，例如，
SELECT * FROM table WHERE id = 1; (where id column is not indexed)
对大量数据库的搜索会比对列建立索引慢？
I'm creating a MySQL MyISAM (Full textual searches are needed) table.
- column_1 - contains a TEXT primary key (Data will be a 64 bit encoded string)
- column_2 - references another table and is used for joins
- column_3 - another TEXT column indexed for searches using MATCH
- ...
The table is likely to hold billions of records over time.
column_1 main search would be performed on the primary key column as follows. e.g.
SELECT * FROM table WHERE column_1 = 123;
column_2 main search would be performed as follows:
SELECT * FROM table_1
JOIN table_2 ON ( table_1.column_2 == table_2.id );
column_3 main search would be performed as follows:
SELECT column_3, MATCH ( column_3 )
AGAINST ( 'TOKEN' ) AS score
FROM table_1;
I would like to take advice on the sort of indexes I would need to setup and any other advice that sounds relevant.
Thanks in advance.
P.S
Am I right in thinking that if you do a search e.g.
SELECT * FROM table WHERE id = 1; (where id column is not indexed)
The search on a substantial db would be slower than if the column was indexed?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
你的最终假设是正确的。该索引由专门为搜索而定制的数据结构组成，其中列作为键，指向正确行的指针作为值。对非索引字段的搜索将需要全表扫描（使数据库检查表的每一行）。
You are right in your final assumption. The index is made up by a data structure specifically tailored for searching in, with the column as key and a pointer to the correct row as the value. A search on a non-indexed field will require a full table scan (making the db examine every row of the table).