主键索引 (聚集索引) 和普通索引 (辅助索引) 的区别

• 什么是聚集索引?

  首先innodb引擎默认在主键上建立聚集索引 , 通常说的主键索引就是聚集索引 , 聚集索引会保存行上的所有数据, 因此不需要额外的IO

• 什么是辅助索引?

  辅助索引(Secondary Index) , 叶子节点只保存了行的键值和指向对应行的"书签" , 一般指向的是聚集索引 , 此外innodb实现了覆盖索引(Covering index) , 即叶子节点除了保存该行的键值还保存了对应索引列的值 , 如果不需要额外数据的话则不需要另外对聚集索引中的数据进行IO

  注: SQL Server的主键索引和普通索引的区别仅仅是唯一非空,而mysql innodb下不是, 另外严格来说主键是约束

• 为什么性别列和其他低选择性的列不适合加索引?

  因为你访问索引需要付出额外的IO开销，你从索引中拿到的只是地址，要想真正访问到数据还是要对表进行一次IO。假如你要从表的100万行数据中取几个数据，那么利用索引迅速定位，访问索引的这IO开销就非常值了。但如果你是从100万行数据中取50万行数据，就比如性别字段，那你相对需要访问50万次索引，再访问50万次表，加起来的开销并不会比直接对表进行一次完整扫描小。

  当然凡事不是绝对，如果把性别字段设为表的聚集索引，那么就肯定能加快大约一半该字段的查询速度了。聚集索引指的是表本身中数据按哪个字段的值来进行排序。因此，聚集索引只能有一个，而且使用聚集索引不会付出额外IO开销。当然你得能舍得把聚集索引这么宝贵资源用到性别字段上。从性别字段不适合建索引说起

• 优化器为什么会选择覆盖索引用于count() 等统计问题?

  因为覆盖索引远小于聚集索引 , 可以减少磁盘IO操作

理解不一定正确,只作为记录,各位多多指教

参考

MySQL技术内幕:innodb存储引擎-第五章

高性能MySQL第三版 3.3剖析mysql查询 第6章 查询性能的优化

本作品采用《CC 协议》，转载必须注明作者和本文链接