MySQL索引学习笔记

myisam和innodb的索引有什么区别?

两个索引都是B+树索引,但是myisam的表存储和索引存储是分开的,索引存储中存放的是表的地址。而innodb表存储本身就是一个B+树,它是用主键来做B+树的key的。

因此innodb需要设置主键,如果没有的话,mysql会优先使用unique键做主键,如果没有unique的话,会生成一个隐含字段做主键。

innodb中的主键最好是要使用自增id,因为这样当进行插入操作的时候是不需要修改表的存储结构的。

innodb的辅助索引key是辅助索引,而value则是主键。那么这样的话,innodb中主键如果是md5之类的字符串的话,那么每个辅助索引的磁盘空间就占用很大,而且也有前一条的问题,当插入操作的时候还需要修改表结构。

覆盖索引

由于innodb的辅助索引带主键的机制就导致一种“覆盖索引”的使用。就是只需要在辅助索引中就可以进行到值查找而不需要进行查表。比如一个InnoDB表student字段有uid(主键),username,age。其中建立一个辅助索引username,那么如果你希望查找一个学生名为yejianfeng的学生是否存在:

select id from student where username=”yejianfeng”  (这个查询语句是使用到覆盖索引的,它只会去索引中进行一次查找,而不会查表,当然如果这个表是myisam表的话,就不会覆盖索引了)

select * from student where username=”yejianfeng”  (这个查询是使用不到覆盖索引的,它会去索引查找一次,然后根据主键id去表中再查找一次。所以说不要滥用*)

索引选择性

索引选择性是建立索引的参考指标,指的是该字段可选择的非重复项在总条数中占的比例。比如说一个表中有100000的条数,有个字段是性别字段,这个性别它只有两个选择,所以它的索引选择性就是2/100000 ~ 0.00002,那么这种情况下,我们就没必要再给这个字段单独加个索引。原因是当选择性太小的话,比如我进行一次查询查出来的100条中有50条是符合查询结果的,相比于选择性大的情况,我进行一次查询查出来的100条中只有5条是符合查询结果的,那么后一种建立索引让索引查询出来的结果变小的效果明显更大。而考虑到索引也占用资源以及增删操作的成本,选择性太小的字段就没有建立索引的必要了。

相关阅读:

相关推荐