唯一性太差的字段不宜建立索引的原因

[imbigsnake1]

因为mysql首先会将索引中的键值取出来与内存中存储表数据的页中的数据相比较，但是数据页中的数据的顺序和索引队列中键值的顺序并不是一致的。假如索引中的键值a先在数据页x中找到了符合的数据，然后又在数据页y中找到了符合条件的数据，这时mysql便会把数据页x销毁掉，把数据页Y读到内存中。如果这时候还有键值b，然后键值b找的数据又在数据页x上，则mysql又要把数据页x读到内存中。也就是说从索引去寻找对应的表数据的时候是随机访问的。（实际情况应该是内存中缓存了好几页的数据，应该不只一页，但是这里假定线程内存中只存在一张页表）。这样的随机访问所造成的io消耗是比全表扫描的io消耗来得大的。（还不如遍历整张表）
假如索引字段唯一性好的话，比如是唯一的，则最多只需要换一次页表。
假如索引字段唯一性差的话，需要进行的换页次数也就相应的提高了。


以上是我以前根据书上的内容作的总结。但是最近又想想其实完全可以在找到索引后直接定位到记录的物理位置然后取出来。
所以真相到底是怎么样的呢，求赐教。

[imbigsnake1]

如果觉得我上半部分不知道在说什么的可以直接略过，给出自己的观点就好

[rucypli]

select * from tb where gender='男'

这种不仅需要扫描这个索引 而且去取有‘男’的数据块基本覆盖了整个表所涉及的数据块

[imbigsnake1]

[Quote=引用 2 楼 rucypli 的回复:]

select * from tb where gender='男'

这种不仅需要扫描这个索引 而且去取有‘男’的数据块基本覆盖了整个表所涉及的数据块
[/Quote]
所以说为重复性高的字段建索引最多是提高的效率不高。而不会像我上面说的那样反而降低查找速度？
那个解释的观点是我之前看的一本mysql优化的书上看到的，现在想想有很多点没弄明白

[imbigsnake1]

我试了一下确实是这样，但是为什么这种情况下使用索引比全表扫描来的慢呢

[popo666]

竟然可以这样

[imbigsnake1]

[Quote=引用 5 楼 acmain_chm 的回复:]

引用所以说为重复性高的字段建索引最多是提高的效率不高。而不会像我上面说的那样反而降低查找速度？举个极端的例子

表A (ID,col1,col2,col3,col4,..col100) 100个字段，现在COL4上创建索引，而COL4中所有的值都为1, update a set col4=1 ，10000条记录，COL4都是1。

如果你查询 select * from A wher……
[/Quote]
我建了一个10个字段的表，一共1W条记录，我把col4都设置为1，查询默认是不使用索引的，explain看了一下rows是7500.然后强制使用索引，rows是9千多的样子，这个rows到底代表什么。。。网上有资料，没看懂。
然后我把一半的col4设置为2，然后查询这时候默认是使用索引的

[ACMAIN_CHM]

[Quote]所以说为重复性高的字段建索引最多是提高的效率不高。而不会像我上面说的那样反而降低查找速度？[/Quote]举个极端的例子

表A (ID,col1,col2,col3,col4,..col100) 100个字段，现在COL4上创建索引，而COL4中所有的值都为1, update a set col4=1 ，10000条记录，COL4都是1。

如果你查询 select * from A where col4=1; MYSQL就不会再去走索引。 因为如果走索引反而速度慢。 MYSQL会自行判断是否需要使用索引。这也是为什么经常会看到EXPLAIN中明明有索引，但并未被使用。


同样。即使 update a set col4=0 where id=10, 这样， 仅ID=10的记录COL4=0，而其余9999条记录仍是COL4=1。 同样select * from A where col4=1; 如果此时去 走索引开销 同样 比不走索引要大。

[zuoxingyu]

rows代表执行可能扫描的行数，一个估算值。

[shine333]

[Quote=引用 6 楼 imbigsnake1 的回复:]

我试了一下确实是这样，但是为什么这种情况下使用索引比全表扫描来的慢呢
[/Quote]
SELECT *的缘故。
由于*，即使用了索引，还是要扫描表，把符合条件的行的信息都取出来。由于COL4=1的比例太高，先检索索引，再去按图索骥扫描对应的行数，和直接扫描表没什么本质区别。甚至可能更慢

[iihero_]

其实，完全可以用查字典的方式进行类比啊。

比如，按拼音检索，某拼音只有简单的几个字，那么，很快就从拼音表里找到对应的字。因为该索引覆盖的数据少，很快就能从索引表里定位到数据(实际的字)的位置(页码)

可是，如果某拼音对应的字非常多，夸张点，比如，有20页的字对应这个拼音，那么，你先得从索引表里找出这20页的字，然后再顺序去读这20页的字。这个总的时间并没占多大优势，极端点，如果有半部字典的字对应同一个拼音 ，那还不如直接从头往后翻，顺序扫描。

[minitoy]

推至极端就可以了。假设你每条数据都一样，使用索引显然是没意义的。