我通常理解索引表的概念(很像纸质书中的索引允许您查找特定术语而无需逐页搜索).但我不清楚何时使用它们.
假设我有3个表：USERS,COMMENTS和VOTES表.我想创建一个类似Stackoverflow的评论线程,其中查询返回注释以及这些注释上/下投票的数量.
USERS table user_id user_name 1 tim 2 sue 3 bill 4 karen 5 ed COMMENTS table comment_id topic_id comment commenter_id 1 1 good job! 1 2 2 nice work 2 3 1 bad job :) 3 VOTES table vote_id vote comment_id voter_id 1 -1 1 5 2 1 1 4 3 1 3 1 4 -1 2 5 5 1 2 4
Here’s the query and SQLFiddle to return the votes on topic_id=1：
select u.user_id,u.user_name,c.comment_id,c.topic_id,c.comment,count(v.vote) as totals,sum(v.vote > 0) as yes,sum(v.vote < 0) as no,my_votes.vote as did_i_vote from comments c join users u on u.user_id = c.commenter_id left join votes v on v.comment_id = c.comment_id left join votes my_votes on my_votes.comment_id = c.comment_id and my_votes.voter_id = 1 where c.topic_id = 1 group by c.comment_id,did_i_vote;
让我们假设评论和投票的数量达到数百万.为了加快查询速度,我的问题是我应该在comments.commenter_id,votes.voter_id和votes.comment_id上放一个索引吗？
解决方法
引擎必须将使用索引的成本与不这样做的成本进行比较.您会注意到我必须添加更多行才能获得所使用的索引.
使用索引,引擎必须使用索引来获取匹配值,这很快.然后它必须使用匹配来查找表中的实际行.如果索引没有缩小行数,那么只需查找表中的所有行就可以更快.
我不确定MysqL是否有类似于sql Server聚簇索引的东西.在这种情况下,索引和表数据具有相同的结构,因此您没有索引查找的第二步.
我以两种不同的方式介绍了索引,首先是通过定义主键在users表上.这将隐式在user_id列上创建唯一索引.唯一索引意味着您不能两次插入相同的值集.对于单列索引,这只意味着您不能两次具有相同的值.
如果您想象一本桌面的用户书,每页有一个用户,那么创建的索引会为您提供一个user_id的排序列表,每个列表都包含用户的页码.该列表通常以某种树形式存储,以便快速查找特定数字.想想你在电话簿中查找名字的方式,你不仅要扫描所有页面,直到找到它,你猜测它会在哪里,然后跳过或转发大块的页面直到你接近.您通常可以在O(log2 n)时间内查找索引中的值,其中n是行数,您需要读取相似数量的索引页.
现在,如果给DB引擎查询select * from user where user_id = 3,它有两个选择.它可以读取每个数据页面,并查找正确的值(它可能会使用主键在第一个时停止的事实).另一种方法是读取索引以获取正确的数据页,然后查找数据页.
为了具体和简单,假设该表有1024个条目.假设每个条目都占用一个数据页面.假设索引树中的每个条目都占用一个索引页.假设索引是平衡的,因此它有10个级别,总共2047个页面. (所有这些假设都是可疑的,但是它们得到了相应的点,特别是索引页几乎总是小于数据页,因为你不倾向于一次索引所有列).
要执行表扫描方法,需要读取1024个数据页.要使用索引,需要读取10个索引页和一个数据页.几乎所有数据库性能都与最小化读取页数有关.
多列索引允许快速查找数据集.如果你有一个索引(col1,col2),即使只是匹配col1也会得到改进.
create index语句只是说明索引了哪些列,以及是否允许重复值.
再次使用本书类比,在投票上创建索引ix_comment_id(comment_id,voter_id)将创建一个有序的comment_id列表,然后是voter_id,并引用相应的数据行.
+------------+--------------+---------+ | comment_id | reference_id | row_ref | +------------+--------------+---------+ | 1 | 4 | ref1 | | 1 | 5 | ref2 | | 2 | 4 | ref3 | | 2 | 5 | ref4 | | 3 | 1 | ref5 | +------------+--------------+---------+
