MySQL:我应该使表中的所有列都建立索引吗? [英] MySQL: should I make all columns in my table indexed?

问题描述

我有一个数据透视表，当然每一行都将包含在查询中:

I have a pivot table, well of course every row will be included in a query:

mysql> select * from blog_posts as bp 
       join blog_joins as bj 
       on bj.post_id=1 
       and bj.taxonomy_id=10
       and bj.type = 1;

这是我的表结构:

是否建议为每一列建立索引?如果没有，您为什么会推荐什么?

Is it recommended to make an index for each column? If not, why and what would you recommend?

mysql > alter table blog_joins add index pid (post_id);
mysql > alter table blog_joins add index tid (taxonomy_id);
mysql > alter table blog_joins add index tp (type);

推荐答案

对于该特定查询，您可能会受益于多列索引:

For that specific query you would probably benefit from a multi-column index:

alter table blog_joins add index pid_tid_tp (post_id,taxonomy_id,type);

我建议对您的代码进行性能分析.尝试将现实的数据添加到您的测试数据库，并尝试一些不同的索引.使用EXPLAIN查看MySQL实际用于每个查询的索引.

I'd recommend profiling your code. Try adding realistic data to your test database and try out a few different indexes. Use EXPLAIN to see which indexes MySQL actually uses for each query.

完成测试后，删除所有未使用的索引，因为尽管索引可以加快查询速度，但也会降低对表的修改速度.

Once you have finished testing remove any unused indexes because while indexes can speed up queries, they will also slow down modifications to the table.

这篇关于MySQL:我应该使表中的所有列都建立索引吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！